短連線引起的 library cache lock

當然啦，我做的例子是生成大量硬解析，但是沒撲捉到... ...

點選(此處)摺疊或開啟

SQL> select * from v$event_name where name like '%library cache lock%';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ----------------------------- ---------------- -------------- -------------------- ------------- ----------- -----------
215 2032051689 latch: library cache lock address number tries 3875070507 4 Concurrency
218 916468430 library cache lock handle address lock address 100*mode+namespace 3875070507 4 Concurrency

點選(此處)摺疊或開啟

declare
sql_text varchar2(100);
begin
for i in 1..1000000 loop
sql_text:='select * from c_lob where id = '|| i;
execute immediate sql_text;
end loop;
end;
/

產生大量硬解析

點選(此處)摺疊或開啟

select sql_text , loads , sql_id from v$sqlarea where sql_text like \'select * from c_lob%\';
SQL_TEXT LOADS SQL_ID
-------------------------------------------------- ---------- -------------
select * from c_lob where id = 209423 1 dktj3b33zw00a
select * from c_lob where id = 207585 1 5g9df2pcws016
select * from c_lob where id = 207612 1 gja66w9u1c01u
select * from c_lob where id = 206835 1 b7rt5h6jq801w
select * from c_lob where id = 207988 1 6htq7p5w5n027
select * from c_lob where id = 209590 1 8ng18qzu1s03k
select * from c_lob where id = 209165 1 8hu0kgz4nn04j
select * from c_lob where id = 209260 1 g9z59f95bs04m
select * from c_lob where id = 206630 1 1pzkydhctc04r
select * from c_lob where id = 207512 1 9qk6ds1c98056
select * from c_lob where id = 209344 1 9y4jz38r4805c
select * from c_lob where id = 209820 1 bwg31uu36s05q
select * from c_lob where id = 207768 1 60vf33utu006z
select * from c_lob where id = 206886 1 c331qsru3c075
select * from c_lob where id = 206733 1 0wj91s3z1807k
select * from c_lob where id = 210217 1 33s4dfj36w07p
select * from c_lob where id = 209693 1 77bn43xr4w092
select * from c_lob where id = 207251 1 a059jv7s6w09d
select * from c_lob where id = 209236 1 4uym4m49un0ba
select * from c_lob where id = 209948 1 0vw5abak7c0bk
select * from c_lob where id = 209025 1 bmk3w6gj8w0cr
select * from c_lob where id = 207735 1 5pk7nbq2k80d7
select * from c_lob where id = 208757 1 7u49nupubn0f9
select * from c_lob where id = 210170 1 a9xx1vza6c0h7
select * from c_lob where id = 208784 1 9b7fqasa8n0kr
select * from c_lob where id = 207274 1 0afz1hv6qn0rm
select * from c_lob where id = 208213 1 2qkbhxp0yw0ry
select * from c_lob where id = 206514 1 07cp95um100tw
select * from c_lob where id = 207736 1 am2k774t3s0ut
select * from c_lob where id = 208651 1 f5kzzvq46s0w1
select * from c_lob where id = 206858 1 7cumd57a540w4
select * from c_lob where id = 209514 1 5nmf9va04w0wv
select * from c_lob where id = 208768 1 5gz1upuwsn0y4
select * from c_lob where id = 207470 1 7rccz6cwzh0y7
select * from c_lob where id = 210164 1 f0d6vfbqs40y7
select * from c_lob where id = 209615 1 bqs2d4aptn103
select * from c_lob where id = 207870 1 gmt5jampz412b
select * from c_lob where id = 206788 1 9yfs2vgxk412k
select * from c_lob where id = 209007 1 2by5fnka5012p
select * from c_lob where id = 208521 1 fzbpuv0fhh12t
select * from c_lob where id = 208686 1 ck3vywzus8133
select * from c_lob where id = 208925 1 az44y96c7w133
select * from c_lob where id = 207125 1 7gwm2qk0q413x
select * from c_lob where id = 210649 1 gu2h2479h414q
select * from c_lob where id = 210549 1 g03qxnjf0c14t
select * from c_lob where id = 208669 1 c75w1kbwzh14y
select * from c_lob where id = 206864 1 b1839t9s30159
select * from c_lob where id = 210696 1 0qtzxm7zs016h
... ...

可以看出產生大量硬解析

生產上awr定位到library cache lock ，但是硬解析，軟解析都不高，通過ash看估計會有listener log證明短連結

這裡0002是鎖的級別
library cache的2表示排它
把4F變成10進製為79，79可以從 x$kglob看出內容來... ...具體我也不知道怎麼看... ...

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29990276/viewspace-1433729/，如需轉載，請註明出處，否則將追究法律責任。

短連線 引起的 library cache lock

相關文章

短連線引起的 library cache lock