I'm coming up with a behaviour of Oracle which I do not understand.
I'm involved in some data migration to new 11g database and one of the last steps is enabling foreign keys, which seems to be the last step in any migration process. Enabling one of the FK had took a little bit more time, so I started investigation. I identified the recusive SQL which was used to find orphaned records in child table. I was quite surprised I hadn't been able to run EXPLAIN PLAN on the recursive query because of library cache lock wait. Optimisation of the recursive query was peace of cake, optimizer chose suboptimal execution plan because of stale statistics on indexes (table statistics were up to date).
I was interested into problem with library cache lock and I built up simple testcase:
CREATE TABLE master (
id NUMBER NOT NULL,
v VARCHAR2(30)
)
/
ALTER TABLE master ADD CONSTRAINT master_pk PRIMARY KEY (id)
/
CREATE TABLE child (
id NUMBER,
id_fk NUMBER,
txt VARCHAR2(500)
)
/
ALTER TABLE child ADD CONSTRAINT child_fk1 FOREIGN KEY (id_fk) REFERENCES master (id) DISABLE
/
--insert some data to child and master
--1st session
ALTER TABLE child ENABLE CONSTRAINT child_fk1
/
--2nd session will wait on library cache lock
SELECT * FROM master
/
This sounds interesting, at least to my mind. It seems oracle is holding library cache lock in exclusive mode even on the master table (or PK on master table). So I tried to check it in 11.2.0.1. Following output is from v$session_wait for blocked session
SELECT sid, event, p1, p1raw, p2, p2raw, p3text, p3, p3raw FROM v$session_wait WHERE sid=484
/
SID EVENT P1 P1RAW P2 P2RAW P3TEXT P3 P3RAW
--- --------------------- -------------------- ---------------- -------------------- ---------------- -------------------- -------------------- ----------------
9 library cache lock 504403158387060088 07000000073EED78 504403158435573872 070000000A233070 100*mode+namespace 77334463872303106 0112BF4900010002
Unfortunately, the P3 / P3RAW column didn't provide me sufficient information. According to P3TEXT (and 11gR2 documentation, too), the P3 argument should contain three digit number. It seems to bee bug, or at least documentation bug. I tried to identify lock LC object in different way.
I started digging into quite well known x$ fixed arrays: x$kgllk (kernel global library cache locks), x$kglob (kernel global library object) and I was lucky:
SELECT addr, indx, kgllkhdl, kgllkses, kglnahsh, kgllkmod, kgllkreq FROM x$kgllk WHERE kgllkhdl = '07000000073EED78'
/
ADDR INDX KGLLKHDL KGLLKSES KGLNAHSH KGLLKMOD KGLLKREQ
---------------- -------------------- ---------------- ---------------- -------------------- -------------------- --------------------
00000001109FD200 5 07000000073EED78 070000002B269F70 3858763180 0 2
00000001109FD300 6 07000000073EED78 070000002B26FC50 3858763180 3 0
SQL> l
1* SELECT kglnaown, kglnaobj, kglfnobj, kglhdnsd, kglobtyd FROM x$kglob WHERE kglnahsh = 3858763180
SQL> /
KGLNAOWN KGLNAOBJ KGLFNOBJ KGLHDNSD KGLOBTYD
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
SYS MASTER MASTER TABLE/PROCEDURE TABLE
Question: Why does Oracle lock master table library object during enabling FK in exclusive mode? I think shared lc lock should be enough (or maybe some X, S, X conversions). I understand only that child table library object has to be locked in exclusive mode during enabling FK.
The minor question is what does the v$session_wait.P3 parameter mean in case of "library cache wait" and "library cache lock" in 10.2.0.5 and 11.2.0.1 It seems to report correct values only in my favorite 9.2.0.8 database.
Regards,
Pavol Babel
Oracle Certified Master 10g/11g