Home » RDBMS Server » Server Administration » Performance tunning
| Performance tunning [message #58806] |
Wed, 01 October 2003 09:17  |
Michael Humphrey
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
All,
I am an Oracle dba, Oracle E-business sweet admin, Unix Admin, Programmer and jack of all trades, but its hard being a master at all. Can someone give me some suggesstions on how to improve performance for my oracle database based on the following statspack report:
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
PRD 1554723292 prd 1 8.1.7.4.0 NO orafin2
Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 2053 30-Sep-03 09:01:25 147
End Snap: 2063 01-Oct-03 10:06:50 147
Elapsed: 1,505.42 (mins)
Cache Sizes
~~~~~~~~~~~
db_block_buffers: 120000 log_buffer: 33554432
db_block_size: 8192 shared_pool_size: 300000000,
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 25,821.90 19,552.21
Logical reads: 8,615.42 6,523.55
Block changes: 221.07 167.39
Physical reads: 46.58 35.27
Physical writes: 6.90 5.23
User calls: 111.02 84.06
Parses: 26.48 20.05
Hard parses: 0.29 0.22
Sorts: 29.34 22.21
Logons: 0.46 0.35
Executes: 120.88 91.53
Transactions: 1.32
% Blocks changed per Read: 2.57 Recursive Call %: 77.30
Rollback per transaction %: 9.72 Rows per Sort: 20.17
Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 99.46 In-memory Sort %: 100.00
Library Hit %: 99.40 Soft Parse %: 98.90
Execute to Parse %: 78.10 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 92.62 % Non-Parse CPU: 95.73
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.73 94.82
% SQL with executions>1: 84.64 82.47
% Memory for SQL w/exec>1: 74.90 73.81
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
queue messages 13,413 13,523,502 68.39
PL/SQL lock timer 4,409 4,516,157 22.84
db file sequential read 1,295,137 354,398 1.79
buffer busy waits 143,754 255,951 1.29
direct path write 56,174 208,174 1.05
-------------------------------------------------------------
Wait Events for DB: PRD Instance: prd Snaps: 2053 -2063
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
queue messages 13,413 13,402 13,523,502 10082 0.1
db file sequential read 1,295,137 0 354,398 3 10.9
buffer busy waits 143,754 1,823 255,951 18 1.2
direct path write 56,174 0 208,174 37 0.5
db file parallel write 25,520 0 164,084 64 0.2
log file parallel write 361,324 0 163,262 5 3.0
db file scattered read 355,724 0 119,855 3 3.0
log file sync 89,555 49 110,010 12 0.8
direct path read 251,281 0 101,233 4 2.1
control file parallel write 37,364 0 92,166 25 0.3
SQL*Net more data to client 4,847,984 0 62,118 0 40.6
latch free 77,486 57,435 55,517 7 0.6
PX Deq: Execution Msg 2,503 0 6,759 27 0.0
log file switch completion 210 7 6,375 304 0.0
direct path read (lob) 11,665 0 5,235 4 0.1
file open 375,540 0 4,310 0 3.1
direct path write (lob) 676 0 4,025 60 0.0
library cache pin 1,481 0 3,718 25 0.0
library cache load lock 151 8 3,460 229 0.0
enqueue 795 5 3,194 40 0.0
log file sequential read 77,280 0 1,679 0 0.6
SQL*Net break/reset to clien 17,512 0 1,530 1 0.1
LGWR wait for redo copy 35,308 4 706 0 0.3
process startup 111 0 674 61 0.0
control file sequential read 62,091 0 557 0 0.5
PX Deq: Signal ACK 348 103 475 14 0.0
log file single write 480 0 465 10 0.0
PX Deq: Msg Fragment 1,918 1 315 2 0.0
refresh controlfile command 1,437 0 167 1 0.0
PX Deq: Parse Reply 286 0 163 6 0.0
single-task message 63 6 160 25 0.0
PX Deq: Join ACK 389 0 139 4 0.0
PX Deq Credit: send blkd 957 0 124 1 0.0
PX Deq: Execute Reply 693 1 103 1 0.0
PX Deq: Table Q Normal 735 0 97 1 0.0
undo segment extension 84,802 84,802 82 0 0.7
local write wait 24 0 47 20 0.0
file identify 985 0 21 0 0.0
library cache lock 3 0 11 37 0.0
db file single write 2 0 4 20 0.0
PX Deq Credit: need buffer 84 0 2 0 0.0
db file parallel read 1 0 2 20 0.0
SQL*Net message from client 9,858,771 0 ########### 580 82.6
wakeup time manager 29,145 28,037 86,738,770 29761 0.2
pipe get 129,712 129,033 54,193,123 4178 1.1
PX Idle Wait 20,720 20,314 4,176,740 2016 0.2
SQL*Net more data from clien 25,076 0 56,199 22 0.2
SQL*Net message to client 9,858,751 0 2,394 0 82.6
-------------------------------------------------------------
Background Wait Events for DB: PRD Instance: prd Snaps: 2053 -2063
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file parallel write 25,520 0 164,084 64 0.2
log file parallel write 361,324 0 163,262 5 3.0
control file parallel write 37,364 0 92,166 25 0.3
direct path read 237,995 0 73,126 3 2.0
db file scattered read 20,954 0 11,122 5 0.2
file open 370,583 0 4,121 0 3.1
log file sequential read 77,280 0 1,679 0 0.6
db file sequential read 9,486 0 817 1 0.1
LGWR wait for redo copy 35,308 4 706 0 0.3
rdbms ipc reply 4 3 671 1678 0.0
log file single write 480 0 465 10 0.0
direct path write 42,982 0 440 0 0.4
control file sequential read 22,131 0 287 0 0.2
enqueue 5 0 243 486 0.0
latch free 47 42 93 20 0.0
file identify 984 0 20 0 0.0
db file single write 2 0 4 20 0.0
refresh controlfile command 4 0 1 3 0.0
buffer busy waits 7 0 0 0 0.0
rdbms ipc message 887,915 114,798 53,789,323 606 7.4
pmon timer 29,340 29,318 9,032,302 3078 0.2
smon timer 294 293 9,008,303 ###### 0.0
-------------------------------------------------------------
-------------------------------------------------------------
Instance Activity Stats for DB: PRD Instance: prd Snaps: 2053 -2063
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 3,798,766 42.1 31.9
CPU used when call started 3,799,130 42.1 31.9
CR blocks created 331,167 3.7 2.8
Cached Commit SCN referenced 9 0.0 0.0
DBWR buffers scanned 128,624 1.4 1.1
DBWR checkpoint buffers written 170,334 1.9 1.4
DBWR checkpoints 147 0.0 0.0
DBWR free buffers found 126,005 1.4 1.1
DBWR lru scans 677 0.0 0.0
DBWR make free requests 686 0.0 0.0
DBWR revisited being-written buff 196 0.0 0.0
DBWR summed scan depth 128,624 1.4 1.1
DBWR transaction table writes 2,520 0.0 0.0
DBWR undo block writes 90,077 1.0 0.8
DFO trees parallelized 56 0.0 0.0
PX local messages recv'd 6,013 0.1 0.1
PX local messages sent 6,013 0.1 0.1
Parallel operations downgraded 25 56 0.0 0.0
Parallel operations downgraded to 37 0.0 0.0
SQL*Net roundtrips to/from client 9,830,560 108.8 82.4
background checkpoints completed 120 0.0 0.0
background checkpoints started 120 0.0 0.0
background timeouts 131,674 1.5 1.1
branch node splits 4 0.0 0.0
buffer is not pinned count 794,034,593 8,790.9 6,656.4
buffer is pinned count 542,933,584 6,010.9 4,551.4
bytes received via SQL*Net from c 1,241,458,111 13,744.4 10,407.2
bytes sent via SQL*Net to client 2,848,652,779 31,537.8 23,880.3
calls to get snapshot scn: kcmgss 17,453,850 193.2 146.3
calls to kcmgas 2,697,509 29.9 22.6
calls to kcmgcs 26,265 0.3 0.2
change write time 59,330 0.7 0.5
cleanouts and rollbacks - consist 233,951 2.6 2.0
cleanouts only - consistent read 25,209 0.3 0.2
cluster key scan block gets 12,529,323 138.7 105.0
cluster key scans 4,575,003 50.7 38.4
commit cleanout failures: block l 16,889 0.2 0.1
commit cleanout failures: buffer 169 0.0 0.0
commit cleanout failures: callbac 227 0.0 0.0
commit cleanout failures: cannot 120,792 1.3 1.0
commit cleanouts 4,225,646 46.8 35.4
commit cleanouts successfully com 4,087,569 45.3 34.3
consistent changes 867,214 9.6 7.3
consistent gets 736,651,041 8,155.6 6,175.4
cursor authentications 6,296 0.1 0.1
data blocks consistent reads - un 691,141 7.7 5.8
db block changes 19,968,336 221.1 167.4
db block gets 41,555,170 460.1 348.4
deferred (CURRENT) block cleanout 1,467,616 16.3 12.3
dirty buffers inspected 969 0.0 0.0
enqueue conversions 29,845 0.3 0.3
enqueue releases 7,844,198 86.8 65.8
enqueue requests 7,870,780 87.1 66.0
enqueue timeouts 26,593 0.3 0.2
enqueue waits 2 0.0 0.0
exchange deadlocks 0 0.0 0.0
Instance Activity Stats for DB: PRD Instance: prd Snaps: 2053 -2063
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
execute count 10,918,127 120.9 91.5
free buffer inspected 10,519 0.1 0.1
free buffer requested 5,240,338 58.0 43.9
hot buffers moved to head of LRU 999,507 11.1 8.4
immediate (CR) block cleanout app 259,160 2.9 2.2
immediate (CURRENT) block cleanou 74,101 0.8 0.6
index fast full scans (full) 3 0.0 0.0
leaf node splits 3,370 0.0 0.0
logons cumulative 41,171 0.5 0.4
logons current
messages received 392,819 4.4 3.3
messages sent 392,819 4.4 3.3
no buffer to keep pinned count 57 0.0 0.0
no work - consistent read gets 247,307,748 2,738.0 2,073.2
opened cursors cumulative 2,040,634 22.6 17.1
opened cursors current
parse count (hard) 26,423 0.3 0.2
parse count (total) 2,391,377 26.5 20.1
parse time cpu 162,373 1.8 1.4
parse time elapsed 175,320 1.9 1.5
physical reads 4,207,169 46.6 35.3
physical reads direct 618,430 6.9 5.2
physical reads direct (lob) 18,607 0.2 0.2
physical writes 623,589 6.9 5.2
physical writes direct 448,655 5.0 3.8
physical writes direct (lob) 679 0.0 0.0
physical writes non checkpoint 578,519 6.4 4.9
pinned buffers inspected 250 0.0 0.0
prefetched blocks 1,919,277 21.3 16.1
prefetched blocks aged out before 30 0.0 0.0
process last non-idle time ################ ############ ############
queries parallelized 56 0.0 0.0
recursive calls 34,142,073 378.0 286.2
recursive cpu usage 800,550 8.9 6.7
redo blocks written 4,886,458 54.1 41.0
redo buffer allocation retries 199 0.0 0.0
redo entries 10,079,726 111.6 84.5
redo log space requests 210 0.0 0.0
redo log space wait time 6,375 0.1 0.1
redo ordering marks 44 0.0 0.0
redo size 2,332,363,336 25,821.9 19,552.2
redo synch time 110,157 1.2 0.9
redo synch writes 89,956 1.0 0.8
redo wastage 90,526,092 1,002.2 758.9
redo write time 728,365 8.1 6.1
redo writer latching time 729 0.0 0.0
redo writes 361,324 4.0 3.0
rollback changes - undo records a 6,024 0.1 0.1
rollbacks only - consistent read 111,504 1.2 0.9
rows fetched via callback 145,609,656 1,612.1 1,220.7
session connect time ################ ############ ############
session cursor cache count 75 0.0 0.0
session cursor cache hits 1,437,784 15.9 12.1
session logical reads 778,187,499 8,615.4 6,523.6
session pga memory 100,788,772,940 1,115,845.8 844,912.6
session pga memory max 107,428,514,524 1,189,355.3 900,573.5
Instance Activity Stats for DB: PRD Instance: prd Snaps: 2053 -2063
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
session uga memory 246,850,836 2,732.9 2,069.4
session uga memory max 6,114,734,352 67,697.0 51,259.8
sorts (disk) 63 0.0 0.0
sorts (memory) 2,649,809 29.3 22.2
sorts (rows) 53,451,920 591.8 448.1
summed dirty queue length 251 0.0 0.0
switch current to new buffer
table fetch by rowid 432,057,897 4,783.4 3,621.9
table fetch continued row 132,072,133 1,462.2 1,107.2
table scan blocks gotten 13,946,202 154.4 116.9
table scan rows gotten 594,479,510 6,581.6 4,983.5
table scans (direct read) 110 0.0 0.0
table scans (long tables) 526 0.0 0.0
table scans (rowid ranges) 109 0.0 0.0
table scans (short tables) 4,484,412 49.7 37.6
total file opens 375,540 4.2 3.2
transaction rollbacks 873 0.0 0.0
transaction tables consistent rea 11 0.0 0.0
transaction tables consistent rea 2,208 0.0 0.0
user calls 10,027,799 111.0 84.1
user commits 107,695 1.2 0.9
user rollbacks 11,594 0.1 0.1
write clones created in backgroun 29 0.0 0.0
write clones created in foregroun 1,055 0.0 0.0
-------------------------------------------------------------
Tablespace IO Stats for DB: PRD Instance: prd Snaps: 2053 -2063
->ordered by IOs (Reads + Writes) desc
Buffer Pool Statistics for DB: PRD Instance: prd Snaps: 2053 -2063
-> Pools D: default pool, K: keep pool, R: recycle pool
Free Write Buffer
Buffer Consistent Physical Physical Buffer Complete Busy
P Gets Gets Reads Writes Waits Waits Waits
- ----------- ------------- ----------- ---------- ------- -------- ----------
D 5,240,340 397,564,468 3,570,124 174,255 0 0 143,752
-------------------------------------------------------------
Buffer wait Statistics for DB: PRD Instance: prd Snaps: 2053 -2063
-> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
data block 130,313 255,066 2
undo block 8,939 366 0
undo header 4,475 67 0
segment header 9 4 0
-------------------------------------------------------------
Enqueue activity for DB: PRD Instance: prd Snaps: 2053 -2063
-> ordered by waits desc, gets desc
Enqueue Gets Waits
---------- ------------ ----------
TX 2,709,767 2
-------------------------------------------------------------
Rollback Segment Stats for DB: PRD Instance: prd Snaps: 2053 -2063
->A high value for "Pct Waits" suggests more rollback segments may be
->required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 302.0 0.00 0 0 0 0
1 387,840.0 0.00 33,388,494 18 0 0
2 357,587.0 0.00 36,916,736 23 0 0
3 452,569.0 0.01 30,773,934 19 0 0
4 340,046.0 0.00 29,791,656 17 0 0
5 360,707.0 0.00 45,277,624 25 0 0
6 542,624.0 0.01 43,088,474 28 0 0
7 361,988.0 0.00 31,874,518 18 0 0
8 348,091.0 0.00 29,188,308 18 0 0
9 330,449.0 0.00 26,653,792 17 0 0
10 508,275.0 0.00 43,406,122 29 0 0
11 632,332.0 0.00 58,105,118 36 1 1
12 357,863.0 0.00 30,303,638 18 0 0
13 423,031.0 0.00 31,141,906 18 0 0
14 26,427.0 0.00 14,442,738 22 0 22
15 342,594.0 0.00 27,201,444 17 0 0
16 361,811.0 0.00 35,197,976 20 0 0
17 359,783.0 0.00 31,845,214 20 0 0
18 366,006.0 0.00 36,212,008 20 0 0
19 362,822.0 0.00 35,917,250 21 0 0
20 363,503.0 0.00 33,304,214 18 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: PRD Instance: prd Snaps: 2053 -2063
->Optimal Size should be larger than Avg Active
RBS No Segment Size Avg Active Optimal Size Maximum Size
------ --------------- --------------- --------------- ---------------
0 401,408 0 401,408
1 10,559,488 1,561,107 10,485,760 10,559,488
2 10,559,488 1,727,272 10,485,760 10,559,488
3 10,559,488 1,627,665 10,485,760 10,559,488
4 10,559,488 1,759,658 10,485,760 10,559,488
5 10,559,488 1,834,517 10,485,760 10,559,488
6 10,559,488 2,025,516 10,485,760 10,559,488
7 10,559,488 1,561,107 10,485,760 10,559,488
8 10,559,488 1,557,004 10,485,760 10,559,488
9 10,559,488 1,609,011 10,485,760 10,559,488
10 10,559,488 2,537,303 10,485,760 10,559,488
11 10,559,488 2,184,003 10,485,760 11,624,448
12 10,559,488 1,561,107 10,485,760 10,559,488
13 10,559,488 1,562,856 10,485,760 10,559,488
14 53,157,888 38,463,299 10,485,760 53,157,888
15 10,559,488 1,619,927 10,485,760 10,559,488
16 10,559,488 1,842,114 10,485,760 10,559,488
17 10,559,488 1,690,188 10,485,760 10,559,488
18 10,559,488 1,930,039 10,485,760 10,559,488
19 10,559,488 1,786,325 10,485,760 10,559,488
20 10,559,488 1,564,430 10,485,760 10,559,488
-------------------------------------------------------------
Latch Activity for DB: PRD Instance: prd Snaps: 2053 -2063
->"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 Pct
Get Get Slps NoWait NoWait
Latch Name Requests Miss /Miss Requests Miss
----------------------------- -------------- ------ ------ ------------ ------
JOX SGA heap latch 21,930 0.0 0
Token Manager 103,940 0.0 12,296 0.0
active checkpoint queue latch 87,097 0.0 0.0 0
archive control 240 0.0 0
archive process latch 240 0.0 0
begin backup scn array 683 0.0 0
cache buffer handles 7,249,059 0.0 0.0 0
cache buffers chains 1,260,773,034 0.0 0.0 5,970,292 0.2
cache buffers lru chain 7,758,264 0.0 0.0 4,016,053 0.0
channel handle pool latch 21,693 0.0 0
channel operations parent lat 32,480 0.0 0
checkpoint queue latch 2,386,665 0.0 0.3 0
dictionary lookup 161 0.0 0
dml lock allocation 9,750,296 0.0 0.0 0
enqueue hash chains 15,745,641 0.1 0.0 0
enqueues 12,957,987 0.0 0.0 0
error message lists 952 1.9 0.0 0
event group latch 10,787 0.0 0
file number translation table 8,427 0.0 0
job_queue_processes parameter 1,404 0.0 0
ktm global data 296 0.0 0
kwqit: protect wakeup time 30,287 0.0 0
latch wait list 33,960 0.9 0.0 33,578 0.1
library cache 77,724,440 0.3 0.2 126,710 0.6
library cache load lock 217,231 0.0 0.0 0
list of block allocation 5,358,468 0.0 0.0 0
loader state object freelist 28,556 0.0 0
longop free list 36,838 0.0 0
messages 2,556,484 0.1 0.0 0
multiblock read objects 862,770 0.0 0.0 2 0.0
ncodef allocation latch 1,404 0.0 0
parallel query alloc buffer 9,856 18.6 0.0 0
parallel query stats 616 26.1 0.1 0
process allocation 10,787 0.0 10,787 0.0
process group creation 21,565 0.0 0
process queue 3,920 0.8 0.0 0
process queue reference 174,583 0.1 0.0 10,343 0.5
query server freelists 5,768 1.4 0.0 0
query server process 112 0.0 112 0.0
redo allocation 10,810,843 0.1 0.0 0
redo writing 3,742,307 0.2 0.0 0
row cache objects 23,007,781 0.2 0.4 654 0.3
sequence cache 315,838 0.0 0.0 0
session allocation 5,736,048 0.1 0.3 0
session idle bit 20,492,654 0.0 0.0 0
session switching 4,616 0.0 0
shared java pool 4,900 0.0 0
shared pool 12,699,561 0.1 0.4 0
sort extent pool 51,903 0.0 0
temp lob duration state obj a 5,472 0.0 0
Latch Activity for DB: PRD Instance: prd Snaps: 2053 -2063
->"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 Pct
Get Get Slps NoWait NoWait
Latch Name Requests Miss /Miss Requests Miss
----------------------------- -------------- ------ ------ ------------ ------
temporary table state object 10 0.0 0
transaction allocation 7,085,824 0.0 0.0 0
transaction branch allocation 1,404 0.0 0
undo global data 9,981,330 0.1 0.0 0
user lock 42,646 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: PRD Instance: prd Snaps: 2053 -2063
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
cache buffers chains 1,260,773,034 316,540 6,886 309674/6849/
16/1/0
library cache 77,724,440 206,179 47,993 175032/14677
/16161/309/0
row cache objects 23,007,781 41,674 15,929 27828/12265/
1244/337/0
undo global data 9,981,330 13,094 18 13076/18/0/0
/0
shared pool 12,699,561 11,961 4,772 9732/62/1880
/287/0
redo allocation 10,810,843 11,199 22 11177/22/0/0
/0
enqueue hash chains 15,745,641 8,172 33 8140/31/1/0/
0
redo writing 3,742,307 7,444 11 7433/11/0/0/
0
enqueues 12,957,987 6,252 12 6240/12/0/0/
0
session allocation 5,736,048 5,198 1,672 3626/1478/88
/6/0
dml lock allocation 9,750,296 3,521 4 3517/4/0/0/0
cache buffers lru chain 7,758,264 2,755 31 2724/31/0/0/
0
messages 2,556,484 2,740 7 2733/7/0/0/0
transaction allocation 7,085,824 1,622 4 1618/4/0/0/0
checkpoint queue latch 2,386,665 265 74 191/74/0/0/0
parallel query stats 616 161 21 140/21/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: PRD Instance: prd Snaps: 2053 -2063
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- -------
cache buffers chains kcbgtcr: kslbegin 0 5,061 455
cache buffers chains kcbchg: kslbegin: bufs not 0 758 1,977
cache buffers chains kcbgcur: kslbegin 0 547 415
cache buffers chains kcbzwb 0 213 78
cache buffers chains kcbchg: kslbegin: call CR 0 172 2,048
cache buffers chains kcbnlc 0 70 347
cache buffers chains kcbrls: kslbegin 0 38 1,443
cache buffers chains kcbzsc 0 5 0
cache buffers chains kcbcge 0 4 119
cache buffers chains kcbbxsv 0 2 0
cache buffers chains kcbzgb: scan from tail. no 0 2 0
cache buffers chains kcbget: pin buffer 0 2 2
cache buffers lru chain kcbbiop: lru scan 0 15 0
cache buffers lru chain kcbzgb: multiple sets nowa 0 15 0
cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 0 1 17
checkpoint queue latch kcbk0rrd: update recovery 0 74 0
dml lock allocation ktaiam 0 3 2
dml lock allocation ktaidm 0 1 2
enqueue hash chains ksqgtl3 0 17 23
enqueue hash chains ksqrcl 0 16 10
enqueues ksqgtl2 0 8 5
enqueues ksqrcl 0 3 6
enqueues ksqdel 0 1 0
library cache kglhdgn: child: 0 41,888 7,275
library cache kglpnal: child: alloc spac 0 903 2,697
library cache kglget: child: KGLDSBYD 0 747 311
library cache kglpnal: child: before pro 0 644 15,783
library cache kglget: child: KGLDSBRD 0 620 158
library cache kglupc: child 0 353 5,322
library cache kglpnp: child 0 231 8,661
library cache kglpnc: child 0 176 5,002
library cache kglidp: parent 0 86 0
library cache kgllkdl: child: cleanup 0 81 303
library cache kglpndl: parent: purge 0 61 2
library cache kglhdgc: child: 0 43 135
library cache kglpnal: child: check gran 0 42 25
library cache kglic 0 26 9
library cache kglpnal: parent held, no p 0 20 0
library cache kglobpn: child: 0 10 35
library cache kgldti: 2child 0 6 61
library cache kgllkdl: child: free pin 0 6 1,505
library cache kglpin 0 5 258
library cache kgldtld: 2child 0 4 59
library cache kglati 0 1 0
messages ksarcv 0 4 4
messages ksarcv: after wait 0 2 0
messages ksaamb: after wakeup 0 1 3
parallel query stats kxfprst: KSLBEGIN 0 21 21
redo allocation kcrfwr: redo allocation 0 16 18
redo allocation kcrfwi: before write 0 5 3
redo allocation kcrfwi: no space 0 1 1
redo writing kcrfsr 0 6 0
redo writing kcrfss 0 3 11
Latch Miss Sources for DB: PRD Instance: prd Snaps: 2053 -2063
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- -------
redo writing kcrfwi: after write 0 2 0
row cache objects kqrpre: find obj 0 15,837 12,104
row cache objects kqreqd 0 16 3,485
row cache objects kqreqd: rel enqueue 0 8 20
session allocation ksuxds: KSUSFCLC not set 0 1,658 594
session allocation ksudlc 0 5 78
session allocation ksucri 0 2 770
session allocation kxfpqidqr 0 2 46
session allocation ksursi 0 2 41
shared pool kghfrunp: alloc: clatch no 0 3,076 0
shared pool kghfrunp: clatch: nowait 0 2,039 0
shared pool kghsmp 0 1,599 16
shared pool kghfrunp: clatch: wait 0 55 101
shared pool kghfrunp: alloc: wait 0 35 0
shared pool kghfnd: min scan 0 29 0
shared pool kghalo 0 15 864
shared pool kghfre 0 14 218
shared pool kghupr1 0 3 3,531
shared pool kghfen: not perm alloc cla 0 1 39
transaction allocation ktcxba 0 3 1
transaction allocation ktcdso 0 1 3
undo global data ktudba: KSLBEGIN 0 10 13
undo global data ktudnx: KSLBEGIN 0 5 2
undo global data ktubnd 0 3 3
-------------------------------------------------------------
Dictionary Cache Stats for DB: PRD Instance: prd Snaps: 2053 -2063
->"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 Pct
Cache Requests Miss Requests Miss Req Usage SGA
---------------------- ------------ ------ -------- ----- -------- ------ ----
dc_constraints 909 33.3 0 909 1 5
dc_database_links 0 0 0 0 0
dc_files 162,355 0.2 0 0 374 99
dc_free_extents 2,022 14.1 158 0.0 684 5 71
dc_global_oids 57,065 0.7 0 0 402 98
dc_histogram_data 299 2.0 0 0 5 31
dc_histogram_data_valu 341 1.2 0 0 3 60
dc_histogram_defs 593,353 61.5 0 994 ###### 100
dc_object_ids 2,293,716 1.6 0 125 ###### 100
dc_objects 1,035,451 1.3 0 391 ###### 100
dc_outlines 0 0 0 0 0
dc_profiles 10,929 0.0 0 0 1 10
dc_rollback_segments 12,393 0.0 0 0 22 88
dc_segments 1,670,824 3.1 0 1,167 ###### 100
dc_sequence_grants 4,568 23.1 0 0 948 100
dc_sequences 6,618 0.8 0 5,925 84 98
dc_synonyms 36,748 1.8 0 64 694 100
dc_tablespace_quotas 13,422 0.0 0 11,390 18 78
dc_tablespaces 219,549 0.1 0 0 354 99
dc_used_extents 292 54.5 0 288 30 86
dc_user_grants 402,942 0.0 0 0 37 65
dc_usernames 506,779 0.0 0 0 171 99
dc_users 726,969 0.0 0 1 185 93
ifs_acl_cache_entries 0 0 0 0 0
-------------------------------------------------------------
Library Cache Activity for DB: PRD Instance: prd Snaps: 2053 -2063
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 313,186 0.2 307,377 0.3 137 0
CLUSTER 1,246 0.7 1,192 1.5 0 0
INDEX 864,159 0.0 864,081 0.0 0 0
OBJECT 0 0 0 0
PIPE 37,593 0.2 38,259 0.2 0 0
SQL AREA 737,205 3.7 13,358,751 0.4 3,300 528
TABLE/PROCEDURE 4,734,365 0.4 11,487,870 0.9 6,037 0
TRIGGER 7,958 1.3 7,958 1.7 31 0
-------------------------------------------------------------
SGA Memory Summary for DB: PRD Instance: prd Snaps: 2053 -2063
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 983,040,000
Fixed Size 73,888
Redo Buffers 33,570,816
Variable Size 584,077,312
----------------
sum 1,600,762,016
-------------------------------------------------------------
SGA breakdown difference for DB: PRD Instance: prd Snaps: 2053 -2063
Pool Name Begin value End value Difference
----------- ------------------------ -------------- -------------- -----------
java pool free memory 56,606,720 53,411,840 -3,194,880
java pool memory in use 3,395,584 6,590,464 3,194,880
large pool free memory 200,000,000 200,000,000 0
shared pool KGFF heap 245,396 245,396 0
shared pool KGK heap 17,208 17,208 0
shared pool KQLS heap 12,932,028 18,068,516 5,136,488
shared pool PL/SQL DIANA 4,564,828 7,780,500 3,215,672
shared pool PL/SQL MPCODE 11,001,992 14,444,788 3,442,796
shared pool PLS non-lib hp 2,096 2,096 0
shared pool PX msg pool 193,488 193,488 0
shared pool PX subheap 22,408 22,408 0
shared pool State objects 385,808 385,808 0
shared pool db_block_buffers 16,320,000 16,320,000 0
shared pool db_block_hash_buckets 2,051,128 2,051,128 0
shared pool dictionary cache 140,378,320 71,883,432 -68,494,888
shared pool enqueue_resources 360,000 360,000 0
shared pool event statistics per ses 1,163,120 1,163,120 0
shared pool fixed allocation callbac 1,280 1,280 0
shared pool free memory 23,550,932 16,783,620 -6,767,312
shared pool joxlod: in ehe 75,824 126,944 51,120
shared pool joxs heap init 4,248 4,248 0
shared pool library cache 23,884,316 34,111,376 10,227,060
shared pool log_buffer 1,048,576 1,048,576 0
shared pool miscellaneous 2,734,108 2,917,384 183,276
shared pool pl/sql source 11,792 24,740 12,948
shared pool ruleset defini 359,888 348,360 -11,528
shared pool sessions 722,260 722,260 0
shared pool sql area 81,434,912 134,271,876 52,836,964
shared pool table columns 55,556 68,756 13,200
shared pool table definiti 18,548 27,712 9,164
shared pool transactions 328,256 328,256 0
shared pool trigger defini 60,124 88,468 28,344
shared pool type object de 109,280 112,180 2,900
db_block_buffers 983,040,000 983,040,000 0
fixed_sga 73,888 73,888 0
log_buffer 33,554,432 33,554,432 0
-------------------------------------------------------------
init.ora Parameters for DB: PRD Instance: prd Snaps: 2053 -2063
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
_complex_view_merging TRUE
_fast_full_scan_enabled FALSE
_like_with_bind_as_equality TRUE
_new_initial_join_orders TRUE
_optimizer_mode_force TRUE
_optimizer_undo_changes FALSE
_or_expand_nvl_predicate TRUE
_ordered_nested_loop TRUE
_push_join_predicate TRUE
_push_join_union_view TRUE
_shared_pool_reserved_min_all 4100
_sort_elimination_cost_ratio 5
_sqlexec_progression_cost 0
_system_trig_enabled FALSE
_table_scan_cost_plus_one TRUE
_trace_files_public TRUE
_use_column_stats_for_functio TRUE
always_anti_join NESTED_LOOPS
always_semi_join NESTED_LOOPS
aq_tm_processes 10
background_dump_dest /d01/app/oracle/admin/prd/bdump
compatible 8.1.7
control_files /d01/oradata/prd/cntrl01.dbf, /d0
core_dump_dest /d01/app/oracle/admin/prd/cdump
cpu_count 6
db_block_buffers 120000
db_block_size 8192
db_file_multiblock_read_count 8
db_files 500
db_name prd
db_writer_processes 2
dml_locks 500
enqueue_resources 5000
hash_area_size 4096000
ifile /d01/app/oracle/admin/prd/pfile/i
java_pool_size 60000000
job_queue_interval 60
job_queue_processes 10
large_pool_size 200M
log_archive_dest_1 LOCATION=/d04/app/oracle/admin/pr
log_archive_dest_2 SERVICE=standby REOPEN=60
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
log_archive_format arch_%t_%s.arc
log_archive_start TRUE
log_buffer 33554432
log_checkpoint_interval 200000
log_checkpoints_to_alert TRUE
max_dump_file_size 10240
max_enabled_roles 40
nls_date_format DD-MON-RR
nls_language american
nls_numeric_characters .,
nls_sort binary
nls_territory america
init.ora Parameters for DB: PRD Instance: prd Snaps: 2053 -2063
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
open_cursors 500
optimizer_features_enable 8.1.7
optimizer_max_permutations 4000
optimizer_mode CHOOSE
optimizer_percent_parallel 0
parallel_max_servers 8
parallel_min_servers 0
processes 300
query_rewrite_enabled TRUE
rollback_segments rbs01, rbs02, rbs03, rbs04, rbs05
row_locking always
session_cached_cursors 100
shared_pool_reserved_size 30000000
shared_pool_size 300000000, 1001580800
sort_area_size 2048000
timed_statistics TRUE
user_dump_dest /d01/app/oracle/admin/prd/udump
utl_file_dir /usr/tmp, /sqlcom/inbound, /sqlco
-------------------------------------------------------------
End of Report
|
|
|
|
| Re: Performance tunning [message #58808 is a reply to message #58806] |
Wed, 01 October 2003 11:00  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Is this a datawarehousing system ? Could you give the H/W & OS config also ? (ie CPU,Ram,architecture,OS version,shared memory,semaphores, filesystem type etc)
Are you experiencing performance problems ? intermittant or persistent ? any recent database changes? software releases ? upgrades ?
Do you have a performance goal ? ie some metrics like tps, load , response time etc ? You know its very hard to tune without a set goal.
-Thiru
|
|
|
|
| Re: Performance tunning [message #58809 is a reply to message #58808] |
Wed, 01 October 2003 12:13 |
Michael Humphrey
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
Thiru,
Sun Solaris 2.6 on 4500 (8 cpu) ufs filesystem on 2 sun t3 arrays.
Problem: Persistent poor/slow performance in Oracle Financials 11i.
I upgraded to oracle Apps 11i in April. Users have been complaining about the speed.
Guess, I can't rely give a goal but to increase speed. Is there a standard benchmark test I can run to check it out?
Top on the server is reporting:
load averages: 0.49, 0.49, 0.43
239 processes: 238 sleeping, 1 on cpu
CPU states: 96.6% idle, 1.8% user, 0.9% kernel, 0.7% iowait, 0.0% swap
Memory: 8192M real, 4035M free, 2877M swap in use, 7703M swap free
Everyonce in a while I have io slowdown ..maybe 10-20% but not common
thanks,
Michael
|
|
|
|
|
|
| Re: Performance tunning [message #58811 is a reply to message #58810] |
Wed, 01 October 2003 13:39 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Michael,
Frankly a 10-20% io slowdown every once in a while doesnt seem to be an issue. I have seen several well performing,well tuned systems with such small spikes.
Maybe an adhoc query going for a full table scan or Index range scan of big table once in a while. Maybe a backup routine or some other job ..
Again your tuning/troubleshooting method varies depending on the scope of slowness ie
whether its a particular query or a
particular application or a
particular database instance or the
whole system.
Statspack may not be the best tool for tuning a specific query or even a particular app.SqlTrace,Explain Plan,Tkprof are best suited for such cases.But its one of the best tools for tuning a particular instance.
Is this a datawarehousing system or an OLTP database ?
Lets focus on the general issues here,if any and then look into specifics.I will reply to your other questions as we go..
-Thiru
|
|
|
|
| Re: Performance tunning [message #58812 is a reply to message #58809] |
Wed, 01 October 2003 13:56 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Michael,
Again, what type of system is this ie transaction intensive, bulk loads/reporting , mixed ?
You are using UFS filesystem. Is this buffered ? or Direct I/O ? I had migrated databases from cooked filesystems to Direct I/O and have seen performance boost.You can either go for Solaris directio( which I believe is in 2.6) or use Veritas. Oracle handles the buffer cache much better than anything else and the Solaris page cache is only a hindrance in most cases. Have you considered Raw devices ?
Are users complaining about the speed ever since you upgraded to Apps 11i ? If yes,you know the culprit . Do you have statspak data since then ? If this is something to do with the app,then you will need to focus on the changed data access pattern/queries. I havent worked with 11i yet ,but an app is an app.
Your load average of < 0.5 for a 8 cpu system is FANTASTIC. Was this taken during your slowdown ?
Your system is almost 97% idle. Is this your peak ? I havent seen a quieter system. Why worry ?
0.7% iowait . It cant get better !
You have tons of memory free! ie you didnt even have to starve the memory to reach such a low iowait or load.
I will be very happy with such a load or iowait or idle.
-Thiru
|
|
|
|
| Re: Performance tunning [message #58815 is a reply to message #58811] |
Wed, 01 October 2003 14:42 |
Michael Humphrey
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
Thiru,
Since this is an Oracle Applications setup I really can't go and change there code. So no need to worry about explain plans or tuning of SQL. I know that the best way to do it if it was my application I was tuning.
The system is primary a OLT processing system. I havn't moved the datafile to a raw partition since the IO is pretty low (yea.. its loooow all the time) right now the system 96-98% idle. But its still slow when you are using the application. All user are directly connected to the same switch as the server. One server (sun 420r) is the forsm/web server the other is the database server (4500) and both are barlying being taxed. All the output from statspack seems to say the rdbms is not being taxed either. But when you are in a form is just seems long/slow to move about and complete transactions. We all have new pc (2.2ghz or better).
also i have analzyed all the schema (except sys & system).
|
|
|
|
| Re: Performance tunning [message #58817 is a reply to message #58815] |
Thu, 02 October 2003 07:19 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Believe me,I have been at the receiving end of vendor applications that you can not tweak much for performance improvement.But I believe tracing a typical(a slow module perhaps that can be reproduced) using dbms_system.set_sql_trace_in_session and doing a TKPROF will help you understand what is it doing and more importantly what are the 'unnecessary' things the app is doing.
For eg, the client had Applix CRM application and it generated queries on the fly whenever moving between certain forms or pressing certain buttons. Analying the session trace file revealed totally unexpected queries being generated. The form was trying to fill up every possible combination of customer/order/product info in different places of the form,even though that wasnt the intention. Once we detected this, the Applix admin was able to contact the vendor and turn off certain options via app config changes and everything was back to normal ! and all that time people were blaming it on Oracle database, as usual !
You can make the database and the system go faster ,but if the app misbehaves, there is not much we can do. Generally I start my tuning cycle in the following order App->DB Access-->Schema-->Instance-->OS. Ofcourse the data model is very important,but most of the times its too late to change and needs an app/schema reorg.
I will try to look into the Statspack report soon and let you know.
-Thiru
|
|
|
|
| Re: Performance tunning [message #58818 is a reply to message #58806] |
Thu, 02 October 2003 07:29 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Michael,
it looks like your Statspack report has been generated based on snapshots ranging over a very wide interval of more than 24hrs and only 10 snapshots within that time interval .
The problem with such a long interval and low frequency of snapshots is that it tends to miss the peaks/problem areas as it tends to average and smoothen out the statistics.
You mentioned that you observe the slowness 'once in a while' for few minutes.If so, you will need to take snapshots very frequently say 10-20 min and the total time between begin & end snapshots be like 1-2 hours inorder for you to capture the problem area.
Also its recommended to have a base line statspack report that was taken when the system/app was performing good for comparision.
-Thiru
|
|
|
|
|
|
| Re: Performance tunning [message #58846 is a reply to message #58845] |
Fri, 03 October 2003 12:39 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Michael, I havent had a chance to look into it. But as I mentioned earlier,it has been generated over an interval of over 24 hours with only 10 snapshots and hence it may not reflect whats really happening with the database when you are experiencing intermittant slowdowns. You will need to schedule a job that takes a snapshot every 10-15 min and generate a report over only 1-2 hours when you are having the performance issue. That will be better.
-Thiru
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 08:21:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
