V$RMAN_STATUS lento

Hola a todos,

El segundo tema de hoy (impresionante 2 en un día después de 6 meses) fue que la gente de BACKUP me comentó que fallaron unos backups de archive logs, y cuando me envían el fichero de log del error, era un timeout, así que ni idea.
Así que bien, como no tengo idea de DATA PROTECTOR, ni de lo que hace por dentro, le pedí a uno de los que administran DATA PROTECTOR que me explicaran un poco la arquitectura y lo que estaba haciendo. Al final deducimos que se quedaba en la ejecución de RMAN.
Después el impresionante seminario con Tanel Poder (Oracle Advance Troubleshooting), del cual comentaré otro día, me dí cuenta que esto le venía bien a su método. Tanel se basa en la sesiones, porque al final ella te va a reportar el error y efectivamente así pasó. Pero fue más "sencillo" de lo normal.
Le pedí a los de BAKCUP que lanzaran de nuevo el BACKUP, después de un rato salió el problema, 2 sesiones estaban consumiendo al 100% una core cada una, con el comando "top" (HP-UX) verifiqué, que el "pid", y lancé la siguiente consulta para ver que estaba ejecutando:

set lines 125 pages 50000 long 200000000
select s.username, s.program, a.sql_id,
p.spid, s.sid, s.status,
s.event, a.sql_fulltext
from v$session s, v$sqlstats a, v$process p
where s.sql_id= a.sql_id
and s.paddr = p.addr
and s.sql_hash_value <> 0
order by 5
/

En mi caso la base de datos desocupada en ese momento, y estaba bastante libre, por tanto no tenía muchas salidas y pude ver fácilmente el pid del server proces que es el spid.v$process. Y la magnifica consulta que tardaba mil años y que consumía muchísima CPU era la siguiente:

select round(sum(MBYTES_PROCESSED)) ,
round(sum(INPUT_BYTES)) ,
round(sum(OUTPUT_BYTES))
from V$RMAN_STATUS
start with (RECID=:b1 and STAMP=:b2)
connect by prior RECID=parent_recid

Obtuve el sql_id, por tanto también tenía el plan de ejecución (dbms_xplan) para comprobar que estaba teniendo problemas, y la verdad es que estaba un poco mal. Entonces hice una consulta sencilla para empezar:

select count(8)
from v$rman_status;

Y efectivamente me tocó “cortarla” (Ctrl+C). Después intenté ver lo que pasaba en otra base de datos, y me dió que era inmediata la consulta, fui a otra base de datos y lo mismo, era inmediata. Así bien tenía problemas en esa consulta y solo en esa base de datos. Dos magnificas vistas que consulto para ver que hace Oracle por dentro son:

v$fixed_view_definition
v$fixed_table

En la primera en el campo view_definition te describe las vistas V$. Pues bien al final del todo eran solo tres tablas X$:

X$KSFQP
X$KCCRSR
X$KRBMRST

Empecé con una prueba sencillita:

select /*+ RULE */ count(8)
from v$rman_status;

Y “voila” tardó milésimas de segundo en ejecutarme la consulta, igual que en la otras base de datos. Hablé con el personal de BACKUP y recomendé que dentro del bloque de comandos agregaran lo siguiente:

RUN
{
SQL “ALTER SESION SET OPTIMIZER_MODE=RULE”
……
}

Pues no se pudo, al final no, preferí hacer esto porque no quise entrar en obtener estadísticas de estas tablas, ya que hasta ese momento no sabía a que otras cosas afectaba el tocar estas tablas. Al final consulté todas las vistas (v$fixed_view_defintion) V$ que utilizan estas X$ y era SOLO esa, así que bien, ¿Cómo se hace para no usar el CBO sino RBO? Si las tablas no tienen estadísticas no le queda otra que usar RBO, así que decidí borrar las estadísticas de esas tablas con lo siguiente:

exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname => 'X$KSFQP' );
exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname => 'X$KCCRSR' );
exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname => 'X$KRBMRST');

Y todavía no servía, sin que sirva de precedente, estas alguna de estas tablas hace referencia al control file, pero recrearlo era mi última opción. Siguiente:

exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'X$KSFQP' );
exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'X$KCCRSR' );
exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'X$KRBMRST');

Y efectivamente mi consulta sin el HINT tardó milésimas de segundo, BIEN!!!. Esto es una forma rápida, para solucionar el problema, realmente, lo que debí hacer es verificar donde se estaba quedando la consulta verificar que tienen estas X$ y lanzar las estadísticas de una forma conciente y correcta, ¿Por qué? Hay un buen documento de Jonathan Lewis que explica bastante bien la cantidad de estadísticas necesarias. Además de este documento, debemos saber que el valor del parámetro de DBMS_STAT en el METHOD_OPT por defecto es “FOR ALL COLUMNS SIZE AUTO” y esto es “MONSTRUOSO” y sino que se lo pregunten a mi compi y amigo Ricardo, y él con documentación Oracle de metalink se los explica bien, o yo lo haré pero en otro post.

El caso es que en ese momento, decidí tirar por la vía rápida (mil disculpas Ricardo) pero funcionó, recolectó estadísticas de esas tablas y la consulta iba bien, por tanto el RMAN función y pudo realizarse el BACKUP de los ARCHIVE LOGs correctamente.

Al final las V$ que la mayoría son las GV$ sin la columna INST_ID, son consultas, así bien, algunos de los problemas que puedas tener viene provocados por la consulta a tablas X$, que estas pueden ser tablas ó estructuras en memoria. Por favor, NO RECOLETAR estadísticas por LEY, NO!!!, hay que pensar que pasa en la consulta, para este caso en concreto decidí hacerlo así prque me iba a quedar sin espacio en el filesystem donde están almacenados los ARCHIVE LOGs, pero si tiene que ver con V$ revisar mil veces antes, estas NO se pueden cambiar, por tanto hice lo rápido.

Más tormentoso, pero que también podría funcionar es lanzar:

exec dbms_stats.gather_dictionary_stats();

Y así recopilar las estadísticas de todo el diccionario, pero esto es muy peligroso, al igual que para nuestro caso pude también ejecutar:

exec dbms_stats.delete_dictionary_stats();

Que al final me iba a borrar estadísticas y mi consulta iba a hacer lo mismo que con el HINT RULE.

Pero como siempre intento decirle a todos DEPENDE, en este caso al mejor solución si deseas estabilidad es centrar todo en esa consulta, así bien son solo el “ALTER SESSION” hubiese estado genial, pero como no se pudo pues tocaron más cosas, pero en serio, si desean estabilidad y no desea errores inesperados, no lancen estadísticas sin saber que se hace por dentro.

Ah, puedes buscar en METALINK y aparece un par de entradas, pero le aseguro que después de solucionarlo, empecé a buscar en METALINK y tardé más en buscar que en ejecutar mis dos o tres consultas, además esto es más divertido que estar solucionando cosas con solo buscar y hacer lo que dice METALINK, por lo menos se aprende un poco más.

DBA_FREE_SPACE miente a veces

Buenas de nuevo, con un nuevo test sobre una funcionalidad de la Oracle 10g:

LA PAPELERA

Si deseas usar el FLASHBACK TABLE, debes tener habilitada la papelera para que en caso tal que borres la tabla puedas recuperar si la borraste. Pues bien, el caso es que, hace un rato me dicen que un índice no se puede crear porque no se puede extender un segmento temporal. El típico ORA-01652 ó ORA-01653 ó ora-01654.
Lo primero que piensas es en que se quedó sin espacio, y no, tenía 600M libres (según la DBA_FREE_SPACE), lo siguiente que te planteas, es que en el momento de crear un índice se crea un segmento temporal para realizar las ordenaciones necesarias para la creación del índice, y sea esta ejecutando una consulta gigante, y no eso no es lo que está pasando.
La tabla y el índice eran creados en el momento, y lo sorprendente es que solo eran 90.000 filas y la tabla tenía 12 campos, la mayoría VARCHAR2(10) y NUMBER. Por tanto, debería tener espacio suficiente para crear la tabla y el índice, de sobre, porque realmente eran unos 12M de tabla y unos 2M de índices. Entonces ¿Donde tenía el espacio ocupado que no estaba usando? Pues el error está en mirar ó confiar en la DBA_FREE_SPACE. La verdad es que se decidió no usar la papelera por tanto el parámetro RECYCLEBIN se colocó en OFF, pero en ese momento la persona encargada de la migración no vació la papelera antes ó después de desactivarla, y pasa que cuando está en OFF ó en ON la vista DBA_FREE_SPACE cuenta como libre ese espacio de segmentos borrados no “purgados”, pero Oracle no lo libera si lo necesita si está en OFF el parámetro. Así bien, cuando está en ON el mismo elimina el segmento de la papelera pero si está en OFF, no lo hace. Entonces compruebas que tienes segmentos en la papelera y ves que tienes, entonces primero antes de ampliar en borrar.
Es una tontería, porque piensas en segmentos temporales, tamaños, espacio libre y te haces mil preguntas, pero al final se puede probar, que con un INSERT en una tabla, que te dice que no puede extenderse un ORA-01653 y es que realmente no libera el espacio que te reporta como libre. Pero la verdad es que es bastante curioso (por lo menos para mi) ver como por un lado Oracle te dice que tienes espacio “libre”, pero la consulta no revisa que ese segmento no puede ser automáticamente borrado, y que la única forma de liberar este espacio, eres tu manualmente con el comando PURGE ó teniendo el parámetro RECYCLEBIN en ON. Que con puedes estar desperdiciando espacio.
Esto pasa en ocasiones cuando realizas migraciones a la versión 10g, y el cliente tiempo después, decide desactivar la papelera, y algunos desactivan la papelera en el acto, pero no se toman la molestia de limpiar la papelera.

Te recomiendo que eches un vistazo a tu papelera por si estas usan espacio que al final Oracle nunca va poder reutilizar:

select count(8)
from dba_recyclebin;
show parameter recyclebin

Con estas dos pequeñas consultas revisas esto. Es algo tonto este post, pero al final resolver los problemas es lo fácil, lo dificil es saber y comprender lo que está haciendo Oracle, porque en ocasiones miente o no te dice toda la verdad.

Si alguien quiere las pruebas del delito que me escriba un correo y se lo envío con mucho gusto, La verdad no se como subir un fichero en este blog , jejejeje :).