drop table SUPPLIER purge; create table SUPPLIER( id INTEGER generated always as identity ,name varchar2(30) not null ,primary key(id) ) ; insert /*+ APPEND */ into SUPPLIER(name) select dbms_random.string('x',30) from xmltable('1 to 100') ; commit; drop table PRODUCT purge; create table PRODUCT( id integer generated always as identity ,supplier_id integer ,product_name varchar2(30) ,price NUMBER ,primary key(id) ,constraint fk_prod_suppl foreign key(supplier_id) references SUPPLIER(id) on delete cascade ) ; alter table PRODUCT drop constraint fk_prod_suppl; alter table PRODUCT add constraint fk_prod_suppl foreign key(supplier_id) references SUPPLIER(id) on delete cascade; insert /*+ APPEND */ into PRODUCT(supplier_id,product_name,price) select trunc(dbms_random.value(1,90)) ,dbms_random.string('x',30) ,dbms_random.value(1,10000) from xmltable('1 to 10000000') ; commit; -- grant execute on dbms_job to POC; -- grant create job to POC; create or replace procedure delete_supplier(suppl_id integer) as begin DBMS_APPLICATION_INFO.set_module(module_name => 'delete_supplier', action_name => 'Delete supplier'); delete from SUPPLIER where id=suppl_id; commit; end; / create or replace procedure parallel_delete_supplier as v_jobno number:=0; begin for i in 51..100 loop dbms_job.submit(v_jobno,'delete_supplier('||to_char(i)||');', sysdate); end loop; commit; end; / -- create a huge locking situation ;) exec parallel_delete_supplier; SQL> @ash/ashtop inst_id,session_id,sql_id,event2,blocking_inst_id,blocking_session,blocking_session_status,P1text,p2,p3 "username='POC'" sysdate-1/24/20 sysdate Total Distinct Distinct Seconds AAS %This INST_ID SESSION_ID SQL_ID EVENT2 BLOCKING_INST_ID BLOCKING_SESSION BLOCKING_SE P1TEXT P2 P3 FIRST_SEEN LAST_SEEN Execs Seen Tstamps --------- ------- ------- ---------- ---------- ------------- ------------------------------------------ ---------------- ---------------- ----------- ------------------------------ ---------- ---------- ------------------- ------------------- ---------- -------- 15 .1 2% | 1 19 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 20 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 21 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 23 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 25 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 27 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 29 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 30 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 31 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 33 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 35 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 38 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 158 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 159 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 15 .1 2% | 1 160 2b4hjy6xfb76s enq: TM - contention [mode=5] 1 450 VALID name|mode 42238 0 2024-02-11 19:09:40 2024-02-11 19:09:54 1 15 -- find enq mode from P1 column og gv$session SQL> select distinct' [mode='||BITAND(p1, POWER(2,14)-1)||']' from gv$session where username='POC' and event like 'enq%'; '[MODE='||BITAND(P1,POWER(2,14)-1)||']' ------------------------------------------------ [mode=5] -- index the FK on child table create index IDX_PRODUCT_SUPPL_ID on PRODUCT(supplier_id);