Oracle OWB Cube load SQL tuning

Question

I have a OWB mapping which takes input from a staging table and add those row to the Cube. The underlying table behind cube is a relational fact table joined with the dimensions using foreign keys. Explain plan behind the query has a rather high cost and the mapping runs for 30 minutes. If you see below, in step 17, the cost goes up to 1,396,573 which is also where nested loops start to appear. Can somebody provide general pointers to tune this query?

Plan

SELECT STATEMENT  ALL_ROWSCost: 1,746,526,275  Bytes: 386,835,904  Cardinality: 464,947                                                             
    46 NESTED LOOPS OUTER  Cost: 1,746,526,275  Bytes: 386,835,904  Cardinality: 464,947                                                        
        41 NESTED LOOPS OUTER  Cost: 1,744,200,663  Bytes: 380,791,593  Cardinality: 464,947                                                    
            37 NESTED LOOPS OUTER  Cost: 1,743,270,415  Bytes: 374,747,282  Cardinality: 464,947                                                
                34 NESTED LOOPS OUTER  Cost: 1,740,476,128  Bytes: 368,702,971  Cardinality: 464,947                                            
                    29 NESTED LOOPS OUTER  Cost: 1,739,545,862  Bytes: 362,658,660  Cardinality: 464,947                                        
                        25 NESTED LOOPS OUTER  Cost: 1,710,193,475  Bytes: 356,614,349  Cardinality: 464,947                                    
                            20 NESTED LOOPS OUTER  Cost: 49,230,267  Bytes: 350,570,038  Cardinality: 464,947                               
                                17 NESTED LOOPS OUTER  Cost: 1,402,837  Bytes: 344,525,727  Cardinality: 464,947                            
                                    13 HASH JOIN RIGHT OUTER  Cost: 7,481  Bytes: 338,481,416  Cardinality: 464,947                         
                                        1 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_HR_SALARY Cost: 6  Bytes: 31  Cardinality: 1                    
                                        12 HASH JOIN RIGHT OUTER  Cost: 7,472  Bytes: 324,068,059  Cardinality: 464,947                     
                                            2 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_ADDRESS Cost: 2,050  Bytes: 65  Cardinality: 1                  
                                            11 HASH JOIN RIGHT OUTER  Cost: 5,420  Bytes: 293,846,504  Cardinality: 464,947                 
                                                3 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_SESSION Cost: 12  Bytes: 70  Cardinality: 1             
                                                10 HASH JOIN RIGHT OUTER  Cost: 5,405  Bytes: 261,300,214  Cardinality: 464,947             
                                                    4 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_LOCATION Cost: 9  Bytes: 21  Cardinality: 1         
                                                    9 HASH JOIN RIGHT OUTER  Cost: 5,393  Bytes: 251,536,327  Cardinality: 464,947          
                                                        5 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_HR_EMPLOYEE Cost: 135  Bytes: 75  Cardinality: 1    
                                                        8 HASH JOIN RIGHT OUTER  Cost: 5,256  Bytes: 216,665,302  Cardinality: 464,947      
                                                            6 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_CLASS_INSTRUCTOR Cost: 12  Bytes: 48  Cardinality: 1  
                                                            7 TABLE ACCESS STORAGE FULL TABLE O_STG.FACT_CLASS_INSTRUCTOR_STG2 Cost: 5,241  Bytes: 194,347,846  Cardinality: 464,947  
                                    16 VIEW SYS. Cost: 3  Bytes: 13  Cardinality: 1                         
                                        15 TABLE ACCESS BY INDEX ROWID TABLE ORION.DIM_CLASS_ATTRIBUTES Cost: 3  Bytes: 153  Cardinality: 1                     
                                            14 INDEX RANGE SCAN INDEX ORION.ALL_ATTRIBUTES_IDX_12 Cost: 2  Cardinality: 1               
                                19 VIEW SYS. Cost: 103  Bytes: 13  Cardinality: 1                           
                                    18 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_COURSE Cost: 103  Bytes: 30  Cardinality: 1                        
                            24 PARTITION HASH ALL  Cost: 3,572  Bytes: 13  Cardinality: 1  Partition #: 27  Partitions accessed #1 - #8                             
                                23 VIEW SYS. Cost: 3,572  Bytes: 13  Cardinality: 1                             
                                    22 TABLE ACCESS BY LOCAL INDEX ROWID TABLE ORION.DIM_PERSON Cost: 3,572  Bytes: 31  Cardinality: 1  Partition #: 27  Partitions accessed #1 - #8                        
                                        21 INDEX RANGE SCAN INDEX ORION.ALL_ATTRIBUTES_IDX_2_P Cost: 8  Cardinality: 3,661  Partition #: 27  Partitions accessed #1 - #8                    
                        28 PARTITION RANGE ALL  Cost: 63  Bytes: 13  Cardinality: 1  Partition #: 31  Partitions accessed #1 - #7                                   
                            27 VIEW SYS. Cost: 63  Bytes: 13  Cardinality: 1                                
                                26 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_TIME_TERM Cost: 63  Bytes: 27  Cardinality: 1  Partition #: 31  Partitions accessed #1 - #7                            
                    33 VIEW SYS. Cost: 2  Bytes: 13  Cardinality: 1                                         
                        32 FILTER                                   
                            31 TABLE ACCESS BY INDEX ROWID TABLE ORION.DIM_HR_JOB Cost: 2  Bytes: 38  Cardinality: 1                                
                                30 INDEX RANGE SCAN INDEX ORION.ALL_ATTRIBUTES_IDX_19 Cost: 1  Cardinality: 1                           
                36 VIEW SYS. Cost: 6  Bytes: 13  Cardinality: 1                                             
                    35 TABLE ACCESS STORAGE FULL TABLE ORION.DIM_ORG_STRUCTURE Cost: 6  Bytes: 37  Cardinality: 1                                       
            40 VIEW SYS. Cost: 2  Bytes: 13  Cardinality: 1                                                 
                39 TABLE ACCESS BY INDEX ROWID TABLE ORION.DIM_DEMOGRAPHICS Cost: 2  Bytes: 43  Cardinality: 1                                              
                    38 INDEX RANGE SCAN INDEX ORION.ALL_ATTRIBUTES_IDX Cost: 1  Cardinality: 1                                          
        45 VIEW SYS. Cost: 5  Bytes: 13  Cardinality: 1                                                     
            44 FILTER                                               
                43 TABLE ACCESS BY INDEX ROWID TABLE ORION.DIM_DEPARTMENT Cost: 5  Bytes: 31  Cardinality: 1                                            
                    42 INDEX RANGE SCAN INDEX ORION.ALL_DEPT_ATTRIBUTES_IDX Cost: 1  Cardinality: 3

Are you using something like TOAD to generate this query plan? Could you use `DBMS_XPLAN` or something else that generates a more friendly text query plan? Can you post the query as well? — Justin Cave, Apr 27 '12 at 18:16

score 0 · Answer 1 · answered Apr 28 '12 at 20:58

Despite the name "Cost Based Optimizer", the actual cost is usually not helpful for troubleshooting explain plans.

Cardinality is usually the most important piece of information. As long as Oracle estimates the number of rows within an order of magnitude, the plan is probably good enough.

In this case, almost every table in the ORION schema has a cardinality of 1. Oracle thinks that every table in that schema is empty, or at least returns no rows after a filter, which I'm guessing is not true. This is probably caused by bad statistics. (Not missing statistics; if the statistics were simply missing, Oracle could have used dynamic sampling to make a rough guess.)

Try re-gathering statistics:

begin
    dbms_stats.gather_schema_stats('ORION');
end;
/

Of course, there are a million ways for plans to go bad. If a simple statistics gathering doesn't fix it, you'll need to post the query, and some information like how long you expect it to run, what are the sizes of the tables, etc.

Thanks Justin and Jone. I added gather statistics hints and got the updated results and compared the estimated vs actual cardinality and found some variances. Then I added cardinality hints. However, that didn't improve the performance. Dropbox links to query, gather stats, cardinality hints are below. Appreciated your help. 1. Original query http://dl.dropbox.com/u/13342121/original_query.rtf 2. Gather stat output: http://dl.dropbox.com/u/13342121/query_gather_stat_output.rtf 3. Query after cardinality hints: http://dl.dropbox.com/u/13342121/modified_query_cardinality_hint.rtf — onlinedev, May 01 '12 at 00:50

Oracle OWB Cube load SQL tuning

1 Answers1