[@more@]

latch:shared pool

shared pool latch 起到保護堆的作用，為了查詢free chunk，檢索空閒列，分配適當的chunk，

必要時候分割chunk。這些全部只能在獲得shared pool latch之後才能執行。

因此在獲得shared pool latch過程中會發生latch:shared pool等待事件.

一般情況下一個例項上只有一個latch:shared pool

harding parse嚴重時，會發生分割chunk的現象.

---==============================================================

測試latch:shard pool等待事件

建立測試指令碼：

create or replace procedure tt_pro is

begin

for i in 1..100000 loop

execute immediate 'select * from tt where object_id='||i;

end loop;

end;

/

同時在三個會話執行這個指令碼，檢視v$session_event

select * from (

select event,total_waits,time_waited from v$session_event where sid in(12,192,197) order by 2 desc )where rownum<=10

EVENT TOTAL_WAITS TIME_WAITED

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

latch: shared pool 84996 612

latch: shared pool 57746 500

latch: shared pool 56557 640

cursor: pin S wait on X 9798 9471

cursor: pin S wait on X 8682 8370

library cache: mutex X 471 9

library cache: mutex X 408 12

library cache: mutex X 241 10

library cache lock 231 81

events in waitclass Other 224 675

10 rows selected.

如果發生latch:shared pool等待事件，不應該加大共享池，加大了共享池，

相應的要管理的chunk數量也要增加，檢索空閒列的時候，佔有

shared pool latch的時間會增大。

---==============================================================

相應的解決方法：

1)oracle9i之後，可以有多個共享池(副池)，最多有7個副池分別管理。

oracle在cpu數位4，共享池大小為250M以上的情況下，透過建立與

_kghdsidx_count值相同量的副池來管理共享池。副池也是獨立的共享池結構。

2)減少共享的大小，這樣就可以減少佔有shared pool latch的時間。

但這回導致ora-04031錯誤

可以利用dbms_shared_pool.keep指定物件永久置於共享池中，來消除問題

3)cursor sharing

---===============================================================

下面，測試一下，使用過個共享池的情況下，會不會減少latch:shared pool

確認cpu_count大於4，共享池大小大於250M

SQL> show parameter cpu_count

NAME TYPE VALUE

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

cpu_count integer 4

SQL> show sga

Total System Global Area 626327552 bytes

Fixed Size 2178336 bytes

Variable Size 461374176 bytes

Database Buffers 155189248 bytes

Redo Buffers 7585792 bytes

SQL> alter system set "_kghdsidx_count"=4 scope=spfile;

System altered.

SQL> select name,gets from v$latch_children where name='shared pool';

NAME GETS

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

shared pool 19

shared pool 27058

shared pool 27862

shared pool 31982

shared pool 27345

可以看出，4個shared pool latch 被啟用

然後再在三個會話中重新執行tt_pro指令碼，看看情況

SQL> select * from (

2 select event,total_waits,time_waited from v$session_event where sid in(9,10,199) order by 2 desc )where rownum<=10

3 ;

EVENT TOTAL_WAITS TIME_WAITED

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

latch: shared pool 23120 126

cursor: pin S wait on X 18792 18766

latch: shared pool 14996 68

cursor: pin S wait on X 13740 13490

latch: shared pool 9860 70

library cache lock 716 256

library cache lock 705 262

library cache: mutex X 692 35

library cache: mutex X 605 41

library cache load lock 479 2

10 rows selected.

相應的latch:shared pool減少很多.

---====================================================

hard parse引起的library cache爭用比shared pool爭用多

latch:shared pool的一點理解

相關文章