查看: 8319|回复: 22

10gSQL:union all+group by 如何优化?

[复制链接]
论坛徽章:
0
发表于 2010-4-20 13:55 | 显示全部楼层 |阅读模式
要优化的SQL:
   INSERT /*+ APPEND */ INTO  XXX.T_4   
   select USR, MIN(B), L_CD, C_CD, SUM(CA_CNT), SUM(CA_DUR)   
   from (                    
          SELECT A.USR, A.L_CD, A.C_CD, A.B, A.CA_CNT, A.CA_DUR         
          FROM XXX.TW_CE_USE_THREE_MO_201003 A         
          UNION ALL                       
          SELECT A.USR, A.L_CD, A.C_CD,A.B,
                nvl(A.DAY17_CA_CNT,0) AS CA_CNT,
                nvl(A.DAY17_CA_DUR,0) AS CA_DUR
          FROM XXX.TW_USR_CE_USE_DAY_201004 A
         ) TMP
  GROUP BY USR, L_CD, C_CD ;
  
表结构:三个表表结构都一样,如下.均分区表,均按USR列进行hash分区,分区数均为8.(分布在不同的表空间上,表空间本身已是跨磁盘的)
         USR          VARCHAR2(20) NOT NULL,
         B            SMALLINT,
         L_CD         VARCHAR2(4)  NOT NULL,
         C_CD         VARCHAR2(4)  NOT NULL,
         CA_CNT       INTEGER,
         CA_DUR       INTEGER

表数据量说明:
T_4                           0
TW_CE_USE_THREE_MO_201003     643947752 行   
TW_USR_CE_USE_DAY_201004        376937305 行.且该表每天会增加约2000万记录的日数据量.每月只存储当月的数据.
其它说明:该应用是在数据仓库中.考虑到业务应用就是要统计所有记录的数据,执行计划总是执行全表扫描.故表中均无索引.
每天在SQL运行前所有表均会用dbms_stats统计分析一次.但运行时间一般都超过1个小时.
请各位大侠赐教!希望能将该SQL的运行时间减小到10分钟以内.

下面是一些执行计划及利用STA工具获取的报告信息.供诊断问题参考.
========================================================================
执行计划如下:
explain plan for
   INSERT /*+ APPEND */ INTO  XXX.T_4   
   select USR, MIN(B), L_CD, C_CD, SUM(CA_CNT), SUM(CA_DUR)   
   from (                    
          SELECT A.USR, A.L_CD, A.C_CD, A.B, A.CA_CNT, A.CA_DUR         
          FROM XXX.TW_CE_USE_THREE_MO_201003 A         
          UNION ALL                       
          SELECT A.USR, A.L_CD, A.C_CD,A.B,
                nvl(A.DAY17_CA_CNT,0) AS CA_CNT,
                nvl(A.DAY17_CA_DUR,0) AS CA_DUR
          FROM XXX.TW_USR_CE_USE_DAY_201004 A
         ) TMP
  GROUP BY USR, L_CD, C_CD ;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2029564871
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |        |      |            |
|   1 |  PX COORDINATOR               |                             |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)         | :TQ10002                    |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,02 | P-〉S | QC (RAND)  |
|   3 |    LOAD AS SELECT             | T_4                         |       |       |            |          |       |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND RANDOM LOCAL     | :TQ10001                    |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,01 | P-〉P | RANDOM LOCA|
|   6 |       HASH GROUP BY           |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE             |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,01 | PCWP |            |
|   8 |         PX SEND HASH          | :TQ10000                    |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,00 | P-〉P | HASH       |
|   9 |          HASH GROUP BY        |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,00 | PCWP |            |
|  10 |           VIEW                |                             |  1427M|    78G| 46224   (6)| 00:13:53 |       |       |  Q1,00 | PCWP |            |
|  11 |            UNION-ALL          |                             |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  12 |             PX BLOCK ITERATOR |                             |  1095M|    31G| 25401   (4)| 00:07:38 |     1 |     8 |  Q1,00 | PCWC |            |
|  13 |              TABLE ACCESS FULL| TW_CE_USE_THREE_MO_201003 |  1095M|    31G| 25401   (4)| 00:07:38 |     1 |     8 |  Q1,00 | PCWP |            |
|  14 |             PX BLOCK ITERATOR |                             |   332M|  9191M| 20823   (9)| 00:06:15 |     1 |     8 |  Q1,00 | PCWC |            |
|  15 |              TABLE ACCESS FULL| TW_USR_CE_USE_DAY_201004   |   332M|  9191M| 20823   (9)| 00:06:15 |     1 |     8 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------
22 rows selected.
用dbms_sqltune辅助检查sql问题得到的报告如下:
set serveroutput on
--step1:
declare
a varchar2(32767);
begin
     a:=dbms_sqltune.CREATE_TUNING_TASK(SQL_TEXT=〉'
                                                   INSERT /*+ APPEND */ INTO  XXX.T_4   
                                                   select USR, MIN(B), L_CD, C_CD, SUM(CA_CNT), SUM(CA_DUR)   
                                                   from (                  
                                                          SELECT A.USR, A.L_CD, A.C_CD, A.B, A.CA_CNT, A.CA_DUR         
                                                          FROM XXX.TW_CE_USE_THREE_MO_201003 A         
                                                          UNION ALL                       
                                                          SELECT A.USR, A.L_CD, A.C_CD,A.B,
                                                                nvl(A.DAY17_CA_CNT,0) AS CA_CNT,
                                                                nvl(A.DAY17_CA_DUR,0) AS CA_DUR
                                                          FROM XXX.TW_USR_CE_USE_DAY_201004 A
                                                         ) TMP
                                                   GROUP BY USR, L_CD, C_CD
                                                  ',
                                        USER_NAME=〉'XXX',
                                        TASK_NAME=〉'STA_TMP4',
                                        DESCRIPTION=〉'test'
                                       );
      dbms_output.put_line(a);
end;
--step2:
exec dbms_sqltune.EXECUTE_TUNING_TASK('STA_TMP4');
--step3:
declare
b varchar2(32767);
begin
     b:=dbms_sqltune.REPORT_TUNING_TASK('STA_TMP4');
     dbms_output.put_line(b);
end;

PL/SQL procedure successfully completed.
Elapsed: 00:00:01.47
SQL〉 declare
  2  b varchar2(32767);
  3  begin
  4       b:=dbms_sqltune.REPORT_TUNING_TASK('STA_TMP4_1');
  5       dbms_output.put_line(b);
  6  end;
  7  /
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : STA_TMP4_1
Tuning Task Owner                 : XXX
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 04/19/2010 18:06:21
Completed at                      : 04/19/2010 18:06:22
-------------------------------------------------------------------------------
Schema Name: XXX
SQL ID     : c6z2r74b9x6z7
SQL Text   :                                                    INSERT /*+
             APPEND */ INTO  XXX.T_4
                                                                select
             USR, MIN(B), L_CD, C_CD, SUM(CA_CNT),
             SUM(CA_DUR)
                                                                from (
                                                                       SELECT
             A.USR, A.L_CD, A.C_CD, A.B, A.CA_CNT,
             A.CA_DUR
                                                                       FROM
             XXX.TW_CE_USE_THREE_MO_201003 A
                                                                       UNION
          ALL               
SELECT
             A.USR, A.L_CD, A.C_CD,A.B,                                                            
             nvl(A.DAY17_CA_CNT,0) AS CA_CNT,                                                         
             nvl(A.DAY17_CA_DUR,0) AS CA_DUR
                                                                       FROM
             XXX.TW_USR_CE_USE_DAY_201004 A
                                                                      ) TMP
                                                                GROUP BY
             USR, L_CD, C_CD
                                                               
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 10 of the execution plan.
  The optimizer cannot merge
a view that contains a set operator.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2029564871
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |        |      |            |
|   1 |  PX COORDINATOR               |                             |       |       |            |
|       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)         | :TQ10002                    |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,02 | P-〉S | QC (RAND)  |
|   3 |    LOAD AS SELECT             | T_4                         |       |       |            |          |       |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND RANDOM LOCAL     | :TQ10001                    |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,01 | P-〉P | RANDOM LOCA|
|   6 |       HASH GROUP BY           |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE             |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,01 | PCWP |            |
|   8 |         PX SEND HASH          | :TQ10000                    |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,00 | P-〉P | HASH       |
|   9 |          HASH GROUP BY        |                             |  1427M|    78G| 51978  (17)| 00:15:36 |       |       |  Q1,00 | PCWP |            |
|  10 |           VIEW                |                             |  1427M|    78G| 46224   (6)| 00:13:53 |       |       |  Q1,00 | PCWP |            |
|  11 |            UNION-ALL          |                             |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  12 |             PX BLOCK ITERATOR |                             |  1095M|    31G| 25401   (4)| 00:07:38 |     1 |     8 |  Q1,00 | PCWC |            |
|  13 |              TABLE ACCESS FULL| TW_CE_USE_THREE_MO_201003   |  1095M|    31G| 25401   (4)| 00:07:38 |     1 |     8 |  Q1,00 | PCWP |            |
|  14 |             PX BLOCK ITERATOR |                             |   332M|  9191M| 20823   (9)| 00:06:15 |     1 |     8 |  Q1,00 | PCWC |            |
|  15 |              TABLE ACCESS FULL| TW_USR_CE_USE_DAY_201004    |   332M|  9191M| 20823   (9)| 00:06:15 |     1 |     8 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------------------------
PL/SQL procedure successfully completed.
==========================================================================================================================================

[ 本帖最后由 swallow723 于 2010-4-20 14:09 编辑 ]
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-4-20 14:32 | 显示全部楼层
1、按照8个分区分8个sql做
2、group by 可以放里面一份试试
3、nvl就不需要了
4、结果集大概多少?

先试一个分区的看看需要多少时间

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-4-20 14:33 | 显示全部楼层
如果仅仅是这条sql,就不用收集统计信息了

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-4-20 14:51 | 显示全部楼层
lz把字体改成宋体吧

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-4-20 14:54 | 显示全部楼层
数据量太大,手工指定插入和查询并行度?
改成对结果集union all或group by?

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-4-20 15:19 | 显示全部楼层
看来我的建议不行


SQL> select c2,c3,c4,sum(v)sv from
  2  (select c2,c3,c4,sum(v)v from t group by c2,c3,c4
  3  union all
  4  select c2,c3,c4,sum(v)v from (select c2,c3,c4,v*2 v from t)group by c2,c3,c4)
  5  group by c2,c3,c4;

已选择934620行。

已用时间:  00: 00: 14.07

执行计划
----------------------------------------------------------
Plan hash value: 1828709681

--------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  1304K|    64M|       |  9716   (2)| 00:01:57 |
|   1 |  HASH GROUP BY        |      |  1304K|    64M|       |  9716   (2)| 00:01:57 |
|   2 |   VIEW                |      |  1304K|    64M|       |  9679   (1)| 00:01:57 |
|   3 |    UNION-ALL          |      |       |       |       |            |          |
|   4 |     HASH GROUP BY     |      |   652K|  9553K|    26M|  4839   (1)| 00:00:59 |
|   5 |      TABLE ACCESS FULL| T    |  1000K|    14M|       |   861   (1)| 00:00:11 |
|   6 |     HASH GROUP BY     |      |   652K|  9553K|    26M|  4839   (1)| 00:00:59 |
|   7 |      TABLE ACCESS FULL| T    |  1000K|    14M|       |   861   (1)| 00:00:11 |
--------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6180  consistent gets
          0  physical reads
          0  redo size
   26414260  bytes sent via SQL*Net to client
     685897  bytes received via SQL*Net from client
      62309  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     934620  rows processed

SQL> select c2,c3,c4,sum(v)sv from
  2  (select c2,c3,c4,v from t
  3  union all
  4  select c2,c3,c4,v*2 v from t)
  5  group by c2,c3,c4;

已选择934620行。

已用时间:  00: 00: 12.90

执行计划
----------------------------------------------------------
Plan hash value: 2440692321

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  2000K|    99M|  1780   (5)| 00:00:22 |
|   1 |  HASH GROUP BY       |      |  2000K|    99M|  1780   (5)| 00:00:22 |
|   2 |   VIEW               |      |  2000K|    99M|  1721   (1)| 00:00:21 |
|   3 |    UNION-ALL         |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T    |  1000K|    14M|   861   (1)| 00:00:11 |
|   5 |     TABLE ACCESS FULL| T    |  1000K|    14M|   861   (1)| 00:00:11 |
-----------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6180  consistent gets
          0  physical reads
          0  redo size
   26414260  bytes sent via SQL*Net to client
     685897  bytes received via SQL*Net from client
      62309  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     934620  rows processed

使用道具 举报

回复
招聘 : Java研发
认证徽章
论坛徽章:
71
马上加薪
日期:2014-02-19 11:55:14蜘蛛蛋
日期:2012-12-26 18:16:01茶鸡蛋
日期:2012-11-16 08:12:48ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07奥运会纪念徽章:网球
日期:2012-08-23 14:58:08奥运会纪念徽章:沙滩排球
日期:2012-07-19 17:28:14版主2段
日期:2012-07-07 02:21:02咸鸭蛋
日期:2012-03-23 18:17:482012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2010-4-20 15:35 | 显示全部楼层
楼主可以将下面sql中的表改成具体的分区执行试一下,跟OO一样输出执行结果:
select USR, MIN(B), L_CD, C_CD, SUM(CA_CNT), SUM(CA_DUR)
  from (SELECT A.USR, A.L_CD, A.C_CD, min(A.B), sum(A.CA_CNT), sum(A.CA_DUR)
          FROM XXX.TW_CE_USE_THREE_MO_201003 A
           GROUP BY USR, L_CD, C_CD
        UNION ALL
        SELECT A.USR, A.L_CD,A.C_CD,min(A.B),sum(A.DAY17_CA_CNT),sum(A.DAY17_CA_DUR)
          FROM XXX.TW_USR_CE_USE_DAY_201004 A
           GROUP BY USR, L_CD, C_CD
           ) TMP
GROUP BY USR, L_CD, C_CD;

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-4-20 15:46 | 显示全部楼层

回复 #7 anlinew 的帖子

set timi on lines 132 pages 50000 autot traceonly

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-4-20 15:57 | 显示全部楼层
并行更糟,也可能我的机器差

SQL> set timi on lines 132 pages 50000 autot traceonly
SQL> select /*+parallel(t,2)*/c2,c3,c4,sum(v)sv from
  2  (select c2,c3,c4,v from t
  3  union all
  4  select c2,c3,c4,v*2 v from t)
  5  group by c2,c3,c4;

已选择934620行。

已用时间:  00: 00: 13.63

执行计划
----------------------------------------------------------
Plan hash value: 2440692321

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  2000K|    99M|  1780   (5)| 00:00:22 |
|   1 |  HASH GROUP BY       |      |  2000K|    99M|  1780   (5)| 00:00:22 |
|   2 |   VIEW               |      |  2000K|    99M|  1721   (1)| 00:00:21 |
|   3 |    UNION-ALL         |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T    |  1000K|    14M|   861   (1)| 00:00:11 |
|   5 |     TABLE ACCESS FULL| T    |  1000K|    14M|   861   (1)| 00:00:11 |
-----------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6180  consistent gets
          0  physical reads
          0  redo size
   26414260  bytes sent via SQL*Net to client
     685897  bytes received via SQL*Net from client
      62309  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     934620  rows processed

SQL> select c2,c3,c4,sum(v)sv from
  2  (select /*+parallel(t,2)*/c2,c3,c4,v from t
  3  union all
  4  select /*+parallel(t,2)*/c2,c3,c4,v*2 v from t)
  5  group by c2,c3,c4;

已选择934620行。

已用时间:  00: 00: 18.95

执行计划
----------------------------------------------------------
Plan hash value: 1709333228

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |  2000K|    99M|   987   (4)| 00:00:12 |        |      |            |
|   1 |  PX COORDINATOR            |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10001 |  2000K|    99M|   987   (4)| 00:00:12 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY           |          |  2000K|    99M|   987   (4)| 00:00:12 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE             |          |  2000K|    99M|   987   (4)| 00:00:12 |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH          | :TQ10000 |  2000K|    99M|   987   (4)| 00:00:12 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY        |          |  2000K|    99M|   987   (4)| 00:00:12 |  Q1,00 | PCWP |            |
|   7 |        VIEW                |          |  2000K|    99M|   956   (1)| 00:00:12 |  Q1,00 | PCWP |            |
|   8 |         UNION-ALL          |          |       |       |            |          |  Q1,00 | PCWP |            |
|   9 |          PX BLOCK ITERATOR |          |  1000K|    14M|   478   (1)| 00:00:06 |  Q1,00 | PCWC |            |
|  10 |           TABLE ACCESS FULL| T        |  1000K|    14M|   478   (1)| 00:00:06 |  Q1,00 | PCWP |            |
|  11 |          PX BLOCK ITERATOR |          |  1000K|    14M|   478   (1)| 00:00:06 |  Q1,00 | PCWC |            |
|  12 |           TABLE ACCESS FULL| T        |  1000K|    14M|   478   (1)| 00:00:06 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
        133  recursive calls
        172  db block gets
       6515  consistent gets
      17228  physical reads
        628  redo size
   26414260  bytes sent via SQL*Net to client
     685897  bytes received via SQL*Net from client
      62309  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     934620  rows processed

SQL> select c2,c3,c4,sum(v)sv from
  2  (select /*+parallel(t,2)*/c2,c3,c4,sum(v)v from t group by c2,c3,c4
  3  union all
  4  select /*+parallel(t,2)*/c2,c3,c4,sum(v*2)v from t group by c2,c3,c4)
  5  group by c2,c3,c4;

已选择934620行。

已用时间:  00: 00: 25.84

执行计划
----------------------------------------------------------
Plan hash value: 1482835795

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |  1304K|    64M|       |  6108   (2)| 00:01:14 |     |         |            |
|   1 |  PX COORDINATOR               |          |       |       |       |            |          |     |         |            |
|   2 |   PX SEND QC (RANDOM)         | :TQ10003 |  1304K|    64M|       |  6108   (2)| 00:01:14 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY              |          |  1304K|    64M|       |  6108   (2)| 00:01:14 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                |          |  1304K|    64M|       |  6108   (2)| 00:01:14 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH             | :TQ10002 |  1304K|    64M|       |  6108   (2)| 00:01:14 |  Q1,02 | P->P | HASH       |
|   6 |       HASH GROUP BY           |          |  1304K|    64M|       |  6108   (2)| 00:01:14 |  Q1,02 | PCWP |            |
|   7 |        VIEW                   |          |  1304K|    64M|       |  6087   (1)| 00:01:14 |  Q1,02 | PCWP |            |
|   8 |         UNION-ALL             |          |       |       |       |            |          |  Q1,02 | PCWP |            |
|   9 |          HASH GROUP BY        |          |   652K|  9553K|    26M|  3044   (1)| 00:00:37 |  Q1,02 | PCWP |            |
|  10 |           PX RECEIVE          |          |  1000K|    14M|       |   478   (1)| 00:00:06 |  Q1,02 | PCWP |            |
|  11 |            PX SEND HASH       | :TQ10000 |  1000K|    14M|       |   478   (1)| 00:00:06 |  Q1,00 | P->P | HASH       |
|  12 |             PX BLOCK ITERATOR |          |  1000K|    14M|       |   478   (1)| 00:00:06 |  Q1,00 | PCWC |            |
|  13 |              TABLE ACCESS FULL| T        |  1000K|    14M|       |   478   (1)| 00:00:06 |  Q1,00 | PCWP |            |
|  14 |          HASH GROUP BY        |          |   652K|  9553K|    26M|  3044   (1)| 00:00:37 |  Q1,02 | PCWP |            |
|  15 |           PX RECEIVE          |          |  1000K|    14M|       |   478   (1)| 00:00:06 |  Q1,02 | PCWP |            |
|  16 |            PX SEND HASH       | :TQ10001 |  1000K|    14M|       |   478   (1)| 00:00:06 |  Q1,01 | P->P | HASH       |
|  17 |             PX BLOCK ITERATOR |          |  1000K|    14M|       |   478   (1)| 00:00:06 |  Q1,01 | PCWC |            |
|  18 |              TABLE ACCESS FULL| T        |  1000K|    14M|       |   478   (1)| 00:00:06 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
        161  recursive calls
         73  db block gets
       6513  consistent gets
      22032  physical reads
        668  redo size
   26414260  bytes sent via SQL*Net to client
     685897  bytes received via SQL*Net from client
      62309  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     934620  rows processed

SQL>

使用道具 举报

回复
认证徽章
论坛徽章:
131
2006年度最佳技术回答
日期:2007-01-24 12:58:48福特
日期:2013-10-24 13:57:422014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:142013年新春福章
日期:2013-02-25 14:51:24
发表于 2010-4-20 17:15 | 显示全部楼层
看看v$session_event, 在运行时该session等待什么事件?
10亿条纪录本来就很难快起来的。

考虑设置大的pga, multiblock_read_count
alter session set db_file_multiblock_read_count=256;
alter session set workarea_size_policy=manual;
alter session set hash_area_size=2000000000;
alter session set sort_area_size=2000000000;
然后根据CPU数设置最大并行度
INSERT /*+ APPEND */ INTO  XXX.T_4   
   select USR, MIN(B), L_CD, C_CD, SUM(CA_CNT), SUM(CA_DUR)   
   from (                    
          SELECT /*+parallel(a,16) */  A.USR, A.L_CD, A.C_CD, A.B, A.CA_CNT, A.CA_DUR         
          FROM XXX.TW_CE_USE_THREE_MO_201003 A         
          UNION ALL                       
          SELECT /*+parallel(a,16) */ A.USR, A.L_CD, A.C_CD,A.B,
                nvl(A.DAY17_CA_CNT,0) AS CA_CNT,
                nvl(A.DAY17_CA_DUR,0) AS CA_DUR
          FROM XXX.TW_USR_CE_USE_DAY_201004 A
         ) TMP
  GROUP BY USR, L_CD, C_CD ;

当然,先单table group 后再一起group也是个不错的选择

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表