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
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