3 Replies Latest reply on Mar 29, 2016 11:21 AM by Jonathan Lewis

    e-rows a-rows counts mismatch in execution plan

    2676873

      Hi i have below query . Execution plan is also there.

       

       

      select wq_m.*,t1.*,t2.*,t3.*

                  from  table(v_c_list) c

                    left outer join table1 t1 on (c.id = t1.id and t1.id_ind = 'Y')

                    left outer join table2 t2 on (t1.id = t2.id)

                    left outer join table3 t3 on (t1.id = t3.id);

        

       

      -----------------------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                                                                   | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

      -----------------------------------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                                              |                                |      1 |           |   1989 |00:00:04.96 |    9280 |    897 |

      |   1 |  NESTED LOOPS OUTER                                         |                               |      1 |   2125 |   1989 |00:00:04.96 |    9280 |    897 |

      |   2 |   NESTED LOOPS OUTER                                        |                               |      1 |   2125 |   1989 |00:00:04.93 |    9271 |    895 |

      |   3 |    NESTED LOOPS OUTER                                       |                               |      1 |   2125 |   1989 |00:00:00.03 |    5732 |      0 |

      |   4 |     COLLECTION ITERATOR PICKLER FETCH          |                               |      1 |   1989 |   1989 |00:00:00.01 |       0 |      0 |

      |*  5 |     TABLE ACCESS BY INDEX ROWID                     | TABLE1                  |   1989 |      1 |   1178 |00:00:00.03 |    5732 |      0 |

      |*  6 |      INDEX RANGE SCAN                                         | IDX_TABL1             |   1989 |      2 |   2197 |00:00:00.02 |    3545 |      0 |

      |   7 |    TABLE ACCESS BY INDEX ROWID                      | TABLE2                  |   1989 |      1 |   1178 |00:00:03.26 |    3539 |    895 |

      |*  8 |     INDEX UNIQUE SCAN                                         | IDX_TABLE2_PK    |   1989 |      1 |   1178 |00:00:03.25 |    2359 |    895 |

      |   9 |   TABLE ACCESS BY INDEX ROWID                       | TABLE3                  |   1989 |      1 |      0 |00:00:00.03 |       9 |      2 |

      |* 10 |    INDEX UNIQUE SCAN                                         | IDX_TABLE3_PK    |   1989 |      1 |      0 |00:00:00.03 |       9 |      2 |

      -----------------------------------------------------------------------------------------------------------------------------------


      Predicate Information (identified by operation id):

      ---------------------------------------------------

       

         5 - filter("t1.ID_IND"='Y')

         6 - access("t1.ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))

         8 - access("t1.ID"="t2.ID")

        10 - access("t1.ID"="t3"."ID")

       

      I am using a table collection list inorder to query above 3 tables( All 3 tables are of size = 1.5 Million. Indexes are intact and good. Collection size is avg 2000 rows . But the execution plan shows e-rows,a-rows difference from step 5 . In step 5 - it should be e-rows = 1989 which is actually the

      collection list count. I am assuming Query is running slow due to this as CBO doing a wrong estimates. How to fix this ?

        • 1. Re: e-rows a-rows counts mismatch in execution plan
          Tubby

          2676873 wrote:

           

          Hi i have below query . Execution plan is also there.

           

           

          select wq_m.*,t1.*,t2.*,t3.*

                      from  table(v_c_list) c

                        left outer join table1 t1 on (c.id = t1.id and t1.id_ind = 'Y')

                        left outer join table2 t2 on (t1.id = t2.id)

                        left outer join table3 t3 on (t1.id = t3.id);

           

           

          -----------------------------------------------------------------------------------------------------------------------------------

          | Id  | Operation                                                                   | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

          -----------------------------------------------------------------------------------------------------------------------------------

          |   0 | SELECT STATEMENT                                              |                                |      1 |           |   1989 |00:00:04.96 |    9280 |    897 |

          |   1 |  NESTED LOOPS OUTER                                         |                               |      1 |   2125 |   1989 |00:00:04.96 |    9280 |    897 |

          |   2 |   NESTED LOOPS OUTER                                        |                               |      1 |   2125 |   1989 |00:00:04.93 |    9271 |    895 |

          |   3 |    NESTED LOOPS OUTER                                       |                               |      1 |   2125 |   1989 |00:00:00.03 |    5732 |      0 |

          |   4 |     COLLECTION ITERATOR PICKLER FETCH          |                               |      1 |   1989 |   1989 |00:00:00.01 |       0 |      0 |

          |*  5 |     TABLE ACCESS BY INDEX ROWID                     | TABLE1                  |   1989 |      1 |   1178 |00:00:00.03 |    5732 |      0 |

          |*  6 |      INDEX RANGE SCAN                                         | IDX_TABL1             |   1989 |      2 |   2197 |00:00:00.02 |    3545 |      0 |

          |   7 |    TABLE ACCESS BY INDEX ROWID                      | TABLE2                  |   1989 |      1 |   1178 |00:00:03.26 |    3539 |    895 |

          |*  8 |     INDEX UNIQUE SCAN                                         | IDX_TABLE2_PK    |   1989 |      1 |   1178 |00:00:03.25 |    2359 |    895 |

          |   9 |   TABLE ACCESS BY INDEX ROWID                       | TABLE3                  |   1989 |      1 |      0 |00:00:00.03 |       9 |      2 |

          |* 10 |    INDEX UNIQUE SCAN                                         | IDX_TABLE3_PK    |   1989 |      1 |      0 |00:00:00.03 |       9 |      2 |

          -----------------------------------------------------------------------------------------------------------------------------------


          Predicate Information (identified by operation id):

          ---------------------------------------------------

           

             5 - filter("t1.ID_IND"='Y')

             6 - access("t1.ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))

             8 - access("t1.ID"="t2.ID")

            10 - access("t1.ID"="t3"."ID")

           

          I am using a table collection list inorder to query above 3 tables( All 3 tables are of size = 1.5 Million. Indexes are intact and good. Collection size is avg 2000 rows . But the execution plan shows e-rows,a-rows difference from step 5 . In step 5 - it should be e-rows = 1989 which is actually the

          collection list count. I am assuming Query is running slow due to this as CBO doing a wrong estimates. How to fix this ?

           

          Step 4 is doing a nested loop join (outer) in to step 5/6, you see the STARTS column is 1989 in step 6 because Oracle is performing a nested loops operation that many times in to the index.

           

          If you have 2000 entries in the in memory object you may be better off loading that in to a GTT which you can use to set the statistics of in order to give Oracle more information about the underlying data (or possibly just let dynamic sampling do it's job).

           

          Do you really need LEFT OUTER JOIN's across T1,2 and 3? It seems odd that you would drive the query off the array and then hit T2 and T3 based on the data in T1 instead of joining from the array itself.

           

          Cheers,

          • 2. Re: e-rows a-rows counts mismatch in execution plan
            gdanby

            |*  5 |     TABLE ACCESS BY INDEX ROWID                     | TABLE1                  |   1989 |      1 |   1178 |00:00:00.03 |    5732 |      0 |

            |*  6 |      INDEX RANGE SCAN                                         | IDX_TABL1             |   1989 |      2 |   2197 |00:00:00.02 |    3545 |      0 |

            |   7 |    TABLE ACCESS BY INDEX ROWID                      | TABLE2                  |   1989 |      1 |   1178 |00:00:03.26 |    3539 |    895 |

            |*  8 |     INDEX UNIQUE SCAN                                         | IDX_TABLE2_PK    |   1989 |      1 |   1178 |00:00:03.25 |    2359 |    895 |

             

            Predicate Information (identified by operation id):

            ---------------------------------------------------

             

               6 - access("t1.ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))

               8 - access("t1.ID"="t2.ID")

              10 - access("t1.ID"="t3"."ID")

             

            You can always use the CARDINALITY hint to influence the plan with regard to TABLE functions (AskTom suggests this is a safe hint), although in your case the estimated cardinality doesn't seem to be wrong at all - it's spot on.

             

            It seems to me that the bulk of the work is being done in line 8 - 895 disk reads. I'd be wondering exactly why this is so different from line 6 where there are no disk reads, so all the index leaf blocks are being read from buffer cache. Assuming that the values for the "ID" column are so spread out, what is so different about lines 6 and 8 ..? Is it a case of the TABLE1 index being accessed more frequently so is held in buffer cache, or is it to do with how the 2 indexes are stored ...

            • 3. Re: e-rows a-rows counts mismatch in execution plan
              Jonathan Lewis

              To address your comment about e-rows and a-rows being different.  The E-rows figure is the estimated number of rows for each execution (start) of the line; A-rows is the cumulative number of rows that flowed through that line over the course of the whole query.  If the optimizer has done a perfect job of predication than you would get A-rows = E-rows * Starts.

               

              As gdanby has pointed out, your problem seems to relate to the cost of reading a lot of (leaf) blocks from the idx_table2_pk. GIven the excellent caching for visits to the table that follows the index access it looks as if there MIGHT be a better index available that the optimizer has failed to pick.

               

               

              Regards

              Jonathan Lewis