Home » RDBMS Server » Performance Tuning » DBMS_STATS.GATHER_SCHEMA_STATS - Suggestion
DBMS_STATS.GATHER_SCHEMA_STATS - Suggestion [message #221129] Sat, 24 February 2007 03:01 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I need suggestion regarding DBMS_STATS.GATHER_SCHEMA_STATS.

DB Version - 9.2
Datawarehouse Environment
DB Size - 300 GB.

First we load the OLTP data then we Transform data. The activity of transforming takes around 2-3 days(not continuous). The frequency of loading is 15 days.

I run the DBMS_STATS.GATHER_SCHEMA_STATS once in a every week. Is it better to gather stats soon after loading OLTP data. Usually I gather stats only after finishng the transformation.

I tried both, but performancewise there is not much difference.

Please suggest..

Brian.
Re: DBMS_STATS.GATHER_SCHEMA_STATS - Suggestion [message #221155 is a reply to message #221129] Sat, 24 February 2007 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I tried both, but performancewise there is not much difference.
I am afraid that there is no third option.
Re: DBMS_STATS.GATHER_SCHEMA_STATS - Suggestion [message #221159 is a reply to message #221129] Sat, 24 February 2007 09:18 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You mean performance of the database?
Collecting statistics does not magically make your database perform better. It will just lay a good/standard base for CBO to do its job as intended. I would isolate the problem and work on it.
Previous Topic: Optimize mode first_rows returns full table
Next Topic: performance enhancement
Goto Forum:
  


Current Time: Sat Dec 10 07:23:28 CST 2016

Total time taken to generate the page: 0.10444 seconds