Estadísticas
Introducción
Para que Oracle pueda resolver una consulta SQL de forma óptima necesita conocer los datos físicos de cada tabla, índice o cluster que intervienen en ella. Estos datos se almacenan en el diccionario de datos y pueden ser consultadas usando las vistas adecuadas: dba_tables, dba_tab_statistics, dba_tab_col_statistics, dba_tab_histograms, dba_indexes, dba_ind_statistics… etc. En concreto, es el optimizador quién de todos los planes de ejecución posibles analiza estos datos estadísticos para evaluar cuál de todos supone un menor coste en términos de I/O y uso de CPU.
Mantenimiento
Las estadísticas deben reflejar verazmente el estado físico de los objetos, y conforme vayan sufriendo transformaciones éstas se irán quedando obsoletas. Es por esto por lo que hay que recopilar las estadísticas de nuevo a medida que ya no reflejen la situación real de los objetos a los que describen.
Si una tabla no posee estadísticas, Oracle puede recopilar ciertos datos dinámicamente en el momento de la consulta. Esto se llama muestreo dinámico “dynamic sampling”. El parámetro que gobierna este comportamiento es OPTIMIZER_DYNAMIC_SAMPLING.
Toma de estadísticas automáticamente
Las estadísticas se pueden recopilar manual o automáticamente. Por omisión, Oracle recopila las estadísticas a través del job programado con scheduler en una ventana de tiempo predefinida: de lunes a viernes de 22:00 a 6:00 y sábados y domingos completos. Durante esta recopilación automática se toman datos de tablas sin estadísticas o con estadísticas obsoletas (que han sufrido más de 10% de modificaciones desde la anterior toma de datos) pero antes debe haberse configurado el parámetro STATISTICS_LEVEL a un valor distinto de BASIC.
Para seguir el número de registros modificados existe la vista all_tab_modifications. Ésta muestra el número aproximado de inserciones, modificaciones, borrados y truncados que ha sufrido la tabla desde la última recopilación. La vista es consultable por el usuario pero hay que tener en cuenta que estos datos no son visibles inmediatamente. Existe un retardo de unos minutos para evitar que la escritura interfiera en el rendimiento. No obstante, se puede volcar la información a la vista usando el procedimiento FLUSH_DATABASE_MONITORING_INFO del paquete DBMS_STATS.
Toma de estadísticas manualmente
Para recopilar las estadísticas
manualmente hay que usar el paquete DBMS_STATS. En versiones anteriores se usaba la sentencia ANALYZE que ya ha quedado obsoleta. El paquete DBMS_STATS tiene métodos para:
- Recopilar estadísticas.
- Borrar estadísticas.
- Guardar y asignar estadísticas.
- Crear o borrar tablas donde guardar juegos de estadísticas.
- Bloquear y desbloquear las estadísticas de las tablas.
- Retornar las estadísticas que tenía una tabla en un momento dado.
Como se puede apreciar se pueden hacer muchas cosas con este paquete. Para la recopilación de estadísticas existen métodos que recopilan de toda la base de datos (GATHER_DATABASE_STATS), para un esquema determinado (GATHER_SCHEMA_STATS), para una tabla determinada (GATHER_TABLE_STATS), o para un índice determinado (GATHER_INDEX_STATS). Asimismo, también existen métodos para recopilar estadísticas de las tablas e índices del diccionario de datos (GATHER_DICCIONARY_STATS), para las vistas de rendimiento dinámico (GATHER_FIXED_OBJECTS_STATS) y para la recopilación de estadísticas del sistema (GATHER_SYSTEM_STATS).
Muchos de los métodos comparten los siguientes parámetros:
- Ownname: indica el propietario de los objetos a analizar.
- Tabname: indica el nombre de la tabla a analizar.
- Partname: indica el nombre de la partición de la tabla a analizar.
- Estimate_percent: si su valor es NULL se obliga a Oracle ha recorrer toda la tabla/índice. Este método logra la mayor exactitud pero puede llevar un tiempo muy grande. Para solucionarlo se puede indicar que se haga una estimación en base a un porcentaje de la tabla. De esta forma, Oracle recorre ese porcentaje y halla las estadísticas suponiendo que se ha recorrido la tabla entera. Por omisión, su valor esAUTO_SAMPLE_SIZE, lo que indica que Oracle escoge si recorrer total o parcialmente la tabla y si es parcial cuánto debe recorrer. Si se indica un valor debe estar en el rango 0.000001 – 100 (%).
- Block_sample: durante la recopilación de estadísticas en modo estimación Oracle escoge al azar filas hasta completar el porcentaje indicado en estimate_percent. Si el parámetro block_sample se pone a TRUE se le indica a Oracle que no escoja filas al azar sino bloques. Este método es más rápido puesto que una vez escoge el bloque lo lee entero (computa todas sus filas) haciendo que el número de saltos aleatorios sea menor. Por el contrario, si las filas no están uniformemente distribuidas a lo largo de todos los bloques puede ser que la estimación resulta menos exacta.
- Degree: cabe la posibilidad de paralelizar la toma de estadísticas con este parámetro. Por omisión, su valor es NULL. Esto indica que se use el grado de paralelismo que tenga la tabla (asignado mediante ALTER TABLE … DEGREE x).
- Granularity: esta parámetro solo tiene relevancia para tablas particionadas. Con él se indica si queremos que se tomen únicamente estadísticas globales de tabla (GLOBAL), de las particiones (PARTITION), de las subparticiones (SUBPARTITION), global y particiones (GLOBAL AND PARTITION) o todas –global, particiones y subparticiones– (ALL). El valor por omisión es AUTO, que permite a Oracle elegir qué recopilar en función del tipo de objeto de que se trate.
- Cascade: si su valor es TRUE al analizar una tabla también se analizan sus índices. Por omisión es TRUE.
- Force: si su valor es TRUE se analiza la tabla incluso si sus estadísticas están bloqueadas.
Para analizar la base de datos completa se puede usar este ejemplo:
SQL> exec dbms_stats.gather_database_stats;
Procedimiento PL/SQL terminado correctamente.
En este caso no se ha indicado ningún parámetro dejando a Oracle los valores por omisión. Para analizar un esquema entero:
SQL> exec dbms_stats.gather_schema_stats (‘USUARIO’);
Procedimiento PL/SQL terminado correctamente.
Para analizar los objetos de los esquemas SYS, SYSTEM se usa el método DBMS_STATS.GATHER_DICTIONARY_STATS. Éste método tiene casi todos los parámetro vistos anteriormente.
Copia de seguridad de la estadísticas
Existe la posibilidad de crear una tabla para guardar una copia de seguridad de las estadísticas que tiene una tabla antes de recalcularle otras. Esto está pensado para los análisis no completos, esto es, para cuando se hacen estimaciones con un pobre porcentaje en tablas muy grandes. Como ya se ha indicado, cuando se analiza una tabla por estimación, Oracle escoge al azar filas o bloques analizándolos. Este procedimiento azaroso puede resultar en una estimación de diferente exactitud, esto es, diferentes estimaciones pueden dar resultados muy distintos. Por esta razón, es muy interesante que al analizar una tabla por estimación se guarden previamente sus estadísticas actuales, de forma que si la nueva estimación resulta menos eficaz se puedan restaurar las estadísticas que tenía.
Para crear esta tabla de copia de seguridad se usa el procedimiento DBMS_STATS.CREATE_STAT_TABLE que tiene por parámetros el nombre del esquema donde se crea y el nombre que recibe la tabla, y el tablespace. Veamos un ejemplo:
SQL> select count(1) from user_tables where table_name='TB_STATS';
SQL> select count(1) from user_tables
A lo hora de analizar la tabla se indica cuál es la tabla de copia de seguridad a través de los siguientes campos:
- Stattab: nombre de la tabla (creada con CREATE_STAT_TABLE) donde guardar las estadísticas.
- Statid: identificador dado a las estadísticas que se guardan (por omisión NULL). Se usa para identificarlas fácilmente.
- Statown: propietario de la tabla donde se guardan las estadísticas.
Un ejemplo donde se analiza la tabla SYSTEM.HELP y se dejan las estadísticas actuales en SYSTEM.TB_STATS es el siguiente:
SQL> begin
dbms_stats.gather_table_stats(ownname=>'SYSTEM',
tabname=>'HELP',
stattab=>'TB_STATS',
statid=>'help_1',
statown=>'SYSTEM' );
end;
/
Procedimiento PL/SQL terminado correctamente.
Con este procedimiento, si ya existen estadísticas guardadas con el mismo statid se sobrescriben. Nótese igualmente, que si es la primera toma de estadísticas de una tabla no se escribe nada en la tabla tabname, ya que no hay nada que salvar.
Para restaurar las estadísticas se tiene que usar DBMS_STATS.IMPORT_TABLE_STATS. Si no se indica ningún statid Oracle restaurará la estadísticas con statid=NULL. Veamos un
ejemplo:
SQL> begin
dbms_stats.import_table_stats(ownname=>'SYSTEM',
tabname=>'HELP2',
statid=>'help_1',
stattab=>'TB_STATS');
end;
/
Procedimiento PL/SQL terminado correctamente.
Estadísticas del sistema
Además de las estadísticas de las tablas existen también las estadísticas del sistema que muestran datos sobre el rendimiento del hardware/sistema operativo. Entre los datos que se recopilan está la velocidad de la cpu, la velocidad de transferencia de los discos, tiempo de localización de un bloque en lectura secuencial y aleatoria y número de bloques leídos secuencialmente en una lectura multibloque.
Estas estadísticas se deben tomar cuando haya carga de trabajo en la máquina que para las cifras obtenidas sean los más reales posibles. Por ello, existen dos métodos: las estadísticas workload y noworkload.
Cuando se ejecutan las workload se toman tiempos suponiendo que la instancia en está en plena carga. Para tomar estas estadísticas hay que hacer dos instantáneas en diferentes momentos, pero siempre en carga de trabajo, y la diferencia entre las cifras de ambas instantáneas serán los datos registrados. Para tomar estas instantáneas se debe ejecutar cualquiera de los métodos:
Método 1:
SQL> dbms_stats.gather_system_stats('start');
Tiempo después:
SQL> dbms_stats.gather_system_stats('stop');
Método 2:
SQL> dbms_stats.gather_system_stats('interval', interval=>N);
Donde N es el tiempo minutos que debe transcurrir para que se lance automáticamente la
segunda instantánea.
En noworkload es la instancia la que genera actividad de forma autónoma lanzando lecturas aleatorias a los datafiles para tomando tiempos. Aunque este método no es tan fiable como el anterior resulta muy útil como línea base de trabajo. De hecho, Oracle recomienda lanzar este método nada más crear una base de datos nueva. Para tomar estas estadísticas hay que hacer:
SQL> dbms_stats.gather_system_stats();
Estas estadísticas se pueden guardar en tablas de copia de seguridad a través de los parámetros stattab, statid y statown o en el diccionario, en cuyo caso lo hace en sys.aux_stats$.
Por último decir que el optimizador usará estas estadísticas para las nuevas SQL que se ejecuten o para las que necesiten ser compiladas de nuevo. Las ya existentes usarán sus planes de ejecución ya precalculados:
Nada más. Espero que este artículo sea comprensible, interesante y que ayude a comprender mejor qué son necesarias las estadísticas y por qué resultan necesarias.
Juan Lorenzo Arellano.
Oracle DBA
Infor Consult Soluciones
