DB：10205

OS: AIX 5

症状：

SELECT

JUSTIN_PROJECT.PROJECT_NM,

Sum(LARGE_HOUSE.ACT_EFF_$)

FROM

JUSTIN_PROJECT,

LARGE_HOUSE,

(

SELECT DISTINCT EXTRACT_DT ,is_completed,IS_LATEST_EXTRACT_DT FROM ADMINISTRIOR

) ADMINISTRIOR,

HOUSE_TIME

WHERE

( ADMINISTRIOR.EXTRACT_DT=HOUSE_TIME.CALENDER_DT )

AND ( LARGE_HOUSE.PROJECT_SK=JUSTIN_PROJECT.PROJECT_SK )

AND ( HOUSE_TIME.CALENDER_DT=LARGE_HOUSE.AS_OF_DT )

AND

(

JUSTIN_PROJECT.PROJECT_NM IN ( 'ABC' )

AND

( ADMINISTRIOR.IS_LATEST_EXTRACT_DT=1 )

)

GROUP BY

JUSTIN_PROJECT.PROJECT_NM

客户抱怨以上这条sql在pre-prod运行很快，但是在prod上则巨慢；

登陆数据库，首先查看执行计划，

--prod

--------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+

--------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+

| 0 | SELECT STATEMENT | | | | 5988 | | | | | | |

| 1 | HASH GROUP BY | | 1 | 57 | 5988 | 00:02:48 | | | | | |

| 2 | PX COORDINATOR | | | | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10001 | 14K | 798K | 5988 | 00:02:48 |:Q1001| P->S |QC (RANDOM)| | |

| 4 | HASH JOIN | | 14K | 798K | 5988 | 00:02:48 |:Q1001| PCWP | | | |

| 5 | BUFFER SORT | | | | | |:Q1001| PCWC | | | |

| 6 | PX RECEIVE | | 12 | 120 | 6 | 00:00:01 |:Q1001| PCWP | | | |

| 7 | PX SEND BROADCAST | :TQ10000 | 12 | 120 | 6 | 00:00:01 | | S->P |BROADCAST | | |

| 8 | VIEW | | 12 | 120 | 6 | 00:00:01 | | | | | |

| 9 | HASH UNIQUE | | 12 | 168 | 6 | 00:00:01 | | | | | |

| 10 | TABLE ACCESS FULL | ADMINISTRIOR | 25 | 350 | 5 | 00:00:01 | | | | | |

| 11 | NESTED LOOPS | | 356K | 16M | 5982 | 00:02:48 |:Q1001| PCWP | | | |

| 12 | NESTED LOOPS | | 356K | 14M | 5980 | 00:02:48 |:Q1001| PCWP | | | |

| 13 | PX BLOCK ITERATOR | | 8 | 184 | 21 | 00:00:01 |:Q1001| PCWC | | 1 | 1 |

| 14 | TABLE ACCESS FULL | JUSTIN_PROJECT | 8 | 184 | 21 | 00:00:01 |:Q1001| PCWP | | 1 | 1 |

| 15 | PARTITION RANGE ALL | | 42K | 676K | 32K | 00:10:58 |:Q1001| PCWP | | 1 | 49 |

| 16 | TABLE ACCESS BY LOCAL INDEX ROWID | LARGE_HOUSE | 42K | 676K | 32K | 00:10:58 |:Q1001| PCWP | | 1 | 49 |

| 17 | INDEX RANGE SCAN | FACT_HOURSCURVES_IDX10| 42K | | 99 | 00:00:02 |:Q1001| PCWP | | 1 | 49 |

| 18 | INDEX UNIQUE SCAN | HOUSE_TIME_CAL_DT | 1 | 8 | 0 | |:Q1001| PCWP | | | |

--------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+

Predicate Information:

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

4 - access("ADMINISTRIOR"."EXTRACT_DT"="HOUSE_TIME"."CALENDER_DT")

10 - filter("IS_LATEST_EXTRACT_DT"=1)

14 - access(:Z>=:Z AND :Z<=:Z)

14 - filter("JUSTIN_PROJECT"."PROJECT_NM"='AKTC2')

17 - access("LARGE_HOUSE"."PROJECT_SK"="JUSTIN_PROJECT"."PROJECT_SK")

18 - access("HOUSE_TIME"."CALENDER_DT"="LARGE_HOUSE"."AS_OF_DT")

--pre-prod

----------------------------------------------------------------------------+-----------------------------------+---------------+

----------------------------------------------------------------------------+-----------------------------------+---------------+

| 0 | SELECT STATEMENT | | | | 1966 | | | |

| 1 | SORT GROUP BY NOSORT | | 1 | 33 | 1966 | 00:00:36 | | |

| 2 | VIEW | | 1397 | 45K | 1966 | 00:00:36 | | |

| 3 | HASH UNIQUE | | 1397 | 132K | 1966 | 00:00:36 | | |

| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | LARGE_HOUSE | 166 | 4648 | 1965 | 00:00:36 | | |

| 5 | NESTED LOOPS | | 1397 | 132K | 1965 | 00:00:36 | | |

| 6 | NESTED LOOPS | | 8 | 552 | 16 | 00:00:01 | | |

| 7 | MERGE JOIN CARTESIAN | | 8 | 392 | 16 | 00:00:01 | | |

| 8 | TABLE ACCESS FULL | ADMINISTRIOR | 1 | 14 | 5 | 00:00:01 | | |

| 9 | BUFFER SORT | | 8 | 280 | 11 | 00:00:01 | | |

| 10 | PARTITION RANGE SINGLE | | 8 | 280 | 10 | 00:00:01 | 1 | 1 |

| 11 | TABLE ACCESS BY LOCAL INDEX ROWID | JUSTIN_PROJECT | 8 | 280 | 10 | 00:00:01 | 1 | 1 |

| 12 | INDEX RANGE SCAN | DIM_PROJECT_IDX01 | 8 | | 1 | 00:00:01 | 1 | 1 |

| 13 | INDEX UNIQUE SCAN | HOUSE_TIME_CAL_DT | 1 | 20 | 0 | | | |

| 14 | PARTITION RANGE ITERATOR | | | | | | KEY | KEY |

| 15 | BITMAP CONVERSION TO ROWIDS | | | | | | | |

| 16 | BITMAP AND | | | | | | | |

| 17 | BITMAP CONVERSION FROM ROWIDS | | | | | | | |

| 18 | INDEX RANGE SCAN | FACT_HOURSCURVES_IDX10| 42K | | 3 | 00:00:01 | KEY | KEY |

| 19 | BITMAP CONVERSION FROM ROWIDS | | | | | | | |

| 20 | INDEX RANGE SCAN | FACT_HOURSCURVES_IDX01| 42K | | 3239 | 00:00:59 | KEY | KEY |

----------------------------------------------------------------------------+-----------------------------------+---------------+

Predicate Information:

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

8 - filter("IS_LATEST_EXTRACT_DT"=1)

12 - access("JUSTIN_PROJECT"."PROJECT_NM"='AKTC2')

13 - access("EXTRACT_DT"="HOUSE_TIME"."CALENDER_DT")

18 - access("LARGE_HOUSE"."PROJECT_SK"="JUSTIN_PROJECT"."PROJECT_SK")

20 - access("HOUSE_TIME"."CALENDER_DT"="LARGE_HOUSE"."AS_OF_DT")

表LARGE_HOUSE比较大，总共49个分区，其字段project_sk和as_of_dt均建有非前缀本地分区索引，理想情况时两个索引进行bitmap conversion转换以避免扫描所有分区，而pre-prod也确实做到了这一点，但是prod却选择了全分区扫描；

两个数据库都是_b_tree_bitmap_plans=true且star_transformation_enabled=false，表结构和其他参数也均一致；

尝试添加各种hint都不济事，调用sql tuning advisor, oracle也推荐使用bitmap conversion的方式访问该表，但是问题究竟出在哪里？

两个环境各做了10053跟踪，发现各自最优执行计划的连接顺序如下

Prod

Join order[3]: JUSTIN_PROJECT[JUSTIN_PROJECT]#0 LARGE_HOUSE[LARGE_HOUSE]#3 HOUSE_TIME[HOUSE_TIME]#2 from$_subquery$_003[ADMINISTRIOR]#1

Pre-prod

Join order[1]: ADMINISTRIOR[ADMINISTRIOR]#0 JUSTIN_PROJECT[JUSTIN_PROJECT]#1 HOUSE_TIME[HOUSE_TIME]#2 LARGE_HOUSE[LARGE_HOUSE]#3

各个表的最佳访问路径和card如下

prod

Table: ADMINISTRIOR Alias: ADMINISTRIOR

Best:: AccessPath: TableScan

Cost: 5.00 Degree: 1 Resp: 5.00 Card: 24.50 Bytes: 0

Table: HOUSE_TIME Alias: HOUSE_TIME

Best:: AccessPath: TableScan

Cost: 5.57 Degree: 16 Resp: 5.57 Card: 37136.00 Bytes: 0

Table: JUSTIN_PROJECT Alias: JUSTIN_PROJECT

Best:: AccessPath: IndexRange Index: DIM_PROJECT_IDX01

Cost: 10.00 Degree: 1 Resp: 10.00 Card: 8.42 Bytes: 0

Table: LARGE_HOUSE Alias: LARGE_HOUSE

Best:: AccessPath: TableScan

Cost: 127240.47 Degree: 16 Resp: 127240.47 Card: 232204798.00 Bytes: 0

Pre-prod

Table: ADMINISTRIOR Alias: ADMINISTRIOR

Best:: AccessPath: TableScan

Cost: 5.00 Degree: 1 Resp: 5.00 Card: 1.00 Bytes: 0

Table: HOUSE_TIME Alias: HOUSE_TIME

Best:: AccessPath: TableScan

Cost: 5.57 Degree: 16 Resp: 5.57 Card: 37136.00 Bytes: 0

Table: JUSTIN_PROJECT Alias: JUSTIN_PROJECT

Best:: AccessPath: IndexRange Index: DIM_PROJECT_IDX01

Cost: 10.00 Degree: 1 Resp: 10.00 Card: 8.42 Bytes: 0

Table: LARGE_HOUSE Alias: LARGE_HOUSE

Best:: AccessPath: TableScan

Cost: 127240.47 Degree: 16 Resp: 127240.47 Card: 232204798.00 Bytes: 0

通过以上对比可以看出一点差异，即表ADMINISTRIOR的Card，prod为24.5而pre-prod为1

表ADMINISTRIOR只有50行数据且统计信息准确无误，但是IS_LATEST_EXTRACT_DT列数据分布有点不规律

SQL> select IS_LATEST_EXTRACT_DT,count(*) from ADMINISTRIOR group by IS_LATEST_EXTRACT_DT ;

IS_LATEST_EXTRACT_DT COUNT(*)

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

1 1

0 48

Pre-prod该列收集有直方图而prod却没有，至此事情有了些许眉目：

在解析的时候ADMINISTRIOR.IS_LATEST_EXTRACT_DT=1被push到了前面的子查询中，

FPD: Considering simple filter push (pre rewrite) in SEL$2 (#0)

FPD: Current where clause predicates in SEL$2 (#0) :

"ADMINISTRIOR"."IS_LATEST_EXTRACT_DT"=1

Registered qb: SEL$1 0x10428b80 (COPY SEL$1)

pre-prod因为有了直方图且该列变量值为1，CBO解析时可以准确的预见该表只返回一行数据，而直方图的缺失让prod误以为该表要返回一半的数据，故在各表作join order的时候不能选择最优的方案，进而不能选择最高效的执行计划；

在prod上收集该列直方图

exec dbms_stats.gather_table_stats('OPAL_DW_ADMIN','ADMINISTRIOR',estimate_percent => 100, method_opt=>'FOR COLUMNS IS_LATEST_EXTRACT_DT SIZE 2');

硬解析后重新运行，此时选择了和pre-prod一样的执行计划；

将变量1替换成0，则重新选择了第一个比较差的执行计划；

小结：直方图是反映数据分布情况的，一般来说，索引列直方图信息的丢失导致sql错误的选择全表扫描或索引扫描的案例比较常见；

但此表的相关列既没有索引(铁定要走全表扫描)，且表只有49行，其直方图的缺失却能让一个sql选择逻辑读高出10几倍的执行计划，真是”差之毫厘，谬以千里”。

ITPub博客

非索引列直方图的丢失导致sql性能急剧下降

最新文章