My SQL Scrapbook for Performance Tuning

1).Historic Details about Execution Plan , Elapsed Time , Plan Hash Value and Number of Execution.

col smple_time for a23
col SMPLE_TIME for 999999999999999
select sn.end_interval_time smple_time,
       sql.SQL_ID,
       sql.instance_number    ins,
       sql.PLAN_HASH_VALUE      planhash,
       sql.OPTIMIZER_COST       cost,
       sql.FETCHES_DELTA        fchs,
       sql.EXECUTIONS_DELTA     exes,
       sql.ROWS_PROCESSED_DELTA/1000    Krwps,
       sql.ELAPSED_TIME_DELTA/1000000   elps
from DBA_HIST_SQLSTAT sql, dba_hist_snapshot sn
where sql.sql_id='&sql_iD'
and sql.snap_id= sn.snap_id
and sql.instance_number=sn.instance_number
order by 1;

2).Historic Execution from AWR

select * from table(dbms_xplan.display_awr('&SQL_ID',PLAN_HASH_VALUE));

3). Bind Variables Used in a Query.

col END_INTERVAL_TIME for 999999999

col NAME for a999999
col name for 99999999
select
sn.END_INTERVAL_TIME,
sb.NAME,
sb.VALUE_STRING
from
DBA_HIST_SQLBIND sb,
DBA_HIST_SNAPSHOT sn
where
sb.sql_id='&SQL_ID' and
sb.WAS_CAPTURED='YES' and
sn.snap_id=sb.snap_id
order by
sb.snap_id,
sb.NAME;

4).Check the Histogram.

select column_name from dba_tab_col_statistics where table_name='&TABLE_NAME' and  and owner='&OWNER' order by COLUMN_NAME ;

5).Check the Index Positioning.

select index_name,column_name,column_position from dba_ind_columns where index_name='&INDEX_NAME' and index_owner='&INDEX_OWNER'

6).To Tune the Query.

From the Session level (as sys).

Alter session set sql_trace=TRUE; => This will not give Binds and Waits Events.
Alter session set statistics_level=ALL;  OR
Use Gather_Plan_Statistics Hint in the query you want to tune. Something like below one..
select /*GATHER_PLAN_STATISTICS */

And get the explain plan using.

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALLSTATS LAST')); 

7). Run SQL Tuning Advisor 

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          begin_snap  => &BEG_SNAP,

                          end_snap    => &END_SNAP,

                          sql_id      => '&SQL_ID',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 10000,

                          task_name   => 'MYTUNINGTASK_&SQL_ID',

                          description => 'Tuning task for statement &SQL_ID in AWR.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&SQL_ID');

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('&SQL_ID') AS recommendations FROM dual;

SET PAGESIZE 24

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

spool sqltune_report.lst

SELECT DBMS_SQLTUNE.report_tuning_task('&SQL_ID') AS recommendations FROM dual;

SET PAGESIZE 24

spool off

8).Find AWR Snapshot  Interval and Change its Interval 

select

       extract( day from snap_interval) *24*60+

       extract( hour from snap_interval) *60+

       extract( minute from snap_interval ) "Snapshot Interval",

       extract( day from retention) *24*60+

       extract( hour from retention) *60+

       extract( minute from retention ) "Retention Interval"

from dba_hist_wr_control;

Output is in Minutes.

execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 1576800);

9).Find the Optimizer Statistics for an Index.

set serveroutput on

declare

      l_numrows   number;

     l_numlblks  number;

     l_numdist   number;

     l_avglblk   number;

     l_avgdblk   number;

     l_clstfct   number;

     l_indlevel  number;

   begin

    dbms_stats.get_index_stats(ownname ='&SCHEMA_NAME', indname ='&INDEX_NAME',

                    numrows => l_numrows, numlblks => l_numlblks,

                    numdist => l_numdist, avglblk => l_avglblk,

                    avgdblk => l_avgdblk, clstfct => l_clstfct,

                    indlevel => l_indlevel);

     dbms_output.put_line('No. of rows: ' || l_numrows);

     dbms_output.put_line('No. of blks: ' || l_numlblks);

     dbms_output.put_line('No. of distinct values: ' || l_numdist);

     dbms_output.put_line('Avg leaf blocks for distinct keys: ' || l_avglblk);

     dbms_output.put_line('Avg data blocks pointed to in the table: ' || l_avgdblk);

     dbms_output.put_line('Clustering factor: ' || l_clstfct);

     dbms_output.put_line('Index height: ' || l_indlevel);

   end;

   /

10). To find Longops Sessions

set pagesize 2000

set linesize 150

col message for a70

col opname for a30

col username for a10

select sid||','||serial# sid_Serial, start_time, opname, sofar, totalwork, round(sofar/totalwork*100) Done, round(time_remaining/60) Time_left, username, message

from gv$session_longops where totalwork != 0 and (sid,serial#) in (select sid,serial# from gv$session)

--and totalwork!=sofar

order by start_time;