Oracle 11.2.0.1
I have a table structure as below.
-- Create table
create table ACTIONS_DUE
(
cod_acct_no CHAR(16) not null,
dat_action DATE not null,
cod_action INTEGER not null,
dat_ins_action DATE,
cod_entity_vpd NUMBER(5) default NVL(sys_context('CLIENTCONTEXT','entity_code'),11) not null
)
tablespace DATA1
pctfree 10
initrans 16
maxtrans 255
storage
(
initial 2048M
next 500M
minextents 1
maxextents unlimited
);
create index IDX_ACTIONS_DUE_1 on ACTIONS_DUE (DAT_ACTION, COD_ACTION, COD_ACCT_NO, COD_ENTITY_VPD)
tablespace DATA1
pctfree 10
initrans 16
maxtrans 255
storage
(
initial 1024M
next 100M
minextents 1
maxextents unlimited
);
1) table is vacant.
2) Processing happens on this table during batch runs.
3) 14million data gets inserted, processing happens on same and then gets deleted after batch processing which makes the table empty again.
Issue we are facing :
Inserts are happening very slow when it tries to inserts 14million data in this table (1000 records in 1min approx). We check SQL waits and could see inserts taking allot of time. He we checked user extents for this table could see table got 44 extents
Hence we dropped the table and recreated the same. after recreation table extent got recreated with 1extent with params passed during table creation.
as a result we could see inserts got faster with speed of 1000records 3secs approx. from minutes earlier.
Further observation we could see whenever we recreate table! Inserts work's fine but once extents on table increases to certain limit we could see insert performance degraded and we are back to performance hit, hence every time we face this issue we recreate the table and move ahead with batch processing. Since this is temporary solution we are applying, Request help to know if this is bug or we are wrong anywhere.