我有一个OWB映射,它从临时表中获取输入,并将这些行添加到Cube中。多维数据集背后的底层表是一个使用外键与维度连接的关系事实表。查询背后的解释计划具有相当高的成本,并且映射运行30分钟。如果您在下面看到,在步骤17中,成本上升到1,396,573,这也是嵌套循环开始出现的地方。有人能提供通用的指针来调优这个查询吗?
计划
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 发布于 2012-04-29 04:58:48
尽管名为“基于成本的优化器”,但实际成本通常对故障排除解释计划没有帮助。
基数通常是最重要的信息。只要Oracle估计一个数量级内的行数,这个计划可能就足够好了。
在本例中,ORION模式中的几乎每个表的基数都为1。Oracle认为该模式中的每个表都是空的,或者至少在过滤之后不返回任何行,我猜这不是真的。这可能是由错误的统计数据引起的。(不是缺少统计信息;如果只是缺少统计信息,Oracle可以使用动态采样来进行粗略猜测。)
尝试重新收集统计数据:
begin
dbms_stats.gather_schema_stats('ORION');
end;
/当然,有一百万种方式会让计划变得糟糕。如果简单的统计信息收集不能修复它,您将需要post查询,以及一些信息,比如您希望它运行多长时间,表的大小等。
https://stackoverflow.com/questions/10355646
复制相似问题