Scrip - Analiza_AWR

Script para generar estadísticas de sentencias (conociendo su sql_id o alguna parte del texto de la sql).

Genera los planes almacenados para la sentencia elegida y propuestas ADDM.

Tiene como parámetros el txt o sql_id de la consulta, usuario de parseo, snap_id inicial si se quiere indicar, snap_id final si se quiere indicar y fechas fec_ini y fec_fin de lanzamiento de la sentencia, además solicita el min_snap y max_snap para sentencias que han tenido varios planes a lo largo de distintos awr.

Cliente de lanzamiento : sqlplus.

set linesize 4500

define v_sql =""
column v_sql format a400 new_value V_sql
column fecha_Max_plan format a20
column min_snap format 999999
column max_snap format 999999
PROMPT
PROMPT =================================================================
PROMPT
PROMPT LISTA DE CONSULTAS Y SQL_IDS
PROMPT
PROMPT =================================================================
PROMPT
accept sqltxt char prompt 'Introduzca una cadena SQL a buscar o SQL_ID: '
PROMPT
accept snap_id_ini char DEFAULT 0 prompt 'Introduzca el SNAP_ID Inicial de localización de las sentencias: '
PROMPT
accept snap_id_fin char DEFAULT 999999999 prompt 'Introduzca el SNAP_ID Final de localización de las sentencias: '
PROMPT
accept fec_ini char DEFAULT sysdate-4000 prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT
accept fec_fin char DEFAULT sysdate prompt 'Introduzca la Fecha Inicial de localización de las sentencias (formato "sysdate - ..."): '
PROMPT

select plan_hash_value,to_date(MAX(cast(END_INTERVAL_TIME as DATE)),'dd/mm/yyyy hh24:mi:ss') Fecha_Max_plan,
min(q.snap_id) Min_Snap, max(q.snap_id) Max_Snap,
T.SQL_ID, SUM(EXECUTIONS_DELTA) EJECUCIONES, ROUND(SUM(ELAPSED_TIME_DELTA)/1000000,2) "Elapsed Time(s)", SUM(DISK_READS_DELTA) "Physical Reads",
ROUND(SUM(ELAPSED_TIME_DELTA)/(1000000*SUM(EXECUTIONS_DELTA)),2) "Elap per Exec(s)/EJEC",
ROUND(SUM(DISK_READS_DELTA)/SUM(EXECUTIONS_DELTA),2) "Reads per Exec",
--replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)) v_sql
        from
            dba_hist_sqlstat q,
            dba_hist_snapshot s,
            (select SQL_ID, SUBSTR(SQL_TEXT,1,4000) SQL_TEXT from dba_hist_sqltext where UPPER(sql_text) like '%'||UPPER('&sqltxt')||'%' or LOWER(sql_id)=LOWER('&sqltxt')) T
        where q.sql_id = T.SQL_ID
            and q.snap_id = s.snap_id
            and q.snap_id BETWEEN (case
                                                when &snap_id_ini=&snap_id_fin then &snap_id_ini
                                                else &snap_id_ini +1 end) AND &snap_id_fin
            and s.begin_interval_time between &fec_ini and &fec_fin
            and executions_delta > 0
GROUP BY PLAN_HASH_VALUE, T.SQL_ID,
--replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39))
TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000))
/

accept sqlid char prompt 'Introduzca el SQL_ID: '
accept min_snap char prompt 'Introduzca el Min_Snap: '
accept max_snap char prompt 'Introduzca el Max_Snap: '

PROMPT
PROMPT =================================================================
PROMPT
PROMPT CONSULTA ELEGIDA PARA ANALISIS
PROMPT
PROMPT =================================================================
PROMPT

SELECT replace(TO_CHAR(SUBSTR(T.SQL_TEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
FROM dba_hist_sqltext T WHERE SQL_ID='&sqlid'
/
   

PROMPT
PROMPT =================================================================
PROMPT
PROMPT PLANES ALMACENADOS EN AWR
PROMPT
PROMPT =================================================================
PROMPT

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));

   
exec dbms_sqltune.drop_tuning_task('sql_tuning_task');
DECLARE
  my_sqltext CLOB;
  task_name VARCHAR2(30);
BEGIN
  my_sqltext := '&v_sql';
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                                                                   begin_snap=> &min_snap,
                                                                   end_snap=> &max_snap,
                                                                   sql_id => '&sqlid',
                                   --bind_list => sql_binds(anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('noticia'),anydata.Convertvarchar2('10')),
                                   --user_name => '&usuario',
                                   scope => 'COMPREHENSIVE',
                                   time_limit => 60,
                                   task_name => 'sql_tuning_task');
END;
/
exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task');

PROMPT
PROMPT =================================================================
PROMPT
PROMPT PLAN DE EJECUCION ACTUAL Y PROPUESTAS
PROMPT
PROMPT =================================================================
PROMPT
COLUMN RESULTADO FORMAT A1000
select dbms_sqltune.report_tuning_task('sql_tuning_task')  AS RESULTADO from dual;