SQL*DBA to Oracle Server Manager Migration Guide | ![]() Library |
![]() Product |
![]() Contents |
This appendix describes:
To improve readability, the following query elements do not appear:
SELECT RAWTOHEX(C.CIRCUIT), D.NAME, S1.NAME, S.SID, S.SERIAL#, C.STATUS, C.QUEUE, C.MESSAGES, C.BYTES FROM V$CIRCUIT C, V$DISPATCHER D, V$SHARED_SERVER S1, V$SESSION S WHERE C.DISPATCHER = D.PADDR(+) AND C.SERVER = S1.PADDR(+) AND C.SADDR = S.SADDR(+) (SQL*DBA: ORDER BY C.CIRCUIT)
SELECT NAME, STATUS, ACCEPT, MESSAGES, BYTES, OWNED, IDLE [formatted to days:hrs:min:sec], BUSY [formatted to days:hrs:min:sec], BUSY/(IDLE+BUSY), FROM V$DISPATCHER (SQL*DBA: ORDER BY NAME)
SELECT NAMESPACE, GETS, GETHITS, GETHITRATIO, PINS, PINHITS, PINHITRATIO, RELOADS, INVALIDATIONS FROM V$LIBRARYCACHE (SQL*DBA: ORDER BY NAMESPACE)
SELECT S.USERNAME, S.SID, S.SERIAL#, L.TYPE, L.ID1, L.ID2, DECODE(L.LMODE, [mode abbreviations for mode type numbers], [default] '?'), DECODE(L.REQUEST, [mode abbreviations for mode type numbers], [default] '?') FROM V$LOCK L, V$SESSION S WHERE L.SID=S.SID (SQL*DBA: ORDER BY S.SID, L.TYPE)
SELECT P.PID, P.SPID, DECODE (P.USERNAME, '?', DECODE(S.USERNAME, NULL, P.USERNAME, '(' || S.USERNAME || ')'), P.USERNAME), NVL(S.TERMINAL, P.TERMINAL), P.LATCHWAIT, NVL(S.PROGRAM, P.PROGRAM) FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR(+)
SELECT P.PID, P.SPID, P.USERNAME, P.LATCHWAIT, DECODE(S.TERMINAL, NULL, P.TERMINAL, S.TERMINAL), DECODE(S.PROGRAM, NULL, P.PROGRAM, S.PROGRAM) FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR ORDER BY PID
SELECT RAWTOHEX(PADDR), TYPE, QUEUED, TOTALQ, DECODE(TOTALQ, 0, 0, WAIT/TOTALQ/100) FROM V$QUEUE (SQL*DBA: ORDER BY PADDR)
SELECT S.SID, S.SERIAL#, P.PID, S.STATUS, S.USERNAME, LOCKWAIT, DECODE (S.COMMAND, 0, 'NONE', NVL(A.NAME, 'UNKNOWN')) FROM V$SESSION S, V$PROCESS P, AUDIT_ACTIONS A WHERE S.PADDR = P.ADDR AND A.ACTION (+) = S.COMMAND
SELECT S.SID, S.SERIAL#, P.PID, S.STATUS, S.USERNAME, LOCKWAIT, DECODE(S.COMMAND, [command names for each command number], [default] 'UNKNOWN') FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR ORDER BY S.SID, S.SERIAL#
SELECT NAME, STATUS, REQUESTS, IDLE [formatted to days:hrs:min:sec], BUSY [formatted to days:hrs:min:sec], BUSY/(IDLE+BUSY), RAWTOHEX(CIRCUIT) FROM V$SHARED_SERVER (SQL*DBA: ORDER BY NAME)
SELECT SQL_TEXT, VERSION_COUNT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM FROM V$SQLAREA (SQL*DBA: ORDER BY SQL_TEXT)
SELECT SID, OWNER, OBJECT FROM V$ACCESS (SQL*DBA: ORDER BY SID,OWNER)
SELECT DS.TABLESPACE_NAME, DECODE(DS.STATUS, 'ONLINE', DS.STATUS, NLS_INITCAP(DS.STATUS)), SUM(DF.BLOCKS), SUM(DT.BLOCKS) FROM SYS.DBA_TABLESPACES DS, SYS.DBA_TS_QUOTAS DT, SYS.DBA_DATA_FILES DF WHERE DS.STATUS IN ('ONLINE', 'OFFLINE') AND DS.TABLESPACE_NAME = DF.TABLESPACE_NAME AND DF.TABLESPACE_NAME = DT.TABLESPACE_NAME (+) GROUP BY DS.TABLESPACE_NAME, DS.STATUS
The following SQL*DBA monitors display quantities derived from the queried values retrieved from the dynamic performance (V$) tables.
DELTA(:1)
the current retrieved value for :1 minus the previous retrieved value for :1.
RATE(DELTA(:1))
DELTA(:1) divided by the amount of time between the queries (note: RATE requires DELTA).
PCTOFTOTAL(:1)
the value in the current row of column :1 expressed as a percentage of the sum of all values in the column.
NORMALIZED(:1)
current retrieved value for :1 minus the retrieved value for :1 when the monitor was started.
TAVG(:1)
NORMALIZED (:1) divided by total time a monitor has been running.
TMIN(:1)
smallest value of TAVG(:1) since the monitor was started.
TMAX(:1)
largest value of TAVG(:1) since the monitor was started.
SELECT NAME, :1 PHYRDS, :2 PHYWRTS, :3 PHYBLKRD, :4 PHYBLKWRT, :5 READTIM, :6 WRITETIM :7 FROM V$DBFILE DF, V$FILESTAT FS WHERE DF.FILE#=FS.FILE# ORDER BY DF.FILE# Server Manager SQL*DBA Column Columns Columns Calculations Datafile Data File :1 -- Request Rate Request Reads/s Reads/s RATE(DELTA(:2)) Request Writes/s Writes/s RATE(DELTA(:3)) -- Batch Size Batch Blks/rd blks/rd DELTA(:4)/DELTA(:2) Batch Blks/wt blks/wt DELTA(:5)/DELTA(:3) -- Response Time Resp Time ms/rd ms/rd DELTA(:6)/DELTA(:2) Resp Time ms/wt ms/wt DELTA(:7)/DELTA(:3) -- Total Blocks Read :2 Written :3
SELECT L.NAME, :1 LH.PID, :2 L.GETS, :3 L.MISSES, :4 L.SLEEPS, :5 L.IMMEDIATE_GETS, :6 L.IMMEDIATE_MISSES :7 FROM V$LATCH L, V$LATCHHOLDER LH WHERE L.ADDR = LH.LADDR(+) Server Manager Column Columns Calculations Latch Name :1 Holder PID :2 -- Willing-to-Wait-Requests Gets (Wait) DELTA(:3) Misses (Wait) DELTA(:4) Sleeps (Wait) DELTA(:5) -- No-Wait-Requests Gets (No Wait) DELTA(:6) Misses (No Wait) DELTA(:7)
SELECT LN.NAME, :1 LH.PID, :2 L.GETS, :3 L.MISSES, :4 L.SLEEPS, :5 L.IMMEDIATE_GETS, :6 L.IMMEDIATE_MISSES :7 FROM V$LATCH L, V$LATCHNAME LN, V$LATCHHOLDER LH WHERE L.LATCH#=LN.LATCH# AND L.ADDR = LH.LADDR(+) ORDER BY L.LEVEL#,L.LATCH# SQL*DBA Column Column Calculations Latch Name :1 Holder PID :2 Gets DELTA(:3) Misses DELTA(:4) Sleeps DELTA(:5) Gets DELTA(:6) Misses DELTA(:7)
SELECT RN.NAME, :1 RN.USN, :2 RS.RSSIZE, :3 RS.EXTENTS, :4 RS.XACTS, :5 RS.WRITES, :6 RS.GETS, :7 RS.WAITS, :8 RS.OPTSIZE, :9 RS.HWMSIZE, :10 RS.AVEACTIVE, :11 RS.AVESHRINK, :12 RS.WRAPS, :13 RS.EXTENDS, :14 RS.SHRINKS, :15 RS.STATUS :16 FROM V$ROLLNAME RN, V$ROLLSTAT RS WHERE RN.USN = RS.USN Server Manager Column Columns Calculations Rollback Segment :1 RS ID :2 Size (bytes) :3 Extents :4 Active Xactions :5 Write Rate (bytes/s) RATE(DELTA(:6)) Header Gets RATE(DELTA(:7)) Header Waits RATE(DELTA(:8)) Optimal Size :9 HWM Size :10 Average Active :11 Average Shrink :12 Wraps :13 Extends :14 Shrinks :15 Status :16
SELECT RN.USN, :1 RN.NAME, :2 RS.STATUS :3 RS.RSSIZE, :4 RS.EXTENTS, :5 RS.XACTS, :6 RS.WRITES, :7 RS.GETS, :8 RS.WAITS, :9 RS.OPTSIZE, :10 RS.HWMSIZE, :11 RS.AVEACTIVE, :12 RS.AVESHRINK, :13 RS.WRAPS, :14 RS.EXTENDS, :15 RS.SHRINKS, :16 FROM V$ROLLNAME RN, V$ROLLSTAT RS WHERE RN.USN=RS.USN ORDER BY RN.USN SQL*DBA Column Columns Calculations ID :1 Rollback Segment :2 Status :3 Size (bytes) :4 Extents :5 Active Xactions :6 Write Rate (bytes/s) RATE(DELTA(:7)) -- Header Gets/s RATE(DELTA(:8)) Waits/s RATE(DELTA(:9)) -- Sizes (bytes) Optimal :10 Highwater :11 -- Avg Sizes (bytes) Active :12 Shrunk :13 -- Occurrences Wraps :14 Extends :15 Shrinks :16
SELECT DISTINCT S.NAME, :1 ST.VALUE :2 FROM V$STATNAME S, V$SESSTAT ST, V$SESSION SS WHERE S.STATISTIC# = ST.STATISTIC# AND ST.SID = SS.SID ORDER BY S.NAME, ST.VALUE Columns Column Calculations Statistic Name :1 Current DELTA(:2) Average TAVG(DELTA(:2)) Minimum TMIN(DELTA(:2)) Maximum TMAX(DELTA(:2)) Total :2
SELECT P.PID, :1 S.SID, :2 P.USERNAME, :3 ((I.BLOCK_GETS + I.CONSISTENT_GETS) / SUM (I.BLOCK_GETS + I.CONSISTENT_GETS + .0001)) * 100, :4 (I.PHYSICAL_READS / SUM (I.PHYSICAL_READS + .0001)) * 100, :5 ((I.BLOCK_CHANGES + I.CONSISTENT_CHANGES) / SUM (I.BLOCK_CHANGES + I.CONSISTENT_CHANGES + .0001)) * 100, :6 ((I.BLOCK_GETS + I.CONSISTENT_GETS) / SUM (I.BLOCK_GETS + I.CONSISTENT_GETS + .0001)) * 100, :7 (I.PHYSICAL_READS / SUM (I.PHYSICAL_READS + .0001)) * 100, :8 ((I.BLOCK_CHANGES + I.CONSISTENT_CHANGES) / SUM (I.BLOCK_CHANGES + I.CONSISTENT_CHANGES + .0001)) * 100 :9 FROM V$PROCESS P, V$SESSION S, V$SESS_IO I WHERE P.ADDR=S.PADDR AND I.SID = S.SID \ GROUP BY P.PID, S.SID, P.USERNAME, I.BLOCK_GETS, I.CONSISTENT_GETS, I.PHYSICAL_READS, I.BLOCK_CHANGES, I.CONSISTENT_CHANGES Server Manager Column Columns Calculations Process ID :1 Session ID :2 System Username :3 Change in % logical reads DELTA(:4) Change in % physical reads DELTA(:5) Change in % logical writes DELTA(:6) Total % logical reads :7 Total % physical reads :8 Total % logical writes :9
SELECT P.PID, :1 S.SID, :2 I.BLOCK_GETS + I.CONSISTENT_GETS, :3 I.PHYSICAL_READS, :4 I.BLOCK_CHANGES + I.CONSISTENT_CHANGES :5 FROM V$PROCESS P, V$SESSION S, V$SESS_IO I WHERE P.ADDR=S.PADDR AND I.SID=S.SID ORDER BY P.PID, S.SID SQL*DBA Column Columns Calculations Process ID :1 Session ID :2 -- Interval %logical reads PCTOFTOTAL(DELTA(:3)) %physical reads PCTOFTOTAL(DELTA(:4)) %logical writes PCTOFTOTAL(DELTA(:5)) -- Cumulative %logical reads PCTOFTOTAL(NORMALIZED(:3)) %physical reads PCTOFTOTAL(NORMALIZED(:4)) %logical writes PCTOFTOTAL(NORMALIZED(:5))
SELECT S.NAME, :1 S.VALUE :2 FROM V$SYSSTAT S ORDER BY S.NAME, S.VALUE Server Manager Column Columns Calculations Statistic Name :1 Total :2 Current DELTA(:2) Average TAVG(DELTA(:2)) Minimum TMIN(DELTA(:2)) Maximum TMAX(DELTA(:2)) SQL*DBA Column Columns Calculations Statistic Name :1 Current DELTA(:2) Average TAVG(DELTA(:2)) Minimum TMIN(DELTA(:2)) Maximum TMAX(DELTA(:2)) Total :2
![]() | ![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |