This discussion is archived
3 Replies Latest reply: Dec 19, 2013 12:07 AM by BluShadow RSS

Tuning question

Rajat Newbie
Currently Being Moderated

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

Legend

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