4 Replies Latest reply: Jan 18, 2013 12:38 PM by Martin Preiss RSS

    Performance SQL - 11g

    user8897201
      Hi,

      (Database version:11gR2)

      I'm executing a sql query using sqlplus with the following options:
      set timing on
      set autotrace trace

      Why I'm getting the following elapsed time:
      Elapsed: 00:00:00.00

      but in explain plan I see higher time?
      | Id  | Operation                          | Name       | Rows  | Bytes |TempSpc | Cost (%CPU)| Time     | Pstart| Pstop |                                       
                                                                                      
      --------------------------------------------------------------------------------
      -----------------------------------------                                       
                                                                                      
      |   0 | SELECT STATEMENT                   |            |   216K|  3385K|       |  9346   (1)| 00:06:15 |       |       |                                       
                                                                                      
      |   1 |  PARTITION RANGE OR                |            |   216K|  3385K|       |  9346   (1)| 00:06:15 |KEY(OR)|KEY(OR)|                                       
                                                                                      
      |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| LOG    |   216K|  3385K||  9346   (1)| 00:06:15 |KEY(OR)|KEY(OR)|                                       
                                                                                      
      |   3 |    BITMAP CONVERSION TO ROWIDS     |            |       |       |      |            |          |       |       |                                       
                                                                                      
      |   4 |     BITMAP OR                      |            |       |       |     |            |          |       |       |                                       
                                                                                      
      |   5 |      BITMAP CONVERSION FROM ROWIDS |            |       |       |          |          |       |       |                                       
                                                                                      
      |   6 |       SORT ORDER BY                |            |       |       |  7912K|            |          |       |       |                                       
                                                                                      
      |*  7 |        INDEX RANGE SCAN            |    LOG_PK |       |       |        818   (0)| 00:00:33 |KEY(OR)|KEY(OR)|                                       
                                                                                      
      |   8 |      BITMAP CONVERSION FROM ROWIDS |            |       |       | |            |          |       |       |                                       
                                                                                      
      |   9 |       SORT ORDER BY                |            |       |       |  7848K           |          |       |       |                                       
                                                                                      
      |* 10 |        INDEX RANGE SCAN            |     LOG_PK |       |       | |   812   (1)| 00:00:33 |KEY(OR)|KEY(OR)|                                       
                                                                                      
      --------------------------------------------------------------------------------
      -----------------------------------------                                       
      I don't really understand why BITMAP CONVERSION is in use (I just have b-tree indexes).

      Thanks

      Edited by: user8897201 on Jan 16, 2013 2:39 AM
        • 1. Re: Performance SQL - 11g
          Mihael
          I'm executing a sql query using sqlplus with the following options:
          set timing on
          set autotrace trace

          Why I'm getting the following elapsed time:
          Elapsed: 00:00:00.00

          but in explain plan I see higher time?
          in sql plan you see estimated time that can be different from real time

          I don't really understand why BITMAP CONVERSION is in use (I just have b-tree indexes).
          this conversion is done internally to optimize the query
          • 2. Re: Performance SQL - 11g
            user8897201
            But when we have set timing on in sqlplus even with set autotrace trace we are getting the real time?

            Thanks a lot
            • 3. Re: Performance SQL - 11g
              JustinCave
              Yes. SET TIMING ON causes the actual elapsed time to be displayed. It appears that the actual elapsed time is many orders of magnitude less than the estimated time which implies that there is likely some problem with the statistics on one or more objects.

              Justin
              • 4. Re: Performance SQL - 11g
                Martin Preiss
                Hi,

                the server is able to convert the b*tree index into bitmaps in memory and combine the result with an effective BITMAP OR operation. You find a small example on Julian Dyke's site: http://www.juliandyke.com/Optimisation/Operations/BitmapConversionFromRowids.html (you see there that the conversion is not a new feature).

                Given your elapsed time the CBO did a good job in this case.

                Regards

                Martin