Como recuperar estadisticas anteriores con Oracle 10G

29 August, 2009 1 comment

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
Categories: Oracle

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

13 July, 2009 7 comments

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.

Categories: Oracle

DBMS_STATS Parte 4, Exportar estadísticas

16 July, 2008 Leave a comment

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>
Categories: Oracle

Tablespace Size en SQL Developer

16 July, 2008 2 comments

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

Categories: Oracle

DBA_FREE_SPACE

17 April, 2008 Leave a comment

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

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

Recycle Bin

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

drop table emp;

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

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

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

Y que tiene que ver el recycle bin con dba_free_space?

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

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

Primero hay que ejecutar un query que contenga la vista:

SQL> select * from v$version;

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

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

System altered.

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

System altered.

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

SQL> /

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

15 rows selected.

Elapsed: 00:02:50.81

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

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

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

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

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

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

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

Elapsed: 00:02:50.81

Del explain plan podemos ver que accesa el recycle bin:

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

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

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

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

Elapsed: 00:28:58.65

SQL> alter system flush shared_pool;

System altered.

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

System altered.

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

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

15 rows selected.

Elapsed: 00:00:06.21

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

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

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

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

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

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

Inmediatamente podemos ver varias cosas:

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

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

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

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

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

Oracle technet en Spanish!!!

6 April, 2008 1 comment

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

Categories: Oracle

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

2 April, 2008 17 comments

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.

Categories: Oracle

Back to the basics!

24 January, 2008 Leave a comment

Últimamente he andado ocupado con entrevistas, buscando nuevo departamento, etc. Y no he tenido tiempo para escribir algo; pues acabo de firmar contrato para trabajar como DBA de Oracle+SQL Server+Informix+SAP, me llama mucho la atención poder aprender otras bases de datos (Informix) y también SAP, al parecer en esta compañía utilizan mucho estos programas así que vamos a ver que sucede. También es una oportunidad para desempolvarme como DBA, hace casi 1 año que no hago ninguna tarea de DBA y ya tengo ganas de regresar a esa área. Por eso volví a leer manuales y documentos de conceptos básicos para que no me agarren de bajada mis nuevos compañeros, sobre todo estoy leyendo sobre backup y recovery y tuning.

Así que ahora que voy a continuar trabajando voy a tener mas ejemplos y casos raros que podré analizar aquí.

Se me acabaron las vacaciones, que bueno ya me estaba aburriendo mucho en la casa, hay que distraerse en el trabajo un rato.

Categories: Personal

Feliz año, feliz navidad, feliz dia de reyes, etc y etc…

11 January, 2008 Leave a comment

Pues no había escrito nada por que últimamente me han pasado varias cosas. Primero mi primer empleo canadiense se acabo, bueno no se acabo, mas bien me lo acabaron; pues resulta que la compañía hizo una reorganización interna y recortaron personal y pues ya saben, me toco que me despidieran. Ni modo c’est la vie y hay que seguir adelante, la verdad me gustaba mucho donde estaba por que conocí muy buenos amigos y en general el ambiente era muy bueno, mi jefe era bien buena “onda”, el trabajo estaba muy relajado y lo único que no me gustaba era que no estaba avanzando profesionalmente y por eso me hicieron un favor por que ya estaba yo dándole vueltas a la idea de cambiar de empleo y pues así me ahorrar la pena de renunciar. Y hasta eso, me fue muy bien por que me pagaron el despido, vacaciones y yo no esperaba nada de eso.

También ya tenia planeadas unas vacaciones a México para navidad y año nuevo así que todo se acomodo “mágicamente”, mi ultimo día de trabajo fue el 21 de Diciembre y yo volé a México el 20 (pedí un día), pasé las vacaciones con mi familia y acabamos de regresar el 5 de Enero, directito a clases de francés. Lo bueno de todo es que todavía no tengo una semana de regresar y ya me salieron 3 ofertas de empleo, una ya la rechace por que no me sentía agusto con el contrato y por que estaba muy lejos de donde vivo y las otras 2 hay van en las negociaciones, una de ellas es de DBA y es la que mas me interesa ya que quiero regresar a ese camino y la otra es de developer ETL.

Y pues esas han sido las razones por que no he escrito nada, no he tenido mucho tiempo y también me he vuelto muy flojo; espero este nuevo año sea mas constante.

Y pues fue gracias a Sir Ragnar que me “taggeo” que vuelvo a las andadas con el blog.

Así que agárrense que hay les voy de nuevo con éste blog y con el de plsqlenespanol a ver que sale.

Categories: Personal

Mis 8 cositas!!!…

11 January, 2008 4 comments

Aunque nunca imagine que me iban a “taggear”, pues mi amigo Sir Ragnar lo hizo y aunque no me gustan las cadenas pues vamos a seguir ésta que esta interesante.

  1. Cuando era niño por poco incendio la casa de mi casa por andar jugando con cerillos (fósforos) cerca de unos botes con gasolina, afortunadamente no paso a mayores ya que mi mamá y unas señoras que estaban con ella apagaron el fuego rápidamente. Pero nunca se me va a olvidar esa travesura.
  2. Soy mexicano, del norte si señor del meritito Chihuahua, viví alrededor de 21 años en ciudad Juárez y actualmente vivo en Montreal Canadá con mi esposa.
  3. La primer vez que aprendi Oracle fue cuando trabajaba para la universidad y nos dieron cursos para migrar las aplicaciones, cosa que desde entonces(1999) no se ha hecho.
  4. Me gusta mucho viajar y he estado en varias partes de USA, varios lugares de México (obviamente) y algunos lugares de Europa. Me gustaría mucho conocer Sudamérica, ahora que conozco más gente de por allá se me hace mas interesante.
  5. Siempre he querido aprender a tocar un instrumento, en especial la guitarra, pero por una u otra razón no se ha dado…bueno más bien por flojonazo!.
  6. No me gusta ver los deportes en la TV, aunque mi esposa me esta instruyendo en el soccer y el fútbol americano. Y no practico ningún deporte, soy un flojonazo!.
  7. Me gusta mucho la fotografía y pues trato de mejorar cada vez más, hay voy, hay voy..
  8. Casi toda la comida me gusta, pero la comida muy condimentada me cae mal al estomago (en especial la árabe).

Y pues eso es todo amigos!!! no pienso continuar la cadena por que no me gustan las cadenas y por que no tengo a quien pasársela :) , si encuentro alguna victima entonces se la haré llegar.

Categories: Personal