This discussion is archived
8 Replies Latest reply: Feb 10, 2013 2:29 PM by user11949974 RSS

High consistent reads

user11949974 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    post both EXPLAIN PLANs
  • 2. Re: High consistent reads
    damorgan Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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');

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points