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