Oracle單實例情況下的library cache pin的問題模擬與問題分析
本機環境:Oracle 10.2.0.5 x86-64bit for RHEL5.8 x86-64bit
第一個session:
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jun 12 17:27:28 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 281018368 bytes Fixed Size 2095672 bytes Variable Size 96470472 bytes Database Buffers 176160768 bytes Redo Buffers 6291456 bytes Database mounted. Database opened. SQL> create user lc0019999 identified by aaaaaa; User created. SQL> grant dba to lc0019999; Grant succeeded. SQL> create user lc0029999 identified by aaaaaa; User created. SQL> grant dba to lc0029999; Grant succeeded. SQL> create user lc0039999 identified by aaaaaa; User created. SQL> grant dba to lc0039999; Grant succeeded. SQL> conn lc0019999/aaaaaa Connected. SQL> show user USER is "LC0019999" SQL> select * from v$mystat where rownum<2; SID STATISTIC# VALUE ---------- ---------- ---------- 159 0 1 SQL> Create or replace procedure dummy is 2 begin 3 null; 4 end; 5 / Procedure created. SQL> Begin 2 Dummy; 3 Dbms_lock.sleep(1000); 4 End; 5 / ------------>一直sleep著
以lc0029999登陸新開的一個session
[oracle@localhost ~]$ sqlplus lc0029999/aaaaaa SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jun 12 17:34:04 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from v$mystat where rownum<2; SID STATISTIC# VALUE ---------- ---------- ---------- 158 0 1 SQL> SQL> alter procedure lc0019999.dummy compile; ------------>一直hang著
以sys用戶新開一個session
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jun 12 17:31:33 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set line 200 SQL> select sid, serial#,event from v$session where event like '%library cache pin%'; SID SERIAL# EVENT ---------- ---------- ---------------------------------------------------------------- 158 28 library cache pin SQL> select sid, serial#,p1raw,event from v$session where event like '%library cache pin%'; SID SERIAL# P1RAW EVENT ---------- ---------- ---------------- -------------------------------------------------- 158 28 000000006BFF19B0 library cache pin SQL> col owner for a30 SQL> col object for a30 SQL> SELECT kglnaown "Owner", kglnaobj "Object" 2 FROM x$kglob 3 WHERE kglhdadr='000000006BFF19B0'---->上面查出的P1RAW值。 4 ; ---->該語句是查詢出這個等待事件發生在哪個object上。 Owner Object ------------------------------ ------------------------------ LC0019999 DUMMY SQL> SQL> SELECT s.sid, kglpnmod "Mode", kglpnreq "Req" 2 FROM x$kglpn p, v$session s 3 WHERE p.kglpnuse=s.saddr 4 AND kglpnhdl='000000006BFF19B0';---->該語句是查詢出這個等待事件的等待者sid(REQ>0)和阻塞者sid(Mode>0) SID Mode Req ---------- ---------- ---------- 158 0 3 159 2 0 SQL>