Also, it completed successfully for period 20142 but the explain plan for that one said it would need 573 TB of TEMP space so now I realize I have zero way to "guess" how much temp space it really needs but that 150 GB isn't enough. The stats are accurate for 100% estimation so I'm not sure why the estimate on the temp space is so high. On Tue, Jan 27, 2015 at 8:50 AM, <Laimutis.Nedzinskas@xxxxxx> wrote: > Apert from thinking how to reduce the temp one question comes into mind: > > who's gonna read (estimated)289G of rows :o ?? > > For me it looks like a programming error. > > Pagarbiai / Sincerely > Laimutis Nedzinskas > Lead of Database Administrators > IT Lithuania, SEB > Direct phone +370-5-2682759 > > > --------------------------------------------------------------------------------- > Please consider the environment before printing this e-mail > > [image: Inactive hide details for Chris Taylor ---2015.01.27 > 16:12:41---Env; 11.2.0.2 Linux 5.8 Machine:]Chris Taylor ---2015.01.27 > 16:12:41---Env; 11.2.0.2 Linux 5.8 Machine: > > From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx> > To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, > Date: 2015.01.27 16:12 > Subject: Large Dataset - Estimated 87TB needed for TEMP - suggestions? > Sent by: oracle-l-bounce@xxxxxxxxxxxxx > ------------------------------ > > > > Env; 11.2.0.2 Linux 5.8 > Machine: > 4 CPU (8 Core, 16 Threads E5-4620) > 256 GB RAM > > This is a "data warehouse" DB but doesn't use DIM/FACT tables - more like > a data dump database. > > I have a query against 3 tables (2 of which are partitioned) and the > estimated TEMP space used for this query is going to be 81 TB. I'm looking > for suggestions on how to get this data set. For the partitioned tables, 1 > table accesses 1 partition, and the other table uses 2 partitions. I've > included the explain plan below and any help/suggestions are appreciated. > (I've updated the table stats yesterday after I kept blowing out my 150GB > temp space and the estimated temp space used was 22GB. After updating > stats, it tells me the estimated Temp Space is going to be 81 TB). > > PGA Agg Target is 20 GB right now. > > I've explained the query using both the default HASH GROUP BY and a SORT > GROUP BY and both estimate using 81 T of temp space. > > Here's the query and the plan (let me know if the fixed formatting doesn't > show up correctly and I'll use pastebin to format it if needed) > > SELECT a.STS_PERF_PERIOD, > a.PART_ACCT_NO, > a.PWN_PART_AKA_NME, > a.PWN_P_W_IND, > a.AFL_SOCIETY_NME, > b.sts_CHANNEL_TYPE, > c.stp_s03_program_cd--TOTAL Music Minutes CALCULATION: > , > SUM ( > CASE > WHEN STG_STATUS = 'DP' > THEN > 0 > ELSE > a.perf_count > * ( ( a.BMI_FEATURE > + a.BMI_BG > + a.BMI_LOGO > + a.BMI_THEME > + a.BMI_BV > + a.ASCAP_FEATURE > + a.ASCAP_BG > + a.ASCAP_LOGO > + a.ASCAP_THEME > + a.ASCAP_BV > + a.FOREIGN_FEATURE > + a.FOREIGN_BG > + a.FOREIGN_LOGO > + a.FOREIGN_THEME > + a.FOREIGN_BV > + a.PD_FEATURE > + a.PD_BG > + a.PD_LOGO > + a.PD_THEME > + a.PD_BV > + a.SESAC_FEATURE > + a.SESAC_BG > + a.SESAC_LOGO > + a.SESAC_THEME > + a.SESAC_BV > + a.NA_FEATURE > + a.NA_BG > + a.NA_LOGO > + a.NA_THEME > + a.NA_BV)) > END) > / 60 > AS "TOT_MUSIC_MINS"--BMI Music Minutes CALCULATION: > , > SUM ( > CASE > WHEN STG_STATUS = 'DP' > THEN > 0 > ELSE > a.perf_count > * ( ( a.BMI_FEATURE > + a.BMI_BG > + a.BMI_LOGO > + a.BMI_THEME > + a.BMI_BV)) > END) > / 60 > AS "BMI_MUSIC_MINS" > FROM PRDM.COWS_PURPLE_PART a, --partition(P20141) > prdm.cows_TCUE_STS_SCHEDULE b, --partition(P20141,P20142) , > prdm.cows_tcue_stp_program c > WHERE A.STS_PERF_PERIOD = b.sts_perf_period > AND A.STS_DPS_TYPE = b.sts_dps_type > AND A.STG_GROUP_NO = b.sts_group_no > AND a.stg_group_seq_no = b.sts_group_seq_no > AND b.sts_provider = c.stp_provider > AND b.sts_program_no = c.stp_program_no > AND a.STS_PERF_PERIOD = '20141' > AND b.sts_perf_source = 'CB' > AND b.COWS_DIST_PERIOD in ('20141','20142') > AND a.PWN_P_W_IND = 'W' > AND c.stp_s03_program_cd <> '36' > GROUP BY a.STS_PERF_PERIOD, > a.PART_ACCT_NO, > a.PWN_PART_AKA_NME, > a.PWN_P_W_IND, > a.AFL_SOCIETY_NME, > b.sts_CHANNEL_TYPE, > c.stp_s03_program_cd > / > > > Plan hash value: 2657901994 > > > ---------------------------------------------------------------------------------------------------------------------------------------------- > | Id | Operation | Name > | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop | > > ---------------------------------------------------------------------------------------------------------------------------------------------- > | 0 | SELECT STATEMENT | > | 289G| 57T| | 451M (9)|501:19:35 | | | > | 1 | HASH GROUP BY | > | 289G| 57T| 81T| 451M (9)|501:19:35 | | | > |* 2 | HASH JOIN | > | 289G| 57T| 42M| 2176K (91)| 02:25:07 | | | > | 3 | PART JOIN FILTER CREATE | :BF0000 > | 680K| 35M| | 39040 (2)| 00:02:37 | | | > |* 4 | HASH JOIN | > | 680K| 35M| 4304K| 39040 (2)| 00:02:37 | | | > | 5 | INLIST ITERATOR | > | | | | | | | | > | 6 | PARTITION RANGE ITERATOR | > | 80046 | 3361K| | 3063 (1)| 00:00:13 |KEY(I) |KEY(I) | > | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| COWS_TCUE_STS_SCHEDULE > | 80046 | 3361K| | 3063 (1)| 00:00:13 |KEY(I) |KEY(I) | > |* 8 | INDEX RANGE SCAN | > COWS_TCUE_STS_SCHED_IDX01 | 9052 | | | 147 (1)| 00:00:01 > |KEY(I) |KEY(I) | > |* 9 | INDEX FAST FULL SCAN | COWS_TCUE_STP_PROG_IDX01 > | 24M| 256M| | 12602 (3)| 00:00:51 | | | > | 10 | PARTITION RANGE SINGLE | > | 10M| 1582M| | 96246 (2)| 00:06:25 |KEY(AP)|KEY(AP)| > |* 11 | TABLE ACCESS FULL | COWS_PURPLE_PART > | 10M| 1582M| | 96246 (2)| 00:06:25 | 21 | 21 | > > ---------------------------------------------------------------------------------------------------------------------------------------------- > > Query Block Name / Object Alias (identified by operation id): > ------------------------------------------------------------- > > 1 - SEL$1 > 7 - SEL$1 / B@SEL$1 > 8 - SEL$1 / B@SEL$1 > 9 - SEL$1 / C@SEL$1 > 11 - SEL$1 / A@SEL$1 > > Outline Data > ------------- > > /*+ > BEGIN_OUTLINE_DATA > USE_HASH_AGGREGATION(@"SEL$1") > USE_HASH(@"SEL$1" "A"@"SEL$1") > USE_HASH(@"SEL$1" "C"@"SEL$1") > LEADING(@"SEL$1" "B"@"SEL$1" "C"@"SEL$1" "A"@"SEL$1") > FULL(@"SEL$1" "A"@"SEL$1") > INDEX_FFS(@"SEL$1" "C"@"SEL$1" > ("COWS_TCUE_STP_PROGRAM"."STP_S03_PROGRAM_CD" > "COWS_TCUE_STP_PROGRAM"."STP_PROVIDER" > "COWS_TCUE_STP_PROGRAM"."STP_PROGRAM_NO")) > INDEX_RS_ASC(@"SEL$1" "B"@"SEL$1" > ("COWS_TCUE_STS_SCHEDULE"."COWS_DIST_PERIOD" > "COWS_TCUE_STS_SCHEDULE"."STS_PERF_PERIOD" > "COWS_TCUE_STS_SCHEDULE"."STS_PERF_SOURCE")) > OUTLINE_LEAF(@"SEL$1") > NO_PARALLEL > DB_VERSION('11.2.0.2') > OPTIMIZER_FEATURES_ENABLE('11.2.0.2') > IGNORE_OPTIM_EMBEDDED_HINTS > END_OUTLINE_DATA > */ > > Predicate Information (identified by operation id): > --------------------------------------------------- > > 2 - access("A"."STS_PERF_PERIOD"="B"."STS_PERF_PERIOD" AND > "A"."STS_DPS_TYPE"="B"."STS_DPS_TYPE" AND > "A"."STG_GROUP_NO"="B"."STS_GROUP_NO" AND > "A"."STG_GROUP_SEQ_NO"="B"."STS_GROUP_SEQ_NO") > 4 - access("B"."STS_PROVIDER"="C"."STP_PROVIDER" AND > "B"."STS_PROGRAM_NO"="C"."STP_PROGRAM_NO") > 8 - access(("B"."COWS_DIST_PERIOD"='20141' OR > "B"."COWS_DIST_PERIOD"='20142') AND "B"."STS_PERF_PERIOD"='20141' AND > "B"."STS_PERF_SOURCE"='CB') > 9 - filter("C"."STP_S03_PROGRAM_CD"<>'36') > 11 - filter("A"."PWN_P_W_IND"='W' AND "A"."STS_PERF_PERIOD"='20141') > > Column Projection Information (identified by operation id): > ----------------------------------------------------------- > > 1 - (#keys=7) "A"."STS_PERF_PERIOD"[VARCHAR2,5], > "A"."PART_ACCT_NO"[NUMBER,22], "A"."PWN_PART_AKA_NME"[VARCHAR2,30], > "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], > "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], > "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2], SUM(CASE "STG_STATUS" WHEN > 'DP' THEN 0 ELSE > > > "A"."PERF_COUNT"*("A"."BMI_FEATURE"+"A"."BMI_BG"+"A"."BMI_LOGO"+"A"."BMI_THEME"+"A"."BMI_BV") > END )[22], SUM(CASE "STG_STATUS" WHEN > 'DP' THEN 0 ELSE > "A"."PERF_COUNT"*("A"."BMI_FEATURE"+"A"."BMI_BG"+"A"."BMI_LOGO"+"A"."BMI_THEME"+"A"."BMI_BV"+"A"."ASCAP_FEATURE"+"A". > > > "ASCAP_BG"+"A"."ASCAP_LOGO"+"A"."ASCAP_THEME"+"A"."ASCAP_BV"+"A"."FOREIGN_FEATURE"+"A"."FOREIGN_BG"+"A"."FOREIGN_LOGO"+"A"."FOREIGN_TH > > > EME"+"A"."FOREIGN_BV"+"A"."PD_FEATURE"+"A"."PD_BG"+"A"."PD_LOGO"+"A"."PD_THEME"+"A"."PD_BV"+"A"."SESAC_FEATURE"+"A"."SESAC_BG"+"A"."SE > > > SAC_LOGO"+"A"."SESAC_THEME"+"A"."SESAC_BV"+"A"."NA_FEATURE"+"A"."NA_BG"+"A"."NA_LOGO"+"A"."NA_THEME"+"A"."NA_BV") > END )[22] > 2 - (#keys=4) "A"."STS_PERF_PERIOD"[VARCHAR2,5], > "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2], "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], > "A"."FOREIGN_LOGO"[NUMBER,22], "A"."PD_LOGO"[NUMBER,22], > "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2], > "A"."NA_LOGO"[NUMBER,22], "A"."SESAC_LOGO"[NUMBER,22], > "A"."PART_ACCT_NO"[NUMBER,22], "A"."PWN_PART_AKA_NME"[VARCHAR2,30], > "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], > "A"."ASCAP_BG"[NUMBER,22], "A"."BMI_BG"[NUMBER,22], > "A"."SESAC_BG"[NUMBER,22], "A"."NA_BG"[NUMBER,22], > "A"."PD_BG"[NUMBER,22], "A"."FOREIGN_BG"[NUMBER,22], > "A"."ASCAP_BV"[NUMBER,22], > "A"."BMI_BV"[NUMBER,22], "A"."SESAC_BV"[NUMBER,22], > "A"."NA_BV"[NUMBER,22], "A"."PD_BV"[NUMBER,22], > "A"."FOREIGN_BV"[NUMBER,22], > "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], > "A"."SESAC_THEME"[NUMBER,22], "A"."NA_THEME"[NUMBER,22], > "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], > "A"."ASCAP_FEATURE"[NUMBER,22], "A"."BMI_FEATURE"[NUMBER,22], > "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], > "A"."PD_FEATURE"[NUMBER,22], "A"."FOREIGN_FEATURE"[NUMBER,22], > "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22] > 3 - "B"."STS_PERF_PERIOD"[VARCHAR2,5], > "B"."STS_PERF_PERIOD"[VARCHAR2,5], "B"."STS_DPS_TYPE"[VARCHAR2,10], > "B"."STS_GROUP_NO"[NUMBER,22], "B"."STS_GROUP_SEQ_NO"[NUMBER,22], > "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2], > "B"."STS_CHANNEL_TYPE"[VARCHAR2,7] > 4 - (#keys=2) "B"."STS_PERF_PERIOD"[VARCHAR2,5], > "B"."STS_GROUP_NO"[NUMBER,22], "B"."STS_GROUP_SEQ_NO"[NUMBER,22], > "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], > "B"."STS_DPS_TYPE"[VARCHAR2,10], "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2] > 5 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], > "B"."STS_GROUP_NO"[NUMBER,22], > "B"."STS_GROUP_SEQ_NO"[NUMBER,22], > "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10], > "B"."STS_PERF_PERIOD"[VARCHAR2,5] > 6 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], > "B"."STS_GROUP_NO"[NUMBER,22], > "B"."STS_GROUP_SEQ_NO"[NUMBER,22], > "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10], > "B"."STS_PERF_PERIOD"[VARCHAR2,5] > 7 - "B"."STS_PROGRAM_NO"[NUMBER,22], "B"."STS_PROVIDER"[VARCHAR2,1], > "B"."STS_GROUP_NO"[NUMBER,22], > "B"."STS_GROUP_SEQ_NO"[NUMBER,22], > "B"."STS_CHANNEL_TYPE"[VARCHAR2,7], "B"."STS_DPS_TYPE"[VARCHAR2,10], > "B"."STS_PERF_PERIOD"[VARCHAR2,5] > 8 - "B".ROWID[ROWID,10], "B"."STS_PERF_PERIOD"[VARCHAR2,5] > 9 - "C"."STP_PROGRAM_NO"[NUMBER,22], "C"."STP_PROVIDER"[VARCHAR2,1], > "C"."STP_S03_PROGRAM_CD"[VARCHAR2,2] > 10 - "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."STS_DPS_TYPE"[VARCHAR2,10], > "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2], > "A"."STG_GROUP_NO"[NUMBER,22], "A"."STG_GROUP_SEQ_NO"[NUMBER,22], > "A"."PART_ACCT_NO"[NUMBER,22], "A"."PWN_PART_AKA_NME"[VARCHAR2,30], > "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], > "A"."ASCAP_BG"[NUMBER,22], "A"."BMI_BG"[NUMBER,22], > "A"."SESAC_BG"[NUMBER,22], "A"."NA_BG"[NUMBER,22], > "A"."PD_BG"[NUMBER,22], "A"."FOREIGN_BG"[NUMBER,22], > "A"."ASCAP_BV"[NUMBER,22], > "A"."BMI_BV"[NUMBER,22], "A"."SESAC_BV"[NUMBER,22], > "A"."NA_BV"[NUMBER,22], "A"."PD_BV"[NUMBER,22], > "A"."FOREIGN_BV"[NUMBER,22], > "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], > "A"."SESAC_THEME"[NUMBER,22], "A"."NA_THEME"[NUMBER,22], > "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], > "A"."ASCAP_FEATURE"[NUMBER,22], "A"."BMI_FEATURE"[NUMBER,22], > "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], > "A"."PD_FEATURE"[NUMBER,22], "A"."FOREIGN_FEATURE"[NUMBER,22], > "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22], > "A"."SESAC_LOGO"[NUMBER,22], "A"."NA_LOGO"[NUMBER,22], > "A"."PD_LOGO"[NUMBER,22], "A"."FOREIGN_LOGO"[NUMBER,22] > 11 - "A"."STS_PERF_PERIOD"[VARCHAR2,5], "A"."STS_DPS_TYPE"[VARCHAR2,10], > "A"."PERF_COUNT"[NUMBER,22], "STG_STATUS"[VARCHAR2,2], > "A"."STG_GROUP_NO"[NUMBER,22], "A"."STG_GROUP_SEQ_NO"[NUMBER,22], > "A"."PART_ACCT_NO"[NUMBER,22], "A"."PWN_PART_AKA_NME"[VARCHAR2,30], > "A"."PWN_P_W_IND"[VARCHAR2,1], "A"."AFL_SOCIETY_NME"[VARCHAR2,8], > "A"."ASCAP_BG"[NUMBER,22], "A"."BMI_BG"[NUMBER,22], > "A"."SESAC_BG"[NUMBER,22], "A"."NA_BG"[NUMBER,22], > "A"."PD_BG"[NUMBER,22], "A"."FOREIGN_BG"[NUMBER,22], > "A"."ASCAP_BV"[NUMBER,22], > "A"."BMI_BV"[NUMBER,22], "A"."SESAC_BV"[NUMBER,22], > "A"."NA_BV"[NUMBER,22], "A"."PD_BV"[NUMBER,22], > "A"."FOREIGN_BV"[NUMBER,22], > "A"."ASCAP_THEME"[NUMBER,22], "A"."BMI_THEME"[NUMBER,22], > "A"."SESAC_THEME"[NUMBER,22], "A"."NA_THEME"[NUMBER,22], > "A"."PD_THEME"[NUMBER,22], "A"."FOREIGN_THEME"[NUMBER,22], > "A"."ASCAP_FEATURE"[NUMBER,22], "A"."BMI_FEATURE"[NUMBER,22], > "A"."SESAC_FEATURE"[NUMBER,22], "A"."NA_FEATURE"[NUMBER,22], > "A"."PD_FEATURE"[NUMBER,22], "A"."FOREIGN_FEATURE"[NUMBER,22], > "A"."ASCAP_LOGO"[NUMBER,22], "A"."BMI_LOGO"[NUMBER,22], > "A"."SESAC_LOGO"[NUMBER,22], "A"."NA_LOGO"[NUMBER,22], > "A"."PD_LOGO"[NUMBER,22], "A"."FOREIGN_LOGO"[NUMBER,22] > > Note > ----- > - automatic DOP: skipped because of IO calibrate statistics are missing > - Warning: basic plan statistics not available. These are only > collected when: > * hint 'gather_plan_statistics' is used for the statement or > * parameter 'statistics_level' is set to 'ALL', at session or > system level > > >