Archive for category Oracle
Como hacer mas rapida la importacion de datos.
Posted by delfinonunez in Oracle on 13 April, 2012
En un post anterior mencione como migrar una base de datos de un servidor a otro pero nunca di ejemplos de como hacerlo. Debido a que algunas personas han leido mi blog y me han hecho preguntas de como se hace en ciertos casos decidi mejor documentar el proceso para ciertos casos.
Como había mencionado en ese post existen maneras sencillas y complejas, además que cada base de datos es diferente y se encuentra en diferente hardware,etc. Así que tal vez las siguientes soluciones no den el mismo resultado en su ambiente.
Primero que nada vamos a crear unas tablas de ejemplo.
SQL> ed
Wrote file afiedt.buf
1 create table t1
2 as
3 with generator as(
4 select rownum id
5 from dual
6 connect by level <=100000
7 )
8 select rownum id,
9 lpad(rownum,10,'0') small_vc,
10 rpad('x',100) padding,
11 lpad(rownum,5,'0') smaller_vc
12 from
13 generator v1,
14 generator v2
15* where rownum <= 500000
SQL> /
Table created.
SQL> create table t2 as select * from t1;
Table created.
SQL> create table t3 as select * from t1;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
500000
SQL>
SQL>
Una vez creada las tablas vamos a crear un par de indices.
create index idx1t1 on t1(id); create index idx2t1 on t1(small_vc); create index idx3t1 on t1(smaller_vc); create index idx1t2 on t2(id); create index idx2t2 on t2(small_vc); create index idx3t2 on t2(smaller_vc); create index idx1t3 on t3(id); create index idx2t3 on t3(small_vc); create index idx3t3 on t3(smaller_vc);
Ahora que las tabla tienen medio millón de registros y sus respectivos indices vamos exportar el usuario.
[oracle@localhost exports][21:25:14]$ exp file=delfino_exp.dmp userid=delfino/delfino owner=delfino Export: Release 11.2.0.3.0 - Production on Thu Apr 12 21:25:23 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8MSWIN1252 character set (possible charset conversion) . exporting pre-schema procedural objects and actions . exporting foreign function library names for user DELFINO . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user DELFINO About to export DELFINO's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export DELFINO's tables via Conventional Path ... . . exporting table T1 500000 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table T2 500000 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table T3 500000 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully with warnings. [oracle@localhost exports][21:25:33]$ exp file=delfino_exp_direct.dmp userid=delfino/delfino owner=delfino direct=Y Export: Release 11.2.0.3.0 - Production on Thu Apr 12 21:25:48 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8MSWIN1252 character set (possible charset conversion) . exporting pre-schema procedural objects and actions . exporting foreign function library names for user DELFINO . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user DELFINO About to export DELFINO's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export DELFINO's tables via Direct Path ... . . exporting table T1 500000 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table T2 500000 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . . exporting table T3 500000 rows exported EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. EXP-00091: Exporting questionable statistics. . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions . exporting post-schema procedural objects and actions . exporting statistics Export terminated successfully with warnings. [oracle@localhost exports][21:26:01]$ ls -lh total 367M -rw-r--r-- 1 oracle oinstall 182M Apr 12 21:26 delfino_exp_direct.dmp -rw-r--r-- 1 oracle oinstall 185M Apr 12 21:25 delfino_exp.dmp
ok ahora podemos ver que hice 2 tipos de export, uno normal y otro con direct=Y, el primero tomó 10 segundos y el segundo 13 segundos, así que la diferencia no es muy significativa en este caso.
Ahora vamos a hacer el import en otro usuario.
Primero hacemos un import normal, con todo por default.
[oracle@localhost exports][21:34:57]$ imp userid=delfino/delfino file=delfino_exp.dmp fromuser=delfino touser=delfino2 Import: Release 11.2.0.3.0 - Production on Thu Apr 12 21:36:29 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path Warning: the objects were exported by DELFINO, not by you import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) . importing DELFINO's objects into DELFINO2 . . importing table "T1" 500000 rows imported . . importing table "T2" 500000 rows imported . . importing table "T3" 500000 rows imported Import terminated successfully without warnings. [oracle@localhost exports][21:37:41]$
El import tomó 1 min 12 segundos (72 segundos)
El siguiente ejemplo es un import sin indices, primero creamos el archivo con los indices y luego creamos los indices aparte.
[oracle@localhost exports][22:29:04]$ imp userid=delfino/delfino file=delfino_exp.dmp ignore=Y indexes=Y indexfile=indices.sql rows=N fromuser=delfino touser=delfino2 Import: Release 11.2.0.3.0 - Production on Thu Apr 12 22:29:54 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) Import terminated successfully without warnings. [oracle@localhost exports][22:29:54]$ imp userid=delfino/delfino file=delfino_exp.dmp ignore=Y indexes=N rows=Y fromuser=delfino touser=delfino2 Import: Release 11.2.0.3.0 - Production on Thu Apr 12 22:30:20 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) . importing DELFINO's objects into DELFINO2 . . importing table "T1" 500000 rows imported . . importing table "T2" 500000 rows imported . . importing table "T3" 500000 rows imported Import terminated successfully without warnings. [oracle@localhost exports][22:30:59]$ SQL> connect delfino2/delfino Connected. SQL> set timing on; SQL> @indices.sql Enter password: Connected. Index created. Elapsed: 00:00:00.45 Index created. Elapsed: 00:00:04.07 Index created. Elapsed: 00:00:00.73 Index created. Elapsed: 00:00:00.86 Index created. Elapsed: 00:00:02.91 Index created. Elapsed: 00:00:03.05 Index created. Elapsed: 00:00:02.86 Index created. Elapsed: 00:00:00.93 Index created. Elapsed: 00:00:00.81 SQL>
En este caso el import tomo solo 39 segundos sin tomar en cuenta lo que toma reconstruir los indices (~12 segundos). Eso fue una reducción de 33 segundos comparado con el anterior. Insignificante en este caso pero hubo una reducción.
Ahora si agregamos otros parametros podemos reducir un poco mas el tiempo.
[oracle@localhost exports][22:38:24]$ imp userid=delfino/delfino file=delfino_exp.dmp ignore=Y indexes=N rows=Y fromuser=delfino touser=delfino2 buffer=32000 commit=N statistics=none Import: Release 11.2.0.3.0 - Production on Thu Apr 12 22:38:33 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.02.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) . importing DELFINO's objects into DELFINO2 . . importing table "T1" 500000 rows imported . . importing table "T2" 500000 rows imported . . importing table "T3" 500000 rows imported Import terminated successfully without warnings. [oracle@localhost exports][22:39:04]$
En este caso el tiempo se redujo a 31 segundos, 8 segundos menos.
En resumen, en realidad se puede reducir el tiempo del import utilizando diferentes parámetros y aplicando ciertas técnicas. Este ejemplo fue muy rápido y en realidad las tablas son muy pequeñas como para representar una migración grande pero da una idea de como reducir el tiempo de importación.
Otras cosas que se pueden hacer para reducir el tiempo de importación:
- Poner el o los archives exportados en discos diferentes a donde se encuentran los “datafiles” de la base de datos para evitar contención en los discos.
- Remover el archiving de la base de datos para que se generen menos logs (menos I/O).
- Utilizar data pump y si es posible utilizar procesos paralelos.
- Utilizar imports en paralelo. Esto se logra exportando ciertas tablas en varios archives y corriendo varios imports separados por cada archivo.
- Y debe haber otros métodos que no recuerdo por el momento…
De SQL Developer y otras cosas…
Posted by delfinonunez in Oracle, SQL Developer on 6 April, 2010
Wow, hace varios meses que no escribo nada…bastante tiempo, he recibido comentarios de la serie de estadisticas y voy a escribir algunas cosas mas al respecto. No prometo fechas, por que la ultima vez dije que iba a escribir mas seguido y casi pasó un año sin escribir…así que mejor digo que voy a escribir pero no prometo fechas.
Pasando a otras cosas… ultimamente he estado usando mas SQL Developer y deje de utilizar TOAD por completo. Aunque hay muchisimas cosas que TOAD hace y que SQLD no las tiene o las hace muy mal, puedo decir que gran parte de las tareas que hago las puedo realizar con SQLD. Estuve moviendole un poco y cree unos reportes y “plugins” que me han sido muy utiles asi que quiero compartirlos por si les sirven a alguien.
Pueden bajarlos y modificarlos a su gusto, no es necesario ningun link ni agradecimiento ni nada, son gratis y cada quien los puede usar como quiera; y por tal razon no me hago resposable si algo pasa con sus bases de datos, no que fallen o hagan algo mal al contrario funcionan muy bien.
Todos los scripts estan probados en la version de SQL Developer 1.5.5, los reportes funcionan en la version 2.1 pero los plugins fallan, al parecer hay un bug en las nuevas versiones que no deja que los plugins funcionen bien, lo reporte en el foro de discusion pero no han arreglado eso asi que para que todo funcione utilicen la version 1.5.5.
LOG SWITCHES
Este reporte muestra los log switchs de la base de datos durante el dia por cada dia en la parte superior del reporte, si le dan click a un registro de la parte superior mostrara el detalle en la parte inferior todos los switches de ese dia y mostrara cuanto tiempo transcurrio de un log switch a otro.
SESSION BROWSER
Este es un reporte donde se pueden ver las sesiones conectadas a la BD y muchisimos detalles sobre estas. No voy a detallar que es lo que hacen todas las opciones pero se puede ver informacion como: current wait, current sql, long operations,etc…
Es muy util para identificar problemas con una sesion en especifico.
TABLESPACE INFO
Permite ver la utilización de espacio de los tablespaces de la base de datos, en la parte inferior se mostrara el detalle de los datafiles del tablespace seleccionado.
En el siguiente post voy a poner algunos editores y navegadores de SQL Developer…
Hasta las proxima.
Saludos y gracias por seguir leyendo.
Como recuperar estadisticas anteriores con Oracle 10G
Posted by delfinonunez in Oracle on 29 August, 2009
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.
Posted by delfinonunez in Oracle on 13 July, 2009
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:
- 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) ?
- La versión del sistema operativo es la misma?
- De que tamaño es la base de datos?
- Hay posibilidad de detener la base de datos durante la migración o tienen que ser en tiempo real?
- 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
Posted by delfinonunez in Oracle on 16 July, 2008
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. |
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
Posted by delfinonunez in Oracle on 16 July, 2008
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
DBA_FREE_SPACE
Posted by delfinonunez in Oracle on 17 April, 2008
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:
- El tiempo se redujo considerablemente: de 00:02:50.81 a 00:00:06.21.
- 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!!!
Posted by delfinonunez in Oracle on 6 April, 2008
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.
Migración de Oracle 10g Standard Edition a Enterprise Edition
Posted by delfinonunez in Oracle on 2 April, 2008
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.
- Primero hay que obtener un respaldo de la base y de los archivos init.ora, listener.ora, sqlnet.ora.
- « Bajar » la base de datos (shutdown immediate).
- Si se tiene instalado DBConsole (Enterprise Manager) hay que apagar los servicios : emctl stop dbconsole.
- Detener los listeners : lsnrctl stop listener.
- Verificar que el directorio oraInventory para nuestra base exista en el folder correcto. Ej : /oracle/oraInventory
- Pasos 7 y 8 son para Unix. Si es windows entonces nada mas ejecutar el installer desde Programs.
- 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.
- Desde la sesión XTerm entrar al directorio ($ORACLE_HOME/oui/bin) y ejecutar el instalador (./runInstaller)
- Click en Deinstall Products
- 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).
- Después de seleccionar el Oracle Home entonces dar click en REMOVE y luego YES.
- La pantalla de REMOVE va aparecer con el progreso de la operación.
- Cuando termine la desinstalación cerrar el Oracle Universal Installer.
- En mi caso tuve que borrar el restante del Oracle Base
- El siguiente paso es instalar el software únicamente pero en la versión Enterprise Edition.
- Una vez instalada la nueva versión entonces instalar los PatchSets y los Patches.
- Regresar los respaldos de init.ora, listener.ora, tnsnames.ora a su lugar original.
- Recrear el password file en caso que se haya tenido activada esta opción.
- 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.
- 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.
- Connect as sys and execute
cd $ORACLE_BASE
rm –Rf product
@$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;
- Verificar que sus backups funcionen.
- Probar el listener y tnsnames.
- Si se tenia DBConsole instalada hay que reinstalar de nuevo.
- 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…
Posted by delfinonunez in Oracle on 15 October, 2007
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
