logo标签列表

Oracle SQL消耗大量临时空间

performanceoraclespacetemp
3

我在临时空间和查询性能之间面临着一个权衡问题。

我的情况是这样的：我有一个巨大的交易表（25个分区，200亿条记录）和一个只有7条记录的小指标查找表。我需要为每个指标记录处理每个交易记录。本质上输出将是7 * 200亿条记录。这个输出将根据5-6列进行聚合。

我考虑了两种选择：

  1. 交叉联接这两个表，并使用“case when”指定与指标相关的处理逻辑，并执行“group by”操作。

  2. 对于每个指标ID都有七个不同的查询，并“UNION ALL”结果。

选项1消耗了大约250 GB的临时空间，选项2运行大约230分钟。

有没有办法优化其中一种选择？我需要在60分钟内完成此查询。

添加查询

@david 查询如下所示

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

你好，

在执行计划中仍需要252GB的临时空间...

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

现在正在运行...我怀疑它可能会陷入同样的问题...

计划采用“UNION ALL”方法。

-------------------------------------------------------------------------------------------------------------------------------------------
| 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 |            |
-------------------------------------------------------------------------------------------------------------------------------------------
1
在230分钟的运行时间中，有多少时间用于读写临时空间？表本身占用了多少空间？表的分区键列是否是分组列之一？排序是最优、单次还是多次通过？预计从分组操作中总共会有多少行？ - David Aldridge
表本身占用了多少空间？18 GB。是的，表的分区键也用于聚合，在分组后，我预计会接近70亿条记录......如何确定排序是否最优？ - user3279189
我很想知道查询是否在分区级别上运行聚合 - 请展示查询和查询的解释计划，以及表的DDL。您可以使用V $ SQL_WORKAREA或V $ SQL_WORKAREA_ACTIVE监视临时空间操作的使用情况。 - David Aldridge
metric_id 可以保留为数字...没有特定的原因... - user3279189
@DavidAldridge 我已经添加了两个计划..... - user3279189
显示剩余6条评论
4个回答
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;
谢谢...我正在测试它...我会回复的。 - user3279189
我是这个论坛的新手，有没有办法让我格式化并粘贴查询/计划？ - user3279189
我尝试了这个，但它失败了，因为它消耗的临时空间超过了150 GB。 - user3279189
很遗憾听到这个消息。150 GB比250 GB好，不是吗 :-) 看来交叉连接太昂贵了。你试过使用分开的语句和union all进行预聚合技术吗？ - Thorsten Kettner
1

仅从查询中看，我会考虑尝试更改实现方法：

  1. 将大表连接到小表
  2. 计算指标值
  3. 聚合到所需级别

... 改为 ...

  1. 将大表聚合到所需级别（如果可能的话应用过滤器以删除不需要的行）。
  2. 连接到小表
  3. 计算指标
  4. 可能再次进行聚合

这应该需要一个较小的临时数据集。

1
获取两种方法的执行计划。 - David Aldridge
我已经粘贴了新方法的说明，请看一下并提出建议...提前感谢您... - user3279189
三个分组操作？ - David Aldridge
如果我使用全局临时表，这会有帮助吗？ - user3279189
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语句，但仍应该表现良好且使用更少的临时表空间。

基本思路是：

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

这是原始查询，经过一些美化修改使其更小。它仍然包含相同的列、条件和分组。请注意，优化器估计将使用262GB的临时表空间，并且执行与您的相当相似。

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

将单个txn，idq_lkp替换为多个txn partition (pXX)，idq_lkp可将最大临时表空间估计值从262G减少到10G。此示例仅包含25个分区中的2个，但添加更多分区不会增加所需的临时表空间。

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
抱歉，我必须在纯SQL中实现这个功能...这是我们内部工具的限制。 - user3279189
你可以通过将25个不同的查询使用UNION ALL组合来实现。这将生成一个非常庞大的查询，但我认为它会更快速并且占用更少的表空间。我明天会尝试发布更多详细信息。 - Jon Heller
嗨Joanearles，谢谢......我会在我的场景中尝试并回复您......跟进问题 - 我原来的查询能够实现分区修剪......这是否相当于在“UNION ALL”模式下查询单个分区？ - user3279189
是的，我相信如此。但仅仅进行分区剪枝是不够的。为了减少临时表空间，必须逐个处理分区。通常可以通过分区连接来实现这一点。但由于此查询连接到一个非常小的表，因此没有简单的方法来实现这一点。 - Jon Heller
0

看起来你可以在不同的CPU上并行运行选项二的七个单独查询。也许设置某种视图来解决需要联合结果的需求？

它已经在并行运行了...这是一个提取任务...所以视图无法帮助 - user3279189
是的，我提交后看到了Oracle标签。因为只有七个线程，所以想到你的硬件可能是有能力的。 - unigeek
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接