Tune by SQLID

A significant proportion of this page is based on this excellent article by Kerry Osbourne...

http://kerryosborne.oracle-guy.com/2008/09/23/sql-tuning-advisor/

Explain Plan

If you are in a graphical environment, try this...

SPOOL temp.html

select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR

(sql_id =>'&SQLID',

report_level =>'all',

type =>'ACTIVE') report

from dual;

SPOOL OFF

Introduced in 12.1

Open the file in a browser.

If you are not in a graphical environmnent use...

  • Run @/u01/dba/tune/getchildno.sql to identify the Child number for your SQLID

  • Run @/u01/dba/tune/dplan.sql to generate the Execution Plan report

Tuning

  • Run @/u01/dba/tune/awr_plan_change.sql to identify any Plan changes within a specified time period.

Profiles...

  • Run @/u01/dba/tune/accept_sql_profile.sql to accept a Profile recommended by the Tuning Advisor (note the Profile name in case you need to disable).

  • Run @/u01/dba/tune/disable_sql_profile.sql to disable a previously accepted Profile

Tuning

Run @?/rdbms/admin/awrrpt.sql to generate an AWR report
Run orasqlidhistory.sql to confirm that SQL with your SQLID has run within a specified time period.
Run oraawrsnaps.sql to identify available snapshots (if you don't already have them from the AWR Report).
Run orasqltunesqlid.sql to get tuning recommendations. You will be prompted for an arbitrary task name (which you should note as you will need it later if you want to accept any recommendations), your SQLID, a begin Snap, an end Snap and a Timeout in seconds (600 is a good starting point).

Scripts

awr_plan_change.sql

This script helps identify when the Execution Plan changes for an entered SQLID...

@/u01/dba/tune/awr_plan_change.sql

http://kerryosborne.oracle-guy.com/2008/09/23/sql-tuning-advisor/

You will be prompted for:

  • SQLID

The script returns, for each execution stored in the historical stats tables:

  • SNAP_ID

  • BEGIN_INTERVAL_TIME

  • PLAN_HASH_VALUE

  • Executions

  • ETIME (Average Execution Time)

  • LIO (Average Logical I/O)

set lines 155

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

SELECT ss.snap_id,

ss.instance_number node,

begin_interval_time,

sql_id,

plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio

from DBA_HIST_SQLSTAT S,

DBA_HIST_SNAPSHOT SS

where sql_id = NVL('&sql_id','4dqs2k5tynk61')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

order by 1, 2, 3

/

disable_sql_profile.sql

@/u01/dba/tune/disable_sql_profile.sql

http://kerryosborne.oracle-guy.com/2008/09/23/sql-tuning-advisor/

You will be prompted for:

  • Profile name (of the Profile to be disabled)

set verify off

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name => '&profile_name', attribute_name => 'STATUS', value => 'DISABLED');

accept_sql_profile.sql

@/u01/dba/tune/accept_sql_profile.sql

http://kerryosborne.oracle-guy.com/2008/09/23/sql-tuning-advisor/

You will be prompted for:

  • Your arbitrary Task Name (as specified when the Tuning Task was created above)

  • Category (generally DEFAULT, but see note below)

This is the category name which must match the value of the SQLTUNE_CATEGORY parameter in a session for the session to use this SQL Profile. It defaults to the value "DEFAULT". This is also the default of the SQLTUNE_CATEGORY parameter. The category must be a valid Oracle identifier. The category name specified is always converted to upper case. The combination of the normalized SQL text and category name creates a unique key for a SQL Profile. An ACCEPT_SQL_PROFILE will fail if this combination is duplicated. (https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#i1010403)

set verify off

exec dbms_sqltune.accept_sql_profile(task_name => '&task_name', category => '&category');

If you want to specify the Profile name rather than allowing the name to be auto-generated, then use this instead...

set verify off

exec dbms_sqltune.accept_sql_profile(task_name => '&task_name', name => '%profile_name', category => '&category');

find_sql.sql

@/u01/dba/tune/find_sql.sql

http://kerryosborne.oracle-guy.com/2008/09/23/sql-tuning-advisor/

You will be prompted for:

  • The start of the SQL_TEXT for the target statement (optional)

  • SQLID (optional)

set verify off

set pagesize 999

col username format a13

col prog format a22

col sql_text format a41

col sid format 999

col child_number format 99999 heading CHILD

col ocategory format a10

col avg_etime format 9,999,999.99

col etime format 9,999,999.99


select sql_id,

child_number,

plan_hash_value plan_hash,

executions execs,

elapsed_time/1000000 etime,

(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,

u.username,

sql_text

from v$sql s, dba_users u

where UPPER(sql_text) LIKE UPPER(NVL('&sql_text',sql_text))

and sql_text NOT LIKE '%from v$sql where sql_text like nvl(%'

and sql_id like nvl('&sql_id',sql_id)

and u.user_id = s.parsing_user_id

/

find_sql_stats.sql

@/u01/dba/tune/find_sql_stats.sql

http://kerryosborne.oracle-guy.com/2008/09/23/sql-tuning-advisor/

You will be prompted for:

  • SQL_TEXT (optional)

  • Address (optional)

  • SQLID (optional)

set verify off

set pagesize 999

col username format a13

col prog format a22

col sql_text format a41

col sid format 999

col child_number format 99999 heading CHILD

col ocategory format a10

col execs format 9,999,999

col execs_per_sec format 999,999.99

col etime format 9,999,999.99

col avg_etime format 9,999,999.99

col cpu format 9,999,999

col avg_cpu format 9,999,999.99

col pio format 9,999,999

col avg_pio format 9,999,999.99

col lio format 9,999,999

col avg_lio format 999,999,999


select sql_id,

child_number,

executions execs,

executions/((sysdate-to_date(first_load_time,'YYYY-MM-DD/HH24:MI:SS'))*(24*60*60)) execs_per_sec,

-- elapsed_time/1000000 etime,

(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,

-- cpu_time/1000000 cpu,

(cpu_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_cpu,

-- disk_reads pio,

disk_reads/decode(nvl(executions,0),0,1,executions) avg_pio,

-- buffer_gets lio,

buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,

sql_text

from v$sql s

where sql_text like nvl('&sql_text',sql_text)

and sql_text not like '%from v$sql where sql_text like nvl(%'

and address like nvl('&address',address)

and sql_id like nvl('&sql_id',sql_id)

order by sql_id, child_number

/

getchildno.sql

@/u01/dba/tune/getchildno.sql

You will be prompted for:

  • SQLID

select sql_id, child_number, sql_text

from v$sql

where sql_id = '&SQLID'

SET verify off

SET pages 9999

SET lines 150

SELECT *

FROM TABLE(dbms_xplan.display_cursor(SQL_ID=>'&sql_id',

CHILD_NUMBER=>'&child_no',

FORMAT=>'ALL +OUTLINE'))

/

For 11.2.0.3 I needed to use this alternate query...

SET verify off

SET pages 9999

SET lines 150

SELECT *

FROM TABLE(dbms_xplan.display_cursor(SQL_ID=>'&sql_id',

CURSOR_CHILD_NO=>&child_no,

FORMAT=>'ALL +OUTLINE'))

/