Performance Tuning Scripts

Listed below are some SQL queries which I find particularly useful for performance tuning. These are based on the Active Session History V$ View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past.
I would like to add that these queries have been customised by me based on SQL scripts obtained from colleagues and peers. So if I am infringing any copyright material let me know and I shall remove the same. Also If anyone has any similar useful scripts to contribute for use by the community do send it to me and I shall include the same on this page

Top Recent Wait Events

col EVENT format a60 

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/

Top Wait Events Since Instance Startup

col event format a60

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
 where wait_class !='Idle'
 group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

List Of Users Currently Waiting

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

Find The Main Database Wait Events In A Particular Time Interval

First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum

Top CPU Consuming SQL During A Certain Time Period

Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
active_session_history.event,
 sum(active_session_history.wait_time +
  active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;

Top 5 SQL statements in the past one hour

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum

SQL with the highest I/O in the past one day

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum

Top CPU consuming queries since past one day

select * from (
select 
 SQL_ID, 
 sum(CPU_TIME_DELTA), 
 sum(DISK_READS_DELTA),
 count(*)
from 
 DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
 s.snap_id = a.snap_id
 and s.begin_interval_time > sysdate -1
 group by 
 SQL_ID
order by 
 sum(CPU_TIME_DELTA) desc)
where rownum

Find what the top SQL was at a particular reported time of day

First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

select * from
 (
select
 sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
 from
dba_hist_sqlstat sql,
dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
and
 s.snap_id= &snapid
 order by
 c3 desc)
 where rownum < 6 
/

Analyse a particular SQL ID and see the trends for the past day

select
 s.snap_id,
 to_char(s.begin_interval_time,'HH24:MI') c1,
 sql.executions_delta c2,
 sql.buffer_gets_delta c3,
 sql.disk_reads_delta c4,
 sql.iowait_delta c5,
sql.cpu_time_delta c6,
 sql.elapsed_time_delta c7
 from
 dba_hist_sqlstat sql,
 dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
 and s.begin_interval_time > sysdate -1
 and
sql.sql_id='&sqlid'
 order by c7
 /

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

select 
  SQL_ID 
, PLAN_HASH_VALUE 
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT 
where SQL_ID in (
'&sqlid') 
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Top 5 Queries for past week based on ADDM recommendations

/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

TOP 10 FULL TABLE SCAN SQLs with BUFFER_GETS

select * from
(select t.* from v$sqlarea t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS' and
p.options='FULL'
and p.object_owner not in ('SYS','SYSTEM'));

select count(username),inst_id from v$session group by inst_id;
select decode(request,0,'Holder: ','Waiter :')||sid sess,
id1,id2,lmode,request,type,inst_id,ctime/60 MIN
from gv$lock
where (id1,id2,type ) in
(select id1,id2,type from gv$lock where request >0)
order by id1,id2;
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2

---------locked_objects-----

SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
b.osuser, b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.SID = a.session_id AND a.object_id = c.object_id;

select event,count(*) from v$session_wait group by event order by 2 desc;
select sid,event,p1,p2 , p3 from v$session_wait where event not like '%message%'
and state like 'WAIT%'
order by event;

select a.sid,b.sql_id from v$session_wait a,v$session b where a.event like '%read by other session%'
and a.state like 'WAIT%' and a.sid=b.sid
select 'kill -9 ' || spid from v$session a,v$process b where a.paddr=b.addr and sid in (select sid from v$session_wait where event like '%read by other session%'
and state like 'WAIT%')

select * from dba_jobs_running

select * from gv$fast_start_transactions

SELECT owner, job_name, enabled FROM dba_scheduler_jobs;

select sid,serial#,sql_id,username,status,server,osuser,program,logon_time,event#,event from v$session where osuser='cmah5277';

select event,p1,p2 from v$session_Wait where sid=1600;

select sid,time_remaining,message from v$session_longops where sid =1600;

select sid,time_remaining,message from v$session_longops where time_remaining > 0

select a.sid,a.serial#,b.spid,a.status,a.sql_id,a.program,a.prev_sql_id from v$session a,v$process b where a.paddr=b.addr and sid=1631;

select * from dwh_error_log1 order by sys_date desc

select * from pol_gen order by pol_date desc

select sql_text from v$sqltext where sql_id='a9d7cf6qcfdhr' order by piece;

select APPLIED,SEQUENCE# from v$archived_log where THREAD#=1 order by SEQUENCE#;

select APPLIED,SEQUENCE# from v$archived_log where THREAD#=2 order by SEQUENCE#;

select APPLIED,SEQUENCE# from v$archived_log where APPLIED='YES' order by 2;

select process,status from v$managed_standby;

select operation,options,object_name,cost from v$sql_plan where sql_id='71qk9tvzs329n'

select sid,time_remaining,message from gv$session_longops where sid =2670 order by start_time desc;

select * from gv$session where program like 'sqlplus%'

select .SESSION_ID,a.OS_USER_NAME,a.ORACLE_USERNAME,a.PROCESS,a.LOCKED_MODE,b.object_name
from v$locked_object a,dba_objects b where a.object_id=b.object_id

---ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE

What user is waiting the most?
select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3

What SQL is currently using the most resources?

select active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
order by 4

space check

select a.tablespace_name , A.Allocated , B.Freespace , round(b.freespace/a.allocated*100) "% Free"
from
(select tablespace_name ,sum(bytes)/1024/1024 Allocated from dba_data_files group by tablespace_name) A ,
(select tablespace_name,sum(bytes)/1024/1024 Freespace
from dba_free_space group by tablespace_name) B
where a.tablespace_name=b.tablespace_name;

check temp tablespace usage
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

-------ALTER SYSTEM ARCHIVE LOG CURRENT;

-----------High Archive generation----------------------------

select trunc(first_time) on_date,
       thread# thread,
       min(sequence#) min_sequence,
       max(sequence#) max_sequence,
       max(sequence#) - min(sequence#) nos_archives,
       (max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from   v$log_history,
       (select avg(bytes/1024/1024) log_avg_mb
        from   v$log)
group by trunc(first_time), thread#, log_avg_mb
order by trunc(first_time) asc
--------------------Temp space usage----------------------
SELECT s.sid "SID",s.username "User",s.program "Program", u.tablespace "Tablespace",
u.contents "Contents", u.extents "Extents", u.blocks*8/1024 "Used Space in MB", q.sql_text "SQL TEXT",
a.object "Object", k.bytes/1024/1024 "Temp File Size"
FROM gv$session s, gv$sort_usage u, gv$access a, dba_temp_files k, gv$sql q
WHERE s.saddr=u.session_addr
and s.sql_address=q.address
and s.sid=a.sid
and u.tablespace=k.tablespace_name;

select * from table (DBMS_XPLAN.DISPLAY_cursor('4mpv1fvn69tfg'))

SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'cache buffer chains';

SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'latch:library cache';

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_PS_Z_EMPMUSTER.sql

select dbms_metadata.get_ddl('TABLE','Policy_motor_details','IPARTNER') from dual;

spool off;

select OWNER,LOG_DATE,JOB_NAME,STATUS,ADDITIONAL_INFO AS ERROR from dba_scheduler_job_run_details@test where owner='IPARTNER';

select OWNER,LOG_DATE,JOB_NAME,STATUS,ADDITIONAL_INFO AS ERROR from dba_scheduler_job_run_details@ipartner where owner='IPARTNER' and trunc( LOG_DATE) between '23-MAR-2011' and '31-MAR-2011'
Pathfinder - badh3n2s

select     oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
, decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
, session_id

----------------------Sessions with high physical reads-------------------

select
OSUSER os_user,username,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
and username is not null
and status='ACTIVE'
order by PHYSICAL_READS;

execute dbm_transaction.purge_lost_db_entry('8.17.78501');

To troubleshoot the wait event “enq: TX – row lock contention”, use the following SQL:
For which SQL is currently waiting on:

select sid,    sql_text from    v$session s,    v$sql q where    sid in    (select       sid    from
       v$session   where       state in ('WAITING')   and       wait_class != 'Idle'   and
       event='enq: TX - row lock contention'   and       (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));
The blocking session is: select    blocking_session,    sid,    serial#,
    wait_class,    seconds_in_wait from    v$session where    blocking_session is not NULLorder by
    blocking_session;

Hidden parameters  select a.ksppinm name,b.ksppstvl value,b.ksppstdf deflt,decode(a.ksppity,1,'boolean', 2,'string', 3,'number', 4,'file', a.ksppity) type,a.ksppdesc description from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm like '\_%'escape'\' order by name
row lock

SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

export backup status

select SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,TIME_REMAINING from v$session_longops where opname like '%EXP%'

select 'alter system kill session '||''''||s.sid||','||s.serial#||''''||' immediate ;', username from    v$session s where sid in (select sid from v$session_wait where event not like '%message%' and state like 'WAIT%' and EVENT like '%file scattered read%');

To Find out estimated SGA/PGA target size

V_$SGA_TARGET_ADVICE
V_$PGA_TARGET_ADVICE

select /*+ rule */
a.owner,
a.segment_name,
a.segment_type,
round(a.bytes/1024/1024,0) MBS,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WAISTED
from dba_segments a, dba_tables b
where a.owner=b.owner
and a.owner not like 'SYS%'
and a.segment_name = b.table_name
and a.segment_type='TABLE'
group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)
having round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc

Jayesh Pande

Search This Blog