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 Continued60 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 Reportcol 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