达梦_10053 trace事件
110053 trace事件
DM8的10053 trace事件是指优化器的调试追踪事件，启用10053 trace事件，可以产生查看优化器内部工作细节的文件——10053 trace文件（.trc文件）。通过10053 trace文件，用户可以获得优化器评估产生的单表访问路径代价、以及多表连接时不同连接顺序和连接方法下的查询代价。
2使用方法
首先给事件设置一个级别，然后运行SQL（或者直接使用explain plan的方式产生执行计划），最后终止事件。生成的trace文件默认保存在dm.ctl所在路径的trace目录，用户也可以通过修改dm.ini中TRACE_PATH参数来改用其它路径。用户直接阅读trace目录下的原始文件.trc即可。10053 trace命名规则是DMSERVER_月日_时分_session_id.trc。其中月日，时分是指开启trace event 10053时的时间，session_id为当前会话的ID。如果同名文件存在则原文件内容被覆盖，如果在已经开启的前提下再次开启，则使用原文件，不再创建新的。
310053 单表trace测试
3.1语法格式
ALTER SESSION [<sess_id>] SET EVENTS '<event_defs>';
<sess_id>:: =0
<event_defs >:: =<event_def> {:<event_def>}
<event_def>:: = <event_name> TRACE <event_qualifiers>
<event_name>:: =IMMEDIATE |
<内部事件号>:: =10001|10002|10003|10046|10053
<event_qualifiers>:: = NAME <trace_name >[trace_qualifiers]
<trace_name>::=CONTEXT|BLOCKDUMP|BUFFERS|VPOOLDUMP|HEAPDUMP|DCT_CACHE| SCP_CACHE| ERRORSTACK| REDOHDR| LOGHIST| PLNDUMP|TREEDUMP
<trace_qualifiers >::= <trace_qualifier> {, <trace_qualifier>}
<trace_qualifier>::=OFF|FOREVER|LEVEL <level_value>|DUMP_FMT <BINARY|STRUCT> |
DUMP_FILE <字符串常量>
参数说明
<sess_id> 为0，表示为所有会话，省略表示为当前会话。
<event_defs> 确定跟踪事件，可以同时设置多个跟踪事件，各个事件由符号“:”分隔。
<event_name>
IMMEDIATE时，表示诊断立即执行，且仅执行一次，IMMEDIATE不允许与<trace_qualifier>中的FOREVER关键字一起使用。
当ecode 为10053，trace name为context时，对应的level只能是1 或2。
Level 1：输出查询语句计划中单表过滤或者多表连接的优化过程。如果一条语句同时包含了上述两种优化，那么只输出多表连接的优化过程。
Level 2：输出查询语句计划中单表过滤以及多表连接的优化过程。
3.2简单测试
测试达梦版本：8-1-3-100-2024.02.06-217991-20081-ENT
环境简介：
SQL> desc test01
行号 NAME TYPE$ NULLABLE
1 OWNER VARCHAR(128) Y
2 OBJECT_NAME VARCHAR(128) Y
3 SUBOBJECT_NAME VARCHAR(128) Y
4 OBJECT_ID NUMBER Y
5 DATA_OBJECT_ID VARCHAR(1) Y
6 OBJECT_TYPE VARCHAR(18) Y
7 CREATED DATETIME(6) Y
8 LAST_DDL_TIME DATETIME(6) Y
9 TIMESTAMP DATETIME(6) Y
10 STATUS VARCHAR(7) Y
11 TEMPORARY VARCHAR(1) Y
行号 NAME TYPE$ NULLABLE
12 GENERATED VARCHAR(1) Y
13 SECONDARY VARCHAR(1) Y
14 NAMESPACE VARCHAR(1) Y
15 EDITION_NAME VARCHAR(1) Y
15 rows got
SQL> select count() from test01;
行号 COUNT()
1 1017
已用时间: 2.574(毫秒). 执行号:503.
SQL> select count(distinct object_id) from test01;
行号 COUNT(DISTINCTOBJECT_ID)
1 963
已用时间: 1.236(毫秒). 执行号:505
Explain 执行计划
SQL> explain select * from test01 where object_id=268436698;
1 #NSET2: [1, 25, 609]
2 #PRJT2: [1, 25, 609]; exp_num(16), is_atom(FALSE)
3 #SLCT2: [1, 25, 609]; TEST01.OBJECT_ID = var1 SLCT_PUSHDOWN(TRUE)
4 #CSCN2: [1, 1017, 609]; INDEX33555465(TEST01) NEED_SLCT(TRUE); btr_scan(1)
已用时间: 1.777(毫秒). 执行号:0.
object_id列并没有建索引，走全表扫描。
Level 1 收集trace
[dmdba@Test bin]$ ./disql SYSDBA/SYSDBA123
服务器[LOCALHOST:5236]:处于普通打开状态
登录使用时间 : 4.135(ms)
disql V8
SQL> alter session set events '10053 trace name context forever, level 1';
操作已执行
已用时间: 0.808(毫秒). 执行号:501.
SQL> select * from test01 where object_id=268436746;
行号 OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY
GENERATED SECONDARY NAMESPACE EDITION_NAME
--------- --------- --------- ------------
1 SYS UTL_I18N NULL 268436746 NULL DSYNOM 2024-04-06 09:54:04.136714 2024-04-06 09:54:04.000000 2024-04-06 09:54:04.000000 VALID N
NULL NULL NULL NULL
已用时间: 2.065(毫秒). 执行号:502.
SQL> alter session set events '10053 trace name context off';
操作已执行
已用时间: 0.921(毫秒). 执行号:503.
查看路径
SQL> show parameter trace
行号 PARA_NAME PARA_VALUE
1 AUTOTRACE_LEVEL 0
2 FILE_TRACE 0
3 COMM_TRACE 0
4 ERROR_TRACE 0
5 UDP_TRACE_MODE 0
6 TRACE_PATH /dm/dmdata/DAMENG/trace
7 BACKTRACE_REPORT 0
查看内容
[root@Test trace]# more DMSERVER_0411_0912_140446101743000.trc
--服务器版本信息
DM Database Server x64 V8[1-3-100-2024.02.06-217991-20081-ENT ], Feb 6 2024 16:48:26 built.
--Trace时间和级别
*** 2024-04-11 09:12:02.314000000
*** Start trace 10053 event [level 1]
--Trace的语句
Current SQL Statement:
select * from test01 where object_id=268436746;
--影响查询效率的部分dm.ini参数
Parameters for this statement
olap_flag = 2
......
--优化前计划
*** Plan before optimized:
project[0x7fbc280532e0] n_exp(16)
select[0x7fbc28052ca8] (TEST01.OBJECT_ID = exp_cast(268436746))
base table[0x7fbc28052618] (TEST01, FULL SEARCH)
--优化的具体过程
--行数的估算
<<<<< selectivity estimate of table TEST01 >>>>>
*** stdesc 1: column = OBJECT_ID, scan_type = EQU, key = (exp_cast(268436746))
stat_info(1015,3,'C')= {
#Valid = 'N',
#Type = '-',
#Card = 1017,
#NDV = 339,
#Nulls = 10,
#LP = 9000,
#LVLS = 3,
#CLUF = 0,
#NK = 0,
#NS = 0}
---> st = 0.02500
total: 1017, estimate match rows: 25, st: 0.02500; -- st_other: 1.000, n_stdesc: 1
--路径选择
---------------- single table access path probe for TEST01 ----------------
*** path 1: INDEX33555465 (FULL search), cost: 0.33230
best access path: INDEX33555465 (FULL search), cost: 0.33230
--优化后计划
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fbc28065690] n_exp(16) (cost: 0.33230, rows: 25)
select[0x7fbc28067848] (TEST01.OBJECT_ID = exp_cast(268436746))(cost: 0.33230, rows: 25)
base table[0x7fbc28068648] (TEST01, INDEX33555465, FULL SEARCH) (cost: 0.33230, rows: 1017)
-------------------------- END --------------------------
10053 trace文件结构
Trace文件内容 说明
服务器版本信息 Trace文件通用部分
Trace时间和级别 每条查询语句都产生完整的该部分内容。
如果用户不指定hint，则没有“用户指定的各类hint”项信息。
Trace的语句
影响查询效率的部分dm.ini参数
用户指定的各类hint
优化前计划
优化的具体过程
优化后计划
对比EPLAIN
SQL> explain select * from test01 where object_id=268436746;
1 #NSET2: [1, 25, 609]
2 #PRJT2: [1, 25, 609]; exp_num(16), is_atom(FALSE)
3 #SLCT2: [1, 25, 609]; TEST01.OBJECT_ID = var1 SLCT_PUSHDOWN(TRUE)
4 #CSCN2: [1, 1017, 609]; INDEX33555465(TEST01) NEED_SLCT(TRUE)
; btr_scan(1)
从上述的语句可以看出目前估算的并不准确，且走全表扫描。我们收集统计信息、并在OBJECT_ID上创建索引。
3.3再次测试
SQL> alter session set events '10053 trace name context forever, level 1';
操作已执行
已用时间: 0.847(毫秒). 执行号:501.
--收集统计信息
SQL> dbms_stats.gather_table_stats('SYSDBA','TEST01',ESTIMATE_PERCENT=>100);
DMSQL 过程已成功完成
已用时间: 98.103(毫秒). 执行号:502.
--执行SQL
SQL> select * from test01 where object_id=268436746;
行号 OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY
GENERATED SECONDARY NAMESPACE EDITION_NAME
--------- --------- --------- ------------
1 SYS UTL_I18N NULL 268436746 NULL DSYNOM 2024-04-06 09:54:04.136714 2024-04-06 09:54:04.000000 2024-04-06 09:54:04.000000 VALID N
NULL NULL NULL NULL
已用时间: 0.946(毫秒). 执行号:503.
--创建索引
SQL> create index index_object_id on test01(object_id);
操作已执行
已用时间: 16.964(毫秒). 执行号:504.
--执行SQL
SQL> select * from test01 where object_id=268436746;
行号 OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY
GENERATED SECONDARY NAMESPACE EDITION_NAME
--------- --------- --------- ------------
1 SYS UTL_I18N NULL 268436746 NULL DSYNOM 2024-04-06 09:54:04.136714 2024-04-06 09:54:04.000000 2024-04-06 09:54:04.000000 VALID N
NULL NULL NULL NULL
查看TRACE文件
[root@Test trace]# more DMSERVER_0411_1525_140586090833096.trc
......
*** Plan before optimized:
project[0x7fdcc00513d0] n_exp(16)
select[0x7fdcc0050d98] (TEST01.OBJECT_ID = exp_cast(268436746))
base table[0x7fdcc0050708] (TEST01, FULL SEARCH)
<<<<< selectivity estimate of table TEST01 >>>>>
*** stdesc 1: column = OBJECT_ID, scan_type = EQU, key = (exp_cast(268436746))
stat_info(1015,3,'C')= {
#Valid = 'Y',
#Type = 'Freq',
#Card = 1017,
#NDV = 963,
#Nulls = 0,
#LP = 3,
#LVLS = 1,
#CLUF = 0,
#NK = 0,
#NS = 1017}
---> st = 0.00098
total: 1017, estimate match rows: 1, st: 0.00098; -- st_other: 1.000, n_stdesc: 1
---------------- single table access path probe for TEST01 ----------------
*** path 1: INDEX33555465 (FULL search), cost: 0.33230
best access path: INDEX33555465 (FULL search), cost: 0.33230
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fdc2fbea4a0] n_exp(16) (cost: 0.33230, rows: 1)
select[0x7fdc2fbec658] (TEST01.OBJECT_ID = exp_cast(268436746))(cost: 0.33230, rows: 1)
base table[0x7fdc2fbed458] (TEST01, INDEX33555465, FULL SEARCH) (cost: 0.33230, rows: 1017)
-------------------------- END --------------------------
*** 2024-04-11 15:27:13.197000000
*** Start trace 10053 event [level 1]
Current SQL Statement:
select * from test01 where object_id=268436746;
......
*** Plan before optimized:
project[0x7fdcc00534c8] n_exp(16)
select[0x7fdcc0052e90] (TEST01.OBJECT_ID = exp_cast(268436746))
base table[0x7fdcc0052800] (TEST01, FULL SEARCH)
<<<<< selectivity estimate of table TEST01 >>>>>
*** stdesc 1: column = OBJECT_ID, scan_type = EQU, key = (exp_cast(268436746))
stat_info(1015,3,'C')= {
#Valid = 'Y',
#Type = 'Freq',
#Card = 1017,
#NDV = 963,
#Nulls = 0,
#LP = 3,
#LVLS = 1,
#CLUF = 0,
#NK = 0,
#NS = 1017}
---> st = 0.00098
total: 1017, estimate match rows: 1, st: 0.00098; -- st_other: 1.000, n_stdesc: 1
---------------- single table access path probe for TEST01 ----------------
*** path 1: INDEX33555465 (FULL search), cost: 0.33230
*** path 2: INDEX_OBJECT_ID (EQU search), cost: 0.06609
best access path: INDEX_OBJECT_ID (EQU search), cost: 0.06609
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fdc2fbea818] n_exp(16) (cost: 0.06609, rows: 1)
base table[0x7fdc2fbec9d0] (TEST01, INDEX_OBJECT_ID, EQU SEARCH) (cost: 0.06609, rows: 1)
-------------------------- END --------------------------
新的trace文件中有执行了两次SQL。
第一次是收集统计信息后，虽然依旧走了全表扫描，但是rows值这次正确，为1。
第二次是再创建索引后，《single table access path probe for TEST01》章节中提示有两个PATH：
*** path 1: INDEX33555465 (FULL search), cost: 0.33230
*** path 2: INDEX_OBJECT_ID (EQU search), cost: 0.06609
很明显PATH2的方式 cost更低。
《BEST PLAN FOR THIS STATEMENT》章节：
project[0x7fdc2fbea818] n_exp(16) (cost: 0.06609, rows: 1)
base table[0x7fdc2fbec9d0] (TEST01, INDEX_OBJECT_ID, EQU SEARCH) (cost: 0.06609, rows: 1)
410053 多表trace测试
4.1OPTIMIZER_MODE=0
当设置OPTIMIZER_MODE=0时，多表连接的计划优化使用卡特兰数。N个表连接存在着N!个不同的连接顺序，对于每一个连接顺序又存在着若干种不同的组合，每一种组合下又存在着过滤条件是否下放的若干种状态。当三元组(连接顺序, 组合顺序, 过滤条件状态) 都确定后，执行计划树的形态也就确定了。优化器在此基础上逐一试探各种连接方法的执行计划，并将该计划的代价和当前的最小代价计划进行比较，如果一个计划的部分代价已经超过了最小代价，那么该计划直接被丢弃。半连接查询由于对表的排列组合存在要求因此计划的搜索范围远低于连接查询。
当参与连接的表数超过ini参数设置的MAX_OPT_N_TABLES时，DM优化器采用类似于动态规划的策略缩小执行计划的搜索范围。
用户指定的各类连接hint（包括JOIN hint, ORDER hint以及USE_HASH等连接方式hint）会直接对计划搜索范围大小产生影响。
SQL> show parameter OPTIMIZER
行号 PARA_NAME PARA_VALUE
1 OPTIMIZER_MODE 1
2 OPTIMIZER_MAX_PERM 7200
3 OPTIMIZER_DYNAMIC_SAMPLING 0
4 OPTIMIZER_OR_NBEXP 29
5 OPTIMIZER_IN_NBEXP 1
6 OPTIMIZER_AGGR_GROUPBY_ELIM 1
7 OPTIMIZER_VERSION 70100
4.1.1简单测试
SQL> alter session 0 set events '10053 trace name context forever, level 1';
操作已执行
已用时间: 1.245(毫秒). 执行号:501.
SQL> select c.city_name,l.street_address from dmhr.city c full join
dmhr.location l on c.city_id=l.city_id;2
行号 CITY_NAME STREET_ADDRESS
1 北京 海淀区北三环西路48号
2 石家庄 桥西区槐安东路28号
......
11 rows got
已用时间: 4.843(毫秒). 执行号:502.
SQL> explain select c.city_name,l.street_address from dmhr.city c full join
dmhr.location l on c.city_id=l.city_id;2
1 #NSET2: [1, 11, 96]
2 #PRJT2: [1, 11, 96]; exp_num(2), is_atom(FALSE)
3 #HASH FULL JOIN2: [1, 11, 96]; key_num(1), mix_aagr(0), mix_dist(0) KEY(C.CITY_ID=L.CITY_ID)
4 #CSCN2: [1, 11, 96]; INDEX33555469(CITY as C); btr_scan(1)
5 #CSCN2: [1, 11, 96]; INDEX33555472(LOCATION as L); btr_scan(1)
已用时间: 0.868(毫秒). 执行号:0.
从explain方式查看执行计划，两张表做full join连接；两张表都走了全表扫描，最后用了HASH FULL JOIN2。
查看TRACE文件
*** Plan before optimized:
project[0x7fa5cc056fe8] n_exp(2)
nest loop full outer join[0x7fa5cc055d40] (C.CITY_ID = L.CITY_ID)
base table[0x7fa5cc056378] (C, FULL SEARCH)
base table[0x7fa5cc0569b0] (L, FULL SEARCH)
*** full outer join optimization start...
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
<<<<< full join card estimate >>>>>
*** stdesc 1: C.CITY_ID = L.CITY_ID ---> st: 1.00000
......
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
---------------- full join method probe ----------------
*** nest loop full join cost: 10.70252
*** index full join is not available
*** hash full join cost: 0.94714
*** merge full join is not available
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fa5cc069f18] n_exp(2) (cost: 0.94714, rows: 11)
hash full outer join[0x7fa5cc06a8c0] (cost: 0.94714, rows: 11)
base table[0x7fa5cc06b268] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fa5cc06b8a0] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
-------------------------- END --------------------------
《full join method probe》章节可以看出这条SQL做全外连接时只能走nest loop全外连接或者hash全外连接，而索引全连接、merge全连接是无法使用的；
nest loop full join和hash full join相比，后者cost值最低。
4.1.2再次测试
启用HINT
SQL> show parameter ENABLE_INJECT_HINT
行号 PARA_NAME PARA_VALUE
1 ENABLE_INJECT_HINT 0
SQL> alter system set 'ENABLE_INJECT_HINT' = 1 both;
DMSQL 过程已成功完成
已用时间: 6.854(毫秒). 执行号:504.
SQL> show parameter ENABLE_INJECT_HINT
行号 PARA_NAME PARA_VALUE
1 ENABLE_INJECT_HINT 1
创建索引、收集统计信息
SQL> create index ind_city_id on dmhr.city(city_id);
操作已执行
已用时间: 16.092(毫秒). 执行号:506.
SQL> create index ind_location_id on dmhr.location(city_id);
操作已执行
已用时间: 8.955(毫秒). 执行号:507.
SQL> dbms_stats.gather_table_stats('DMHR','CITY',ESTIMATE_PERCENT=>100);
DMSQL 过程已成功完成
已用时间: 66.055(毫秒). 执行号:508.
SQL> dbms_stats.gather_table_stats('DMHR','LOCATION',ESTIMATE_PERCENT=>100);
DMSQL 过程已成功完成
已用时间: 18.649(毫秒). 执行号:509.
这里因为全外连接，且数据存在NULL，强制两表通过NL方式去连接并没有效果。
SQL> explain select /*+use_nl(c,l) */ c.city_name,l.street_address from dmhr.city c full join dmhr.location l on c.city_id=l.city_id;
1 #NSET2: [1, 11, 96]
2 #PRJT2: [1, 11, 96]; exp_num(2), is_atom(FALSE)
3 #HASH FULL JOIN2: [1, 11, 96]; key_num(1), mix_aagr(0), mix_dist(0) KEY(C.CITY_ID=L.CITY_ID)
4 #CSCN2: [1, 11, 96]; INDEX33555469(CITY as C); btr_scan(1)
5 #CSCN2: [1, 11, 96]; INDEX33555472(LOCATION as L); btr_scan(1)
换为内连接测试：
SQL> alter session set events '10053 trace name context forever, level 1';
操作已执行
已用时间: 1.036(毫秒). 执行号:501.
SQL> select c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
行号 CITY_NAME STREET_ADDRESS
1 北京 海淀区北三环西路48号
2 石家庄 桥西区槐安东路28号
......
11 rows got
已用时间: 2.112(毫秒). 执行号:502.
SQL> select /*+use_nl(c,l) */ c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
行号 CITY_NAME STREET_ADDRESS
1 北京 海淀区北三环西路48号
2 石家庄 桥西区槐安东路28号
......
11 rows got
已用时间: 1.105(毫秒). 执行号:503.
SQL> select /*+use_hash(c,l) */ c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
行号 CITY_NAME STREET_ADDRESS
1 北京 海淀区北三环西路48号
2 成都 金牛区人民北路1号
......
11 rows got
已用时间: 1.776(毫秒). 执行号:504.
SQL>
第一条SQL：
*** 2024-04-11 18:01:04.466000000
*** Start trace 10053 event [level 1]
Current SQL Statement:
select c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
......
*** Plan before optimized:
project[0x7fd7fc057aa0] n_exp(2)
nest loop join[0x7fd7fc0567a0] (C.CITY_ID = L.CITY_ID)
base table[0x7fd7fc056dd8] (C, FULL SEARCH)
base table[0x7fd7fc057410] (L, FULL SEARCH)
*** cross join leaf rel pre-process start
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
*** cross join leaf rel pre-process complete
*** cross join optimization start...
---------------- [Join Order 1 of 2] ----------------
{ "C" }{ "L" }
<<<<< inner join card estimate (not push down filter) >>>>>
*** stdesc 1: C.CITY_ID = L.CITY_ID ---> st: 1.00000
......
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 10.72893
*** index inner join cost: 0.76923 (IND_LOCATION_ID, EQU)
*** hash inner join cost: 0.94714
*** merge inner join is not available
current best plan(1, 1), cost: 0.76923
index join[0x7fd7fc0925a8] (cost: 0.76923, rows: 11)
base table[0x7fd7fc092bd0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc0931f8] (L, IND_LOCATION_ID, EQU SEARCH) (cost: 0.72335, rows: 1)
---------------- [Join Order 2 of 2] ----------------
{ "L" }{ "C" }
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join is not available
*** index inner join cost: 0.76923 (INDEX33555470, EQU)
*** hash inner join cost: 0.94714
*** merge inner join is not available
no better plan found
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fd7fc06ccc0] n_exp(2) (cost: 0.76923, rows: 11)
index join[0x7fd7fc06d668] (cost: 0.76923, rows: 11)
base table[0x7fd7fc06dca0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc06e2d8] (L, IND_LOCATION_ID, EQU SEARCH) (cost: 0.72335, rows: 1)
第二条SQL
*** 2024-04-11 18:01:20.703000000
*** Start trace 10053 event [level 1]
Current SQL Statement:
select /*+use_nl(c,l) */ c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
......
*** join hint : USE_NL(C L) specified.
*** Plan before optimized:
project[0x7fd7fc054a40] n_exp(2)
nest loop join[0x7fd7fc053740] (C.CITY_ID = L.CITY_ID)
base table[0x7fd7fc053d78] (C, FULL SEARCH)
base table[0x7fd7fc0543b0] (L, FULL SEARCH)
*** Hints for current project[0x7fd7fc054a40]
*** join hint : USE_NL(C L) specified.
*** cross join leaf rel pre-process start
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
*** cross join leaf rel pre-process complete
*** cross join optimization start...
---------------- [Join Order 1 of 2] ----------------
{ "C" }{ "L" }
<<<<< inner join card estimate (not push down filter) >>>>>
*** stdesc 1: C.CITY_ID = L.CITY_ID ---> st: 1.00000
......
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 10.72893
*** index inner join is bypassed due to hint
*** hash inner join is bypassed due to hint
*** merge inner join is bypassed due to hint
current best plan(1, 1), cost: 10.72893
nest loop join[0x7fd7fc0925a8] (C.CITY_ID = L.CITY_ID)(cost: 10.72893, rows: 11)
base table[0x7fd7fc092bd0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc0931f8] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fd7fc0686b0] n_exp(2) (cost: 10.72893, rows: 11)
select[0x7fd7fc06a940] (C.CITY_ID = L.CITY_ID)(cost: 10.72893, rows: 11)
nest loop join[0x7fd7fc069058] (cost: 10.72893, rows: 11)
base table[0x7fd7fc069cd0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc06a308] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
-------------------------- END --------------------------
第三条SQL
*** 2024-04-11 18:01:32.402000000
*** Start trace 10053 event [level 1]
Current SQL Statement:
select /*+use_hash(c,l) */ c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
......
*** join hint : USE_HASH(C L) specified.
*** Plan before optimized:
project[0x7fd7fc054a40] n_exp(2)
nest loop join[0x7fd7fc053740] (C.CITY_ID = L.CITY_ID)
base table[0x7fd7fc053d78] (C, FULL SEARCH)
base table[0x7fd7fc0543b0] (L, FULL SEARCH)
*** Hints for current project[0x7fd7fc054a40]
*** join hint : USE_HASH(C L) specified.
*** cross join leaf rel pre-process start
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
*** cross join leaf rel pre-process complete
*** cross join optimization start...
---------------- [Join Order 1 of 2] ----------------
{ "C" }{ "L" }
<<<<< inner join card estimate (not push down filter) >>>>>
*** stdesc 1: C.CITY_ID = L.CITY_ID ---> st: 1.00000
......
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 10.72893
*** index inner join is bypassed due to hint
*** hash inner join cost: 0.94714
*** merge inner join is bypassed due to hint
current best plan(1, 1), cost: 0.94714
hash inner join[0x7fd7fc0925a8] (cost: 0.94714, rows: 11)
base table[0x7fd7fc092bd0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc0931f8] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fd7fc0683d0] n_exp(2) (cost: 0.94714, rows: 11)
hash inner join[0x7fd7fc068d78] (cost: 0.94714, rows: 11)
base table[0x7fd7fc069720] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc069d58] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
-------------------------- END --------------------------
第一条SQL：
《[Join Order 1 of 2]》章节是优化的具体过程如：
*** cross join optimization start...
---------------- [Join Order 1 of 2] ----------------
{ "C" }{ "L" }
.......
current best plan(1, 1), cost: 0.76923
index join[0x7fd7fc0925a8] (cost: 0.76923, rows: 11)
base table[0x7fd7fc092bd0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc0931f8] (L, IND_LOCATION_ID, EQU SEARCH) (cost: 0.72335, rows: 1)
......
---------------- [Join Order 2 of 2] ----------------
{ "L" }{ "C" }
no better plan found
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fd7fc06ccc0] n_exp(2) (cost: 0.76923, rows: 11)
index join[0x7fd7fc06d668] (cost: 0.76923, rows: 11)
base table[0x7fd7fc06dca0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc06e2d8] (L, IND_LOCATION_ID, EQU SEARCH) (cost: 0.72335, rows: 1)
第二三条SQL因为使用的HINT，强制SQL按照指定的方式去走。
第二条SQL：
*** join hint : USE_NL(C L) specified.
......
*** Hints for current project[0x7fd7fc054a40]
*** join hint : USE_NL(C L) specified.
......
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 10.72893
*** index inner join is bypassed due to hint
*** hash inner join is bypassed due to hint
*** merge inner join is bypassed due to hint
current best plan(1, 1), cost: 10.72893
nest loop join[0x7fd7fc0925a8] (C.CITY_ID = L.CITY_ID)(cost: 10.72893, rows: 11)
base table[0x7fd7fc092bd0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc0931f8] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fd7fc0686b0] n_exp(2) (cost: 10.72893, rows: 11)
select[0x7fd7fc06a940] (C.CITY_ID = L.CITY_ID)(cost: 10.72893, rows: 11)
nest loop join[0x7fd7fc069058] (cost: 10.72893, rows: 11)
base table[0x7fd7fc069cd0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc06a308] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
第三条SQL：
*** join hint : USE_HASH(C L) specified.
......
*** Hints for current project[0x7fd7fc054a40]
*** join hint : USE_HASH(C L) specified.
......
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 10.72893
*** index inner join is bypassed due to hint
*** hash inner join cost: 0.94714
*** merge inner join is bypassed due to hint
current best plan(1, 1), cost: 0.94714
hash inner join[0x7fd7fc0925a8] (cost: 0.94714, rows: 11)
base table[0x7fd7fc092bd0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc0931f8] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7fd7fc0683d0] n_exp(2) (cost: 0.94714, rows: 11)
hash inner join[0x7fd7fc068d78] (cost: 0.94714, rows: 11)
base table[0x7fd7fc069720] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7fd7fc069d58] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
4.2OPTIMIZER_MODE=1
当OPTIMIZER_MODE=1时，优化器使用左深树的方式进行优化空间探测。以内连接为例，对于N个表的内连接，优化器会考虑N!种连接顺序，每种排列会考虑三种状态：过滤条件下放、过滤条件不下放和采用变var方式探测。上述每种状态又会尝试四种连接方式如嵌套循环连接、HASH连接等。理论上N个表的内连接的计划探测空间最大为N! * 3 * 4，半连接和外连接的处理大体类似。下面看一个简单的例子。
SQL> alter system set 'OPTIMIZER_MODE' = 1 both;
DMSQL 过程已成功完成
已用时间: 11.772(毫秒). 执行号:601.
SQL> show parameter OPTIMIZER_MODE
行号 PARA_NAME PARA_VALUE
1 OPTIMIZER_MODE 1
执行3条SQL
SQL> alter session set events '10053 trace name context forever, level 1';
操作已执行
已用时间: 1.535(毫秒). 执行号:501.
SQL> select c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
行号 CITY_NAME STREET_ADDRESS
1 北京 海淀区北三环西路48号
2 石家庄 桥西区槐安东路28号
......
11 rows got
已用时间: 3.004(毫秒). 执行号:502.
SQL> select /*+use_nl(c,l) */ c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
行号 CITY_NAME STREET_ADDRESS
1 北京 海淀区北三环西路48号
2 石家庄 桥西区槐安东路28号
......
11 rows got
已用时间: 1.116(毫秒). 执行号:503.
SQL> select /*+use_hash(c,l) */ c.city_name,l.street_address from dmhr.city c,dmhr.location l where c.city_id=l.city_id;
行号 CITY_NAME STREET_ADDRESS
1 北京 海淀区北三环西路48号
2 成都 金牛区人民北路1号
......
11 rows got
已用时间: 3.102(毫秒). 执行号:504.
第一条：
......
*** Plan before optimized:
project[0x7f8450067ef0] n_exp(2)
nest loop join[0x7f8450066bf0] (C.CITY_ID = L.CITY_ID)
base table[0x7f8450067228] (C, FULL SEARCH)
base table[0x7f8450067860] (L, FULL SEARCH)
*** cross join leaf rel pre-process start
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
*** cross join leaf rel pre-process complete
*** cross join optimization start...
---------------- [Join Order 1 of 2] ----------------
{ "C" }{ "L" }
<<<<< inner join card estimate (not push down filter) >>>>>
*** stdesc 1: C.CITY_ID = L.CITY_ID ---> st: 1.00000
......
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
--各类状态和连接方式的探测
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 10.72893
*** index inner join cost: 0.76923 (IND_LOCATION_ID, EQU)
*** hash inner join cost: 0.94714
*** merge inner join is not available
current best plan(1, 1), cost: 0.76923
index join[0x7f84500a29f8] (cost: 0.76923, rows: 11)
base table[0x7f84500a3020] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7f84500a3648] (L, IND_LOCATION_ID, EQU SEARCH) (cost: 0.72335, rows: 1)
---------------- [Join Order 2 of 2] ----------------
{ "L" }{ "C" }
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join is not available
*** index inner join cost: 0.76923 (INDEX33555470, EQU)
*** hash inner join cost: 0.94714
*** merge inner join is not available
no better plan found
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7f845007d110] n_exp(2) (cost: 0.76923, rows: 11)
index join[0x7f845007dab8] (cost: 0.76923, rows: 11)
base table[0x7f845007e0f0] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7f845007e728] (L, IND_LOCATION_ID, EQU SEARCH) (cost: 0.72335, rows: 1)
-------------------------- END --------------------------
第二条
*** join hint : USE_NL(C L) specified.
*** Plan before optimized:
project[0x7f8450064e90] n_exp(2)
nest loop join[0x7f8450063b90] (C.CITY_ID = L.CITY_ID)
base table[0x7f84500641c8] (C, FULL SEARCH)
base table[0x7f8450064800] (L, FULL SEARCH)
*** Hints for current project[0x7f8450064e90]
*** join hint : USE_NL(C L) specified.
*** cross join leaf rel pre-process start
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
*** cross join leaf rel pre-process complete
*** cross join optimization start...
---------------- [Join Order 1 of 2] ----------------
{ "C" }{ "L" }
<<<<< inner join card estimate (not push down filter) >>>>>
*** stdesc 1: C.CITY_ID = L.CITY_ID ---> st: 1.00000
......
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 10.72893
*** index inner join is bypassed due to hint
*** hash inner join is bypassed due to hint
*** merge inner join is bypassed due to hint
current best plan(1, 1), cost: 10.72893
nest loop join[0x7f84500a29f8] (C.CITY_ID = L.CITY_ID)(cost: 10.72893, rows: 11)
base table[0x7f84500a3020] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7f84500a3648] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7f8450078b00] n_exp(2) (cost: 10.72893, rows: 11)
select[0x7f845007ad90] (C.CITY_ID = L.CITY_ID)(cost: 10.72893, rows: 11)
nest loop join[0x7f84500794a8] (cost: 10.72893, rows: 11)
base table[0x7f845007a120] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7f845007a758] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
-------------------------- END --------------------------
第三条
*** join hint : USE_HASH(C L) specified.
*** Plan before optimized:
project[0x7f8450064e90] n_exp(2)
nest loop join[0x7f8450063b90] (C.CITY_ID = L.CITY_ID)
base table[0x7f84500641c8] (C, FULL SEARCH)
base table[0x7f8450064800] (L, FULL SEARCH)
*** Hints for current project[0x7f8450064e90]
*** join hint : USE_HASH(C L) specified.
*** cross join leaf rel pre-process start
---------------- single table access path probe for C ----------------
*** path 1: INDEX33555469 (FULL search), cost: 0.04588
best access path: INDEX33555469 (FULL search), cost: 0.04588
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
*** cross join leaf rel pre-process complete
*** cross join optimization start...
---------------- [Join Order 1 of 2] ----------------
{ "C" }{ "L" }
<<<<< inner join card estimate (not push down filter) >>>>>
*** stdesc 1: C.CITY_ID = L.CITY_ID ---> st: 1.00000
......
---------------- single table access path probe for L ----------------
*** path 1: INDEX33555472 (FULL search), cost: 0.04588
best access path: INDEX33555472 (FULL search), cost: 0.04588
---------------- inner join method probe (not push down filter) ----------------
*** nest loop inner join cost: 10.72893
*** index inner join is bypassed due to hint
*** hash inner join cost: 0.94714
*** merge inner join is bypassed due to hint
current best plan(1, 1), cost: 0.94714
hash inner join[0x7f84500a29f8] (cost: 0.94714, rows: 11)
base table[0x7f84500a3020] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7f84500a3648] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
*** BEST PLAN FOR THIS STATEMENT ***
project[0x7f8450078820] n_exp(2) (cost: 0.94714, rows: 11)
hash inner join[0x7f84500791c8] (cost: 0.94714, rows: 11)
base table[0x7f8450079b70] (C, INDEX33555469, FULL SEARCH) (cost: 0.04588, rows: 11)
base table[0x7f845007a1a8] (L, INDEX33555472, FULL SEARCH) (cost: 0.04588, rows: 11)
-------------------------- END --------------------------
作者
文章
阅读量
获赞
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1