Previous | Table of Contents | Next

Page 537


      135 SQL*Net roundtrips to/from client                 1    2588500
      136 bytes sent via SQL*Net to dblink                  1          0
      137 bytes received via SQL*Net from dblink            1          0
      138 SQL*Net roundtrips to/from dblink                 1          0
       84 redo entries                                      2    2040536
       85 redo size                                         2  502002531
       86 redo entries linearized                           2          0
       87 redo buffer allocation retries                    2       2391
       88 redo small copies                                 2    1807035
       89 redo wastage                                      2   76782505
       90 redo writer latching time                         2        888
       91 redo writes                                       2     183133
       92 redo blocks written                               2     576689
       93 redo write time                                   2     502618
       94 redo log space requests                           2         66
       95 redo log space wait time                          2       2635
       96 redo log switch interrupts                        2          0
       97 redo ordering marks                               2          0
       22 enqueue timeouts                                  4         25
       23 enqueue waits                                     4         57
       24 enqueue deadlocks                                 4          0
       25 enqueue requests                                  4     707487
       26 enqueue conversions                               4       7259
       27 enqueue releases                                  4     707376
       37 db block gets                                     8    5809539
       38 consistent gets                                   8  202261999
       39 physical reads                                    8  124879028
       40 physical writes                                   8     367214
       41 write requests                                    8      25221
       42 summed dirty queue length                         8      37479
       43 db block changes                                  8    3994241
       44 change write time                                 8      56399
       45 consistent changes                                8     822006
       46 redo synch writes                                 8      23407
       47 redo synch time                                   8     103518
       48 exchange deadlocks                                8          0
       49 free buffer requested                             8  123453575
       50 dirty buffers inspected                           8      42044
       51 free buffer inspected                             8      86462
       52 commit cleanout failure: write disabled           8          0
       53 commit cleanout failures: hot backup in           8          0
          progress

       54 commit cleanout failures: buffer being w          8        164
          ritten

       55 commit cleanout failures: callback failu          8       2769
          re

       56 total number commit cleanout calls                8     547911
       57 commit cleanout number successfully comp          8     538392
          leted

       58 DBWR timeouts                                     8      10503
       59 DBWR make free requests                           8      38615
								continues

Page 538

Listing 22.1 Continued

       60 DBWR free buffers found                           8   18186694
       61 DBWR lru scans                                    8      42222
       62 DBWR summed scan depth                            8   20800400
       63 DBWR buffers scanned                              8   20699788
       64 DBWR checkpoints                                  8        998
       70 recovery blocks read                              8          0
       71 recovery array reads                              8          0
       72 recovery array read time                          8          0
       73 CR blocks created                                 8     125716
       74 Current blocks converted for CR                   8      14791
       99 background checkpoints started                    8         18
      100 background checkpoints completed                  8         18
       28 global lock gets (non async)                     32          1
       29 global lock gets (async)                         32          0
       30 global lock get time                             32          0
       31 global lock converts (non async)                 32          0
       32 global lock converts (async)                     32          0
       33 global lock convert time                         32          0
       34 global lock releases (non async)                 32          0
       35 global lock releases (async)                     32          0
       36 global lock release time                         32          0
       78 next scns gotten without going to DLM            32          0
       79 Unnecessary process cleanup for SCN batch        32          0
          ing

       80 calls to get snapshot scn: kcmgss                32    2619387
       81 kcmgss waited for batching                       32          0
       82 kcmgss read scn without going to DLM             32          0
       83 kcmccs called get current scn                    32          0
       65 DBWR cross instance writes                       40          0
       66 remote instance undo block writes                40          0
       67 remote instance undo header writes               40          0
       68 remote instance undo requests                    40          0
       69 cross instance CR read                           40          0
       98 hash latch wait gets                             40          0
      118 table scans (short tables)                       64     366085
      119 table scans (long tables)                        64      10819
      120 table scans (rowid ranges)                       64         15
      121 table scans (cache partitions)                   64          0
      122 table scans (direct read)                        64         15
      123 table scan rows gotten                           64  904956876
      124 table scan blocks gotten                         64  129018277
      125 table fetch by rowid                             64   31893640
      126 table fetch continued row                        64     506029
      127 cluster key scans                                64     135034
      128 cluster key scan block gets                      64     363979
      129 parse time cpu                                   64      34740
      130 parse time elapsed                               64      55148
      131 parse count                                      64     532516
      132 execute count                                    64    2390178
      139 sorts (memory)                                   64      40412
      140 sorts (disk)                                     64        268
      141 sorts (rows)                                     64   49141580
      142 session cursor cache hits                        64          0

Page 539

      143 session cursor cache count                       64          0
	  11 CPU used when call started                      128    4896911
      14 process last non-idle time                      128 6.2794E+11
      17 messages sent                                   128     160593
      18 messages received                               128     160593
      19 background timeouts                             128      31883
      75 calls to kcmgcs                                 128      90759
      76 calls to kcmgrs                                 128    4168092
      77 calls to kcmgas                                 128     306090
     102 transaction lock foreground requests            128          0
     103 transaction lock foreground wait time           128          0
     104 transaction lock background gets                128          0
     105 transaction lock background get time            128          0
     106 transaction tables consistent reads - un        128     143669
          do records applied

     107 transaction tables consistent read rollb        128        131
         acks

     108 data blocks consistent reads - undo reco        128     678275
         rds applied

    109 no work - consistent read gets                  128  194428691
      110 cleanouts only - consistent read gets           128      25194
      111 rollbacks only - consistent read gets           128      86452
      112 cleanouts and rollbacks - consistent rea        128      54180
          d gets

      113 rollback changes - undo records applied         128      14117
      114 transaction rollbacks                           128       1520
      115 immediate (CURRENT) block cleanout appli        128      73789
          cations

      116 immediate (CR) block cleanout applicatio        128      79374
          ns


      117 deferred (CURRENT) block cleanout applic        128     264576
          ations

      144 cursor authentications                          128      95248

NOTE
Be careful when you use this view. Some of the statistic values in this view can get so large that, due to existing bugs in some Oracle versions, these may show wrong values.n

The statistic we are interested in is CPU used by this session having a statistic# value
of 12:

12 CPU used by this session                          1    4896925
SQL> select * from v$sysstat order by class,statistic# WHERE name = `CPU used
by Âthis session'

Page 540

Now the value of CPU used by this session is in hundredths of a second. Converting the value into minutes, we modify the query as follows:

col name format a35
col value format 999.99 heading "Time in | Mins"
select statistic#,name,class ,value/60/100 value
from v$sysstat
where statistic# =  12
/
STATISTIC# NAME                                     CLASS     Mins
---------- ---------------------------------- ----------- --------
       12  CPU used by this session                     1   817.56

This output shows that the RDBMS with all its background and foreground server processes has consumed approximately 817 minutes of CPU time since startup. This is again information at an overall level. You now want to find which session has consumed how much of the CPU resource.

There is another dynamic performance view called V$SESSTAT. Basically, the only difference between the two is that V$SYSSTAT stores the summary level information, and V$SESSTAT stores information at the session level; V$SESSTAT can be called the child table of V$SYSSTAT. You can use the information in V$SESSTAT to find out which session is consuming the maximum CPU. You do that by using the script in Listing 22.2.

Listing 22.2 Script and Output to Produce CPU Usage Report
col prog format a10
col value format 9999.99 heading "Time In|    Mins"

Select a.sid,
                spid,
                status,
                substr(a.program,1,10) prog,
                a.terminal,
                osuser,
                value/60/100 value
From   v$session a,
                v$process b,
                v$sesstat c
Where  c.statistic# = 12
And    c.sid        = a.sid
And    a.paddr      = b.addr
Order by value desc;

 Time In
      SID SPID      STATUS   PROG       TERMINAL   Schema       Mins
--------- -------- -------- ---------- ---------- -------- --------
       45 11145     INACTIVE C:\ARMS\BS Windows PC RICHARDH    95.87
       95 12370     INACTIVE C:\WINDOWS Windows PC MATHEWS     22.99
       47 9778      INACTIVE C:\ARMS\BS Windows PC KARENC      10.22
       22 9295      ACTIVE   C:\ARMS\DE Windows PC SUNIT        3.96
       37 14427     INACTIVE C:\ARMS\CS Windows PC PETER         .50
      107 9454      INACTIVE sqlplus@ar ttyp2      TOM           .34

Previous | Table of Contents | Next