达梦查询慢sql

转 https://eco.dameng.com/community/training/625a4c03f0f0e8acf835773057548014

第一步:标注行号
SELECT count(distinct(QM.queueno))
FROM CQ_QM_QUEUESERIAL_LOG QM, CQ_PARA_WINDOW_INFO QW, CQ_PARA_CALLRULE_INFO RU
WHERE QW.RuleID = RU.RuleID --1
AND (QM.BsID = RU.BsID -2
AND QM.QueueTpID = RU.QueueTpID or QM.QueueCallTp = '2') --3
AND QM.Brno = QW.Brno --4
AND QM.QueueTpStatus = '0' --5
AND (QM.QueueCallTp in ('1','2')) --6
AND QW.Brno = '769001' --7
AND QW.WinNo = '2' --8
and (qm.WinNo is null or (qm.WinNo = '2' and qm.QueueCallTp = '2')) --9
AND QM.WorkDate ='20231124' --10
AND QueueCallTime is null --11

第二步：通过黄线标准，执行步骤在执行段的位置（上图手工标注的行）

44 #HASH RIGHT SEMI JOIN2: [15, 1, 744]; n_keys(1) KEY(DMTEMPVIEW_16826789.colname='2') KEY_NULL_EQU(0)
45 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
1,2,3 #HASH2 INNER JOIN: [15, 1, 744]; KEY_NUM(3); KEY(QM.QUEUETPID=RU.QUEUETPID AND QW.RULEID=RU.RULEID AND exp_cast(QM.BSID)=exp_cast(RU.BSID)-var5) KEY_NULL_EQU(0, 0, 0)
47 #SLCT2: [15, 1, 744]; exp_cast(QM.BSID) = exp_cast(RU.BSID)-var4
48 #NEST LOOP INDEX JOIN2: [15, 1, 744]
49 #ACTRL: [15, 1, 744];
50 #NEST LOOP INDEX JOIN2: [15, 1, 592]
9,11,9,,5,4,7 #SLCT2: [15, 1, 440]; (QM.QUEUECALLTIME IS NULL AND QM.WINNO = '2' AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231124' AND QM.BRNO = '769001') ->这里执行了9,11,9,,5,4,7 步
52 #CSCN2: [15, 74094, 440]; INDEX33556329(CQ_QM_QUEUESERIAL_LOG as QM)
53 #BLKUP2: [1, 1, 96]; INDEX33555760(QW)
7,8 #SSEK2: [1, 1, 96]; scan_type(ASC), INDEX33555760(CQ_PARA_WINDOW_INFO as QW), scan_range[('769001','2'),('769001','2')]
55 #SSEK2: [1, 1, 144]; scan_type(ASC), INDEX33555752(CQ_PARA_CALLRULE_INFO as RU), scan_range[(QW.RULEID,QM.QUEUETPID,min),(QW.RULEID,QM.QUEUETPID,max))
1 #SSCN: [1, 187, 152]; INDEX33555752(CQ_PARA_CALLRULE_INFO as RU)

达梦数据库监控正在执行的长SQL

欢喜感恩的心（如心） 2022/11/28 280 0 0

我们在运维中经常会遇到一个需求：就是业务组请求支援，说跑批卡死，要求定位出哪条长SQL语句在执行。确认语句无误后kill掉该会话。

在ORACLE数据库中，v$session视图中有一个SQL_EXEC_START字段代表会话正在调用sql的本次开始执行时间。用如下语句可以方便查出系统中正运行超过1小时的长SQL：

select session_id,program,username,sql_id,event
from v$session 
where status='ACTIVE' and SQL_EXEC_START<sysdate-1/24

复制

当然可以根据实际情况修改时长。比如定位执行超过半小时的语句，上面1/24修改为1/48。定位执行时间超过10分钟的语句，修改为1/24/6。

通过select sql_text from v$sqlarea where sql_id=‘xxx’;或者v$sqltext视图查出sql_id对应的真实语法。业务组确认sql语句无误后，kill掉该会话。

达梦数据库引入了一个视图v$long_exec_sqls，可以方便地查询出执行时间超过1000毫秒的语句。但在实验中发现该视图只能显示已经执行完了的语句，尚未结束的长SQL无法追踪。因此我编了一个小程序实现此功能：

select elapsed,clnt_ip,appname,user_name,RUN_STATUS,sql_text,b.sql_id,
to_char(create_time,'yyyy-mm-dd hh24:mi:ss') session_logon_time,to_char(sql_start_time,
'yyyy-mm-dd hh24:mi:ss') sql_exec_start,sess_id,state
from
(  select sec_to_time(count(*)) elapsed,min(sample_time) sql_start_time,
   session_id,session_serial#,sql_id
   from v$active_session_history 
   group by  session_id,session_serial#,sql_id,sql_exec_id
   having time_to_sec(max(bu_time))>time_to_sec(sysdate)-2
) active_session,v$sessions b
where active_session.session_id=b.sess_id and active_session.session_serial#=b.SESS_SEQ
and active_session.sql_id=b.sql_id
order by elapsed desc
limit 10

复制

上面程序输出系统中所有正在执行的SQL语句，按照已执行时间倒排序，显示TOP10。

业务组确认语句无误后，通过sp_close_session（session_id）kill掉会话。

还有更简单的实现方法：

select clnt_ip,sec_to_time(datediff(ss,last_send_time,sysdate)) elapsed,appname,user_name,RUN_STATUS,sql_id,sql_text,last_send_time
from v$sessions where state in ('ACTIVE','WAIT')
order by elapsed desc
limit 10

复制

通过last_send_time和sysdate的差值计算sql语句的执行时间。last_send_time每次发起新的调用时重置，相当于ORACLE里的sql_exec_start字段。不过在测试中发现，极个别时last_send_time停留在上次调用没有刷新，计算出的elapsed有可能偏大。

######sample 3: 达梦sql 优化案例一则

###############sql 1: 索引隐式转发以及效率不高导致，执行效率很慢， oracle 耗时大概2.676s 达梦大概 dm:99.618s，

总结如下 OMCP

两表关联：一张表1000万，一张表2000万，两者使用hash 关联查询，索引未使用导致执行效率非常慢。
1.怀疑是数据隐身转换问题，exp_cast(THIS_.TRAN_INST) = 756045 , 导致数据库没有使用到索引IDX_FLOW_WATER_02 (表T_FLOW_WATER ),加入引号，从46 变成9秒

(详细说明：其中EXP_CAST(T2.ID)> 5 提供的信息告诉我们，列ID 和数字5进行比较，是要将列作类型转换的，那么也就是说，就算ID列上存在索引，可能也不能进行范围扫描，因为索引范围扫描的输入要求是和索引列上的类型相同，我们验证一下。"TRAN_DATE" VARCHAR2(32)定义的是字符类型，查询条件写的是数字，涉及隐身转换)

CREATE INDEX "IDX_FLOW_WATER_02" ON "USEROPR"."T_FLOW_WATER"("SCAN_BATCHNUM" ASC,"TRAN_INST" ASC,"IS_COP" ASC,"TRAN_DATE" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;

2. "IDX_FLOW_WATER_02" 组合索引里面字段的顺序也会影响数据库组合索引是否能够使用，等值= 过滤性最强的应该放在最前面
CREATE INDEX "IDX_FLOW_WATER_02" ON "USEROPR"."T_FLOW_WATER"("TRAN_INST" ASC,"IS_COP" ASC,"SCAN_BATCHNUM" ASC,"TRAN_DATE" ASC)

3 .sql 加入hint ,/*+ enable_index_filter(1) */, 可以将sql 从4秒提升到3秒

lain
1 #NSET2: [15997, 195, 2365]
2 #PRJT2: [15997, 195, 2365]; exp_num(100), is_atom(FALSE)
3 #PRJT2: [15997, 195, 2365]; exp_num(100), is_atom(FALSE)
4 #SORT3: [15997, 195, 2365]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
5 #HASH LEFT JOIN2: [15982, 19591, 2365]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(exp_cast(THIS_.IMG_ID)=IMGINFODTO2_.ID)
6 #INDEX JOIN LEFT JOIN2: [15982, 19591, 2365] ret_null(0)
7 #ACTRL: [15982, 19591, 2365];
8 #PARALLEL: [14878, 19591, 2365]; scan_type(FULL), key_num(0, 0, 0), simple(0)

【解释：搜索条件是 (NOT(THIS_.SCAN_BATCHNUM IS NULL) AND exp_cast(THIS_.TRAN_INST) = 756045 AND exp_cast(THIS_.IS_COP) = 1 AND exp_cast(THIS_.TRAN_DATE) >= 20200818)，返回了19591】
9 #SLCT2: [14878, 19591, 2365]; (NOT(THIS_.SCAN_BATCHNUM IS NULL) AND exp_cast(THIS_.TRAN_INST) = 756045 AND exp_cast(THIS_.IS_COP) = 1 AND exp_cast(THIS_.TRAN_DATE) >= 20200818)
10 #CSCN2: [14878, 19542548, 2365]; INDEX33560845(T_FLOW_WATER as THIS_)

【解释：例如：第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是14878，扫描的记录行数是 19542548 行，输出字节数是 2365个。】
11 #PARALLEL: [220, 1, 30]; scan_type(FULL), key_num(0, 0, 0), simple(0)
12 #BLKUP2: [220, 1, 30]; INDEX33561349(IMGINFODTO2_)
13 #SSEK2: [220, 1, 30]; scan_type(ASC), INDEX33561349(T_IMG_INFO as IMGINFODTO2_), scan_range[exp_cast(THIS_.IMG_ID),exp_cast(THIS_.IMG_ID)]
14 #PARALLEL: [15978, 24627876, 2041]; scan_type(FULL), key_num(0, 0, 0), simple(0)
15 #CSCN2: [15978, 24627876, 2041]; INDEX33560822(T_IMG_INFO as IMGINFODTO2_)

->例如：第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是15978，扫描的记录行数是 24627876行，输出字节数是 2365个。

--具体介绍如下：

因为问题可以重现，所以方法如下：
step 0 : open et parameter 在session 级别打开MONITOR_SQL_EXEC，默认是不打开的。现在打开，就可以收集et 报告

SELECT * FROM V$DM_INI WHERE PARA_NAME IN ('ENABLE_MONITOR','MONITOR_TIME','MONITOR_SQL_EXEC');
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);

step 1: collect et report ，是显示消耗时间的报告

after the sql running ,then you can collect ex report ,using dm management tool:

select
*
from
( select
this_.ID as ID278_1_,
this_.ACC_NAME1 as ACC2_278_1_,
this_.ACC_NAME2 as ACC3_278_1_,
this_.ACC_NO1 as ACC4_278_1_,
this_.ACC_NO2 as ACC5_278_1_,
this_.AMT as AMT278_1_,
this_.AUTH_OPT_NAME as AUTH7_278_1_,
this_.AUTH_OPT_NO as AUTH8_278_1_,
this_.CARD_NO1 as CARD9_278_1_,
this_.CARD_NO2 as CARD10_278_1_,
this_.CCY as CCY278_1_,
this_.CHECK_NO as CHECK12_278_1_,
this_.CLT_SEQNO as CLT13_278_1_,
this_.CORE_SEQ as CORE14_278_1_,
this_.CSH_TSF_FLAG as CSH15_278_1_,
this_.CURR_MONTH as CURR16_278_1_,
this_.CUSTOM_REQ as CUSTOM17_278_1_,
this_.DR_CR_FLAG as DR18_278_1_,
this_.EXTRA_RULE as EXTRA19_278_1_,
this_.FIRST_ORGID as FIRST20_278_1_,
this_.FIRST_ORGNAME as FIRST21_278_1_,
this_.FIRST_ORGNUM as FIRST22_278_1_,
this_.FIRST_ORGTYPE as FIRST23_278_1_,
this_.FIRST_ORGTYPENAME as FIRST24_278_1_,
this_.HAND_DATE as HAND25_278_1_,
this_.IMG_ID as IMG53_278_1_,
this_.IS_COP as IS26_278_1_,
this_.IS_REMOTE_AUTHORITY as IS27_278_1_,
this_.MONITOR_RULE as MONITOR28_278_1_,
this_.RELATION_STATUS as RELATION29_278_1_,
this_.REMARKS as REMARKS278_1_,
this_.REMARKS1 as REMARKS31_278_1_,
this_.REVERSAL_FLAG as REVERSAL32_278_1_,
this_.SCAN_BATCHNUM as SCAN33_278_1_,
this_.SECOND_ORGID as SECOND34_278_1_,
this_.SECOND_ORGNAME as SECOND35_278_1_,
this_.SECOND_ORGNUM as SECOND36_278_1_,
this_.SUPERVISE_DATE as SUPERVISE37_278_1_,
this_.SUPERVISE_STATUS as SUPERVISE38_278_1_,
this_.SUPERVISOR_CODE as SUPERVISOR39_278_1_,
this_.SUPERVISOR_NAME as SUPERVISOR40_278_1_,
this_.THIRD_ORGID as THIRD41_278_1_,
this_.THIRD_ORGNAME as THIRD42_278_1_,
this_.THIRD_ORGNUM as THIRD43_278_1_,
this_.TRADE_BREED_NAME as TRADE44_278_1_,
this_.TRADE_SUB_NAME as TRADE45_278_1_,
this_.TRAN_DATE as TRAN46_278_1_,
this_.TRAN_INST as TRAN47_278_1_,
this_.TRAN_OPT_NAME as TRAN48_278_1_,
this_.TRAN_OPT_NO as TRAN49_278_1_,
this_.TRAN_STAT as TRAN50_278_1_,
this_.TRAN_TYPE as TRAN51_278_1_,
this_.TRAN_TIME as TRAN52_278_1_,
imginfodto2_.ID as ID259_0_,
imginfodto2_.ACCOUNT as ACCOUNT259_0_,
imginfodto2_.ACCOUNT1 as ACCOUNT3_259_0_,
imginfodto2_.ACCOUNT1_NAME as ACCOUNT4_259_0_,
imginfodto2_.ACCOUNT_NAME as ACCOUNT5_259_0_,
imginfodto2_.AMT as AMT259_0_,
imginfodto2_.AMT1 as AMT7_259_0_,
imginfodto2_.AMT_D as AMT8_259_0_,
imginfodto2_.BATCH_NO as BATCH9_259_0_,
imginfodto2_.CETIF_DATE as CETIF10_259_0_,
imginfodto2_.CETIF_NO as CETIF11_259_0_,
imginfodto2_.CETIF_TYPE_ID as CETIF12_259_0_,
imginfodto2_.CETIF_TYPE_NAME as CETIF13_259_0_,
imginfodto2_.CLI_SERIAL_NO as CLI14_259_0_,
imginfodto2_.COMPOSITE as COMPOSITE259_0_,
imginfodto2_.CURR_MONTH as CURR16_259_0_,
imginfodto2_.EXTRA_RULE as EXTRA17_259_0_,
imginfodto2_.FH_ORG as FH18_259_0_,
imginfodto2_.FLOW_TRAN_NOTE as FLOW19_259_0_,
imginfodto2_.FLOW_WATER_ID as FLOW20_259_0_,
imginfodto2_.HAS_PROBLEMS as HAS21_259_0_,
imginfodto2_.IMAGE_CHANNEL as IMAGE22_259_0_,
imginfodto2_.IMAGESTATION_NID as IMAGEST23_259_0_,
imginfodto2_.IMAGESTATION_PID as IMAGEST24_259_0_,
imginfodto2_.IMG_N as IMG25_259_0_,
imginfodto2_.IMG_P as IMG26_259_0_,
imginfodto2_.INNER_ID as INNER27_259_0_,
imginfodto2_.IS_NEED_IMPORTANT as IS28_259_0_,
imginfodto2_.IS_SCANTYPE_ADD as IS29_259_0_,
imginfodto2_.MAKEUP_DATE as MAKEUP30_259_0_,
imginfodto2_.MAKEUP_USER as MAKEUP31_259_0_,
imginfodto2_.MONITOR_RULE as MONITOR32_259_0_,
imginfodto2_.OPERATOR_NO as OPERATOR33_259_0_,
imginfodto2_.ORG_NO as ORG34_259_0_,
imginfodto2_.OUTO_CHECK as OUTO35_259_0_,
imginfodto2_.PS_LEVEL as PS36_259_0_,
imginfodto2_.SCAN_ADD_REMARK as SCAN37_259_0_,
imginfodto2_.SIGN as SIGN259_0_,
imginfodto2_.SLAVE_COUNT as SLAVE39_259_0_,
imginfodto2_.SP_FLAG as SP40_259_0_,
imginfodto2_.SUPERVISE_DATE as SUPERVISE41_259_0_,
imginfodto2_.SUPERVISE_STATUS as SUPERVISE42_259_0_,
imginfodto2_.SUPERVISES as SUPERVISES259_0_,
imginfodto2_.SUPERVISOR_CODE as SUPERVISOR44_259_0_,
imginfodto2_.SUPERVISOR_NAME as SUPERVISOR45_259_0_,
imginfodto2_.TRADE_DATE as TRADE46_259_0_,
imginfodto2_.Z_INNERID as Z47_259_0_
from
USERopr.T_FLOW_WATER this_,
USERopr.T_IMG_INFO imginfodto2_
where
this_.IMG_ID=imginfodto2_.ID(+)
and this_.TRAN_INST=756045
and this_.IS_COP=1
and this_.TRAN_DATE>=20200818
and (
this_.SCAN_BATCHNUM is not null
)
order by
this_.ID desc )
where
rownum <= 15;

step2 : collect explain ,no need running the sql .

方案1 显示执行计划：

打开达梦工具的窗口选项如下配置项目，可显示执行计划

编辑器如下配置项目

方案2 显示执行计划：：

explain select
*
from
( select
this_.ID as ID278_1_,
this_.ACC_NAME1 as ACC2_278_1_,
this_.ACC_NAME2 as ACC3_278_1_,
this_.ACC_NO1 as ACC4_278_1_,
this_.ACC_NO2 as ACC5_278_1_,
this_.AMT as AMT278_1_,
this_.AUTH_OPT_NAME as AUTH7_278_1_,
this_.AUTH_OPT_NO as AUTH8_278_1_,
this_.CARD_NO1 as CARD9_278_1_,
this_.CARD_NO2 as CARD10_278_1_,
this_.CCY as CCY278_1_,
this_.CHECK_NO as CHECK12_278_1_,
this_.CLT_SEQNO as CLT13_278_1_,
this_.CORE_SEQ as CORE14_278_1_,
this_.CSH_TSF_FLAG as CSH15_278_1_,
this_.CURR_MONTH as CURR16_278_1_,
this_.CUSTOM_REQ as CUSTOM17_278_1_,
this_.DR_CR_FLAG as DR18_278_1_,
this_.EXTRA_RULE as EXTRA19_278_1_,
this_.FIRST_ORGID as FIRST20_278_1_,
this_.FIRST_ORGNAME as FIRST21_278_1_,
this_.FIRST_ORGNUM as FIRST22_278_1_,
this_.FIRST_ORGTYPE as FIRST23_278_1_,
this_.FIRST_ORGTYPENAME as FIRST24_278_1_,
this_.HAND_DATE as HAND25_278_1_,
this_.IMG_ID as IMG53_278_1_,
this_.IS_COP as IS26_278_1_,
this_.IS_REMOTE_AUTHORITY as IS27_278_1_,
this_.MONITOR_RULE as MONITOR28_278_1_,
this_.RELATION_STATUS as RELATION29_278_1_,
this_.REMARKS as REMARKS278_1_,
this_.REMARKS1 as REMARKS31_278_1_,
this_.REVERSAL_FLAG as REVERSAL32_278_1_,
this_.SCAN_BATCHNUM as SCAN33_278_1_,
this_.SECOND_ORGID as SECOND34_278_1_,
this_.SECOND_ORGNAME as SECOND35_278_1_,
this_.SECOND_ORGNUM as SECOND36_278_1_,
this_.SUPERVISE_DATE as SUPERVISE37_278_1_,
this_.SUPERVISE_STATUS as SUPERVISE38_278_1_,
this_.SUPERVISOR_CODE as SUPERVISOR39_278_1_,
this_.SUPERVISOR_NAME as SUPERVISOR40_278_1_,
this_.THIRD_ORGID as THIRD41_278_1_,
this_.THIRD_ORGNAME as THIRD42_278_1_,
this_.THIRD_ORGNUM as THIRD43_278_1_,
this_.TRADE_BREED_NAME as TRADE44_278_1_,
this_.TRADE_SUB_NAME as TRADE45_278_1_,
this_.TRAN_DATE as TRAN46_278_1_,
this_.TRAN_INST as TRAN47_278_1_,
this_.TRAN_OPT_NAME as TRAN48_278_1_,
this_.TRAN_OPT_NO as TRAN49_278_1_,
this_.TRAN_STAT as TRAN50_278_1_,
this_.TRAN_TYPE as TRAN51_278_1_,
this_.TRAN_TIME as TRAN52_278_1_,
imginfodto2_.ID as ID259_0_,
imginfodto2_.ACCOUNT as ACCOUNT259_0_,
imginfodto2_.ACCOUNT1 as ACCOUNT3_259_0_,
imginfodto2_.ACCOUNT1_NAME as ACCOUNT4_259_0_,
imginfodto2_.ACCOUNT_NAME as ACCOUNT5_259_0_,
imginfodto2_.AMT as AMT259_0_,
imginfodto2_.AMT1 as AMT7_259_0_,
imginfodto2_.AMT_D as AMT8_259_0_,
imginfodto2_.BATCH_NO as BATCH9_259_0_,
imginfodto2_.CETIF_DATE as CETIF10_259_0_,
imginfodto2_.CETIF_NO as CETIF11_259_0_,
imginfodto2_.CETIF_TYPE_ID as CETIF12_259_0_,
imginfodto2_.CETIF_TYPE_NAME as CETIF13_259_0_,
imginfodto2_.CLI_SERIAL_NO as CLI14_259_0_,
imginfodto2_.COMPOSITE as COMPOSITE259_0_,
imginfodto2_.CURR_MONTH as CURR16_259_0_,
imginfodto2_.EXTRA_RULE as EXTRA17_259_0_,
imginfodto2_.FH_ORG as FH18_259_0_,
imginfodto2_.FLOW_TRAN_NOTE as FLOW19_259_0_,
imginfodto2_.FLOW_WATER_ID as FLOW20_259_0_,
imginfodto2_.HAS_PROBLEMS as HAS21_259_0_,
imginfodto2_.IMAGE_CHANNEL as IMAGE22_259_0_,
imginfodto2_.IMAGESTATION_NID as IMAGEST23_259_0_,
imginfodto2_.IMAGESTATION_PID as IMAGEST24_259_0_,
imginfodto2_.IMG_N as IMG25_259_0_,
imginfodto2_.IMG_P as IMG26_259_0_,
imginfodto2_.INNER_ID as INNER27_259_0_,
imginfodto2_.IS_NEED_IMPORTANT as IS28_259_0_,
imginfodto2_.IS_SCANTYPE_ADD as IS29_259_0_,
imginfodto2_.MAKEUP_DATE as MAKEUP30_259_0_,
imginfodto2_.MAKEUP_USER as MAKEUP31_259_0_,
imginfodto2_.MONITOR_RULE as MONITOR32_259_0_,
imginfodto2_.OPERATOR_NO as OPERATOR33_259_0_,
imginfodto2_.ORG_NO as ORG34_259_0_,
imginfodto2_.OUTO_CHECK as OUTO35_259_0_,
imginfodto2_.PS_LEVEL as PS36_259_0_,
imginfodto2_.SCAN_ADD_REMARK as SCAN37_259_0_,
imginfodto2_.SIGN as SIGN259_0_,
imginfodto2_.SLAVE_COUNT as SLAVE39_259_0_,
imginfodto2_.SP_FLAG as SP40_259_0_,
imginfodto2_.SUPERVISE_DATE as SUPERVISE41_259_0_,
imginfodto2_.SUPERVISE_STATUS as SUPERVISE42_259_0_,
imginfodto2_.SUPERVISES as SUPERVISES259_0_,
imginfodto2_.SUPERVISOR_CODE as SUPERVISOR44_259_0_,
imginfodto2_.SUPERVISOR_NAME as SUPERVISOR45_259_0_,
imginfodto2_.TRADE_DATE as TRADE46_259_0_,
imginfodto2_.Z_INNERID as Z47_259_0_
from
ocrpopr.T_FLOW_WATER this_,
ocrpopr.T_IMG_INFO imginfodto2_
where
this_.IMG_ID=imginfodto2_.ID(+)
and this_.TRAN_INST=756045
and this_.IS_COP=1
and this_.TRAN_DATE>=20200818
and (
this_.SCAN_BATCHNUM is not null
)
order by
this_.ID desc )
where
rownum <= 15;

# #et 报告如下：time

获取执行时间占比

执行时间占比，可以看到主要消耗时间在2个大表的全表扫描上，消耗了48.79% 和 47.25%的时间，全表扫描分别循环了82111 次和65161 次

1900w的记录要读出来是可能的哈每次可能取个bdta size的大小，这个库的bdta_size=300吧那算出来大概就是65141 和et中的enter次数相近，每次读取300

所以读取了82111 次

查找官方资料（https://eco.dameng.com/document/dm/zh-cn/ops/performance-optimization）知道

CSCN2 是 CLUSTER INDEX SCAN 的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。全表扫描 I/O 开销较大，在一个高并发的系统中应尽量避免全表扫描。

CSCN2 53328500 47.25% 2 15 82111 0 0

CSCN2 55074728 48.79% 1 10 65161 0 0

第二列是time (us，微妙), n-enter 列是循环了多少次的含义
DLCK 3 0% 16 0 2 0 0
PRJT2 6 0% 14 3 4 0 0
PRJT2 6 0% 14 2 4 0 0
NSET2 214 0% 13 1 3 0 0
PLL 446 0% 12 11 1992 0 0
BLKUP2 538 0% 11 12 1992 0 0
SSEK2 741 0% 10 13 996 0 0
ACTRL 10449 0.01% 9 7 36453 0 0
SORT3 11397 0.01% 8 4 1636 0 0
PLL 13654 0.01% 7 8 36464 0 0
IJLO2 16429 0.01% 6 6 38303 0 0
PLL 58976 0.05% 5 14 164210 0 0
SLCT2 1539699 1.36% 4 9 83399 0 0
HLO2 2814029 2.49% 3 5 102814 1 191088
CSCN2 53328500 47.25% 2 15 82111 0 0
CSCN2 55074728 48.79% 1 10 65161 0 0

SLCT：选择

--#PARALLEL：控制水平分区子表的扫描

##exp

lain
1 #NSET2: [15997, 195, 2365]
2 #PRJT2: [15997, 195, 2365]; exp_num(100), is_atom(FALSE)
3 #PRJT2: [15997, 195, 2365]; exp_num(100), is_atom(FALSE)
4 #SORT3: [15997, 195, 2365]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
5 #HASH LEFT JOIN2: [15982, 19591, 2365]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(exp_cast(THIS_.IMG_ID)=IMGINFODTO2_.ID)
6 #INDEX JOIN LEFT JOIN2: [15982, 19591, 2365] ret_null(0)
7 #ACTRL: [15982, 19591, 2365];
8 #PARALLEL: [14878, 19591, 2365]; scan_type(FULL), key_num(0, 0, 0), simple(0)

【解释： 搜索条件是 (NOT(THIS_.SCAN_BATCHNUM IS NULL) AND exp_cast(THIS_.TRAN_INST) = 756045 AND exp_cast(THIS_.IS_COP) = 1 AND exp_cast(THIS_.TRAN_DATE) >= 20200818)，返回了19591】
9 #SLCT2: [14878, 19591, 2365]; (NOT(THIS_.SCAN_BATCHNUM IS NULL) AND exp_cast(THIS_.TRAN_INST) = 756045 AND exp_cast(THIS_.IS_COP) = 1 AND exp_cast(THIS_.TRAN_DATE) >= 20200818)
10 #CSCN2: [14878, 19542548, 2365]; INDEX33560845(T_FLOW_WATER as THIS_)

【解释：例如：第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是14878，扫描的记录行数是 19542548 行，输出字节数是 2365个。】
11 #PARALLEL: [220, 1, 30]; scan_type(FULL), key_num(0, 0, 0), simple(0)
12 #BLKUP2: [220, 1, 30]; INDEX33561349(IMGINFODTO2_)
13 #SSEK2: [220, 1, 30]; scan_type(ASC), INDEX33561349(T_IMG_INFO as IMGINFODTO2_), scan_range[exp_cast(THIS_.IMG_ID),exp_cast(THIS_.IMG_ID)]
14 #PARALLEL: [15978, 24627876, 2041]; scan_type(FULL), key_num(0, 0, 0), simple(0)
15 #CSCN2: [15978, 24627876, 2041]; INDEX33560822(T_IMG_INFO as IMGINFODTO2_)

->例如：第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是15978，扫描的记录行数是 24627876行，输出字节数是 2365个。

##create tabel sql

方案1：

属性查看DDL

抓取sql ddl 如下：

CREATE TABLE "USEROPR"."T_FLOW_WATER"
(
"ID" NUMBER(30,0) NOT NULL,
"CLT_SEQNO" VARCHAR2(208),
"CHECK_NO" VARCHAR2(120),
"AUTH_OPT_NO" VARCHAR2(120),
"AUTH_OPT_NAME" VARCHAR2(120),
"TRAN_OPT_NO" VARCHAR2(120),
"TRAN_OPT_NAME" VARCHAR2(120),
"TRAN_INST" VARCHAR2(40),
"TRAN_DATE" VARCHAR2(32),
"TRAN_TIME" VARCHAR2(76),
"ACC_NO1" VARCHAR2(1500),
"ACC_NAME1" VARCHAR2(1024),
"CARD_NO1" VARCHAR2(200),
"ACC_NO2" VARCHAR2(300),
"ACC_NAME2" VARCHAR2(1024),
"CARD_NO2" VARCHAR2(200),
"CCY" VARCHAR2(12),
"AMT" NUMBER(18,2),
"DR_CR_FLAG" VARCHAR2(4),
"CSH_TSF_FLAG" VARCHAR2(4),
"TRAN_STAT" VARCHAR2(4),
"TRAN_TYPE" VARCHAR2(12),
"REMARKS" VARCHAR2(2048),
"CORE_SEQ" VARCHAR2(120),
"REMARKS1" VARCHAR2(2048),
"IMG_ID" VARCHAR2(200),
"SUPERVISE_STATUS" VARCHAR2(10),
"FIRST_ORGID" NUMBER(18,0),
"FIRST_ORGNUM" VARCHAR2(20),
"FIRST_ORGTYPE" NUMBER(18,0),
"FIRST_ORGTYPENAME" VARCHAR2(100),
"FIRST_ORGNAME" VARCHAR2(512),
"SECOND_ORGID" NUMBER(18,0),
"SECOND_ORGNUM" VARCHAR2(20),
"SECOND_ORGNAME" VARCHAR2(512),
"THIRD_ORGID" NUMBER(18,0),
"THIRD_ORGNUM" VARCHAR2(20),
"THIRD_ORGNAME" VARCHAR2(512),
"CURR_MONTH" VARCHAR2(2),
"SUPERVISOR_CODE" VARCHAR2(255),
"SUPERVISOR_NAME" VARCHAR2(255),
"SUPERVISE_DATE" TIMESTAMP(0),
"MONITOR_RULE" NUMBER(18,0),
"RELATION_STATUS" VARCHAR2(4),
"REVERSAL_FLAG" VARCHAR2(4),
"TRADE_END_TIME" TIMESTAMP(6),
"BIZ_INFO" CLOB,
"CUSTOM_REQ" VARCHAR2(32),
"MAIN_VOUCHER_CODE" VARCHAR2(20),
"IS_COP" VARCHAR2(2),
"ACCEPT_CHANNEL" VARCHAR2(10),
"EXTRA_RULE" NUMBER(18,0),
"SCAN_BATCHNUM" VARCHAR2(64),
"IS_REMOTE_AUTHORITY" VARCHAR2(10),
"TRADE_BREED_NAME" VARCHAR2(255),
"TRADE_SUB_NAME" VARCHAR2(255),
"HAND_DATE" VARCHAR2(8),
"IS_PROCESS" VARCHAR2(2),
CONSTRAINT "PK_T_FLOW_WATER" NOT CLUSTER PRIMARY KEY("ID"))
PARTITION BY LIST("CURR_MONTH")
(
PARTITION "T_FLOW_WATER_01" VALUES('01') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_02" VALUES('02') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_03" VALUES('03') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_04" VALUES('04') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_05" VALUES('05') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_06" VALUES('06') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_07" VALUES('07') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_08" VALUES('08') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_09" VALUES('09') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_10" VALUES('10') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_11" VALUES('11') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_12" VALUES('12') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_FLOW_WATER_OTHER" VALUES(DEFAULT) STORAGE(ON "USER_DATA", CLUSTERBTR)
) STORAGE(ON "USER_DATA", CLUSTERBTR) ENABLE ROW MOVEMENT;

CREATE INDEX "IDX_ACC_CARD" ON "USEROPR"."T_FLOW_WATER"("ACC_NO1" ASC,"CARD_NO1" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IDX_CUSTREQ" ON "USEROPR"."T_FLOW_WATER"("CUSTOM_REQ" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IDX_FLOW_WATER" ON "USEROPR"."T_FLOW_WATER"("CORE_SEQ" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IDX_FLOW_WATER_02" ON "USEROPR"."T_FLOW_WATER"("SCAN_BATCHNUM" ASC,"TRAN_INST" ASC,"IS_COP" ASC,"TRAN_DATE" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IDX_OPT_NO" ON "USEROPR"."T_FLOW_WATER"("TRAN_OPT_NO" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IX_DT" ON "USEROPR"."T_FLOW_WATER"("TRAN_DATE" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IDX_FOR_COP" ON "USEROPR"."T_FLOW_WATER"("TRAN_TYPE" ASC,"IS_COP" ASC,"SCAN_BATCHNUM" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IDX_TRANTYPE" ON "USEROPR"."T_FLOW_WATER"("REMARKS" ASC,"TRAN_TYPE" ASC,"SUPERVISE_STATUS" ASC,"SUPERVISOR_CODE" ASC,"SUPERVISOR_NAME" ASC,"SUPERVISE_DATE" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IX_FLOWER_WATER_01" ON "USEROPR"."T_FLOW_WATER"("TRAN_DATE" ASC,"TRAN_INST" ASC,"TRAN_TYPE" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;

CREATE TABLE "USEROPR"."T_IMG_INFO"
(
"ID" NUMBER(18,0) NOT NULL,
"TRADE_DATE" VARCHAR2(8),
"ORG_NO" VARCHAR2(25),
"FH_ORG" VARCHAR2(200),
"OPERATOR_NO" VARCHAR2(25),
"BATCH_NO" NUMBER(18,0),
"INNER_ID" NUMBER(18,0),
"IMG_P" VARCHAR2(250),
"IMG_N" VARCHAR2(250),
"CETIF_TYPE_NAME" VARCHAR2(100),
"CETIF_TYPE_ID" VARCHAR2(100),
"CLI_SERIAL_NO" VARCHAR2(100),
"ACCOUNT" VARCHAR2(1024),
"ACCOUNT_NAME" VARCHAR2(1024),
"AMT" VARCHAR2(100),
"ACCOUNT1" VARCHAR2(200),
"AMT1" VARCHAR2(100),
"ACCOUNT1_NAME" VARCHAR2(1024),
"CETIF_DATE" VARCHAR2(100),
"SIGN" VARCHAR2(100),
"PS_LEVEL" VARCHAR2(100),
"SLAVE_COUNT" NUMBER(18,0),
"Z_INNERID" NUMBER(18,0),
"COMPOSITE" VARCHAR2(250),
"IS_SCANTYPE_ADD" VARCHAR2(100),
"IS_NEED_IMPORTANT" VARCHAR2(1),
"SCAN_ADD_REMARK" VARCHAR2(250),
"OUTO_CHECK" VARCHAR2(10),
"SUPERVISE_STATUS" VARCHAR2(10),
"FLOW_WATER_ID" NUMBER(18,0),
"CETIF_NO" VARCHAR2(30),
"MAKEUP_USER" VARCHAR2(100),
"MAKEUP_DATE" VARCHAR2(10),
"SP_FLAG" VARCHAR2(100),
"SUPERVISES" CLOB,
"CURR_MONTH" VARCHAR2(2),
"SUPERVISOR_CODE" VARCHAR2(255),
"SUPERVISOR_NAME" VARCHAR2(255),
"SUPERVISE_DATE" TIMESTAMP(0),
"AMT_D" NUMBER(18,2),
"MONITOR_RULE" NUMBER(18,0),
"HAS_PROBLEMS" NUMBER(2,0),
"IMAGESTATION_PID" VARCHAR2(255),
"IMAGESTATION_NID" VARCHAR2(255),
"IMAGE_CHANNEL" VARCHAR2(10),
"VOUCHER_CODE" VARCHAR2(18),
"EXTRA_RULE" NUMBER(18,0),
"FLOW_TRAN_NOTE" VARCHAR2(200),
CONSTRAINT "PK_T_IMG_INFO" NOT CLUSTER PRIMARY KEY("ID"))
PARTITION BY LIST("CURR_MONTH")
(
PARTITION "T_IMG_INFO_01" VALUES('01') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_02" VALUES('02') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_03" VALUES('03') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_04" VALUES('04') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_05" VALUES('05') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_06" VALUES('06') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_07" VALUES('07') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_08" VALUES('08') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_09" VALUES('09') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_10" VALUES('10') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_11" VALUES('11') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_12" VALUES('12') STORAGE(ON "USER_DATA", CLUSTERBTR) ,
PARTITION "T_IMG_INFO_OTHER" VALUES(DEFAULT) STORAGE(ON "USER_DATA", CLUSTERBTR)
) STORAGE(ON "USER_DATA", CLUSTERBTR) ENABLE ROW MOVEMENT;

index_name,index cloumn

CREATE INDEX "IDX_IMG" ON "USEROPR"."T_IMG_INFO"("TRADE_DATE" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IX_IMG_INFO_02" ON "USEROPR"."T_IMG_INFO"("BATCH_NO" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IDX_IMG_COMB" ON "USEROPR"."T_IMG_INFO"("SUPERVISE_DATE" ASC,"SUPERVISOR_CODE" ASC,"CETIF_TYPE_ID" ASC,"SUPERVISE_STATUS" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR) ;
CREATE INDEX "IX_IMG_INFO_01" ON "USEROPR"."T_IMG_INFO"("TRADE_DATE" ASC,"OPERATOR_NO" ASC) GLOBAL STORAGE(ON "USER_DATA", CLUSTERBTR);

方案2：直接用sql 统计表和索引列的信息，sql 如下：

### 查看对应表的统计信息 table statics
select * from (
select o.owner,o.object_name,b.column_name,b.column_id,b.data_type,s.t_total,s.n_smaple,s.n_distinct,s.n_null,s.n_buckets,b.histogram,s.last_gathered
from sysstats s
inner join dba_objects o on s.id=o.object_id
inner join dba_tab_columns b on o.owner=b.owner and o.object_name=b.table_name and s.colid+1=b.column_id
where o.owner=upper('USERopr')
and o.object_name=upper('T_FLOW_WATER')
and o.object_type='TABLE')
order by b.column_id;

select * from (
select o.owner,o.object_name,b.column_name,b.column_id,b.data_type,s.t_total,s.n_smaple,s.n_distinct,s.n_null,s.n_buckets,b.histogram,s.last_gathered
from sysstats s
inner join dba_objects o on s.id=o.object_id
inner join dba_tab_columns b on o.owner=b.owner and o.object_name=b.table_name and s.colid+1=b.column_id
where o.owner=upper('USERopr')
and o.object_name=upper('T_IMG_INFO')
and o.object_type='TABLE')
order by b.column_id;

---index info: 对应索引的信息
SELECT TABLE_OWNER as "模式名",
TABLE_NAME as "表名",
COLUMN_NAME as "列名",
INDEX_NAME as "索引名"
from DBA_IND_COLUMNS
WHERE TABLE_OWNER=upper('USERopr')
AND TABLE_NAME=upper('T_IMG_INFO');

USEROPR T_IMG_INFO ID INDEX33561349
USEROPR T_IMG_INFO TRADE_DATE IDX_IMG
USEROPR T_IMG_INFO BATCH_NO IX_IMG_INFO_02
USEROPR T_IMG_INFO SUPERVISE_DATE IDX_IMG_COMB
USEROPR T_IMG_INFO SUPERVISOR_CODE IDX_IMG_COMB
USEROPR T_IMG_INFO SUPERVISE_STATUS IDX_IMG_COMB
USEROPR T_IMG_INFO CETIF_TYPE_ID IDX_IMG_COMB
USEROPR T_IMG_INFO OPERATOR_NO IX_IMG_INFO_01
USEROPR T_IMG_INFO TRADE_DATE IX_IMG_INFO_01

SELECT TABLE_OWNER as "模式名",
TABLE_NAME as "表名",
COLUMN_NAME as "列名",
INDEX_NAME as "索引名"
from DBA_IND_COLUMNS

WHERE TABLE_OWNER=upper('USERopr')
AND TABLE_NAME=upper('T_FLOW_WATER');

USEROPR T_FLOW_WATER ID INDEX33561372
USEROPR T_FLOW_WATER CARD_NO1 IDX_ACC_CARD
USEROPR T_FLOW_WATER ACC_NO1 IDX_ACC_CARD
USEROPR T_FLOW_WATER CUSTOM_REQ IDX_CUSTREQ
USEROPR T_FLOW_WATER CORE_SEQ IDX_FLOW_WATER
USEROPR T_FLOW_WATER SCAN_BATCHNUM IDX_FLOW_WATER_02
USEROPR T_FLOW_WATER IS_COP IDX_FLOW_WATER_02
USEROPR T_FLOW_WATER TRAN_DATE IDX_FLOW_WATER_02
USEROPR T_FLOW_WATER TRAN_INST IDX_FLOW_WATER_02 《——已经创建了索引，但是没用到
USEROPR T_FLOW_WATER TRAN_OPT_NO IDX_OPT_NO
USEROPR T_FLOW_WATER TRAN_DATE IX_DT
USEROPR T_FLOW_WATER SCAN_BATCHNUM IDX_FOR_COP
USEROPR T_FLOW_WATER IS_COP IDX_FOR_COP
USEROPR T_FLOW_WATER TRAN_TYPE IDX_FOR_COP
USEROPR T_FLOW_WATER SUPERVISE_DATE IDX_TRANTYPE
USEROPR T_FLOW_WATER SUPERVISOR_NAME IDX_TRANTYPE
USEROPR T_FLOW_WATER SUPERVISOR_CODE IDX_TRANTYPE
USEROPR T_FLOW_WATER SUPERVISE_STATUS IDX_TRANTYPE
USEROPR T_FLOW_WATER REMARKS IDX_TRANTYPE
USEROPR T_FLOW_WATER TRAN_TYPE IDX_TRANTYPE
USEROPR T_FLOW_WATER TRAN_TYPE IX_FLOWER_WATER_01
USEROPR T_FLOW_WATER TRAN_DATE IX_FLOWER_WATER_01
USEROPR T_FLOW_WATER TRAN_INST IX_FLOWER_WATER_01

从以上信息，我们看到主要查找列都建立了索引，但是索引没用到，使用到全表索引，导致性能慢的问题。

--1. 怀疑是数据隐身转换问题，导致数据库没有使用到索引IDX_FLOW_WATER_02,加入引号，从46 变成9秒
修改数字成'数字'，"TRAN_INST" VARCHAR2(40),"IS_COP" VARCHAR2(2),"TRAN_DATE" VARCHAR2(32),

from
and this_.TRAN_INST=756045
and this_.IS_COP=1
and this_.TRAN_DATE>=20200818

to
and this_.TRAN_INST='756045'
and this_.IS_COP=1
and this_.TRAN_DATE>='20200818'

---执行计划变成全表扫描到索引扫描
IDX_INST

1 #NSET2: [17265, 730, 2365]
2 #PRJT2: [17265, 730, 2365]; exp_num(100), is_atom(FALSE)
3 #PRJT2: [17265, 730, 2365]; exp_num(100), is_atom(FALSE)
4 #SORT3: [17265, 730, 2365]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
5 #INDEX JOIN LEFT JOIN2: [12047, 73022, 2365] ret_null(0)
6 #PARALLEL: [3032, 73022, 2365]; scan_type(FULL), key_num(0, 0, 0), simple(0)
7 #SLCT2: [3032, 73022, 2365]; (NOT(THIS_.SCAN_BATCHNUM IS NULL) AND THIS_.TRAN_DATE >= '20200818' AND exp_cast(THIS_.IS_COP) = 1)
8 #BLKUP2: [3032, 657078, 2365]; IDX_INST(THIS_)
9 #SSEK2: [3032, 657078, 2365]; scan_type(ASC), IDX_INST(T_FLOW_WATER as THIS_), scan_range['756045','756045']
10 #PARALLEL: [823, 1, 30]; scan_type(FULL), key_num(0, 0, 0), simple(0)
11 #BLKUP2: [823, 1, 30]; INDEX33561349(IMGINFODTO2_)
12 #SSEK2: [823, 1, 30]; scan_type(ASC), INDEX33561349(T_IMG_INFO as IMGINFODTO2_), scan_range[exp_cast(THIS_.IMG_ID),exp_cast(THIS_.IMG_ID)]

2.组合索引里面字段的顺序也会影响数据库组合索引是否能够使用，等值= 过滤性最强的应该放在最前面

修改前：
CREATE INDEX "IDX_FLOW_WATER_02" ON "USEROPR"."T_FLOW_WATER"("SCAN_BATCHNUM" ASC,"TRAN_INST" ASC,"IS_COP" ASC,"TRAN_DATE" ASC)

修改后：索引是等值TRAN_INST= 这个条件，将其放在组合索引的最前面
CREATE INDEX "IDX_FLOW_WATER_02" ON "USEROPR"."T_FLOW_WATER"("TRAN_INST" ASC,"IS_COP" ASC,"SCAN_BATCHNUM" ASC,"TRAN_DATE" ASC)

1 #NSET2: [15522, 730, 2365]
2 #PRJT2: [15522, 730, 2365]; exp_num(100), is_atom(FALSE)
3 #PRJT2: [15522, 730, 2365]; exp_num(100), is_atom(FALSE)
4 #SORT3: [15522, 730, 2365]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
5 #INDEX JOIN LEFT JOIN2: [10305, 73022, 2365] ret_null(0)
6 #PARALLEL: [1289, 73022, 2365]; scan_type(FULL), key_num(0, 0, 0), simple(0)
7 #SLCT2: [1289, 73022, 2365]; (NOT(THIS_.SCAN_BATCHNUM IS NULL) AND THIS_.TRAN_DATE >= '20200818')
8 #BLKUP2: [1289, 277357, 2365]; IDX_FLOW_WATER_02(THIS_)
9 #SSEK2: [1289, 277357, 2365]; scan_type(ASC), IDX_FLOW_WATER_02(T_FLOW_WATER as THIS_), scan_range[('756045','1',min,min),('756045','1',max,max))
10 #PARALLEL: [823, 1, 30]; scan_type(FULL), key_num(0, 0, 0), simple(0)
11 #BLKUP2: [823, 1, 30]; INDEX33561349(IMGINFODTO2_)
12 #SSEK2: [823, 1, 30]; scan_type(ASC), INDEX33561349(T_IMG_INFO as IMGINFODTO2_), scan_range[exp_cast(THIS_.IMG_ID),exp_cast(THIS_.IMG_ID)]

第8行的操作执行了2284次。消耗了3秒，

DLCK 3 0% 12 0 2 0 0
PRJT2 3 0% 12 2 4 0 0
PRJT2 7 0% 11 3 4 0 0
NSET2 182 0% 10 1 3 0 0
PLL 569 0.01% 9 6 1556 0 0
SLCT2 17902 0.37% 8 7 1920 0 0
SSEK2 29827 0.62% 7 9 1142 0 0
PLL 77795 1.61% 6 10 384170 0 0
BLKUP2 94297 1.96% 5 11 384170 0 0
SORT3 115401 2.4% 4 4 192088 0 0
SSEK2 167493 3.48% 3 12 192085 0 0
IJLO2 346316 7.19% 2 5 384949 0 0
BLKUP2 3967810 82.36% 1 8 2284 0 0

3.sql 加入hint ,/*+ enable_index_filter(1) */, 可以将sql 从4秒提升到3秒
select /*+ enable_index_filter(1) */
*
from
( select
this_.ID as ID278_1_,
this_.ACC_NAME1 as ACC2_278_1_,

scan_range[('756045','1',min,min),('756045','1',max,max)) -》 756045 代表字段TRAN_INST的值，1代表IS_COP的值

1 #NSET2: [15520, 730, 2365]
2 #PRJT2: [15520, 730, 2365]; exp_num(100), is_atom(FALSE)
3 #PRJT2: [15520, 730, 2365]; exp_num(100), is_atom(FALSE)
4 #SORT3: [15520, 730, 2365]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
5 #INDEX JOIN LEFT JOIN2: [10302, 73022, 2365] ret_null(0)
6 #PARALLEL: [1287, 73022, 2365]; scan_type(FULL), key_num(0, 0, 0), simple(0)
7 #BLKUP2: [1287, 73022, 2365]; IDX_FLOW_WATER_02(THIS_)
8 #SLCT2: [1287, 73022, 2365]; (NOT(THIS_.SCAN_BATCHNUM IS NULL) AND THIS_.TRAN_DATE >= '20200818')
9 #SSEK2: [1287, 277357, 2365]; scan_type(ASC), IDX_FLOW_WATER_02(T_FLOW_WATER as THIS_), scan_range[('756045','1',min,min),('756045','1',max,max))
10 #PARALLEL: [823, 1, 30]; scan_type(FULL), key_num(0, 0, 0), simple(0)
11 #BLKUP2: [823, 1, 30]; INDEX33561349(IMGINFODTO2_)
12 #SSEK2: [823, 1, 30]; scan_type(ASC), INDEX33561349(T_IMG_INFO as IMGINFODTO2_), scan_range[exp_cast(THIS_.IMG_ID),exp_cast(THIS_.IMG_ID)]

MONITOR_SQL_EXEC

#########sample 3.1.1 子查询执行计划选择不好，导致hash 查询占用了100G临时表空间，进而导致SQL 性能很慢 20240914

一、问题现象：

执行存储过程异常消耗临时表空间（临时表空间大小上限100G）

二、一般原因：

临时表空间暴增一般和内存过小，sql排序过多，临时结果集过大有关

三、排查步骤：

2024-09-13

存储过程：SP_IN_ACCUMULATION_D、SP_SY_SYS_TABLEOUT_WAR

①　查看内存参数设置

查看buffer=1000m

调整到10G , 该问题得到改善

②　更新统计信息

手工收集统计信息，这样统计信息就是准的

③　查看temp使用率

SELECT BYTES/1024/1024/1024,* FROM DBA_DATA_FILES WHERE DBA_DATA_FILES.TABLESPACE_NAME='TEMP';

稳定在40g左右

④　查找执行慢的存储过程，无改善

⑤　排查存储过程。开发提出慢sql 如下：

待优化sql

SELECT h.clt_id, --业务id

a.sys_src_cd, --系统来源

(SELECT c.type_nm FROM ST_GUR_BUSINESS_TYPE c

WHERE c.type_no = a.bsn_tp_cd

AND c.sys_src_cd = h.sys_src_cd

AND c.is_inuse <> '2') type_nm, --20150311/zoulq/ 改为子查询

-- c.type_nm, --业务品种名称

a.post_org_no, --入账机构

a.post_org_nm, --入账机构名称

a.opr_org_no, --经办机构

fn_get_org_name(a.sys_src_cd,a.opr_org_no), --经办机构名称

a.opr_user_no, --经办人

e.user_nm, --经办人名称

h.clt_no, --业务编号

h.clt_nm, --业务名称

h.clt_cgy_cd, --抵质押类别

h.clt_tp_cd, --业务种类

f.catalog_nm, --业务种类名称

f.reeval_prd, --重估周期

f.reeval_prd_unt_cd, --重估周期时间单位

h.CONF_AMT, --我行确认价值

h.CONF_DT, --价值确认日期

h.owner_no, --抵质押人编号

h.owner_nm, --抵质押人名称

a.project_no --合作商项目编号

FROM AP_WAR_CTR_GUAR_REL1_TMP a, --有效合同信息临时表

ST_GUR_GUARANTY_RELATIVE g, --担保合同与抵质押物关联信息表

BS_COL_COLLBASEINFO h, --业务基本信息

AP_SYS_PARTY b, --客户表

-- ST_GUR_BUSINESS_TYPE c, --资产业务品种表 --20150311/zoulq/删除关联，改成子查询

AP_SYS_USER e, --用户信息表

BS_COL_COLLATERAL_CATALOG f --业务种类表

WHERE a.guar_ctr_no = g.guar_ctr_no

AND g.clt_no = h.clt_no

--AND h.sys_src_cd = i_src_system --系统来源，4001零售 4002对公

AND g.eff_flg = '1' --0无效 1有效 --20150104/胡汉辉/v1.02 新增‘【担保合同与抵质押物关联信息表】.是否有效’的判断

AND a.cst_no = b.customer_num(+)

-- AND a.bsn_tp_cd = c.type_no(+) --20150311/zoulq/删除关联，改成子查询

--20150122/胡汉辉/v1.03 删除条件：a.post_org_no = d.ORG_NO(+)

and h.clt_info_sts_cd in ('01','02','03') --01新建中 02已生效 03已设押

AND a.opr_user_no = e.login_id(+)

AND h.clt_tp_cd = f.catalog_cd(+);

--2、将查询有效合同与业务关系详细信息插入临时表2

--- INSERT INTO AP_WAR_CTR_GUAR_REL2_TMP

SELECT h.clt_id, --业务id

a.sys_src_cd, --系统来源

a.ctr_no, --借款合同编号

a.guar_ctr_no, --担保合同编号

a.cst_no, --借款人编号

b.party_name, --借款人名称

a.ctr_amt, --借款合同金额

a.ctr_bal, --借款合同余额

a.ctr_ccy_cd, --合同币种

a.ctr_eff_dt, --合同生效日期

a.ctr_mat_dt, --合同到期日期

a.loan_issue_dt, --贷款首次发放日期

a.ctr_sts_cd, --合同状态

a.bsn_tp_cd, --业务品种

(SELECT c.type_nm FROM ST_GUR_BUSINESS_TYPE c

WHERE c.type_no = a.bsn_tp_cd

AND c.sys_src_cd = h.sys_src_cd

AND c.is_inuse <> '2') type_nm, --20150311/zoulq/ 改为子查询

-- c.type_nm, --业务品种名称

a.post_org_no, --入账机构

a.post_org_nm, --入账机构名称

a.opr_org_no, --经办机构

fn_get_org_name(a.sys_src_cd,a.opr_org_no), --经办机构名称

a.opr_user_no, --经办人

e.user_nm, --经办人名称

h.clt_no, --业务编号

h.clt_nm, --业务名称

h.clt_cgy_cd, --抵质押类别

h.clt_tp_cd, --业务种类

f.catalog_nm, --业务种类名称

f.reeval_prd, --重估周期

f.reeval_prd_unt_cd, --重估周期时间单位

h.CONF_AMT, --我行确认价值

h.CONF_DT, --价值确认日期

h.owner_no, --抵质押人编号

h.owner_nm, --抵质押人名称

a.project_no --合作商项目编号

FROM AP_WAR_CTR_GUAR_REL1_TMP a, --有效合同信息临时表

ST_GUR_GUARANTY_RELATIVE g, --担保合同与抵质押物关联信息表

BS_COL_COLLBASEINFO h, --业务基本信息

AP_SYS_PARTY b, --客户表

-- ST_GUR_BUSINESS_TYPE c, --资产业务品种表 --20150311/zoulq/删除关联，改成子查询

AP_SYS_USER e, --用户信息表

BS_COL_COLLATERAL_CATALOG f --业务种类表

WHERE a.guar_ctr_no = g.guar_ctr_no

AND g.clt_no = h.clt_no

--AND h.sys_src_cd = i_src_system --系统来源，4001零售 4002对公

AND g.eff_flg = '1' --0无效 1有效 --20150104/胡汉辉/v1.02 新增‘【担保合同与抵质押物关联信息表】.是否有效’的判断

AND a.cst_no = b.customer_num(+)

-- AND a.bsn_tp_cd = c.type_no(+) --20150311/zoulq/删除关联，改成子查询

--20150122/胡汉辉/v1.03 删除条件：a.post_org_no = d.ORG_NO(+)

and h.clt_info_sts_cd in ('01','02','03') --01新建中 02已生效 03已设押

AND a.opr_user_no = e.login_id(+)

AND h.clt_tp_cd = f.catalog_cd(+);

执行计划如下：

5张表关联：

1 #NSET2: [893, 39572, 1310]

2 #PIPE2: [893, 39572, 1310]

3 #PRJT2: [522, 39572, 1310]; exp_num(33), is_atom(FALSE)

4 #HASH RIGHT JOIN2: [522, 39572, 1310]; key_num(1), ret_null(0), KEY(E.LOGIN_ID=A.OPR_USER_NO)

5 #CSCN2: [1, 3596, 96]; INDEX33555530(AP_SYS_USER as E)

6 #HASH LEFT JOIN2: [512, 39572, 1310]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.CST_NO=B.CUSTOMER_NUM)

7 #HASH2 INNER JOIN: [376, 39565, 1310]; KEY_NUM(1); KEY(G.GUAR_CTR_NO=A.GUAR_CTR_NO) KEY_NULL_EQU(0)

8 #HASH RIGHT JOIN2: [333, 39567, 627]; key_num(1), ret_null(0), KEY(F.CATALOG_CD=H.CLT_TP_CD)

9 #CSCN2: [1, 108, 174]; INDEX33555680(BS_COL_COLLATERAL_CATALOG as F)

10 #HASH RIGHT SEMI JOIN2: [326, 39567, 627]; n_keys(1) KEY(DMTEMPVIEW_16892782.colname=H.CLT_INFO_STS_CD) KEY_NULL_EQU(0)

11 #CONST VALUE LIST: [1, 3, 48]; row_num(3), col_num(1),

12 #HASH2 INNER JOIN: [326, 39567, 627]; KEY_NUM(1); KEY(H.CLT_NO=G.CLT_NO) KEY_NULL_EQU(0)

13 #CSCN2: [102, 501764, 483]; IDX_SYS_SRC_CD_H(BS_COL_COLLBASEINFO as H)

14 #SLCT2: [76, 516986, 144]; G.EFF_FLG = '1'

15 #CSCN2: [76, 534037, 144]; INDEX33556687(ST_GUR_GUARANTY_RELATIVE as G)

16 #CSCN2: [19, 78840, 683]; INDEX33555577(AP_WAR_CTR_GUAR_REL1_TMP as A)

17 #CSCN2: [72, 584274, 96]; INDEX33555523(AP_SYS_PARTY as B)

18 #SPL2: [370, 77521795200, 1406]; key_num(3), spool_num(0), is_atom(FALSE), has_var(0), sites(-)

19 #PRJT2: [370, 77521795200, 1406]; exp_num(3), is_atom(FALSE)

20 #HASH2 INNER JOIN: [370, 77521795200, 1406]; KEY_NUM(1); KEY(C.SYS_SRC_CD=H.SYS_SRC_CD) KEY_NULL_EQU(0)

21 #HASH2 INNER JOIN: [32, 315360, 875]; KEY_NUM(1); KEY(C.TYPE_NO=A.BSN_TP_CD) KEY_NULL_EQU(0)

22 #SLCT2: [1, 237, 192]; C.IS_INUSE <> '2'

23 #CSCN2: [1, 387, 192]; IDX_SYS_SRC_CD(ST_GUR_BUSINESS_TYPE as C)

24 #CSCN2: [19, 78840, 683]; INDEX33555577(AP_WAR_CTR_GUAR_REL1_TMP as A)

25 #HASH2 INNER JOIN: [165, 491658, 531]; KEY_NUM(1); KEY(DMTEMPVIEW_16892781.colname=H.CLT_INFO_STS_CD) KEY_NULL_EQU(0)

26 #CONST VALUE LIST: [1, 3, 48]; row_num(3), col_num(1),

27 #CSCN2: [102, 501764, 483]; IDX_SYS_SRC_CD_H(BS_COL_COLLBASEINFO as H)

C和A表关联，然后与H表关联，都是全表扫描，这些关联消耗了47%的时间使用率。

(SELECT c.type_nm FROM ST_GUR_BUSINESS_TYPE c

WHERE c.type_no = a.bsn_tp_cd

AND c.sys_src_cd = h.sys_src_cd

AND c.is_inuse <> '2') type_nm, --20150311/zoulq/ 改为子查询

部分执行计划：此返回结果集（C和A表关联，然后与H表）大概吃了77521795200行数据

#SPL2: [370, 77521795200, 1406]; key_num(3), spool_num(0), is_atom(FALSE), has_var(0), sites(-)

19 #PRJT2: [370, 77521795200, 1406]; exp_num(3), is_atom(FALSE)

20 #HASH2 INNER JOIN: [370, 77521795200, 1406]; KEY_NUM(1); KEY(C.SYS_SRC_CD=H.SYS_SRC_CD) KEY_NULL_EQU(0)

21 #HASH2 INNER JOIN: [32, 315360, 875]; KEY_NUM(1); KEY(C.TYPE_NO=A.BSN_TP_CD) KEY_NULL_EQU(0)

22 #SLCT2: [1, 237, 192]; C.IS_INUSE <> '2'

⑥　优化方式：

发现点1：执行20分钟无结果

看执行慢在#HASH2 INNER JOIN: [370, 77521795200, 1406]; KEY_NUM(1); KEY(C.SYS_SRC_CD=H.SYS_SRC_CD) KEY_NULL_EQU(0)。

结果集返回77521795200异常

select count(distinct(clt_no)) from ST_GUR_GUARANTY_RELATIVE g;

499323

select count(distinct(clt_no)) from BS_COL_COLLBASEINFO h;

491697

发现点2：

将C.SYS_SRC_CD=H.SYS_SRC_CD条件去除发现执行很快，因此定位此条件，慢在这个关联条件是，c表和h表hash inner join慢。

去除的执行计划为如下。1. C和A 表关联：

H和G表关联，g.clt_no= h.clt_no

过滤性更好

select count(distinct(clt_no)) from ST_GUR_GUARANTY_RELATIVE g;

499323

select count(distinct(clt_no)) from BS_COL_COLLBASEINFO h;

491697

尝试1：

尝试加hint ENABLE_RQ_TO_NONREF_SPL(3)处理

对查询项包含子查询进行优化处理, 对查询项where表达式中出现相关子查进行优化处理。

ENABLE_RQ_TO_NONREF_SPL参数

以行级处理方式实现相关子查询的平坦化，于普通的子查询平坦化机制不同，不需要生成SPL的临时结果集。将相关列转为变量var。实际上减少结果集返回。

(参数说明：1：对查询项中出现的相关子查询表达式进行优化处理；2：对查询项和 WHERE 表达式中出现的相关子查询表达式进行优化处理；4：相关查询采用 SPL 方式去相关性后，可以作为单表过滤条件。支持使用上述有效值的组合值，如 3 表示同时进行 1 和 2 的优化。)

优化后语句执行5s

⑦　优化结果：

部分执行计划：

19 #SPL2: [1, 1, 192]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)

20 #PRJT2: [1, 1, 192]; exp_num(1), is_atom(TRUE)

21 #SLCT2: [1, 1, 192]; (C.IS_INUSE <> '2' AND C.TYPE_NO = var2)

22 #CSEK2: [1, 193, 192]; scan_type(ASC), IDX_SYS_SRC_CD(ST_GUR_BUSINESS_TYPE as C), scan_range[var3,var3]

2024-09-11

存储过程SP_SA_CTR_COLL_INFO中的Sql2

--------查询相关表记录行

select 'AP_WAR_CTR_GUAR_REL1_TMP',count(*) from AP_WAR_CTR_GUAR_REL1_TMP

union

select 'ST_GUR_GUARANTY_RELATIVE',count(*) from ST_GUR_GUARANTY_RELATIVE --担保合同与抵质押物关联信息表

union

select 'BS_COL_COLLBASEINFO',count(*) from BS_COL_COLLBASEINFO --业务基本信息

union

select 'AP_SYS_PARTY',count(*) from AP_SYS_PARTY --客户表

union

select 'AP_SYS_USER',count(*) from AP_SYS_USER --用户信息表

union

select 'BS_COL_COLLATERAL_CATALOG',count(*) from BS_COL_COLLATERAL_CATALOG;

----6个表的统计数据行数

AP_WAR_CTR_GUAR_REL1_TMP 78840

ST_GUR_GUARANTY_RELATIVE 534037

BS_COL_COLLBASEINFO 501764

AP_SYS_PARTY 584274

AP_SYS_USER 3596

BS_COL_COLLATERAL_CATALOG 108

存储过程中的查询sql提取出来单独执行,sql2不能直接在客户端上执行，但是从查询的表分析，存在多表连接查询的，且其中有3个50w行数据的表。存在查询结果集较大的问题。目前建议调整表之间的查询连接方式，规避结果集庞大

##############sample 3.1.4 connect by 无 prior优化

WITH ms_codes AS ( SELECT DISTINCT a.as_code, a.app_name, a.app_code, a.as_name, a.unit_name, a.unit_code, REGEXP_SUBSTR(a.codes, '[^,]+', 1, LEVEL) AS ms_code FROM ( SELECT a.app_name, a.app_code, a.as_name, a.as_code, a.unit_name, a.unit_code, a.as_his_no, REPLACE(REPLACE(REPLACE(a.codes, '[', ''), ']', ''), '"', '') AS codes FROM ( SELECT c.app_name, c.app_code, a.as_name, a.as_code, b.unit_code, b.unit_name, d.as_his_no, JSON_QUERY(f.AS_COORDINATE, '$.nodeDataArray.info.code' WITH WRAPPER) AS codes FROM t_flow_app_service_info a LEFT JOIN t_flow_unit_conf b ON a.unit_no = b.unit_no LEFT JOIN t_flow_app_conf c ON a.app_no = c.app_no LEFT JOIN t_flow_as_version_list d ON a.as_no = d.as_no LEFT JOIN T_FLOW_AS_CHOREOGRAPHY_INFO f ON d.as_his_no = f.as_his_no WHERE c.sys_version_no = '1686552742190_a124b8688afa4dd3b3d784b989bf3ad3' ORDER BY c.app_code, a.as_code ) a ) a CONNECT BY LEVEL <= LENGTH(a.codes) - LENGTH(REGEXP_REPLACE(a.codes, ',', '')) + 1 ) SELECT a.app_name, a.app_code, a.as_name, a.as_code, a.unit_name, a.unit_code, b.ms_name, b.ms_code FROM ms_codes a LEFT JOIN t_flow_microservice_info b ON a.ms_code = b.ms_code LEFT JOIN t_flow_module_conf c ON b.module_no = c.module_no WHERE c.sys_version_no = '1686552742190_a124b8688afa4dd3b3d784b989bf3ad3' ORDER BY a.app_code, a.as_code, a.unit_code, a.ms_code;

下面是设计上面sql语句每个表的数据总量：t_flow_app_service_info 2213 t_flow_unit_conf 115 t_flow_app_conf 20 t_flow_as_version_list 2611 T_FLOW_AS_CHOREOGRAPHY_INFO 17312 t_flow_microservice_info 4847 t_flow_module_conf 55

-》分析原因：

WITH ms_codes AS (SELECT DISTINCT a.as_code, a.app_name, a.app_code, a.as_name, a.unit_name, a.unit_code, REGEXP_SUBSTR(a.codes, '[^,]+', 1, LEVEL) AS ms_code FROM (SELECT a.app_name, a.app_code, a.as_name, a.as_code, a.unit_name, a.unit_code, a.as_his_no, REPLACE(REPLACE(REPLACE(a.codes, '[', ''), ']', ''), '"', '') AS codes FROM (SELECT c.app_name, c.app_code, a.as_name, a.as_code, b.unit_code, b.unit_name, d.as_his_no, JSON_QUERY(f.AS_COORDINATE, '$.nodeDataArray.info.code' WITH WRAPPER) AS codes FROM t_flow_app_service_info a LEFT JOIN t_flow_unit_conf b ON a.unit_no = b.unit_no LEFT JOIN t_flow_app_conf c ON a.app_no = c.app_no LEFT JOIN t_flow_as_version_list d ON a.as_no = d.as_no LEFT JOIN T_FLOW_AS_CHOREOGRAPHY_INFO f ON d.as_his_no = f.as_his_no WHERE c.sys_version_no = '1686552742190_a124b8688afa4dd3b3d784b989bf3ad3' ORDER BY c.app_code, a.as_code) a) a CONNECT BY LEVEL <= LENGTH(a.codes) - LENGTH(REGEXP_REPLACE(a.codes, ',', '')) + 1) SELECT a.app_name, a.app_code, a.as_name, a.as_code, a.unit_name, a.unit_code, b.ms_name, b.ms_code FROM ms_codes a LEFT JOIN t_flow_microservice_info b ON a.ms_code = b.ms_code LEFT JOIN t_flow_module_conf c ON b.module_no = c.module_no WHERE c.sys_version_no = '1686552742190_a124b8688afa4dd3b3d784b989bf3ad3' ORDER BY a.app_code, a.as_code, a.unit_code, a.ms_code;

-》发现慢在with

SELECT DISTINCT a.as_code, a.app_name, a.app_code, a.as_name, a.unit_name, a.unit_code, REGEXP_SUBSTR(a.codes, '[^,]+', 1, LEVEL) AS ms_code FROM ( SELECT a.app_name, a.app_code, a.as_name, a.as_code, a.unit_name, a.unit_code, a.as_his_no, REPLACE(REPLACE(REPLACE(a.codes, '[', ''), ']', ''), '"', '') AS codes FROM ( SELECT c.app_name, c.app_code, a.as_name, a.as_code, b.unit_code, b.unit_name, d.as_his_no, JSON_QUERY(f.AS_COORDINATE, '$.nodeDataArray.info.code' WITH WRAPPER) AS codes FROM t_flow_app_service_info a LEFT JOIN t_flow_unit_conf b ON a.unit_no = b.unit_no LEFT JOIN t_flow_app_conf c ON a.app_no = c.app_no LEFT JOIN t_flow_as_version_list d ON a.as_no = d.as_no LEFT JOIN T_FLOW_AS_CHOREOGRAPHY_INFO f ON d.as_his_no = f.as_his_no WHERE c.sys_version_no = '1686552742190_a124b8688afa4dd3b3d784b989bf3ad3' ORDER BY c.app_code, a.as_code ) a ) a CONNECT BY LEVEL <= LENGTH(a.codes) - LENGTH(REGEXP_REPLACE(a.codes, ',', '')) + 1

这是一段多表关联的 CONNECT BY 分成查询

-》该条查询是做如下处理：

这个查询的结果是将tableb表中的codes字段中的每个逗号分隔的值作为单独的行返回，同时携带其他列的数据。每行对应codes字段中的一个值，直到所有值都被处理完毕。

优化思路1：发现慢sql 那一段去掉distinct 就会非常快。2秒之内出结果。最后证明即使2秒出结果，实际只是返回结果集刚开始的行数非常快，因为假定1000万结果，但是

出前面100行，也是非常快

执行计划，先从下面线 heaptable 那一行扫描，然后结果集合会合到上面一行线的2个 heap tablescan，在进行hirearchical query,

实际发现即便统计信息是正确的，这里预估的执行计划的行数30945 仍然是不正确的，

进一步测试发现： --步骤1 ：

创建一个临时表，非常快 create table tmp as select * from ( SELECT a.app_name, a.app_code, a.as_name, a.as_code, a.unit_name, a.unit_code, a.as_his_no, REPLACE(REPLACE(REPLACE(a.codes, '[', ''), ']', ''), '"', '') AS codes FROM ( SELECT c.app_name, c.app_code, a.as_name, a.as_code, b.unit_code, b.unit_name, d.as_his_no, JSON_QUERY(f.AS_COORDINATE, '$.nodeDataArray.info.code' WITH WRAPPER) AS codes FROM t_flow_app_service_info a LEFT JOIN t_flow_unit_conf b ON a.unit_no = b.unit_no LEFT JOIN t_flow_app_conf c ON a.app_no = c.app_no LEFT JOIN t_flow_as_version_list d ON a.as_no = d.as_no LEFT JOIN T_FLOW_AS_CHOREOGRAPHY_INFO f ON d.as_his_no = f.as_his_no WHERE c.sys_version_no = '1686552742190_a124b8688afa4dd3b3d784b989bf3ad3' ORDER BY c.app_code, a.as_code ) a)

--步骤2 问题点：再次创建一个临时表，select 很快因为达梦预先读出前面数据并且显示。发现结果集非常大，创建临时表hang住

create table tmp1 as select count（*） from 《-慢在这里 ( SELECT a.as_code, a.app_name, a.app_code, a.as_name, a.unit_name, a.unit_code, REGEXP_SUBSTR(a.codes, '[^,]+', 1, LEVEL) AS ms_code FROM tmp a CONNECT BY LEVEL <= LENGTH(a.codes) - LENGTH(REGEXP_REPLACE(a.codes, ',', '')) + 1)

select DISTINCT * from ( SELECT a.as_code, a.app_name, a.app_code, a.as_name, a.unit_name, a.unit_code, REGEXP_SUBSTR(a.codes, '[^,]+', 1, LEVEL) AS ms_code FROM tmp a CONNECT BY LEVEL <= LENGTH(a.codes) - LENGTH(REGEXP_REPLACE(a.codes, ',', '')) + 1)

-咨询高手，建议所有该子查询集合的结果集。，

优化思路优化前： only connect by ,没有 prior 查询 /

优化后： connect by 加入 prior 查询极大提高效率

测不同场景下。

-》，CONNECT by 和 PRIOR 的用法 --优化前测试1：造重复数据，no PRIOR 的用法，返回数据大概20行

WITH T_DATA AS ( SELECT 'A' AS LINEID,'1,2' AS VAL UNION ALL SELECT 'B' AS LINEID,'3,4,5' AS VAL )

SELECT LINEID,REGEXP_SUBSTR(VAL,'[^,]+',1,LEVEL) AS SUBVAL FROM T_DATA CONNECT BY LEVEL <= REGEXP_COUNT(VAL,',') + 1

-》优化后测试2：返回数据大概5行，可以有效减小数据集。

这个查询的目的是将T_DATA中的VAL字段中的每个逗号分隔的值作为单独的行返回，同时携带LINEID的数据。每行对应VAL字段中的一个值，直到所有值都被处理完毕。由于PRIOR关键字的使用，这个查询会为每个LINEID生成一个层次结构，其中每个值都是前一个值的后续。

WITH T_DATA AS ( SELECT 'A' AS LINEID,'1,2' AS VAL UNION ALL SELECT 'B' AS LINEID,'3,4,5' AS VAL )

SELECT LINEID,REGEXP_SUBSTR(VAL,'[^,]+',1,LEVEL) AS SUBVAL FROM T_DATA CONNECT BY LEVEL <= REGEXP_COUNT(VAL,',') + 1 AND PRIOR LINEID = LINEID AND PRIOR SYS_GUID IS NOT NULL

最后建议客户采用connect by 加入 prior 查询极大提高效率

##############sample 3.1.5 group by and in 子查询临时表空间消耗过大的sql 改写

达梦支持临时表空间，但是过于消耗临时表空间对数据库性能是个巨大的消耗

在这一点不如oracle.

以下SQL 由于带多个子查询和group by，因此非常消耗临时表空间，导致数据库

性能缓慢。

开发优化方法：

通过建立临时表方式，以空间换时间

总结如下：

Sql1 优化建议：当语句比较复杂涉及子查询和大数据量的分组，排序时，可能性能比较低，可以将其拆分多个语句实现相同的逻辑。
对于大数据集，特别是当子查询返回大量行时，EXISTS 通常比 IN 更高效。
Sql2 优化建议：多表连接时，可以去除没必要的查询条件（不影响结果集的情况下），提高效率。

-》以下是详细的分析过程

SQL1:

原语句，这是一条复杂delete 语句，2个条件复杂的子查询带有in, group by,

这条语句使用到复杂子查询，oracle 运行性能可以接受，但是dameng 运行非常慢

，并且十分消耗临时表空间。

原语句如下：

将以上删除语句修改为。

具体思路如下：

通过建立临时表方式，以空间换时间

其一是优化将相对复杂逻辑delete语句拆分成多个（三条）逻辑简单的delete和insert语句，

将group by子句中的数据放在临时表slow_release_split_result_temp_cf中，

再关联主表将条件中不需要的数据删除，

其二就是将多列值in子查询优化成exits子查询，优化delete效率。

其一是优化将相对复杂逻辑delete语句拆分成多个（三条）逻辑简单的delete和insert语句，将group by子句中的数据放在临时表slow_release_split_result_temp_cf中，再关联主表将条件中不需要的数据删除，其二就是将多列值in子查询优化成exits子查询，优化delete效率。

SQL2:

修改2

将inner join子查询临时表t改成inner join 表slow_release_split_result_temp，消除冗余子查询，去除分组，简化内连接逻辑。

总结
当语句比较复杂涉及子查询和大数据量的分组，排序时，可能性能比较低，可以将其拆分多个语句实现相同的逻辑。
对于大数据集，特别是当子查询返回大量行时，EXISTS 通常比 IN 更高效。
多表连接时，可以去除没必要的查询条件（不影响结果集的情况下），提高效率。

附录：

查询临时表空间SQL

select t.sessid,

sql_txt,

recycle_logic_read_cnt,

recycle_phy_read_cnt,

s.state

from v$sql_stat t,v$sessions s

where t.sessid=sess_id

order by 3 desc ,

4 desc;

#################sample 3.1.1 UNION FOR OR2 的执行计划的改善

UNION FOR OR2 的执行计划的改善

1首先把所有 or 语句作为一个集合，这样可以可以减少or 查询里每个条件查询的调用次数。

多个OR 查询简化成一个OR 查询

2.其次使用hint 将or 查询里的条件看作一个整体进行查询，现在将 OPTIMIZER_OR_NBEXP 置为 2，执行相同的语句。此时 OR 表达式作为 T1 的整体过滤条件处理。

Q1: SELECT /*+ OPTIMIZER_OR_NBEXP(2)*/ 可以优化到0.1 秒，oracle 首次执行达到0.3秒，

第二次执行可以达到0.03秒，每次都在0.03秒左右，达梦可以有更好的解决方案

回答如下：

原因如下：

--当前执行计划如下：黄线的几段4个CQ_QM_QUEUESERIAL_LOG全表扫描比较慢

1 #NSET2: [193, 1, 744]

2 #PRJT2: [193, 1, 744]; exp_num(1), is_atom(FALSE)

3 #AAGR2: [193, 1, 744]; grp_num(0), sfun_num(1), distinct_flag[1]; slave_empty(0)

4 #UNION FOR OR2: [193, 34, 744]; key_num(3), outer_join(-)

5 #UNION FOR OR2: [145, 33, 744]; key_num(3), outer_join(-)

6 #UNION FOR OR2: [96, 32, 744]; key_num(3), outer_join(-)

7 #HASH RIGHT SEMI JOIN2: [47, 16, 744]; n_keys(1) KEY(DMTEMPVIEW_18170683.colname='2') KEY_NULL_EQU(0)

8 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),

9 #NEST LOOP INNER JOIN2: [47, 16, 744];

10 #SLCT2: [47, 1, 440]; (QM.WINNO IS NULL AND QM.QUEUECALLTIME IS NULL AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

11 #CSCN2: [47, 231390, 440]; INDEX33555505(CQ_QM_QUEUESERIAL_LOG as QM)

12 #NEST LOOP INDEX JOIN2: [1, 16, 304]

13 #BLKUP2: [1, 1, 152]; INDEX33555549(QW)

14 #SSEK2: [1, 1, 152]; scan_type(ASC), INDEX33555549(CQ_PARA_WINDOW_INFO as QW), scan_range[('756001','9'),('756001','9')]

15 #SSEK2: [1, 16, 144]; scan_type(ASC), INDEX33555541(CQ_PARA_CALLRULE_INFO as RU), scan_range[(QW.RULEID,min,min),(QW.RULEID,max,max))

16 #HASH RIGHT SEMI JOIN2: [47, 16, 744]; n_keys(1) KEY(DMTEMPVIEW_18170684.colname='2') KEY_NULL_EQU(0)

17 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),

18 #NEST LOOP INNER JOIN2: [47, 16, 744];

19 #SLCT2: [47, 1, 440]; (QM.QUEUECALLTIME IS NULL AND QM.WINNO = '9' AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

20 #CSCN2: [47, 231390, 440]; INDEX33555505(CQ_QM_QUEUESERIAL_LOG as QM)

21 #NEST LOOP INDEX JOIN2: [1, 16, 304]

22 #BLKUP2: [1, 1, 152]; INDEX33555549(QW)

23 #SSEK2: [1, 1, 152]; scan_type(ASC), INDEX33555549(CQ_PARA_WINDOW_INFO as QW), scan_range[('756001','9'),('756001','9')]

24 #SSEK2: [1, 16, 144]; scan_type(ASC), INDEX33555541(CQ_PARA_CALLRULE_INFO as RU), scan_range[(QW.RULEID,min,min),(QW.RULEID,max,max))

25 #NEST LOOP INDEX JOIN2: [48, 1, 744]

26 #NEST LOOP INDEX JOIN2: [48, 1, 592]

27 #HASH LEFT SEMI JOIN2: [48, 1, 440]; KEY_NUM(1); KEY(QM.QUEUECALLTP=DMTEMPVIEW_18170682.colname) KEY_NULL_EQU(0)

28 #SLCT2: [47, 1, 440]; (QM.WINNO IS NULL AND QM.QUEUECALLTIME IS NULL AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

29 #CSCN2: [47, 231390, 440]; INDEX33555505(CQ_QM_QUEUESERIAL_LOG as QM)

30 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),

31 #BLKUP2: [1, 1, 96]; INDEX33555549(QW)

32 #SSEK2: [1, 1, 96]; scan_type(ASC), INDEX33555549(CQ_PARA_WINDOW_INFO as QW), scan_range[('756001','9'),('756001','9')]

33 #SSEK2: [1, 1, 144]; scan_type(ASC), INDEX33555541(CQ_PARA_CALLRULE_INFO as RU), scan_range[(QW.RULEID,QM.BSID,QM.QUEUETPID),(QW.RULEID,QM.BSID,QM.QUEUETPID)]

34 #HASH RIGHT SEMI JOIN2: [47, 1, 744]; n_keys(1) KEY(DMTEMPVIEW_18170685.colname='2') KEY_NULL_EQU(0)

35 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),

36 #NEST LOOP INDEX JOIN2: [47, 1, 744]

37 #NEST LOOP INDEX JOIN2: [47, 1, 592]

38 #SLCT2: [47, 1, 440]; (QM.QUEUECALLTIME IS NULL AND QM.WINNO = '9' AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

39 #CSCN2: [47, 231390, 440]; INDEX33555505(CQ_QM_QUEUESERIAL_LOG as QM)

40 #BLKUP2: [1, 1, 96]; INDEX33555549(QW)

41 #SSEK2: [1, 1, 96]; scan_type(ASC), INDEX33555549(CQ_PARA_WINDOW_INFO as QW), scan_range[('756001','9'),('756001','9')]

42 #SSEK2: [1, 1, 144]; scan_type(ASC), INDEX33555541(CQ_PARA_CALLRULE_INFO as RU), scan_range[(QW.RULEID,QM.BSID,QM.QUEUETPID),(QW.RULEID,QM.BSID,QM.QUEUETPID)]

查询了2次，

(QM.WINNO IS NULL AND QM.QUEUECALLTIME IS NULL AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

1.(QM.QUEUECALLTIME IS NULL AND QM.WINNO = '9' AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

(QM.WINNO IS NULL AND QM.QUEUECALLTIME IS NULL AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

2. (QM.QUEUECALLTIME IS NULL AND QM.WINNO = '9' AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);

et 报告如下：

sql1: 查询到该字段过滤性比较好，可以加索引, 过滤了万分之一

select count(*) from CQ_QM_QUEUESERIAL_LOG;

233205

select count(*) select count(DISTINCT(WorkDate)) from CQ_QM_QUEUESERIAL_LOG;

978

暂时优化建议1：减少union alll , 把4个QM 查询条件简化到2个QM 查询条件， QM 查询条件AND的放在一个括号 ,OR 放在一个括号，执行时间为0.25秒，时间赶得上oracle

SELECT count(distinct(QM.queueno))

FROM CQ_QM_QUEUESERIAL_LOG QM, CQ_PARA_WINDOW_INFO QW, CQ_PARA_CALLRULE_INFO RU

WHERE QW.RuleID = RU.RuleID AND (QM.BsID = RU.BsID

AND QM.QueueTpID = RU.QueueTpID AND QM.Brno = QW.Brno AND QM.QueueTpStatus = '0' AND (QM.QueueCallTp in ('1','2'))

AND QM.WorkDate = '20231127' and qm.WinNo is null )

AND QW.Brno = '756001'

AND QW.WinNo = '9'

AND QueueCallTime is null

or (qm.WinNo = '9' and qm.QueueCallTp = '2')

查询了2次，减小查询次数

1.(QM.QUEUECALLTIME IS NULL AND QM.WINNO = '9' AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

2. (QM.QUEUECALLTIME IS NULL AND QM.WINNO = '9' AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

(QM.WINNO IS NULL AND QM.QUEUECALLTIME IS NULL AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

(QM.WINNO IS NULL AND QM.QUEUECALLTIME IS NULL AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QM.BRNO = '756001')

执行计划如下：

1 #NSET2: [46420184136003, 1, 720]

2 #PRJT2: [46420184136003, 1, 720]; exp_num(1), is_atom(FALSE)

3 #AAGR2: [46420184136003, 1, 720]; grp_num(0), sfun_num(1), distinct_flag[1]; slave_empty(0)

4 #NEST LOOP SEMI JOIN2: [46420184136003, 2351249335, 720]; join condition(((QM.WINNO = '9' AND QM.QUEUECALLTP = '2') OR (QM.WINNO IS NULL AND QM.QUEUECALLTIME IS NULL AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231127' AND QW.BRNO = '756001' AND QW.WINNO = '9' AND QM.BRNO = '756001' AND QM.BSID = RU.BSID AND QM.QUEUETPID = RU.QUEUETPID AND QW.RULEID = RU.RULEID AND QM.QUEUECALLTP IN LIST)))

5 #NEST LOOP INNER JOIN2: [334111, 38015349, 576];

6 #CSCN2: [1, 163, 144]; INDEX33555502(CQ_PARA_WINDOW_INFO as QW)

7 #CSCN2: [44, 231390, 432]; INDEX33555505(CQ_QM_QUEUESERIAL_LOG as QM)

8 #SSCN: [1, 1096, 144]; INDEX33555541(CQ_PARA_CALLRULE_INFO as RU)

暂时优化建议2：/*+ OPTIMIZER_OR_NBEXP(2)*/

SELECT /*+ OPTIMIZER_OR_NBEXP(2)*/ count(distinct(QM.queueno))

FROM CQ_QM_QUEUESERIAL_LOG QM, CQ_PARA_WINDOW_INFO QW, CQ_PARA_CALLRULE_INFO RU

WHERE QW.RuleID = RU.RuleID AND (QM.BsID = RU.BsID

AND QM.QueueTpID = RU.QueueTpID or QM.QueueCallTp = '2')

AND QM.Brno = QW.Brno AND QM.QueueTpStatus = '0'

AND (QM.QueueCallTp in ('1','2')) AND QW.Brno = '756001'

AND QW.WinNo = '9'

and (qm.WinNo is null or (qm.WinNo = '9' and qm.QueueCallTp = '2'))

AND QM.WorkDate = '20231127'

AND QueueCallTime is null;

首次执行时间比oracle 快一倍，执行时间为0.125秒

1 #NSET2: [46420184136003, 1, 720]

2 #PRJT2: [46420184136003, 1, 720]; exp_num(1), is_atom(FALSE)

3 #AAGR2: [46420184136003, 1, 720]; grp_num(0), sfun_num(1), distinct_flag[1]; slave_empty(0)

5 #NEST LOOP INNER JOIN2: [334111, 38015349, 576];

6 #CSCN2: [1, 163, 144]; INDEX33555502(CQ_PARA_WINDOW_INFO as QW)

7 #CSCN2: [44, 231390, 432]; INDEX33555505(CQ_QM_QUEUESERIAL_LOG as QM)

8 #SSCN: [1, 1096, 144]; INDEX33555541(CQ_PARA_CALLRULE_INFO as RU)

##该优化hint 解释如下：

语句WHERE条件里面有大量的OR条件，有时查询效率较低，针对OR条件的优化，有以下几个思路可以尝试一下。
一、SQL改写，可以把SQL语句里面的or查询条件，改写为union all查询语句替换；
二、可以使用or查询优化参数，根据经验适当的进行hint优化；
DROP TABLE T1;
DROP TABLE T2;
CREATE TABLE T1(C1 INT,C2 INT,C3 INT);
CREATE TABLE T2(D1 INT,D2 INT,D3 INT);
CREATE INDEX IT1C1 ON T1(C1);
执行下面的语句，OPTIMIZER_OR_NBEXP 为 0，使用 UNION FOR OR 处理 OR 表达式，然后通过 ROWID 去掉重复数据
EXPLAIN SELECT /*+ OPTIMIZER_OR_NBEXP(0) */ * FROM T1 WHERE C1=1 OR C2=5;

现在将 OPTIMIZER_OR_NBEXP 置为 2，执行相同的语句。此时 OR 表达式作为 T1 的整体过滤条件处理。

OPTIMIZER_OR_NBEXP 取不同值对应 OR 表达式不同的优化处理，具体如下：
✓ 0：不进行优化；
✓ 1：扩展 OR 条件，加入 LNNVL 函数，使用 UNION ALL 方式实现；
✓ 2：OR 条件作为整体处理；
✓ 4：相关子查询中的 OR 表达式作为整体处理；
✓ 8：对于形如(b1 AND b2) OR (b3 AND b4)的表达式，其中都是对同一个列的常量过滤，进行合并处理；
✓ 16：对于形如 COL1 OPS CONST OR COL1 IS NULL 的表达式，进行合并，计划生成阶段生成 UNION ALL，避免 UNION FOR OR 的去重处理。

在实际优化处理的过程中，先要通过分段定位方法定位到执行耗时较长的SQL语句片段，然后在针对性的进行处理即可。如果定位到是因为or条件导致的SQL执行性能较差。可以使用hint方式修改OPTIMIZER_OR_NBEXP参数值尝试解决；具体优化方法可以参考《参数OPTIMIZER_OR_NBEXP对or表达式的优化实践案例》
访问网址：https://eco.dameng.com/community/post/20230216145614MZ0HL17NA5MQ95Q7VJ

#################sample 3.1.3 大表（无索引）的SQL优化-20240918

此SQL查询慢耗时10分钟都未出结果

select ORGID,NVL(SUM(DEBITAMT - CREDITAMT), 0) AS GUARANTYNUM,'4001'

FROM (

SELECT DEBITAMT , CREDITAMT, ORGID

FROM userETL.D001_ACCT_SUBLEDGER_DETAIL_A

WHERE OBJECTTYPE = 'Contract'

AND OCCURDATE <= to_char('2023/6/21','yyyy/mm/dd')

AND ACCOUNTCODENO = '7040102'

union all

SELECT DEBITAMT , CREDITAMT, ORGID

FROM userDATA.AP_ACCT_DETAIL_T140101

WHERE OBJECTTYPE = 'Contract'

AND ACCOUNTCODENO = '7040102') AA group by ORGID;

---原SQL语句，表未指定模式名

-》分别执行union all的 SQL

-》优化前的SQL 执行计划

-》优化后的SQL执行计划

(1) 查询表对应的模式名

SELECT * FROM ALL_TABLES WHERE TABLE_NAME='D001_ACCT_SUBLEDGER_DETAIL_A';

SELECT * FROM ALL_TABLES WHERE TABLE_NAME='AP_ACCT_DETAIL_T140101';

(2) 检查表字段的索引信息

select index_name,a.table_name,uniqueness,column_name from all_indexes a join all_ind_columns USING (index_name) where a.table_name='D001_ACCT_SUBLEDGER_DETAIL_A' and owner='userETL';

---查询表字段无索引信息

(3) 统计表数据量

select count(*) from userETL.D001_ACCT_SUBLEDGER_DETAIL_A;

---904248275 9亿行数据量

select count(*) from userDATA.AP_ACCT_DETAIL_T140101;

---数据量为空

结合上述分析

SQL查询耗时主要消耗在userETL.D001_ACCT_SUBLEDGER_DETAIL_A

(1) 获取下过滤字段的过滤性如何

①　等值条件1： ACCOUNTCODENO = '7040102'

select count(*) from userETL.D001_ACCT_SUBLEDGER_DETAIL_A where ACCOUNTCODENO = '7040102';

9亿行记录该过滤字段117107行，过滤性很好。

②　等值条件2：OBJECTTYPE = 'Contract'

select count(*) from userETL.D001_ACCT_SUBLEDGER_DETAIL_A where OBJECTTYPE = 'Contract';

9亿行记录该过滤字段也是117107行。

③　时间范围：

时间范围内的数据量大的话回表也会影响性能

OCCURDATE <= to_char('2023/6/21','yyyy/mm/dd')

(2) 优化结果：

在ACCOUNTCODENO字段新建索引后查询效率如下耗时38s：

使用CREATE INDEX语句创建索引，并在语句中使用PARALLEL关键字指定并行度。例如： CREATE INDEX "IDX_D001_ASDA_OCDATE" ON "userETL"."D001_ACCT_SUBLEDGER_DETAIL_A"("OCCURDATE" ASC) STORAGE(ON "user_DATA", CLUSTERBTR) ;

CREATE INDEX "IDX_D001_ASDA_ACNO" ON "userETL"."D001_ACCT_SUBLEDGER_DETAIL_A"("ACCOUNTCODENO" ASC) STORAGE(ON "user_DATA", CLUSTERBTR) ;

[执行语句1]:

select ORGID,NVL(SUM(DEBITAMT - CREDITAMT), 0) AS GUARANTYNUM,'4001'

FROM (

SELECT DEBITAMT , CREDITAMT, ORGID

FROM userETL.D001_ACCT_SUBLEDGER_DETAIL_A

WHERE OBJECTTYPE = 'Contract'

AND OCCURDATE <= to_char('2023/6/21','yyyy/mm/dd')

AND ACCOUNTCODENO = '7040102'

union all

SELECT DEBITAMT , CREDITAMT, ORGID

FROM userDATA.AP_ACCT_DETAIL_T140101

WHERE OBJECTTYPE = 'Contract'

AND ACCOUNTCODENO = '7040102') AA group by ORGID;

执行成功, 执行耗时38秒 462毫秒. 执行号:173403

##########sample 3.2 优化案例又一则

问题：

客户反馈正常的sql 只需要10毫秒，但是加了order by desc 后，速度降低到2秒左右

该表记录只有60万，该表在order_id 上建立一个索引。

返回记录数目在50万，过滤性不好。因为原来的sql 生成结果记录数目太多，无法生成ET 报告，

所以在sql 里加入 top 1 ,这样可以看到et 报告

##quickly 快sql

select top 1 * from dddd.aaa_FORMS t where t.org_path like '999888%'

##slowly,需要优化慢sql

select top 1 * from dddd.aaa_FORMS t

where t.org_path like '999888%'

order by t.ORDER_SUBMIT_DT DESC;

该表的索引索引关系如下，在order_id 上建立一个索引：

分析：

该表有60万记录，where t.org_path like '999888%'

过滤后数目仍然有50万笔。

->有问题执行计划如下：

Et 报告如下：最后的全表扫描进入了1998次。每次时间为8000us.

->去掉order by 正常速度的执行计划如下：

Et报告如下：全表扫描为1次，每次819 us

->目前怀疑是全表扫描进入次数过多从1到1998次导致。

为了验证执行如上模拟sql

执行如上top 600.

select top 600 * from dddd.aaa_FORMS t where t.org_path like '999888%'

为了获得ET 报告点击右下角，输出所有记录

生成ET 报告如下：确实运行了6次(n-enter)，也就是每100条记录都是enter 一次

-》优化方法：

1.建议排序索引DESC, 因为SQL 语句使用DESC 排序，所以索引也要建立DESC

CREATE INDEX "IDX_FM_ORDER_FORMS_ORDER_SUBMIT_DT" ON "aaa"."FM_ORDER_FORMS"

("ORDER_SUBMIT_DT" DESC;

如果仍然发现仍然是走的全表扫描CSCN2，达梦优化器的计算错误，错误的认为

全表扫描成本为46558，索引扫描成本在49437

索引扫描成本如下：

全表扫描成本如下：

3，只能用如下hint ,强制走索引

/*+INDEX(t,index_name) */

修改后的sql 如下：

select /*+INDEX(t,IDX_FM_ORDER_FORMS_ORDER_SUBMIT_DT) */ * from dddd.aaa_FORMS t

where t.org_path like '99988%'

order by t.ORDER_SUBMIT_DT DESC；

执行速度从2秒降低到20毫秒。

;

##############sample 3.3 如何收集ET 报告

第一部分：
-》前面加explain执行可以看计划

中间按钮，显示执行计划

右下角最大化执行计划

Txt 文件方式显示执行计划

第二部分：
-》生成ET 报告方法如下：
Step1:
授权给用户ET 权限
grant execute on et to aaa;

Step2: 登陆达梦dm 工具

执行如下SQL,打开监控，

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);

Step3 : 执行慢sql
select * from AUTHORITY_DESC

选择下方的消息，执行号的数字，就可以打开ET 报告

Step 4.ET 报告如下：

另外如果ET报告没有展示：可能问题，结果集为空，可能是返回结果集过多导致

解决办法：
Step2: 登陆达梦dm 工具

执行如下SQL,打开监控，

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);

Step3 : 执行慢sql
将慢sql改写，加top1,然后执行 :
select * from AUTHORITY_DESC
改写成
select top 1 * from AUTHORITY_DESC

选择下方的消息，执行号的数字，就可以打开ET 报告

###########sample 3.4

问题介绍：

某客户反馈某存储过程执行的过慢，尝试重现方法，问题可以重现，一条存储过程

（PR_EAST_INIT_aa）运行了好几个小时无结果。

重现方法如下：

BEGIN

PR_EAST_INIT_('20230831','');

END

分析过程：

1.尝试通过V$SQL_STAT分析，发现无法分析存储过程正在运行的SQL

select S.*

from V$SQL_STAT S

WHERE S.SESSID = '139715692267824';

2.咨询专家，需要通过另外的视图分析V$DMSQL_EXEC_TIME 分析，红色部分

为存储过程名字

select * from V$DMSQL_EXEC_TIME where exec_id=(

select max(exec_id) from V$DMSQL_EXEC_TIME where method like '%PR_EAST_INIT%'

order by level,seq,time_used

等15秒，再次执行，发现time_used这个字段都没有变化，如果time_used这个字段发生变化，说明正在运行，经过检查该字段没有变化，所以没有运行

只是怀疑到可能跟INSERT INTO EAST_INIT_aa sql慢有关系，尝试打开该sql 语句详细内容，但是该SQL 长度超过600，所以无法显示完整的SQL.

检查锁的情况，没有发现问题

glock0

查询数据库日志$DM_HOME/log,没有发现问题

select * from v$instance_log_history order by log_time desc

5.使用 gdb attach 命令，也没有发现问题
下面举例打印一个正在执行 SQL 的线程堆栈：

通过 V$SESSIONS 视图的 THRD_ID 列找出当前会话对应的线程号：

select THRD_ID from V$SESSIONS where sess_id=139716027811840

582184

使用 pstack 工具打印其堆栈信息：

pstack 582184

[root@dmperfdw1 log]# pstack 582184

Thread 1 (process 582184):

#0 0x00007f13b7865a1c in pthread_cond_wait () from /lib64/libpthread.so.0

#1 0x000000000040fe5b in os_event2_wait ()

#2 0x00000000014aac51 in uevent_wait_wevt ()

#3 0x00000000004dc099 in buf4_io_wait.part ()

#4 0x00000000004f46c3 in buf4_page_get_low_for_LRU ()

#5 0x00000000004dbd9f in buf4_page_get_org ()

#6 0x000000000065fa18 in nbtr_cur_lock_leaves ()

#7 0x00000000006683cd in pbcur_move_to_right_user_rec_fast ()

#8 0x0000000000f79653 in cscn2_exec_get_nrecs ()

#9 0x0000000000f7ec33 in cscn2_exec_fill_data_low ()

#10 0x0000000000f7ee2c in cscn2_exec_fetch ()

#11 0x0000000000f83c90 in cscn2_exec ()

#12 0x000000000111defa in vm_run_low ()

#13 0x000000000111e339 in vm_run ()

#14 0x0000000001123609 in vm_run_pln_low ()

#15 0x0000000001123792 in vm_run_pln ()

#16 0x00000000015b53d0 in ntsk_process_exec_low ()

#17 0x00000000015b905b in ntsk_process_exec ()

#18 0x00000000015c107d in ntsk_process_cop ()

#19 0x00000000014ad837 in uthr_db_main_for_sess ()

#20 0x00007f13b785ff2b in ?? () from /lib64/libpthread.so.0

#21 0x00007f13b745c6bf in clone () from /lib64/libc.so.6

尝试抓取EAST_INIT_aa的缓存信息，发现只有存储过程的

缓存，没有具体sql的缓存INSERT INTO ,因此也是也没有发现问题

select cache_item, sqlstr from v$cachepln

select * from

where sqlstr like '%EAST_INIT_aa%'

6。查看执行计划

拷贝存储过程SQL，根据2的怀疑sql 语句执行时间，

筛选出 INSERT INTO EAST_INIT_aa

筛选出3条INSERT语句分别在163,360,532行

-》复制存储过程SQL新建个存储过程，PR_EAST_INIT_aa_TEST

根据3条INSERT 语句行号，定位到对应行

去掉INSERT语句，因为3条insert 是根据IF 条件（ IF v_data_date = util.get_month_end_date (v_data_date)）判断的，只会执行一个，

所以需要判断执行哪一个insert 语句

-》筛选出SELECT语句中所有的变量，并拼接成select 该变量值 from dual;

变量是v_方式定义，ctrl + f 弹出查找框，查找变量名即可

select 1 是为了区分是第几个select语句，后面跟的变量参数是select中的变量

select 1,v_data_date1,v_data_date,v_load_date,v_first_day from dual;

return;

select 2,v_data_date1,v_data_date,v_load_date,v_first_day from dual;

return;

select 3,v_data_date1,v_data_date,v_load_date,v_first_day from dual;

return;

变量筛选完后，select语句删除即可，无需执行.

这样可以判断该条件20230831传参数执行是第一条、第二条、第三条具体哪条sql.

最后判断执行的是第一条sql

第2-3个select 语句同理，删除掉insert语句，筛选出select中的变量名

select 2 ,v_data_date1,v_data_date,v_load_date,v_first_day from dual;

return;

select 3,v_data_date1,v_data_date,v_load_date,v_first_day from dual;

return;

修改完后创建修改完的新的测试存储过程(PR_EAST_INIT_aa_TEST)并执行

-》执行结果显示的是第一个select语句

先保存变量值

-》管理工具新建查询

复制定位到第一个select 语句到窗口中，将变量改成常量，再次执行，获取执行计划，分析

Ctrl + f 将select 语句中的变量用查询出来的值进行替换

1 v_data_date1 v_data_date v_load_date v_first_day

1 2023-08-31 20230831 20231101 20230801

其他变量同理，全部替换

执行计划如下：

Select中的变量替换为执行存储过程得到的常量

)
AND org.ods_start_dt <= '2023-08-31' and org.ods_end_dt > '2023-08-31'
AND loan.ods_start_dt <= '2023-08-31' and loan.ods_end_dt > '2023-08-31'
AND subject_def.ods_start_dt <= '2023-08-31' and subject_def.ods_end_dt > '2023-08-31'
AND code.ods_start_dt <= '2023-08-31' and code.ods_end_dt > '2023-08-31'
AND trans.ods_load_dt between '20230801' and '2023-08-31'
AND detail.ods_load_dt between '20230801' and '2023-08-31'
AND cus.ods_start_dt(+) <='2023-08-31' and cus.ods_end_dt(+) >'2023-08-31'
AND CODE1.ods_start_dt(+) <='2023-08-31' and CODE1.ods_end_dt(+) >'2023-08-31'

结论

该条SQL select SQL 过慢，跟如下SQL 有关系，现在暂时优化建议

执行计划如下:

第5行和第11行走的是全表扫描，

暂时优化建议

(D001_CODE_LIBRARY as CODE1)表，(D001_ACCT_TRANSACTION 建立索引，使用索引扫描

###############

附录：

以下为存储过程SQL语句

#############sample 3.5 统计信息不准确导致数据库的执行计划的计算偏差，优化了统计信息，执行计划统计统计信息准确，sql 从2秒变成1秒

SQL>set linesize 999
SQL> SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
DMSQL executed successfully
used time: 6.436(ms). Execute id is 12738800.
SQL> set autotrace trace
SQL> SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
DMSQL executed successfully

used time: 5.626(ms). Execute id is 12738802.
SQL> set autotrace trace
SQL> select * from (select t1.*, rownum rn from (
2 select REF_CHANNEL, REF_NAME, REF_NO, REFERER, REF_BRCHNAME, CUST_CMONCD, APPLY_DATE, CUST_ACCT, CUST_NO, CUST_NAME,
3 decode(DRAFT_TYPE,'1','银票','2','商票','3','财票') AS DRAFT_TYPE, DRAFT_NUMBER, CD_RANGE,
4 REMIT_DATE, MATURITY_DATE, REMITTER_NAME, REMITTER_ACCOUNT, REMITTER_BANK_NAME, REMITTER_BANK_NO,
5 DRAFT_AMOUNT, ACCEPTOR_ACCOUNT, ACCEPTOR_BANK_NAME, ACCEPTOR_BANK_NO, ACCEPTOR_NAME, PAYEE_NAME,
6 PAYEE_ACCOUNT, PAYEE_BANK_NO, PAYEE_BANK_NAME, RATE,decode(APPLY_STATUS,'00','待提交','10','已提交','20','贴现签收成功','30','贴现撤销','40','贴现拒签',
7 '50','贴现签收失败','60','日终拒签','70','已结清') AS APPLY_STATUS, ERRMSG, CREATE_TIME,TRANS_NO,AOA_ACCOUNT,AOA_BANK_ID,AOA_BANK_NAME,APPLY_ID,
8 STATUS,UPDATE_TIME,PAY_AMOUNT,INTEREST,PROMOTION_EXPENSES,decode(IS_AUTO_DISC,'1','是','0','否') AS IS_AUTO_DISC, decode
9 (t.DRAFT_TYPE, '1', '-', '2', decode(t.ISSPMT, '1', '是', '2', '否', '', '否')) AS ISSPMT, BR_NAME, DECODE(CREDIT_PRODUCT_TYPE,
10 '11', '商票保贴', '24', '信付通', '270', '润秒贴银票', '367', '商票宝', '19', '普通产品') AS CREDIT_PRODUCT_TYPE from
11 (select b.REF_CHANNEL, a.ISSPMT, b.REF_NO, b.REF_NAME, a.CUST_CMONCD, a.APPLY_DATE, a.CUST_ACCT, a.CUST_NO, a.CUST_NAME, a.DRAFT_TYPE ,
12 a.DRAFT_NUMBER, a.CD_RANGE, a.REMIT_DATE, a.MATURITY_DATE, a.REMITTER_NAME, a.REMITTER_ACCOUNT, a.REMITTER_BANK_NAME, a.REMITTER_BANK_NO,
13 a.DRAFT_AMOUNT, a.ACCEPTOR_ACCOUNT, a.ACCEPTOR_BANK_NAME, a.ACCEPTOR_BANK_NO, a.ACCEPTOR_NAME, a.PAYEE_NAME, a.PAYEE_ACCOUNT,
14 a.PAYEE_BANK_NO, a.PAYEE_BANK_NAME, a.RATE, a.STATUS, a.ERRMSG, a.CREATE_TIME, a.TRANS_NO, a.AOA_ACCOUNT, a.AOA_BANK_ID,
15 a.AOA_BANK_NAME, a.APPLY_ID, a.APPLY_STATUS, a.UPDATE_TIME, a.PAY_AMOUNT, a.INTEREST, a.IS_GYL_DISC, a.DISC_TYPE, b.REF_BRCHNO,
16 b.REFERER, c.DEPT_NAME REF_BRCHNAME, a.PROMOTION_EXPENSES, a.IS_AUTO_DISC, a.BR_NAME, a.CREDIT_PRODUCT_TYPE from aaaadata.aaaa_tx_apply a
17 inner join aaaadata.aaaa_pf_com_signinfo b on a.cust_no = b.cust_no left join aaaadata.aaaa_brch_dept_rela c on b.ref_brchno = c.brchno) t
18 WHERE NOT EXISTS
19 (select bctl.BR_NO from aaaadata.aaaa_PF_BCTL bctl
20 where bctl.BANK_NO=t.CUST_ACCT) AND t.REF_BRCHNO = '756888' AND t.CUST_NAME like '%'||'偏安'||'%' order by t.CREATE_TIME desc)
21 t1 order by rownum)
22 where rn < 10 and rn > 3;
no rows

1 #NSET2: [14, 1, 2192]
2 #PRJT2: [14, 1, 2192]; exp_num(47), is_atom(FALSE)
3 #SLCT2: [14, 1, 2192];
4 #PRJT2: [14, 9, 2192]; exp_num(47), is_atom(FALSE)
5 #SORT3: [14, 9, 2192]; key_num(1), is_distinct(FALSE), is_adaptive(0)
6 #RN: [13, 917, 2192]
7 #PRJT2: [13, 917, 2192]; exp_num(46), is_atom(FALSE)
8 #SORT3: [13, 917, 2192]; key_num(1), is_distinct(FALSE), is_adaptive(0)
9 #HASH RIGHT SEMI JOIN2: [12, 917, 2192]; key_num(1) (ANTI),
10 #CSCN2: [1, 91, 96]; INDEX33556888(aaaa_PF_BCTL)
11 #PRJT2: [11, 1007, 2192]; exp_num(46), is_atom(FALSE)
12 #HASH RIGHT JOIN2: [11, 1007, 2192]; key_num(1); col_num(46)
13 #CSCN2: [1, 10->10, 96]; INDEX33557020(aaaa_BRCH_DEPT_RELA)
14 #SLCT2: [9, 1007->0, 2192];
15 #HASH2 INNER JOIN: [9, 1007->206446, 2192]; LKEY_UNIQUE KEY_NUM(1);
16 #SLCT2: [9, 1007, 2192];
17 #NEST LOOP INDEX JOIN2: [9, 1007->87486, 2192]
18 #ACTRL: [9, 1007->7697, 2192];
19 #SLCT2: [3, 503->7697, 288];
20 #CSCN2: [3, 20145->20145, 288]; INDEX33556892(aaaa_PF_COM_SIGNINFO)
21 #BLKUP2: [3, 2->87486, 48]; IDX_TX_APPLY_CUSTNO(aaaa_TX_APPLY)
22 #SSEK2: [3, 2->87486, 48]; scan_type(ASC), IDX_TX_APPLY_CUSTNO(aaaa_TX_APPLY)
23 #CSCN2: [443, 732888->732888, 1904]; INDEX33556914(aaaa_TX_APPLY)

used time: 00:00:02.809. Execute id is 12738804.

call et(12738804);

LINEID OP TIME(US) PERCENT RANK SEQ N_ENTER HASH_USED_CELLS HASH_CONFLICT
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
1 PRJT2 0 0% 22 4 2 0 0
2 PRJT2 0 0% 22 11 2 0 0
3 PRJT2 1 0% 20 7 2 0 0
4 PRJT2 1 0% 20 2 2 0 0
5 DLCK 4 0% 19 0 2 0 0
6 HRS2 6 0% 18 9 2 0 0
7 SLCT2 7 0% 17 3 2 0 0
8 RN 13 0% 16 6 2 0 0
9 SORT3 16 0% 15 8 2 0 0
10 CSCN2 32 0% 14 13 2 0 0
11 SORT3 36 0% 13 5 2 0 0

LINEID OP TIME(US) PERCENT RANK SEQ N_ENTER HASH_USED_CELLS HASH_CONFLICT
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- --------------------
12 ACTRL 42 0% 12 18 139 0 0
13 NSET2 57 0% 11 1 2 0 0
14 SLCT2 575 0.02% 10 19 138 0 0
15 HRO2 767 0.03% 9 12 4 20 0
16 SLCT2 8096 0.29% 8 16 957 0 0
17 CSCN2 10865 0.39% 7 20 69 0 0
18 IJI2 19563 0.7% 6 17 2798 0 0
19 SLCT2 22291 0.8% 5 14 2278 0 0
20 SSEK2 23689 0.85% 4 22 1829 0 0
21 HI3 78439 2.81% 3 15 4778 13338 82
22 BLKUP2 910457 32.57% 2 21 3658 0 0

23 rows got

Day1: 测试生成索引，性能是否有改善，发现并无改善。

如果有测试环境，那就在测试库加俩索引看看执行计划，生产环境别动
CREATE?INDEX?IDX_aaaa_PF_BCTL_TST1?ON?aaaa_PF_BCTL(BANK_NO);
CREATE?INDEX?IDX_aaaa_PF_COM_SIGNINFO_TST1?ON?aaaa_PF_COM_SIGNINFO(REF_BRCHNO);

Running sql again :

select * from (select t1.*, rownum rn from (
select REF_CHANNEL, REF_NAME, REF_NO, REFERER, REF_BRCHNAME, CUST_CMONCD, APPLY_DATE, CUST_ACCT, CUST_NO, CUST_NAME,
decode(DRAFT_TYPE,'1','银票','2','商票','3','财票') AS DRAFT_TYPE, DRAFT_NUMBER, CD_RANGE,
REMIT_DATE, MATURITY_DATE, REMITTER_NAME, REMITTER_ACCOUNT, REMITTER_BANK_NAME, REMITTER_BANK_NO,
DRAFT_AMOUNT, ACCEPTOR_ACCOUNT, ACCEPTOR_BANK_NAME, ACCEPTOR_BANK_NO, ACCEPTOR_NAME, PAYEE_NAME,
PAYEE_ACCOUNT, PAYEE_BANK_NO, PAYEE_BANK_NAME, RATE,decode(APPLY_STATUS,'00','待提交','10','已提交','20','贴现签收成功','30','贴现撤销','40','贴现拒签',
'50','贴现签收失败','60','日终拒签','70','已结清') AS APPLY_STATUS, ERRMSG, CREATE_TIME,TRANS_NO,AOA_ACCOUNT,AOA_BANK_ID,AOA_BANK_NAME,APPLY_ID,
STATUS,UPDATE_TIME,PAY_AMOUNT,INTEREST,PROMOTION_EXPENSES,decode(IS_AUTO_DISC,'1','是','0','否') AS IS_AUTO_DISC, decode
(t.DRAFT_TYPE, '1', '-', '2', decode(t.ISSPMT, '1', '是', '2', '否', '', '否')) AS ISSPMT, BR_NAME, DECODE(CREDIT_PRODUCT_TYPE,
'11', '商票保贴', '24', '信付通', '270', '润秒贴银票', '367', '商票宝', '19', '普通产品') AS CREDIT_PRODUCT_TYPE from
(select b.REF_CHANNEL, a.ISSPMT, b.REF_NO, b.REF_NAME, a.CUST_CMONCD, a.APPLY_DATE, a.CUST_ACCT, a.CUST_NO, a.CUST_NAME, a.DRAFT_TYPE ,
a.DRAFT_NUMBER, a.CD_RANGE, a.REMIT_DATE, a.MATURITY_DATE, a.REMITTER_NAME, a.REMITTER_ACCOUNT, a.REMITTER_BANK_NAME, a.REMITTER_BANK_NO,
a.DRAFT_AMOUNT, a.ACCEPTOR_ACCOUNT, a.ACCEPTOR_BANK_NAME, a.ACCEPTOR_BANK_NO, a.ACCEPTOR_NAME, a.PAYEE_NAME, a.PAYEE_ACCOUNT,
a.PAYEE_BANK_NO, a.PAYEE_BANK_NAME, a.RATE, a.STATUS, a.ERRMSG, a.CREATE_TIME, a.TRANS_NO, a.AOA_ACCOUNT, a.AOA_BANK_ID,
a.AOA_BANK_NAME, a.APPLY_ID, a.APPLY_STATUS, a.UPDATE_TIME, a.PAY_AMOUNT, a.INTEREST, a.IS_GYL_DISC, a.DISC_TYPE, b.REF_BRCHNO,
b.REFERER, c.DEPT_NAME REF_BRCHNAME, a.PROMOTION_EXPENSES, a.IS_AUTO_DISC, a.BR_NAME, a.CREDIT_PRODUCT_TYPE from aaaadata.aaaa_tx_apply a
inner join aaaadata.aaaa_pf_com_signinfo b on a.cust_no = b.cust_no left join aaaadata.aaaa_brch_dept_rela c on b.ref_brchno = c.brchno) t
WHERE NOT EXISTS
(select bctl.BR_NO from aaaadata.aaaa_PF_BCTL bctl
where bctl.BANK_NO=t.CUST_ACCT) AND t.REF_BRCHNO = '756888' AND t.CUST_NAME like '%'||'偏安'||'%' order by t.CREATE_TIME desc)
t1 order by rownum)
where rn < 10 and rn > 3;

###使用如下方法，可以少用hash,但是实际时间并无改善
select /*+enable_hash_join(0)*/ * from (select t1.*, rownum rn from (
select REF_CHANNEL, REF_NAME, REF_NO, REFERER, REF_BRCHNAME, CUST_CMONCD, APPLY_DATE, CUST_ACCT, CUST_NO, CUST_NAME,
decode(DRAFT_TYPE,'1','银票','2','商票','3','财票') AS DRAFT_TYPE, DRAFT_NUMBER, CD_RANGE,
REMIT_DATE, MATURITY_DATE, REMITTER_NAME, REMITTER_ACCOUNT, REMITTER_BANK_NAME, REMITTER_BANK_NO,
DRAFT_AMOUNT, ACCEPTOR_ACCOUNT, ACCEPTOR_BANK_NAME, ACCEPTOR_BANK_NO, ACCEPTOR_NAME, PAYEE_NAME,
PAYEE_ACCOUNT, PAYEE_BANK_NO, PAYEE_BANK_NAME, RATE,decode(APPLY_STATUS,'00','待提交','10','已提交','20','贴现签收成功','30','贴现撤销','40','贴现拒签',
'50','贴现签收失败','60','日终拒签','70','已结清') AS APPLY_STATUS, ERRMSG, CREATE_TIME,TRANS_NO,AOA_ACCOUNT,AOA_BANK_ID,AOA_BANK_NAME,APPLY_ID,
STATUS,UPDATE_TIME,PAY_AMOUNT,INTEREST,PROMOTION_EXPENSES,decode(IS_AUTO_DISC,'1','是','0','否') AS IS_AUTO_DISC, decode
(t.DRAFT_TYPE, '1', '-', '2', decode(t.ISSPMT, '1', '是', '2', '否', '', '否')) AS ISSPMT, BR_NAME, DECODE(CREDIT_PRODUCT_TYPE,
'11', '商票保贴', '24', '信付通', '270', '润秒贴银票', '367', '商票宝', '19', '普通产品') AS CREDIT_PRODUCT_TYPE from
(select b.REF_CHANNEL, a.ISSPMT, b.REF_NO, b.REF_NAME, a.CUST_CMONCD, a.APPLY_DATE, a.CUST_ACCT, a.CUST_NO, a.CUST_NAME, a.DRAFT_TYPE ,
a.DRAFT_NUMBER, a.CD_RANGE, a.REMIT_DATE, a.MATURITY_DATE, a.REMITTER_NAME, a.REMITTER_ACCOUNT, a.REMITTER_BANK_NAME, a.REMITTER_BANK_NO,
a.DRAFT_AMOUNT, a.ACCEPTOR_ACCOUNT, a.ACCEPTOR_BANK_NAME, a.ACCEPTOR_BANK_NO, a.ACCEPTOR_NAME, a.PAYEE_NAME, a.PAYEE_ACCOUNT,
a.PAYEE_BANK_NO, a.PAYEE_BANK_NAME, a.RATE, a.STATUS, a.ERRMSG, a.CREATE_TIME, a.TRANS_NO, a.AOA_ACCOUNT, a.AOA_BANK_ID,
a.AOA_BANK_NAME, a.APPLY_ID, a.APPLY_STATUS, a.UPDATE_TIME, a.PAY_AMOUNT, a.INTEREST, a.IS_GYL_DISC, a.DISC_TYPE, b.REF_BRCHNO,
b.REFERER, c.DEPT_NAME REF_BRCHNAME, a.PROMOTION_EXPENSES, a.IS_AUTO_DISC, a.BR_NAME, a.CREDIT_PRODUCT_TYPE from aaaadata.aaaa_tx_apply a
inner join aaaadata.aaaa_pf_com_signinfo b on a.cust_no = b.cust_no left join aaaadata.aaaa_brch_dept_rela c on b.ref_brchno = c.brchno) t
WHERE NOT EXISTS
(select bctl.BR_NO from aaaadata.aaaa_PF_BCTL bctl
where bctl.BANK_NO=t.CUST_ACCT) AND t.REF_BRCHNO = '756888' AND t.CUST_NAME like '%'||'偏安'||'%' order by t.CREATE_TIME desc)
t1 order by rownum)
where rn < 10 and rn > 3;

call et(12738811);

Day2.收集统计信息后，执行计划仍然没有改善，预估的值仍然不准

收集了该表统计信息，
aaaadata.aaaa_tx_apply
call DBMS_STATS.GATHER_TABLE_STATS('aaaaDATA','aaaa_TX_APPLY',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

call DBMS_STATS.GATHER_TABLE_STATS('aaaaDATA','aaaa_PF_COM_SIGNINFO',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

call DBMS_STATS.GATHER_TABLE_STATS('aaaaDATA','aaaa_PF_BCTL',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

收集了该索引统计信息
CALL SP_TAB_INDEX_STAT_INIT ('aaaaDATA', 'aaaa_TX_APPLY');

再次建议：一层层的剥吧，测试下下面这个SQL的耗时，并看看执行计划。
使用pl/sql developer 编辑SQL ，并且屏蔽,帮助不大，因为每次执行，执行都是执行结果集时间非常长，导致sql trace 看不了

再次建议。清空缓存

--清空缓存，使得新的执行计划生效
select cache_item,sqlstr,'call sp_clear_plan_cache('||cache_item||');' from v$cachepln where sqlstr like '%偏安%';

执行计划得以优化，执行时间从2秒减少到1秒

总结1：
set autotrace trace
会有统计信息出来。但是没有condition
#NSET2: [19614942, 18, 2192]
2 #PRJT2: [19614942, 18, 2192]; exp_num(47), is_atom(FALSE)
3 #SLCT2: [19614942, 18, 2192];
4 #PRJT2: [19614942, 362, 2192]; exp_num(47), is_atom(FALSE)
5 #SORT3: [19614942, 362, 2192]; key_num(1), is_distinct(FALSE), is_adaptive(0)
6 #RN: [19614917, 36215, 2192]
7 #PRJT2: [19614917, 36215, 2192]; exp_num(46), is_atom(FALSE)
8 #SORT3: [19614917, 36215, 2192]; key_num(1), is_distinct(FALSE), is_adaptive(0)
9 #NEST LOOP SEMI JOIN2: [19614891, 36215, 2192]; key_num(0) (ANTI),
10 #PRJT2: [978, 36644, 2192]; exp_num(46), is_atom(FALSE)
11 #INDEX JOIN LEFT JOIN2: [978, 36644, 2192]: col_num(46)
12 #SLCT2: [715, 36644, 2192];
13 #NEST LOOP INDEX JOIN2: [715, 36644, 2192]
14 #SLCT2: [450, 36644->0, 1904];
15 #CSCN2: [450, 732888->732888, 1904]; INDEX33556914(aaaa_TX_APPLY)
16 #BLKUP2: [240, 1, 48]; INDEX33556930(aaaa_PF_COM_SIGNINFO)
17 #SSEK2: [240, 1, 48]; scan_type(ASC), INDEX33556930(aaaa_PF_COM_SIGNINFO)
18 #BLKUP2: [239, 1, 48]; INDEX33557134(aaaa_BRCH_DEPT_RELA)
19 #SSEK2: [239, 1, 48]; scan_type(ASC), INDEX33557134(aaaa_BRCH_DEPT_RELA)
20 #CSCN2: [1, 91->91, 96]; INDEX33556888(aaaa_PF_BCTL)

总结2：

F9 就会有conditon ,但是没有统计信息记录

1 #NSET2: [507, 9, 2192]
2 #PRJT2: [507, 9, 2192]; exp_num(47), is_atom(FALSE)
3 #SLCT2: [507, 9, 2192]; DMTEMPVIEW_16818973.RN > var3
4 #PRJT2: [507, 184, 2192]; exp_num(47), is_atom(FALSE)
5 #SORT3: [507, 184, 2192]; key_num(1), is_distinct(FALSE), top_flag(1), is_adaptive(0)
6 #RN: [494, 18450, 2192]
7 #PRJT2: [494, 18450, 2192]; exp_num(46), is_atom(FALSE)
8 #SORT3: [494, 18450, 2192]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
9 #HASH RIGHT SEMI JOIN2: [481, 18450, 2192]; n_keys(1) (ANTI), KEY(BCTL.BANK_NO=T.CUST_ACCT) KEY_NULL_EQU(0)
10 #CSCN2: [1, 91, 96]; INDEX33556888(aaaa_PF_BCTL as BCTL)
11 #PRJT2: [474, 18668, 2192]; exp_num(46), is_atom(FALSE)
12 #HASH RIGHT JOIN2: [474, 18668, 2192]; key_num(1), ret_null(0), KEY(C.BRCHNO=B.REF_BRCHNO)
13 #CSCN2: [1, 10, 96]; INDEX33557020(aaaa_BRCH_DEPT_RELA as C)
14 #HASH2 INNER JOIN: [467, 18668, 2192]; LKEY_UNIQUE KEY_NUM(1); KEY(B.CUST_NO=A.CUST_NO) KEY_NULL_EQU(0)
15 #SLCT2: [3, 7697, 288]; B.REF_BRCHNO = '756888'
16 #CSCN2: [3, 20145, 288]; INDEX33556892(aaaa_PF_COM_SIGNINFO as B)
17 #SLCT2: [450, 36644, 1904]; exp11 > 0
18 #CSCN2: [450, 732888, 1904]; INDEX33556914(aaaa_TX_APPLY as A)

#############sample 3.6 经典解析注释符和行号

这样的sql 在原有2个组合索引，发现不能提高sql 执行效率，新建一个单列索引才能提高SQL 执行效率，如此情况在oracle 不存在，为此特意写出此文档
Step1 加入注释符和行号

SELECT count(distinct(QM.queueno))
FROM CQ_QM_QUEUESERIAL_LOG QM, CQ_PARA_WINDOW_INFO QW, CQ_PARA_CALLRULE_INFO RU
WHERE QW.RuleID = RU.RuleID --1
AND (QM.BsID = RU.BsID -2
AND QM.QueueTpID = RU.QueueTpID or QM.QueueCallTp = '2') --3
AND QM.Brno = QW.Brno --4
AND QM.QueueTpStatus = '0' --5
AND (QM.QueueCallTp in ('1','2')) --6
AND QW.Brno = '769001' --7
AND QW.WinNo = '2' --8
and (qm.WinNo is null or (qm.WinNo = '2' and qm.QueueCallTp = '2')) --9
AND QM.WorkDate ='20231124' --10
AND QueueCallTime is null --11

通过黄线标准，执行步骤在执行段的位置（上图手工标注的行）

44 #HASH RIGHT SEMI JOIN2: [15, 1, 744]; n_keys(1) KEY(DMTEMPVIEW_16826789.colname='2') KEY_NULL_EQU(0)
45 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
1,2,3 #HASH2 INNER JOIN: [15, 1, 744]; KEY_NUM(3); KEY(QM.QUEUETPID=RU.QUEUETPID AND QW.RULEID=RU.RULEID AND exp_cast(QM.BSID)=exp_cast(RU.BSID)-var5) KEY_NULL_EQU(0, 0, 0)
47 #SLCT2: [15, 1, 744]; exp_cast(QM.BSID) = exp_cast(RU.BSID)-var4
48 #NEST LOOP INDEX JOIN2: [15, 1, 744]
49 #ACTRL: [15, 1, 744];
50 #NEST LOOP INDEX JOIN2: [15, 1, 592]
9,11,9,,5,4,7 #SLCT2: [15, 1, 440]; (QM.QUEUECALLTIME IS NULL AND QM.WINNO = '2' AND QM.QUEUECALLTP = '2' AND QM.QUEUETPSTATUS = '0' AND QM.WORKDATE = '20231124' AND QM.BRNO = '769001') -》->这里执行了9,11,9,,5,4,7 步
52 #CSCN2: [15, 74094, 440]; INDEX33556329(CQ_QM_QUEUESERIAL_LOG as QM)
53 #BLKUP2: [1, 1, 96]; INDEX33555760(QW)
7,8 #SSEK2: [1, 1, 96]; scan_type(ASC), INDEX33555760(CQ_PARA_WINDOW_INFO as QW), scan_range[('769001','2'),('769001','2')]
55 #SSEK2: [1, 1, 144]; scan_type(ASC), INDEX33555752(CQ_PARA_CALLRULE_INFO as RU), scan_range[(QW.RULEID,QM.QUEUETPID,min),(QW.RULEID,QM.QUEUETPID,max))
1 #SSCN: [1, 187, 152]; INDEX33555752(CQ_PARA_CALLRULE_INFO as RU)

Step 2: 打开et 报告看看
SQL>set linesize 999
SQL> SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
DMSQL executed successfully
used time: 6.436(ms). Execute id is 12738800.
SQL> set autotrace trace
SQL> SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
DMSQL executed successfully
SQL> runing sql
used time: 153.462(ms). Execute id is 25625305.
call et(25625305);

9,11,9,,5,4,7 49 #SLCT2: [14, 1->0, 392];
50 #CSCN2: [14, 74094->74130, 392]; INDEX33556329(CQ_QM_QUEUESERIAL_LOG)

LINEID OP TIME(US) PERCENT RANK SEQ N_ENTER HASH_USED_CELLS
---------- ----- -------------------- ------- -------------------- ----------- ----------- --------------------
HASH_CONFLICT
--------------------
29 CSCN2 40214 26.29% 2 37 249 0
0

30 CSCN2 40260 26.32% 1 50 249 0
0

--检查条件的查询效率
SELECT count(*)
FROM dasd.CQ_QM_QUEUESERIAL_LOG QM where QM.WORKDATE = '20231124'
10063

SELECT count(*)
FROM dasd.CQ_QM_QUEUESERIAL_LOG QM where QM.BRNO = '769001'
0

（根据专家描述：看结果集记录数的评估值，两种可能：
1?统计数据不准确，
2?索引不够理想，不能尽量筛选到比较少的记录，
3.索引扫描效率很高，但索引结果集再做回表和过滤则会慢，所以索引结果集尽量小）

结论如下：
-- 头2个索引是oracle 自带的，后一个是新建的
CREATE INDEX "IDX_CQ_QM_QUEUESERIAL_LOG" ON "dasd"."CQ_QM_QUEUESERIAL_LOG"("WORKDATE" ASC,"BRNO" ASC,"QUEUENO" ASC) STORAGE(ON "MIQS_IDX", CLUSTERBTR) ;
CREATE INDEX "IDX_CQ_QM_QUEUESERIAL_LOG_1" ON "dasd"."CQ_QM_QUEUESERIAL_LOG"("BSID" ASC) STORAGE(ON "MIQS_IDX", CLUSTERBTR) ;
CREATE INDEX "IDX_CQ_QM_QUEUESERIAL_LOG_2" ON "dasd"."CQ_QM_QUEUESERIAL_LOG"("WORKDATE" ASC) STORAGE(ON "MIQS_IDX", CLUSTERBTR) ;

#########sample 4 各个执行计划的含义

https://eco.dameng.com/document/dm/zh-cn/ops/performance-optimization

https://eco.dameng.com/document/dm/zh-cn/pm/sql-tuning

性能优化

一、前言

1.1 概述

性能优化是指在不影响系统运行正确性的前提下，使之运行地更快，完成特定功能所需的时间更短。达梦数据库性能优化主要包含以下内容：

数据库架构优化
数据库参数优化
SQL 优化
统计信息

1.2 工具与术语

数据库性能优化中可能使用到的相关工具：

达梦 SQL 日志分析工具 DMLOG：通过分析数据库的 SQL 日志文件，直观地反映 SQL 执行情况。
命令行调试工具 dmdbg：DM 数据库安装目录的“bin”子目录下可找到 dmdbg 执行程序，可调试直接执行的 DMSQL 程序或非 DDL 的 SQL 语句。
DM 性能监控工具 Monitor：Monitor 是 DM 系统管理员用来监视服务器的活动和性能情况的客户端工具。它允许系统管理员在本机或远程监控服务器的运行状况，并根据系统情况对系统参数进行调整，以提高系统效率。

数据库性能优化中可能使用到的相关术语：

通配符：通配符是一种特殊语句，主要有星号 (*) 和问号 (?)，用来模糊搜索文件。
回滚段 (rollback segments)：用于临时存储数据库还原信息。
统计信息：对象统计信息描述了对象数据的分布特征。统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。
CBO（基于代价的优化器）：它是看语句的代价，这里的代价主要指 cpu 和内存。优化器在判断是否用这种方式时，主要参照的是表及索引的统计信息，统计信息给出表的大小、多少行、每行的长度等信息。
执行计划：执行计划是一条 SQL 语句在数据库中的执行过程或访问路径的描述。

1.3 适用范围

本文中所涉及内容适用于 DM7 及 DM8 版本数据库产品。

二、数据库架构优化

达梦数据库提供多种数据库架构，用于解决多种场景的数据库安全、性能等问题。根据业务的特性，选择合适的数据库架构非常必要。

数据库架构	架构介绍	架构特性
数据守护集群 DMDataWatch	DMDataWatch 是一种高可用数据库解决方案，主备节点间通过日志同步来保证数据的同步，可以实现数据库快速切换与灾难性恢复，满足用户对数据安全性和高可用性的需求，提供不间断的数据库服务。	实时保证数据完全一致，备库支持临时表，故障秒级切换。
读写分离集群 DMRWC	DMRWC 在保障主库和备库事务强一致的前提下，开创性地在接口层（JDBC、DPI 等）将只读操作自动分流到备库，有效降低主库的负载，提升系统吞吐量，适用于读多写少的业务场景。	事务自动分发，OA 办公系统的最佳选择，高可用性。
数据共享集群 DMDSC	DMDSC 是一个多实例、单数据库的系统。主要由数据库和数据库实例、共享存储、本地存储、通信网络、以及集群控制软件 DMCSS 组成，允许多个数据库实例同时访问、获得完整的数据库服务。	金融级高可用，自动负载均衡，应用高效迁移，高性能存储管理，企业级容灾，全面支持国产平台。
大规模并行处理集群 DMMPP	DMMPP 用于解决海量数据存储和处理，满足高并发、高性价比、高性能的需求，提供高端数据仓库解决方案，以极低的成本代价，为客户提供业界领先的计算性能。	同时支持行存储和列存储引擎充，充分利用硬件资源，数据分布方式灵活多样。
新一代分布式集群 DMDPC	DMDPC 同时支持在线分析处理和在线事务处理，具备高可用、高扩展、高性能、高吞吐量、继承了 DM8 良好的兼容性，应用无需改造即可迁移到 DMDPC。	高可用，高可扩展，高性能，高吞吐量，透明易用。

三、数据库参数优化

3.1 INI 参数配置说明

参数	含义	优化建议
MEMORY_POOL	共享内存池大小，以 M 为单位。	高并发时应调大，避免频繁向 OS 申请内存。
MEMORY_N_POOLS	共享内存池个数，减少内存临界区冲突。	设置较大会导致启动时报错申请内存失败。
BUFFER	系统缓冲区大小，以 M 为单位。	如果数据量小于内存，则设置为数据量大小；否则设置为总内存的 2/3 比较合适。
BUFFER_POOLS	BUFFER 系统分区数，有效值范围（1~512），当 MAX_BUFFER>BUFFER 时，动态扩展的缓冲区不参与分区。	并发较大的系统需要配置该参数，减少数据缓冲区并发冲突，建议 BUFFER=MAX_BUFFER。
RECYCLE	RECYCLE 缓冲区大小，以 M 为单位。	高并发或大量使用 with、临时表、排序等时，可以将值调大。
DICT_BUF_SIZE	字典缓冲区大小，以 M 为单位。	如果数据库中对象数量较多，或者存在大量分区表，可适当调大。
HJ_BUF_GLOBAL_SIZE	HASH 连接操作符的数据总缓存大小（>= HJ_BUF_SIZE），系统级参数，以 M 为单位。	内存足够的情况下，可以适当调大。实际使用大小，由包含 HASH JOIN 操作符的 SQL 并发数决定。
HJ_BUF_SIZE	单个 HASH 连接操作符的数据总缓存大小，以 M 为单位。	在 OLTP 环境中，建议采用默认值。在 OLAP 环境下，可以根据参与 HASH JOIN 的数据量调大。
HAGR_BUF_GLOBAL_SIZE	HAGR、DIST、集合操作、SPL2、NTTS2 以及 HTAB 操作符的数据总缓存大小（>= HAGR_BUF_SIZE），系统级参数，以 M 为单位。	高并发、大量的聚集操作如 sum 等，可适当调大。
HAGR_BUF_SIZE	单个 HAGR、DIST、集合操作、SPL2、NTTS2 以及 HTAB 操作符的数据总缓存大小，以 M 为单位。	监控 V$SORT_HISTORY，判断是否需要调整，有大表的 hash 分组应调大。
WORKER_THREADS	工作线程的数目。有效值范围（1~64）	建议设置为 cpu 核数或其两倍
ENABLE_MONITOR	用于打开或者关闭系统的监控功能。1：打开；0：关闭。	性能优化是设置为 3，运行时设置为 2。
OLAP_FLAG	启用联机分析处理。0：不启用；1：启用；2：不启用，同时倾向于使用索引范围扫描。	该参数会影响到计划的生成。在 OLTP 环境下，通常保持默认值 2。
SORT_BUF_SIZE	原排序机制下，排序缓存区最大值，以 M 为单位。	建索引时可以适当调大，通常不超过 20M。
TOP_ORDER_OPT_FLAG	优化带有 TOP 和 ORDER BY 子句的查询，使得 SORT 操作符可以省略。	优化的效果是尽量使得 ORDER BY 的排序列所对应的基表可以使用包含排序列的索引，从而可以移除排序 SORT 操作符，减少排序操作。如果排序列不属于同一个基表，或者排序列不是基表列，则无法进行优化。

3.2 参数修改方法

参数分为：静态、动态、手动。如下表所示：

参数	描述
静态	可以被动态修改，需重启服务器生效。
动态	可以被动态修改，修改后即时生效；动态分为会话级和系统级；会话级：新参数值只影响新创建的会话，之前的会话不受影响；系统级：修改后会影响所有会话。
手动	不能动态修改，只能修改 dm.ini 然后重启。

3.2.1 调用系统过程

1. 参数查询

（1）查询数值类型参数值。

Copy--语法格式：select SF_GET_PARA_VALUE (scope int, paraname varchar(256));--SCOPE 参数为1表示获取INI 文件中配置参数的值--SCOPE 参数为2表示获取内存中配置参数的值

--例如：获取DM.INI 文件中动态参数HFS_CACHE_SIZE的当前值select SF_GET_PARA_VALUE (1,'HFS_CACHE_SIZE');

（2）查询浮点型参数值。

Copy--语法格式select SF_GET_PARA_DOUBLE_VALUE(scope int, paraname varchar(8187));--SCOPE 参数为1表示获取INI 文件中配置参数的值--SCOPE 参数为2表示获取内存中配置参数的值

--例如：获取DM.INI 文件中动态参数SEL_RATE_EQU的当前值select SF_GET_PARA_DOUBLE_VALUE(2,'SEL_RATE_EQU');

（3）查询字符串类型参数值。

Copy--语法格式select SF_GET_PARA_STRING_VALUE(scope int, paraname varchar(8187));--SCOPE 参数为1表示获取INI 文件中配置参数的值--SCOPE 参数为2表示获取内存中配置参数的值

--例如：获取DM.INI 文件中动态参数SQL_TRACE_MASK的当前值select SF_GET_PARA_STRING_VALUE(1,'SQL_TRACE_MASK');

（4）获得当前会话的某个会话级 INI 参数的值。

Copy--语法格式select SF_GET_SESSION_PARA_VALUE (paraname varchar(8187));--例如：获取当前会话USE_HAGR_FLA参数的值select SF_GET_SESSION_PARA_VALUE ('USE_HAGR_FLAG');

2. 参数修改

（1）修改整型静态配置参数和动态配置参数。

Copy--语法格式

SP_SET_PARA_VALUE (scope int, paraname varchar(256), value int64);--SCOPE参数为1表示在内存和INI文件中都修改参数值，此时只能修改动态的配置参数。当SCOPE等于1，试图修改静态配置参数时服务器会返回错误信息--SCOPE参数为2表示只在 INI 文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数

--例如：将 DM.INI 文件中动态参数 HFS_CACHE_SIZE 设置为 320，在 disql 中执行以下命令即可立即生效

SP_SET_PARA_VALUE (1,'HFS_CACHE_SIZE',320);

（2）修改浮点型静态配置参数和动态配置参数。

Copy--语法格式

SP_SET_PARA_DOUBLE_VALUE(scope int,paraname varchar(8187),value double);--SCOPE参数为1表示在内存和INI文件中都修改参数值，此时只能修改动态的配置参数。当SCOPE等于1，试图修改静态配置参数时服务器会返回错误信息--SCOPE参数为2表示只在 INI 文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数

--例如：将 DM.INI 文件中动态参数 SEL_RATE_EQU 设置为 0.3，在 disql 中执行以下命令即可立即生效

SP_SET_PARA_DOUBLE_VALUE(1, 'SEL_RATE_EQU', 0.3);

（3）修改系统整型、double、 varchar 的静态配置参数或动态配置参数。

Copy--语法格式

SF_SET_SYSTEM_PARA_VALUE(paraname varchar(256),value int64\double\varchar(256),deferred int,scope int64);--DEFERRED参数为0表示当前session修改的参数立即生效，默认为0--DEFERRED参数为1表示当前 session不生效，后续再生效--SCOPE参数为1表示在内存和INI文件中都修改参数值，此时只能修改动态的配置参数--SCOPE参数为2表示只在INI文件中修改配置参数，此时可用来修改静态配置参数和动态配置参数

--例如：将动态参数ENABLE_DDL_ANY_PRIV设置为1，且当前session立即生效select SF_SET_SYSTEM_PARA_VALUE('ENABLE_DDL_ANY_PRIV',1,0,1);

（4）修改某个会话级 INI 参数的值，设置的参数值只对本会话有效。

Copy--语法格式

SF_SET_SESSION_PARA_VALUE (paraname varchar(8187), value bigint);--例如：将USE_HAGR_FLAG设置为1，且只对本会话有效select SF_SET_SESSION_PARA_VALUE ('USE_HAGR_FLAG',1);

（5）重置某个会话级 INI 参数的值，使得这个 INI 参数的值和系统 INI 参数的值保持一致。

Copy--语法格式

SP_RESET_SESSION_PARA_VALUE (paraname varchar(8187));--例如：重置USE_HAGR_FLAGselect SP_RESET_SESSION_PARA_VALUE ('USE_HAGR_FLAG');

3.2.2 ALTER 命令修改

修改系统参数：

Copy--语法格式ALTER SYSTEM SET ‘<参数名称>’ =<参数值> [DEFERRED] [MEMORY|BOTH|SPFILE];

--静态参数修改ALTER SYSTEM SET ‘MTAB_MEM_SIZE’ =1200 spfile;--PURGE关键字指是否清理执行计划ALTER SESSION SET ‘<参数名称>’ =<参数值> [PURGE];--修改当前会话参数ALTER SESSION SET ‘HAGR_HASH_SIZE’ =2000000;

3.2.3 修改 INI 文件

dm.ini 文件一般情况下位于数据库实例路径下，可以通过 vi dm.ini 命令修改。修改完成后，可以通过 v$dm_ini 或者 v$parameter 查询参数值。例如：

Copy--查询v$dm_iniselect * from v$dm_ini where para_name LIKE 'PK_WITH%';--查询v$parameterselect * from v$parameter where name LIKE 'PK_WITH%'

四、SQL 优化

4.1 定位慢 SQL

定位执行效率低的 SQL 语句是 SQL 优化的第一步。待优化的 SQL 可大致分为两类：

SQL 执行时间在十几秒到数十秒之间，但执行频率不高，此类 SQL 对数据库整体性能影响并不大，可以放到最后进行优化。
SQL 单独执行时间可能很快，在几百毫秒到几秒之间，但执行频率非常高，甚至达到每秒上百次，高并发下执行效率降低，很可能导致系统瘫痪，此类 SQL 是优化的首要对象。

以下介绍两种定位慢 SQL 的方法，可记录下具体 SQL 语句以及对应执行时间，为后续 SQL 优化工作奠定基础。

4.1.1 开启跟踪日志记录

跟踪日志文件是一个纯文本文件，以”dmsql_实例名_日期_时间命名.log”，默认生成在 DM 安装目录的 log 子目录下。跟踪日志内容包含系统各会话执行的 SQL 语句、参数信息、错误信息、执行时间等。跟踪日志主要用于分析错误和分析性能问题，基于跟踪日志可以对系统运行状态进行分析。

1. 跟踪日志记录配置

（1）配置 dm.ini 文件，设置 SVR_LOG = 1 以启用 sqllog.ini 配置，该参数为动态参数，可通过调用数据库函数直接修改，如下所示：

CopySP_SET_PARA_VALUE(1,'SVR_LOG',1);

（2）配置数据文件目录下的 sqllog.ini 文件。

Copy[dmdba@localhost DAMENG]$ cat sqllog.ini

BUF_TOTAL_SIZE = 10240 #SQLs Log Buffer Total Size(K)(1024~1024000)

BUF_SIZE = 1024 #SQLs Log Buffer Size(K)(50~409600)

BUF_KEEP_CNT = 6 #SQLs Log buffer keeped count(1~100)

[SLOG_ALL]

FILE_PATH = ../log

PART_STOR = 0

SWITCH_MODE = 1

SWITCH_LIMIT = 100000

ASYNC_FLUSH = 0

FILE_NUM = 200

ITEMS = 0

SQL_TRACE_MASK = 2:3:23:24:25

MIN_EXEC_TIME = 0

USER_MODE = 0

USERS =

注意

为避免记录 SQL log 对服务器产生较大的影响，可以配置异步日志刷新（参数 ASYNC_FLUSH 设置为 1）。

（3）如果对 sqllog.ini 进行了修改，可通过调用以下函数即时生效，无需重启数据库，如下所示：

CopySP_REFRESH_SVR_LOG_CONFIG();

（4）各配置项详细说明如下表所示：

参数名	缺省值	说明
SQL_TRACE_MASK	1	LOG 记录的语句类型掩码，是一个格式化的字符串，表示一个 32 位整数上哪一位将被置为 1，置为 1 的位则表示该类型的语句要记录，格式为：号:位号:位号。如：3:5:7 表示第 3，第 5，第 7 位上的值被置为 1。每一位的含义见下面说明（2~17 前提是:SQL 标记位 24 也要设置)： 1 全部记录（全部记录并不包含原始语句） 2 全部 DML 类型语句 3 全部 DDL 类型语句 4 UPDATE 类型语句（更新） 5 DELETE 类型语句（删除） 6 INSERT 类型语句（插入） 7 SELECT 类型语句（查询） 8 COMMIT 类型语句（提交） 9 ROLLBACK 类型语句（回滚） 10 CALL 类型语句（过程调用） 11 BACKUP 类型语句（备分） 12 RESTORE 类型语句（恢复） 13 创建对象操作 (CREATE DDL) 14 修改对象操作 (ALTER DDL) 15 删除对象操作 (DROP DDL) 16 授权操作 (GRANT DDL) 17 回收操作 (REVOKE DDL) 22 绑定参数 23 存在错误的语句（语法错误，语义分析错误等） 24 是否需要记录执行语句 25 是否需要打印计划和语句和执行的时间 26 是否需要记录执行语句的时间 27 原始语句（服务器从客户端收到的未加分析的语句） 28 是否记录参数信息，包括参数的序号、数据类型和值 29 是否记录事务相关事件
FILE_NUM	0	总共记录多少个日志文件，当日志文件达到这个设定值以后，再生成新的文件时，会删除最早的那个日志文件，日志文件的命令格式为 dmsql_实例名_日期时间.log。当这个参数配置成 0 时，只会生成两个日志相互切换着记录。有效值范围（0~1024）。例如，当 FILE_NUM=0，实例名为 PDM 时，根据当时的日期时间，生成的日志名称为：DMSQL_PDM_20180719_163701.LOG， DMSQL_PDM_20180719_163702.LOG
SWITCH_MODE	0	表示 SQL 日志文件切换的模式： 0：不切换 1：按文件中记录数量切换 2：按文件大小切换 3：按时间间隔切换
SWITCH_LIMIT	100000	不同切换模式 SWITCH_MODE 下，意义不同：按数量切换时，一个日志文件中的 SQL 记录条数达到多少条之后系统会自动将日志切换到另一个文件中。一个日志文件中的 SQL 记录条数达到多少条之后系统会自动将日志切换到另一个文件中。有效值范围（1000-10000000）按文件大小切换时，一个日志文件达到该大小后，系统自动将日志切换到另一个文件中，单位为 MB。有效值范围（1-2000）按时间间隔切换时，每个指定的时间间隔，按文件新建时间进行文件切换，单位为分钟。有效值范围（1-30000）
ASYNC_FLUSH	0	是否打开异步 SQL 日志功能。 0：表示关闭； 1：表示打开
MIN_EXEC_TIME	0	详细模式下，记录的最小语句执行时间，单位为毫秒。执行时间小于该值的语句不记录在日志文件中。有效值范围（0-4294967294）
FILE_PATH	../log	日志文件所在的文件夹路径
BUF_TOTAL_SIZE	10240	SQL 日志 BUFFER 占用空间的上限，单位为 KB，取值范围（1024-1024000）
BUF_SIZE	1024	一块 SQL 日志 BUFFER 的空间大小，单位为 KB，取值范围（50-09600）
BUF_KEEP_CNT	6	系统保留的 SQL 日志缓存的个数，有效值范围（1-100）
PART_STOR	0	SQL 日志分区存储，表示 SQL 日志进行分区存储的划分条件。 0 表示不划分； 1 表示 USER：根据不同用户分布存储
ITEMS	0	配置 SQL 日志记录中的那些列要被记录。该参数是一个格式化的字符串，表示一个记录中的那些项目要被记录，格式为：列号:列号:列号。如：3:5:7 表示第 3，第 5，第 7 列要被记录。0 表示记录所有的列。 1 TIME 执行的时间 2 SEQNO 服务器的站点号 3 SESS 操作的 SESS 地址 4 USER 执行的用户 5 TRXID 事务 ID 6 STMT 语句地址 7 APPNAME 客户端工具 8 IP 客户端 IP 9 STMT_TYPE 语句类型 10 INFO 记录内容 11 RESULT 运行结果，包括运行用时和影响行数（可能没有）
USER_MODE	0	SQL 日志按用户过滤时的过滤模式，取值 0：关闭用户过滤 1：白名单模式，只记录列出的用户操作的 SQL 日志 2：黑名单模式，列出的用户不记录 SQL 日志
USERS	空串	打开 USER_MODE 时指定的用户列表。格式为：用户名：用户名：用户名

2. 查询方法

sqllog.ini 文件配置成功后可在 dmsql 指定目录下生成 dmsql 开头的 log 日志文件。日志内容如下所示：

上图中选中记录执行 SQL 语句如下所示，SQL 语句执行时间为 33.815 秒。

Copyselect * from t1 left join t2 on t1.c1=t2.c1 and t1.c1=999933;

可以通过正则表达式在 dmsql 日志文件中查找执行时间超过一定阈值的 SQL 语句。例如：查找执行时间超过 10 秒的 SQL 语句。

Copy[1-9][0-9][0-9][0-9][0-9](ms)

如需进行更为系统全面的分析，可使用 DMLOG 工具进行分类汇总。

4.1.2 通过系统视图查看

DM 数据库提供系统动态视图，可自动记录执行时间超过设定阈值的 SQL 语句。

1. SQL 记录配置

当 INI 参数 ENABLE_MONITOR=1、MONITOR_TIME=1 打开时，显示系统最近 1000 条执行时间超过预定值的 SQL 语句，默认预定值为 1000 毫秒。

以上两个参数可通过 SP_SET_PARA_VALUE 系统函数修改，通过 SF_GET_PARA_VALUE 系统函数查看当前值。

Copy--修改参数值

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);

SP_SET_PARA_VALUE(1,'MONITOR_TIME',1);

--查看参数值select SF_GET_PARA_VALUE(1,'ENABLE_MONITOR');select SF_GET_PARA_VALUE(1,'MONITOR_TIME');

注意

两个参数均为动态参数，可直接调用系统函数进行修改，无须重启数据库实例服务；
通过 SP_SET_PARA_VALUE 方式修改的参数值仅对当前会话以及新建会话生效，对其它已建立会话不生效。

2. 查询方式

（1）查询当前正在执行的会话信息。

CopySELECT * FROM (SELECT 'SP_CLOSE_SESSION('||SESS_ID||');' AS CLOSE_SESSION,

DATEDIFF(SS,LAST_SEND_TIME,SYSDATE) sql_exectime,

TRX_ID,

CLNT_IP,

B.IO_WAIT_TIME AS IO_WAIT_TIME,

SF_GET_SESSION_SQL(SESS_ID) FULLSQL,

A.SQL_TEXT

FROM V$SESSIONS a,V$SQL_STAT B WHERE STATE IN ('ACTIVE','WAIT')

AND A.SESS_ID = B.SESSID

)

SQL_TEXT 列记录的是部分 SQL 语句；FULLSQL 列存储了完整的执行 SQL 语句。

（2）查询超过执行时间阈值的 SQL 语句。

可通过查询 V$LONG_EXEC_SQLS 系统视图获取结果：

CopySELECT * FROM V$LONG_EXEC_SQLS;

查询结果字段详细信息介绍如下表所示：

列名	说明
SESS_ID	会话 ID，会话唯一标识
SQL_ID	语句 ID，语句唯一标识
SQL_TEXT	SQL 文本
EXEC_TIME	执行时间（毫秒）
FINISH_TIME	执行结束时间
N_RUNS	执行次数
SEQNO	编号
TRX_ID	事务号

4.2 SQL 分析方法

4.2.1 执行计划

1. 概述

简单来说，执行计划就是一条 SQL 语句在数据库中的执行过程或访问路径的描述。SQL 语言是种功能强大且非过程性的编程语言，比如以下这条 SQL 语句：

CopySELECT * FROM T1, T2 WHERE T1.ID = T2.ID AND T1.ID = 6;

开发人员只关心 SQL 语句能否返回 T1 与 T2 表的关联查询结果，不需要指定该 SQL 如何执行，也就是说不关心该 SQL 是先访问 T1 表还是先访问 T2 表。对于 SQL 来说，两种访问方式就是两个执行计划，查询优化器 (CBO) 将根据代价也就是开销来选择最优的执行计划。以如下 SQL 语句执行计划为例：

CopySELECT * FROM SYSOBJECTS;

1 #NSET2: [0, 1282, 396]2 #PRJT2: [0, 1282, 396]; exp_num(17), is_atom(FALSE)3 #CSCN2: [0, 1282, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

执行计划的每行即为一个计划节点，主要包含三部分信息。

第一部分 NEST2、PRJT2、CSCN2 为操作符及数据库具体执行了什么操作。
第二部分的三元组为该计划节点的执行代价，具体含义为[代价，记录行数，字节数]。
第三部分为操作符的补充信息。

例如：第三个计划节点表示操作符是 CSCN2（即全表扫描），代价估算是 0 ms，扫描的记录行数是 1282 行，输出字节数是 396 个。

各计划节点的执行顺序为：缩进越多的越先执行，同样缩进的上面的先执行，下面的后执行，上下的优先级高于内外。缩进最深的，最先执行；缩进深度相同的，先上后下。口诀：最右最上先执行。

Copy#CSCN2: [1, 2, 12]; INDEX33555496(TEST)

操作符，[代价，行数，字节数] 描述

2. 查看执行计划

达梦数据库可通过两种方式查看执行计划。

方式一：通过 DM 数据库配套管理工具查看。

方式二：使用 explain 命令查看。

以下对两种查看方式进行介绍。

（1）管理工具查看执行计划

在 DM 配套管理工具中，选中待查看执行计划的 SQL 语句，点击工具栏中的按钮，或使用快捷键 F9，即可查看执行计划。

（2）使用 explain 命令查看执行计划

在待查看执行计划的 SQL 语句前加 explain 执行 SQL 语句即可查看执行计划：

Copyexplain select * from sysobjects;

--执行计划1 #NSET2: [1, 986, 396] 2 #PRJT2: [1, 986, 396]; exp_num(17), is_atom(FALSE) 3 #CSCN2: [1, 986, 396]; SYSINDEXSYSOBJECTS(SYSOBJECTS as SYSOBJECTS)

4.2.2 常见操作符解读

下面通过几个例子来介绍一些常见操作符。准备测试表及数据如下：

CopyDROP TABLE T1;DROP TABLE T2;CREATE TABLE T1(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );CREATE TABLE T2(C1 INT ,C2 CHAR(1),C3 VARCHAR(10) ,C4 VARCHAR(10) );INSERT INTO T1SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL FROM DUALCONNECT BY LEVEL<=10000;INSERT INTO T2SELECT LEVEL C1,CHR(65+MOD(LEVEL,57)) C2,'TEST',NULL FROM DUALCONNECT BY LEVEL<=10000;CREATE INDEX IDX_C1_T1 ON T1(C1);

SP_INDEX_STAT_INIT(USER,'IDX_C1_T1');

1. NSET：结果集收集

CopyEXPLAIN SELECT * FROM T1;

1 #NSET2: [1, 10000, 156]2 #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)3 #CSCN2: [1, 10000, 156]; INDEX33556710(T1)

NSET 是用于结果集收集的操作符，一般是查询计划的顶层节点，优化工作中无需对该操作符过多关注，一般没有优化空间。

2. PRJT：投影

CopyEXPLAIN SELECT * FROM T1;

1 #NSET2: [1, 10000, 156]2 #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)3 #CSCN2: [1, 10000, 156]; INDEX33556710(T1)

PRJT 是关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。优化工作中无需对该操作符过多关注，一般没有优化空间。

3. SLCT：选择

CopyEXPLAIN SELECT * FROM T1 WHERE C2='TEST';

1 #NSET2: [1, 250, 156]2 #PRJT2: [1, 250, 156]; exp_num(5), is_atom(FALSE)3 #SLCT2: [1, 250, 156]; T1.C2 = TEST4 #CSCN2: [1, 10000, 156]; INDEX33556717(T1)

SLCT 是关系的【选择】运算，用于查询条件的过滤。可比较返回结果集与代价估算中是否接近，如相差较大可考虑收集统计信息。若该过滤条件过滤性较好，可考虑在条件列增加索引。

4. AAGR：简单聚集

CopyEXPLAIN SELECT COUNT(*) FROM T1 WHERE C1 = 10;

1 #NSET2: [0, 1, 4]2 #PRJT2: [0, 1, 4]; exp_num(1), is_atom(FALSE)3 #AAGR2: [0, 1, 4]; grp_num(0), sfun_num(1)4 #SSEK2: [0, 1, 4]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

AAGR 用于没有 GROUP BY 的 COUNT、SUM、AGE、MAX、MIN 等聚集函数的计算。

5. FAGR：快速聚集

CopyEXPLAIN SELECT MAX(C1) FROM T1;

1 #NSET2: [1, 1, 0]2 #PRJT2: [1, 1, 0]; exp_num(1), is_atom(FALSE)3 #FAGR2: [1, 1, 0]; sfun_num(1)

FAGR 用于没有过滤条件时，从表或索引快速获取 MAX、MIN、COUNT 值。

6. HAGR：HASH 分组聚集

CopyEXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C2;

1 #NSET2: [1, 100, 48]2 #PRJT2: [1, 100, 48]; exp_num(1), is_atom(FALSE)3 #HAGR2: [1, 100, 48]; grp_num(1), sfun_num(1)4 #CSCN2: [1, 10000, 48]; INDEX33556717(T1)

HAGR 用于分组列没有索引只能走全表扫描的分组聚集，该示例中 C2 列没有创建索引。

7. SAGR：流分组聚集

CopyEXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;

1 #NSET2: [1, 100, 4]2 #PRJT2: [1, 100, 4]; exp_num(1), is_atom(FALSE)3 #SAGR2: [1, 100, 4]; grp_num(1), sfun_num(1)4 #SSCN: [1, 10000, 4]; IDX_C1_T1(T1)

SAGR 用于分组列是有序的情况下，可以使用流分组聚集，C1 列上已经创建了索引，SAGR2 性能优于 HAGR2。

8. BLKUP：二次扫描 (回表)

CopyEXPLAIN SELECT * FROM T1 WHERE C1=10;

1 #NSET2: [0, 1, 156]2 #PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)3 #BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)4 #SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

BLKUP 先使用二级索引索引定位 rowid，再根据表的主键、聚集索引、rowid 等信息获取数据行中其它列。

9. CSCN：全表扫描

CopyEXPLAIN SELECT * FROM T1;

1 #NSET2: [1, 10000, 156]2 #PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)3 #CSCN2: [1, 10000, 156]; INDEX33556710(T1)

10. SSEK、CSEK、SSCN：索引扫描

Copy-- 创建所需索引CREATE CLUSTER INDEX IDX_C1_T2 ON T2(C1);CREATE INDEX IDX_C1_C2_T1 ON T1(C1,C2);

（1）SSEK

CopyEXPLAIN SELECT * FROM T1 WHERE C1=10;

1 #NSET2: [0, 1, 156]2 #PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)3 #BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)4 #SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]

SSEK2 是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid 等信息去扫描表。

（2）CSEK

CopyEXPLAIN SELECT * FROM T2 WHERE C1=10;

1 #NSET2: [0, 250, 156]2 #PRJT2: [0, 250, 156]; exp_num(5), is_atom(FALSE)3 #CSEK2: [0, 250, 156]; scan_type(ASC), IDX_C1_T2(T2), scan_range[10,10]

CSEK2 是聚集索引扫描只需要扫描索引，不需要扫描表，即无需 BLKUP 操作，如果 BLKUP 开销较大时，可考虑创建聚集索引。

（3）SSCN

CopyEXPLAIN SELECT C1,C2 FROM T1;

1 #NSET2: [1, 10000, 60]2 #PRJT2: [1, 10000, 60]; exp_num(3), is_atom(FALSE)3 #SSCN: [1, 10000, 60]; IDX_C1_C2_T1(T1)

SSCN 是索引全扫描，不需要扫描表。

11. NEST LOOP：嵌套循环连接

嵌套循环连接是最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。驱动表的行数就是循环的次数，将在很大程度上影响执行效率。

连接列是否有索引，都可以走 NEST LOOP，但没有索引，执行效率会很差，语句如下所示：

Copyselect /*+use_nl(t1,t2)*/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';1 #NSET2: [17862, 24725, 296] 2 #PRJT2: [17862, 24725, 296]; exp_num(8), is_atom(FALSE) 3 #SLCT2: [17862, 24725, 296]; T1.C1 = T2.C14 #NEST LOOP INNER JOIN2: [17862, 24725, 296]; 5 #SLCT2: [1, 250, 148]; T1.C2 = 'A'6 #CSCN2: [1, 10000, 148]; INDEX33555594(T1)7 #CSCN2: [1, 10000, 148]; INDEX33555595(T2)

可针对 T1 和 T2 的连接列创建索引，并收集统计信息，语句如下所示：

CopyCREATE INDEX IDX_T1_C2 ON T1(C2);CREATE INDEX IDX_T2_C1 ON T2(C1);

DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T1_C2');

DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T2_C1');

再次查看执行计划可看出效率明显改善，代价有显著下降，语句如下所示：

Copyselect /*+use_nl(t1,t2)*/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';1 #NSET2: [9805, 17151, 296] 2 #PRJT2: [9805, 17151, 296]; exp_num(8), is_atom(FALSE) 3 #SLCT2: [9805, 17151, 296]; T1.C1 = T2.C14 #NEST LOOP INNER JOIN2: [9805, 17151, 296]; 5 #BLKUP2: [1, 175, 148]; IDX_T1_C2(T1)6 #SSEK2: [1, 175, 148]; scan_type(ASC), IDX_T1_C2(T1), scan_range['A','A']7 #CSCN2: [1, 10000, 148]; INDEX33555585(T2)

适用场景：

驱动表有很好的过滤条件
表连接条件能使用索引
结果集比较小

12. HASH JOIN：哈希连接

哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算。

Copyselect * from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';1 #NSET2: [4, 24502, 296] 2 #PRJT2: [4, 24502, 296]; exp_num(8), is_atom(FALSE) 3 #HASH2 INNER JOIN: [4, 24502, 296]; KEY_NUM(1); KEY(T1.C1=T2.C1) KEY_NULL_EQU(0)4 #SLCT2: [1, 250, 148]; T1.C2 = 'A'5 #CSCN2: [1, 10000, 148]; INDEX33555599(T1)6 #CSCN2: [1, 10000, 148]; INDEX33555600(T2)

哈希连接比较消耗内存如果系统有很多这种连接时，需调整以下 3 个参数：

参数名	说明
HJ_BUF_GLOBAL_SIZE	HASH 连接操作符的数据总缓存大小 ()>=HJ_BUF_SIZE)，系统级参数，以兆为单位。有效值范围（10~500000）
HJ_BUF_SIZE	单个哈希连接操作符的数据总缓存大小，以兆为单位。有效值范围（2~100000）
HJ_BLK_SIZE	哈希连接操作符每次分配缓存( BLK )大小，以兆为单位，必须小于 HJ_BUF_SIZE。有效值范围（1~50）

13. MERGE JOIN：归并排序连接

归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。

Copy-- 对连接列创建索引CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);

Copyselect /*+use_merge(t1 t2)*/t1.c1,t2.c1 from t1 inner join t2 on t1.c1=t2.c1 where t2.c2='b';1 #NSET2: [13, 24725, 56] 2 #PRJT2: [13, 24725, 56]; exp_num(2), is_atom(FALSE) 3 #SLCT2: [13, 24725, 56]; T2.C2 = 'b'4 #MERGE INNER JOIN3: [13, 24725, 56]; KEY_NUM(1); KEY(COL_0 = COL_0) KEY_NULL_EQU(0)5 #SSCN: [1, 10000, 4]; IDX_C1_T1(T1)6 #BLKUP2: [1, 10000, 52]; IDX_T2_C1(T2)7 #SSCN: [1, 10000, 52]; IDX_T2_C1(T2)

4.2.2 ET 工具

ET 工具是 DM 数据库自带的 SQL 性能分析工具，能够统计 SQL 语句执行过程中每个操作符的实际开销，为 SQL 优化提供依据以及指导。

1. 功能的开启/关闭

ET 功能默认关闭，可通过配置 INI 参数中的 ENABLE_MONITOR=1、MONITOR_SQL_EXEC=1 开启该功能。

Copy--两个参数均为动态参数，可直接调用系统函数进行修改

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);

SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',1);

--关闭 ET

SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',0);

SP_SET_PARA_VALUE(1,'MONITOR_SQL_EXEC',0);

注意

ET 功能的开启将对数据库整体性能造成一定影响，优化工作结束后尽量关闭该功能以提升数据库整体运行效率；DM 数据库新版本的开启 ET 功能时需要设置： ENABLE_MONITOR = 1（默认打开） MONITOR_TIME = 1 （默认打开） MONITOR_SQL_EXEC = 1（设置成1）。

部分 DM 数据库版本的开启方式可能存在一定区别，详细内容请参照安装目录 /doc 下《系统管理员手册》。

2. 查看方式

执行 SQL 语句后，客户端会返回 SQL 语句的执行号。单击执行号即可查看 SQL 语句对应的 ET 结果。

如果没有图形界面，调用存储过程可返回相同结果。

CopyCALL ET(55);

ET 结果说明：

OP: 操作符
TIME(us): 时间开销，单位为微秒
PERCENT: 执行时间占总时间百分比
RANK: 执行时间耗时排序
SEQ: 执行计划节点号
N_ENTER: 进入次数

以 SORT3 操作符为例，时间开销为 2.8 ms，占总执行时间的 59.13%，可作为优化的重点对象。对 T2 表的 C1 字段建二级索引，消除排序操作符。

CopyCREATE INDEX IDX_T2_C1 ON T2(C1);

与之前 ET 结果相比较，可看出 SORT3 操作符由于 C1 列已有序被消除，总耗时明显减少。

4.2.3 存储过程调试

在 DM 数据库中，我们可以利用 DM PL/SQL 进行存储过程及函数的编写。有时，我们编写的程序块中可能会有 BUG 导致编译失败，这种情况下就需要用到相关工具来进行调试。本章节主要介绍两种存储过程的调试方法。

1. 图形化界面工具进行调试

在有图形化界面的情况下，我们可以利用管理工具来进行匿名块的调试。

（1）使用匿名块调试

开启系统包 DEBUG SYSTEM。【工具包】->DEBUG SYSTEM-> 右键->【启用】；
点击上方的【调试】按钮进入调试。

（2）创建测试存储过程，并进行调试

创建测试存储过程。

Copy--当i到3的时候就会报除0错误DECLARE

--变量

aa int;BEGIN

--调试语句

for i in 1..3

loop

aa = 1 / (3-i);

print aa;

end loop;

END

进入调试，点击【进入】按钮，会依照代码逻辑进行分步调试，显示区域会显示当前变量执行结果。若遇到报错会终止调试，并显示报错内容，可根据报错内容进行代码修改。

2. DMDBG 进行调试

当我们没有图形界面，或者通过远程连接服务器的时候，我们也可以利用 DM 提供的命令行调试工具 dmdbg ，来完成同样的任务。

（1）登录 dmdbg。dmdbg 与 disql 同级目录，都在 dmdbms/bin 目录下，登录方式也与 disql 类似：

Copycd /dmdbms/bin

./dmdbg SYSDBA/SYSDBA@LOCALHOST:5236

--可以使用 help 命令查看参数

DEG> help

（2）引用存储过程。在 disql 中创建如下测试存储过程，然后在 DBG 中把 call TEST_DMDBG; 放到 SQL 内执行，如下所示：

Copy--注意调试前确保已经开启系统包 DEBUG SYSTEM--登录disql，在disql中创建如下测试存储过程create or replace procedure TEST_DEBUGas

aa int;begin

for i in 1..3

loop

aa = 1 / (3-i);

print aa;

end loop;end;

--登录dmdbg，把 call TEST_DMDBG; 放到 SQL 内执行

DBG> sql call TEST_DMDBG;

（3）添加断点。从头开始调试，将断点放到最开始的位置，可以在需要的行数打上断点，如下所示：

CopyDBG> B 0

Breakpoint 1 at @dbg_main, line: 1@{call TEST_DMDBG;}

（4）开始调试。

CopyDBG> r

Breakpoint 1, line: 1@{call TEST_DMDBG;}

（5）进入循环里调试。

CopyDBG> s

SYSDBA.TEST_DMDBG line: 5 @{ for i in 1..3}

DBG> s

SYSDBA.TEST_DMDBG line: 7 @{ aa = 1 / (3-i);}

（6）查看当前的堆栈。

CopyDBG> bt

\#0 SYSDBA.TEST_DMDBG() line: 7@{ aa = 1 / (3-i);}

\#1 @dbg_main line: 1@{call TEST_DMDBG;}

（7）查看当前变量的数值。

Copy--可以通过 P 变量名的方式输出打印

DBG> p aa

$3 = 0--当 i 到3的时候就会报除0错误

DBG> s

[TEST_DMDBG] 除0错误.error code=-6103--报错停止

4.3 SQL 语句优化

4.3.1 索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

索引结构：最常见的索引结构为 B*树索引，存储结构如下图所示：

最顶层的为根节点，最底层的为叶子节点，中间层为内节点。实际使用当中一般不止 3 层（取决于数据量大小），除根节点以及叶子节点以外仅为内节点。对于一个 m 阶（本例中 m=2）的 B*树存储结构有以下几个特点：

每个结点最多有 m 个子结点。
除了根结点和叶子结点外，每个结点最少有 m/2（向上取整）个子结点。
如果根结点不是叶子结点，那根结点至少包含两个子结点。
所有的叶子结点都位于同一层。
每个结点都包含 k 个元素，这里 m/2 ≤ k < m，这里 m/2 向下取整。
每个节点中的元素从小到大排列。
每个元素左结点的值都小于或等于该元素，右结点的值都大于或等于该元素。
所有的非叶子节点只存储关键字信息。
所有的叶子结点中包含了全部元素的信息。
所有叶子节点之间都有一个链指针。

可以看出在该存储结构中查找特定数据的算法复杂度为 O(log2N)，查找速度仅与树高度有关。
对于聚集索引叶子节点存储的元素是数据块即为整行数据，对于非聚集索引叶子节点存储的元素是索引字段的所对应的聚集索引的值或 rowid，如果需要获取其它字段信息需要根据聚集索引的值或 rowid 回表 (BLKUP) 进行查询。

索引适用范围：

在以下场景下可考虑创建索引：

仅当要通过索引访问表中很少的一部分行（1%~20%）。
索引可覆盖查询所需的所有列，不需额外去访问表。

注意

对于一个表来说索引并非越多越好，过多的索引将影响该表的 DML 效率。

存在下列情况将导致无法使用索引：

组合索引中，条件列中没有组合索引的首列。
条件列带有函数或计算。
索引排序是按照字段值进行排序的，字段值通过函数或计算后的值索引无法获取。
索引过滤性能不好时。

例如对一张 10 万条记录的表进行条件查询，获取 5 万条数据，通过索引进行查找效率低于全表扫描，将放弃使用索引。

建立索引的原则：

建立唯一索引。唯一索引能够更快速地帮助我们进行数据定位；
为经常需要进行查询操作的字段建立索引；
对经常需要进行排序、分组以及联合操作的字段建立索引；
在建立索引的时候，要考虑索引的最左匹配原则（在使用 SQL 语句时，如果 where 部分的条件不符合最左匹配原则，可能导致索引失效，或者不能完全发挥建立的索引的功效）；
不要建立过多的索引。因为索引本身会占用存储空间；
如果建立的单个索引查询数据很多，查询得到的数据的区分度不大，则考虑建立合适的联合索引；
尽量考虑字段值长度较短的字段建立索引，如果字段值太长，会降低索引的效率。

4.3.2 SQL 语句改写

DM 数据库针对 SQL 语句有以下常见几种改写方法：

1. 优化 GROUP BY

提高 GROUP BY 语句的效率，可以在 GROUP BY 之前过滤掉不需要的内容。

Copy--优化前SELECT JOB,AVG(AGE) FROM TEMP GROUP BY JOB HAVING JOB = 'STUDENT' OR JOB = 'MANAGER';--优化后SELECT JOB,AVG(AGE) FROM TEMP WHERE JOB = 'STUDENT' OR JOB = 'MANAGER' GROUP BY JOB;

2. 用 UNION ALL 替换 UNION

当 SQL 语句需要 UNION 两个查询结果集合时，这两个结果集合会以 UNION ALL 的方式被合并，在输出最终结果前进行排序。用 UNION ALL 替代 UNION, 这样排序就不是必要了，效率就会因此得到提高。

注意

UNION 将对结果集合排序，这个操作会使用到 SORT_AREA_SIZE 这块内存，对于这块内存的优化也很重要；UNION ALL 将重复输出两个结果集合中相同记录，要从业务需求判断使用 UNION ALL 的可行性。

Copy--优化前SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20' UNION SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20'; --优化后SELECT USER_ID,BILL_ID FROM USER_TAB1 WHERE AGE = '20' UNION ALL SELECT USER_ID,BILL_ID FROM USER_TAB2 WHERE AGE = '20';

3. 用 EXISTS 替换 DISTINCT

当 SQL 包含一对多表查询时，避免在 SELECT 子句中使用 DISTINCT，一般用 EXISTS 替换 DISTINCT 查询更为迅速。

Copy--优化前SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E WHERE D.USER_ID= E.USER_ID;--优化后SELECT USER_ID,BILL_ID FROM USER_TAB1 D WHERE EXISTS(SELECT 1 FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID);

4. 多使用 COMMIT

可以在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。
COMMIT 所释放的资源:

回滚段上用于恢复数据的信息；
被程序语句获得的锁；
redo log buffer 中的空间；
为管理上述 3 种资源中的内部花销。

5. 用 WHERE 子句替换 HAVING 子句

避免使用 HAVING 子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、总计等操作，可以通过 WHERE 子句限制记录的数目。on、where、having 三个都可以加条件子句，其中，on 是最先执行，where 次之，having 最后。

on 是先把不符合条件的记录过滤后才进行统计，在两个表联接时才用 on；
在单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，where 和 having 结果是一样的，但 where 比 having 快
如果涉及到计算字段，where 的作用时间是在计算之前完成，而 having 是在计算后才起作用，两者的结果会不同；
在多表联接查询时，on 比 where 更早起作用。首先会根据各个表之间的关联条件，把多个表合成一个临时表后，由 where 进行过滤再计算，计算完再由 having 进行过滤。

6. 用 TRUNCATE 替换 DELETE

当删除表中的记录时，在通常情况下, 回滚段用来存放可以被恢复的信息。如果没有 COMMIT 事务，会将数据恢复到执行删除命令之前的状况；而当运用 TRUNCATE 时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。

注意

TRUNCATE 只在删除全表适用，TRUNCATE 是 DDL 不是 DML。

7. 用 EXISTS 替换 IN、用 NOT EXISTS 替换 NOT IN

在基于基础表的查询中可能会需要对另一个表进行联接。在这种情况下, 使用 EXISTS (或 NOT EXISTS )通常将提高查询的效率。在子查询中，NOT IN 子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN 都是最低效的(要对子查询中的表执行一个全表遍历)，所以尽量将 NOT IN 改写成外连接( Outer Joins )或 NOT EXISTS。

Copy--优化前SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0 AND A.ID IN(SELECT ID FROM TEMP1 WHERE NAME ='TOM'); --优化后SELECT A.* FROM TEMP(基础表) A WHERE AGE > 0 AND EXISTS(SELECT 1 FROM TEMP1 WHERE A.ID= ID AND NAME='TOM');

4.3.3 表设计优化

表设计优化可以从三个方面入手：选择合适的表类型、设置分区表、设置全局临时表。

1. 表类型选择

达梦数据库提供了三种表类型：行存储表、列存储表（HUGE）和堆表。运维人员可根据实际需求选择合适的表类型。

表类型	描述	主要特征	适用场景
行存储表	行存储是以记录为单位进行存储的，数据页面中存储的是完整的若干条记录	1．按行存储 2．每个表都创建一个 B 树，并在叶子上存放数据	适用于高并发 OLTP 场景。
列存储表（HUGE）	列存储是以列为单位进行存储的，每一个列的所有行数据都存储在一起，而且一个指定的页面中存储的都是某一个列的连续数据。	1．按列存储 2．非事务型 HUGE 表：LOG NONE、LOG LAST、LOG ALL3．事务型 HUGE 表	适用于海量数据分析场景
堆表	堆表是指采用了物理 ROWID 形式的表，即使用文件号、页号和页内偏移而得到 ROWID 值，这样就不需要存储 ROWID 值，可以节省空间	1．数据页都是通过链表形式存储 2．可设置并发分支	并发插入性能较高

2. 水平分区表

（1）分区类型

范围(range)水平分区：对表中的某些列上值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上；
哈希（hash）水平分区：通过指定分区编号来均匀分布数据的一种分区类型，通过在 I/O 设备上进行散列分区，使得这些分区大小基本一致；
列表（list）水平分区：通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。例如，可以对表上的 status 列的值在('A'，'H'，'O')放在一个分区，值在('B'，'I'，'P')放在另一个分区，以此类推；
多级分区表：按上述三种分区方法进行任意组合，将表进行多次分区，称为多级分区表。

（2）分区优势

减少访问数据
操作灵活：可以操作分区 truncate、分区 drop、分区 add、分区 exchange

（3）举例说明

Copyselect *

from range_part_tab

where deal_date >= TO_DATE('2019-08-04','YYYY-MM-DD')

and deal_date <= TO_DATE('2019-08-07','YYYY-MM-DD');

执行计划：

Copy1 #NSET2:[24,18750,158]2 #PRJT2:[24,18750,158];exp_num(6),is_atom(FALSE)3 #PARALLEL:[24,18750,158];scan_type(GE_LE),key_num(0,1,1)4 #SLCT2:[24,18750,158];[(RANGE_PART_TAB.DEAL_DATE >= var2 AND RANGE_PART_TAB.DEAL_DATE <= var4)]5 #CSCN2:[73,500000,158];INDEX33555933(RANGE_PART_TAB)

--#PARALLEL：控制水平分区子表的扫描

对主表和所有子表都收集统计信息
对索引收集统计信息

注意

如果 SQL 中有可利用的索引，普通表也可能比分区表性能高。

3. 全局临时表

当处理复杂的查询或事务时，由于在数据写入永久表之前需要暂时存储一些行信息或需要保存查询的中间结果，可能需要一些表来临时存储这些数据。DM 允许创建临时表来保存会话甚至事务中的数据。在会话或事务结束时，这些表上的数据将会被自动清除。

（1）全局临时表类型

事务级-ON COMMIT DELETE ROWS
会话级-ON COMMIT PRESERVE ROWS

（2）全局临时表优势

不同 session 数据独立
自动清理

（3）举例说明

第一步：原始语句如下：

Copy--T_1 视图（与 oracle 的 dblink 全表查询）--T_1 视图的结构为--(INIT_DATE int , BRANCH_NO int , FUND_ACCOUNT int , BUSINESS_FLAG int , remark varchar(32))

--T_2 表--T_2 表的结构为--(BRANCH_NO int,FUND_ACCOUNT int , prodta_no int,v_config_4662 varchar(32))

select a.init_date as oc_date,a.BRANCH_NO,a.FUND_ACCOUNT,a.BUSINESS_FLAG,a.remark,b.BRANCH_NO,b.FUND_ACCOUNT,b.prodta_no

from T_1 a,T_2 b

where init_date = 20181120

AND a.BRANCH_NO = b.BRANCH_NO

AND a.FUND_ACCOUNT = b.FUND_ACCOUNT

and instr(v_config_4662, ',' || b.prodta_no || ',')>0

and a.BUSINESS_FLAG in (2629,2630)

and nvl(a.remark,' ')not like '%实时TA%';

第二步：创建临时表 T1_20181122，将 T_1 视图中部分数据插入临时表中。

CopyCREATE GLOBAL TEMPORARY TABLE "T1_20181122"

(init_date int, BRANCH_NO int, FUND_ACCOUNT int,BUSINESS_FLAG int,remark varchar(32));

--插入dblink获取的数据到临时表

insert into T1_20181122

select *

from T_1 a

where init_date = 20181120

and a.BUSINESS_FLAG in (2629,2630)

and nvl(a.remark,' ')not like '%实时TA%';

第三步：语句改写

Copyselect a.init_date as oc_date,a.BRANCH_NO,a.FUND_ACCOUNT,a.BUSINESS_FLAG,a.remark,b.BRANCH_NO,b.FUND_ACCOUNT,b.prodta_no

from T1_20181122 a, T_2 b

where a.BRANCH_NO = b.BRANCH_NO

AND a.FUND_ACCOUNT = b.FUND_ACCOUNT

and instr(v_config_4662, ',' || b.prodta_no || ',')>0;

执行计划：50 分钟 >>1 分钟

Copy--原语句执行计划1 #NSET2:[11,1,1644]2 #PRJT2:[11,1,1644];exp_num(41),is_atom(FALSE)3 #HASH2 INNER JOIN:[11,1,1644];KEY_NUM(2);4 #SLCT2:[0,1,270];exp11>05 #CSCN2:[0,1,270];INDEX33560908(T_HSOTCPRODCASHACCT as B)6 #HASH RIGHT SEMI JOIN2:[10,380,1374];n_keys(1)7 #CONST VALUE LIST:[0,2,30];row_num(2),col_num(1),8 #SLCT2:[10,380,1374];(A.INIT_DATE = var4 AND NOT(exp11 LIKE '%实时TA%'))9 #PRJT2:[10,1000,1374];exp_num(13),is_atom(FALSE)10 #REMOTE SCAN:[0,0,0] HIS_FUNDJOUR@HS08HIS

--改写后执行计划1 #NSET2: [1, 1, 124] 2 #PRJT2: [1, 1, 124]; exp_num(8), is_atom(FALSE) 3 #HASH2 INNER JOIN: [1, 1, 124]; KEY_NUM(2); KEY(B.BRANCH_NO=A.BRANCH_NO AND B.FUND_ACCOUNT=A.FUND_ACCOUNT) KEY_NULL_EQU(0, 0)4 #SLCT2: [1, 1, 60]; exp11 > 05 #CSCN2: [1, 1, 60]; INDEX33555476(T_2 as B)6 #CSCN2: [1, 1, 64]; INDEX33555478(T1_20181122 as A)

五、统计信息

5.1 统计信息概述

统计信息主要是描述数据库中表和索引的大小数以及数据分布状况等的一类信息。比如：表的行数、块数、平均每行的大小、索引的高度、叶子节点数以及索引字段的行数等。

统计信息对于 CBO（基于代价的优化器）生成执行计划具有直接影响。例如在嵌套循环连接（链接）中需要选择小表作为驱动表，两个关联表哪个是小表完全取决于统计信息中记录的数据量信息。此外，访问一个表是否要走索引，关联查询能否采用其它关联方式等都是 CBO 基于统计信息确定的。因此，统计信息的准确是生成最优执行计划的必要前提。

5.2 收集统计信息

DM 收集统计信息的方法分为手动收集和自动收集。

5.2.1 手动收集

Copy--收集指定用户下所有表所有列的统计信息：

DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集指定用户下所有索引的统计信息：

DBMS_STATS.GATHER_SCHEMA_STATS('usename',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');--或收集单个索引统计信息：

DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');

--收集指定用户下某表统计信息：

DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集某表某列的统计信息：

STAT 100 ON table_name(column_name);

注意

统计信息收集过程中将对数据库性能造成一定影响，避免在业务高峰期收集统计信息。

5.2.2 自动收集

DM 数据库支持统计信息的自动收集，当全表数据量变化超过设定阈值后可自动更新统计信息。

Copy--打开表数据量监控开关，参数值为 1 时监控所有表，2 时仅监控配置表

SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);

--设置 SYSDBA.T 表数据变化率超过 15% 时触发自动更新统计信息

DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T','STALE_PERCENT',15);

--配置自动收集统计信息触发时机

SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'14:36', '2020/3/31',60,1);

函数各参数介绍

SP_CREATE_AUTO_STAT_TRIGGER(

TYPE INT, --间隔类型，默认为天

FREQ_INTERVAL INT, --间隔频率，默认 1

FREQ_SUB_INTERVAL INT, --间隔频率，与 FREQ_INTERVAL 配合使用

FREQ_MINUTE_INTERVAL INT, --间隔分钟，默认为 1440

STARTTIME VARCHAR(128), --开始时间，默认为 22:00

DURING_START_DATE VARCHAR(128), --重复执行的起始时间，默认 1900/1/1

MAX_RUN_DURATION INT, --允许的最长执行时间(秒)，默认不限制

ENABLE INT --0 关闭，1 启用 --默认为 1

);

5.3 查看统计信息

Copy--用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。

dbms_stats.table_stats_show('模式名','表名');

--用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。返回两个结果集：一个是索引的统计信息；另一个是直方图的统计信息。

dbms_stats.index_stats_show('模式名','索引名');

–用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。

dbms_stats.COLUMN_STATS_SHOW('模式名','表名','列名');

5.4 更新统计信息

Copy--更新已有统计信息

DBMS_STATS.UPDATE_ALL_STATS();

5.5 删除统计信息

Copy--表

DBMS_STATS.DELETE_TABLE_STATS('模式名','表名','分区名',...);

--模式

DBMS_STATS.DELETE_SCHMA_STATS('模式名','','',...);

--索引

DBMS_STATS.DELETE_INDEX_STATS('模式名','索引名','分区表名',...);

--字段

DBMS_STATS.DELETE_COLUMN_STATS('模式名','表名','列名','分区表名',...);

六、参考

若以上内容无法解决您的问题，可以在达梦技术社区提问交流。

posted @ 2023-03-09 14:53 feiyun8616 阅读(1639) 评论(0) 编辑收藏举报

刷新页面返回顶部

feiyun8616的作坊 (半个程序员and dba) 脚本化 python

达梦查询慢sql

达梦数据库监控正在执行的长SQL

-》优化后 测试2： 返回数据大概5行，可以有效减小数据集。

问题：

分析：

问题介绍：

分析过程：

结论

https://eco.dameng.com/document/dm/zh-cn/pm/sql-tuning

性能优化

一、前言

1.1 概述

1.2 工具与术语

1.3 适用范围

二、数据库架构优化

三、数据库参数优化

3.1 INI 参数配置说明

3.2 参数修改方法

3.2.1 调用系统过程

3.2.2 ALTER 命令修改

3.2.3 修改 INI 文件

四、SQL 优化

4.1 定位慢 SQL

4.1.1 开启跟踪日志记录

4.1.2 通过系统视图查看

4.2 SQL 分析方法

4.2.1 执行计划

4.2.2 常见操作符解读

4.2.2 ET 工具

4.2.3 存储过程调试

4.3 SQL 语句优化

4.3.1 索引

4.3.2 SQL 语句改写

4.3.3 表设计优化

五、统计信息

5.1 统计信息概述

5.2 收集统计信息

5.2.1 手动收集

5.2.2 自动收集

5.3 查看统计信息

5.4 更新统计信息

5.5 删除统计信息

六、参考

公告

-》优化后测试2：返回数据大概5行，可以有效减小数据集。