Como recuperar estadisticas anteriores con Oracle 10G

A partir de la version 10g de Oracle se incluye una funcionalidad muy util para recuperar estadísticas. Cada vez que se utiliza el paquete DBMS_STATS para modificar las estadísticas la versión anterior de ellas es guardada automáticamente en caso que se desee recuperar. Esta opción es muy util si notamos que despues de calcular estadísticas el performance se degradó de manera considerable y necesitamos regresar las anteriores.

Pero no hay nada mejor que un ejemplo para mostrar como funciona…

Primero creamos una tabla para nuestras pruebas

SQL> create table t as select * from dba_objects;

Table created.

Contamos los registros y verificamos que no hay estadisticas

SQL> select count(*) from t;

COUNT(*)
----------
49779

SQL> select table_name, num_rows, blocks, avg_space, avg_row_len, last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T

Recopilamos las estadisticas de la tabla

SQL>exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Verificamos que las estadisticas fueran recopiladas

SQL>select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09

Aqui podemos constatar que el NUM_ROWS es igual al COUNT(*) que hicimos anteriormente, aunque no siempre es asi.
Ahora vamos a verificar cuanta historia de estadisticas tenemos para esta tabla

SQL>select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

TABLE STATS_UPDATE_TIME
----- ---------------------------------------------------------------------------
T     26-AUG-09 06.51.01.496224 AM -04:00

Podemos observar que se han calculado una sola vez las estadisticas para la tabla.
Ahora vamos a insertar mas registros y recalcular estadisticas

SQL>insert into t select * from dba_objects;
49778 rows created.
SQL> /
49778 rows created.
SQL> /
49778 rows created.
SQL> commit;
Commit complete.

Verificamos el numero de registros y las estadisticas actuales. Podemos verificar que las estadisticas no estan actualizadas ya que el numero de registros es por mucho mayor al registrado en las estadisticas.

SQL>select count(*) from t;

COUNT(*)
----------
199113

SQL>select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09

Ahora volvemos a recopilar estadisticas y verificamos que sean similares al COUNT

SQL>exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         199113       2784          0          93 26-AUG-09

Ahora verificamos cuanto historial tenemos

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

TABLE STATS_UPDATE_TIME
----- ---------------------------------------------------------------------------
T     26-AUG-09 06.51.01.496224 AM -04:00
T     26-AUG-09 07.01.01.161096 AM -04:00

Podemos observar que ahora tenemos 2 registros de cuando se calcularon estadisticas.
Ahora podemos regresar en el tiempo y dejar estadisticas a la fecha que queramos.
Vamos a crear otro registro historico para juagar un poco las estadisticas.

SQL>insert into t select * from dba_objects;
49778 rows created.
SQL> /
49778 rows created.
SQL> select count(*) from t;

COUNT(*)
----------
298669

SQL>select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         199113       2784          0          93 26-AUG-09

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL> l
1  select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2* from user_tables
SQL> /

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         298002       4170          0          93 26-AUG-09

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
T      26-AUG-09 06.51.01.496224 AM -04:00
T      26-AUG-09 07.01.01.161096 AM -04:00
T      26-AUG-09 07.04.17.929191 AM -04:00

Tenemos 3 registros historicos de estadisticas y ahora podemos regresar las estadisticas tal y como estaban al inicio si lo deseamos.
Actualmente tenemos las estadisticas actualizadas, o sea NUM_ROWS=298,002
Vamos a regresar las estadisticas cuando NUM_ROWS era 119,113

SQL> exec dbms_stats.restore_table_stats(user,'T','26-AUG-09 07.01.01.161096 AM -04:00');

PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         199113       2784          0          93 26-AUG-09

Podemos ver que regresamos las estadisticas al valor anterior.
Vamos a verificar cuanta historia tenemos ahora.

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner = user;

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
T      26-AUG-09 06.51.01.496224 AM -04:00
T      26-AUG-09 07.01.01.161096 AM -04:00
T      26-AUG-09 07.04.17.929191 AM -04:00

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
T      26-AUG-09 07.06.50.924803 AM -04:00

Podemos observas que se creo otro registro historico, esto es normal ya que se crea 1 registro cada vez que se usa el paquete DBMS_STATS.
Vamos a regresar las estadisticas como estaban al inicio

SQL> exec dbms_stats.restore_table_stats(user,'T','26-AUG-09 06.51.01.496224 AM -04:00');

PL/SQL procedure successfully completed.
SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09

SQL> select count(*) from t;

COUNT(*)
----------
298669

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner = user;

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
T      26-AUG-09 06.51.01.496224 AM -04:00
T      26-AUG-09 07.01.01.161096 AM -04:00
T      26-AUG-09 07.04.17.929191 AM -04:00
T      26-AUG-09 07.06.50.924803 AM -04:00
T      26-AUG-09 07.10.30.136210 AM -04:00

Con esta opción podemos estar seguros que podremos regresar las estadisticas de las tablas en caso necesario. Por default Oracle guarda los ultimos 31 dias de estadisticas, y como muchas opciones esto puede ser configurado a nuestras necesidades. Hay que notar que solo las estadisticas calculadas por medio de DBMS_STATS son las que se pueden regresar y registrar en la vista DBA_STATS_HISTORY, si por alguna razon se calculan estadisticas con ANALYZE TABLE estas no se pueden recuperar.

Al borrar el historico de las estadisticas obviamente limpia la vista que contiene los registros historicos y no se pueden recuperar.

SQL> exec dbms_stats.purge_stats(systimestamp);

PL/SQL procedure successfully completed.

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

no rows selected<

SQL> drop table t;

Table dropped.

Ahora vamos a ver un ejemplo con la opcion de regresar las estadisticas para todo el schema. No voy a describir los pasos ya se explican por si mismos.

SQL> create table t as select * from dba_objects;

Table created.
SQL> create table d as select * from dba_objects;

Table created.
SQL> exec dbms_stats.gather_Schema_stats(user);

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09
D          49779        704          0          93 26-AUG-09

SQL> insert into t select * from dba_objects;
49779 rows created.
SQL> /
49779 rows created.
SQL> insert into d select * from dba_objects;
49779 rows created.
SQL> /
49779 rows created.
SQL> /
49779 rows created.
SQL> exec dbms_stats.gather_Schema_stats(user);
PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         149337       2090          0          93 26-AUG-09
D         199116       2784          0          93 26-AUG-09

SQL> select table_name,stats_update_time
2  from dba_tab_stats_history
3  where owner=user;

TABLE  STATS_UPDATE_TIME
-----  ---------------------------------------------------------------------------
D      26-AUG-09 07.18.00.582750 AM -04:00
T      26-AUG-09 07.18.01.161388 AM -04:00
D      26-AUG-09 07.17.06.242711 AM -04:00
T      26-AUG-09 07.17.06.781828 AM -04:00

SQL> exec dbms_stats.restore_schema_stats(user,'26-AUG-09 07.18.00.582750 AM -04:00');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09
D         199116       2784          0          93 26-AUG-09

SQL> exec dbms_stats.restore_schema_stats(user,'26-AUG-09 07.18.01.161388 AM -04:00');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T         149337       2090          0          93 26-AUG-09
D         199116       2784          0          93 26-AUG-09

SQL> exec dbms_stats.restore_schema_stats(user,'26-AUG-09 07.18.00.000000 AM -04:00');

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,avg_space,avg_row_len,last_analyzed
2  from user_tables;

TABLE   NUM_ROWS     BLOCKS  AVG_SPACE AVG_ROW_LEN LAST_ANAL
----- ---------- ---------- ---------- ----------- ---------
T          49779        704          0          93 26-AUG-09
D          49779        704          0          93 26-AUG-09

Como migrar/copiar una base de datos a otro servidor.

Un pequeño parentesis antes de comenzar con el post.
Después de mucho tiempo (wow! casi un año), aqui vuelvo a las andadas. La verdad no había escrito algo por que no tenia ganas, o me entretenía haciendo otras cosas (WEBeando/surfeando), o no encontraba algún tema que tratar sobre Oracle; pero el día de hoy recibí una pregunta de Paula sobre la migración de una base de datos a otro servidor y me pareció interesante escribir al respecto de manera rápida y sin mucho rodeo técnico, ademas sirve de pretexto para comenzar de nuevo a escribir…espero y así sea.

Bueno, ahora a lo que interesa, como le hago para mover/migrar una base de datos de un servidor a otro, la respuesta como todo en Oracle es muy sencilla…depende.

Bueno, depende de la información que tengamos. Basado en la información de Paula “Tengo una bd oracle 10g y la quiero “mover” a otro servidor”, pues la siguientes preguntas serian:

1. El nuevo servidor es también 10g y la misma versión que el fuente, esto quiere decir que si es 10.0.2 a 10.0.2 y si ambas son de la misma edición (standard, enterprise, etc) ?
2. La versión del sistema operativo es la misma?
3. De que tamaño es la base de datos?
4. Hay posibilidad de detener la base de datos durante la migración o tienen que ser en tiempo real?
5. que mas…? mas detalles. No se me ocurren mas preguntas por el momento…

Que opciones tengo para la migración?

Afortunadamente Oracle provee muchos métodos para realizar esta tarea, de nuevo, todo depende como la queramos hacer y depende de los requerimientos que tengamos. Pero existen soluciones sencillas y soluciones complejas, vamos a ver algunas de ellas de manera general y sin entrar en los detalles de cada una.

Sencillas

Copia Directa

Suponiendo que el nuevo servidor es exactamente igual que el anterior respecto a versiones de software (sistema operativo, base de datos, parches, etc) en teoría si copiamos todos los archivos de datos, control files, redo logs, etc la base de datos debería de funcionar correctamente. Personalmente yo no utilizaría esta opción por que siempre falta algo, un parche, un parámetro,  etc.

Export/Import (DataPump en 10g)

Este podría ser también complejo pero normalmente se hace un export del usuario(s) de la aplicación y se importa en la otra base de datos, creo que es de las mas sencillas que puede existir ademas que podemos hacer el export de una versión anterior a la nueva base de datos por ejemplo de 9i a 10g. El único problema de esta opción es que debemos tener mas o menos el mismo espacio en disco que la base de datos actual para poder hacer el export; por ejemplo en una base de datos de 10Gb tal vez si es conveniente hacerlo pero en una de 600Gb o de Tb ya no es tan practico hacerlo, y si ademas se tiene que transferir el archivo(s) del export pues seria muchísimo mas tardado.

Complejas

RMAN Duplicate.

RMAN es una utilitaria de Oracle para realizar backups/restore. Una opcion que es muy interesante es el “duplicate” y como su nombre lo dice duplica una base de datos a otro servidor. Básicamente como funciona es de la siguiente manera: se hace un backup de la base de datos “fuente/original”, una vez con el backup guardado en el tape/disco/etc el resto del trabajo se hace en el servidor “destino/nuevo”, en el destino se debe tener el software de Oracle instalado, hay que configurar el tnsnames.ora para tener acceso a la base “fuente”, se copia el init.ora del fuente al destino (para tener la misma configuración) y se hacen los cambios necesarios al archivo (cambiar el destino de los control files, memoria, etc), se agregan al init.ora 2 parámetros para el duplicate (db_file_name_convert y log_file_name_convert), se crea el spfile y se crea un script de RMAN para duplicar la base de datos.
El script se ejecuta en la base de datos destino y básicamente el script o RMAN buscan el backup que hicimos de la fuente y hace un restore al destino de todos los archivos, hace un recover de la base hasta la hora/scn/etc que le digamos y deja la nueva base de datos lista para trabajar con ella.
Personalmente este es un método preferido para hacer “refresh” de ambientes por ejemplo de produccion a QA o desarrollo, es rápido (bueno depende de ciertos factores red/servidor/discos/etc) y sencillo ya que una vez teniendo los scripts es solo cuestión de ejecutarlos y RMAN se encarga de todo.

NOTA.- Muchísimos pasos no son mencionados y cierta configuracion se tiene que hacer para que esto funcione correctamente.

Backup/Restore

Es muy similar al Duplicate pero casi todos los pasos los tenemos que hacer manualmente, hacer un backup del fuente, hacer el restore en el destino, recrear control files, hacer recover,etc.

Copy a nivel SAN.

Esta opción es ideal cuando se tienen bases de datos grandes (GB a TB+), en esencia la copia de la información se hace a nivel de hardware/discos. Si la base de datos fuente y destino están en una SAN entonces solo hay que copiar los discos que contienen la base de datos fuente a los discos que van a contener la base de datos destino. Todo esto se hace por medio del sistema operativo y regularmente lo hacen los encargados de los servidores. La participación del DBA en esta opción es mínima, solo hay que poner los tablespaces en la base de datos fuente en modo backup, se hace la copia a nivel hardware, una vez terminada la copia se quita el modo backup de la fuente, se envian los archivos del archive de la fuente al destino (de preferencia se hace un log switch) y en el destino se recrean los control files si es necesario y se hace un recover (aquí va a preguntar por los archives que acabamos de copiar) y listo, la BD queda funcional.

Y estas son las opciones que conozco para migrar/mover/copiar una base de datos de un lado a otro. No entre en detalles técnicos ya que cada sitio puede tener una configuración diferente y hardware diferente, por ejemplo donde trabajo actualmente tenemos un servidor de backups y eso facilita mucho hacer el duplicate de una base de datos en USA a Canadá por medio de la red (algo tardado). También faltan muchos detalles sobre que comandos usar para RMAN y para hacer la copia a nivel SAN, pero todos esos los pueden encontrar en la red en Google.

Si alguien conoce algún método diferente puede agregarlo a los comentarios…

Saludos.

DBMS_STATS Parte 4, Exportar estadísticas

Esta semana recibimos una llamada de un usuario diciendo que un reporte estaba muy lento (« típico caso») que antes se ejecutaba rápidamente pero ahora estaba tomando mucho tiempo. Y en efecto, el reporte estaba tomando como 1 hora para terminar.
Ok, manos a la obra ; lo primero que hicimos fue pedirles a los developers el query del reporte, no lo envían y hasta me asuste con el monstruo de query… en total 1969 líneas de código que incluían alrededor de 5 tablas con subqueries, group by, case when, sum over partition,etc,etc… digno de regresárselo al developer inmediatamente y decirle que no mam… que lo reescriba. Pero obviamente no hicimos eso, lo primero fue sacar un explain plan del query y al parecer estaba usando índices correctamente con excepción de 2 tablas donde estaba haciendo full scan. Primer síntoma que encontramos fue esos full scans pero “típico” los Developers nos dijeron que no habían cambiado nada en el reporte que ese era el Query que siempre había funcionado bien (y al final supimos que era cierto?) . Bueno entonces algo tuvo que cambiar para que el reporte estuviera lento, entonces lo segundo que revisamos fueron las estadísticas de las tablas y ahí fue donde encontramos el error, no se habían calculado desde la creación del sistema en Marzo.
Bueno lo que haríamos seria calcular estadísticas y ver que pasaba con el reporte, pero me acorde que en varios lados recomiendan antes de cambiar las estadísticas guardar un respaldo en caso que deseemos regresar a las anteriores, mmmm, como hacer eso. Investigue un poco y de eso voy a hablar en este post, como exportar estadísticas y que se puede hacer con ellas.

Para que exportar estadísticas?

El “problema” al calcular estadísticas es que los planes de ejecución pueden cambiar en algún punto en la vida de la base de datos; un grupo de tablas al inicio puede tener cientos de registros pero después de 2 años tienen millones, esto hace que al calcular las estadísticas en esas tablas los planes de ejecución cambien. Pero no se supone que ese es el punto de calcular estadísticas, darle a Oracle la información mas actualizada sobre las tablas para que pueda tomar mejores decisiones en cuanto al plan de ejecución? totalmente cierto, eso es lo que queremos, pero hay ciertas situaciones en las que necesitamos saber que es lo que ha cambiado y nadie nos puede dar la respuesta (ni los developers, ni los managers, menos los usuarios). Por lo tanto lo ideal seria poder ver el plan de ejecución anterior (el bueno) con el plan de ejecución actual (el malo, lento); pero esto es complicado ya que tendríamos que estar guardando planes de ejecución por cada Query,etc. Pero que tal si tenemos las estadísticas de la semana pasada o el mes pasado, podríamos importarlas en el ambiente de pruebas y ver que es lo que ha cambiado, probar, probar y probar.
Bueno, pues Oracle ya pensó al respecto y provee un conjunto de procedimientos para facilitar la tarea. A continuación voy a mostrar con un ejemplo como guardar y comparar estadísticas para una tabla.

Vamos a crear una tabla y calcularle estadísticas.
SQL> create table emp2 as select * from emp;
Table created.

SQL> set null <<null>>
 SQL> select to_char(last_analyzed,'dd/mm/yyyy hh24:mi:ss') last_analyzed,
 2         num_rows,
 3         blocks,
 4         avg_space
 5   from user_tables where table_name='EMP2'
 6  ;

LAST_ANALYZED         NUM_ROWS     BLOCKS  AVG_SPACE
 ------------------- ---------- ---------- ----------
 <<null>>            <<null>>   <<null>>   <<null>>

SQL> exec dbms_stats.gather_Table_stats(user,'EMP2');

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'dd/mm/yyyy hh24:mi:ss') last_analyzed,
 2         num_rows,
 3         blocks,
 4         avg_space
 5   from user_tables where table_name='EMP2';

LAST_ANALYZED         NUM_ROWS     BLOCKS  AVG_SPACE
 ------------------- ---------- ---------- ----------
 16/07/2008 15:57:41         14          4          0

Bueno ahora hay que hacer que las estadísticas cambien y para hacerlo hay que cambiar la tabla EMP2. Vamos insertar 1 millón de registros.

SQL> insert into emp2 select * from emp connect by rownum <= 1000000;

1000013 rows created.

Ahora si calculamos estadísticas obviamente todo va a cambiar, pero lo que queremos hacer es guardar las anteriores y hacer una comparación.

Entonces para hacer un respaldo de las estadísticas anteriores primero debemos crear una tabla donde guardemos las estadísticas pasadas. Esto se realiza fácilmente con el procedimiento CREATE_STAT_TABLE.

DBMS_STATS.CREATE_STAT_TABLE (
ownname VARCHAR2,
stattab VARCHAR2,
tblspace VARCHAR2 DEFAULT NULL);

 








Parámetro


Descripción




Owname


Nombre
  del schema/usuario que va a tener la tabla de estadisticas.




Stattab


Nombre de
  la nueva tabla a crear.




Tblspace


Nombre
  del tablespace donde queremos guardarla, si no se especifica se crea en el
  tablespace default de owname.




En nuestro caso owname es el mismo usuario de la tabla “problema”.

SQL> exec dbms_stats.create_stat_table(USER,’HISTORIAL’);

PL/SQL procedure successfully completed.

Según la documentación de Oracle la información de estadísticas se debe acceder solamente con los procedimientos de DBMS_STATS, pero la tabla no es cosa del otro mundo y si alguien esta interesado puede jugar con ella.

SQL> desc historial;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
STATID                                             VARCHAR2(30)
TYPE                                               CHAR(1)
VERSION                                            NUMBER
FLAGS                                              NUMBER
C1                                                 VARCHAR2(30)
C2                                                 VARCHAR2(30)
C3                                                 VARCHAR2(30)
C4                                                 VARCHAR2(30)
C5                                                 VARCHAR2(30)
N1                                                 NUMBER
N2                                                 NUMBER
N3                                                 NUMBER
N4                                                 NUMBER
N5                                                 NUMBER
N6                                                 NUMBER
N7                                                 NUMBER
N8                                                 NUMBER
N9                                                 NUMBER
N10                                                NUMBER
N11                                                NUMBER
N12                                                NUMBER
D1                                                 DATE
R1                                                 RAW(32)
R2                                                 RAW(32)
CH1                                                VARCHAR2(1000)

Ok, ahora que ya tenemos nuestra tabla como copiamos nuestras estadísticas a ella. Para eso utilizamos el procedimiento dbms_stats.EXPORT_TABLE_STATS, aunque para este ejemplo solo estamos utilizando la parte de una sola tabla hay otros procedimientos que permiten hacerlo a nivel schema, base de datos, etc:
EXPORT_COLUMN_STATS Procedure
EXPORT_DATABASE_STATS Procedure
EXPORT_DICTIONARY_STATS Procedure
EXPORT_FIXED_OBJECTS_STATS Procedure
EXPORT_INDEX_STATS Procedure
EXPORT_SCHEMA_STATS Procedure
EXPORT_SYSTEM_STATS Procedure
EXPORT_TABLE_STATS Procedure

En lo personal yo recomendaría hacerlo hasta nivel schema para así no perder las estadísticas de una tabla.

EXPORT_TABLE_STATS
Permite sacar las estadisticas de una tabla y guardarlas en otra.

DBMS_STATS.EXPORT_TABLE_STATS (
 ownname  VARCHAR2,
 tabname  VARCHAR2,
 partname VARCHAR2 DEFAULT NULL,
 stattab  VARCHAR2,
 statid   VARCHAR2 DEFAULT NULL,
 cascade  BOOLEAN  DEFAULT TRUE,
 statown  VARCHAR2 DEFAULT NULL);

Parametro


Descripción




Owname


Nombre
  del schema/usuario dueño de la tabla de la cual queremos respaldar.




Tabname


Nombre de
  la tabla que queremos respaldar.




Partname


Nombre de
  la partición.




Stattab


Nombre de
  la tabla donde vamos a guardar las estadísticas.




Statid


Identificador
  para las estadísticas. Es opcional pero es recomendable identificarlas.




Cascade


TRUE
  default, si deseamos exportar estadísticas de índices y columnas.




Statown


Quien es
  el dueño de la tabla de estadísticas, diferente a owname.

SQL> exec dbms_stats.export_table_stats(USER,’EMP2′,null, ‘HISTORIAL’,'Primera’,TRUE,’SCOTT’);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM HISTORIAL;

COUNT(*)
———-
9

Ahora ya tenemos un respaldo de estadísticas (si alguien esta interesado puede hacer un select a la tabla y ver que contiene), en este momento podemos calcular las nuevas y comenzar a comparar.

SQL> exec dbms_stats.gather_Table_stats(user,'EMP2');

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'dd/mm/yyyy hh24:mi:ss') last_analyzed,
 2         num_rows,
 3         blocks,
 4         avg_row_len
 5   from user_tables where table_name='EMP2';

LAST_ANALYZED         NUM_ROWS     BLOCKS AVG_ROW_LEN
 ------------------- ---------- ---------- -----------
 16/07/2008 16:29:16    1007517       5539          34

Perfecto, ahora tenemos estadísticas nuevas. Obviamente en nuestro ejemplo ya sabemos que fue lo que cambio, pero en un ambiente real regularmente no lo sabemos.

Bueno ahora como le hacemos para comparar las estadísticas actuales con las estadísticas anteriores. Para esto nos vamos a servir de un poco de PL/SQL, la verdad no se si Oracle provee alguna herramienta mas amigable para comparar por que hacer un programa para comparar si es un poco fastidioso, sobre todo cuando se desea comparar varias tablas a la vez.
El procedimiento que nos da información (muy poca a mi parecer y un poco limitado) de las estadísticas es dbms_stats.GET_TABLE_STATS.

DBMS_STATS.GET_TABLE_STATS (
 ownname         VARCHAR2,
 tabname         VARCHAR2,
 partname        VARCHAR2 DEFAULT NULL,
 stattab         VARCHAR2 DEFAULT NULL,
 statid          VARCHAR2 DEFAULT NULL,
 numrows     OUT NUMBER,
 numblks     OUT NUMBER,
 avgrlen     OUT NUMBER,
 statown         VARCHAR2 DEFAULT NULL,
 cachedblk   OUT NUMBER,
 cachehit    OUT NUMBER);
 SQL> set serveroutput on
 SQL>
 SQL> DECLARE
 2   numr NUMBER;
 3   numb NUMBER;
 4   avgr NUMBER;
 5   vtabname varchar2(100);
 6   vstattab varchar2(100);
 7   vstatid  varchar2(100);
 8  BEGIN
 9
 10    vtabname := 'EMP2';
 11    vstatid  := 'PRIMERA';
 12    vstattab := 'HISTORIAL';
 13
 14    dbms_stats.get_table_stats(
 15    ownname=> USER,
 16    tabname=> vtabname,
 17    partname=>null,
 18    stattab=>vstattab,
 19    statid=> vstatid,
 20    numrows=>numr,
 21    numblks=>numb,
 22    avgrlen=>avgr,
 23    statown=>USER);
 24
 25    dbms_output.put_line ('Table: '||vtabname);
 26    dbms_output.put_line('# of rows: ' || TO_CHAR(numr));
 27    dbms_output.put_line('# of blocks: ' || TO_CHAR(numb));
 28    dbms_output.put_line('Avg row len: ' || TO_CHAR(avgr) || ' bytes');
 29
 30  end;
 31  /
 Table: EMP2
 # of rows: 14
 # of blocks: 4
 Avg row len: 37 bytes

PL/SQL procedure successfully completed.

Con esto podemos comparar el antes y despues

Antes


Despues




Rows


14


1007517




Blocks


4


5539




Avg Row
  Len


37


34

Así que esta es una manera “rápida” y sucia de respaldar las estadísticas de una tabla (aunque es posible hacerlo para el schema, BD,etc) y después comparar para saber que fue lo que paso.
En el siguiente post voy a mostrar que más se puede hacer con las estadísticas que tenemos guardadas.

</pre>

Tablespace Size en SQL Developer

Para los que utilizan SQL Developer, este es un reporte que muestra la información sobre el tamaño de los tablespaces en una base de datos.
Regularmente nos piden el tamaño de la base de datos o simplemente tenemos que ajustar el tamaño de los tablespaces como parte del mantenimiento.
El siguiente reporte nos permite ver de manera rápida toda esta información.

• Dar click en el reporte dentro la sección de User Defined Reports.

• Seleccionar la conexión sobre la cual deseamos obtener la información.

• Seleccionar a que porcentaje deseamos dejar los tablespaces. Por ejemplo si actualmente sabemos que unos estan a 98% y queremos dejarlos al 50% entonces ponemos 50. El reporte obtendrá cuanto espacio (MB) se necesita agregar a los tablespaces para bajarlos a 50%

• El reporte se muestra en la pantalla con toda la información que necesitamos.

El query que da la información anterior es el siguiente:

 SELECT nvl(name,'=== TOTAL for DB ===') "Tablespace"                          ,
  TO_CHAR(SUM(mbytes),'999,999,999.99') "Total MB" ,
  TO_CHAR(SUM(used),'999,999,999.99') "Used MB"    ,
  TO_CHAR(SUM(free),'999,999,999.99') "Free MB"    ,
  ROUND(AVG(pct_used),2) "%Used"                   ,
  ROUND(AVG(pct_free),2) "%Free"                   ,
  'SQLDEV:GAUGE:0:100:0:0:'
  || AVG(pct_used) AS "Tablespace Utilization"          ,
  TO_CHAR(SUM(new_sz),'999,999,999.99') "Expected Size" ,
  TO_CHAR(SUM(add_sz),'999,999,999.99') "Add MB for desired %"
   FROM
  (SELECT name                ,
    ROUND(mbytes,2) mbytes    ,
    ROUND(used,2) used        ,
    ROUND(free,2) free        ,
    ROUND(pct_used,2) pct_used,
    ROUND(pct_free,2) pct_free,
    ROUND(
    CASE
      WHEN ( ((100 *used)/ trim(:desired_pct))-mbytes < 0)
      THEN mbytes
      ELSE ((100*used)/ trim(:desired_pct))
    END,2) new_sz,
    ROUND(
    CASE
      WHEN ( (((100 *used)/ trim(:desired_pct))-mbytes) < 0 )
      THEN 0
      ELSE (((100 *used)/ trim(:desired_pct))-mbytes)
    END,2) add_sz
     FROM
    (SELECT NVL(b.tablespace_name, NVL(a.tablespace_name,'UNKOWN')) name,
      mbytes_alloc Mbytes                                               ,
      mbytes_alloc-NVL(mbytes_free,0) used                              ,
      NVL(mbytes_free,0) free                                           ,
      ((mbytes_alloc-NVL(mbytes_free,0))/ mbytes_alloc)*100 pct_used    ,
      100           -(((mbytes_alloc-NVL(mbytes_free,0))/ mbytes_alloc)*100) pct_free
       FROM
      (SELECT SUM(bytes)/1024/1024 Mbytes_free,
        MAX(bytes)      /1024/1024 largest    ,
        tablespace_name
         FROM dba_free_space
     GROUP BY tablespace_name
      ) a                                      ,
      (SELECT SUM(bytes)/1024/1024 Mbytes_alloc,
        SUM(maxbytes)   /1024/1024 Mbytes_max  ,
        tablespace_name
         FROM sys.dba_data_files
     GROUP BY tablespace_name

    UNION ALL

       SELECT SUM(bytes)/1024/1024 Mbytes_alloc,
        SUM(maxbytes)   /1024/1024 Mbytes_max  ,
        tablespace_name
         FROM dba_temp_files
     GROUP BY tablespace_name
      ) b
      WHERE a.tablespace_name (+) = b.tablespace_name
    )
  )
GROUP BY rollup (name)
ORDER BY
  CASE
    WHEN name IS NULL
    THEN 200
    ELSE ROUND(AVG(pct_free),2)
  END ASC

Para crear el reporte solo hay que dar click derecho sobre User Defined Reports y seleccionar Add Report

Luego llenar los detalles del reporte:

Después Apply…y listo, a disfrutar del reporte.

Aquí esta el codigo XML para importar el reporte

.
Reporte en XML

DBA_FREE_SPACE

Esta semana me encontré con un problema muy raro, cuando hacia un query sobre la vista dba_free_space éste se tardaba mucho para regresar la respuesta cuando en otras bases de datos el resultados lo regresaba en cuestión de milisegundos.

Después de obtener el explain plan me di cuenta que a partir de la versión 10g esta vista incluye también el recycle_bin. A continuación voy a explicar el problema y como resolverlo (después de calcular estadísticas de sistema, modificar parámetros del init,etc).

Recycle Bin

Básicamente el recycle bin es similar al de windows, es un lugar dentro de la base de datos que sirve para guardar todos los objectos borrados. El recycle bin esta habilitado por default; así que si nosotros hacemos algo como:

drop table emp;

la tabla es “borrada” y nosotros podemos volver a crear otra tabla emp (igualita) y trabajar con ella,etc. Pero en realidad la tabla no fue borrada, lo que sucedió fue que oracle nada mas renombro la tabla con el prefijo BIN$$ y una secuencia de caracteres, la tabla (la estructura, indices, constraints, registros) siguen dentro del mismo tablespace ocupando espacio y no son borrados hasta que se utiliza el comando PURGE.

La razón de esta funcionalidad es variada pero principalmente es para poder recuperar información borrada de manera accidental.

Mas información sobre el recycle bin: What is the recycle bin?

Y que tiene que ver el recycle bin con dba_free_space?

Pues a partir de la versión 10g la vista accesa tambien la tabla sys.recyclebin$ para saber cuanto espacio queda libre en un tablespace, como habia mecionado anteriormente los objetos borrados no son eliminados completamente y en esta tabla se guarda la información(nombre, tamaño,etc) respecto a estos objetos.
Tiene sentido que la vista ahora incluya el recycle_bin (RBin) ya que hay que tomar en cuenta ahora cuanto espacio ocupan los objetos “borrados”.

Bueno, ahora mejor muestro un ejemplo sobre lo que puede suceder y como resolverlo.

Primero hay que ejecutar un query que contenga la vista:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> set lines 130
SQL> set pages 10000
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.29
SQL> l
  1  select tablespace_name TS2,
  2         sum(bytes) SUM_B2,
  3         max(bytes) MAX_B2,
  4         count(bytes) CNT_B2
  5    from dba_free_space
  6*  group by tablespace_name

SQL> /

TS2                                SUM_B2     MAX_B2     CNT_B2
------------------------------ ---------- ---------- ----------
PSAPPW1                        3.7242E+10 2990538752      21213
PSAPPW1640                     1.6777E+10 4160749568          8
PSAPPW1700                     3194159104 1581252608          7
PSAPPW1DB                       671744000  670957568          2
PSAPPW1DIM                     6167658496 3471835136        305
PSAPPW1FACT                    2222653440  900661248         64
PSAPPW1ODS                     6.9220E+11 3220176896      17456
PSAPPW1USR                     1730150400  455475200       3486
PSAPUNDO                       5.5332E+10 1736441856       1242
PSAPZFCT0                      2.6376E+10 1534066688      10457
PSAPZFCT1                      9236316160  443547648        523
PSAPZODS0                      7.2968E+10 2434793472        452
PSAPZODS1                      2.5842E+10 3226468352       1011
SYSAUX                          892534784  834600960         96
SYSTEM                         2040774656 2040479744          7

15 rows selected.

Elapsed: 00:02:50.81

Execution Plan
----------------------------------------------------------
Plan hash value: 2686115433

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |   649 | 19470 |       |   126K|
|   1 |  SORT GROUP BY               |                  |   649 | 19470 |       |   126K|
|   2 |   VIEW                       | DBA_FREE_SPACE   |   649 | 19470 |       |   126K|
|   3 |    UNION-ALL                 |                  |       |       |       |       |
|   4 |     NESTED LOOPS             |                  |     7 |   259 |       |     5 |
|   5 |      NESTED LOOPS            |                  |     7 |   210 |       |     5 |
|*  6 |       TABLE ACCESS FULL      | TS$              |     2 |    40 |       |     3 |
|*  7 |       TABLE ACCESS CLUSTER   | FET$             |     4 |    40 |       |     1 |
|*  8 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|   9 |     NESTED LOOPS             |                  |    74 |  5328 |       |    90 |
|  10 |      NESTED LOOPS            |                  |    74 |  4810 |       |    90 |
|* 11 |       TABLE ACCESS FULL      | TS$              |    14 |   364 |       |     3 |
|* 12 |       FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |     5 |   195 |       |     7 |
|* 13 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|  14 |     NESTED LOOPS             |                  |     1 |    97 |       |   125K|
|  15 |      NESTED LOOPS            |                  |     1 |    90 |       |   125K|
|* 16 |       HASH JOIN              |                  | 20579 |   763K|   488K|    42 |
|  17 |        TABLE ACCESS FULL     | RECYCLEBIN$      | 20791 |   243K|       |    18 |
|* 18 |        TABLE ACCESS FULL     | TS$              |    14 |   364 |       |     3 |
|* 19 |       FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |     1 |    52 |       |     7 |
|* 20 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|  21 |     NESTED LOOPS             |                  |   567 | 29484 |       |   462 |
|* 22 |      HASH JOIN               |                  |   567 | 25515 |       |   462 |
|* 23 |       HASH JOIN              |                  |  7475 |   240K|       |   430 |
|* 24 |        TABLE ACCESS FULL     | TS$              |     2 |    40 |       |     3 |
|  25 |        TABLE ACCESS FULL     | UET$             |  7475 | 97175 |       |   426 |
|  26 |       TABLE ACCESS FULL      | RECYCLEBIN$      | 20791 |   243K|       |    18 |
|* 27 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("TS"."BITMAPPED"=0)
   7 - filter("TS"."TS#"="F"."TS#")
   8 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  11 - filter("TS"."BITMAPPED"0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
              "TS"."CONTENTS$"=0)
  12 - filter("TS"."TS#"="F"."KTFBFETSN")
  13 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  16 - access("TS"."TS#"="RB"."TS#")
  18 - filter("TS"."BITMAPPED"0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
              "TS"."CONTENTS$"=0)
  19 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#"
              AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  20 - access("RB"."TS#"="FI"."TS#" AND "RB"."FILE#"="FI"."RELFILE#")
  22 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
              "U"."SEGBLOCK#"="RB"."BLOCK#")
  23 - access("TS"."TS#"="U"."TS#")
  24 - filter("TS"."BITMAPPED"=0)
  27 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
     520872  recursive calls
       1992  db block gets
     123081  consistent gets
      31977  physical reads
          0  redo size
       1235  bytes sent via SQL*Net to client
        465  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
         15  rows processed

Del query podemos observar lo siguiente:
Tiempo de ejecución:

Elapsed: 00:02:50.81

Del explain plan podemos ver que accesa el recycle bin:

TABLE ACCESS FULL      | RECYCLEBIN$      | 20791 |   243K|

Y también podemos ver que las estadísticas son algo altas para esta simple vista.

Ok, ahora hacemos un Purge y volvemos a ejecutar el query y revisamos el resultado.

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

Elapsed: 00:28:58.65

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.10
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:01.51
SQL> set timing on;
SQL> set autotrace on;
SQL> select tablespace_name TS2,
  2  sum(bytes) SUM_B2,
  3  max(bytes) MAX_B2,
  4  count(bytes) CNT_B2
  5  from dba_free_space
  6  group by tablespace_name;

TS2                                SUM_B2     MAX_B2     CNT_B2
------------------------------ ---------- ---------- ----------
PSAPPW1                        3.7242E+10 2990538752       3370
PSAPPW1640                     1.6777E+10 4160749568          8
PSAPPW1700                     3194159104 1581252608          7
PSAPPW1DB                       671744000  670957568          2
PSAPPW1DIM                     6167658496 3471835136        288
PSAPPW1FACT                    2222653440  900661248         64
PSAPPW1ODS                     6.9220E+11 3220176896      17456
PSAPPW1USR                     1730150400 1233649664         12
PSAPUNDO                       5.4988E+10 1736441856       1234
PSAPZFCT0                      2.6376E+10 1534066688       3412
PSAPZFCT1                      9236316160  443547648        523
PSAPZODS0                      7.2968E+10 2434793472        452
PSAPZODS1                      2.5842E+10 3226468352       1011
SYSAUX                          892534784  834600960         96
SYSTEM                         2040774656 2040479744          7

15 rows selected.

Elapsed: 00:00:06.21

Execution Plan
----------------------------------------------------------
Plan hash value: 2686115433

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes |TempSpc| Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |   649 | 19470 |       |   126K|
|   1 |  SORT GROUP BY               |                  |   649 | 19470 |       |   126K|
|   2 |   VIEW                       | DBA_FREE_SPACE   |   649 | 19470 |       |   126K|
|   3 |    UNION-ALL                 |                  |       |       |       |       |
|   4 |     NESTED LOOPS             |                  |     7 |   259 |       |     5 |
|   5 |      NESTED LOOPS            |                  |     7 |   210 |       |     5 |
|*  6 |       TABLE ACCESS FULL      | TS$              |     2 |    40 |       |     3 |
|*  7 |       TABLE ACCESS CLUSTER   | FET$             |     4 |    40 |       |     1 |
|*  8 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|   9 |     NESTED LOOPS             |                  |    74 |  5328 |       |    90 |
|  10 |      NESTED LOOPS            |                  |    74 |  4810 |       |    90 |
|* 11 |       TABLE ACCESS FULL      | TS$              |    14 |   364 |       |     3 |
|* 12 |       FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |     5 |   195 |       |     7 |
|* 13 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|  14 |     NESTED LOOPS             |                  |     1 |    97 |       |   125K|
|  15 |      NESTED LOOPS            |                  |     1 |    90 |       |   125K|
|* 16 |       HASH JOIN              |                  | 20579 |   763K|   488K|    42 |
|  17 |        TABLE ACCESS FULL     | RECYCLEBIN$      | 20791 |   243K|       |    18 |
|* 18 |        TABLE ACCESS FULL     | TS$              |    14 |   364 |       |     3 |
|* 19 |       FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |     1 |    52 |       |     7 |
|* 20 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
|  21 |     NESTED LOOPS             |                  |   567 | 29484 |       |   462 |
|* 22 |      HASH JOIN               |                  |   567 | 25515 |       |   462 |
|* 23 |       HASH JOIN              |                  |  7475 |   240K|       |   430 |
|* 24 |        TABLE ACCESS FULL     | TS$              |     2 |    40 |       |     3 |
|  25 |        TABLE ACCESS FULL     | UET$             |  7475 | 97175 |       |   426 |
|  26 |       TABLE ACCESS FULL      | RECYCLEBIN$      | 20791 |   243K|       |    18 |
|* 27 |      INDEX UNIQUE SCAN       | I_FILE2          |     1 |     7 |       |       |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("TS"."BITMAPPED"=0)
   7 - filter("TS"."TS#"="F"."TS#")
   8 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  11 - filter("TS"."BITMAPPED"0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
              "TS"."CONTENTS$"=0)
  12 - filter("TS"."TS#"="F"."KTFBFETSN")
  13 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  16 - access("TS"."TS#"="RB"."TS#")
  18 - filter("TS"."BITMAPPED"0 AND ("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND
              "TS"."CONTENTS$"=0)
  19 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#"
              AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  20 - access("RB"."TS#"="FI"."TS#" AND "RB"."FILE#"="FI"."RELFILE#")
  22 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND
              "U"."SEGBLOCK#"="RB"."BLOCK#")
  23 - access("TS"."TS#"="U"."TS#")
  24 - filter("TS"."BITMAPPED"=0)
  27 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
       1411  recursive calls
       1992  db block gets
       8777  consistent gets
       8820  physical reads
          0  redo size
       1233  bytes sent via SQL*Net to client
        465  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
         15  rows processed

Inmediatamente podemos ver varias cosas:

1. El tiempo se redujo considerablemente: de 00:02:50.81 a 00:00:06.21.
2. La estadísticas cambiaron muchísimo.

Con este ejemplo quería mostrar que el recycle bin puede afectar el performance de algunos queries.
Después de buscar un poco en metalink encontre las siguientes notas en las cuales mencionan el problema, en algunas dice que es un bug y en otras dice que es un comportamiento “normal” y que podemos recrear la vista agregandole un hint para mejorar el performance.

Cuando se haga el purge se debe realizar a dba_recyclebin con un usuario que tenga el rol sysdba ya que si solo hacemos purge recyclebin solo se eliminaran los objetos del usuario actual y no de toda la base de datos.

Si se cree que no es necesario estar utilizando el recycle bin entonces podemos deshabilitarlo usando: alter system set recyclebin = off scope=both; cuidado ya que aunque deshabilitemos el recycle bin los objetos que se encontraban antes de esta acción continuaran ocupando espacio (por lo cual debemos hacer primero un purge).

Metalink: Note:302333.1, Note:271169.1,Note:3492682.8

Oracle technet en Spanish!!!

No se cuantos de ustedes utilicen OTN para ver noticias, artículos, etc sobre Oracle; al menos yo utilizo de manera regular la versión en ingles. Pues ya sacaron la versión en español y lo interesante es que hay links hacia unos blogs de latinoamericanos que están interesantes, así que échenle un vistazo a la versión latina a ver si les gusta.

Oracle Technology Network

Migración de Oracle 10g Standard Edition a Enterprise Edition

Hace unas semanas en mi nuevo empleo hice unas instalaciones de Oracle de las cuales me pidieron que instalara la versión Standard, pero por razones de licenciamiento (compraron otro tipo de licencias!) decidieron que era mejor tener instalada la versión Enterprise.

Yo nunca había hecho una migración de standard a enterprise, así que inmediatamente me fui a Googlear la documentación de Oracle y me encontré el siguiente documento :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/intro.htm#BABFBJCC

Que básicamente dice :

Moving From the Standard Edition to the Enterprise Edition

If you are using the Standard Edition of the Oracle Database and want to move to the Enterprise Edition, then complete the following steps:

1. Ensure that the release number of your Standard Edition server software is the same release as the Enterprise Edition server software.

For example, if your Standard Edition server software is release 10.2.0, then you should upgrade to release 10.2.0 of the Enterprise Edition.

2. Shut down your database.

3. If your operating system is Windows, then stop all Oracle services, including the OracleServiceSID Oracle service, where SID is the instance name.

4. Deinstall the Standard Edition server software.

5. Install the Enterprise Edition server software using the Oracle Universal Installer.

Select the same Oracle home that was used for the de-installed Standard Edition. During the installation, be sure to select the Enterprise Edition. When prompted, choose Software Only from the Database Configuration screen.

6. Start up your database.

Your database is now upgraded to the Enterprise Edition.

Simple verdad? Pues no, no era tan simple hay algunos detalles que no
se muestran en este documento. A continuación voy a poner los pasos que realicé en mi actualización.

1. Primero hay que obtener un respaldo de la base y de los archivos init.ora, listener.ora, sqlnet.ora.
2. « Bajar » la base de datos (shutdown immediate).
3. Si se tiene instalado DBConsole (Enterprise Manager) hay que apagar los servicios : emctl stop dbconsole.
4. Detener los listeners : lsnrctl stop listener.
5. Verificar que el directorio oraInventory para nuestra base exista en el folder correcto. Ej : /oracle/oraInventory
6. Pasos 7 y 8 son para Unix. Si es windows entonces nada mas ejecutar el installer desde Programs.
7. Abrir una sesion de XTerm (Unix) para hacer la desinstalación. No olvidar la variable DISPLAY (en Unix), todo este proceso es similar a la instalación.
8. Desde la sesión XTerm entrar al directorio ($ORACLE_HOME/oui/bin) y ejecutar el instalador (./runInstaller)
9. Click en Deinstall Products
10. Como deseamos desinstalar el SOFTWARE solamente hay que seleccionar el Oracle home que deseamos desinstalar. Antes de desinstalar asegúrese de tener backup de init (spfile), listener, tnsnames ya que este proceso « borra » el Oracle Base (donde esta instalado el software de oracle).
11. Después de seleccionar el Oracle Home entonces dar click en REMOVE y luego YES.
12. La pantalla de REMOVE va aparecer con el progreso de la operación.
13. Cuando termine la desinstalación cerrar el Oracle Universal Installer.
14. En mi caso tuve que borrar el restante del Oracle Base

cd $ORACLE_BASE
rm –Rf product

15. El siguiente paso es instalar el software únicamente pero en la versión Enterprise Edition.
16. Una vez instalada la nueva versión entonces instalar los PatchSets y los Patches.
17. Regresar los respaldos de init.ora, listener.ora, tnsnames.ora a su lugar original.
18. Recrear el password file en caso que se haya tenido activada esta opción.
19. Abrir la base de datos de nuevo. Como es la misma versión (10g) no hay que hacer migración y los mismos archivos (datafiles, controlfiles, redo logs,etc) deben funcionar correctamente.
20. En algunos documentos que lei solo recomiendan re-ejecutar el catalog.sql y catproc.sql, pero en mi caso yo ejecute casi todos los scripts de nuevo.
21. Connect as sys and execute

@$ORACLE_HOME/rdbms/admin/catalog.sql;
@$ORACLE_HOME/rdbms/admin/catblock.sql;
@$ORACLE_HOME/rdbms/admin/catproc.sql;
@$ORACLE_HOME/rdbms/admin/catoctk.sql;
@$ORACLE_HOME/rdbms/admin/owminst.plb;
Conectarse como system y ejecutar:
@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;

1. Verificar que sus backups funcionen.
2. Probar el listener y tnsnames.
3. Si se tenia DBConsole instalada hay que reinstalar de nuevo.
4. En mi caso, y creo que fue por que re-ejecute todos los scripts tuve que hacer grants a algunos usuarios como el usuario de backups. Asi que esten atentos por que puede ser que deban reasignar los privilegios a ciertos usuarios. Básicamente por que los scripts recrean de nuevo el diccionario de datos

Y pues simplemente este es el pequeño proceso para hacer una migración de Standard Edition a Enterprise Edition. En general todo debe ser muy sencillo a exepción de la ejecución de los scripts que hicieron que varios usuarios perdieran privilegios.

DBMS_STATS Parte 3, continuación…

GATHER_TABLE_STATS

Continuando con gather_table_stats, ahora quiero ver la opción METHOD_OPT. Vamos a recordar que opciones podemos tener para este parámetro:

FOR ALL [INDEXED | HIDDEN] COLUMNS
[size_clause]

FOR COLUMNS [size clause] column|attribute [size_clause] [,column|attribute [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

-Integer : numéro de buckets de histogramas dentro del rango de 1 .. 254.

-Repeat: Calcula histogramas solamente en las columnas que ya tienen histogramas.

-Auto: Oracle determina a cuales columnas les debe de calcular histogramas basándose en la carga y distribución de datos.

-Skewonly: Oracle determina a cuales columnas debe calcular histogramas basándose en la distribución de datos.

El valor de DEFAULT es FOR ALL COLUMNS SIZE AUTO

Histogramas

Antes de pasar a escribir instrucciones vamos a ver que son los histogramas para comprender por que es útil la opción mencionada.

Cuando se calculan estadísticas en una tabla, DBMS_STATS obtiene información sobre la distribución de los datos en las columnas de la tabla. La información básica sobre la distribución puede ser el valor máximo y mínimo de una columna, pero esta información o este nivel de estadísticas pueden ser insuficientes si los datos dentro de la columna no están distribuidos uniformemente (skewed, no se como traducir esa palabra). Para distribuciones “no uniformes” se utilizan los histogramas y le dicen a Oracle como están distribuidos los datos en la columna.

Básicamente los histogramas son útiles cuando tenemos datos distribuidos de manera no uniforme, por ejemplo si tenemos una tabla con 1,000,000 de registros donde 950,000 tienen el valor ‘X’ y otros 50,000 tienen otros valores diferentes(Y, Z, A, B, C, etc). Cuando queremos obtener los datos que tienen X (where columna=’X') obviamente no queremos que utilice un índice ya que va a tener un costo muy alto debido a que tiene que accesar casi TODA (FULL) la tabla + todo el índice, pero cuando queremos un valor especifico (una minoría) como Y (where columna=’Y') entonces ahí si queremos que use un índice ya que no va a accesar toda la tabla si no una porción.

Oracle utiliza dos tipos de histogramas: Heigh-balanced y frecuencia.

Heigh-Balanced

En este tipo los valores de las columnas son divididos en bandas o grupos y cada grupo contiene aproximadamente el mismo número de registros. Tomemos por ejemplo una columna que tiene valores del 1 al 100 y un histograma con 10 grupos (buckets), entonces el histograma seria como sigue:

En este ejemplo el número de registros por grupo es 1/10 de todos los registros de la tabla, esto quiere decir 10 registros por cada grupo.

Si los datos no estuvieran uniformemente podría ser algo así:

Aquí la mayoría de los registros tienen un valor de 5 y por ejemplo los registros con un valor entre 60 y 100 son 1/10 de todos los registros de la tabla, esto quiere decir que pueden ser como máximo 10 registros en comparación con el ejemplo anterior que son 4/10 y que podrían llegar a ser 40 registros.

Frecuencia

En este tipo cada valor de la columna corresponde a un grupo (bucket) del histograma. Cada grupo contiene el número de ocurrencias/repeticiones de un valor.

5	20	5	6	10	5	3	23	234	12
a	b	c	d	e	f	g	h	i	x

Y pues de esta manera tenemos 10 grupos donde el grupo de “a” tiene 5 registros, el grupo de “e” tiene 10 repeticiones, etc.

Manos a la obra…

Para terminar con la teoría vamos a ver un ejemplo como funciona este rollo…

SQL> CREATE TABLE DEMO AS SELECT * FROM ALL_OBJECTS;
Table created.
SQL> CREATE INDEX STAT_IDX ON DEMO(STATUS);
Index created.
SQL> SET AUTOTRACE TRACEONLY EXPLAIN;
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 3974250510
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     5 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| STAT_IDX |    94 |   470 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='INVALID')
Note
-----
   - dynamic sampling used for this statement
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='VALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 2265447936
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     5 |    32   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |     5 |            |          |
|*  2 |   INDEX FAST FULL SCAN| STAT_IDX | 50562 |   246K|    32   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STATUS"='VALID')
Note
-----
   - dynamic sampling used for this statement

Ahora vamos a calcular estadisticas con histogramas.

SQL> exec dbms_stats.gather_table_stats('SCOTT','DEMO',METHOD_OPT=>'FOR COLUMNS SIZE AUTO STATUS');
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 3974250510
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| STAT_IDX |   146 |  1022 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='INVALID')
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='VALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 2265447936
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     7 |    31   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| STAT_IDX | 52655 |   359K|    31   (7)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STATUS"='VALID')
SQL> SET AUTOT OFF;
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='INVALID';
  COUNT(*)
----------
        94
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='VALID';
  COUNT(*)
----------
     52711

Aquí podemos que el explain plan no cambio mucho, “al parecer” salió peor que sin histogramas, por ejemplo con el valor INVALID tenemos rows 94 vs 146 al final, con la otra opción tenemos 50562 vs 52655 lo cual esta un poco alejado al numero de registros que existen. Esto se debe a que le dijimos a Oracle que hiciera el cálculo de manera AUTOmatica.

Vamos ahora a decirle un número de buckets.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','DEMO',METHOD_OPT=>'FOR COLUMNS SIZE 100 STATUS');
PL/SQL procedure successfully completed.
SQL> CONNECT SCOTT/ORACLE;
Connected.
SQL> SET AUTOT TRACEONLY EXPLAIN
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='INVALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 3974250510
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| STAT_IDX |    99 |   693 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='INVALID')
SQL> SELECT COUNT(*)
  2  FROM DEMO
  3  WHERE STATUS='VALID';
Execution Plan
----------------------------------------------------------
Plan hash value: 2265447936
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |     7 |    31   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| STAT_IDX | 52702 |   360K|    31   (7)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STATUS"='VALID')

Orale, ahora vemos que la información que tiene Oracle es mas exacta respecto al numero de registros, por ejemplo INVALID 94 vs 99 y VALID 50562 vs 52702, cuando los valores reales son 94 y 52711. Así que el numero de bandas (buckets) que le especifiquemos a Oracle que tiene que crear influyen bastante en como Oracle va a identificar los diferentes rangos.

Cuando debo calcular histogramas.

Bueno igual que en todo los casos de performance tunning no existe una regla que aplique a todos los casos, según lo que he leído por ejemplo muchas personas recomiendan calcular histogramas en sistemas de datawarehouse y no “siempre” en OLTP (aunque hay excepciones), si se conoce muy bien la aplicación y se sabe que alguna(s) columna(s) tiene una distribución de datos no uniforme por ejemplo de 1 millón de registros solo 1000 tienen valores diferentes y el resto tiene el valor X, tal vez esa columna sea una candidata para histogramas. Una vez observe que un query tenia un explain plan “perfecto” accesaba índices, el costo era bajo, etc. Sin embargo para algunos casos se tardaba “mas” tiempo que en otros casos y querían saber cual era la razón, estuvimos probando con varias opciones y nos dimos cuenta que una de las columnas usadas en el query caia en el esquema mencionado anteriormente, calculamos histogramas (200 buckets) en esa columna y el query mejoro bastante su ejecución.

Aunque esto se aplica a las columnas yo no recomendaría calcular histogramas a TODAS las columnas debido a que no siempre será benéfico o no tendrá un gran impacto en la ejecución y pues además el calculo implica mas tiempo al obtener estadísticas con gather_table_stats.

Conclusión

Quería mencionar esta opción de gather_table_stats debido a que puede ayudar muchísimo a la ejecución de un query, le da mas información a Oracle sobre la distribución de la información y eso ayuda a obtener un mejor explain plan. Aunque recomiendo hacer pruebas antes de implementarlo en un ambiente de producción.

Hasta la próxima…a lo mejor continuo con gather_schema_Stats

DBMS_STATS Parte 2

GATHER_TABLE_STATS.

Bueno continuamos con la onda de DBMS_STATS, ahora con el procedimiento GATHER_TABLE_STATS. Este procedimiento permite calcular las estadísticas de una tabla, columnas e índices (como ya lo había mencionado anteriormente).

Sintaxis

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2,
   tabname          VARCHAR2,
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type
                                    (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   force            BOOLEAN DEFAULT FALSE);

Parámetro	Descripción
Owname	Usuario/schema/dueño de la tabla(s).
Tabname	Nombre de la tabla.
Partname	Nombre de la partición.
Estímate_percent	Porcentaje de registros para calcular las estadísticas. NULL significa compute y el rango puede ir de [0.000001,100], igual que en los índices, también se puede utilizar DBMS_STATS.AUTO_SAMPLE_SIZE.
Block_sample	Si queremos utilizar el cálculo basado en bloques de datos (mínima estructura de almacenamiento de Oracle) en lugar de utilizar registros (rows). Lo hace de manera aleatoria (random).
Method_opt	Si deseamos calcular histogramas. Los valores pueden ser: FOR ALL [INDEXED | HIDDEN] COLUMNS [size clause] FOR COLUMNS [size clause] column|attribute [size_clause]..] size_clause es definida como size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY} Integer: Numero de histogramas (buckets), el valor es de 1 a 254. REPEAT: calcula histogramas solo en las columnas que ya tienen. AUTO: Oracle determina a que columnas les va a calcular histograma basado en la carga y distribución de los datos de la columna (s). SKEWONLY: Oracle determina a cuales columnas les va a calcular histograma basado en la distribución de los datos. El default es FOR ALL COLUMNS SIZE AUTO. Este esta interesante, a ver si puedo hacer un caso de prueba.
Degree	Grado de paralelismo para calcular las estadísticas. Es muy similar al del índice así que si desean mas detalle por favor vean el manual en línea.
Granularity	Nivel de detalle a calcular estadísticas, solo se utiliza cuando la tabla esta particionada. Opciones: ALL- Todas las particiones, subparticiones. AUTO- Oracle determina a cuales. Es el default. GLOBAL- Calcula estadísticas globales. GLOBAL AND PARTITION- Calcula igual que global y a nivel partición. PARTITION- A nivel partición. SUBPARTITION- A nivel subparticion.
Cascade	Calcula estadísticas en todos los índices de la tabla. Si se utiliza DBMS_STATS.AUTO_CASCADE Oracle determina a que índices calcular y a cuales no. Esta opción es lo mismo que gather_index_stats a cada uno de los índices de manera manual.
Stattab	Donde se van a guardar las estadísticas actuales (o viejitas).
Statid	Como se van a identificar las estadísticas actuales cuando se guarden.
Statown	Dueño de la tabla donde se guardan las estadísticas.
No_invalidate	TRUE/FALSE si se desea invalidar los cursores (queries “parseados”). Por default Oracle determina si lo hace o no.
Force	Calcular estadísticas sin importar si la tabla esta bloqueada.

A la practica…

Vamos a crear una tabla con la cual podamos jugar y ver si nos da los resultados que deseamos.

SQL> set timing on;
SQL> create table emps as
  2  select level empid,
  3         sysdate - (((18 * 365)/level) + dbms_random.value()*(47*365)) hired,
  4         trunc((50000 + dbms_random.value()*90000)) salary,
  5         dbms_random.string('A',10) name
  6  from dual
  7  connect by level <=1000000
  8  ;
Table created.
Elapsed: 00:00:53.67

Hay que crear el PK y un índice para la fecha de contratación basado en el año solamente.

SQL> alter table emps add constraint pk_emps primary key (empid);
Table altered.
SQL> create index emps_hired on emps (extract(year from hired));
Index created.
Elapsed: 00:00:02.86
SQL> set lines 130
SQL> alter session set nls_date_format='dd.mon.yyyy hh24:mi:ss';
Session altered.
Elapsed: 00:00:00.00
SQL> set null {null}

Ahora vamos a revisar que la tabla no tiene estadísticas y el índice si.

SQL> select table_name,
  2      num_rows,
  3      blocks,
  4      empty_blocks,
  5      avg_space,
  6      chain_cnt,
  7      avg_row_len,
  8      sample_size,
  9      last_analyzed
 10    from dba_tables
 11   where owner = 'SCOTT' and table_name = 'EMPS';
TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANA
--------------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ---
EMPS            {null}     {null}     {null}       {null}     {null}     {null}      {null}      {null}
Elapsed: 00:00:00.00
SQL> select index_name, last_analyzed, num_rows, distinct_keys, leaf_blocks
  2    from dba_indexes
  3   where owner = 'SCOTT' and index_name = 'PK_EMPS';
INDEX_NAME                     LAST_ANALYZED        NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS
------------------------------ ------------------ ---------- ------------- -----------
PK_EMPS                        05.oct.07 10:13:11    1000000       1000000        2087
Elapsed: 00:00:00.03

Borramos las estadísticas del índice para ver como se comporta.

SQL> exec dbms_stats.delete_INDEX_sTATS('SCOTT','PK_EMPS');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
SQL> exec dbms_stats.delete_index_stats('SCOTT','EMPS_HIRED');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SQL> select index_name, last_analyzed, num_rows, distinct_keys, leaf_blocks
  2    from dba_indexes
  3   where owner = 'SCOTT' and index_name like '%EMPS%';
INDEX_NAME                     LAST_ANALYZED        NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS
------------------------------ ------------------ ---------- ------------- -----------
PK_EMPS                        {null}             {null}     {null}        {null}
EMPS_HIRED                     {null}             {null}     {null}        {null}
Elapsed: 00:00:00.01

Vamos a ver como se comporta Oracle con índices y tablas sin estadísticas.

SQL> select *
  2  from emps
  3  where extract(year from hired) = '1959';
Elapsed: 00:00:01.11
Execution Plan
----------------------------------------------------------
Plan hash value: 2150600608
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    53 |   105K|     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       |    53 |   105K|     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |  4524 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=1959)
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
        738  recursive calls
          0  db block gets
        197  consistent gets
        583  physical reads
          0  redo size
        597  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select *
  2  from emps
  3  where extract(year from hired) = '2000';
21438 rows selected.
Elapsed: 00:00:02.37
Execution Plan
----------------------------------------------------------
Plan hash value: 2150600608
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            | 23305 |    45M|     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       | 23305 |    45M|     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |  4524 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=2000)
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       7324  consistent gets
       4171  physical reads
          0  redo size
     828924  bytes sent via SQL*Net to client
      16100  bytes received via SQL*Net from client
       1431  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21438  rows processed
SQL> select *
  2  from emps
  3  where to_char(hired,'YYYY') = '2000';
21438 rows selected.
Elapsed: 00:00:01.45
Execution Plan
----------------------------------------------------------
Plan hash value: 2715242515
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 23305 |    45M|  1197  (13)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| EMPS | 23305 |    45M|  1197  (13)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIRED"),'YYYY')='2000')
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       6224  consistent gets
         46  physical reads
          0  redo size
     828924  bytes sent via SQL*Net to client
      16100  bytes received via SQL*Net from client
       1431  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21438  rows processed

Podemos ver que no esta mal, utiliza el índice que creamos y agregue un full scan para ver el comportamiento.

Ahora vamos a calcular las estadísticas con gather_table_stats para ver que es lo que cambia.

SQL> select table_name,
  2         num_rows,
  3      blocks,
  4      empty_blocks,
  5      avg_space,
  6      chain_cnt,
  7      avg_row_len,
  8      sample_size,
  9      last_analyzed
 10    from dba_tables
 11   where owner = 'SCOTT' and table_name = 'EMPS';
TABLE_NAME        NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANA
--------------- ---------- ---------- ------------ ---------- ---------- ----------- ----------- ---
EMPS                995047       4798            0          0          0          32       49610 05.oct.07 10:45:13
Elapsed: 00:00:00.09
SQL> select index_name, last_analyzed, num_rows, distinct_keys, leaf_blocks
  2    from dba_indexes
  3   where owner = 'SCOTT' and index_name like '%EMPS%';
INDEX_NAME                     LAST_ANALYZED        NUM_ROWS DISTINCT_KEYS LEAF_BLOCKS
------------------------------ ------------------ ---------- ------------- -----------
PK_EMPS                        05.oct.07 10:45:22    1000000       1000000        2087
EMPS_HIRED                     05.oct.07 10:45:27    1000000            49        2089
Elapsed: 00:00:00.42

Empezamos viendo el full scan para comparar. Podemos ver que después de calcular estadísticas el COST BASED OPTIMIZER mejoro el plan de ejecución, por ejemplo el número de bytes anterior era de 45M y después es de 281K una gran mejora.

SQL> select *
  2  from emps
  3  where to_char(hired,'YYYY') = '2000';
21438 rows selected.
Elapsed: 00:00:02.06
Execution Plan
----------------------------------------------------------
Plan hash value: 2715242515
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  9950 |   281K|  1180  (11)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| EMPS |  9950 |   281K|  1180  (11)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR(INTERNAL_FUNCTION("HIRED"),'YYYY')='2000')
Statistics
----------------------------------------------------------
        664  recursive calls
          0  db block gets
       6255  consistent gets
       4731  physical reads
          0  redo size
     828924  bytes sent via SQL*Net to client
      16100  bytes received via SQL*Net from client
       1431  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
      21438  rows processed

Ahhh, ahora podemos ver como después de calcular estadísticas Oracle toma otro camino diferente. Antes de calcular estadísticas Oracle decidió de usar el índice.

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            | 23305 |    45M|     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       | 23305 |    45M|     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |  4524 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=2000)

Pero ahora vemos que Oracle esta decidiendo hacer un full scan…mmmm.

SQL> select *
  2  from emps
  3  where extract(year from hired) = '2000';
21438 rows selected.
Elapsed: 00:00:02.67
Execution Plan
----------------------------------------------------------
Plan hash value: 2715242515
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 24125 |   683K|  1181  (12)| 00:00:15 |
|*  1 |  TABLE ACCESS FULL| EMPS | 24125 |   683K|  1181  (12)| 00:00:15 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=2000)
Statistics
----------------------------------------------------------
        689  recursive calls
          0  db block gets
       6265  consistent gets
       4740  physical reads
          0  redo size
     828924  bytes sent via SQL*Net to client
      16100  bytes received via SQL*Net from client
       1431  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
      21438  rows processed

Podemos ver como el calculo de estadísticas afecto el explain plan para un mismo query, básicamente Oracle nos esta diciendo que es mas “barato” hacer un full scan que usar un índice. Vamos a analizar los explain plans; el costo (CPU) es mas alto 5 vs 1181 pero los bytes son mejores en el full scan (683K vs 45M) así que Oracle ha decidido que es mejor hacer un full scan (no quiero entrar en detalles de la razón ya que me llevaría mucho tiempo explicarlo, por el momento vamos a dejarlo así pero existe una razón por que es mas costoso usar el índice vs un full scan EN ESTE CASO).

Lo que quería mostrar es que el plan de ejecución puede (va a cambiar) con el calculo de estadísticas y esto es debido a que Oracle tiene mas información para decidir cual es el “mejor” camino.

Pero podemos ver que para el siguiente caso continua utilizando el índice y de hecho mejora mucho el plan de ejecución.

Antes de calcular estadísticas el plan era el siguiente:

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    53 |   105K|     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       |    53 |   105K|     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |  4524 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Después de calcular estadísticas:

SQL> select *
  2  from emps
  3  where extract(year from hired) = '1959';
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
Plan hash value: 2150600608
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    29 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPS       |     1 |    29 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPS_HIRED |     1 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(EXTRACT(YEAR FROM INTERNAL_FUNCTION("HIRED"))=1959)
Statistics
----------------------------------------------------------
        689  recursive calls
          0  db block gets
        125  consistent gets
         21  physical reads
          0  redo size
        597  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          1  rows processed

Podemos observar varias cosas aquí, el numero de bytes accesadsos son menos (29 vs 105K) lo mismo para el numero de rows (1 vs 53), inclusive si vemos las estadísticas, el numero de lecturas físicas es menor (21 vs 583) hay grandes diferencias.

Vamos a analizar poquito por que ahora con estadísticas Oracle los planes cambiaron.

SQL> select extract(year from hired), count(*),(count(*)/1000000)*100 PCT
  2  from scott.emps
  3  group by extract(year from hired)
  4  having extract(year from hired) in ('2000','1959');
EXTRACT(YEARFROMHIRED)   COUNT(*)        PCT
---------------------- ---------- ----------
                  2000      21438     2.1438
                  1959          1      .0001
Elapsed: 00:00:01.79

En los ejemplos que estamos utilizando buscamos por dos años,2000 y 1959, podemos ver que del total de millón de registros el año 2000 contiene 21,438(2%) y el año 1959 1(casi nada %), y es por eso que Oracle en el primero considera mejor hacer un full scan ya que va accesar una cantidad considerable de registros y en el otro caso es preferible usar un índice ya que es solo 1 registro el que se va accesar; obviamente Oracle no tenia esa información antes de calcular estadísticas, no sabia cuantos valores diferentes tenia ni que porcentaje del total de la tabla había respecto a un dato.

Conclusión

Podemos ver que al plan de ejecución mejoro muchísimo después de calcular estadísticas y eso que solo utilizamos los valores por default. También observamos que el explain plan va a cambiar una vez que calculemos estadísticas, el cambio puede ser benéfico o puede afectar así que es mejor probar antes de aplicarlo a una base de producción, tal vez en el caso del full scan nosotros deseamos que utilicé el índice, en este caso tendríamos que hacer algunos cambios pero por ahora así lo vamos a dejar para no ahondar en otras cosas y perdernos mas. Otra cosa que se vio fue que al momento de calcular estadísticas a la tabla también se le calcularon estadísticas a los índices de manera automática y realmente no necesitamos calcular individualmente cada índice.

En el próximo capitulo de esta telenovela voy a continuar con GATHER_TABLE_STATS y voy a utilizar otros parámetros para calcular las estadísticas y observar que hay de diferente.

Hasta la próxima…

DBMS_STATS. Parte 1

DBMS_STATS

Otra manera de calcular estadisticas además del comando ANALYZE es el paquete DBMS_STATS. Este paquete se utiliza para modificar, ver, exportar, importar y borrar estadisticas de la base de datos.

Como ya habia mencionado Oracle recomienda la utilización de este paquete a partir de la versión 9i para el calculo de estadisticas en lugar de usar ANALYZE debido a que es mas exacto y mas eficiente. Aunque ANALYZE ya no sea la opción adecuada para calcular estadisticas eso no quiere decir que ya no sirva para nada, todavia se puede utilizar para validar la estructura de una tabla o buscar por CHAINED ROWS y algunas otras cosas.

Cuando se generan nuevas estadisticas para una tabla, columna o indice las estadisticas existentes son actualizadas por Oracle, cuando se actualizan las estadisticas Oracle invalida cualquier SQL que se encuentra en memoria (parsed) que accesa el objeto al cual se les estan calculando estadisticas. Esto quiere decir que si alguien ejecuto un query sobre la tabla empleados y se empiezan a calcular estadisticas sobre esa table Oracle invalida el query SQL que esta compartido en la memoria en lugar de re-utilizarlo (espero no haber confundido aqui esto tiene que ver con la manera en que Oracle reutiliza SQL previamente ejecutados, cualquier duda pregunten). Oracle utiliza las nuevas estadisticas cuando el query SQL es ejecutado de nuevo y por lo cual puede utilizar un plan de ejecucion diferente.

Procedimientos para el calculo de estadisticas dentro del paquete DBMS_STATS

Procedimiento	Que calcula?
GATHER_INDEX_STATS	Indices
GATHER_TABLE_STATS	Tablas, columnas e indices
GATHER_SCHEMA_STATS	Para todos los objetos del schema
GATHER_DICTIONARY_STATS	Para todos los objetos del diccionario de datos
GATHER_DATABASE_STATS	Para todos los objetos en la base de datos

Bueno vamos a empezar con el primero antes de que se haga mas aburrido y luego vamos explicando el por que de algunas cosas.

GATHER_INDEX_STATS

Bueno ya habiamos mencionado que este procedimiento calcula estadisticas a los indices.

Sintaxis

DBMS_STATS.GATHER_INDEX_STATS (
ownname          VARCHAR2,
indname          VARCHAR2,
partname         VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER   DEFAULT to_estimate_percent_type
(GET_PARAM('ESTIMATE_PERCENT')),
stattab          VARCHAR2 DEFAULT NULL,
statid           VARCHAR2 DEFAULT NULL,
statown          VARCHAR2 DEFAULT NULL,
degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')),
force            BOOLEAN DEFAULT FALSE);

Parametro Descripcion

ownname Schema/usuario/dueño del indice al cual se le van a calcular

indname Nombre del indice

partname Nombre de la particion

estimate_percent Porcentaje de registros a estimar (NULL significa compute[todos]). Rango valido [0.000001,100]. Se recomienda usar la constante DBMS_STATS.AUTO_SAMPLE_SIZE para dejar que Oracle obtenga el valor adecuado para calcular las estadisticas.

stattab Tabla de estadisticas donde se guardan las actuales (mas adelante mencionamos eso)

statid Identificador para asociar las estadisticas actuales

statown Schema que contiene la tabla de estadisticas (si es diferente al usuario)

degree Grado de paralelismo, el default es NULL, lo cual significa que usa el que tenga la tabla cuando se creo.

granularity Detalle del calculo, solo se utiliza cuando la tabla esta particionada.

ALL,AUTO,DEFAULT,GLOBAL,GLOBAL AND PARTITION, PARTITION, SUBPARTITION.

no_invalidate No invalida los cursores (SQL query) que dependen del objeto si se pone TRUE. Por default los invalida inmediatamente.

force Calcula estadisticas inclusive si el objeto esta bloqueado.

Verficar el manual para obtener mas detalles de los parametros.(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#sthref8108)\

Bueno, primero vamos a usar un procedimiento que ayuda a borrar las estadisticas actuales.

SQL> EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','PK_EMP');

PL/SQL procedure successfully completed.

Ahora vamos a ver que es lo que esta almacenado como estadisticas:

SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'
6  and index_name ='PK_EMP';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP

Aqui podemos ver que el indice no ha sido analizado y por lo tanto no tiene estadisticas aunque la tabla puede tenerlas como se muestra en el siguiente ejemplo:

SQL> select OWNER,table_NAME,LAST_ANALYZED
2  from dba_tables
3  where owner = 'SCOTT'
4  and table_name = 'EMP';
OWNER      TABLE_NAME                     LAST_ANAL
---------- ------------------------------ ---------
SCOTT      EMP                            22-AUG-07

Asi que podemos tener tablas con estadisticas e indices sin estadisticas y viceversa. Aunque no es remendable tener este tipo de estadisticas es posible.

Ahora vamos a calcular las estadisticas del indice:

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'
6  and index_name ='PK_EMP';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP             14          14 02-OCT-07          0           1            14

Y asi de sencillo se pueden calcular estadisticas para un indice. En este caso utilizamos unicamente las opciones por default.

Vamos a analizar que se hizo, le dijimos que indice queremos calcular, pero podemos ver que el sample_size utilizado fue el mismo numero de registros de la tabla esto quiere decir que Oracle uso NULL que significa COMPUTE que significa 100% o todos los registros. Para este caso en que la tabla es muy pequeña es muy rapido pero hay casos donde las tablas son de millones de registros estoy puede tardar varios minutos asi que se recomienda dejar que oracle lo determine utilizando DBMS_STATS.AUTO_SAMPLE_SIZE.

Vamos a cambiar este parametro para compara resultados con una tabla con algunos registros demas:

SQL> INSERT INTO SCOTT.EMP2(EMPNO,ENAME)
2  SELECT LEVEL,'TEST'
3  FROM DUAL
4  CONNECT BY LEVEL <=1000000;
1000000 rows created.
SQL> EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','PK_EMP2');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2       1000000     1000000 02-OCT-07          2        1875       1000000
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,10);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2        971743      593140 02-OCT-07          2        1822        971743
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,50);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANALYZED            BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- -------------------- ---------- ----------- -----------
SCOTT      PK_EMP2       1008372      615498 02/OCT/2007 11:53:18          2        1891       1008372
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANALYZED            BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- -------------------- ---------- ----------- -----------
SCOTT      PK_EMP2       1000000     1000000 02/OCT/2007 11:55:34          2        1875       1000000

Aqui podemos ver que al igual que el comando ANALYZE si le damos un valor mas alto a estimate_percent entones obtendremos estadisticas mas exactas, pero de igual manera va a tardar mas tiempo en calcularlas. Puede observarse que en el ultimo ejemplo se calcularon usando AUTO_SAMPLE_SIZE y Oracle decidio que lo ideal era calcular para todos los registros.

Y asi se calculan estadisticas para indices, en lo personal yo casi nunca he utilizado este metodo debido a que siempre las calculo automaticamente con el procedimiento GATHER_TABLES_STATS o GATHER_SCHEMA_STATS, esto lo voy a mostrar en los proximos dias.

Los procedimientos tienen mas parametros sobre los cuales no mostre como funcionaban, la verdad no lo hice por que los parametros explican claramente que hacen cada uno y debido a que se utilizan para casos mas “avanzados” como particiones, paralelismo, etc. Y la verdad me dio flojera hacer una caso de prueba para ese tipo, creo que los mas importantes y que se aplican mas seguido son los mostrados, a menos que yo personalmente considere mencionar otro lo voy a hacer y si alguien desea saber como funciona entonces haganmelo saber.

Cuando calcular estadisticas?

De nuevo, no existe una regla sobre cada cuando se deben de calcular; pero algunos consejos son por ejemplo si se insertan/borran/actualizan un gran numero de registros a una tabla, tal vez millones, entonces inmediatamente despues hay que calcular debido a que si puede afectar los planes de ejecucion ya que se hizo un gran cambio. Si se desea hacer de manera automatica entonces depende de la carga de la base de datos, de la aplicacion, puede haber casos que semanalmente esta bien o hay veces que 1 vez al mes.

Bueno hasta la siguiente…con GATHER_TABLE_STATS.