Home » RDBMS Server » Performance Tuning » My production statspack report
My production statspack report [message #234114] Mon, 30 April 2007 01:25 Go to next message
kesevan
Messages: 17
Registered: April 2007
Location: MUMABI
Junior Member

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 #234119 is a reply to message #234114] Mon, 30 April 2007 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, did you read spdoc.txt in your ORACLE_HOME/rdbms/admin directory.

Next, you should read StatsPack papers at: http://www.oracle.com/technology/deploy/performance/WhitePapers.html

Regards
Michel
Re: My production statspack report [message #234120 is a reply to message #234114] Mon, 30 April 2007 01:46 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Is this Report generated during peak/average load?

I see the some of the jobs are run externally (SQL * Plus).

Brayan.
Re: My production statspack report [message #234123 is a reply to message #234120] Mon, 30 April 2007 01:53 Go to previous messageGo to next message
kesevan
Messages: 17
Registered: April 2007
Location: MUMABI
Junior Member

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 #234127 is a reply to message #234114] Mon, 30 April 2007 02:09 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Please read Metalink Note:228913.1

For your second question - In your Report ...

Quote:

SQL ordered by Gets for DB: PFNW Instance: PFNW Snaps: 4453 -4463

Module: SQL*Plus




Brayan.
Re: My production statspack report [message #234484 is a reply to message #234114] Tue, 01 May 2007 14:40 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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.
Re: My production statspack report [message #237952 is a reply to message #234114] Wed, 16 May 2007 03:33 Go to previous message
kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA
Junior Member
Hi,
Stats Pack report has diluted values. It was generated for 60mins. Pls generate only for 15min during peak load and upload.
Previous Topic: Slow query responce
Next Topic: dbms_profiler
Goto Forum:
  


Current Time: Fri Dec 09 09:38:25 CST 2016

Total time taken to generate the page: 0.29916 seconds