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幾倍的執行計劃，真是”差之毫釐，謬以千里”。

非索引列直方圖的丟失導致sql效能急劇下降

相關文章