Main Env info : OLAP, RAC, 4 node. block size=16k
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 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
Today ETL developer asked me to check a long running SQL which had running for about 2 hours without results.
Below is the SQL code.
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;
Below is the execution plan
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.
Please see ID=48 ,operation is MERGE JOIN and Bytes=68G. That's too strange.And why Oracle not use HASH JOIN?
SQL> alter session set optimizer_features_enable='10.2.0.3';
Session altered.
Let's Check Execution Plan again.
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.
There is no SORT MERGE JOIN, try to run SQL
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
check if stats is stale using below SQL.
exec dbms_stats.flush_database_monitoring_info;
select owner || '.' || table_name name , object_type,stale_stats,last_analyzed from dba_tab_statistics
where owner='ADWU_OPTIMA_CE11' and table_name in
('OPT_ACCT_BRAND_FY_SPDNG_TEMP',
'OPT_BRAND_BASLN_IFCT',
'OPT_PRMTN_DIM',
'OPT_FUND_DIM'
) and stale_stats='YES';
I find table OPT_PRMTN_DIM is stale
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.
Check Execution Plan again.
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.
Still use SORT MERGE JOIN instead of HASH JOIN
I searched Metalink, not related bugs.
But it's no doubt that the issue is CBO issue.
扫一扫
1659
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
