Oracle Database Monitoring Queries
QUERY TO CHECK SIZE OF ALL TABLESPACES
SELECT /* + RULE */ df.tablespace_name
“Tablespace”,
df.bytes / ( 1024 * 1024 )
“Size (MB)”,
SUM(fs.bytes) / ( 1024 * 1024 )
“Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes), 1)
“% Free”,
Round(( df.bytes – SUM(fs.bytes) ) * 100 / df.bytes)
“% Used”
FROM dba_free_space fs,
(SELECT tablespace_name,
SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,
df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / ( 1024 * 1024 ),
SUM(df.bytes_free) / ( 1024 * 1024 ),
Nvl(Round(( SUM(fs.bytes) – df.bytes_used ) * 100 /
fs.bytes), 1),
Round(( SUM(fs.bytes) – df.bytes_free ) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,
bytes_free,
bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,
bytes_free,
bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,
fs.bytes,
df.bytes_free,
df.bytes_used
ORDER BY 4 DESC;
QUERY TO CHECK BLOCKING SESSIONS PER USER
SELECT s1.username
|| ‘@’
|| s1.machine
|| ‘ ( SID=’
|| s1.sid
|| ‘ ) is blocking ‘
|| s2.username
|| ‘@’
|| s2.machine
|| ‘ ( SID=’
|| s2.sid
|| ‘ ) ‘ AS blocking_status
FROM v$lock l1,
v$session s1,
v$lock l2,
v$session s2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2;
QUERY TO CHECK LIST OF LOCKS IN THE DATABASE
SELECT a.inst_id,
a.sid,
a.TYPE,
a.id1,
a.id2,
Decode(a.lmode, 0, ‘NONE’,
1, ‘NULL’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’,
‘?’),
Decode(a.request, 0, ‘NONE’,
1, ‘NULL’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’,
‘?’),
a.BLOCK
FROM gv$lock a
WHERE a.id1 IN (SELECT id1
FROM gv$lock
WHERE request <> 0)
ORDER BY a.id1,
a.request,
a.sid;
QUERY TO CHECK TOP 10 DATABASE LOAD QUERIES
SELECT *
FROM (SELECT sql_text,
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 < 11;
QUERY TO CHECK OPEN CURSOR BY USER
SELECT SUM(a.value) total_cur,
Avg(a.value) avg_cur,
Max(a.value) max_cur,
s.username,
s.machine
FROM v$sesstat a,
v$statname b,
v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = ‘opened cursors current’
GROUP BY s.username,
s.machine
ORDER BY 1 DESC;
QUERY TO CHECK USERS, SESSIONS, CPU AND I/O CONSUMPTION
SELECT p.spid SPID,
To_char(s.logon_time, ‘DDMonYY HH24:MI’) date_login,
s.username,
Decode(Nvl(p.background, 0), 1, bg.description,
s.program) program,
ss.value / 100 CPU,
physical_reads disk_io,
( Trunc(SYSDATE, ‘J’) – Trunc(logon_time, ‘J’) ) days,
Round(( ss.value / 100 ) / ( Decode(( Trunc(SYSDATE, ‘J’) –
Trunc(logon_time, ‘J’) ),
0, 1,
(
Trunc(SYSDATE, ‘J’) – Trunc(
logon_time, ‘J’) )) ), 2) cpu_per_day
FROM v$process p,
v$session s,
v$sesstat ss,
v$sess_io si,
v$bgprocess bg
WHERE s.paddr = p.addr
AND ss.sid = s.sid
AND ss.statistic# = 12
AND si.sid = s.sid
AND bg.paddr(+) = p.addr
AND Round(( ss.value / 100 ), 0) > 10
ORDER BY 8;
QUERY TO FIND ACTIVE SESSIONS IN DATABASE
SELECT ‘ACTIVE SESSION’,
sid,
serial#,
machine,
osuser,
username,
status
FROM v$session
WHERE username != ‘NULL’
AND status = ‘ACTIVE’;
QUERY TO CHECK FOR TABLE LOCKS
SELECT a.sid,
a.serial#,
a.username,
c.os_user_name,
a.terminal,
b.object_id,
Substr(b.object_name, 1, 40) object_name
FROM v$session a,
dba_objects b,
v$locked_object c
WHERE a.sid = c.session_id
AND b.object_id = c.object_id;
QUERY TO FIND Undo Tablespace USAGE
SELECT s.username,
s.sid,
rn.name,
rs.rssize / 1024 / 1024 “UsedSize IN MBs”,
rs.status,
t.used_ublk,
t.used_urec,
do.object_name
FROM v$transaction t,
v$session s,
v$rollname rn,
v$rollstat rs,
v$locked_object lo,
dba_objects DO
WHERE t.addr = s.taddr
AND t.xidusn = rn.usn
AND rn.usn = rs.usn
AND t.xidusn = lo.xidusn(+)
AND do.object_id = lo.object_id;
QUERY TO FIND LONG RUNNING SQL statements
SELECT s.rows_processed,
s.loads,
s.executions,
s.buffer_gets,
s.disk_reads,
t.sql_text,
s.MODULE,
s.action
FROM v$sql
/*area*/ s,
v$sqltext t
WHERE s.address = t.address
AND ( ( buffer_gets > 10000000 )
OR ( disk_reads > 1000000 )
OR ( executions > 1000000 ) )
ORDER BY ( ( s.disk_reads * 100 ) + s.buffer_gets ) DESC,
t.address,
t.piece;
QUERY TO FIND DATABASE SIZE
SELECT SUM(tot.bytes / ( 1024 * 1024 * 1024 ))
“Total size iN GBs”,
SUM(tot.bytes / ( 1024 * 1024 ) – SUM(Nvl(fre.bytes, 0)) /
( 1024 * 1024 )) Used,
SUM(SUM(Nvl(fre.bytes, 0)) / ( 1024 * 1024 ))
Free,
SUM(( 1 – SUM(Nvl(fre.bytes, 0)) / tot.bytes ) * 100)
Pct
FROM dba_free_space fre,
(SELECT tablespace_name,
SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) tot,
dba_tablespaces tbs
WHERE tot.tablespace_name = tbs.tablespace_name
AND fre.tablespace_name(+) = tbs.tablespace_name
GROUP BY tbs.tablespace_name,
tot.bytes / ( 1024 * 1024 ),
tot.bytes;
QUERY TO FIND Database, Redologs, Temp AND Control File SIZE
SELECT ‘Full Data Size: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’ FULL_DATABASE_SIZE
FROM dba_data_files
UNION
SELECT ‘TEMP: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM dba_temp_files
UNION
SELECT ‘REDO LOGS: ‘
|| SUM(bytes) / 1024 / 1024 / 1024
|| ‘ GBytes’
FROM v$log
UNION
SELECT ‘CONTROLFILE: ‘
|| SUM(file_size_blks * block_size) / 1024 / 1024
|| ‘ MBytes’
FROM v$controlfile;