OraFAQ Forum: Performance Tuning » Speed Up the gathering of stats.

Home » RDBMS Server » Performance Tuning » Speed Up the gathering of stats. (Windows XP, Oracle 10g)

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

Speed Up the gathering of stats. [message #510088]

Thu, 02 June 2011 09:00

oraranjangmailcom
Messages: 67
Registered: April 2010

Member

Hi

I am gathering stats by using below block i.e., for some 3 million records and there are 6 indexes on the table. What is the relevance of value 4 here (i.e., method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4')?
If I increase 4 to 250 will there be any speed change in gathering stats. My intention is to speed up the gathering of stats.

begin
dbms_stats.gather_table_stats(
ownname => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
tabname => 'LEGAL_VIEW_TARGET',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4',
cascade => TRUE
);
END;

Thanks in Advance.

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510093 is a reply to message #510088]

Thu, 02 June 2011 09:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>If I increase 4 to 250 will there be any speed change in gathering stats
METHOD_OPT is for histograms.

[Updated on: Thu, 02 June 2011 09:08]

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510096 is a reply to message #510093]

Thu, 02 June 2011 09:10

oraranjangmailcom
Messages: 67
Registered: April 2010

Member

If I increase 4 to 250 will there be any speed up in gathering histograms.

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510100 is a reply to message #510096]

Thu, 02 June 2011 09:15

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

Senior Member

Have you considered reading the docs to see what that setting does?

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510107 is a reply to message #510096]

Thu, 02 June 2011 09:21

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

May or may not. It has nothing to do with the "Speed of gathering".
It will affect the histograms and your plan.
If you have no idea about your data, just use FOR ALL COLUMNS SIZE AUTO.
I would look into parallel options.

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510111 is a reply to message #510100]

Thu, 02 June 2011 09:31

oraranjangmailcom
Messages: 67
Registered: April 2010

Member

I have gone through the docs and understood different options available in method_opt but as I was unable to find answer to my above question, raised it here...

Actually, '4' was proposed by DBA. I am just trying to understand "If I increase 4 to 250 will there be any speed up in gathering histograms" or "other options in speedup of the below block"

dbms_stats.gather_table_stats(
ownname => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
tabname => 'LEGAL_VIEW_TARGET',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4',
cascade => TRUE
);

[Updated on: Thu, 02 June 2011 09:33]

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510114 is a reply to message #510111]

Thu, 02 June 2011 09:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am just trying to understand "If I increase 4 to 250 will there be any speed up in gathering histograms"
>or "other options in speedup of the below block"

benchmark testing will provide answer(s)

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510117 is a reply to message #510111]

Thu, 02 June 2011 09:49

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Well,
If you choose not to collect any histogram, there is less work done and the stats may get collected faster.
It may have more worse effects with your plan.

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510120 is a reply to message #510114]

Thu, 02 June 2011 09:52

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

Senior Member

More buckets = more detailed histogram.
So increasing the number I would assume is more likely to decrease the speed of stats gathering.
However as Blackswan points out the only way to tell for sure is to test it.

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510231 is a reply to message #510120]

Fri, 03 June 2011 01:31

oraranjangmailcom
Messages: 67
Registered: April 2010

Member

Thanks ...

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510235 is a reply to message #510088]

Fri, 03 June 2011 01:46

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

As stats gathering makes use of sort area, try:

ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET sort_area_size = <some LARGE value>;

HTH.

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510619 is a reply to message #510235]

Mon, 06 June 2011 18:24

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

one way to speed stats collection is to use a QA environment if you have one.

If you have another environment that has the same data (say QA), then you can collect stats there and then move the stats over to your PROD environment. As long as you have the same data and the same version of Oracle and use the same stats collection commands, then you are pretty much good to go with reusing stats. Thus you can generally collect any time when QA is not in use heavily and collect 100% stats. Who cares if it takes 3 days to complete.

Just putting it out there. Kevin

Report message to a moderator

Re: Speed Up the gathering of stats. [message #510633 is a reply to message #510111]

Mon, 06 June 2011 21:37

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

>Actually, '4' was proposed by DBA

Ask the DBA if he meant to recommend DEGREE=>4

Hemant K Chitale

Report message to a moderator

Previous Topic:	Tuning Select Not IN statement(2 Merged)
Next Topic:	Delete statement being running for over 2hrs

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 14:42:51 CST 2025