| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> can anyone help me tune the database
STATSPACK report for
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 52 23-Aug-06 22:28:51 16 5.4
End Snap: 53 23-Aug-06 22:49:26 16 4.7
Elapsed: 20.58 (mins)
Cache Sizes (end)
Buffer Cache: 504M Std Block Size: 8K
Shared Pool Size: 216M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 8,152.77
1,006,867.60
Logical reads: 1,323.56
163,459.10
Block changes: 43.14
5,327.50
Physical reads: 12.50
1,543.40
Physical writes: 200.46
24,757.40
User calls: 0.09
11.10
Parses: 0.68
83.90
Hard parses: 0.07
8.30
Sorts: 0.41
51.10
Logons: 0.01
0.90
Executes: 14.71
1,816.80
Transactions: 0.01
% Blocks changed per Read: 3.26 Recursive Call %: 99.77
Rollback per transaction %: 40.00 Rows per Sort: 86.15
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.06 In-memory Sort %: 100.00
Library Hit %: 97.38 Soft Parse %: 90.11
Execute to Parse %: 95.38 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 92.42 % Non-Parse CPU: 97.64
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 91.75 94.29
% SQL with executions>1: 52.05 53.31
Top 5 Timed Events
Event Waits Time (s)Ela Time
-------------------------------------------- ------------ -----------
--------
CPU time 83
62.08
direct path write 16,504 43
32.23
db file sequential read 927 3
2.02
db file scattered read 962 2
1.25
control file parallel write 401 1
1.11
-------------------------------------------------------------
Wait Events for DB: OCTOPUS Instance: octopus Snaps: 52 -53
-> 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
---------------------------- ------------ ---------- ---------- ------
--------
direct path write 16,504 0 43 3
1,650.4
db file sequential read 927 0 3 3
92.7
db file scattered read 962 0 2 2
96.2
control file parallel write 401 0 1 4
40.1
log file parallel write 96 91 1 7
9.6
ARCH wait on SENDREQ 20 0 1 31
2.0
control file sequential read 304 0 0 1
30.4
log file sync 6 0 0 8
0.6
db file parallel write 10 5 0 2
1.0
SQL*Net break/reset to clien 2 0 0 8
0.2
log buffer space 1 0 0 12
0.1
latch free 1 0 0 9
0.1
SQL*Net more data to client 14 0 0 0
1.4
LGWR wait for redo copy 5 0 0 0
0.5
SQL*Net message from client 96 0 3,070 31977
9.6
SQL*Net message to client 96 0 0 0
9.6
-------------------------------------------------------------
Background Wait Events for DB: OCTOPUS Instance: octopus Snaps: 52
-53
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
--------
control file parallel write 401 0 1 4
40.1
log file parallel write 96 91 1 7
9.6
ARCH wait on SENDREQ 20 0 1 31
2.0
control file sequential read 260 0 0 1
26.0
db file sequential read 2 0 0 81
0.2
db file parallel write 10 5 0 2
1.0
latch free 1 0 0 9
0.1
LGWR wait for redo copy 5 0 0 0
0.5
rdbms ipc message 1,426 1,345 11,689 8197
142.6
smon timer 4 4 1,229 ######
0.4
-------------------------------------------------------------
At first I found that the statistics were not present and used the dbms_stats utility to create them .Until I created it was using rule based optimizer.There is visible improvement.But during the end of the day process and the month end process it takes more time.This statspack is for the end of day process Received on Mon Sep 11 2006 - 10:21:47 CDT
 |
 |