0. 排查sql
select EXECSQLEXECTIMERANGE,count(*) from SQLLOG_OA_2024
group by EXECSQLEXECTIMERANGE
select execsqlstr,paramsstr,count(*) from (
select to_char(execsqlstr) execsqlstr,to_char(paramsstr) paramsstr,length(paramsstr) from SQLLOG_OA_2024
where
(EXECSQLEXECTIMERANGE='10s_30s' or EXECSQLEXECTIMERANGE='30s_1min' or
EXECSQLEXECTIMERANGE='1min_5min')
and execsqlstr like '% citics_co_main %'
and ( length(paramsstr)!=0)
order by execsqlexectime desc) group by execsqlstr, paramsstr;
1. 88sql优化1-like+fd_id in
select citicscoma0_.fd_id as fd1_88_,
citicscoma0_.sync_data_to_calendar_time as sync2_88_,
citicscoma0_.fd_last_modified_time as fd3_88_,
citicscoma0_.doc_subject as doc4_88_,
citicscoma0_.fd_current_number as fd5_88_,
citicscoma0_.fd_feedback_modify as fd6_88_,
citicscoma0_.fd_feedback_executed as fd7_88_,
citicscoma0_.fd_number as fd8_88_,
citicscoma0_.doc_creator_id as doc9_88_,
citicscoma0_.doc_create_time as doc10_88_,
citicscoma0_.fd_department_id as fd11_88_,
citicscoma0_.doc_publish_time as doc12_88_,
citicscoma0_.doc_read_count as doc13_88_,
citicscoma0_.extend_file_path as extend14_88_,
citicscoma0_.fd_use_form as fd17_88_,
citicscoma0_.fd_disable_mobile_form as fd18_88_,
citicscoma0_.doc_status as doc19_88_,
citicscoma0_.auth_att_nodownload as auth20_88_,
citicscoma0_.auth_att_nocopy as auth21_88_,
citicscoma0_.auth_att_noprint as auth22_88_,
citicscoma0_.auth_reader_flag as auth23_88_,
citicscoma0_.fd_change_reader_flag as fd24_88_,
citicscoma0_.fd_rbp_flag as fd25_88_,
citicscoma0_.fd_change_att as fd26_88_,
citicscoma0_.fd_model_name as fd27_88_,
citicscoma0_.fd_model_id as fd28_88_,
citicscoma0_.fd_work_id as fd29_88_,
citicscoma0_.fd_phase_id as fd30_88_,
citicscoma0_.fd_title_regulation as fd31_88_,
citicscoma0_.fd_template_id as fd32_88_,
citicscoma0_.auth_area_id as auth33_88_
from citics_co_main citicscoma0_
where citicscoma0_.fd_id in (select citicscoma1_.fd_id
from citics_co_main citicscoma1_
inner join citics_co_main_areader authallrea2_
on citicscoma1_.fd_id=authallrea2_.fd_doc_id
inner join sys_org_element sysorgelem3_
on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id
where (lower(citicscoma1_.doc_subject) like '%安鑫固收20号%')
and (sysorgelem3_.fd_id in ('1183b0b84ee4f581bba001c47a78b39d',
'16f579a9a65cbdb583f878940a9b64fd',
'15cedbcac0ca5181d5b9afe47f2875bb',
'171ba1f2a7bee7e39ed03934fafad9e9',
'17216119369cdceaf7068fa49edab66b',
'17494e28b43045f2c07c6d946bb979ad',
'1549efdc8f72b8e452be9524e0eac685',
'1549f068e46b26d8d970562414b8d0e7',
'16b880568d334e113d1603340dabf692',
'1635c98810526a9b5e57e884bd39d66b',
'16fa715c85b35d9db5e65124021b672b',
'16fc74e4d78c015f04f045e41f4950ce',
'17ae0dc5b3ce706d4e90f4c47c5b9e53',
'16f78e2e9c67cf5c645bbcc4c3ba9ead',
'172a74e6a8da8ecf2a296664ae78f80d',
'1725422173088761b8e15d44e21b3ada',
'1886b80ab2b3b37c7456ad24ba8afd30',
'188b81998f6a725d9a39b5541fb895d4',
'15678d66c764ab9b3e616b748de9da06',
'186b16f95b0a606bc2a4cfa4cf1ac8dc',
'1842cc006fa8cfc00e3b1d54b3585ffa',
'18d3915c891f81e75d23ddd497883822' ,
'18ecc3ecbacbfda9ed9dac9483aafb47')))
order by citicscoma0_.doc_create_time desc,
citicscoma0_.fd_id desc limit 15;
原始用时51s。
执行计划：
1 #NSET2: [6015, 14, 1179]
2 #PRJT2: [6015, 14, 1179]; exp_num(32), is_atom(FALSE)
3 #TOPN2: [6015, 14, 1179]; top_num(15)
4 #SORT3: [6015, 14, 1179]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(1)
5 #HASH RIGHT SEMI JOIN2: [5951, 170960, 1179]; n_keys(1) KEY(DMTEMPVIEW_892977291.colname=CITICSCOMA0_.FD_ID) KEY_NULL_EQU(0)
6 #PRJT2: [1172, 170960, 192]; exp_num(1), is_atom(FALSE)
7 #HASH2 INNER JOIN: [1172, 170960, 192]; RKEY_UNIQUE KEY_NUM(1); KEY(AUTHALLREA2_.FD_DOC_ID=CITICSCOMA1_.FD_ID) KEY_NULL_EQU(0)
8 #NEST LOOP INDEX JOIN2: [25, 170960, 96]
9 #CONST VALUE LIST: [1, 23, 48]; row_num(23), col_num(1),
10 #SSEK2: [25, 7433, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER as AUTHALLREA2_), scan_range[(DMTEMPVIEW_892977293.colname,min),(DMTEMPVIEW_892977293.colname,max))
11 #SLCT2: [1089, 406396, 96]; exp11 LIKE '%安鑫固收20号%'
12 #SSCN: [1089, 8119804, 96]; IDX_DM_20236274(CITICS_CO_MAIN as CITICSCOMA1_)
13 #BLKUP2: [3043, 8119804, 1179]; I_DMTEST_L008(CITICSCOMA0_)
14 #SSEK2: [3043, 8119804, 1179]; scan_type(DESC), I_DMTEST_L008(CITICS_CO_MAIN as CITICSCOMA0_), scan_range((min,min,min),(max,max,max))
1 #NSET2: [6015, 14->10, 1179]
2 #PRJT2: [6015, 14->10, 1179]; exp_num(32), is_atom(FALSE)
3 #TOPN2: [6015, 14->10, 1179];
4 #SORT3: [6015, 14, 1179]; key_num(2), is_distinct(FALSE), is_adaptive(1), MEM_USED(0KB), DISK_USED(0KB)
5 #HASH RIGHT SEMI JOIN2: [5951, 170960->10, 1179]; key_num(1), MEM_USED(21183KB), DISK_USED(0KB) KEY(DMTEMPVIEW_891775939.colname=CITICSCOMA0_.FD_ID) KEY_NULL_EQU(0)
6 #PRJT2: [1172, 170960->21, 192]; exp_num(1), is_atom(FALSE)
7 #HASH2 INNER JOIN: [1172, 170960->21, 192]; RKEY_UNIQUE KEY_NUM(1), MEM_USED(70335KB), DISK_USED(0KB) KEY(AUTHALLREA2_.FD_DOC_ID=CITICSCOMA1_.FD_ID) KEY_NULL_EQU(0)
8 #NEST LOOP INDEX JOIN2: [25, 170960->214969, 96]
9 #CONST VALUE LIST: [1, 23->23, 48]; row_num(23), col_num(1),
10 #SSEK2: [25, 7433->214969, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER), scan_range[(DMTEMPVIEW_891775941.colname,min),(DMTEMPVIEW_891775941.colname,max))
11 #SLCT2: [1089, 406392->63, 96]; exp11 LIKE '%安鑫固收20号%'
12 #SSCN: [1089, 8119804->8127847, 96]; IDX_DM_20236274(CITICS_CO_MAIN)
13 #BLKUP2: [3043, 8119804->8127846, 1179]; I_DMTEST_L008(CITICS_CO_MAIN)
14 #SSEK2: [3043, 8119804->8127846, 1179]; scan_type(DESC), I_DMTEST_L008(CITICS_CO_MAIN), scan_range((min,min,min),(max,max,max))
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
24287037 logical reads
294839 physical reads
0 redo size
10407 bytes sent to client
4551 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
94165 io wait time(ms)
158329 exec time(ms)
已用时间: 00:02:38.329. 执行号:36768604.
1 #NSET2: [6015, 14, 1179]
2 #PRJT2: [6015, 14, 1179]; exp_num(32), is_atom(FALSE)
3 #TOPN2: [6015, 14, 1179]; top_num(15)
4 #SORT3: [6015, 14, 1179]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(1)
5 #HASH RIGHT SEMI JOIN2: [5951, 170960, 1179]; n_keys(1) KEY(DMTEMPVIEW_892977291.colname=CITICSCOMA0_.FD_ID) KEY_NULL_EQU(0)
6 #PRJT2: [1172, 170960, 192]; exp_num(1), is_atom(FALSE)
7 #HASH2 INNER JOIN: [1172, 170960, 192]; RKEY_UNIQUE KEY_NUM(1); KEY(AUTHALLREA2_.FD_DOC_ID=CITICSCOMA1_.FD_ID) KEY_NULL_EQU(0)
8 #NEST LOOP INDEX JOIN2: [25, 170960, 96]
9 #CONST VALUE LIST: [1, 23, 48]; row_num(23), col_num(1),
10 #SSEK2: [25, 7433, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER as AUTHALLREA2_), scan_range[(DMTEMPVIEW_892977293.colname,min),(DMTEMPVIEW_892977293.colname,max))
11 #SLCT2: [1089, 406396, 96]; exp11 LIKE '%安鑫固收20号%'
12 #SSCN: [1089, 8119804, 96]; IDX_DM_20236274(CITICS_CO_MAIN as CITICSCOMA1_)
13 #BLKUP2: [3043, 8119804, 1179]; I_DMTEST_L008(CITICSCOMA0_)
14 #SSEK2: [3043, 8119804, 1179]; scan_type(DESC), I_DMTEST_L008(CITICS_CO_MAIN as CITICSCOMA0_), scan_range((min,min,min),(max,max,max))
1 #NSET2: [6015, 15->10, 1179]
2 #PRJT2: [6015, 15->10, 1179]; exp_num(32), is_atom(FALSE)
3 #SORT3: [6015, 15->10, 1179]; key_num(2), is_distinct(FALSE), is_adaptive(0), MEM_USED(18432KB), DISK_USED(0KB)
4 #HASH RIGHT SEMI JOIN2: [5951, 170959->10, 1179]; key_num(1), MEM_USED(21183KB), DISK_USED(0KB) KEY(DMTEMPVIEW_891776098.colname=CITICSCOMA0_.FD_ID) KEY_NULL_EQU(0)
5 #PRJT2: [1172, 170959->21, 192]; exp_num(1), is_atom(FALSE)
6 #HASH2 INNER JOIN: [1172, 170959->21, 192]; RKEY_UNIQUE KEY_NUM(1), MEM_USED(70335KB), DISK_USED(0KB) KEY(AUTHALLREA2_.FD_DOC_ID=CITICSCOMA1_.FD_ID) KEY_NULL_EQU(0)
7 #NEST LOOP INDEX JOIN2: [25, 170960->214969, 96]
8 #CONST VALUE LIST: [1, 23->23, 48]; row_num(23), col_num(1),
9 #SSEK2: [25, 7433->214969, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER), scan_range[(DMTEMPVIEW_891776100.colname,min),(DMTEMPVIEW_891776100.colname,max))
10 #SLCT2: [1089, 406394->63, 96]; exp11 LIKE '%安鑫固收20号%'
11 #SSCN: [1089, 8119804->8127889, 96]; IDX_DM_20236274(CITICS_CO_MAIN)
12 #BLKUP2: [3043, 8119804->8127897, 1179]; I_DMTEST_L008(CITICS_CO_MAIN)
13 #SSCN: [3043, 8119804->8127897, 1179]; I_DMTEST_L008(CITICS_CO_MAIN)
分析autotrace可知，无论加不加hint，80%的时间都耗费在CITICS_CO_MAIN表的回表上。
加HINT /*+ enable_index_filter(1) enable_hash_join(0) use_nl_with_index(authallrea2_,IDX_DM_20236274)*/ 调整。
enable_hash_join(0) 可去掉。
/*+ enable_index_filter(1) use_nl_with_index(authallrea2_,IDX_DM_20236274)*/
此HINT适合查询条件没有日期，且LIKE匹配过滤性较好的sql。优化后2.776s。
1 #NSET2: [19639, 1->10, 1371]
2 #PRJT2: [19639, 1->10, 1371]; exp_num(32), is_atom(FALSE)
3 #SORT3: [19639, 1->10, 1371]; key_num(2), is_distinct(FALSE), is_adaptive(0), MEM_USED(30720KB), DISK_USED(0KB)
4 #NEST LOOP INDEX JOIN2: [19638, 1->10, 1371]
5 #DISTINCT: [19638, 1->10, 192], MEM_USED(7KB), DISK_USED(0KB)
6 #PRJT2: [19603, 170960->21, 192]; exp_num(1), is_atom(FALSE)
7 #NEST LOOP INDEX JOIN2: [19603, 170960->21, 192]
8 #NEST LOOP INDEX JOIN2: [25, 170960->214975, 96]
9 #CONST VALUE LIST: [1, 23->23, 48]; row_num(23), col_num(1),
10 #SSEK2: [25, 7433->214975, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER), scan_range[(DMTEMPVIEW_891778451.colname,min),(DMTEMPVIEW_891778451.colname,max))
11 #SLCT2: [763, 1->21, 96]; exp11 > 0
12 #SSEK2: [763, 1->214975, 96]; scan_type(ASC), IDX_DM_20236274(CITICS_CO_MAIN), scan_range[(AUTHALLREA2_.FD_DOC_ID,min),(AUTHALLREA2_.FD_DOC_ID,max))
13 #BLKUP2: [1, 1->10, 48]; INDEX33589155(CITICS_CO_MAIN)
14 #SSEK2: [1, 1->10, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN), scan_range[DMTEMPVIEW_891778449.colname,DMTEMPVIEW_891778449.colname]
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
838778 logical reads
24514 physical reads
0 redo size
10415 bytes sent to client
9660 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
882 io wait time(ms)
5115 exec time(ms)
已用时间: 00:00:05.115. 执行号:36768612.
SQL> et(36768612);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
1 PRJT2 3 0% 14 2 4 0 0 0
0 NULL NULL 0
2 DLCK 3 0% 14 0 2 0 0 0
0 NULL NULL 0
3 IJI2 12 0% 13 4 33 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
4 CONSTV 21 0% 12 9 2 0 0 0
0 NULL NULL 0
5 PRJT2 35 0% 11 6 44 0 0 0
0 NULL NULL 0
6 NSET2 125 0% 10 1 3 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
7 SSEK2 148 0% 9 14 20 0 0 0
0 NULL NULL 0
8 BLKUP2 191 0% 8 13 40 0 0 0
0 NULL NULL 0
9 SORT3 209 0% 7 3 13 30720 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
10 DIST 221 0% 6 5 24 7 0 10
0 NULL NULL 0
11 IJI2 2259 0.04% 5 8 1462 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
12 SSEK2 23407 0.47% 4 10 741 0 0 0
0 NULL NULL 0
13 IJI2 28936 0.57% 3 7 215737 0 0 0
0 NULL NULL 0
14 SLCT2 291997 5.8% 2 11 644946 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
15 SSEK2 4685253 93.09% 1 12 429950 0 0 0
0 NULL NULL 0
15 rows got
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
276 logical reads
0 physical reads
0 redo size
3135 bytes sent to client
78 bytes received from client
1 roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
15 rows processed
0 io wait time(ms)
22 exec time(ms)
已用时间: 22.144(毫秒). 执行号:36768613.
2. 88sql优化2-同sql 1
--set schema ekp;
select /*+ enable_index_filter(1) use_nl_with_index(authallrea2_,IDX_DM_20236274)*/ citicscoma0_.fd_id as fd1_88_,
citicscoma0_.sync_data_to_calendar_time as sync2_88_,
citicscoma0_.fd_last_modified_time as fd3_88_,
citicscoma0_.doc_subject as doc4_88_,
citicscoma0_.fd_current_number as fd5_88_,
citicscoma0_.fd_feedback_modify as fd6_88_,
citicscoma0_.fd_feedback_executed as fd7_88_,
citicscoma0_.fd_number as fd8_88_,
citicscoma0_.doc_creator_id as doc9_88_,
citicscoma0_.doc_create_time as doc10_88_,
citicscoma0_.fd_department_id as fd11_88_,
citicscoma0_.doc_publish_time as doc12_88_,
citicscoma0_.doc_read_count as doc13_88_,
citicscoma0_.extend_file_path as extend14_88_,
citicscoma0_.fd_use_form as fd17_88_,
citicscoma0_.fd_disable_mobile_form as fd18_88_,
citicscoma0_.doc_status as doc19_88_,
citicscoma0_.auth_att_nodownload as auth20_88_,
citicscoma0_.auth_att_nocopy as auth21_88_,
citicscoma0_.auth_att_noprint as auth22_88_,
citicscoma0_.auth_reader_flag as auth23_88_,
citicscoma0_.fd_change_reader_flag as fd24_88_,
citicscoma0_.fd_rbp_flag as fd25_88_,
citicscoma0_.fd_change_att as fd26_88_,
citicscoma0_.fd_model_name as fd27_88_,
citicscoma0_.fd_model_id as fd28_88_,
citicscoma0_.fd_work_id as fd29_88_,
citicscoma0_.fd_phase_id as fd30_88_,
citicscoma0_.fd_title_regulation as fd31_88_,
citicscoma0_.fd_template_id as fd32_88_,
citicscoma0_.auth_area_id as auth33_88_
from citics_co_main citicscoma0_
where citicscoma0_.fd_id in (select citicscoma1_.fd_id
from citics_co_main citicscoma1_
inner join citics_co_main_areader authallrea2_
on citicscoma1_.fd_id=authallrea2_.fd_doc_id
inner join sys_org_element sysorgelem3_
on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id
where (lower(citicscoma1_.doc_subject) like '%~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~%')
and (sysorgelem3_.fd_id in ('1183b0b84ee4f581bba001c47a78b39d',
'18e3358e5f3dfab8c9ae41a4eebafd91',
'1549efdc8f72b8e452be9524e0eac685',
'1549f068e9721199349b11143edad83d',
'1635c98810526a9b5e57e884bd39d66b',
'1862b074a031c8ad5379fc14551a7d0f',
'1886b80ab2b3b37c7456ad24ba8afd30',
'188b867a0d308722ea26453436998899' )))
order by citicscoma0_.doc_create_time desc,
citicscoma0_.fd_id desc limit 15;
--原始执行计划
1 #NSET2: [486, 1, 1371]
2 #PRJT2: [486, 1, 1371]; exp_num(32), is_atom(FALSE)
3 #SORT3: [486, 1, 1371]; key_num(2), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #NEST LOOP INDEX JOIN2: [485, 1, 1371]
5 #DISTINCT: [485, 1, 192]
6 #PRJT2: [483, 17019, 192]; exp_num(1), is_atom(FALSE)
7 #SLCT2: [483, 17019, 192]; exp11 LIKE exp_param(no:0)
8 #NEST LOOP INDEX JOIN2: [483, 17019, 192]
9 #NEST LOOP INDEX JOIN2: [2, 17019, 96]
10 #CONST VALUE LIST: [1, 8, 48]; row_num(8), col_num(1),
11 #SSEK2: [2, 2127, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER as AUTHALLREA2_), scan_range[(DMTEMPVIEW_895632136.colname,min),(DMTEMPVIEW_895632136.colname,max))
12 #BLKUP2: [111, 1, 48]; INDEX33589155(CITICSCOMA1_)
13 #SSEK2: [111, 1, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN as CITICSCOMA1_), scan_range[AUTHALLREA2_.FD_DOC_ID,AUTHALLREA2_.FD_DOC_ID]
14 #BLKUP2: [1, 1, 48]; INDEX33589155(CITICSCOMA0_)
15 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN as CITICSCOMA0_), scan_range[DMTEMPVIEW_895632131.colname,DMTEMPVIEW_895632131.colname]
--优化后执行计划
1 #NSET2: [385, 1, 1371]
2 #PRJT2: [385, 1, 1371]; exp_num(32), is_atom(FALSE)
3 #SORT3: [385, 1, 1371]; key_num(2), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #NEST LOOP INDEX JOIN2: [384, 1, 1371]
5 #DISTINCT: [384, 1, 192]
6 #PRJT2: [382, 17019, 192]; exp_num(1), is_atom(FALSE)
7 #NEST LOOP INDEX JOIN2: [382, 17019, 192]
8 #NEST LOOP INDEX JOIN2: [2, 17019, 96]
9 #CONST VALUE LIST: [1, 8, 48]; row_num(8), col_num(1),
10 #SSEK2: [2, 2127, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER as AUTHALLREA2_), scan_range[(DMTEMPVIEW_896322856.colname,min),(DMTEMPVIEW_896322856.colname,max))
11 #SLCT2: [76, 1, 96]; exp11 LIKE exp_param(no:0)
12 #SSEK2: [76, 1, 96]; scan_type(ASC), IDX_DM_20236274(CITICS_CO_MAIN as CITICSCOMA1_), scan_range[(AUTHALLREA2_.FD_DOC_ID,min),(AUTHALLREA2_.FD_DOC_ID,max))
13 #BLKUP2: [1, 1, 48]; INDEX33589155(CITICSCOMA0_)
14 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN as CITICSCOMA0_), scan_range[DMTEMPVIEW_896322854.colname,DMTEMPVIEW_896322854.colname]
3. 88sql优化3- 日期+like+fd_id in
select
/*+ enable_index_filter(1) enable_hash_join(0) */
--/*+ no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3) */
citicscoma0_.fd_id as fd1_88_,
citicscoma0_.sync_data_to_calendar_time as sync2_88_,
citicscoma0_.fd_last_modified_time as fd3_88_,
citicscoma0_.doc_subject as doc4_88_,
citicscoma0_.fd_current_number as fd5_88_,
citicscoma0_.fd_feedback_modify as fd6_88_,
citicscoma0_.fd_feedback_executed as fd7_88_,
citicscoma0_.fd_number as fd8_88_,
citicscoma0_.doc_creator_id as doc9_88_,
citicscoma0_.doc_create_time as doc10_88_,
citicscoma0_.fd_department_id as fd11_88_,
citicscoma0_.doc_publish_time as doc12_88_,
citicscoma0_.doc_read_count as doc13_88_,
citicscoma0_.extend_file_path as extend14_88_,
citicscoma0_.fd_use_form as fd17_88_,
citicscoma0_.fd_disable_mobile_form as fd18_88_,
citicscoma0_.doc_status as doc19_88_,
citicscoma0_.auth_att_nodownload as auth20_88_,
citicscoma0_.auth_att_nocopy as auth21_88_,
citicscoma0_.auth_att_noprint as auth22_88_,
citicscoma0_.auth_reader_flag as auth23_88_,
citicscoma0_.fd_change_reader_flag as fd24_88_,
citicscoma0_.fd_rbp_flag as fd25_88_,
citicscoma0_.fd_change_att as fd26_88_,
citicscoma0_.fd_model_name as fd27_88_,
citicscoma0_.fd_model_id as fd28_88_,
citicscoma0_.fd_work_id as fd29_88_,
citicscoma0_.fd_phase_id as fd30_88_,
citicscoma0_.fd_title_regulation as fd31_88_,
citicscoma0_.fd_template_id as fd32_88_,
citicscoma0_.auth_area_id as auth33_88_
from citics_co_main citicscoma0_
where citicscoma0_.fd_id in (select citicscoma1_.fd_id
from citics_co_main citicscoma1_
inner join citics_co_main_areader authallrea2_
on citicscoma1_.fd_id=authallrea2_.fd_doc_id
inner join sys_org_element sysorgelem3_
on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id
where (citicscoma1_.doc_create_time between '2023-04-15 23:59:59.000000' and '2024-04-16 23:59:59.000000')
and (lower(citicscoma1_.doc_subject) like '%h20230707%')
and (sysorgelem3_.fd_id in ( '1183b0b84ee4f581bba001c47a78b39d',
'1549f25a25ea99d34cd048348b2be4cc',
'156bb287c741e3b8ae0fef843c29665f',
'16b555735744411948abd404fa4ae663',
'1549efdc8f72b8e452be9524e0eac685',
'1549f068dff980100e71b984a77909b6',
'1549f06937dc04c1ce0f5374941896b4',
'1561feb428b61697160627540e0a8d2b',
'1635c98810526a9b5e57e884bd39d66b',
'16fa715c85b35d9db5e65124021b672b',
'1886b80ab2b3b37c7456ad24ba8afd30',
'173d5bdb25ce81ab440cc2c4dce99e80',
'175cfcda4083417279dd6da4480914e4',
'1756e5571731b4c198a126d4fc2a358a',
'1862b074a031c8ad5379fc14551a7d0f',
'184d11eb446ec50e56438c1439fb7110',
'187a3487ef99c58a50d8b054ff2a2ba4',
'172a755489c290bb9fe1b98439795b1e',
'186acd13696d4168388a00749818a26f',
'188b7e7052d39ade0d91f8d4ecfb38e3',
'18d3910060584892718563243318e31c')))
order by citicscoma0_.doc_create_time desc,
citicscoma0_.fd_id desc limit 15;
原始执行计划
1 #NSET2: [515, 152, 1384]
2 #PRJT2: [515, 152, 1384]; exp_num(32), is_atom(FALSE)
3 #SORT3: [515, 152, 1384]; key_num(2), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #NEST LOOP INDEX JOIN2: [508, 15241, 1384]
5 #DISTINCT: [67, 15241, 205]
6 #PRJT2: [63, 44676, 205]; exp_num(1), is_atom(FALSE)
7 #HASH2 INNER JOIN: [63, 44676, 205]; LKEY_UNIQUE KEY_NUM(1); KEY(CITICSCOMA1_.FD_ID=AUTHALLREA2_.FD_DOC_ID) KEY_NULL_EQU(0)
8 #SLCT2: [49, 15241, 109]; exp11 LIKE exp_param(no:2)
9 #SSEK2: [49, 304492, 109]; scan_type(ASC), IDX_DM_013002(CITICS_CO_MAIN as CITICSCOMA1_), scan_range[(exp_param(no:0),min,min),(exp_param(no:1),max,max))
10 #NEST LOOP INDEX JOIN2: [6, 44676, 96]
11 #CONST VALUE LIST: [1, 21, 48]; row_num(21), col_num(1),
12 #SSEK2: [6, 2127, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER as AUTHALLREA2_), scan_range[(DMTEMPVIEW_896379277.colname,min),(DMTEMPVIEW_896379277.colname,max))
13 #BLKUP2: [102, 1, 48]; INDEX33589155(CITICSCOMA0_)
14 #SSEK2: [102, 1, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN as CITICSCOMA0_), scan_range[DMTEMPVIEW_896379275.colname,DMTEMPVIEW_896379275.colname]
优化后执行计划
1 #NSET2: [11213, 15, 1384]
2 #PRJT2: [11213, 15, 1384]; exp_num(32), is_atom(FALSE)
3 #SORT3: [11213, 15, 1384]; key_num(2), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #NEST LOOP INDEX JOIN2: [11177, 83172, 1384]
5 #DISTINCT: [5166, 83172, 205]
6 #PRJT2: [5080, 914895, 205]; exp_num(1), is_atom(FALSE)
7 #NEST LOOP INDEX JOIN2: [5080, 914895, 205]
8 #SLCT2: [272, 83172, 109]; exp11 LIKE '%h20230707%'
9 #SSEK2: [272, 83172, 109]; scan_type(ASC), IDX_DM_013002(CITICS_CO_MAIN as CITICSCOMA1_), scan_range[(exp_cast('2023-04-15 23:59:59.000000'),min,min),(exp_cast('2024-04-16 23:59:59.000000'),max,max))
10 #SLCT2: [383, 11, 96]; AUTHALLREA2_.AUTH_ALL_READER_ID IN LIST
11 #SSEK2: [383, 11, 96]; scan_type(ASC), IDX_DM_202332304(CITICS_CO_MAIN_AREADER as AUTHALLREA2_), scan_range[(CITICSCOMA1_.FD_ID,min,min),(CITICSCOMA1_.FD_ID,max,max))
12 #BLKUP2: [557, 1, 48]; INDEX33589155(CITICSCOMA0_)
13 #SSEK2: [557, 1, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN as CITICSCOMA0_), scan_range[DMTEMPVIEW_897237076.colname,DMTEMPVIEW_897237076.colname]
trace
1 #NSET2: [11213, 15->2, 1384]
2 #PRJT2: [11213, 15->2, 1384]; exp_num(32), is_atom(FALSE)
3 #SORT3: [11213, 15->2, 1384]; key_num(2), is_distinct(FALSE), is_adaptive(0), MEM_USED(30720KB), DISK_USED(0KB)
4 #NEST LOOP INDEX JOIN2: [11177, 83172->2, 1384]
5 #DISTINCT: [5166, 83172->2, 205], MEM_USED(8814KB), DISK_USED(0KB)
6 #PRJT2: [5080, 914894->4, 205]; exp_num(1), is_atom(FALSE)
7 #NEST LOOP INDEX JOIN2: [5080, 914894->4, 205]
8 #SLCT2: [272, 83172->3, 109]; exp11 LIKE '%h20230707%'
9 #SSEK2: [272, 83172->1669811, 109]; scan_type(ASC), IDX_DM_013002(CITICS_CO_MAIN), scan_range[(exp_cast('2023-04-15 23:59:59.000000'),min,min),(exp_cast('2024-04-16 23:59:59.000000'),max,max))
10 #SLCT2: [383, 11->4, 96]; AUTHALLREA2_.AUTH_ALL_READER_ID IN LIST
11 #SSEK2: [383, 11->51, 96]; scan_type(ASC), IDX_DM_202332304(CITICS_CO_MAIN_AREADER), scan_range[(CITICSCOMA1_.FD_ID,min,min),(CITICSCOMA1_.FD_ID,max,max))
12 #BLKUP2: [557, 1->2, 48]; INDEX33589155(CITICS_CO_MAIN)
13 #SSEK2: [557, 1->2, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN), scan_range[DMTEMPVIEW_891780767.colname,DMTEMPVIEW_891780767.colname]
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
322 logical reads
14922 physical reads
0 redo size
4304 bytes sent to client
3329 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
3263 io wait time(ms)
5134 exec time(ms)
已用时间: 00:00:05.134. 执行号:36912108.
SQL> et(36912108);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
1 DLCK 3 0% 14 0 2 0 0 0
0 NULL NULL 0
2 PRJT2 5 0% 13 2 4 0 0 0
0 NULL NULL 0
3 IJI2 7 0% 12 4 9 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
4 IJI2 10 0% 10 7 12 0 0 0
0 NULL NULL 0
5 PRJT2 10 0% 10 6 6 0 0 0
0 NULL NULL 0
6 NSET2 77 0% 9 1 3 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
7 BLKUP2 118 0% 8 12 8 0 0 0
0 NULL NULL 0
8 SLCT2 147 0% 7 10 11 0 0 0
0 NULL NULL 0
9 SORT3 217 0% 6 3 5 30720 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
10 SSEK2 658 0.01% 5 13 4 0 0 0
0 NULL NULL 0
11 DIST 1383 0.03% 4 5 5 8814 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
12 SSEK2 3732 0.07% 3 11 6 0 0 0
0 NULL NULL 0
13 SLCT2 1102119 21.51% 2 8 5646 0 0 0
0 NULL NULL 0
14 SSEK2 4016017 78.37% 1 9 5642 0 0 0
0 NULL NULL 0
14 rows got
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
275 logical reads
1 physical reads
0 redo size
3000 bytes sent to client
78 bytes received from client
1 roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
0 io wait time(ms)
22 exec time(ms)
已用时间: 22.356(毫秒). 执行号:36912109.
执行时间2s左右
4. 88sql优化4, 日期+fd_id in
select /*+ no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3) */
citicscoma0_.fd_id as fd1_88_,
citicscoma0_.sync_data_to_calendar_time as sync2_88_,
citicscoma0_.fd_last_modified_time as fd3_88_,
citicscoma0_.doc_subject as doc4_88_,
citicscoma0_.fd_current_number as fd5_88_,
citicscoma0_.fd_feedback_modify as fd6_88_,
citicscoma0_.fd_feedback_executed as fd7_88_,
citicscoma0_.fd_number as fd8_88_,
citicscoma0_.doc_creator_id as doc9_88_,
citicscoma0_.doc_create_time as doc10_88_,
citicscoma0_.fd_department_id as fd11_88_,
citicscoma0_.doc_publish_time as doc12_88_,
citicscoma0_.doc_read_count as doc13_88_,
citicscoma0_.extend_file_path as extend14_88_,
citicscoma0_.fd_use_form as fd17_88_,
citicscoma0_.fd_disable_mobile_form as fd18_88_,
citicscoma0_.doc_status as doc19_88_,
citicscoma0_.auth_att_nodownload as auth20_88_,
citicscoma0_.auth_att_nocopy as auth21_88_,
citicscoma0_.auth_att_noprint as auth22_88_,
citicscoma0_.auth_reader_flag as auth23_88_,
citicscoma0_.fd_change_reader_flag as fd24_88_,
citicscoma0_.fd_rbp_flag as fd25_88_,
citicscoma0_.fd_change_att as fd26_88_,
citicscoma0_.fd_model_name as fd27_88_,
citicscoma0_.fd_model_id as fd28_88_,
citicscoma0_.fd_work_id as fd29_88_,
citicscoma0_.fd_phase_id as fd30_88_,
citicscoma0_.fd_title_regulation as fd31_88_,
citicscoma0_.fd_template_id as fd32_88_,
citicscoma0_.auth_area_id as auth33_88_
from citics_co_main citicscoma0_
where citicscoma0_.fd_id in (select citicscoma1_.fd_id
from citics_co_main citicscoma1_
inner join citics_co_main_areader authallrea2_
on citicscoma1_.fd_id=authallrea2_.fd_doc_id
inner join sys_org_element sysorgelem3_
on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id
where (citicscoma1_.doc_create_time between '2023-04-15 23:59:59.000000'
and '2024-04-16 23:59:59.000000')
and (sysorgelem3_.fd_id in ( '1183b0b84ee4f581bba001c47a78b39d',
'16f579a9a65cbdb583f878940a9b64fd',
'15cedbcac0ca5181d5b9afe47f2875bb',
'171ba1f2a7bee7e39ed03934fafad9e9',
'17216119369cdceaf7068fa49edab66b',
'17494e28b43045f2c07c6d946bb979ad',
'1549efdc8f72b8e452be9524e0eac685',
'1549f068e46b26d8d970562414b8d0e7',
'16b880568d334e113d1603340dabf692',
'1635c98810526a9b5e57e884bd39d66b',
'16fa715c85b35d9db5e65124021b672b',
'16fc74e4d78c015f04f045e41f4950ce',
'17ae0dc5b3ce706d4e90f4c47c5b9e53',
'16f78e2e9c67cf5c645bbcc4c3ba9ead',
'172a74e6a8da8ecf2a296664ae78f80d',
'1725422173088761b8e15d44e21b3ada',
'1886b80ab2b3b37c7456ad24ba8afd30',
'188b81998f6a725d9a39b5541fb895d4',
'15678d66c764ab9b3e616b748de9da06',
'186b16f95b0a606bc2a4cfa4cf1ac8dc',
'1842cc006fa8cfc00e3b1d54b3585ffa',
'18d3915c891f81e75d23ddd497883822',
'18ecc3ecbacbfda9ed9dac9483aafb47')))
order by citicscoma0_.doc_create_time desc,
citicscoma0_.fd_id desc limit 15
-- set schema ekp
原始执行计划
1 #NSET2: [2185, 489, 1336]
2 #PRJT2: [2185, 489, 1336]; exp_num(32), is_atom(FALSE)
3 #SORT3: [2185, 489, 1336]; key_num(2), is_distinct(FALSE), top_flag(1), is_adaptive(0)
4 #NEST LOOP INDEX JOIN2: [2165, 48930, 1336]
5 #DISTINCT: [84, 48930, 157]
6 #PRJT2: [80, 48930, 157]; exp_num(1), is_atom(FALSE)
7 #HASH2 INNER JOIN: [80, 48930, 157]; RKEY_UNIQUE KEY_NUM(1); KEY(AUTHALLREA2_.FD_DOC_ID=CITICSCOMA1_.FD_ID) KEY_NULL_EQU(0)
8 #NEST LOOP INDEX JOIN2: [7, 48930, 96]
9 #CONST VALUE LIST: [1, 23, 48]; row_num(23), col_num(1),
10 #SSEK2: [7, 2127, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER as AUTHALLREA2_), scan_range[(DMTEMPVIEW_897750630.colname,min),(DMTEMPVIEW_897750630.colname,max))
11 #SSEK2: [43, 304834, 61]; scan_type(ASC), IDX_DM_011202(CITICS_CO_MAIN as CITICSCOMA1_), scan_range[(exp_param(no:1),min),(exp_param(no:0),max))
12 #BLKUP2: [327, 1, 48]; INDEX33589155(CITICSCOMA0_)
13 #SSEK2: [327, 1, 48]; scan_type(ASC), INDEX33589155(CITICS_CO_MAIN as CITICSCOMA0_), scan_range[DMTEMPVIEW_897750626.colname,DMTEMPVIEW_897750626.colname]
使用hint /*+ no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3) */
1 #NSET2: [5248, 14->15, 1179]
2 #PRJT2: [5248, 14->15, 1179]; exp_num(32), is_atom(FALSE)
3 #TOPN2: [5248, 14->15, 1179];
4 #SORT3: [5248, 14, 1179]; key_num(2), is_distinct(FALSE), is_adaptive(1), MEM_USED(0KB), DISK_USED(0KB)
5 #HASH RIGHT SEMI JOIN2: [5184, 170960->17, 1179]; key_num(1), MEM_USED(21183KB), DISK_USED(0KB) KEY(DMTEMPVIEW_891781903.colname=CITICSCOMA0_.FD_ID) KEY_NULL_EQU(0)
6 #PRJT2: [407, 170960->41264, 157]; exp_num(1), is_atom(FALSE)
7 #HASH2 INNER JOIN: [407, 170960->41264, 157]; RKEY_UNIQUE KEY_NUM(1), MEM_USED(70335KB), DISK_USED(0KB) KEY(AUTHALLREA2_.FD_DOC_ID=CITICSCOMA1_.FD_ID) KEY_NULL_EQU(0)
8 #NEST LOOP INDEX JOIN2: [25, 170960->215022, 96]
9 #CONST VALUE LIST: [1, 23->23, 48]; row_num(23), col_num(1),
10 #SSEK2: [25, 7433->215022, 96]; scan_type(ASC), IDX_DM_011102(CITICS_CO_MAIN_AREADER), scan_range[(DMTEMPVIEW_891781905.colname,min),(DMTEMPVIEW_891781905.colname,max))
11 #SSEK2: [236, 1663513->1670147, 61]; scan_type(ASC), IDX_DM_011202(CITICS_CO_MAIN), scan_range[(exp_cast('2024-04-16 23:59:59.000000'),min),(exp_cast('2023-04-15 23:59:59.000000'),max))
12 #BLKUP2: [3043, 8119804->600, 1179]; I_DMTEST_L008(CITICS_CO_MAIN)
13 #SSEK2: [3043, 8119804->600, 1179]; scan_type(DESC), I_DMTEST_L008(CITICS_CO_MAIN), scan_range((min,min,min),(max,max,max))
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
3103 logical reads
5813 physical reads
0 redo size
10619 bytes sent to client
3316 bytes received from client
1 roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed
2775 io wait time(ms)
3370 exec time(ms)
已用时间: 00:00:03.370. 执行号:36912112.
SQL> et(36912112);
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
1 DLCK 3 0% 14 0 2 0 0 0
0 NULL NULL 0
2 SORT3 6 0% 13 4 4 0 0 0
0 NULL NULL 0
3 PRJT2 8 0% 12 2 6 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
4 CONSTV 20 0% 11 9 2 0 0 0
0 NULL NULL 0
5 TOPN2 72 0% 10 3 5 0 0 0
0 NULL NULL 0
6 NSET2 173 0.01% 9 1 4 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
7 SSEK2 395 0.01% 8 13 2 0 0 0
0 NULL NULL 0
8 PRJT2 1468 0.04% 7 6 7948 0 0 0
0 NULL NULL 0
9 IJI2 2186 0.07% 6 8 1462 0 0 0
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
10 BLKUP2 3895 0.12% 5 12 4 0 0 0
0 NULL NULL 0
11 HRS2 12078 0.36% 4 5 3978 21183 0 23145
0 NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS
---------- ----- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- --------------------
HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
-------------------- ----------------- --------------- --------------------
12 SSEK2 21798 0.65% 3 10 741 0 0 0
0 NULL NULL 0
13 HI3 371890 11.06% 2 7 10263 70335 0 248798
31814 NULL NULL 292122
14 SSEK2 2947843 87.69% 1 11 5570 0 0 0
0 NULL NULL 0
14 rows got
Statistics
-----------------------------------------------------------------
0 data pages changed
0 undo pages changed
275 logical reads
1 physical reads
0 redo size
3012 bytes sent to client
78 bytes received from client
1 roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
0 io wait time(ms)
22 exec time(ms)
已用时间: 22.465(毫秒). 执行号:36912113.
SQL>
5. 小结
a.
like + fd_id in 条件 /*+ enable_index_filter(1) use_nl_with_index(authallrea2_,IDX_DM_20236274)*/
b.
日期范围 + like + fd_id in /*+ enable_index_filter(1) enable_hash_join(0) */
c.
日期范围 + fd_id in /*+ no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3) */
绑定示例：
call sf_inject_hint('','no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3)','INJECT202401073',null,true,true);
--查看统计信息
select * from (
select s.last_gathered,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.v_min,s.v_max,s.n_buckets
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='EKP'
and (o.object_name='CITICS_CO_MAIN' OR o.object_name='CITICS_CO_MAIN_AREADER' OR o.object_name='SYS_ORG_ELEMENT' )
and o.object_type='TABLE')
order by b.column_id;
--20240417变化
SF_ALTER_HINT('INJECT202401075', 'STATUS', 'DISABLED');
--like + fd_id in
call sf_inject_hint('from citics_co_main citicscoma0_ where citicscoma0_.fd_id in (select citicscoma1_.fd_id from citics_co_main citicscoma1_ inner join citics_co_main_areader authallrea2_ on citicscoma1_.fd_id=authallrea2_.fd_doc_id inner join sys_org_element sysorgelem3_ on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id where (lower(citicscoma1_.doc_subject) like ?) and (sysorgelem3_.fd_id in (?','enable_index_filter(1) use_nl_with_index(authallrea2_,IDX_DM_20236274)','INJECT20240417',null,true,true);
--日期范围 + like + fd_id in
call sf_inject_hint('from citics_co_main citicscoma0_ where citicscoma0_.fd_id in (select citicscoma1_.fd_id from citics_co_main citicscoma1_ inner join citics_co_main_areader authallrea2_ on citicscoma1_.fd_id=authallrea2_.fd_doc_id inner join sys_org_element sysorgelem3_ on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id where (citicscoma1_.doc_create_time between ? and ?) and (lower(citicscoma1_.doc_subject) like ?) and (sysorgelem3_.fd_id in (?','enable_index_filter(1) enable_hash_join(0)','INJECT2024041702',null,true,true);
--日期范围 + fd_id in
call sf_inject_hint('from citics_co_main citicscoma0_ where citicscoma0_.fd_id in (select citicscoma1_.fd_id from citics_co_main citicscoma1_ inner join citics_co_main_areader authallrea2_ on citicscoma1_.fd_id=authallrea2_.fd_doc_id inner join sys_org_element sysorgelem3_ on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id where (citicscoma1_.doc_create_time between ? and ?) and (sysorgelem3_.fd_id in (?','no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3)','INJECT2024041703',null,true,true);
--fd_id in
call sf_inject_hint('from citics_co_main citicscoma0_ where citicscoma0_.fd_id in (select citicscoma1_.fd_id from citics_co_main citicscoma1_ inner join citics_co_main_areader authallrea2_ on citicscoma1_.fd_id=authallrea2_.fd_doc_id inner join sys_org_element sysorgelem3_ on authallrea2_.auth_all_reader_id=sysorgelem3_.fd_id where sysorgelem3_.fd_id in (?','no_semi_gen_cross HI_RIGHT_ORDER_FLAG(2) ADAPTIVE_NPLN_FLAG(3)','INJECT2024041704',null,true,true);
--删除hint
SF_DEINJECT_HINT('INJECT20240417');
SF_DEINJECT_HINT('INJECT2024041702');
SF_DEINJECT_HINT('INJECT2024041703');
SF_DEINJECT_HINT('INJECT2024041704');
--将HINT置为无效
SF_ALTER_HINT('INJECT20240417', 'STATUS', 'DISABLED');
SF_ALTER_HINT('INJECT2024041702', 'STATUS', 'DISABLED');
