故障处理：案例：what？Oracle CBO居然选择cost值高的执行计划
我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
本文转自朋友的真实案例分享。
案例：what？Oracle CBO居然选择cost值高的执行计划
本案例来自北区某客户的一条sql出现了性能问题，在分析过程中发现这是一个非常诡异的sql案例，所以记录下来。如果有遇到过的朋友，欢迎交流。
客户数据库版本11.2.0.4，有一条sql在主库上运行缓慢，但是在ogg同步的一个目标库上执行性能很好，sql优化就太简单了，把目标库上的执行计划outline固定到主库上就好了，优化本身也不是本文的重点，但是在固定的过程中发现了一个诡异的现象。。。
话不多说我们来看看sql文本：
sqltextOracle PL/SQL
select
row_num
from (select
rownum as row_num
from hs_his.his_terminalinfos a
where ((a.source_type = '0' and a.login_flag <> '0' and
((a.curr_date = 20220304 and a.curr_time <= 153000) or
(a.curr_date =
(select max(a.init_date)
from hs_user.exchangedate a
where a.init_date < 20220304) and
a.curr_time > 153000) or (a.curr_date >
(select max(a.init_date)
from hs_user.exchangedate a
where a.init_date < 20220304) and
a.curr_date < 20220304)))))
where mod(row_num, 5000) = 1
sql文本非常简单，错误的执行计划如下：
Inst: 1 Child: 0 Plan hash value: 595849078
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30780 | 390K| 215K (1)| 00:43:06 | | |
|* 1 | VIEW | | 30780 | 390K| 215K (1)| 00:43:06 | | |
| 2 | COUNT | | | | | | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| HIS_TERMINALINFOS | 30780 | 961K| 215K (1)| 00:43:06 | ROWID | ROWID |
|* 4 | INDEX SKIP SCAN | IDX_HIS_HIS_TERMINALINFOS_SOUR | 17M| | 9923 (1)| 00:02:00 | | |
| 5 | SORT AGGREGATE | | 1 | 13 | | | | |
| 6 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
| 8 | SORT AGGREGATE | | 1 | 13 | | | | |
| 9 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 10 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / from$_subquery$_001@SEL$1
2 - SEL$2
3 - SEL$2 / A@SEL$2
4 - SEL$2 / A@SEL$2
5 - SEL$4
7 - SEL$4 / A@SEL$4
8 - SEL$3
10 - SEL$3 / A@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
OPT_PARAM('optimizer_index_cost_adj' 10)
OPT_PARAM('optimizer_index_caching' 90)
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
INDEX_SS(@"SEL$2" "A"@"SEL$2" ("HIS_TERMINALINFOS"."CURR_DATE" "HIS_TERMINALINFOS"."SOURCE_TYPE"
"HIS_TERMINALINFOS"."LOGIN_FLAG"))
PUSH_SUBQ(@"SEL$3")
PUSH_SUBQ(@"SEL$4")
INDEX(@"SEL$4" "A"@"SEL$4" ("EXCHANGEDATE"."INIT_DATE" "EXCHANGEDATE"."FINANCE_TYPE" "EXCHANGEDATE"."EXCHANGE_TYPE"))
INDEX(@"SEL$3" "A"@"SEL$3" ("EXCHANGEDATE"."INIT_DATE" "EXCHANGEDATE"."FINANCE_TYPE" "EXCHANGEDATE"."EXCHANGE_TYPE"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MOD("ROW_NUM",5000)=1)
3 - filter((("A"."CURR_DATE"=20220304 AND "A"."CURR_TIME"<=153000) OR ("A"."CURR_DATE"<20220304 AND "A"."CURR_DATE">) OR
("A"."CURR_TIME">153000 AND "A"."CURR_DATE"=)))
4 - access("A"."SOURCE_TYPE"='0')
filter(("A"."SOURCE_TYPE"='0' AND "A"."LOGIN_FLAG"<>'0'))
7 - access("A"."INIT_DATE"<20220304)
10 - access("A"."INIT_DATE"<20220304)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ROW_NUM"[NUMBER,22]
2 - ROWNUM[4]
4 - "A".ROWID[ROWID,10], "A"."CURR_DATE"[NUMBER,22]
5 - (#keys=0) MAX("A"."INIT_DATE")[22]
6 - "A"."INIT_DATE"[NUMBER,22]
7 - "A"."INIT_DATE"[NUMBER,22]
8 - (#keys=0) MAX("A"."INIT_DATE")[22]
9 - "A"."INIT_DATE"[NUMBER,22]
10 - "A"."INIT_DATE"[NUMBER,22]
Note
-----
- dynamic sampling used for this statement (level=2)
主库固定执行计划outline正确的执行计划如下：
Plan hash value: 2344781673
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31295 | 397K| 2551 (0)| 00:00:31 | | |
|* 1 | VIEW | | 31295 | 397K| 2551 (0)| 00:00:31 | | |
| 2 | COUNT | | | | | | | |
| 3 | CONCATENATION | | | | | | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| HIS_TERMINALINFOS | 741 | 23712 | 616 (1)| 00:00:08 | ROWID | ROWID |
|* 5 | INDEX RANGE SCAN | IDX_HIS_HIS_TERMINALINFOS_CURR | 25125 | | 14 (0)| 00:00:01 | | |
| 6 | SORT AGGREGATE | | 1 | 13 | | | | |
| 7 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| HIS_TERMINALINFOS | 29301 | 915K| 1320 (0)| 00:00:16 | ROWID | ROWID |
|* 10 | INDEX RANGE SCAN | IDX_HIS_HIS_TERMINALINFOS_CURR | 52744 | | 57 (0)| 00:00:01 | | |
| 11 | SORT AGGREGATE | | 1 | 13 | | | | |
| 12 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
| 14 | SORT AGGREGATE | | 1 | 13 | | | | |
| 15 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 16 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| HIS_TERMINALINFOS | 1253 | 40096 | 616 (1)| 00:00:08 | ROWID | ROWID |
|* 18 | INDEX RANGE SCAN | IDX_HIS_HIS_TERMINALINFOS_CURR | 25125 | | 14 (0)| 00:00:01 | | |
| 19 | SORT AGGREGATE | | 1 | 13 | | | | |
| 20 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
| 22 | SORT AGGREGATE | | 1 | 13 | | | | |
| 23 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 24 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MOD("ROW_NUM",5000)=1)
4 - filter("A"."CURR_TIME">153000)
5 - access("A"."CURR_DATE"= (SELECT /*+ PUSH_SUBQ INDEX ("A" "IDX_EXCHANGEDATE") */ MAX("A"."INIT_DATE") FROM
"HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304) AND "A"."SOURCE_TYPE"='0')
filter("A"."LOGIN_FLAG"<>'0')
8 - access("A"."INIT_DATE"<20220304)
9 - filter(LNNVL("A"."CURR_TIME">153000) OR LNNVL("A"."CURR_DATE"= (SELECT /*+ PUSH_SUBQ INDEX ("A" "IDX_EXCHANGEDATE") */
MAX("A"."INIT_DATE") FROM "HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304)))
10 - access("A"."CURR_DATE"> (SELECT /*+ PUSH_SUBQ INDEX ("A" "IDX_EXCHANGEDATE") */ MAX("A"."INIT_DATE") FROM
"HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304) AND "A"."SOURCE_TYPE"='0' AND "A"."CURR_DATE"<20220304)
filter("A"."SOURCE_TYPE"='0' AND "A"."LOGIN_FLAG"<>'0')
13 - access("A"."INIT_DATE"<20220304)
16 - access("A"."INIT_DATE"<20220304)
17 - filter("A"."CURR_TIME"<=153000 AND (LNNVL("A"."CURR_DATE"<20220304) OR LNNVL("A"."CURR_DATE"> (SELECT /*+ PUSH_SUBQ
INDEX ("A" "IDX_EXCHANGEDATE") */ MAX("A"."INIT_DATE") FROM "HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304))) AND
(LNNVL("A"."CURR_TIME">153000) OR LNNVL("A"."CURR_DATE"= (SELECT /*+ PUSH_SUBQ INDEX ("A" "IDX_EXCHANGEDATE") */
MAX("A"."INIT_DATE") FROM "HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304))))
18 - access("A"."CURR_DATE"=20220304 AND "A"."SOURCE_TYPE"='0')
filter("A"."LOGIN_FLAG"<>'0')
21 - access("A"."INIT_DATE"<20220304)
24 - access("A"."INIT_DATE"<20220304)
错误的执行计划选择了一个错误的索引IDX_HIS_HIS_TERMINALINFOS_SOUR的index skip scan，cost高达215k；而正确的执行计划通过or展开选择了IDX_HIS_HIS_TERMINALINFOS_CURR的index range scan，cost仅为2551。那么问题来了，为何CBO会选择一个cost高的执行计划呢？最常见的原因就是sql本身已经固定了执行计划，但是从sqlhc脚本采集的信息中看到，实际并没有看到有profile/baseline/sqlpatch绑定过该sql。
SQL Plan Baselines (DBA_SQL_PLAN_BASELINES)
Available on 11g or higher. If this section is empty that means there are no plans in plan history for this SQL.
SQL Profiles (DBA_SQL_PROFILES)
Available on 10g or higher. If this section is empty that means there are no profiles for this SQL.
SQL Patches (DBA_SQL_PATCHES)
Available on 11g or higher. If this section is empty that means there are no patches for this SQL.
那么具体到底是怎么回事呢？尝试从10053中寻找答案。
首先介绍一下or-Expansion，or-Expansion是一种非启发式查询转换，也就是说cbo评估转换后的成本要低于转换前的成本，才会执行该查询转换。这里特别要注意的是，or-Expansion会对每个OR-branching进行评估，如果有其中一个OR-branching的转换成本高于转换前的成本时，则会终止后面的OR-branching评估，CBO就认为该sql不能进行or-Expansion。
该sql总共有2个or，那么应该会有3个OR-branching，但实际10053中只出现了2个，说明在做第二个OR-branching成本评估时，发现该分支or展开成本过高。
第一个OR-branching评估如下，cost计算以及索引选择都是正确的。
Trying or-Expansion on query block SEL$2 (#0)
******** OR-branching ********
...
...
Access Path: index (RangeScan)
Index: IDX_HIS_HIS_TERMINALINFOS_CURR
resc_io: 6153.00 resc_cpu: 64874383
ix_sel: 0.001072 ix_sel_with_filters: 0.000715
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 250.00
***** End Logdef Adjustment ******
Cost: 615.50 Resp: 615.50 Degree: 1
Access Path: index (skip-scan)
SS scan sel: 0.500000 SS filter sel: 0.500000 ANDV (#skips): 560.000000
SS io: 9912.000000 vs. table scan io: 1481700.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: IDX_HIS_HIS_TERMINALINFOS_SOUR
resc_io: 2153610.00 resc_cpu: 28348566488
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 215437.85 Resp: 215437.85 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: IDX_HIS_HIS_TERMINALINFOS_CURR
Cost: 615.50 Degree: 1 Resp: 615.50 Card: 1256.27 Bytes: 0
第二个OR-branching评估就出现了诡异的现象
******** OR-branching ********
or-expansion-subheap (create addr=0x7fb2c0854c30)
...
,,.
Access Path: index (RangeScan)
Index: IDX_HIS_HIS_TERMINALINFOS_CURR
resc_io: 13199.00 resc_cpu: 151353467
ix_sel: 0.004500 ix_sel_with_filters: 0.001500
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 250.00
***** End Logdef Adjustment ******
Cost: 1320.42 Resp: 1320.42 Degree: 1
Access Path: index (skip-scan)
SS scan sel: 0.500000 SS filter sel: 0.500000 ANDV (#skips): 560.000000
SS io: 9912.000000 vs. table scan io: 1481700.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: IDX_HIS_HIS_TERMINALINFOS_SOUR
resc_io: 2153610.00 resc_cpu: 32478849773
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 215449.05 Resp: 215449.05 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: IDX_HIS_HIS_TERMINALINFOS_SOUR
Cost: 215449.05 Degree: 1 Resp: 215449.05 Card: 29302.45 Bytes: 0
诡异点：
CBO对cost的计算完全正确，但是选择发生了错误。并没有选择cost为1320.42的IDX_HIS_HIS_TERMINALINFOS_CURR(range scan)，而去选择了一个cost为215449.05的IDX_HIS_HIS_TERMINALINFOS_SOUR(skip scan)。
Best:: AccessPath: IndexRange，但实际选择的是SkipScan。
并且认为or展开成本过高终止了评估，并且认为该sql不进行or展开
or-expansion-subheap (delete addr=0x7fb2c0854c30, in-use=19280, alloc=32840)
or-expansion is worse cost:216065.546500
如果观察仔细的话，可以发现实际计算的cost都打了个1折，如resc_io: 2153610.00(IO_COST) resc_cpu: 32478849773(CPU_COST)，而实际cost仅为Cost: 215449.05，这明显是optimizer_index_cost_adj参数的作用效果，从执行计划outline中可以看到OPT_PARAM(‘optimizer_index_cost_adj’ 10)，确实是会把索引访问成本乘以0.1得到实际成本。
当发现该环境设置了optimizer_index_cost_adj为10之后，我认为这并不是CBO选择cost高的执行计划的原因，因为无论是index range scan还是index skip scan，都会受到optimizer_index_cost_adj的作用去乘以0.1的系数，并且cost计算是完全没问题的，只是选择了一个cost高的访问路径。
但是打脸来的就是那么快，经过同事的测试验证，对于该案例的sql来说optimizer_index_cost_adj设置高于40时，CBO选择会完全正确。这就很不科学了。。。
SQL> alter session set optimizer_index_cost_adj=100;
Session altered.
SQL> explain plan for
2 select
3 row_num
4 from (select
5 rownum as row_num
6 from hs_his.his_terminalinfos a
7 where ((a.source_type = '0' and a.login_flag <> '0' and
8 ((a.curr_date = 20220304 and a.curr_time <= 153000) or
9 (a.curr_date =
10 (select max(a.init_date)
11 from hs_user.exchangedate a
12 where a.init_date < 20220304) and
13 a.curr_time > 153000) or (a.curr_date >
14 (select max(a.init_date)
15 from hs_user.exchangedate a
16 where a.init_date < 20220304) and
17 a.curr_date < 20220304)))))
18 where mod(row_num, 5000) = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2344781673
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31295 | 397K| 25514 (1)| 00:05:07 | | |
|* 1 | VIEW | | 31295 | 397K| 25514 (1)| 00:05:07 | | |
| 2 | COUNT | | | | | | | |
| 3 | CONCATENATION | | | | | | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| HIS_TERMINALINFOS | 741 | 23712 | 6155 (1)| 00:01:14 | ROWID | ROWID |
|* 5 | INDEX RANGE SCAN | IDX_HIS_HIS_TERMINALINFOS_CURR | 25125 | | 135 (0)| 00:00:02 | | |
| 6 | SORT AGGREGATE | | 1 | 13 | | | | |
| 7 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| HIS_TERMINALINFOS | 29301 | 915K| 13204 (1)| 00:02:39 | ROWID | ROWID |
|* 10 | INDEX RANGE SCAN | IDX_HIS_HIS_TERMINALINFOS_CURR | 52744 | | 568 (1)| 00:00:07 | | |
| 11 | SORT AGGREGATE | | 1 | 13 | | | | |
| 12 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
| 14 | SORT AGGREGATE | | 1 | 13 | | | | |
| 15 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 16 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| HIS_TERMINALINFOS | 1253 | 40096 | 6155 (1)| 00:01:14 | ROWID | ROWID |
|* 18 | INDEX RANGE SCAN | IDX_HIS_HIS_TERMINALINFOS_CURR | 25125 | | 135 (0)| 00:00:02 | | |
| 19 | SORT AGGREGATE | | 1 | 13 | | | | |
| 20 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
| 22 | SORT AGGREGATE | | 1 | 13 | | | | |
| 23 | FIRST ROW | | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 24 | INDEX RANGE SCAN (MIN/MAX) | IDX_EXCHANGEDATE | 1 | 13 | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MOD("ROW_NUM",5000)=1)
4 - filter("A"."CURR_TIME">153000)
5 - access("A"."CURR_DATE"= (SELECT MAX("A"."INIT_DATE") FROM "HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304)
AND "A"."SOURCE_TYPE"='0')
filter("A"."LOGIN_FLAG"<>'0')
8 - access("A"."INIT_DATE"<20220304)
9 - filter(LNNVL("A"."CURR_TIME">153000) OR LNNVL("A"."CURR_DATE"= (SELECT MAX("A"."INIT_DATE") FROM
"HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304)))
10 - access("A"."CURR_DATE"> (SELECT MAX("A"."INIT_DATE") FROM "HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304)
AND "A"."SOURCE_TYPE"='0' AND "A"."CURR_DATE"<20220304)
filter("A"."SOURCE_TYPE"='0' AND "A"."LOGIN_FLAG"<>'0')
13 - access("A"."INIT_DATE"<20220304)
16 - access("A"."INIT_DATE"<20220304)
17 - filter("A"."CURR_TIME"<=153000 AND (LNNVL("A"."CURR_DATE"<20220304) OR LNNVL("A"."CURR_DATE"> (SELECT
MAX("A"."INIT_DATE") FROM "HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304))) AND (LNNVL("A"."CURR_TIME">153000) OR
LNNVL("A"."CURR_DATE"= (SELECT MAX("A"."INIT_DATE") FROM "HS_USER"."EXCHANGEDATE" "A" WHERE "A"."INIT_DATE"<20220304))))
18 - access("A"."CURR_DATE"=20220304 AND "A"."SOURCE_TYPE"='0')
filter("A"."LOGIN_FLAG"<>'0')
21 - access("A"."INIT_DATE"<20220304)
24 - access("A"."INIT_DATE"<20220304)
通过10053查看之前评估错误的OR-branching，这次它又行了。。。百思不得其解呀。。。
Access Path: index (RangeScan)
Index: IDX_HIS_HIS_TERMINALINFOS_CURR
resc_io: 13199.00 resc_cpu: 151353467
ix_sel: 0.004500 ix_sel_with_filters: 0.001500
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 250.00
***** End Logdef Adjustment ******
Cost: 13204.18 Resp: 13204.18 Degree: 1
Access Path: index (skip-scan)
SS scan sel: 0.500000 SS filter sel: 0.500000 ANDV (#skips): 560.000000
SS io: 99120.000000 vs. table scan io: 1481700.000000
Skip Scan chosen
Access Path: index (SkipScan)
Index: IDX_HIS_HIS_TERMINALINFOS_SOUR
resc_io: 2153610.00 resc_cpu: 32478849773
ix_sel: 0.500000 ix_sel_with_filters: 0.500000
Cost: 2154490.45 Resp: 2154490.45 Degree: 1
****** trying bitmap/domain indexes ******
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: IndexRange
Index: IDX_HIS_HIS_TERMINALINFOS_CURR
Cost: 13204.18 Degree: 1 Resp: 13204.18 Card: 29302.45 Bytes: 0
...
...
or-expansion-subheap (delete addr=0x7f851bd9fc30, in-use=19312, alloc=32840)
or-expansion is better cost:25514.203435
尝试过调整optimizer_features_enable到8i也是正常的，所以想通过遍历fix_control的方式，看看能不能找到是为了修复哪个bug导致的，很不幸也没有找到。遍历fix_control的脚本使用的anbob大神的脚本，脚本分享如下：
DECLARE
l_unique_id VARCHAR2(200);
l_test_id NUMBER := 0;
l_test_id_rp_i NUMBER := 0;
l_spoolfile_name_p VARCHAR2(100);
l_spoolfile_name_vs VARCHAR2(100);
l_spoolfile_name_rp_i_p VARCHAR2(100);
l_spoolfile_name_rp_i_vs VARCHAR2(100);
l_alter_session VARCHAR2(4000);
l_alter_session_bck VARCHAR2(4000);
l_skip_string_script VARCHAR2(4000);
l_skip_string_driver VARCHAR2(4000);
l_child_list VARCHAR2(4000);
PROCEDURE print (p_alter_session IN VARCHAR2)
IS
BEGIN
l_test_id := l_test_id + 1;
l_spoolfile_name_p := LPAD(l_test_id, 5, '0');
dbms_output.put_line('PRO '||l_test_id||') "'||replace(p_alter_session,'ALTER SESSION SET',''));
dbms_output.put_line(p_alter_session);
dbms_output.put_line('@script');
END;
begin
FOR i IN (
WITH cbo_param AS (
SELECT /*+ materialize */ pname_qksceserow name
FROM x$qksceses
WHERE sid_qksceserow = SYS_CONTEXT('USERENV', 'SID')
)
SELECT x.indx+1 num,
x.ksppinm name,
x.ksppity type,
y.ksppstvl value,
y.ksppstdvl display_value,
y.ksppstdf isdefault,
x.ksppdesc description,
y.ksppstcmnt update_comment,
x.ksppihash hash
FROM x$ksppi x,
x$ksppcv y,
cbo_param
WHERE x.indx = y.indx
AND BITAND(x.ksppiflg, 268435456) = 0
AND TRANSLATE(x.ksppinm, '_', '#') NOT LIKE '##%'
AND x.ksppinm = cbo_param.name
AND x.inst_id = USERENV('Instance')
AND DECODE(BITAND(x.ksppiflg/256, 1), 1, 'TRUE', 'FALSE') = 'TRUE'
AND x.ksppity IN (1, 2, 3)
--and lower(x.ksppinm) || ' ' || lower(x.ksppdesc) like lower('%parallel%')
ORDER BY x.ksppinm)
LOOP
IF SUBSTR(i.name , 1, 1) = CHR(95) -- "_"
THEN
l_alter_session := 'ALTER SESSION SET "'||i.name ||'" = ';
ELSE
l_alter_session := 'ALTER SESSION SET '||i.name ||' = ';
END IF;
IF i.type = 1 THEN -- Boolean
IF LOWER(i.value) = 'true' THEN
l_alter_session := l_alter_session||' FALSE;';
ELSIF LOWER(i.value) = 'false' THEN
l_alter_session := l_alter_session||' TRUE;';
ELSE
dbms_output.put_line('--');
dbms_output.put_line('-- skip test on '||i.name ||'. baseline value: '||i.value);
END IF;
print(l_alter_session);
ELSIF i.type = 2 THEN -- String
-- this is used as base ALTER SESSION for the LOV
l_alter_session_bck := l_alter_session;
FOR j IN (SELECT value_kspvld_values value
FROM x$kspvld_values
WHERE LOWER(name_kspvld_values) = i.name
AND LOWER(value_kspvld_values) <> i.value
ORDER BY value_kspvld_values)
LOOP
l_alter_session := l_alter_session_bck||' '''||j.value||''';';
print(l_alter_session);
END LOOP;
end if;
end loop;
FOR i IN (SELECT * FROM v$session_fix_control WHERE session_id = SYS_CONTEXT('USERENV', 'SID') ORDER BY bugno) LOOP
IF i.value = 0 THEN --number
l_alter_session := 'ALTER SESSION SET "_fix_control" = '''||i.bugno||':1'';';
ELSIF i.value = 1 THEN
l_alter_session := 'ALTER SESSION SET "_fix_control" = '''||i.bugno||':0'';';
ELSE
l_alter_session := 'ALTER SESSION SET "_fix_control" = '''||i.bugno||':0'';';
END IF;
print(l_alter_session);
END LOOP;
end;
/
查看mos也没有找到匹配的bug，这种非常少见的问题和场景也没必要太过纠结，但是还是有必要记录一下。因为这个案例给了我们一个启发，optimizer_index_cost_adj，包括所有的优化器参数，谨慎调整，严格测试，理论是理论，但是总有意外，因为不知道又会触发什么奇怪的问题。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
提供ORACLE技术支持(系统优化，故障处理，安装升级，数据恢复等） TEL:18081072613，微信、QQ同手机号。
浙公网安备 33010602011771号