SQL*DBA to Oracle Server Manager Migration Guide Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents


Go to previous file in sequence

System Monitors

This appendix provides you with an overview of the differences between Server Manager System Monitors and SQL*DBA System Monitors.

This appendix describes:

Overview of System Monitors

The following table describes the Server Manager monitors. For a detailed description of each monitor, see Chapters 10 and 11 in the

Simple Monitors

Simple monitors display the results of queries against the V$ tables.

To improve readability, the following query elements do not appear:

Note: Some functions have comments in square brackets to describe the functionality of deleted PL/SQL such as formatting and DECODEs.

Circuit Monitor (Server Manager, SQL*DBA)

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)

Dispatcher Monitor (Server Manager, SQL*DBA)

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)

Library Cache Monitor (Server Manager, SQL*DBA)

SELECT   NAMESPACE,
         GETS,
         GETHITS,
         GETHITRATIO,
         PINS,
         PINHITS,
         PINHITRATIO,
         RELOADS,
         INVALIDATIONS
FROM     V$LIBRARYCACHE
(SQL*DBA: ORDER BY NAMESPACE)

Lock Monitor (Server Manager, SQL*DBA)

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)

Process Monitor (Server Manager)

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(+)

Process Monitor (SQL*DBA)

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

Queue Monitor (Server Manager, SQL*DBA)

SELECT   RAWTOHEX(PADDR),
         TYPE,
         QUEUED,
         TOTALQ,
         DECODE(TOTALQ, 0, 0, WAIT/TOTALQ/100)
FROM     V$QUEUE
(SQL*DBA: ORDER BY PADDR)

Session Monitor (Server Manager)

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

Session Monitor (SQL*DBA)

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#

Shared Server Monitor (Server Manager, SQL*DBA)

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)

SQL Area Monitor (Server Manager, SQL*DBA)

SELECT   SQL_TEXT,
         VERSION_COUNT,
         SHARABLE_MEM,
         PERSISTENT_MEM,
         RUNTIME_MEM
FROM     V$SQLAREA
(SQL*DBA: ORDER BY SQL_TEXT)

Table Access Monitor (Server Manager, SQL*DBA)

SELECT   SID,
         OWNER,
         OBJECT
FROM     V$ACCESS
(SQL*DBA: ORDER BY SID,OWNER)

Tablespace Monitor (Server Manager only)

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

Complex System Monitors

Complex monitors perform calculations on the queried value and display the calculated values instead of or in addition to the queried column values.

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.

File I/O Monitor (Server Manager, SQL*DBA)

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

Latch Monitor (Server Manager)

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)

Latch Monitor (SQL*DBA)

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)

Rollback Monitor (Server Manager)

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

Rollback Monitor (SQL*DBA)

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

Session Statistics Monitor (SQL*DBA only)

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

System I/O Monitor (Server Manager)

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

System I/O Monitor (SQL*DBA)

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))

System Statistics Monitor (Server Manager, SQL*DBA)

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


Go to previous file in sequence Prev Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.		 Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents