| |
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: 10.02 (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:
1,816,614.40 6,098.30
Logical reads:
89,851.90 301.63
Block changes:
7,266.68 24.39
Physical reads:
3,227.66 10.84
Physical writes:
1,233.78 4.14
User calls:
4,044.55 13.58
Parses:
154.38 0.52
Hard
parses: 3.64 0.01
Sorts:
330.61 1.11
Logons: 0.22 0.00
Executes:
2,885.80 9.69
Transactions:
297.89
% Blocks changed per Read: 8.09
Recursive Call %: 26.63
Rollback per transaction %: 95.96
Rows per Sort: 1040.70
Instance Efficiency Percentages (Target
100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99
Redo NoWait %: 100.00
Buffer Hit %: 97.39
In-memory Sort %: 99.97
Library Hit %: 99.83
Soft Parse %: 97.65
Execute to Parse %:
94.65 Latch Hit %: 99.68
Parse CPU to Parse Elapsd %: 54.02 %
Non-Parse CPU: 99.32
Shared Pool Statistics Begin
End ------ ------
Memory Usage %: 96.78
96.83
% SQL with executions>1: 45.32
43.95
% Memory for SQL w/exec>1: 58.03
56.82
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
-------------------------------------------- ------------ -----------
--------
db file sequential
read 568,948 4,375 66.15
CPU
time 1,983 29.99
db file scattered
read 106,287 65 .99
log file
sync 7,053 50 .75
log buffer
space 1,717 47 .71
-------------------------------------------------------------
Wait Events for DB: 123456 Instance: 123456
Snaps: 7150 -7151
-> 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
568,948 0 4,375 8 3.2
db file scattered read
106,287 0 65 1 0.6
log file
sync 7,053 0 50 7
0.0
log buffer
space 1,717 0 47 27 0.0
SQL*Net message from dblink
37 0 22 591 0.0
direct path read
12,871 0 18 1 0.1
db file parallel write
618 0 13 21 0.0
log file sequential read
984 0 11 11 0.0
library cache pin
7 3 9 1,286 0.0
buffer busy waits
4,490 0 7 2 0.0
db file parallel read
309 0 5 16 0.0
latch free
1,673 325 2 1 0.0
async disk IO
2,840 0 2 1 0.0
control file parallel write
431 0 1 3 0.0
enqueue
19 0 1 54 0.0
SQL*Net more data to client
151,977 0 1 0 0.8
LGWR wait for redo copy
563 4 1 1 0.0
log file switch completion
39 0 0 12 0.0
direct path write 1,020
0 0 0 0.0
SQL*Net break/reset to clien
416 0 0 0 0.0
control file sequential read
1,232 0 0 0 0.0
log file parallel write
8,263 8,091 0 0 0.0
log file single write
16 0 0 2 0.0
process startup
1 0 0 21 0.0
direct path read (lob)
36 0 0 0 0.0
SQL*Net message to dblink
37 0 0 0 0.0
buffer deadlock
7 7 0 0 0.0
SQL*Net message from client
2,450,615 0 214,735 88 13.7
PX Idle Wait
1,175 1,175 2,350 2,000 0.0
pipe get
179 175 700 3,908 0.0
virtual circuit status
20 20 586 29,297 0.0
wakeup time manager
19 19 570 30,000 0.0
SQL*Net more data from clien
654 0 15 24 0.0
SQL*Net message to client
2,450,615 0 1 0 13.7
-------------------------------------------------------------
Background Wait Events for DB: 123456
Instance: 123456 Snaps: 7150 -7151
-> 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
616 0 13 21 0.0
log file sequential read
984 0 11 11 0.0
direct path read
3,068 0 4 1 0.0
async disk IO
2,840 0 2 1 0.0
control file parallel write
430 0 1 3 0.0
enqueue
1 0 1 963 0.0
LGWR wait for redo copy
563 4 1 1 0.0
db file scattered read
90 0 0 4 0.0
log file parallel write
8,256 8,084 0 0 0.0
db file sequential read
71 0 0 1 0.0
log file single write
16 0 0 2 0.0
direct path write
720 0 0 0 0.0
control file sequential read
590 0 0 0 0.0
process startup
1 0 0 21 0.0
log buffer space
8 0 0 2 0.0
latch free
2 0 0 0 0.0
rdbms ipc message
25,892 17,240 5,966 230 0.1
smon timer
2 2 600 300,000 0.0
pmon timer
202 202 586 2,901 0.0
-------------------------------------------------------------
. . .
Instance Activity Stats for DB: 123456
Instance: 123456 Snaps: 7150 -7151
Statistic Total per Second per
Trans
---------------------------------
------------------ -------------- ------------
CPU used by this
session 198,333 330.0 1.1
CPU used when call
started 198,374 330.1 1.1
CR blocks
created 2,957 4.9 0.0
DBWR buffers
scanned 266,470 443.4 1.5
DBWR checkpoint buffers
written 204,356 340.0 1.1
DBWR
checkpoints 4 0.0 0.0
DBWR free buffers
found 266,432 443.3 1.5
DBWR lru scans
93 0.2 0.0
DBWR make free
requests 93 0.2 0.0
DBWR revisited being-written
buff 0 0.0 0.0
DBWR summed scan depth
266,470 443.4 1.5
DBWR transaction table
writes 100 0.2 0.0
DBWR undo block
writes 69,080 114.9 0.4
Parallel operations downgraded to
0 0.0 0.0
SQL*Net roundtrips to/from client
2,427,484 4,039.1 13.6
SQL*Net roundtrips to/from dblink
47 0.1 0.0
active txn count during
cleanout 68,741 114.4 0.4
background checkpoints
completed 4 0.0 0.0
background checkpoints
started 4 0.0 0.0
background
timeouts 1,265 2.1 0.0
branch node
splits 14 0.0 0.0
buffer is not pinned count
25,816,192 42,955.4 144.2
buffer is pinned count
87,025,454 144,801.1 486.1
bytes received via SQL*Net from c
315,280,030 524,592.4 1,761.0
bytes received via SQL*Net from d
11,821 19.7 0.1
bytes sent via SQL*Net to client
710,726,648 1,182,573.5 3,969.9
bytes sent via SQL*Net to dblink
12,036 20.0 0.1
calls to get snapshot scn: kcmgss
2,100,631 3,495.2 11.7
calls to kcmgas
42,867 71.3 0.2
calls to kcmgcs
72,357 120.4 0.4
change write
time 6,668 11.1 0.0
cleanout - number of ktugct
calls 69,837 116.2 0.4
cluster key scan block
gets 16,341 27.2 0.1
cluster key
scans 8,128 13.5 0.1
commit cleanout failures: block
l 0 0.0 0.0
commit cleanout failures:
buffer 4,955 8.2 0.0
commit cleanout failures: callbac
2,675 4.5 0.0
commit cleanout failures:
cannot 8 0.0 0.0
commit cleanout failures: hot bac
0 0.0 0.0
commit
cleanouts 203,358 338.4 1.1
commit cleanouts successfully
com 195,720 325.7 1.1
commit txn count during
cleanout 21,874 36.4 0.1
consistent
changes 25,984 43.2 0.2
consistent gets
49,593,708 82,518.7 277.0
consistent gets - examination
19,464,027 32,386.1 108.7
current blocks converted for
CR 0 0.0 0.0
cursor
authentications 308 0.5 0.0
data blocks consistent reads -
un 25,900 43.1 0.1
db block changes
4,367,273 7,266.7 24.4
db block gets
4,405,293 7,329.9 24.6
deferred (CURRENT) block
cleanout 89,733 149.3 0.5
Instance Activity Stats for DB: 123456
Instance: 123456 Snaps: 7150 -7151
Statistic Total per Second per
Trans
---------------------------------
------------------ -------------- ------------
dirty buffers
inspected 72 0.1 0.0
enqueue
conversions 745 1.2 0.0
enqueue releases
1,962,502 3,265.4 11.0
enqueue requests
1,962,495 3,265.4 11.0
enqueue
timeouts 0 0.0 0.0
enqueue
waits 16 0.0 0.0
exchange deadlocks
7 0.0 0.0
execute count
1,734,365 2,885.8 9.7
free buffer
inspected 2,350 3.9 0.0
free buffer requested
1,518,574 2,526.8 8.5
hot buffers moved to head of
LRU 338,881 563.9 1.9
immediate (CR) block cleanout
app 5,557 9.3 0.0
immediate (CURRENT) block cleanou
32,067 53.4 0.2
index fast full scans
(full) 798 1.3 0.0
index fetch by key
2,817,840 4,688.6 15.7
index scans kdiixs1
6,262,938 10,420.9 35.0
leaf node 90-10
splits 61 0.1 0.0
leaf node
splits 2,970 4.9 0.0
logons
cumulative 131 0.2 0.0
messages
received 8,905 14.8 0.1
messages
sent 8,905 14.8 0.1
no buffer to keep pinned
count 0 0.0 0.0
no work - consistent read gets
23,695,478 39,426.8 132.4
opened cursors
cumulative 76,615 127.5 0.4
parse count
(failures) 97 0.2 0.0
parse count
(hard) 2,185 3.6 0.0
parse count
(total) 92,782 154.4 0.5
parse time cpu
1,351 2.3 0.0
parse time
elapsed 2,501 4.2 0.0
physical reads
1,939,822 3,227.7 10.8
physical reads
direct 531,883 885.0 3.0
physical reads direct
(lob) 36 0.1 0.0
physical
writes 741,500 1,233.8 4.1
physical writes
direct 537,034 893.6 3.0
physical writes non checkpoint
736,637 1,225.7 4.1
pinned buffers
inspected 2,175 3.6 0.0
prefetched
blocks 735,317 1,223.5 4.1
prefetched blocks aged out
before 39 0.1 0.0
process last non-idle time
149,599,768,883 248,918,084.7 835,608.2
recursive
calls 882,456 1,468.3 4.9
recursive cpu
usage 111,279 185.2 0.6
redo blocks written
2,202,134 3,664.1 12.3
redo buffer allocation
retries 1,740 2.9 0.0
redo entries
2,343,003 3,898.5 13.1
redo log space
requests 39 0.1 0.0
redo log space wait
time 47 0.1 0.0
redo ordering
marks 3 0.0 0.0
redo size
1,091,785,252 1,816,614.4 6,098.3
redo synch
time 5,114 8.5 0.0
redo synch
writes 7,062 11.8 0.0
redo wastage
1,983,996 3,301.2 11.1
Instance Activity Stats for DB: 123456
Instance: 123456 Snaps: 7150 -7151
Statistic Total per Second per
Trans
---------------------------------
------------------ -------------- ------------
redo write
time 6,075 10.1 0.0
redo writer latching
time 52 0.1 0.0
redo writes
8,159 13.6 0.1
rollback changes - undo records
a 1,178 2.0 0.0
rows fetched via callback
2,257,493 3,756.2 12.6
session connect time
149,599,768,883 248,918,084.7 835,608.2
session logical reads
54,000,990 89,851.9 301.6
session pga memory
47,913,536 79,723.0 267.6
session uga memory
41,663,280 69,323.3 232.7
session uga memory max
9,094,517,624 15,132,308.9 50,798.6
shared hash latch upgrades - no w
6,202,999 10,321.1 34.7
shared hash latch upgrades -
wait 1,790 3.0 0.0
sorts
(disk) 66 0.1 0.0
sorts
(memory) 198,630 330.5 1.1
sorts (rows)
206,783,146 344,065.1 1,155.0
summed dirty queue
length 111 0.2 0.0
switch current to new
buffer 25,886 43.1 0.1
table fetch by rowid
49,085,557 81,673.1 274.2
table fetch continued
row 447,404 744.4 2.5
table scan blocks gotten
6,289,478 10,465.0 35.1
table scan rows gotten
343,127,765 570,928.1 1,916.6
table scans (cache
partitions) 2 0.0 0.0
table scans (long
tables) 11 0.0 0.0
table scans (short
tables) 87,400 145.4 0.5
transaction
rollbacks 33 0.1 0.0
transaction tables consistent rea
0 0.0 0.0
transaction tables consistent rea
0 0.0 0.0
user calls
2,430,777 4,044.6 13.6
user
commits 7,237 12.0 0.0
user
rollbacks 171,794 285.9 1.0
workarea executions - onepass
130 0.2 0.0
workarea executions -
optimal 278,674 463.7 1.6
write clones created in backgroun
0 0.0 0.0
write clones created in foregroun
323 0.5 0.0
-------------------------------------------------------------
. . .
Buffer Pool Statistics for DB: 123456
Instance: 123456 Snaps: 7150 -7151
-> 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 97.4 54,746,567
1,396,413 206,245 0 0 4,881
-------------------------------------------------------------
Instance Recovery Stats for DB: 123456
Instance: 123456 Snaps: 7150 -7151
-> 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 27 41222 454012
442368 442368 1131577
E 300 31 52876 442083
442368 442368 1366902
-------------------------------------------------------------
Buffer Pool Advisory for DB: 123456
Instance: 123456 End Snap: 7151
-> 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 396,571,233
D 2,304 .2
285,840 2.33 310,016,650
D 3,456 .3
428,760 1.86 247,924,478
D 4,608 .4
571,680 1.57 209,136,872
D 5,760 .5
714,600 1.37 182,801,899
D 6,912 .6
857,520 1.24 164,654,121
D 8,064 .7
1,000,440 1.14 152,061,901
D 9,216 .8
1,143,360 1.08 143,454,688
D 10,368 .9
1,286,280 1.03 137,575,270
D 11,504 1.0
1,427,215 1.00 133,139,478
D 11,520 1.0
1,429,200 1.00 133,076,790
D 12,672 1.1
1,572,120 0.97 128,691,089
D 13,824 1.2
1,715,040 0.94 125,196,307
D 14,976 1.3
1,857,960 0.92 121,922,155
D 16,128 1.4
2,000,880 0.90 119,663,650
D 17,280 1.5
2,143,800 0.89 117,887,942
D 18,432 1.6
2,286,720 0.87 116,379,898
D 19,584 1.7
2,429,640 0.86 115,024,183
D 20,736 1.8
2,572,560 0.86 113,833,108
D 21,888 1.9
2,715,480 0.85 112,858,656
D 23,040 2.0
2,858,400 0.84 111,966,259
-------------------------------------------------------------
Buffer wait Statistics for DB: 123456
Instance: 123456 Snaps: 7150 -7151
-> ordered by wait time desc, waits desc
Tot
Wait Avg
Class Waits Time (s)
Time (ms)
------------------ ----------- ----------
---------
data block 4,789
7 1
undo block 71
0 0
1st level bmb 8
0 3
undo header 6
0 0
segment header 1
0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: 123456
Instance: 123456 Snaps: 7150 -7151
-> 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
--------------- ----------------
-------------------------
79.1
19,461 5,143
%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,701 1,288.9
117.7 9.1 100.0 .0 102,400
E 2,500 1,687 1,314.9
155.2 11.8 100.0 .0 102,400
-------------------------------------------------------------
PGA Aggr Target Histogram for DB: 123456
Instance: 123456 Snaps: 7150 -7151
-> Optimal Executions are purely in-memory
operations
Low High
Optimal Optimal Total Execs Optimal
Execs 1-Pass Execs M-Pass Execs
------- ------- --------------
------------- ------------ ------------
8K 16K
230,854 230,854 0 0
16K 32K
45,231 45,231 0 0
32K 64K
1,365 1,365 0 0
64K 128K
180 180 0 0
128K 256K
452 452 0 0
256K 512K
35 35 0 0
512K 1024K
1 1 0 0
2M 4M
8 8 0 0
4M 8M
4 4 0 0
8M 16M
12 12 0 0
32M
64M 129 0 129 0
64M 128M 155
155 0 0
512M 1024M 1
0 1 0
-------------------------------------------------------------
PGA Memory Advisory for DB: 123456
Instance: 123456 End Snap: 7151
-> 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,773,882.2
28,614,489.1 42.0 91,104
625 0.3 20,773,882.2
19,223,130.1 52.0 30,864
1,250 0.5 20,773,882.2
9,384,356.6 69.0 0
1,875 0.8 20,773,882.2
3,192,034.6 87.0 0
2,500 1.0 20,773,882.2
3,183,089.0 87.0 0
3,000 1.2 20,773,882.2
391,786.0 98.0 0
3,500 1.4 20,773,882.2
391,786.0 98.0 0
4,000 1.6 20,773,882.2
391,786.0 98.0 0
4,500 1.8 20,773,882.2
391,786.0 98.0 0
5,000 2.0 20,773,882.2
391,786.0 98.0 0
7,500 3.0 20,773,882.2
391,786.0 98.0 0
10,000 4.0 20,773,882.2
391,786.0 98.0 0
15,000 6.0 20,773,882.2
391,786.0 98.0 0
20,000 8.0 20,773,882.2
391,786.0 98.0 0
-------------------------------------------------------------
Enqueue activity for DB: 123456 Instance:
123456 Snaps: 7150 -7151
-> 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)
-- ------------ ------------ -----------
----------- ------------- ------------
CF 363 363
0 1 987.00 1
TX 13,878 13,880
0 14 4.79 0
SQ 205 205
0 1 .00 0
-------------------------------------------------------------
Rollback Segment Stats for DB: 123456
Instance: 123456 Snaps: 7150 -7151
->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 3.0 0.00
0 0 0 0
1 3,133.0 0.03
10,251,272 10 0 10
2 2,303.0 0.00
7,687,452 4 0 4
3 1,062.0 0.00
3,392,160 4 0 3
4 2,051.0 0.00
6,640,728 7 0 7
5 1,516.0 0.00
4,490,716 5 0 5
6 25.0 0.00
0 0 0 0
7 103.0 0.00
21,178 0 0 0
8 1,400.0 0.00
4,058,866 0 0 0
9 2,904.0 0.00
9,700,594 2 0 2
10 16,880.0 0.00
117,097,010 16 0 16
11 1,903.0 0.00
6,720,982 8 0 8
12 15,366.0 0.00
113,053,980 17 0 17
13 3,089.0 0.00
10,650,642 1 0 1
14 21.0 0.00
0 0 0 0
15 30.0 0.00
0 0 0 0
16 237.0 0.00
620,904 0 0 0
17 2,226.0 0.00
7,395,562 10 0 6
18 2,258.0 0.00
7,330,524 8 0 7
19 2,260.0 0.00
7,562,908 8 0 8
20 15,262.0 0.00
112,015,694 14 0 14
21 31.0 0.00
0 0 0 0
22 30.0 0.00
0 0 0 0
23 36.0 0.00
0 0 0 0
24 39.0 0.00
0 0 0 0
25 2,014.0 0.00
6,279,404 7 0 7
26 2,254.0 0.00
7,993,620 1 0 0
27 5.0 0.00
0 0 0 0
28 1,466.0 0.00
5,175,140 5 0 5
29 2,802.0 0.00
9,128,352 9 0 9
30 11.0 0.00
0 0 0 0
31 3.0 0.00
0 0 0 0
32 1,728.0 0.00
5,887,152 6 0 6
33 3,471.0 0.00
12,616,100 14 0 14
34 3.0 0.00
0 0 0 0
35 17,150.0 0.00
118,145,660 41 0 41
36 3.0 0.00
0 0 0 0
37 3,697.0 0.00
12,733,614 14 0 14
38 2,114.0 0.00
7,954,128 9 0 9
39 5.0 0.00
0 0 0 0
40 3.0 0.00
0 0 0 0
41 2,046.0 0.00
6,803,798 7 0 7
42 4.0 0.00
0 0 0 0
43 4.0 0.00
1,052 0 0 0
44 1,312.0 0.00
4,327,630 5 0 5
-------------------------------------------------------------
Rollback Segment Storage for DB: 123456
Instance: 123456 Snaps: 7150 -7151
->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 61,005,824
999,518 1,610,735,616
2 75,620,352
1,078,156 4,227,981,312
3 43,114,496
945,706 1,979,834,368
4 58,843,136
1,051,742 700,571,648
5 59,891,712
34,609,720 4,227,981,312
6 66,183,168
3,700,772 788,652,032
7 35,774,464
1,241,147 861,003,776
8 77,717,504
252,393,963 805,429,248
9 92,397,568
2,444,808 1,543,626,752
10 193,060,864
59,828,097 4,227,981,312
11 59,891,712
1,152,799 59,891,712
12 184,672,256
53,017,799 184,672,256
13 100,786,176
6,823,932 218,226,688
14 16,900,096
990,490 31,580,160
15 203,546,624
73,950,674 268,558,336
16 92,397,568
162,591,770 873,586,688
17 46,260,224
918,311 46,260,224
18 30,531,584
999,105 30,531,584
19 59,891,712
1,049,916 59,891,712
20 209,838,080
53,503,116 209,838,080
21 31,580,160
2,013,934 31,580,160
22 35,774,464
1,012,863 35,774,464
23 49,405,952
1,039,950 49,405,952
24 8,511,488
0 8,511,488
25 55,697,408
1,044,243 55,697,408
26 59,891,712
3,338,330 159,506,432
27 29,483,008
987,066 29,483,008
28 29,483,008
988,279 29,483,008
29 27,385,856
1,005,955 27,385,856
30 20,111,360
884,335 20,111,360
31 7,462,912
460,498 7,462,912
32 49,405,952
1,040,263 49,405,952
33 33,546,240
1,111,236 33,546,240
34 12,574,720
701,514 12,574,720
35 167,763,968
147,535,220 167,763,968
36 6,283,264
395,521 6,283,264
37 59,760,640
1,045,554 59,760,640
38 59,760,640
1,045,543 59,760,640
39 26,206,208
960,354 26,206,208
40 27,254,784
1,783,204 27,254,784
41 52,420,608
1,042,239 52,420,608
42 36,691,968
1,017,812 36,691,968
43 9,428,992
581,242 9,428,992
44 19,914,752
883,753 19,914,752
-------------------------------------------------------------
Undo Segment Summary for DB: 123456
Instance: 123456 Snaps: 7150 -7151
-> 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 43,298 20,132,631
2,263 29 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Undo Segment Stats for DB: 123456 Instance:
123456 Snaps: 7150 -7151
-> 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:54 43,298 ########
2,263 29 0 0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity for DB: 123456 Instance:
123456 Snaps: 7150 -7151
->"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 8,165
0.0 0 0
FAL request queue 8
0.0 0 0
FIB s.o chain latch 70
0.0 0 0
FOB s.o list latch 6,672
0.0 0 0
SQL memory manager latch 1
0.0 0 193 0.0
SQL memory manager worka 54,976
0.0 0.0 0 0
active checkpoint queue 1,495
0.2 0.0 0 0
alert log latch 20
0.0 0 0
archive control 261
0.0 0 0
archive process latch 36
5.6 0.5 0 0
cache buffer handles 340,828
0.0 0.0 0 0
cache buffers chains 100,446,295
0.1 0.0 0 2,393,142 0.0
cache buffers lru chain 231,007
0.1 0.0 0 2,017,747 0.5
channel handle pool latc 270
0.0 0 0
channel operations paren 793
0.0 0 0
checkpoint queue latch 519,585
0.0 0.0 0 220,487 0.1
child cursor hash table 17,182
0.0 0 0
dml lock allocation 116,216
0.0 0.0 0 0
dummy allocation 272
0.0 0 0
enqueue hash chains 3,924,464
2.9 0.0 0 0
enqueues 3,965,960
4.5 0.0 0 0
event group latch 130
0.0 0 0
global tx hash mapping 89
0.0 0 0
hash table column usage 127
0.0 0 9,148 0.0
internal temp table obje 8
0.0 0 0
job workq parent latch 1
0.0 0 0
job_queue_processes para 9
0.0 0 0
ktm global data 2
0.0 0 0
kwqit: protect wakeup ti 19
0.0 0 0
lgwr LWN SCN 8,179
0.0 0.0 0 0
library cache 7,149,229
0.4 0.0 1 7,694 3.4
library cache load lock 1,338
0.0 0 0
library cache pin 5,503,478
0.1 0.0 0 0
library cache pin alloca 802,800
0.0 0.0 0 0
list of block allocation 10,430
0.0 0.0 0 0
loader state object free 972
0.0 0 0
longop free list parent 6
0.0 0 6 0.0
messages 61,532
0.2 0.0 0 0
mostly latch-free SCN 8,321
0.7 0.0 0 0
multiblock read objects 311,007
0.2 0.0 0 0
ncodef allocation latch 10
0.0 0 0
post/wait queue 11,277
0.0 0 8,706 0.1
process allocation 130
0.0 0 130 0.0
process group creation 268
0.0 0 0
redo allocation 2,359,370
2.3 0.0 0 0
Latch Activity for DB: 123456 Instance:
123456 Snaps: 7150 -7151
->"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
------------------------ --------------
------ ------ ------ ------------ ------
redo copy
0 0 2,341,557 0.0
redo writing 26,422
0.0 0.0 0 0
resmgr group change latc 1,885,097
0.3 0.0 0 0
resmgr:actses active lis 270
0.0 0 0
resmgr:actses change gro 126
0.0 0 0
resmgr:actses change sta 7
0.0 0 0
resmgr:plan CPU method 239
0.0 0 0
resmgr:resource group CP 525
0.4 0.0 0 0
resmgr:schema config 1,883,994
0.0 0 1,263 0.0
row cache enqueue latch 279,462
0.0 0.0 0 0
row cache objects 287,274
0.0 0.0 0 648 0.0
sequence cache 18,406
0.1 0.0 0 0
session allocation 374,347
0.0 0.2 0 0
session idle bit 5,050,157
0.0 0.0 0 0
session switching 40
0.0 0 0
session timer 202
0.0 0 0
shared pool 3,107,447
0.2 0.0 0 0
sim partition latch
0 0 691 0.0
simulator hash latch 3,216,603
0.0 0.0 0 0
simulator lru latch 135,442
0.0 0.0 0 12,632 0.8
sort extent pool 544
0.0 0 0
spilled msgs queues list 19
0.0 0 0
temp lob duration state 2
0.0 0 0
transaction allocation 3,350
0.0 0 0
transaction branch alloc 65
0.0 0 0
undo global data 141,171
0.0 0.0 0 0
user lock 552
0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: 123456
Instance: 123456 Snaps: 7150 -7151
-> ordered by misses desc
Get Spin &
Latch Name
Requests Misses Sleeps Sleeps 1->4
-------------------------- --------------
----------- ----------- ------------
enqueues
3,965,960 179,594 33 179561/33/0/
0/0
enqueue hash chains
3,924,464 114,517 20 114497/20/0/
0/0
redo allocation
2,359,370 55,300 23 55277/23/0/0
/0
cache buffers chains 100,446,295
55,023 318 0/0/0/0/0
library cache
7,149,229 31,981 1,185 30796/1185/0
/0/0
shared pool
3,107,447 5,816 29 5787/29/0/0/
0
resmgr group change latch
1,885,097 5,534 24 5510/24/0/0/
0
library cache pin
5,503,478 3,086 1 3085/1/0/0/0
session idle bit
5,050,157 693 13 0/0/0/0/0
session allocation
374,347 61 12 49/12/0/0/0
archive process latch
36 2 1 1/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: 123456 Instance:
123456 Snaps: 7150 -7151
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name
Where Misses Sleeps Sleeps
------------------------
-------------------------------- ---------- ---------- --------
archive process latch
kcrrpa 0 1 0
cache buffers chains kcbgtcr: kslbegin
excl 0 187 157
cache buffers chains kcbrls: kslbegin
0 67 104
cache buffers chains kcbgcur:
kslbegin 0 13 10
cache buffers chains kcbgtcr: fast
path 0 12 28
cache buffers chains kcbget: pin
buffer 0 9 6
cache buffers chains kcbzgb: scan from
tail. nowait 0 7 0
cache buffers chains kcbget:
exchange 0 6 2
cache buffers chains kcbzib:
multi-block read: nowait 0 6 0
cache buffers chains
kcbzwb 0 3 2
cache buffers chains kcbchg: kslbegin:
bufs not pinne 0 2 3
cache buffers chains kcbchg: kslbegin:
call CR func 0 2 1
cache buffers chains kcbzib: finish
free bufs 0 2 4
cache buffers chains
kcbnew 0 2 0
cache buffers chains kcbget: exchange
rls 0 1 2
enqueue hash chains
ksqrcl 0 19 2
enqueue hash chains
ksqgtl3 0 1 18
enqueues
ksqdel 0 21 6
enqueues ksqgel: create
enqueue 0 12 26
library cache kglpnc:
child 0 522 593
library cache kglpin: child:
heap processing 0 141 1
library cache kglupc:
child 0 122 211
library cache kglpndl: child:
before processin 0 110 114
library cache kglhdgc:
child: 0 106 3
library cache kgllkdl: child:
cleanup 0 45 2
library cache kglpnp:
child 0 43 155
library cache kglpndl: child:
after processing 0 32 0
library cache kglobpn:
child: 0 22 0
library cache kglhdgn:
child: 0 21 65
library cache
kglic 0 9 16
library cache kgldte: child
0 0 4 17
library cache kgldti:
2child 0 1 0
library cache
kglpin 0 1 3
library cache pin
kglupc 0 1 0
redo allocation
kcrfwr 0 21 22
redo allocation kcrfwi: more
space 0 1 1
redo allocation kcrfwr: redo
allocation 0 0 1 0
resmgr group change latc
kskincrstat1 0 24 24
session allocation ksufap: active
sessions 0 12 0
session idle bit ksupuc: clear
busy 0 10 6
session idle bit ksupuc: set
busy 0 3 7
shared pool
kghalo 0 16 2
shared pool
kghupr1 0 7 25
shared pool kghfrunp: alloc:
wait 0 6 0
shared pool kghfrunp: clatch:
nowait 0 4 0
shared pool kghfrunp: clatch:
wait 0 2 3
-------------------------------------------------------------
Dictionary Cache Stats for DB: 123456
Instance: 123456 Snaps: 7150 -7151
->"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 9
0.0 0 0 6
dc_global_oids 54
0.0 0 0 20
dc_histogram_defs 48,739
0.0 0 0 4,562
dc_object_ids 20,470
0.0 0 0 1,063
dc_objects 12,261
4.6 0 0 13,801
dc_profiles 135
0.0 0 0 4
dc_rollback_segments 2,909
0.0 0 0 314
dc_segments 13,429
0.1 0 0 1,006
dc_sequences 206
0.0 0 206 25
dc_tablespace_quotas 4
0.0 0 1 3
dc_tablespaces 2,061
0.0 0 0 68
dc_user_grants 5,662
0.0 0 0 220
dc_usernames 7,928
0.1 0 0 246
dc_users 33,287
0.0 0 7 466
-------------------------------------------------------------
Library Cache Activity for DB: 123456
Instance: 123456 Snaps: 7150 -7151
->"Pct Misses" should be very low
Get
Pct Pin Pct Invali-
Namespace Requests Miss
Requests Miss Reloads dations
--------------- ------------ ------
-------------- ------ ---------- --------
BODY 7,120
0.0 7,120 0.0 1 0
CLUSTER 6
0.0 8 0.0 0 0
INDEX 76
0.0 76 0.0 0 0
PIPE 66
1.5 70 1.4 0 0
SQL AREA 81,986 2.5
1,980,968 0.2 185 0
TABLE/PROCEDURE 290,943 0.3
845,688 0.1 169 0
TRIGGER 1,970
0.0 1,970 0.1 2 0
-------------------------------------------------------------
Shared Pool Advisory for DB: 123456
Instance: 123456 End Snap: 7151
-> 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 337
39,058 2,460,632 1.0 3,836,117
416 .7 400
46,632 2,460,637 1.0 3,836,826
480 .8 462
54,529 2,460,637 1.0 3,837,105
544 .9 525
63,837 2,460,647 1.0 3,837,192
608 1.0 590
74,834 2,460,650 1.0 3,837,223
672 1.1 653
85,805 2,460,650 1.0 3,837,238
736 1.2 716
95,196 2,460,650 1.0 3,837,239
800 1.3 779
105,732 2,460,650 1.0 3,837,239
864 1.4 843
114,716 2,460,650 1.0 3,837,249
928 1.5 906
123,815 2,460,650 1.0 3,837,261
992 1.6 969
132,807 2,460,650 1.0 3,837,270
1,056 1.7 1,032
140,841 2,460,650 1.0 3,837,276
1,120 1.8 1,096
149,069 2,460,650 1.0 3,837,276
1,184 1.9 1,138
153,402 2,460,650 1.0 3,837,276
1,248 2.1 1,186
156,730 2,460,650 1.0 3,837,276
-------------------------------------------------------------
SGA Memory Summary for DB: 123456 Instance:
123456 Snaps: 7150 -7151
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: 7150 -7151
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 4,935,968 4,402,344 -10.81
shared KGSK scheduler
623,000 623,000 0.00
shared KGSKI
schedule 43,512 43,512 0.00
shared KQR L PO
15,680,976 15,717,840 0.24
shared KQR M PO
3,538,696 3,543,856 0.15
shared KQR M
SO 95,440 96,464 1.07
shared KQR S
SO 56,816 57,072 0.45
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,183,200 3,859,392 -7.74
shared PL/SQL
MPCODE 6,322,376 6,447,072 1.97
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 206,856 166,896 -19.32
shared event statistics per sess
95,472,000 95,472,000 0.00
shared fixed allocation
callback 960 960 0.00
shared free memory
30,221,176 29,829,608 -1.30
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
207,085,696 207,344,336 0.12
shared miscellaneous
70,470,832 71,023,848 0.78
shared
parameters 128,352 141,216 10.02
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: 7150 -7151
Pool Name
Begin value End value % Diff
------ ------------------------------
---------------- ---------------- ----------
shared sim memory hea
11,957,424 11,957,424 0.00
shared sql area
290,896,456 291,209,768 0.11
shared
subheap 102,312 102,312 0.00
shared table
definiti 28,832 25,176 -12.68
shared transaction
15,358,928 15,358,928 0.00
shared trigger
defini 102,736 92,568 -9.90
shared trigger
inform 10,128 7,080 -30.09
shared trigger
source 872 872 0.00
shared type object
de 9,264 9,264 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: 7150 -7151
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 xxx
db_writer_processes 4
disk_asynch_io TRUE
dispatchers
(PROTOCOL=TCP) (SERVICE=xxxx)
fast_start_mttr_target 300
fast_start_parallel_rollback FALSE
hash_join_enabled FALSE
ifile /xxxx
instance_name npcprd
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: 7150 -7151
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. |
|
|
|
and include the URL for the page.
