Oracle Database Discussions

my users have been complaining that the application is very slow. What do i need to look for in this report.

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
XXXX 629101424 XXXX 1 9.2.0.6.0 NO scheduprod-s
ol

Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 4527 12-Mar-07 15:02:05 81 231.9
End Snap: 4531 12-Mar-07 15:42:01 81 234.1
Elapsed: 39.93 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 304M Std Block Size: 8K
Shared Pool Size: 608M Log Buffer: 512K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 3,891.02 2,878.32
Logical reads: 865.79 640.45
Block changes: 22.12 16.36
Physical reads: 13.86 10.25
Physical writes: 0.61 0.45
User calls: 231.43 171.20
Parses: 48.95 36.21
Hard parses: 8.44 6.25
Sorts: 4.25 3.14
Logons: 0.15 0.11
Executes: 51.42 38.04
Transactions: 1.35

% Blocks changed per Read: 2.55 Recursive Call %: 17.42
Rollback per transaction %: 2.53 Rows per Sort: 55.75

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.13 In-memory Sort %: 100.00
Library Hit %: 91.94 Soft Parse %: 82.75
Execute to Parse %: 4.81 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 98.26 % Non-Parse CPU: 50.19


Shared Pool Statistics Begin End
------ ------
Memory Usage %: 97.40 97.69
% SQL with executions>1: 18.64 31.84
% Memory for SQL w/exec>1: 17.60 31.65

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 342 50.98
log file parallel write 7,798 138 20.59
log file sync 3,587 128 19.03
db file parallel write 193 29 4.39
control file parallel write 790 21 3.20
-------------------------------------------------------------
^LWait Events for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 7,798 0 138 18 2.4
log file sync 3,587 0 128 36 1.1
db file parallel write 193 0 29 153 0.1
control file parallel write 790 0 21 27 0.2
SQL*Net more data to client 111,847 0 6 0 34.5
db file sequential read 432 0 2 5 0.1
db file scattered read 2,419 0 1 0 0.7
latch free 698 0 1 1 0.2
process startup 14 0 1 47 0.0
direct path read (lob) 14,912 0 1 0 4.6
SQL*Net break/reset to clien 920 0 0 0 0.3
control file sequential read 822 0 0 0 0.3
SQL*Net message from client 454,457 0 35,768 79 140.3
virtual circuit status 40,421 81 4,724 117 12.5
wakeup time manager 79 79 2,248 28461 0.0
jobq slave wait 264 252 765 2897 0.1
SQL*Net message to client 454,457 0 1 0 140.3
SQL*Net more data from clien 328 0 0 0 0.1
-------------------------------------------------------------
^LBackground Wait Events for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 7,798 0 138 18 2.4
db file parallel write 193 0 29 153 0.1
control file parallel write 790 0 21 27 0.2
process startup 2 0 0 35 0.0
control file sequential read 556 0 0 0 0.2
rdbms ipc reply 3 0 0 1 0.0
latch free 2 0 0 0 0.0
rdbms ipc message 6,444 2,366 13,269 2059 2.0
smon timer 8 8 2,344 ###### 0.0
pmon timer 834 834 2,339 2804 0.3
-------------------------------------------------------------
-------------------------------------------------------------
^LInstance Activity Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 34,169 14.3 10.6
CPU used when call started 33,176 13.9 10.2
CR blocks created 83 0.0 0.0
DBWR buffers scanned 0 0.0 0.0
DBWR checkpoint buffers written 1,459 0.6 0.5
DBWR checkpoints 0 0.0 0.0
DBWR free buffers found 0 0.0 0.0
DBWR lru scans 0 0.0 0.0
DBWR make free requests 0 0.0 0.0
DBWR revisited being-written buff 0 0.0 0.0
DBWR summed scan depth 0 0.0 0.0
DBWR transaction table writes 13 0.0 0.0
DBWR undo block writes 787 0.3 0.2
PX local messages recv'd 0 0.0 0.0
PX local messages sent 0 0.0 0.0
SQL*Net roundtrips to/from client 492,393 205.5 152.0
SQL*Net roundtrips to/from dblink 0 0.0 0.0
active txn count during cleanout 100 0.0 0.0
background checkpoints completed 0 0.0 0.0
background checkpoints started 0 0.0 0.0
background timeouts 2,898 1.2 0.9
branch node splits 0 0.0 0.0
buffer is not pinned count 1,617,141 674.9 499.3
buffer is pinned count 3,219,198 1,343.6 993.9
bytes received via SQL*Net from c 48,664,626 20,310.8 15,024.6
bytes received via SQL*Net from d 0 0.0 0.0
bytes sent via SQL*Net to client 380,182,385 158,673.8 117,376.5
bytes sent via SQL*Net to dblink 0 0.0 0.0
calls to get snapshot scn: kcmgss 370,472 154.6 114.4
calls to kcmgas 5,800 2.4 1.8
calls to kcmgcs 319 0.1 0.1
change write time 91 0.0 0.0
cleanout - number of ktugct calls 178 0.1 0.1
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 15 0.0 0.0
cluster key scan block gets 88,031 36.7 27.2
cluster key scans 44,560 18.6 13.8
commit cleanout failures: block l 0 0.0 0.0
commit cleanout failures: buffer 0 0.0 0.0
commit cleanout failures: callbac 0 0.0 0.0
commit cleanout failures: cannot 0 0.0 0.0
commit cleanouts 16,509 6.9 5.1
commit cleanouts successfully com 16,509 6.9 5.1
commit txn count during cleanout 140 0.1 0.0
consistent changes 162 0.1 0.1
consistent gets 2,042,196 852.3 630.5
consistent gets - examination 578,365 241.4 178.6
cursor authentications 7,954 3.3 2.5
data blocks consistent reads - un 162 0.1 0.1
db block changes 52,993 22.1 16.4
db block gets 47,334 19.8 14.6
deferred (CURRENT) block cleanout 4,180 1.7 1.3
dirty buffers inspected 0 0.0 0.0
enqueue conversions 117 0.1 0.0
enqueue releases 32,269 13.5 10.0
enqueue requests 32,270 13.5 10.0
^LInstance Activity Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531


Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
enqueue timeouts 0 0.0 0.0
enqueue waits 0 0.0 0.0
execute count 123,202 51.4 38.0
free buffer inspected 0 0.0 0.0
free buffer requested 19,766 8.3 6.1
hot buffers moved to head of LRU 23 0.0 0.0
immediate (CR) block cleanout app 15 0.0 0.0
immediate (CURRENT) block cleanou 5,664 2.4 1.8
index fast full scans (full) 6 0.0 0.0
index fetch by key 214,924 89.7 66.4
index scans kdiixs1 554,891 231.6 171.3
leaf node 90-10 splits 9 0.0 0.0
leaf node splits 61 0.0 0.0
logons cumulative 362 0.2 0.1
messages received 3,953 1.7 1.2
messages sent 3,953 1.7 1.2
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 894,670 373.4 276.2
opened cursors cumulative 116,168 48.5 35.9
parse count (failures) 9 0.0 0.0
parse count (hard) 20,231 8.4 6.3
parse count (total) 117,273 49.0 36.2
parse time cpu 17,018 7.1 5.3
parse time elapsed 17,319 7.2 5.4
physical reads 33,199 13.9 10.3
physical reads direct 0 0.0 0.0
physical reads direct (lob) 15,100 6.3 4.7
physical writes 1,459 0.6 0.5
physical writes direct 0 0.0 0.0
physical writes direct (lob) 0 0.0 0.0
physical writes non checkpoint 496 0.2 0.2
pinned buffers inspected 0 0.0 0.0
prefetch clients - default 0 0.0 0.0
prefetched blocks 15,247 6.4 4.7
prefetched blocks aged out before 0 0.0 0.0
process last non-idle time 2,396 1.0 0.7
recursive calls 116,941 48.8 36.1
recursive cpu usage 1,523 0.6 0.5
redo blocks written 20,871 8.7 6.4
redo buffer allocation retries 0 0.0 0.0
redo entries 29,618 12.4 9.1
redo log space requests 0 0.0 0.0
redo log space wait time 0 0.0 0.0
redo ordering marks 0 0.0 0.0
redo size 9,322,888 3,891.0 2,878.3
redo synch time 13,084 5.5 4.0
redo synch writes 3,587 1.5 1.1
redo wastage 1,028,916 429.4 317.7
redo write time 14,064 5.9 4.3
redo writer latching time 0 0.0 0.0
redo writes 3,899 1.6 1.2
rollback changes - undo records a 422 0.2 0.1
rollbacks only - consistent read 162 0.1 0.1
rows fetched via callback 350,472 146.3 108.2
session connect time 0 0.0 0.0
session logical reads 2,074,430 865.8 640.5
^LInstance Activity Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session pga memory 14,446,752 6,029.5 4,460.3
session pga memory max 24,898,104 10,391.5 7,687.0
session uga memory 17,184,593,312 7,172,200.9 5,305,524.3
session uga memory max 28,405,696 11,855.5 8,769.9
shared hash latch upgrades - no w 546,341 228.0 168.7
shared hash latch upgrades - wait 1 0.0 0.0
sorts (disk) 0 0.0 0.0
sorts (memory) 10,174 4.3 3.1
sorts (rows) 567,241 236.7 175.1
summed dirty queue length 0 0.0 0.0
switch current to new buffer 1,334 0.6 0.4
table fetch by rowid 1,958,222 817.3 604.6
table fetch continued row 1,912 0.8 0.6
table scan blocks gotten 143,317 59.8 44.3
table scan rows gotten 7,318,367 3,054.4 2,259.5
table scans (long tables) 15 0.0 0.0
table scans (short tables) 2,029 0.9 0.6
transaction rollbacks 421 0.2 0.1
user calls 554,503 231.4 171.2
user commits 3,157 1.3 1.0
user rollbacks 82 0.0 0.0
workarea executions - multipass 0 0.0 0.0
workarea executions - onepass 0 0.0 0.0
workarea executions - optimal 14,430 6.0 4.5
write clones created in backgroun 0 0.0 0.0
write clones created in foregroun 1 0.0 0.0
-------------------------------------------------------------
^LTablespace IO Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
USERS
17,588 7 0.1 1.9 69 0 0 0.0
UNDOTBLSPCE
0 0 0.0 802 0 0 0.0
PERFSTAT
136 0 14.2 1.0 315 0 0 0.0
SCHEDWIN_INDX
20 0 10.5 1.0 164 0 0 0.0
INDX
14 0 11.4 1.0 96 0 0 0.0
SYSTEM
0 0 0.0 13 0 0 0.0
-------------------------------------------------------------
File IO Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531
->ordered by Tablespace, File

Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
INDX /opt/u01/oradata/XXXX/dbf/indx01.dbf
14 0 11.4 1.0 96 0 0

PERFSTAT /opt/u01/oradata/XXXX/dbf/perfstat01.dbf
136 0 14.2 1.0 315 0 0

SCHEDWIN_INDX /opt/u01/oradata/XXXX/dbf/schedwin_indx01.dbf
10 0 12.0 1.0 94 0 0
/opt/u02/oradata/XXXX/dbf/schedwin_indx02.dbf
10 0 9.0 1.0 70 0 0

SYSTEM /opt/u01/oradata/XXXX/dbf/system01.dbf
0 0 13 0 0

UNDOTBLSPCE /opt/u01/oradata/XXXX/dbf/undotblspce01.dbf
0 0 802 0 0

USERS /opt/u01/oradata/XXXX/dbf/users01.dbf
11,608 5 0.1 1.4 43 0 0
/opt/u02/oradata/XXXX/dbf/users02.dbf
2,369 1 0.5 3.4 14 0 0
/opt/u03/oradata/XXXX/dbf/users03.dbf
3,611 2 0.2 2.4 12 0 0

-------------------------------------------------------------
^LBuffer Pool Statistics for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> 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 37,715 99.1 2,073,831 18,097 1,459 0 0 0
-------------------------------------------------------------

Instance Recovery Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> 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 160 34 1278 19692 19707 110592 19707
E 160 30 1056 17051 16555 110592 16555
-------------------------------------------------------------

Buffer Pool Advisory for DB: XXXX Instance: XXXX End Snap: 4531
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate (default block size first)

Size for Size Buffers for Est Physical Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D 32 .1 3,970 2.53 37,533,655
D 64 .2 7,940 2.07 30,675,635
D 96 .3 11,910 1.81 26,843,986
D 128 .4 15,880 1.67 24,744,049
D 160 .5 19,850 1.61 23,889,520
D 192 .6 23,820 1.57 23,290,550
D 224 .7 27,790 1.36 20,129,947
D 256 .8 31,760 1.16 17,216,468
D 288 .9 35,730 1.05 15,577,531
D 304 1.0 37,715 1.00 14,849,589
D 320 1.1 39,700 0.96 14,224,396
D 352 1.2 43,670 0.89 13,166,810
D 384 1.3 47,640 0.85 12,628,194
D 416 1.4 51,610 0.84 12,417,548
D 448 1.5 55,580 0.82 12,242,048
D 480 1.6 59,550 0.82 12,197,717
D 512 1.7 63,520 0.82 12,148,615
D 544 1.8 67,490 0.81 12,085,500
D 576 1.9 71,460 0.81 12,032,229
D 608 2.0 75,430 0.81 11,972,157
D 640 2.1 79,400 0.80 11,902,712
-------------------------------------------------------------
^LPGA Aggr Target Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> 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
--------------- ---------------- -------------------------
100.0 219 0

%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 245 200 40.2 0.3 .7 11.8 88.2 12,544
E 245 200 40.8 0.3 .7 11.8 88.2 12,544
-------------------------------------------------------------

PGA Aggr Target Histogram for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> Optimal Executions are purely in-memory operations

Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
8K 16K 14,257 14,257 0 0
16K 32K 124 124 0 0
32K 64K 62 62 0 0
64K 128K 14 14 0 0
128K 256K 8 8 0 0
256K 512K 5 5 0 0
512K 1024K 4 4 0 0
1M 2M 32 32 0 0
2M 4M 2 2 0 0
4M 8M 2 2 0 0
-------------------------------------------------------------

PGA Memory Advisory for DB: XXXX Instance: XXXX End Snap: 4531
-> 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
---------- ------- ---------------- ---------------- -------- ----------
31 0.1 70,206.2 6,260.3 92.0 5
61 0.3 70,206.2 4,168.3 94.0 0
123 0.5 70,206.2 3,168.1 96.0 0
184 0.8 70,206.2 2,757.2 96.0 0
245 1.0 70,206.2 1,869.3 97.0 0
294 1.2 70,206.2 1,125.7 98.0 0
343 1.4 70,206.2 1,002.5 99.0 0
392 1.6 70,206.2 930.4 99.0 0
441 1.8 70,206.2 888.9 99.0 0
490 2.0 70,206.2 888.9 99.0 0
735 3.0 70,206.2 635.4 99.0 0
980 4.0 70,206.2 428.8 99.0 0
1,470 6.0 70,206.2 377.1 99.0 0
1,960 8.0 70,206.2 204.8 100.0 0
-------------------------------------------------------------
^LRollback Segment Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531
->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 13.0 0.00 0 0 0 0
1 856.0 0.00 224,282 0 0 0
2 965.0 0.00 473,740 1 0 0
3 1,304.0 0.00 565,018 1 0 1
4 859.0 0.00 258,906 1 0 0
11 886.0 0.00 345,162 0 0 0
12 1,332.0 0.00 546,236 1 0 0
13 841.0 0.00 229,950 1 0 0
14 797.0 0.00 215,582 0 0 0
15 1,252.0 0.00 349,772 2 1 1
16 850.0 0.00 222,894 1 0 0
-------------------------------------------------------------
^LRollback Segment Storage for DB: XXXX Instance: XXXX Snaps: 4527 -4531
->Optimal Size should be larger than Avg Active

RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 385,024 0 385,024
1 22,142,976 11,545,361 100,786,176
2 4,317,184 680,223 51,503,104
3 4,317,184 684,561 142,729,216
4 3,268,608 685,593 50,454,528
11 11,657,216 3,215,066 167,895,040
12 4,317,184 726,453 13,754,368
13 4,317,184 667,922 27,385,856
14 4,317,184 738,701 49,405,952
15 9,560,064 642,634 84,008,960
16 5,365,760 694,846 15,851,520
-------------------------------------------------------------
^LUndo Segment Summary for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> 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
---- -------------- ---------- -------- ---------- -------- ------ -------------
13 754 8,780,184 149 1 0 0 0/0/0/0/0/0
-------------------------------------------------------------


Undo Segment Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> 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
------------ ------------ -------- ------- -------- ------- ------ -------------
12-Mar 15:38 166 ######## 68 1 0 0 0/0/0/0/0/0
12-Mar 15:28 204 ######## 3 1 0 0 0/0/0/0/0/0
12-Mar 15:18 214 ######## 3 1 0 0 0/0/0/0/0/0
12-Mar 15:08 170 ######## 149 1 0 0 0/0/0/0/0/0
-------------------------------------------------------------
^LLatch Activity for DB: XXXX Instance: XXXX Snaps: 4527 -4531
->"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 3,899 0.0 0 0
FOB s.o list latch 113 0.0 0 0
SQL memory manager latch 4 0.0 0 790 0.0
SQL memory manager worka 66,516 0.0 0 0
active checkpoint queue 980 0.0 0 0
archive control 89 0.0 0 0
archive process latch 48 0.0 0 0
cache buffer handles 392 0.0 0 0
cache buffers chains 3,696,307 0.0 0.0 0 34,306 0.0
cache buffers lru chain 21,217 0.0 0.0 0 21,384 0.0
channel handle pool latc 106 0.0 0 0
channel operations paren 1,773 0.0 0 0
checkpoint queue latch 48,252 0.0 0 1,219 0.0
child cursor hash table 144,268 0.0 0.0 0 0
dml lock allocation 10,464 0.0 0 0
dummy allocation 724 0.0 0 0
enqueue hash chains 64,677 0.0 0.0 0 0
enqueues 77,340 0.0 0.2 0 0
event group latch 52 0.0 0 0
global tx hash mapping 21 0.0 0 0
hash table column usage 126 0.0 0 491,356 0.0
job workq parent latch 0 0 8 0.0
job_queue_processes para 43 0.0 0 0
kmcptab latch 79 0.0 0 0
kmcpvec latch 0 0 79 0.0
ktm global data 8 0.0 0 0
kwqit: protect wakeup ti 79 0.0 0 0
lgwr LWN SCN 4,217 0.0 0.0 0 0
library cache 1,798,405 0.3 0.0 0 47,204 0.9
library cache load lock 2,480 0.0 0 0
library cache pin 879,035 0.0 0.0 0 0
library cache pin alloca 603,279 0.0 0.0 0 0
list of block allocation 381 0.0 0 0
loader state object free 27,796 0.0 0 0
messages 18,381 0.0 0.0 0 0
mostly latch-free SCN 4,220 0.1 0.0 0 0
multiblock read objects 5,328 0.0 0 0
ncodef allocation latch 40 0.0 0 0
post/wait queue 5,523 0.0 0 3,587 0.0
process allocation 52 0.0 0 52 0.0
process group creation 106 0.0 0 0
redo allocation 37,748 0.0 0 0
redo copy 0 0 29,635 0.0
redo writing 14,340 0.0 0 0
row cache enqueue latch 11,874,838 0.0 0.0 0 0
row cache objects 11,876,412 0.0 0.0 0 81 0.0
sequence cache 1,113 0.0 0 0
session allocation 117,333 0.0 0.0 0 0
session idle bit 1,141,301 0.0 0.0 0 0
session switching 40 0.0 0 0
^LLatch Activity for DB: XXXX Instance: XXXX Snaps: 4527 -4531
->"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
------------------------ -------------- ------ ------ ------ ------------ ------
session timer 834 0.0 0 0
shared pool 1,804,311 0.2 0.1 1 0
sim partition latch 0 0 8 0.0
simulator hash latch 110,923 0.0 0 0
simulator lru latch 71 0.0 0 1,975 0.0
sort extent pool 48 0.0 0 0
spilled msgs queues list 79 0.0 0 0
transaction allocation 15,341 0.0 0 0
transaction branch alloc 61 0.0 0 0
undo global data 14,952 0.0 0 0
user lock 1,400 0.0 0 0
virtual circuit buffers 413,970 0.0 0.0 0 0
virtual circuit queues 187,775 0.0 0.0 0 0
virtual circuits 80,454 0.0 0 0
-------------------------------------------------------------
^LLatch Sleep breakdown for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> ordered by misses desc

Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
library cache 1,798,405 4,608 221 4390/215/3/0
/0
shared pool 1,804,311 3,662 477 3196/455/11/
0/0
enqueues 77,340 5 1 4/1/0/0/0
-------------------------------------------------------------
^LLatch Miss Sources for DB: XXXX Instance: XXXX Snaps: 4527 -4531
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
enqueues ksqgel: create enqueue 0 1 1
library cache kgllkdl: child: cleanup 0 14 45
library cache kglupc: child 0 9 37
library cache kglic 0 7 4
library cache kglpin: child: heap proces 0 7 2
library cache kglhdgc: child: 0 5 10
library cache kglpndl: child: after proc 0 5 2
library cache kglobpn: child: 0 3 9
library cache kglpndl: child: before pro 0 3 9
library cache kgldti: 2child 0 2 0
library cache kglpnc: child 0 2 53
library cache kglhdgn: child: 0 1 30
shared pool kghalo 0 302 127
shared pool kghfrunp: alloc: wait 0 124 0
shared pool kghfrunp: clatch: nowait 0 89 0
shared pool kghfrunp: clatch: wait 0 74 10
shared pool kghupr1 0 47 339
shared pool kghalp 0 4 4
-------------------------------------------------------------
^LDictionary Cache Stats for DB: XXXX Instance: XXXX Snaps: 4527 -4531
->"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_histogram_defs 2,118,905 0.0 0 0 2,547
dc_object_ids 3,216,369 0.0 0 0 701
dc_objects 22,189 0.2 0 0 456
dc_profiles 349 0.0 0 0 1
dc_rollback_segments 350 0.0 0 0 22
dc_segments 505,804 0.0 0 0 1,085
dc_sequences 18 0.0 0 18 2
dc_tablespaces 30,016 0.0 0 0 12
dc_user_grants 969 0.0 0 0 22
dc_usernames 21,334 0.0 0 0 7
dc_users 19,245 0.0 0 0 23
-------------------------------------------------------------


Library Cache Activity for DB: XXXX Instance: XXXX Snaps: 4527 -4531
->"Pct Misses" should be very low

Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 64 0.0 64 0.0 0 0
CLUSTER 340 0.6 348 1.1 0 0
INDEX 237 0.0 237 0.0 0 0
SQL AREA 116,941 6.6 391,824 10.0 11,529 2
TABLE/PROCEDURE 47,075 0.2 108,992 1.0 732 0
TRIGGER 746 1.5 746 1.5 0 0
-------------------------------------------------------------
^LShared Pool Advisory for DB: XXXX Instance: XXXX End Snap: 4531
-> 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 375 15,062 574,744 1.0 97,562,016
416 .7 439 17,218 576,603 1.0 97,833,828
480 .8 503 19,153 578,104 1.0 98,135,338
544 .9 566 21,093 579,653 1.0 98,617,796
608 1.0 619 22,788 581,195 1.0 99,127,166
672 1.1 681 30,538 583,092 1.0 99,741,869
736 1.2 735 34,290 587,030 1.0 100,967,607
800 1.3 798 38,022 594,101 1.0 103,269,753
864 1.4 858 42,002 602,185 1.0 106,482,793
928 1.5 922 44,110 606,329 1.0 108,314,799
992 1.6 985 48,321 608,336 1.0 109,245,477
1,056 1.7 1,049 50,582 609,325 1.0 109,697,087
1,120 1.8 1,113 53,649 610,059 1.0 110,009,531
1,184 1.9 1,177 60,950 610,680 1.1 110,251,066
1,248 2.1 1,248 65,824 611,135 1.1 110,424,882
-------------------------------------------------------------
^LSGA Memory Summary for DB: XXXX Instance: XXXX Snaps: 4527 -4531

SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 318,767,104
Fixed Size 732,784
Redo Buffers 811,008
Variable Size 771,751,936
----------------
sum 1,092,062,832
-------------------------------------------------------------


SGA breakdown difference for DB: XXXX Instance: XXXX Snaps: 4527 -4531

Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 33,554,432 33,554,432 0.00
large free memory 64,089,912 63,913,256 -0.28
large session heap 3,018,952 3,195,608 5.85
shared 1M buffer 2,098,176 2,098,176 0.00
shared Checkpoint queue 513,280 513,280 0.00
shared FileOpenBlock 1,191,104 1,191,104 0.00
shared KGK heap 7,000 7,000 0.00
shared KGLS heap 2,993,944 3,009,616 0.52
shared KQR L PO 1,312,072 1,322,312 0.78
shared KQR L SO 156,688 156,688 0.00
shared KQR M PO 2,065,256 2,065,256 0.00
shared KQR M SO 86,616 86,616 0.00
shared KQR S SO 8,216 8,216 0.00
shared KQR X PO 2,576 2,576 0.00
shared KSXR pending messages que 853,952 853,952 0.00
shared KSXR receive buffers 1,034,000 1,034,000 0.00
shared MTTR advisory 74,672 74,672 0.00
shared PL/SQL DIANA 616,680 616,880 0.03
shared PL/SQL MPCODE 764,312 611,264 -20.02
shared PLS non-lib hp 2,088 2,088 0.00
shared PX subheap 24,336 24,336 0.00
shared XDB Schema Cac 5,927,720 5,927,720 0.00
shared dictionary cache 3,229,952 3,229,952 0.00
shared errors 384 384 0.00
shared event statistics per sess 1,923,040 1,923,040 0.00
shared fixed allocation callback 472 472 0.00
shared free memory 17,476,144 15,506,552 -11.27
shared joxs heap init 4,240 4,240 0.00
shared library cache 68,887,280 65,624,984 -4.74
shared message pool freequeue 771,984 771,984 0.00
shared miscellaneous 49,162,528 49,205,880 0.09
shared parameters 55,784 35,760 -35.90
shared sim memory hea 328,304 328,304 0.00
shared sql area 509,408,184 514,745,136 1.05
shared subheap 100,040 100,040 0.00
shared table definiti 5,104 3,648 -28.53
shared trigger defini 544 544 0.00
shared trigger inform 1,800 1,800 0.00
shared trigger source 168 168 0.00
buffer_cache 318,767,104 318,767,104 0.00
fixed_sga 732,784 732,784 0.00
log_buffer 787,456 787,456 0.00
-------------------------------------------------------------
^Linit.ora Parameters for DB: XXXX Instance: XXXX Snaps: 4527 -4531

End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 1
background_dump_dest /opt/oracle/admin/XXXX/bdump
compatible 9.2.0.0.0
control_files /opt/u01/oradata/XXXX/ctl/cont
core_dump_dest /opt/oracle/admin/XXXX/cdump
db_block_size 8192
db_cache_size 318767104
db_domain
db_file_multiblock_read_count 8
db_name XXXX
dispatchers (PROTOCOL=TCP)
fast_start_mttr_target 160
hash_join_enabled TRUE
instance_name XXXX
java_pool_size 33554432
job_queue_processes 10
large_pool_size 67108864
log_archive_start TRUE
open_cursors 300
oracle_trace_enable TRUE
pga_aggregate_target 256901120
processes 150
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
shared_pool_size 637534208
sort_area_size 524288
star_transformation_enabled FALSE
timed_statistics TRUE
trace_enabled FALSE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBLSPCE
user_dump_dest /opt/oracle/admin/XXXX/udump
-------------------------------------------------------------

End of Report

Message was edited by:
pinjam