说明：
本文只是记录近期学习达梦数据库的一些内容，并没有说达梦数据库有什么坑，希望有些所谓的"友商"不要恶意抹黑，实际上在我接触过的几家国产数据库中，个人认为达梦数据库无论是产品成熟度，还是技术社区、兼容性、运维习惯等都是名列前茅的，至少对曾经的Oracle DBA来讲，是比较友好的。
问题回顾：
工作群又在疯狂闪烁了，原来是某同事在抱怨SQL执行太慢了，慢的令人发指！
???这么严重吗，赶快要来慢SQL，让我看看怎么回事：
慢SQL如下，看见没，多么平平无奇的一条SQL，究竟会有多慢才让人这么的无法忍受？是几分钟还是几十分钟，或者几小时？
select a.xxxcjcxx_id , max( a.object_cccccccccc) as share_cccccccccc, max( a.xxxcjcxx_downlJCd) as share_downlJCd
from CJC_xxxcjcxx_cccccccccc a
where a.cccccccccc_type=0
and a.object_type =2
and a.del_flag=0
and a.xxxcjcxx_id in (
select id from CJC_xxxcjcxx where create_by in ( '10000000-1' ) and del_flag=0
)
group by a.xxxcjcxx_id ;
先看下数据量，并不大呀，能慢到哪去呢？
SELECT COUNT(*) from JC.CJC_xxxcjcxx_cccccccccc; ---753267
SELECT COUNT(*) from JC.CJC_xxxcjcxx; ---42508
生产库上执行，看下耗时，what？653毫秒，也就是0.65秒，不到1秒中，真的就这么没耐心吗？
已用时间: 653.949(毫秒). 执行号:80000000.
同事立马反驳，0.65秒还不慢吗，我觉得已经很慢了，非常慢，慢的不能再慢了，因为测试环境只需要11毫秒，也就是0.011秒！
测试库耗时：
18 rows got
已用时间: 11.393(毫秒). 执行号:100000.
难怪觉得慢了，没有对比就没有伤害，生产环境高配物理机，测试环境低配虚拟机，但测试环境更快一定说明生产库有问题吗，我想大概可能有几个原因导致测试更快：
1.数据量不同，测试环境数据更少；
2.数据量相同，但生产环境表碎片多，表段更大，导致扫描数据时需要更多的I/O；
3.索引不一致，或其他原因导致的执行计划不同；
4.数据库版本、补丁版本差异、bug等(版本相同8.1.3.62，补丁版本有差异22和23)。
问题分析：
顺着这个思路，开始排查：
1.对比两个环境表数据量，完全相同。
2.对比两个表段大小，O My God，居然被我猜对了，测试库CJC_xxxcjcxx表段一共80MB，生产库CJC_xxxcjcxx表多988MB，是测试库的12倍。
表段大小查询如下：
测试库：
select owner,segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name in ('CJC_xxxcjcxx','CJC_xxxcjcxx_cccccccccc');
行号 owner segment_name segment_type bytes/1024/1024
---------- ----- ----------------------- ------------ --------------------
1 XXX CJC_xxxcjcxx_cccccccccc TABLE 73
2 XXX CJC_xxxcjcxx TABLE 40
3 XXX CJC_xxxcjcxx TABLE 40
生产库：
SQL> select owner,segment_name,segment_type,bytes/1024/1024 from dba_segments where segment_name in ('CJC_xxxcjcxx','CJC_xxxcjcxx_cccccccccc');
行号 owner segment_name segment_type bytes/1024/1024
---------- ----- ----------------------- ------------ --------------------
1 XXX CJC_xxxcjcxx_cccccccccc TABLE 73
2 XXX CJC_xxxcjcxx TABLE 494
3 XXX CJC_xxxcjcxx TABLE 494
此时我犹如福尔摩斯附体，立刻通过过往的一些细节凭空勾勒出了完整的画面：
首先生产库是通过oracle迁移过来的，迁移过程中由于上线初期测试阶段，会反复迁移多次，猜测一定是执行过大批量的insert,delete操作，导致表高水位非常高。
之后这些表进行全表扫等操作时，虽然看上去数据量不多，但是根据Oracle运维经验来看，高水位问题会导致扫描更多的块，所以速度会慢。
而测试库一般都是通过生产库dexp/dimp逻辑导入进去的，并没有经历过大批量反复的insert,delete操作，所以没有高水位问题。
分析到这里，剩下的问题就很好解决了：
1.证明此表碎片化严重，有高水位问题；
2.想办法整理碎片，降低高水位。
如何查询达梦高水位？
根据Oracle运维经验来看，有多种查询方式，具体查询方式可以参考我的另一篇文章：
Oracle 高水位查询和处理
Oracle高水位查询和处理方法汇总
chenjuchao，公众号：IT小Chen
Oracle 高水位查询和处理方法汇总
那么这些查询方法适用达梦吗？先尝试通过查询user_tables表来查看高水位：
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'CJC_XXXCJCXX';
很明显此种查询方式数据来源于统计信息，为了数据的准确性，需要先收集下统计信息，先在测试环境试下：
DBMS_STATS.GATHER_TABLE_STATS('CJC','CJC_XXXCJCXX',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
再次查询，发现没有数据，仔细看下，发现虽然收集完统计信息，但是AVG_ROW_LEN列仍然没有数据，所以不能使用此方法查询高水位。
再换一种方法，把表逻辑dexp/dimp导出到测试库，或者用CTAS方式创建一张临时表，如果表段很小，就说明表有碎片，存在高水位的问题，测试如下：
创建中间表
create table t200 as select * from CJC_xxxcjcxx;
查看段大小
select owner,segment_name,segment_type,bytes/1024/1024,TABLESPACE_NAME from dba_segments where segment_name in ('T200');
行号 owner segment_name segment_type bytes/1024/1024 TABLESPACE_NAME
---------- ----- ------------ ------------ -------------------- ---------------
2 XXX t200 TABLE 24 MAIN
段大小只有24MB，看来真的是高水位问题!
晴天霹雳
马上反馈给厂商，咨询达梦是否有在线回收高水位的功能。
万万没想到，厂商给出的答复居然是达梦数据库一般不会有高水位问题，表碎片问题达梦数据库会自动回缩，通常不需要人为干预。
你在说什么？还有这么牛的功能？我表示怀疑，随便测试一下，到底会不会自动回缩，测试过程如下：
创建测试数据：
create table t10(id int,xxx varchar(10));
insert into t10 values(1,'a');
insert into t10 values(2,'a');
insert into t10 values(3,'a');
insert into t10 values(4,'a');
insert into t10 values(5,'a');
insert into t10 values(6,'a');
insert into t10 values(7,'a');
insert into t10 values(8,'axxxxxxxx');
insert into t10 values(9,'abbbbbb');
insert into t10 values(10,'aaaaaa');
commit;
insert into t10 select * from t10;
/
/
...
/
commit;
创建中间临时表：
create table t20 as select * from t10;
检查表段大小，322MB。
select owner,segment_name,segment_type,bytes/1024/1024,TABLESPACE_NAME from dba_segments where segment_name in ('T10','T20');
行号 owner segment_name segment_type bytes/1024/1024 TABLESPACE_NAME
---------- ----- ------------ ------------ -------------------- ---------------
1 XXX t10 TABLE 322 MAIN
2 XXX t20 TABLE 322 MAIN
t10表继续插入大量数据
insert into t10 select * from t10;
/
/
/
commit;
查看段大小，2564MB。
SQL> select owner,segment_name,segment_type,bytes/1024/1024,TABLESPACE_NAME from dba_segments where segment_name in ('T10','T20');
行号 owner segment_name segment_type bytes/1024/1024 TABLESPACE_NAME
---------- ----- ------------ ------------ -------------------- ---------------
1 XXX t10 TABLE 2564 MAIN
2 XXX t20 TABLE 322 MAIN
删除t10全部数据
delete from t10;
影响行数 83886080
commit;
插回原数据
INSERT INTO T10 SELECT * FROM T20;
影响行数 10485760
commit;
查看段大小，现在是2884MB，比之前322MB大很多
SQL> select owner,segment_name,segment_type,bytes/1024/1024,TABLESPACE_NAME from dba_segments where segment_name in ('T10','T20');
行号 owner segment_name segment_type bytes/1024/1024 TABLESPACE_NAME
---------- ----- ------------ ------------ -------------------- ---------------
1 XXX t10 TABLE 2884 MAIN
2 XXX t20 TABLE 322 MAIN
那么会不会自动回缩呢，厂商回复自动回缩时间和UNDO_RETENTION有关，等到90秒。
SQL> show parameter undo
行号 para_name para_value
---------- ------------------- ----------
1 UNDO_EXTENT_NUM 4
2 UNDO_RETENTION 90.000000
3 UNDO_BATCH_FAST 0
4 FINS_UNDO_OPT 1
5 HFS_UNDO_ERR_POLICY 0
下面就是见证奇迹的时刻！
查看段大小，居然真的在自动回缩了，现在是1795MB，
SQL> select owner,segment_name,segment_type,bytes/1024/1024,TABLESPACE_NAME from dba_segments where segment_name in ('T10','T20');
行号 owner segment_name segment_type bytes/1024/1024 TABLESPACE_NAME
---------- ----- ------------ ------------ -------------------- ---------------
1 XXX t10 TABLE 1795 MAIN
2 XXX t20 TABLE 322 MAIN
再查，1788MB
行号 owner segment_name segment_type bytes/1024/1024 TABLESPACE_NAME
---------- ----- ------------ ------------ -------------------- ---------------
1 XXX t10 TABLE 1788 MAIN
2 XXX t20 TABLE 322 MAIN
再查，最终回缩到323MB，几乎完全自动回缩到了原大小，不得不说，达梦很牛！
行号 owner segment_name segment_type bytes/1024/1024 TABLESPACE_NAME
---------- ----- ------------ ------------ -------------------- ---------------
1 XXX t10 TABLE 323 MAIN
2 XXX t20 TABLE 322 MAIN
回缩时，查看日志，有space相关的记录。
tail -100f dm_CJC_202403.log
......
2024-03-12 17:08:53.422 [INFO] database P0000016368 T0000000000000016413 checkpoint end, 148 pages flushed, used_space[350384128], free_space[3944574976].
2024-03-12 17:08:54.407 [INFO] database P0000016368 T0000000000000016430 checkpoint requested by CKPT_RLOG_SIZE, rlog free space[3944574976], used space[350384128]
2024-03-12 17:08:54.407 [INFO] database P0000016368 T0000000000000016430 checkpoint generate by used_space[350384128] > ckpt_rlog_size[134217728]
2024-03-12 17:08:54.407 [INFO] database P0000016368 T0000000000000016413 checkpoint begin, used_space[350384128], free_space[3944574976]...
2024-03-12 17:08:54.425 [INFO] database P0000016368 T0000000000000016413 ckpt2_log_adjust: full_status: 160, ptx_reserved: 0
2024-03-12 17:08:54.425 [INFO] database P0000016368 T0000000000000016413 ckpt2_log_adjust: ckpt_lsn(186609595), ckpt_fil(1), ckpt_off(595357696), cur_lsn(186807275), l_next_seq(55188), g_next_seq(55188), cur_free(797622272), total_space(4294959104), used_space(202264576), free_space(4092694528), n_ep(1), db_open_id(1)
但是，先别高兴，为什么生产库的那张表没有自动回缩，在测试库用CJC_xxxcjcxx表进行测试，发现确实有回缩的动作，
但是最终只回缩了了1/3，还有2/3没有回缩，怀疑自动回缩存在限制，比如字段类型，表数据类型等。
等等，我不是在优化SQL吗，怎么在研究表自动回缩的问题，似乎跑题了，那么这种没完全回缩的表究竟对性能有没有影响：
经测试，使用膨胀后的表和原始表进行全表扫描，耗时居然相同，执行计划查看代价等几乎完全一致。
也有可能是因为达梦默认创建的是索引组织表，oracle默认创建的是堆表。
看来单独凭oracle的运维经验复制到达梦上并不完全靠谱，还需要更理性客观的分析问题。
跑题了，回归正题！
让我们在回到最初的问题，为什么生产库执行这条SQL比测试库慢，对比看下SQL执行计划：
测试库：执行计划
1 #NSET2: [3, 1, 72]
2 #PRJT2: [3, 1, 72]; exp_num(3), is_atom(FALSE)
3 #HAGR2: [3, 1, 72]; grp_num(1), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(a.xxxcjcxx_id)
4 #HASH LEFT SEMI JOIN2: [2, 4, 72]; KEY_NUM(1); KEY(a.xxxcjcxx_id=DMTEMPVIEW_889244065.colname) KEY_NULL_EQU(0)
5 #SLCT2: [1, 24, 72]; exp_cast(a.del_flag) = 0
6 #CSEK2: [1, 487, 72]; scan_type(ASC), CJC_xxxcjcxx_cccccccccc_pod(CJC_xxxcjcxx_cccccccccc as a), scan_range[(0,2,min),(0,2,max))
7 #PRJT2: [1, 53, 104]; exp_num(1), is_atom(FALSE)
8 #SLCT2: [1, 53, 104]; exp_cast(CJC_xxxcjcxx.del_flag) = 0
9 #BLKUP2: [1, 1074, 104]; index_CJC_xxxcjcxx_create_by(CJC_xxxcjcxx)
10 #SSEK2: [1, 1074, 104]; scan_type(ASC), index_CJC_xxxcjcxx_create_by(CJC_xxxcjcxx), scan_range['10000000-1','10000000-1']
生产库：执行计划
1 #NSET2: [1, 1, 72]
2 #PRJT2: [1, 1, 72]; exp_num(3), is_atom(FALSE)
3 #HAGR2: [1, 1, 72]; grp_num(1), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(a.xxxcjcxx_id)
4 #INDEX JOIN SEMI JOIN2: [1, 1, 72]; join condition((CJC_xxxcjcxx.create_by = '10000000-1' AND exp_cast(CJC_xxxcjcxx.del_flag) = 0))
5 #SLCT2: [1, 24, 72]; exp_cast(a.del_flag) = 0
6 #CSEK2: [1, 487, 72]; scan_type(ASC), CJC_xxxcjcxx_cccccccccc_pod(CJC_xxxcjcxx_cccccccccc as a), scan_range[(0,2,min),(0,2,max))
7 #BLKUP2: [1, 1, 8]; INDEX33556933(CJC_xxxcjcxx)
8 #SSEK2: [1, 1, 8]; scan_type(ASC), INDEX33556933(CJC_xxxcjcxx), scan_range[a.xxxcjcxx_id,a.xxxcjcxx_id]
达梦的执行计划怎么看呢，和Oracle执行计划还是有一些区别的，至少操作符名称是不同的。
每个计划节点中包含例如操作符SSEK2和它的代价([1, 1, 8])等信息。
代价由一个三元组组成[代价,记录行数,字节数]。
代价的单位是毫秒，记录行数表示该计划节点输出的行数，字节数表示该计划节点输出的字节数。
在看执行分析执行计划之前，先使用ET工具，收集下资源消耗分布情况：
什么是ET工具呢？
ET工具是 DM 数据库自带的 SQL 性能分析工具，能够统计 SQL 语句执行过程中每个操作符的实际开销，为 SQL 优化提供依据以及指导。
但是ET功能的开启将对数据库整体性能造成一定影响，优化工作结束后尽量关闭该功能以提升数据库整体运行效率；
–启动ET
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);
执行慢SQL语句，获取执行号。
通过执行号，获取ET数据。
CALL ET(执行号);
测试库：
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- --------------------
1 PRJT2 2 0.02% 10 7 4 0 0 0 0 NULL NULL 0
2 PRJT2 2 0.02% 10 2 4 0 0 0 0 NULL NULL 0
3 DLCK 3 0.03% 9 0 2 0 0 0 0 NULL NULL 0
4 SLCT2 5 0.04% 8 8 4 0 0 0 0 NULL NULL 0
5 SSEK2 22 0.20% 7 10 2 0 0 0 0 NULL NULL 0
6 NSET2 55 0.49% 6 1 3 0 0 0 0 NULL NULL 0
7 BLKUP2 67 0.59% 5 9 4 0 0 0 0 NULL NULL 0
8 SLCT2 813 7.21% 4 5 293 0 0 0 0 NULL NULL 0
9 CSEK2 1913 16.96% 3 6 148 0 0 0 0 NULL NULL 0
10 HAGR2 3672 32.55% 2 3 4 1652 0 18 0 NULL NULL 0
11 HLS2 4726 41.90% 1 4 149 23231 0 39791 0 NULL NULL 6486
11 rows got
生产库：
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- --------------------
1 DLCK 3 0% 8 0 2 0 0 0 0 NULL NULL 0
2 PRJT2 3 0% 8 2 4 0 0 0 0 NULL NULL 0
3 NSET2 79 0.01% 7 1 3 0 0 0 0 NULL NULL 0
4 HAGR2 252 0.04% 6 3 6 1652 0 18 0 NULL NULL 0
5 SLCT2 1922 0.29% 5 5 293 0 0 0 0 NULL NULL 0
6 CSEK2 6558 0.98% 4 6 148 0 0 0 0 NULL NULL 0
7 IJS2 55037 8.20% 3 4 86445 0 0 0 0 NULL NULL 0
8 BLKUP2 283113 42.18% 2 7 172592 0 0 0 0 NULL NULL 0
9 SSEK2 324247 48.31% 1 8 86296 0 0 0 0 NULL NULL 0
9 rows got
ET 结果说明：
OP: 操作符
TIME(us): 时间开销，单位为微秒
PERCENT: 执行时间占总时间百分比
RANK: 执行时间耗时排序
SEQ: 执行计划节点号
N_ENTER: 进入次数
ET执行完，记得及时关闭：
–关闭 ET
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);
通过ET结果来看，生产库，时间开销最大的两部分是：SSEK2和BLKUP2操作，分别占比48.31%和42.18%，并且SEQ 7,8指执行计划节点号。在结合之前的执行计划，最耗时的SSEK2和BLKUP2分别对应执行计划如下两部分：
7 #BLKUP2: [1, 1, 8]; INDEX33556933(CJC_xxxcjcxx)
8 #SSEK2: [1, 1, 8]; scan_type(ASC), INDEX33556933(CJC_xxxcjcxx), scan_range[a.xxxcjcxx_id,a.xxxcjcxx_id]
可以看到，问题都出在了CJC_xxxcjcxx表的INDEX33556933索引上：
看下索引和表、列的关系：可以看到，INDEX33556933索引是CJC_xxxcjcxx表id列上的二级索引，通过dba_indexes可以看到，索引类型normal，不是cluster index。
生产库：
select TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where table_name in ('CJC_xxxcjcxx_cccccccccc','CJC_xxxcjcxx') and TABLE_OWNER='CCJC';
行号 TABLE_NAME COLUMN_NAME INDEX_NAME
---------- ----------------------- --------------- -----------------------------------------
1 CJC_xxxcjcxx id INDEX33556933
2 CJC_xxxcjcxx create_by index_CJC_xxxcjcxx_create_by
3 CJC_xxxcjcxx directory_id index_CJC_xxxcjcxx_directory_id
4 CJC_xxxcjcxx create_time index_CJC_xxxcjcxx_craete_time
5 CJC_xxxcjcxx update_time index_CJC_xxxcjcxx_update_time
6 CJC_xxxcjcxx id index_CJC_xxxcjcxx_id
7 CJC_xxxcjcxx del_flag index_CJC_xxxcjcxx_del_flag
8 CJC_xxxcjcxx_cccccccccc object_type CJC_xxxcjcxx_cccccccccc_pod
9 CJC_xxxcjcxx_cccccccccc del_flag CJC_xxxcjcxx_cccccccccc_pod
10 CJC_xxxcjcxx_cccccccccc cccccccccc_type CJC_xxxcjcxx_cccccccccc_pod
11 CJC_xxxcjcxx_cccccccccc id INDEX33557490
12 CJC_xxxcjcxx_cccccccccc xxxcjcxx_id index_CJC_xxxcjcxx_cccccccccc_xxxcjcxx_id
12 rows got
查看索引创建语句,是一个二级唯一索引
SQL> select dbms_metadata.get_ddl('INDEX','INDEX33556933','CCJC') from dual;
行号 dbms_metadata.get_ddl('INDEX','INDEX33556933','CCJC')
---------- -------------------------------------------------------------------------------------------------------------------
1 CREATE OR REPLACE UNIQUE INDEX "INDEX33556933" ON "CCJC"."CJC_xxxcjcxx"("id" ASC) STORAGE(ON "CCJC", CLUSTERBTR) ;
再回到最开始的慢SQL：
和CJC_xxxcjcxx表有关的部分如下：
select id from CJC_xxxcjcxx where create_by in ( '10000000-1' ) and del_flag=0
可以看到，生产库在子查询里执行这条语句时，居然走的是select中id列的索引，而没走where条件中create_by列的索引,但是测试单独运行这个子查询语句，走的是create_by列的索引。
生产库，走id列索引时：
#SSEK2: [1, 1, 8]; scan_type(ASC), INDEX33556933(CJC_xxxcjcxx), scan_range[a.xxxcjcxx_id,a.xxxcjcxx_id]
1.利用 a 表当前的 xxxcjcxx_id 值作为二级索引 INDEX33556933 定位查找的 KEY，返回结果给父节点；
这一过程理论上是比较耗时的，但是[1, 1, 8]预估只耗时1ms，1行记录，1字节数，很有可能是因为统计信息不准确导致的，实际上这个表上没有收集统计信息。
#BLKUP2: [1, 1, 8]; INDEX33556933(CJC_xxxcjcxx)
2.在通过上面的key值
在使用二级索引索引定位 rowid，再根据CJC_xxxcjcxx表的主键、聚集索引、rowid 等信息回表获取数据行中其它列的值，create_by、del_flag；
而测试库走的是where条件中create_by列的索引：
#SSEK2: [1, 1074, 104]; scan_type(ASC), index_CJC_xxxcjcxx_create_by(CJC_xxxcjcxx), scan_range['10000000-1','10000000-1']
1.利用CJC_xxxcjcxx表create_by in ( ‘10000000-1’ )条件值进行二级索引index_CJC_xxxcjcxx_create_by的扫描。
代价[1, 1074, 104]看上去更合理一些，不了解为什么测试库表也没有统计信息，但为什么预估的基数、代价更合理一些。
#BLKUP2: [1, 1074, 104]; index_CJC_xxxcjcxx_create_by(CJC_xxxcjcxx)
2.在使用二级索引索引定位 rowid，再根据CJC_xxxcjcxx表的主键、聚集索引、rowid 等信息回表获取数据行中其它列的值，del_flag、id；
其中create_by列数值虽然有部分倾斜，但走create_by列索引效率仍然很高，至少和生产库比，是先过滤大部分数据，然后在和a表的xxxcjcxx_id列进行关联。
在看下表统计信息，生产库和测试库这两张表都没有收集过统计信息。
SQL> SELECT OWNER,TABLE_NAME,STATUS,NUM_ROWS,BLOCKS,LAST_ANALYZED FROM DBA_TABLES WHERE OWNER='JC' AND TABLE_NAME IN ('CJC_xxxcjcxx_cccccccccc','CJC_xxxcjcxx');
行号 OWNER TABLE_NAME STATUS NUM_ROWS BLOCKS LAST_ANALYZED
---------- ----- ----------------------- ------ -------- ------ -------------
1 JC CJC_xxxcjcxx VALID NULL NULL NULL
2 JC CJC_xxxcjcxx_cccccccccc VALID NULL NULL NULL
分析到这里，我们可以猜测，如果通过hint强制走索引：
1.让测试库强制走CJC_xxxcjcxx表id列的索引，速度会变慢；
2.让生产库强制走CJC_xxxcjcxx表create_by列的索引，速度会变快。
先看下，让测试库强制走CJC_xxxcjcxx表id列的索引，是否和生产库执行计划相同：
测试库，变慢了，由11毫秒，变成了276毫秒，但速度还是比生产库执快，执行计划也不是完全相同。
select a.xxxcjcxx_id , max( a.object_cccccccccc) as share_cccccccccc, max( a.xxxcjcxx_downlJCd) as share_downlJCd
from CJC_xxxcjcxx_cccccccccc a
where a.cccccccccc_type=0
and a.object_type =2
and a.del_flag=0
and a.xxxcjcxx_id in (
select /*+index(CJC_xxxcjcxx INDEX33555465) */ id from CJC_xxxcjcxx where create_by in ( '10000000-1' ) and del_flag=0
)
group by a.xxxcjcxx_id ;
18 rows got
已用时间: 276.070(毫秒). 执行号:1303.
1 #NSET2: [5, 1, 72]
2 #PRJT2: [5, 1, 72]; exp_num(3), is_atom(FALSE)
3 #HAGR2: [5, 1, 72]; grp_num(1), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(a.xxxcjcxx_id)
4 #NEST LOOP SEMI JOIN2: [4, 1, 72]; join condition(a.xxxcjcxx_id = DMTEMPVIEW_889194448.colname)[with var]
5 #SLCT2: [1, 24, 72]; exp_cast(a.del_flag) = 0
6 #CSEK2: [1, 487, 72]; scan_type(ASC), CJC_xxxcjcxx_cccccccccc_pod(CJC_xxxcjcxx_cccccccccc as a), scan_range[(0,2,min),(0,2,max))
7 #PRJT2: [1, 1, 104]; exp_num(1), is_atom(FALSE)
8 #SLCT2: [1, 1, 104]; (CJC_xxxcjcxx.create_by = '10000000-1' AND exp_cast(CJC_xxxcjcxx.del_flag) = 0)
9 #BLKUP2: [1, 1, 104]; INDEX33555465(CJC_xxxcjcxx)
10 #SSEK2: [1, 1, 104]; scan_type(ASC), INDEX33555465(CJC_xxxcjcxx), scan_range[var1,var1]
已用时间: 1.803(毫秒). 执行号:0.
再试下，让生产库强制走CJC_xxxcjcxx表create_by列的索引，速度会变快：
执行计划没变，hint没生效，还是走的id列的索引？
select a.xxxcjcxx_id , max( a.object_cccccccccc) as share_cccccccccc, max( a.xxxcjcxx_downlJCd) as share_downlJCd
from CJC_xxxcjcxx_cccccccccc a
where a.cccccccccc_type=0
and a.object_type =2
and a.del_flag=0
and a.xxxcjcxx_id in (
select /*+index(CJC_xxxcjcxx,index_CJC_xxxcjcxx_create_by) */ id from CJC_xxxcjcxx where create_by in ( '10000000-1' ) and del_flag=0
)
group by a.xxxcjcxx_id ;
山穷水尽
为什么生产库的hint不生效，除非优化器认为通过hint得出的是错误的数据才会忽略hint。
难道生产库不支持子查询里的hint？
测试如下，发现只有这套库不支持在子查询里添加hint。
创建两张简单的表，测试子查询里的hint：
创建测试数据
create table t0314(id int,name varchar(10),xxx int);
insert into t0314 values(1,'aaa',1);
commit;
create index i_t0314_01 on t0314(id);
create index i_t0314_02 on t0314(id,name);
create index i_t0314_03 on t0314(name);
create table t0315 as select * from t0314;
create index i_t0315_01 on t0315(id);
create index i_t0315_02 on t0315(id,name);
insert into t0315 values(2,'ccc',1);
insert into t0315 values(3,'ddd',3);
insert into t0315 values(4,'fff',2);
insert into t0315 values(5,'xxx',6);
commit;
hint测试,默认走i_t0314_02索引
explain select id,name from t0315 where xxx>=0 and id in (select id from t0314 where name='aaa');
1 #NSET2: [1, 1, 68]
2 #PRJT2: [1, 1, 68]; exp_num(3), is_atom(FALSE)
3 #INDEX JOIN SEMI JOIN2: [1, 1, 68];
4 #SLCT2: [1, 1, 68]; t0315.xxx >= 0
5 #CSCN2: [1, 5, 68]; INDEX33557674(t0315)
6 #SSEK2: [1, 1, 52]; scan_type(ASC), i_t0314_02(t0314), scan_range[(t0315.id,'aaa'),(t0315.id,'aaa')]
通过hint强制走i_t0314_03索引，没生效，还是走i_t0314_02索引？？？
explain select id,name from t0315 where xxx>=0 and id in (select /*+index(t0314 i_t0314_03)*/ id from t0314 where name='aaa');
1 #NSET2: [1, 1, 68]
2 #PRJT2: [1, 1, 68]; exp_num(3), is_atom(FALSE)
3 #INDEX JOIN SEMI JOIN2: [1, 1, 68];
4 #SLCT2: [1, 1, 68]; t0315.xxx >= 0
5 #CSCN2: [1, 5, 68]; INDEX33557674(t0315)
6 #SSEK2: [1, 1, 52]; scan_type(ASC), i_t0314_02(t0314), scan_range[(t0315.id,'aaa'),(t0315.id,'aaa')]
不在子查询里的hint可以生效
SQL> explain select id from t0314 where name='aaa';
1 #NSET2: [1, 1, 64]
2 #PRJT2: [1, 1, 64]; exp_num(2), is_atom(FALSE)
3 #BLKUP2: [1, 1, 64]; i_t0314_03(t0314)
4 #SSEK2: [1, 1, 64]; scan_type(ASC), i_t0314_03(t0314), scan_range['aaa','aaa']
SQL> explain select /*+index(t0314 i_t0314_01)*/ id from t0314 where name='aaa';
1 #NSET2: [1, 1, 64]
2 #PRJT2: [1, 1, 64]; exp_num(2), is_atom(FALSE)
3 #SLCT2: [1, 1, 64]; t0314.name = 'aaa'
4 #BLKUP2: [1, 1, 64]; i_t0314_01(t0314)
5 #SSCN: [1, 1, 64]; i_t0314_01(t0314)
其他库测试都是正常的，hint都可以生效，这套库有什么特别吗？
首先想到的会不会有什么参数控制hint行为呢，对比两套库部分参数信息，发现完全一致：
set pagesize 100;
select name,type,value,default_value,isdefault from v$parameter
where name in
(
'ENABLE_HASH_JOIN',
'ENABLE_INDEX_JOIN',
'ENABLE_DIST_IN_SUBQUERY_OPT',
'SUBQ_CVT_SPL_FLAG',
'ENABLE_RQ_TO_SPL',
'PRJT_REPLACE_NPAR',
'ENABLE_RQ_TO_INV',
'SUBQ_EXP_CVT_FLAG',
'REFED_SUBQ_CROSS_FLAG',
'IN_LIST_AS_JOIN_KEY',
'ENABLE_RQ_TO_NONREF_SPL',
'NONREFED_SUBQUERY_AS_CONST',
'ENABLE_IN_VALUE_LIST_OPT',
'PUSH_SUBQ',
'SEL_ITEM_HTAB_FLAG',
'OP_SUBQ_CVT_IN_FLAG',
'USE_REFER_TAB_ONLY',
'OPTIMIZER_MODE',
'OLAP_FLAG',
'OPTIMIZER_DYNAMIC_SAMPLING'
) order by 1;
柳暗花明
那么还有其他办法在不影响使用的情况下，提前查看干预生产库这条SQL的执行计划效果吗？
通过上面的测试可以看到，生产库子查询里的hint不生效，那么是否可以在外层添加hint？
在看一下慢SQL，结合执行计划、索引信息等可以知道，外层a表走的索引是CJC_xxxcjcxx_cccccccccc_pod，这个索引是object_type、del_flag、cccccccccc_type列的组合索引。
奇怪，这个执行计划也有问题，难道不应该走和子查询关联的xxxcjcxx_id列索引吗？
select a.xxxcjcxx_id , max( a.object_cccccccccc) as share_cccccccccc, max( a.xxxcjcxx_downlJCd) as share_downlJCd
from CJC_xxxcjcxx_cccccccccc a
where a.cccccccccc_type=0
and a.object_type =2
and a.del_flag=0
and a.xxxcjcxx_id in (
select id from CJC_xxxcjcxx where create_by in ( '10000000-1' ) and del_flag=0
)
group by a.xxxcjcxx_id ;
再次执行hint,强制走a表的xxxcjcxx_id列索引，这次的hint并没有加到子查询里，所以是可以生效的：
select /*+index(a index_CJC_xxxcjcxx_cccccccccc_xxxcjcxx_id) */ a.xxxcjcxx_id , max( a.object_cccccccccc) as share_cccccccccc, max( a.xxxcjcxx_downlJCd) as share_downlJCd
from CJC_xxxcjcxx_cccccccccc a
where a.cccccccccc_type=0
and a.object_type =2
and a.del_flag=0
and a.xxxcjcxx_id in (
select id from CJC_xxxcjcxx where create_by in ( '10000000-1' ) and del_flag=0
)
group by a.xxxcjcxx_id ;
已用时间: 2.022(毫秒). 执行号:86854935.
可以看到hint生效了，执行时间大幅度降低了，由最开始的653.949(毫秒)降低到2.022(毫秒)，而且另一个表的索引也走对了，不再走id列索引了。
查看执行计划
1 #NSET2: [7, 1, 176]
2 #PRJT2: [7, 1, 176]; exp_num(3), is_atom(FALSE)
3 #HAGR2: [7, 1, 176]; grp_num(1), sfun_num(2), distinct_flag[0,0]; slave_empty(0) keys(a.xxxcjcxx_id)
4 #SLCT2: [6, 107, 176]; (a.cccccccccc_type = 0 AND a.object_type = 2 AND exp_cast(a.del_flag) = 0)
5 #NEST LOOP INDEX JOIN2: [6, 107, 176]
6 #PRJT2: [1, 53, 104]; exp_num(1), is_atom(FALSE)
7 #SLCT2: [1, 53, 104]; exp_cast(CJC_xxxcjcxx.del_flag) = 0
8 #BLKUP2: [1, 1076, 104]; index_CJC_xxxcjcxx_create_by(CJC_xxxcjcxx)
9 #SSEK2: [1, 1076, 104]; scan_type(ASC), index_CJC_xxxcjcxx_create_by(CJC_xxxcjcxx), scan_range['10000000-1','10000000-1']
10 #BLKUP2: [3, 2, 64]; index_CJC_xxxcjcxx_cccccccccc_xxxcjcxx_id(a)
11 #SSEK2: [3, 2, 64]; scan_type(ASC), index_CJC_xxxcjcxx_cccccccccc_xxxcjcxx_id(CJC_xxxcjcxx_cccccccccc as a), scan_range[DMTEMPVIEW_889564116.colname,DMTEMPVIEW_889564116.colname]
解决方案
分析到这里，可以知道生产库的这个SQL在不改写的情况下，是可以变快的，有优化空间，实际上测试将子查询改写成表关联效果也并不好。
接下来就是要分析为什么执行计划会走错，如何解决。
当然凭经验来看，很有可能是因为统计信息缺失，基数预估不准，导致生成错误的执行计划，但究竟是不是这个原因，还需要通过10053或其他技术来深入研究了。
后面可能会操作的部分：1.通过导出导入等方式手动回缩表；2.收集相关表和索引统计信息等。
还有一些其他的问题仍是一头雾水：
比如表碎片不完全自动回缩，自动回缩的原理是什么？有哪些限制？
为什么生产库子查询中的hint不生效？
看来还是要继续学习了。
达梦数据库执行计划相关知识点
执行计划说明：
https://eco.dameng.com/document/dm/zh-cn/ops/performance-optimization.html
索引扫描
SSEK、CSEK、SSCN：索引扫描
SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。
CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。
SSCN 是索引全扫描，不需要扫描表。
BLKUP：二次扫描 (回表)
BLKUP 先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。
CSCN：全表扫描
CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。
全表扫描 I/O 开销较大，在一个高并发的系统中应尽量避免全表扫描。
NSET：结果集收集
NSET 是用于结果集收集的操作符，一般是查询计划的顶层节点，优化工作中无需对该操作符过多关注，一般没有优化空间。
PRJT：投影
PRJT 是关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。优化工作中无需对该操作符过多关注，一般没有优化空间。
SLCT：选择
SLCT 是关系的【选择】运算，用于查询条件的过滤。
可比较返回结果集与代价估算中是否接近，如相差较大可考虑收集统计信息。
若该过滤条件过滤性较好，可考虑在条件列增加索引。
AAGR：简单聚集
AAGR 用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。
EXPLAIN SELECT COUNT(*) FROM T1 WHERE C1 = 10;
FAGR：快速聚集
FAGR 用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值。
EXPLAIN SELECT MAX(C1) FROM T1;
SAGR：流分组聚集
SAGR 用于分组列是有序的情况下，可以使用流分组聚集，C1 列上已经创建了索引，SAGR2 性能优于 HAGR2。
EXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;
NEST LOOP：嵌套循环连接
嵌套循环连接是最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。
驱动表的行数就是循环的次数，将在很大程度上影响执行效率。
连接列是否有索引，都可以走 NEST LOOP，但没有索引，执行效率会很差
select /+use_nl(t1,t2)/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2=‘A’;
适用场景：
驱动表有很好的过滤条件
表连接条件能使用索引
结果集比较小
HASH JOIN：哈希连接
哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。
哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算。
MERGE JOIN：归并排序连接
归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。
更多详细信息见：
https://eco.dameng.com/document/dm/zh-cn/pm/dm8-admin-manual-appendix#%E9%99%84%E5%BD%95%204%20%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92%E6%93%8D%E4%BD%9C%E7%AC%A6
###chenjuchao 20240316###
欢迎关注我的公众号《IT小Chen》