本案例发生在 2010 年，当时作者罗老师在惠普担任开发 DBA，支撑宝洁公司的数据仓库项目。为了避免泄露信息，他对 SQL 语句做了适当修改。ETL 开发人员发来邮件说有个 long running job，执行了两小时左右还未完成，需要检查一下。收到邮件后，立即检查数据库中正在运行的 SQL，经过与 ETL 开发人员确认，抓出执行计划（为了排版需要，删除了执行计划中非关键部分）。
select * from table(dbms_xplan.display_cursor('gh1hw18uz6dcm',0));
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------SQL_ID gh1hw18uz6dcm, child number 0-------------------------------------create table OPT_REF_BASE_UOM_TEMP_SDIM parallel 2nologging as SELECT PROD_SKID, RELTV_CURR_QTY,STAT_CURR_VAL, BAR_CURR_CODE FROM OPT_REF_BASE_UOM_DIM_VWPlan hash value: 2933813170-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |-------------------------------------------------------------------------------------| 0 | CREATE TABLE STATEMENT | | | || 1 | PX COORDINATOR | | | || 2 | PX SEND QC (RANDOM) | :TQ10001 | 54 | 2916 || 3 | LOAD AS SELECT | | | || 4 | HASH GROUP BY | | 54 | 2916 || 5 | PX RECEIVE | | 54 | 2916 || 6 | PX SEND HASH | :TQ10000 | 54 | 2916 || 7 | HASH GROUP BY | | 54 | 2916 || 8 | NESTED LOOPS | | | || 9 | NESTED LOOPS | | 3134 | 165K|| 10 | PX BLOCK ITERATOR | | | ||* 11 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 3065 | 104K||* 12 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | ||* 13 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------11 - access(:Z>=:Z AND :Z<=:Z)filter("UOM"."RELTV_CURR_QTY"=1)12 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")13 - filter(("PROD"."BUOM_CURR_SKID" IS NOT NULL AND"PROD"."PROD_END_DATE"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-ddhh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND "PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID"))
这个工作很简单，就是 create table …..as select …..。
create table OPT_REF_BASE_UOM_TEMP_SDIM parallel 2 nologgingas SELECT PROD_SKID， RELTV_CURR_QTY， STAT_CURR_VAL， BAR_CURR_CODEFROM OPT_REF_BASE_UOM_DIM_VW；
OPT_REF_BASE_UOM_DIM_VW 是一个视图，该视图定义：
SELECT UOM.PROD_SKID，MAX （UOM.RELTV_CURR_QTY） RELTV_CURR_QTY，MAX （UOM.STAT_CURR_VAL） STAT_CURR_VAL，MAX （UOM.BAR_CURR_CODE） BAR_CURR_CODEFROM OPT_REF_UOM_TEMP_SDIM UOM，REF_PROD_DIM PRODWHERE UOM.RELTV_CURR_QTY = 1AND PROD.CURR_IND = 'Y'AND PROD.PROD_END_DATE = TO_DATE （'31-12-9999', 'dd-mm-yyyy'）AND PROD.PROD_SKID = UOM.PROD_SKIDAND PROD.BUOM_CURR_SKID = UOM.UOM_SKIDGROUP BY UOM.PROD_SKID；
这个视图的查询效率就直接决定了 ETL JOB 的效率，现在我们查看这个视图的执行计划。
explain plan for SELECT UOM.PROD_SKID,MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODEFROM OPT_REF_UOM_TEMP_SDIM UOM,REF_PROD_DIM PRODWHERE UOM.RELTV_CURR_QTY = 1AND PROD.CURR_IND = 'Y'AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')AND PROD.PROD_SKID = UOM.PROD_SKIDAND PROD.BUOM_CURR_SKID = UOM.UOM_SKIDGROUP BY UOM.PROD_SKID;
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------Plan hash value: 3215660883-------------------------------------------------------------------------------------| Id |Operation |Name |Rows | Bytes | Cost(%CPU)|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 78| 4212 | 15507 (1)|| 1 | HASH GROUP BY | | 78| 4212 | 15507 (1)|| 2 | NESTED LOOPS | | | | || 3 | NESTED LOOPS | | 3034| 159K| 15506 (1)||* 4 | TABLE ACCESS FULL |OPT_REF_UOM_TEMP_SDIM| 2967| 101K| 650 (14)||* 5 | INDEX RANGE SCAN |PROD_DIM_PK | 3| | 2 (0)||* 6 | TABLE ACCESS BY INDEX ROWID|PROD_DIM | 1| 19 | 5 (0)|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------4 - filter("UOM"."RELTV_CURR_QTY"=1)5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")22 rows selected.
Id=4 是执行计划的入口，它是嵌套循环的驱动表。CBO 估算 Id=4 返回 2 967 行数据。对于嵌套循环，我们首先要检查驱动表返回的真实行数是否与估算的行数有较大偏差，现在查看驱动表总行数。
select count(*) from OPT_REF_UOM_TEMP_SDIM;
COUNT(*)----------2137706
我们查看驱动表返回的真实行数。
select count(*) from OPT_REF_UOM_TEMP_SDIM where "RELTV_CURR_QTY"=1;
COUNT(*)----------946432
驱动表实际上返回了 94 万行数据，与估算的 2 967 相差巨大。嵌套循环中，驱动表返回多少行数据，被驱动表就会被扫描多少次，这里被驱动表会被扫描 94 万次，这就解释了为什么 SQL 执行了两个小时还没执成功。显然执行计划是错误的，应该走 HASH 连接。
本案例是因为 Rows 估算有严重偏差，导致走错执行计划。Rows 估算与统计信息有关。Id=4 过滤条件是 RELTV_CURR_QTY = 1，现在我们来查看表和列的统计信息。
select a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,a.histogramfrom dba_tab_col_statistics a,dba_tables bwhere a.owner=b.owner and a.table_name=b.table_nameand a.table_name='OPT_REF_UOM_TEMP_SDIM' and a.column_name='RELTV_CURR_QTY';
NAME COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM---------------------- -------------- ------------------- --------- -------------OPT_REF_UOM_TEMP_SDIM RELTV_CURR_QTY 2160000 728 .000337037 NONE
统计信息中表总行数有 2 160 000 行数据，与真实的行数（2 137 706）十分接近，这说明表的统计信息没有问题。RELTV_CURR_QTY 列的基数等于 728，没有直方图（HISTOGRAM =NONE）。为什么 Id=4 会估算返回 2 967 行数据呢？正是因为 RELTV_CURR_QTY 列基数太低，而且没有收集直方图，CBO 认为该列数据分布是均衡的，导致在估算 Rows 的时候，直接以表总行数/列基数 =216 000/728=2967 来进行估算。所以我们需要对 RELTV_CURR_QTY 列收集直方图。
BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'XXXX',tabname => 'OPT_REF_UOM_TEMP_SDIM',estimate_percent => 100,method_opt => 'for columns RELTV_CURR_QTY size skewonly',degree => DBMS_STATS.AUTO_DEGREE,cascade=>TRUE);END;
PL/SQL procedure successfully completed.
收集完直方图之后，我们再来查看执行计划。
explain plan for SELECT UOM.PROD_SKID,MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODEFROM OPT_REF_UOM_TEMP_SDIM UOM,REF_PROD_DIM PRODWHERE UOM.RELTV_CURR_QTY = 1AND PROD.CURR_IND = 'Y'AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')AND PROD.PROD_SKID = UOM.PROD_SKIDAND PROD.BUOM_CURR_SKID = UOM.UOM_SKIDGROUP BY UOM.PROD_SKID;
Explained.
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------Plan hash value: 612020119-------------------------------------------------------------------------------------| Id |Operation | Name |Rows | Bytes |TempSpc| Cost(%CPU)|-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | |12097| 637K| | 44911 (5)|| 1 | HASH GROUP BY | |12097| 637K| | 44911 (5)||* 2 | HASH JOIN | | 951K| 48M| 29M| 44799 (5)||* 3 | TABLE ACCESS FULL| PROD_DIM | 998K| 18M| | 43022 (5)||* 4 | TABLE ACCESS FULL| OPT_REF_UOM_TEMP_SDIM | 951K| 31M| | 654 (15)|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID" AND"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")3 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y')4 - filter("UOM"."RELTV_CURR_QTY"=1)20 rows selected.
现在执行计划自动走了 HASH 连接，这才是正确的执行计划，走了正确的执行计划之后，SQL 能在 8 分钟左右执行完毕。
我们也可以换种思路优化该 SQL。该 SQL 属于 ETL，ETL 一般都需要清洗大量数据，两表关联处理大量数据应该走 HASH 连接，所以我们可以直接让两个表走 HASH 连接。另外该 SQL 有分组汇总（GROUP BY），需要分组汇总的 SQL 一般也是处理大量数据，基于此该 SQL 也应该走 HASH 连接。
若有收获，就点个赞吧
0 人点赞
