In the previous example, we mainly solve the problem with the X $ KGLLK base table and Event SystemState, then if you don't know how to use it, or forget how to use it, it will not be nervous. Here, it will introduce a conventional approach. .
From the dump information of System State, we have noticed that Process 28 is currently waiting for 'library cache lock'. 'Handle Address' is the address in the library cache waiting for the Process 28 process.
Now we continue to find Oracle Process containing the 'Handle = C000000122E2A6D8' string in the trace file, which is to find information about Blocking Session, discovering that the information is as follows:
Process 26: --------------- Blocking Oracle processes of other sessions, here Process 26 corresponds to the value of PID in V $ Process --------- ----------------------------- SO: C000000109C831E0, TYPE: 2, OWNER: 000000000000000000, Flag: init / - / - / 0x00 (Process) Oracle PID = 26, Calls Cur / Top: C00000010B2774D0 / C00000010B2774D0 / C0000000010B2774D0, FLAG: (0) - INT Error: 0, Call Error: 0, Sess Error: 0, Txn Error 0 (POST INFO) Last Post Received: 17 24 6 last post received-location: ksusig last process to post me: c000000109c840f8 25 0 last post sent: 751404 0 15 last post sent-location: ksasnd last process posted by me: c000000109c836e8 1 6 (latch info) wait_event = 0 bits = 0 Process Group: Default, Pseudo Proc: C000000109EEFDA0 O / S INFO: User: ORA9I, TERM: UNKNOWN, OSPID: 20552 OSD PID INFO: UNIX Process PID: 20552, Image: Oracle @ CS_DC02 (TNS V1-V3) --- ------------------------------------- SO: C0000001180B9510, TYPE: 8, OWNER: C000000109C831E0, FLAG : Init / - / - / 0x00 (FOB Flags = 2 FIB PTR = 162e1b48 IncnO = 0 Pending I / O CNT = 0 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- SO: C000000001180B9458, TYPE: 8, OWNER: C000000109C831E0, FLAG: INIT / - / - / 0X00 (FOB) Flags = 2 FIB PTR = 162deb18 incno = 0 Pending I / O CNT = 0 - -------------------------------------- SO: C0000001180B8230, TYPE: 8, OWNER: C000000109C831E0 , Flag: init / - / - / 0x00 (FOB) Flags = 2 FIB PTR = 162de848 Incno = 0 Pending I / O CNT = 0 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- SO: C0000001180B7B00, TYPE: 8, OWNER: C000000109C831E0, FLAG: INIT / - / - / 0x00 (FOB) Flags = 2 FIB PTR = 162DE578 Incno = 0 Pending I / O CNT =
0 ---------------------------------------- SO: C000000108C99E28, TYPE: 4, OWNER : C000000109C831E0, FLAG: INIT / - / - / 0X00C000000108C99E28 Corresponding to the value of the Saddr in the V $ session, you can find the SID of the Blocking Session. Trans: C0000001169403C0, CREATOR: C000000109C831E0, FLAG: 100041) USR / - BSY / - / - / - / - / - DID: 0002-001A-0000007D, Short-Term Did: 0000-0000-00000000 TXN Branch: C00000011B825E18 OCT: 0, PRV: 0, SQL: 800003FB0005F7B0, psql: c00000011fbe3f98, user: 50 / pUBUSER O / S info: user: report16, term:, ospid: 20550, machine: cs_dc02 program: sqlplus @ cs_dc02 (TNS V1-V3) application name: SQL * Plus, hash value = 3669949024 Waiting for 'SQL * Net Message from DBLINK' Blocking Sess = 0x0 SEQ = 3319 Wait_time = 0 Driver ID = 28444553, # Bytes = 1, = 0 ---------------------------------------------------------------------------------------------------------------------------- - Here, # bytes means a Server Process Send the number of bytes (bytes) DRIVER ID sent to another Server Process, which we need to convert it to a 16-based number, and then find it Corresponding to our corresponding information in Event 10046: *** 2005-01-10 11: 44: 48.200Wait # 1: nam = 'sql * net message from dblink' ELA = 104397696 P1 = 675562835 P2 = 1 p3 = 0wait # 1: nam = 'sql * net message to dblink' ELA = 4 p1 = 675562835 P2 = 1 p3 = 0
SQL> SELECT TO_CHAR (675562835, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR (675562835, 'xxxxxxxxxxxxx --------------------------------- 28444553
SQL> Temporary Object Counter: 0 ---------------------------------------- SO: C00000011A4496B0 , type: 51, owner: c000000108c99e28, flag: INIT / - / - / 0x00 LIBRARY OBJECT LOCK: lock = c00000011a4496b0 handle = c00000012029f968 mode = N call pin = 0000000000000000 session pin = c00000011a44ad70 htl = c00000011a449720 [c00000011a4baa78, c00000011a4baa78] htb = c00000011a4baa78 user = c000000108c99e28 session = c000000108c99e28 count = 1 flags = [00] savepoint = 173 LIBRARY OBJECT hANDLE: handle = c00000012029f968 namespace = CRSR flags = RON / KGHP / PN0 / [10010000] kkkk-dddd-llll = 0000-0041-0041 lock = N pin = 0 latch # = 3 lwt = c00000012029f998 [c00000012029f998, c00000012029f998] ltm = c00000012029f9a8 [c00000012029f9a8, c00000012029f9a8] pwt = c00000012029f9c8 [c00000012029f9c8, c00000012029f9c8] ptm = c00000012029fa58 [c00000012029fa58, c00000012029fa58] ref = c00000012029f978 [c0000001202a0068, c0000001202a0068] lnd = C00000012029FA70 [C00000012029FA70, C00000012029FA70] LIBRARY OBJECT: object = c00000012029f5c8 type = CRSR flags = EXS [0001] pflags = [00] status = VALD load = 0 DEPENDENCIES: count = 1 size = 16 AUTHORIZATIONS: count = 1 size = 16 minimum entrysize = 16 ACCESSES: count = 1 size = 16 Data Blocks: Data # HEAP POINTER STATUS PINS CHANGE ------------ ------------ ---- ----- - 0 C00000012029F8A8 C00000012029F288 I / P / A 0 None 6 C00000012029F6E8 C00000012029E7C8 I / - / A 0 none ------------------------------ -----------. . . . . .
---------------------------------------- SO: C00000011A44A150, TYPE: 51, OWNER: C0000001169403C0, FLAG: INIT / - / - / 0x00 / / x $ kgllk.kgllkadr corresponds to SO (SO: C00000011A44A150 / / / / x $ kgllk.kgllkuse and x $ kgllk.kgllkses corresponds to the value of Owner (Owner: C0000001169403C0) LIBRARY OBJECT LOCK: lock = c00000011a44a150 handle = c000000122e2a6d8 mode = X // X $ KGLLK.KGLLKADR values corresponding to the lock and SO (SO: c00000011a44a150, lock = c00000011a44a150) // // X $ KGLLK.KGLLKHDL handle corresponds to the value of (handle = c000000122e2a6d8) call pin = 0000000000000000 session pin = 0000000000000000 // X $ KGLLK.KGLLKPNS value corresponding to the session pin (session pin = 0000000000000000) // htl = c00000011a44a1c0 [c00000011a4bb328, c00000011a4bb328] htb = c00000011a4bb328 user = c000000108c99e28 session = C000000108C99E28 Count = 1 Flags = [00] SavePoint = 179 USER and SESSION values correspond to X $ KGLLK.KGLLKUSE and X $ KGLLK.KGLLLKSES, which also blocks other sessions in the v $ session Saddr // x $ kgllk. KGLLKSPN corresponds to the value of SavePoint (SavePoint = 179) // LIBRARY OBJECT HANDLE: handle = c000000122e2a6d8 name = PUBUSER.CSNOZ629926699966 hash = eddf82b5 timestamp = 01-08-2005 13:00:18 previous = NULL namespace = TABL / PRCD / TYPE flags = KGHP / TIM / PTM / SML / [02000000] kkkk-dddd-llll = 0000-0709-0001 lock = X pin = X latch # = 3 lwt = c000000122e2a708 [c00000011a449e40, c00000011a449e40] ltm = c000000122e2a718 [c000000122e2a718, c000000122e2a718] pwt = c000000122e2a738 [c000000122e2a738, c000000122e2a738] ptm = c000000122e2a7c8 [c000000122e2a7c8 , C00000012E2A7C8] Ref = C000000122E2A6E8 [C000000122E2A6E8, C000000122E2A6E8] LND =
c000000122e2a7e0 [c000000122e2a7e0, c000000122e2a7e0] LOCK INSTANCE LOCK: id = LBcafc8485d0949f81 PIN INSTANCE LOCK: id = NBcafc8485d0949f81 mode = X release = F flags = [00] LIBRARY OBJECT: object = c000000122e12f70 type = TABL flags = EXS / LOC / CRT [0015] Pflags = [00] status = vald load = 0 Data Blocks: Data # Heap Pointer Status Pins Change ------------ -------------- - ------ 0 C000000122E2A618 C000000122E13118 I / P / A 0 insert 3 C00000012E13178 0 - / P / - 1 None 8 C000000122E12C30 C000000122FEBDB8 I / P / A 1 Update 9 C00000012E13090 0 - / P / - 1 None 10 C00000012E12CE0 C000000122ACBC70 I / P / A 1 Update --------------------------. . . . . .
According to the above two Oracle Process No. (Oracle PID), we can find their session information and operating system process information SQL> SELECT SPID, PID, AddR from V $ Process Where Pid in (26, 28);
Spid pid addr ------------ -------------------------- 20552 26 C000000109C831E0 ------ ----- Blocking Oracle Process 22580 28 C000000109C83BF0 ----------- is blocked Oracle process
SQL>
Let's further confirm the above information:
SQL> COL UserName for A20SQL> Col osuser for A20SQL> Col Machine for A20SQL> L 1 Select SID, Serial #, Username, OSuser, Machine, To_Char (Logon_Time, 'YYYY / MM / DD HH24: MI: SS') Logontime 2 * From v $ sessions where paddr in (SELECT AddR from V $ process where spid = '& spid') SQL> / Enter Value for SPID: 20552 ----------- Oracle process to block other sessions
Old 2: from v $ session where paddr in (SELECT ADDR from V $ process where spid = '& spid') New 2: from v $ session where paddr in (SELECT AddR from v $ process where spid = '20552') SID Serial # Username Osuser Machine Logontime ---------- ----------------------------------- ------------------------------------- ---- 37 2707 Pubuser Report16 CS_DC02 2005/01/08 13:00:17
SQL> / Enter Value for SPID: 22580 ----------- is blocked Oracle process
Old 2: from v $ sessions where paddr in (SELECT AddR from V $ process where spid = '& spid') New 2: from v $ sessions where paddr in (SELECT AddR from V $ process where spid = '22580')
SID Serial # username Osuser Machine Logontime -------------------------------------- --- ------------------------------------- ------ 30 24167 Pubuser Ora9i CS_DC02 2005/01/10 10:20:31
SQL> Select Sid, Saddr, Paddr, Username, Status, OSUser from V $ Session WHERE SID IN (37, 30);
SID SADDR PADDR UserName Status Osuser ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------- 30 C000000109F02C68 C000000109C83BF0 Pubuser Active Ora9i 37 C000000108C99E28 C000000109C831E0 Pubuser Active Report16
SQL> Now, the problem has been married, the solution and method 1 are in the method 1 (directly kill directly KILL in the operating system).
Of course, in the purpose of research, we can learn more about the above two sessions (SID 30 and SID 37) All already hold locks: SQL> Set Linesize 150SQL> Set Pages 10000sql> SELECT * FROM V $ LOCK WHERE SID In (37, 30); AddR Kaddr Sid Ty Id1 ID2 LMODE Request Ctime Block -------------------------------- -------- - - ------------------------- - ---------- ---------- C0000001169403C0 C000000116940538 37 TX 917507 26579 6 0 180478 2C00000011676DAE0 C00000011676DB08 37 TM 18 0 3 0 180478 2C00000010B30C4E8 C00000010B30C508 37 XR 4 0 2 0 180369 2C00000010B30C460 C00000010B30C480 37 DX 21 0 1 0 68 0
SQL> It is not difficult to see that session 37 blocks other sessions
Now, let's further look at the session 37 which objects are currently added to the lock: SQL> Select Object_Name, Object_id from dba_objects where object_id in ('917507', '18', '4', '21') Order By Object_ID;
Object_name object_id -------------------------------------- Tab $ 4obj $ 18COL $ 21
SQL> /
Object_name object_id -------------------------------------- Tab $ 4obj $ 18COL $ 21
SQL>
Next, look at the session of the SID 37 in library cache, request and hold object locks in library cache: SQL> Col Kglnaobj for A30SQL> Col User_name for A10SQL> L 1 Select Inst_ID, User_Name, Kglnaobj, Kgllksnm, Kgllkuse , KGLLKSES, KGLLKMOD, KGLLKREQ 2 * from x $ kgllk where kgllksnm = 37sql> / inst_id user_name kglnaobj kgllksnm kgllkuse kgllkses kgllkmod kgllkreq ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------- - ------------------------ ---------- 2 Pubuser DBMS_OUTPUT 37 C00000000108C99E28 C000000108C99E28 1 0 2 Pubuser dbms_output 37 C000000108C99E28 C000000108C99E28 1 0 2 Pubuser DBMS_Standard 37 C000000108C99E28 C000000108C99E28 1 0 2 Pubuser Pubuser 37 C000000108C99E28 1 0 2 Pubuser SELECT Minor_Version from Sy 37 C000000108C99E28 C000000108C99E28 1 0 S.cdc_system $
2 Pubuser SELECT Minor_Version from Sy 37 C000000108C99E28 C000000108C99E28 1 0 s.cdc_system $
2 PUBUSER DBMS_CDC_PUBLISH 37 C000000108C99E28 C000000108C99E28 1 0 2 PUBUSER DBMS_CDC_PUBLISH 37 C000000108C99E28 C000000108C99E28 1 0 2 PUBUSER CSNOZ629926699966 37 C000000108C99E28 C000000108C99E28 3 0 2 PUBUSER DBMS_APPLICATION_INFO 37 C000000108C99E28 C000000108C99E28 1 0 2 PUBUSER DBMS_APPLICATION_INFO 37 C000000108C99E28 C000000108C99E28 1 0 2 PUBUSER DATABASE 37 C000000108C99E28 C000000108C99E28 1 012 rows selected .
SQL>
Take a look at the SID 30 session in library cache, seek and hold object locks in Library Cache: SQL> Select Inst_ID, User_Name, Kglnaobj, Kgllksnm, Kgllkuse, Kgllkses, Kgllkmod, KgllkReq 2 from x $ kgllk where kgllksnm = 30 3 /
Inst_id user_name kglnaobj kgllksnm kgllkuse kgllkses kgllkmod kgllkreq ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ---------- 2 pUBUSER pUBUSER 30 C000000109F02C68 C000000109F02C68 1 0 2 pUBUSER CSNOZ629926699966 30 C000000109F02C68 C000000109F02C68 0 2 2 pUBUSER DBMS_APPLICATION_INFO 30 C000000109F02C68 C000000109F02C68 1 0 2 pUBUSER DBMS_APPLICATION_INFO 30 C000000109F02C68 C000000109F02C68 1 0 2 Pubuser Database 30 C00000000109F02C68 C00000000109F02C68 1 0sql> kglnaobj column contains the first 80 characters of the statement to execute the command on the object in Librarky Cac, in fact, from here we can also significantly narrow the range of KGLLKSES corresponding to V $ session The value of the Saddr column KGLLKSNM corresponds to the value of the SID (session ID) kgllkhdl in the V $ Session and the 'HAN in the tracking file in the method 1 The value of DLE Address' corresponds to the value of KGLLKPNS corresponding to the value of 'ssession PIN' in the tracking file in method 1.
SQL> set linesize 2000SQL> l 1 select INST_ID, USER_NAME, KGLNAOBJ, KGLLKSNM, KGLLKUSE, KGLLKSES, KGLLKMOD, KGLLKREQ, KGLLKPNS, KGLLKHDL 2 * from x $ kgllk where KGLLKSNM in (30,37) order by KGLLKSNM, KGLNAOBJSQL> /
INST_ID USER_NAME KGLNAOBJ KGLLKSNM KGLLKUSE KGLLKSES KGLLKMOD KGLLKREQ KGLLKPNS KGLLKHDL ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ - - ---------- ------------------------------------------------------------------------------------------------------------------------------------------------ ---- 2 pUBUSER DATABASE 30 C000000109F02C68 C000000109F02C68 1 0 00 C000000119F8EC58 2 pUBUSER DBMS_APPLICATION_INFO 30 C000000109F02C68 C000000109F02C68 1 0 00 C00000011CCDDA48 2 pUBUSER DBMS_APPLICATION_INFO 30 C000000109F02C68 C000000109F02C68 1 0 00 C00000011CCD81B8 2 P UBUSER PUBUSER 30 C000000109F02C68 C000000109F02C68 1 0 00 C00000011CBFDAA8 2 PUBUSER CSNOZ629926699966 30 C000000109F02C68 C000000109F02C68 0 2 00 C000000122E2A6D8 2 PUBUSER DATABASE 37 C000000108C99E28 C000000108C99E28 1 0 00 C000000119F8EC58 2 PUBUSER DBMS_APPLICATION_INFO 37 C000000108C99E28 C000000108C99E28 1 0
00 C00000011CCDDA48 2 PUBUSER DBMS_APPLICATION_INFO 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CCD81B8 2 PUBUSER DBMS_CDC_PUBLISH 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011FEA4918 2 PUBUSER DBMS_CDC_PUBLISH 37 C000000108C99E28 C000000108C99E28 1 0 00 C0000001202A4988 2 PUBUSER DBMS_OUTPUT 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CCB48B0 2 PUBUSER DBMS_OUTPUT 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011FFF5098 2 PUBUSER DBMS_STANDARD 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CCF0ED8 2 PUBUSER PUBUSER 37 C000000108C99E28 C000000108C99E28 1 0 00 C00000011CBFDAA8 2 PUBUSER SELECT MINOR_VERSION FROM SYS.CDC_SYSTEM $ 37 C000000108C99E28 C000000108C99E28 1 0 C00000011A44AD70 C00000012029F968 2 PUBUSER SELECT MINOR_VERSION FROM SYS.CDC_SYSTEM $ 37 C000000108
C99E28 C000000108C99E28 1 0 00 C0000001202A0228 2 PUBUSER CSNOZ629926699966 37 C000000108C99E28 C000000108C99E28 3 0 00 C000000122E2A6D817 rows selected.
SQL>
