Hello --
The first and most obvious thing is your buffer cache is very very
small. 64MB isn't going to be sufficient for anything but the smallest
database. Your buffer cache hit ratio reflects this, by being at only
83%. Ideally buffer cache hit should be over 95%, and in fact up around
98-99. Once you get that corrected, you may have other issues as well.
Shared pool size is probably good, at 800M. Your redo log buffer may be
oversized, which can lead to performance problems as well if it is way
too big. 1-5M is more standard, but that has to be analyzed.
Thanks,
Tim V.
-----Original Message-----
From: nagarajanv
[mailto:oracledba-ezmlmshield-x96386134.[Email address protected]
Sent: Thursday, April 28, 2005 11:20 AM
To: LazyDBA Discussion
Subject: RE: Application performance issue
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