Forum Stats

  • 3,782,577 Users
  • 2,254,668 Discussions
  • 7,880,129 Comments

Discussions

Performance issue - Oracle Index not working

VK-Ora-newbee-Oracle
VK-Ora-newbee-Oracle Member Posts: 31
edited Dec 23, 2015 1:36AM in General Database Discussions

Hi Gurus,

I'm facing performance issue in my application. we are having two trans tables and each tables having more than Millions of records.

From application layer we are fetching data this tables for summary screen. In that screen we are using default filters. after that filter we are getting nearly 40% of data.

So the indexing is not working. please give your suggestions .

@Thanks Advance

-VK

Tagged:
Pini Dibaskandrewmy
«1

Answers

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Dec 22, 2015 8:36AM

    Hi,

    1. You have not shared the query

    2. You have not shared oracle db version

    3. Have you verified the statistics of table / indexes -

    4. Can you post the last good plan - used for this query

    5. Can you compare the stats for both plans good and bad plan ? is this query entirely new one ?

    6. what is value for Optimizer feature version ?

    - Pavan Kumar N

    Pini Dibask
  • Unknown
    edited Dec 22, 2015 9:02AM

    My car is not working.

    Please tell me how to make my car go.

    https://community.oracle.com/message/1814597#1814597

  • VK-Ora-newbee-Oracle
    VK-Ora-newbee-Oracle Member Posts: 31
    edited Dec 22, 2015 9:29AM

    Thanks for replay :d

  • VK-Ora-newbee-Oracle
    VK-Ora-newbee-Oracle Member Posts: 31
    edited Dec 22, 2015 9:34AM


    We are using Oracle 11GR2  version

    Please find below Explain Plan

    Plan hash value: 1223010966
    
    --------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                      | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                               |                               |     3 |  2931 | 24187   (2)| 00:04:51 |
    |*  1 |  COUNT STOPKEY                                 |                               |       |       |            |          |
    |   2 |   VIEW                                         |                               |     3 |  2931 | 24187   (2)| 00:04:51 |
    |   3 |    UNION-ALL                                   |                               |       |       |            |          |
    |   4 |     SORT AGGREGATE                             |                               |     1 |     8 |            |          |
    |*  5 |      TABLE ACCESS BY INDEX ROWID               | T_DLF_ATTACHDOCSI             |     2 |    16 |     2   (0)| 00:00:01 |
    |*  6 |       INDEX RANGE SCAN                         | T_DLF_ATTACHDOCSI_IDX2        |     2 |       |     1   (0)| 00:00:01 |
    |*  7 |     VIEW                                       |                               |     2 |  1954 | 24174   (2)| 00:04:51 |
    |   8 |      COUNT                                     |                               |       |       |            |          |
    |   9 |       VIEW                                     |                               |     2 |  1928 | 24174   (2)| 00:04:51 |
    |  10 |        HASH UNIQUE                             |                               |     2 |   740 | 24174   (2)| 00:04:51 |
    |  11 |         NESTED LOOPS                           |                               |       |       |            |          |
    |  12 |          NESTED LOOPS                          |                               |     2 |   740 | 24173   (2)| 00:04:51 |
    |* 13 |           HASH JOIN OUTER                      |                               |     2 |   726 | 24171   (2)| 00:04:51 |
    |  14 |            NESTED LOOPS                        |                               |       |       |            |          |
    |  15 |             NESTED LOOPS                       |                               |     2 |   552 |  4969   (1)| 00:01:00 |
    |  16 |              NESTED LOOPS                      |                               |   345 | 63825 |  3933   (1)| 00:00:48 |
    |  17 |               SORT UNIQUE                      |                               |  6926 | 90038 |   263   (2)| 00:00:04 |
    |  18 |                VIEW                            | M_BLF_AD_BRN_VW               |  6926 | 90038 |   263   (2)| 00:00:04 |
    |  19 |                 HASH UNIQUE                    |                               |  6926 |   293K|   263  (58)| 00:00:04 |
    |  20 |                  UNION-ALL                     |                               |       |       |            |          |
    |* 21 |                   HASH JOIN                    |                               |     1 |    46 |   113   (2)| 00:00:02 |
    |* 22 |                    TABLE ACCESS FULL           | M_USER_BRANCH_MAP             |    23 |   414 |     6   (0)| 00:00:01 |
    |* 23 |                    TABLE ACCESS FULL           | M_ORGANIZATION                |   583 | 16324 |   107   (2)| 00:00:02 |
    |* 24 |                   HASH JOIN                    |                               |  5793 |   243K|   113   (2)| 00:00:02 |
    |* 25 |                    TABLE ACCESS FULL           | M_USER_BRANCH_MAP             |    22 |   330 |     6   (0)| 00:00:01 |
    |* 26 |                    TABLE ACCESS FULL           | M_ORGANIZATION                |  4850 |   132K|   107   (2)| 00:00:02 |
    |  27 |                   NESTED LOOPS                 |                               |  1132 | 50940 |    36   (0)| 00:00:01 |
    |  28 |                    TABLE ACCESS BY INDEX ROWID | M_USER                        |     1 |    17 |     2   (0)| 00:00:01 |
    |* 29 |                     INDEX UNIQUE SCAN          | M_USER_PK                     |     1 |       |     1   (0)| 00:00:01 |
    |* 30 |                    TABLE ACCESS BY INDEX ROWID | M_ORGANIZATION                |  1132 | 31696 |    34   (0)| 00:00:01 |
    |* 31 |                     INDEX RANGE SCAN           | M_ORGANIZATION_BRN_USERID_IDX |   286 |       |     1   (0)| 00:00:01 |
    |  32 |               TABLE ACCESS BY INDEX ROWID      | T_INVOICE_ORDR                |   345 | 59340 |  3669   (1)| 00:00:45 |
    |* 33 |                INDEX RANGE SCAN                | T_INVOICE_ORDR_UK1            |  9453 |       |   118   (0)| 00:00:02 |
    |* 34 |              INDEX RANGE SCAN                  | DISC_REQ_TEMP_DET_BUY_REF_IDX |     1 |       |     2   (0)| 00:00:01 |
    |* 35 |             TABLE ACCESS BY INDEX ROWID        | T_ORDR_REQUEST_MAPR_DETAILS   |     1 |    91 |     3   (0)| 00:00:01 |
    |  36 |            VIEW                                |                               |     2 |   174 | 19201   (2)| 00:03:51 |
    |  37 |             UNION-ALL                          |                               |       |       |            |          |
    |  38 |              NESTED LOOPS                      |                               |       |       |            |          |
    |  39 |               NESTED LOOPS                     |                               |     1 |   225 | 16978   (2)| 00:03:24 |
    |  40 |                NESTED LOOPS                    |                               |     1 |   198 | 16975   (2)| 00:03:24 |
    |* 41 |                 HASH JOIN RIGHT SEMI           |                               |   120 | 15600 | 16615   (2)| 00:03:20 |
    |  42 |                  VIEW                          | M_BLF_AD_BRN_VW               |  6926 | 90038 |   263   (2)| 00:00:04 |
    |  43 |                   UNION-ALL                    |                               |       |       |            |          |
    |* 44 |                    HASH JOIN                   |                               |     1 |    46 |   113   (2)| 00:00:02 |
    |* 45 |                     TABLE ACCESS FULL          | M_USER_BRANCH_MAP             |    23 |   414 |     6   (0)| 00:00:01 |
    |* 46 |                     TABLE ACCESS FULL          | M_ORGANIZATION                |   583 | 16324 |   107   (2)| 00:00:02 |
    |* 47 |                    HASH JOIN                   |                               |  5793 |   243K|   113   (2)| 00:00:02 |
    |* 48 |                     TABLE ACCESS FULL          | M_USER_BRANCH_MAP             |    22 |   330 |     6   (0)| 00:00:01 |
    |* 49 |                     TABLE ACCESS FULL          | M_ORGANIZATION                |  4850 |   132K|   107   (2)| 00:00:02 |
    |  50 |                    NESTED LOOPS                |                               |  1132 | 50940 |    36   (0)| 00:00:01 |
    |  51 |                     TABLE ACCESS BY INDEX ROWID| M_USER                        |     1 |    17 |     2   (0)| 00:00:01 |
    |* 52 |                      INDEX UNIQUE SCAN         | M_USER_PK                     |     1 |       |     1   (0)| 00:00:01 |
    |* 53 |                     TABLE ACCESS BY INDEX ROWID| M_ORGANIZATION                |  1132 | 31696 |    34   (0)| 00:00:01 |
    |* 54 |                      INDEX RANGE SCAN          | M_ORGANIZATION_BRN_USERID_IDX |   286 |       |     1   (0)| 00:00:01 |
    |* 55 |                  TABLE ACCESS FULL             | T_INVC_REQUEST                |   583K|    65M| 16345   (2)| 00:03:17 |
    |  56 |                 TABLE ACCESS BY INDEX ROWID    | T_INVOICE_ORDR                |     1 |    68 |     3   (0)| 00:00:01 |
    |* 57 |                  INDEX UNIQUE SCAN             | T_INVOICE_ORDR_UK1            |     1 |       |     2   (0)| 00:00:01 |
    |* 58 |                INDEX RANGE SCAN                | ORDR_REF_PYMT_MAP_PYMT_ID     |     1 |       |     2   (0)| 00:00:01 |
    |* 59 |               TABLE ACCESS BY INDEX ROWID      | T_ORDR_REF_INVC_MAPPING       |     1 |    27 |     3   (0)| 00:00:01 |
    |  60 |              NESTED LOOPS SEMI                 |                               |     1 |   224 |  2223   (1)| 00:00:27 |
    |  61 |               NESTED LOOPS                     |                               |     1 |   211 |  2213   (1)| 00:00:27 |
    |  62 |                NESTED LOOPS                    |                               |     1 |   184 |  2210   (1)| 00:00:27 |
    |* 63 |                 TABLE ACCESS FULL              | T_INVC_REQUEST_TRANS          |    73 |  8468 |  1991   (1)| 00:00:24 |
    |  64 |                 TABLE ACCESS BY INDEX ROWID    | T_INVOICE_ORDR                |     1 |    68 |     3   (0)| 00:00:01 |
    |* 65 |                  INDEX UNIQUE SCAN             | T_INVOICE_ORDR_UK1            |     1 |       |     2   (0)| 00:00:01 |
    |* 66 |                TABLE ACCESS BY INDEX ROWID     | T_ORDR_REF_INVC_MAPPING       |     1 |    27 |     3   (0)| 00:00:01 |
    |* 67 |                 INDEX RANGE SCAN               | ORDR_REF_PYMT_MAP_PYMT_ID     |     1 |       |     2   (0)| 00:00:01 |
    |  68 |               VIEW                             | M_BLF_AD_BRN_VW               |     1 |    13 |    10   (0)| 00:00:01 |
    |  69 |                UNION ALL PUSHED PREDICATE      |                               |       |       |            |          |
    |  70 |                 NESTED LOOPS                   |                               |     1 |    46 |     4   (0)| 00:00:01 |
    |* 71 |                  TABLE ACCESS BY INDEX ROWID   | M_ORGANIZATION                |     1 |    28 |     2   (0)| 00:00:01 |
    |* 72 |                   INDEX UNIQUE SCAN            | M_ORGANIZATION_PK             |     1 |       |     1   (0)| 00:00:01 |
    |* 73 |                  TABLE ACCESS BY INDEX ROWID   | M_USER_BRANCH_MAP             |     1 |    18 |     2   (0)| 00:00:01 |
    |* 74 |                   INDEX RANGE SCAN             | M_USER_BRANCH_MAP_UK          |     1 |       |     1   (0)| 00:00:01 |
    |  75 |                 NESTED LOOPS                   |                               |     1 |    43 |     3   (0)| 00:00:01 |
    |* 76 |                  TABLE ACCESS BY INDEX ROWID   | M_ORGANIZATION                |     1 |    28 |     2   (0)| 00:00:01 |
    |* 77 |                   INDEX UNIQUE SCAN            | M_ORGANIZATION_PK             |     1 |       |     1   (0)| 00:00:01 |
    |* 78 |                  TABLE ACCESS BY INDEX ROWID   | M_USER_BRANCH_MAP             |    22 |   330 |     1   (0)| 00:00:01 |
    |* 79 |                   INDEX UNIQUE SCAN            | M_USER_BRANCH_MAP_PK          |     1 |       |     0   (0)| 00:00:01 |
    |  80 |                 NESTED LOOPS                   |                               |     1 |    45 |     3   (0)| 00:00:01 |
    |* 81 |                  TABLE ACCESS BY INDEX ROWID   | M_ORGANIZATION                |     1 |    28 |     2   (0)| 00:00:01 |
    |* 82 |                   INDEX UNIQUE SCAN            | M_ORGANIZATION_PK             |     1 |       |     1   (0)| 00:00:01 |
    |* 83 |                  TABLE ACCESS BY INDEX ROWID   | M_USER                        |     1 |    17 |     1   (0)| 00:00:01 |
    |* 84 |                   INDEX UNIQUE SCAN            | M_USER_PK                     |     1 |       |     0   (0)| 00:00:01 |
    |* 85 |           INDEX UNIQUE SCAN                    | M_CURRENCY_PK                 |     1 |       |     0   (0)| 00:00:01 |
    |  86 |          TABLE ACCESS BY INDEX ROWID           | M_CURRENCY                    |     1 |     7 |     1   (0)| 00:00:01 |
    |  87 |     SORT AGGREGATE                             |                               |     1 |     8 |            |          |
    |* 88 |      TABLE ACCESS BY INDEX ROWID               | T_DLF_ATTACHDOCSI             |     2 |    16 |     2   (0)| 00:00:01 |
    |* 89 |       INDEX RANGE SCAN                         | T_DLF_ATTACHDOCSI_IDX2        |     2 |       |     1   (0)| 00:00:01 |
    |* 90 |     VIEW                                       |                               |     1 |   408 |    13   (8)| 00:00:01 |
    |  91 |      COUNT                                     |                               |       |       |            |          |
    |  92 |       VIEW                                     |                               |     1 |   395 |    13   (8)| 00:00:01 |
    |  93 |        SORT ORDER BY                           |                               |     1 |   395 |    13   (8)| 00:00:01 |
    |  94 |         VIEW                                   |                               |     1 |   395 |    13   (8)| 00:00:01 |
    |  95 |          HASH UNIQUE                           |                               |     1 |   547 |    13   (8)| 00:00:01 |
    |  96 |           NESTED LOOPS SEMI                    |                               |     1 |   547 |    12   (0)| 00:00:01 |
    |  97 |            NESTED LOOPS                        |                               |     1 |   534 |     2   (0)| 00:00:01 |
    |  98 |             NESTED LOOPS                       |                               |     1 |   527 |     2   (0)| 00:00:01 |
    |  99 |              TABLE ACCESS FULL                 | T_REORDERD_DISC_REQ_TMP_DTL   |     1 |   167 |     2   (0)| 00:00:01 |
    | 100 |              TABLE ACCESS BY INDEX ROWID       | T_REORDERD_INVOICE_PYMT       |     1 |   360 |     0   (0)| 00:00:01 |
    |*101 |               INDEX RANGE SCAN                 | T_RECALL_INVOICE_PYMT_IDX02   |     1 |       |     0   (0)| 00:00:01 |
    | 102 |             TABLE ACCESS BY INDEX ROWID        | M_CURRENCY                    |     1 |     7 |     0   (0)| 00:00:01 |
    |*103 |              INDEX UNIQUE SCAN                 | M_CURRENCY_PK                 |     1 |       |     0   (0)| 00:00:01 |
    | 104 |            VIEW                                | M_BLF_AD_BRN_VW               |     1 |    13 |    10   (0)| 00:00:01 |
    | 105 |             UNION ALL PUSHED PREDICATE         |                               |       |       |            |          |
    | 106 |              NESTED LOOPS                      |                               |     1 |    46 |     4   (0)| 00:00:01 |
    |*107 |               TABLE ACCESS BY INDEX ROWID      | M_ORGANIZATION                |     1 |    28 |     2   (0)| 00:00:01 |
    |*108 |                INDEX UNIQUE SCAN               | M_ORGANIZATION_PK             |     1 |       |     1   (0)| 00:00:01 |
    |*109 |               TABLE ACCESS BY INDEX ROWID      | M_USER_BRANCH_MAP             |     1 |    18 |     2   (0)| 00:00:01 |
    |*110 |                INDEX RANGE SCAN                | M_USER_BRANCH_MAP_UK          |     1 |       |     1   (0)| 00:00:01 |
    | 111 |              NESTED LOOPS                      |                               |     1 |    43 |     3   (0)| 00:00:01 |
    |*112 |               TABLE ACCESS BY INDEX ROWID      | M_ORGANIZATION                |     1 |    28 |     2   (0)| 00:00:01 |
    |*113 |                INDEX UNIQUE SCAN               | M_ORGANIZATION_PK             |     1 |       |     1   (0)| 00:00:01 |
    |*114 |               TABLE ACCESS BY INDEX ROWID      | M_USER_BRANCH_MAP             |    22 |   330 |     1   (0)| 00:00:01 |
    |*115 |                INDEX UNIQUE SCAN               | M_USER_BRANCH_MAP_PK          |     1 |       |     0   (0)| 00:00:01 |
    | 116 |              NESTED LOOPS                      |                               |     1 |    45 |     3   (0)| 00:00:01 |
    |*117 |               TABLE ACCESS BY INDEX ROWID      | M_ORGANIZATION                |     1 |    28 |     2   (0)| 00:00:01 |
    |*118 |                INDEX UNIQUE SCAN               | M_ORGANIZATION_PK             |     1 |       |     1   (0)| 00:00:01 |
    |*119 |               TABLE ACCESS BY INDEX ROWID      | M_USER                        |     1 |    17 |     1   (0)| 00:00:01 |
    |*120 |                INDEX UNIQUE SCAN               | M_USER_PK                     |     1 |       |     0   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------------------------------
    
    
  • Unknown
    edited Dec 22, 2015 9:34AM

    problems posted with NO details result in responses with no answers.

    You are welcome.

    If you provide needed details you may get a helpful response or two.

  • ddf_dba
    ddf_dba Member Posts: 1,398 Bronze Trophy
    edited Dec 22, 2015 10:29AM

    "11GR2" tells us nothing, really.  We need the EXACT version you're using, such as 11.2.0.4, 11.2.0.3, etc.  What I can tell you from the plan is you're using a 'where rownum <= ...' predicate, which still doesn't tell us much.  I agree with Pavan that you need to post all of the information he requested if you want anyone to provide an accurate assessment.

    David Fitzjarrell

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    edited Dec 22, 2015 10:49AM

    could you add the query and the predicate section of the plan (which would tell us something about the access predicates and filter conditions used by the optimizer)? Could you also tell us something about the number of rows in the tables?

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Dec 22, 2015 11:01AM

    Hi,

    1. Given a try from explain to understand 14 -35 are repeated (38-59 and semi ) with hash outer join (on result from 38-59 and nested loop join with lines 61-84)

    2. Difference is it picked up |* 55 |                  TABLE ACCESS FULL             | T_INVC_REQUEST                |   583K|    65M| 16345   (2)| 00:03:17 |

    -Instead of pickup index it went for FTS , you need to check predicates utilized which is opting for wrong access path

    3 You can optimize the query instead of re-iterating the set of same fetching set of rows from the same tables.

    Hope it helps

    - Pavan Kumar N

  • jkostic
    jkostic Member Posts: 64
    edited Dec 22, 2015 11:37AM

    Would be great to have the query, the predicates and the estimate and actual rows. (execute your query with /*+ gather_plan_statistics   */   hint)

    Point given by Kumar is you target.

         -> cost of the operation is 16345  , more than 67% of the total cost !

    jko

  • jgarry
    jgarry Member Posts: 13,842
    edited Dec 22, 2015 12:08PM

    Let's say you have to get students from their school to their rival school for a football game 50 miles away.  You have a hundred students, and ten volunteer drivers who can each take 5 students at 70 mph.  Wouldn't a bus that can hold 100 students and go 55 mph be quicker?

    When you do an index access, you have to get an index block, then get a data block.  When you do a full table scan, you can skip getting the index blocks entirely, as well as get more blocks at a time with multi block reads.  The exact volume where the fts gets faster varies based on the data distribution and query access, but 40% of the data is likely to favor the fts.  The optimizer figures that out based on the statistics, and possibly some additional information it can acquire or you can give it.  Sometimes the engine can transform the query.

    If you are doing multiple full table scans, that might not be so good.  The real trick to performance is to get rid of unnecessary work.

    So:

    First, you should apply the latest patches, improvements and bug fixes happen all the time.

    Second, follow the directions in the link sol.beach gave you.

    Help us help you.

This discussion has been closed.