本案例来自北区某客户的一条sql出现了性能问题,在分析过程中发现这是一个非常诡异的sql案例,所以记录下来。如果有遇到过的朋友,欢迎交流。
客户数据库版本11.2.0.4,有一条sql在主库上运行缓慢,但是在ogg同步的一个目标库上执行性能很好,sql优化就太简单了,把目标库上的执行计划outline固定到主库上就好了,优化本身也不是本文的重点,但是在固定的过程中发现了一个诡异的现象。。。
话不多说我们来看看sql文本:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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文本非常简单,错误的执行计划如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
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正确的执行计划如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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。
1 2 3 4 5 6 |
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计算以及索引选择都是正确的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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评估就出现了诡异的现象
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
******** 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展开
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选择会完全正确。这就很不科学了。。。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
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,这次它又行了。。。百思不得其解呀。。。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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大神的脚本,脚本分享如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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) 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,包括所有的优化器参数,谨慎调整,严格测试,理论是理论,但是总有意外,因为不知道又会触发什么奇怪的问题。
如果有兴趣的朋友不妨提个sr看看原厂的研发团队是否能解释这个问题。