Oracle 9i 整體效能優化概述(zt)

2.3 診斷FREE LIST競爭 6
2.3.1 概念 6
2.3.2 是否存在free list爭用 6
2.3.3 確定free list 爭用的段 7
2.3.4 優化free list爭用 7
2.4 診斷LOCK競爭 8
2.4.1 概念 8
2.4.2 可能引起lock contention的原因 8
2.4.3 鎖解決辦法 9
2.4.4 死鎖 9

2 調整爭用
爭用：每當一個Oracle 程式試圖訪問一個Oracle結構，但由於該結構正由另一個程式結構使用而未能成功訪問到它時，就發生對Oracle資源的爭用。常見的有latch、Free List 、lock爭用。
主要維護的爭用有：
 Latch（鎖存器）：可作為記憶體效能的指標，說明記憶體需要調整。
 Free List：會導致繁忙表上的DML操作效能很差。
 Lock：會遇到徹底的暫停，產生巨大的效能影響。

2.1優化維護
維護時，主要通過檢查，判斷存在的latch和free list爭用是否合理，不合理，則啟動相應的優化工作。
而lock，在遇到問題的時候，可作為維護參考，平時不進行太多的維護（或從應用上考慮優化）。（除了定期去$ORACLE_HOME/admin/$ORACLE_SID/udump檢視死鎖情況外）

2.2診斷latch競爭
2.2.1概念
Latch是簡單的、低層次的序列化技術，用以保護SGA中的共享資料結構，比如併發使用者列表和buffer cache裡的blocks資訊。一個伺服器程式或後臺程式在開始操作或尋找一個共享資料結構之前必須獲得對應的latch，在完成以後釋放latch。不必對latch本身進行優化，如果latch存在競爭，表明SGA的一部分正在經歷不正常的資源使用。

1）Latch的作用：
A、序列化訪問：保護SGA中的共享資料結構；保護共享記憶體的分配。
B、序列化執行：避免同時執行某些關鍵程式碼；避免互相干擾。

2）Latch請求的兩種型別：
A、willing-to-wait：請求的程式經過短時間的等待後再次發出請求，直到獲得latch
B、immediate：如果沒有獲得latch，請求的程式不等待，而是繼續處理其他指令。

2.2.2是否存在latch爭用
select event,total_waits,time_waited
from v$system_event
where event = ‘latch free’;
如：
EVENT TOTAL_WAITS TIME_WAITED
---------- ----------- -----------
latch free 4320663 779167
自例項啟動以來，發生過4320663個latch爭用，等候這些latch所 花費的時間為779167毫秒。

2.2.3 檢查Latch是否主要競爭
檢查latch free是不是主要的wait event：
Select event,total_waits,time_waited from v$system_event order by time_waited desc;
2.2.4 DBA關注的latch內容
DBA需要關注的latch有：
Select name,gets,misses,sleeps,wait_time,spin_gets,immediate_gets,immediate_misses from v$latch
where name like ‘%shared pool%’
or name like ‘%library cache’
or name like ‘%cache buffers lru chain%’
or name like ‘%cache buffers chains%’
or name like ‘%redo allocation%’
or name like ‘%redo copy%’;

與willing-to-wait請求有關的列：
Select name,gets,misses,sleeps,wait_time,spin_gets from v$latch;
與immediate請求有關的列：
Select name,immediate_gets,immediate_misses from v$latch
Gets: number of successful willing-to-wait requests for a latch;
Misses: number of times an initial wiling-to-wait request was unsuccessful;
Sleeps: number of times a process waited after an initial willing-to-wait request;
Wait_time: number of milliseconds waited after willing-to-wait request;
Spin_gets: gets that misses first try but succeed after spinning.
Immediate_gets: number of successful immediate requests for each latch;
Immediate_misss: number of unsuccessful immediate requests for each latch;

 shared pool：需要優化shared pool。（在沒有配置large pool情況下使用shared server選件，會導致很高的shared pool latch.）
 library cache：需要優化library cache。
 cache buffers LRU chain：管理database cache buffers上的LRU List上的塊，自由緩衝區列表。此爭用有兩種可能：
- 導致嚴重的全部掃描的SQL語句或執行計劃。SQL需要優化。
- 髒緩衝區寫盤database writer未能跟上I/O請求。優化磁碟I/O。
 cache buffers chains：預示某些某些緩衝塊被重複搜尋，塊大小比較大的比塊大小小的更容易遇見此latch。優化塊大小與I/O關係。
 redo allocation：許多使用者同時放入redo log buffer，則產生該latch。優化redo log buffer。
 redo copy:使用者server process複製資訊到redo log buffer時發生。優化redo log buffer。

一般無需調整latch(實際上在9i中，init.ora已經廢棄了所有的latch調整引數)，但是下列的措施是有用的（根據v$latch作為調整其他效能指標的指示器，而不是調整latch本身）：
A、對處於競爭中的latch做進一步的調查
B、如果競爭主要存在於shared pool和library cache中，可以考慮調整應用
C、如果進一步的調查顯示需要調整shared pool和buffer cache，就進行調整

2.3診斷free list競爭
（使用management local的表空間不用調整，對錶的insert和delete,update操作有重要指導意義）
判斷表空間是否management local：
select tablespace_name,contents,extent_management,allocation_type,segment_space_management
from dba_tablespaces;

2.3.1概念
每個段保持一個free list來包含這個段中能接受新行的塊。
比如如果應用程式有許多頻繁插入行的使用者，在試圖訪問該表的free list就可能會經歷等待。

2.3.2是否存在free list爭用
select event,total_waits,time_waited from v$system_event
where event = ‘buffer busy waits’;
自資料庫啟動來，可能發生的free list 爭用。

還應經過以下查詢確定：
select class,count,time from v$waitstat
where class in
(‘free list’,’segment header’);
count:訪問一個塊的等待次數。
Time：等待訪問塊所花費的總時間。

例：
CLASS COUNT TIME
------------------ ---------- ----------
segment header 1869 1725
free list 0 0

說明：針對段頭部的free list相關等待發生了1896次，針對自由列表發生的free list為0次。

2.3.3確定free list 爭用的段
select s.segment_name,s.segment_type,s.freelists
from dba_segments s,v$session_wait w
where w.p1 = s.header_file
and w.p2 = s.header_block
and w.event = 'buffer busy waits';
（確定當前的爭用）

2.3.4優化free list爭用
1） 增加附加的free list
預設建立只有一個free list，若要增加更多的free list，可以使用
alter table hr.employee storage (freelists 5);
進行修改。
2） 把段轉移到自動段管理表空間上（Oracle 9i新特性）
management local
使用自動段管理表空間，將不再利用free list管理塊，而是利用表空間資料檔案頭的點陣圖來管理表空間中每個段的自由塊分配（包括新建的任何附加段），此時該段在dba_segments檢視中的freelists的值將是NULL空值。
（create一個management local的表空間，使用
alter table hr.employee move tablespace app1_data;
的方法把表從一個表空間移動到另外一個management local的表空間）
(表裡有long欄位，得用其他方法移動)

2.4診斷lock競爭
2.4.1概念
lock 用來保護對資料的訪問的一致性，latch用來保護對記憶體的訪問（latch從不在process之間共享）。
Lock通常發生在許多使用者正在少量的表上執行ＤＭＬ操作的時候發生。一旦lock申請獲得，lock會一直保留給該使用者，直到lock的使用者釋出一條commit或rollback為止。
使用Oracle預設加鎖機制，可以：
１） 修改同一個表的不同行，不用擔心鎖問題。
２） 修改同一個表的同一行，由等待佇列根據system change number(SCN)決定誰的修改結果是最終修改結果。（預設佇列最多數從init.ora的session獲得，也可修改enqueue_resources手工調整）

如果需要嚴格的鎖機制，可把init.ora的row_locking從always（row lock）修改為intent的（table lock）。

一共有兩類鎖“DML資料鎖（Data lock）”和“DDL目錄鎖（Dictionary lock）”，有兩種模式使用鎖：“獨佔型（Exclusive lock）”和“共享型（Share lock）”模式，實現資料的併發性和一致性。

v$lock：報告鎖模式等情況。
v$locked_object:報告被鎖的物件資訊。
dba_waiters:報告阻塞的具體情況，有鎖模式，wait session和hold　session。
dba_blockers:報告霸佔資源導致別的session阻塞的sid.（如果沒有，則執行$ORALCE_HOME/rdbms/admin/catblock.sql和utllockt.sql指令碼建立）

DML事務使用row-level locks,查詢不會鎖定資料。鎖有兩種模式：exlusive、share。
鎖的型別：
• DML or data locks:
– Table-level locks（TM的數量一般有init.ora的transactions獲，但可修改dml_locks做調整）
– Row-level locks（TX）
• DDL or dictionary locks
一個transaction至少獲得兩個鎖：一個共享的表鎖，一個專有的行鎖。Oracle server將所有的鎖維護在一個佇列裡，佇列跟蹤了等待鎖的使用者、申請鎖的型別以及使用者的順序資訊。
Lock在下列情況會釋放：commit；rollback；terminated（此時由pmon清理locks）。

Quiesced database：一個資料庫如果除了sys和system之外沒有其他活動session，這個資料庫即處於quiesced狀態。活動session是指這個session當前處於一個transaction中，或一個查詢中，一個fetch中，或正佔有某種共享資源。

2.4.2可能引起lock contention的原因
不必要的高層次的鎖；
長時間執行的transaction；
未提交的修改；
其他產品施加的高層次的鎖。

2.4.3鎖解決辦法
如果出現了阻塞，則可通過下列方法之一解決這種爭用：
１） 修改應用程式，使之不要使用嚴格的鎖和不要有太長的事務（可設邏輯提交點）。（更不要使用顯示鎖select .. for update,lock table .. in exclusive mode;）
２） 查出鎖住物件，製造阻塞的session，通知使用者commit或rollback。
可使用profile的方式，宣告斷開已空閒一段時間的使用者。
３） 使用alter system kill session ‘sid,$serial’等方法殺死引起阻塞的session（如果該session沒有在活動不會收到被kill的資訊，直到發出新操作才得到kill提示）。

2.4.4死鎖
Oracle自動檢測和解決死鎖，方法是通過回滾引起死鎖的語句（statement），但是這條語句對應的transaction並沒有回滾，因此當收到死鎖的錯誤資訊後，應該去回滾改transaction的剩餘部分。
發生死鎖後，會自動在$ORACLE_BASE/admin/$ORACLE_SID/udump目錄下生成一個詳細描述死鎖的跟蹤檔案。
(Ｏracle會有租塞，但不會有死鎖，因發生死鎖後，導致發生死鎖的操作會取消並報告“ORA-00060: 等待資源時檢測到死鎖”錯誤，但可能會繼續出現阻塞（因無人commit或rollback）。)