Your buffer cache is set to ONLY 64m??? With all that ram you ought to at least have between 1 and 2GB to start with. You need to less physical I/O and a lot more LOGICAL. With that kind or memory, you might be able to go a lot further with both shared pool and buffer cache. Depends on how many users, whether you are running MTS vs dedicated connections, etc...
M
>>> "nagarajanv" <oracledba-ezmlmshield-x96386134.[Email address protected] 04/28/05 11:20 AM >>>
Hi,
Pls find the statspack report.which we took at high load period. The Production is running Oracle 9.2.0.5 on an AIX 5.1 server with 15g memory.Now our system is very low.
Could you please advice for imporoving the performance?
If need more information ,Getback..
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
PRAS 754436798 PRAS 1 9.2.0.5.0 NO p04
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 17 22-Apr-05 19:05:01 376 33.2
End Snap: 18 22-Apr-05 20:05:01 377 33.2
Elapsed: 60.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 64M Std Block Size: 8K
Shared Pool Size: 800M Log Buffer: 6,400K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 10,260.65 68,786.50
Logical reads: 2,773.13 18,590.84
Block changes: 53.28 357.20
Physical reads: 465.76 3,122.44
Physical writes: 4.43 29.68
User calls: 26.79 179.60
Parses: 40.13 269.03
Hard parses: 0.07 0.45
Sorts: 3.89 26.10
Logons: 0.03 0.23
Executes: 82.39 552.35
Transactions: 0.15
% Blocks changed per Read: 1.92 Recursive Call %: 86.71
Rollback per transaction %: 18.06 Rows per Sort: 18.80
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 83.20 In-memory Sort %: 100.00
Library Hit %: 99.88 Soft Parse %: 99.83
Execute to Parse %: 51.29 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 24.66 % Non-Parse CPU: 99.35
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 91.76 91.80
% SQL with executions>1: 89.12 88.75
% Memory for SQL w/exec>1: 88.21 87.95
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 2,185 41.72
sbtwrite2 189,721 1,629 31.10
db file sequential read 115,712 489 9.34
async disk IO 305,742 460 8.78
db file scattered read 207,042 414 7.91
-------------------------------------------------------------
Wait Events for DB: PRAS Instance: PRAS Snaps: 17 -18
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
sbtwrite2 189,721 0 1,629 9 353.3
db file sequential read 115,712 0 489 4 215.5
async disk IO 305,742 0 460 2 569.4
db file scattered read 207,042 0 414 2 385.6
enqueue 8 4 18 2291 0.0
sbtclose2 1 0 9 8934 0.0
db file parallel write 185 0 6 30 0.3
sbtinfo2 1 0 5 5051 0.0
log file sync 352 0 4 13 0.7
latch free 299 55 4 15 0.6
db file parallel read 187 0 4 20 0.3
control file parallel write 1,192 0 3 3 2.2
sbtend 1 0 3 3003 0.0
sbtbackup 1 0 2 2146 0.0
control file sequential read 2,630 0 1 0 4.9
buffer busy waits 112 0 0 2 0.2
SQL*Net break/reset to clien 8 0 0 11 0.0
SQL*Net more data to client 510 0 0 0 0.9
log file parallel write 787 786 0 0 1.5
sbtinit 1 0 0 46 0.0
control file single write 9 0 0 4 0.0
db file single write 48 0 0 1 0.1
LGWR wait for redo copy 8 0 0 1 0.0
undo segment extension 328 328 0 0 0.6
sbtinit2 1 0 0 0 0.0
SQL*Net message from client 98,545 0 837,580 8499 183.5
SQL*Net message to client 98,545 0 1 0 183.5
-------------------------------------------------------------
Background Wait Events for DB: PRAS Instance: PRAS Snaps: 17 -18
-> 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 185 0 6 30 0.3
db file scattered read 784 0 3 4 1.5
control file parallel write 1,168 0 3 3 2.2
db file sequential read 362 0 1 3 0.7
control file sequential read 816 0 1 1 1.5
latch free 6 2 0 22 0.0
log file parallel write 787 786 0 0 1.5
rdbms ipc reply 7 0 0 6 0.0
LGWR wait for redo copy 8 0 0 1 0.0
buffer busy waits 2 0 0 1 0.0
rdbms ipc message 6,030 5,118 20,136 3339 11.2
smon timer 15 10 3,722 ###### 0.0
pmon timer 1,204 1,204 3,514 2919 2.2
-------------------------------------------------------------
DISCLAIMER:
Information transmitted by this e-mail may be proprietary to Ramco Systems Ltd., and / or the authors of the information and is intended for use only by the individual or entity to which it is addressed, and may contain confidential or legally privileged information. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are not authorised to access, read, disclose, copy, use or otherwise deal with it and any such actions are prohibited and may be unlawful.
Internet communications cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, arrive late or contain viruses. Ramco Systems Limited therefore does not accept liability for any errors, omissions, viruses or computer problems experienced as a result of this transmission.
If you have received this e-mail in error, please notify us immediately at mail to: [Email address protected] and delete this mail from your records. Notice is hereby given that no representation, contract or other binding obligation shall be created by this e-mail.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page