Foreign key constraint columns are not indexed, resulting in a large number of library cache pin/library cache locks and librarypin

Foreign key constraint columns are not indexed, resulting in a large number of library cache pin/library cache locks and librarypin
The foreign key constraint column is not indexed, resulting in a large number of library cache pin/library cache locks.

Clear the data of a large table with more than 1 million rows and find that it has been executed for several hours:
Delete B001.T _ B11;
The following SQL statement is used to track and find that the library cache pin and library cache lock Wait often occur. It is suspected that a large number of recursive SQL statements are being executed, so this session is executed for 10046:
A large number of SQL statements are found to be executed. Each row T_B11 is deleted, the following two SQL statements are executed once,
Parsing in cursor #3 len = 93 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1435131097407618 hv = 2174374139 ad = 'b4b86f9e0' sqlid = 'a16ztda0tnn7v'
Select/* + all_rows */count (1) from "B001". "T_BA19" where "BID" =: 1
END OF STMT
BINDS #3:
Bind #0
Oacdty = 02 mxl = 22 (22) mxlc = 00 mal = 00 scl = 00 pre = 00
Oacflg = 12 fl2 = 0001 frm = 00 csi = 00 siz = 24 off = 0
Kxsbbbfp = 2b20a2af4d90 bln = 22 avl = 04 flg = 05
Value = 232156
EXEC #3: c = 0, e = 198, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 2, og = 1, plh = 4133059621, tim = 1435131097407770
FETCH #3: c = 0, e = 39, p = 0, cr = 1, cu = 0, mis = 0, r = 1, dep = 2, og = 1, plh = 4133059621, tim = 1435131097407841
CLOSE #3: c = 0, e = 3, dep = 2, type = 3, tim = 1435131097407880
==================================
Parsing in cursor #3 len = 87 dep = 2 uid = 0 oct = 3 lid = 0 tim = 1435131097410498 hv = 2660531033 ad = 'b0000000028 'sqlid = '1mhux5ug98yut'
Select/* + all_rows */count (1) from "B001". "T_BA18" where "BID" =: 1
END OF STMT
BINDS #3:
Bind #0
Oacdty = 02 mxl = 22 (22) mxlc = 00 mal = 00 scl = 00 pre = 00
Oacflg = 12 fl2 = 0001 frm = 00 csi = 00 siz = 24 off = 0
Kxsbbbfp = 2b20a2af4d90 bln = 22 avl = 04 flg = 05
Value = 232156
EXEC #3: c = 0, e = 193, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 2, og = 1, plh = 3617316486, tim = 1435131097410645
FETCH #3: c = 6000, e = 5402, p = 0, cr = 1342, cu = 0, mis = 0, r = 1, dep = 2, og = 1, plh = 3617316486, tim = 1435131097416075
CLOSE #3: c = 0, e = 2, dep = 2, type = 3, tim = 1435131097416182
Both T_BA18 and T_BA19 have a foreign key associated with T_B11. As you can see, it is basically determined that no index is created on the foreign key columns corresponding to T_BA18 and T_BA19, resulting in a slow delete of the primary table.
Because each row of the primary table is deleted, the query is performed in the subtable. If no index is created on the subtable, the query is slow.
In this process, the S lock is applied to the sub-table, which is the origin of the library cache pin.
(Oracle's explanation of the library cache pin is very classic:
An X request (3) will be blocked by anypins held S mode (2) on the object.
An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request .)
After adding an index to the BID column of the two sub-tables, the problem is solved.