0 Replies Latest reply: Oct 18, 2012 9:05 AM by 967489 RSS

    FACT table parallelism setting

    967489
      I'm not sure if this is the correct forum but I have been trying to do some performance tuning on our data warehouse due to issues with long running queries. Our FACT table contains 207 million rows. Table contains a composite partition (FISCAL YEAR/FISCAL MONTH) and all dimension keys have local BITMAP indexes. The degree of parallelism on table and indexes is set to 4. We have database parameter STAR_TRANSFORMATION_ENABLED set to TRUE.
      Queries run against this fact table when querying data for a FISCAL_YEAR take 30+ minutes sometimes 2+ hours to rerturn results. A normal query is aggregating dollar amounts which includes a group by statement.

      For test purposes, I have created a test FACT table containing 2 fiscal year's worth of data and created the same partitions and indexes. I have adjusted the parallelism for this table down to 2. My test FACT table contains approximately 27 million rows. I use explain plan to view the execution plan and the results from EXPLAIN PLAN uses the STAR TRANSFORMATION but when I actually execute the statement, the execution plan for the running query is something completely different. Can anyone tell me why this would occur. I am using the same user and instance.

      Here's my example.

      set autotrace on exp
      set pagesize 0
      set timing on

      EXPLAIN PLAN SET STATEMENT_ID = 'swcname'
      into schittam.plan_table FOR
      select sn.swc_owner, sn.swc_name, bep.compl_program_code, bep.program_name,
      SUM(program_budget_auth_amount) program_budget_auth_amount,
      sum(credit_amount) - sum(debit_amount) allotment_amount
      from dw_ice.dim_swc_name sn,
      dw_ice.dim_swc_program_bep bep,
      dw_frr.dim_account_month am,
      dw_frr.dim_account a,
      dw_frr.dim_fiscal_year fy,
      dw_ice.fact_gltransdtl_fy12
      where swc_name_sid_fk = swc_name_sid_pk
      and swc_program_bep_sid_fk = swc_program_bep_sid_pk
      and fiscal_year_sid_fk = fiscal_year_sid_pk
      and fy.fiscal_year = '2012'
      and account_month_sid_fk = account_month_sid_pk
      and account_month_label = 'DEC' -----in ('OCT','NOV','DEC','JAN','FEB','MAR','APR','MAY','JUN','JUL')
      and account_sid_fk = account_sid_pk
      and a.account_nbr = '4610'
      and transaction_source_code = 'ALLOT'
      group by sn.swc_owner, sn.swc_name, bep.compl_program_code, bep.program_name;

      set linesize 132
      SELECT * FROM TABLE(dbms_xplan.display);

      set autotrace on exp
      set pagesize 0
      set timing on

      EXPLAIN PLAN SET STATEMENT_ID = 'swcname'
      into schittam.plan_table FOR
      select sn.swc_owner, sn.swc_name, bep.compl_program_code, bep.program_name,
      SUM(program_budget_auth_amount) program_budget_auth_amount,
      sum(credit_amount) - sum(debit_amount) allotment_amount
      from dw_ice.dim_swc_name sn,
      dw_ice.dim_swc_program_bep bep,
      dw_frr.dim_account_month am,
      dw_frr.dim_account a,
      dw_frr.dim_fiscal_year fy,
      dw_ice.fact_gltransdtl_fy12
      where swc_name_sid_fk = swc_name_sid_pk
      and swc_program_bep_sid_fk = swc_program_bep_sid_pk
      and fiscal_year_sid_fk = fiscal_year_sid_pk
      and fy.fiscal_year = '2012'
      and account_month_sid_fk = account_month_sid_pk
      and account_month_label = 'DEC' -----in ('OCT','NOV','DEC','JAN','FEB','MAR','APR','MAY','JUN','JUL')
      and account_sid_fk = account_sid_pk
      and a.account_nbr = '4610'
      and transaction_source_code = 'ALLOT'
      group by sn.swc_owner, sn.swc_name, bep.compl_program_code, bep.program_name;

      set linesize 132
      SELECT * FROM TABLE(dbms_xplan.display);

      Row#     PLAN_TABLE_OUTPUT

      1     Plan hash value: 1729805786
      2     
      3     ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      4     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
      5     ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      6     | 0 | SELECT STATEMENT | | 119 | 21777 | 1371 (1)| 00:00:20 | | | | | |
      7     | 1 | PX COORDINATOR | | | | | | | | | | |
      8     | 2 | PX SEND QC (RANDOM) | :TQ10004 | 119 | 21777 | 1371 (1)| 00:00:20 | | | Q1,04 | P->S | QC (RAND) |
      9     | 3 | HASH GROUP BY | | 119 | 21777 | 1371 (1)| 00:00:20 | | | Q1,04 | PCWP | |
      10     | 4 | PX RECEIVE | | 119 | 21777 | 1370 (1)| 00:00:20 | | | Q1,04 | PCWP | |
      11     | 5 | PX SEND HASH | :TQ10003 | 119 | 21777 | 1370 (1)| 00:00:20 | | | Q1,03 | P->P | HASH |
      12     |* 6 | HASH JOIN | | 119 | 21777 | 1370 (1)| 00:00:20 | | | Q1,03 | PCWP | |
      13     | 7 | PX RECEIVE | | 119 | 17969 | 1367 (1)| 00:00:20 | | | Q1,03 | PCWP | |
      14     | 8 | PX SEND BROADCAST | :TQ10002 | 119 | 17969 | 1367 (1)| 00:00:20 | | | Q1,02 | P->P | BROADCAST |
      15     |* 9 | HASH JOIN | | 119 | 17969 | 1367 (1)| 00:00:20 | | | Q1,02 | PCWP | |
      16     |* 10 | HASH JOIN | | 119 | 14637 | 1364 (1)| 00:00:20 | | | Q1,02 | PCWP | |
      17     | 11 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
      18     | 12 | PX RECEIVE | | 1 | 8 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
      19     | 13 | PX SEND BROADCAST | :TQ10000 | 1 | 8 | 2 (0)| 00:00:01 | | | | S->P | BROADCAST |
      20     | 14 | TABLE ACCESS BY INDEX ROWID | DIM_FISCAL_YEAR | 1 | 8 | 2 (0)| 00:00:01 | | | | | |
      21     |* 15 | INDEX RANGE SCAN | UNQ_DIM_FISCAL_YEAR_IDX | 1 | | 1 (0)| 00:00:01 | | | | | |
      22     | 16 | PX PARTITION LIST SUBQUERY | | 238 | 27370 | 1361 (0)| 00:00:20 |KEY(SQ)|KEY(SQ)| Q1,02 | PCWC | |
      23     |* 17 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_GLTRANSDTL_FY12 | 238 | 27370 | 1361 (0)| 00:00:20 | KEY | KEY | Q1,02 | PCWP | |
      24     | 18 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q1,02 | PCWP | |
      25     | 19 | BITMAP AND | | | | | | | | Q1,02 | PCWP | |
      26     | 20 | BITMAP MERGE | | | | | | | | Q1,02 | PCWP | |
      27     | 21 | BITMAP KEY ITERATION | | | | | | | | Q1,02 | PCWP | |
      28     | 22 | BUFFER SORT | | | | | | | | Q1,02 | PCWP | |
      29     |* 23 | TABLE ACCESS FULL | DIM_ACCOUNT | 1 | 9 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
      30     |* 24 | BITMAP INDEX RANGE SCAN | FK_ACCOUNT_GLTRANSFY12_IDX | | | | | KEY | KEY | Q1,02 | PCWP | |
      31     | 25 | BITMAP MERGE | | | | | | | | Q1,02 | PCWP | |
      32     | 26 | BITMAP KEY ITERATION | | | | | | | | Q1,02 | PCWP | |
      33     | 27 | BUFFER SORT | | | | | | | | Q1,02 | PCWP | |
      34     |* 28 | TABLE ACCESS FULL | DIM_ACCOUNT_MONTH | 1 | 8 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
      35     |* 29 | BITMAP INDEX RANGE SCAN | FK_ACCOUNTMTH_GLTRANSFY12_IDX | | | | | KEY | KEY | Q1,02 | PCWP | |
      36     | 30 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
      37     | 31 | PX RECEIVE | | 52 | 1456 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
      38     | 32 | PX SEND BROADCAST | :TQ10001 | 52 | 1456 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | BROADCAST |
      39     | 33 | PX BLOCK ITERATOR | | 52 | 1456 | 2 (0)| 00:00:01 | | | Q1,01 | PCWC | |
      40     | 34 | TABLE ACCESS FULL | DIM_SWC_NAME | 52 | 1456 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
      41     | 35 | PX BLOCK ITERATOR | | 1913 | 61216 | 3 (0)| 00:00:01 | | | Q1,03 | PCWC | |
      42     | 36 | TABLE ACCESS FULL | DIM_SWC_PROGRAM_BEP | 1913 | 61216 | 3 (0)| 00:00:01 | | | Q1,03 | PCWP | |
      43     ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      44     
      45     Predicate Information (identified by operation id):
      46     ---------------------------------------------------
      47     
      48     6 - access("SWC_PROGRAM_BEP_SID_FK"="SWC_PROGRAM_BEP_SID_PK")
      49     9 - access("SWC_NAME_SID_FK"="SWC_NAME_SID_PK")
      50     10 - access("FISCAL_YEAR_SID_FK"="FISCAL_YEAR_SID_PK")
      51     15 - access("FY"."FISCAL_YEAR"='2012')
      52     17 - filter("TRANSACTION_SOURCE_CODE"='ALLOT')
      53     23 - filter("A"."ACCOUNT_NBR"='4610')
      54     24 - access("ACCOUNT_SID_FK"="ACCOUNT_SID_PK")
      55     28 - filter("ACCOUNT_MONTH_LABEL"='DEC')
      56     29 - access("ACCOUNT_MONTH_SID_FK"="ACCOUNT_MONTH_SID_PK")
      57     
      58     Note
      59     -----
      60     - star transformation used for this statement


      ************************************************************************************
      Execution Plan using TOAD'S Explain Plan utility
      Plan
      SELECT STATEMENT ALL_ROWSCost: 1 K Bytes: 21 K Cardinality: 119                                                                                           
           36 PX COORDINATOR                                                                                      
                35 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10004 :Q1004Cost: 1 K Bytes: 21 K Cardinality: 119                                                                                 
                     34 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1004Cost: 1 K Bytes: 21 K Cardinality: 119                                                                            
                          33 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1004Cost: 1 K Bytes: 21 K Cardinality: 119                                                                       
                               32 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10003 :Q1003Cost: 1 K Bytes: 21 K Cardinality: 119                                                                  
                                    31 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 1 K Bytes: 21 K Cardinality: 119                                                             
                                         28 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 1 K Bytes: 18 K Cardinality: 119                                                        
                                              27 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10002 :Q1002Cost: 1 K Bytes: 18 K Cardinality: 119                                                   
                                                   26 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1002Cost: 1 K Bytes: 18 K Cardinality: 119                                              
                                                        20 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1002Cost: 1 K Bytes: 14 K Cardinality: 119                                         
                                                             5 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1002                                   
                                                                  4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1002Cost: 2 Bytes: 8 Cardinality: 1                               
                                                                       3 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10000 Cost: 2 Bytes: 8 Cardinality: 1                          
                                                                            2 TABLE ACCESS BY INDEX ROWID TABLE DW_FRR.DIM_FISCAL_YEAR Cost: 2 Bytes: 8 Cardinality: 1                     
                                                                                 1 INDEX RANGE SCAN INDEX (UNIQUE) DW_FRR.UNQ_DIM_FISCAL_YEAR_IDX Cost: 1 Cardinality: 1                
                                                             19 PX PARTITION LIST SUBQUERY PARALLEL_COMBINED_WITH_CHILD :Q1002Cost: 1 K Bytes: 27 K Cardinality: 238 Partition #: 16 Partitions accessed #KEY(SUBQUERY)                                   
                                                                  18 TABLE ACCESS BY LOCAL INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.FACT_GLTRANSDTL_FY12 :Q1002Cost: 1 K Bytes: 27 K Cardinality: 238 Partition #: 16                               
                                                                       17 BITMAP CONVERSION TO ROWIDS PARALLEL_COMBINED_WITH_PARENT :Q1002                         
                                                                            16 BITMAP AND PARALLEL_COMBINED_WITH_PARENT :Q1002                    
                                                                                 10 BITMAP MERGE PARALLEL_COMBINED_WITH_PARENT :Q1002               
                                                                                      9 BITMAP KEY ITERATION PARALLEL_COMBINED_WITH_PARENT :Q1002          
                                                                                           7 BUFFER SORT PARALLEL_COMBINED_WITH_PARENT :Q1002     
                                                                                                6 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_ACCOUNT :Q1002Cost: 2 Bytes: 9 Cardinality: 1
                                                                                           8 BITMAP INDEX RANGE SCAN INDEX (BITMAP) PARALLEL_COMBINED_WITH_PARENT DW_ICE.FK_ACCOUNT_GLTRANSFY12_IDX :Q1002Partition #: 16      
                                                                                 15 BITMAP MERGE PARALLEL_COMBINED_WITH_PARENT :Q1002               
                                                                                      14 BITMAP KEY ITERATION PARALLEL_COMBINED_WITH_PARENT :Q1002          
                                                                                           12 BUFFER SORT PARALLEL_COMBINED_WITH_PARENT :Q1002     
                                                                                                11 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_ACCOUNT_MONTH :Q1002Cost: 2 Bytes: 8 Cardinality: 1
                                                                                           13 BITMAP INDEX RANGE SCAN INDEX (BITMAP) PARALLEL_COMBINED_WITH_PARENT DW_ICE.FK_ACCOUNTMTH_GLTRANSFY12_IDX :Q1002Partition #: 16      
                                                        25 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1002                                        
                                                             24 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1002Cost: 2 Bytes: 1 K Cardinality: 52                                    
                                                                  23 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001Cost: 2 Bytes: 1 K Cardinality: 52                               
                                                                       22 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001Cost: 2 Bytes: 1 K Cardinality: 52                          
                                                                            21 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.DIM_SWC_NAME :Q1001Cost: 2 Bytes: 1 K Cardinality: 52                     
                                         30 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1003Cost: 3 Bytes: 60 K Cardinality: 2 K                                                        
                                              29 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.DIM_SWC_PROGRAM_BEP :Q1003Cost: 3 Bytes: 60 K Cardinality: 2 K

      *****************************
      Execution Plan of the Running Query - Query returns results in 8+ minutes


      Plan
      SELECT STATEMENT ALL_ROWSCost: 32 K                                                                                      
           36 PX COORDINATOR                                                                                 
                35 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10005 :Q1005Cost: 32 K Bytes: 15 K Cardinality: 138                                                                            
                     34 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1005Cost: 32 K Bytes: 15 K Cardinality: 138                                                                       
                          33 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1005Cost: 32 K Bytes: 15 K Cardinality: 138                                                                  
                               32 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10004 :Q1004Cost: 32 K Bytes: 15 K Cardinality: 138                                                             
                                    31 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1004Cost: 32 K Bytes: 15 K Cardinality: 138                                                        
                                         28 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1004Cost: 32 K Bytes: 11 K Cardinality: 138                                                   
                                              27 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10003 :Q1003Cost: 32 K Bytes: 11 K Cardinality: 138                                              
                                                   26 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 32 K Bytes: 11 K Cardinality: 138                                         
                                                        20 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 32 K Bytes: 7 K Cardinality: 138                                    
                                                             14 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 32 K Bytes: 13 K Cardinality: 277                               
                                                                  4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 2 Bytes: 8 Cardinality: 1                          
                                                                       3 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001Cost: 2 Bytes: 8 Cardinality: 1                     
                                                                            2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001Cost: 2 Bytes: 8 Cardinality: 1                
                                                                                 1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_ACCOUNT_MONTH :Q1001Cost: 2 Bytes: 8 Cardinality: 1           
                                                                  13 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1003                         
                                                                       11 NESTED LOOPS PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 32 K Bytes: 127 K Cardinality: 3 K                     
                                                                            6 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1003               
                                                                                 5 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_FRR.DIM_ACCOUNT :Q1003Cost: 2 Bytes: 9 Cardinality: 1           
                                                                            10 PARTITION LIST ALL PARALLEL_COMBINED_WITH_PARENT :Q1003Partition #: 20 Partitions accessed #1 - #5               
                                                                                 9 PARTITION LIST ALL PARALLEL_COMBINED_WITH_PARENT :Q1003Partition #: 21 Partitions accessed #1 - #12          
                                                                                      8 BITMAP CONVERSION TO ROWIDS PARALLEL_COMBINED_WITH_PARENT :Q1003     
                                                                                           7 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) PARALLEL_COMBINED_WITH_PARENT DW_ICE.FK_ACCOUNT_GLTRANSFY12_IDX :Q1003Partition #: 21 Partitions accessed #1 - #60
                                                                       12 TABLE ACCESS BY LOCAL INDEX ROWID TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.FACT_GLTRANSDTL_FY12 :Q1003Cost: 32 K Bytes: 84 K Cardinality: 3 K Partition #: 21 Partitions accessed #1                    
                                                             19 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1003                              
                                                                  18 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 2 Bytes: 8 Cardinality: 1                          
                                                                       17 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10000 Cost: 2 Bytes: 8 Cardinality: 1                     
                                                                            16 TABLE ACCESS BY INDEX ROWID TABLE DW_FRR.DIM_FISCAL_YEAR Cost: 2 Bytes: 8 Cardinality: 1                
                                                                                 15 INDEX RANGE SCAN INDEX (UNIQUE) DW_FRR.UNQ_DIM_FISCAL_YEAR_IDX Cost: 1 Cardinality: 1           
                                                        25 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1003                                   
                                                             24 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1003Cost: 2 Bytes: 1 K Cardinality: 52                               
                                                                  23 PX SEND BROADCAST PARALLEL_TO_PARALLEL SYS.:TQ10002 :Q1002Cost: 2 Bytes: 1 K Cardinality: 52                          
                                                                       22 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1002Cost: 2 Bytes: 1 K Cardinality: 52                     
                                                                            21 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.DIM_SWC_NAME :Q1002Cost: 2 Bytes: 1 K Cardinality: 52                
                                         30 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1004Cost: 3 Bytes: 60 K Cardinality: 2 K                                                   
                                              29 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT DW_ICE.DIM_SWC_PROGRAM_BEP :Q1004Cost: 3 Bytes: 60 K Cardinality: 2 K                                              
                                                        


      I am very confused with these results. If anyone can point me in the right direction on how to get the SQL statement to use the correct execution plan. We are not using SQL Plan management functionality and we are generating statistics on tables nightly.

      Thanks in advance