Home » RDBMS Server » Performance Tuning » Stale statistics and Dynamic Sampling (Oracle 10gR2 (10.2.0.4))
Stale statistics and Dynamic Sampling [message #423245] Wed, 23 September 2009 01:32 Go to next message
Ferrarist
Messages: 29
Registered: March 2006
Location: Netherlands - Den Haag
Junior Member
Hello everyone,

I have 2 questions that I have googled on but I can't seem to find an answer for.

In an Oracle 10.2 database, when a table has stale statistics, will the optimizer decide to use these statistics when the table is queried or will the optimizer decide to estimate the statistics using dynamic sampling? I know the OPTIMIZER_DYNAMIC_SAMPLING parameter has something to do with this. Let's say this parameter holds the default value 2.

And what if this same table is queried again in an inline view of the same query mentioned above. Will the optimizer choose to use the stale statistics again or use dynamic sampling?

Regards,
Rob
Re: Stale statistics and Dynamic Sampling [message #423248 is a reply to message #423245] Wed, 23 September 2009 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Performance Tuning Guide
Chapter 14 Managing Optimizer Statistics
Section 14.5.6 Estimating Statistics with Dynamic Sampling

Regards
Michel

Re: Stale statistics and Dynamic Sampling [message #423251 is a reply to message #423248] Wed, 23 September 2009 01:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As far as I can see, this does not tell whether the stale stats will or will not be used by the optimizer.
Either I did not read it correctly, or the list of sampling levels says nothing about stale statistics.
Re: Stale statistics and Dynamic Sampling [message #423255 is a reply to message #423251] Wed, 23 September 2009 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
During parsing Oracle does not check if statistics are stale or not, just if they are present or not (at least in 10g and below).

Regards
Michel
Re: Stale statistics and Dynamic Sampling [message #423258 is a reply to message #423245] Wed, 23 September 2009 02:51 Go to previous messageGo to next message
Ferrarist
Messages: 29
Registered: March 2006
Location: Netherlands - Den Haag
Junior Member
Thanks Michel,

So when parsing the optimizer uses the statistics even if they are stale and does not compute new statistics through dynamic sampling.

Regards,
Rob

[Updated on: Wed, 23 September 2009 02:52]

Report message to a moderator

Re: Stale statistics and Dynamic Sampling [message #423268 is a reply to message #423258] Wed, 23 September 2009 03:19 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that it is said that dynamic sampling:
Quote:
Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust.

This applies from level 3 but there is nothing about the meaning of "too out of date to trust".

If we refer to the definition of DBA_TAB_STATISTICS, statistics are stale if:
(inserts + deletes + updates) > 0.1*rowcnt or bitand(flags,1) = 1

Columns are from sys.mon_mods_all$ without knowing how "flags" is set.
Is this that is used for "too out of date to trust"? No clue.

Regards
Michel



Previous Topic: FLUSH SHARED_POOL
Next Topic: Bulk insert is faster then insert into select * ......
Goto Forum:
  


Current Time: Mon Sep 26 06:02:54 CDT 2016

Total time taken to generate the page: 0.06132 seconds