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".
