Home » RDBMS Server » Performance Tuning » what to do about this one !!!
what to do about this one !!! [message #190343] Wed, 30 August 2006 05:31 Go to next message
seemit
Messages: 7
Registered: July 2006
Junior Member
Hey all,
I am given a query which generates 175 K records in 45 minutes. I need to get it down to <10 minutes. I am not supposed to change the query and yet performance gain is required ( but we can forget this "NO CHANGE" right now, so lets change however we want). I can do a 'diff' after spooling the 2 results on Unix, in case the results differ.

I am told to use HINTS...

Here is the scenario..

SELECT 40 columns, sum(some_col) from 6 tables where 6 joins AND some_col='A' AND a condition with IN
group by 40 columns

UNION ALL --same query repeats

SELECT 40 columns, sum(some_col) from 6 tables where 6 joins AND some_col!='A' AND a condition with IN
group by 40 columns -- notice != 'A'
HAVING
sum(some_col)>=some_int value --this sum is same as query before union


Help me with this one !! for one, my team man is trying INDEXing. something else you guys could suggest?
Re: what to do about this one !!! [message #190345 is a reply to message #190343] Wed, 30 August 2006 05:42 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
It helps if you provide the entire query and explain plan. Be sure to post your query and plans [code]inside[/code] code blocks.
Re: what to do about this one !!! [message #190353 is a reply to message #190345] Wed, 30 August 2006 05:58 Go to previous messageGo to next message
seemit
Messages: 7
Registered: July 2006
Junior Member
okay here you go..

QUERY ===>

SELECT
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA) NET_CVA, sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ASGN_DELTA) A_NET_CVA,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_X_ROLE_DELTA) X_NET_CVA, sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ROLE_DELTA) F_NET_CVA,
DIM_CLIENT.SPN_ID
FROM
FACT_EMPLOYEE_ORDER,
DIM_CLIENT,
DIM_TEAM,
DIM_EMPLOYEE_ROLE,
DIM_PRODUCT_HIERARCHY,
DIM_SOURCE_SYSTEM,
DIM_DATE,
DIM_FINANCIAL_HIERARCHY DIM_TRADING_DESK
WHERE
( FACT_EMPLOYEE_ORDER.ID_SOURCE_SYSTEM=DIM_SOURCE_SYSTEM.ID_SOURCE_SYSTEM )
AND ( FACT_EMPLOYEE_ORDER.ID_CLIENT=DIM_CLIENT.ID_CLIENT )
AND ( FACT_EMPLOYEE_ORDER.ID_EMPLOYEE_ROLE=DIM_EMPLOYEE_ROLE.ID_EMPLOYEE_ROLE )
AND ( FACT_EMPLOYEE_ORDER.ID_LE_OU_TRADING_DESK=DIM_TRADING_DESK.ID_LE_OU )
AND ( DIM_PRODUCT_HIERARCHY.ID_INSTRUMENT_PRODUCT=FACT_EMPLOYEE_ORDER.ID_INSTRUMENT_PRODUCT )
AND ( DIM_TEAM.ID_TEAM=FACT_EMPLOYEE_ORDER.ID_TEAM )
AND ( FACT_EMPLOYEE_ORDER.ID_REPORTING_DATE=DIM_DATE.ID_DATE )
AND (IND_TO_BE_DELETED = 'N' AND IND_IS_REPORTABLE = 'Y' )
AND (
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM IN ('EQ OTC DERIV', 'OTHER CR EXO/HYB', 'SYNTH ETIC CDO - INV', 'FX SPOT', 'SOFTS',
'STR ALTERN INVEST', 'PRECIOUS METALS', 'FX SWAP', 'GAS', 'IRD SWAPS', 'IRD EXO/HYB', 'FX EXOTIC OPTIONS', 'POWER',
'TRANCHE', 'FX HYBRID OPTIONS', 'EQ SECURIT DERIV', 'EMISSIONS', 'FX OPTIONS', 'FX FORWARD', 'OIL', 'IRD OPTIONS',
'FTD', 'BASE METALS', 'SYNTHETIC CDO - MGR', 'COAL')
AND ( DIM_DATE.YEAR_MONTH BETWEEN 200601 AND 200607)
AND ( DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE = 'SALES' )
AND DIM_CLIENT.SECTOR_L1_3_NM = 'CORPORATES'
)
GROUP BY
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
DIM_CLIENT.SPN_ID
UNION ALL
SELECT
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA), sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ASGN_DELTA),
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_X_ROLE_DELTA), sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ROLE_DELTA),
DIM_CLIENT.SPN_ID
FROM
FACT_EMPLOYEE_ORDER,
DIM_CLIENT,
DIM_TEAM,
DIM_EMPLOYEE_ROLE,
DIM_PRODUCT_HIERARCHY,
DIM_SOURCE_SYSTEM,
DIM_DATE,
DIM_FINANCIAL_HIERARCHY DIM_TRADING_DESK
WHERE
( FACT_EMPLOYEE_ORDER.ID_SOURCE_SYSTEM=DIM_SOURCE_SYSTEM.ID_SOURCE_SYSTEM )
AND ( FACT_EMPLOYEE_ORDER.ID_CLIENT=DIM_CLIENT.ID_CLIENT )
AND ( FACT_EMPLOYEE_ORDER.ID_EMPLOYEE_ROLE=DIM_EMPLOYEE_ROLE.ID_EMPLOYEE_ROLE )
AND ( FACT_EMPLOYEE_ORDER.ID_LE_OU_TRADING_DESK=DIM_TRADING_DESK.ID_LE_OU )
AND ( DIM_PRODUCT_HIERARCHY.ID_INSTRUMENT_PRODUCT=FACT_EMPLOYEE_ORDER.ID_INSTRUMENT_PRODUCT )
AND ( DIM_TEAM.ID_TEAM=FACT_EMPLOYEE_ORDER.ID_TEAM )
AND ( FACT_EMPLOYEE_ORDER.ID_REPORTING_DATE=DIM_DATE.ID_DATE )
AND (IND_TO_BE_DELETED = 'N' AND IND_IS_REPORTABLE = 'Y')
AND (
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM IN ('EQ OTC DERIV', 'OTHER CR EXO/HYB', 'SYNTH ETIC CDO - INV', 'FX SPOT', 'SOFTS',
'STR ALTERN INVEST', 'PRECIOUS METALS', 'FX SWAP', 'GAS', 'IRD SWAPS', 'IRD EXO/HYB', 'FX EXOTIC OPTIONS', 'POWER',
'TRANCHE', 'FX HYBRID OPTIONS', 'EQ SECURIT DERIV', 'EMISSIONS', 'FX OPTIONS', 'FX FORWARD', 'OIL', 'IRD OPTIONS',
'FTD', 'BASE METALS', 'SYNTHETIC CDO - MGR', 'COAL')
AND ( DIM_DATE.YEAR_MONTH BETWEEN 200601 AND 200607)
AND ( DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE = 'SALES' )
AND DIM_CLIENT.SECTOR_L1_3_NM != 'CORPORATES'
)
GROUP BY
DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
DIM_CLIENT.SPN_ID
HAVING
sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA) >= 250000;



EXPLAIN PLAN ==> has 22 FULL TABLE ACCESSES copy it to textpad <if you have it >

Plan hash value: 2635515769

------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------
| 0 | SELECT STATEMENT | | 1830 | 892K| 48942 (65)| 00:11:26 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,01 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT | | | | | | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,01 | PCWP | |
| 6 | PX SEND ROUND-ROBIN | :TQ10000 | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | P->P | RND-ROBIN |
| 7 | PX BLOCK ITERATOR | | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | DIM_CLIENT | 48785 | 4383K| 2213 (1)| 00:00:31 | | | Q1,00 | PCWP | |
| 9 | PX COORDINATOR | | | | | | | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ20018 | | | | | | | Q2,18 | P->S | QC (RAND) |
| 11 | BUFFER SORT | | 1830 | 892K| 48942 (65)| 00:11:26 | | | Q2,18 | PCWP | |
| 12 | UNION-ALL | | | | | | | | Q2,18 | PCWP | |
| 13 | SORT GROUP BY | | 298 | 136K| 15289 (2)| 00:03:35 | | | Q2,18 | PCWP | |
| 14 | PX RECEIVE | | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,18 | PCWP | |
| 15 | PX SEND HASH | :TQ20016 | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,16 | P->P | HASH |
|* 16 | HASH JOIN | | 298 | 136K| 15288 (2)| 00:03:35 | | | Q2,16 | PCWP | |
| 17 | PX RECEIVE | | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,16 | PCWP | |
| 18 | PX SEND BROADCAST | :TQ20014 | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,14 | P->P | BROADCAST |
|* 19 | HASH JOIN | | 298 | 106K| 15009 (2)| 00:03:31 | | | Q2,14 | PCWP | |
| 20 | PX RECEIVE | | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,14 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ20012 | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,12 | P->P | BROADCAST |
|* 22 | HASH JOIN | | 298 | 84632 | 14984 (2)| 00:03:30 | | | Q2,12 | PCWP | |
| 23 | PX RECEIVE | | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,12 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ20010 | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,10 | P->P | BROADCAST |
|* 25 | HASH JOIN | | 298 | 51554 | 14956 (2)| 00:03:30 | | | Q2,10 | PCWP | |
| 26 | PX RECEIVE | | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,10 | PCWP | |
| 27 | PX SEND BROADCAST | :TQ20007 | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,07 | P->P | BROADCAST |
|* 28 | HASH JOIN BUFFERED | | 298 | 48276 | 14950 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 29 | PX JOIN FILTER CREATE | :BF0000 | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 30 | PX RECEIVE | | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,07 | PCWP | |
| 31 | PX SEND HASH | :TQ20002 | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,02 | P->P | HASH |
|* 32 | HASH JOIN | | 298 | 34270 | 14944 (2)| 00:03:30 | | | Q2,02 | PCWP | |
| 33 | PX RECEIVE | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 34 | PX SEND BROADCAST | :TQ20000 | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | P->P | BROADCAST |
| 35 | PX BLOCK ITERATOR | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | PCWC | |
|* 36 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,00 | PCWP | |
|* 37 | HASH JOIN | | 895 | 93080 | 14938 (2)| 00:03:30 | | | Q2,02 | PCWP | |
| 38 | PX RECEIVE | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 39 | PX SEND BROADCAST | :TQ20001 | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | P->P | BROADCAST |
| 40 | PX BLOCK ITERATOR | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | PCWC | |
|* 41 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 42 | PX PARTITION RANGE ALL | | 6179 | 470K| 14926 (2)| 00:03:29 | 1 | 26 | Q2,02 | PCWC | |
|* 43 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_EMPLOYEE_ORDER | 6179 | 470K| 14926 (2)| 00:03:29 | 1 | 26 | Q2,02 | PCWP | |
| 44 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q2,02 | PCWP | |
| 45 | BITMAP AND | | | | | | | | Q2,02 | PCWP | |
| 46 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 47 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 48 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 49 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 50 | BITMAP INDEX RANGE SCAN | XIF35FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 51 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 52 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 53 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 54 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 55 | BITMAP INDEX RANGE SCAN | XIF26FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 56 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 57 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 58 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
|* 59 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 60 | BITMAP CONVERSION FROM ROWID| | | | | | | | Q2,02 | PCWP | |
|* 61 | INDEX RANGE SCAN | XIF13FACT_EMPLOYEE_ORDER | | | 265 (1)| 00:00:04 | 1 | 26 | Q2,02 | PCWP | |
| 62 | BITMAP MERGE | | | | | | | | Q2,02 | PCWP | |
| 63 | BITMAP KEY ITERATION | | | | | | | | Q2,02 | PCWP | |
| 64 | BUFFER SORT | | | | | | | | Q2,02 | PCWP | |
| 65 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_4091BC55 | 1 | 13 | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 66 | BITMAP INDEX RANGE SCAN | XIF34FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,02 | PCWP | |
| 67 | PX RECEIVE | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,07 | PCWP | |
| 68 | PX SEND HASH | :TQ20003 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | P->P | HASH |
| 69 | PX JOIN FILTER USE | :BF0000 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWP | |
| 70 | PX BLOCK ITERATOR | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWC | |
|* 71 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,03 | PCWP | |
| 72 | PX BLOCK ITERATOR | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,10 | PCWC | |
| 73 | TABLE ACCESS FULL | DIM_SOURCE_SYSTEM | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,10 | PCWP | |
| 74 | PX BLOCK ITERATOR | | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,12 | PCWC | |
| 75 | TABLE ACCESS FULL | DIM_TEAM | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,12 | PCWP | |
| 76 | PX BLOCK ITERATOR | | 48785 | 3906K| 25 (0)| 00:00:01 | | | Q2,14 | PCWC | |
| 77 | TABLE ACCESS FULL | SYS_TEMP_0FD9D663D_4091BC55 | 48785 | 3906K| 25 (0)| 00:00:01 | | | Q2,14 | PCWP | |
| 78 | PX BLOCK ITERATOR | | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,16 | PCWC | |
| 79 | TABLE ACCESS FULL | DIM_FINANCIAL_HIERARCHY | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,16 | PCWP | |
|* 80 | FILTER | | | | | | | | Q2,18 | PCWC | |
| 81 | SORT GROUP BY | | 1532 | 755K| 31440 (1)| 00:07:21 | | | Q2,18 | PCWP | |
| 82 | PX RECEIVE | | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,18 | PCWP | |
| 83 | PX SEND HASH | :TQ20017 | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,17 | P->P | HASH |
|* 84 | HASH JOIN | | 3451 | 1701K| 31439 (1)| 00:07:21 | | | Q2,17 | PCWP | |
| 85 | PX RECEIVE | | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,17 | PCWP | |
| 86 | PX SEND BROADCAST | :TQ20015 | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,15 | P->P | BROADCAST |
|* 87 | HASH JOIN | | 3451 | 1391K| 29225 (1)| 00:06:50 | | | Q2,15 | PCWP | |
| 88 | PX RECEIVE | | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,15 | PCWP | |
| 89 | PX SEND BROADCAST | :TQ20013 | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,13 | P->P | BROADCAST |
|* 90 | HASH JOIN | | 3451 | 1044K| 28946 (1)| 00:06:46 | | | Q2,13 | PCWP | |
| 91 | PX BLOCK ITERATOR | | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,13 | PCWC | |
| 92 | TABLE ACCESS FULL | DIM_TEAM | 20810 | 2255K| 27 (0)| 00:00:01 | | | Q2,13 | PCWP | |
| 93 | BUFFER SORT | | | | | | | | Q2,13 | PCWC | |
| 94 | PX RECEIVE | | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,13 | PCWP | |
| 95 | PX SEND BROADCAST | :TQ20011 | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,11 | P->P | BROADCAST |
|* 96 | HASH JOIN BUFFERED | | 3451 | 670K| 28918 (1)| 00:06:45 | | | Q2,11 | PCWP | |
| 97 | PX JOIN FILTER CREATE | :BF0001 | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,11 | PCWP | |
| 98 | PX RECEIVE | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,11 | PCWP | |
| 99 | PX SEND HASH | :TQ20008 | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | P->P | HASH |
| 100 | PX BLOCK ITERATOR | | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | PCWC | |
| 101 | TABLE ACCESS FULL | DIM_SOURCE_SYSTEM | 13200 | 141K| 6 (0)| 00:00:01 | | | Q2,08 | PCWP | |
| 102 | PX RECEIVE | | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,11 | PCWP | |
| 103 | PX SEND HASH | :TQ20009 | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | P->P | HASH |
| 104 | PX JOIN FILTER USE | :BF0001 | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | PCWP | |
|*105 | HASH JOIN | | 3451 | 633K| 28912 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 106 | PX RECEIVE | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 107 | PX SEND BROADCAST | :TQ20004 | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | P->P | BROADCAST |
| 108 | PX BLOCK ITERATOR | | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | PCWC | |
|*109 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,04 | PCWP | |
|*110 | HASH JOIN | | 3452 | 475K| 28906 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 111 | PX RECEIVE | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 112 | PX SEND BROADCAST | :TQ20005 | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | P->P | BROADCAST |
| 113 | PX BLOCK ITERATOR | | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | PCWC | |
|*114 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,05 | PCWP | |
|*115 | HASH JOIN | | 23843 | 2677K| 28894 (1)| 00:06:45 | | | Q2,09 | PCWP | |
| 116 | PX RECEIVE | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 117 | PX SEND BROADCAST | :TQ20006 | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | P->P | BROADCAST |
| 118 | PX BLOCK ITERATOR | | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | PCWC | |
|*119 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,06 | PCWP | |
| 120 | PX PARTITION RANGE ALL | | 71528 | 7264K| 28888 (1)| 00:06:45 | 1 | 26 | Q2,09 | PCWC | |
|*121 | TABLE ACCESS BY LOCAL INDEX ROWID| FACT_EMPLOYEE_ORDER | 71528 | 7264K| 28888 (1)| 00:06:45 | 1 | 26 | Q2,09 | PCWP | |
| 122 | BITMAP CONVERSION TO ROWIDS | | | | | | | | Q2,09 | PCWP | |
| 123 | BITMAP AND | | | | | | | | Q2,09 | PCWP | |
| 124 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 125 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 126 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*127 | TABLE ACCESS FULL | DIM_EMPLOYEE_ROLE | 1 | 11 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
|*128 | BITMAP INDEX RANGE SCAN | XIF35FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,09 | PCWP | |
| 129 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 130 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 131 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*132 | TABLE ACCESS FULL | DIM_PRODUCT_HIERARCHY | 802 | 37694 | 6 (0)| 00:00:01 | | | Q2,09 | PCWP | |
|*133 | BITMAP INDEX RANGE SCAN | XIF26FACT_EMPLOYEE_ORDER | | | | | 1 | 26 | Q2,09 | PCWP | |
| 134 | BITMAP MERGE | | | | | | | | Q2,09 | PCWP | |
| 135 | BITMAP KEY ITERATION | | | | | | | | Q2,09 | PCWP | |
| 136 | BUFFER SORT | | | | | | | | Q2,09 | PCWP | |
|*137 | TABLE ACCESS FULL | DIM_DATE | 120 | 3120 | 11 (0)| 00:00:01 | | | Q2,09 | PCWP | |
| 138 | BITMAP CONVERSION FROM ROWID| | | | | | | | Q2,09 | PCWP | |
|*139 | INDEX RANGE SCAN | XIF13FACT_EMPLOYEE_ORDER | 92814 | | 265 (1)| 00:00:04 | 1 | 26 | Q2,09 | PCWP | |
| 140 | PX BLOCK ITERATOR | | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,15 | PCWC | |
| 141 | TABLE ACCESS FULL | DIM_FINANCIAL_HIERARCHY | 130K| 12M| 278 (2)| 00:00:04 | | | Q2,15 | PCWP | |
| 142 | PX BLOCK ITERATOR | | 632K| 55M| 2213 (1)| 00:00:31 | | | Q2,17 | PCWC | |
|*143 | TABLE ACCESS FULL | DIM_CLIENT | 632K| 55M| 2213 (1)| 00:00:31 | | | Q2,17 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------

Re: what to do about this one !!! [message #190370 is a reply to message #190353] Wed, 30 August 2006 06:57 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
How about reposting it inside [code][/code] tags as suggested. The code tag preserves the formatting, and makes it easy to read. Explain plans should be posted with their original formatting, as it clearly outlines driving tables and join orders.

You are making it difficult for anyone to want to help.
Re: what to do about this one !!! [message #190371 is a reply to message #190370] Wed, 30 August 2006 07:06 Go to previous messageGo to next message
seemit
Messages: 7
Registered: July 2006
Junior Member
okay my bad

here is the query
 SELECT   
  DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
  DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
  DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
  DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
  DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
  DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
  DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
  DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
  DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
  DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
  sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA) NET_CVA,  sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ASGN_DELTA) A_NET_CVA, 
  sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_X_ROLE_DELTA) X_NET_CVA, sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ROLE_DELTA) F_NET_CVA,
  DIM_CLIENT.SPN_ID
FROM
  FACT_EMPLOYEE_ORDER,
  DIM_CLIENT,
  DIM_TEAM,
  DIM_EMPLOYEE_ROLE,
  DIM_PRODUCT_HIERARCHY,
  DIM_SOURCE_SYSTEM,
  DIM_DATE,
  DIM_FINANCIAL_HIERARCHY  DIM_TRADING_DESK
WHERE
  ( FACT_EMPLOYEE_ORDER.ID_SOURCE_SYSTEM=DIM_SOURCE_SYSTEM.ID_SOURCE_SYSTEM  )
  AND  ( FACT_EMPLOYEE_ORDER.ID_CLIENT=DIM_CLIENT.ID_CLIENT  )
  AND  ( FACT_EMPLOYEE_ORDER.ID_EMPLOYEE_ROLE=DIM_EMPLOYEE_ROLE.ID_EMPLOYEE_ROLE  )
  AND  ( FACT_EMPLOYEE_ORDER.ID_LE_OU_TRADING_DESK=DIM_TRADING_DESK.ID_LE_OU  )
  AND  ( DIM_PRODUCT_HIERARCHY.ID_INSTRUMENT_PRODUCT=FACT_EMPLOYEE_ORDER.ID_INSTRUMENT_PRODUCT  )
  AND  ( DIM_TEAM.ID_TEAM=FACT_EMPLOYEE_ORDER.ID_TEAM  )
  AND  ( FACT_EMPLOYEE_ORDER.ID_REPORTING_DATE=DIM_DATE.ID_DATE  )
  AND  (IND_TO_BE_DELETED = 'N' AND IND_IS_REPORTABLE = 'Y' )
  AND  (
  DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM  IN  ('EQ OTC DERIV', 'OTHER CR EXO/HYB', 'SYNTH ETIC CDO - INV', 'FX SPOT', 'SOFTS', 
	'STR ALTERN INVEST', 'PRECIOUS METALS', 'FX SWAP', 'GAS', 'IRD SWAPS', 'IRD EXO/HYB', 'FX EXOTIC OPTIONS', 'POWER', 
	'TRANCHE', 'FX HYBRID OPTIONS', 'EQ SECURIT DERIV', 'EMISSIONS', 'FX OPTIONS', 'FX FORWARD', 'OIL', 'IRD OPTIONS', 
	'FTD', 'BASE METALS', 'SYNTHETIC CDO - MGR', 'COAL')
  AND  ( DIM_DATE.YEAR_MONTH BETWEEN 200601 AND 200607)
  AND  ( DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE = 'SALES'  )
  AND  DIM_CLIENT.SECTOR_L1_3_NM  =  'CORPORATES'
  )
GROUP BY
  DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
  DIM_CLIENT.FRANCHISE_L1_1_NM, FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
  DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
  DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
  DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
  DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
  DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
  DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
  DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
  DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
  DIM_CLIENT.SPN_ID
UNION  ALL
SELECT   
  DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
  DIM_CLIENT.FRANCHISE_L1_1_NM,  FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
  DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
  DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
  DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
  DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
  DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
  DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
  DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
  DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
  sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA), sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ASGN_DELTA),
  sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_X_ROLE_DELTA), sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_ROLE_DELTA),
  DIM_CLIENT.SPN_ID
FROM
  FACT_EMPLOYEE_ORDER,
  DIM_CLIENT,
  DIM_TEAM,
  DIM_EMPLOYEE_ROLE,
  DIM_PRODUCT_HIERARCHY,
  DIM_SOURCE_SYSTEM,
  DIM_DATE,
  DIM_FINANCIAL_HIERARCHY  DIM_TRADING_DESK
WHERE
  ( FACT_EMPLOYEE_ORDER.ID_SOURCE_SYSTEM=DIM_SOURCE_SYSTEM.ID_SOURCE_SYSTEM  )
  AND  ( FACT_EMPLOYEE_ORDER.ID_CLIENT=DIM_CLIENT.ID_CLIENT  )
  AND  ( FACT_EMPLOYEE_ORDER.ID_EMPLOYEE_ROLE=DIM_EMPLOYEE_ROLE.ID_EMPLOYEE_ROLE  )
  AND  ( FACT_EMPLOYEE_ORDER.ID_LE_OU_TRADING_DESK=DIM_TRADING_DESK.ID_LE_OU  )
  AND  ( DIM_PRODUCT_HIERARCHY.ID_INSTRUMENT_PRODUCT=FACT_EMPLOYEE_ORDER.ID_INSTRUMENT_PRODUCT  )
  AND  ( DIM_TEAM.ID_TEAM=FACT_EMPLOYEE_ORDER.ID_TEAM  )
  AND  ( FACT_EMPLOYEE_ORDER.ID_REPORTING_DATE=DIM_DATE.ID_DATE  )
  AND  (IND_TO_BE_DELETED = 'N' AND IND_IS_REPORTABLE = 'Y')
  AND  (
  DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM  IN  ('EQ OTC DERIV', 'OTHER CR EXO/HYB', 'SYNTH ETIC CDO - INV', 'FX SPOT', 'SOFTS', 
	'STR ALTERN INVEST', 'PRECIOUS METALS', 'FX SWAP', 'GAS', 'IRD SWAPS', 'IRD EXO/HYB', 'FX EXOTIC OPTIONS', 'POWER', 
	'TRANCHE', 'FX HYBRID OPTIONS', 'EQ SECURIT DERIV', 'EMISSIONS', 'FX OPTIONS', 'FX FORWARD', 'OIL', 'IRD OPTIONS', 
	'FTD', 'BASE METALS', 'SYNTHETIC CDO - MGR', 'COAL')
  AND  ( DIM_DATE.YEAR_MONTH BETWEEN 200601 AND 200607)
  AND  ( DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE = 'SALES'  )
  AND  DIM_CLIENT.SECTOR_L1_3_NM  !=  'CORPORATES'
  )
GROUP BY
  DIM_CLIENT.NM_CMU_4, DIM_CLIENT.NM_CMU_3, DIM_CLIENT.NM_CMU_2, DIM_CLIENT.NM_CMU_1,
  DIM_CLIENT.FRANCHISE_L1_1_NM,  FACT_EMPLOYEE_ORDER.FEED_LEADING_SOURCE_SYSTEM_ID,
  DIM_SOURCE_SYSTEM.NM_LEADING_SOURCE_SYSTEM, FACT_EMPLOYEE_ORDER.ARTEMIS_SOURCE_SYSTEM_ID,
  DIM_DATE.NUM_YEAR, DIM_DATE.NM_ABBR_MONTH,
  DIM_TEAM.TEAM_LEVEL_6_NM, DIM_TEAM.TEAM_LEVEL_5_NM, DIM_TEAM.TEAM_LEVEL_4_NM, DIM_TEAM.TEAM_LEVEL_3_NM, DIM_TEAM.TEAM_LEVEL_2_NM, DIM_TEAM.TEAM_LEVEL_1_NM,
  DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_4_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_3_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_2_NM, DIM_PRODUCT_HIERARCHY.PRODUCT_LEVEL_1_NM,
  DIM_TRADING_DESK.NM_OU_ABBR, DIM_TRADING_DESK.ID_OU, DIM_TRADING_DESK.NM_LE, DIM_TRADING_DESK.ID_LE,
  DIM_TRADING_DESK.NM_LEVEL_9, DIM_TRADING_DESK.NM_LEVEL_8, DIM_TRADING_DESK.NM_LEVEL_7, DIM_TRADING_DESK.NM_LEVEL_6, DIM_TRADING_DESK.NM_LEVEL_5,
  DIM_TRADING_DESK.SUMM_PT_LEVEL_9, DIM_TRADING_DESK.SUMM_PT_LEVEL_8, DIM_TRADING_DESK.SUMM_PT_LEVEL_7, DIM_TRADING_DESK.SUMM_PT_LEVEL_6, DIM_TRADING_DESK.SUMM_PT_LEVEL_5,
  DIM_DATE.DT_DATE, DIM_EMPLOYEE_ROLE.NM_EMPLOYEE_ROLE, DIM_TEAM.TEAM_LEVEL_0_NM, DIM_CLIENT.SECTOR_L1_1_NM,
  DIM_CLIENT.SPN_ID
HAVING
  sum(FACT_EMPLOYEE_ORDER.AM_NET_CVA_DELTA)  >=  250000;



and here is the explain plan
----------------------------------------------------------
Plan hash value: 2635515769

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                             |  1830 |   892K| 48942  (65)| 00:11:26 |       |       |        |      |            |
|   1 |  TEMP TABLE TRANSFORMATION                                   |                             |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                                             |                             |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)                                       | :TQ10001                    | 48785 |  4383K|  2213   (1)| 00:00:31 |       |       |  Q1,01 | P->S | QC (RAND)  |
|   4 |     LOAD AS SELECT                                           |                             |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|   5 |      PX RECEIVE                                              |                             | 48785 |  4383K|  2213   (1)| 00:00:31 |       |       |  Q1,01 | PCWP |            |
|   6 |       PX SEND ROUND-ROBIN                                    | :TQ10000                    | 48785 |  4383K|  2213   (1)| 00:00:31 |       |       |  Q1,00 | P->P | RND-ROBIN  |
|   7 |        PX BLOCK ITERATOR                                     |                             | 48785 |  4383K|  2213   (1)| 00:00:31 |       |       |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL                                    | DIM_CLIENT                  | 48785 |  4383K|  2213   (1)| 00:00:31 |       |       |  Q1,00 | PCWP |            |
|   9 |   PX COORDINATOR                                             |                             |       |       |            |          |       |       |        |      |            |
|  10 |    PX SEND QC (RANDOM)                                       | :TQ20018                    |       |       |            |          |       |       |  Q2,18 | P->S | QC (RAND)  |
|  11 |     BUFFER SORT                                              |                             |  1830 |   892K| 48942  (65)| 00:11:26 |       |       |  Q2,18 | PCWP |            |
|  12 |      UNION-ALL                                               |                             |       |       |            |          |       |       |  Q2,18 | PCWP |            |
|  13 |       SORT GROUP BY                                          |                             |   298 |   136K| 15289   (2)| 00:03:35 |       |       |  Q2,18 | PCWP |            |
|  14 |        PX RECEIVE                                            |                             |   298 |   136K| 15288   (2)| 00:03:35 |       |       |  Q2,18 | PCWP |            |
|  15 |         PX SEND HASH                                         | :TQ20016                    |   298 |   136K| 15288   (2)| 00:03:35 |       |       |  Q2,16 | P->P | HASH       |
|* 16 |          HASH JOIN                                           |                             |   298 |   136K| 15288   (2)| 00:03:35 |       |       |  Q2,16 | PCWP |            |
|  17 |           PX RECEIVE                                         |                             |   298 |   106K| 15009   (2)| 00:03:31 |       |       |  Q2,16 | PCWP |            |
|  18 |            PX SEND BROADCAST                                 | :TQ20014                    |   298 |   106K| 15009   (2)| 00:03:31 |       |       |  Q2,14 | P->P | BROADCAST  |
|* 19 |             HASH JOIN                                        |                             |   298 |   106K| 15009   (2)| 00:03:31 |       |       |  Q2,14 | PCWP |            |
|  20 |              PX RECEIVE                                      |                             |   298 | 84632 | 14984   (2)| 00:03:30 |       |       |  Q2,14 | PCWP |            |
|  21 |               PX SEND BROADCAST                              | :TQ20012                    |   298 | 84632 | 14984   (2)| 00:03:30 |       |       |  Q2,12 | P->P | BROADCAST  |
|* 22 |                HASH JOIN                                     |                             |   298 | 84632 | 14984   (2)| 00:03:30 |       |       |  Q2,12 | PCWP |            |
|  23 |                 PX RECEIVE                                   |                             |   298 | 51554 | 14956   (2)| 00:03:30 |       |       |  Q2,12 | PCWP |            |
|  24 |                  PX SEND BROADCAST                           | :TQ20010                    |   298 | 51554 | 14956   (2)| 00:03:30 |       |       |  Q2,10 | P->P | BROADCAST  |
|* 25 |                   HASH JOIN                                  |                             |   298 | 51554 | 14956   (2)| 00:03:30 |       |       |  Q2,10 | PCWP |            |
|  26 |                    PX RECEIVE                                |                             |   298 | 48276 | 14950   (2)| 00:03:30 |       |       |  Q2,10 | PCWP |            |
|  27 |                     PX SEND BROADCAST                        | :TQ20007                    |   298 | 48276 | 14950   (2)| 00:03:30 |       |       |  Q2,07 | P->P | BROADCAST  |
|* 28 |                      HASH JOIN BUFFERED                      |                             |   298 | 48276 | 14950   (2)| 00:03:30 |       |       |  Q2,07 | PCWP |            |
|  29 |                       PX JOIN FILTER CREATE                  | :BF0000                     |   298 | 34270 | 14944   (2)| 00:03:30 |       |       |  Q2,07 | PCWP |            |
|  30 |                        PX RECEIVE                            |                             |   298 | 34270 | 14944   (2)| 00:03:30 |       |       |  Q2,07 | PCWP |            |
|  31 |                         PX SEND HASH                         | :TQ20002                    |   298 | 34270 | 14944   (2)| 00:03:30 |       |       |  Q2,02 | P->P | HASH       |
|* 32 |                          HASH JOIN                           |                             |   298 | 34270 | 14944   (2)| 00:03:30 |       |       |  Q2,02 | PCWP |            |
|  33 |                           PX RECEIVE                         |                             |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|  34 |                            PX SEND BROADCAST                 | :TQ20000                    |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,00 | P->P | BROADCAST  |
|  35 |                             PX BLOCK ITERATOR                |                             |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,00 | PCWC |            |
|* 36 |                              TABLE ACCESS FULL               | DIM_EMPLOYEE_ROLE           |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
|* 37 |                           HASH JOIN                          |                             |   895 | 93080 | 14938   (2)| 00:03:30 |       |       |  Q2,02 | PCWP |            |
|  38 |                            PX RECEIVE                        |                             |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|  39 |                             PX SEND BROADCAST                | :TQ20001                    |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,01 | P->P | BROADCAST  |
|  40 |                              PX BLOCK ITERATOR               |                             |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,01 | PCWC |            |
|* 41 |                               TABLE ACCESS FULL              | DIM_DATE                    |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
|  42 |                            PX PARTITION RANGE ALL            |                             |  6179 |   470K| 14926   (2)| 00:03:29 |     1 |    26 |  Q2,02 | PCWC |            |
|* 43 |                             TABLE ACCESS BY LOCAL INDEX ROWID| FACT_EMPLOYEE_ORDER         |  6179 |   470K| 14926   (2)| 00:03:29 |     1 |    26 |  Q2,02 | PCWP |            |
|  44 |                              BITMAP CONVERSION TO ROWIDS     |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  45 |                               BITMAP AND                     |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  46 |                                BITMAP MERGE                  |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  47 |                                 BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  48 |                                  BUFFER SORT                 |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|* 49 |                                   TABLE ACCESS FULL          | DIM_EMPLOYEE_ROLE           |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|* 50 |                                  BITMAP INDEX RANGE SCAN     | XIF35FACT_EMPLOYEE_ORDER    |       |       |            |          |     1 |    26 |  Q2,02 | PCWP |            |
|  51 |                                BITMAP MERGE                  |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  52 |                                 BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  53 |                                  BUFFER SORT                 |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|* 54 |                                   TABLE ACCESS FULL          | DIM_PRODUCT_HIERARCHY       |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|* 55 |                                  BITMAP INDEX RANGE SCAN     | XIF26FACT_EMPLOYEE_ORDER    |       |       |            |          |     1 |    26 |  Q2,02 | PCWP |            |
|  56 |                                BITMAP MERGE                  |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  57 |                                 BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  58 |                                  BUFFER SORT                 |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|* 59 |                                   TABLE ACCESS FULL          | DIM_DATE                    |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|  60 |                                  BITMAP CONVERSION FROM ROWID|                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|* 61 |                                   INDEX RANGE SCAN           | XIF13FACT_EMPLOYEE_ORDER    |       |       |   265   (1)| 00:00:04 |     1 |    26 |  Q2,02 | PCWP |            |
|  62 |                                BITMAP MERGE                  |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  63 |                                 BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  64 |                                  BUFFER SORT                 |                             |       |       |            |          |       |       |  Q2,02 | PCWP |            |
|  65 |                                   TABLE ACCESS FULL          | SYS_TEMP_0FD9D663D_4091BC55 |     1 |    13 |     2   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
|* 66 |                                  BITMAP INDEX RANGE SCAN     | XIF34FACT_EMPLOYEE_ORDER    |       |       |            |          |     1 |    26 |  Q2,02 | PCWP |            |
|  67 |                       PX RECEIVE                             |                             |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,07 | PCWP |            |
|  68 |                        PX SEND HASH                          | :TQ20003                    |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,03 | P->P | HASH       |
|  69 |                         PX JOIN FILTER USE                   | :BF0000                     |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,03 | PCWP |            |
|  70 |                          PX BLOCK ITERATOR                   |                             |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,03 | PCWC |            |
|* 71 |                           TABLE ACCESS FULL                  | DIM_PRODUCT_HIERARCHY       |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,03 | PCWP |            |
|  72 |                    PX BLOCK ITERATOR                         |                             | 13200 |   141K|     6   (0)| 00:00:01 |       |       |  Q2,10 | PCWC |            |
|  73 |                     TABLE ACCESS FULL                        | DIM_SOURCE_SYSTEM           | 13200 |   141K|     6   (0)| 00:00:01 |       |       |  Q2,10 | PCWP |            |
|  74 |                 PX BLOCK ITERATOR                            |                             | 20810 |  2255K|    27   (0)| 00:00:01 |       |       |  Q2,12 | PCWC |            |
|  75 |                  TABLE ACCESS FULL                           | DIM_TEAM                    | 20810 |  2255K|    27   (0)| 00:00:01 |       |       |  Q2,12 | PCWP |            |
|  76 |              PX BLOCK ITERATOR                               |                             | 48785 |  3906K|    25   (0)| 00:00:01 |       |       |  Q2,14 | PCWC |            |
|  77 |               TABLE ACCESS FULL                              | SYS_TEMP_0FD9D663D_4091BC55 | 48785 |  3906K|    25   (0)| 00:00:01 |       |       |  Q2,14 | PCWP |            |
|  78 |           PX BLOCK ITERATOR                                  |                             |   130K|    12M|   278   (2)| 00:00:04 |       |       |  Q2,16 | PCWC |            |
|  79 |            TABLE ACCESS FULL                                 | DIM_FINANCIAL_HIERARCHY     |   130K|    12M|   278   (2)| 00:00:04 |       |       |  Q2,16 | PCWP |            |
|* 80 |       FILTER                                                 |                             |       |       |            |          |       |       |  Q2,18 | PCWC |            |
|  81 |        SORT GROUP BY                                         |                             |  1532 |   755K| 31440   (1)| 00:07:21 |       |       |  Q2,18 | PCWP |            |
|  82 |         PX RECEIVE                                           |                             |  3451 |  1701K| 31439   (1)| 00:07:21 |       |       |  Q2,18 | PCWP |            |
|  83 |          PX SEND HASH                                        | :TQ20017                    |  3451 |  1701K| 31439   (1)| 00:07:21 |       |       |  Q2,17 | P->P | HASH       |
|* 84 |           HASH JOIN                                          |                             |  3451 |  1701K| 31439   (1)| 00:07:21 |       |       |  Q2,17 | PCWP |            |
|  85 |            PX RECEIVE                                        |                             |  3451 |  1391K| 29225   (1)| 00:06:50 |       |       |  Q2,17 | PCWP |            |
|  86 |             PX SEND BROADCAST                                | :TQ20015                    |  3451 |  1391K| 29225   (1)| 00:06:50 |       |       |  Q2,15 | P->P | BROADCAST  |
|* 87 |              HASH JOIN                                       |                             |  3451 |  1391K| 29225   (1)| 00:06:50 |       |       |  Q2,15 | PCWP |            |
|  88 |               PX RECEIVE                                     |                             |  3451 |  1044K| 28946   (1)| 00:06:46 |       |       |  Q2,15 | PCWP |            |
|  89 |                PX SEND BROADCAST                             | :TQ20013                    |  3451 |  1044K| 28946   (1)| 00:06:46 |       |       |  Q2,13 | P->P | BROADCAST  |
|* 90 |                 HASH JOIN                                    |                             |  3451 |  1044K| 28946   (1)| 00:06:46 |       |       |  Q2,13 | PCWP |            |
|  91 |                  PX BLOCK ITERATOR                           |                             | 20810 |  2255K|    27   (0)| 00:00:01 |       |       |  Q2,13 | PCWC |            |
|  92 |                   TABLE ACCESS FULL                          | DIM_TEAM                    | 20810 |  2255K|    27   (0)| 00:00:01 |       |       |  Q2,13 | PCWP |            |
|  93 |                  BUFFER SORT                                 |                             |       |       |            |          |       |       |  Q2,13 | PCWC |            |
|  94 |                   PX RECEIVE                                 |                             |  3451 |   670K| 28918   (1)| 00:06:45 |       |       |  Q2,13 | PCWP |            |
|  95 |                    PX SEND BROADCAST                         | :TQ20011                    |  3451 |   670K| 28918   (1)| 00:06:45 |       |       |  Q2,11 | P->P | BROADCAST  |
|* 96 |                     HASH JOIN BUFFERED                       |                             |  3451 |   670K| 28918   (1)| 00:06:45 |       |       |  Q2,11 | PCWP |            |
|  97 |                      PX JOIN FILTER CREATE                   | :BF0001                     | 13200 |   141K|     6   (0)| 00:00:01 |       |       |  Q2,11 | PCWP |            |
|  98 |                       PX RECEIVE                             |                             | 13200 |   141K|     6   (0)| 00:00:01 |       |       |  Q2,11 | PCWP |            |
|  99 |                        PX SEND HASH                          | :TQ20008                    | 13200 |   141K|     6   (0)| 00:00:01 |       |       |  Q2,08 | P->P | HASH       |
| 100 |                         PX BLOCK ITERATOR                    |                             | 13200 |   141K|     6   (0)| 00:00:01 |       |       |  Q2,08 | PCWC |            |
| 101 |                          TABLE ACCESS FULL                   | DIM_SOURCE_SYSTEM           | 13200 |   141K|     6   (0)| 00:00:01 |       |       |  Q2,08 | PCWP |            |
| 102 |                      PX RECEIVE                              |                             |  3451 |   633K| 28912   (1)| 00:06:45 |       |       |  Q2,11 | PCWP |            |
| 103 |                       PX SEND HASH                           | :TQ20009                    |  3451 |   633K| 28912   (1)| 00:06:45 |       |       |  Q2,09 | P->P | HASH       |
| 104 |                        PX JOIN FILTER USE                    | :BF0001                     |  3451 |   633K| 28912   (1)| 00:06:45 |       |       |  Q2,09 | PCWP |            |
|*105 |                         HASH JOIN                            |                             |  3451 |   633K| 28912   (1)| 00:06:45 |       |       |  Q2,09 | PCWP |            |
| 106 |                          PX RECEIVE                          |                             |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,09 | PCWP |            |
| 107 |                           PX SEND BROADCAST                  | :TQ20004                    |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,04 | P->P | BROADCAST  |
| 108 |                            PX BLOCK ITERATOR                 |                             |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,04 | PCWC |            |
|*109 |                             TABLE ACCESS FULL                | DIM_PRODUCT_HIERARCHY       |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
|*110 |                          HASH JOIN                           |                             |  3452 |   475K| 28906   (1)| 00:06:45 |       |       |  Q2,09 | PCWP |            |
| 111 |                           PX RECEIVE                         |                             |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,09 | PCWP |            |
| 112 |                            PX SEND BROADCAST                 | :TQ20005                    |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,05 | P->P | BROADCAST  |
| 113 |                             PX BLOCK ITERATOR                |                             |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,05 | PCWC |            |
|*114 |                              TABLE ACCESS FULL               | DIM_DATE                    |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,05 | PCWP |            |
|*115 |                           HASH JOIN                          |                             | 23843 |  2677K| 28894   (1)| 00:06:45 |       |       |  Q2,09 | PCWP |            |
| 116 |                            PX RECEIVE                        |                             |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,09 | PCWP |            |
| 117 |                             PX SEND BROADCAST                | :TQ20006                    |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,06 | P->P | BROADCAST  |
| 118 |                              PX BLOCK ITERATOR               |                             |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,06 | PCWC |            |
|*119 |                               TABLE ACCESS FULL              | DIM_EMPLOYEE_ROLE           |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,06 | PCWP |            |
| 120 |                            PX PARTITION RANGE ALL            |                             | 71528 |  7264K| 28888   (1)| 00:06:45 |     1 |    26 |  Q2,09 | PCWC |            |
|*121 |                             TABLE ACCESS BY LOCAL INDEX ROWID| FACT_EMPLOYEE_ORDER         | 71528 |  7264K| 28888   (1)| 00:06:45 |     1 |    26 |  Q2,09 | PCWP |            |
| 122 |                              BITMAP CONVERSION TO ROWIDS     |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
| 123 |                               BITMAP AND                     |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
| 124 |                                BITMAP MERGE                  |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
| 125 |                                 BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
| 126 |                                  BUFFER SORT                 |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
|*127 |                                   TABLE ACCESS FULL          | DIM_EMPLOYEE_ROLE           |     1 |    11 |     6   (0)| 00:00:01 |       |       |  Q2,09 | PCWP |            |
|*128 |                                  BITMAP INDEX RANGE SCAN     | XIF35FACT_EMPLOYEE_ORDER    |       |       |            |          |     1 |    26 |  Q2,09 | PCWP |            |
| 129 |                                BITMAP MERGE                  |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
| 130 |                                 BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
| 131 |                                  BUFFER SORT                 |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
|*132 |                                   TABLE ACCESS FULL          | DIM_PRODUCT_HIERARCHY       |   802 | 37694 |     6   (0)| 00:00:01 |       |       |  Q2,09 | PCWP |            |
|*133 |                                  BITMAP INDEX RANGE SCAN     | XIF26FACT_EMPLOYEE_ORDER    |       |       |            |          |     1 |    26 |  Q2,09 | PCWP |            |
| 134 |                                BITMAP MERGE                  |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
| 135 |                                 BITMAP KEY ITERATION         |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
| 136 |                                  BUFFER SORT                 |                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
|*137 |                                   TABLE ACCESS FULL          | DIM_DATE                    |   120 |  3120 |    11   (0)| 00:00:01 |       |       |  Q2,09 | PCWP |            |
| 138 |                                  BITMAP CONVERSION FROM ROWID|                             |       |       |            |          |       |       |  Q2,09 | PCWP |            |
|*139 |                                   INDEX RANGE SCAN           | XIF13FACT_EMPLOYEE_ORDER    | 92814 |       |   265   (1)| 00:00:04 |     1 |    26 |  Q2,09 | PCWP |            |
| 140 |               PX BLOCK ITERATOR                              |                             |   130K|    12M|   278   (2)| 00:00:04 |       |       |  Q2,15 | PCWC |            |
| 141 |                TABLE ACCESS FULL                             | DIM_FINANCIAL_HIERARCHY     |   130K|    12M|   278   (2)| 00:00:04 |       |       |  Q2,15 | PCWP |            |
| 142 |            PX BLOCK ITERATOR                                 |                             |   632K|    55M|  2213   (1)| 00:00:31 |       |       |  Q2,17 | PCWC |            |
|*143 |             TABLE ACCESS FULL                                | DIM_CLIENT                  |   632K|    55M|  2213   (1)| 00:00:31 |       |       |  Q2,17 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Re: what to do about this one !!! [message #190372 is a reply to message #190353] Wed, 30 August 2006 07:06 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Why do you need the UNION ALL? The only difference between the first half and the second half is:

- AND dim_client.sector_l1_3_nm = 'CORPORATES'
+ AND dim_client.sector_l1_3_nm != 'CORPORATES'

Why don't you just drop the second half and replace the clause in the top half with:

AND dim_client.sector_l1_3_nm IS NOT NULL

Re: what to do about this one !!! [message #190374 is a reply to message #190343] Wed, 30 August 2006 07:16 Go to previous messageGo to next message
seemit
Messages: 7
Registered: July 2006
Junior Member
Because there is a HAVING clause in lower half. I want ALL CORPORATES irrespective of their CVA value (upper half) and all NON CORPORATES HAVING CVA >=25 K. Is there anyway to do it?
Re: what to do about this one !!! [message #190378 is a reply to message #190374] Wed, 30 August 2006 07:23 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Give a dynamic view a shot, how about something like this?

SELECT *
  FROM (SELECT   dim_client.nm_cmu_4
                ,dim_client.nm_cmu_3
                ,dim_client.nm_cmu_2
                ,dim_client.nm_cmu_1
                ,dim_client.franchise_l1_1_nm
                ,fact_employee_order.feed_leading_source_system_id
                ,dim_source_system.nm_leading_source_system
                ,fact_employee_order.artemis_source_system_id
                ,dim_date.num_year
                ,dim_date.nm_abbr_month
                ,dim_team.team_level_6_nm
                ,dim_team.team_level_5_nm
                ,dim_team.team_level_4_nm
                ,dim_team.team_level_3_nm
                ,dim_team.team_level_2_nm
                ,dim_team.team_level_1_nm
                ,dim_product_hierarchy.product_level_4_nm
                ,dim_product_hierarchy.product_level_3_nm
                ,dim_product_hierarchy.product_level_2_nm
                ,dim_product_hierarchy.product_level_1_nm
                ,dim_trading_desk.nm_ou_abbr
                ,dim_trading_desk.id_ou
                ,dim_trading_desk.nm_le
                ,dim_trading_desk.id_le
                ,dim_trading_desk.nm_level_9
                ,dim_trading_desk.nm_level_8
                ,dim_trading_desk.nm_level_7
                ,dim_trading_desk.nm_level_6
                ,dim_trading_desk.nm_level_5
                ,dim_trading_desk.summ_pt_level_9
                ,dim_trading_desk.summ_pt_level_8
                ,dim_trading_desk.summ_pt_level_7
                ,dim_trading_desk.summ_pt_level_6
                ,dim_trading_desk.summ_pt_level_5
                ,dim_date.dt_date
                ,dim_employee_role.nm_employee_role
                ,dim_team.team_level_0_nm
                ,dim_client.sector_l1_1_nm
                ,SUM (fact_employee_order.am_net_cva_delta) net_cva
                ,SUM (fact_employee_order.am_net_cva_asgn_delta) a_net_cva
                ,SUM (fact_employee_order.am_net_cva_x_role_delta) x_net_cva
                ,SUM (fact_employee_order.am_net_cva_role_delta) f_net_cva
                ,dim_client.spn_id
            FROM fact_employee_order
                ,dim_client
                ,dim_team
                ,dim_employee_role
                ,dim_product_hierarchy
                ,dim_source_system
                ,dim_date
                ,dim_financial_hierarchy dim_trading_desk
           WHERE fact_employee_order.id_source_system =
                                            dim_source_system.id_source_system
             AND fact_employee_order.id_client = dim_client.id_client
             AND fact_employee_order.id_employee_role =
                                            dim_employee_role.id_employee_role
             AND fact_employee_order.id_le_ou_trading_desk =
                                                     dim_trading_desk.id_le_ou
             AND dim_product_hierarchy.id_instrument_product =
                                     fact_employee_order.id_instrument_product
             AND dim_team.id_team = fact_employee_order.id_team
             AND fact_employee_order.id_reporting_date = dim_date.id_date
             AND ind_to_be_deleted = 'N'
             AND ind_is_reportable = 'Y'
             AND dim_product_hierarchy.product_level_2_nm IN
                    ('EQ OTC DERIV'
                    ,'OTHER CR EXO/HYB'
                    ,'SYNTH ETIC CDO - INV'
                    ,'FX SPOT'
                    ,'SOFTS'
                    ,'STR ALTERN INVEST'
                    ,'PRECIOUS METALS'
                    ,'FX SWAP'
                    ,'GAS'
                    ,'IRD SWAPS'
                    ,'IRD EXO/HYB'
                    ,'FX EXOTIC OPTIONS'
                    ,'POWER'
                    ,'TRANCHE'
                    ,'FX HYBRID OPTIONS'
                    ,'EQ SECURIT DERIV'
                    ,'EMISSIONS'
                    ,'FX OPTIONS'
                    ,'FX FORWARD'
                    ,'OIL'
                    ,'IRD OPTIONS'
                    ,'FTD'
                    ,'BASE METALS'
                    ,'SYNTHETIC CDO - MGR'
                    ,'COAL'
                    )
             AND dim_date.year_month BETWEEN 200601 AND 200607
             AND dim_employee_role.nm_employee_role = 'SALES'
             AND dim_client.sector_l1_3_nm IS NOT NULL
        GROUP BY dim_client.nm_cmu_4
                ,dim_client.nm_cmu_3
                ,dim_client.nm_cmu_2
                ,dim_client.nm_cmu_1
                ,dim_client.franchise_l1_1_nm
                ,fact_employee_order.feed_leading_source_system_id
                ,dim_source_system.nm_leading_source_system
                ,fact_employee_order.artemis_source_system_id
                ,dim_date.num_year
                ,dim_date.nm_abbr_month
                ,dim_team.team_level_6_nm
                ,dim_team.team_level_5_nm
                ,dim_team.team_level_4_nm
                ,dim_team.team_level_3_nm
                ,dim_team.team_level_2_nm
                ,dim_team.team_level_1_nm
                ,dim_product_hierarchy.product_level_4_nm
                ,dim_product_hierarchy.product_level_3_nm
                ,dim_product_hierarchy.product_level_2_nm
                ,dim_product_hierarchy.product_level_1_nm
                ,dim_trading_desk.nm_ou_abbr
                ,dim_trading_desk.id_ou
                ,dim_trading_desk.nm_le
                ,dim_trading_desk.id_le
                ,dim_trading_desk.nm_level_9
                ,dim_trading_desk.nm_level_8
                ,dim_trading_desk.nm_level_7
                ,dim_trading_desk.nm_level_6
                ,dim_trading_desk.nm_level_5
                ,dim_trading_desk.summ_pt_level_9
                ,dim_trading_desk.summ_pt_level_8
                ,dim_trading_desk.summ_pt_level_7
                ,dim_trading_desk.summ_pt_level_6
                ,dim_trading_desk.summ_pt_level_5
                ,dim_date.dt_date
                ,dim_employee_role.nm_employee_role
                ,dim_team.team_level_0_nm
                ,dim_client.sector_l1_1_nm
                ,dim_client.spn_id)
 WHERE (    dim_client.sector_l1_3_nm != 'CORPORATES'
        AND net_cva_delta >= 250000
       )
    OR dim_client.sector_l1_3_nm = 'CORPORATES'


Full scans are not evil, and in this case many of them seem reasonable. It would be a good idea to eliminate the second half of the query and the buffer sort. Give the query above a shot and post a new plan, let us know if it improved or not. I believe the results should be the same.

[Updated on: Wed, 30 August 2006 07:32]

Report message to a moderator

Re: what to do about this one !!! [message #190381 is a reply to message #190378] Wed, 30 August 2006 07:30 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
BTW, I should point out that the full scan on DIM_CLIENT is actually cheaper than hash joining the 632K rows.
Re: what to do about this one !!! [message #190384 is a reply to message #190378] Wed, 30 August 2006 07:46 Go to previous messageGo to next message
seemit
Messages: 7
Registered: July 2006
Junior Member
In the query you modified, refer to following portion
WHERE (    dim_client.sector_l1_3_nm != 'CORPORATES'
        AND net_cva_delta >= 250000
       )
<--- i put even this one in CODE tags.

The where clause in braces will not detect table.column as there is no FROM in it.
Moreover even if it did, i wanted a sum(NET_CVA_DELTA) which ofcourse wont be possible after a where clause.
Re: what to do about this one !!! [message #190385 is a reply to message #190384] Wed, 30 August 2006 07:48 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
My expectation is that you know enough to figure it out. Clearly I don't have the tables to validate syntax errors, so some of the items were overlooked.

It is evaluated based the SUM of the column, but I guess you do not understand my advice.
Re: what to do about this one !!! [message #190389 is a reply to message #190385] Wed, 30 August 2006 07:58 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
I would recommend reading up on dynamic views, just because you do not understand the suggestion doesn't mean it will not work.
Re: what to do about this one !!! [message #190393 is a reply to message #190343] Wed, 30 August 2006 08:31 Go to previous message
seemit
Messages: 7
Registered: July 2006
Junior Member
am sorry that I goofed up !
I totally understand the logic behind your query. Had to add a column in the dynamic view to make it work. here is the modified one
SELECT *
  FROM (SELECT  dim_client.sector_l1_3_nm sector 
  	   			,dim_client.nm_cmu_4
                ,dim_client.nm_cmu_3
                ,dim_client.nm_cmu_2
                ,dim_client.nm_cmu_1
                ,dim_client.franchise_l1_1_nm
                ,fact_employee_order.feed_leading_source_system_id
                ,dim_source_system.nm_leading_source_system
                ,fact_employee_order.artemis_source_system_id
                ,dim_date.num_year
                ,dim_date.nm_abbr_month
                ,dim_team.team_level_6_nm
                ,dim_team.team_level_5_nm
                ,dim_team.team_level_4_nm
                ,dim_team.team_level_3_nm
                ,dim_team.team_level_2_nm
                ,dim_team.team_level_1_nm
                ,dim_product_hierarchy.product_level_4_nm
                ,dim_product_hierarchy.product_level_3_nm
                ,dim_product_hierarchy.product_level_2_nm
                ,dim_product_hierarchy.product_level_1_nm
                ,dim_trading_desk.nm_ou_abbr
                ,dim_trading_desk.id_ou
                ,dim_trading_desk.nm_le
                ,dim_trading_desk.id_le
                ,dim_trading_desk.nm_level_9
                ,dim_trading_desk.nm_level_8
                ,dim_trading_desk.nm_level_7
                ,dim_trading_desk.nm_level_6
                ,dim_trading_desk.nm_level_5
                ,dim_trading_desk.summ_pt_level_9
                ,dim_trading_desk.summ_pt_level_8
                ,dim_trading_desk.summ_pt_level_7
                ,dim_trading_desk.summ_pt_level_6
                ,dim_trading_desk.summ_pt_level_5
                ,dim_date.dt_date
                ,dim_employee_role.nm_employee_role
                ,dim_team.team_level_0_nm
                ,dim_client.sector_l1_1_nm
                ,SUM (fact_employee_order.am_net_cva_delta) net_cva
                ,SUM (fact_employee_order.am_net_cva_asgn_delta) a_net_cva
                ,SUM (fact_employee_order.am_net_cva_x_role_delta) x_net_cva
                ,SUM (fact_employee_order.am_net_cva_role_delta) f_net_cva
                ,dim_client.spn_id
            FROM fact_employee_order
                ,dim_client
                ,dim_team
                ,dim_employee_role
                ,dim_product_hierarchy
                ,dim_source_system
                ,dim_date
                ,dim_financial_hierarchy dim_trading_desk
           WHERE fact_employee_order.id_source_system = dim_source_system.id_source_system
             AND fact_employee_order.id_client = dim_client.id_client
             AND fact_employee_order.id_employee_role = dim_employee_role.id_employee_role
             AND fact_employee_order.id_le_ou_trading_desk = dim_trading_desk.id_le_ou
             AND dim_product_hierarchy.id_instrument_product = fact_employee_order.id_instrument_product
             AND dim_team.id_team = fact_employee_order.id_team
             AND fact_employee_order.id_reporting_date = dim_date.id_date
             AND ind_to_be_deleted = 'N'
             AND ind_is_reportable = 'Y'
             AND dim_product_hierarchy.product_level_2_nm IN
                    ('EQ OTC DERIV'
                    ,'OTHER CR EXO/HYB'
                    ,'SYNTH ETIC CDO - INV'
                    ,'FX SPOT'
                    ,'SOFTS'
                    ,'STR ALTERN INVEST'
                    ,'PRECIOUS METALS'
                    ,'FX SWAP'
                    ,'GAS'
                    ,'IRD SWAPS'
                    ,'IRD EXO/HYB'
                    ,'FX EXOTIC OPTIONS'
                    ,'POWER'
                    ,'TRANCHE'
                    ,'FX HYBRID OPTIONS'
                    ,'EQ SECURIT DERIV'
                    ,'EMISSIONS'
                    ,'FX OPTIONS'
                    ,'FX FORWARD'
                    ,'OIL'
                    ,'IRD OPTIONS'
                    ,'FTD'
                    ,'BASE METALS'
                    ,'SYNTHETIC CDO - MGR'
                    ,'COAL'
                    )
             AND dim_date.year_month BETWEEN 200601 AND 200607
             AND dim_employee_role.nm_employee_role = 'SALES'
             AND dim_client.sector_l1_3_nm IS NOT NULL
        GROUP BY dim_client.sector_l1_3_nm
				,dim_client.nm_cmu_4
                ,dim_client.nm_cmu_3
                ,dim_client.nm_cmu_2
                ,dim_client.nm_cmu_1
                ,dim_client.franchise_l1_1_nm
                ,fact_employee_order.feed_leading_source_system_id
                ,dim_source_system.nm_leading_source_system
                ,fact_employee_order.artemis_source_system_id
                ,dim_date.num_year
                ,dim_date.nm_abbr_month
                ,dim_team.team_level_6_nm
                ,dim_team.team_level_5_nm
                ,dim_team.team_level_4_nm
                ,dim_team.team_level_3_nm
                ,dim_team.team_level_2_nm
                ,dim_team.team_level_1_nm
                ,dim_product_hierarchy.product_level_4_nm
                ,dim_product_hierarchy.product_level_3_nm
                ,dim_product_hierarchy.product_level_2_nm
                ,dim_product_hierarchy.product_level_1_nm
                ,dim_trading_desk.nm_ou_abbr
                ,dim_trading_desk.id_ou
                ,dim_trading_desk.nm_le
                ,dim_trading_desk.id_le
                ,dim_trading_desk.nm_level_9
                ,dim_trading_desk.nm_level_8
                ,dim_trading_desk.nm_level_7
                ,dim_trading_desk.nm_level_6
                ,dim_trading_desk.nm_level_5
                ,dim_trading_desk.summ_pt_level_9
                ,dim_trading_desk.summ_pt_level_8
                ,dim_trading_desk.summ_pt_level_7
                ,dim_trading_desk.summ_pt_level_6
                ,dim_trading_desk.summ_pt_level_5
                ,dim_date.dt_date
                ,dim_employee_role.nm_employee_role
                ,dim_team.team_level_0_nm
                ,dim_client.sector_l1_1_nm
                ,dim_client.spn_id) ABC
WHere ( abc.net_cva >=25000 and
				abc.sector != 'CORPORATES' ) 
				    OR abc.sector = 'CORPORATES'


I wonder if its gonna be same.

Explain plan seems attractive.

Once again, Sorry I didnt even look at what you had pasted and posted something back for it. and Yes, I need to study lots of stuff bout Oracle, still in a newbee category. Thanks a ton for your help. I have fired the query up and will update you as soon as this gives same result.
Previous Topic: Oracle 9i redo logging performance poor in relation to DB2 on Solaris. Please help.
Next Topic: when was COST optimizer introduced ?
Goto Forum:
  


Current Time: Sat Dec 03 08:08:17 CST 2016

Total time taken to generate the page: 0.13449 seconds