Home » RDBMS Server » Performance Tuning » Analyze stats running long (Oracle 11g R2, Windows 32 bit)
Analyze stats running long [message #591825] Wed, 31 July 2013 12:22 Go to next message
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.
Re: Analyze stats running long [message #591827 is a reply to message #591825] Wed, 31 July 2013 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Analyze stats running long [message #591830 is a reply to message #591827] Wed, 31 July 2013 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 22902
Registered: January 2009
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?
Re: Analyze stats running long [message #591832 is a reply to message #591825] Wed, 31 July 2013 13:00 Go to previous messageGo to next message
John Watson
Messages: 4683
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.
Re: Analyze stats running long [message #591836 is a reply to message #591832] Wed, 31 July 2013 13:18 Go to previous messageGo to next message
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!
Re: Analyze stats running long [message #591840 is a reply to message #591836] Wed, 31 July 2013 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59421
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Analyze stats running long [message #591866 is a reply to message #591836] Wed, 31 July 2013 22:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2444
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
Re: Analyze stats running long [message #591888 is a reply to message #591866] Thu, 01 August 2013 02:49 Go to previous messageGo to next message
cookiemonster
Messages: 11073
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.
Re: Analyze stats running long [message #591960 is a reply to message #591888] Thu, 01 August 2013 12:09 Go to previous messageGo to next message
nvembu
Messages: 4
Registered: July 2013
Junior Member
Thank you for all the references.
Re: Analyze stats running long [message #592165 is a reply to message #591960] Mon, 05 August 2013 05:15 Go to previous message
pointers
Messages: 350
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
Previous Topic: INSERT INTO ... SELECT FROM t@dblink
Next Topic: Full Index Scan issue
Goto Forum:
  


Current Time: Wed Oct 22 19:45:54 CDT 2014

Total time taken to generate the page: 0.08097 seconds