达梦数据库学习笔记 – DM支持多块读吗
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 达梦数据库学习笔记 – DM支持多块读吗
在之前的研究测试中发现达梦仅支持单page写入，无法进行合并。这是针对写操作而言，那么对于读呢？ 是否支持类似Oracle一样的多块读呢？这里我们仍然通过一些简单实验来进行观察分析;默认情况下MULTI_PAGE_GET_NUM参数为1，这里我将其修改为16.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
----创建测试表 SQL> select owner,segment_name,TABLESPACE_NAME,HEADER_FILE,BLOCKS,extents from dba_segments where segment_name like '%TEST%'; OWNER SEGMENT_NAME TABLESPACE_NAME HEADER_FILE BLOCKS EXTENTS ------ ------------ --------------- ----------- -------------------- -------------------- SYSDBA TEST0307 HTS_TEST -1 0 0 SYSDBA TEST0320 MAIN 0 20880 1305 SYSDBA TEST0329 MAIN 0 320 20 SYSDBA TEST1107 MAIN 0 64 4 used time: 00:00:01.052. Execute id is 3528. SQL> SQL> DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TEST0329',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO'); DMSQL executed successfully used time: 393.742(ms). Execute id is 3522. SQL> select owner,table_name,num_rows,BLOCKS,EMPTY_BLOCKS from dba_tables where table_name like '%TEST%'; LINEID OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS ---------- ------ ---------- -------- ------ ------------ 1 SYSDBA TEST1107 NULL NULL NULL 2 SYSDBA TEST0307 NULL NULL NULL 3 SYSDBA TEST0320 NULL NULL NULL 4 SYSDBA TEST0329 20000 NULL NULL used time: 55.916(ms). Execute id is 3523. SQL> select /*+full */ count(1) from test0329 where owner='A'; LINEID COUNT(1) ---------- -------------------- 1 0 used time: 62.841(ms). Execute id is 3505. SQL> select 131072/8192 from dual; |
与此同时，我们在进行查询时，对dm主进程进行strace跟踪，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
[root@kylin-dm1 ~]# strace -T -tt -f -e trace=read,open,write,pwrite64,pread64 -o /tmp/381088.log -p 381088 strace: Process 381088 attached with 62 threads strace: Process 382072 attached strace: Process 382079 attached strace: Process 382080 attached strace: Process 382081 attached strace: Process 382082 attached strace: Process 382083 attached ...... strace: Process 381154 detached strace: Process 381640 detached strace: Process 382244 detached strace: Process 382245 detached [root@kylin-dm1 ~]# [root@kylin-dm1 ~]# [root@kylin-dm1 ~]# cat /tmp/381088.log |grep "pread64(" 381098 11:18:58.347951 pread64(6, "\0\0\0\0\367\1\0\0\0\0\365\1\0\0\0\0\370\1\0\0\27\0\0\0\0\0\0\0\177\331\217\t"..., 8192, 4120576) = 8192 <0.004130> 381105 11:18:58.352518 pread64(6, "\0\0\0\0\301\2\0\0\0\0\310\2\0\0\0\0\302\2\0\0\26\0\0\0\0\0\0\0\225\212\0\0"..., 8192, 5775360) = 8192 <0.005363> 381100 11:19:06.390659 pread64(29, "\4\0\0\0`R\0\0\377\377\377\377\377\377\377\377\377\377\377\377\25\0\0\0\0\0\0\0\323\321\26\n"..., 8192, 172752896) = 8192 <0.014420> 381095 11:19:06.405478 pread64(29, "\4\0\0\0pR\0\0\377\377\377\377\377\377\0\0qR\0\0\24\0\0\0\0\0\0\0\373\256\5\n"..., 8192, 172883968) = 8192 <0.000367> 381096 11:19:06.406278 pread64(29, "\4\0\0\0qR\0\0\0\0pR\0\0\0\0rR\0\0\24\0\0\0\0\0\0\0\375\256\5\n"..., 122880, 172892160) = 122880 <0.013349> 381097 11:19:06.420553 pread64(29, "\4\0\0\0\200R\0\0\0\0\177R\0\0\0\0\201R\0\0\24\0\0\0\0\0\0\0 \257\5\n"..., 131072, 173015040) = 131072 <0.000249> 381108 11:19:06.421847 pread64(29, "\4\0\0\0\220R\0\0\0\0\217R\0\0\0\0\221R\0\0\24\0\0\0\0\0\0\0E\257\5\n"..., 131072, 173146112) = 131072 <0.000106> 381103 11:19:06.422783 pread64(29, "\4\0\0\0\240R\0\0\0\0\237R\0\0\0\0\241R\0\0\24\0\0\0\0\0\0\0j\257\5\n"..., 131072, 173277184) = 131072 <0.000107> 381102 11:19:06.426136 pread64(29, "\4\0\0\0\260R\0\0\0\0\257R\0\0\0\0\261R\0\0\24\0\0\0\0\0\0\0\217\257\5\n"..., 131072, 173408256) = 131072 <0.011054> 381093 11:19:06.437787 pread64(29, "\4\0\0\0\300R\0\0\0\0\277R\0\0\0\0\301R\0\0\24\0\0\0\0\0\0\0\264\257\5\n"..., 131072, 173539328) = 131072 <0.000131> 381106 11:19:06.438597 pread64(29, "\4\0\0\0\320R\0\0\0\0\317R\0\0\0\0\321R\0\0\24\0\0\0\0\0\0\0\331\257\5\n"..., 131072, 173670400) = 131072 <0.000059> 381101 11:19:06.439253 pread64(29, "\4\0\0\0\340R\0\0\0\0\337R\0\0\0\0\341R\0\0\24\0\0\0\0\0\0\0\376\257\5\n"..., 131072, 173801472) = 131072 <0.000061> 381094 11:19:06.444386 pread64(29, "\4\0\0\0\360R\0\0\0\0\357R\0\0\0\0\361R\0\0\24\0\0\0\0\0\0\0#\260\5\n"..., 131072, 173932544) = 131072 <0.000062> 381099 11:19:06.445049 pread64(29, "\4\0\0\0\0S\0\0\0\0\377R\0\0\0\0\1S\0\0\24\0\0\0\0\0\0\0,\320\26\n"..., 131072, 174063616) = 131072 <0.000062> 381104 11:19:06.445588 pread64(29, "\4\0\0\0\20S\0\0\0\0\17S\0\0\0\0\21S\0\0\24\0\0\0\0\0\0\0a\320\26\n"..., 131072, 174194688) = 131072 <0.000117> 381107 11:19:06.446313 pread64(29, "\4\0\0\0 S\0\0\0\0\37S\0\0\0\0!S\0\0\24\0\0\0\0\0\0\0\226\320\26\n"..., 131072, 174325760) = 131072 <0.000075> 381098 11:19:06.446869 pread64(29, "\4\0\0\0000S\0\0\0\0/S\0\0\0\0001S\0\0\24\0\0\0\0\0\0\0\313\320\26\n"..., 131072, 174456832) = 131072 <0.001883> 381105 11:19:06.449174 pread64(29, "\4\0\0\0@S\0\0\0\0?S\0\0\0\0AS\0\0\24\0\0\0\0\0\0\0\377\320\26\n"..., 131072, 174587904) = 131072 <0.000057> 381100 11:19:06.449686 pread64(29, "\4\0\0\0PS\0\0\0\0OS\0\0\0\0QS\0\0\24\0\0\0\0\0\0\0004\321\26\n"..., 131072, 174718976) = 131072 <0.000152> 381095 11:19:06.450274 pread64(29, "\4\0\0\0`S\0\0\0\0_S\0\0\0\0aS\0\0\24\0\0\0\0\0\0\0i\321\26\n"..., 131072, 174850048) = 131072 <0.000086> 381096 11:19:06.450796 pread64(29, "\4\0\0\0pS\0\0\0\0oS\0\0\0\0qS\0\0\24\0\0\0\0\0\0\0\236\321\26\n"..., 131072, 174981120) = 131072 <0.000059> 381097 11:19:06.451537 pread64(29, "\4\0\0\0\200S\0\0\0\0\177S\0\0\0\0\201S\0\0\24\0\0\0\0\0\0\0\323\321\26\n"..., 131072, 175112192) = 131072 <0.000066> [root@kylin-dm1 ~]# [root@kylin-dm1 ~]# ls -ltr /proc/381088/fd 总用量 0 lrwx------ 1 dmdba dinstall 64 3月 30 11:14 9 -> 'socket:[413764859]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 8 -> 'socket:[413764858]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 7 -> /opt/dm/data/enmotech/TEMP.DBF lrwx------ 1 dmdba dinstall 64 3月 30 11:14 6 -> /opt/dm/data/enmotech/SYSTEM.DBF lrwx------ 1 dmdba dinstall 64 3月 30 11:14 5 -> 'socket:[413761196]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 4 -> 'socket:[413761195]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 34 -> 'socket:[413782168]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 33 -> 'socket:[413772936]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 32 -> /opt/dm/dmarch/STANDBY_ARCHIVE_0x172C4D3E_EP0_2023-03-30_11-07-28.log lrwx------ 1 dmdba dinstall 64 3月 30 11:14 31 -> /opt/dm/data/enmotech/HTS_TEST.dbf lrwx------ 1 dmdba dinstall 64 3月 30 11:14 30 -> /opt/dm/data/enmotech/benchmarksql.dbf lr-x------ 1 dmdba dinstall 64 3月 30 11:14 3 -> /var/lib/sss/mc/passwd lrwx------ 1 dmdba dinstall 64 3月 30 11:14 29 -> /opt/dm/data/enmotech/MAIN.DBF lrwx------ 1 dmdba dinstall 64 3月 30 11:14 28 -> /opt/dm/data/enmotech/ROLL.DBF l-wx------ 1 dmdba dinstall 64 3月 30 11:14 27 -> 'pipe:[413764867]' lr-x------ 1 dmdba dinstall 64 3月 30 11:14 26 -> 'pipe:[413764867]' l-wx------ 1 dmdba dinstall 64 3月 30 11:14 25 -> 'pipe:[413764866]' lr-x------ 1 dmdba dinstall 64 3月 30 11:14 24 -> 'pipe:[413764866]' l-wx------ 1 dmdba dinstall 64 3月 30 11:14 23 -> 'pipe:[413764865]' lr-x------ 1 dmdba dinstall 64 3月 30 11:14 22 -> 'pipe:[413764865]' l-wx------ 1 dmdba dinstall 64 3月 30 11:14 21 -> 'pipe:[413764864]' lr-x------ 1 dmdba dinstall 64 3月 30 11:14 20 -> 'pipe:[413764864]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 2 -> '/dev/pts/0 (deleted)' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 19 -> 'socket:[413763796]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 18 -> 'socket:[413763795]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 17 -> 'socket:[413763794]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 16 -> /opt/dm/data/enmotech/SYSAWR.DBF lrwx------ 1 dmdba dinstall 64 3月 30 11:14 15 -> /opt/dm/data/enmotech/enmotech02.log lrwx------ 1 dmdba dinstall 64 3月 30 11:14 14 -> /opt/dm/data/enmotech/enmotech01.log lrwx------ 1 dmdba dinstall 64 3月 30 11:14 13 -> 'socket:[413764861]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 12 -> 'socket:[413763791]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 11 -> 'socket:[413764860]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 10 -> 'socket:[413763790]' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 1 -> '/dev/pts/0 (deleted)' lrwx------ 1 dmdba dinstall 64 3月 30 11:14 0 -> '/dev/pts/0 (deleted)' lrwx------ 1 dmdba dinstall 64 3月 30 11:19 36 -> 'socket:[413782166]' |
从跟踪的结果来看，IO线程最多读取了15个Block，尽管我的参数设置为了16. 初步猜想可能跟extent有关。于是进一步将参数调到到64（最大值）。如下是重建了测试表进行的读取测试和跟踪：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
SQL> CREATE TABLE "SYSDBA"."TEST0330" 2 ( "OWNER" VARCHAR(128), "OBJECT_NAME" VARCHAR(128), "SUBOBJECT_NAME" VARCHAR(128), "OBJECT_ID" DEC, "DATA_OBJECT_ID" VARCHAR(1), "OBJECT_TYPE" VARCHAR(18), 3 4 5 6 7 8 9 "CREATED" TIMESTAMP(6), "LAST_DDL_TIME" TIMESTAMP(6), "TIMESTAMP" TIMESTAMP(6), 10 11 12 "STATUS" VARCHAR(7), "TEMPORARY" VARCHAR(1), "GENERATED" VARCHAR(1), "SECONDARY" VARCHAR(1), "NAMESPACE" VARCHAR(1), "EDITION_NAME" VARCHAR(1)) STORAGE( INITIAL 1, MINEXTENTS 16, NEXT 16,ON "MAIN", CLUSTERBTR) ;13 14 15 16 17 18 executed successfully used time: 19.687(ms). Execute id is 3534. SQL> insert into test0330 select * from test0329; affect rows 20000 used time: 28.089(ms). Execute id is 3535. SQL> insert into test0330 select * from test0329; affect rows 20000 used time: 31.491(ms). Execute id is 3536. SQL> commit; executed successfully used time: 2.179(ms). Execute id is 3537. SQL> select /*+full */ count(1) from test0330 where owner='SYS'; COUNT(1) -------------------- 39764 used time: 22.277(ms). Execute id is 801. SQL> SQL> select owner,segment_name,TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK,BLOCKS,extents,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_segments where segment_name like '%TEST%'; OWNER SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS ------ ------------ --------------- ----------- ------------ -------------------- -------------------- -------------------- -------------------- ----------- -------------------- SYSDBA TEST0307 HTS_TEST -1 -1 0 0 131072 131072 1 2147483645 SYSDBA TEST0320 MAIN 0 64 20880 1305 131072 131072 1 2147483645 SYSDBA TEST0329 MAIN 0 21088 320 20 131072 131072 1 2147483645 SYSDBA TEST0330 MAIN 0 21392 592 37 131072 2097152 16 2147483645 SYSDBA TEST1107 MAIN 0 16 64 4 131072 131072 1 2147483645 used time: 00:00:01.103. Execute id is 808. SQL> select 21392*8192 from dual; 21392*8192 ----------- <span style="color: #ff0000;">175243264</span> used time: 0.643(ms). Execute id is 809. SQL> |
跟踪方式仍然一样，这里省略strace过程，直接看日志：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
[root@kylin-dm1 ~]# cat /tmp/385453.log |grep "pread64(28" 385478 11:57:39.627148 pread64(28, "\4\0\0\0\221S\0\0\377\377\377\377\377\377\377\377\377\377\377\377\32\0\32\32\0\0\0\0\303\17\37\n"..., 8192, 175251456) = 8192 <0.000020> 385476 11:57:39.628058 pread64(28, "\4\0\0\0\220S\0\0\377\377\377\377\377\377\377\377\377\377\377\377\25\0\0\0\0\0\0\0\327\16\37\n"..., 8192, 175243264) = 8192 <0.000012> 385475 11:57:39.628200 pread64(28, "\4\0\0\0\222S\0\0\377\377\377\377\377\377\0\0\223S\0\0\25\0\0\0\0\0\0\0\327\16\37\n"..., 8192, 175259648) = 8192 <0.000009> 385474 11:57:39.628306 pread64(28, "\4\0\0\0\240S\0\0\377\377\377\377\377\377\0\0\241S\0\0\24\0\0\0\0\0\0\0\270\10\37\n"..., 8192, 175374336) = 8192 <0.000009> 385473 11:57:39.634651 pread64(28, "\4\0\0\0\241S\0\0\0\0\240S\0\0\0\0\242S\0\0\24\0\0\0\0\0\0\0\272\10\37\n"..., 122880, 175382528) = 122880 <0.000036> 385472 11:57:39.634986 pread64(28, "\4\0\0\0\260S\0\0\0\0\257S\0\0\0\0\261S\0\0\24\0\0\0\0\0\0\0\355\10\37\n"..., 131072, 175505408) = 131072 <0.000031> 385471 11:57:39.635261 pread64(28, "\4\0\0\0\300S\0\0\0\0\277S\0\0\0\0\301S\0\0\24\0\0\0\0\0\0\0#\t\37\n"..., 131072, 175636480) = 131072 <0.000031> 385470 11:57:39.635504 pread64(28, "\4\0\0\0\320S\0\0\0\0\317S\0\0\0\0\321S\0\0\24\0\0\0\0\0\0\0X\t\37\n"..., 131072, 175767552) = 131072 <0.000027> 385467 11:57:39.635728 pread64(28, "\4\0\0\0\340S\0\0\0\0\337S\0\0\0\0\341S\0\0\24\0\0\0\0\0\0\0\215\t\37\n"..., 131072, 175898624) = 131072 <0.000029> 385466 11:57:39.635964 pread64(28, "\4\0\0\0\360S\0\0\0\0\357S\0\0\0\0\361S\0\0\24\0\0\0\0\0\0\0\275\t\37\n"..., 131072, 176029696) = 131072 <0.000028> 385469 11:57:39.636223 pread64(28, "\4\0\0\0\0T\0\0\0\0\377S\0\0\0\0\1T\0\0\24\0\0\0\0\0\0\0\362\t\37\n"..., 131072, 176160768) = 131072 <0.000031> 385480 11:57:39.636476 pread64(28, "\4\0\0\0\20T\0\0\0\0\17T\0\0\0\0\21T\0\0\24\0\0\0\0\0\0\0'\n\37\n"..., 131072, 176291840) = 131072 <0.000029> 385477 11:57:39.636886 pread64(28, "\4\0\0\0 T\0\0\0\0\37T\0\0\0\0!T\0\0\24\0\0\0\0\0\0\0\\\n\37\n"..., 131072, 176422912) = 131072 <0.000028> 385468 11:57:39.637442 pread64(28, "\4\0\0\0000T\0\0\0\0/T\0\0\0\0001T\0\0\24\0\0\0\0\0\0\0\221\n\37\n"..., 131072, 176553984) = 131072 <0.000060> 385479 11:57:39.637820 pread64(28, "\4\0\0\0@T\0\0\0\0?T\0\0\0\0AT\0\0\24\0\0\0\0\0\0\0\307\n\37\n"..., 131072, 176685056) = 131072 <0.000032> 385478 11:57:39.638321 pread64(28, "\4\0\0\0PT\0\0\0\0OT\0\0\0\0QT\0\0\24\0\0\0\0\0\0\0\375\n\37\n"..., 131072, 176816128) = 131072 <0.000029> 385481 11:57:39.638704 pread64(28, "\4\0\0\0`T\0\0\0\0_T\0\0\0\0aT\0\0\24\0\0\0\0\0\0\0002\v\37\n"..., 131072, 176947200) = 131072 <0.000058> 385476 11:57:39.640153 pread64(28, "\4\0\0\0pT\0\0\0\0oT\0\0\0\0qT\0\0\24\0\0\0\0\0\0\0g\v\37\n"..., 131072, 177078272) = 131072 <0.000041> 385475 11:57:39.640404 pread64(28, "\4\0\0\0\200T\0\0\0\0\177T\0\0\0\0\201T\0\0\24\0\0\0\0\0\0\0\234\v\37\n"..., 131072, 177209344) = 131072 <0.000027> 385474 11:57:39.640605 pread64(28, "\4\0\0\0\220T\0\0\0\0\217T\0\0\0\0\221T\0\0\24\0\0\0\0\0\0\0\314\v\37\n"..., 131072, 177340416) = 131072 <0.000031> 385473 11:57:39.640809 pread64(28, "\4\0\0\0\240T\0\0\0\0\237T\0\0\0\0\241T\0\0\24\0\0\0\0\0\0\0\1\f\37\n"..., 131072, 177471488) = 131072 <0.000027> 385472 11:57:39.640998 pread64(28, "\4\0\0\0\260T\0\0\0\0\257T\0\0\0\0\261T\0\0\24\0\0\0\0\0\0\0006\f\37\n"..., 131072, 177602560) = 131072 <0.000027> 385471 11:57:39.641185 pread64(28, "\4\0\0\0\300T\0\0\0\0\277T\0\0\0\0\301T\0\0\24\0\0\0\0\0\0\0r\f\37\n"..., 131072, 177733632) = 131072 <0.000028> 385470 11:57:39.641400 pread64(28, "\4\0\0\0\320T\0\0\0\0\317T\0\0\0\0\321T\0\0\24\0\0\0\0\0\0\0\247\f\37\n"..., 131072, 177864704) = 131072 <0.000028> 385467 11:57:39.641588 pread64(28, "\4\0\0\0\340T\0\0\0\0\337T\0\0\0\0\341T\0\0\24\0\0\0\0\0\0\0\327\f\37\n"..., 131072, 177995776) = 131072 <0.000027> 385466 11:57:39.641805 pread64(28, "\4\0\0\0\360T\0\0\0\0\357T\0\0\0\0\361T\0\0\24\0\0\0\0\0\0\0\f\r\37\n"..., 131072, 178126848) = 131072 <0.000029> 385469 11:57:39.641994 pread64(28, "\4\0\0\0\0U\0\0\0\0\377T\0\0\0\0\1U\0\0\24\0\0\0\0\0\0\0A\r\37\n"..., 131072, 178257920) = 131072 <0.000091> 385480 11:57:39.642254 pread64(28, "\4\0\0\0\20U\0\0\0\0\17U\0\0\0\0\21U\0\0\24\0\0\0\0\0\0\0v\r\37\n"..., 131072, 178388992) = 131072 <0.000026> 385477 11:57:39.642438 pread64(28, "\4\0\0\0 U\0\0\0\0\37U\0\0\0\0!U\0\0\24\0\0\0\0\0\0\0\253\r\37\n"..., 131072, 178520064) = 131072 <0.000026> 385468 11:57:39.642741 pread64(28, "\4\0\0\0000U\0\0\0\0/U\0\0\0\0001U\0\0\24\0\0\0\0\0\0\0\340\r\37\n"..., 131072, 178651136) = 131072 <0.000053> 385479 11:57:39.643042 pread64(28, "\4\0\0\0@U\0\0\0\0?U\0\0\0\0AU\0\0\24\0\0\0\0\0\0\0\26\16\37\n"..., 131072, 178782208) = 131072 <0.000032> 385478 11:57:39.643408 pread64(28, "\4\0\0\0PU\0\0\0\0OU\0\0\0\0QU\0\0\24\0\0\0\0\0\0\0K\16\37\n"..., 131072, 178913280) = 131072 <0.000027> 385481 11:57:39.643847 pread64(28, "\4\0\0\0`U\0\0\0\0_U\0\0\0\0aU\0\0\24\0\0\0\0\0\0\0\200\16\37\n"..., 131072, 179044352) = 131072 <0.000051> 385476 11:57:39.644188 pread64(28, "\4\0\0\0pU\0\0\0\0oU\0\0\0\0qU\0\0\24\0\0\0\0\0\0\0\265\16\37\n"..., 131072, 179175424) = 131072 <0.000032> 385475 11:57:39.644595 pread64(28, "\4\0\0\0\200U\0\0\0\0\177U\0\0\0\0\201U\0\0\24\0\0\0\0\0\0\0\347\16\37\n"..., 131072, 179306496) = 131072 <0.000026> 385474 11:57:39.644801 pread64(28, "\4\0\0\0\220U\0\0\0\0\217U\0\0\0\0\221U\0\0\24\0\0\0\0\0\0\0\34\17\37\n"..., 131072, 179437568) = 131072 <0.000029> 385473 11:57:39.644986 pread64(28, "\4\0\0\0\240U\0\0\0\0\237U\0\0\0\0\241U\0\0\24\0\0\0\0\0\0\0Q\17\37\n"..., 131072, 179568640) = 131072 <0.000033> 385472 11:57:39.645193 pread64(28, "\4\0\0\0\260U\0\0\0\0\257U\0\0\0\0\261U\0\0\24\0\0\0\0\0\0\0\206\17\37\n"..., 131072, 179699712) = 131072 <0.000066> 385471 11:57:39.645425 pread64(28, "\4\0\0\0\300U\0\0\0\0\277U\0\0\0\0\301U\0\0\24\0\0\0\0\0\0\0\274\17\37\n"..., 131072, 179830784) = 131072 <0.000117> |
对于我们表所在数据文件，共计读取了38次，其中有test0330表的段头地址是175243264。也就是说针对test0330表共计读取了37次，其中1次段头，2次单块读，35次多块读（1次读了15个block，34次每次16个block).
此时我检查达梦数据库环境的extent设置发现默认值为16.
|
1 2 3 4 5 6 7 8 9 |
SQL> select name,value from v$parameter where name like '%EXTENT_SIZE%'; NAME VALUE ------------------ ----- MEMORY_EXTENT_SIZE 32 GLOBAL_EXTENT_SIZE 16 used time: 4.669(ms). Execute id is 812. SQL> |
实际上对于extent_size，是在数据库初始化的时候就需要进行指定的(取值范围为16，32，64). 每次多块读的情况下，也不过跨extent大小，这可能也就是为什么dm多块读参数最大值是64了。
从前面的测试还是发现了几个小问题，其中对于extent 问题，我认为是个重大缺陷。
1、 数据库一旦初始化完成后，无法对extent_size 进行更改（巨大缺陷）
2、统计信息收集后，dba_segments等视图相关信息缺失，比如blocks，empty_blocks等。
至于说为什么无法进行extent 修改,下篇文章再谈。
Leave a Reply
You must be logged in to post a comment.