navin wrote:
Hi we query on fact table partition by date.
The query access takes lot of time
since it access 2 partition date
Please could you advise on how to improve the performance it currently
taking 7 minutes to run
here is the explain of the query.
---
Plan hash value: 1770408712
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name
| Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 | 713 | 41355 (2)| 00:08:17 | | |
|* 1 | FILTER
|
| | | | | | |
| 2 | HASH GROUP BY
|
| 1 | 713 | 41355 (2)| 00:08:17 | | |
|* 3 | HASH JOIN SEMI
|
| 1 | 713 | 41354 (2)| 00:08:17 | | |
| 4 | NESTED LOOPS
|
| 1 | 701 | 20183 (2)| 00:04:03 | | |
| 5 | NESTED LOOPS
|
| 1 | 655 | 20182 (2)| 00:04:03 | | |
| 6 | NESTED LOOPS
|
| 1 | 633 | 20180 (2)| 00:04:03 | | |
| 7 | NESTED LOOPS
|
| 1 | 570 | 20178 (2)| 00:04:03 | | |
| 8 | NESTED LOOPS
|
| 1 | 428 | 20177 (2)| 00:04:03 | | |
| 9 | PARTITION RANGE INLIST
|
| 16 | 4624 | 20161 (2)| 00:04:02 |KEY(I) |KEY(I) |
|* 10 | HASH JOIN
|
| 16 | 4624 | 20161 (2)| 00:04:02 | | |
| 11 | INLIST ITERATOR
|
| | | | | | |
| 12 | TABLE ACCESS BY LOCAL INDEX ROWID |
PRODUCT_CATEGORY_DIM
| 432 | 24192 | 33 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 13 | INDEX RANGE SCAN |
PRODUCT_CATEGORY_DIM_PK | 23 | | 2 (0)| 00:00:01 |
KEY(I)
|KEY(I) |
|* 14 | HASH JOIN
|
| 300 | 69900 | 20127 (2)| 00:04:02 | | |
| 15 | INLIST ITERATOR
|
| | | | | | |
| 16 | TABLE ACCESS BY LOCAL INDEX ROWID |
MRO_DIM
| 47 | 1504 | 6 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 17 | INDEX RANGE SCAN |
MRO_DIM_PK
| 3 | | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 18 | HASH JOIN
|
| 5546 | 1088K| 20120 (2)| 00:04:02 | | |
| 19 | INLIST ITERATOR
|
| | | | | | |
| 20 | TABLE ACCESS BY LOCAL INDEX ROWID |
KR_WEIGHT_CATEGORY_DIM | 23 | 552 | 10 (0)| 00:00:01 |
KEY(I)
|KEY(I) |
|* 21 | INDEX RANGE SCAN |
KR_WEIGHT_CATEGORY_DIM_PK | 1 | | 2 (0)| 00:00:01 |
KEY(I)
|KEY(I) |
|* 22 | HASH JOIN
|
| 102K| 17M| 20109 (2)| 00:04:02 | | |
| 23 | INLIST ITERATOR
|
| | | | | | |
| 24 | TABLE ACCESS BY LOCAL INDEX ROWID|
LER_WEIGHT_CATEGORY_DIM | 10 | 250 | 9 (0)| 00:00:01 |
KEY(I)
|KEY(I) |
|* 25 | INDEX RANGE SCAN |
LER_WEIGHT_CATEGORY_DIM_PK | 1 | | 4 (0)| 00:00:01 |
KEY(I)
|KEY(I) |
|* 26 | TABLE ACCESS FULL |
POSITION_FACT
| 2267K| 328M| 20081 (2)| 00:04:01 |KEY(I) |KEY(I) |
| 27 | PARTITION RANGE INLIST
|
| 1 | 139 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 28 | TABLE ACCESS BY LOCAL INDEX ROWID |
VAR_DIM
| 1 | 139 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 29 | INDEX UNIQUE SCAN |
VAR_DIM_PK
| 1 | | 0 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 30 | PARTITION RANGE INLIST
|
| 1 | 142 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 31 | TABLE ACCESS BY LOCAL INDEX ROWID |
PARTNER_DIM
| 1 | 142 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 32 | INDEX UNIQUE SCAN |
PARTNER_DIM_PK
| 1 | | 0 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 33 | PARTITION RANGE INLIST
|
| 1 | 63 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 34 | TABLE ACCESS BY LOCAL INDEX ROWID |
ISSUE_DIM
| 1 | 63 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 35 | INDEX UNIQUE SCAN |
ISSUE_DIM_PK
| 1 | | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 36 | PARTITION RANGE INLIST
|
| 1 | 22 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 37 | INDEX RANGE SCAN |
REP_GROUP_MAP_PK
| 1 | 22 | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 38 | PARTITION RANGE INLIST
|
| 1 | 46 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 39 | TABLE ACCESS BY LOCAL INDEX ROWID |
REPORTING_GROUP_DIM
| 1 | 46 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 40 | INDEX UNIQUE SCAN |
REPORTING_GROUP_UK
| 1 | | 0 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 41 | VIEW |
VW_NSO_1
| 1 | 12 | 21170 (1)| 00:04:15 | | |
|* 42 | FILTER
|
| | | | | | |
| 43 | HASH GROUP BY
|
| 1 | 248 | 21170 (1)| 00:04:15 | | |
| 44 | NESTED LOOPS
|
| 1 | 248 | 21169 (1)| 00:04:15 | | |
|* 45 | HASH JOIN
|
| 130 | 29380 | 21039 (1)| 00:04:13 | | |
|* 46 | HASH JOIN
|
| 2363 | 454K| 20351 (1)| 00:04:05 | | |
| 47 | PARTITION RANGE INLIST
|
| 3927 | 364K| 275 (1)| 00:00:04 |KEY(I) |KEY(I) |
|* 48 | TABLE ACCESS FULL |
VAR_DIM
| 3927 | 364K| 275 (1)| 00:00:04 |KEY(I) |KEY(I) |
|* 49 | HASH JOIN
|
| 217K| 21M| 20074 (1)| 00:04:01 | | |
| 50 | MERGE JOIN CARTESIAN
|
| 83 | 5395 | 14 (0)| 00:00:01 | | |
| 51 | INLIST ITERATOR
|
| | | | | | |
| 52 | PARTITION RANGE ITERATOR
|
| 2 | 66 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 53 | TABLE ACCESS BY LOCAL INDEX ROWID |
REPORTING_GROUP_DIM
| 2 | 66 | 3 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 54 | INDEX RANGE SCAN |
REPORTING_GROUP_UK
| 1 | | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 55 | BUFFER SORT
|
| 47 | 1504 | 11 (0)| 00:00:01 | | |
| 56 | INLIST ITERATOR
|
| | | | | | |
| 57 | PARTITION RANGE ITERATOR
|
| 47 | 1504 | 6 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 58 | TABLE ACCESS BY LOCAL INDEX ROWID|
MRO_DIM
| 47 | 1504 | 6 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 59 | INDEX RANGE SCAN |
MRO_DIM_PK
| 3 | | 2 (0)| 00:00:01 |KEY(I) |KEY(I) |
| 60 | PARTITION RANGE INLIST
|
| 2267K| 80M| 20041 (1)| 00:04:01 |KEY(I) |KEY(I) |
|* 61 | TABLE ACCESS FULL |
POSITION_FACT
| 2267K| 80M| 20041 (1)| 00:04:01 |KEY(I) |KEY(I) |
| 62 | PARTITION RANGE INLIST
|
| 58560 | 1658K| 687 (1)| 00:00:09 |KEY(I) |KEY(I) |
|* 63 | TABLE ACCESS FULL |
PARTNER_DIM
| 58560 | 1658K| 687 (1)| 00:00:09 |KEY(I) |KEY(I) |
| 64 | PARTITION RANGE INLIST
|
| 1 | 22 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 65 | INDEX UNIQUE SCAN |
REP_GROUP_MAP_PK
| 1 | 22 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SUM("POSITION"."LOSS_GIVEN_EVENT")<>0 OR
SUM("POSITION"."MAX_DEFAULT_EXPOSURE")<>0)
3 - access("PARTNER"."PARTNER_GROUP_CODE"="$nso_col_1")
10 -
access("POSITION"."BUSINESS_DATE"="PRODUCT_CATEGORY"."BUSINESS_DATE"
AND
"POSITION"."PRODUCT_CATEGORY_ID"="PRODUCT_CATEGORY"."PRODUCT_CATEGORY_ID"
AND
"POSITION"."SNAPSHOT_ID"="PRODUCT_CATEGORY"."SNAPSHOT_ID")
13 - access("PRODUCT_CATEGORY"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR
"PRODUCT_CATEGORY"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
14 - access("POSITION"."BUSINESS_DATE"="MRO"."BUSINESS_DATE" AND
"POSITION"."MRO_ID"="MRO"."MRO_ID" AND
"POSITION"."SNAPSHOT_ID"="MRO"."SNAPSHOT_ID")
17 - access("MRO"."BUSINESS_DATE"=TO_DATE('2007-05-30 00:00:00',
'yyyy-mm-dd
hh24:mi:ss') OR
"MRO"."BUSINESS_DATE"=TO_DATE('2007-05-31 00:00:00',
'yyyy-mm-dd
hh24:mi:ss'))
18 -
access("POSITION"."BUSINESS_DATE"="KR_WEIGHT_CATEGORY"."BUSINESS_DATE"
AND
"POSITION"."KLUMPEN_WEIGHT_CATEGORY_ID"="KR_WEIGHT_CATEGORY"."KLUMPEN_WEIGHT_CATEGORY_ID"
AND
"POSITION"."SNAPSHOT_ID"="KR_WEIGHT_CATEGORY"."SNAPSHOT_ID")
21 - access("KR_WEIGHT_CATEGORY"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"KR_WEIGHT_CATEGORY"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
22 -
access("POSITION"."BUSINESS_DATE"="LER_WEIGHT_CATEGORY"."BUSINESS_DATE"
AND
"POSITION"."SNAPSHOT_ID"="LER_WEIGHT_CATEGORY"."SNAPSHOT_ID" AND
"POSITION"."LER_WEIGHT_CATEGORY_ID"="LER_WEIGHT_CATEGORY"."LER_WEIGHT
_CATEGORY_ID")
25 -
access("LER_WEIGHT_CATEGORY"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"LER_WEIGHT_CATEGORY"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
26 - filter("POSITION"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR
"POSITION"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
28 - filter("VAR"."AREA"='Equities')
29 - access("POSITION"."BUSINESS_DATE"="VAR"."BUSINESS_DATE" AND
"POSITION"."SNAPSHOT_ID"="VAR"."SNAPSHOT_ID" AND
"POSITION"."VAR_ID"="VAR"."VAR_ID")
filter("VAR"."BUSINESS_DATE"=TO_DATE('2007-05-30 00:00:00',
'yyyy-mm-dd
hh24:mi:ss') OR
"VAR"."BUSINESS_DATE"=TO_DATE('2007-05-31 00:00:00',
'yyyy-mm-dd
hh24:mi:ss'))
32 - access("POSITION"."BUSINESS_DATE"="PARTNER"."BUSINESS_DATE" AND
"POSITION"."SNAPSHOT_ID"="PARTNER"."SNAPSHOT_ID" AND
"POSITION"."PARTNER_ID"="PARTNER"."PARTNER_ID")
filter("PARTNER"."BUSINESS_DATE"=TO_DATE('2007-05-30 00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR
"PARTNER"."BUSINESS_DATE"=TO_DATE('2007-05-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
35 - access("POSITION"."BUSINESS_DATE"="ISSUE"."BUSINESS_DATE" AND
"POSITION"."SNAPSHOT_ID"="ISSUE"."SNAPSHOT_ID" AND
"POSITION"."ISSUE_ID"="ISSUE"."ISSUE_ID")
filter("ISSUE"."BUSINESS_DATE"=TO_DATE('2007-05-30 00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR
"ISSUE"."BUSINESS_DATE"=TO_DATE('2007-05-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
37 -
access("POSITION"."BUSINESS_DATE"="POSITION_REPORTING_GROUP"."BUSINESS_DATE"
AND
"POSITION"."SNAPSHOT_ID"="POSITION_REPORTING_GROUP"."SNAPSHOT_ID"
AND
"POSITION"."POSITION_ID"="POSITION_REPORTING_GROUP"."POSITION_ID")
filter("POSITION_REPORTING_GROUP"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"POSITION_REPORTING_GROUP"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
39 - filter("REPORTING_GROUP"."REPORT_FLAG"='Y' AND
"POSITION_REPORTING_GROUP"."REPORTING_GROUP_ID"="REPORTING_GROUP"."REPORTING_GRO
UP_ID")
40 -
access("POSITION_REPORTING_GROUP"."BUSINESS_DATE"="REPORTING_GROUP"."BUSINESS_DATE"
AND
"POSITION_REPORTING_GROUP"."SNAPSHOT_ID"="REPORTING_GROUP"."SNAPSHOT_ID"
AND
"REPORTING_GROUP"."REPORTING_GROUP_NAME"='Business Group
IB')
filter("REPORTING_GROUP"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR
"REPORTING_GROUP"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
42 - filter(ABS(SUM(CASE "POSITION"."BUSINESS_DATE" WHEN
TO_DATE('2007-05-31
00:00:00', 'yyyy-mm-dd hh24:mi:ss') THEN
"POSITION"."LOSS_GIVEN_EVENT" ELSE 0 END ))>40 OR
ABS(SUM(CASE
"POSITION"."BUSINESS_DATE" WHEN TO_DATE('2007-05-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss') THEN
"POSITION"."LOSS_GIVEN_EVENT" ELSE
0 END )-SUM(CASE "POSITION"."BUSINESS_DATE" WHEN TO_DATE('2007-05-30
00:00:00', 'yyyy-mm-dd hh24:mi:ss') THEN
"POSITION"."LOSS_GIVEN_EVENT" ELSE 0 END ))>40)
45 - access("POSITION"."BUSINESS_DATE"="PARTNER"."BUSINESS_DATE" AND
"POSITION"."PARTNER_ID"="PARTNER"."PARTNER_ID" AND
"POSITION"."SNAPSHOT_ID"="PARTNER"."SNAPSHOT_ID")
46 - access("POSITION"."BUSINESS_DATE"="VAR"."BUSINESS_DATE" AND
"POSITION"."SNAPSHOT_ID"="VAR"."SNAPSHOT_ID" AND
"POSITION"."VAR_ID"="VAR"."VAR_ID")
48 - filter("VAR"."AREA"='Equities' AND
("VAR"."BUSINESS_DATE"=TO_DATE('2007-05-30 00:00:00', 'yyyy-mm-dd
hh24:mi:ss')
OR
"VAR"."BUSINESS_DATE"=TO_DATE('2007-05-31 00:00:00',
'yyyy-mm-dd
hh24:mi:ss')))
49 - access("POSITION"."BUSINESS_DATE"="MRO"."BUSINESS_DATE" AND
"POSITION"."MRO_ID"="MRO"."MRO_ID" AND
"POSITION"."SNAPSHOT_ID"="MRO"."SNAPSHOT_ID")
53 - filter("REPORTING_GROUP"."REPORT_FLAG"='Y')
54 - access(("REPORTING_GROUP"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR
"REPORTING_GROUP"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00',
'yyyy-mm-dd hh24:mi:ss')) AND
"REPORTING_GROUP"."REPORTING_GROUP_NAME"='Business Group
IB')
filter("REPORTING_GROUP"."REPORTING_GROUP_NAME"='Business Group
IB')
59 - access("MRO"."BUSINESS_DATE"=TO_DATE('2007-05-30 00:00:00',
'yyyy-mm-dd
hh24:mi:ss') OR
"MRO"."BUSINESS_DATE"=TO_DATE('2007-05-31 00:00:00',
'yyyy-mm-dd
hh24:mi:ss'))
61 - filter("POSITION"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR
"POSITION"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
63 - filter("PARTNER"."BUSINESS_DATE"=TO_DATE('2007-05-30 00:00:00',
'yyyy-mm-dd hh24:mi:ss') OR
"PARTNER"."BUSINESS_DATE"=TO_DATE('2007-05-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
65 -
access("POSITION"."BUSINESS_DATE"="POSITION_REPORTING_GROUP"."BUSINESS_DATE"
AND
"POSITION"."SNAPSHOT_ID"="POSITION_REPORTING_GROUP"."SNAPSHOT_ID"
AND
"POSITION"."POSITION_ID"="POSITION_REPORTING_GROUP"."POSITION_ID"
AND
"POSITION_REPORTING_GROUP"."REPORTING_GROUP_ID"="REPORTING_GROUP"."REPORTING_GROUP_ID")
filter(("POSITION_REPORTING_GROUP"."BUSINESS_DATE"=TO_DATE('2007-05-30
00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR
"POSITION_REPORTING_GROUP"."BUSINESS_DATE"=TO_DATE('2007-05-31
00:00:00', 'yyyy-mm-dd hh24:mi:ss')) AND
"POSITION_REPORTING_GROUP"."BUSINESS_DATE"="REPORTING_GROUP"."BUSINESS_DATE"
AND
"POSITION_REPORTING_GROUP"."SNAPSHOT_ID"="REPORTING_GROUP"."SNAPSHOT_ID")
Received on Thu Jul 19 2007 - 11:18:38 CDT
