Home » RDBMS Server » Performance Tuning » statistics collection (Oracle 10.2.0.3)
statistics collection [message #512137] Fri, 17 June 2011 02:47 Go to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
We collected statistics including histograms on some tables.But after that statistics were gathered
again on the schema.Does the new stats can overwrite the statistics for the tables for which histograms were gathered?

Regards
Ved
Re: statistics collection [message #512141 is a reply to message #512137] Fri, 17 June 2011 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68509
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does the new stats can overwrite the statistics for the tables for which histograms were gathered?

Check DBA_PART_HISTOGRAMS

Regards
Michel
Re: statistics collection [message #512378 is a reply to message #512137] Sun, 19 June 2011 21:58 Go to previous message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
Yes, possibly.

Both gather statistics runs would have been using the same interface. : DBMS_STATS GATHER_TABLE_STATS or GATHER_SCHEMA_STATS.
Method_Opt enables gathering of histograms.
If you explicitly specify Method_Opt ("for .. columns ... size ..."), Oracle uses the specification.
If you do not specify Method_Opt, Oracle may default to "for all columns size auto") which may drive collection of histograms on some columms but not on others -- it depends on the column usage information that Oracle uses to determine the "SIZE AUTO".

If you want specific histograms, always be careful to ensure that you include the specification.
If you are comfortable with the automatic histograms, you can rely on the auto behaviour.

Note that the "default" Method_Opt can also be overriden with DBMS_STATS.SET_PARAM.

Hemant K Chitale
Previous Topic: Tuning option selection (merged 2)
Next Topic: Big Table data Maintenance
Goto Forum:
  


Current Time: Sat Sep 30 01:23:03 CDT 2023