如何精准清理Oracle共享池中的特定SQL

如果刷新shared pool，则游标中的所有语句都将被刷新。因此，如果您希望 sql 查询执行硬解析，则可以从共享池中刷新特定的 sql 语句。

11g之后：

-- 根据sql id获取ADDRESS和hash_value
select address, hash_value, sql_text from v$sqlarea where sql_id='79dur52cv5t43'; 

ADDRESS          HASH_VALUE     SQL_TEXT
---------------- ----------   -----------------------------------------------------------------
0000000F38E275D8 2578637955   xxxxxxxx

-- 根据ADDRESS和hash_value 从shared_pool清除
exec dbms_shared_pool.purge('0000000F38E275D8,2578637955','C'); 

如果报错
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.PURGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

需要先安装@?/rdbms/admin/dbmspool.sql

后来有人写了个flush_from_cursor.sql，可以直接用

REM Flushes one cursor out of the shared pool. Works on 11g+
REM To create DBMS_SHARED_POOL, run the DBMSPOOL.SQL script.
REM The PRVTPOOL.PLB script is automatically executed after DBMSPOOL.SQL runs.
REM These scripts are not run by as part of standard database creation.
SPO flush_cursor_&&sql_id..txt;
PRO *** before flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
BEGIN
FOR i IN (SELECT address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.')
LOOP
SYS.DBMS_SHARED_POOL.PURGE(i.address||','||i.hash_value, 'C');
END LOOP;
END;
/
PRO *** after flush ***
SELECT inst_id, loaded_versions, invalidations, address, hash_value
FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1;
SELECT inst_id, child_number, plan_hash_value, executions, is_shareable
FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2;
UNDEF sql_id;
SPO OFF;

输出结果如下：

SQL> @flush_from_cursor.sql
Enter value for sql_id: 0k81dk25hwcx8
*** before flush ***
old 2: FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1
new 2: FROM gv$sqlarea WHERE sql_id = '0k81dk25hwcx8' ORDER BY 1

INST_ID LOADED_VERSIONS INVALIDATIONS ADDRESS HASH_VALUE
---------- --------------- ------------- ---------------- ----------
1 1 1 00000024FC21E608 2332963752
2 1 1 00000027BA545408 2332963752

old 2: FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2
new 2: FROM gv$sql WHERE sql_id = '0k81dk25hwcx8' ORDER BY 1, 2

INST_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I
---------- ------------ --------------- ---------- -
1 0 1169678410 344 Y
2 0 1169678410 344 Y

old 3: FROM gv$sqlarea WHERE sql_id = '&&sql_id.')
new 3: FROM gv$sqlarea WHERE sql_id = '0k81dk25hwcx8')

PL/SQL procedure successfully completed.

*** after flush ***
old 2: FROM gv$sqlarea WHERE sql_id = '&&sql_id.' ORDER BY 1
new 2: FROM gv$sqlarea WHERE sql_id = '0k81dk25hwcx8' ORDER BY 1

INST_ID LOADED_VERSIONS INVALIDATIONS ADDRESS HASH_VALUE
---------- --------------- ------------- ---------------- ----------
1 1 2 00000024FC21E608 2332963752
2 1 1 00000027BA545408 2332963752

old 2: FROM gv$sql WHERE sql_id = '&&sql_id.' ORDER BY 1, 2
new 2: FROM gv$sql WHERE sql_id = '0k81dk25hwcx8' ORDER BY 1, 2

INST_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS I
---------- ------------ --------------- ---------- -
1 0 1169678410 344 Y
2 0 1169678410 344 Y

结语

通过本文的介绍，您可以了解到如何查询和清理Oracle共享池中的指定SQL语句，以优化数据库性能。定期维护共享池、优化SQL代码，是确保数据库高效运行的关键步骤。

「欢迎关注我们的公众号，获取更多技术分享与经验交流。」