Home » RDBMS Server » Performance Tuning » Please help me for getting best execution plan as my create view script is taking more time (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| Please help me for getting best execution plan as my create view script is taking more time [message #610856] |
Tue, 25 March 2014 04:20  |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Hi Experts,
Please help me for getting best execution plan for given query.Please find query and Execution plan also.
CREATE VIEW TEST
AS
SELECT ID_TYPE,
ID_NUMBER,
CUSTOMER_NUM,
MSISDN,
ACCOUNT_NUM,
CUST_TYPE,
CUST_SUB_TYPE
FROM(
SELECT ID_TYPE,
ID_NUMBER,
CUSTOMER_NUM,
MSISDN,
ACCOUNT_NUM,
CUST_TYPE,
CUST_SUB_TYPE,
ROW_NUMBER() OVER ( PARTITION BY ID_TYPE,ID_NUMBER ORDER BY ID_TYPE,ID_NUMBER) AS ROW_NUM
FROM(
SELECT ACCT.X_COMPANY_ID_TYPE ID_TYPE,
ACCT.X_COMMERCIAL_REG_NUM ID_NUMBER,
ACCT.OU_NUM CUSTOMER_NUM,
AST.SERIAL_NUM MSISDN,
BILL.OU_NUM ACCOUNT_NUM,
EBUCHK.CUSTOMER_TYPE CUST_TYPE,
EBUCHK.CUSTOMER_SUBTYPE CUST_SUB_TYPE
FROM
DM_CRM_GSM.S_ORG_EXT ACCT,
DM_CRM_GSM.S_ORG_EXT BILL,
DM_CRM_GSM.S_ASSET_EDI AST,
ABINITIO.STC_EBU_CUSTTYPESUBTYPE EBUCHK
WHERE 1=1
AND ACCT.ROW_ID = BILL.MASTER_OU_ID
AND BILL.ACCNT_TYPE_CD LIKE '%Billing%'
AND AST.BILL_ACCNT_ID = BILL.ROW_ID
AND AST.ROW_ID = AST.ROOT_ASSET_ID
AND ACCT.OU_TYPE_CD = EBUCHK.CUSTOMER_TYPE
AND ACCT.X_ACCOUNT_SUB_TYPE = EBUCHK.CUSTOMER_SUBTYPE
UNION ALL
SELECT
ID_TYPE,
ID_NUMBER,
CUSTOMER_NUMBER CUSTOMER_NUM,
ACCESS_NUMBER MSISDN,
ACCOUNT_NUMBER ACCOUNT_NUM,
CUST_TYPE,
CUST_SUB_TYPE
FROM (SELECT
ID_MAP.CRM_ID_TYPE ID_TYPE,
CMICUS.ID_NUMBER,
CMMAST.CUSTOMER_NUMBER,
CMACCT.ACCOUNT_NUMBER,
SUBXTL.ACCESS_NUMBER,
ACNT_MAP.CRM_TYPE CUST_TYPE,
ACNT_MAP.CRM_SUB_TYPE CUST_SUB_TYPE
FROM
DM_CRM_GSM.CX_ID_MAPPING ID_MAP,
DM_ICMS_LL.CUSTOMER_ID_CMICUS00 CMICUS,
DM_ICMS_LL.CUSTOMER_CMMAST00 CMMAST,
DM_ICMS_LL.ACCOUNT_CMACCT00 CMACCT,
DM_ICMS_LL.CUSTOMER_SERVICES_LL_SUBXTL SUBXTL,
DM_CRM_GSM.CX_ICM_ACNT_MAP ACNT_MAP
WHERE 1 = 1
AND ID_MAP.ICMS_ID_TYPE = CMICUS.ID_TYPE
AND CMICUS.CUSTOMER_NUMBER = CMMAST.CUSTOMER_NUMBER
AND CMICUS.EXPIRY_DATE > SYSDATE
AND CMMAST.CUSTOMER_NUMBER = CMACCT.CUSTOMER_NUMBER
AND CMMAST.CUSTOMER_TYPE = ACNT_MAP.ICMS_TYPE
AND CMMAST.CUSTOMER_SUBTYPE = ACNT_MAP.ICMS_SUB_TYPE
AND CMACCT.ACCOUNT_NUMBER = SUBXTL.ACCOUNT_NUMBER(+)
AND CMACCT.CUSTOMER_NUMBER = SUBXTL.CUSTOMER_NUMBER(+)
AND SUBXTL.DISCONNECTION_DATE > SYSDATE
UNION ALL
SELECT
ID_MAP.CRM_ID_TYPE ID_TYPE,
CMICUS.ID_NUMBER,
CMMAST.CUSTOMER_NUMBER,
CMACCT.ACCOUNT_NUMBER,
CLSIMB.ACCESS_NUMBER,
ACNT_MAP.CRM_TYPE CUST_TYPE,
ACNT_MAP.CRM_SUB_TYPE CUST_SUB_TYPE
FROM DM_CRM_GSM.CX_ID_MAPPING ID_MAP,
DM_ICMS_LL.CUSTOMER_ID_CMICUS00 CMICUS,
DM_ICMS_LL.CUSTOMER_CMMAST00 CMMAST,
DM_ICMS_LL.ACCOUNT_CMACCT00 CMACCT,
DM_ICMS_LL.SPECIAL_ACCESS_NUM_CLSIMB CLSIMB,
DM_CRM_GSM.CX_ICM_ACNT_MAP ACNT_MAP
WHERE 1 = 1
AND ID_MAP.ICMS_ID_TYPE = CMICUS.ID_TYPE
AND CMICUS.CUSTOMER_NUMBER = CMMAST.CUSTOMER_NUMBER
AND CMICUS.EXPIRY_DATE > SYSDATE
AND CMMAST.CUSTOMER_NUMBER = CMACCT.CUSTOMER_NUMBER
AND CMMAST.CUSTOMER_TYPE = ACNT_MAP.ICMS_TYPE
AND CMMAST.CUSTOMER_SUBTYPE = ACNT_MAP.ICMS_SUB_TYPE
AND CMACCT.ACCOUNT_NUMBER = CLSIMB.ACCOUNT_NUMBER(+)
AND CMACCT.CUSTOMER_NUMBER = CLSIMB.CUSTOMER_NUMBER(+)
AND CLSIMB.EXPIRY_DATE > SYSDATE)
)
)
WHERE ROW_NUM = 1
Execution Plan :
PLAN_TABLE_OUTPUT
Plan hash value: 3839869828
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 811K| 671M| | 3068K (1)| 00:02:00 |
|* 1 | VIEW | | 811K| 671M| | 3068K (1)| 00:02:00 |
|* 2 | WINDOW SORT PUSHED RANK | | 811K| 660M| 704M| 3068K (1)| 00:02:00 |
| 3 | VIEW | | 811K| 660M| | 2917K (1)| 00:01:54 |
| 4 | UNION-ALL | | | | | | |
| 5 | NESTED LOOPS | | | | | | |
| 6 | NESTED LOOPS | | 1 | 171 | | 2686K (1)| 00:01:45 |
|* 7 | HASH JOIN | | 307K| 38M| | 2655K (1)| 00:01:44 |
| 8 | TABLE ACCESS STORAGE FULL | STC_EBU_CUSTTYPESUBTYPE | 5 | 95 | | 3 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | | | | | |
| 10 | NESTED LOOPS | | 4332K| 470M| | 2655K (1)| 00:01:44 |
|* 11 | TABLE ACCESS STORAGE FULL | S_ORG_EXT | 4332K| 194M| | 2525K (1)| 00:01:39 |
|* 12 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 67 | | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | BILL_ACCNT_ID_INDEX1 | 13 | | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 38 | | 1 (0)| 00:00:01 |
|* 16 | HASH JOIN | | 811K| 226M| | 230K (1)| 00:00:10 |
| 17 | TABLE ACCESS STORAGE FULL | CX_ID_MAPPING | 16 | 256 | | 5 (0)| 00:00:01 |
| 18 | VIEW | VW_JF_SET$1BA56959 | 913K| 240M| | 230K (1)| 00:00:10 |
| 19 | UNION-ALL | | | | | | |
| 20 | NESTED LOOPS | | | | | | |
| 21 | NESTED LOOPS | | 838K| 115M| | 197K (1)| 00:00:08 |
|* 22 | HASH JOIN | | 836K| 88M| 149M| 171K (1)| 00:00:07 |
|* 23 | HASH JOIN | | 2141K| 124M| | 111K (1)| 00:00:05 |
| 24 | TABLE ACCESS STORAGE FULL | CX_ICM_ACNT_MAP | 223 | 5352 | | 6 (0)| 00:00:01 |
|* 25 | HASH JOIN | | 12M| 431M| 232M| 111K (1)| 00:00:05 |
| 26 | TABLE ACCESS STORAGE FULL | CUSTOMER_CMMAST00 | 8711K| 132M| | 30616 (1)| 00:00:02 |
| 27 | VIEW | index$_join$_011 | 12M| 244M| | 44864 (1)| 00:00:02 |
|* 28 | HASH JOIN | | | | | | |
| 29 | INDEX STORAGE FAST FULL SCAN| ACCOUNT_CMACCT00_ACC_NO_01_IX | 12M| 244M| | 445 (1)| 00:00:01 |
| 30 | INDEX STORAGE FAST FULL SCAN| ACCOUNT_CMACCT00_CTMR_NBR_1_IX | 12M| 244M| | 712 (1)| 00:00:01 |
|* 31 | TABLE ACCESS STORAGE FULL | CUSTOMER_SERVICES_LL_SUBXTL | 4770K| 227M| | 35665 (1)| 00:00:02 |
|* 32 | INDEX RANGE SCAN | CUSTOMER_ID_CUSTNO_01_IX | 1 | | | 1 (0)| 00:00:01 |
|* 33 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ID_CMICUS00 | 1 | 34 | | 1 (0)| 00:00:01 |
| 34 | NESTED LOOPS | | | | | | |
| 35 | NESTED LOOPS | | 74924 | 10M| | 33568 (1)| 00:00:02 |
|* 36 | HASH JOIN | | 74766 | 8104K| | 31325 (1)| 00:00:02 |
| 37 | TABLE ACCESS STORAGE FULL | CX_ICM_ACNT_MAP | 223 | 5352 | | 6 (0)| 00:00:01 |
| 38 | NESTED LOOPS | | | | | | |
| 39 | NESTED LOOPS | | 426K| 35M| | 31317 (1)| 00:00:02 |
| 40 | NESTED LOOPS | | 426K| 28M| | 18519 (1)| 00:00:01 |
|* 41 | TABLE ACCESS STORAGE FULL | SPECIAL_ACCESS_NUM_CLSIMB | 426K| 20M| | 5722 (1)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_CMACCT00 | 1 | 21 | | 1 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | ACCOUNT_CMACCT00_ACC_NO_01_IX | 1 | | | 1 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 16 | | 1 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | CUSTOMER_ID_CUSTNO_01_IX | 1 | | | 1 (0)| 00:00:01 |
|* 47 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_ID_CMICUS00 | 1 | 34 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROW_NUM"=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID_TYPE","ID_NUMBER" ORDER BY NULL )<=1)
7 - access("ACCT"."OU_TYPE_CD"="EBUCHK"."CUSTOMER_TYPE" AND "ACCT"."X_ACCOUNT_SUB_TYPE"="EBUCHK"."CUSTOMER_SUBTYPE")
11 - storage("BILL"."ACCNT_TYPE_CD" LIKE '%Billing%' AND "BILL"."ACCNT_TYPE_CD" IS NOT NULL)
filter("BILL"."ACCNT_TYPE_CD" LIKE '%Billing%' AND "BILL"."ACCNT_TYPE_CD" IS NOT NULL)
12 - access("ACCT"."ROW_ID"="BILL"."MASTER_OU_ID")
14 - access("AST"."BILL_ACCNT_ID"="BILL"."ROW_ID")
15 - filter("AST"."ROW_ID"="AST"."ROOT_ASSET_ID")
16 - access("ID_MAP"."ICMS_ID_TYPE"="ITEM_1")
22 - access("CMACCT"."CUSTOMER_NUMBER"="SUBXTL"."CUSTOMER_NUMBER" AND "CMACCT"."ACCOUNT_NUMBER"="SUBXTL"."ACCOUNT_NUMBER")
23 - access("CMMAST"."CUSTOMER_SUBTYPE"="ACNT_MAP"."ICMS_SUB_TYPE" AND "CMMAST"."CUSTOMER_TYPE"="ACNT_MAP"."ICMS_TYPE")
25 - access("CMMAST"."CUSTOMER_NUMBER"="CMACCT"."CUSTOMER_NUMBER")
28 - access(ROWID=ROWID)
31 - storage("SUBXTL"."DISCONNECTION_DATE">SYSDATE@!)
filter("SUBXTL"."DISCONNECTION_DATE">SYSDATE@!)
32 - access("CMICUS"."CUSTOMER_NUMBER"="CMMAST"."CUSTOMER_NUMBER")
33 - filter("CMICUS"."EXPIRY_DATE">SYSDATE@!)
36 - access("CMMAST"."CUSTOMER_SUBTYPE"="ACNT_MAP"."ICMS_SUB_TYPE" AND "CMMAST"."CUSTOMER_TYPE"="ACNT_MAP"."ICMS_TYPE")
41 - storage("CLSIMB"."EXPIRY_DATE">SYSDATE@!)
filter("CLSIMB"."EXPIRY_DATE">SYSDATE@!)
42 - filter("CMACCT"."CUSTOMER_NUMBER"="CLSIMB"."CUSTOMER_NUMBER")
43 - access("CMACCT"."ACCOUNT_NUMBER"="CLSIMB"."ACCOUNT_NUMBER")
44 - access("CMMAST"."CUSTOMER_NUMBER"="CMACCT"."CUSTOMER_NUMBER")
46 - access("CMICUS"."CUSTOMER_NUMBER"="CMMAST"."CUSTOMER_NUMBER")
47 - filter("CMICUS"."EXPIRY_DATE">SYSDATE@!)
Note
-----
- 'PLAN_TABLE' is old version
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 06:53:14 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
