重新收集统计信息之后 执行计划不改变

ETL coder发信来说 某个ETL job 跑了450分钟还没完，叫我check一下

SQL> select * from v$version where rownum=1; BANNER ------------------------------------------------------------------------------ Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

经过5分钟的检查，发现某个表recreate之后没有收集统计信息，其实我一直强调统计信息，但是那帮coder就是不听话，奶奶的 下面就是那个错误的执行计划

SQL> Select * from table(dbms_xplan.display_cursor('95n1023ybm3u8',0)); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 95n1023ybm3u8, child number 0 ------------------------------------- MERGE INTO OPT_ACTVY_FCT A USING (SELECT C.REVSD_VAR_ESTMT_COST_AMT, C.ACTVY_ID FROM OPT_ACTVY_REVSD_PLC C, OPT_ACTVY_DIM D, OPT_PRMTN_DIM E WHERE C.ACTVY_ID = D.ACTVY_ID AND D.PRMTN_ID = E.PRMTN_ID AND E.PRMTN_STTUS_CODE <> 'Completed') PLC ON (PLC.ACTVY_ID = A.PRMTN_FACT_ID) WHEN MATCHED THEN UPDATE SET A.REVSD_VAR_ESTMT_COST_AMT = PLC.REVSD_VAR_ESTMT_COST_AMT Plan hash value: 2187111512 -------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | | | 4585 (100)| | | | | 1 | MERGE | OPT_ACTVY_FCT | | | | | | | | 2 | VIEW | | | | | | | | | 3 | NESTED LOOPS | | | | | | | | | 4 | NESTED LOOPS | | 1 | 471 | 4585 (4)| 00:00:15 | | | | 5 | NESTED LOOPS | | 1 | 451 | 4583 (4)| 00:00:15 | | | | 6 | MERGE JOIN CARTESIAN | | 1 | 436 | 4583 (4)| 00:00:15 | | | | 7 | PARTITION RANGE ALL | | 1 | 416 | 2 (0)| 00:00:01 | 1 | 7 | | 8 | PARTITION LIST ALL | | 1 | 416 | 2 (0)| 00:00:01 | 1 | 16 | | 9 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 1 | 416 | 2 (0)| 00:00:01 | 1 | 112 | | 10 | BUFFER SORT | | 468K| 9143K| 4581 (4)| 00:00:15 | | | | 11 | PARTITION LIST ALL | | 468K| 9143K| 4581 (4)| 00:00:15 | 1 | 16 | | 12 | TABLE ACCESS FULL | OPT_ACTVY_DIM | 468K| 9143K| 4581 (4)| 00:00:15 | 1 | 16 | | 13 | TABLE ACCESS BY INDEX ROWID | OPT_ACTVY_REVSD_PLC | 1 | 15 | 0 (0)| | | | |* 14 | INDEX UNIQUE SCAN | OPT_ACTVY_REVSD_PLC_PK | 1 | | 0 (0)| | | | |* 15 | INDEX RANGE SCAN | OPT_PRMTN_DIM_NX1 | 1 | | 1 (0)| 00:00:01 | | | |* 16 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_PRMTN_DIM | 1 | 20 | 2 (0)| 00:00:01 | ROWID | ROWID | -------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 14 - access("C"."ACTVY_ID"="D"."ACTVY_ID") filter("C"."ACTVY_ID"="A"."PRMTN_FACT_ID") 15 - access("D"."PRMTN_ID"="E"."PRMTN_ID") 16 - filter("E"."PRMTN_STTUS_CODE"<>'Completed')

后来我kill了那个session,重新收集了某个表的统计信息，新的执行计划如下：

SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ Plan hash value: 54186536 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 4321 | 69136 | 12196 (4)| 00:00:39 | | | | 1 | MERGE | OPT_ACTVY_FCT | | | | | | | | 2 | VIEW | | | | | | | | |* 3 | HASH JOIN | | 4321 | 696K| 12196 (4)| 00:00:39 | | | |* 4 | HASH JOIN | | 4321 | 232K| 10201 (3)| 00:00:32 | | | |* 5 | HASH JOIN | | 6638 | 226K| 4601 (4)| 00:00:15 | | | | 6 | TABLE ACCESS FULL | OPT_ACTVY_REVSD_PLC | 6638 | 99570 | 5 (0)| 00:00:01 | | | | 7 | PARTITION LIST ALL| | 468K| 9143K| 4581 (4)| 00:00:15 | 1 | 16 | | 8 | TABLE ACCESS FULL| OPT_ACTVY_DIM | 468K| 9143K| 4581 (4)| 00:00:15 | 1 | 16 | | 9 | PARTITION LIST ALL | | 105K| 2064K| 5596 (3)| 00:00:18 | 1 | 16 | |* 10 | TABLE ACCESS FULL | OPT_PRMTN_DIM | 105K| 2064K| 5596 (3)| 00:00:18 | 1 | 16 | | 11 | PARTITION RANGE ALL | | 461K| 48M| 1980 (7)| 00:00:07 | 1 | 7 | | 12 | PARTITION LIST ALL | | 461K| 48M| 1980 (7)| 00:00:07 | 1 | 16 | | 13 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 461K| 48M| 1980 (7)| 00:00:07 | 1 | 112 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("C"."ACTVY_ID"="A"."PRMTN_FACT_ID") 4 - access("D"."PRMTN_ID"="E"."PRMTN_ID") 5 - access("C"."ACTVY_ID"="D"."ACTVY_ID") 10 - filter("E"."PRMTN_STTUS_CODE"<>'Completed') 已选择28行。

重新收集统计信息之后，让ETL重跑(我们ETL 是用contrl-m调度的),发现执行计划居然没改变，执行计划还是以前错误的执行计划

于是我又手工登陆SQLPLUS，自己跑了一下SQL，执行计划是正确的执行计划。这次见鬼了，可能遇到BUG了

那么这个时候解决方案有3个;
1.FLUSH SHARED POOL ,我这里是PB级别的数据仓库，FLASH 之后对性能影响肯定很大,而且我没权限，fuck
2.采用dbms_shared_pool.purge包，但是没权限,奶奶的我不是SYSDBA，fuck
3.更改ETL，加了个空格，让SQL硬解析，最终我采用了这个方案，幸好ETL可以更改代码，不然就悲催了，只能找SYSDBA走第二个方案了

-----update--------

原来是 gather统计信息之后忘记加上 no_invalidate=>FALSE

这个事情提醒俺看文档一定要仔细....