| Stale statistics and Dynamic Sampling [message #423245] |
Wed, 23 September 2009 01:32  |
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 #423251 is a reply to message #423248] |
Wed, 23 September 2009 01:50  |
Frank
Messages: 7901
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 #423268 is a reply to message #423258] |
Wed, 23 September 2009 03:19 |
 |
Michel Cadot
Messages: 68653
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
