This discussion is archived
4 Replies Latest reply: Jan 18, 2013 10:38 AM by Martin Preiss RSS

Performance SQL - 11g

user8897201 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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