从共享池中失效指定sql语句的执行计划

郑全

中秋节一早起来,就接到电话,用户反馈系统有点慢哟,登陆系统,
发现一个sql语句的执行计划有点怪异,多了一个全表扫描,而且该表记录上百万,增加全表扫描,肯定慢
语句为:
SELECT A.RECIPE_ID
  FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1)) B
WHERE A.RECIPE_ID = B.COLUMN_VALUE
    OR A.PARENTID = B.COLUMN_VALUE;
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |       |       |   142K(100)|          |
|   1 |  CONCATENATION                       |                             |       |       |            |          |
|*  2 |   HASH JOIN                          |                             |   698M|    27G|   140K  (7)| 00:12:27 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH | STR2LIST                    |  8168 | 16336 |    17   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL                 | CIS_OPD_CLINIC              |  8547K|   326M|   132K  (2)| 00:11:45 |
|   5 |   NESTED LOOPS                       |                             |       |       |            |          |
|   6 |    NESTED LOOPS                      |                             |   408 | 17136 |  2470   (1)| 00:00:14 |
|   7 |     COLLECTION ITERATOR PICKLER FETCH| STR2LIST                    |  8168 | 16336 |    17   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN                | PK_OUTPATIENT_CLINIC_ORDER2 |     1 |       |     1   (0)| 00:00:01 |
|*  9 |    TABLE ACCESS BY INDEX ROWID       | CIS_OPD_CLINIC              |     1 |    40 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

试作修改该语句,加一个hint
SELECT /*+ rule */ A.RECIPE_ID
  FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1)) B
WHERE A.RECIPE_ID = B.COLUMN_VALUE
    OR A.PARENTID = B.COLUMN_VALUE;
重新执行,发现非常快
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |       |       |  2660 (100)|          |
|   1 |  NESTED LOOPS                       |                             |       |       |            |          |
|   2 |   NESTED LOOPS                      |                             | 31393 |  1900K|  2660   (1)| 00:00:15 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2LIST                    |  8168 | 16336 |    17   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS      |                             |       |       |            |          |
|   5 |     BITMAP OR                       |                             |       |       |            |          |
|   6 |      BITMAP CONVERSION FROM ROWIDS  |                             |       |       |            |          |
|*  7 |       INDEX RANGE SCAN              | PK_OUTPATIENT_CLINIC_ORDER2 |       |       |     1   (0)| 00:00:01 |
|   8 |      BITMAP CONVERSION FROM ROWIDS  |                             |       |       |            |          |
|*  9 |       INDEX RANGE SCAN              | CIS_OPD_CLINIC_IX_PARENTID1 |       |       |     1   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID       | CIS_OPD_CLINIC              |     4 |   240 |  2660   (1)| 00:00:15 |
-------------------------------------------------------------------------------------------------------------------
实际结果,应该是很少行,怎么会把全表进行扫描呢?
检查该表的统计信息,发现该表的num_rows为null,看来统计信息没有收集呀
重新收集统计信息
execute sys.dbms_stats.gather_table_stats(ownname=>'xxx',tabname=>'CIS_OPD_CLINIC',cascade=>true,estimate_percent=>100);
再去执行该语句,与优化器修改为rule一样,执行正常.
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |       |       |  2660 (100)|          |
|   1 |  NESTED LOOPS                       |                             |       |       |            |          |
|   2 |   NESTED LOOPS                      |                             | 31393 |  1900K|  2660   (1)| 00:00:15 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2LIST                    |  8168 | 16336 |    17   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS      |                             |       |       |            |          |
|   5 |     BITMAP OR                       |                             |       |       |            |          |
|   6 |      BITMAP CONVERSION FROM ROWIDS  |                             |       |       |            |          |
|*  7 |       INDEX RANGE SCAN              | PK_OUTPATIENT_CLINIC_ORDER2 |       |       |     1   (0)| 00:00:01 |
|   8 |      BITMAP CONVERSION FROM ROWIDS  |                             |       |       |            |          |
|*  9 |       INDEX RANGE SCAN              | CIS_OPD_CLINIC_IX_PARENTID1 |       |       |     1   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID       | CIS_OPD_CLINIC              |     4 |   240 |  2660   (1)| 00:00:15 |
-------------------------------------------------------------------------------------------------------------------

但前端反应现在系统依旧慢,什么情况呢,检查系统中该语句的实际执行情况:
  
1.找到sga中实际执行的sql语:
  SQL>   SELECT sql_id, child_number , sql_text
           FROM v$sql
          WHERE  upper(sql_text) Like '%SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 )) B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE%';
SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
cn2c9mf5f9xda            0 SELECT sql_id, child_number , sql_text FROM v$sql WHERE  upper(sql_text) Like '%
07zdmp13w1zw6            0 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6            1 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6            2 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
07zdmp13w1zw6            3 SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(ZLBASE.STR2LIST(:B1 )) B WHERE A
gxa9q5swsq2cr            0    select sql_id, address, hash_value, executions, loads, parse_calls, invalidat
3akum7jd5b8j5            0   SELECT sql_id, child_number , sql_text FROM v$sql WHERE  upper(sql_text) Like
1uwmkpzw17qb6            0 explain plan set statement_id = 'SYS' for  SELECT A.RECIPE_ID FROM CIS_OPD_CLINI
2na7kxsh0msa6            0 select sql_id, address, hash_value, executions, loads, parse_calls, invalidation
9 rows selected
这里我们看到,sql_id=07zdmp13w1zw6
2.检查该语句的执行计划
  select * from table(dbms_xplan.display_cursor('07zdmp13w1zw6'));
SQL_ID  07zdmp13w1zw6, child number 0
-------------------------------------
SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 ))
B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE

Plan hash value: 2166250467

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |       |       |   142K(100)|          |
|   1 |  CONCATENATION                       |                             |       |       |            |          |
|*  2 |   HASH JOIN                          |                             |   698M|    27G|   140K  (7)| 00:12:27 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH | STR2LIST                    |  8168 | 16336 |    17   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL                 | CIS_OPD_CLINIC              |  8547K|   326M|   132K  (2)| 00:11:45 |
|   5 |   NESTED LOOPS                       |                             |       |       |            |          |
|   6 |    NESTED LOOPS                      |                             |   408 | 17136 |  2470   (1)| 00:00:14 |
|   7 |     COLLECTION ITERATOR PICKLER FETCH| STR2LIST                    |  8168 | 16336 |    17   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN                | PK_OUTPATIENT_CLINIC_ORDER2 |     1 |       |     1   (0)| 00:00:01 |
|*  9 |    TABLE ACCESS BY INDEX ROWID       | CIS_OPD_CLINIC              |     1 |    40 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."PARENTID"=VALUE(KOKBF$))
   8 - access("A"."RECIPE_ID"=VALUE(KOKBF$))
   9 - filter(LNNVL("A"."PARENTID"=VALUE(KOKBF$)))

Note
-----
   - dynamic sampling used for this statement (level=2)
   
   看来系统中,该语句仍旧使用以前的执行计划,新的修改,对该语句没有起作用,如果让该语句使用新的执行执行计划,其实就是让以前的sql语句执行计划失效.
   办法应该有很多,比如修改表结构,执行授权语句,清除共享池,但这些动作都比较大,能否只让该sql语句失效,上网上搜索,发现11g 的DBMS_SHARED_POOL包新增了PURGE功能，
   可以完美的解决这个问题。
   
   过程PURGE的第一个参数为V$SQLAREA中用逗号分隔的ADDRESS列和HASH_VALUE列的值，第二个参数’c’表示PURGE的对象是CURSOR，具体可以查看该过程的定义信息。
   简单的语法为：
   exec dbms_shared_pool.purge('ADDRESS,HASH_VALUE', 'C');
      
2.获取sql语句 addess, hash value
SQL>    select sql_id, address, hash_value, executions, loads, parse_calls, invalidations
  2   from v$sqlarea
  3   where upper(sql_text) like '%SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 )) B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE%';
SQL_ID        ADDRESS          HASH_VALUE EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS
------------- ---------------- ---------- ---------- ---------- ----------- -------------
cn2c9mf5f9xda 00000007C249CD38 2330260906          6          1           3             0
07zdmp13w1zw6 00000007CF3F9B68 1203830662         24        135          16            31
gxa9q5swsq2cr 00000007C95E2680  965413271          1          1           1             0
1uwmkpzw17qb6 00000007CDDFE060 4162050406          1          1           1             1
2na7kxsh0msa6 00000007CF243B00  537518406         10          1           3             0
3.清除该游标
exec dbms_shared_pool.purge('00000007CF3F9B68,1203830662', 'c');
4.再次去提取该sql的执行计划，找不到该语句了。再等一下，又可以看到了该语句了，而且执行计划已经变化。


SQL_ID  07zdmp13w1zw6, child number 0
-------------------------------------
SELECT A.RECIPE_ID FROM CIS_OPD_CLINIC A, TABLE(xxx.STR2LIST(:B1 ))
B WHERE A.RECIPE_ID = B.COLUMN_VALUE OR A.PARENTID = B.COLUMN_VALUE

Plan hash value: 1700308364

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |       |       |  2660 (100)|          |
|   1 |  NESTED LOOPS                       |                             |       |       |            |          |
|   2 |   NESTED LOOPS                      |                             | 31393 |  1900K|  2660   (1)| 00:00:15 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2LIST                    |  8168 | 16336 |    17   (0)| 00:00:01 |
|   4 |    BITMAP CONVERSION TO ROWIDS      |                             |       |       |            |          |
|   5 |     BITMAP OR                       |                             |       |       |            |          |
|   6 |      BITMAP CONVERSION FROM ROWIDS  |                             |       |       |            |          |
|*  7 |       INDEX RANGE SCAN              | PK_OUTPATIENT_CLINIC_ORDER2 |       |       |     1   (0)| 00:00:01 |
|   8 |      BITMAP CONVERSION FROM ROWIDS  |                             |       |       |            |          |
|*  9 |       INDEX RANGE SCAN              | CIS_OPD_CLINIC_IX_PARENTID1 |       |       |     1   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID       | CIS_OPD_CLINIC              |     4 |   240 |  2660   (1)| 00:00:15 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("A"."RECIPE_ID"=VALUE(KOKBF$))
   9 - access("A"."PARENTID"=VALUE(KOKBF$))