1，控制方法和命令：
DBMS_STATS Constants
| Name | Type | Description |
|
|
Copies global preferences |
|
|
Lets Oracle decide whether to collect statistics for indexes or not |
|
|
Lets Oracle select the degree of parallelism based on size of the object, number of CPUs and initialization parameters |
|
|
Lets Oracle decide when to invalidate dependent cursors |
|
|
Indicates that auto-sample size algorithms should be used |
|
|
A flag that can be passed to the PURGE_STATS Procedure and unconditionally deletes all the history statistics. The deletion uses |
|
|
A constant used for reclaiming synopsis table space. |
2，失效时间
- when DBMS_STATS modifies statistics for an object, all current cached cursors depending on this object are marked for rolling invalidation. Let's call this time T0.
- the next time a session executes a cursor marked for rolling invalidation, it sets a timestamp. This timestamp can take a (pseudo-)random value up to _optimizer_invalidation_period sec from the time of this parse. The reason it does this is to randomly distribute the actual invalidation so as to avoid multiple parses occurring at the same time as much as possible.The default for this parameter is 18000 sec i.e. 5 hours. Let's call the time of this parse T1 and the timestamp value Tmax. On this (first) execution and parse we reuse the existing cursor i.e. we do not hard-parse and do not use the modified statistics to generate a new plan (it is a soft parse.)
- on every subsequent execution and parse of this cursor (which is now marked for rolling invalidation and timestamped) we check whether the current time T2 exceeds the timestamp Tmax. If not, we reuse the existing cursor again, as happened on the first (soft) parse at time T1. If Tmax has been exceeded, we invalidate the cached cursor and create a new version of it (a new child cursor) which uses the new statistics of the object to generate its execution plan. The new child is marked ROLL_INVALID_MISMATCH in V$SQL_SHARED_CURSOR to explain why we could not share the previous child.
- the next time a session executes a cursor marked for rolling invalidation, it sets a timestamp. This timestamp can take a (pseudo-)random value up to _optimizer_invalidation_period sec from the time of this parse. The reason it does this is to randomly distribute the actual invalidation so as to avoid multiple parses occurring at the same time as much as possible.The default for this parameter is 18000 sec i.e. 5 hours. Let's call the time of this parse T1 and the timestamp value Tmax. On this (first) execution and parse we reuse the existing cursor i.e. we do not hard-parse and do not use the modified statistics to generate a new plan (it is a soft parse.)
Resolving Issues Where Dbms_stats Invalidates Cursors in Auto_invalidate mode (Doc ID 1477717.1)
Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)
