Geek asks and answers

Oracle SQL consumes huge temporary space

I ran into a trade-off between temporary space and query performance.

My scenario is as follows: I have a huge transaction table (20 billion records in 25 sections) and a small indicator table with 7 records. I need to process each transaction record for each metric record. Essentially, the output will be 7 * 20 billion records. This output should be aggregated based on 5-6 columns.

I considered two options:

  • Cross these two tables and specify the processing logic by metric using "case when" and perform the operation "group by".

  • You have seven different queries for each metric identifier and "UNION ALL".

# 1 consumes a huge temporary space of about 250 GB and # 2 works for about 230 minutes.

Is there a way I can optimize one of these options? I need this request to be completed in 60 minutes.

Adding a Request

@ david request inserted 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 of need for temporary space in terms of explanation ...

-------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |            |
---------------------------------------------------------------------------------------------------------

Now it works ... I doubt that it can get stuck in the same problem ...

Plan the 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 |            |
-------------------------------------------------------------------------------------------------------------------------------------------
+3
source share
4 answers

Just looking at the query, one of the approaches that I would try to do is to switch from this implementation method:

  • Attach a large table to a small table
  • Calculate the value of the indicator
  • Set of required level

... to that...

  • ( , , ).

temp.

+1

- . , : , . . , , . . .

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;
+1

, , . , - ?

0

. , .

. , 25 . , , . .

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;
/

- ,

SQL. , SQL- helluva.

:

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

, :

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 request, with a few cosmetic modifications to make it smaller. It still contains the same columns, conditions, and groupings. Note that the optimizer estimates that it will use 262 GB of temporary table space, 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 a single txn,idq_lkpwith a few txn partition (pXX),idq_lkpreduces the maximum temporary rating of a table space from 262G to 10G. This example contains only 2 of 25 partitions, but adding more partitions does not increase the required temporary table space.

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
0
source

More articles:


All Articles