ANALYZE and GATHER STATS [message #638370] |
Wed, 10 June 2015 03:38 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi All,
In our company we are using the below method for getting better performance in every weekend.
But I have a doubt, whether will it do anything against ROW CHAINING, UNUSED SPACE of TABLE/INDEX?
exec DBMS_UTILITY.ANALYZE_SCHEMA ('******','COMPUTE');
exec dbms_stats.gather_schema_stats('******');
Also I am searching in the internet simultaneously.
Kindly suggest
Muktha
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: ANALYZE and GATHER STATS [message #638397 is a reply to message #638387] |
Wed, 10 June 2015 10:43 |
John Watson
Messages: 8964 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I haven't tested (and I'm not going to) but....
I think that the analyze may destroy some customizations for histograms and preferences. Furthermore, it will reset the dba_tab_modifications so that the dbms_stats call won't actually analyze anything. Open to correction, of course.
|
|
|
|
Re: ANALYZE and GATHER STATS [message #638408 is a reply to message #638399] |
Thu, 11 June 2015 01:09 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi All,
Thanks for the involving,
I understood, there is no relation between ANALYZE\STATS with unused spaces.
And now we will start use only "exec dbms_stats.gather_schema_stats('******');".
But what could be the harm, if we use "exec DBMS_UTILITY.ANALYZE_SCHEMA ('******','COMPUTE');"
Like John watson told,
I think that the analyze may destroy some customizations for histograms and preferences. Furthermore, it will reset the dba_tab_modifications so that the dbms_stats call won't actually analyze anything.
Also if the "DBMS_UTILITY.ANALYZE_SCHEMA" package is deprecated, why this is still available in 12c DB, is there any purpose for it?
Regards
Muktha
|
|
|
|
Re: ANALYZE and GATHER STATS [message #638410 is a reply to message #638409] |
Thu, 11 June 2015 01:21 |
muktha_22
Messages: 527 Registered: December 2009
|
Senior Member |
|
|
Hi Michel,
Yes, I read,
We need to delete all the statistics, then start using only the "exec dbms_stats.gather_schema_stats('******');" package.
As we use both, it may confuse the Optimizer to make a good execution plan.
Am I right?
Regards
Muktha
|
|
|
Re: ANALYZE and GATHER STATS [message #638412 is a reply to message #638408] |
Thu, 11 June 2015 01:23 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
@muktha_22,
From documentation,
Quote:ANALYZE
For the collection of most statistics, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. See Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_STATS package.
Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer:
> To use the VALIDATE or LIST CHAINED ROWS clauses
> To collect information on freelist blocks
|
|
|