Home » RDBMS Server » Performance Tuning » OWB Cube load SQL tuning (119, exadata)
| OWB Cube load SQL tuning [message #552741] |
Fri, 27 April 2012 13:13  |
 |
onlinedev
Messages: 1
Registered: April 2012
|
Junior Member |
|
|
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? Query plan is also attached in the image format.
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
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 12:36:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
