Recolección automática de estadísticas:

En Oracle 11 existe un job que se encarga de recolección automática de estadísticas, tanto obsoletas como inexistentes para el optimizador basado en costos (CBO).
Las estadísticas automáticas no almacenan información sobre las filas encadenadas. Esto implica que no se almacena información para el campo CHAIN_CNT. Para obtener está información se debe utilizar el Automatic segment advisor.
Los intervalos de ejecución de job para recolectar estadísticas son:
Todas las noches de 10 p.m a 2 a.m
Todos los fines de semana de 6.a.m a 20.
En Oracle 11 el GATHER_STATS_JOB no existe. Este ha sido incluido en Automatic Maintenance Tasks.
Estas son las tareas que se programan en las ventanas de mantenimiento:

select client_name from dba_autotask_client
CLIENT_NAME

----------------------------------------------------------------

auto optimizer stats collection

auto space advisor

sql tuning advisor

Cómo se activan la recolección automática de estadísticas:

exec DBMS_AUTO_TASK_ADMIN.ENABLE(
 client_name => 'auto optimizer stats collection',
 operation => NULL,
 window_name => NULL);

Cómo se desactivan la recolección automática de estadísticas:

 exec DBMS_AUTO_TASK_ADMIN.DISABLE(
 client_name => 'auto optimizer stats collection',
 operation => NULL,
 window_name => NULL);

Cómo se desactivan la recolección automática de estadísticas:

select client_name, JOB_SCHEDULER_STATUS
 from DBA_AUTOTASK_CLIENT_JOB
 where client_name='auto optimizer stats collection';

Los posibles estatus son:
o DISABLED
o RETRY SCHEDULED
o SCHEDULED
o RUNNING
o COMPLETED
o BROKEN
o FAILED
o REMOTE
o SUCCEEDED
o CHAIN_STALLED
Cómo se desactivan la recolección automática de estadísticas:

 

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
 FROM dba_autotask_client_history
 WHERE client_name like '%stats%';
CLIENT_NAME WINDOW_NAME JOBS JOBS JOBS
 CREATED STARTED COMPLETED
 ------------------------------- ---------------- ------- -------- ----------
 auto optimizer stats collection THURSDAY_WINDOW 1 1 1
 auto optimizer stats collection SUNDAY_WINDOW 3 3 3
 auto optimizer stats collection MONDAY_WINDOW 1 1 1
 auto optimizer stats collection SATURDAY_WINDOW 2 2 2

Cómo se desactivan la recolección automática de estadísticas:

 SQL> exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

 

Este comando inicializa el job de recolección de estadísticas, salvo que ya este inicializado. Si crea tendrá un nombre similar a este: ORA$_AT_OS_MANUAL_nnnnnn.Al contrario que el job Automated Maintenance , este job manual no está asociado a ninguna ventana de mantenimiento.
Cómo comprobar los valor de parámetros (porcentaje de estimación, tipos de histogramas, etc) utilizados por el job:

 BMS_STATS.GET_PARAM (pname IN VARCHAR2) RETURN VARCHAR2;

Y para ver si han modificado las preferencias por defecto:

 DBMS_STATS.GET_PREFS (pname IN VARCHAR2,ownname IN VARCHAR2 DEFAULT NULL, tabname IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

Posibles preferencias:
o AUTOSTATS_TARGET
o CASCADE
o DEGREE
o ESTIMATE_PERCENT
o METHOD_OPT
o NO_INVALIDATE
o GRANULARITY
o PUBLISH
o INCREMENTAL
o STALE_PERCENT
Cómo se modifican las preferencias:

Si se modifican las preferencias se esto afectara al todos los esquemas incluido SYS

exec DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');

También existe:

 SET_DATABASE_PREFS

Qué significa parámetros AUTOSTAT_TARGET de SET_GLOBAL_PREFS
Este parámetro controla que objetos monitorizara el job de recolección automática de estadísticas. Los posibles valores de este parámetro son:
o ALL
Todos lo objetos de la base de datos.
o ORACLE
Solo los esquemas de Oracle (sys, system, etc)
o AUTO (default)
Oracle decide sobre que objetos debe ejecutar las estadísticas. Actualmente AUTO y ALL se comportan de la misma manera

Anuncios

Medir el rendimiento de Shared Pool

Este parte de memoria en Oracle se encarga de almacenar en memoria los últimos SQL y PLSQL ejecutados.

Se compone de tres segmentos:

1. Library cache

2. Row cache

3. UGA Si está configurada la opción de Shared Server en vez de conexiones dedicadas.

1. Library cache:

Cuando escribimos la siguiente sentencia:

SQL> SELECT empno, ename 
FROM emp WHERE empno = 1;

Oracle implícitamente realiza las siguientes acciones:

1. Convierte los caracteres del sql en sus correspondientes códigos numéricos de ASCCI

2. Estos caracteres ASCII se pasan a través de un algoritmo de hash el cual produce un único valor.

3. Oracle comprueba que este valor exista en la Shared Pool. Si existe,el proceso de servidor del usuario utiliza la versión cacheada de la sentencia para ejecutarla. Si no tiene que parsear la sentencia.

Parsear de nuevo la sentencia significa un costo extra, ya que se deben volver a ejecutar los siguientes pasos: Comprobación de los errores de sintaxis

Comprobación de los privilegios sobre los objetos que componen la sentencia.

Recoger las estadísticas para los objetos referenciados.

Preparar el plan de ejecución en función de las estadísticas disponibles.

Generar el P code.

Cuando oracle encuentra la sentencia en la Shared Pool se llama Cache hit y cuando no Cache miss. Por tanto aumentar los porcentajes de cache hint y disminuir los de cahe miss es uno de los retos a la hora de ajustar la Shared Pool.

Medir el rendimiento de la library cache requiere del análisis de los ‘HIT RATIOS’.

Esto se hace consultando la vista V$librarycache:

SQL>SELECT namespace, gets,gethits,pins,pinhits,gethitratio,pinhitratio,reloads, invalidations 
           FROM v$librarycache;

Gethitratio

Cada vez que una sentencia es parseada, el valor de GETS en la vista V$LIBRARYCACHE se incrementa en 1. La columna GETHIT almacena el número de veces que el código SQL y PL/SQL es encontrado en la shared pool con lo cual no requiere parsearlo. Por lo tanto porcentajes cercanos al 90 % son muy buenos

Pinhitratio

Mientras el GETS está relacionado con los bloqueos que se producen en tiempo de parseo, el PINS refleja los bloqueos que se producen en tiempo de ejecución. Cada vez que una sentencia es ejecutada, el valor de PINS se incrementa en 1. La columna PINHITRATIO en V$LIBRARYCACHE refleja la frecuencia con la una sentencia ejecutada encuentra su correspondiente parseo. Valores cercanos al 90% son muy óptimos

Reloads

La columna RELOADS muestra el número de veces que una sentencia ejecutada tiene que re-parsed porque la Library Cache esta anticuadao no sirve la versión parseada de la sentencia. Se puede calcular la numero de reloads comparándolo con la sentencias ejecutadas(PINS):

 SQL> SELECT SUM(reloads)/SUM(pins) "Reload Ratio" 
         FROM v$librarycache 
             Reload Ratio
             ------------ 
             .001501321 0.15

significa que no hay sentencias re-parseadas

Invalidation

Las invalidaciones se producen cuando una sentencia cachada se marca como invalida y se le fuerza a parsearse otra vez a pesar de que esta en la library cache porque alguno de los objetos que componen la sentencia ha sido modificado de cualquier manera.

2. ROW Cache:

El Dictionary Cache hit ratio muestra la frecuencia en una aplicación encuentra la información del diccionario datos que necesita en memoria en vez de tener que leerlo de disco. Esta información de it-ratio se encuentra la vista dinámica V$ROWCACHE.

SQL> SELECT   (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio" 
            FROM v$rowcache; 
 Data Dictionary Hit Ratio 
------------------------- 
.989400103

Ejecutar las estadísticas

El paquete DBMS_STATS permite generar y manejar las estadísticas para manejar el optimizador basado en costos. Los procedimientos que componene este paquete son:

Procedure Recolecta

GATHER_INDEX_STATS                 Las estadísticas de índices
GATHER_TABLE_STATS                 Estadísticas de tablas ,columnas e índices
GATHER_SCHEMA_STATS             Estadísticas de todos los objetos del esquema
GATHER_DATABASE_STATS        Estadísticas de todos los objetos de la base de datos
GATHER_SYSTEM_STATS              Estadísticas del sistema sobre CPU y I/O

A continuación vamos a porner algunos ejemplos de como se ejecutan las estadísticas.
Obtener estadísticas de la table empleados en el esquema scott:

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'EMPLEADOS' ) ;
PL/SQL procedure successfully completed.

Los dos parametros OWNNAME para el nombre del esquema y TABNAME para el nombre de la tabla son obligatorios. En el ejemplo se utiliza la sintaxis con el nombre de los parámetros antes de poner su valor (ownname=>) para identificarlos. Se pude poner simplemente el valor sin identificar el parámetro, siguiendo el orden en que están definidos en el paquete, por ejemplo;

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT' , 'EMPLEADOS' ) ;
PL/SQL procedure successfully completed.

El procedimiento tiene parámetros adicionales como ESTIMATE_PERCENT
Si no se especifican los parámetros adicionales se le da un valor por defecto. En los ejemplos anteriores al no especificarse el parámetro ESTIMATE_PERCENT se calculan las estadísticas completas.
Si todos lo parámetros no caben en una línea o se quiren escribir en varías líneas, se debe escribir así el procedimiento:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname=>'SCOTT' ,
    tabname=>'EMPLEADOS' ,
    cascade=>TRUE) ;
END;
/

El parámetro CASCADE le indica al procedimiento que recoja las estadísticas tanto de los índices como de las tablas. Para conocer los diferentes parámetro que tiene los procedimientos se puede utilizar el comando DESCRIBE de SQL*Plus.
La diferencia entre Oracle 10g y superiores y Oracle 9 I es que en Oracle 9 la actualización de las estadísticas no se hace automáticamente, normalmente se utiliza el cron o un servicio en Windows o se puede crear un job de base de datos.

Comando ANALYZE:


Se puede utilizar también la sentencia ANALYZE para generar las estadísticas para el optimizador basado en costos.
Oracle recomienda que se utilice el paquete DBMS_STATS en vez de ANALYZE para las estadísticas. DBMS_STATS permite recolectar estdísticas en forma paralela, recolecta estadísticas para objetos particionados, etc. Incluso en algunos casos el obtimizador basado en costos solo utilizará las estadísticas generadas por el DBMS_STATS.
El comando ANALIZE solo se de utilizar para recolectar estadísticas no relacionadas con el optimizador basado en costos como:
    El uso de las sentencias VALIDATE o LIST_CHINED_ROWS
    Recolectar información sobre freelist bolques.