Home » RDBMS Server » Server Administration » When to gather table statistics (Oracle 10.2.0.4.0,SunOS)
When to gather table statistics [message #551852] Sat, 21 April 2012 09:12 Go to next message
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

Re: When to gather table statistics [message #551853 is a reply to message #551852] Sat, 21 April 2012 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 26763
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?
Re: When to gather table statistics [message #551854 is a reply to message #551853] Sat, 21 April 2012 09:30 Go to previous messageGo to next message
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
Re: When to gather table statistics [message #551856 is a reply to message #551854] Sat, 21 April 2012 09:40 Go to previous messageGo to next message
BlackSwan
Messages: 26763
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.
Re: When to gather table statistics [message #551858 is a reply to message #551856] Sat, 21 April 2012 10:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26763
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 Go to previous messageGo to next message
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
Re: When to gather table statistics [message #552019 is a reply to message #551901] Mon, 23 April 2012 03:41 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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.
Re: When to gather table statistics [message #552020 is a reply to message #551901] Mon, 23 April 2012 03:42 Go to previous messageGo to next message
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.
Re: When to gather table statistics [message #553708 is a reply to message #552020] Tue, 08 May 2012 02:03 Go to previous message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

Thank you all for explaining the concept as why we gather stats regularly.
Previous Topic: monitoring unusable objects
Next Topic: Implementing Case-insensitive Search
Goto Forum:
  


Current Time: Mon Apr 12 02:35:33 CDT 2021