直接拼接变量到SQL，每次执行的SQL都不一样，会导致SQL缓存激增，影响正常的SQL的常驻缓存。解决问题的根本办法，就是优化SQL的写法。但是也有很多的情况我们没法修改代码，比如有些是产品的代码，我们没有源代码；或者代码要改的地方太多，修改代码有风险。这时就只能通过数据库层面来考虑优化方案了。
最简单的办法直接执行命令清除SQL缓存：alter system flush shared_pool。但是这种方法会清空整个SQL缓存，在缓存里有用的没用的SQL都会被清除，使SQL缓存起不到该有的作用，另外一次性清空缓存，执行的时间可能会比较长，在数据库繁忙时，有导致数据库崩溃的风险。所以这种方法并不好。
另外一种办法是查出来重复解析超过指定值的SQL，只针对这些SQL从缓存中清除，这样既清理了没用的SQL，又不影响有用的SQL常驻缓存，是一种比较好的办法，只是处理过程就相对比较复杂一点，下面就给出这种方法的具体实现。
我们首先给出这种办法的手工处理方案，然后再给出方案自动的自动处理方案。手工处理方案分三步：
--1.查询非绑定变量的SQL，重复解析次数超过20
SELECT to_char(FORCE_MATCHING_SIGNATURE), COUNT (1)
FROM V$SQL WHERE FORCE_MATCHING_SIGNATURE > 0
AND FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING (COUNT (1) >20) ORDER BY 2 desc;
--2.查询每个超过1小时没有使用的SQL的ADDRESS_HASH_VALUE
select address || ',' || hash_value as ADDRESS_HASH_VALUE
from v$sqlarea
where FORCE_MATCHING_SIGNATURE=6075142212657914118
and last_load_time
--3. 根据ADDRESS_HASH_VALUE从缓存中清除
--方式1： 当SQL执行
call sys.dbms_shared_pool.purge('00000000787B50F0,3727297360', 'c');
--方式2：在存储过程里执行
declare
begin
sys.dbms_shared_pool.purge('00000000787B50F0,3727297360', 'c');
end;
--方式3：在命令行窗口执行
execute sys.dbms_shared_pool.purge('00000000787B50F0,3727297360', 'c');
手工方案还是比较简单的，但是只能用于原理说明，真要手工去执行，SQL太多了，会累死。
自动方案就是根据以上原理，写一个PACKAGE SQL_MANAGEMENT_PKG,完整的代码见文末。主函数为：
--清理SQL缓存,查询缓存中数量超过P_MAX_SQL_COUNT的非绑定变量的SQL语句进行清理，清理时间不超过P_MAX_EXECUTE_TIME秒
procedure clean_sql_pool(P_MAX_SQL_COUNT integer,P_MAX_EXECUTE_TIME integer);
我们可以通过手工来执行这个函数，但是为了自动处理，我们可以定义一个定时任务来执行：
declare
job number;
begin
--任务每隔10分钟执行一次
--清理数量超过20的SQL缓存，每次最多清理10秒时间
sys.dbms_job.submit(job,'SQL_MANAGEMENT_PKG.clean_sql_pool(20,10);',sysdate,'sysdate+10/1440');
end;
PACKAGE SQL_MANAGEMENT_PKG的完整代码如下：
create or replace package SQL_MANAGEMENT_PKG is
-- Author : 程序员跳跳鱼
-- Created : 2019/10/21 9:13:28
-- Purpose : SQL 缓存清理
--调试，true会输出调试日志
IS_DEBUG BOOLEAN:=false;
--调试睡眠，true会sleep延长执行时间
IS_DEBUG_SLEEP BOOLEAN:=false;
--清理SQL缓存,查询缓存中数量超过P_MAX_SQL_COUNT的非绑定变量的SQL语句进行清理，清理时间不超过P_MAX_EXECUTE_TIME秒
procedure clean_sql_pool(P_MAX_SQL_COUNT integer,P_MAX_EXECUTE_TIME integer);
--清理SQL缓存
procedure clean_sql_pool(P_MAX_SQL_COUNT integer,
P_MAX_EXECUTE_TIME integer,
P_IS_DEBUG BOOLEAN,
P_IS_DEBUG_SLEEP BOOLEAN);
--等待一定时间，调试用
procedure sleep(P_SECOND INTEGER) ;
--输出日志
procedure log(P_MESSAGE VARCHAR2);
--输出调试日志
procedure log_debug(P_MESSAGE VARCHAR2);
--判断是否到达最大执行时间
function is_timeout(p_start_time date, p_max_execute_time integer) return boolean;
end SQL_MANAGEMENT_PKG;
/
create or replace package body SQL_MANAGEMENT_PKG is
procedure clean_sql_pool(P_MAX_SQL_COUNT integer,
P_MAX_EXECUTE_TIME integer) is
V_START_TIME DATE;
V_CLEAN_COUNT_SUM INTEGER;
V_CLEAN_COUNT INTEGER;
begin
log('clean sql pool start ...');
V_CLEAN_COUNT_SUM := 0;
select sysdate into V_START_TIME from dual;
--查询缓存中数量超过P_MAX_SQL_COUNT的非绑定变量的SQL语句
for SQL_INFO in (SELECT to_char(FORCE_MATCHING_SIGNATURE) as signature,
COUNT(1) as sql_count
FROM V$SQL
WHERE FORCE_MATCHING_SIGNATURE > 0
AND FORCE_MATCHING_SIGNATURE !=EXACT_MATCHING_SIGNATURE
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING(COUNT(1) > P_MAX_SQL_COUNT) --FORCE_MATCHING_SIGNATURE的计数,20
ORDER BY 2 desc) loop
log_debug('FORCE_MATCHING_SIGNATURE=' || SQL_INFO.signature ||
',COUNT=' || SQL_INFO.SQL_COUNT);
V_CLEAN_COUNT := 0;
--查询每个超过1小时没有使用的SQL的ADDRESS_HASH_VALUE
for sqlarea_info in (select address || ',' || hash_value as ADDRESS_HASH_VALUE
from v$sqlarea
where FORCE_MATCHING_SIGNATURE =
SQL_INFO.SIGNATURE
and last_load_time
--根据ADDRESS_HASH_VALUE从缓存中清除
log_debug(sqlarea_info.ADDRESS_HASH_VALUE);
SYS.DBMS_SHARED_POOL.purge(sqlarea_info.ADDRESS_HASH_VALUE, 'c');
sleep(1);
V_CLEAN_COUNT := V_CLEAN_COUNT + 1;
if is_timeout(V_START_TIME, P_MAX_EXECUTE_TIME) then
EXIT;
end if;
end loop;
log_debug('clean count = ' || V_CLEAN_COUNT);
V_CLEAN_COUNT_SUM := V_CLEAN_COUNT_SUM + V_CLEAN_COUNT;
sleep(1);
if is_timeout(V_START_TIME, P_MAX_EXECUTE_TIME) then
EXIT;
end if;
end loop;
log('total clean count = ' || V_CLEAN_COUNT_SUM);
log('clean sql pool end.');
end;
procedure clean_sql_pool(P_MAX_SQL_COUNT integer,
P_MAX_EXECUTE_TIME integer,
P_IS_DEBUG BOOLEAN,
P_IS_DEBUG_SLEEP BOOLEAN) is
BEGIN
IS_DEBUG := P_IS_DEBUG;
IS_DEBUG_SLEEP := P_IS_DEBUG_SLEEP;
clean_sql_pool(P_MAX_SQL_COUNT, P_MAX_EXECUTE_TIME);
end;
--等待一定时间，调试用
procedure sleep(P_SECOND INTEGER) is
BEGIN
if IS_DEBUG AND IS_DEBUG_SLEEP then
DBMS_LOCK.Sleep(P_SECOND);
END IF;
end;
--输出日志
procedure log(P_MESSAGE VARCHAR2) is
BEGIN
dbms_output.put_line(TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') ||
' - ' || P_MESSAGE);
end;
--输出调试日志
procedure log_debug(P_MESSAGE VARCHAR2) is
BEGIN
if IS_DEBUG then
log(P_MESSAGE);
end if;
end;
--判断是否到达最大执行时间
function is_timeout(p_start_time date, p_max_execute_time integer)
return boolean is
V_CURRENT_TIME DATE;
begin
select sysdate into V_CURRENT_TIME from dual;
IF ((V_CURRENT_TIME - p_start_time) * 24 * 3600 >= p_max_execute_time) then
return true;
else
return false;
end if;
end;
--begin
-- Initialization
--NULL;
end SQL_MANAGEMENT_PKG;
/
本文来自投稿，不代表本人立场，如若转载，请注明出处：http://www.sosokankan.com/article/2230827.html