Oracle: health check queries (wait events, locks, space)
SQL*Plus queries to troubleshoot an Oracle database: wait events, locks, CPU, tablespaces.
A set of SQL*Plus queries to spot what an Oracle database is waiting on. The gv$ views cover all RAC instances; on a single instance use v$ instead.
Top 25 wait events
set lines 180
set pages 1000
col event format a50
PROMPT --> Top 25 Wait Events
select * from (
select inst_id,event,count(*) E_COUNT from gv$session_wait
where event <> 'SQL*Net message from client'
group by inst_id,event order by 3 desc)
where rownum < 26
order by 3
/
Active sessions waiting on a given event
SQL*Plus prompts for the event name (a LIKE pattern).
col sql format a35
col username format a20
col child format 999
col secs format 9999
col machine format a12
col event format a25
col state format a10
select /*+ rule */ distinct
w.sid,s.username,substr(w.event,1,25) event,substr(s.machine,1,12) machine,substr(w.state,1,10) state,s.SQL_ID,
substr(q.sql_text,1,33) "SQL",round(s.LAST_CALL_ET/60) "MINS", round(s.LAST_CALL_ET) "Sec"
from gv$session_wait w,gv$session s,gv$sql q where w.event like '%&event%'
and w.sid=s.sid
and s.SQL_HASH_VALUE=q.HASH_VALUE(+)
and s.status='ACTIVE'
and s.username is not null
and substr(w.event,1,25) not like 'SQL*Net message from clie%'
order by "MINS"
/
Foreground session count
select count(*), status, inst_id from gv$session
where username is not null
group by status, inst_id order by 3;
Blocking locks
select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
SQL ordered by CPU usage
column sql_text format a40 word_wrapped heading 'SQL|Text'
column cpu_time heading 'CPU|Time'
column elapsed_time heading 'Elapsed|Time'
column disk_reads heading 'Disk|Reads'
column buffer_gets heading 'Buffer|Gets'
column rows_processed heading 'Rows|Processed'
set pages 55 lines 132
select * from
(select sql_text,sql_id,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 10
/
Database CPU and wait time ratio
SET LINESIZE 1000
SET PAGESIZE 1000
COLUMN metric_name FORMAT a40 HEADING 'METRIC_NAME'
COLUMN metric_unit FORMAT a30 HEADING 'METRIC_UNIT'
COLUMN inst_id FORMAT 99 HEADING 'inst_id' JUSTIFY right
COLUMN end_time FORMAT a20 HEADING 'end_time' TRUNC
select METRIC_NAME,metric_unit,INST_ID,INTSIZE_CSEC,
VALUE,begin_time,end_time
from GV$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio',
'Database Wait Time Ratio')
and (INTSIZE_CSEC, inst_id) in
(select max(INTSIZE_CSEC), inst_id from GV$SYSMETRIC group by inst_id)
order by 3;
Wait classes and time waited
Instance 1 only; remove the a.inst_id = 1 filter for all instances.
SET LINESIZE 145
SET PAGESIZE 9999
select b.wait_class, a.inst_id, a.begin_time,
a.end_time, a.dbtime_in_wait, a.time_waited
from GV$WAITCLASSMETRIC a, GV$SYSTEM_WAIT_CLASS b
where a.wait_class_id = b.wait_class_id
and a.inst_id = 1
and a.inst_id = b.inst_id
-- and b.wait_class='Commit'
order by 5 desc;
Temporary 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;
Tablespace utilization
set line 1000;
col TABLESPACE_NAME for a30;
set pagesize 128;
set feedback off;
select a.TABLESPACE_NAME,
a.BYTES/1024/1024/1024 GB_used,
b.BYTES/1024/1024/1024 GB_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(select TABLESPACE_NAME, sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME, sum(BYTES) BYTES, max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc;
Notes
GB_usedis the allocated size of the data files, not the space actually used;percent_usedis the real usage ratio.- The
/*+ rule */hint comes from the original query; the rule-based optimizer is desupported in recent versions.