Home » RDBMS Server » Performance Tuning » ANALYZE and GATHER STATS (windows 2012, oracle 12c)
ANALYZE and GATHER STATS [message #638370] Wed, 10 June 2015 03:38 Go to next message
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 #638371 is a reply to message #638370] Wed, 10 June 2015 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Kindly explain your question, it is clear as mud.

Re: ANALYZE and GATHER STATS [message #638375 is a reply to message #638370] Wed, 10 June 2015 06:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Analyze_schema is deprecated.
Re: ANALYZE and GATHER STATS [message #638382 is a reply to message #638370] Wed, 10 June 2015 07:39 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
You should be using the DBMS_STATS package.
Re: ANALYZE and GATHER STATS [message #638383 is a reply to message #638382] Wed, 10 June 2015 07:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gazzag wrote on Wed, 10 June 2015 05:39
You should be using the DBMS_STATS package.



like OP is already doing?

>exec dbms_stats.gather_schema_stats('******');
Re: ANALYZE and GATHER STATS [message #638384 is a reply to message #638383] Wed, 10 June 2015 07:47 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
This too:
Quote:
exec DBMS_UTILITY.ANALYZE_SCHEMA ('******','COMPUTE');
Re: ANALYZE and GATHER STATS [message #638385 is a reply to message #638384] Wed, 10 June 2015 07:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
gazzag wrote on Wed, 10 June 2015 05:47
This too:
Quote:
exec DBMS_UTILITY.ANALYZE_SCHEMA ('******','COMPUTE');



above is like giving chicken soup to a dead man. It won't make anything worse (except waste system resources).
Re: ANALYZE and GATHER STATS [message #638386 is a reply to message #638385] Wed, 10 June 2015 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Using both statements and mixing statistics is the right way to fool the optimizer.

Re: ANALYZE and GATHER STATS [message #638387 is a reply to message #638386] Wed, 10 June 2015 07:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Using both statements and mixing statistics is the right way to fool the optimizer.

IMO, the last one in, WINS so DBMS_STAT values will be used by CBO.
Re: ANALYZE and GATHER STATS [message #638389 is a reply to message #638387] Wed, 10 June 2015 08:06 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
And what does the "O" stand for in "IMO"? Wink I agree with Michel.

[Edit: addendum]

[Updated on: Wed, 10 June 2015 08:07]

Report message to a moderator

Re: ANALYZE and GATHER STATS [message #638393 is a reply to message #638389] Wed, 10 June 2015 10:03 Go to previous messageGo to next message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member

gazzag wrote on Wed, 10 June 2015 08:06
And what does the "O" stand for in "IMO"? Wink I agree with Michel.

[Edit: addendum]


IMO = In My Opinion


Cheers,
Brian
Re: ANALYZE and GATHER STATS [message #638394 is a reply to message #638370] Wed, 10 June 2015 10:05 Go to previous messageGo to next message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member

muktha_22 wrote on Wed, 10 June 2015 03:38
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?


As said, no reason to use DBMS_UTILITY. Use just the call to DBMS_STATS instead.

Those calls are calculating statistics that can be used by the CBO for determining an optimal execution plan. They do nothing to fix/resolve row chaining or modify the unused space of a segment.

Cheers,
Brian
Re: ANALYZE and GATHER STATS [message #638397 is a reply to message #638387] Wed, 10 June 2015 10:43 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #638399 is a reply to message #638397] Wed, 10 June 2015 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I had this case with a previous client: someone had the good idea to execute an ANALYZE and then all execution plans were bad and performances a nightmare. The only solution was to delete all statistics using both ANALYZE and DBMS_STATS and recompute the statistics with DBMS_STATS.

Re: ANALYZE and GATHER STATS [message #638408 is a reply to message #638399] Thu, 11 June 2015 01:09 Go to previous messageGo to next message
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 #638409 is a reply to message #638408] Thu, 11 June 2015 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But what could be the harm,

Did you read ALL the messages?
I say that because the answer is there.

Quote:
why this is still available in 12c DBc


Try to think a little bit; what is your opinion?

[Updated on: Thu, 11 June 2015 01:14]

Report message to a moderator

Re: ANALYZE and GATHER STATS [message #638410 is a reply to message #638409] Thu, 11 June 2015 01:21 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Oracle Performance Issue linked to Concurrency
Next Topic: SQL Tuning Advise
Goto Forum:
  


Current Time: Thu Mar 28 17:41:10 CDT 2024