Translate

Search This Blog

Simplification of use of Oracle 10g/11g sql tuning advisory without using sql tuning set

Procedure Advisory in this post gives SQL Tuning advisory report based on parameters user name,starting date and end date. This procedure used DBA_HIST_ views to extract top n sqls from time period between starting date and end date This procedure required user executing this procedure has been granted advisory privilege along with the select privileges on views DBA_HIST_SQLTEXT,DBA_HIST_SNAPSHOT,DBA_HIST_SQLSTAT  Further this procedure uses another procedure Create_Exec_Task defined later in this post

CREATE OR REPLACE PROCEDURE Advisory(p_user VARCHAR2,p_date1 DATE,p_date2 DATE, n NUMBER) AS
sqlt CLOB;
v_report CLOB;
CURSOR c1 IS
SELECT  DBA_HIST_SQLTEXT.sql_text sqlt
FROM
(
SELECT
sub.module, parsing_schema_name,
ROUND(sub.seconds_since_date/60,2) elapsed_time_delta_mins,
sub.execs_since_date,
sub.gets_since_date,
sub.snap_id,
ROUND(sub.seconds_since_date/DECODE(execs_since_date,0,1,
execs_since_date)/60,2) avg_exec_time,
sub.sql_id
FROM
( -- sub to sort before rownum
SELECT module, parsing_schema_name,
sql_id,
ROUND(SUM(elapsed_time_delta)/1000000) AS seconds_since_date,
SUM(executions_delta) AS execs_since_date,
SUM(buffer_gets_delta) AS gets_since_date,
snap_id
FROM
dba_hist_snapshot NATURAL JOIN dba_hist_sqlstat
WHERE
dba_hist_sqlstat.parsing_schema_name IN (p_user)
AND
begin_interval_time BETWEEN p_date1 AND p_date2-- '21-sep-2010' AND '30-sep-2010'
AND module NOT LIKE '%exp%' AND module NOT LIKE '%imp%'
AND module NOT LIKE '%TOAD%'
GROUP BY
module,sql_id,snap_id,parsing_schema_name
ORDER BY snap_id DESC
) sub
WHERE ROWNUM <=n
)T1 , DBA_HIST_SQLTEXT , dba_hist_snapshot
WHERE T1.sql_id=DBA_HIST_SQLTEXT.sql_id
AND T1.snap_id=dba_hist_snapshot.snap_id
--AND ( LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%delete%' --OR
-- LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%insert%' OR LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%delete%')
AND sql_text NOT LIKE '%DBMS_STATS%'
AND sql_text NOT LIKE  '%parallel(t,2)%'
AND sql_text NOT LIKE '%maxbkt%'
AND sql_text NOT LIKE '%substrb%'
ORDER BY elapsed_time_delta_mins ;--T1.snap_id
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO sqlt;
Create_Exec_Task(sqlt,v_report);
EXIT WHEN c1% NOTFOUND;
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_report);
CLOSE c1;
END;
/

CREATE OR REPLACE PROCEDURE Create_Exec_Task(v_sql IN CLOB,v_report OUT CLOB ) AS
v_task_id VARCHAR2(254);
v_task_name VARCHAR2(254) DEFAULT 'task_name' ;
BEGIN
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK(v_task_name);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
v_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => v_sql,
time_limit => 120,
SCOPE => 'COMPREHENSIVE',
task_name =>v_task_name ,
description => 'no description'
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task_name);
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_name') INTO v_report FROM dual;
DBMS_SQLTUNE.DROP_TUNING_TASK(v_task_name);
END;
/

SQL> set serveroutput on size 999999
SQL>  set long 999999
SQL> exec advisory(user,sysdate-1,sysdate,10)

spool c:\advisory.log
set long 999999
set serveroutput on size 999999
declare
v_Sql clob;
v_out clob;
begin
v_sql:=' select * from t1,t2 where t1.c1=t2.c1 and t1.c1<=500';
create_exec_task(v_sql,v_out);
dbms_output.put_line(v_out);
end;
/

spool off

Follow by Email