viernes, 11 de diciembre de 2009

Reporte para analizar planes que referencien a una tabla dada

Cada tanto me consultan desde el area de desarrollo sobre si agregando tal o cual indice a una tabla mejoraria el rendimiento de la aplicación. Como en muchas ocasiones yo no conozco el negocio me resulta dificil saber como se usa la tabla, es decir, que sentencias la referencian, si se hacen updates, deletes, inserts o solo se consulta. Para poder analizar esto a veces uso el script que copio abajo, que da el plan de ejecución y la ultima vez que se ejecutaron las sentencias que referencian a una cierta tabla. A esto se le puede sumar la generación de sugerencias usando advisor tales como SQL Tuning y SQL Access Advisors.


set serverout on
set line 120
set pagesize 9999
set verify off
set feed off

ACCEPT tabla PROMPT "Ingrese Tabla a Analizar: "
PROMPT
PROMPT

begin
for i in (select sp.sql_id,max(sh.begin_interval_time) ufecha
from dba_hist_sql_plan sp,
dba_hist_sqlstat ss,
dba_hist_snapshot sh
where sp.sql_id = ss.sql_id
and ss.snap_id = sh.snap_id
and sp.object_name = upper('&tabla')
group by sp.sql_id)
loop
dbms_output.put_line('Ultima Ejecución: '||to_char(i.ufecha,'DD/MM/YYYY HH24:MI'));
for j in (select * from table(dbms_xplan.display_awr(i.sql_id)))
loop
dbms_output.put_line (j.plan_table_output);
end loop;
dbms_output.put_line(chr(10)||rpad('*',100,'*')||chr(10));
end loop;
end;
/

set verify on
set feed on

4 comentarios:

  1. Pablo te hago dos consultas con respecto a este artículo:

    1) ¿Desde donde se puede ver el resultado de este reporte? porque yo lo estoy ejecutando desde un TOAD y no me devuelve nada, luego creé como un procedimiento y solo me informa que el procedimiento se corrió satisfactoriamente.

    2) Tengo entendido que para usar el SQL Tunning se necesita tener una versión Enterprise, es así?

    Muchas gracias.

    ResponderEliminar
  2. Hola Facundo, desde sqlplus tendrias que tener seteado: set serverout on para poder ver la salida del dbms_output.put_line. Podes usar SQL tuning con version Standard tambien.

    ResponderEliminar
  3. buenas, estuve probando pero no se si lo estoy ejecutando mal o lo he copiado mal pero me da estos errores:

    ERROR at line 9:
    ORA-06550: line 9, column 5:
    PL/SQL: ORA-00933: SQL command not properly ended
    ORA-06550: line 2, column 14:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 10, column 88:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:
    loop
    ORA-06550: line 15, column 36:
    PLS-00103: Encountered the symbol "RPAD" when expecting one of the following:
    . ( ) , * % & | = - + < / > at in is mod remainder not range
    rem => .. <> or != or ~= >= <= <> and or
    like LIKE2_ LIKE4_ LIKEC_ as
    ORA-06550: line 15, column 53:
    PLS-00103: Encountered the symbol "CHR" when expecting one of the following:
    . ( ) , * % & | = - + < / > at in is mod remainder not range
    rem => .. <> or != or ~= >= <= <> and or
    like LIKE2_ LIKE4_ LIKEC_ as
    ORA-06550: line 17, column 1:
    PLS-00103: Encountered the symbol "END"

    Sabes por que podria estar pasando esto?..

    Saludos y gracias.

    ResponderEliminar
  4. Le faltaban los operadores de concatenación de strings (||). Ya lo corregí. Probalo y cualquier cosa comentame.

    ResponderEliminar