106 lines
6.7 KiB
Plaintext
106 lines
6.7 KiB
Plaintext
|
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);
|
||
|
|
|