5 Replies Latest reply: Nov 26, 2012 1:34 AM by Gaurav RSS

    High cost of TABLE ACCESS BY LOCAL INDEX ROWID

    Gaurav
      Hi,

      We are having a query which is running very slow, while checking the execution plan we found high cost on "TABLE ACCESS BY LOCAL INDEX ROWID"

      Db version : 11.2.0.1
      EBS version: 12.1.2
      Os version : Aix 6.1
      SID        : 567
      ADDRESS    : 07000004EB12A7A8
      HASH_VALUE : 556917643
      MODULE     : ora_rw20_run@erpprodapp (TNS V1-V3)
      PROGRAM    : ora_rw20_run@erpprodapp (TNS V1-V3)
      MACHINE    : erpprodapp
      CHILD CNT  : 2
      
      
       [  Current SQL  ]
      ---------------------------------------------------------------------------------------------------------------------------------------------
      SELECT API.INVOICE_TYPE_LOOKUP_CODE
      ,   DECODE(API.INVOICE_TYPE_LOOKUP_CODE, 'CREDIT', 0, Z.AMT_VAL ) CREDIT_VAL, 0 ACCT_CR
      ,   API.EXCHANGE_RATE EXCHANGE_RATE
      ,   API.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE, API.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
      ,   API.EXCHANGE_DATE EXCHANGE_DATE
      FROM AP_INVOICES_ALL API, AP_INVOICE_DISTRIBUTIONS_ALL APD, (SELECT NVL(SUM(APD.AMOUNT),0) AMT_VAL
      ,   API.INVOICE_ID
      FROM AP_INVOICES_ALL API, AP_INVOICE_DISTRIBUTIONS_ALL APD
      WHERE API.INVOICE_ID = APD.INVOICE_ID
      AND API.INVOICE_TYPE_LOOKUP_CODE <> :B6
      AND APD.MATCH_STATUS_FLAG = 'A'
      AND API.VENDOR_ID = :B5
      AND API.VENDOR_SITE_ID = :B4
      AND APD.ACCOUNTING_DATE < :B3
      AND (API.ORG_ID = :B2
      OR API.ORG_ID IS NULL)
      AND APD.LINE_TYPE_LOOKUP_CODE <> :B1
      GROUP BY API.INVOICE_ID) Z
      WHERE Z.INVOICE_ID = API.INVOICE_ID
      AND API.INVOICE_ID = APD.INVOICE_ID
      AND APD.ROWID = (SELECT ROWID
      FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE ROWNUM=1
      AND INVOICE_ID=APD.INVOICE_ID
      AND MATCH_STATUS_FLAG = 'A'
      AND ACCOUNTING_DATE < :B3 )
      AND API.INVOICE_TYPE_LOOKUP_CODE <> :B6
      AND APD.MATCH_STATUS_FLAG = 'A'
      AND API.VENDOR_ID = :B5 AND API.VENDOR_SITE_ID = :B4
      AND APD.ACCOUNTING_DATE < :B3
      AND (API.ORG_ID = :B2
      OR API.ORG_ID IS NULL)
      AND ((API.INVOICE_TYPE_LOOKUP_CODE <> :B9 )
      OR ( (API.INVOICE_TYPE_LOOKUP_CODE = :B9 ) AND ( NOT EXISTS (SELECT '1'
      FROM AP_INVOICE_PAYMENTS_ALL APP, AP_CHECKS_ALL APC
      WHERE APP.CHECK_ID = APC.CHECK_ID
      AND APP.INVOICE_ID = API.INVOICE_ID
      AND APC.PAYMENT_TYPE_FLAG = 'R' ) ) ) ) AND API.INVOICE_CURRENCY_CODE =NVL( :B8
      ,  API.INVOICE_CURRENCY_CODE)
      AND API.ACCTS_PAY_CODE_COMBINATION_ID = NVL(:B7 ,API.ACCTS_PAY_CODE_COMBINATION_ID) UNION ALL
      SELECT API.INVOICE_TYPE_LOOKUP_CODE, DECODE(API.INVOICE_TYPE_LOOKUP_CODE,'CREDIT'
      ,   DECODE(STATUS_LOOKUP_CODE,'VOIDED', APP.AMOUNT+ NVL(DISCOUNT_AMOUNT_TAKEN
      ,   0) , ABS(APP.AMOUNT)+ ABS(NVL(DISCOUNT_AMOUNT_TAKEN, 0)) )
      ,   0) CREDIT_VAL, 0 ACCT_CR, APC.EXCHANGE_RATE EXCHANGE_RATE
      ,   APC.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
      ,   API.PAYMENT_CURRENCY_CODE INVOICE_CURRENCY_CODE, APC.EXCHANGE_DATE EXCHANGE_DATE
      FROM AP_INVOICES_ALL API, AP_INVOICE_PAYMENTS_ALL APP, AP_CHECKS_ALL APC
      WHERE APP.INVOICE_ID = API.INVOICE_ID
      AND APP.CHECK_ID = APC.CHECK_ID AND APC.STATUS_LOOKUP_CODE IN (:B15 ,:B14 ,:B13 ,:B12 ,:B11
      ,  :B10 )
      AND API.VENDOR_ID = :B5
      AND API.VENDOR_SITE_ID = :B4
      AND APP.ACCOUNTING_DATE < TRUNC(:B3 )
      AND ( API.ORG_ID = :B2
      OR API.ORG_ID IS NULL )
      AND EXISTS (
      SELECT '1'
      FROM AP_INVOICE_DISTRIBUTIONS_ALL APD , AP_INVOICE_LINES_ALL APIL
      WHERE APD.INVOICE_ID = API.INVOICE_ID
      AND APIL.INVOICE_ID = APD.INVOICE_ID
      AND APD.MATCH_STATUS_FLAG ='A'
      AND APIL.LINE_NUMBER = APD.INVOICE_LINE_NUMBER)
      AND API.INVOICE_CURRENCY_CODE =NVL( :B8 ,API.INVOICE_CURRENCY_CODE)
      AND API.ACCTS_PAY_CODE_COMBINATION_ID = NVL(:B7 ,API.ACCTS_PAY_CODE_COMBINATION_ID) UNION ALL
      SELECT 'LOSS' INVOICE_TYPE_LOOKUP_CODE, 0 CREDIT_VAL, DECODE(XAL.ACCOUNTING_CLASS_CODE
      ,  'LOSS', ACCOUNTED_DR,0) ACCT_CR
      ,   XAL.CURRENCY_CONVERSION_RATE EXCHANGE_RATE , XAL.CURRENCY_CONVERSION_TYPE EXCHANGE_RATE_TYPE
      ,   XAL.CURRENCY_CODE INVOICE_CURRENCY_CODE
      ,   XAL.CURRENCY_CONVERSION_DATE EXCHANGE_DATE
      FROM XLA_AE_LINES XAL, XLA_AE_HEADERS XAH, XLA_TRANSACTION_ENTITIES XTE, AP_INVOICES_ALL API
      WHERE XAL.APPLICATION_ID = 200
      AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
      AND XAL.ACCOUNTING_CLASS_CODE IN ( :B18 ,:B17 )
      AND XAH.APPLICATION_ID = 200 AND XAH.ENTITY_ID = XTE.ENTITY_ID
      AND XTE.APPLICATION_ID = 200 AND XTE.ENTITY_CODE =:B16
      AND XTE.SOURCE_ID_INT_1 = API.INVOICE_ID
      AND API.VENDOR_ID = :B5
      AND API.VENDOR_SITE_ID = :B4
      AND XAH.ACCOUNTING_DATE < :B3
      AND (API.ORG_ID = :B2
      OR API.ORG_ID IS NULL )
      AND API.INVOICE_CURRENCY_CODE =NVL( :B8 ,API.INVOICE_CURRENCY_CODE)
      AND API.ACCTS_PAY_CODE_COMBINATION_ID = NVL(:B7 ,API.ACCTS_PAY_CODE_COMBINATION_ID) UNION ALL
      SELECT 'LOSS' INVOICE_TYPE_LOOKUP_CODE, 0 CREDIT_VAL, DECODE(XAL.ACCOUNTING_CLASS_CODE
      ,  'LOSS', ACCOUNTED_DR,0) ACCT_CR
      ,   XAL.CURRENCY_CONVERSION_RATE EXCHANGE_RATE , XAL.CURRENCY_CONVERSION_TYPE EXCHANGE_RATE_TYPE
      ,   XAL.CURRENCY_CODE INVOICE_CURRENCY_CODE
      ,   XAL.CURRENCY_CONVERSION_DATE EXCHANGE_DATE
      FROM XLA_AE_LINES XAL, XLA_AE_HEADERS XAH, XLA_TRANSACTION_ENTITIES XTE, AP_INVOICES_ALL API, AP_CHECKS_ALL AC
      ,   AP_INVOICE_PAYMENTS_ALL APP
      WHERE XAL.APPLICATION_ID = 200
      AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
      AND XAL.ACCOUNTING_CLASS_CODE IN ( :B18 ,:B17 )
      AND XAH.APPLICATION_ID = 200
      AND XAH.ENTITY_ID = XTE.ENTITY_ID
      AND XTE.APPLICATION_ID = 200
      AND XTE.ENTITY_CODE = :B19
      AND XTE.SOURCE_ID_INT_1 = AC.CHECK_ID
      AND XAH.EVENT_ID = APP.ACCOUNTING_EVENT_ID
      AND API.INVOICE_ID = APP.INVOICE_ID AND APP.CHECK_ID = AC.CHECK_ID
      AND AC.STATUS_LOOKUP_CODE IN (:B15 ,:B14 ,:B13 , :B12 ,:B11 ,:B10 )
      AND API.VENDOR_ID = :B5
      AND API.VENDOR_SITE_ID = :B4
      AND XAH.ACCOUNTING_DATE < :B3
      AND (API.ORG_ID = :B2
      OR API.ORG_ID IS NULL )
      AND API.INVOICE_CURRENCY_CODE =NVL( :B8 ,API.INVOICE_CURRENCY_CODE)
      AND API.ACCTS_PAY_CODE_COMBINATION_ID = NVL(:B7
      ,  API.ACCTS_PAY_CODE_COMBINATION_ID)
      
      Plan hash value: 1352234085
      
      ------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Ps
      ------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                         |                              |       |       |  1904 (100)|          |       |
      |   1 |  UNION-ALL                               |                              |       |       |            |          |       |
      |   2 |   FILTER                                 |                              |       |       |            |          |       |
      |   3 |    NESTED LOOPS                          |                              |       |       |            |          |       |
      |   4 |     NESTED LOOPS                         |                              |     1 |    87 |    20   (5)| 00:06:09 |       |
      |   5 |      NESTED LOOPS                        |                              |     1 |    60 |    17   (6)| 00:05:14 |       |
      |   6 |       VIEW                               |                              |     1 |    18 |    15   (7)| 00:04:37 |       |
      |   7 |        HASH GROUP BY                     |                              |     1 |    54 |    15   (7)| 00:04:37 |       |
      |   8 |         NESTED LOOPS                     |                              |       |       |            |          |       |
      |   9 |          NESTED LOOPS                    |                              |     1 |    54 |    14   (0)| 00:04:19 |       |
      |  10 |           TABLE ACCESS BY INDEX ROWID    | AP_INVOICES_ALL              |     1 |    27 |    11   (0)| 00:03:23 |       |
      |  11 |            INDEX RANGE SCAN              | AP_INVOICES_N2               |    11 |       |     3   (0)| 00:00:56 |       |
      |  12 |           INDEX RANGE SCAN               | AP_INVOICE_DISTRIBUTIONS_N33 |     2 |       |     2   (0)| 00:00:37 |       |
      |  13 |          TABLE ACCESS BY INDEX ROWID     | AP_INVOICE_DISTRIBUTIONS_ALL |     1 |    27 |     3   (0)| 00:00:56 |       |
      |  14 |       TABLE ACCESS BY INDEX ROWID        | AP_INVOICES_ALL              |     1 |    42 |     2   (0)| 00:00:37 |       |
      |  15 |        INDEX UNIQUE SCAN                 | AP_INVOICES_U1               |     1 |       |     1   (0)| 00:00:19 |       |
      |  16 |      INDEX RANGE SCAN                    | AP_INVOICE_DISTRIBUTIONS_N33 |     1 |       |     2   (0)| 00:00:37 |       |
      |  17 |       COUNT STOPKEY                      |                              |       |       |            |          |       |
      |  18 |        TABLE ACCESS BY INDEX ROWID       | AP_INVOICE_DISTRIBUTIONS_ALL |     1 |    27 |     4   (0)| 00:01:14 |       |
      |  19 |         INDEX RANGE SCAN                 | AP_INVOICE_DISTRIBUTIONS_N33 |     2 |       |     3   (0)| 00:00:56 |       |
      |  20 |     TABLE ACCESS BY INDEX ROWID          | AP_INVOICE_DISTRIBUTIONS_ALL |     1 |    27 |     3   (0)| 00:00:56 |       |
      |  21 |    NESTED LOOPS                          |                              |       |       |            |          |       |
      |  22 |     NESTED LOOPS                         |                              |     1 |    17 |     6   (0)| 00:01:51 |       |
      |  23 |      TABLE ACCESS BY INDEX ROWID         | AP_INVOICE_PAYMENTS_ALL      |     1 |    10 |     4   (0)| 00:01:14 |       |
      |  24 |       INDEX RANGE SCAN                   | AP_INVOICE_PAYMENTS_N1       |     1 |       |     3   (0)| 00:00:56 |       |
      |  25 |      INDEX UNIQUE SCAN                   | AP_CHECKS_U1                 |     1 |       |     1   (0)| 00:00:19 |       |
      |  26 |     TABLE ACCESS BY INDEX ROWID          | AP_CHECKS_ALL                |     1 |     7 |     2   (0)| 00:00:37 |       |
      |  27 |   NESTED LOOPS SEMI                      |                              |     1 |   102 |    25   (0)| 00:07:41 |       |
      |  28 |    NESTED LOOPS                          |                              |     1 |   100 |    20   (0)| 00:06:09 |       |
      |  29 |     NESTED LOOPS                         |                              |     1 |    67 |    18   (0)| 00:05:32 |       |
      |  30 |      TABLE ACCESS BY INDEX ROWID         | AP_INVOICES_ALL              |     1 |    42 |    15   (0)| 00:04:37 |       |
      |  31 |       INDEX RANGE SCAN                   | AP_INVOICES_N7               |    15 |       |     3   (0)| 00:00:56 |       |
      |  32 |      TABLE ACCESS BY INDEX ROWID         | AP_INVOICE_PAYMENTS_ALL      |     1 |    25 |     3   (0)| 00:00:56 |       |
      |  33 |       INDEX RANGE SCAN                   | AP_INVOICE_PAYMENTS_N1       |     1 |       |     2   (0)| 00:00:37 |       |
      |  34 |     TABLE ACCESS BY INDEX ROWID          | AP_CHECKS_ALL                |     1 |    33 |     2   (0)| 00:00:37 |       |
      |  35 |      INDEX UNIQUE SCAN                   | AP_CHECKS_U1                 |     1 |       |     1   (0)| 00:00:19 |       |
      |  36 |    VIEW PUSHED PREDICATE                 | VW_SQ_1                      |     1 |     2 |     5   (0)| 00:01:33 |       |
      |  37 |     NESTED LOOPS                         |                              |     1 |    20 |     5   (0)| 00:01:33 |       |
      |  38 |      TABLE ACCESS BY INDEX ROWID         | AP_INVOICE_DISTRIBUTIONS_ALL |     1 |    11 |     4   (0)| 00:01:14 |       |
      |  39 |       INDEX RANGE SCAN                   | AP_INVOICE_DISTRIBUTIONS_N33 |     2 |       |     3   (0)| 00:00:56 |       |
      |  40 |      INDEX UNIQUE SCAN                   | AP_INVOICE_LINES_U1          |     1 |     9 |     1   (0)| 00:00:19 |       |
      |  41 |   NESTED LOOPS                           |                              |       |       |            |          |       |
      |  42 |    NESTED LOOPS                          |                              |     1 |   113 |  1825   (0)| 09:20:33 |       |
      |  43 |     NESTED LOOPS                         |                              |     1 |    79 |  1822   (0)| 09:19:38 |       |
      |  44 |      MERGE JOIN CARTESIAN                |                              |     1 |    50 |  1820   (0)| 09:19:01 |       |
      |  45 |       TABLE ACCESS BY INDEX ROWID        | AP_INVOICES_ALL              |     1 |    27 |    15   (0)| 00:04:37 |       |
      |  46 |        INDEX RANGE SCAN                  | AP_INVOICES_N7               |    15 |       |     3   (0)| 00:00:56 |       |
      |  47 |       BUFFER SORT                        |                              | 17282 |   388K|  1805   (0)| 09:14:24 |       |
      |  48 |        PARTITION LIST SINGLE             |                              | 17282 |   388K|  1805   (0)| 09:14:24 |   KEY |
      |  49 |         TABLE ACCESS BY LOCAL INDEX ROWID| XLA_AE_HEADERS               | 17282 |   388K|  1805   (0)| 09:14:24 |     1 |
      |  50 |          INDEX RANGE SCAN                | XLA_AE_HEADERS_N5            |  5445 |       |   100   (0)| 00:30:43 |     1 |
      |  51 |      PARTITION LIST SINGLE               |                              |     1 |    29 |     2   (0)| 00:00:37 |   KEY |
      |  52 |       TABLE ACCESS BY LOCAL INDEX ROWID  | XLA_TRANSACTION_ENTITIES     |     1 |    29 |     2   (0)| 00:00:37 |     1 |
      |  53 |        INDEX UNIQUE SCAN                 | XLA_TRANSACTION_ENTITIES_U1  |     1 |       |     1   (0)| 00:00:19 |     1 |
      |  54 |     PARTITION LIST SINGLE                |                              |     1 |       |     2   (0)| 00:00:37 |   KEY |
      |  55 |      INDEX RANGE SCAN                    | XLA_AE_LINES_U1              |     1 |       |     2   (0)| 00:00:37 |     1 |
      |  56 |    TABLE ACCESS BY LOCAL INDEX ROWID     | XLA_AE_LINES                 |     1 |    34 |     3   (0)| 00:00:56 |     1 |
      |  57 |   NESTED LOOPS                           |                              |       |       |            |          |       |
      |  58 |    NESTED LOOPS                          |                              |     1 |   151 |    30   (0)| 00:09:13 |       |
      |  59 |     NESTED LOOPS                         |                              |     1 |   135 |    28   (0)| 00:08:37 |       |
      |  60 |      NESTED LOOPS                        |                              |     1 |   101 |    25   (0)| 00:07:41 |       |
      |  61 |       NESTED LOOPS                       |                              |     1 |    72 |    23   (0)| 00:07:04 |       |
      |  62 |        NESTED LOOPS                      |                              |     1 |    43 |    18   (0)| 00:05:32 |       |
      |  63 |         TABLE ACCESS BY INDEX ROWID      | AP_INVOICES_ALL              |     1 |    27 |    15   (0)| 00:04:37 |       |
      |  64 |          INDEX RANGE SCAN                | AP_INVOICES_N7               |    15 |       |     3   (0)| 00:00:56 |       |
      |  65 |         TABLE ACCESS BY INDEX ROWID      | AP_INVOICE_PAYMENTS_ALL      |     1 |    16 |     3   (0)| 00:00:56 |       |
      |  66 |          INDEX RANGE SCAN                | AP_INVOICE_PAYMENTS_N1       |     1 |       |     2   (0)| 00:00:37 |       |
      |  67 |        PARTITION LIST SINGLE             |                              |     1 |    29 |     5   (0)| 00:01:33 |   KEY |
      |  68 |         TABLE ACCESS BY LOCAL INDEX ROWID| XLA_AE_HEADERS               |     1 |    29 |     5   (0)| 00:01:33 |     1 |
      |  69 |          INDEX RANGE SCAN                | XLA_AE_HEADERS_N2            |     1 |       |     2   (0)| 00:00:37 |     1 |
      |  70 |       PARTITION LIST SINGLE              |                              |     1 |    29 |     2   (0)| 00:00:37 |   KEY |
      |  71 |        TABLE ACCESS BY LOCAL INDEX ROWID | XLA_TRANSACTION_ENTITIES     |     1 |    29 |     2   (0)| 00:00:37 |     1 |
      |  72 |         INDEX UNIQUE SCAN                | XLA_TRANSACTION_ENTITIES_U1  |     1 |       |     1   (0)| 00:00:19 |     1 |
      |  73 |      PARTITION LIST SINGLE               |                              |     1 |    34 |     3   (0)| 00:00:56 |   KEY |
      |  74 |       TABLE ACCESS BY LOCAL INDEX ROWID  | XLA_AE_LINES                 |     1 |    34 |     3   (0)| 00:00:56 |     1 |
      |  75 |        INDEX RANGE SCAN                  | XLA_AE_LINES_U1              |     1 |       |     2   (0)| 00:00:37 |     1 |
      |  76 |     INDEX UNIQUE SCAN                    | AP_CHECKS_U1                 |     1 |       |     1   (0)| 00:00:19 |       |
      |  77 |    TABLE ACCESS BY INDEX ROWID           | AP_CHECKS_ALL                |     1 |    16 |     2   (0)| 00:00:37 |       |
      ------------------------------------------------------------------------------------------------------------------------------------
       
      XLA_AE_HEADERS is a table partition

      Regards,
      Gaurav
        • 1. Re: High cost of TABLE ACCESS BY LOCAL INDEX ROWID
          Paul  Horth
          It looks like you are not completely partition eliminating on XLA_AE_HEADERS.

          I can't see the partition end from what you've posted but you're definitely starting
          at partition 1. Is there any way of starting at a later partition?
          • 2. Re: High cost of TABLE ACCESS BY LOCAL INDEX ROWID
            Gaurav
            Thanks for the prompt reply.

            XLA_AE_HEADERS is a table partition and showing high cost on TABLE ACCESS BY LOCAL INDEX ROWID similarly XLA_TRANSACTION_ENTITIES is also a table partion
            OWNER    SEGMENT_NAME                   SEGMENT_T BYTES/1024/1024
            -------- ------------------------------ --------- ---------------
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR        2014.375
            XLA      XLA_AE_HEADERS                 TABLE PAR          4424.5
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            XLA      XLA_AE_HEADERS                 TABLE PAR          10.125
            XLA      XLA_AE_HEADERS                 TABLE PAR            .125
            • 3. Re: High cost of TABLE ACCESS BY LOCAL INDEX ROWID
              rp0428
              >
              XLA_AE_HEADERS is a table partition and showing high cost on TABLE ACCESS BY LOCAL INDEX ROWID similarly XLA_TRANSACTION_ENTITIES is also a table partion
              >
              Post the rest of the right side of the plan - it got cut off.
              • 4. Re: High cost of TABLE ACCESS BY LOCAL INDEX ROWID
                Nikolay Savvinov
                Hi,

                all we have is optimizer estimates. If the optimizer is correct, then you already have the best plan possible (btw the cost is quite low -- 2,000; assuming 90% cache hit ratio and 5ms per disk read, it should complete within 1 s). If the optimizer is wrong, we got nothing to find where and why it's wrong: no A-time, no A-rows, no predicate section of the plan, nothing.

                If you want some help, you need to provide more information (trace file, dbms_xplan output with statistics_level = all, SQL monitor output etc.).

                Best regards,
                Nikolay
                • 5. Re: High cost of TABLE ACCESS BY LOCAL INDEX ROWID
                  Gaurav
                  Hi,

                  Thanks for your reply, I will upload the SQT report soon