---
SQL Tuning Task using SQL_ID
---
first capture Highest SQL using following Query for last 60 minutes.
select *
from (select s.BUFFER_GETS, s.DISK_READS ,nvl(s.sql_id,'null') as sql_id , Nvl(S.sql_text, 'NULL') AS SQL_text,
round(COUNT(*) / 60, 2) DB_TIME,
ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER(), 2) AS PCT_LOAD
FROM V$active_Session_History A, v$SQL S
WHERE A.SQL_ID = S.SQL_ID
AND SAMPLE_TIME > SYSDATE - 60 / 24 / 60
AND SESSION_TYPE <> 'BACKGROUND'
GROUP BY s.sql_id, s.SQL_text,
s.BUFFER_GETS,s.DISK_READS
ORDER BY COUNT(*) DESC)
where rownum <= 1;
--- notedown sql_id from above
command. e.g 3hww0a8at1tnv
--- now execute following plsql block for sql tuning using sql_id and
place your sql_id in sql_id parameter
declare
stmt_task VARCHAR2(100);
begin
stmt_task :=
DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '3hww0a8at1tnv');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
---
notedown task_id from above plsql block.
e.g TASK_18790
---
task is created, now execute task using following.
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_18790'); end;
---
queries related to TASK.
select * from v$advisor_progress x ;
SELECT task_name, status FROM DBA_ADVISOR_LOG;
--
generate report using following
set long 100000
set longchecksize 10000
set pages 0
set lines 250
SELECT DBMS_SQLTUNE.
REPORT_TUNING_TASK('TASK_18789') AS recommendations FROM dual;
as always its better to follow ORACLE documentation for complete understandby refer following link.
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm
feeling trouble???????????? dont hasitate to contact me @ adnan.rafi@gmail.com
