oracle latch優化

zhengbao_jun發表於2010-11-17
Oracle優化
tch產生的原因，診斷以及對策

Latch爭用通常意味這某個程式持有latch的時間過長。如果latch爭用明顯，系統效能將顯著下降。在高併發的環境中，latch爭用經常發生，並且你無法完全消除latch爭用。在v$system_event中總會出現latch free等待事件。只有當time_waited相對例項啟動以來的總時間比較明顯時，你才需要關注latch爭用。當latch在系統範圍內的等待時間比較顯著時，你可以通過v$latch中的sleeps列來發現爭用顯著的latch：

Select name, gets, misses, immediate_gets, immediate_misses, sleeps 

from   v$latch 

order by sleeps;

                                             immediate immediate

name                       gets     misses        gets    misses     sleeps

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

enqueue hash chains    42770950       4279           0         0       1964

shared pool             9106650       5400           0         0       2632

row cache objects      69059887      27938         409         0       7517

enqueues               80443314     330167           0         0      13761

library cache          69447172     103349      465827       190      44328

cache buffers chains 1691040252    1166249    61532689      5909     127478

. . .

對不同的latch，其產生的原因以及可採取的對策都有所不同。詳細的說明所有的latch可以寫成一本書了。這裡我們只選擇最常見的五個latch加以說明：shared pool, library cache, cache buffers chains, cache buffers lru chain和row cache objects。

Shared pool和library cache latch

Oracle的共享池由不同的結構組成。主要包括：資料字典快取，sql區和庫快取。通過v$sgastat你可以檢視其他一些結構。Shared pool latch主要用來保護共享池的記憶體結構，當分配或者釋放共享池記憶體時需要先獲得該latch。例如，為一個新的sql語句或pl/sql過程、函式、包，觸發器等分配空間（硬解析）時，或者為換出、清除某些記憶體塊，以便為新的物件騰出足夠的空間時，都需要獲取shared pool latch。

在oracle9i之前，共享池記憶體結構由一個獨立shared pool latch保護，從9i開始，則有最多7個子latch可以用於共享池的保護。這也是為什麼oracle9i可以將共享池分成多個子共享池的原因（伺服器至少需要4顆cpu，並且shared_pool_size大於250m才能使用多個子共享池的特性）。子共享池的個數可以通過隱含引數_kghdsidx_count手動調節，該引數同時會指定合適的shared pool子latch的個數。如果你手動增加子共享池的個數，你應該同時增加shared_pool_size的值，因為每個子共享池都有自己的結構，lru列表和shared pool latch。否則，例項啟動時可能會遇到以下錯誤：

Ora-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","sga heap(5,0)","fixed allocation callback").

下面的統計資訊是從一個16顆cpu，shared_pool_size為256m的oracle9i資料庫中讀取的。由_kghdsidx_count引數可知共享池被分成2個子池，通過x$kghlu(kernel generic heap lru)可以知道lru列表也有2個。v$latch_children檢視顯示了7個子latch中的2個已經被使用。

Select a.ksppinm, b.ksppstvl 

from   x$ksppi a, x$ksppsv b 

where  a.indx = b.indx 

and    a.ksppinm = ’_kghdsidx_count’;



ksppinm            ksppstvl

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

_kghdsidx_count    2



select addr, kghluidx, kghlufsh, kghluops, kghlurcr, kghlutrn, kghlumxa 

from   x$kghlu;



addr             kghluidx   kghlufsh   kghluops kghlurcr kghlutrn   kghlumxa

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

80000001001581b8        2   41588416  496096025    14820    17463 2147483647

8000000100157e18        1   46837096 3690967191    11661    19930 2147483647



select addr, name, gets, misses, waiters_woken

from   v$latch_children 

where name = ‘shared pool’;



addr             name                 gets     misses waiters_woken

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

c00000004c5b06b0 shared pool             0          0             0c00000004c5b0590 shared pool             0          0             0c00000004c5b0470 shared pool             0          0             0c00000004c5b0350 shared pool             0          0             0c00000004c5b0230 shared pool             0          0             0c00000004c5b0110 shared pool    1385021389   90748637      12734879c00000004c5afff0 shared pool    2138031345  413319249      44738488

庫快取中主要儲存遊標，sql語句，執行計劃，分析樹等。這些結構由library cache latch保護。當oracle程式修改、檢查、銷連線(pinning)、鎖定、裝載，或者執行庫快取中的結構時，都需要先獲得library cache latch。通過查詢v$latch_children可以得知當前例項中的library cache子latch的個數。通常應該為大於cpu個數的最小質數，該值由隱含引數_kgl_latch_count控制。從oracle9i開始，v$sqlarea檢視增加了一個child_latch列，用來指示遊標在各個library cache latch是如何分佈的。

Select count(*)

from   v$latch_children 

where  name = ‘library cache’;

Shared pool 和 library cache latch爭用原因一 ―― 分析

Shared pool和library cache latch爭用通常是由於硬分析引起。硬分析需要分配新的遊標，或者將已經換出的遊標重新執行。硬分析過多說明sql語句沒有充分繫結變數。硬分析是代價十分昂貴的操作，在分析期間需要一直持有ibrary cache latch。

n 通過下列查詢可以發現系統中是否存在大量硬分析。軟分析數則可以用總分析數減去硬分析數獲得

Select a.*, sysdate-b.startup_time days_old 

from   v$sysstat a, v$instance b 

where a.name like ‘parse%’;



statistic# name                      class      value   days_old

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

       230 parse time cpu               64   33371587  4.6433912

       231 parse time elapsed           64   63185919  4.6433912

       232 parse count (total)          64 2137380227  4.6433912

       233 parse count (hard)           64   27006791  4.6433912

       234 parse count (failures)       64      58945  4.6433912
備註：分析失敗可能是由於“ora-00942: table or view does not exist”錯誤或者共享記憶體不足。

n 檢視當前會話是否有大量硬分析

Select a.sid, c.username, b.name, a.value, 

       round((sysdate - c.logon_time)*24) hours_connected

from   v$sesstat a, v$statname b, v$session c

where  c.sid        = a.sid

and    a.statistic# = b.statistic#

and    a.value      > 0

and    b.name       = ‘parse count (hard)’

order by a.value;



 sid username   name                    value hours_connected

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

 510 sys        parse count (hard)         12               4

 413 pmappc     parse count (hard)        317              51

  37 pmhcmc     parse count (hard)      27680             111

 257 pmappc     parse count (hard)      64652              13

 432 pmappc     parse count (hard)     105505              13

在oracle10g中，通過v$sess_time_model檢視中對硬分析和失敗分析的時間統計資訊，可以知道硬分析的來源。下面的例子顯示了某個會話的v$sess_time_model資訊。

Select * 
From   v$sess_time_model 
Where  sid = (select max(sid) from v$mystat);
 sid    stat_id stat_name                                             value
---- ---------- ------------------------------------------------ ----------
 148 3649082374 db time                                            11141191
 148 2748282437 db cpu                                              9530592
 148 4157170894 background elapsed time                                   0
 148 2451517896 background cpu time                                       0
 148 4127043053 sequence load elapsed time                                0
  148 1431595225 parse time elapsed                                  3868898
  148  372226525 hard parse elapsed time                             3484672
 148 2821698184 sql execute elapsed time                            9455020
 148 1990024365 connection management call elapsed time                6726
  148 1824284809 failed parse elapsed time                                 0
  148 4125607023 failed parse (out of shared memory) elapsed time          0
  148 3138706091 hard parse (sharing criteria) elapsed time            11552
  148  268357648 hard parse (bind mismatch) elapsed time                4440
 148 2643905994 pl/sql execution elapsed time                         70350
 148  290749718 inbound pl/sql rpc elapsed time                           0
 148 1311180441 pl/sql compilation elapsed time                      268477
 148  751169994 java execution elapsed time                               0

上面的分析統計資訊可以按照下面的方法分組：

1. parse time elapsed

2. hard parse elapsed time

3. hard parse (sharing criteria) elapsed time

4. hard parse (bind mismatch) elapsed time

2. failed parse elapsed time

3. failed parse (out of shared memory) elapsed time

n 確定系統中的常量sql語句（literal sql），它們往往都是可以使用並且應該使用繫結變數的。下面通過查詢v$sqlarea檢視，列出超過4個執行例項的sql語句的前40個字元，這裡假設你的系統中前40個字元相同的語句被認為是沒有使用繫結變數的常量sql。很明顯，如果使用更長的字串或者更多的執行例項作為過濾條件，得到的結果sql語句會少很多。然後你可以根據得到的結果，建議程式開發人員對這些常量sql語句儘量使用繫結變數。

Select hash_value, substr(sql_text,1,80)

from   v$sqlarea

where  substr(sql_text,1,40) in (select substr(sql_text,1,40)

                                 from   v$sqlarea

                                 having count(*) > 4

                                 group by substr(sql_text,1,40))

order by sql_text;



hash_value substr(sql_text,1,80)

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

2915282817 select revenue.customer_id, revenue.orig_sys, revenue.service_typ

2923401936 select revenue.customer_id, revenue.orig_sys, revenue.service_typ

 303952184 select revenue.customer_id, revenue.orig_sys, revenue.service_typ

 416786153 select revenue.customer_id, revenue.orig_sys, revenue.service_typ

2112631233 select revenue.customer_id, revenue.orig_sys, revenue.service_typ

3373328808 select region_id from person_to_chair where chair_id = 988947

 407884945 select region_id from person_to_chair where chair_id = 990165

3022536167 select region_id from person_to_chair where chair_id = 990166

3204873278 select region_id from person_to_chair where chair_id = 990167

 643778054 select region_id from person_to_chair where chair_id = 990168

2601269433 select region_id from person_to_chair where chair_id = 990169

3453662597 select region_id from person_to_chair where chair_id = 991393

3621328440 update plan_storage set last_month_plan_id = 780093, pay_code

2852661466 update plan_storage set last_month_plan_id = 780093, pay_code

 380292598 update plan_storage set last_month_plan_id = 780093, pay_code

2202959352 update plan_storage set last_month_plan_id = 780093, pay_code

. . .

在oracle9i中，也可以通過如下的語句查詢v$sql檢視中使用了相同的執行計劃的sql語句，這些語句也可能是常量sql語句。

Select plan_hash_value, hash_value
from v$sql
order by 1,2;

如果你的系統中存在大量的常量sql語句，當你將它們改為充分使用繫結變數後，對shared pool latch和library cache latch的爭用將會顯著減少。更改sql語句，使用繫結變數，這通常需要修改應用程式。另外一個不需要改動應用的方法是，修改初始化引數cursor_sharing，將其值改為force（注：原文如此。一般情況下請儘量使用similar而不是force），這個引數允許系統對一些只有常量值不一樣的sql語句共享遊標，以減少latch爭用、記憶體佔用和硬分析。

注意：在oracle8i早期版本中，使用cursor_sharing可能導致bug。在使用了物化檢視的環境中，請慎用該引數，否則可能導致長久的library cache pin等待。另外，使用cursor_sharing = force可能導致優化器生成錯誤的執行計劃。這或多或少的會對系統效能造成影響。從oracle9i起，優化器可以通過窺視pga中的資訊來進行變數繫結，以此生成合適的執行計劃，該特性可以通過隱含引數_optim_peek_user_binds來開啟，並且該特性只對那些需要硬分析的sql語句有效，這意味著會基於繫結變數的第一個值來生成執行計劃。

當一個新的sql語句到達時，oracle首先在庫快取中檢查是否已經有相同的語句存在。如果已經存在，則可以花費較小的代價執行該語句，這就是所謂的軟分析。硬分析通常意味著較壞的效能，而軟分析過多也不是什麼好事。在軟分析期間，需要持有library cache latch，並且oracle依然需要對語句進行語法和語義檢查，除非該語句已經在會話的遊標快取中。你可以通過設定引數session_cached_cursors來減少library cache latch的持有時間（具體資訊請檢視oracle metalin，編號#30804.1和 #62143.1）。但是，減少軟分析的最佳方法還是優化應用程式。最好是分析一次，執行多次（很像java的宣傳口號），而不要分析一次，執行一次。你可以通過v$sqlarea的parse_calls列來查詢分析過多的sql語句。

Shared pool latch爭用原因二 ―― 過大的共享池

從oracle9i起，由於引入了多個子共享池的特性，過大的共享池不再是一種壞事。在9i之前，過大的共享池通常會引起shared pool latch爭用。共享池中可用記憶體分成不同的記憶體塊(chunk)，不同大小範圍的塊由不同的可用列表(freelist)來管理。在共享池中分配空間時，需要掃描可用列表，掃描期間，需要持有shared pool latch。過大的共享池會使得可用列表過長，從而使得shared pool latch的持有時間變長。在高併發環境中，latch持有時間過長就可能造成latch爭用（表現為較高的sleeps和misses值），尤其是大量使用常量sql的系統，對這樣的系統，不要一味想著加大共享池，更重要的是想一想你為什麼會需要儲存這麼多不能共享的語句到共享池中。

通過alter session set events ’immediate trace name heapdump level 2’可以轉存共享池資訊，從中可以看到共享池的可用列表資訊。在生成的跟蹤檔案中查詢bucket，你可以發現記憶體塊(chunk)分配到不同的bucket上。另外，你也可以通過下面的方法生成一個查詢來列出共享池的可用記憶體管理資訊。該查詢只要生成一次，就可以在生成該跟蹤檔案的資料庫中重複使用，但不要在其他不同版本的資料庫中使用，否則可能得到錯誤的結果。該查詢在oracle10gR1中不可用，因為它限制了case分支數不能超過128（具體資訊參考oracle metalink 編號#131557.1和bug號#3503496），或者你可以通過使用decode和sign函式來完成該功能。

Sql> oradebug setmypid

statement processed.

Sql> oradebug dump heapdump 2

statement processed.

Sql> oradebug tracefile_name

/u01/admin/webmon/udump/orcl_ora_17550.trc

sql> exit

(注：上面是使用oradebug獲得轉存檔案，也可以使用alter session set events ’immediate trace name heapdump level 2’)

$ grep bucket /u01/admin/webmon/udump/orcl_ora_17550.trc > tmp.lst

$ sed ’s/size=/ksmchsiz>=/’ tmp.lst > tmp2.lst

$ sed ’s/ bucket //’ tmp2.lst | sort –nr > tmp.lst




# 通過shell指令碼生成查詢

echo ’select ksmchidx, (case’

cat tmp.lst | while read line

do

  echo $line | awk ’{print "when " $2 " then " $1}’

done

echo ’end) bucket#,’

echo ’       count(*) free_chunks,’

echo ’       sum(ksmchsiz) free_space,’

echo ’       trunc(avg(ksmchsiz)) avg_chunk_size’

echo ’from   x$ksmsp’

echo "where  ksmchcls = ’free’"

echo ’group by ksmchidx, (case’;

cat tmp.lst | while read line

do

  echo $line | awk ’{print "when " $2 " then " $1’}

done

echo ’end);’

如果你發現資料庫的共享池可用列表過長，並且系統中使用了常量sql，你或許應該考慮減少shared_pool_size。這會降低系統中對shared pool latch的爭用。但要注意共享池不能太小，否則可能導致ora-04031錯誤。另外，通過使用dbms_shared_pool.keep將常用物件釘在記憶體中也是個好主意，v$db_object_cache中儲存了釘在記憶體中的物件的資訊。

Library cache latch爭用原因三 ―― 語句版本數過多

對於字元完全一致但是由於引用不同的物件而不能共享的sql語句，oracle使用多個子遊標來指向該語句的不同版本。例如，系統中有三個名叫customer的表，但是屬於不同的模式。則對於語句select * from customer，不同的模式執行該語句，語句字元上完全一樣，其hash值完全一樣，但是該語句無法共享，因為它引用的物件不同。所以會生成該語句的不同子版本。當一個sql語句有多個子版本時，oracle需要比較該語句的所有存在的子版本，在此期間需要持有library cache latch，這樣可能導致library cache latch爭用。解決這種情況也很簡單，在系統中，儘量不要使用相同的物件名。下面的查詢列出了v$sqlarea中子版本超過20的所有sql語句：

Select version_count, sql_text
from v$sqlarea
where version_count > 20
order by version_count, hash_value;

注意：在oracle8i中，語句的版本過多，可能導致和sql執行監控相關的bug(參考oracle metalink 編號#62143.1)。這個bug會導致sql無法共享。可以通過將隱含引數_sqlexec_progression_cost設定為0來禁用sql執行監控特性，該引數同時會禁止v$session_longops中的資料。

Oracle提供了檢視v$sql_shared_cursor，其中可以看到為什麼無法共享一個已經存在子游標。每個列都限制了遊標無法共享的一個原因。

-- 大寫的列是oracle9i起才有列.
-- oracle 10g R1還有其他8個列
select a.*, b.hash_value, b.sql_text
from v$sql_shared_cursor a, v$sqltext b, x$kglcursor c
where a.unbound_cursor || a.sql_type_mismatch ||
a.optimizer_mismatch || a.outline_mismatch ||
a.stats_row_mismatch || a.literal_mismatch ||
a.sec_depth_mismatch || a.explain_plan_cursor ||
a.buffered_dml_mismatch || a.pdml_env_mismatch ||
a.inst_drtld_mismatch || a.slave_qc_mismatch ||
a.typecheck_mismatch || a.auth_check_mismatch ||
a.bind_mismatch || a.describe_mismatch ||
a.language_mismatch || a.translation_mismatch ||
a.row_level_sec_mismatch || a.insuff_privs ||
a.insuff_privs_rem || a.remote_trans_mismatch ||
a.logminer_session_mismatch || a.incomp_ltrl_mismatch ||
a.overlap_time_mismatch || a.sql_redirect_mismatch ||
a.mv_query_gen_mismatch || a.user_bind_peek_mismatch ||
a.typchk_dep_mismatch || a.no_trigger_mismatch ||
a.flashback_cursor <> ’nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn’
and a.address = c.kglhdadr
and b.hash_value = c.kglnahsh
order by b.hash_value, b.piece;

Cache buffers chains latch

當一個資料塊讀入到sga中時，該塊的緩衝區頭(buffer header)會放置在一個hash bucket的連結串列(hash chain)中。該記憶體結構由一系列cache buffers chains子latch保護（又名hash latch或者cbc latch）。下圖描述了hash latch，hash bucket，buffer header和hash chain的關係

圖一. Oracle8i以上的資料緩衝區示意圖

一個程式要新增，刪除，查詢，檢視，讀取或者修改hash chain上的塊，必須先獲得cache buffers chains latch，以保證對該chain的排他訪問，為保證完整性，必須犧牲併發性。

注：從oracle9i開始，對cache buffer chains latch可用只讀共享訪問，這可以減少部分爭用，但並不能完全消除爭用。

一個特定的塊頭具體分配到哪個hash bucket，是通過dba(data block address)和隱含引數_db_block_hash_buckets實現的。例如，hash bucket = mod(dba, _db_block_hash_buckets)。通過查詢v$bh和x$bh檢視可以發現緩衝區頭的爭用，也可以通過以下語句轉存緩衝區頭的資訊：

Alter system set events ’immediate trace name buffers level 1’;

在oracle8.0之前，每一個hash bucket都有一個cache buffers chains latch（hash latch），並且hash bucket都只有一個hash chain連結串列。換句話說，hash latch,hash backet和hash chain之間是1:1:1的關係。預設的hash bucket個數為大於db_block_buffers / 4的最小質數，通過隱含引數_db_block_hash_buckets可以修改該值。例如，假如db_block_buffers = 50000，則該例項中有12501個hash latch，有12501個hash bucket，有12501個hash chain。

從oracle8i開始，oracle將hash latch和hash bucket之前的關係改成了 1:m，但hash bucket和hash chain之間還是1:1，也就是一個hash latch可以同時保護多個hash chain連結串列。這樣，可以顯著的減少系統中hash latch的個數。Hash latch的預設個數還是和db_block_buffers的值相關。當資料緩衝區小於1G時，一般都是1024個。通過隱含引數_db_blocks_hash_latches可以修改該值。下面的語句查詢例項中的hash latch數目：

Select count(distinct(hladdr)) 

from   x$bh;




Count(distinct(hladdr))

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

                   1024




Select count(*) 

from   v$latch_children 

where  name = ’cache buffers chains’;




  count(*)

----------

      1024

Hash bucket的預設個數等於2 * db_block_buffers，可以通過隱含引數_db_block_hash_buckets修改。這樣，假如db_block_buffers=50000，則系統中有100000個hash bucket和100000個hash chain，但是隻有1024個hash latch（假設塊大小為8k）。由此可以看出，oracle8i和oracle8.0中，hash latch數目發生了顯著的變化。許多DBA認為，由於hash latch數目顯著減少，可能會導致latch爭用的增加。但是，oracle解釋說，通過以8為因子增加hash chain連結串列的個數，單個連結串列會比以前變得更短，這樣每次cache buffer chains latch的持有時間也變得更短，以此來補償latch個數減少帶來的latch爭用。但不要依賴這個機制，你還是會看到很多latch爭用。

Oracle10g使用了不同的演算法來決定系統中hash bucket的預設個數。一開始好像等於db_cache_size / 4。但後來的測試證明，在某些不同的db_cache_size值範圍內，例如當db_cache_size在132m和260m之間時，hash bucket的個數是一個常數。下面的表中列出了從oracle8i到oracle10g，不同的資料緩衝大小時的hash bucket的預設個數，其中資料塊大小都是8k。

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

相關文章