| The Server Performance deteriorates after some time after the instance is restarted [message #128086] |
Thu, 14 July 2005 23:39  |
rhm1
Messages: 1
Registered: July 2005
|
Junior Member |
|
|
Hi I am a new DBA. I am facing some performance issues. Need some guidance in finding the root cause of the performance problem.
The Server Performance deteriorates after some time, after the instance has been restarted. On restarting the instance performance improves and again tends to deteriorate.
It takes alot of time to generate outputs for a query for a particular schema. The statspack report generated is as follows:
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
ORABMTD 4106449721 orabmtd 1 9.2.0.1.0 NO ctsintcosgm1
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 1 14-Jul-05 09:05:01 101 3.4
End Snap: 2 14-Jul-05 09:14:03 103 3.5
Elapsed: 9.03 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 112M Std Block Size: 8K
Shared Pool Size: 112M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 18,309.11 14,529.34
Logical reads: 4,234.38 3,360.23
Block changes: 51.67 41.01
Physical reads: 54.17 42.99
Physical writes: 102.53 81.36
User calls: 54.85 43.53
Parses: 34.27 27.20
Hard parses: 0.70 0.56
Sorts: 3.20 2.54
Logons: 0.01 0.01
Executes: 35.64 28.28
Transactions: 1.26
% Blocks changed per Read: 1.22 Recursive Call %: 94.02
Rollback per transaction %: 0.00 Rows per Sort: 186.13
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.97
Buffer Hit %: 99.24 In-memory Sort %: 99.48
Library Hit %: 98.81 Soft Parse %: 97.94
Execute to Parse %: 3.85 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 68.97 % Non-Parse CPU: 98.17
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.14 93.74
% SQL with executions>1: 36.69 45.07
% Memory for SQL w/exec>1: 54.26 60.96
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
direct path write 8,519 3,176 87.24
CPU time 229 6.29
direct path read 2,169 119 3.27
log file sync 686 38 1.05
log file parallel write 2,072 21 .56
-------------------------------------------------------------
Please give me your reviews. Also let me know what information to gather to identify the problem. Do let me know if you require addtional information.
Thanks in advance.
|
|
|
|
| Re: The Server Performance deteriorates after some time after the instance is restarted [message #128264 is a reply to message #128086] |
Sat, 16 July 2005 11:11  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
The biggest thing you can do to help yourself is to read two books in the standard oracle documentation. The concepts guide and the performance tuning guide. I'd suggest reading the 10g versions of these documents as those versions are excellent.
Also, if you are a new dba and on a new system, don't use 9i but use 10g instead. If for some reason you are forced to use 9i, then upgrade your version from 9.2.0.1 to the highest patch level (6?) that you can. 9.2.0.1 has lots of bugs.
Also, at least for now, don't look at your database performance as being a single entity to tune. Examine closely specific queries. Time them, tkprof them, analyze their plans (those documentation books tell you how), and monitor how each query performs throughout the day/week to see what happens when your system "slows down" after being up a while.
Also, buy and read Effective Oracle By Design by Tom Kyte. And preorder (not yet out) his new Expert oracle 9i/10g version book due out in September.
And gather statistics on your objects, all of them. Read about and use dbms_stats fully. And use bind variables.
Heck, we might should make a sticky for all this...
[Updated on: Sat, 16 July 2005 11:12] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
