达梦8 BUFFER缓冲区对查询语句性能的影响

老刘学达梦

已于 2022-12-23 08:24:09 修改

阅读量734

点赞数 1

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

于 2022-12-22 19:50:10 首次发布

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

版权

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

28 篇文章 1 订阅

订阅专栏

达梦

28 篇文章 1 订阅

订阅专栏

数据缓冲区是 DM Server 在将数据页写入磁盘之前以及从磁盘上读取数据页之后，数

据页所存储的地方。这是 DM Server 至关重要的内存区域之一，将其设定得太小，会导致

缓冲页命中率低，磁盘 IO 频繁，SQL执行性能变差；将其设定得太大，又会导致操作系统内存本身不够用。

创建测试表

create table T2
(   ID INT, 
    NAME VARCHAR(10) )；
insert into t2
select trunc(DBMS_RANDOM.value(1,10000000)),DBMS_RANDOM.string('A',10)
from dual connect by level <=10000000；
create table T3
(   ID INT, 
    NAME VARCHAR(10) )；
insert into t3 select * from t2 connect by level <=5;

显示表的大小

SQL> select segment_name,bytes/1024/1024 from dba_segments
2   where segment_name in ('T2','T3') and owner='SYSDBA';

行号       SEGMENT_NAME BYTES/1024/1024
---------- ------------ --------------------
1          T3           1891
2          T2           382

由上可见，t2表382兆字节，t3表1891兆字节。

查询BUFFER缓冲区统计信息

SQL> select 'BUFFER' || rownum POOL,N_PAGES * 8 / 1024 M_SIZE,N_LOGIC_READS,N_DISCARD,N_PHY_READS,trunc(RAT_HIT*100,4) HIT_RATIO from v$bufferpool where NAME='NORMAL'
2   union all
3   select 'SUM' ,sum(N_PAGES) * 8 / 1024,sum(N_LOGIC_READS),sum(N_DISCARD),sum(N_PHY_READS),trunc(sum(N_LOGIC_READS) * 100.0/(sum(N_LOGIC_READS)+sum(N_PHY_READS)),4)
4   from v$bufferpool where NAME='NORMAL'
5   /

行号       POOL    M_SIZE               N_LOGIC_READS        N_DISCARD            N_PHY_READS          HIT_RATIO
---------- ------- -------------------- -------------------- -------------------- -------------------- ---------
1          BUFFER1 235                  9                    0                    281                  3.1034
2          BUFFER2 235                  48                   0                    276                  14.8148
3          BUFFER3 235                  6                    0                    267                  2.1978
4          BUFFER4 235                  17                   0                    264                  6.0498
5          BUFFER5 235                  4                    0                    278                  1.4184
6          SUM     1176                 84                   0                    1366                 5.7931

由上可见，系统共有5个BUFFER缓冲池，共计1176兆字节

查询系统统计信息

SQL> select name,stat_val from v$sysstat where name in ('physical read count','logic read count')
2   /

行号       NAME                STAT_VAL
---------- ------------------- --------------------
1          logic read count    186
2          physical read count 14

由上可见，逻辑读累计186次，物理读累计14次。数据库服务器启动后会将一些系统对象加载到内存。

查询t2表

###############查询当前会话ID
SQL> select sessid ;

行号       SESSID
---------- --------------------
1          2130575672

已用时间: 2.937(毫秒). 执行号:501.

###############设置autotrace
SQL> set autotrace trace

###############执行查询
SQL> select name from t2 where id=1
/

行号       NAME
---------- ----------
1          WJWyDebcjM

1   #NSET2: [1275, 250000->1, 64]
2     #PRJT2: [1275, 250000->1, 64]; exp_num(2), is_atom(FALSE)
3       #SLCT2: [1275, 250000->1, 64];
4         #CSCN2: [1275, 10000000->10000000, 64]; INDEX33562607(T2)

已用时间: 00:00:08.948. 执行号:502.

由上可见，该查询对t2表进行了全表扫描（CSCN2），用时8.848秒。

查询sql统计视图

将上面的执行号502代入如下语句中

SQL> select a.sql_id,sql_txt,logic_read_cnt,phy_read_cnt,io_wait_time
2   from v$sql_stat_history a,(select sql_id  from v$sql_history where  exec_id=502 ) b
3   where  a.sql_id=b.sql_id
4   /

行号       SQL_ID      SQL_TXT                        LOGIC_READ_CNT       PHY_READ_CNT         IO_WAIT_TIME
---------- ----------- ------------------------------ -------------------- -------------------- --------------------
1          9           select name from t2 where id=1 63                   48317                6197

由上可见物理读48317个数据块，约337兆字节。逻辑读63个数据块。我们知道头一次访问表数据时一定是物理读。

查询会话统计视图

SQL> select parse_cnt,hard_parse_cnt,io_wait_time,logic_read_cnt,phy_read_cnt
2   from v$session_stat where sessid=2130575672
3   /

行号       PARSE_CNT            HARD_PARSE_CNT       IO_WAIT_TIME         LOGIC_READ_CNT       PHY_READ_CNT
---------- -------------------- -------------------- -------------------- -------------------- --------------------
1          3                    3                    6204                 249                  48330

由上可见与sql统计视图查询结果相差无几，因为该会话还有系统内部语句隐含执行，所以数据稍微大一点。

查询系统统计信息

SQL> select name,stat_val from v$sysstat where name in ('physical read count','logic read count')
2   /

行号       NAME                STAT_VAL
---------- ------------------- --------------------
1          logic read count    817
2          physical read count 48352

由上可见，查询结果跟会话统计差不多，因为还有系统任务在执行，所以稍微多一点。

二次查询t2表

###############执行查询
SQL> select name from t2 where id=1
/
行号       NAME
---------- ----------
1          WJWyDebcjM


1   #NSET2: [1275, 250000->1, 64]
2     #PRJT2: [1275, 250000->1, 64]; exp_num(2), is_atom(FALSE)
3       #SLCT2: [1275, 250000->1, 64];
4         #CSCN2: [1275, 10000000->10000000, 64]; INDEX33562607(T2)

已用时间: 984.149(毫秒). 执行号:503.

由上可见，相比第一次执行时间由8.948秒缩短到948毫秒。减少了90%。

查询sql统计视图

SQL> select sql_txt,logic_read_cnt,phy_read_cnt,io_wait_time
2   from v$sql_stat_history  where sql_id=9
/

行号       SQL_TXT                        LOGIC_READ_CNT       PHY_READ_CNT         IO_WAIT_TIME
---------- ------------------------------ -------------------- -------------------- --------------------
1          select name from t2 where id=1 48312                0                    0
2          select name from t2 where id=1 63                   48317                6197

由上可见，新增加了一行记录（行号1）。逻辑读由63变成了48312，物理读由48317降低为0。IO_WAIT_TIME由6197毫秒变成了0毫秒。因此执行速度大幅提升。

查询会话统计视图

SQL> select parse_cnt,hard_parse_cnt,io_wait_time,logic_read_cnt,phy_read_cnt
2   from v$session_stat where sessid=2130575672
3   /

行号       PARSE_CNT            HARD_PARSE_CNT       IO_WAIT_TIME         LOGIC_READ_CNT       PHY_READ_CNT
---------- -------------------- -------------------- -------------------- -------------------- --------------------
1          4                    3                    6204                 48561                48330

由上可见，相比第一次SQL查询，物理读没有增加。代表软解析的PARSE_CNT增加1。

查询系统统计信息

SQL> select name,stat_val from v$sysstat where name in ('physical read count','logic read count')
2   /

行号       NAME                STAT_VAL
---------- ------------------- --------------------
1          logic read count    296008
2          physical read count 51933

由上可见，比第一次SQL查询物理读没有明显增加（还有系统任务在执行）。

查询BUFFER缓冲池统计

累计物理读跟刚才查询v$sysstat差不多。

查询t3表（换一个窗口）

QL> select name from t3 where id=1
2   /

行号       NAME
---------- ----------
1          WJWyDebcjM
2          WJWyDebcjM
3          WJWyDebcjM
4          WJWyDebcjM
5          WJWyDebcjM

已用时间: 00:00:47.964. 执行号:604.

查询BUFFER缓冲池统计

内存池1176兆字节，t3表1891兆字节，因此内存将完全被t3表覆盖。由上可见，大量的物理读产生，大量的内存页被换出（N_DISCARD）

第三次查询t2表

###############执行查询
SQL> select name from t2 where id=1
/
行号       NAME
---------- ----------
1          WJWyDebcjM

1   #NSET2: [1275, 250000->1, 64]
2     #PRJT2: [1275, 250000->1, 64]; exp_num(2), is_atom(FALSE)
3       #SLCT2: [1275, 250000->1, 64];
4         #CSCN2: [1275, 10000000->10000000, 64]; INDEX33562607(T2)

已用时间: 00:00:09.600. 执行号:504.

由上可见，相比第二次执行时间由948毫秒增加到了9.609秒。比第一次查询时还略多（因为BUFFER池已经充满，每个数据块都要从硬盘读进内存，而且还要把部分t3表的数据淘汰出去）。

查询sql统计视图

select sql_txt,logic_read_cnt,phy_read_cnt,io_wait_time
from v$sql_stat_history  where sql_id=9
/

行号       SQL_TXT                        LOGIC_READ_CNT       PHY_READ_CNT         IO_WAIT_TIME
---------- ------------------------------ -------------------- -------------------- --------------------
1          select name from t2 where id=1 10                   48316                6773
2          select name from t2 where id=1 48312                0                    0
3          select name from t2 where id=1 63                   48317                6197

由上可见，新增加了一行记录（行号1）。逻辑读由48312降低为10，物理读由0变成了为4836。IO_WAIT_TIME比第一次执行时还多（6197-->6773）。因此执行速度大幅提升。

结论

BUFFER 缓冲池设置的大小对系统性能有非常大的影响。建议将物理内存的三分之二设置成BUFFER缓存。