tx_index_contention_1.sql

/* This testcase demonstrates
   huge "enq: TX index contention" waits
   caused by simultaneous inserts from 100 sessions
   and a concurrent delete operation.

   Prerequisites: db_cache_size=100m, ASSM, ASM */
set echo on
/* 1. Create a table and an index: */
drop table txa purge;
drop sequence txs;
CREATE TABLE txa
(
  ID   varchar2(2000)
) initrans 50
TABLESPACE USERS;
create index txi on txa(id) initrans 50 TABLESPACE USERS;

/* 2. Create a workload consisting of 100 parallel inserts: */

create sequence txs cache 1000;
create procedure txproc is
begin loop
   insert into txa(id) values((lpad(to_char(txs.nextval),100,''0'')));
   commit;
   dbms_lock.sleep(0.05);
   end loop;
end;
/

begin
  for i in 1 .. 100 loop
      dbms_scheduler.create_job(job_name=>'TXI_'||to_char(i,'FM099'),
     job_type=>'STORED_PROCEDURE',job_action=>'txproc',enabled=>true);
    end loop;
end;
/

/* 3. Wait till the table and the index grew larger than db_cache_size
      and induce the contention by a large uncommitted delete: */

exec dbms_lock.sleep(1800);
delete txa;
pause

/* 4. The cleanup: */
commit;
begin
  for r in (select job_name from user_scheduler_jobs
             where job_name like 'TXI%')
  loop
    dbms_scheduler.drop_job(r.job_name,true);
  end loop;
end;
/