0

I want to aggregate data from table1 in all possible combinations of the 6 columns. Table1 has 60Million rows in it. The query is running for more than an hour and still not fetching any data. Need some help in tuning this

select /*+ parallel(a,8)*/
    event_date,
         a.COL1,
         COL2,
         COL3,
         COL4,
         COL5,
         COL6,
         count (distinct id) base_count,
         count (distinct case when active_flg = 'Y' then id end) unique_active_cnt,
         count (case when active_flg = 'Y' then id end) active_cnt,
         GROUPING_ID (a.COL1,
                      COL2,
                      COL3,
                      COL4,
                      COL5,
                      COL6)
            AS grp_id
    from table1 partition (P2017_01) a
group by event_date,
         cube (a.COL1,
               COL2,
               COL3,
               COL4,
               COL5,
               COL6);

Explain plan for the above sql:-

Explain complete.

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3317440810                                                                                                                                                                                                                                                                                 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                 
| Id  | Operation                              | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |                                                                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                 
|   0 | SELECT STATEMENT                       |                             |   392K|    81M|       | 96682   (4)| 00:00:08 |       |       |        |      |            |                                                                                                                                 
|   1 |  TEMP TABLE TRANSFORMATION             |                             |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|   2 |   PX COORDINATOR                       |                             |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|   3 |    PX SEND QC (RANDOM)                 | :TQ10001                    |   392K|    20M|       |  1157   (5)| 00:00:01 |       |       |  Q1,01 | P->S | QC (RAND)  |                                                                                                                                 
|   4 |     LOAD AS SELECT (TEMP SEGMENT MERGE)| SYS_TEMP_0FDA4B738_9AA210CA |       |       |       |            |          |       |       |  Q1,01 | PCWP |            |                                                                                                                                 
|   5 |      SORT GROUP BY ROLLUP              |                             |   392K|    20M|   293M|  1157   (5)| 00:00:01 |       |       |  Q1,01 | PCWP |            |                                                                                                                                 
|   6 |       PX RECEIVE                       |                             |  4040K|   208M|       |  1121   (2)| 00:00:01 |       |       |  Q1,01 | PCWP |            |                                                                                                                                 
|   7 |        PX SEND HASH                    | :TQ10000                    |  4040K|   208M|       |  1121   (2)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |                                                                                                                                 
|   8 |         PX BLOCK ITERATOR              |                             |  4040K|   208M|       |  1121   (2)| 00:00:01 |    25 |    25 |  Q1,00 | PCWC |            |                                                                                                                                 
|   9 |          TABLE ACCESS FULL             | TABLE1                      |  4040K|   208M|       |  1121   (2)| 00:00:01 |    25 |    25 |  Q1,00 | PCWP |            |                                                                                                                                 
|  10 |   LOAD AS SELECT                       | SYS_TEMP_0FDA4B738_9AA210CA |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|  11 |    PARTITION RANGE SINGLE              |                             |   185K|  9224K|       | 27366   (2)| 00:00:03 |    25 |    25 |        |      |            |                                                                                                                                 
|  12 |     SORT GROUP BY ROLLUP               |                             |   185K|  9224K|   278M| 27366   (2)| 00:00:03 |       |       |        |      |            |                                                                                                                                 
|  13 |      TABLE ACCESS FULL                 | TABLE1                      |  4040K|   196M|       |  8081   (2)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  14 |   LOAD AS SELECT                       | SYS_TEMP_0FDA4B738_9AA210CA |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|  15 |    PARTITION RANGE SINGLE              |                             | 32686 |  1595K|       | 26191   (2)| 00:00:03 |    25 |    25 |        |      |            |                                                                                                                                 
|  16 |     SORT GROUP BY ROLLUP               |                             | 32686 |  1595K|   278M| 26191   (2)| 00:00:03 |       |       |        |      |            |                                                                                                                                 
|  17 |      TABLE ACCESS FULL                 | TABLE1                      |  4040K|   192M|       |  8081   (2)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  18 |   LOAD AS SELECT                       | SYS_TEMP_0FDA4B738_9AA210CA |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|  19 |    PARTITION RANGE SINGLE              |                             |    73 |  2336 |       |  8374   (5)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  20 |     SORT GROUP BY ROLLUP               |                             |    73 |  2336 |       |  8374   (5)| 00:00:01 |       |       |        |      |            |                                                                                                                                 
|  21 |      TABLE ACCESS FULL                 | TABLE1                      |  4040K|   123M|       |  8081   (2)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  22 |   LOAD AS SELECT                       | SYS_TEMP_0FDA4B738_9AA210CA |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|  23 |    PARTITION RANGE SINGLE              |                             | 15408 |   707K|       |  8374   (5)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  24 |     SORT GROUP BY ROLLUP               |                             | 15408 |   707K|       |  8374   (5)| 00:00:01 |       |       |        |      |            |                                                                                                                                 
|  25 |      TABLE ACCESS FULL                 | TABLE1                      |  4040K|   181M|       |  8081   (2)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  26 |   LOAD AS SELECT                       | SYS_TEMP_0FDA4B738_9AA210CA |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|  27 |    PARTITION RANGE SINGLE              |                             |    34 |   986 |       |  8374   (5)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  28 |     SORT GROUP BY ROLLUP               |                             |    34 |   986 |       |  8374   (5)| 00:00:01 |       |       |        |      |            |                                                                                                                                 
|  29 |      TABLE ACCESS FULL                 | TABLE1                      |  4040K|   111M|       |  8081   (2)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  30 |   LOAD AS SELECT                       | SYS_TEMP_0FDA4B738_9AA210CA |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|  31 |    PARTITION RANGE SINGLE              |                             |     6 |   168 |       |  8374   (5)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  32 |     SORT GROUP BY ROLLUP               |                             |     6 |   168 |       |  8374   (5)| 00:00:01 |       |       |        |      |            |                                                                                                                                 
|  33 |      TABLE ACCESS FULL                 | TABLE1                      |  4040K|   107M|       |  8081   (2)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  34 |   LOAD AS SELECT                       | SYS_TEMP_0FDA4B738_9AA210CA |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|  35 |    PARTITION RANGE SINGLE              |                             |     3 |    75 |       |  8374   (5)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  36 |     SORT GROUP BY ROLLUP               |                             |     3 |    75 |       |  8374   (5)| 00:00:01 |       |       |        |      |            |                                                                                                                                 
|  37 |      TABLE ACCESS FULL                 | TABLE1                      |  4040K|    96M|       |  8081   (2)| 00:00:01 |    25 |    25 |        |      |            |                                                                                                                                 
|  38 |   PX COORDINATOR                       |                             |       |       |       |            |          |       |       |        |      |            |                                                                                                                                 
|  39 |    PX SEND QC (RANDOM)                 | :TQ20000                    |   392K|    81M|       |    97   (3)| 00:00:01 |       |       |  Q2,00 | P->S | QC (RAND)  |                                                                                                                                 
|  40 |     VIEW                               |                             |   392K|    81M|       |    97   (3)| 00:00:01 |       |       |  Q2,00 | PCWP |            |                                                                                                                                 
|  41 |      PX BLOCK ITERATOR                 |                             |   392K|    20M|       |    97   (3)| 00:00:01 |       |       |  Q2,00 | PCWC |            |                                                                                                                                 
|  42 |       TABLE ACCESS FULL                | SYS_TEMP_0FDA4B738_9AA210CA |   392K|    20M|       |    97   (3)| 00:00:01 |       |       |  Q2,00 | PCWP |            |                                                                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                 

Note                                                                                                                                                                                                                                                                                                        
-----                                                                                                                                                                                                                                                                                                       
   - Degree of Parallelism is 8 because of table property                                                                                                                                                                                                                                                   

53 rows selected.
BobC
  • 4,208
  • 1
  • 12
  • 15
Soumya7oct
  • 13
  • 5
  • Can you share the SQL Monitor report. – BobC Feb 08 '17 at 13:38
  • Sorry BobC we do not have access to that here – Soumya7oct Feb 08 '17 at 14:17
  • OK. Can you at least get the execution plan. Can you run: select * from table( dbms_xplan.display_cursor( null, null, 'TYPICAL' ) ) – BobC Feb 08 '17 at 14:19
  • @BobC I have added the explain plan. – Soumya7oct Feb 08 '17 at 15:56
  • Which database version is this? Can you also try (or at least do the explain plan) without the two COUNT( DISTINCT's .. ) – BobC Feb 08 '17 at 16:57
  • @BobC We are using "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production". I removed the COUNT(DISTINCT's..) and run the query again. It got finished in 01:18min. So its the COUNT(DISTINCT's..) that is causing the pain. But I need the distinct counts as well, how can I achieve that? – Soumya7oct Feb 09 '17 at 07:43
  • Can you try using the APPROX_COUNT_DISTINCT – BobC Feb 09 '17 at 08:13
  • @BobC But I require exact counts not any approximation of that – Soumya7oct Feb 09 '17 at 09:12
  • I want to see if the Approx will perform faster. – BobC Feb 09 '17 at 09:15
  • @BobC: Yes APPROX_COUNT_DISTINCT did perform faster. It took ~12mins to complete. But as I was telling I would be requiring exact DISTINCT COUNTs here. Can that be achieved somehow? – Soumya7oct Feb 09 '17 at 10:49
  • Do you really need a cube? With 6 columns, that is 64 combinations. The other option you have, would be to re-write the query, which might give you better performance. – BobC Feb 10 '17 at 02:47
  • @BobC Yes... I do require 64 combinations of the 6 columns.... How do you propose to rewrite the query? – Soumya7oct Feb 11 '17 at 13:54
  • You would need to write that would essentially be each dimension you need to group by and then UNION ALL them. If you need an example of how to do that, let me know. – BobC Feb 11 '17 at 16:19
  • I did try that also.... 64 different GROUP BYs combined with UNION ALLs.... but still it is not fetching data after running for more than 1hour – Soumya7oct Feb 11 '17 at 17:22
  • Can you determine which ones are fast and which are slow? Can you get a SQL Monitor report? – BobC Feb 12 '17 at 03:13

0 Answers0