Script - Analiza_SGA

Script para generar estadísticas de sentencias (conociendo su sql_id o alguna parte del texto de la sql), utilizando como variables bind las máximas utilizadas en el último snap_id almacenado.

Genera los planes almacenados para la sentencia elegida y posteriormente muestra el análisis actual (con la sustitución de las variables bind) 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:

Cliente de lanzamiento : sqlplus.


set linesize 4500

define v_sql =""
column v_sql format a4000 new_value V_sql
column fecha_Max_plan format a20
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 (en AWR y SGA): '
PROMPT
accept usuario char DEFAULT SYSTEM prompt 'Introduzca el usuario de Parseo de la sentencia: '
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, 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
        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))
union
select plan_hash_value, TO_DATE(to_date(LAST_LOAD_TIME,'YYYY-MM-DD/hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss') Fecha_Max_plan, T.SQL_ID, sum(executions) ejecucions, ROUND(SUM(ELAPSED_TIME)/1000000,2) "Elapsed Time(s)", SUM(DISK_READS) "Physical Reads",
    ROUND(SUM(ELAPSED_TIME)/(1000000*SUM(EXECUTIONS)),2) "Elap per Exec(s)/EJEC",
    ROUND(SUM(DISK_READS)/SUM(EXECUTIONS),2) "Reads per Exec",
    replace(TO_CHAR(SUBSTR(T.SQL_fullTEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql
from
    v$sql T
where
    (UPPER(sql_text) like '%'||UPPER('&sqltxt')||'%' or LOWER(sql_id)=LOWER('&sqltxt') )
    AND EXECUTIONS > 0
GROUP BY PLAN_HASH_VALUE, T.SQL_ID, TO_DATE(to_date(LAST_LOAD_TIME,'YYYY-MM-DD/hh24:mi:ss'),'dd/mm/yyyy hh24:mi:ss'),
replace(TO_CHAR(SUBSTR(T.SQL_FULLTEXT,1,4000)),chr(39),chr(39)||'||chr(39)||'||chr(39))    
/

accept sqlid char prompt 'Introduzca el SQL_ID: '

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'));

-- AHORA NOS QUEDAMOS CON LOS ÚLTIMOS VALORES INFORMADOS SEGÚN DBA_HIST_SQLBIND
var sqlidd varchar2(4000);
begin
:sqlidd:='&v_sql';
FOR rec in (select name, value_string from DBA_HIST_SQLBIND WHERE SQL_ID='&sqlid' and snap_id =(select max(snap_id) from DBA_HIST_SQLBIND WHERE SQL_ID='&sqlid'))
LOOP
    :sqlidd := substr(replace('&v_sql',rec.name,CHR(39)||rec.value_string||CHR(39)),1,4000);
END LOOP;
end;
/
column sqlid2 format a4000 new_value Sqlid
select replace(:sqlidd,chr(39),chr(39)||'||chr(39)||'||chr(39)) v_sql from dual;

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( sql_text => my_sqltext,
                                   --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(CON SUSTITUCIÓN DE VARIABLES BIND) Y PROPUESTAS
PROMPT
PROMPT =========================================================================
PROMPT
COLUMN RESULTADO FORMAT A1000
select dbms_sqltune.report_tuning_task('sql_tuning_task')  AS RESULTADO from dual;