Wednesday, April 29, 2009

Cheap and light way to get record count frequently in a big table

How to get count(*) without really scanning all the records in a huge table:

If your requirement is to frequently get count of records in a huge table and to get it quickly, then you may use the following method to achieve it. One real life requirement example is to find the count of records in an exception queue table to determine how many application threads need to be started to process the exception queue for meeting SLA's.

Basically, we would create a new tracking table to be populated though trigger on the main table to maintain the counters for any inserts or deletes on the main table. We also need an Oracle sequence to distribute the counter trackers for better concurrency and locking mechanism. In essence, counter will be incremented by 1 for an insert and decremented by 1 for a delete statement.

Oracle Materialized view does similar thing but it is not scalable for a busy table (you will see enqueue contention).


create table test_tmp(id1 varchar2(30), id2 number, id3 number);
Table created.

create table test_cnt (id1 varchar2(30), id2 number, c1 char(25),
c2 number, c3 number) initrans 50; #This is the tracking table
Table created.

create unique index test_cnt_idx on test_cnt(id1, id2, c2);
Index created.

create sequence test_cnt_seq start with 1 increment by 1 maxvalue 100 cycle cache 20;
Sequence created.

create or replace trigger test_tr before insert or delete on test_tmp
for each row
declare
v1 number;
begin
select test_cnt_seq.nextval into v1 from dual;
if inserting then
update test_cnt set c3 = c3+1 where id1=:new.id1 and id2 = :new.id2 and c2=v1;
if sql%notfound then
insert into test_cnt values (:new.id1, :new.id2, 'X', v1, 1);
end if;
end if;
if deleting then
update test_cnt set c3 = c3-1 where id1=:old.id1 and id2 = :old.id2 and c2=v1;
if sql%notfound then
insert into test_cnt values (:old.id1, :new.id2, 'X', v1, -1);
end if;
end if;
end;
/

Trigger created.

insert into test_tmp values ('A', 1, 1);
1 row created.

insert into test_tmp values ('B', 2, 2);
1 row created.

insert into test_tmp values ('C', 3, 3);
1 row created.

insert into test_tmp values ('D', 4, 4);
1 row created.

insert into test_tmp values ('E', 5, 5);
1 row created.

commit;
Commit complete.

select id1, sum(c3) from test_cnt group by id1 having sum(c3) > 0;

id1 SUM(C3)
------------------------------ ----------
D 1
A 1
B 1
C 1
E 1

delete from test_tmp where id3=5;
1 row deleted.

commit;
Commit complete.

select id1, sum(c3) from test_cnt group by id1 having sum(c3) > 0;

id1 SUM(C3)
------------------------------ ----------
D 1
A 1
B 1
C 1


Triggers have it's downside and they can fire multiple times for the same record in a single sql statement execution as per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:11504247549852


posted by Saibabu Devabhaktuni @ 11:33 PM 6 comments

Tuesday, April 28, 2009

Beware of index contention after mass delete and insert

One insert can cause an outage on your system:

If you delete large number of records from a table having an index and insert the same records as part of another transaction, then one of the subsequent inserts would have to perform tremendous amount of work while splitting an index block as it has to find a free block from the free list but the blocks reused from delete-insert operation were still there in free list and hence this operation would have to unlink them one by one until it finds an all empty block.

This can be demonstrated easily with the following test case (on 10.2.0.3 with 8k block size and no assm tablespace):
create tablespace idx1 datafile '?/dbs/idx1.dbf' size 500M
segment space management manual
extent management local uniform size 10M;

create table idx1(a number) tablespace idx1;

create index idx1_idx on idx1 (a) tablespace idx1 pctfree 0;

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000;

commit;

create table idx2 tablespace idx1 as select * from idx1 where 1=2;

insert into idx2
select * from idx1 where rowid in
(select rid from
(select rid, rownum rn from
(select rowid rid from idx1 where a between 10127 and 243625 order by a)
)
where mod(rn, 250) = 0
)
/

commit;

delete from idx1 where a between 10127 and 243625;

commit;

insert into idx1 select * from idx2;

commit;

insert into idx1 select 250000+rownum from all_objects where rownum <= 126;

commit;

select sql_id, executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql
where sql_text like '%insert%idx1%' and sql_text not like '%v$sql%';

insert into idx1 values (251000);

commit;

select sql_id, executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql
where sql_text like '%insert%idx1%' and sql_text not like '%v$sql%';


If you run the above statements, you will see that one insert statement consuming over 900 buffer gets. While it is in the middle of getting 900 blocks (some of them may be on the disk), all other inserts would have to wait for it to finish.

If you either coalesce or shrink the index with an alter statement, then this problem will go away (alternatively you can also do online index rebuild), but the best way to deal with it is by doing the delete and insert in one transaction.

I've opened bug# 8446989 with Oracle support on this one, hopefully it will be fixed soon.


Update (06/2012):
Oracle finally fixed this issue, you need to set event 45051 for enabling this fix. You will still about 150 buffer gets for one insert but most of them are for repeated reads so the impact is very minimal. Big thanks to Oracle for fixing this issue.

Thanks,
Sai.

posted by Saibabu Devabhaktuni @ 12:36 AM 9 comments