Re: Large Dataset - Estimated 87TB needed for TEMP - suggestions?
Date: Tue, 27 Jan 2015 09:03:33 -0600
Message-ID: <CAP79kiSuxFQUMzC3MYXPZ8dfwQa7ahv6j6wh+aTW6LVdnTE_VQ_at_mail.gmail.com>
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_at_seb.lt> 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_at_gmail.com>
> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>,
> Date: 2015.01.27 16:12
> Subject: Large Dataset - Estimated 87TB needed for TEMP - suggestions?
> Sent by: oracle-l-bounce_at_freelists.org
> ------------------------------
>
>
>
> 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_at_SEL$1
> 8 - SEL$1 / B_at_SEL$1
> 9 - SEL$1 / C_at_SEL$1
> 11 - SEL$1 / A_at_SEL$1
>
> Outline Data
> -------------
>
> /*+
> BEGIN_OUTLINE_DATA
> USE_HASH_AGGREGATION(_at_"SEL$1")
> USE_HASH(_at_"SEL$1" "A"_at_"SEL$1")
> USE_HASH(_at_"SEL$1" "C"_at_"SEL$1")
> LEADING(_at_"SEL$1" "B"_at_"SEL$1" "C"@"SEL$1" "A"@"SEL$1")
> FULL(_at_"SEL$1" "A"_at_"SEL$1")
> INDEX_FFS(_at_"SEL$1" "C"_at_"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(_at_"SEL$1" "B"_at_"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(_at_"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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 27 2015 - 16:03:33 CET