| My production statspack report [message #234114] |
Mon, 30 April 2007 01:25  |
|
|
Dear Sirs,
Just now i have started my "Tuning" and i don't have much experience in tuning.. I am in a position to anlayze my statspack report and tune the same. Herewith i have attached my statspack report ( production database ) and it need to be tuned further as per the instruction.
After go though my statspack report, kindly adv me what are all the parameters to be checked / changed... further.
And they say some SQL queries are running very slow..... so pls adv me the memory related parameters to be tuned in production database.
Further to your reply, i will give my current parameter settings.. i.e. i can attach my v$parameter and v$sysstat output in my next mail....
Since matter is urgent, i request you to provide the reply promptly in this regard...
Thanks a lot !
kesavan.
|
|
|
|
|
|
|
|
| Re: My production statspack report [message #234123 is a reply to message #234120] |
Mon, 30 April 2007 01:53  |
|
|
Dear Brayan,
Yes, subject statspack report has been generated during peak hours.... please guide me further...so that i can adv my production team to genrate the report properly during peak load.... and tell me how did you find out that there are some jobs are running externally ( sql*plus )
Thank you for your very prompt response..
kesavan.
|
|
|
|
|
|
| Re: My production statspack report [message #234484 is a reply to message #234114] |
Tue, 01 May 2007 14:40 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
A number of problems:
1. Low/Very low
Execute to Parse %: 53.60
Parse CPU to Parse Elapsd %: 33.63
may be caused either by too small shared pool size ( 64M ) or
by overparsing (you are not using bind variables and trust Oracle to do the job for you).
2. Look at section: SQL ordered by Reads for DB
Try tuning at least the to 7 statements (IMHO all of them perform full table scans).
3. Consider increasing db_cache_size to 128M (instead of 64M).
HTH.
Michael
|
|
|
|
| Re: My production statspack report [message #234842 is a reply to message #234484] |
Thu, 03 May 2007 05:20 |
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
|
Member |
|
|
Few Points to look into
1.There are hard parses (Look at the Hard parses under Load Profile Section)
2.Rollback per transaction %: 92.45
This pretty Huge investigate why there is are lot many Rolbacks.
3.Execute to Parse %: 53.60 This is on the lower side , but if this is a web application no need to worry on this front.
This could be normal here.
But with a stated connection, parse < execute is expected.
Gets per exec is more for many of the queries.
See section
SQL ordered by Sharable Memory for
SQL ordered by Version Count
As verified with more reloads on the sqlarea
The shared pool is fragmented it seems like (see the shared pool usage)
There are many statements where the sharable memory usage is high with a high version count.
Seems like this is due to cursor_sharing = SIMILAR.
All the statements with high version counts will be having multiple child cursors present which is fragmenting the shared pool.
The reasons could be you would have collected histograms on almost all the tables.
The only way to avoid this is to use bind variables not the system generated binds.
If this is not possibe consider collecting statistics for the tables refereced with high version count queries with no histograms.
Change the value of pga aggregate target to 100MB.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
