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.
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
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 < 6;
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 < 6;
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 Segments ordered by Physical Reads
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads 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 < 6;
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 < 6;
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 < 6;
Find what the top SQL was at a particular reported time of day
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
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;
Find locks Between 5 and 6 PM on 3/10/15
REM Find locks Between 5 and 6 PM on 3/10/15
set linesize 200
set pagesize 200
col sql_text format a40
col module format a20
SELECT
distinct a.sql_id,
a.inst_id,
a.blocking_session as BLK_sess,
a.blocking_session_serial# as BLK_SESS_Serial#,
a.user_id,
s.sql_text,
a.module
FROM
GV$ACTIVE_SESSION_HISTORY a,
gv$sql s
where
a.sql_id=s.sql_id
and
blocking_session is not null
and
a.user_id <> 0
and
a.sample_time between
to_timestamp('2015-03-10 19:00:00','YYYY-MM-DD HH24:MI:SS') and
to_timestamp('2015-03-10 18:00:00','YYYY-MM-DD HH24:MI:SS');
Find Invalid Objects
COLUMN object_name FORMAT A30
set linesize 200
set pagesize 200
col object_name format a30
select
owner,
object_name,
object_type, status
from
dba_objects
where
owner not in
('SYS','SYSTEM','DBSNMP','OUTLN','OLAPSYS','MDSYS','ORDPLUGINS','XDB','DMSYS')
and
status='INVALID'
and
not object_name like 'BIN$%';
Find Largest Tables in Database (row wise)
WITH BigTbl AS (
SELECT DISTINCT num_rows, table_name, owner
FROM ALL_TABLES
WHERE num_rows >= 5
ORDER BY num_rows DESC)
SELECT ROWNUM RANK, num_rows, table_name, owner
FROM BigTbl
WHERE ROWNUM <= 10;
Find TOP 10 FULL TABLE SCAN SQLs
select * from
(select b.username,
t.sql_id,
sql_fulltext,
p.operation,
p.options
from
v$sqlarea t,
v$sql_plan p,
v$session b
where
t.hash_value=p.hash_value
and
t.sql_id=b.sql_id
and
p.operation='TABLE ACCESS'
and
p.options='FULL'
and
p.object_owner not in ('SYS','SYSTEM')
order by
DISK_READS DESC,
EXECUTIONS DESC)
where rownum <=10;
Shows Top “Running SQLs”
select distinct spid,
s.sid,
s.serial#,
to_char(sysdate - last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",
logon_time,
osuser,
s.program,
schemaname,
sql_text
from v$session s,
v$process p,
v$sql t
where s.paddr=p.addr
and t.hash_value(+)=s.sql_hash_value
and s.type !='BACKGROUND';
Temporary tablespace usage
SELECT
s.sid,
s.username,
u.tablespace,
s.sql_hash_value||'/'||u.sqlhash hash_value,
u.segtype,
u.contents,
u.blocks
FROM
v$session s,
v$tempseg_usage u
WHERE
s.saddr=u.session_addr
order by
u.blocks;
REM DATAFILE I/O NOTES:
REM File Name - Datafile name
REM Physical Reads - Number of physical reads
REM Reads % - Percentage of physical reads
REM Physical Writes - Number of physical writes
REM Writes % - Percentage of physical writes
REM Total Block I/O's - Number of I/O blocks
REM Use this report to identify any "hot spots" or I/O contention
set linesize 200
set pagesize 200
col name format a46
set linesize 200
set pagesize 200
col name format a46
select NAME,
PHYRDS "Physical Reads",
round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
PHYWRTS "Physical Writes",
round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
select sum(PHYRDS) PHYS_READS,
sum(PHYWRTS) PHYS_WRTS
from v$filestat
) pd,
v$datafile df,
v$filestat fs
where
df.FILE# = fs.FILE#
order by
fs.PHYBLKRD+fs.PHYBLKWRT desc;
I hope this
article helped you. Your suggestions/feedbacks
are most welcome.
Keep learning... Have a great day!!!
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
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 < 6;
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 < 6;
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 Segments ordered by Physical Reads
col segment_name format a20
col owner format a10
select segment_name,object_type,total_physical_reads
from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
where statistic_name in ('physical reads')
order by total_physical_reads 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 < 6;
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 < 6;
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 < 6;
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;
Find locks Between 5 and 6 PM on 3/10/15
REM Find locks Between 5 and 6 PM on 3/10/15
set linesize 200
set pagesize 200
col sql_text format a40
col module format a20
SELECT
distinct a.sql_id,
a.inst_id,
a.blocking_session as BLK_sess,
a.blocking_session_serial# as BLK_SESS_Serial#,
a.user_id,
s.sql_text,
a.module
FROM
GV$ACTIVE_SESSION_HISTORY a,
gv$sql s
where
a.sql_id=s.sql_id
and
blocking_session is not null
and
a.user_id <> 0
and
a.sample_time between
to_timestamp('2015-03-10 19:00:00','YYYY-MM-DD HH24:MI:SS') and
to_timestamp('2015-03-10 18:00:00','YYYY-MM-DD HH24:MI:SS');
Find Invalid Objects
COLUMN object_name FORMAT A30
set linesize 200
set pagesize 200
col object_name format a30
select
owner,
object_name,
object_type, status
from
dba_objects
where
owner not in
('SYS','SYSTEM','DBSNMP','OUTLN','OLAPSYS','MDSYS','ORDPLUGINS','XDB','DMSYS')
and
status='INVALID'
and
not object_name like 'BIN$%';
Find Largest Tables in Database (row wise)
WITH BigTbl AS (
SELECT DISTINCT num_rows, table_name, owner
FROM ALL_TABLES
WHERE num_rows >= 5
ORDER BY num_rows DESC)
SELECT ROWNUM RANK, num_rows, table_name, owner
FROM BigTbl
WHERE ROWNUM <= 10;
Find TOP 10 FULL TABLE SCAN SQLs
select * from
(select b.username,
t.sql_id,
sql_fulltext,
p.operation,
p.options
from
v$sqlarea t,
v$sql_plan p,
v$session b
where
t.hash_value=p.hash_value
and
t.sql_id=b.sql_id
and
p.operation='TABLE ACCESS'
and
p.options='FULL'
and
p.object_owner not in ('SYS','SYSTEM')
order by
DISK_READS DESC,
EXECUTIONS DESC)
where rownum <=10;
Shows Top “Running SQLs”
select distinct spid,
s.sid,
s.serial#,
to_char(sysdate - last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",
logon_time,
osuser,
s.program,
schemaname,
sql_text
from v$session s,
v$process p,
v$sql t
where s.paddr=p.addr
and t.hash_value(+)=s.sql_hash_value
and s.type !='BACKGROUND';
Temporary tablespace usage
SELECT
s.sid,
s.username,
u.tablespace,
s.sql_hash_value||'/'||u.sqlhash hash_value,
u.segtype,
u.contents,
u.blocks
FROM
v$session s,
v$tempseg_usage u
WHERE
s.saddr=u.session_addr
order by
u.blocks;
REM DATAFILE I/O NOTES:
REM File Name - Datafile name
REM Physical Reads - Number of physical reads
REM Reads % - Percentage of physical reads
REM Physical Writes - Number of physical writes
REM Writes % - Percentage of physical writes
REM Total Block I/O's - Number of I/O blocks
REM Use this report to identify any "hot spots" or I/O contention
set linesize 200
set pagesize 200
col name format a46
set linesize 200
set pagesize 200
col name format a46
select NAME,
PHYRDS "Physical Reads",
round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
PHYWRTS "Physical Writes",
round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
select sum(PHYRDS) PHYS_READS,
sum(PHYWRTS) PHYS_WRTS
from v$filestat
) pd,
v$datafile df,
v$filestat fs
where
df.FILE# = fs.FILE#
order by
fs.PHYBLKRD+fs.PHYBLKWRT desc;
Keep learning... Have a great day!!!
