测试方法
   1,如何模拟library cache load lock
   2,查找上述latch的addr,
   3,用oradebug poke上面的latch addr
   4,另启一会话即发生关于上述latch wait

参考资料：
---oredebug usage
http://blog.csdn.net/tianlesoftware/article/details/6525628

测试过程

/***********获取shared pool latch的内存地址*******parent latch and child latch****/
SQL> select * from v$latch where name like '%shared pool%' and addr='000000000823AC50';

ADDR                 LATCH#     LEVEL# NAME                                                                   HASH       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES WAITERS_WOKEN WAITS_HOLDING_LATCH SPIN_GETS     SLEEP1     SLEEP2     SLEEP3     SLEEP4     SLEEP5     SLEEP6     SLEEP7     SLEEP8     SLEEP9    SLEEP10    SLEEP11 WAIT_TIME
---------------- ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- -------------- ---------------- ------------- ------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000000823AC50        293          7 shared pool                                                      2276811941     314022        510         39              0                0             0                   0        476          0          0          0          0          0          0          0          0          0          0          0 1930812098

/******拼接oradebug poke修改内存命令*********/
SQL> select 'oradebug poke 0x'||addr ||' 4 0x00000001;' from v$latch_children where name='shared pool';

'ORADEBUGPOKE0X'||ADDR||'40X00
----------------------------------------------
oradebug poke 0x000000000D59A540 4 0x00000001;
oradebug poke 0x000000000D59A4A0 4 0x00000001;
oradebug poke 0x000000000D59A400 4 0x00000001;
oradebug poke 0x000000000D59A360 4 0x00000001;
oradebug poke 0x000000000D59A2C0 4 0x00000001;
oradebug poke 0x000000000D59A220 4 0x00000001;
oradebug poke 0x000000000D59A180 4 0x00000001;

7 rows selected

/**********启用oradebug**********/
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x 000000000D59A180 4 0x00000001
ORA-00073: command POKE takes between 3 and 3 argument(s)
SQL> oradebug poke 0x000000000D59A540 4 0x00000001
BEFORE: [00D59A540, 00D59A544) = 00000000
AFTER: [00D59A540, 00D59A544) = 00000001
SQL> oradebug poke 0x000000000D59A540 4 0x00000001;
BEFORE: [00D59A540, 00D59A544) = 00000001
AFTER: [00D59A540, 00D59A544) = 00000001
SQL> oradebug poke 0x000000000D59A4A0 4 0x00000001;
BEFORE: [00D59A4A0, 00D59A4A4) = 00000000
AFTER: [00D59A4A0, 00D59A4A4) = 00000001
SQL> oradebug poke 0x000000000D59A400 4 0x00000001;
BEFORE: [00D59A400, 00D59A404) = 00000000
AFTER: [00D59A400, 00D59A404) = 00000001
SQL> oradebug poke 0x000000000D59A360 4 0x00000001;
BEFORE: [00D59A360, 00D59A364) = 00000000
AFTER: [00D59A360, 00D59A364) = 00000001
SQL> oradebug poke 0x000000000D59A2C0 4 0x00000001;
BEFORE: [00D59A2C0, 00D59A2C4) = 00000000
AFTER: [00D59A2C0, 00D59A2C4) = 00000001
SQL> oradebug poke 0x000000000D59A220 4 0x00000001;
BEFORE: [00D59A220, 00D59A224) = 00000000
AFTER: [00D59A220, 00D59A224) = 00000001
SQL> oradebug poke 0x000000000D59A180 4 0x00000001;
BEFORE: [00D59A180, 00D59A184) = 00000000
AFTER: [00D59A180, 00D59A184) = 00000001
SQL> select event from v$session where type='USER';

/********另启一个会话hang住**********/
SQL> select count(a) from t_new;

/******解除锁定****************/
SQL> oradebug setmypid
Statement processed.
SQL> oradebug poke 0x000000000D59A540 4 0x00000000;
BEFORE: [00D59A540, 00D59A544) = 00000001
AFTER: [00D59A540, 00D59A544) = 00000000
SQL> oradebug poke 0x000000000D59A4A0 4 0x00000000;
BEFORE: [00D59A4A0, 00D59A4A4) = 00000001
AFTER: [00D59A4A0, 00D59A4A4) = 00000000
SQL> oradebug poke 0x000000000D59A400 4 0x00000000;
BEFORE: [00D59A400, 00D59A404) = 00000001
AFTER: [00D59A400, 00D59A404) = 00000000
SQL> oradebug poke 0x000000000D59A360 4 0x00000000;
BEFORE: [00D59A360, 00D59A364) = 00000001
AFTER: [00D59A360, 00D59A364) = 00000000
SQL> oradebug poke 0x000000000D59A2C0 4 0x00000000;
BEFORE: [00D59A2C0, 00D59A2C4) = 00000001
AFTER: [00D59A2C0, 00D59A2C4) = 00000000
SQL> oradebug poke 0x000000000D59A220 4 0x00000000;
BEFORE: [00D59A220, 00D59A224) = 00000001
AFTER: [00D59A220, 00D59A224) = 00000000
SQL> oradebug poke 0x000000000D59A180 4 0x00000000;
BEFORE: [00D59A180, 00D59A184) = 00000001
AFTER: [00D59A180, 00D59A184) = 00000000
SQL> oradebug poke 0x000000000D59A220 4 0x00000000;

/********另启一个会话hang住顺利继续执行**********/
SQL> select count(a) from t_new;

COUNT(A)
----------
1

/**********上述测试说明shared pool latch会导致sql不能执行*****是不是所有的sql不能执行了，还是首次执行的sql不能执行，重复运行的sql可以执行吗*********/

----会话1
SQL> select count(a) from t_new;

COUNT(A)
----------
1

---会话2,oredebug
SQL> oradebug poke 0x000000000D59A540 4 0x00000001;
BEFORE: [00D59A540, 00D59A544) = 00000000
AFTER: [00D59A540, 00D59A544) = 00000001
SQL> oradebug poke 0x000000000D59A4A0 4 0x00000001;
---中间略
BEFORE: [00D59A220, 00D59A224) = 00000000
AFTER: [00D59A220, 00D59A224) = 00000001
SQL> oradebug poke 0x000000000D59A180 4 0x00000001;
BEFORE: [00D59A180, 00D59A184) = 00000000
AFTER: [00D59A180, 00D59A184) = 00000001

---会话1,发现重复执行的sql不会影响
SQL> /

COUNT(A)
----------
1
---新的首次执行的sql就hang住了
SQL> select count(1) from t_new;

小结:1,shared pool latch用于硬解析，即首次执行sql会持此锁，如果软解析，不会持此锁
     2,所以如果生产系统出现过高的shared pool latch，表示sql写得很烂，从减少硬解析
       入手吧

ITPub博客

oradebug poke模拟shared pool latch与硬解析原理小析

最新文章