主要环境 Oracle 11.1.0.7.0, OLAP 环境，block size=16K 4节点RAC 环境
SQL> select * from v$version where rownum=1;
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
SQL> show parameter block_size
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------
db_block_size integer 16384
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ --------------------------------- -----
cpu_count integer 24
parallel_threads_per_cpu integer 2
resource_manager_cpu_allocation integer 24
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ --------------------------------- -------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 21G
sga_target big integer 21G
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ --------------------------------- -------------
pga_aggregate_target big integer 30G
ETL开发人员叫我看一下下面的这个SQL语句，他说这个SQL跑了2小时都还没跑完
create table OPT_ACCT_BRAND_FY_SPDNG_FCT1 parallel 2 TABLESPACE OPTIMA01M nologging as SELECT BRAND.PROD_SKID, CALENDAR.MTH_SKID, CALENDAR.FISC_YR_SKID, BU.BUS_UNIT_SKID, ACCT.ACCT_SKID, ACCT.ACCT_NAME ACCOUNT, ACCT.NAME ACCOUNT_ID, CAT.PROD_DESC CATEGORY, CAT.PROD_NAME CATEGORY_ID, BRAND.PROD_DESC BRAND, BRAND.PROD_NAME BRAND_ID, CALENDAR.MTH_NAME MONTH_NAME, BU.BUS_UNIT_NAME, CALENDAR.FISC_YR_ABBR_NAME, --Optima R11, Dec 28th, Barbara Modified Begin, For TSP accounts, show NULL for Base and Increment SU DECODE(SUBSTR(ACCT.FUND_FRCST_MODEL_DESC,1,3),'TSP',NULL,SUM (BRAND_BASLN.ESTMT_BASLN_CASES_AMT)) BASE_SU, DECODE(SUBSTR(ACCT.FUND_FRCST_MODEL_DESC,1,3),'TSP',NULL,SUM (BRAND_BASLN.INCRM_SU_AMT)) INC_SU, --Optima R11, Dec 28th, Barbara Modified End SUM (BRAND_BASLN.TOT_PLAN_SU_AMT) TOTAL_SU, SUM (BRAND_BASLN.TOT_PLAN_GIV_AMT) GIV, SUM (BRAND_BASLN.TOT_PLAN_NIV_AMT) NIV, --Modified By Barbara on Nov 22nd 2010 in R11 for B018, --TOT_PLAN_NOS_AMT in opt_brand_basln_ifct has been aggregated result along account hierarchy, in this view, we do not want aggregated result --SUM (BRAND_BASLN.TOT_PLAN_NOS_AMT) NOS, SUM (BRAND_BASLN.TOT_PLAN_GIV_AMT) - SUM (BRAND_BASLN.TOT_SLOG_AMT) - SUM (TOT_MDA_LOR) NOS, SUM (BRAND_BASLN.TOT_SLOG_AMT) SLOG, SUM (ESTMT_FIXED_MDA_LOR) ESTMT_FIXED_MDA_LOR, SUM (ESTMT_VARIABLE_MDA_LOR) ESTMT_VARIABLE_MDA_LOR, SUM (ESTMT_FIXED_MDA_EXPENSE) ESTMT_FIXED_MDA_EXPENSE, SUM (ESTMT_VARIABLE_MDA_EXPENSE) ESTMT_VARIABLE_MDA_EXPENSE FROM (SELECT PROD_SKID, DATE_SKID, FY_DATE_SKID, PRMTN_ACCT_SKID, BUS_UNIT_SKID, SUM (ESTMT_BASLN_CASES_AMT) ESTMT_BASLN_CASES_AMT, SUM (INCRM_SU_AMT) INCRM_SU_AMT, SUM (TOT_PLAN_SU_AMT) TOT_PLAN_SU_AMT, SUM (TOT_PLAN_GIV_AMT) TOT_PLAN_GIV_AMT, SUM (TOT_PLAN_NIV_AMT) TOT_PLAN_NIV_AMT, SUM (TOT_PLAN_NOS_AMT) TOT_PLAN_NOS_AMT, SUM ((SLOG_TRADE_TERM_PCT/100) * TOT_PLAN_GIV_AMT) TOT_SLOG_AMT, 0 TOT_MDA_LOR, 0 ESTMT_FIXED_MDA_LOR, 0 ESTMT_VARIABLE_MDA_LOR, 0 ESTMT_FIXED_MDA_EXPENSE, 0 ESTMT_VARIABLE_MDA_EXPENSE FROM OPT_BRAND_BASLN_IFCT --Optima R11,Dec 28th, Barbara Add Begin,for OPT_BRAND_BASLN_IFCT contains many more accounts because of account aggregation WHERE PRMTN_ACCT_SKID IN (SELECT ACCT_SKID FROM OPT_PRMTN_DIM UNION all SELECT ACCT_SKID FROM OPT_FUND_DIM) --Optima R11,Dec 28th, Barbara Add End GROUP BY PROD_SKID, DATE_SKID, FY_DATE_SKID, PRMTN_ACCT_SKID, BUS_UNIT_SKID UNION ALL SELECT BRAND_SKID PROD_SKID, MTH_SKID DATE_SKID, FY_DATE_SKID, ACCT_SKID PRMTN_ACCT_SKID, BUS_UNIT_SKID, 0 ESTMT_BASLN_CASES_AMT, 0 INCRM_SU_AMT, 0 TOT_PLAN_SU_AMT, 0 TOT_PLAN_GIV_AMT, 0 TOT_PLAN_NIV_AMT, 0 TOT_PLAN_NOS_AMT, 0 TOT_SLOG_AMT, SUM (TOT_MDA_LOR) TOT_MDA_LOR, SUM (ESTMT_FIXED_MDA_LOR) ESTMT_FIXED_MDA_LOR, SUM (ESTMT_VARIABLE_MDA_LOR) ESTMT_VARIABLE_MDA_LOR, SUM (ESTMT_FIXED_MDA_EXPENSE) ESTMT_FIXED_MDA_EXPENSE, SUM (ESTMT_VARIABLE_MDA_EXPENSE) ESTMT_VARIABLE_MDA_EXPENSE FROM OPT_ACCT_BRAND_FY_SPDNG_TEMP GROUP BY BRAND_SKID, MTH_SKID, FY_DATE_SKID, ACCT_SKID, BUS_UNIT_SKID) BRAND_BASLN, OPT_PROD_FY_FDIM BRAND, OPT_PROD_FY_FDIM CAT, OPT_CAL_MASTR_MV01 CALENDAR, OPT_ACCT_FDIM ACCT, OPT_BUS_UNIT_FDIM BU WHERE BRAND_BASLN.PROD_SKID = BRAND.PROD_SKID AND BRAND_BASLN.FY_DATE_SKID = BRAND.FY_DATE_SKID AND BRAND.FY_DATE_SKID = CAT.FY_DATE_SKID AND BRAND.PARNT_PROD_ID = CAT.PROD_ID AND BRAND_BASLN.PRMTN_ACCT_SKID = ACCT_SKID AND BRAND_BASLN.BUS_UNIT_SKID = BRAND.BUS_UNIT_SKID AND BRAND_BASLN.DATE_SKID = CALENDAR.CAL_MASTR_SKID AND BRAND_BASLN.BUS_UNIT_SKID = BU.BUS_UNIT_SKID GROUP BY ACCT.ACCT_NAME, ACCT.NAME, CAT.PROD_DESC, CAT.PROD_NAME, BRAND.PROD_DESC, BRAND.PROD_NAME, CALENDAR.MTH_NAME, BU.BUS_UNIT_NAME, CALENDAR.FISC_YR_ABBR_NAME, BRAND.PROD_SKID, CALENDAR.MTH_SKID, BU.BUS_UNIT_SKID, CALENDAR.FISC_YR_SKID, ACCT.FUND_FRCST_MODEL_DESC, ACCT.ACCT_SKID;
好的， 那我们来看看执行计划
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1551275144 ------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 370M| 164G| | 19M (3)| 16:58:01 | | 1 | PX COORDINATOR | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10010 | 370M| 164G| | 19M (3)| 16:58:01 | | 3 | HASH GROUP BY | | 370M| 164G| 171G| 19M (3)| 16:58:01 | |* 4 | HASH JOIN | | 370M| 164G| | 105K (58)| 00:05:30 | | 5 | BUFFER SORT | | | | | | | | | 6 | PX RECEIVE | | 219K| 12M| | 2161 (4)| 00:00:07 | | 7 | PX SEND HASH | :TQ10004 | 219K| 12M| | 2161 (4)| 00:00:07 | | 8 | PARTITION LIST ALL | | 219K| 12M| | 2161 (4)| 00:00:07 | | 9 | TABLE ACCESS FULL | OPT_ACCT_FDIM | 219K| 12M| | 2161 (4)| 00:00:07 | | 10 | PX RECEIVE | | 368M| 142G| | 91951 (55)| 00:04:48 | | 11 | PX SEND HASH | :TQ10009 | 368M| 142G| | 91951 (55)| 00:04:48 | |* 12 | HASH JOIN BUFFERED | | 368M| 142G| | 91951 (55)| 00:04:48 | | 13 | BUFFER SORT | | | | | | | | | 14 | PX RECEIVE | | 37190 | 1271K| | 41 (10)| 00:00:01 | | 15 | PX SEND HASH | :TQ10003 | 37190 | 1271K| | 41 (10)| 00:00:01 | | 16 | TABLE ACCESS FULL | OPT_CAL_MASTR_MV01 | 37190 | 1271K| | 41 (10)| 00:00:01 | | 17 | PX RECEIVE | | 368M| 130G| | 80727 (48)| 00:04:13 | | 18 | PX SEND HASH | :TQ10008 | 368M| 130G| | 80727 (48)| 00:04:13 | |* 19 | HASH JOIN BUFFERED | | 368M| 130G| | 80727 (48)| 00:04:13 | | 20 | BUFFER SORT | | | | | | | | | 21 | PX RECEIVE | | 29 | 406 | | 1 (0)| 00:00:01 | | 22 | PX SEND HASH | :TQ10002 | 29 | 406 | | 1 (0)| 00:00:01 | | 23 | INDEX FULL SCAN | OPT_BUS_UNIT_FDIM_UX2 | 29 | 406 | | 1 (0)| 00:00:01 | | 24 | PX RECEIVE | | 368M| 125G| | 69546 (40)| 00:03:38 | | 25 | PX SEND HASH | :TQ10007 | 368M| 125G| | 69546 (40)| 00:03:38 | |* 26 | HASH JOIN BUFFERED | | 368M| 125G| | 69546 (40)| 00:03:38 | | 27 | BUFFER SORT | | | | | | | | | 28 | PX RECEIVE | | 1277K| 193M| | 7514 (14)| 00:00:24 | | 29 | PX SEND HASH | :TQ10001 | 1277K| 193M| | 7514 (14)| 00:00:24 | | 30 | PARTITION RANGE ALL | | 1277K| 193M| | 7514 (14)| 00:00:24 | | 31 | MERGE JOIN | | 1277K| 193M| | 7514 (14)| 00:00:24 | | 32 | SORT JOIN | | 1277K| 103M| 254M| 3823 (16)| 00:00:12 | | 33 | PARTITION LIST ALL | | 1277K| 103M| | 3515 (8)| 00:00:11 | | 34 | TABLE ACCESS FULL | OPT_PROD_FY_FDIM | 1277K| 103M| | 3515 (8)| 00:00:11 | |* 35 | SORT JOIN | | 1277K| 90M| 215M| 3691 (13)| 00:00:12 | | 36 | PARTITION LIST ALL | | 1277K| 90M| | 3383 (5)| 00:00:11 | | 37 | TABLE ACCESS FULL | OPT_PROD_FY_FDIM | 1277K| 90M| | 3383 (5)| 00:00:11 | | 38 | PX RECEIVE | | 368M| 71G| | 45115 (21)| 00:02:22 | | 39 | PX SEND HASH | :TQ10006 | 368M| 71G| | 45115 (21)| 00:02:22 | | 40 | BUFFER SORT | | 370M| 164G| | 19M (3)| 16:58:01 | | 41 | VIEW | | 368M| 71G| | 45115 (21)| 00:02:22 | | 42 | UNION-ALL | | | | | | | | | 43 | BUFFER SORT | | | | | | | | | 44 | PX RECEIVE | | | | | | | | | 45 | PX SEND ROUND-ROBIN | :TQ10000 | | | | | | | | 46 | HASH GROUP BY | | 362M| 68G| | 38247 (33)| 00:02:00 | | 47 | MERGE JOIN | | 362M| 68G| | 38247 (33)| 00:02:00 | | 48 | SORT JOIN | | 3262K| 588M| | 25060 (7)| 00:01:19 | | 49 | VIEW | VW_GBC_7 | 3262K| 588M| | 25060 (7)| 00:01:19 | | 50 | HASH GROUP BY | | 3262K| 164M| 324M| 25060 (7)| 00:01:19 | | 51 | PARTITION RANGE ALL | | 3262K| 164M| | 2150 (21)| 00:00:07 | | 52 | PARTITION LIST ALL | | 3262K| 164M| | 2150 (21)| 00:00:07 | | 53 | TABLE ACCESS FULL | OPT_BRAND_BASLN_IFCT | 3262K| 164M| | 2150 (21)| 00:00:07 | |* 54 | SORT JOIN | | 220K| 2798K| 8656K| 2280 (4)| 00:00:08 | | 55 | VIEW | VW_NSO_1 | 220K| 2798K| | 1687 (2)| 00:00:06 | | 56 | HASH UNIQUE | | 220K| 1291K| | 1687 (99)| 00:00:06 | | 57 | UNION-ALL | | | | | | | | | 58 | PARTITION LIST ALL | | 84900 | 497K| | 27 (0)| 00:00:01 | | 59 | BITMAP CONVERSION TO ROWIDS | | 84900 | 497K| | 27 (0)| 00:00:01 | | 60 | BITMAP INDEX FAST FULL SCAN| OPT_PRMTN_DIM_BX2 | | | | | | 1 | | 61 | PARTITION LIST ALL | | 135K| 794K| | 1660 (2)| 00:00:06 | | 62 | TABLE ACCESS FULL | OPT_FUND_DIM | 135K| 794K| | 1660 (2)| 00:00:06 | | 63 | HASH GROUP BY | | 5699K| 706M| | 6867 (19)| 00:00:22 | | 64 | PX RECEIVE | | 5699K| 706M| | 6867 (19)| 00:00:22 | | 65 | PX SEND HASH | :TQ10005 | 5699K| 706M| | 6867 (19)| 00:00:22 | | 66 | HASH GROUP BY | | 5699K| 706M| | 6867 (19)| 00:00:22 | | 67 | PX BLOCK ITERATOR | | 5699K| 706M| | 5945 (6)| 00:00:19 | | 68 | TABLE ACCESS FULL | OPT_ACCT_BRAND_FY_SPDNG_TEMP | 5699K| 706M| | 5945 (6)| 00:00:19 | ------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("BRAND_BASLN"."PRMTN_ACCT_SKID"="ACCT_SKID") 12 - access("BRAND_BASLN"."DATE_SKID"="CALENDAR"."CAL_MASTR_SKID") 19 - access("BRAND_BASLN"."BUS_UNIT_SKID"="BU"."BUS_UNIT_SKID") 26 - access("BRAND_BASLN"."PROD_SKID"="BRAND"."PROD_SKID" AND "BRAND_BASLN"."FY_DATE_SKID"="BRAND"."FY_DATE_SKID" AND "BRAND_BASLN"."BUS_UNIT_SKID"="BRAND"."BUS_UNIT_SKID") 35 - access("BRAND"."FY_DATE_SKID"="CAT"."FY_DATE_SKID" AND "BRAND"."PARNT_PROD_ID"="CAT"."PROD_ID") filter("BRAND"."PARNT_PROD_ID"="CAT"."PROD_ID" AND "BRAND"."FY_DATE_SKID"="CAT"."FY_DATE_SKID") 54 - access("ITEM_1"="ACCT_SKID") filter("ITEM_1"="ACCT_SKID") 87 rows selected.
请看ID=47 CBO选择了 SORT MERGE JOIN，并且Bytes=68G 这太奇怪了，我怀疑CBO出问题了
SQL> alter session set optimizer_features_enable='10.2.0.3';
Session altered.
我们再来看看执行计划
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3669890889 --------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13M| 6341M| | 2036K (9)| 01:46:08 | | 1 | PX COORDINATOR | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10012 | 13M| 6341M| | 2036K (9)| 01:46:08 | | 3 | HASH GROUP BY | | 13M| 6341M| 12G| 2036K (9)| 01:46:08 | |* 4 | HASH JOIN | | 13M| 6341M| | 1304K (14)| 01:08:01 | | 5 | BUFFER SORT | | | | | | | | 6 | PX RECEIVE | | 219K| 12M| | 2161 (4)| 00:00:07 | | 7 | PX SEND HASH | :TQ10005 | 219K| 12M| | 2161 (4)| 00:00:07 | | 8 | PARTITION LIST ALL | | 219K| 12M| | 2161 (4)| 00:00:07 | | 9 | TABLE ACCESS FULL | OPT_ACCT_FDIM | 219K| 12M| | 2161 (4)| 00:00:07 | | 10 | PX RECEIVE | | 13M| 5512M| | 1302K (14)| 01:07:52 | | 11 | PX SEND HASH | :TQ10011 | 13M| 5512M| | 1302K (14)| 01:07:52 | |* 12 | HASH JOIN BUFFERED | | 13M| 5512M| | 1302K (14)| 01:07:52 | | 13 | BUFFER SORT | | | | | | | | 14 | PX RECEIVE | | 1277K| 90M| | 3383 (5)| 00:00:11 | | 15 | PX SEND HASH | :TQ10004 | 1277K| 90M| | 3383 (5)| 00:00:11 | | 16 | PARTITION RANGE ALL | | 1277K| 90M| | 3383 (5)| 00:00:11 | | 17 | PARTITION LIST ALL | | 1277K| 90M| | 3383 (5)| 00:00:11 | | 18 | TABLE ACCESS FULL | OPT_PROD_FY_FDIM | 1277K| 90M| | 3383 (5)| 00:00:11 | | 19 | PX RECEIVE | | 13M| 4531M| | 1298K (14)| 01:07:40 | | 20 | PX SEND HASH | :TQ10010 | 13M| 4531M| | 1298K (14)| 01:07:40 | |* 21 | HASH JOIN BUFFERED | | 13M| 4531M| | 1298K (14)| 01:07:40 | | 22 | BUFFER SORT | | | | | | | | 23 | PX RECEIVE | | 1277K| 103M| | 3515 (8)| 00:00:11 | | 24 | PX SEND HASH | :TQ10003 | 1277K| 103M| | 3515 (8)| 00:00:11 | | 25 | PARTITION RANGE ALL | | 1277K| 103M| | 3515 (8)| 00:00:11 | | 26 | PARTITION LIST ALL | | 1277K| 103M| | 3515 (8)| 00:00:11 | | 27 | TABLE ACCESS FULL | OPT_PROD_FY_FDIM | 1277K| 103M| | 3515 (8)| 00:00:11 | | 28 | PX RECEIVE | | 13M| 3405M| | 1294K (14)| 01:07:28 | | 29 | PX SEND HASH | :TQ10009 | 13M| 3405M| | 1294K (14)| 01:07:28 | |* 30 | HASH JOIN BUFFERED | | 13M| 3405M| | 1294K (14)| 01:07:28 | | 31 | BUFFER SORT | | | | | | | | 32 | PX RECEIVE | | 37190 | 1271K| | 41 (10)| 00:00:01 | | 33 | PX SEND HASH | :TQ10002 | 37190 | 1271K| | 41 (10)| 00:00:01 | | 34 | TABLE ACCESS FULL | OPT_CAL_MASTR_MV01 | 37190 | 1271K| | 41 (10)| 00:00:01 | | 35 | PX RECEIVE | | 13M| 2941M| | 1293K (14)| 01:07:26 | | 36 | PX SEND HASH | :TQ10008 | 13M| 2941M| | 1293K (14)| 01:07:26 | |* 37 | HASH JOIN BUFFERED | | 13M| 2941M| | 1293K (14)| 01:07:26 | | 38 | BUFFER SORT | | | | | | | | 39 | PX RECEIVE | | 29 | 406 | | 1 (0)| 00:00:01 | | 40 | PX SEND HASH | :TQ10001 | 29 | 406 | | 1 (0)| 00:00:01 | | 41 | INDEX FULL SCAN | OPT_BUS_UNIT_FDIM_UX2 | 29 | 406 | | 1 (0)| 00:00:01 | | 42 | PX RECEIVE | | 13M| 2756M| | 1287K (13)| 01:07:08 | | 43 | PX SEND HASH | :TQ10007 | 13M| 2756M| | 1287K (13)| 01:07:08 | | 44 | BUFFER SORT | | 13M| 6341M| | 2036K (9)| 01:46:08 | | 45 | VIEW | | 13M| 2756M| | 1287K (13)| 01:07:08 | | 46 | UNION-ALL | | | | | | | | 47 | BUFFER SORT | | | | | | | | 48 | PX RECEIVE | | | | | | | | 49 | PX SEND ROUND-ROBIN | :TQ10000 | | | | | | | 50 | HASH GROUP BY | | 8194K| 515M| 33G| 1280K (14)| 01:06:46 | |* 51 | HASH JOIN | | 362M| 22G| 5392K| 18070 (65)| 00:00:57 | | 52 | VIEW | VW_NSO_1 | 220K| 2798K| | 1687 (2)| 00:00:06 | | 53 | HASH UNIQUE | | 220K| 1291K| | 1687 (99)| 00:00:06 | | 54 | UNION-ALL | | | | | | | | 55 | PARTITION LIST ALL | | 84900 | 497K| | 27 (0)| 00:00:01 | | 56 | BITMAP CONVERSION TO ROWIDS | | 84900 | 497K| | 27 (0)| 00:00:01 | | 57 | BITMAP INDEX FAST FULL SCAN| OPT_PRMTN_DIM_BX2 | | | | | | | 58 | PARTITION LIST ALL | | 135K| 794K| | 1660 (2)| 00:00:06 | | 59 | TABLE ACCESS FULL | OPT_FUND_DIM | 135K| 794K| | 1660 (2)| 00:00:06 | | 60 | PARTITION RANGE ALL | | 3262K| 164M| | 2150 (21)| 00:00:07 | | 61 | PARTITION LIST ALL | | 3262K| 164M| | 2150 (21)| 00:00:07 | | 62 | TABLE ACCESS FULL | OPT_BRAND_BASLN_IFCT | 3262K| 164M| | 2150 (21)| 00:00:07 | | 63 | HASH GROUP BY | | 5699K| 706M| | 6874 (19)| 00:00:22 | | 64 | PX RECEIVE | | 5699K| 706M| | 6874 (19)| 00:00:22 | | 65 | PX SEND HASH | :TQ10006 | 5699K| 706M| | 6874 (19)| 00:00:22 | | 66 | HASH GROUP BY | | 5699K| 706M| | 6874 (19)| 00:00:22 | | 67 | PX BLOCK ITERATOR | | 5699K| 706M| | 5945 (6)| 00:00:19 | | 68 | TABLE ACCESS FULL | OPT_ACCT_BRAND_FY_SPDNG_TEMP | 5699K| 706M| | 5945 (6)| 00:00:19 | ------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("BRAND_BASLN"."PRMTN_ACCT_SKID"="ACCT_SKID") 12 - access("BRAND"."FY_DATE_SKID"="CAT"."FY_DATE_SKID" AND "BRAND"."PARNT_PROD_ID"="CAT"."PROD_ID") 21 - access("BRAND_BASLN"."PROD_SKID"="BRAND"."PROD_SKID" AND "BRAND_BASLN"."FY_DATE_SKID"="BRAND"."FY_DATE_SKID" AND "BRAND_BASLN"."BUS_UNIT_SKID"=" 30 - access("BRAND_BASLN"."DATE_SKID"="CALENDAR"."CAL_MASTR_SKID") 37 - access("BRAND_BASLN"."BUS_UNIT_SKID"="BU"."BUS_UNIT_SKID") 51 - access("PRMTN_ACCT_SKID"="ACCT_SKID") 85 rows selected.
这回执行计划中没有SORT MERGE JOIN,
SQL> create table OPT_ACCT_BRAND_FY_SPDNG_FCT1 2 parallel 2 TABLESPACE OPTIMA01M 3 nologging as 4 SELECT BRAND.PROD_SKID, 5 CALENDAR.MTH_SKID, 6 CALENDAR.FISC_YR_SKID, 7 BU.BUS_UNIT_SKID, 8 ACCT.ACCT_SKID, 9 ACCT.ACCT_NAME ACCOUNT, 10 ACCT.NAME ACCOUNT_ID, 11 CAT.PROD_DESC CATEGORY, 12 CAT.PROD_NAME CATEGORY_ID, 13 BRAND.PROD_DESC BRAND, 14 BRAND.PROD_NAME BRAND_ID, 15 CALENDAR.MTH_NAME MONTH_NAME, 16 BU.BUS_UNIT_NAME, 17 CALENDAR.FISC_YR_ABBR_NAME, 18 --Optima R11, Dec 28th, Barbara Modified Begin, For TSP accounts, show NULL for Base and Increment SU 19 DECODE(SUBSTR(ACCT.FUND_FRCST_MODEL_DESC,1,3),'TSP',NULL,SUM (BRAND_BASLN.ESTMT_BASLN_CASES_AMT)) BASE_SU, 20 DECODE(SUBSTR(ACCT.FUND_FRCST_MODEL_DESC,1,3),'TSP',NULL,SUM (BRAND_BASLN.INCRM_SU_AMT)) INC_SU, 21 --Optima R11, Dec 28th, Barbara Modified End 22 SUM (BRAND_BASLN.TOT_PLAN_SU_AMT) TOTAL_SU, 23 SUM (BRAND_BASLN.TOT_PLAN_GIV_AMT) GIV, 24 SUM (BRAND_BASLN.TOT_PLAN_NIV_AMT) NIV, 25 --Modified By Barbara on Nov 22nd 2010 in R11 for B018, 26 --TOT_PLAN_NOS_AMT in opt_brand_basln_ifct has been aggregated result along account hierarchy, in this view, we do not want aggregated result 27 --SUM (BRAND_BASLN.TOT_PLAN_NOS_AMT) NOS, 28 SUM (BRAND_BASLN.TOT_PLAN_GIV_AMT) - SUM (BRAND_BASLN.TOT_SLOG_AMT) - SUM (TOT_MDA_LOR) NOS, 29 SUM (BRAND_BASLN.TOT_SLOG_AMT) SLOG, 30 SUM (ESTMT_FIXED_MDA_LOR) ESTMT_FIXED_MDA_LOR, 31 SUM (ESTMT_VARIABLE_MDA_LOR) ESTMT_VARIABLE_MDA_LOR, 32 SUM (ESTMT_FIXED_MDA_EXPENSE) ESTMT_FIXED_MDA_EXPENSE, 33 SUM (ESTMT_VARIABLE_MDA_EXPENSE) ESTMT_VARIABLE_MDA_EXPENSE 34 FROM (SELECT PROD_SKID, 35 DATE_SKID, 36 FY_DATE_SKID, 37 PRMTN_ACCT_SKID, 38 BUS_UNIT_SKID, 39 SUM (ESTMT_BASLN_CASES_AMT) ESTMT_BASLN_CASES_AMT, 40 SUM (INCRM_SU_AMT) INCRM_SU_AMT, 41 SUM (TOT_PLAN_SU_AMT) TOT_PLAN_SU_AMT, 42 SUM (TOT_PLAN_GIV_AMT) TOT_PLAN_GIV_AMT, 43 SUM (TOT_PLAN_NIV_AMT) TOT_PLAN_NIV_AMT, 44 SUM (TOT_PLAN_NOS_AMT) TOT_PLAN_NOS_AMT, 45 SUM ((SLOG_TRADE_TERM_PCT/100) * TOT_PLAN_GIV_AMT) TOT_SLOG_AMT, 46 0 TOT_MDA_LOR, 47 0 ESTMT_FIXED_MDA_LOR, 48 0 ESTMT_VARIABLE_MDA_LOR, 49 0 ESTMT_FIXED_MDA_EXPENSE, 50 0 ESTMT_VARIABLE_MDA_EXPENSE 51 FROM OPT_BRAND_BASLN_IFCT 52 --Optima R11,Dec 28th, Barbara Add Begin,for OPT_BRAND_BASLN_IFCT contains many more accounts because of account aggregation 53 WHERE PRMTN_ACCT_SKID IN (SELECT ACCT_SKID 54 FROM OPT_PRMTN_DIM 55 UNION all 56 SELECT ACCT_SKID 57 FROM OPT_FUND_DIM) 58 --Optima R11,Dec 28th, Barbara Add End 59 GROUP BY PROD_SKID, 60 DATE_SKID, 61 FY_DATE_SKID, 62 PRMTN_ACCT_SKID, 63 BUS_UNIT_SKID 64 UNION ALL 65 SELECT BRAND_SKID PROD_SKID, 66 MTH_SKID DATE_SKID, 67 FY_DATE_SKID, 68 ACCT_SKID PRMTN_ACCT_SKID, 69 BUS_UNIT_SKID, 70 0 ESTMT_BASLN_CASES_AMT, 71 0 INCRM_SU_AMT, 72 0 TOT_PLAN_SU_AMT, 73 0 TOT_PLAN_GIV_AMT, 74 0 TOT_PLAN_NIV_AMT, 75 0 TOT_PLAN_NOS_AMT, 76 0 TOT_SLOG_AMT, 77 SUM (TOT_MDA_LOR) TOT_MDA_LOR, 78 SUM (ESTMT_FIXED_MDA_LOR) ESTMT_FIXED_MDA_LOR, 79 SUM (ESTMT_VARIABLE_MDA_LOR) ESTMT_VARIABLE_MDA_LOR, 80 SUM (ESTMT_FIXED_MDA_EXPENSE) ESTMT_FIXED_MDA_EXPENSE, 81 SUM (ESTMT_VARIABLE_MDA_EXPENSE) ESTMT_VARIABLE_MDA_EXPENSE 82 FROM OPT_ACCT_BRAND_FY_SPDNG_TEMP 83 GROUP BY BRAND_SKID, 84 MTH_SKID, 85 FY_DATE_SKID, 86 ACCT_SKID, 87 BUS_UNIT_SKID) BRAND_BASLN, 88 OPT_PROD_FY_FDIM BRAND, 89 OPT_PROD_FY_FDIM CAT, 90 OPT_CAL_MASTR_MV01 CALENDAR, 91 OPT_ACCT_FDIM ACCT, 92 OPT_BUS_UNIT_FDIM BU 93 WHERE BRAND_BASLN.PROD_SKID = BRAND.PROD_SKID 94 AND BRAND_BASLN.FY_DATE_SKID = BRAND.FY_DATE_SKID 95 AND BRAND.FY_DATE_SKID = CAT.FY_DATE_SKID 96 AND BRAND.PARNT_PROD_ID = CAT.PROD_ID 97 AND BRAND_BASLN.PRMTN_ACCT_SKID = ACCT_SKID 98 AND BRAND_BASLN.BUS_UNIT_SKID = BRAND.BUS_UNIT_SKID 99 AND BRAND_BASLN.DATE_SKID = CALENDAR.CAL_MASTR_SKID 100 AND BRAND_BASLN.BUS_UNIT_SKID = BU.BUS_UNIT_SKID 101 GROUP BY ACCT.ACCT_NAME, 102 ACCT.NAME, 103 CAT.PROD_DESC, 104 CAT.PROD_NAME, 105 BRAND.PROD_DESC, 106 BRAND.PROD_NAME, 107 CALENDAR.MTH_NAME, 108 BU.BUS_UNIT_NAME, 109 CALENDAR.FISC_YR_ABBR_NAME, 110 BRAND.PROD_SKID, 111 CALENDAR.MTH_SKID, 112 BU.BUS_UNIT_SKID, 113 CALENDAR.FISC_YR_SKID, 114 ACCT.FUND_FRCST_MODEL_DESC, 115 ACCT.ACCT_SKID; Table created. Elapsed: 00:04:20.81
SQL执行了不到5分钟，你们可能怀疑是统计信息出了问题于是查看统计信息过期的表，我发现OPT_PRMTN_DIM统计信息过期了
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWU_OPTIMA_CE11',
3 tabname => 'OPT_PRMTN_DIM',
4 estimate_percent => 30,
5 method_opt => 'for all columns size repeat',
6 degree => 6,
7 granularity => 'ALL',
8 cascade=>TRUE
9 );
10 END;
11 /
PL/SQL procedure successfully completed.
我们再看一下执行计划
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1551275144 ------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 369M| 163G| | 19M (3)| 16:54:53 | | 1 | PX COORDINATOR | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10010 | 369M| 163G| | 19M (3)| 16:54:53 | | 3 | HASH GROUP BY | | 369M| 163G| 170G| 19M (3)| 16:54:53 | |* 4 | HASH JOIN | | 369M| 163G| | 105K (58)| 00:05:29 | | 5 | BUFFER SORT | | | | | | | | | 6 | PX RECEIVE | | 219K| 12M| | 2161 (4)| 00:00:07 | | 7 | PX SEND HASH | :TQ10004 | 219K| 12M| | 2161 (4)| 00:00:07 | | 8 | PARTITION LIST ALL | | 219K| 12M| | 2161 (4)| 00:00:07 | | 9 | TABLE ACCESS FULL | OPT_ACCT_FDIM | 219K| 12M| | 2161 (4)| 00:00:07 | | 10 | PX RECEIVE | | 367M| 142G| | 91813 (54)| 00:04:48 | | 11 | PX SEND HASH | :TQ10009 | 367M| 142G| | 91813 (54)| 00:04:48 | |* 12 | HASH JOIN BUFFERED | | 367M| 142G| | 91813 (54)| 00:04:48 | | 13 | BUFFER SORT | | | | | | | | | 14 | PX RECEIVE | | 37190 | 1271K| | 41 (10)| 00:00:01 | | 15 | PX SEND HASH | :TQ10003 | 37190 | 1271K| | 41 (10)| 00:00:01 | | 16 | TABLE ACCESS FULL | OPT_CAL_MASTR_MV01 | 37190 | 1271K| | 41 (10)| 00:00:01 | | 17 | PX RECEIVE | | 367M| 130G| | 80624 (48)| 00:04:13 | | 18 | PX SEND HASH | :TQ10008 | 367M| 130G| | 80624 (48)| 00:04:13 | |* 19 | HASH JOIN BUFFERED | | 367M| 130G| | 80624 (48)| 00:04:13 | | 20 | BUFFER SORT | | | | | | | | | 21 | PX RECEIVE | | 29 | 406 | | 1 (0)| 00:00:01 | | 22 | PX SEND HASH | :TQ10002 | 29 | 406 | | 1 (0)| 00:00:01 | | 23 | INDEX FULL SCAN | OPT_BUS_UNIT_FDIM_UX2 | 29 | 406 | | 1 (0)| 00:00:01 | | 24 | PX RECEIVE | | 367M| 125G| | 69477 (40)| 00:03:38 | | 25 | PX SEND HASH | :TQ10007 | 367M| 125G| | 69477 (40)| 00:03:38 | |* 26 | HASH JOIN BUFFERED | | 367M| 125G| | 69477 (40)| 00:03:38 | | 27 | BUFFER SORT | | | | | | | | | 28 | PX RECEIVE | | 1277K| 193M| | 7514 (14)| 00:00:24 | | 29 | PX SEND HASH | :TQ10001 | 1277K| 193M| | 7514 (14)| 00:00:24 | | 30 | PARTITION RANGE ALL | | 1277K| 193M| | 7514 (14)| 00:00:24 | | 31 | MERGE JOIN | | 1277K| 193M| | 7514 (14)| 00:00:24 | | 32 | SORT JOIN | | 1277K| 103M| 254M| 3823 (16)| 00:00:12 | | 33 | PARTITION LIST ALL | | 1277K| 103M| | 3515 (8)| 00:00:11 | | 34 | TABLE ACCESS FULL | OPT_PROD_FY_FDIM | 1277K| 103M| | 3515 (8)| 00:00:11 | |* 35 | SORT JOIN | | 1277K| 90M| 215M| 3691 (13)| 00:00:12 | | 36 | PARTITION LIST ALL | | 1277K| 90M| | 3383 (5)| 00:00:11 | | 37 | TABLE ACCESS FULL | OPT_PROD_FY_FDIM | 1277K| 90M| | 3383 (5)| 00:00:11 | | 38 | PX RECEIVE | | 367M| 71G| | 45080 (21)| 00:02:21 | | 39 | PX SEND HASH | :TQ10006 | 367M| 71G| | 45080 (21)| 00:02:21 | | 40 | BUFFER SORT | | 369M| 163G| | 19M (3)| 16:54:53 | | 41 | VIEW | | 367M| 71G| | 45080 (21)| 00:02:21 | | 42 | UNION-ALL | | | | | | | | | 43 | BUFFER SORT | | | | | | | | | 44 | PX RECEIVE | | | | | | | | | 45 | PX SEND ROUND-ROBIN | :TQ10000 | | | | | | | | 46 | HASH GROUP BY | | 361M| 68G| | 38212 (33)| 00:02:00 | | 47 | MERGE JOIN | | 361M| 68G| | 38212 (33)| 00:02:00 | | 48 | SORT JOIN | | 3262K| 588M| | 25060 (7)| 00:01:19 | | 49 | VIEW | VW_GBC_7 | 3262K| 588M| | 25060 (7)| 00:01:19 | | 50 | HASH GROUP BY | | 3262K| 164M| 324M| 25060 (7)| 00:01:19 | | 51 | PARTITION RANGE ALL | | 3262K| 164M| | 2150 (21)| 00:00:07 | | 52 | PARTITION LIST ALL | | 3262K| 164M| | 2150 (21)| 00:00:07 | | 53 | TABLE ACCESS FULL | OPT_BRAND_BASLN_IFCT | 3262K| 164M| | 2150 (21)| 00:00:07 | |* 54 | SORT JOIN | | 219K| 2790K| 8624K| 2280 (4)| 00:00:08 | | 55 | VIEW | VW_NSO_1 | 219K| 2790K| | 1688 (2)| 00:00:06 | | 56 | HASH UNIQUE | | 219K| 1287K| | 1688 (99)| 00:00:06 | | 57 | UNION-ALL | | | | | | | | | 58 | PARTITION LIST ALL | | 84210 | 493K| | 28 (0)| 00:00:01 | | 59 | BITMAP CONVERSION TO ROWIDS | | 84210 | 493K| | 28 (0)| 00:00:01 | | 60 | BITMAP INDEX FAST FULL SCAN| OPT_PRMTN_DIM_BX2 | | | | | | 1 | | 61 | PARTITION LIST ALL | | 135K| 794K| | 1660 (2)| 00:00:06 | | 62 | TABLE ACCESS FULL | OPT_FUND_DIM | 135K| 794K| | 1660 (2)| 00:00:06 | | 63 | HASH GROUP BY | | 5699K| 706M| | 6867 (19)| 00:00:22 | | 64 | PX RECEIVE | | 5699K| 706M| | 6867 (19)| 00:00:22 | | 65 | PX SEND HASH | :TQ10005 | 5699K| 706M| | 6867 (19)| 00:00:22 | | 66 | HASH GROUP BY | | 5699K| 706M| | 6867 (19)| 00:00:22 | | 67 | PX BLOCK ITERATOR | | 5699K| 706M| | 5945 (6)| 00:00:19 | | 68 | TABLE ACCESS FULL | OPT_ACCT_BRAND_FY_SPDNG_TEMP | 5699K| 706M| | 5945 (6)| 00:00:19 | ------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("BRAND_BASLN"."PRMTN_ACCT_SKID"="ACCT_SKID") 12 - access("BRAND_BASLN"."DATE_SKID"="CALENDAR"."CAL_MASTR_SKID") 19 - access("BRAND_BASLN"."BUS_UNIT_SKID"="BU"."BUS_UNIT_SKID") 26 - access("BRAND_BASLN"."PROD_SKID"="BRAND"."PROD_SKID" AND "BRAND_BASLN"."FY_DATE_SKID"="BRAND"."FY_DATE_SKID" AND "BRAND_BASLN"."BUS_UNIT_S 35 - access("BRAND"."FY_DATE_SKID"="CAT"."FY_DATE_SKID" AND "BRAND"."PARNT_PROD_ID"="CAT"."PROD_ID") filter("BRAND"."PARNT_PROD_ID"="CAT"."PROD_ID" AND "BRAND"."FY_DATE_SKID"="CAT"."FY_DATE_SKID") 54 - access("ITEM_1"="ACCT_SKID") filter("ITEM_1"="ACCT_SKID") 87 rows selected.
执行计划一点没变，还是走了SORT MERGE JOIN
所以我怀疑遇到11g CBO SORT MERGE JOIN的bug,不过搜索了Metalink半天没找到
83
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
