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);

