Hi Friends,
If i run below SQL in our DB, there will be immediately many "enq: TM - contention", and many blocking session in database.
I logged all contentions in attached txt
SQL> select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
------ ---------- --------
595 0 0
1 row selected.
SQL> update codes
2 set code_code ='ASNR33'
3 WHERE code_tab_name = 'ASSET_NOSCANREASON' and code_code like '%ASNR3';
....
Once i cancelled above sql, all contentions and blocking sessions disappeared to nothing.
From the log attached, I found the sid 595 blocking many other sessions , like sid 30,271,418...etc,
also it was blocked by sid 172 , I believe sid 172 was doing DML on table "INV_HDR" , but has nothing to do with table "CODES" is it because of foreign key on INV_HDR referencing parent table "CODES" ?
This is constraint already created on table INV_HDR
ALTER TABLE "PSD"."INV_HDR" ADD CONSTRAINT "INV_HDR_CODE_FK" FOREIGN KEY ("INV
OICE_SOURCE")
REFERENCES "PSD"."CODES" ("CODE_CODE") ENABLE