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 Go to next message
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


Re: Please help me for getting best execution plan as my create view script is taking more time [message #610859 is a reply to message #610856] Tue, 25 March 2014 04:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
msol25 wrote on Tue, 25 March 2014 09:20
Note
-----
   - 'PLAN_TABLE' is old version



You need to fix that and then regenerate the explain plan.
Re: Please help me for getting best execution plan as my create view script is taking more time [message #610860 is a reply to message #610859] Tue, 25 March 2014 04:42 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi Cookie,

When using:


1. explain plan for
     <select query>

2.  select *
    from   table(dbms_xplan.display)



That time also getting same like :


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



Is there any other way to resolve this?
Re: Please help me for getting best execution plan as my create view script is taking more time [message #610861 is a reply to message #610860] Tue, 25 March 2014 04:47 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Run (or get your DBA ro run) catplan.sql
Re: Please help me for getting best execution plan as my create view script is taking more time [message #610866 is a reply to message #610860] Tue, 25 March 2014 05:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I would drop the PLAN_TABLE. There is a public synonym PLAN_TABLE that points to the correct version, a global temporary table owned by SYS.
Re: Please help me for getting best execution plan as my create view script is taking more time [message #612599 is a reply to message #610866] Tue, 22 April 2014 09:52 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I am suspicious about the validity of some objects statistics where the number of rows is reported to be 1.
Previous Topic: Archive log issues
Next Topic: Scripts to find Indexes which are candidate for Rebuild
Goto Forum:
  


Current Time: Fri Mar 29 00:49:02 CDT 2024