| |
STATSPACK report for
DB Name DB Id Instance Inst
Num Release Cluster Host
------------ ----------- ------------
-------- ----------- ------- ------------
123456 xxx
123456 1 9.2.0.4.0 NO xxxx
. . .
Elapsed: 9.98 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache:
11,504M Std Block Size: 8K
Shared Pool Size:
592M Log Buffer: 1,280K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size:
417,109.35 1,414.69
Logical reads:
156,148.30 529.60
Block changes:
2,122.03 7.20
Physical reads:
6,358.78 21.57
Physical writes:
1,041.54 3.53
User calls:
2,765.77 9.38
Parses:
164.26 0.56
Hard
parses: 2.59 0.01
Sorts:
184.62 0.63
Logons: 0.21 0.00
Executes:
1,704.78 5.78
Transactions:
294.84
% Blocks changed per Read: 1.36
Recursive Call %: 30.68
Rollback per transaction %: 97.19
Rows per Sort: 2005.74
Instance Efficiency Percentages (Target
100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99
Redo NoWait %: 100.00
Buffer Hit %: 96.49
In-memory Sort %: 99.94
Library Hit %: 99.82
Soft Parse %: 98.42
Execute to Parse %:
90.36 Latch Hit %: 99.91
Parse CPU to Parse Elapsd %: 88.91 %
Non-Parse CPU: 99.45
Shared Pool Statistics Begin
End ------ ------
Memory Usage %: 94.50
95.04
% SQL with executions>1: 44.87
45.69
% Memory for SQL w/exec>1: 58.06
58.60
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
-------------------------------------------- ------------ -----------
--------
CPU
time 2,733 44.85
db file sequential
read 677,443 2,494 40.93
background parameter
adjustment 58,882 580 9.51
db file scattered
read 174,315 153 2.50
SQL*Net message from dblink
113 53 .87
-------------------------------------------------------------
Wait Events for DB: 123456 Instance: 123456
Snaps: 7147 -7148
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc
(idle events last)
Avg
Total Wait
wait Waits
Event Waits
Timeouts Time (s) (ms) /txn
---------------------------- ------------
---------- -------------- ---------- --------
db file sequential read
677,443 0 2,494 4 3.8
background parameter adjustm
58,882 58,882 580 10 0.3
db file scattered read
174,315 0 153 1 1.0
SQL*Net message from dblink
113 0 53 470 0.0
log file sync
4,056 0 28 7 0.0
db file parallel write
1,500 0 15 10 0.0
direct path read
10,112 0 14 1 0.1
buffer busy waits
6,149 0 12 2 0.0
log file sequential read
332 0 4 11 0.0
db file parallel read
239 0 4 15 0.0
latch free
1,306 254 3 2 0.0
control file parallel write
273 0 1 4 0.0
async disk IO
1,030 0 0 0 0.0
direct path write
560 0 0 0 0.0
SQL*Net break/reset to clien
450 0 0 0 0.0
SQL*Net more data to client
5,705 0 0 0 0.0
LGWR wait for redo copy
32 1 0 2 0.0
direct path read (lob)
102 0 0 0 0.0
control file sequential read 1,584
0 0 0 0.0
log file switch completion
3 0 0 7 0.0
log file parallel write
4,769 4,750 0 0 0.0
log file single write
4 0 0 2 0.0
enqueue
1 0 0 0 0.0
SQL*Net message to dblink
113 0 0 0 0.0
SQL*Net more data from dblin
1 0 0 0 0.0
buffer deadlock
2 2 0 0 0.0
SQL*Net message from client
1,651,599 0 180,538 109 9.4
PX Idle Wait
1,169 1,169 2,334 1,997 0.0
pipe get
284 280 907 3,194 0.0
virtual circuit status
20 20 570 28,491 0.0
wakeup time manager
19 19 544 28,623 0.0
SQL*Net more data from clien
1,012 0 18 18 0.0
SQL*Net message to client
1,651,610 0 1 0 9.4
-------------------------------------------------------------
Background Wait Events for DB: 123456
Instance: 123456 Snaps: 7147 -7148
-> ordered by wait time desc, waits desc
(idle events last)
Avg
Total Wait
wait Waits
Event Waits
Timeouts Time (s) (ms) /txn
---------------------------- ------------
---------- -------------- ---------- --------
db file parallel write
1,498 0 15 10 0.0
log file sequential read
308 0 3 11 0.0
control file parallel write
273 0 1 4 0.0
latch free
156 0 0 2 0.0
async disk IO
895 0 0 0 0.0
rdbms ipc reply
134 0 0 1 0.0
db file scattered read
20 0 0 4 0.0
LGWR wait for redo copy
32 1 0 2 0.0
direct path read
860 0 0 0 0.0
control file sequential read
282 0 0 0 0.0
db file sequential read
12 0 0 1 0.0
log file parallel write
4,767 4,748 0 0 0.0
direct path write
206 0 0 0 0.0
log file single write
4 0 0 2 0.0
rdbms ipc
message 16,936 10,331 5,646 333
0.1
pmon
timer 202 202 584
2,890 0.0
smon timer
2 1 539 269,479 0.0
-------------------------------------------------------------
. . .
Instance Activity Stats for DB: 123456
Instance: 123456 Snaps: 7147 -7148
Statistic Total per Second per
Trans
---------------------------------
------------------ -------------- ------------
CPU used by this
session 273,270 456.2 1.6
CPU used when call
started 273,329 456.3 1.6
CR blocks
created 2,475 4.1 0.0
DBWR buffers
scanned 863,085 1,440.9 4.9
DBWR checkpoint buffers
written 17,930 29.9 0.1
DBWR
checkpoints 1 0.0 0.0
DBWR free buffers
found 838,995 1,400.7 4.8
DBWR lru
scans 841 1.4 0.0
DBWR make free
requests 876 1.5 0.0
DBWR revisited being-written
buff 0 0.0 0.0
DBWR summed scan
depth 863,085 1,440.9 4.9
DBWR transaction table
writes 22 0.0 0.0
DBWR undo block
writes 9,943 16.6 0.1
Parallel operations downgraded
to 0 0.0 0.0
SQL*Net roundtrips to/from client
1,655,074 2,763.1 9.4
SQL*Net roundtrips to/from dblink
93 0.2 0.0
active txn count during
cleanout 20,286 33.9 0.1
background checkpoints
completed 1 0.0 0.0
background checkpoints
started 1 0.0 0.0
background
timeouts 1,310 2.2 0.0
branch node splits
3 0.0 0.0
buffer is not pinned count
66,117,706 110,380.1 374.4
buffer is pinned count
491,902,356 821,205.9 2,785.3
bytes received via SQL*Net from c
184,566,777 308,124.8 1,045.1
bytes received via SQL*Net from d
26,038 43.5 0.2
bytes sent via SQL*Net to client
224,526,278 374,835.2 1,271.3
bytes sent via SQL*Net to dblink
24,328 40.6 0.1
calls to get snapshot scn: kcmgss
1,386,578 2,314.8 7.9
calls to kcmgas
22,001 36.7 0.1
calls to kcmgcs
32,969 55.0 0.2
change write
time 598 1.0 0.0
cleanout - number of ktugct
calls 20,284 33.9 0.1
cluster key scan block
gets 547 0.9 0.0
cluster key
scans 347 0.6 0.0
commit cleanout failures: block
l 1 0.0 0.0
commit cleanout failures:
buffer 0 0.0 0.0
commit cleanout failures: callbac
2,761 4.6 0.0
commit cleanout failures:
cannot 1 0.0 0.0
commit cleanout failures: hot bac
0 0.0 0.0
commit
cleanouts 70,868 118.3 0.4
commit cleanouts successfully
com 68,105 113.7 0.4
commit txn count during
cleanout 10,579 17.7 0.1
consistent changes
17,673 29.5 0.1
consistent gets
91,982,851 153,560.7 520.8
consistent gets - examination
16,984,759 28,355.2 96.2
current blocks converted for CR
0 0.0 0.0
cursor
authentications 225 0.4 0.0
data blocks consistent reads -
un 17,536 29.3 0.1
db block changes
1,271,093 2,122.0 7.2
db block gets
1,547,615 2,583.7 8.8
deferred (CURRENT) block
cleanout 18,813 31.4 0.1
Instance Activity Stats for DB: 123456
Instance: 123456 Snaps: 7147 -7148
Statistic Total per Second per
Trans
---------------------------------
------------------ -------------- ------------
dirty buffers
inspected 918 1.5 0.0
enqueue
conversions 824 1.4 0.0
enqueue
releases 596,965 996.6 3.4
enqueue
requests 596,965 996.6 3.4
enqueue
timeouts 0 0.0 0.0
enqueue
waits 1 0.0 0.0
exchange
deadlocks 2 0.0 0.0
execute count
1,021,165 1,704.8 5.8
free buffer
inspected 974 1.6 0.0
free buffer requested
3,313,304 5,531.4 18.8
hot buffers moved to head of
LRU 427,689 714.0 2.4
immediate (CR) block cleanout
app 3,859 6.4 0.0
immediate (CURRENT) block cleanou
12,958 21.6 0.1
index fast full scans
(full) 850 1.4 0.0
index fetch by key
2,047,839 3,418.8 11.6
index scans kdiixs1
6,144,497 10,257.9 34.8
leaf node 90-10 splits
24 0.0 0.0
leaf node
splits 1,248 2.1 0.0
logons
cumulative 127 0.2 0.0
messages received
54,366,894 90,762.8 307.8
messages sent
54,366,943 90,762.8 307.8
no buffer to keep pinned
count 0 0.0 0.0
no work - consistent read gets
68,541,443 114,426.5 388.1
opened cursors
cumulative 85,612 142.9 0.5
parse count
(failures) 106 0.2 0.0
parse count
(hard) 1,552 2.6 0.0
parse count
(total) 98,391 164.3 0.6
parse time cpu
1,499 2.5 0.0
parse time
elapsed 1,686 2.8 0.0
physical reads
3,808,912 6,358.8 21.6
physical reads
direct 521,605 870.8 3.0
physical reads direct
(lob) 102 0.2 0.0
physical
writes 623,882 1,041.5 3.5
physical writes
direct 580,947 969.9 3.3
physical writes non
checkpoint 618,078 1,031.9 3.5
pinned buffers
inspected 0 0.0 0.0
prefetch clients -
default 0 0.0 0.0
prefetched blocks
2,431,051 4,058.5 13.8
prefetched blocks aged out
before 56 0.1 0.0
process last non-idle time
148,402,739,110 247,750,816.5 840,285.0
recursive
calls 733,061 1,223.8 4.2
recursive cpu usage
109,842 183.4 0.6
redo blocks
written 506,240 845.1 2.9
redo buffer allocation
retries 3 0.0 0.0
redo entries
680,796 1,136.6 3.9
redo log space
requests 3 0.0 0.0
redo log space wait
time 3 0.0 0.0
redo ordering
marks 0 0.0 0.0
redo size
249,848,500 417,109.4 1,414.7
redo synch
time 2,906 4.9 0.0
redo synch
writes 4,314 7.2 0.0
Instance Activity Stats for DB: 123456
Instance: 123456 Snaps: 7147 -7148
Statistic Total per Second per
Trans
---------------------------------
------------------ -------------- ------------
redo wastage
1,198,216 2,000.4 6.8
redo write
time 2,821 4.7 0.0
redo writer latching
time 6 0.0 0.0
redo
writes 4,790 8.0 0.0
rollback changes - undo records
a 583 1.0 0.0
rows fetched via callback
1,517,982 2,534.2 8.6
session connect time
148,402,739,110 247,750,816.5 840,285.0
session logical reads
93,532,834 156,148.3 529.6
session pga memory
200,385,904 334,534.1 1,134.6
session pga memory max
902,162,536 1,506,114.4 5,108.2
session uga memory
192,894,560 322,027.7 1,092.2
session uga memory max
9,354,979,808 15,617,662.5 52,969.7
shared hash latch upgrades - no w
6,202,061 10,354.0 35.1
shared hash latch upgrades -
wait 456 0.8 0.0
sorts
(disk) 65 0.1 0.0
sorts (memory)
110,523 184.5 0.6
sorts (rows)
221,810,270 370,301.0 1,255.9
summed dirty queue
length 25,018 41.8 0.1
switch current to new
buffer 11,334 18.9 0.1
table fetch by rowid
270,591,682 451,739.0 1,532.1
table fetch continued
row 658,624 1,099.5 3.7
table scan blocks gotten
8,505,000 14,198.7 48.2
table scan rows gotten
376,409,086 628,395.8 2,131.3
table scans (cache
partitions) 3 0.0 0.0
table scans (long
tables) 14 0.0 0.0
table scans (short
tables) 68,433 114.3 0.4
transaction
rollbacks 29 0.1 0.0
transaction tables consistent rea
0 0.0 0.0
transaction tables consistent rea
0 0.0 0.0
user calls
1,656,695 2,765.8 9.4
user
commits 4,958 8.3 0.0
user
rollbacks 171,652 286.6 1.0
workarea executions -
onepass 124 0.2 0.0
workarea executions -
optimal 143,794 240.1 0.8
write clones created in backgroun
0 0.0 0.0
write clones created in foregroun
1 0.0 0.0
-------------------------------------------------------------
. . .
Buffer Pool Statistics for DB: 123456
Instance: 123456 Snaps: 7147 -7148
-> Standard block size Pools D: default,
K: keep, R: recycle
-> Default Pools for other block sizes: 2k,
4k, 8k, 16k, 32k
Free Write
Buffer
Number of Cache Buffer
Physical Physical Buffer Complete Busy
P Buffers Hit % Gets
Reads Writes Waits Waits Waits
--- ---------- ----- -----------
----------- ---------- ------- -------- ------
D 1,427,215 96.6 94,673,046
3,260,533 43,269 0 0 7,687
-------------------------------------------------------------
Instance Recovery Stats for DB: 123456
Instance: 123456 Snaps: 7147 -7148
-> B: Begin snapshot, E: End
snapshot
Targt
Estd Log File Log Ckpt Log Ckpt
MTTR MTTR
Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo
Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- ----------
---------- ---------- ---------- ----------
B 300 22 27182 444424
442368 442368 681532
E 300 22 28089 446911
442368 442368 1004265
-------------------------------------------------------------
Buffer Pool Advisory for DB: 123456
Instance: 123456 End Snap: 7148
-> Only rows with estimated physical reads
>0 are displayed
-> ordered by Block Size, Buffers For
Estimate
Size for Size Buffers for
Est Physical Estimated
P Estimate (M) Factr Estimate
Read Factor Physical Reads
--- ------------ ----- ----------------
------------- ------------------
D 1,152 .1
142,920 2.98 382,394,492
D 2,304 .2
285,840 2.33 298,972,131
D 3,456 .3
428,760 1.86 239,074,321
D 4,608 .4
571,680 1.57 201,646,142
D 5,760 .5
714,600 1.37 176,236,302
D 6,912 .6
857,520 1.24 158,743,971
D 8,064 .7
1,000,440 1.14 146,615,654
D 9,216 .8
1,143,360 1.08 138,340,654
D 10,368 .9
1,286,280 1.03 132,670,066
D 11,504 1.0
1,427,215 1.00 128,392,612
D 11,520 1.0
1,429,200 1.00 128,332,181
D 12,672 1.1
1,572,120 0.97 124,103,353
D 13,824 1.2
1,715,040 0.94 120,735,111
D 14,976 1.3
1,857,960 0.92 117,580,127
D 16,128 1.4
2,000,880 0.90 115,412,206
D 17,280 1.5
2,143,800 0.89 113,700,321
D 18,432 1.6
2,286,720 0.87 112,251,482
D 19,584 1.7
2,429,640 0.86 110,945,944
D 20,736 1.8
2,572,560 0.86 109,799,930
D 21,888 1.9
2,715,480 0.85 108,861,596
D 23,040 2.0
2,858,400 0.84 108,001,454
-------------------------------------------------------------
Buffer wait Statistics for DB: 123456
Instance: 123456 Snaps: 7147 -7148
-> ordered by wait time desc, waits desc
Tot
Wait Avg
Class Waits Time (s)
Time (ms)
------------------ ----------- ----------
---------
data block 7,736
12 2
undo block 14
0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: 123456
Instance: 123456 Snaps: 7147 -7148
-> B: Begin snap E: End snap (rows
dentified with B or E contain data
which is absolute i.e. not diffed over
the interval)
-> PGA cache hit % - percentage of W/A (WorkArea)
data processed only in-memory
-> Auto PGA Target - actual workarea memory
target
-> W/A PGA Used - amount of memory used
for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA
memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea
memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea
memory under manual control
PGA Cache Hit % W/A MB Processed Extra W/A
MB Read/Written
--------------- ----------------
-------------------------
80.1
17,856 4,425
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A
PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M)
Mem Mem Mem Bound(K)
- --------- --------- ---------- ----------
------ ------ ------ ----------
B 2,500 1,750 1,152.0
72.4 6.3 100.0 .0 102,400
E 2,500 1,711 1,525.8
215.3 14.1 100.0 .0 102,400
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: 123456
Instance: 123456 Snaps: 7147 -7148
-> Optimal Executions are purely in-memory
operations
Low High
Optimal Optimal Total Execs Optimal
Execs 1-Pass Execs M-Pass Execs
------- ------- --------------
------------- ------------ ------------
8K 16K
137,525 137,525 0 0
16K 32K
5,249 5,249 0 0
32K 64K
554 554 0 0
64K 128K
249 249 0 0
128K 256K
84 84 0 0
256K 512K
77 77 0 0
512K 1024K
2 2 0 0
1M 2M
2 2 0 0
4M 8M
2 2 0 0
8M 16M 4
4 0 0
32M
64M 124 0 124 0
64M 128M 170
170 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: 123456
Instance: 123456 End Snap: 7148
-> When using Auto Memory Mgmt, minimally
choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd
Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A
MB Read/ Cache Overalloc
Est (MB) Factr Processed Written
to Disk Hit % Count
---------- ------- ----------------
---------------- -------- ----------
313 0.1 20,713,301.0
28,507,495.2 42.0 90,739
625 0.3 20,713,301.0
19,126,354.7 52.0 30,710
1,250 0.5 20,713,301.0
9,357,796.3 69.0 0
1,875 0.8 20,713,301.0
3,183,647.8 87.0 0
2,500 1.0 20,713,301.0
3,174,702.3 87.0 0
3,000 1.2 20,713,301.0
390,713.4 98.0 0
3,500 1.4 20,713,301.0
390,713.4 98.0 0
4,000 1.6 20,713,301.0
390,713.4 98.0 0
4,500 1.8 20,713,301.0
390,713.4 98.0 0
5,000 2.0 20,713,301.0
390,713.4 98.0 0
7,500 3.0 20,713,301.0
390,713.4 98.0 0
10,000 4.0 20,713,301.0
390,713.4 98.0 0
15,000 6.0 20,713,301.0
390,713.4 98.0 0
20,000 8.0 20,713,301.0
390,713.4 98.0 0
-------------------------------------------------------------
Enqueue activity for DB: 123456 Instance:
123456 Snaps: 7147 -7148
-> Enqueue stats gathered prior to 9i
should not be compared with 9i data
-> ordered by Wait Time desc, Waits desc
Avg Wt Wait
Eq Requests Succ Gets Failed
Gets Waits Time (ms) Time (s)
-- ------------ ------------ -----------
----------- ------------- ------------
SQ 109 109
0 1 .00 0
-------------------------------------------------------------
Rollback Segment Stats for DB: 123456
Instance: 123456 Snaps: 7147 -7148
->A high value for "Pct Waits" suggests
more rollback segments may be required
->RBS stats may not be accurate between
begin and end snaps when using Auto Undo,
managment, as RBS may be dynamically
created and dropped as needed
Trans Table Pct Undo Bytes
RBS No Gets Waits
Written Wraps Shrinks Extends
------ -------------- -------
--------------- -------- -------- --------
0 15.0 0.00
2,276 0 0 0
1 1,595.0 0.00
4,631,490 8 0 6
2 672.0 0.00
1,954,156 2 0 2
3 482.0 0.00
1,355,942 2 0 2
4 1,571.0 0.00
4,165,504 5 0 5
5 4.0 0.00
0 0 0 0
6 1,203.0 0.00
2,942,544 0 0 0
7 9.0 0.00
0 0 0 0
8 10.0 0.00
0 0 0 0
9 2,917.0 0.00
8,666,180 9 0 9
10 5.0 0.00
0 0 0 0
11 1,187.0 0.00
3,338,090 3 0 3
12 10.0 0.00
0 0 0 0
13 767.0 0.00
1,967,000 2 0 2
14 5.0 0.00
0 0 0 0
15 9.0 0.00
0 0 0 0
16 2,084.0 0.00
5,536,268 4 14 3
17 1,939.0 0.00
5,296,376 6 0 5
18 1,788.0 0.00
5,015,070 5 0 5
19 5.0 0.00
0 0 0 0
20 545.0 0.00
1,580,750 0 0 0
21 8.0 0.00
0 0 0 0
22 19.0 0.00
274 0 0 0
23 206.0 0.00
510,844 0 0 0
24 9.0 0.00
0 0 0 0
25 1,281.0 0.00
3,791,880 4 0 4
26 99.0 0.00
125,890 0 0 0
27 965.0 0.00
2,953,018 4 0 4
28 5.0 0.00
0 0 0 0
29 4.0 0.00
0 0 0 0
30 5.0 0.00
0 0 0 0
31 3.0 0.00
0 0 0 0
32 1,467.0 0.00
3,900,342 4 0 4
33 3.0 0.00
0 0 0 0
34 3.0 0.00
0 0 0 0
35 669.0 0.00
1,564,950 2 0 2
36 4.0 0.00
0 0 0 0
37 762.0 0.00
1,953,148 2 0 2
38 1,794.0 0.00
5,096,810 6 0 6
39 949.0 0.00
2,915,802 3 0 3
40 3.0 0.00
0 0 0 0
41 1,264.0 0.00
3,885,568 4 0 4
42 2,673.0 0.00
7,642,590 8 0 8
-------------------------------------------------------------
Rollback Segment Storage for DB: 123456
Instance: 123456 Snaps: 7147 -7148
->Optimal Size should be larger than Avg
Active
RBS No Segment Size Avg Active
Optimal Size Maximum Size
------ --------------- ---------------
--------------- ---------------
0 385,024
30,222 385,024
1 48,422,912
855,593 1,610,735,616
2 38,920,192
1,676,662 4,227,981,312
3 32,628,736
1,166,869 1,979,834,368
4 42,065,920
1,065,676 700,571,648
5 50,454,528
107,180,162 4,227,981,312
6 65,134,592
3,179,902 788,652,032
7 30,531,584
1,495,937 861,003,776
8 77,717,504
309,629,788 805,429,248
9 47,308,800
1,065,134 1,543,626,752
10 56,745,984
141,157,532 4,227,981,312
11 37,871,616
1,042,808 56,745,984
12 59,891,712
1,107,001 59,891,712
13 64,086,016
8,964,603 218,226,688
14 16,900,096
990,490 31,580,160
15 203,546,624
73,950,674 268,558,336
16 75,620,352
198,762,883 873,586,688
17 25,288,704
1,022,220 26,337,280
18 12,705,792
719,004 12,705,792
19 46,260,224
1,053,860 46,260,224
20 75,620,352
1,055,814 75,620,352
21 31,580,160
2,013,934 31,580,160
22 35,774,464
1,012,863 35,774,464
23 36,823,040
1,018,045 36,823,040
24 8,511,488
0 8,511,488
25 32,628,736
1,004,618 32,628,736
26 59,891,712
2,153,698 159,506,432
27 26,337,280
964,202 26,337,280
28 11,657,216
687,041 11,657,216
29 13,754,368 199,228
13,754,368
30 20,111,360
884,335 20,111,360
31 7,462,912
460,498 7,462,912
32 29,483,008
987,066 29,483,008
33 10,477,568
620,105 10,477,568
34 12,574,720
701,514 12,574,720
35 26,206,208
960,354 26,206,208
36 6,283,264
395,521 6,283,264
37 32,497,664
1,001,871 32,497,664
38 36,691,968
1,017,812 36,691,968
39 20,963,328
899,174 20,963,328
40 27,254,784
1,783,204 27,254,784
41 34,594,816
1,010,597 34,594,816
42 32,497,664
1,001,690 32,497,664
-------------------------------------------------------------
Undo Segment Summary for DB: 123456
Instance: 123456 Snaps: 7147 -7148
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired
Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired
Released, eU - expired reUsed
Undo Undo Num Max Qry
Max Tx Snapshot Out of uS/uR/uU/
TS# Blocks Trans Len (s)
Concurcy Too Old Space eS/eR/eU
---- -------------- -------------- --------
---------- -------- ------ -------------
1 8,381 20,088,635
1,444 40 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: 123456 Instance:
123456 Snaps: 7147 -7148
-> ordered by Time desc
Undo Num Max Qry
Max Tx Snap Out of uS/uR/uU/
End Time Blocks Trans Len (s)
Concy Too Old Space eS/eR/eU
------------ ------------ -------- -------
-------- ------- ------ -------------
04-Dec 11:24 8,381 ########
1,444 40 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: 123456 Instance:
123456 Snaps: 7147 -7148
->"Get Requests", "Pct Get Miss" and "Avg
Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are
for no-wait latch get requests
->"Pct Misses" for both should be very
close to 0.0
Pct Avg Wait Pct
Get
Get Slps Time NoWait NoWait
Latch Requests
Miss /Miss (s) Requests Miss
------------------------ --------------
------ ------ ------ ------------ ------
Consistent RBA 4,794
0.0 0 0
FAL request queue 2
0.0 0 0
FIB s.o chain latch 16
0.0 0 0
FOB s.o list latch 7,243
0.0 0.0 0 0
SQL memory manager latch 1
0.0 0 199 0.0
SQL memory manager worka 49,053
0.0 0 0
active checkpoint queue 3,988
0.0 0 0
alert log latch 5
0.0 0 0
archive control 602
0.0 0 0
archive process latch 18
0.0 0 0
cache buffer handles 508,309
0.0 0.0 0 0
cache buffers chains 177,035,441
0.0 0.0 0 5,918,917 0.0
cache buffers lru chain 80,975
0.2 0.0 0 5,827,837 0.3
channel handle pool latc 245
0.0 0 0
channel operations paren 54,342,835
0.0 0 0
checkpoint queue latch 312,078
0.0 0.0 0 43,946 0.0
child cursor hash table 12,142
0.0 0 0
commit callback allocati 4
0.0 0 0
dml lock allocation 58,601
0.0 0.0 0 0
dummy allocation 247
0.0 0 0
enqueue hash chains 1,195,607
0.1 0.0 0 0
enqueues 1,303,904
0.2 0.0 0 0
event group latch 126
0.0 0 0
global tx hash mapping 247
0.0 0 0
granule operation 1
0.0 0 0
hash table column usage 0
0 13,560 0.0
internal temp table obje 12
0.0 0 0
job_queue_processes para 10
0.0 0 0
ktm global data 4
0.0 0 0
kwqit: protect wakeup ti 19
0.0 0 0
latch for background adj 1
0.0 0 0
lgwr LWN SCN 4,794
0.0 0 0
library cache 5,266,357
0.3 0.0 1 5,729 3.0
library cache load lock 988
0.0 0 0
library cache pin 3,677,062
0.0 0.0 0 0
library cache pin alloca 841,339
0.0 0.0 0 0
list of block allocation 1,814
0.0 0 0
loader state object free 960
0.0 0 0
longop free list parent 9
0.0 0 10 0.0
messages 108,729,083
0.0 0.0 0 0
mostly latch-free SCN 4,807
0.0 0.0 0 0
multiblock read objects 481,398
0.1 0.0 0 0
ncodef allocation latch 10
0.0 0 0
post/wait queue 6,198
0.0 0 4,074 0.0
process allocation 126
0.0 0 126 0.0
Latch Activity for DB: 123456 Instance:
123456 Snaps: 7147 -7148
->"Get Requests", "Pct Get Miss" and "Avg
Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are
for no-wait latch get requests
->"Pct Misses" for both should be very
close to 0.0
Pct Avg Wait Pct
Get
Get Slps Time NoWait NoWait
Latch Requests
Miss /Miss (s) Requests Miss
------------------------ --------------
------ ------ ------ ------------ ------
process group creation 245
0.0 0 0
redo allocation 690,945
0.1 0.0 0 0
redo copy
0 0 681,374 0.0
redo writing 18,797
0.0 0 0
resmgr group change latc 553,592
0.0 0.0 0 0
resmgr:actses active lis 247
0.0 0 0
resmgr:actses change gro 125
0.0 0 0
resmgr:actses change sta 1
0.0 0 0
resmgr:plan CPU method 1
0.0 0 0
resmgr:resource group CP 3
0.0 0 0
resmgr:schema config 553,696
0.0 0.0 0 202 0.0
row cache enqueue latch 291,858
0.0 0.0 0 0
row cache objects 296,853
0.0 0.0 0 449 0.0
sequence cache 9,867
0.0 0.0 0 0
session allocation 370,684
0.0 0.2 0 0
session idle bit 3,503,095
0.0 0.0 0 0
session switching 40
0.0 0 0
session timer 202
0.0 0 0
shared pool 545,531,896
0.1 0.0 1 0
sim partition latch
0 0 559 0.0
simulator hash latch 6,177,083
0.0 0.0 0 0
simulator lru latch 258,888
0.0 0.0 0 28,370 0.9
sort extent pool 561
0.0 0 0
spilled msgs queues list 19
0.0 0 0
temp lob duration state 8
0.0 0 0
transaction allocation 1,649
0.0 0 0
transaction branch alloc 167
0.0 0 0
undo global data 59,543
0.0 0.0 0 10 0.0
user lock 506
0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: 123456
Instance: 123456 Snaps: 7147 -7148
-> ordered by misses desc
Get Spin &
Latch Name
Requests Misses Sleeps Sleeps 1->4
-------------------------- --------------
----------- ----------- ------------
shared pool
545,531,896 733,944 325 733619/325/0
/0/0
cache buffers chains
177,035,441 42,993 260 0/0/0/0/0
library cache
5,266,357 17,929 726 17204/724/1/
0/0
library cache pin
3,677,062 759 3 756/3/0/0/0
multiblock read objects
481,398 292 2 290/2/0/0/0
cache buffers lru chain
80,975 201 3 198/3/0/0/0
session allocation
370,684 89 17 72/17/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: 123456 Instance:
123456 Snaps: 7147 -7148
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name
Where Misses Sleeps Sleeps
------------------------
-------------------------------- ---------- ---------- --------
cache buffers chains kcbgtcr: kslbegin
excl 0 169 178
cache buffers chains kcbzib:
multi-block read: nowait 0 30 0
cache buffers chains kcbrls:
kslbegin 0 28 56
cache buffers chains kcbzgb: scan from
tail. nowait 0 17 0
cache buffers chains kcbgtcr: fast
path 0 8 18
cache buffers chains kcbchg: kslbegin:
bufs not pinne 0 2 0
cache buffers chains
kcbibr 0 2 0
cache buffers chains kcbget: pin
buffer 0 2 0
cache buffers chains kcbget:
exchange 0 1 0
cache buffers chains
kcbzwb 0 1 1
cache buffers lru chain
kcbbic2 13 1 1
cache buffers lru chain kcbbiop: lru
scan 485 1 0
cache buffers lru chain kcbzgb: multiple
sets nowait 4,395 1 0
library cache kglpnc:
child 0 333 323
library cache kglupc:
child 0 74 105
library cache kglpin: child:
heap processing 0 73 2
library cache kglhdgc:
child: 0 72 0
library cache kgllkdl: child:
cleanup 0 48 10
library cache kglpndl: child:
before processin 0 45 78
library cache kglobpn:
child: 0 17 4
library cache kglpnp:
child 0 17 64
library cache kglhdgn:
child: 0 15 101
library cache kglpndl: child:
after processing 0 14 0
library cache kgldte: child
0 0 7 18
library cache kgldti:
2child 0 2 1
library cache kglget: child:
KGLDSBRD 0 1 6
library cache
kglic 0 1 8
library cache kglini:
child 0 1 0
library cache pin kglpnc:
child 0 3 1
multiblock read objects kcbzib: mbr
get 0 1 0
multiblock read objects kcbzib: normal mbr
free 0 1 2
session allocation ksufap: active
sessions 0 16 0
session allocation
ksucri 0 1 12
shared pool
kghalo 0 117 0
shared pool kgh: add extent to
quiesced list 0 112 155
shared pool
kghupr1 0 73 170
shared pool kghfrunp: alloc:
wait 0 13 0
shared pool kghfrunp: clatch:
nowait 0 6 0
shared pool kgh: unquiesce
extent 0 5 0
shared pool
kghalp 0 3 0
shared pool kghfen: not perm
alloc class 0 1 0
shared pool kghfrunp: clatch:
wait 0 1 2
shared pool
kghfre 0 1 0
-------------------------------------------------------------
Dictionary Cache Stats for DB: 123456
Instance: 123456 Snaps: 7147 -7148
->"Pct Misses" should be very low (< 2% in
most cases)
->"Cache Usage" is the number of cache
entries being used
->"Pct SGA" is the ratio of usage to
allocated size for that cache
Get
Pct Scan Pct Mod Final
Cache Requests
Miss Reqs Miss Reqs Usage
------------------------- ------------
------ ------- ----- -------- ----------
dc_database_links 35
0.0 0 0 6
dc_histogram_defs 42,803
0.0 0 0 4,572
dc_object_ids 18,452
0.0 0 0 1,056
dc_objects 12,576
5.5 0 0 13,223
dc_profiles 123
0.0 0 0 4
dc_rollback_segments 3,304
0.0 0 6 314
dc_segments 13,690
0.0 0 6 1,005
dc_sequences 107
0.0 0 107 26
dc_tablespace_quotas 4
0.0 0 1 3
dc_tablespaces 1,115
0.0 0 0 72
dc_user_grants 3,083
0.2 0 0 215
dc_usernames 7,679
0.1 0 0 238
dc_users 45,570
0.0 0 3 457
-------------------------------------------------------------
Library Cache Activity for DB: 123456
Instance: 123456 Snaps: 7147 -7148
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss
Requests Miss Reloads dations
--------------- ------------ ------
-------------- ------ ---------- --------
BODY 13,010 0.0
13,010 0.0 0 0
CLUSTER 5
0.0 8 0.0 0 0
INDEX 76
0.0 76 0.0 0 0
PIPE 173
1.2 177 1.1 0 0
SQL AREA 79,528 1.8
1,261,309 0.2 151 0
TABLE/PROCEDURE 299,398 0.3
597,932 0.1 84 0
TRIGGER 2,194
0.0 2,194 0.0 1 0
-------------------------------------------------------------
Shared Pool Advisory for DB: 123456
Instance: 123456 End Snap: 7148
-> Note there is often a 1:Many correlation
between a single logical object
in the Library Cache, and the physical
number of memory objects associated
with it. Therefore comparing the number
of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib
Cache Memory Objects is invalid
Estd
Shared Pool SP Estd
Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache
Cache Time Saved Estd Lib Cache
Estim (M) Factr Size (M) Mem
Obj Saved (s) Factr Mem Obj Hits
----------- ----- ---------- ------------
------------ ------- ---------------
352 .6 334
41,059 1,533,562 1.0 2,571,011
416 .7 398
50,739 1,533,563 1.0 2,571,159
480 .8 462
62,344 1,533,563 1.0 2,571,181
544 .9 526
73,057 1,533,563 1.0 2,571,184
608 1.0 589
83,060 1,533,563 1.0 2,571,184
672 1.1 652
93,132 1,533,563 1.0 2,571,190
736 1.2 715
101,317 1,533,563 1.0 2,571,191
800 1.3 778
110,451 1,533,563 1.0 2,571,191
864 1.4 841
119,213 1,533,563 1.0 2,571,201
928 1.5 904
127,285 1,533,563 1.0 2,571,213
992 1.6 967
134,951 1,533,563 1.0 2,571,219
1,056 1.7 1,015
140,008 1,533,563 1.0 2,571,219
1,120 1.8 1,049
143,396 1,533,563 1.0 2,571,219
1,184 1.9 1,070
144,129 1,533,563 1.0 2,571,219
1,248 2.1 1,070
144,155 1,533,563 1.0 2,571,219
-------------------------------------------------------------
SGA Memory Summary for DB: 123456 Instance:
123456 Snaps: 7147 -7148
SGA regions Size in
Bytes
------------------------------
----------------
Database Buffers
12,062,818,304
Fixed Size
764,208
Redo Buffers
1,585,152
Variable Size
3,154,116,608
----------------
sum
15,219,284,272
-------------------------------------------------------------
SGA breakdown difference for DB: 123456
Instance: 123456 Snaps: 7147 -7148
Pool Name
Begin value End value % Diff
------ ------------------------------
---------------- ---------------- ----------
java free memory
79,433,728 79,433,728 0.00
java memory in use
4,452,352 4,452,352 0.00
large PX msg
pool 983,040 983,040 0.00
large free memory
49,348,608 49,348,608 0.00
shared Checkpoint queue
10,246,400 10,246,400 0.00
shared DML
lock 7,260,072 7,260,072 0.00
shared
FileIdentificatonBlock 899,080 899,080 0.00
shared FileOpenBlock
16,270,720 16,270,720 0.00
shared KGK
heap 26,200 26,200 0.00
shared KGLS
heap 5,676,736 5,495,048 -3.20
shared KGSK scheduler
623,000 623,000 0.00
shared KGSKI
schedule 43,512 43,512 0.00
shared KQR L PO
14,855,536 15,058,320 1.37
shared KQR M
PO 3,576,528 3,527,888 -1.36
shared KQR M
SO 89,808 93,392 3.99
shared KQR S
SO 54,000 55,792 3.32
shared KQR X
PO 569,424 569,424 0.00
shared KSXR receive
buffers 1,034,000 1,034,000 0.00
shared MTTR
advisory 4,517,632 4,517,632 0.00
shared PL/SQL
DIANA 4,324,016 4,185,224 -3.21
shared PL/SQL
MPCODE 6,183,728 6,039,920 -2.33
shared PLS non-lib
hp 2,944 2,944 0.00
shared PX
subheap 73,496 73,496 0.00
shared Temporary Tables State
Ob 1,992,664 1,992,664 0.00
shared UNDO INFO SEGMENTED
ARRAY 1,667,000 1,667,000 0.00
shared VIRTUAL CIRCUITS
17,275,680 17,275,680 0.00
shared XDB Schema
Cac 7,318,136 7,318,136 0.00
shared
branch 3,031,632 3,031,632 0.00
shared
constraints 2,116,280 2,116,280 0.00
shared db_block_hash_buckets
50,520,304 50,520,304 0.00
shared
db_handles 928,000 928,000 0.00
shared dictionary
cache 3,229,952 3,229,952 0.00
shared
dummy 690,024 690,024 0.00
shared enqueue
12,075,416 12,075,416 0.00
shared enqueue
resources 4,972,088 4,972,088 0.00
shared
enqueue_hash 817,680 817,680 0.00
shared
errors 205,968 205,968 0.00
shared event statistics per sess
95,472,000 95,472,000 0.00
shared fixed allocation
callback 960 960 0.00
shared free memory
51,646,744 46,597,640 -9.78
shared joxlod: in
ehe 272,384 272,384 0.00
shared joxs heap
init 480 480 0.00
shared ktlbk state
objects 5,011,600 5,011,600 0.00
shared library cache
204,206,184 204,626,528 0.21
shared miscellaneous
67,544,248 68,650,632 1.64
shared
parameters 157,632 119,312 -24.31
shared qmps
connections 3,604,000 3,604,000 0.00
shared replication session
stats 2,584,000 2,584,000 0.00
shared sessions
22,984,000 22,984,000 0.00
SGA breakdown difference for DB: 123456
Instance: 123456 Snaps: 7147 -7148
Pool Name
Begin value End value % Diff
------ ------------------------------
---------------- ---------------- ----------
shared sim memory hea
11,957,424 11,957,424 0.00
shared sql area
275,298,520 279,168,888 1.41
shared
subheap 102,312 102,312 0.00
shared table
definiti 30,296 28,400 -6.26
shared transaction
15,358,928 15,358,928 0.00
shared trigger
defini 104,592 100,288 -4.12
shared trigger
inform 10,032 11,328 12.92
shared trigger source
872 872 0.00
shared type object
de 9,232 9,232 0.00
buffer_cache
12,062,818,304 12,062,818,304 0.00
fixed_sga
764,208 764,208 0.00
log_buffer 1,573,888 1,573,888 0.00
-------------------------------------------------------------
init.ora Parameters for DB: 123456
Instance: 123456 Snaps: 7147 -7148
End value
Parameter Name Begin
value (if different)
-----------------------------
--------------------------------- --------------
aq_tm_processes 1
audit_file_dest xxx
audit_sys_operations TRUE
audit_trail TRUE
background_dump_dest xxx
blank_trimming TRUE
compatible 9.2.0.0.0
control_file_record_keep_time 280
control_files xxx
core_dump_dest xxx
db_block_size 8192
db_cache_size 12062818304
db_domain world
db_file_multiblock_read_count 32
db_files 800
db_name npcprd
db_writer_processes 4
disk_asynch_io TRUE
dispatchers
(PROTOCOL=TCP) (SERVICE=xxx)
fast_start_mttr_target 300
fast_start_parallel_rollback FALSE
hash_join_enabled FALSE
ifile xxx
instance_name xxx
java_pool_size 83886080
job_queue_processes 10
large_pool_size 50331648
log_archive_dest_1 LOCATION=xxx
log_archive_dest_2 LOCATION=/xxx
log_archive_dest_3 LOCATION=/xxx
log_archive_dest_state_1 ENABLE
log_archive_dest_state_2 ENABLE
log_archive_dest_state_3 DEFER
log_archive_format
arch_%t_%s.arc
log_archive_start TRUE
log_archive_trace 0
max_enabled_roles 80
nls_date_format DD-MON-YYYY
open_cursors 900
open_links 120
optimizer_features_enable 9.2.0
os_authent_prefix
parallel_adaptive_multi_user TRUE
parallel_automatic_tuning TRUE
parallel_max_servers 16
parallel_min_percent 0
parallel_min_servers 4
parallel_threads_per_cpu 2
pga_aggregate_target 2621440000
processes 800
init.ora Parameters for DB: 123456
Instance: 123456 Snaps: 7147 -7148
End value
Parameter Name Begin value
(if different)
-----------------------------
--------------------------------- --------------
query_rewrite_enabled TRUE
remote_dependencies_mode TIMESTAMP
remote_login_passwordfile EXCLUSIVE
resource_manager_plan xxx
session_max_open_files 100
sessions 8500
sga_max_size 15219284272
shared_pool_size 620756992
sort_area_size 2621440
star_transformation_enabled TRUE
timed_statistics TRUE
transactions_per_rollback_seg 4
undo_management AUTO
undo_retention 10800
undo_tablespace xxx
use_indirect_data_buffers FALSE
user_dump_dest xxx
utl_file_dir xxx
-------------------------------------------------------------
End of Report
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
|
|
|
