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.