Bitmap vs B-Tree indexes - contention

In 11g R1

SQL> conn apps/apps SQL> create table tb1 ( a number, dt timestamp, c varchar2(12));

SQL> create bitmap index tb1_bidx on tb1 (a);

SQL> select index_name, index_type from user_indexes;
INDEX_NAME INDEX_TYPE

------------------------------ ---------------------------
TB1_BIDX BITMAP


cat pop1.sql
set timing on
begin
for nn in 1 .. 10000 loop
insert into tb1 select trunc(dbms_random.value(1,11)),sysdate, 'bm' from dual;
end loop;
end;
/
-- commit;
exit


SQL> !date
Fri May 28 13:58:29 EST 2010

SQL> EXEC dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

1=1
maxlp=10
while [ $i -le $maxlp ]; do sqlplus apps/apps @pop1.sql & i=`echo $(($i+1))`; done

Elapsed: 00:00:12.86
Elapsed: 00:00:36.45
Elapsed: 00:00:44.34
Elapsed: 00:00:48.84
Elapsed: 00:00:57.59
Elapsed: 00:01:06.21
Elapsed: 00:01:10.08
Elapsed: 00:01:09.17
Elapsed: 00:01:20.89
Elapsed: 00:01:26.39
So longest took 1.5 mins. Add all up:


60*5+12+36+44+48+57+6+10+9+20+26
--------------------------------
568

Fri May 28 14:02:37 2010
SQL> EXEC dbms_workload_repository.create_snapshot;


sqlplus / as sysdba @awrrpt



Top 5 Timed Foreground Events - BM

Event                         Waits Time(s) Avg wait (ms) % DB time Wait Class

enq: TX - row lock contention 35    454     12977         75.68 Application

DB CPU                        70    11.74

Data file init write         426    24      57             4.01 User I/O

library cache: mutex X         5    4       839            0.70 Concurrency

log file sync                 21    3       140            0.49 Commit

SQL> drop index tb1_bidx;
Index dropped.

SQL> create index tb1_idx on tb1(a);
Index created.

SQL> EXEC dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

SQL> !date
Fri May 28 14:21:15 EST 2010

1=1
maxlp=10
while [ $i -le $maxlp ]; do sqlplus apps/apps @pop1.sql & i=`echo $(($i+1))`; done

Elapsed: 00:00:08.33
Elapsed: 00:00:09.54
Elapsed: 00:00:07.39
Elapsed: 00:00:06.00
Elapsed: 00:00:09.91
Elapsed: 00:00:08.40
Elapsed: 00:00:09.76
Elapsed: 00:00:12.15
Elapsed: 00:00:04.55
Elapsed: 00:00:13.84

SQL> select 8+9+7+6+9+8+9+12+4+13 from dual;
8+9+7+6+9+8+9+12+4+13
---------------------
85 <-- instead of 558!

Top 5 Timed Foreground Events - BT

Event              Waits Time(s) Avg wait (ms) % DB time Wait Class
buffer deadlock        7 25      3522          23.27     Other
DB CPU                21                       20.26
enq: HW - contention   4 5       1210           4.57     Configuration
buffer busy waits     47 4       79             3.49     Concurrency
Data file init write  44 3       69             2.88     User I/O

So the response time dropped from 1 min 26 secs to 14 secs, as "row-lock contention" was replaced by (much lower) instances of "buffer deadlock".