Oracle删除一条SQL在Shared Pool里缓存的执行计划的三(2)

zx@MYDB>select object_name from s1 where object_id=20;
 
OBJECT_NAME
------------------------------
ICOL$
 
zx@MYDB>select object_name from s1 where object_id=30;
 
OBJECT_NAME
------------------------------
I_COBJ#
 
zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%';
 
SQL_TEXT                                    SQL_ID                VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------
select object_name from s1 where object_id=20                  1s45nwjtws2tj                      1    1
select object_name from s1 where object_id=30                  1hdyqyxhtavqs                      1    1
 
zx@MYDB>select object_name from s1 where object_id=20;
 
OBJECT_NAME
------------------------------
ICOL$
 
zx@MYDB>select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select object_name from s%';
 
SQL_TEXT                                    SQL_ID                VERSION_COUNT EXECUTIONS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ----------
select object_name from s1 where object_id=20                  1s45nwjtws2tj                      1    2
select object_name from s1 where object_id=30                  1hdyqyxhtavqs                      1    1

上面查询对表s1做了两个不同的查询，从输出可以看出上面执行的两个SQL的执行计划和解析树被缓存到了Shared Pool中，再次执行时会直接用缓存的执行计划(EXECUTIONS变为2)。现在要删除object_id=20对应SQL的执行计划，这里选择对表添加注释(COMMENT)，它也是DDL操作。

zx@MYDB>comment on table s1 is 'test shared cursor';
 
Comment created.
 
zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS from v$sqlarea where sql_text like 'select object_name from s%';
 
SQL_TEXT                                    SQL_ID                VERSION_COUNT EXECUTIONS OBJECT_STATUS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- ---------------
select object_name from s1 where object_id=20                  1s45nwjtws2tj                      1    2 INVALID_UNAUTH
select object_name from s1 where object_id=30                  1hdyqyxhtavqs                      1    1 INVALID_UNAUTH
 
zx@MYDB>select object_name from s1 where object_id=20;
 
OBJECT_NAME
------------------------------
ICOL$
 
zx@MYDB>select sql_text,sql_id,version_count,executions,OBJECT_STATUS from v$sqlarea where sql_text like 'select object_name from s%';
 
SQL_TEXT                                    SQL_ID                VERSION_COUNT EXECUTIONS OBJECT_STATUS
-------------------------------------------------------------------------------- --------------------------------------- ------------- ---------- ---------------
select object_name from s1 where object_id=20                  1s45nwjtws2tj                      1    1 VALID
select object_name from s1 where object_id=30                  1hdyqyxhtavqs                      1    1 INVALID_UNAUTH