2 Replies Latest reply on Mar 3, 2013 4:12 AM by Yasu

    Wrong estimation of ROWS and COST

    Yasu
      Oracle Version : 11.2.0.7

      Below plan is estimating cost as 0 and rows as 1, all the tables are having statistics on them.

      Could any one help me in finding the reason for wrong estimation of Cost and Rows .
      select           BIT.BIT_id as BITId, 
                BIT.BIT_tp as BITType, 
                BIT.rgs_cd as rgsCd, 
                BIT.source_priority as sourcePriority, 
                BIT.grp as grp, 
                BIT.prd_item_id as prdItemId, 
                BIT.prt_dt as printDate, 
                BIT.partition_key as partitionKey, 
                BIT.guid, 
                BIT.image_guid, 
                BIT.full_pub_fl ,
                monument.doc_tp as monumentType 
      from           BIT , 
                monument, 
                abbba_BIT, 
                drag, 
                soft_INDEX 
      where
                BIT.group_id in (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25) 
                and BIT.partition_key = :26 
                and BIT.BIT_id=monument.BIT_id (+) 
                and BIT.BIT_id=abbba_BIT.abbba_BIT_ID 
                and BIT.partition_key=abbba_BIT.partition_key 
                and abbba_BIT.abbba_BIT_ID=drag.abbba_BIT_ID (+) 
                and BIT.BIT_id=soft_INDEX.BIT_id 
                and BIT.partition_key=soft_INDEX.partition_key 
                and soft_INDEX.partition_key = :28 
                and abbba_BIT.partition_key = :27 
                and drag.drag_NO in (:29, :30, :31, :32, :33) 
                and BIT.del_fl = :34 
      order by NLSsoft(soft_INDEX.INDEX_QRY_NUM, 'NLS_soft = binary'), 
                BIT.source_priority, 
                NLSsoft(soft_INDEX.INDEX_DOM_EXT, 'NLS_soft =binary'), 
                BIT.ref_no
      Real Time SQL Monitoring Report:
      Global Information
       Status              :  EXECUTING
       Instance ID         :  3
       Session ID          :  2941
       SQL ID              :  1htru5m48h2cs
       SQL Execution ID    :  50331658
       Plan Hash Value     :  3421880714
       Execution Started   :  03/01/2013 14:03:20
       First Refresh Time  :  03/01/2013 14:03:27
       Last Refresh Time   :  03/02/2013 09:43:08
      
      -------------------------------------------------------------------------------------
      | Elapsed |   Cpu   |    IO    | Concurrency | Cluster  |  Other   | Buffer | Reads |
      | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Waits(s) |  Gets  |       |
      -------------------------------------------------------------------------------------
      |   70788 |   66482 |     0.16 |        1135 |     0.16 |     3170 |  2267M |     3 |
      -------------------------------------------------------------------------------------
      
      
      SQL Plan Monitoring Details
      ==========================================================================================================================================================================
      | Id    |                  Operation                   |      Name      |  Rows   | Cost |   Time    | Start  | Starts |   Rows   | Memory | Activity  | Activity Detail |
      |       |                                              |                | (Estim) |      | Active(s) | Active |        | (Actual) |        | (percent) |   (sample #)    |
      ==========================================================================================================================================================================
      |     0 | SELECT STATEMENT                             |                |         |    7 |           |        |      1 |          |        |           |                 |
      |     1 |   SORT ORDER BY                              |                |       1 |    7 |     23645 |  +2715 |      1 |        0 |        |           |                 |
      |     2 |    FILTER                                    |                |         |      |     23645 |  +2715 |      1 |       37 |        |           |                 |
      |     3 |     NESTED LOOPS                             |                |         |      |     23645 |  +2715 |      1 |       37 |        |           |                 |
      |     4 |      NESTED LOOPS                            |                |       1 |    6 |     27708 |     +7 |      1 |       37 |        |           |                 |
      |     5 |       NESTED LOOPS OUTER                     |                |       1 |    3 |     27708 |     +7 |      1 |      364 |        |           |                 |
      |  -> 6 |        NESTED LOOPS                          |                |       1 |    0 |     70782 |     +7 |      1 |      364 |        |           |                 |
      |  -> 7 |         MERGE JOIN CARTESIAN                 |                |       1 |    0 |     70782 |     +7 |      1 |    8418K |        |           |                 |
      |     8 |          PARTITION RANGE SINGLE              |                |       1 |    0 |     70766 |     +7 |      1 |     1256 |        |           |                 |
      |  -> 9 |           INDEX RANGE SCAN                   | ABBBA_BIT_AK2  |       1 |    0 |     70782 |     +7 |      1 |     1256 |        |           |                 |
      |    10 |          BUFFER SORT                         |                |       1 |    0 |     70782 |     +7 |   1256 |    8418K |   338K |      0.03 | Cpu (4)         |
      |    11 |           PARTITION RANGE SINGLE             |                |       1 |    0 |         1 |     +7 |      1 |     6705 |        |           |                 |
      |    12 |            TABLE ACCESS BY LOCAL INDEX ROWID | SOFT_INDEX     |       1 |    0 |         1 |     +7 |      1 |     6705 |        |           |                 |
      |    13 |             INDEX RANGE SCAN                 | SOFT_INDEX_BK1 |       1 |    0 |         1 |     +7 |      1 |     6705 |        |           |                 |
      |    14 |         PARTITION RANGE SINGLE               |                |       1 |    0 |     68305 |     +7 |  8418K |      364 |        |      0.04 | Cpu (5)         |
      | -> 15 |          TABLE ACCESS BY LOCAL INDEX ROWID   | BIT            |       1 |    0 |     70783 |     +7 |  8418K |      364 |        |     81.90 | Cpu (11320)     |
      |    16 |           INDEX RANGE SCAN                   | BIT_BK3        |       1 |    0 |     70782 |     +7 |  8418K |    4295M |        |     18.03 | Cpu (2492)      |
      |    17 |        TABLE ACCESS BY INDEX ROWID           | MONUMENT       |       1 |    3 |           |        |    364 |          |        |           |                 |
      |    18 |         INDEX RANGE SCAN                     | MONUMENT_IF2   |       1 |    2 |           |        |    364 |          |        |           |                 |
      |    19 |       INDEX RANGE SCAN                       | DRAG_IF1       |       1 |    2 |     23645 |  +2715 |    364 |       37 |        |           |                 |
      |    20 |      TABLE ACCESS BY INDEX ROWID             | DRAG           |       1 |    3 |     23645 |  +2715 |     37 |       37 |        |           |                 |
      ==========================================================================================================================================================================
      Execution plan fetched using dbms_xplan.display_cursor.
      -----------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      -----------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                           |                |       |       |     7 (100)|          |       |       |
      |   1 |  SORT ORDER BY                             |                |     1 |  1759 |     7  (15)| 00:00:01 |       |       |
      |*  2 |   FILTER                                   |                |       |       |            |          |       |       |
      |   3 |    NESTED LOOPS                            |                |       |       |            |          |       |       |
      |   4 |     NESTED LOOPS                           |                |     1 |  1759 |     6   (0)| 00:00:01 |       |       |
      |   5 |      NESTED LOOPS OUTER                    |                |     1 |  1749 |     3   (0)| 00:00:01 |       |       |
      |   6 |       NESTED LOOPS                         |                |     1 |  1730 |     0   (0)|          |       |       |
      |   7 |        MERGE JOIN CARTESIAN                |                |     1 |   968 |     0   (0)|          |       |       |
      |   8 |         PARTITION RANGE SINGLE             |                |     1 |    26 |     0   (0)|          |   KEY |   KEY |
      |*  9 |          INDEX RANGE SCAN                  | ABBBA_BIT_AK2  |     1 |    26 |     0   (0)|          |   KEY |   KEY |
      |  10 |         BUFFER SORT                        |                |     1 |   942 |     0   (0)|          |       |       |
      |  11 |          PARTITION RANGE SINGLE            |                |     1 |   942 |     0   (0)|          |   KEY |   KEY |
      |  12 |           TABLE ACCESS BY LOCAL INDEX ROWID| SOFT_INDEX     |     1 |   942 |     0   (0)|          |   KEY |   KEY |
      |* 13 |            INDEX RANGE SCAN                | SOFT_INDEX_BK1 |     1 |       |     0   (0)|          |   KEY |   KEY |
      |  14 |        PARTITION RANGE SINGLE              |                |     1 |   762 |     0   (0)|          |   KEY |   KEY |
      |* 15 |         TABLE ACCESS BY LOCAL INDEX ROWID  | BIT            |     1 |   762 |     0   (0)|          |   KEY |   KEY |
      |* 16 |          INDEX RANGE SCAN                  | BIT_BK3        |     1 |       |     0   (0)|          |   KEY |   KEY |
      |  17 |       TABLE ACCESS BY INDEX ROWID          | MONUMENT       |     1 |    19 |     3   (0)| 00:00:01 |       |       |
      |* 18 |        INDEX RANGE SCAN                    | MONUMENT_IF2   |     1 |       |     2   (0)| 00:00:01 |       |       |
      |* 19 |      INDEX RANGE SCAN                      | DRAG_IF1       |     1 |       |     2   (0)| 00:00:01 |       |       |
      |* 20 |     TABLE ACCESS BY INDEX ROWID            | DRAG           |     1 |    10 |     3   (0)| 00:00:01 |       |       |
      -----------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - filter((:27=:28 AND :28=:26 AND :27=:26))
         9 - access("ABBBA_BIT"."PARTITION_KEY"=:28)
             filter(("ABBBA_BIT"."PARTITION_KEY"=:27 AND "ABBBA_BIT"."PARTITION_KEY"=:26))
        13 - access("SOFT_INDEX"."PARTITION_KEY"=:26)
             filter(("SOFT_INDEX"."PARTITION_KEY"=:28 AND "SOFT_INDEX"."PARTITION_KEY"=:27))
        15 - filter(("BIT"."DEL_FL"=:34 AND INTERNAL_FUNCTION("BIT"."GROUP_ID") AND
                    "BIT"."BIT_ID"="ABBBA_BIT"."ABBBA_BIT_ID" AND "BIT"."BIT_ID"="SOFT_INDEX"."BIT_ID"))
        16 - access("BIT"."PARTITION_KEY"=:28)
             filter(("BIT"."PARTITION_KEY"=:26 AND "BIT"."PARTITION_KEY"=:27))
        18 - access("BIT"."BIT_ID"="MONUMENT"."BIT_ID")
             filter("MONUMENT"."BIT_ID" IS NOT NULL)
        19 - access("ABBBA_BIT"."ABBBA_BIT_ID"="drag"."ABBBA_BIT_ID")
        20 - filter(("DRAG"."DRAG_NO"=:29 OR "DRAG"."DRAG_NO"=:30 OR "DRAG"."DRAG_NO"=:31 OR "DRAG"."DRAG_NO"=:32 OR
                    "DRAG"."DRAG_NO"=:33))
      Edited by: Yasu on Mar 3, 2013 12:53 AM
        • 1. Re: Wrong estimation of ROWS and COST
          sb92075
          post results from SQL below

          SELECT TABLE_NAME, NUM_ROWS, LAST_ANALYZED
          FROM ALL_TABLES
          WHERE TABLE_NAME IN ('BIT' , 'MONUMENT','ABBBA_BIT','DRAG','SOFT_INDEX');

          Handle:     Yasu
          Status Level:     Newbie (5)
          Registered:     Dec 9, 2007
          Total Posts:     418
          Total Questions:     114 (92 unresolved)

          why you you waste your time & our time since you rarely get answers to your questions here?
          • 2. Re: Wrong estimation of ROWS and COST
            Yasu
            why you you waste your time & our time since you rarely get answers to your questions here?
            You are right, even i felt that recently. Its my mistake that i did not care to mark most of my threads as answered. Does that state i need to go back and mark all my old threads as answered ?

            My role is some what isolated, i have to do it on my own. So some times i think of posting questions here in hope of getting some clues but at the same time i don't feel bad if i don't get answers, since i have learned a lot from this forum.
            TABLE_NAME                       NUM_ROWS LAST_ANALYZED
            ------------------------------ ---------- ------------------
            MONUMENT                          7978540 22-FEB-13
            DRAG                               406030 20-FEB-13
            BIT                             121783270 02-MAR-13
            SOFT_INDEX                      119212340 26-FEB-13
            ABBBA_HIT                       121276950 22-FEB-13
            Edited by: Yasu on Mar 3, 2013 9:40 AM