SQL缓冲区提供在执行SQL语句过程中所需的内存,包括计划、SQL语句和结果集缓存。很多应用都存在反复执行相同SQL语句的情况,此时可以使用缓冲区保存这些语句和它的执行计划,这就是计划重用。这样带来的好处是提高了SQL语句的执行效率。
通过CACHE_POOL_SIZE参数来改变SQL缓冲区的大小。设置太小会导致命中率太低影响SQL执行效率,设置太大对主机内存增加压力。
测试如下:
select round(data_size/1024/1024.0,2) "SQL CACHE USED(M)",
total_size/1024/1024.0 "SQL CACHE SIZE(M)",
round(data_size*100.0/total_size,2) "SQL CACHE USE RATIO(%)",n_free
from v$mem_pool
where name='SQL CACHE MANAGERMENT'
行号 SQL CACHE USED(M) SQL CACHE SIZE(M) SQL CACHE USE RATIO(%) N_FREE
---------- ----------------- ----------------- ---------------------- -----------------------
1 1.93 200 0.96 0
由上可见,SQL缓冲区共200M,目前使用了1.93M,使用率0.96%。没有页面(n_free)换出。
当然这是测试机刚启动的样子。如果生产机这样的情况就说明缓冲区分配的太大,内存严重浪费。
执行如下测试语句,循环执行5000次查询:
begin
for x in 1..5000 loop
exec immediate 'select id from t1 where id='||x;
end loop;
end;
/
行号 ID
---------- -----------
1 1
已用时间: 00:00:01.507. 执行号:1201.
连续执行0次
再查询SQL缓冲区使用情况:
行号 SQL CACHE USED(M) SQL CACHE SIZE(M) SQL CACHE USE RATIO(%) N_FREE
---------- ----------------- ----------------- ---------------------- -----------------------
1 274.26 278 98.65 0
由上可见,SQL缓冲区增长到了278M,目前使用了274.26M,使用率98.65%。没有页面换出。
检查缓存详细情况:
select PLAN_MEM_SIZE+SQL_MEM_SIZE ,SQL_CNT,PLAN_CNT,HIT_CNT
from
( select sum(item_size)/1024/1024.0 SQL_MEM_SIZE,COUNT(*) SQL_CNT from v$cachesql,v$cacheitem where sql like 'select id from t1 where id=%' and cache_item=address ) ,
( select sum(a.mem_size)/1024/1024.0 PLAN_MEM_SIZE,sum(n_hit) HIT_CNT,count(*) PLAN_CNT from v$cachepln a,v$cacheitem where sqlstr like 'select id from t1 where id=%' and cache_item=address)
行号 PLAN_MEM_SIZE+SQL_MEM_SIZE SQL_CNT PLAN_CNT HIT_CNT
---------- -------------------------- -------------------- -------------------- --------------------
1 270.72265625 5000 5000 45000
由上可见,循环执行的5000条语句共计占内存270.72M(缓冲区当前占用274.26M,还有几兆是洗系统内部语句消耗的),SQL和执行计划各自5000条。命中了45000次,平均每条语句命中了9次。
通过v$sysstat视图查看数据库SQL命中率如下:
SQL> SELECT
2 PARSE_CNT ,
3 HARD_PARSE_CNT,
4 (PARSE_CNT - HARD_PARSE_CNT ) HIT_CNT,
5 round((PARSE_CNT - HARD_PARSE_CNT ) * 100.0/PARSE_CNT,2) "HIT RATIO %"
6 FROM
7 (SELECT STAT_VAL PARSE_CNT FROM v$sysstat WHERE NAME='parse count') a,
8 (SELECT STAT_VAL HARD_PARSE_CNT FROM v$sysstat WHERE NAME='hard parse count') b
9 /
行号 PARSE_CNT HARD_PARSE_CNT HIT_CNT HIT RATIO %
---------- -------------------- -------------------- -------------------- -----------
1 50533 5147 45386 89.81
与上面吻合。软解析50533次,硬解析5147次,命中45386次。此处还包括了系统内部任务的sql语句,所以较上面统计数字大一点。
现在把测试语句修改一下,循环执行20000次查询:
SQL> begin
2 for x in 1..20000 loop
3 exec immediate 'select id from t1 where id='||x;
4 end loop;
5 end;
6 /
行号 ID
---------- -----------
1 1
已用时间: 00:00:04.703. 执行号:1213.
重复执行10次
通过v$sysstat视图查看数据库SQL命中率:
SQL> SELECT
2 PARSE_CNT ,
3 HARD_PARSE_CNT,
4 (PARSE_CNT - HARD_PARSE_CNT ) HIT_CNT,
5 round((PARSE_CNT - HARD_PARSE_CNT ) * 100.0/PARSE_CNT,2) "HIT RATIO %"
6 FROM
7 (SELECT STAT_VAL PARSE_CNT FROM v$sysstat WHERE NAME='parse count') a,
8 (SELECT STAT_VAL HARD_PARSE_CNT FROM v$sysstat WHERE NAME='hard parse count') b
9 /
行号 PARSE_CNT HARD_PARSE_CNT HIT_CNT HIT RATIO %
---------- -------------------- -------------------- -------------------- -----------
1 250693 143457 107236 42.78
有14万硬解析,命中率才42.78%。按说上面循环语句反复执行了10次,应该只有2万次硬解析,后续的执行都应该命中。查询V$MEM_POOL检查缓冲池分配情况:
SQL> select round(data_size/1024/1024.0,2) "SQL CACHE USED(M)",
2 total_size/1024/1024.0 "SQL CACHE SIZE(M)",
3 round(data_size*100.0/total_size,2) "SQL CACHE USE RATIO(%)",
4 n_free
5 from v$mem_pool
6 where name='SQL CACHE MANAGERMENT'
7 /
行号 SQL CACHE USED(M) SQL CACHE SIZE(M) SQL CACHE USE RATIO(%) N_FREE
---------- ----------------- ----------------- ---------------------- -----------
1 552.62 599.013671875 92.26 459260
因为缓存不够,无法容纳新生成的执行计划,累计换出过45260次页面。因此很多执行计划没有命中。
解决方法有两个,一个是扩大SQL缓冲区尺寸,使之不产生N_FREE,从而执行计划可以重用。另一个方法是改写测试语句。修改为使用绑定变量方式。
重启数据库后执行如下语句:
begin
for x in 1..20000 loop
exec immediate 'select id from t1 where id=:x' using x;
end loop;
end;
反复执行10次
查看系统命中率:
SQL> SELECT
2 PARSE_CNT ,
3 HARD_PARSE_CNT,
4 (PARSE_CNT - HARD_PARSE_CNT ) HIT_CNT,
5 round((PARSE_CNT - HARD_PARSE_CNT ) * 100.0/PARSE_CNT,2) "HIT RATIO %"
6 FROM
7 (SELECT STAT_VAL PARSE_CNT FROM v$sysstat WHERE NAME='parse count') a,
8 (SELECT STAT_VAL HARD_PARSE_CNT FROM v$sysstat WHERE NAME='hard parse count') b
9 /
行号 PARSE_CNT HARD_PARSE_CNT HIT_CNT HIT RATIO %
---------- -------------------- -------------------- -------------------- -----------
1 200031 12 200019 99.99
如上可见,循环20000次查询的代码块被重复执行了10次。整个系统的硬解析次数只有12次,命中率99.99%。
查看SQL缓冲区详细情况:
SQL> select PLAN_MEM_SIZE+SQL_MEM_SIZE ,SQL_CNT,PLAN_CNT,HIT_CNT
2 from
3 ( select sum(item_size)/1024/1024.0 SQL_MEM_SIZE,COUNT(*) SQL_CNT from v$cachesql,v$cacheitem where sql like 'select id from t1 where id=%' and cache_item=address ) ,
4 ( select sum(a.mem_size)/1024/1024.0 PLAN_MEM_SIZE,sum(n_hit) HIT_CNT,count(*) PLAN_CNT from v$cachepln a,v$cacheitem where sqlstr like 'select id from t1 where id=%' and cache_item=address)
5 /
行号 PLAN_MEM_SIZE+SQL_MEM_SIZE SQL_CNT PLAN_CNT HIT_CNT
---------- -------------------------- -------------------- -------------------- --------------------
1 0.0537109375 1 1 199999
循环20000次查询重用同一条SQL语句和执行计划。重复调用代码块10次。累计命中199999次,除第一次硬解析以外全部命中。
以上通过绑定变量方式完美的解决了问题,大幅提高了速度。
如果遇到实在不能改写绑定变量的时候只能通过sp_set_para_value命令修改CACHE_POOL_SIZE参数扩大缓冲区尺寸了。