Archive for category Oracle

Como hacer mas rapida la importacion de datos.

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…

Leave a comment

De SQL Developer y otras cosas…

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.

image

DOWNLOAD LOG SWITCH

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.

image

 

DOWNLOAD SESSION BROWSER

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.

image

 

DOWNLOAD TABLESPACE INFO

 

En el siguiente post voy a poner algunos editores y navegadores de SQL Developer…

Hasta las proxima.

 

Saludos y gracias por seguir leyendo.

2 Comments

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&lt;

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

1 Comment

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.

13 Comments

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>

1 Comment

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

6 Comments

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

Leave a comment

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

1 Comment

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
  15. cd $ORACLE_BASE
    rm –Rf product

  16. El siguiente paso es instalar el software únicamente pero en la versión Enterprise Edition.
  17. Una vez instalada la nueva versión entonces instalar los PatchSets y los Patches.
  18. Regresar los respaldos de init.ora, listener.ora, tnsnames.ora a su lugar original.
  19. Recrear el password file en caso que se haya tenido activada esta opción.
  20. 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.
  21. 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.
  22. 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.

17 Comments

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

6 Comments

Follow

Get every new post delivered to your Inbox.