otak-otak | favorite evening food

April 21, 2011

BITMAP CONVERSION TO ROWIDS

Filed under: sql tuning — ora62 @ 8:54 am

on 10.2.0.5 AIX

The query is really simple just querying a table filtered by combination of columns, the table has 2 indexes (each 1 column) which is as part of where clause. from the plan, oracle seems to deal with just only cost, ya it is true cost is cheaper than by scanning only 1 index, but IO cost would likely double (scanning 2 indexes, bitmap and operation and than conver bitmap to rowid).

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |     1 |   112 |    66   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID     | T_4             |     1 |   112 |    66   (2)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |                 |       |       |            |          |
|   3 |    BITMAP AND                    |                 |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
|*  5 |      INDEX RANGE SCAN            | IDX_1           | 25237 |       |     7   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
|*  7 |      INDEX RANGE SCAN            | IDX_0           | 25237 |       |    40   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

here by force to use index 1,

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |   112 |   501   (1)| 00:00:07 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_4             |     1 |   112 |   501   (1)| 00:00:07 |
|*  2 |   INDEX RANGE SCAN          | IDX_1           |  3801 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

it seems the cost is higher than bitmap conversion, but from the real execution time the second one is really faster. it seems cost calculation on the first plan is misleading.

August 14, 2008

trace 10046 analyzer

Filed under: sql tuning — ora62 @ 7:07 pm

generating trace 10046 quite interesting in analysis.

from the 10046 trace raw contents, we actually can do analysis, but internal recursive sql sometimes makes our live harder :)

metalink note 224270.1 give us a tool to analyze the trace easily, but again got problem when I need to know the history by mean of step-by-step application make database calls. I cannot see control transaction on the trace file.

we can only find “XCTEND rlbk=<number>, rd_only=<number>”, based on metalink note 371984.1:

rlbk 1 if a rollback was performed, 0 if no rollback (commit).
rd_only 1 if transaction was read only, 0 if changes occurred.
the harder part of the raw data of trace 10046 those transaction control is reside at the middle of cursor parsing :(

FETCH #10:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=49665367470
WAIT #10: nam=’SQL*Net message from client’ ela= 472 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49665367990
STAT #10 id=1 cnt=1 pid=0 pos=1 obj=0 op=’FAST DUALĀ  (cr=0 pr=0 pw=0 time=1 us)’
WAIT #12: nam=’SQL*Net message to client’ ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49665368337
WAIT #12: nam=’SQL*Net message from client’ ela= 376 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49665368739
XCTEND rlbk=0, rd_only=1
WAIT #0: nam=’SQL*Net message to client’ ela= 1 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49665368872
*** 2008-08-14 12:06:51.980
WAIT #0: nam=’SQL*Net message from client’ ela= 53635836 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49719004740
WAIT #0: nam=’SQL*Net message to client’ ela= 3 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49719005053
WAIT #0: nam=’SQL*Net message from client’ ela= 6340 driver id=1952673792 #bytes=1 p3=0 obj#=-1 tim=49719011426

even when I tried to make a trick by generating new cursor parsing by replacing the XCTEND bla..bla…bla with something like this:

:%s/XCTEND rlbk=0, rd_only=1/^M=====================^MPARSING IN CURSOR #10 len=27 dep=0 uid=57 oct=3 lid=57 tim=49665366604 hv=824003789 ad=’ca1c6268’^Mcommit

———————————————

Note to get ^M use following
Hold down the Ctrl, and press v m
DO NOT USE Shift+6 and Shift+M
———————————————

still from the trace analyzer output give me a confusing (the order of transaction control still doen’t make any sense – it totally wrong order).

anyway good tools to analyze execution plan, due to the parameter that makes CBO work are in one place with the explain plan gathered.

Ujang| Indonesia | Jakarta

Create a free website or blog at WordPress.com.