Oracle_dbms_shared_pool.purge 包的使用

自10.2.0.4 开始oracle引入一种方法，它可以删除指定的缓存在库缓存中的shared cursor,它可以让oracle 在执行目标SQL时使用硬解析。

现在，我们来测试一下使用DBMS_SHARED_POOL.PURGE.执行一下SQL:

SQL> SELECT  SQL_TEXT,SQL_ID,VERSION_COUNT ,executions,ADDRESS,HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM SDXJ.%';

SQL_TEXT                                                                                                                                        SQL_ID               VERSION_COUNT EXECUTIONS ADDRESS                  HASH_VALUE
---------------------------------------------------------------------- -------------------- ------------- ---------- -------------------- --------------
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 60000     6d2d6y357kfws                 1        1                           B8BD275C                 3396942744
SELECT COUNT(*) FROM SDXJ.t2 WHERE OBJECT_ID BETWEEN 999 AND 600000    3j7bxvb0qusb9                  2        3                           B4C6E11C                 3245171049
SELECT COUNT(*) FROM SDXJ.t2 WHERE OBJECT_ID BETWEEN 989 AND 600000    ccw4344g2uvmg                2       3                          B4BDD76C                  506293871
SELECT COUNT(*) FROM SDXJ.T2  WHERE OBJECT_ID BETWEEN :x AND :y                 a6fkgvf7s34vd                      2       5                        B8FD0970                2407633773

---查看执行计划

Plan hash value: 1410530761
---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |       |       |    46 (100)|          |
|   1 |  SORT AGGREGATE        |        |     1 |     5 |            |          |
|*  2 |   FILTER               |        |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| IDX_T1 | 54911 |   268K|    46   (3)| 00:00:01 

---------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T2@SEL$1
Outline Data
------------
  /*+
      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------

   1 - :X (NUMBER): 999
   2 - :Y (NUMBER): 60000

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:X<=:Y)
   3 - filter(("OBJECT_ID"<=:Y AND "OBJECT_ID">=:X))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT(*)[22]
Note
-----
   - SQL plan baseline SQL_PLAN_8c75u4j38bvqy8856a46c used for this statement

已选择56行。

将上述查询到的列address 和 hash-value 拼成一个字符串（中间用逗号分隔）后作为第一个参数传入包，第二个参数为常量:c(表示清除的是shared cursor).

--执行的sql

SQL> exec DBMS_SHARED_POOL.PURGE('B8FD0970,2407633773','c');

PL/SQL 过程已成功完成。

--再次查询结果
SQL> SELECT  SQL_TEXT,SQL_ID,VERSION_COUNT ,executions,ADDRESS,HASH_VALUE FROM V$SQLAREA WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM SDXJ.%';

SQL_TEXT                                                               SQL_ID               VERSION_COUNT EXECUTIONS ADDRESS                  HASH_VALUE
---------------------------------------------------------------------- -------------------- ------------- ---------- -------------------- --------------
SELECT COUNT(*) FROM SDXJ.T1 WHERE OBJECT_ID BETWEEN 999 AND 60000     6d2d6y357kfws                 1     1 B8BD275C                 3396942744
SELECT COUNT(*) FROM SDXJ.t2 WHERE OBJECT_ID BETWEEN 999 AND 600000    3j7bxvb0qusb9                  2     3 B4C6E11C                 3245171049
SELECT COUNT(*) FROM SDXJ.t2 WHERE OBJECT_ID BETWEEN 989 AND 600000    ccw4344g2uvmg                 2     3 B4BDD76C                  506293871

SELECT COUNT(*) FROM SDXJ.T2  WHERE OBJECT_ID BETWEEN :x AND :y                 a6fkgvf7s34vd                      2       5       B8FD0970                 2407633773

  --这行消失了。

----- 未完待续