Home » RDBMS Server » Performance Tuning » tuning with statpack
| tuning with statpack [message #133391] |
Thu, 18 August 2005 17:27  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Bellow is my statpack. I need to tune the DB as the performance is slow. For the first time running the app on this server. It looks that there's a log problems. How can I troubleshoot this?
Thanks a lot,mj
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
ORAPERF 1011474293 oraperf 1 10.1.0.4.0 NO aixperfdb
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 1430 17-Aug-05 14:00:04 14 19,439.9
End Snap: 1431 17-Aug-05 15:00:04 52 5,430.8
Elapsed: 60.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 5,072M Std Block Size: 8K
Shared Pool Size: 992M Log Buffer: 9,766K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,686,822.29 14,913.33
Logical reads: 22,329.04 197.41
Block changes: 10,514.69 92.96
Physical reads: 0.01 0.00
Physical writes: 154.07 1.36
User calls: 4,206.65 37.19
Parses: 2,055.97 18.18
Hard parses: 0.33 0.00
Sorts: 7.31 0.06
Logons: 0.10 0.00
Executes: 2,056.93 18.19
Transactions: 113.11
% Blocks changed per Read: 47.09 Recursive Call %: 6.21
Rollback per transaction %: 0.04 Rows per Sort: 40.98
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.83 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.94 Soft Parse %: 99.98
Execute to Parse %: 0.05 Latch Hit %: 99.81
Parse CPU to Parse Elapsd %: 92.38 % Non-Parse CPU: 88.70
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 28.31 30.75
% SQL with executions>1: 88.62 87.42
% Memory for SQL w/exec>1: 78.76 79.76
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
log file sync 412,330 12,830 61.11
CPU time 5,994 28.55
log file parallel write 678,265 1,646 7.84
db file parallel write 37,587 203 .97
buffer busy waits 133,038 126 .60
-------------------------------------------------------------
Wait Events DB/Inst: ORAPERF/oraperf Snaps: 1430-1431
-> 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 sync 412,330 3,497 12,830 31 1.0
log file parallel write 678,265 0 1,646 2 1.7
db file parallel write 37,587 0 203 5 0.1
buffer busy waits 133,038 1 126 1 0.3
SQL*Net break/reset to clien 97,882 0 110 1 0.2
control file parallel write 1,423 0 51 36 0.0
latch: cache buffers chains 21,602 21,557 18 1 0.1
control file single write 117 0 3 28 0.0
process startup 54 0 3 50 0.0
latch: library cache 917 0 2 3 0.0
library cache pin 42 0 2 38 0.0
log file switch completion 62 0 1 21 0.0
latch free 388 193 1 2 0.0
LGWR wait for redo copy 14,686 17 1 0 0.0
control file sequential read 1,503 0 1 1 0.0
enq: TX - contention 189 0 0 2 0.0
rdbms ipc reply 881 0 0 0 0.0
latch: In memory undo latch 118 0 0 3 0.0
enq: TX - index contention 147 0 0 2 0.0
enq: HW - contention 748 0 0 0 0.0
latch: shared pool 152 0 0 2 0.0
latch: library cache lock 102 0 0 2 0.0
latch: undo global data 73 0 0 2 0.0
latch: redo allocation 51 0 0 2 0.0
latch: enqueue hash chains 35 0 0 3 0.0
enq: TX - row lock contentio 33 0 0 2 0.0
direct path write 48 0 0 1 0.0
latch: library cache pin 21 0 0 2 0.0
direct path read 48 0 0 0 0.0
log file single write 6 0 0 4 0.0
log file sequential read 6 0 0 2 0.0
latch: session allocation 9 0 0 1 0.0
row cache lock 9 0 0 0 0.0
latch: cache buffers lru cha 5 0 0 1 0.0
enq: US - contention 3 0 0 1 0.0
latch: redo writing 2 0 0 1 0.0
latch: messages 1 0 0 1 0.0
enq: TX - allocate ITL entry 4 0 0 0 0.0
enq: FB - contention 1 0 0 0 0.0
SQL*Net more data to client 8 0 0 0 0.0
buffer deadlock 61 61 0 0 0.0
SQL*Net message from client 7,825,219 0 145,990 19 19.2
queue messages 720 720 3,514 4880 0.0
virtual circuit status 120 120 3,495 29122 0.0
jobq slave wait 1,108 1,105 3,243 2927 0.0
SQL*Net message to client 7,825,240 0 14 0 19.2
SQL*Net more data from clien 65 0 6 87 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: ORAPERF/oraperf Snaps: 1430-1431
|
|
|
|
 Re: tuning with statpack [message #133441 is a reply to message #133391] |
Fri, 19 August 2005 01:59  |
kugenuma
Messages: 9
Registered: August 2005
Location: Japan
|
Junior Member |
|
|
Hi mj.
Your issue would be that redo i/o problem or too much amount of redo.
Fist of all, you may check where redo file is.
You can move it to more hi-speed disk.
Secoundly, pls check application logic which related to commit/rollback.
Best regards, kuge.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 09:45:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
