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.

Advertisements

April 19, 2011

Skip streams capture SCN

Filed under: streams — ora62 @ 8:16 pm

I have a nice experience on streams capture 10.2.0.1
when captures is set to grab all dml at the schema levels,
and suddenly user performed CTAS, and magically oracle
ora lucky number ORA-600 coming.

got error that capture builder has error, OK well done.
I started manually the capture process and after 10 minutes
PMON always kill capture builder process.

PMON has hidden parameter by default 10 minutes
to scan dead process.

no chance to do workaround as of metalink,
the note will state that database should be upgraded
to atleast 10.2.0.3…phiew

dong sync manualy from the latest applied SCN
and then skip capture SCN to start from
the last sync SCN :(

exec dbms_capture_adm.stop_capture(capture_name => 'AQ_CAPTURE');
delete from system.logmnr_restart_ckpt$ where spare1 < nnnn ;
exec dbms_capture_adm.alter_capture(capture_name => 'AQ_CAPTURE', first_scn => nnnn, start_scn => nnnn);
exec dbms_capture_adm.start_capture(capture_name => 'AQ_CAPTURE');

by looking at the alert log, luckily only objects belongs to sysman schema
got warning…which those are not captured.

Create a free website or blog at WordPress.com.