Oracle SQL consumes huge temp space

Question

I'm facing a trade-off issue between temp space & query performance.

My scenario is like this: I have a huge transaction table (20 billion records across 25 partitions) and a small metric lookup table with 7 records. I need to process each transaction record for each metric record. Essentially output will be 7 * 20 billion records. This output will have to be aggregated based on 5-6 columns.

I have considered two options:

Cross join these two tables and specify processing logic wrt metric using "case when" and do "group by" operation.
Have seven different queries for each metric id and "UNION ALL" the results.

#1 is consuming huge temp space of about 250 GB and #2 runs for around 230 mins.

Is there a way, I can optimize one of these options? I need to get this query completed in 60 mins.

Adding query

@david query is pasted below

WITH IDQ_LKP AS 
    (SELECT '703' AS METRIC_ID,'desc1' AS EN_METRIC_1_NM
    FROM DUAL
    UNION ALL
    SELECT '702' AS METRIC_ID,'desc1' AS EN_METRIC_1_NM
    FROM DUAL
    UNION ALL
    SELECT '704' AS METRIC_ID,'% desc1' AS EN_METRIC_1_NM
    FROM DUAL
    UNION ALL 
    SELECT '705' AS METRIC_ID,'desc2' AS EN_METRIC_1_NM
    FROM DUAL
    UNION ALL 
    SELECT '706' AS METRIC_ID,'desc3' AS EN_METRIC_1_NM
    FROM DUAL
    UNION ALL
    SELECT '707' AS METRIC_ID,'desc5' AS EN_METRIC_1_NM
    FROM DUAL
    UNION ALL
    SELECT '701' AS METRIC_ID,'desc4' AS EN_METRIC_1_NM
    FROM DUAL)
SELECT /*+ parallel(16) USE_HASH_AGGREGATION */ col1 ,
         col2 ,
         'Monthly Snapshots' AS Time_Rollup , col3 , date_pk , colr , col5 , colr_DESC , col5_DESC , metric_id , EN_METRIC_1_NM , sum (
    CASE
    WHEN (metric_id='704'
        AND record_identifier=17 )
        OR (metric_id='705'
        AND record_identifier=123)
        OR (metric_id='706'
        AND record_identifier=43)
        OR (metric_id='707'
        AND record_identifier=34) THEN
    nvl ( record_count,0 )
    WHEN metric_id NOT IN ('704','705','706','707') THEN
    NULL
    ELSE 0
    END ) AS METRIC_1_CY , NULL AS METRIC_1_LY , sum (
    CASE
    WHEN (metric_id='703'
        AND record_identifier=17)
        OR (metric_id='705'
        AND record_identifier=777 )
        OR (metric_id='702'
        AND record_identifier=123 )
        OR (metric_id='704'
        AND record_identifier=17 )
        OR (metric_id='706'
        AND record_identifier=99999997 )
        OR (metric_id='707'
        AND record_identifier=99999996) THEN
    nvl ( record_count,0 )
    WHEN metric_id NOT IN ('702','703','704','705','706','707') THEN
    NULL
    ELSE 0
    END ) AS METRIC_2_CY , NULL AS METRIC_2_LY , NULL AS METRIC_3_CY , NULL AS METRIC_3_LY
FROM TXN,LKP
WHERE col1=2
GROUP BY  col1 , col2 , col3 , date_pk , colr , col5 , colr_DESC , col5_DESC, metric_id , EN_METRIC_1_NM

Hi ,

I still see 252 GB need of temp space in explain plan...

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |       |       |       |    17M|       |       |        |      |            |
|   1 |  PX COORDINATOR                |                           |       |       |       |       |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10003                  |  1894M|   217G|       |    17M|       |       |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY               |                           |  1894M|   217G|   262G|    17M|       |       |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                 |                           |  1894M|   217G|       |    17M|       |       |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH              | :TQ10002                  |  1894M|   217G|       |    17M|       |       |  Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY            |                           |  1894M|   217G|   262G|    17M|       |       |  Q1,02 | PCWP |            |
|   7 |        MERGE JOIN CARTESIAN    |                           |  1894M|   217G|       |   149K|       |       |  Q1,02 | PCWP |            |
|   8 |         BUFFER SORT            |                           |       |       |       |       |       |       |  Q1,02 | PCWC |            |
|   9 |          PX RECEIVE            |                           |     7 |   154 |       |    14 |       |       |  Q1,02 | PCWP |            |
|  10 |           PX SEND BROADCAST    | :TQ10000                  |     7 |   154 |       |    14 |       |       |        | S->P | BROADCAST  |
|  11 |            VIEW                |                           |     7 |   154 |       |    14 |       |       |        |      |            |
|  12 |             UNION-ALL          |                           |       |       |       |       |       |       |        |      |            |
|  13 |              FAST DUAL         |                           |     1 |       |       |     2 |       |       |        |      |            |
|  14 |              FAST DUAL         |                           |     1 |       |       |     2 |       |       |        |      |            |
|  15 |              FAST DUAL         |                           |     1 |       |       |     2 |       |       |        |      |            |
|  16 |              FAST DUAL         |                           |     1 |       |       |     2 |       |       |        |      |            |
|  17 |              FAST DUAL         |                           |     1 |       |       |     2 |       |       |        |      |            |
|  18 |              FAST DUAL         |                           |     1 |       |       |     2 |       |       |        |      |            |
|  19 |              FAST DUAL         |                           |     1 |       |       |     2 |       |       |        |      |            |
|  20 |         BUFFER SORT            |                           |   270M|    25G|       |    17M|       |       |  Q1,02 | PCWP |            |
|  21 |          VIEW                  |                           |   270M|    25G|       |       |       |       |  Q1,02 | PCWP |            |
|  22 |           HASH GROUP BY        |                           |   270M|    22G|    29G|   115K|       |       |  Q1,02 | PCWP |            |
|  23 |            PX RECEIVE          |                           |   270M|    22G|       |   843 |       |       |  Q1,02 | PCWP |            |
|  24 |             PX SEND HASH       | :TQ10001                  |   270M|    22G|       |   843 |       |       |  Q1,01 | P->P | HASH       |
|  25 |              PX BLOCK ITERATOR |                           |   270M|    22G|       |   843 |    28 |    55 |  Q1,01 | PCWC |            |
|* 26 |               TABLE ACCESS FULL| TXN                       |   270M|    22G|       |   843 |    28 |    55 |  Q1,01 | PCWP |            |
---------------------------------------------------------------------------------------------------------

Its running now... I doubt it may get stuck into same problem....

Plan for "UNION ALL" approach..

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                      | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                           |       |       |       |   774K|       |       |        |      |            |
|   1 |  UNION-ALL               |                           |       |       |       |       |       |       |        |      |            |
|   2 |   PX COORDINATOR         |                           |       |       |       |       |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10001                  |   270M|    18G|       |   100K|       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     HASH GROUP BY        |                           |   270M|    18G|    24G|   100K|       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE          |                           |   270M|    18G|       |   843 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000                  |   270M|    18G|       |   843 |       |       |  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |                           |   270M|    18G|       |   843 |    28 |    55 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| TXN                       |   270M|    18G|       |   843 |    28 |    55 |  Q1,00 | PCWP |            |
|   9 |   PX COORDINATOR         |                           |       |       |       |       |       |       |        |      |            |
|  10 |    PX SEND QC (RANDOM)   | :TQ20001                  |   270M|    21G|       |   112K|       |       |  Q2,01 | P->S | QC (RAND)  |
|  11 |     HASH GROUP BY        |                           |   270M|    21G|    28G|   112K|       |       |  Q2,01 | PCWP |            |
|  12 |      PX RECEIVE          |                           |   270M|    21G|       |   843 |       |       |  Q2,01 | PCWP |            |
|  13 |       PX SEND HASH       | :TQ20000                  |   270M|    21G|       |   843 |       |       |  Q2,00 | P->P | HASH       |
|  14 |        PX BLOCK ITERATOR |                           |   270M|    21G|       |   843 |    28 |    55 |  Q2,00 | PCWC |            |
|* 15 |         TABLE ACCESS FULL| TXN                       |   270M|    21G|       |   843 |    28 |    55 |  Q2,00 | PCWP |            |
|  16 |   PX COORDINATOR         |                           |       |       |       |       |       |       |        |      |            |
|  17 |    PX SEND QC (RANDOM)   | :TQ30001                  |   270M|    21G|       |   112K|       |       |  Q3,01 | P->S | QC (RAND)  |
|  18 |     HASH GROUP BY        |                           |   270M|    21G|    28G|   112K|       |       |  Q3,01 | PCWP |            |
|  19 |      PX RECEIVE          |                           |   270M|    21G|       |   843 |       |       |  Q3,01 | PCWP |            |
|  20 |       PX SEND HASH       | :TQ30000                  |   270M|    21G|       |   843 |       |       |  Q3,00 | P->P | HASH       |
|  21 |        PX BLOCK ITERATOR |                           |   270M|    21G|       |   843 |    28 |    55 |  Q3,00 | PCWC |            |
|* 22 |         TABLE ACCESS FULL| TXN                       |   270M|    21G|       |   843 |    28 |    55 |  Q3,00 | PCWP |            |
|  23 |   PX COORDINATOR         |                           |       |       |       |       |       |       |        |      |            |
|  24 |    PX SEND QC (RANDOM)   | :TQ40001                  |   270M|    21G|       |   112K|       |       |  Q4,01 | P->S | QC (RAND)  |
|  25 |     HASH GROUP BY        |                           |   270M|    21G|    28G|   112K|       |       |  Q4,01 | PCWP |            |
|  26 |      PX RECEIVE          |                           |   270M|    21G|       |   843 |       |       |  Q4,01 | PCWP |            |
|  27 |       PX SEND HASH       | :TQ40000                  |   270M|    21G|       |   843 |       |       |  Q4,00 | P->P | HASH       |
|  28 |        PX BLOCK ITERATOR |                           |   270M|    21G|       |   843 |    28 |    55 |  Q4,00 | PCWC |            |
|* 29 |         TABLE ACCESS FULL| TXN                       |   270M|    21G|       |   843 |    28 |    55 |  Q4,00 | PCWP |            |
|  30 |   PX COORDINATOR         |                           |       |       |       |       |       |       |        |      |            |
|  31 |    PX SEND QC (RANDOM)   | :TQ50001                  |   270M|    21G|       |   112K|       |       |  Q5,01 | P->S | QC (RAND)  |
|  32 |     HASH GROUP BY        |                           |   270M|    21G|    28G|   112K|       |       |  Q5,01 | PCWP |            |
|  33 |      PX RECEIVE          |                           |   270M|    21G|       |   843 |       |       |  Q5,01 | PCWP |            |
|  34 |       PX SEND HASH       | :TQ50000                  |   270M|    21G|       |   843 |       |       |  Q5,00 | P->P | HASH       |
|  35 |        PX BLOCK ITERATOR |                           |   270M|    21G|       |   843 |    28 |    55 |  Q5,00 | PCWC |            |
|* 36 |         TABLE ACCESS FULL| TXN                       |   270M|    21G|       |   843 |    28 |    55 |  Q5,00 | PCWP |            |
|  37 |   PX COORDINATOR         |                           |       |       |       |       |       |       |        |      |            |
|  38 |    PX SEND QC (RANDOM)   | :TQ60001                  |   270M|    21G|       |   112K|       |       |  Q6,01 | P->S | QC (RAND)  |
|  39 |     HASH GROUP BY        |                           |   270M|    21G|    28G|   112K|       |       |  Q6,01 | PCWP |            |
|  40 |      PX RECEIVE          |                           |   270M|    21G|       |   843 |       |       |  Q6,01 | PCWP |            |
|  41 |       PX SEND HASH       | :TQ60000                  |   270M|    21G|       |   843 |       |       |  Q6,00 | P->P | HASH       |
|  42 |        PX BLOCK ITERATOR |                           |   270M|    21G|       |   843 |    28 |    55 |  Q6,00 | PCWC |            |
|* 43 |         TABLE ACCESS FULL| TXN                       |   270M|    21G|       |   843 |    28 |    55 |  Q6,00 | PCWP |            |
|  44 |   PX COORDINATOR         |                           |       |       |       |       |       |       |        |      |            |
|  45 |    PX SEND QC (RANDOM)   | :TQ70001                  |   270M|    21G|       |   112K|       |       |  Q7,01 | P->S | QC (RAND)  |
|  46 |     SORT GROUP BY        |                           |   270M|    21G|    28G|   112K|       |       |  Q7,01 | PCWP |            |
|  47 |      PX RECEIVE          |                           |   270M|    21G|       |   843 |       |       |  Q7,01 | PCWP |            |
|  48 |       PX SEND HASH       | :TQ70000                  |   270M|    21G|       |   843 |       |       |  Q7,00 | P->P | HASH       |
|  49 |        PX BLOCK ITERATOR |                           |   270M|    21G|       |   843 |    28 |    55 |  Q7,00 | PCWC |            |
|* 50 |         TABLE ACCESS FULL| TXN                       |   270M|    21G|       |   843 |    28 |    55 |  Q7,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------

Of the 230 minutes run time, what amount is used in reading and writing the temporary space? How much space does the table itself take up? Is table's partitioning key column one of the grouping columns? Is the sort optimal, single pass, or multipass? How many rows are expected from the grouping operation in total? — David Aldridge, Feb 06 '14 at 11:04
Any chance to aggregate the transaction table, before doing the metric table join? — Thorsten Kettner, Feb 06 '14 at 11:08
How much space does the table itself take up? 18 GB Yes table's partitioning key is also used in aggregation , after grouping - I would expect close to 7 billion records.... how do I find if sort is optimal ? — user3279189, Feb 06 '14 at 11:11
I'd be interested in knowing whether the query is running aggregations at a partition level -- show the query and the explain plan for the query please, and DDL for the tables. You monitor use of temp space operations with V$SQL_WORKAREA or V$SQL_WORKAREA_ACTIVE. — David Aldridge, Feb 06 '14 at 11:34
@david - I shall share the plan... one more observation is , it spends a lot of time on "buffer sort".... is there a way I can avoid that ? — user3279189, Feb 06 '14 at 11:44
Is there any particular reason why you're using strings instead of numbers for your metric_id values? — Frank Schmitt, Feb 06 '14 at 12:03
What about a query plan? Do you really need undocumented `USE_HASH_AGGREGATION` hint (please review [this answer on Database Administrators part of StackExcahnge](http://dba.stackexchange.com/a/13170/25530))? — ThinkJet, Feb 06 '14 at 12:10
A buffer sort is generally just an in-memory storage of data to prevent it having to be read multiple times. It's probably your lkp data, I'd think. — David Aldridge, Feb 06 '14 at 12:23

score 1 · Answer 1 · answered Feb 06 '14 at 12:34

1

Just looking at the query, one approach that I'd consider trying is to change from this method of implementation:

Join large table to small table
Calculate metric value
Aggregate to required level

... to this ...

Aggregate large table to required level (applying filter if possible to remove rows not required).
Join to small table
Calculate metrics
Possibly aggregate again

That ought to require a smaller temp data set.

answered Feb 06 '14 at 12:34

David Aldridge

51,479
8
68
96

1

Get explain plans for both approaches – David Aldridge Feb 06 '14 at 13:11
I have pasted the explain for the new approach , pls take a look and suggest...thanks in advance... – user3279189 Feb 06 '14 at 14:52
Three group-by operations? – David Aldridge Feb 06 '14 at 15:51
Will it help if I use global temp table ? – user3279189 Feb 07 '14 at 04:34

score 1 · Answer 2 · answered Feb 06 '14 at 13:31

My comment to your request went somehow unheard. So again: To avoid huge intermediate data, reduce it beforehand. Please try the following. First the large table gets pre-aggregated, then cross joined, then aggregated again. This reduces the intermediate data in the cartesian product. This may solve your problem.

select 
  col1, col2, 'Monthly Snapshots' AS Time_Rollup, col3, date_pk, colr, col5, colr_DESC, col5_DESC, metric_id, en_metric_1_nm
  , sum 
  (
    case
    when (metric_id='704' and record_identifier=17 )
     or (metric_id='705' and record_identifier=123)
     or (metric_id='706' and record_identifier=43)
     or (metric_id='707' and record_identifier=34) then
      sum_record_count
    when metric_id not in ('704','705','706','707') then
      null
    else 
      0
    end
  ) as metric_1_cy
  , null as metric_1_ly 
  , sum 
  (
    case
    when (metric_id='703' and record_identifier=17)
     or (metric_id='705' and record_identifier=777 )
     or (metric_id='702' and record_identifier=123 )
     or (metric_id='704' and record_identifier=17 )
     or (metric_id='706' and record_identifier=99999997 )
     or (metric_id='707' and record_identifier=99999996) then
      sum_record_count
    when metric_id not in ('702','703','704','705','706','707') then
      null
    else 
      0
    end
  ) as metric_2_cy
  , null as metric_2_ly
  , null as metric_3_cy 
  , null as metric_3_ly
from
(
  select col1, col2, col3, date_pk, colr, col5, colr_desc, col5_desc, record_identifier, 
    sum(nvl(record_count,0)) as sum_record_count
  from txn
  where col1=2
  group by col1, col2, col3, date_pk, colr, col5, colr_desc, col5_desc, record_identifier
) pre_aggregate
cross join 
(
  select '703' as metric_id,'desc1' as en_metric_1_nm from dual
  union all
  select '702' as metric_id,'desc1' as en_metric_1_nm from dual
  ...
) lkp 
group by col1, col2, col3, date_pk, colr, col5, colr_DESC, col5_DESC, metric_id, en_metric_1_nm;

I'm new to this forum , is there a way I can format and paste query / plan ? — user3279189, Feb 06 '14 at 14:46
I tried this , it has failed as it is consuming temp space more than 150 GB — user3279189, Feb 06 '14 at 14:57
Sorry to hear that. Well, 150 GB is better than 250 GB, isn't it :-) Seems that a cross join is too expensive then anyway. Have you tried this pre-aggregation technique with separate statements and union all, yet? — Thorsten Kettner, Feb 06 '14 at 16:02

score 0 · Answer 3 · answered Feb 06 '14 at 11:06

0

Looks like you could make the seven separate queries of option two run in parallel over different CPUs. Maybe set up some kind of view to address the need to union the result?

answered Feb 06 '14 at 11:06

unigeek

2,656
27
27

It already runs in parallel...Its an extract job ..so view will not be of help – user3279189 Feb 06 '14 at 11:15
Yeah, I saw the Oracle tag after I submitted.. Figured your hardware probably was capable since only seven threads. – unigeek Feb 06 '14 at 11:17

Jon Heller · Answer 4 · 2014-02-08T06:26:06.123

Create a query for each partition. Since the partition key is one of the aggregate columns each partition will generate separate data.

Breaking a single statement into multiple smaller statements is almost always less efficient. But with partitioning the entire table's data will only be read once even though it is queried 25 times. This change should be only slightly less efficient than your original approach but should use significantly less temporary tablespace. Unless the partitions are horribly skewed.

begin
    --Loop through all partitions.
    for partition_names in
    (
        select partition_name
        from user_tab_partitions
        where table_name = 'TXN'
    ) loop
        --Execute the statement.
        execute immediate q'<
            WITH IDQ_LKP AS
            ...
            FROM TXN partition (>'||partition_names.partition_name||q'<),LKP
            ...
        >';

        --Normally commits should not be in DML loops, but a commit is required
        --if the statement uses direct-path writes.
        commit;
    end loop;
end;
/

Update - A single query approach and evidence that it might work

The queries on each partition can be combined into a single SQL statement. This should still perform well and use less temporary tablespace, although it will be one helluva large SQL statement.

The basic idea is:

with lookup as ...
select txn partition (partition1), lookup ... union all
select txn partition (partition2), lookup ... union all
...
select txn partition (partition25), lookup ... union all

Here's the sample table, sample data, and fake statistics:

create table txn(date_pk date, col1 number, col2 number, col3 number, colr number
    ,col5 number, colr_desc number, col5_desc number, record_count number
    ,record_identifier number)
partition by hash (col2)
(
    partition p01,partition p02,partition p03,partition p04,partition p05,
    partition p06,partition p07,partition p08,partition p09,partition p10,
    partition p11,partition p12,partition p13,partition p14,partition p15,
    partition p16,partition p17,partition p18,partition p19,partition p20,
    partition p21,partition p22,partition p23,partition p24,partition p25
);

--A few fake rows just to create a column censity for GROUP BY 
insert into txn
select sysdate, 2, level, level, level, level, level, level, level, level
from dual connect by level <= 100000;

begin
    --Gather mostly for column density.
    --Use GLOBAL because I don't know how to fake partition stats.
    dbms_stats.gather_table_stats(user, 'txn', granularity => 'global');
    --Fake 452M rows.  This number will generate 262G of TempSpc.
    --I can't reproduce it with 20G because I don't know the column densities.
    dbms_stats.set_table_stats(user, 'TXN', numrows => 452000000);
end;
/

Here's the original query, with a few cosmetic modifications to make it smaller. It still contains the same columns, conditions, and groupings. Note that the optimizer estimates it will use 262GB of temporary tablespace, and the execution is pretty similar to yours.

explain plan for
WITH IDQ_LKP AS 
(
    SELECT '703' AS METRIC_ID,'desc1' AS EN_METRIC_1_NM FROM DUAL UNION ALL
    SELECT '702' AS METRIC_ID,'desc1' AS EN_METRIC_1_NM FROM DUAL UNION ALL
    SELECT '704' AS METRIC_ID,'% desc1' AS EN_METRIC_1_NM FROM DUAL UNION ALL 
    SELECT '705' AS METRIC_ID,'desc2' AS EN_METRIC_1_NM FROM DUAL UNION ALL 
    SELECT '706' AS METRIC_ID,'desc3' AS EN_METRIC_1_NM FROM DUAL UNION ALL
    SELECT '707' AS METRIC_ID,'desc5' AS EN_METRIC_1_NM FROM DUAL UNION ALL
    SELECT '701' AS METRIC_ID,'desc4' AS EN_METRIC_1_NM FROM DUAL
)
SELECT /*+ parallel(16) */
    col1,col2,'Monthly Snapshots' Time_Rollup,col3,date_pk,colr,col5,colr_DESC,col5_DESC,metric_id,EN_METRIC_1_NM,NULL METRIC_1_LY,NULL METRIC_2_LY,NULL METRIC_3_CY,NULL METRIC_3_LY
    ,sum (CASE WHEN (metric_id='704' AND record_identifier=17 ) THEN nvl ( record_count,0 ) ELSE 0 END ) AS METRIC_1_CY
    ,sum (CASE WHEN (metric_id='703' AND record_identifier=17) THEN nvl ( record_count,0 ) ELSE 0 END ) AS METRIC_2_CY 
FROM TXN,IDQ_LKP
WHERE col1=2
GROUP BY  col1 , col2 , col3 , date_pk , colr , col5 , colr_DESC , col5_DESC, metric_id , EN_METRIC_1_NM;

select * from table(dbms_xplan.display);


Plan hash value: 2764457837

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |  3163M|   179G|       |    67M  (1)| 00:43:46 |       |       |        |      |            |
|   1 |  PX COORDINATOR            |          |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002 |  3163M|   179G|       |    67M  (1)| 00:43:46 |       |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY           |          |  3163M|   179G|   262G|    67M  (1)| 00:43:46 |       |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE             |          |  3163M|   179G|       |    67M  (1)| 00:43:46 |       |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH          | :TQ10001 |  3163M|   179G|       |    67M  (1)| 00:43:46 |       |       |  Q1,01 | P->P | HASH       |
|   6 |       HASH GROUP BY        |          |  3163M|   179G|   262G|    67M  (1)| 00:43:46 |       |       |  Q1,01 | PCWP |            |
|   7 |        MERGE JOIN CARTESIAN|          |  3163M|   179G|       |   488K  (1)| 00:00:20 |       |       |  Q1,01 | PCWP |            |
|   8 |         PX RECEIVE         |          |     7 |    70 |       |    14   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|   9 |          PX SEND BROADCAST | :TQ10000 |     7 |    70 |       |    14   (0)| 00:00:01 |       |       |  Q1,00 | S->P | BROADCAST  |
|  10 |           PX SELECTOR      |          |       |       |       |            |          |       |       |  Q1,00 | SCWC |            |
|  11 |            VIEW            |          |     7 |    70 |       |    14   (0)| 00:00:01 |       |       |  Q1,00 | SCWC |            |
|  12 |             UNION-ALL      |          |       |       |       |            |          |       |       |  Q1,00 | SCWC |            |
|  13 |              FAST DUAL     |          |     1 |       |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | SCWP |            |
|  14 |              FAST DUAL     |          |     1 |       |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | SCWP |            |
|  15 |              FAST DUAL     |          |     1 |       |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | SCWP |            |
|  16 |              FAST DUAL     |          |     1 |       |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | SCWP |            |
|  17 |              FAST DUAL     |          |     1 |       |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | SCWP |            |
|  18 |              FAST DUAL     |          |     1 |       |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | SCWP |            |
|  19 |              FAST DUAL     |          |     1 |       |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | SCWP |            |
|  20 |         BUFFER SORT        |          |   451M|    21G|       |    67M  (1)| 00:43:46 |       |       |  Q1,01 | PCWP |            |
|  21 |          PX BLOCK ITERATOR |          |   451M|    21G|       |   570  (95)| 00:00:01 |     1 |    25 |  Q1,01 | PCWC |            |
|* 22 |           TABLE ACCESS FULL| TXN      |   451M|    21G|       |   570  (95)| 00:00:01 |     1 |    25 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  22 - filter("COL1"=2)

Note
-----
   - Degree of Parallelism is 16 because of hint

Replacing the single txn,idq_lkp with multiple txn partition (pXX),idq_lkp reduces the maximum temporary tablespace estimate from 262G to 10G. This example only contains 2 of the 25 partitions but adding more partitions does not increase the temporary tablespace required.

explain plan for
WITH IDQ_LKP AS 
(
    SELECT '703' AS METRIC_ID,'desc1' AS EN_METRIC_1_NM FROM DUAL UNION ALL
    SELECT '702' AS METRIC_ID,'desc1' AS EN_METRIC_1_NM FROM DUAL UNION ALL
    SELECT '704' AS METRIC_ID,'% desc1' AS EN_METRIC_1_NM FROM DUAL UNION ALL 
    SELECT '705' AS METRIC_ID,'desc2' AS EN_METRIC_1_NM FROM DUAL UNION ALL 
    SELECT '706' AS METRIC_ID,'desc3' AS EN_METRIC_1_NM FROM DUAL UNION ALL
    SELECT '707' AS METRIC_ID,'desc5' AS EN_METRIC_1_NM FROM DUAL UNION ALL
    SELECT '701' AS METRIC_ID,'desc4' AS EN_METRIC_1_NM FROM DUAL
)
SELECT /*+ parallel(16) */
    col1,col2,'Monthly Snapshots' Time_Rollup,col3,date_pk,colr,col5,colr_DESC,col5_DESC,metric_id,EN_METRIC_1_NM,NULL METRIC_1_LY,NULL METRIC_2_LY,NULL METRIC_3_CY,NULL METRIC_3_LY
    ,sum (CASE WHEN (metric_id='704' AND record_identifier=17 ) THEN nvl ( record_count,0 ) ELSE 0 END ) AS METRIC_1_CY
    ,sum (CASE WHEN (metric_id='703' AND record_identifier=17) THEN nvl ( record_count,0 ) ELSE 0 END ) AS METRIC_2_CY 
FROM TXN partition (p01),IDQ_LKP
WHERE col1=2
GROUP BY  col1 , col2 , col3 , date_pk , colr , col5 , colr_DESC , col5_DESC, metric_id , EN_METRIC_1_NM
union all
SELECT /*+ parallel(16) */
    col1,col2,'Monthly Snapshots' Time_Rollup,col3,date_pk,colr,col5,colr_DESC,col5_DESC,metric_id,EN_METRIC_1_NM,NULL METRIC_1_LY,NULL METRIC_2_LY,NULL METRIC_3_CY,NULL METRIC_3_LY
    ,sum (CASE WHEN (metric_id='704' AND record_identifier=17 ) THEN nvl ( record_count,0 ) ELSE 0 END ) AS METRIC_1_CY
    ,sum (CASE WHEN (metric_id='703' AND record_identifier=17) THEN nvl ( record_count,0 ) ELSE 0 END ) AS METRIC_2_CY 
FROM TXN partition (p02),IDQ_LKP
WHERE col1=2
GROUP BY  col1 , col2 , col3 , date_pk , colr , col5 , colr_DESC , col5_DESC, metric_id , EN_METRIC_1_NM;


select * from table(dbms_xplan.display);

Plan hash value: 260310120

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |   253M|    14G|       |  3923K (51)| 00:02:34 |       |       |        |      |            |
|   1 |  TEMP TABLE TRANSFORMATION     |                           |       |       |       |            |          |       |       |        |      |            |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9D66A0_D66798 |       |       |       |            |          |       |       |        |      |            |
|   3 |    UNION-ALL                   |                           |       |       |       |            |          |       |       |        |      |            |
|   4 |     FAST DUAL                  |                           |     1 |       |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|   5 |     FAST DUAL                  |                           |     1 |       |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|   6 |     FAST DUAL                  |                           |     1 |       |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|   7 |     FAST DUAL                  |                           |     1 |       |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|   8 |     FAST DUAL                  |                           |     1 |       |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|   9 |     FAST DUAL                  |                           |     1 |       |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|  10 |     FAST DUAL                  |                           |     1 |       |       |     2   (0)| 00:00:01 |       |       |        |      |            |
|  11 |   UNION-ALL                    |                           |       |       |       |            |          |       |       |        |      |            |
|  12 |    PX COORDINATOR              |                           |       |       |       |            |          |       |       |        |      |            |
|  13 |     PX SEND QC (RANDOM)        | :TQ10002                  |   126M|  7362M|       |  1961K  (1)| 00:01:17 |       |       |  Q1,02 | P->S | QC (RAND)  |
|  14 |      HASH GROUP BY             |                           |   126M|  7362M|    10G|  1961K  (1)| 00:01:17 |       |       |  Q1,02 | PCWP |            |
|  15 |       PX RECEIVE               |                           |   126M|  7362M|       |  1961K  (1)| 00:01:17 |       |       |  Q1,02 | PCWP |            |
|  16 |        PX SEND HASH            | :TQ10001                  |   126M|  7362M|       |  1961K  (1)| 00:01:17 |       |       |  Q1,01 | P->P | HASH       |
|  17 |         HASH GROUP BY          |                           |   126M|  7362M|    10G|  1961K  (1)| 00:01:17 |       |       |  Q1,01 | PCWP |            |
|  18 |          MERGE JOIN CARTESIAN  |                           |   126M|  7362M|       | 19567   (1)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  19 |           PX RECEIVE           |                           |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  20 |            PX SEND BROADCAST   | :TQ10000                  |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |
|  21 |             VIEW               |                           |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  22 |              PX BLOCK ITERATOR |                           |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |
|  23 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D66A0_D66798 |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
|  24 |           BUFFER SORT          |                           |    18M|   879M|       |  1961K  (1)| 00:01:17 |       |       |  Q1,01 | PCWP |            |
|  25 |            PX BLOCK ITERATOR   |                           |    18M|   879M|       |    23  (92)| 00:00:01 |     1 |     1 |  Q1,01 | PCWC |            |
|* 26 |             TABLE ACCESS FULL  | TXN                       |    18M|   879M|       |    23  (92)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |
|  27 |    PX COORDINATOR              |                           |       |       |       |            |          |       |       |        |      |            |
|  28 |     PX SEND QC (RANDOM)        | :TQ20002                  |   126M|  7362M|       |  1961K  (1)| 00:01:17 |       |       |  Q2,02 | P->S | QC (RAND)  |
|  29 |      HASH GROUP BY             |                           |   126M|  7362M|    10G|  1961K  (1)| 00:01:17 |       |       |  Q2,02 | PCWP |            |
|  30 |       PX RECEIVE               |                           |   126M|  7362M|       |  1961K  (1)| 00:01:17 |       |       |  Q2,02 | PCWP |            |
|  31 |        PX SEND HASH            | :TQ20001                  |   126M|  7362M|       |  1961K  (1)| 00:01:17 |       |       |  Q2,01 | P->P | HASH       |
|  32 |         HASH GROUP BY          |                           |   126M|  7362M|    10G|  1961K  (1)| 00:01:17 |       |       |  Q2,01 | PCWP |            |
|  33 |          MERGE JOIN CARTESIAN  |                           |   126M|  7362M|       | 19567   (1)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
|  34 |           PX RECEIVE           |                           |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
|  35 |            PX SEND BROADCAST   | :TQ20000                  |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q2,00 | P->P | BROADCAST  |
|  36 |             VIEW               |                           |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
|  37 |              PX BLOCK ITERATOR |                           |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWC |            |
|  38 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D66A0_D66798 |     7 |    70 |       |     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
|  39 |           BUFFER SORT          |                           |    18M|   879M|       |  1961K  (1)| 00:01:17 |       |       |  Q2,01 | PCWP |            |
|  40 |            PX BLOCK ITERATOR   |                           |    18M|   879M|       |    23  (92)| 00:00:01 |     2 |     2 |  Q2,01 | PCWC |            |
|* 41 |             TABLE ACCESS FULL  | TXN                       |    18M|   879M|       |    23  (92)| 00:00:01 |     2 |     2 |  Q2,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  26 - filter("COL1"=2)
  41 - filter("COL1"=2)

Note
-----
   - Degree of Parallelism is 16 because of hint

Sory , I have to achieve this in pure SQL....its a restriction in our internal tool... — user3279189, Feb 07 '14 at 04:35
You can accomplish that with a `UNION ALL` of the 25 different queries. It would be a really huge query but I think it would be fast and use less tablespace. I'll try to post some more details tomorrow. — Jon Heller, Feb 07 '14 at 07:22
Hi Joanearles, Thanks.... I will try on my scenario and revert back to you... Follow-up question -- My original query was able to achieve partition pruning ... Is that equivalent to querying individual partitions in "UNION ALL" mode ? — user3279189, Feb 10 '14 at 05:06
Yes, I believe so. But partition pruning is not enough. To reduce temporary tablespace the partitions must be processed one at a time. Normally this can be achieved through partition-wise joins. But since this query joins to such a small table there's no easy way to achieve that. — Jon Heller, Feb 10 '14 at 05:21

Oracle SQL consumes huge temp space

4 Answers4