Oracle monitoring query

1. Access Report

àList of objects in the database currently accessed by currently parsed cursors

Select sid, owner,object ,type

From v$access

Order by 1,2,3,4

 

2. Data Dictionary Cache Efficiency

àPercentage of time that data dictionary data are found in the dictionary cache

select round(sum(gets)/(sum(gets)+sum(getmisses))*100,2)

from v$rowcache

 

3.Data Dictionary Details Report

àDetail data about the data dictionary

select cache#, type, subordinate#, parameter, count, usage, fixed

,gets,getmisses "Get Misses", scans, scanmisses "Scan Misses",

scancompletes "Scan Complete", modifications, flushes

from v$rowcache

 

4.Data Dictionary Report

àAggregate data about the data dictionary

Select sum(count) Count, sum(usage) Usage, sum(fixed) Fixed,

Sum(gets) Gets, sum(getmisses) “Get Misses”, sum(scans) Scans,

Sum(scanmisses) “Scan Misses”, sum(scancompletes) “Scan Completes”,

Sum(modifications) Modifications, sum(flushes) Flushes

From v$rowcache

 

5.DB BLOCK COUNT

àCount of gets in db block buffer

Select sum(value)

From v$sysstat

Where name in (‘db block gets’,’consistent gets’)

 

6.DB Block Efficiency

->Percentage of time that requested data are found in the data cache

select round(1-(pr.value/(bg.value+cg.value))*100,2)

from v$sysstat pr, v$sysstat bg, v$sysstat cg

where pr.name = 'physical reads'

and bg.name = 'db block gets'

and cg.name = 'consistent gets'

 

7.DB Block Report

àFormated SQL*Plus report with assorted buffer cache statistics

select pr.value physical_read,bg.value block_get, cg.value consistent_gets,

(1-(pr.value/(bg.value+cg.value)))*100 bchr

from v$sysstat pr, v$sysstat bg, v$sysstat cg

where pr.name = 'physical read'

and bg.name = 'db block gets'

and cg.name = 'consistent gets'

 

8.DB Block Usage

àLists status of all database blocks

select decode(state, 0 ,'Free',

      1,'Read and Modified',

      2,'Read and Not Modified',

      3,'Currently Being Read','Other'

      ), count(*)

from x$bh

group by decode(state, 0 ,'Free',

      1,'Read and Modified',

      2,'Read and Not Modified',

      3,'Currently Being Read','Other'

      )

 

9.Disk Reads Logical Max SQL

àReturns the first 1000 bytes of the user SQL statement consuming the most blocks in the DB Block Buffer

Select

From v$sqlarea, v$session

Where address = sql_address

And username is not null

And buffer_gets/executions = (select max(buffer_gets/executions)

                             From v$sqlarea, v$session

                             Where address = sql_address

)

 

10. Disk Reads Logical Max User

àReturns the username associated with the user SQL Statement consuming the most blocks in the DB Block buffer

Select Username

From v$sqlarea, v$session

Where address = sql_address

And username is not null

And buffer_gets/executions = (select max(buffer_gets/executions)

                              From v$sqlarea , v$session

                              Where address = sql_address

                              And username is not null

                              And executions > 0

)

 

11. Disk Reads Physical Max SQL

àReturns the first 1000 bytes of the user SQL Statement having the highest number of physical disk reads per statement execution

select sql_text

from v$sqlarea , v$session

where address = sql_address

and username is not null

and disk_reads/executions = (select max(disk_reads/executions)

                             from v$sqlarea, v$session

                             where address = sql_address

                             and username is not null

                             and executions > 0

                            )

 

12.Disk Reads Physical Max SQL Current Session

àReturns the first 1000 bytes of the user SQL Statement having the highest number of physical disk reads per statement execution for current sessions

select sql_text

from v$sqlarea , v$session

where address = sql_address

and username is not null

and disk_reads/executions = (select max(disk_reads/executions)

                             from v$sqlarea, v$session

                             where address = sql_address

                             and username is not null

                             and executions > 0

                            )

 

13.Disk Reads Physical Max User

à Returns the username associated with the user SQL Statement having the highest number of physical disk reads per statement executions

select username

from v$sqlarea , v$session

where address = sql_address

and username is not null

and disk_reads/executions = (select max(disk_reads/executions)

                             from v$sqlarea, v$session

                             where address = sql_address

                             and username is not null

                             and executions > 0

                            )

 

14.Extent List

àOrdered list of table and index extent information

select  SEGMENT_NAME, SEGMENT_TYPE, COUNT(*) NUMEXT,

ROUND(SUM(BYTES)/1024/1024,1) MB

from sys.dba_extents

where owner not in ('SYS','SYSTEM')

GROUP BY SEGMENT_NAME, SEGMENT_TYPE

ORDER BY SEGMENT_TYPE, ROUND(SUM(BYTES)/1024/1024,1) DESC, SEGMENT_NAME

 

15.EXTENTS MAX COUNT

àLARGEST NUMBER OF EXTENTS USED BY ANY DATA BASE OBJECT, I,E. TBLE OR INDEX

SELECT MAX(EXTENT_ID)+1

FROM SYS.DBA_EXTENTS

WHERE OWNER NOT IN (‘SYS’,’SYSTEM’)

 

16.Extents Max Object Name

àName of the table or index using the maximum number of extents in any tablespace

select SEGMENT_NAME

from sys.dba_extents

where owner not in ('SYS','SYSTEM')

and extent_id=(select max(extent_id)

              from sys.dba_extents

              where owner not in ('SYS','SYSTEM')

              )

 

17. File Report

àInformation about the Oracle data files

Select name, phyrds  “Total Reads”, phywrts  “Total Writes”,

Phyblkrd “Blocks Read”, phyblkwrt “Blocks Written”

From v$datafile d, v$filestats

Where d.file# = s.file#

Order by d.file#

 

18.Free List Contention

àPercentage of time that a request for data resulted in a wait for a free block

Select

select round(sum(decode(w.class,'free list', count,0))

/(sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0)))*100,2)

from v$waitstat w, v$sysstat

 

19.Index System Count

àNumber of indexes created in the SYSTEM tablespace by users

Select count(*)

From sys.dba_indexes

Where tablespace_name=’SYSTEM’

And owner not in (‘SYS’,’SYSTEM’)

 

20.IO Network Bytes

àNumber of bytes communicated to the RDBMS via SQL*Net

select sum(value)

from v$sysstat

where name like 'byte%SQL*Net%'

 

21.IO Physical Count

àCount of the total number of physical reads and writes for the RDBMS since statup

Select sum(phyrds) + sum(phywrts) “Total I/O”

From v$filestat

 

22.IO Physical Count

àCount of the total number of physical reads and writes for the RDBMS since startup

select sum(decode(name,'db block changes',value,0)) "Block Changes",

(sum(decode(name,'db block gets',value,0))+ sum(decode(name,'consistent gets',value,0))) "buffer gets",

sum(decode(name,'physical read',value,0)) "Physical Reads",

(sum(decode(name,'db block gets',value,0)) + sum(decode(name,'consistent gets',value,0)))

/sum(decode(name,'physical reads',value,0)) "GETS/READS"

from v$sysstat

 

23.Latch Contention

àLargest Percentage of latch contention from key latches

select round(greatest(

(sum(decode(ln.name,'cache buffers lru chain',misses,0))

/ greatest(sum(decode(ln.name,'cache buffers lru chain',gets,0)),1)),

(sum(decode(ln.name,'enqueues',misses,0))

/greatest(sum(decode(ln.name,'enqueues',gets,0)),1)),

(sum(decode(ln.name,'redo allocation',misses,0))

/greatest(sum(decode(ln.name,'redo allocation',gets,0)),1)),

(sum(decode(ln.name,'redo copy', misses,0))

/greatest(sum(decode(ln.name,'redo copy',gets,0)),1)))*100,2)

from v$latch l , v$latchname ln

where l.latch# = ln.latch#

 

24.Latch Detail Report

àDetailed information about each current latch

select ln.name, lh.pid,

l.immediate_gets, l.immediate_misses,

l.gets, l.misses, l.sleeps

from v$latch l, v$latchholder lh, v$latchname ln

where l.latch# = ln.latch#

and l.addr = lh.laddr(+)

 

25.Latch Efficiency

àPercent of time that a request for a latch was statisfied, although not necessarify immediately

select round(((sum(immediate_gets)+sum(misses)+sum(gets))

/ (sum(immediate_gets)+sum(immediate_misses)+sum(gets)+sum(misses)))*100,2)

from v$latch

 

26.Latch Immediate Efficiency

àPercent of time that a request for a latch was immediately statisfied

select round(sum(immediate_gets)/(sum(immediate_gets)+sum(immediate_misses)

+sum(gets)+sum(misses))*100,2)

from v$latch

 

27.Latch Report

àAggregate Latch data

Select sum(l.immediate_gets),

Sum(l.immediate_misses),

Sum(l.gets),sum(l.misses), sum(l.sleeps)

From v$latch l,v$latchholder lh, v$latchname ln

Where l.latch# = ln.latch#

And l.addr = lh.laddr(+)

 

28.Library Cache Efficiency

àPercentage of time a SQL Statement does not need to be reloaded.

Select round(sum(pinhits)/sum(pins)*100,2)

From v$librarycache

 

29.Library Cache Report

->Returns Various information on library cache management

Select namespace name, gets, gethits,round(gethitratio*100,2) “GetHit Percentage”,

Pins, pinhits, round(pinhitratio*100,2) “PinHit Percentage”, reloads, invalidations

From v$librarycache

Order by 1

 

30.Lock All Report

àDisplays various Information for each current, non-DDL lock

select username

, l.sid

, serial#

, l.type

, id1

, id2

,decode(request,1,'NULL',2,'ROW SHARE',3,'ROW EXCLUSIVE',4,'SHARE',

5,'SHARE ROW EXCLUSIVE',6,'EXCLUSIVE','?') "REQUEST",

DECODE(LMODE,1,'NULL',2,'ROW SHARE',3,'ROW EXCLUSIVE',4,'SHARE',5,'SHARE ROW EXCLUSIVE'

,6,'EXCLUSIVE','?') "LOCK MODE"

from v$lock l,v$session b

[출처] ORACLE 모니터링 하기 위한 쿼리 1