达梦8 SQL缓冲区的优化

老刘学达梦

已于 2022-12-26 10:16:23 修改

阅读量1.1k

点赞数

分类专栏：国产化数据库达梦文章标签：数据库

于 2022-12-25 17:49:53 首次发布

本文链接：https://blog.csdn.net/weixin_56866387/article/details/128435933

版权

国产化数据库同时被 2 个专栏收录

27 篇文章 1 订阅

订阅专栏

达梦

27 篇文章 1 订阅

订阅专栏

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参数扩大缓冲区尺寸了。