Home » RDBMS Server » Performance Tuning » Another question related to Statspack (Oracle 9.2.0.8 on Solaris)
Another question related to Statspack [message #281753] Mon, 19 November 2007 03:33 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,

I am analyzing a Stataspack report and have a question for which I need some help. The Statspack report is a one hour report and it contains in part, the following info:


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                        2,911    74.26
db file sequential read                           388,744         397    10.13
db file scattered read                            124,211         254     6.48
direct path read                                  412,291         153     3.89
global cache cr request                           689,368         120     3.05
          -------------------------------------------------------------

So it means that the application is CPU Bound as CPU waits are 75%. Is this conclusion correct. and in this case to reduce the CPU waits I should identify the SQLs that cause max. load on CPU and try to tune it right?

Now the section on "SQL ordered by Gets for DB" contains the following:

SQL ordered by Gets for DB: db1  Instance: inst1  Snaps: 10162 -10163
-> End Buffer Gets Threshold:   10000
-> Note that resources reported for PL/SQL includes the resources used by
   all SQL statements called within the PL/SQL code.  As individual SQL
   statements are also reported, it is possible and valid for the summed
   total % to exceed 100

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------

BEGIN dbms_stats.gather_schema_stats('Schema1',estimate_percent=>nul
l,cascade=>true,method_opt=>'for all indexed columns size auto',
granularity=>'all'); END;

      1,559,149            1    1,559,149.0   14.9  1200.86   1541.56  797380495




There are two such other statements for other 2 schemas and totally all three statements account for 30% of Total Time.
Similarly on the section of SQL ordered by Reads , the same statements are there, and they account for about 90% of Total time.


So to tune this what should I do?




Re: Another question related to Statspack [message #281804 is a reply to message #281753] Mon, 19 November 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't read lines larger than 80 characters.

Regards
Michel
Re: Another question related to Statspack [message #281843 is a reply to message #281753] Mon, 19 November 2007 08:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The processes you're looking at are the DBMS_STATS package gathering table statistics for your schemas. YOu probably shouldn't be running that during peak load times.
You should also look into the 'GATHER STALE' options for DBMS_STATS.
Re: Another question related to Statspack [message #281845 is a reply to message #281753] Mon, 19 November 2007 08:47 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you, I will check on this with Prod. DBAs, can you also suggest as to what can be done for tuning these procs and whether tuning of them is right decision?
Re: Another question related to Statspack [message #282270 is a reply to message #281845] Wed, 21 November 2007 06:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The code in DBMS_STATS is supplied by Oracle and is not user maintainable.
No tuning is possible.
Your DBAs can gather different statistics, which may put less load on the system..
Re: Another question related to Statspack [message #282285 is a reply to message #281753] Wed, 21 November 2007 06:45 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thanks a lot JRowbottom.
Previous Topic: Maximum number of oracle processes
Next Topic: Similar SQLs : Index Usage/Non-usage
Goto Forum:
  


Current Time: Fri Dec 09 05:51:49 CST 2016

Total time taken to generate the page: 0.25727 seconds