ORACLE数据库SQL缓存自动智能清理

直接拼接变量到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 < sysdate - 1 / 24 ;

--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 < sysdate - 1 / 24) loop

--根据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;

/