OraFAQ Forum: Performance Tuning » Analyze stats running long

Home » RDBMS Server » Performance Tuning » Analyze stats running long (Oracle 11g R2, Windows 32 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Analyze stats running long [message #591825]

Wed, 31 July 2013 12:22

nvembu
Messages: 4
Registered: July 2013

Junior Member

Hi all,

I have a Datamart DB which has a continuously increasing volume.
I run a daily optimize job, to have the data analysed using the below:-

EXECUTE dbms_stats.gather_schema_stats(ownname=> 'xxx' ,estimate_percent=> 25 , DEGREE => 4, cascade=> TRUE );

analyze table xxx.abc compute statistics;

But this optimization itself is taking nearly 4 hrs to complete and I can't afford to have the delay.

Is there a better way of running this optimization?

Please help. Thanks.

Report message to a moderator

Re: Analyze stats running long [message #591827 is a reply to message #591825]

Wed, 31 July 2013 12:35

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Never ever use ANALYSE TABLE to compute statistics ABOVE ALL when you also use dbms_stats, you screw up the statistics.

Regards
Michel

Report message to a moderator

Re: Analyze stats running long [message #591830 is a reply to message #591827]

Wed, 31 July 2013 13:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there a better way of running this optimization?
Since V10 Oracle has default job that collects statitics; as needed.
I suspect that you Brute Force approach, consumes more resources than it save.

Can you post SQL & results that show & prove that your code results in any measurable benefits?
Or are you doing it just because you think YOU need to do something; regardless of it effectiveness?

Report message to a moderator

Re: Analyze stats running long [message #591832 is a reply to message #591825]

Wed, 31 July 2013 13:00

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

"Best Practice" (if you believe in such things) would be to enable the stats gathering autotask, and let Uncle Oracle decide what, and how much, needs to be analyzed every day. It runs with a low priority, so it shouldn't disrupt any other work.

Report message to a moderator

Re: Analyze stats running long [message #591836 is a reply to message #591832]

Wed, 31 July 2013 13:18

nvembu
Messages: 4
Registered: July 2013

Junior Member

Michel:- are you saying that AnalyZe table on top of DBMS_Stats screws up the purpose. So, should I use DBMS_STATS or ANALYZE TABLE

Black Swan:- If I dont run this Analyze & DBMS_STATS, my successive reports run forever.

John Watson:- how do I go about setting up AUTOTASK?

I have limited experience in perfomance tuning, so any help would be appreciated!

Report message to a moderator

Re: Analyze stats running long [message #591840 is a reply to message #591836]

Wed, 31 July 2013 13:25

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Michel:- are you saying that AnalyZe table on top of DBMS_Stats screws up the purpose. So, should I use DBMS_STATS or ANALYZE TABLE

Yes and no. Yes you screw up the statistics; no do not use either one or the other one, ALWAYS use dbms_stats, ANALYZE is obsolete to gather statistics.

Quote:

John Watson:- how do I go about setting up AUTOTASK?

select * from DBA_AUTOTASK_TASK;

Search for AUTOTASK in the documentation.

Regards
Michel

Report message to a moderator

Re: Analyze stats running long [message #591866 is a reply to message #591836]

Wed, 31 July 2013 22:31

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Here is an example where a poster in asktom is confused about ANALYZE being obsolete/deprecated/not supported. Tom clarifies his doubts http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2581065100346263573

Sorry for the long url, not able to make it short

Report message to a moderator

Re: Analyze stats running long [message #591888 is a reply to message #591866]

Thu, 01 August 2013 02:49

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

You make it short like this:

[url=http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2581065100346263573]analyze in 11g[/url]

Which gives:
analyze in 11g

Not that the length really matters.

Report message to a moderator

Re: Analyze stats running long [message #591960 is a reply to message #591888]

Thu, 01 August 2013 12:09

nvembu
Messages: 4
Registered: July 2013

Junior Member

Thank you for all the references.

Report message to a moderator

Re: Analyze stats running long [message #592165 is a reply to message #591960]

Mon, 05 August 2013 05:15

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

Coming to the stats collection of tables, you may find the below url useful, it has two parts -- look for the pdf in the same page.
Lies, damned lies, and statistics Part 2

It is advised in 11g to set dbms_stats parameter values to its default.
How does AUTO_SAMPLE_SIZE work in Oracle Database 11g

Regards,
Pointers

Report message to a moderator

Previous Topic:	INSERT INTO ... SELECT FROM t@dblink
Next Topic:	Full Index Scan issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 18:23:38 CST 2025