案例：what’s fuck？CBO居然选择cost值高的

本案例来自北区某客户的一条sql出现了性能问题，在分析过程中发现这是一个非常诡异的sql案例，所以记录下来。如果有遇到过的朋友，欢迎交流。

客户数据库版本11.2.0.4，有一条sql在主库上运行缓慢，但是在ogg同步的一个目标库上执行性能很好，sql优化就太简单了，把目标库上的执行计划outline固定到主库上就好了，优化本身也不是本文的重点，但是在固定的过程中发现了一个诡异的现象。。。

话不多说我们来看看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

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)

Inst: 1 Child: 0 Plan hash value: 595849078

---------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------

| 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)

Plan hash value: 2344781673

----------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------

| 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.

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

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

******** 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

1 2	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)

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

----------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------

| 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

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;
/

100

101

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)

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，包括所有的优化器参数，谨慎调整，严格测试，理论是理论，但是总有意外，因为不知道又会触发什么奇怪的问题。

如果有兴趣的朋友不妨提个sr看看原厂的研发团队是否能解释这个问题。

案例：what’s fuck？CBO居然选择cost值高的

发表回复取消回复

近期文章

近期评论

案例：what’s fuck？CBO居然选择cost值高的

发表回复 取消回复

近期文章

近期评论

发表回复取消回复