Dear sir,

I have got big sql as, prepared the explain for the sql, need help in understanding the explain plan.

thanks

SQL:

WITH T1 AS
(SELECT S.ACCOUNT_NUMBER,
S.CS_PS_IND,
S.CUSTOMER_TN_STATUS,
S.DWELLING_TYPE,
S.DWELLING_TYPE_DESC,
S.SERVICE_CODE,
S.SERVICE_OCCURRENCE,
S.SITE_ID,
S.TN
FROM CDT_SERVICES S
),
T2 AS
(SELECT DISTINCT SW.*,
PROV.SERVICE_CODE
FROM
(SELECT
/*+ parallel (A,2) Parallel(B,2) */
A.ACCOUNT_NUMBER,
A.CS_PS_IND,
A.CUSTOMER_TN_STATUS,
A.DWELLING_TYPE,
A.DWELLING_TYPE_DESC,
A.EXIST_IN_ICOMS,
A.EXIST_IN_SWITCH,
A.SITE_ID,
B.*
FROM CDT_SERVICES A,
(SELECT TN, FEATURES, 'BTS' AS SWITCH_TYPE FROM IMS_BTS_TN_FEATURES
UNION ALL
SELECT TN,
CASE
WHEN FEATURES IN ('DRING', 'DRCW')
THEN 'DRCW'
WHEN FEATURES IN ('CWT', 'CWTACT', 'CWI')
THEN 'CWT'
WHEN FEATURES IN ('LDSA', 'LDSO')
THEN 'LDSA'
ELSE FEATURES
END AS FEATURES,
'CS2K' AS SWITCH_TYPE
FROM IMS_DMS_TN_FEATURES
) B
WHERE A.TN = B.TN
) SW,
(SELECT SITE_ID,
SERVICE_CODE,
CASE
WHEN FEATURE_TYPE IN ('CFDA', 'CFBL')
THEN 'CFW'
WHEN FEATURE_TYPE IN ('CWT', 'CWTACT', 'CWI')
THEN 'CWT'
WHEN FEATURE_TYPE IN ('LDSA', 'LDSO')
THEN 'LDSA'
WHEN FEATURE_TYPE IN ('SC1')
THEN 'SCS'
WHEN FEATURE_TYPE IN ('SC2')
THEN 'SCL'
WHEN FEATURE_TYPE IN ('DRING', 'DRCW')
THEN 'DRCW'
ELSE FEATURE_TYPE
END AS FEATURE_TYPE
FROM REF_DMS_SERV_CD_FEAT_TRANSL
WHERE FEATURE_TYPE NOT LIKE '%DEACT'
AND FEATURE_TYPE NOT IN ('VM', 'AINDN', 'CFGDA', 'CFRA', 'CID', 'CIR', 'DNYCWT', 'LCC', 'LCDR', 'LOD', 'PORT', 'PUBLIC')
UNION ALL
SELECT SITE_ID,
SERVICE_CODE,
CASE
WHEN FEATURE IN ('CFBVA', 'CFBVD')
THEN 'CFB'
WHEN FEATURE IN ('CFNAVA', 'CFNAVD')
THEN 'CFNA'
ELSE FEATURE
END AS FEATURE
FROM REF_BTS_SERV_CD_FEAT_TRANSL
WHERE FEATURE NOT LIKE '%DEACT'
AND FEATURE NOT IN ('SCA_DEACT', 'SCF_DEACT', 'SCR_DEACT', 'VM', 'VM_ACT', 'VM_DEACT', 'VMA', 'VMA_ACT', 'VMA_DEACT', 'WARMLINE')
) PROV
WHERE SW.SITE_ID = PROV.SITE_ID
AND SW.FEATURES = PROV.FEATURE_TYPE
AND SW.FEATURES NOT IN ('PIC', 'LPIC')
)
SELECT ACCOUNT_NUMBER,
CS_PS_IND,
CUSTOMER_TN_STATUS,
DWELLING_TYPE,
DWELLING_TYPE_DESC,
EXIST_IN_ICOMS,
EXIST_IN_SWITCH,
FEATURES,
ICOMS_SERVICE_CODE,
SERVICE_CODE,
SITE_ID,
SWITCH_TYPE,
TN
FROM
(SELECT
/*+ parallel (T1,2) Parallel(T2,2) */
DISTINCT T2.*,
T1.SERVICE_CODE AS ICOMS_SERVICE_CODE,
DENSE_RANK () OVER (PARTITION BY T2.TN, T2.FEATURES ORDER BY T1.SERVICE_CODE, T2.SERVICE_CODE) RK
FROM T1,
T2
WHERE T2.TN = T1.TN(+)
AND T2.SITE_ID = T1.SITE_ID(+)
AND T2.SERVICE_CODE = T1.SERVICE_CODE(+)
)
WHERE RK = 1;

Explain plan:

Plan hash value: 3770834883

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53G| 201T| | 21G (1)|999:59:59 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10008 | 53G| 201T| | 21G (1)|999:59:59 | Q1,08 | P->S | QC (RAND) |
|* 3 | VIEW | | 53G| 201T| | 21G (1)|999:59:59 | Q1,08 | PCWP | |
| 4 | HASH UNIQUE | | 53G| 102T| 133T| 21G (1)|999:59:59 | Q1,08 | PCWP | |
|* 5 | WINDOW SORT PUSHED RANK | | 53G| 102T| 133T| 21G (1)|999:59:59 | Q1,08 | PCWP | |
| 6 | PX RECEIVE | | 53G| 102T| | 21G (1)|999:59:59 | Q1,08 | PCWP | |
| 7 | PX SEND HASH | :TQ10007 | 53G| 102T| | 21G (1)|999:59:59 | Q1,07 | P->P | HASH |
|* 8 | WINDOW CHILD PUSHED RANK | | 53G| 102T| | 21G (1)|999:59:59 | Q1,07 | PCWP | |
|* 9 | HASH JOIN RIGHT OUTER | | 53G| 102T| | 6051M (1)|999:59:59 | Q1,07 | PCWP | |
| 10 | PX RECEIVE | | 10M| 226M| | 31796 (1)| 00:06:22 | Q1,07 | PCWP | |
| 11 | PX SEND HASH | :TQ10005 | 10M| 226M| | 31796 (1)| 00:06:22 | Q1,05 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 10M| 226M| | 31796 (1)| 00:06:22 | Q1,05 | PCWC | |
| 13 | TABLE ACCESS STORAGE FULL | CDT_SERVICES | 10M| 226M| | 31796 (1)| 00:06:22 | Q1,05 | PCWP | |
| 14 | PX RECEIVE | | 53G| 101T| | 6051M (1)|999:59:59 | Q1,07 | PCWP | |
| 15 | PX SEND HASH | :TQ10006 | 53G| 101T| | 6051M (1)|999:59:59 | Q1,06 | P->P | HASH |
| 16 | VIEW | | 53G| 101T| | 6051M (1)|999:59:59 | Q1,06 | PCWP | |
| 17 | HASH UNIQUE | | 53G| 150T| 200T| 6051M (1)|999:59:59 | Q1,06 | PCWP | |
| 18 | PX RECEIVE | | 53G| 150T| | 6051M (1)|999:59:59 | Q1,06 | PCWP | |
| 19 | PX SEND HASH | :TQ10004 | 53G| 150T| | 6051M (1)|999:59:59 | Q1,04 | P->P | HASH |
| 20 | HASH UNIQUE | | 53G| 150T| 200T| 6051M (1)|999:59:59 | Q1,04 | PCWP | |
|* 21 | HASH JOIN | | 53G| 150T| | 1675K (9)| 05:35:11 | Q1,04 | PCWP | |
| 22 | PX RECEIVE | | 15670 | 15M| | 73 (0)| 00:00:01 | Q1,04 | PCWP | |
| 23 | PX SEND BROADCAST | :TQ10001 | 15670 | 15M| | 73 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 24 | VIEW | | 15670 | 15M| | 73 (0)| 00:00:01 | Q1,01 | PCWP | |
| 25 | UNION-ALL | | | | | | | Q1,01 | PCWP | |
| 26 | PX BLOCK ITERATOR | | 5242 | 83872 | | 53 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 27 | TABLE ACCESS STORAGE FULL | REF_DMS_SERV_CD_FEAT_TRANSL | 5242 | 83872 | | 53 (0)| 00:00:01 | Q1,01 | PCWP | |
| 28 | PX BLOCK ITERATOR | | 10428 | 173K| | 20 (5)| 00:00:01 | Q1,01 | PCWC | |
|* 29 | TABLE ACCESS STORAGE FULL | REF_BTS_SERV_CD_FEAT_TRANSL | 10428 | 173K| | 20 (5)| 00:00:01 | Q1,01 | PCWP | |
|* 30 | HASH JOIN | | 72M| 138G| 314M| 1533K (1)| 05:06:46 | Q1,04 | PCWP | |
| 31 | PX RECEIVE | | 10M| 511M| | 105K (1)| 00:21:09 | Q1,04 | PCWP | |
| 32 | PX SEND HASH | :TQ10002 | 10M| 511M| | 105K (1)| 00:21:09 | Q1,02 | P->P | HASH |
| 33 | VIEW | VW_DTP_075D1686 | 10M| 511M| | 105K (1)| 00:21:09 | Q1,02 | PCWP | |
| 34 | HASH UNIQUE | | 10M| 511M| 753M| 105K (1)| 00:21:09 | Q1,02 | PCWP | |
| 35 | PX RECEIVE | | 10M| 511M| | 31845 (1)| 00:06:23 | Q1,02 | PCWP | |
| 36 | PX SEND HASH | :TQ10000 | 10M| 511M| | 31845 (1)| 00:06:23 | Q1,00 | P->P | HASH |
| 37 | PX BLOCK ITERATOR | | 10M| 511M| | 31845 (1)| 00:06:23 | Q1,00 | PCWC | |
| 38 | TABLE ACCESS STORAGE FULL| CDT_SERVICES | 10M| 511M| | 31845 (1)| 00:06:23 | Q1,00 | PCWP | |
| 39 | PX RECEIVE | | 28M| 53G| | 35809 (2)| 00:07:10 | Q1,04 | PCWP | |
| 40 | PX SEND HASH | :TQ10003 | 28M| 53G| | 35809 (2)| 00:07:10 | Q1,03 | P->P | HASH |
| 41 | VIEW | | 28M| 53G| | 35809 (2)| 00:07:10 | Q1,03 | PCWP | |
| 42 | UNION-ALL | | | | | | | Q1,03 | PCWP | |
| 43 | PX BLOCK ITERATOR | | 5931K| 96M| | 9993 (1)| 00:02:00 | Q1,03 | PCWC | |
|* 44 | TABLE ACCESS STORAGE FULL | IMS_BTS_TN_FEATURES | 5931K| 96M| | 9993 (1)| 00:02:00 | Q1,03 | PCWP | |
| 45 | PX BLOCK ITERATOR | | 22M| 347M| | 25816 (2)| 00:05:10 | Q1,03 | PCWC | |
|* 46 | TABLE ACCESS STORAGE FULL | IMS_DMS_TN_FEATURES | 22M| 347M| | 25816 (2)| 00:05:10 | Q1,03 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("RK"=1)
5 - filter(DENSE_RANK() OVER ( PARTITION BY "T2"."TN","T2"."FEATURES" ORDER BY "S"."SERVICE_CODE","T2"."SERVICE_CODE")<=1)
8 - filter(DENSE_RANK() OVER ( PARTITION BY "T2"."TN","T2"."FEATURES" ORDER BY "S"."SERVICE_CODE","T2"."SERVICE_CODE")<=1)
9 - access("T2"."TN"="S"."TN"(+) AND "T2"."SITE_ID"="S"."SITE_ID"(+) AND "T2"."SERVICE_CODE"="S"."SERVICE_CODE"(+))
21 - access("ITEM_2"="PROV"."SITE_ID" AND "B"."FEATURES"="PROV"."FEATURE_TYPE")
27 - storage("FEATURE_TYPE" NOT LIKE '%DEACT' AND "FEATURE_TYPE"<>'LCC' AND "FEATURE_TYPE"<>'CFRA' AND "FEATURE_TYPE"<>'AINDN' AND "FEATURE_TYPE"<>'CFGDA'
AND "FEATURE_TYPE"<>'LOD' AND "FEATURE_TYPE"<>'CIR' AND "FEATURE_TYPE"<>'PORT' AND "FEATURE_TYPE"<>'PUBLIC' AND "FEATURE_TYPE"<>'DNYCWT' AND
"FEATURE_TYPE"<>'LCDR' AND "FEATURE_TYPE"<>'CID' AND "FEATURE_TYPE"<>'VM' AND "FEATURE_TYPE" IS NOT NULL AND CASE "FEATURE_TYPE" WHEN 'CFDA' THEN 'CFW' WHEN
'CFBL' THEN 'CFW' WHEN 'CWT' THEN 'CWT' WHEN 'CWTACT' THEN 'CWT' WHEN 'CWI' THEN 'CWT' WHEN 'LDSA' THEN 'LDSA' WHEN 'LDSO' THEN 'LDSA' WHEN 'SC1' THEN 'SCS'
WHEN 'SC2' THEN 'SCL' WHEN 'DRING' THEN 'DRCW' WHEN 'DRCW' THEN 'DRCW' ELSE "FEATURE_TYPE" END <>'PIC' AND CASE "FEATURE_TYPE" WHEN 'CFDA' THEN 'CFW' WHEN
'CFBL' THEN 'CFW' WHEN 'CWT' THEN 'CWT' WHEN 'CWTACT' THEN 'CWT' WHEN 'CWI' THEN 'CWT' WHEN 'LDSA' THEN 'LDSA' WHEN 'LDSO' THEN 'LDSA' WHEN 'SC1' THEN 'SCS'
WHEN 'SC2' THEN 'SCL' WHEN 'DRING' THEN 'DRCW' WHEN 'DRCW' THEN 'DRCW' ELSE "FEATURE_TYPE" END <>'LPIC')
filter("FEATURE_TYPE" NOT LIKE '%DEACT' AND "FEATURE_TYPE"<>'LCC' AND "FEATURE_TYPE"<>'CFRA' AND "FEATURE_TYPE"<>'AINDN' AND "FEATURE_TYPE"<>'CFGDA'
AND "FEATURE_TYPE"<>'LOD' AND "FEATURE_TYPE"<>'CIR' AND "FEATURE_TYPE"<>'PORT' AND "FEATURE_TYPE"<>'PUBLIC' AND "FEATURE_TYPE"<>'DNYCWT' AND
"FEATURE_TYPE"<>'LCDR' AND "FEATURE_TYPE"<>'CID' AND "FEATURE_TYPE"<>'VM' AND "FEATURE_TYPE" IS NOT NULL AND CASE "FEATURE_TYPE" WHEN 'CFDA' THEN 'CFW' WHEN
'CFBL' THEN 'CFW' WHEN 'CWT' THEN 'CWT' WHEN 'CWTACT' THEN 'CWT' WHEN 'CWI' THEN 'CWT' WHEN 'LDSA' THEN 'LDSA' WHEN 'LDSO' THEN 'LDSA' WHEN 'SC1' THEN 'SCS'
WHEN 'SC2' THEN 'SCL' WHEN 'DRING' THEN 'DRCW' WHEN 'DRCW' THEN 'DRCW' ELSE "FEATURE_TYPE" END <>'PIC' AND CASE "FEATURE_TYPE" WHEN 'CFDA' THEN 'CFW' WHEN
'CFBL' THEN 'CFW' WHEN 'CWT' THEN 'CWT' WHEN 'CWTACT' THEN 'CWT' WHEN 'CWI' THEN 'CWT' WHEN 'LDSA' THEN 'LDSA' WHEN 'LDSO' THEN 'LDSA' WHEN 'SC1' THEN 'SCS'
WHEN 'SC2' THEN 'SCL' WHEN 'DRING' THEN 'DRCW' WHEN 'DRCW' THEN 'DRCW' ELSE "FEATURE_TYPE" END <>'LPIC')
29 - storage("FEATURE" NOT LIKE '%DEACT' AND "FEATURE"<>'SCA_DEACT' AND "FEATURE"<>'SCF_DEACT' AND "FEATURE"<>'SCR_DEACT' AND "FEATURE"<>'VM' AND
"FEATURE"<>'VM_ACT' AND "FEATURE"<>'VM_DEACT' AND "FEATURE"<>'VMA' AND "FEATURE"<>'VMA_ACT' AND "FEATURE"<>'VMA_DEACT' AND "FEATURE"<>'WARMLINE' AND "FEATURE"
IS NOT NULL AND CASE "FEATURE" WHEN 'CFBVA' THEN 'CFB' WHEN 'CFBVD' THEN 'CFB' WHEN 'CFNAVA' THEN 'CFNA' WHEN 'CFNAVD' THEN 'CFNA' ELSE "FEATURE" END <>'PIC'
AND CASE "FEATURE" WHEN 'CFBVA' THEN 'CFB' WHEN 'CFBVD' THEN 'CFB' WHEN 'CFNAVA' THEN 'CFNA' WHEN 'CFNAVD' THEN 'CFNA' ELSE "FEATURE" END <>'LPIC')
filter("FEATURE" NOT LIKE '%DEACT' AND "FEATURE"<>'SCA_DEACT' AND "FEATURE"<>'SCF_DEACT' AND "FEATURE"<>'SCR_DEACT' AND "FEATURE"<>'VM' AND
"FEATURE"<>'VM_ACT' AND "FEATURE"<>'VM_DEACT' AND "FEATURE"<>'VMA' AND "FEATURE"<>'VMA_ACT' AND "FEATURE"<>'VMA_DEACT' AND "FEATURE"<>'WARMLINE' AND "FEATURE"
IS NOT NULL AND CASE "FEATURE" WHEN 'CFBVA' THEN 'CFB' WHEN 'CFBVD' THEN 'CFB' WHEN 'CFNAVA' THEN 'CFNA' WHEN 'CFNAVD' THEN 'CFNA' ELSE "FEATURE" END <>'PIC'
AND CASE "FEATURE" WHEN 'CFBVA' THEN 'CFB' WHEN 'CFBVD' THEN 'CFB' WHEN 'CFNAVA' THEN 'CFNA' WHEN 'CFNAVD' THEN 'CFNA' ELSE "FEATURE" END <>'LPIC')
30 - access("ITEM_1"="B"."TN")
44 - storage("FEATURES"<>'PIC' AND "FEATURES"<>'LPIC')
filter("FEATURES"<>'PIC' AND "FEATURES"<>'LPIC')
46 - storage(CASE "FEATURES" WHEN 'DRING' THEN 'DRCW' WHEN 'DRCW' THEN 'DRCW' WHEN 'CWT' THEN 'CWT' WHEN 'CWTACT' THEN 'CWT' WHEN 'CWI' THEN 'CWT' WHEN
'LDSA' THEN 'LDSA' WHEN 'LDSO' THEN 'LDSA' ELSE "FEATURES" END <>'PIC' AND CASE "FEATURES" WHEN 'DRING' THEN 'DRCW' WHEN 'DRCW' THEN 'DRCW' WHEN 'CWT' THEN
'CWT' WHEN 'CWTACT' THEN 'CWT' WHEN 'CWI' THEN 'CWT' WHEN 'LDSA' THEN 'LDSA' WHEN 'LDSO' THEN 'LDSA' ELSE "FEATURES" END <>'LPIC')
filter(CASE "FEATURES" WHEN 'DRING' THEN 'DRCW' WHEN 'DRCW' THEN 'DRCW' WHEN 'CWT' THEN 'CWT' WHEN 'CWTACT' THEN 'CWT' WHEN 'CWI' THEN 'CWT' WHEN
'LDSA' THEN 'LDSA' WHEN 'LDSO' THEN 'LDSA' ELSE "FEATURES" END <>'PIC' AND CASE "FEATURES" WHEN 'DRING' THEN 'DRCW' WHEN 'DRCW' THEN 'DRCW' WHEN 'CWT' THEN
'CWT' WHEN 'CWTACT' THEN 'CWT' WHEN 'CWI' THEN 'CWT' WHEN 'LDSA' THEN 'LDSA' WHEN 'LDSO' THEN 'LDSA' ELSE "FEATURES" END <>'LPIC')

Note
-----
- dynamic sampling used for this statement (level=5)