測試方法
   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寫得很爛，從減少硬解析
       入手吧

oradebug poke模擬shared pool latch與硬解析原理小析

相關文章