Consultas basicas para lob segments

Posted on 11 marzo, 2018 por admin

Hoy vamos a otra de estas entradas para dummies que recopilan SQL utles, en este caso para tratar con los lobs.
Las variables de formateo del sqlplus para estas consultas serian

set linesize 180 pagesize 900
column SEGMENT_NAME format a40;
column TABLE_NAME format a60;
column TABLESPACE_NAME format a30;
column owner format a20;

Lista de los lobs mas grandes y lo que ocupan para el esquema

select  e.owner,l.tablespace_name,
l.table_name,
l.segment_name,sum(e.bytes/(1024*1024*1024)) Gb
from dba_extents e,dba_lobs  l
where
	e.owner = l.owner
	and 	e.segment_name = l.segment_name
	and 	e.OWNER='ESQUEMA'
	and 	e.segment_type = 'LOBSEGMENT'
     group by  
     e.owner,l.tablespace_name,
    l.table_name,
     l.segment_name 
     order by Gb desc ;

Obtener los datos (esquema,tabla y columna) de un LOB determinado

select OWNER,TABLE_NAME,COLUMN_NAME from dba_lobs
 where SEGMENT_NAME='SYS_LOBXXXXXX$$';

Bytes ocupados por un LOB

select sum(dbms_lob.getlength (COLUMNA))/1024/1024 Mb ESQUEMA.TABLA;
o bien 

select bytes/1024/1024 Mb

from dba_segments where segment_name ='SYS_LOBXXXXXX$$' ;
o esta mas completa 
set serveroutput on
declare
     l_segment_size_blocks NUMBER;
     l_segment_size_bytes NUMBER;
     l_used_blocks NUMBER;
     l_used_bytes NUMBER;
     l_expired_blocks NUMBER;
     l_expired_bytes NUMBER;
     l_unexpired_blocks NUMBER;
     l_unexpired_bytes NUMBER;
     l_unused_blocks NUMBER;
     l_unused_bytes NUMBER;
     l_non_data_blocks NUMBER;
     l_non_data_bytes NUMBER;
 BEGIN
	DBMS_SPACE.SPACE_USAGE(
   	  segment_owner =>'ESQUEMA',
	  segment_name => 'SYS_LOB0000227238C00034$$',
	  segment_type => 'LOB',
	  segment_size_blocks => l_segment_size_blocks,
	  segment_size_bytes => l_segment_size_bytes,
	  used_blocks => l_used_blocks,
	  used_bytes => l_used_bytes,
	  expired_blocks => l_expired_blocks,
	  expired_bytes => l_expired_bytes,
	  unexpired_blocks => l_unexpired_blocks,
	  unexpired_bytes => l_unexpired_bytes
           );
      l_unused_blocks := l_segment_size_blocks - (l_used_blocks + l_expired_blocks + l_unexpired_blocks);
      l_unused_bytes := l_segment_size_bytes - (l_used_bytes + l_expired_bytes + l_unexpired_bytes);
	  l_non_data_blocks := l_unused_blocks + l_expired_blocks + l_unexpired_blocks;
	  l_non_data_bytes :=  l_unused_bytes + l_expired_bytes + l_unexpired_bytes;
	  DBMS_OUTPUT.ENABLE;
	   DBMS_OUTPUT.PUT_LINE(' Segment Blocks/Bytes   = '||l_segment_size_blocks||' / '||l_segment_size_bytes);
	  DBMS_OUTPUT.PUT_LINE(' Unused Blocks/Bytes    = '||l_unused_blocks||' / '||l_unused_bytes);
	  DBMS_OUTPUT.PUT_LINE(' Used Blocks/Bytes      = '||l_used_blocks||' / '||l_used_bytes);
	  DBMS_OUTPUT.PUT_LINE(' Expired Blocks/Bytes   = '||l_expired_blocks||' / '||l_expired_bytes);
	  DBMS_OUTPUT.PUT_LINE(' Unexpired Blocks/Bytes = '||l_unexpired_blocks||' / '||l_unexpired_bytes);
	  DBMS_OUTPUT.PUT_LINE('===========================================================================');
	  DBMS_OUTPUT.PUT_LINE(' NON Data Blocks/Bytes  = '||l_non_data_blocks||' / '||l_non_data_bytes);
 END;
 /

 Mover una tabla con  Lobs  ( para hacer shrink) 
Este metodo implica bloqueo durante el traslado 
ALTER TABLE ESQUEMA.TABLA MOVETABLESPACE NUEVOTABLESPACE;
Y para cada uno de los lobs de la tabla 

ALTER TABLE ESQUEMA.TABLA MOVE LOB(COLUMNA) STORE AS SECUREFILE (TABLESPACE NUEVOTABLESPACE);

Apagando instancias

Posted on 8 febrero, 2018 por admin

Responder

Hoy vamos aver un pequeño script que nos facilitara el apagar todas las bases de datos de un servidor aunque no esten incluidas en el rac

Si nos fijamos un poco en el script, podemos ver como las funciones de apagado estan comentadas, esto es una mera medida de seguridad para evitar posibles errrores de «copy & paste »

[cc lang=»shell»]
#!/bin/bash
#
#
# Name: stop_oratab_alone.sh
# Desc: Stops all oratab databases (not RAC)

function estado
{
echo » The status of $ORACLE_SID is:»
sqlplus -s «/as sysdba» << EOF 2>>/dev/null
set linesize 400;
column host_name format a30;
column instance_name format a20;
column Started format a25;
column uptime format a60;
select
host_name,
instance_name,
to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) Started,
‘Uptime : ‘ || floor(sysdate – startup_time) || ‘ days(s) ‘ ||
trunc( 24*((sysdate-startup_time) –
trunc(sysdate-startup_time))) || ‘ hour(s) ‘ ||
mod(trunc(1440*((sysdate-startup_time) –
trunc(sysdate-startup_time))), 60) ||’ minute(s) ‘ ||
mod(trunc(86400*((sysdate-startup_time) –
trunc(sysdate-startup_time))), 60) ||’ seconds’ uptime
from
sys.v_\$instance;
EOF

}

function apagar
{
#srvctl stop database -d ${ORACLE_DB} ${ORACLE_SID} -stopoption IMMEDIATE

}

function test_status
{
srvctl status database -d ${ORACLE_DB} ${ORACLE_SID} RED=’\033[0;31m’
GREEN=’\033[0;32m’
NC=’\033[0m’
COUNT=` ps -ef |grep smon |grep -v grep | grep $ORACLE_SID|wc -l `
if [ ${COUNT} -ne 0 ]; then
printf «${RED}FAIL${NC} There are ${COUNT} smon_${ORACLE_SID} processes\n»
else
printf «${GREEN} OK ${NC} There is not any smon_${ORACLE_SID} process\n»
fi
}

export ORAENV_ASK=NO
export NODO=`hostname -a`
for i in `cat /etc/oratab |tr ‘:’ ‘ ‘ |awk ‘{ print ($1)’}|grep -v «#»|grep -v «+»|grep -v «-» |grep -v [1-9]`
do
clear
ORACLE_DB=$i
ORACLE_SID=$i
. oraenv 2>/dev/null
export ORACLE_SID=`ps -ef|grep pmon|grep $i |tr ‘_’ ‘ ‘|awk ‘{ print ($10)’}`
echo «New oracle SID=$ORACLE_SID»
estado;
#apagar
test_status;

read
echo «[press any key]»
done

[/cc]

Este script tiene una particularidad, y es que no esta pensado para usarse en RAC, para usarse en un RAC solamente habra que modificar la sintaxsis de apagado incluyendo el instance name y el nodo

Exportando las tablas de auditoria

Posted on 31 enero, 2018 por admin

Responder

Hoy vamos a volver a las entradas rapidas para dummies.

Una de las tareas que podemos tener que hacer es el respaldar las tablas de auditoria para eliminarlas posteriormente. La primera opción que nos viene a la cabeza es hacer un export (expdp), pero si lo intentamos recibiremos el error:

ORA-39166: Object SYS.AUD$ was not found.
ORA-31655: no data or metadata objects selected for job

¿Que estamos haciendo mal?
Realmente no estamos haciendo nada mal , el problema es que el nuevo expdp no nos va a dejar exportar estas tablas (asi como objetos de los esquemas SYS,SYSTEM … )

¿Como los solucionamos ?

Con un CTAS ( create table as select)

Esta es la opcion mas extendida en los foros de soporte de oracle, los pasos serian

CREATE TABLE MIAUDIT AS SELECT * FROM SYS.AUD$
TRUNCATE SYS.AUD$
EXPDP xxxxx table=MIAUDIT
DROP TABLE MIAUDIT

¿Cual es el problema de esta opcion ?
Seguramente, si neceistamos exportar y truncar la tabla de auditoria es por que ya esta ocupando demasiado espacio, por lo que la opción de duplicar este espacio en la base de datos no siempre es viable.

Usar el export clasico

Cuando la opción del CTAS no es valida, nos queda siempre el uso del exp clásico.
La herraienta antigua de exportación no tiene estas limitaciones respecto a os objetos del sistema y si que nos permitira exportar los datos

Mas información como siempre en los foros de soporte de oracle o en la nota :

How to Export the AUD$ Table (Doc ID 745540.1)

Uptime de una base de datos Oracle

Posted on 22 enero, 2018 por admin

Responder

Hoy vamos a volver con una nueva entrada para muy dummies.

Como averiguamos hace cuanto tiempo esta la base de datos levantada?

Podemo usar alguna de estas consultas

select instance_name,
to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time 
from v$instance;

O si asumimos que es cuando arranco el pmon

SELECT database_name, 
TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS') 
FROM v$session WHERE program LIKE '%PMON%';

O como siempre, podemos buscar que nos dicen en Burleson colsulting

select 
   'Hostname : ' || host_name
   ,'Instance Name : ' || instance_name
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) - 
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from 
   sys.v_$instance;

Checklist de SQLtuning en la version 12c

Posted on 6 diciembre, 2017 por admin

Responder

Hoy vamos a seguir con pequeñas notas sobre la nueva version 12c, en este caso veremos mejoras en la gestion de estadisticas y planes

Uso de Adaptative Execution Plans/adaptative query optimization

La nueva funcionalidad de los planes de ejecicion flexibles (también llamado optimización de querys flexibles) permite que el optimizador sea capaz de ajustar los planes en tiempo de ejecución y obtener información adicional que le permita obtener mejores planes de ejecución.
En ved de usar planes de ejecución fijos esta funcionalidad permite al optimizador el posponer el plan hasta el momento de la ejecución.
Esta funcionalidad se basa en dos métodos:

Adaptative plans: Se centra en mejorar el plan del optimizador en runtime
Adaptative statistics: Se centra en obtener estadísticas adicionales para mejorar las siguientes ejecuciones de una consulta

Adaptative plans

Muchas veces el optimizador falla en la cardinalidad de las consultas generando un plan deficiente ( default plan), la funcionalidad de adaptative plans ajusta el plan a las estadísticas actuales, que son mas exactas que las estadísticas iniciales que fueron obtenidas en una ejecución anterior. Cuando selecciona el plan el optimizador usará este plan en las siguientes ejecuciones (del child cursor)
Esta funcionalidad depende de los dos siguientes parámetros

optimizer_features_enable            string  12.1.0.2
optimizer_adaptive_reporting_only    boolean FALSE

Si ponemos el parámetro OPTIMIZER_ADAPTIVE_REPORTING_ONLY=TRUE el optimizador si que obtiene las estadísticas, pero no las usará (usará el default plan)
Lo que hace el optimizador con esta funcionalidad es que obtiene multiples subplanes para porciones del plan para determinar en tiempo de ejecución que método usar. Una vez el optimizador decide un plan dejará de recopliar estadísticas y usará este plan para las siguientes ejecuciones del child cursor
Podemos saber cuales de las SQL han usado esta funcionalidad mediante la nueva columna de la vista v$SQL IS_RESOLVED_ADAPTIVE_PLAN que tendría un Y
Cuando una consulta usa el Adptative Query Optimization el plan mostrado en el EXPLAIN PLAN no tiene por que ser el que ha usado, para ver el que ha usado deberíamos de usar la función XDBS_XPLAN.DISPLAY_CURSOR
Este método también es “muy útil” con las ejecuciones paralelas , aquí se usa una técnica llamada hybrid data distribution

Adaptative statistics

Las Adaptative statistics son usadas por el optimizador para lidiar con los predicados complejos. En la versión 12 el dynamic sampling ha sido renombrado como dynamic statistics y puede obtener estadísticas no solamente de una tabla sino también de joins y predicados del tipo group-by
Esto se gestiona mediante el parámetro OPTIMIZER_DYNAMIC_SAMPLING (modificable a nivel sesión),que puede tener valores entre 0 y 11

LEVEL

WHEN
DYNAMIC STATISTICS WILL BE USED

SAMPLE
SIZE (BLOCKS)

Switches
off dynamic statistics

N/A

At least one non-partitioned
table in the statement has no statistics

2(default)

One or
more tables in the statement have no statistics

This
setting is suitable for the majority of systems

Any
statement that meets level 2 criteria and any statement that has one or more
expressions used in the where clause predicates e.g. Where
substr(CUSTLASTNAME,1,3) or Where a + b =5

Any statement that meets level 3 criteria and any
statement that has complex predicates. An OR or AND operator between multiple
predicates on the same table

Any
statement that meets level 4 criteria

128

Any statement that meets level 4 criteria

256

Any
statement that meets level 4 criteria

512

Any statement that meets level 4 criteria

1024

Any
statement that meets level 4 criteria

4086

All statements

All Blocks

The
database determines automatically if dynamic statistics are required

Automatically
determined

AUTOMATIC REOPTIMIZATION

Muchas veces los Apdaptative plans no son posibles(queryes con joins ineficientes ..), en esos casos el optimizador opta por esta funcionalidad que es el automatic reoptimization, donde el optimizador cambia el plan para las siguientes ejecuciones.
El optimizador usa estos dos métodos

Statistics feedback

También llamado cardinality feedback , lo que hace el planificador en este caso es mejorar los planes en los que encuentra cardinalediades erróneas , cuando se usa eeste método el optimizador compara las estadísticas que tiene con las cardinalidades observadas durante la ejecución, en caso de que difieran significativamente el optimizador hace dos cosas:
1- Guarda la cardinalidad nueva en las estadísticas
2- Crea un SQLplan mejor para las siguientes ejecuciones de esta consilta
Performance feedback
Esta funcionalidad neceita tener el parámetro PARALLEL_DEGREE_POLICY=ADAPTATIVE
Está mas enfocado a el grado de paralelismo, tras la ejecución compara el grado de paralelismo elegido por el optimizador con el grado de paralelismo,si los dos varían significativamente, el optimizador lo marcará para reoptimizacion y guardará las estadísticas para un mejor plan en la siguientes execuciones

SQL Plan directives

Creados en shared pool y bajados a sysaux

Creados para SQL con cardinalidad incorrecta

NO Gestionados
por el optimizador (solo usados por)

NO para
recopilar estadísticas de objetos que la tienen mal

Uso avanzado de obtencion de estadisticas

La versión 12c nos aporta mejoras substanciales en lo que a la obtención de estadísticas se refiere

Estadisticas online para bulk-load
incremental statisticas
concurrent statistics
Extended statistics (gloumn group)

La version 12c introduce una mejora im

Online Statistics gathering for bulk-load

La version 12c introduce una mejora importante en la obtención de estadísticas en los procesos de carga BULK-LOAD, ahora el optimizador regoje estadísticas d manera automática en procesos de carga de este tipo, esto incluye los procesos deCTAS (create table as), INSERT INTO …

La base de datos obtendrá las estadísticas del objeto, es decir, si se hace sobre una tabla particionada obtendrá estadísticas a nuivel de tabla, pero no de paticion, si se hace sobre una partición si que obtiene estadísticas a nivel de partición , pero no a nivel global de la tabla.
La base de datos no obtendrá estadísticas en los casos de
• Esquema SYS
• Es una nested table
• Es un IOT (index organized table)

Incremental statistics

Antes de la 12c cuando había cambios en las filas de una de lsparticiones de la tabla cambiaban todaslas estadísticas de la tabla quedaban como STALE. En la 12c tenemos u nuevo parámetro llamado INCREMENTAL_STALENESS que nos permite determinar cuando las estadísticas de las particiones deben de ser consideradas como STALE.
El valor por defecto de este parámetro es NULL (lo que hace que se comporte como en la 11g)
Puede tomar los valores:
• USE_STALE_PERCENT: Las estadísticas de la partición no serán consideradas STALE hasta que el número de filas que se cambien no sea mayor del porcentaje que se indique ( 10% por defecto)
• USE_LOCKED_STATS: Si las estadísticas de la partición están bloqueadas se usarán para generar las estadísticas globales indepeneientemente de las filas que hayan sido modificadas
Otra mejora en las estadísticas incrementales está relaccionada con el EXCHANGE PARTITION, el Exchange partition permite meter los datos de una tabla como una partición de otra.

Concurrent statistics

Ahora es posible obtener estadísticas de la tabla y de los índices de manera concurrente,se puede configurar incluso que tablas particionadas quieres que usen esté método y cual no.
Para poder usar esta funcionalidad es necesario
• JOB_QUEUE_PROCESSES debe de ser mayor de 4
• Tener el resource manager habilitado

Esta funcionalidad usa la propiedad CONCURRENT del paquete DBMS_STATS en las prefrencias globales. Este puede tener 3 valoers
• MANUAL.concurrencia habilitada solo para las ejecuciones manuales
• AUTOMATIC: Concurrencia habilitado solo apra las automáticas
• ALL: habilitado para todas

El valor por defecto es OFF

SELECT DBMS_STATS.GET_PREFS('CONCURRENT') FROM DUAL;
OFF

Extended statistics/column group

La idea de esta funcionalidad es la de obtener estadísticas en casos en que los predicados son complejos. Ahora se pueden hacer dos tipos mas de estadísticas:
• Column group statistics: Estadistias sobre grupos de columnas
• Expression statistics: Estadisticas sobre columnas que son parte de un Where

El proceso para obtener este tipo de estadistiacas es:
1-Habilitar la monitorización de la carga, para esto se usa el paquete SEED_COL_USAGE del DBMS_STATS
exec DBMS_STATS.SEED_COL_USAGE(null,null,600)
2-Ejecutas la carga de trabajo
3-Revisas el uso de las columnas con DBMS_STATS.REPORT_COL_USAGE
Select DBMS_STATS.REPORT_COL_USAGE(‘ESQUEMA’,’TABLA’) from dual
4-creas las estadisticas grupales que consideres con DBMS_STATS.CREATE_EXTENDED_STATS
Select DBMS_STATS.CREATE_EXTENDED_STATS(‘ESQUEMA’,’TABLA’) from dual

Uso de Adaptive SQL Plan Management (SPM)

El SPM es el mecanismo que tiene oracle dede la 11c para evitar que los planes de ejecución se degraden, el SPM captura los SQLplans, verifica su rendimiento y en caso de ser mejorables los evoluciona.

El optimizador solamente utiliza los “know plans”, si fijamos a TRUE el parámetro OPTIMIZER_CAPTURE_SQLPLAN_BASELINES, entonces capturará los planes, pero seguirá usando solo los conocidos. El trabajo nocturno DBMS_SPM.EVOLVE_SQL_PLAN_BASELINES comprueba los planes con carga mas alta y mira a ver si hay un plan mejor, para que , de manera manual el DBA lo evolucione y se use ese plan.
Este conjunto de planes conocidos es lo que llamamos una BASELINE
Esto forma parte del TUNNING PACK
SPM consiste en tres componentes
1-SQLPLAN baseline capture
2-SQLPLAN baseline selection
3-SQLPLAN baseline evolution

En la 12c Oracle ofrece el SPM Evolve Advisor que evoluciona los planes recientemente añadidos sn necesidad de que el DBA

Normalmente los pasos son:
• Create an evolve task
• (optionally) set evolve tas parameters
• Execute the evolve task
• Implement the recoomendatios in the task
• Report the task outcome

Dba stuff

Un cajon desastre para el día a día

Archivo de la categoría: dummie

Consultas basicas para lob segments

Lista de los lobs mas grandes y lo que ocupan para el esquema

Obtener los datos (esquema,tabla y columna) de un LOB determinado

Bytes ocupados por un LOB

Mover una tabla con Lobs ( para hacer shrink)

Apagando instancias

Exportando las tablas de auditoria

Con un CTAS ( create table as select)

Usar el export clasico

Uptime de una base de datos Oracle

Checklist de SQLtuning en la version 12c

Uso de Adaptative Execution Plans/adaptative query optimization

Adaptative plans

Adaptative statistics

AUTOMATIC REOPTIMIZATION

Statistics feedback

SQL Plan directives

Uso avanzado de obtencion de estadisticas

Online Statistics gathering for bulk-load

Incremental statistics

Concurrent statistics

Extended statistics/column group

Uso de Adaptive SQL Plan Management (SPM)