3 Replies Latest reply on Dec 19, 2013 8:07 AM by BluShadow

    Tuning question

    Rajat

      Dear all,

       

      Before executing a certain query in the database i have checked the explan plan in the DB.

      For every row COST(%CPU)|TIME is showing 0:00:01 .

       

      Which i think is minimum but still it is taking a long time for execution. Could you please help me to understand how i can foresee a long execution after seeing the EXPLAN_PLAN table.

       

       

       

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------
      Plan hash value: 3861503892
      
      -----------------------------------------------------------------------------------------------------------
      | Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                   |                      |     1 |   131 |    16   (7)| 00:00:01 |
      |   1 |  SORT ORDER BY                     |                      |     1 |   131 |    16   (7)| 00:00:01 |
      |*  2 |   FILTER                           |                      |       |       |            |          |
      |*  3 |    FILTER                          |                      |       |       |            |          |
      |   4 |     NESTED LOOPS                   |                      |       |       |            |          |
      |   5 |      NESTED LOOPS                  |                      |     1 |   131 |     8   (0)| 00:00:01 |
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------
      |   6 |       NESTED LOOPS                 |                      |     1 |   112 |     6   (0)| 00:00:01 |
      |   7 |        NESTED LOOPS                |                      |     1 |    89 |     4   (0)| 00:00:01 |
      |*  8 |         TABLE ACCESS BY INDEX ROWID| BL                   |     1 |    71 |     2   (0)| 00:00:01 |
      |*  9 |          INDEX RANGE SCAN          | BL_OSTAMP_CA_TYP_HBL |     1 |       |     2   (0)| 00:00:01 |
      |* 10 |         TABLE ACCESS BY INDEX ROWID| BL_BKG               |     1 |    18 |     2   (0)| 00:00:01 |
      |* 11 |          INDEX RANGE SCAN          | BBS_PK_PRIM          |     1 |       |     1   (0)| 00:00:01 |
      |* 12 |        TABLE ACCESS BY INDEX ROWID | BKG_ROUTE            |     1 |    23 |     2   (0)| 00:00:01 |
      |* 13 |         INDEX RANGE SCAN           | BRT_PK_PRIM          |     3 |       |     1   (0)| 00:00:01 |
      |* 14 |       INDEX RANGE SCAN             | BRT_PK_PRIM          |     3 |       |     1   (0)| 00:00:01 |
      |* 15 |      TABLE ACCESS BY INDEX ROWID   | BKG_ROUTE            |     2 |    38 |     2   (0)| 00:00:01 |
      |* 16 |    INDEX RANGE SCAN                | CAI_PK_PRIM          |     1 |    12 |     2   (0)| 00:00:01 |
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------------------------------------
      |* 17 |    TABLE ACCESS BY INDEX ROWID     | BL                   |     2 |    28 |     4   (0)| 00:00:01 |
      |* 18 |     INDEX RANGE SCAN               | BL_BY_BL_CA_OFFSET   |     4 |       |     2   (0)| 00:00:01 |
      |  19 |    SORT AGGREGATE                  |                      |     1 |    13 |            |          |
      |* 20 |     TABLE ACCESS BY INDEX ROWID    | BL                   |     2 |    26 |     3   (0)| 00:00:01 |
      |* 21 |      INDEX RANGE SCAN              | BL_PK_PRIM           |     2 |       |     2   (0)| 00:00:01 |
      -----------------------------------------------------------------------------------------------------------
      

       

       

      Regards

      Rajat