第三、学以致用

模拟过程：

首先创建一个存储过程：

SQL> create or replace procedure rhys (amy_sleep in boolean,rhys_compile in boolean)

2 as

3 begin

4 if(amy_sleep) then

5 dbms_lock.sleep(3000);

6 elsif(rhys_compile) then

7 execute immediate 'alter procedure rhys compile';

8 end if;

9 end;

10 /

Procedure created.

SQL>

SQL>

session 1：

SQL> select * from v$mystat where rownuM<2;

SID STATISTIC# VALUE

---------- ---------- ----------

22 0 0

SQL> execute rhys(true,false);

处于等待状态：

session 2：

SQL> select * from v$mystat where rownuM<2;

SID STATISTIC# VALUE

---------- ---------- ----------

42 0 0

SQL> execute rhys(false ,true);

处于等待状态

产生是否有等待时间

SQL> select sid,username,sql_id,event,p1,p1raw,p2,p2raw,p3,p3raw,wait_time,seconds_in_wait from v$session where event='library cache pin';

SID USERNAME SQL_ID EVENT P1 P1RAW P2 P2RAW P3 P3RAW WAIT_TIME SECONDS_IN_WAIT

---------- ---------- ------------- -------------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- ---------- ---------------

42 SYS 2yv7ja732z3p0 library cache pin 2024609840 0000000078AD1830 2123642056 000000007E9434C8 4.0490E+14 0001704200010003 0 10

SQL> r

1* select sid,username,sql_id,event,p1,p1raw,p2,p2raw,p3,p3raw,wait_time,seconds_in_wait from v$session where event='library cache pin'

SID USERNAME SQL_ID EVENT P1 P1RAW P2 P2RAW P3 P3RAW WAIT_TIME SECONDS_IN_WAIT

---------- ---------- ------------- -------------------- ---------- ---------------- ---------- ---------------- ---------- ---------------- ---------- ---------------

42 SYS 2yv7ja732z3p0 library cache pin 2024609840 0000000078AD1830 2123642056 000000007E9434C8 4.0490E+14 0001704200010003 0 15

SQL>

可以看到等待一直在增加。

通过sql_id可以得到sql

SQL> col sql_text for a60

SQL> r

1* select sql_text,sql_id from v$sqlarea where sql_id='2yv7ja732z3p0'

SQL_TEXT SQL_ID

------------------------------------------------------------ -------------

alter procedure rhys compile 2yv7ja732z3p0

SQL>

获得锁定 信息：

方法一）

下面通过oradebug查看内容：

SQL> oradebug setmypid

Statement processed.

SQL> oradebug unlimit

Statement processed.

SQL> oradebug dump systemstate 266

Statement processed.

SQL> @trace.sql

TRACE_FILE_NAME

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------

/opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_1759.trc

SQL>

查看日志发现：

client details:

O/S info: user: oracle, term: pts/2, ospid: 1929

machine: oracle-one program: sqlplus@oracle-one (TNS V1-V3)

application name: sqlplus@oracle-one (TNS V1-V3), hash value=632623916

Current Wait Stack:

0: waiting for 'library cache pin'

handle address=0x78ad1830, pin address=0x7e9434c8, 100*mode+namespace=0x1704200010003