OraFAQ Forum: Server Administration » When to gather table statistics

Home » RDBMS Server » Server Administration » When to gather table statistics (Oracle 10.2.0.4.0,SunOS)

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

When to gather table statistics [message #551852]

Sat, 21 April 2012 09:12

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

Hi,

Why do we gather table statistics manually ?Is it because of database performance.

I know In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the
likelihood of poorly performing SQL statements due to stale or invalid statistics and
enhances SQLexecution performance by providing optimal input to the query optimizer.

Optimizer gathers statistics when 10% table rows have been changed.

Please give your comments

Report message to a moderator

Re: When to gather table statistics [message #551853 is a reply to message #551852]

Sat, 21 April 2012 09:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Why do we gather table statistics manually ?
I do not gather table statistics manually?
Why do you do so?

Report message to a moderator

Re: When to gather table statistics [message #551854 is a reply to message #551853]

Sat, 21 April 2012 09:30

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

Swan
Quote:

I do not gather table statistics manually?
Why do you do so?

I just want to know why oracle collects table statistics every day automatically.Want to know the reason of stats gather.

Thank you

Report message to a moderator

Re: When to gather table statistics [message #551856 is a reply to message #551854]

Sat, 21 April 2012 09:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I just want to know why oracle collects table statistics every day automatically.
>Want to know the reason of stats gather.
the CBO uses the statistics to determine "optimal" PLAN for every SQL statement.
If/when statistics do not match reality, then SQL performance can suffer.

Report message to a moderator

Re: When to gather table statistics [message #551858 is a reply to message #551856]

Sat, 21 April 2012 10:50

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

Swan,
Quote:

If/when statistics do not match reality, then SQL performance can suffer.

Can you elaborate your above sentence?

Thank you

[Updated on: Sat, 21 April 2012 11:09]

Report message to a moderator

Re: When to gather table statistics [message #551860 is a reply to message #551858]

Sat, 21 April 2012 11:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Hopefully you are willing & able to Read The Fine Manual

http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF94638

[Updated on: Sat, 21 April 2012 11:17]

Report message to a moderator

Re: When to gather table statistics [message #551901 is a reply to message #551860]

Sun, 22 April 2012 03:45

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

Swan,
>the CBO uses the statistics to determine "optimal" PLAN for every SQL statement.

What if table statistics are not up to date? Would the query performance will be slow ?

Say for example one table was last analyzed on 20th Dec 2011?

Would there be slowness in the query using the table ?

Can you please clarify above statements ?

Thank you

Report message to a moderator

Re: When to gather table statistics [message #552019 is a reply to message #551901]

Mon, 23 April 2012 03:41

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

Senior Member

If the volume of data hasn't noticably changed since the statistics were last gathered then the performance shouldn't change.
If the volume of data has noticably changed since the statistics were last gathered then the performance may suffer.
The reasons for this are varied and complex and you should read the manuals if you really want to understand.

Report message to a moderator

Re: When to gather table statistics [message #552020 is a reply to message #551901]

Mon, 23 April 2012 03:42

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

The CBO could choose a suboptimal path, based on non-accurate statistics, slowing the query down. If your data never changes, the old statistics are still valid.

Report message to a moderator

Re: When to gather table statistics [message #553708 is a reply to message #552020]

Tue, 08 May 2012 02:03

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

Thank you all for explaining the concept as why we gather stats regularly.

Report message to a moderator

Previous Topic:	monitoring unusable objects
Next Topic:	Implementing Case-insensitive Search

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 01:03:25 CDT 2024