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;
lunes, 14 de mayo de 2012
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;
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;
Suscribirse a:
Entradas (Atom)