Using 10.2.0.4 Standard Edition RAC. I have a table very busy with inserts. Oracle waits for events "gc current block busy", "gc buffer busy release" etc. There seems contention of the blocks between instance. I pre-allocated extent to each instance:
alter table busy_table allocate extent (100m instance 1);
alter table busy_table allocate extent (100m instance 2);
But this seems not reducing the events. Is there any more to do?
Some additional questions:
1) How to I know the extent is allocated to a specific instance? DBA_EXTENTS has no instance information. And X$KTFBUE always shows the current instance.
select INST_ID, KTFBUEFNO, KTFBUEBNO from X$KTFBUE
where KTFBUESEGBNO=(
select header_block from dba_segments
where segment_name='BUSY_TABLE');
returned the same inforamtion except that INST_ID is different.
2) Does the preallocation affects insert sql only? I guess other operation are free to use any extent. Correct?
3) We are using ASSM. So I am not supposed to be tuning FREELISTS AND FREELIST GROUPS. Correct?
DB: 10.2.0.4
OS: RHEL 5.3