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