8 Replies Latest reply: Feb 10, 2013 4:29 PM by user11949974 RSS

    High consistent reads

    user11949974
      I am having performance issues on a query in a production environment that I cannot replicate in our test environment. Our test environment is an import of production. Version information is:
      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
      PL/SQL Release 10.2.0.3.0 - Production
      CORE     10.2.0.3.0     Production
      TNS for Solaris: Version 10.2.0.3.0 - Production
      NLSRTL Version 10.2.0.3.0 - Production
      When I run the query in test, I get the following results using TKPROF
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        2      0.03       0.05          0          0          0           0
      Execute      1      0.00       0.00          0          0          0           0
      Fetch        1      0.02       0.01        241       1033          0           2
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        4      0.05       0.07        241       1033          0           2
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      The same query in Production
      call     count       cpu    elapsed       disk      query    current        rows
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      Parse        2      0.03       0.04          0          8          0           0
      Execute      1      0.00       0.00          0          0          0           0
      Fetch        1     36.15      35.61          4    8187612          0           2
      ------- ------  -------- ---------- ---------- ---------- ----------  ----------
      total        4     36.18      35.65          4    8187620          0           2
      
      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      This performance problem started a few weeks ago and the problem seems to be in the high number of consistent reads during fetch. The DBA tried restarting the instance and gathering fresh stats on the tables but that has not made a difference. Now he wants to export the tables, drop the schema and re-import. I would like to understand why this his happening though.
        • 1. Re: High consistent reads
          sb92075
          post both EXPLAIN PLANs
          • 2. Re: High consistent reads
            damorgan
            Am I allowed to guess that the data volume in production is many times larger than the volume in test?

            Or as to why anyone would be working in 2013 with software so old it is in desupport mode and unpatched at that?

            The explain plans would be helpful. So would row be the SQL, the row counts from the tables, and the clustering factor from any indexes.
            • 3. Re: High consistent reads
              user11949974
              The data volumes are the same. The explain plans are large and exceed the 30000 character limit so I have to break them up, but here they are:

              h1. TEST Pt 1
              ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
              | Id  | Operation                                                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |  
              ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
              |   1 |  TABLE ACCESS BY INDEX ROWID                                 | CMPLN               |      5 |      1 |      5 |00:00:00.01 |      15 |      0 |       |       |          |  
              |*  2 |   INDEX UNIQUE SCAN                                          | CMP_PK              |      5 |      1 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
              |   3 |  SORT UNIQUE                                                 |                     |      1 |      1 |      5 |00:00:00.10 |    2056 |      1 |  9216 |  9216 | 8192  (0)|  
              |*  4 |   COUNT STOPKEY                                              |                     |      1 |        |      5 |00:00:00.10 |    2041 |      1 |       |       |          |  
              |*  5 |    FILTER                                                    |                     |      1 |        |      5 |00:00:00.10 |    2041 |      1 |       |       |          |  
              |   6 |     NESTED LOOPS OUTER                                       |                     |      1 |      1 |      5 |00:00:00.10 |    2031 |      1 |       |       |          |  
              |   7 |      NESTED LOOPS OUTER                                      |                     |      1 |      1 |      5 |00:00:00.10 |    2012 |      1 |       |       |          |  
              |   8 |       NESTED LOOPS OUTER                                     |                     |      1 |      1 |      5 |00:00:00.10 |    1992 |      1 |       |       |          |  
              |   9 |        NESTED LOOPS OUTER                                    |                     |      1 |      1 |      5 |00:00:00.10 |    1987 |      1 |       |       |          |  
              |  10 |         NESTED LOOPS OUTER                                   |                     |      1 |      1 |      5 |00:00:00.10 |    1987 |      1 |       |       |          |  
              |  11 |          NESTED LOOPS OUTER                                  |                     |      1 |      1 |      5 |00:00:00.10 |    1987 |      1 |       |       |          |  
              |  12 |           NESTED LOOPS OUTER                                 |                     |      1 |      1 |      5 |00:00:00.10 |    1967 |      1 |       |       |          |  
              |  13 |            NESTED LOOPS OUTER                                |                     |      1 |      1 |      5 |00:00:00.10 |    1967 |      1 |       |       |          |  
              |  14 |             NESTED LOOPS                                     |                     |      1 |      1 |      5 |00:00:00.10 |    1967 |      1 |       |       |          |  
              |  15 |              NESTED LOOPS                                    |                     |      1 |      1 |      5 |00:00:00.10 |    1943 |      1 |       |       |          |  
              |  16 |               NESTED LOOPS                                   |                     |      1 |      1 |      6 |00:00:00.10 |    1931 |      1 |       |       |          |  
              |  17 |                NESTED LOOPS                                  |                     |      1 |      1 |      6 |00:00:00.10 |    1913 |      1 |       |       |          |  
              |  18 |                 NESTED LOOPS                                 |                     |      1 |      1 |      5 |00:00:00.10 |    1892 |      1 |       |       |          |  
              |  19 |                  NESTED LOOPS OUTER                          |                     |      1 |      1 |      5 |00:00:00.10 |    1882 |      1 |       |       |          |  
              |  20 |                   NESTED LOOPS SEMI                          |                     |      1 |      1 |      5 |00:00:00.10 |    1875 |      1 |       |       |          |  
              |  21 |                    NESTED LOOPS OUTER                        |                     |      1 |      1 |      5 |00:00:00.09 |     429 |      1 |       |       |          |  
              |  22 |                     NESTED LOOPS                             |                     |      1 |      1 |      5 |00:00:00.09 |     427 |      1 |       |       |          |  
              |  23 |                      NESTED LOOPS                            |                     |      1 |      1 |      5 |00:00:00.09 |     420 |      1 |       |       |          |  
              |  24 |                       NESTED LOOPS OUTER                     |                     |      1 |      1 |      5 |00:00:00.09 |     408 |      1 |       |       |          |  
              |  25 |                        NESTED LOOPS OUTER                    |                     |      1 |      1 |      5 |00:00:00.09 |     396 |      1 |       |       |          |  
              |  26 |                         NESTED LOOPS OUTER                   |                     |      1 |      1 |      5 |00:00:00.09 |     386 |      1 |       |       |          |  
              |  27 |                          NESTED LOOPS OUTER                  |                     |      1 |      1 |      5 |00:00:00.09 |     379 |      1 |       |       |          |  
              |  28 |                           NESTED LOOPS OUTER                 |                     |      1 |      1 |      5 |00:00:00.09 |     367 |      1 |       |       |          |  
              |  29 |                            NESTED LOOPS OUTER                |                     |      1 |      1 |      5 |00:00:00.09 |     358 |      1 |       |       |          |  
              |  30 |                             NESTED LOOPS OUTER               |                     |      1 |      1 |      5 |00:00:00.09 |     351 |      1 |       |       |          |  
              |  31 |                              NESTED LOOPS OUTER              |                     |      1 |      1 |      5 |00:00:00.09 |     344 |      1 |       |       |          |  
              |  32 |                               NESTED LOOPS OUTER             |                     |      1 |      1 |      5 |00:00:00.09 |     332 |      1 |       |       |          |  
              |  33 |                                NESTED LOOPS OUTER            |                     |      1 |      1 |      5 |00:00:00.09 |     320 |      1 |       |       |          |  
              |  34 |                                 NESTED LOOPS OUTER           |                     |      1 |      1 |      5 |00:00:00.09 |     311 |      1 |       |       |          |  
              |  35 |                                  NESTED LOOPS OUTER          |                     |      1 |      1 |      5 |00:00:00.09 |     299 |      1 |       |       |          |  
              |  36 |                                   NESTED LOOPS OUTER         |                     |      1 |      1 |      5 |00:00:00.09 |     299 |      1 |       |       |          |  
              |  37 |                                    NESTED LOOPS OUTER        |                     |      1 |      1 |      5 |00:00:00.09 |     289 |      1 |       |       |          |  
              |  38 |                                     NESTED LOOPS OUTER       |                     |      1 |      1 |      5 |00:00:00.09 |     269 |      1 |       |       |          |  
              |* 39 |                                      HASH JOIN SEMI          |                     |      1 |      1 |      5 |00:00:00.09 |     267 |      1 |   712K|   712K|  365K (0)|  
              |  40 |                                       NESTED LOOPS           |                     |      1 |      1 |      5 |00:00:00.09 |     237 |      1 |       |       |          |  
              |  41 |                                        NESTED LOOPS OUTER    |                     |      1 |      1 |      5 |00:00:00.09 |     225 |      1 |       |       |          |  
              |  42 |                                         NESTED LOOPS         |                     |      1 |      1 |      5 |00:00:00.09 |     208 |      1 |       |       |          |  
              |  43 |                                          NESTED LOOPS OUTER  |                     |      1 |      1 |      5 |00:00:00.09 |     191 |      1 |       |       |          |  
              |  44 |                                           NESTED LOOPS OUTER |                     |      1 |      1 |      5 |00:00:00.09 |     184 |      1 |       |       |          |  
              |  45 |                                            NESTED LOOPS OUTER|                     |      1 |      1 |      5 |00:00:00.09 |     172 |      1 |       |       |          |  
              |  46 |                                             NESTED LOOPS OUTE|                     |      1 |      1 |      5 |00:00:00.09 |     172 |      1 |       |       |          |  
              |  47 |                                              NESTED LOOPS OUT|                     |      1 |      1 |      5 |00:00:00.09 |     155 |      1 |       |       |          |  
              |  48 |                                               NESTED LOOPS OU|                     |      1 |      1 |      5 |00:00:00.09 |     143 |      1 |       |       |          |  
              |  49 |                                                NESTED LOOPS O|                     |      1 |      1 |      5 |00:00:00.09 |     143 |      1 |       |       |          |  
              |  50 |                                                 NESTED LOOPS |                     |      1 |      1 |      5 |00:00:00.09 |     126 |      1 |       |       |          |  
              |  51 |                                                  NESTED LOOPS|                     |      1 |      4 |      5 |00:00:00.09 |     114 |      1 |       |       |          |  
              |  52 |                                                   NESTED LOOP|                     |      1 |      4 |      5 |00:00:00.09 |     114 |      1 |       |       |          |  
              |  53 |                                                    TABLE ACCE| NAME                |      1 |      4 |     26 |00:00:00.09 |      24 |      1 |       |       |          |  
              |* 54 |                                                     INDEX RAN| NAME_KEY_NME_I      |      1 |      4 |     26 |00:00:00.09 |       3 |      1 |       |       |          |  
              |* 55 |                                                    TABLE ACCE| LINK                |     26 |      1 |      5 |00:00:00.01 |      90 |      0 |       |       |          |  
              |* 56 |                                                     INDEX RAN| LINK_NAM_FK_I       |     26 |      2 |     52 |00:00:00.01 |      54 |      0 |       |       |          |  
              |* 57 |                                                   INDEX UNIQU| IR_PK               |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
              |* 58 |                                                  TABLE ACCESS| RSPN                |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |* 59 |                                                   INDEX UNIQU| RES_PK              |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
              |  60 |                                                 TABLE ACCESS | LIC                 |      5 |      1 |      5 |00:00:00.01 |      17 |      0 |       |       |          |  
              |* 61 |                                                  INDEX UNIQUE| LIC_PK              |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |* 62 |                                                INDEX UNIQUE S| EMA_PK              |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
              |* 63 |                                               INDEX UNIQUE SC| PHO_PK              |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |  64 |                                              TABLE ACCESS BY | ADDR                |      5 |      1 |      5 |00:00:00.01 |      17 |      0 |       |       |          |  
              |* 65 |                                               INDEX UNIQUE SC| ADDR_PK             |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |  66 |                                             TABLE ACCESS BY I| CNTRY               |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
              |* 67 |                                              INDEX UNIQUE SCA| CNTRY_PK            |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
              |  68 |                                            TABLE ACCESS BY IN| CNTY                |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |* 69 |                                             INDEX UNIQUE SCAN| CTY_PK              |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
              |  70 |                                           TABLE ACCESS BY IND| STATE               |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
              |* 71 |                                            INDEX UNIQUE SCAN | STE_PK              |      5 |      1 |      5 |00:00:00.01 |       2 |      0 |       |       |          |  
              |  72 |                                          TABLE ACCESS BY INDE| XENT                |      5 |      1 |      5 |00:00:00.01 |      17 |      0 |       |       |          |  
              |* 73 |                                           INDEX UNIQUE SCAN  | EE_PK               |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |* 74 |                                         INDEX RANGE SCAN     | IND_EE_FK_I         |      5 |      1 |      0 |00:00:00.01 |      17 |      0 |       |       |          |  
              |  75 |                                        TABLE ACCESS BY INDEX | CMPLN               |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |* 76 |                                         INDEX UNIQUE SCAN    | CMP_PK              |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
              |  77 |                                       VIEW                   | VW_NSO_1            |      1 |      4 |     89 |00:00:00.01 |      30 |      0 |       |       |          |  
              |* 78 |                                        CONNECT BY WITH FILTER|                     |      1 |        |     89 |00:00:00.01 |      30 |      0 |  9216 |  9216 | 8192  (0)|  
              |  79 |                                         TABLE ACCESS BY INDEX| CLNT                |      1 |        |      1 |00:00:00.01 |       2 |      0 |       |       |          |  
              |* 80 |                                          INDEX UNIQUE SCAN   | CLNT_PK             |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |  
              |  81 |                                         NESTED LOOPS         |                     |      4 |        |     88 |00:00:00.01 |      28 |      0 |       |       |          |  
              |  82 |                                          BUFFER SORT         |                     |      4 |        |     89 |00:00:00.01 |       0 |      0 |  9216 |  9216 | 8192  (0)|  
              |  83 |                                           CONNECT BY PUMP    |                     |      4 |        |     89 |00:00:00.01 |       0 |      0 |       |       |          |  
              |  84 |                                          TABLE ACCESS BY INDE| CLNT                |     89 |      4 |     88 |00:00:00.01 |      28 |      0 |       |       |          |  
              |* 85 |                                           INDEX RANGE SCAN   | CLNT_CLNT_FK_I      |     89 |      4 |     88 |00:00:00.01 |       4 |      0 |       |       |          |  
              |  86 |                                         TABLE ACCESS FULL    | CLNT                |      0 |      4 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
              |* 87 |                                      INDEX UNIQUE SCAN       | ECP_PK              |      5 |      1 |      5 |00:00:00.01 |       2 |      0 |       |       |          |  
              |* 88 |                                     TABLE ACCESS BY INDEX ROW| CMPLN_TXT           |      5 |      1 |      0 |00:00:00.01 |      20 |      0 |       |       |          |  
              |* 89 |                                      INDEX RANGE SCAN        | CT1_CMP_FK_I        |      5 |      4 |      5 |00:00:00.01 |      15 |      0 |       |       |          |  
              |  90 |                                    TABLE ACCESS BY INDEX ROWI| BRD_ENF_CDE         |      5 |      1 |      4 |00:00:00.01 |      10 |      0 |       |       |          |  
              |* 91 |                                     INDEX UNIQUE SCAN        | BECD_PK             |      5 |      1 |      4 |00:00:00.01 |       6 |      0 |       |       |          |  
              |* 92 |                                   INDEX UNIQUE SCAN          | ECMPLXYT_PK         |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
              |  93 |                                  TABLE ACCESS BY INDEX ROWID | BRD_ENF_CDE         |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |* 94 |                                   INDEX UNIQUE SCAN          | BECD_PK             |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
              |  95 |                                 TABLE ACCESS BY INDEX ROWID  | BRD_ENF_CDE         |      5 |      1 |      4 |00:00:00.01 |       9 |      0 |       |       |          |  
              |* 96 |                                  INDEX UNIQUE SCAN           | BECD_PK             |      5 |      1 |      4 |00:00:00.01 |       5 |      0 |       |       |          |  
              |  97 |                                TABLE ACCESS BY INDEX ROWID   | BRD_ENF_CDE         |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              |* 98 |                                 INDEX UNIQUE SCAN            | BECD_PK             |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
              |  99 |                               TABLE ACCESS BY INDEX ROWID    | BRD_ENF_CDE         |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
              • 4. Re: High consistent reads
                user11949974
                h1. TEST Pt 2
                |*100 |                                INDEX UNIQUE SCAN             | BECD_PK             |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                |*101 |                              INDEX UNIQUE SCAN               | STA_PK              |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                |*102 |                             INDEX UNIQUE SCAN                | ECD_PK              |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                | 103 |                            TABLE ACCESS BY INDEX ROWID       | ENF_CDE             |      5 |      1 |      4 |00:00:00.01 |       9 |      0 |       |       |          |  
                |*104 |                             INDEX UNIQUE SCAN                | ECD_PK              |      5 |      1 |      4 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 105 |                           TABLE ACCESS BY INDEX ROWID        | ENF_CDE             |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
                |*106 |                            INDEX UNIQUE SCAN                 | ECD_PK              |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                |*107 |                          INDEX UNIQUE SCAN                   | ECD_PK              |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                | 108 |                         TABLE ACCESS BY INDEX ROWID          | ENF_CDE             |      5 |      1 |      4 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*109 |                          INDEX UNIQUE SCAN                   | ECD_PK              |      5 |      1 |      4 |00:00:00.01 |       6 |      0 |       |       |          |  
                | 110 |                        TABLE ACCESS BY INDEX ROWID           | CLNT_CMPLN_STA      |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
                |*111 |                         INDEX UNIQUE SCAN                    | CCST_PK             |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                | 112 |                       TABLE ACCESS BY INDEX ROWID            | CLNT_CMPLN_STA      |      5 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
                |*113 |                        INDEX UNIQUE SCAN                     | CCST_PK             |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                | 114 |                      TABLE ACCESS BY INDEX ROWID             | CLNT                |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                |*115 |                       INDEX UNIQUE SCAN                      | CLNT_PK             |      5 |      1 |      5 |00:00:00.01 |       2 |      0 |       |       |          |  
                |*116 |                     INDEX UNIQUE SCAN                        | ESCL_PK             |      5 |      1 |      5 |00:00:00.01 |       2 |      0 |       |       |          |  
                |*117 |                    VIEW                                      | VW_ENF_STFF_SECUR   |      5 |    154 |      5 |00:00:00.01 |    1446 |      0 |       |       |          |  
                | 118 |                     SORT UNIQUE                              |                     |      5 |        |     17 |00:00:00.01 |    1446 |      0 |  9216 |  9216 | 8192  (0)|  
                | 119 |                      UNION-ALL PARTITION                     |                     |      5 |        |     25 |00:00:00.01 |    1446 |      0 |       |       |          |  
                | 120 |                       NESTED LOOPS                           |                     |      5 |      1 |      0 |00:00:00.01 |     354 |      0 |       |       |          |  
                | 121 |                        NESTED LOOPS                          |                     |      5 |     20 |    100 |00:00:00.01 |     172 |      0 |       |       |          |  
                |*122 |                         TABLE ACCESS BY INDEX ROWID          | CLNT                |      5 |      1 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*123 |                          INDEX UNIQUE SCAN                   | CLNT_PK             |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 124 |                         TABLE ACCESS BY INDEX ROWID          | ENF_SECUR_CLS       |      5 |     20 |    100 |00:00:00.01 |     162 |      0 |       |       |          |  
                | 125 |                          INDEX FULL SCAN                     | ESCL_PK             |      5 |     20 |    100 |00:00:00.01 |      81 |      0 |       |       |          |  
                |*126 |                        INDEX UNIQUE SCAN                     | ESS_2_UK            |    100 |      1 |      0 |00:00:00.01 |     182 |      0 |       |       |          |  
                | 127 |                       NESTED LOOPS                           |                     |      5 |      1 |      0 |00:00:00.01 |     354 |      0 |       |       |          |  
                | 128 |                        NESTED LOOPS                          |                     |      5 |     20 |    100 |00:00:00.01 |     172 |      0 |       |       |          |  
                |*129 |                         TABLE ACCESS BY INDEX ROWID          | CLNT                |      5 |      1 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*130 |                          INDEX UNIQUE SCAN                   | CLNT_PK             |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 131 |                         TABLE ACCESS BY INDEX ROWID          | ENF_SECUR_CLS       |      5 |     20 |    100 |00:00:00.01 |     162 |      0 |       |       |          |  
                | 132 |                          INDEX FULL SCAN                     | ESCL_PK             |      5 |     20 |    100 |00:00:00.01 |      81 |      0 |       |       |          |  
                |*133 |                        INDEX UNIQUE SCAN                     | ESS_2_UK            |    100 |      1 |      0 |00:00:00.01 |     182 |      0 |       |       |          |  
                | 134 |                       NESTED LOOPS                           |                     |      5 |      1 |      0 |00:00:00.01 |     364 |      0 |       |       |          |  
                | 135 |                        NESTED LOOPS                          |                     |      5 |     20 |    100 |00:00:00.01 |     182 |      0 |       |       |          |  
                | 136 |                         NESTED LOOPS                         |                     |      5 |      1 |      5 |00:00:00.01 |      20 |      0 |       |       |          |  
                |*137 |                          TABLE ACCESS BY INDEX ROWID         | CLNT                |      5 |      1 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*138 |                           INDEX UNIQUE SCAN                  | CLNT_PK             |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 139 |                          TABLE ACCESS BY INDEX ROWID         | CLNT                |      5 |     89 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*140 |                           INDEX UNIQUE SCAN                  | CLNT_PK             |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 141 |                         TABLE ACCESS BY INDEX ROWID          | ENF_SECUR_CLS       |      5 |     20 |    100 |00:00:00.01 |     162 |      0 |       |       |          |  
                | 142 |                          INDEX FULL SCAN                     | ESCL_PK             |      5 |     20 |    100 |00:00:00.01 |      81 |      0 |       |       |          |  
                |*143 |                        INDEX UNIQUE SCAN                     | ESS_2_UK            |    100 |      1 |      0 |00:00:00.01 |     182 |      0 |       |       |          |  
                | 144 |                       NESTED LOOPS                           |                     |      5 |      1 |     25 |00:00:00.01 |     374 |      0 |       |       |          |  
                | 145 |                        NESTED LOOPS                          |                     |      5 |     20 |    100 |00:00:00.01 |     192 |      0 |       |       |          |  
                | 146 |                         NESTED LOOPS                         |                     |      5 |      1 |      5 |00:00:00.01 |      30 |      0 |       |       |          |  
                | 147 |                          NESTED LOOPS                        |                     |      5 |      1 |      5 |00:00:00.01 |      20 |      0 |       |       |          |  
                |*148 |                           TABLE ACCESS BY INDEX ROWID        | CLNT                |      5 |      1 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*149 |                            INDEX UNIQUE SCAN                 | CLNT_PK             |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 150 |                           TABLE ACCESS BY INDEX ROWID        | CLNT                |      5 |     89 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*151 |                            INDEX UNIQUE SCAN                 | CLNT_PK             |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 152 |                          TABLE ACCESS BY INDEX ROWID         | CLNT                |      5 |     89 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*153 |                           INDEX UNIQUE SCAN                  | CLNT_PK             |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 154 |                         TABLE ACCESS BY INDEX ROWID          | ENF_SECUR_CLS       |      5 |     20 |    100 |00:00:00.01 |     162 |      0 |       |       |          |  
                | 155 |                          INDEX FULL SCAN                     | ESCL_PK             |      5 |     20 |    100 |00:00:00.01 |      81 |      0 |       |       |          |  
                |*156 |                        INDEX UNIQUE SCAN                     | ESS_2_UK            |    100 |      1 |     25 |00:00:00.01 |     182 |      0 |       |       |          |  
                | 157 |                   TABLE ACCESS BY INDEX ROWID                | CMPLN_STA           |      5 |      1 |      5 |00:00:00.01 |       7 |      0 |       |       |          |  
                |*158 |                    INDEX UNIQUE SCAN                         | CSTA_PK             |      5 |      1 |      5 |00:00:00.01 |       2 |      0 |       |       |          |  
                | 159 |                  TABLE ACCESS BY INDEX ROWID                 | CMPLN_STA           |      5 |      1 |      5 |00:00:00.01 |      10 |      0 |       |       |          |  
                |*160 |                   INDEX UNIQUE SCAN                          | CSTA_PK             |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 161 |                 TABLE ACCESS BY INDEX ROWID                  | INVL_PARTY          |      5 |      1 |      6 |00:00:00.01 |      21 |      0 |       |       |          |  
                |*162 |                  INDEX RANGE SCAN                            | IVP_CMP_FK_I        |      5 |      1 |      6 |00:00:00.01 |      15 |      0 |       |       |          |  
                | 163 |                TABLE ACCESS BY INDEX ROWID                   | CLNT_INVL_PARTY_TYP |      6 |      1 |      6 |00:00:00.01 |      18 |      0 |       |       |          |  
                |*164 |                 INDEX UNIQUE SCAN                            | CIPT_PK             |      6 |      1 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                |*165 |               TABLE ACCESS BY INDEX ROWID                    | INVL_PARTY_TYP      |      6 |      1 |      5 |00:00:00.01 |      12 |      0 |       |       |          |  
                |*166 |                INDEX UNIQUE SCAN                             | IVPT_PK             |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                |*167 |              TABLE ACCESS BY INDEX ROWID                     | LINK                |      5 |      1 |      5 |00:00:00.01 |      24 |      0 |       |       |          |  
                |*168 |               INDEX RANGE SCAN                               | LINK_LP_FK_I        |      5 |      1 |      5 |00:00:00.01 |      19 |      0 |       |       |          |  
                |*169 |             INDEX UNIQUE SCAN                                | EMA_PK              |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                |*170 |            INDEX UNIQUE SCAN                                 | PHO_PK              |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                | 171 |           TABLE ACCESS BY INDEX ROWID                        | ADDR                |      5 |      1 |      5 |00:00:00.01 |      20 |      0 |       |       |          |  
                |*172 |            INDEX UNIQUE SCAN                                 | ADDR_PK             |      5 |      1 |      5 |00:00:00.01 |      15 |      0 |       |       |          |  
                |*173 |          INDEX UNIQUE SCAN                                   | CNTRY_PK            |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                |*174 |         INDEX UNIQUE SCAN                                    | CTY_PK              |      5 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                |*175 |        INDEX UNIQUE SCAN                                     | STE_PK              |      5 |      1 |      5 |00:00:00.01 |       5 |      0 |       |       |          |  
                | 176 |       TABLE ACCESS BY INDEX ROWID                            | NAME                |      5 |      1 |      5 |00:00:00.01 |      20 |      0 |       |       |          |  
                |*177 |        INDEX UNIQUE SCAN                                     | NAM_PK              |      5 |      1 |      5 |00:00:00.01 |      15 |      0 |       |       |          |  
                |*178 |      INDEX RANGE SCAN                                        | IND_EE_FK_I         |      5 |      1 |      5 |00:00:00.01 |      19 |      0 |       |       |          |  
                | 179 |     SORT AGGREGATE                                           |                     |      2 |      1 |      2 |00:00:00.01 |       4 |      0 |       |       |          |  
                |*180 |      INDEX UNIQUE SCAN                                       | ESL_STFF_ENF_UK     |      2 |      1 |      2 |00:00:00.01 |       4 |      0 |       |       |          |  
                | 181 |       TABLE ACCESS BY INDEX ROWID                            | ENF_STFF_SECUR_LVL  |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                |*182 |        INDEX RANGE SCAN                                      | ESL_STFF_ENF_UK     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                | 183 |        TABLE ACCESS BY INDEX ROWID                           | ENF_STFF_SECUR_LVL  |      2 |      1 |      2 |00:00:00.01 |       6 |      0 |       |       |          |  
                |*184 |         INDEX UNIQUE SCAN                                    | ESL_STFF_ENF_UK     |      2 |      1 |      2 |00:00:00.01 |       4 |      0 |       |       |          |  
                ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
                • 5. Re: High consistent reads
                  user11949974
                  h1. PROD Explain Plan Pt 1
                  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
                  | Id  | Operation                                                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |  
                  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
                  |   1 |  TABLE ACCESS BY INDEX ROWID                                 | CMPLN               |      6 |      1 |      6 |00:00:00.01 |      14 |      0 |       |       |          |  
                  |*  2 |   INDEX UNIQUE SCAN                                          | CMP_PK              |      6 |      1 |      6 |00:00:00.01 |       8 |      0 |       |       |          |  
                  |   3 |  SORT UNIQUE                                                 |                     |      1 |      1 |      6 |00:00:37.73 |    8191K|     38 |  9216 |  9216 | 8192  (0)|  
                  |*  4 |   COUNT STOPKEY                                              |                     |      1 |        |      6 |00:00:37.73 |    8191K|     38 |       |       |          |  
                  |*  5 |    FILTER                                                    |                     |      1 |        |      6 |00:00:37.73 |    8191K|     38 |       |       |          |  
                  |   6 |     NESTED LOOPS SEMI                                        |                     |      1 |      1 |      6 |00:00:37.73 |    8191K|     38 |       |       |          |  
                  |*  7 |      HASH JOIN SEMI                                          |                     |      1 |      1 |      6 |00:00:37.73 |    8191K|     38 |   706K|   706K|  473K (0)|  
                  |   8 |       NESTED LOOPS OUTER                                     |                     |      1 |      1 |      6 |00:00:10.50 |    8191K|     38 |       |       |          |  
                  |   9 |        NESTED LOOPS OUTER                                    |                     |      1 |      1 |      6 |00:00:10.50 |    8191K|     38 |       |       |          |  
                  |  10 |         NESTED LOOPS OUTER                                   |                     |      1 |      1 |      6 |00:00:10.50 |    8191K|     38 |       |       |          |  
                  |  11 |          NESTED LOOPS OUTER                                  |                     |      1 |      1 |      6 |00:00:10.50 |    8191K|     38 |       |       |          |  
                  |  12 |           NESTED LOOPS OUTER                                 |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|     29 |       |       |          |  
                  |  13 |            NESTED LOOPS OUTER                                |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|     29 |       |       |          |  
                  |  14 |             NESTED LOOPS OUTER                               |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|     29 |       |       |          |  
                  |  15 |              NESTED LOOPS OUTER                              |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|     29 |       |       |          |  
                  |  16 |               NESTED LOOPS OUTER                             |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|     19 |       |       |          |  
                  |  17 |                NESTED LOOPS OUTER                            |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|     12 |       |       |          |  
                  |  18 |                 NESTED LOOPS OUTER                           |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|     12 |       |       |          |  
                  |  19 |                  NESTED LOOPS                                |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|      8 |       |       |          |  
                  |  20 |                   NESTED LOOPS OUTER                         |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|      4 |       |       |          |  
                  |  21 |                    NESTED LOOPS OUTER                        |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|      4 |       |       |          |  
                  |  22 |                     NESTED LOOPS OUTER                       |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|      4 |       |       |          |  
                  |  23 |                      NESTED LOOPS OUTER                      |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|      4 |       |       |          |  
                  |  24 |                       NESTED LOOPS OUTER                     |                     |      1 |      1 |      6 |00:00:10.49 |    8191K|      2 |       |       |          |  
                  |  25 |                        NESTED LOOPS                          |                     |      1 |      1 |      6 |00:00:09.43 |    8191K|      0 |       |       |          |  
                  |  26 |                         NESTED LOOPS OUTER                   |                     |      1 |      1 |    125K|00:00:35.51 |    7689K|      0 |       |       |          |  
                  |  27 |                          NESTED LOOPS                        |                     |      1 |      1 |    125K|00:00:35.26 |    7689K|      0 |       |       |          |  
                  |  28 |                           NESTED LOOPS                       |                     |      1 |      1 |    125K|00:00:32.13 |    7061K|      0 |       |       |          |  
                  |  29 |                            NESTED LOOPS OUTER                |                     |      1 |      1 |    125K|00:00:29.62 |    6559K|      0 |       |       |          |  
                  |  30 |                             NESTED LOOPS OUTER               |                     |      1 |      1 |    125K|00:00:28.74 |    6327K|      0 |       |       |          |  
                  |  31 |                              NESTED LOOPS OUTER              |                     |      1 |      1 |    125K|00:00:27.73 |    6094K|      0 |       |       |          |  
                  |  32 |                               NESTED LOOPS OUTER             |                     |      1 |      1 |    125K|00:00:27.11 |    5880K|      0 |       |       |          |  
                  |  33 |                                NESTED LOOPS OUTER            |                     |      1 |      1 |    125K|00:00:25.85 |    5559K|      0 |       |       |          |  
                  |  34 |                                 NESTED LOOPS OUTER           |                     |      1 |      1 |    125K|00:00:24.72 |    5203K|      0 |       |       |          |  
                  |  35 |                                  NESTED LOOPS OUTER          |                     |      1 |      1 |    125K|00:00:23.34 |    4847K|      0 |       |       |          |  
                  |  36 |                                   NESTED LOOPS OUTER         |                     |      1 |      1 |    125K|00:00:22.21 |    4534K|      0 |       |       |          |  
                  |  37 |                                    NESTED LOOPS OUTER        |                     |      1 |      1 |    125K|00:00:21.08 |    4221K|      0 |       |       |          |  
                  |  38 |                                     NESTED LOOPS OUTER       |                     |      1 |      1 |    125K|00:00:20.33 |    4013K|      0 |       |       |          |  
                  |  39 |                                      NESTED LOOPS OUTER      |                     |      1 |      1 |    125K|00:00:19.08 |    3701K|      0 |       |       |          |  
                  |  40 |                                       NESTED LOOPS OUTER     |                     |      1 |      1 |    125K|00:00:13.30 |    2491K|      0 |       |       |          |  
                  |  41 |                                        NESTED LOOPS          |                     |      1 |      1 |    125K|00:00:12.43 |    2246K|      0 |       |       |          |  
                  |  42 |                                         NESTED LOOPS         |                     |      1 |      1 |    125K|00:00:11.30 |    1995K|      0 |       |       |          |  
                  |  43 |                                          NESTED LOOPS OUTER  |                     |      1 |      1 |    125K|00:00:10.17 |    1619K|      0 |       |       |          |  
                  |  44 |                                           NESTED LOOPS OUTER |                     |      1 |      1 |    125K|00:00:09.04 |    1368K|      0 |       |       |          |  
                  |  45 |                                            NESTED LOOPS OUTER|                     |      1 |      1 |    125K|00:00:07.66 |     991K|      0 |       |       |          |  
                  |  46 |                                             NESTED LOOPS     |                     |      1 |      1 |    125K|00:00:07.03 |     889K|      0 |       |       |          |  
                  |  47 |                                              NESTED LOOPS OUT|                     |      1 |      1 |    125K|00:00:05.90 |     638K|      0 |       |       |          |  
                  |  48 |                                               NESTED LOOPS OU|                     |      1 |      1 |    125K|00:00:05.52 |     638K|      0 |       |       |          |  
                  |  49 |                                                NESTED LOOPS  |                     |      1 |      1 |    125K|00:00:05.02 |     638K|      0 |       |       |          |  
                  |  50 |                                                 NESTED LOOPS |                     |      1 |      1 |    125K|00:00:03.01 |     387K|      0 |       |       |          |  
                  |  51 |                                                  NESTED LOOPS|                     |      1 |      4 |    125K|00:00:00.38 |   11175 |      0 |       |       |          |  
                  |* 52 |                                                   HASH JOIN  |                     |      1 |      1 |     89 |00:00:00.01 |      45 |      0 |  1517K|  1517K|  633K (0)|  
                  |* 53 |                                                    TABLE ACCE| INVL_PARTY_TYP      |      1 |      1 |      1 |00:00:00.01 |       7 |      0 |       |       |          |  
                  |  54 |                                                    TABLE ACCE| CLNT_INVL_PARTY_TYP |      1 |  12357 |  12592 |00:00:00.01 |      38 |      0 |       |       |          |  
                  |  55 |                                                   TABLE ACCES| INVL_PARTY          |     89 |     13 |    125K|00:00:00.38 |   11130 |      0 |       |       |          |  
                  |* 56 |                                                    INDEX RANG| IVP_CIPT_FK_I       |     89 |    622 |    125K|00:00:00.01 |     415 |      0 |       |       |          |  
                  |* 57 |                                                  TABLE ACCESS| LINK                |    125K|      1 |    125K|00:00:02.50 |     376K|      0 |       |       |          |  
                  |* 58 |                                                   INDEX RANGE| LINK_LP_FK_I        |    125K|      1 |    125K|00:00:01.60 |     251K|      0 |       |       |          |  
                  |  59 |                                                 TABLE ACCESS | CMPLN               |    125K|      1 |    125K|00:00:01.79 |     251K|      0 |       |       |          |  
                  |* 60 |                                                  INDEX UNIQUE| CMP_PK              |    125K|      1 |    125K|00:00:00.72 |     125K|      0 |       |       |          |  
                  |* 61 |                                                INDEX UNIQUE S| ECP_PK              |    125K|      1 |    125K|00:00:00.26 |       2 |      0 |       |       |          |  
                  |* 62 |                                               INDEX UNIQUE SC| ESCL_PK             |    125K|      1 |    125K|00:00:00.26 |       2 |      0 |       |       |          |  
                  |  63 |                                              TABLE ACCESS BY | CLNT                |    125K|      1 |    125K|00:00:01.01 |     250K|      0 |       |       |          |  
                  |* 64 |                                               INDEX UNIQUE SC| CLNT_PK             |    125K|      1 |    125K|00:00:00.48 |     125K|      0 |       |       |          |  
                  |* 65 |                                             INDEX UNIQUE SCAN| ECMPLXYT_PK         |    125K|      1 |    101K|00:00:00.39 |     101K|      0 |       |       |          |  
                  |  66 |                                            TABLE ACCESS BY IN| CLNT_CMPLN_STA      |    125K|      1 |    125K|00:00:01.10 |     376K|      0 |       |       |          |  
                  |* 67 |                                             INDEX UNIQUE SCAN| CCST_PK             |    125K|      1 |    125K|00:00:00.65 |     250K|      0 |       |       |          |  
                  |  68 |                                           TABLE ACCESS BY IND| CMPLN_STA           |    125K|      1 |    125K|00:00:00.85 |     250K|      0 |       |       |          |  
                  |* 69 |                                            INDEX UNIQUE SCAN | CSTA_PK             |    125K|      1 |    125K|00:00:00.42 |     125K|      0 |       |       |          |  
                  |  70 |                                          TABLE ACCESS BY INDE| CLNT_CMPLN_STA      |    125K|      1 |    125K|00:00:01.08 |     376K|      0 |       |       |          |  
                  |* 71 |                                           INDEX UNIQUE SCAN  | CCST_PK             |    125K|      1 |    125K|00:00:00.59 |     250K|      0 |       |       |          |  
                  |  72 |                                         TABLE ACCESS BY INDEX| CMPLN_STA           |    125K|      1 |    125K|00:00:00.92 |     250K|      0 |       |       |          |  
                  |* 73 |                                          INDEX UNIQUE SCAN   | CSTA_PK             |    125K|      1 |    125K|00:00:00.41 |     125K|      0 |       |       |          |  
                  |* 74 |                                        INDEX UNIQUE SCAN     | STA_PK              |    125K|      1 |    122K|00:00:00.68 |     244K|      0 |       |       |          |  
                  |* 75 |                                       TABLE ACCESS BY INDEX R| CMPLN_TXT           |    125K|      1 |  33273 |00:00:03.69 |    1209K|      0 |       |       |          |  
                  |* 76 |                                        INDEX RANGE SCAN      | CT1_CMP_FK_I        |    125K|      4 |    414K|00:00:02.88 |     790K|      0 |       |       |          |  
                  |  77 |                                      TABLE ACCESS BY INDEX RO| BRD_ENF_CDE         |    125K|      1 |    104K|00:00:01.04 |     312K|      0 |       |       |          |  
                  |* 78 |                                       INDEX UNIQUE SCAN      | BECD_PK             |    125K|      1 |    104K|00:00:00.61 |     208K|      0 |       |       |          |  
                  |* 79 |                                     INDEX UNIQUE SCAN        | ECD_PK              |    125K|      1 |    104K|00:00:00.51 |     208K|      0 |       |       |          |  
                  |  80 |                                    TABLE ACCESS BY INDEX ROWI| BRD_ENF_CDE         |    125K|      1 |    104K|00:00:00.96 |     312K|      0 |       |       |          |  
                  |* 81 |                                     INDEX UNIQUE SCAN        | BECD_PK             |    125K|      1 |    104K|00:00:00.56 |     208K|      0 |       |       |          |  
                  |  82 |                                   TABLE ACCESS BY INDEX ROWID| ENF_CDE             |    125K|      1 |    104K|00:00:00.90 |     312K|      0 |       |       |          |  
                  |* 83 |                                    INDEX UNIQUE SCAN         | ECD_PK              |    125K|      1 |    104K|00:00:00.51 |     208K|      0 |       |       |          |  
                  |  84 |                                  TABLE ACCESS BY INDEX ROWID | BRD_ENF_CDE         |    125K|      1 |    118K|00:00:01.04 |     356K|      0 |       |       |          |  
                  |* 85 |                                   INDEX UNIQUE SCAN          | BECD_PK             |    125K|      1 |    118K|00:00:00.61 |     237K|      0 |       |       |          |  
                  |  86 |                                 TABLE ACCESS BY INDEX ROWID  | ENF_CDE             |    125K|      1 |    118K|00:00:00.98 |     356K|      0 |       |       |          |  
                  |* 87 |                                  INDEX UNIQUE SCAN           | ECD_PK              |    125K|      1 |    118K|00:00:00.56 |     237K|      0 |       |       |          |  
                  |  88 |                                TABLE ACCESS BY INDEX ROWID   | BRD_ENF_CDE         |    125K|      1 |    106K|00:00:00.94 |     320K|      0 |       |       |          |  
                  |* 89 |                                 INDEX UNIQUE SCAN            | BECD_PK             |    125K|      1 |    106K|00:00:00.55 |     213K|      0 |       |       |          |  
                  |* 90 |                               INDEX UNIQUE SCAN              | ECD_PK              |    125K|      1 |    106K|00:00:00.47 |     213K|      0 |       |       |          |  
                  |  91 |                              TABLE ACCESS BY INDEX ROWID     | BRD_ENF_CDE         |    125K|      1 |  77559 |00:00:00.76 |     232K|      0 |       |       |          |  
                  |* 92 |                               INDEX UNIQUE SCAN              | BECD_PK             |    125K|      1 |  77559 |00:00:00.43 |     155K|      0 |       |       |          |  
                  |  93 |                             TABLE ACCESS BY INDEX ROWID      | ENF_CDE             |    125K|      1 |  77559 |00:00:00.72 |     232K|      0 |       |       |          |  
                  |* 94 |                              INDEX UNIQUE SCAN               | ECD_PK              |    125K|      1 |  77559 |00:00:00.39 |     155K|      0 |       |       |          |  
                  • 6. Re: High consistent reads
                    user11949974
                    h1. PROD Explain Plan Pt 2
                    |  95 |                            TABLE ACCESS BY INDEX ROWID       | RSPN                |    125K|      1 |    125K|00:00:02.44 |     501K|      0 |       |       |          |  
                    |* 96 |                             INDEX RANGE SCAN                 | RES_CMP_FK_I        |    125K|      1 |    125K|00:00:01.69 |     376K|      0 |       |       |          |  
                    |* 97 |                           TABLE ACCESS BY INDEX ROWID        | LINK                |    125K|      1 |    125K|00:00:02.96 |     627K|      0 |       |       |          |  
                    |* 98 |                            INDEX RANGE SCAN                  | LINK_LP_FK_I        |    125K|      1 |    125K|00:00:02.12 |     501K|      0 |       |       |          |  
                    |* 99 |                          INDEX UNIQUE SCAN                   | IR_PK               |    125K|      1 |      0 |00:00:00.10 |       0 |      0 |       |       |          |  
                    |*100 |                         TABLE ACCESS BY INDEX ROWID          | NAME                |    125K|      1 |      6 |00:00:01.86 |     501K|      0 |       |       |          |  
                    |*101 |                          INDEX UNIQUE SCAN                   | NAM_PK              |    125K|      1 |    125K|00:00:01.18 |     376K|      0 |       |       |          |  
                    | 102 |                        TABLE ACCESS BY INDEX ROWID           | LIC                 |      6 |      1 |      6 |00:00:01.06 |      25 |      2 |       |       |          |  
                    |*103 |                         INDEX UNIQUE SCAN                    | LIC_PK              |      6 |      1 |      6 |00:00:01.06 |      18 |      1 |       |       |          |  
                    |*104 |                       INDEX RANGE SCAN                       | IND_EE_FK_I         |      6 |      1 |      0 |00:00:00.01 |      18 |      2 |       |       |          |  
                    |*105 |                      INDEX RANGE SCAN                        | IND_EE_FK_I         |      6 |      1 |      5 |00:00:00.01 |      23 |      0 |       |       |          |  
                    |*106 |                     INDEX UNIQUE SCAN                        | EMA_PK              |      6 |      1 |      1 |00:00:00.01 |       3 |      0 |       |       |          |  
                    |*107 |                    INDEX UNIQUE SCAN                         | EMA_PK              |      6 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    | 108 |                   TABLE ACCESS BY INDEX ROWID                | XENT                |      6 |      1 |      6 |00:00:00.01 |      24 |      4 |       |       |          |  
                    |*109 |                    INDEX UNIQUE SCAN                         | EE_PK               |      6 |      1 |      6 |00:00:00.01 |      18 |      2 |       |       |          |  
                    |*110 |                  INDEX UNIQUE SCAN                           | PHO_PK              |      6 |      1 |      6 |00:00:00.01 |      18 |      4 |       |       |          |  
                    |*111 |                 INDEX UNIQUE SCAN                            | PHO_PK              |      6 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    | 112 |                TABLE ACCESS BY INDEX ROWID                   | NAME                |      6 |      1 |      6 |00:00:00.01 |      24 |      7 |       |       |          |  
                    |*113 |                 INDEX UNIQUE SCAN                            | NAM_PK              |      6 |      1 |      6 |00:00:00.01 |      18 |      5 |       |       |          |  
                    | 114 |               TABLE ACCESS BY INDEX ROWID                    | ADDR                |      6 |      1 |      6 |00:00:00.01 |      24 |     10 |       |       |          |  
                    |*115 |                INDEX UNIQUE SCAN                             | ADDR_PK             |      6 |      1 |      6 |00:00:00.01 |      18 |      4 |       |       |          |  
                    | 116 |              TABLE ACCESS BY INDEX ROWID                     | STATE               |      6 |      1 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    |*117 |               INDEX UNIQUE SCAN                              | STE_PK              |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    | 118 |             TABLE ACCESS BY INDEX ROWID                      | CNTRY               |      6 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |  
                    |*119 |              INDEX UNIQUE SCAN                               | CNTRY_PK            |      6 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |  
                    | 120 |            TABLE ACCESS BY INDEX ROWID                       | CNTY                |      6 |      1 |      6 |00:00:00.01 |      18 |      0 |       |       |          |  
                    |*121 |             INDEX UNIQUE SCAN                                | CTY_PK              |      6 |      1 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    | 122 |           TABLE ACCESS BY INDEX ROWID                        | ADDR                |      6 |      1 |      6 |00:00:00.01 |      24 |      9 |       |       |          |  
                    |*123 |            INDEX UNIQUE SCAN                                 | ADDR_PK             |      6 |      1 |      6 |00:00:00.01 |      18 |      4 |       |       |          |  
                    |*124 |          INDEX UNIQUE SCAN                                   | STE_PK              |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    |*125 |         INDEX UNIQUE SCAN                                    | CNTRY_PK            |      6 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |  
                    |*126 |        INDEX UNIQUE SCAN                                     | CTY_PK              |      6 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    | 127 |       VIEW                                                   | VW_NSO_1            |      1 |      4 |     45 |00:00:00.01 |      32 |      0 |       |       |          |  
                    |*128 |        CONNECT BY WITH FILTERING                             |                     |      1 |        |     45 |00:00:00.01 |      32 |      0 |  9216 |  9216 | 8192  (0)|  
                    | 129 |         TABLE ACCESS BY INDEX ROWID                          | CLNT                |      1 |        |      1 |00:00:00.01 |       2 |      0 |       |       |          |  
                    |*130 |          INDEX UNIQUE SCAN                                   | CLNT_PK             |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |  
                    | 131 |         NESTED LOOPS                                         |                     |      4 |        |     88 |00:00:00.01 |      30 |      0 |       |       |          |  
                    | 132 |          BUFFER SORT                                         |                     |      4 |        |     89 |00:00:00.01 |       0 |      0 |  9216 |  9216 | 8192  (0)|  
                    | 133 |           CONNECT BY PUMP                                    |                     |      4 |        |     89 |00:00:00.01 |       0 |      0 |       |       |          |  
                    | 134 |          TABLE ACCESS BY INDEX ROWID                         | CLNT                |     89 |      4 |     88 |00:00:00.01 |      30 |      0 |       |       |          |  
                    |*135 |           INDEX RANGE SCAN                                   | CLNT_CLNT_FK_I      |     89 |      4 |     88 |00:00:00.01 |       4 |      0 |       |       |          |  
                    | 136 |         TABLE ACCESS FULL                                    | CLNT                |      0 |      4 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    |*137 |      VIEW                                                    | VW_ENF_STFF_SECUR   |      6 |    153 |      6 |00:00:00.01 |     193 |      0 |       |       |          |  
                    | 138 |       SORT UNIQUE                                            |                     |      6 |        |     21 |00:00:00.01 |     193 |      0 |  9216 |  9216 | 8192  (0)|  
                    | 139 |        UNION-ALL PARTITION                                   |                     |      6 |        |     30 |00:00:00.01 |     193 |      0 |       |       |          |  
                    | 140 |         NESTED LOOPS                                         |                     |      6 |      1 |      0 |00:00:00.01 |      30 |      0 |       |       |          |  
                    | 141 |          NESTED LOOPS                                        |                     |      6 |      1 |      0 |00:00:00.01 |      30 |      0 |       |       |          |  
                    |*142 |           TABLE ACCESS BY INDEX ROWID                        | CLNT                |      6 |      1 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    |*143 |            INDEX UNIQUE SCAN                                 | CLNT_PK             |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    |*144 |           TABLE ACCESS BY INDEX ROWID                        | ENF_STFF_SECUR      |      6 |      1 |      0 |00:00:00.01 |      18 |      0 |       |       |          |  
                    |*145 |            INDEX RANGE SCAN                                  | ESS_STA_FK_I        |      6 |      6 |     30 |00:00:00.01 |      12 |      0 |       |       |          |  
                    | 146 |          TABLE ACCESS BY INDEX ROWID                         | ENF_SECUR_CLS       |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    |*147 |           INDEX UNIQUE SCAN                                  | ESCL_PK             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    | 148 |         NESTED LOOPS                                         |                     |      6 |      1 |      0 |00:00:00.01 |      30 |      0 |       |       |          |  
                    | 149 |          NESTED LOOPS                                        |                     |      6 |      1 |      0 |00:00:00.01 |      30 |      0 |       |       |          |  
                    |*150 |           TABLE ACCESS BY INDEX ROWID                        | CLNT                |      6 |      1 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    |*151 |            INDEX UNIQUE SCAN                                 | CLNT_PK             |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    |*152 |           TABLE ACCESS BY INDEX ROWID                        | ENF_STFF_SECUR      |      6 |      1 |      0 |00:00:00.01 |      18 |      0 |       |       |          |  
                    |*153 |            INDEX RANGE SCAN                                  | ESS_STA_FK_I        |      6 |      6 |     30 |00:00:00.01 |      12 |      0 |       |       |          |  
                    | 154 |          TABLE ACCESS BY INDEX ROWID                         | ENF_SECUR_CLS       |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    |*155 |           INDEX UNIQUE SCAN                                  | ESCL_PK             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    | 156 |         NESTED LOOPS                                         |                     |      6 |      1 |      0 |00:00:00.01 |      42 |      0 |       |       |          |  
                    | 157 |          NESTED LOOPS                                        |                     |      6 |      1 |      0 |00:00:00.01 |      42 |      0 |       |       |          |  
                    | 158 |           NESTED LOOPS                                       |                     |      6 |      1 |      6 |00:00:00.01 |      24 |      0 |       |       |          |  
                    |*159 |            TABLE ACCESS BY INDEX ROWID                       | CLNT                |      6 |      1 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    |*160 |             INDEX UNIQUE SCAN                                | CLNT_PK             |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    | 161 |            TABLE ACCESS BY INDEX ROWID                       | CLNT                |      6 |     89 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    |*162 |             INDEX UNIQUE SCAN                                | CLNT_PK             |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    |*163 |           TABLE ACCESS BY INDEX ROWID                        | ENF_STFF_SECUR      |      6 |      1 |      0 |00:00:00.01 |      18 |      0 |       |       |          |  
                    |*164 |            INDEX RANGE SCAN                                  | ESS_STA_FK_I        |      6 |      6 |     30 |00:00:00.01 |      12 |      0 |       |       |          |  
                    | 165 |          TABLE ACCESS BY INDEX ROWID                         | ENF_SECUR_CLS       |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    |*166 |           INDEX UNIQUE SCAN                                  | ESCL_PK             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    | 167 |         NESTED LOOPS                                         |                     |      6 |      1 |     30 |00:00:00.01 |      91 |      0 |       |       |          |  
                    | 168 |          NESTED LOOPS                                        |                     |      6 |      1 |     30 |00:00:00.01 |      54 |      0 |       |       |          |  
                    | 169 |           NESTED LOOPS                                       |                     |      6 |      1 |      6 |00:00:00.01 |      36 |      0 |       |       |          |  
                    | 170 |            NESTED LOOPS                                      |                     |      6 |      1 |      6 |00:00:00.01 |      24 |      0 |       |       |          |  
                    |*171 |             TABLE ACCESS BY INDEX ROWID                      | CLNT                |      6 |      1 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    |*172 |              INDEX UNIQUE SCAN                               | CLNT_PK             |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    | 173 |             TABLE ACCESS BY INDEX ROWID                      | CLNT                |      6 |     89 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    |*174 |              INDEX UNIQUE SCAN                               | CLNT_PK             |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    | 175 |            TABLE ACCESS BY INDEX ROWID                       | CLNT                |      6 |     89 |      6 |00:00:00.01 |      12 |      0 |       |       |          |  
                    |*176 |             INDEX UNIQUE SCAN                                | CLNT_PK             |      6 |      1 |      6 |00:00:00.01 |       6 |      0 |       |       |          |  
                    |*177 |           TABLE ACCESS BY INDEX ROWID                        | ENF_STFF_SECUR      |      6 |      1 |     30 |00:00:00.01 |      18 |      0 |       |       |          |  
                    |*178 |            INDEX RANGE SCAN                                  | ESS_STA_FK_I        |      6 |      6 |     30 |00:00:00.01 |      12 |      0 |       |       |          |  
                    | 179 |          TABLE ACCESS BY INDEX ROWID                         | ENF_SECUR_CLS       |     30 |      1 |     30 |00:00:00.01 |      37 |      0 |       |       |          |  
                    |*180 |           INDEX UNIQUE SCAN                                  | ESCL_PK             |     30 |      1 |     30 |00:00:00.01 |       7 |      0 |       |       |          |  
                    | 181 |     SORT AGGREGATE                                           |                     |      2 |      1 |      2 |00:00:00.01 |       4 |      0 |       |       |          |  
                    |*182 |      INDEX UNIQUE SCAN                                       | ESL_STFF_ENF_UK     |      2 |      1 |      2 |00:00:00.01 |       4 |      0 |       |       |          |  
                    | 183 |       TABLE ACCESS BY INDEX ROWID                            | ENF_STFF_SECUR_LVL  |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    |*184 |        INDEX RANGE SCAN                                      | ESL_STFF_ENF_UK     |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |  
                    | 185 |        TABLE ACCESS BY INDEX ROWID                           | ENF_STFF_SECUR_LVL  |      2 |      1 |      2 |00:00:00.01 |       6 |      0 |       |       |          |  
                    |*186 |         INDEX UNIQUE SCAN                                    | ESL_STFF_ENF_UK     |      2 |      1 |      2 |00:00:00.01 |       4 |      0 |       |       |          |  
                    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
                    • 7. Re: High consistent reads
                      Nikolay Savvinov
                      Hi,

                      1) obviously, the performance problem is caused by the poor choice of the driving table
                      2) this, in its turn, is caused by wrong cardinality estimates in steps 51-56 of the production plan: the optimizer expects 4 rows where 120k rows are returned
                      3) in order to understand the reason for inaccurate cardinality estimates, we need to see the predicates which you haven't posted, and maybe also stats on columns involved in the predicate, as well the actual and estimated rowcounts


                      Best regards,
                      Nikolay
                      • 8. Re: High consistent reads
                        user11949974
                        I took a look at the rows in the plan where there were large discrepencies betweeen the expected and actual rows which gave me the tables to look at. I looked at the stats on the columns in those tables and noticed that histograms were being generated in production but not in the lower environments. I used the procedures to delete stats and regenerate them without histograms, and voila, consistent reads and performance got much better with a similar execution path as in the test environment.
                        exec DBMS_STATS.DELETE_TABLE_STATS ('schema', 't1');
                        exec DBMS_STATS.GATHER_TABLE_STATS (ownname =>'schema',Tabname => 't1',method_opt=> 'for all columns size 1');