Home » RDBMS Server » Performance Tuning » Any Suggestion on this STATSPACK Report
Any Suggestion on this STATSPACK Report [message #229103] Thu, 05 April 2007 07:19 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I have attached a statspack report of Datawarehouse environment.

Any suggestion to improve the performance.

Brayan.
Re: Any Suggestion on this STATSPACK Report [message #229171 is a reply to message #229103] Thu, 05 April 2007 11:23 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Just a starter:

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 512M Std Block Size: 8K
Shared Pool Size: 304M Log Buffer: 102,400K

Log Buffer of 100M look way TOO large. I would decrease it and use the freed space to increase buffer cache.



Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 45.10 In-memory Sort %: 73.43
Library Hit %: 99.65 Soft Parse %: 99.55
Execute to Parse %: 76.86 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 12.10 % Non-Parse CPU: 99.52

Buffer Hit %: 45.10 - VERY LOW (IMHO - even for DW).
Either you have insufficient db_cache_size (see prev comment about log) or you have very inefficient SQL statements.


HTH.
Michael
Re: Any Suggestion on this STATSPACK Report [message #231896 is a reply to message #229103] Wed, 18 April 2007 14:56 Go to previous messageGo to next message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Also, consider running statspack snapshots every 15 mins rather than 60 mins. A lot can happen in an hour to skew the report.

Regards
Re: Any Suggestion on this STATSPACK Report [message #234850 is a reply to message #231896] Thu, 03 May 2007 05:50 Go to previous message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
The cursor_space_time is set to true , but u have reloads and invalidations in the sql area , so its advisble not to set this to true unless you have no invalidations and reloeads.

You have more FTS happening for tables with lesser number of blocks.

Consider caching these tables under the keep pool.

Refer http://www.dba-oracle.com/t_plan9i_sql_full_table_scans.htm

for more details
Previous Topic: Partitioning
Next Topic: RBO in 10g
Goto Forum:
  


Current Time: Thu Dec 08 04:00:33 CST 2016

Total time taken to generate the page: 0.11207 seconds