We have already known how to solve the 'library cache lock' to cause the process hang.
So how do you avoid this type of problem? This article mainly introduces the causes of library cache lock and how to avoid it.
When we perform some DML, DDL, even DESC TABLENAME, etc., the session lives, there is a situation, when we use create or replace procedure / function to modify Procedure and Function, the session will also HANG live, why?
When the above situation occurs, we can query the event through the V $ session_wait, of course, in this case, the SESSION wait event must be 'library cache lock'.
Let us explain,
The first case, when session 1 (session 1) performs DML or DDL to a table, at the same time there is another session, our aunt, called session 2 (session 2), this session 2 is also on this table Perform DDL (such as alter table), when the session 2 is required for a long time (depending on the specific amount of data), session 1 will live, at this time, you query session 1 wait event is' Library Cache Lock '.
In the second case, when the session 1 (session 1) is modified a package, there is another session with another session, we call it session 2 (session 2), this session 2 is executing session 1 modified Package Procedure or Function, session 1 will live, at this time, your waiting event for session 1 is 'Library Cache Lock'.
Therefore, when compiling and analyzing Package / Procedure / Function / View, we must determine that no one is compiling and analyzing the same object, that is, it is ensured that no one has changed these needs (DROP and RECREATE) The definition of the object.
SQL> CREATE OR Replace Procedure Who_is_using Wrapped 2 0 3 ABCD 4 ABCD 5 ABCD 6 ABCD 7 ABCD 8 ABCD 9 ABCD 10 ABCD 11 ABCD 12 ABCD 13 ABCD 14 ABCD 15 ABCD 16 ABCD 17 ABCD 18 ABCD 19 7 20 200f000 21 1 22 4 23 0 24 1E 25 C who_is_using: 26 8 Obj_name: 27 8 VARCHAR2: 28 B DBMS_OUTPUT: 29 6 Enable: 30 7 1000000: 31 1 I: 32 1 B: 33 8 Username: 34 3 SID: 35 3 SYS: 36 7 X $ kglpn: 37 1 A: 38 9 V $ session: 39 7 x $ KGLOB: 40 1 C: 41 8 KGLPNUSE: 42 1 =
: 43 5 Saddr: 44 5 Upper: 45 8 kglnaobj: 46 4 Like: 47 8 KGLPNHDL: 48 8 KGLHDADR: 49 4 LOOP: 50 8 PUT_LINE: 51 1 (: 52 2 ||: 53 7 TO_CHAR: 54 4) - : 55 0 56 57 0 58 0 59 74 60 2 61 0 1D 9A 8F A0 B0 3D B4 62 55 6A: 2 A0 6B 51 A5 57 91 63: 2 A0 6B: 2 A0 6B AC: 2 A0 6B A0 64 B9: 2 A0 B9: 2 A0 6B A0 B9 B2 65 EE: 2 A0 6B A0 7E A0 6B B4 66 2E: 3 A0 6B A5 B 7E: 2 A0 A5 67 B B4 2E A 10: 2 A0 6B A0 68 7e A0 6B B4 2E a 10 AC 69 D0 E5 E9 37: 3 A0 6B A5 B B4E 7E 6E 71 B4 2E 7E: 2 A0 6B B4 2E A5 72 57 B7 A0 47 B7 A4 B1 11 73 68 4F 17 B5 74 74 75 14 20 13 77 25 29 2D 31 35 38 3B 3C 78 41 45 49 4D 50 54 58 5B 79 5C 60 64 67 11 6B 6F 73 80 75 79 7D 80 84 86 87 8E 81 92 96 99 9D A0 A4 A7 A8 82 AD B1 B5 BC BD BF CE 1 D3 D8 84 DC E0 E3 E7 EE F1 F2 85 1 F7 FC FD 101 106 10b 10D 86 111 115 119 11C 120 123 127 12B 87 12F 132 133 135 136 13B 13E 142 88 143 148 14B 14F 153 156 157 15C 89 15D 162 164 168 16f 171 175 177 91 74 92 2 93 0 1 B 18 21: 2 18 17: 2 1 94 2: 2 E 15: 2 2 6 1C: 2 1E 27 95: 2 29 1C 11 15 11 1D 11 1F 96 29 1F 2B 2F 2B 37 2B C 97 11 12: 2 14 1F 1D: 2 21: 2 1D 12 98 18: 2 1A: 3 12 2A 30: 2 2A: 5 12: 2 14 99 1F 1D: 2 21: 2 1D: 2 12: 4 C 2 2B100 3: 2 F 18 1B 1D 25: 2 27: 2 1D101: 2 18 2B 2D: 2 18 33 35: 2 37: 2 18102: 2 3 2B 6 2: 8 1103 74104 2105 0: 9 1: 6 3: 8 4: F 5: 9 6: D 7: 2 6106: 9 8: 2 6 5: 4 4 8: 19 9 8 A107 4: 2 2: 6 1108 191109 2110 : 4 0 1: A 0 70 1 1A 1B 511: 2 3: 3 0 2: 6 0 5 4: 3 0 7112: 2 0 70 2 8: 2 0 4: 3 0 5113: 3 0 AB 0 6: 2 0 7 C114 E: 2 0 6C 7: 3 0 8: 3 0 9115: 3 0 11 12 0 8: 3 0 A: 3 0116 14 15 0 9 B: 3 0 C: 3 0117 18 19 0 D: 3 0 E: 3 0 8118:
3 0 1D 1e B: 3 0 F: 3 0 20119 21 0 10: 3 0 22 23 C 25120 49 0 4A: 3 0 D: 3 0 11: 3 0121 27 28 0 8: 3 0 12: 2 0 13122: 3 0 2A 2C 0 12 2B 2E: 3 0123 14: 3 0 10: 3 0 15: 3 0 31 32124 0 10 30 34 16: 2 0 14: 3 0125 2: 3 0 15 37 39 17 36 3B126: 3 0 2F 3D 3C: 2 0 D: 3 0 17127: 3 0 3F 40 0 10: 3 0 12: 2 0128 18: 3 0 42 44 0 1C 43 46129: 3 0 3E 48 47: 3 0 2 17 26130 0 4B: 5 0 4C: 2 0 4E 10 4D131 19: 3 0 4: 3 0 1A: 3 0 50 51132 0 1B: 3 0 1C: 2 0 1D: 3 0 7133: 3 0 A: 3 0 56 57 0 1A 55134 59 1F 54 5B: 3 0 1C: 2 0 1E135: 3 0 22 5D 5F: 3 0 1C: 2 0 7136: 3 0 9: 3 0 62 63 0 25 61137 65: 3 0 28 52 67: 2 0 69 2A138 6B 19: 3 0 4E 69: 4 0 6C 2C139 6F: 3 0 6F 0 6F 6E 6C 6D140: 6 0 70 0 2 8 6F 72: 2 0141 1 70 73: 6 0142 2F143 2144: 3 0 1 3 1 6 1 D 2145 13 16 3 1C 1F 24 1 33146 2 29 2D 1 38 2 35 3A147 1 58 2 41 45 2 5A148 2 5E 2 60 64 1 66149 1 68 2 F 6B150 1151 4152 0153 72154 0155 1156 14157 2158 3159 0 1 0 0 0 0 0 0160 0 0 0 0 0 0 0 0161 0 0 0 0162 2 0 116 .
ELAPSED: 00: 00: 00.07sql>
