Re: performance partition by date query

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 19 Jul 2007 10:09:46 -0700
Message-ID: <1184864986.782855@bubbleator.drizzle.com>

navin_mahindroo wrote:
> 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")
No version.
No DDL (partition keys, local/global indexes, etc.) No DML (where's the query?)
No bind variables

How much help do you expect? <g>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Jul 19 2007 - 12:09:46 CDT