程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle單實例情況下的librarycachepin的問題模擬與問題分析

Oracle單實例情況下的librarycachepin的問題模擬與問題分析

編輯:Oracle教程

Oracle單實例情況下的librarycachepin的問題模擬與問題分析


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> 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved