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;
