浅析Cursor Pins

--统计更新
a)DBMS_STATS gathers statistics for a table, table cluster, or index when the NO_INVALIDATE parameter is FALSE.
--  NO_INVALIDATE  -->FALSE 
--   DBMS_STATS
--   no_invalidate - Do not invalide the dependent cursors if set to TRUE.
--     The procedure invalidates the dependent cursors immediately
--     if set to FALSE.
--     Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
--     invalidate dependend cursors. This is the default. The default
--     can be changed using set_param procedure.
--DDL操作Cursor依赖对象
b)A SQL statement references a schema object, which is later modified by a DDL statement that uses immediate cursor invalidation (default).
--Cursor依赖对象DDL更改immediate invalidation
You can manually specify immediate invalidation on statements such as 
ALTER TABLE ... IMMEDIATE VALIDATION and ALTER INDEX ... IMMEDIATE VALIDATION, 
or set the CURSOR_INVALIDATION initialization parameter to IMMEDIATE at the session or system level.
Note:A DDL statement using the DEFERRED VALIDATION clause overrides the IMMEDIATE setting of the CURSOR_INVALIDATION initialization parameter.
注意:DDL使用DEFERRED VALIDATION覆盖初始化参数CURSOR_INVALIDATION
ALTER TABLE ... PARALLEL    IMMEDIATE INVALIDATION 
ALTER INDEX ... UNUSABLE   IMMEDIATE INVALIDATION 
ALTER INDEX ... REBUILD      IMMEDIATE INVALIDATION 
CREATE INDEX                     IMMEDIATE INVALIDATION 
DROP INDEX                        IMMEDIATE INVALIDATION 
TRUNCATE TABLE on partitioned tables   IMMEDIATE INVALIDATION 
c)Invalidate硬解析
When the preceding conditions are met, the database reparses the affected statements at next execution.
When the database invalidates a cursor, the V$SQL.INVALIDATIONS value increases (for example, from 0 to 1), 
and V$SQL.OBJECT_STATUS shows INVALID_UNAUTH.
--DBMS_STAS:NO_INVALIDATE  -->FALSE(immediate invalidate) 
--分区自动split
--truncate分区清数据
--CURSOR_INVALIDATION = { DEFERRED | IMMEDIATE }
d)Example
-seesion1
create table t_invalidation(
ACCOUNT_NO NUMBER(18) not null,
TX_TIME      TIMESTAMP(6),
c1 number,
c2 number
)
partition by range (TX_TIME)
(
partition p00037 values less than (TIMESTAMP' 2015-08-06 00:00:00'),
partition p00038 values less than (TIMESTAMP' 2015-08-07 00:00:00'),
partition p00039 values less than (TIMESTAMP' 2015-08-08 00:00:00'),
partition p00040 values less than (TIMESTAMP' 2015-08-09 00:00:00')
);
select * from t_invalidation;
--sessin2:truncate partition
SELECT CHILD_NUMBER, EXECUTIONS,PARSE_CALLS, INVALIDATIONS, OBJECT_STATUS FROM V$SQL where sql_text like '%select * from t_invalidation%';
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
           0          1           1             0 VALID
--session1
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
           0          1           1             1 INVALID_UNAUTH
--session2:
dbms_stats.no_invalidate=>FALSE
EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'t_invalidation',no_invalidate => FALSE);

a) Roll invalid判定
When cursors are marked rolling invalid (V$SQL.IS_ROLLING_INVALID is Y), the database gradually performs hard parses over an extended time
Note:When V$SQL.IS_ROLLING_REFRESH_INVALID is Y, the underlying object has changed, but recompilation of the cursor is not required. The database updates metadata in the cursor.
 V$SQL.IS_ROLLING_REFRESH_INVALID =Y,cursor下涉及的对对象被更改,游标recompile不需要,只更新cursor metadata
b)Purpose of Rolling Invalidation--延迟Invalidation目的
Because a sharp increase in hard parses can significantly degrade performance, rolling invalidation—also called deferred invalidation—is useful for workloads that simultaneously invalidate many cursors. The database assigns each invalid cursor a randomly generated time period. SQL areas invalidated at the same time typically have different time periods.
A hard parse occurs only if a query accessing the cursor executes after the time period has expired. In this way, the database diffuses the overhead of hard parsing over time.
--爆发式硬解析增长带来明显性能下降,roll_invalidation用于并发性invalidate cursors：
--每个invalid cursor获取一个随机时间time(<=time window for invalidation of cursors of analyzed objects)减少并发invalidateion对DB的影响影响.
--只有在访问游标的查询在随机时间time过期后执行时才会发生硬解析.通过这种方式数据库就均衡了随着时间的推移硬解析的开销
NAME                                     VALUE                DESCRIBE
---------------------------------------- -------------------- ------------------------------------------------------------
_optimizer_invalidation_period           18000                time window for invalidation of cursors of analyzed objects
注：如parallel SQL语句被标记为无效,在下一次执行时执行硬解析而不管cursor是否满足exipre time(age out)
在RAC环境中,这种技术确保并行执行服务器的执行计划和查询协调器之间的一致性。
c)Roll invalid使用方式
By default, DDL specifies that statements accessing the object use immediate cursor invalidation. For example, if you create a table or an index, then cursors that reference this table or index use immediate invalidation.
--DDL默认使用immediate cursor invalidation
If a DDL statement supports deferred cursor invalidation, then you can override the default behavior by using statements such as ALTER TABLE ... DEFERRED INVALIDATION. The options depend on the DDL statement. For example, ALTER INDEX only supports DEFERRED INVALIDATION when the UNUSABLE or REBUILD option is also specified.
--使用初始化参数CURSOR_INVALIDATION 
An alternative to DDL is setting the CURSOR_INVALIDATION initialization parameter to DEFERRED at the session or system level. A DDL statement using the IMMEDIATE INVALIDATION clause overrides the DEFERRED setting of the CURSOR_INVALIDATION initialization parameter.
d)Rolling Invalidation触发场景
If the DEFERRED INVALIDATION attribute applies to an object, either as a result of DDL or an initialization parameter setting, then statements that access the object may be subject to deferred invalidation. The database marks shared SQL areas as rolling invalid in either of the following circumstances:
--d1.DBMS_STATS gathers statistics for a table, table cluster, or index when the NO_INVALIDATE parameter is set to DBMS_STATS.AUTO_INVALIDATE. This is the default setting.
DBMS_STATS => DBMS_STATS.AUTO_INVALIDATE(默认值)
--d2.One of the following statements is issued with DEFERRED INVALIDATION in circumstances that do not prevent the use of deferred invalidation:
ALTER TABLE on partitioned tables
ALTER TABLE ... PARALLEL    DEFERRED INVALIDATION 
ALTER INDEX ... UNUSABLE    DEFERRED INVALIDATION 
ALTER INDEX ... REBUILD     DEFERRED INVALIDATION 
CREATE INDEX                DEFERRED INVALIDATION 
DROP INDEX                  DEFERRED INVALIDATION 
TRUNCATE TABLE on partitioned tables   DEFERRED INVALIDATION 
A subset of DDL statements require immediate cursor invalidation for DML (INSERT, UPDATE, DELETE, or MERGE) but not SELECT statements. Many factors relating to the specific DDL statements and affected cursors determine whether Oracle Database uses deferred invalidation.
DDL语句的子集要求DML（INSERT、UPDATE、DELETE或MERGE）的游标立即失效,而非SELECT语句。与特定DDL语句和受影响的游标相关的许多因素决定了Oracle数据库是否使用延迟失效。