3 Replies Latest reply: Dec 19, 2013 2:07 AM by BluShadow RSS

    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

        • 1. Re: Tuning question
          Ramin Hashimzadeh

          Looking by execution plan, the plan is normal ... But maybe you are having wrong statistic for tables because of this ORACLE create a wrong plan.

          Another thing is NESTED LOOPS, i couldn't say it is good or not (it depend your data , your query and stats) but i recommended read about NESTED LOOPS

           

          Which i think is minimum but still it is taking a long time for execution

           

          Long time??

          1sec ? 1min? 1 hour? 1day? 1month? how much?!!

           

          ----

          Ramin Hashimzade

          • 2. Re: Tuning question
            Purvesh K

            It could be an issue due to incorrect statistics. But with the bare minimum data you have provided us, it is not possible to comment.

             

            Please read Re: HOW TO: Post a SQL statement tuning request - template posting

            • 3. Re: Tuning question
              BluShadow

              Your explain plan suggests that the optimizer has looked at the statistics on the tables and determined there is only going to be 1,2 or 3 rows from each, accessed via the index range scans.

              Is it true that your tables only have a small amount of rows in them?  If not then, as others have suggested, your statistics may not be up to date, and the optimizer is basing it's decision on wrong information, and then when the query actually comes to execute it finds it has to do more work than it planned.

               

              See the 2 links in the FAQ:  Re: 3. How to  improve the performance of my query? / My query is running slow.

               

              and post all the information people will need to be able to help you.