首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Oracle OWB多维数据集加载SQL优化

问Oracle OWB多维数据集加载SQL优化
EN

Stack Overflow用户
提问于 2012-04-28 02:03:27
回答 1查看 479关注 0票数 2

我有一个OWB映射,它从临时表中获取输入,并将这些行添加到Cube中。多维数据集背后的底层表是一个使用外键与维度连接的关系事实表。查询背后的解释计划具有相当高的成本,并且映射运行30分钟。如果您在下面看到,在步骤17中,成本上升到1,396,573,这也是嵌套循环开始出现的地方。有人能提供通用的指针来调优这个查询吗?

计划

代码语言:javascript
复制
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  
EN

回答 1

Stack Overflow用户

发布于 2012-04-29 04:58:48

尽管名为“基于成本的优化器”,但实际成本通常对故障排除解释计划没有帮助。

基数通常是最重要的信息。只要Oracle估计一个数量级内的行数,这个计划可能就足够好了。

在本例中,ORION模式中的几乎每个表的基数都为1。Oracle认为该模式中的每个表都是空的,或者至少在过滤之后不返回任何行,我猜这不是真的。这可能是由错误的统计数据引起的。(不是缺少统计信息;如果只是缺少统计信息,Oracle可以使用动态采样来进行粗略猜测。)

尝试重新收集统计数据:

代码语言:javascript
复制
begin
    dbms_stats.gather_schema_stats('ORION');
end;
/

当然,有一百万种方式会让计划变得糟糕。如果简单的统计信息收集不能修复它,您将需要post查询,以及一些信息,比如您希望它运行多长时间,表的大小等。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/10355646

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档