Thursday 28 February 2013

SQL Tune Query



1) To tune particular sql_id.



DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '&&1',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 4000,
                          task_name   => '&&1'||'_manual_tuning_task',
                          description => 'Manual Tuning on 18 march 10');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&&1'||'_manual_tuning_task');

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'METASEA';
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 1000
SELECT DBMS_SQLTUNE.report_tuning_task('&&1'||'_manual_tuning_task') AS recommendations FROM dual;
SET PAGESIZE 24
undef 1


2) One can use also for tuning Top SQL.

@?/rdbms/admin/sqltrpt.sql 
or
ORACLE_HOME/rdbms/admin/sqltrpt.sql

3) Top SQL finder.



set lin 1000

col "Command Type" for a20

select CTYP "Command Type", OBJ "Name", 0 - EXEM   "Number of Executions" , GETS "Buffer Gets" ,

ROWP "Rows Processed" from (select distinct EXEM, CTYP, OBJ, GETS, ROWP

from ( select decode (S.COMMAND_TYPE ,  2, 'Insert into ' ,  3,'Select from ',  6, 'Update  of  ' ,  7, 'Delete from ' ,

26,'Lock    of  ') CTYP , O.OWNER || '.' || O.NAME    OBJ , sum(0 - S.EXECUTIONS)
EXEM  , sum(S.BUFFER_GETS) GETS  , sum(S.ROWS_PROCESSED) ROWP from V$SQL  S ,
V$OBJECT_DEPENDENCY D , V$DB_OBJECT_CACHE   O
where S.COMMAND_TYPE in (2,3,6,7,26)
and D.FROM_ADDRESS = S.ADDRESS and D.TO_OWNER = O.OWNER
and D.TO_NAME= O.NAME   and O.TYPE = 'TABLE'
--and O.OWNER NOT IN ('SYS','SYSTEM')
and O.OWNER = upper('&&user_name')
group by S.COMMAND_TYPE , O.OWNER  , O.NAME )  )
where ROWNUM <= 25
;
set lin 80
  


1 comment: