Pages

Thursday, June 13, 2013

DBMS_SQLTUNE | SQL Tuning Task using SQL_ID



--- 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


No comments:

Post a Comment