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
Posts
