Oracle效能調整的十大要點.doc

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4

一、SGA

1、Shared pool tunning
Shared pool的最佳化應該放在優先考慮，因為一個cache miss在shared pool中發生比在data buffer中發生導致的成本更高，由於dictionary資料一般比library cache中的資料在記憶體中儲存的時間長，所以關鍵是library cache的最佳化。
Gets：（parse）在namespace中查詢物件的次數；
Pins：（execution）在namespace中讀取或執行物件的次數；
Reloads：(reparse)在執行階段library cache misses的次數，導致sql需要重新解析。

1） 檢查v$librarycache中sql area的gethitratio是否超過90％，如果未超過90％，應該檢查應用程式碼，提高應用程式碼的效率。
Select gethitratio from v$librarycache where namespace=’sql area’;

2) v$librarycache中reloads/pins的比率應該小於1％，如果大於1％，應該增加引數shared_pool_size的值。
Select sum(pins) “executions”,sum(reloads) “cache misses”,sum(reloads)/sum(pins) from v$librarycache;

reloads/pins>1%有兩種可能，一種是library cache空間不足，一種是sql中引用的物件不合法。

3）shared pool reserved size一般是shared pool size的10％，不能超過50％。V$shared_pool_reserved中的request misses＝0或沒有持續增長，或者free_memory大於shared pool reserved size的50%，表明shared pool reserved size過大，可以壓縮。

4）將大的匿名pl/sql程式碼塊轉換成小的匿名pl/sql程式碼塊呼叫過程。

5）從9i開始，可以將execution plan與sql語句一起儲存在library cache中，方便進行效能診斷。從v$sql_plan中可以看到execution plans。

6）保留大的物件在shared pool中。大的物件是造成記憶體碎片的主要原因，為了騰出空間許多小物件需要移出記憶體，從而影響了使用者的效能。因此需要將一些常用的大的物件保留在shared pool中，下列物件需要保留在shared pool中：
a. 經常使用的過程；
b. 經常操作的表上的已編譯的觸發器
c. Sequence，因為Sequence移出shared pool後可能產生號碼丟失。
查詢沒有儲存在library cache中的大物件：
Select * from v$db_object_cache where sharable_mem>10000 and type in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and kept='NO';
將這些物件儲存在library cache中：
Execute dbms_shared_pool.keep(‘package_name’);
對應指令碼：dbmspool.sql

7)查詢是否存在過大的匿名pl/sql程式碼塊。兩種解決方案：
A．轉換成小的匿名塊呼叫儲存過程
B．將其保留在shared pool中
查詢是否存在過大的匿名pl/sql塊：
Select sql_text from v$sqlarea where command_type=47 and length(sql_text)>500;

8）Dictionary cache的 最佳化
避免出現Dictionary cache的misses，或者misses的數量保持穩定,只能透過調整shared_pool_size來間接調整dictionary cache的大小。

Percent misses應該很低：大部分應該低於2％，合計應該低於15％
Select sum(getmisses)/sum(gets) from v$rowcache;
若超過15％，增加shared_pool_size的值。

2、Buffer Cache

1）granule大小的設定，db_cache_size以位元組為單位定義了default buffer pool的大小。
如果SGA<128M，granule=4M,否則granule＝16M，即需要調整sga的時候以granule為單位增加大小，並且sga的大小應該是granule的整數倍。

2) 根據v$db_cache_advice調整buffer cache的大小
SELECT size_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_reads FROM v$db_cache_advice WHERE NAME='DEFAULT' AND advice_status='ON' AND block_size=(SELECT Value FROM v$parameter WHERE NAME='db_block_size');

estd_physical_read_factor<=1

3) 統計buffer cache的cache hit ratio>90%，如果低於90％，可以用下列方案解決：

增加buffer cache的值；
使用多個buffer pool；
Cache table；
為 sorting and parallel reads 建獨立的buffer cache；

SELECT NAME,value FROM v$sysstat WHERE NAME IN ('session logical reads','physical reads','physical reads direct','physical reads direct(lob)');

Cache hit ratio=1-(physical reads-physical reads direct-physical reads direct (lob))/session logical reads;

Select 1-(phy.value-dir.value-lob.value)/log.value from v$sysstat log, v$sysstat phy, v$sysstat dir, v$sysstat LOB where log.name='session logical reads' and phy.name='physical reads' and dir.name='physical reads direct' and lob.name='physical reads direct (lob)';

影響cache hit ratio的因素：
全表掃描；應用設計；大表的隨機訪問；cache hits的不均衡分佈

4）表空間使用自動空間管理，消除了自由空間列表的需求，可以減少資料庫的競爭

3、其他SGA物件

1）redo log buffer
對應的引數是log_buffer，預設值與 OS相關，一般是500K。檢查v$session_wait中是否存在log buffer wait,v$sysstat中是否存在redo buffer allocation retries

A、檢查是否存在log buffer wait：
Select * from v$session_wait where event=’log buffer wait’ ;
如果出現等待，一是可以增加log buffer的大小，也可以透過將log 檔案移到訪問速度更快的磁碟來解決。

B、Select name,value from v$sysstat where name in (‘redo buffer allocation retries’,’redo entries’)
Redo buffer allocation retries接近0，小於redo entries 的1％，如果一直在增長，表明程式已經不得不等待redo buffer的空間。如果Redo buffer allocation retries過大，增加log_buffer的值。

C、檢查日誌檔案上是否存在磁碟IO競爭現象
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch completion%’;
如果存在競爭，可以考慮將log檔案轉移到獨立的、更快的儲存裝置上或增大log檔案。

D、檢查點的設定是否合理
檢查alert.log檔案中，是否存在‘checkpoint not complete’；
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch (check%’;

如果存在等待，調整log_checkpoint_interval、log_checkpoint_timeout的設定。

E、檢查log archiver的工作
Select event,total_waits,time_waited,average_wait from v$system_event where event like ‘log file switch (arch%’;

如果存在等待，檢查儲存歸檔日誌的儲存裝置是否已滿，增加日誌檔案組，調整log_archiver_max_processes。

F、DB_block_checksum=true，因此增加了效能負擔。（為了保證資料的一致性，oracle的寫資料的時候加一個checksum在block上，在讀資料的時候對checksum進行驗證）

2）java pool
對於大的應用，java_pool_size應>=50M，對於一般的java儲存過程，預設的20M已經夠用了。
3）檢查是否需要調整DBWn
Select total_waits from v$system_event where event=’free buffer waits’;

二、資料庫配置和IO問題

降低磁碟的IO
分散磁碟的IO
表空間使用本地管理
1、將檔案分散到不同的裝置上
1）將資料檔案與日誌檔案分開
2）減少與無關的磁碟IO
3）評估裸裝置的使用
4）分割表資料

2、表空間的使用

系統表空間保留給資料字典物件
建立本地管理表空間以避免空間管理問題
將表和索引分散到獨立的表空間中
使用獨立的回滾表空間
將大的資料庫物件儲存在各自獨立的表空間中
建立一個或多個獨立的臨時表空間

下列資料庫物件應該有單獨的表空間：
資料字典、回滾段、索引、臨時段、表、大物件

3、檢查IO統計資料
Select phyrds,phywrts,d.name from v$datafile d,v$filestat f where f.file#=d.file# order by d.name;
檢查最有可能引起磁碟IO瓶頸的檔案。

4、分割檔案
可以透過RAID和手工進行
Alter table table_name allocate extent (datafile ‘fiile_name’ size 10M);
但手工操作工作量很大。

5、最佳化全表掃描操作

1）檢查有多少全表發生：
Select name,value from v$sysstat where name like ‘%table scan%’;
table scans (short tables)/ table scans (long tables)與全表掃描相關，如果table scans (long tables)的值很高，說明大部分的table access 沒有經過索引查詢，應該檢查應用或建立索引，要確保有效的索引在正確的位置上。

合理的DB_FILE_MULTIBLOCK_READ_COUNT能減少table scan需要呼叫的IO次數，提高效能（與OS相關）。

2）檢視full table scan操作：
Select sid,serial#,opname,target，to_char(start_time,’HH24:MI:SS’) “start”,(sofar/totalwork)*100 “percent_complete” from v$session_longops;
透過v$session_longops裡的sql_hash_value與v$sqltext關聯，可以查詢導致full table scan的sql。
6、Checkpoint
Checkpoint進行的操作：DBWn進行IO操作；CKPT更新資料檔案頭和控制檔案。
經常進行Checkpoint的結果：減少恢復所需的時間；降低了系統執行時的效能。

LGWR以迴圈的方式將日誌寫到各個日誌組，當一個日誌組滿時，oracle server必須進行一個Checkpoint，這意味著：DBWn將對應log覆蓋的所有或部分髒資料塊寫進資料檔案；CKPT更新資料檔案頭和控制檔案。如果DBWn沒有完成操作而LGWR需要同一個檔案，LGWR只能等待。
在OLTP環境下，如果SGA很大並且checkpoint的次數不多，在Checkpoint的過程中容易出現磁碟競爭的狀況，在這種情況下，經常進行Checkpoint可以減少每次Checkpoint涉及到的髒資料塊的數目。

調節Checkpoint次數的辦法：
增大日誌檔案；增加日誌組以增加覆蓋的時間間隔。

7、日誌檔案

建立大小合適的日誌檔案以最小化競爭；
提供足夠的日誌檔案組以消除等待現象；
將日誌檔案存放在獨立的、能快速訪問的儲存裝置上（日誌檔案可以建立在裸裝置上）。日誌檔案以組的方式組織管理，每個組裡的日誌檔案的內容完全相同。

8、歸檔日誌檔案
如果選擇歸檔模式，必須要有兩個或兩個以後的日誌組，當從一個組切換到另一個組時，會引起兩種操作：DBWn進行Checkpoint；一個日誌檔案進行歸檔。

歸檔有時候會報錯：
ARC0：Beginning to archive log# 4 seq# 2772
Current log# 3 seq# 2773……
ARC0: Failed to archive log# 4 seq# 2772
ARCH: Completed to archiving log#4 seq# 2772
建議init引數修改如下：
log_archive_max_processes=2
#log_archive_dest = ‘/u05/prodarch’
log_archive_dest_1 = "location=/u05/prodarch MANDATORY’
log_archive_dest_state_1 = enable

log_archive_dest_2 = "location=/u05/prodarch2 OPTIONAL reopen=10" （或其它目錄）
log_archive_dest_state_2 = enable
log_archive_min_succeed_dest=1

log_archive_dest_state_3 = DEFER
log_archive_dest_state_4 = DEFER
log_archive_dest_state_5 = DEFER

三、最佳化排序操作

1、概念
首先在sort_area_size指定大小的記憶體區域裡排序，如果所需的空間超過sort_area_size，排序會在臨時表空間裡進行。在專用伺服器模式下，排序空間在PGA中，在共享伺服器模式下，排序空間在UGA中。如果沒有建立large pool，UGA處於shared pool中，如果建立了large pool，UGA就處於large pool中，而PGA不在sga中，它是與每個程式對應單獨存在的。

PGA：program global area,為單個程式（伺服器程式或後臺程式）儲存資料和控制資訊的記憶體區域。PGA與程式一一對應，且只能被起對應的程式讀寫，PGA在使用者登入資料庫建立會話的時候建立。

有關排序空間自動管理的兩個引數：
Pga_aggregate_target: 10M-4000G，等於分配給oracle instance的所有記憶體減去SGA後的大小。
Workarea_size_policy: auto/manual，只有Pga_aggregate_target已定義時才能設定為auto。
這兩個引數會取代所有的*_area_size引數。

措施：

儘可能避免排序；儘可能在記憶體中排序；分配合適的臨時空間以減少空間分配呼叫。

2、需要進行排序的操作：
A、建立索引；
B、涉及到索引維護的並行插入
C、order by或者group by（儘可能對索引欄位排序）
D、Distinct
E、union/intersect/minus
F、sort-merge join
G、analyze命令（僅可能使用estamate而不是compute）

3、診斷和措施
Select * from v$sysstat where name like ‘%sort%’;
Sort(disk):要求Io去臨時表空間的排序數目
Sort(memory)：完全在memory中完成的排序數目
Sort(rows)：被排序的行數合計

Sort（disk）/ Sort（memory）<5%,如果超過5％，增加sort_area_size的值。
SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio FROM v$sysstat disk,v$sysstat mem WHERE mem.NAME='sorts (memory)' AND disk.NAME='sorts (disk)';

4、監控臨時表空間的使用情況及其配置
Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks FROM v$sort_segment ;

Column Description
CURRENT_USERS Number of active users
TOTAL_EXTENTS Total number of extents
USED_EXTENTS Extents currently allocated to sorts
EXTENT_HITS Number of times an unused extent was found in the pool
MAX_USED_BLOCKS Maximum number of used blocks
MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort

臨時表空間的配置：
A、initial/next設定為sort_area_size的整數倍，允許額外的一個block作為segment的header
B、pctincrease=0
C、基於不同的排序需要建立多個臨時表空間
D、將臨時表空間檔案分散到多個磁碟上

四、診斷latch競爭

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、檢查Latch競爭
檢查latch free是不是主要的wait event：
Select * from v$system_event order by time_waited;

檢查latch的使用情況：
Select * from v$latch:
與willing-to-wait請求有關的列：gets、misses、sleeps、wait_time、cwait_time、spin_gets
與immediate請求有關的列：immediate_gets、immediate_misses

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;
Cwait_time: a measure of the cumulative wait time including the time spent spinning and sleeping,the overhead of context switches due to OS time slicing and page faults and interrupts;
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;

一般無需調整latch，但是下列的措施是有用的：
A、對處於競爭中的latch做進一步的調查
B、如果競爭主要存在於shared pool和library cache中，可以考慮調整應用
C、如果進一步的調查顯示需要調整shared pool和buffer cache，就進行調整

Select * from v$latch where name like ‘%shared pool%’ or name like ‘%library cache%’；

如果競爭是在shared pool或library cache上，表示下列集中情況：
A、不能共享的sql,應檢查他們是否相似，考慮以變數代替sql中的常量：
Select sql_text from v$sqlarea where executions=1 order by upper(sql_text);
B、共享sql被重新編譯，考慮library cache的大小是否需要調整：
SELECT sql_text,parse_calls,executions FROM v$sqlarea where parse_calls>5;

五、Rollback(undo) Segment 最佳化

1、概念
Transaction以輪循的方式使用rollback segment裡的extent，當前所在的extent滿時就移動到下一個extent。可能有多個transaction同時向同一個extent寫資料，但一個rollback segment block中只能儲存一個transaction的資料。

在每個Rollback segment header中儲存了一個transaction table，包括了每個rollback segment中包含的事務資訊，rollback segment header的活動控制了向rollbak segment寫入被修改的資料。rollback segment header是經常被修改的資料庫塊，因此它應該被長時間留在buffer cache中，為了避免在transaction table產生競爭導致效能下降，應有多個rollback segment或應儘量使用oracle server 自動管理的rollback segment。

2、診斷rollback segment header的競爭
如果rollback segment 由手工管理，下列措施診斷rollback segment header的競爭
SELECT class,count FROM v$waitstat WHERE class LIKE '%undo%' ;
SELECT Sum(Value) sum FROM v$sysstat WHERE NAME IN ('db block gets','consistent gets');
任何型別的等待次數（count）與總請求數(sum)的比率，不能超過1％。
或
select sum(waits)*100/sum(gets) "Ratio", sum(waits) "Waits", sum(gets) "Gets" from v$rollstat;
waits的彙總數與gets的彙總數的比率應低於1％，如果超過1％，應建立更多的rollback segment。

下列欄位數值如果大於0，則表明在rollback segment header上存在競爭：
A、v$rollstat 中的waits
B、v$waitstat中的undo header行
C、v$system_event中的undo segment tx slot事件

3、消耗更少的rollback segment
1）如果是刪除表裡所有的資料，儘可能使用trauncate而不是delete。
2）在應用中允許使用者有規律的提交，儘可能不用長事務。
3）• Import
– Set COMMIT = Y
– Size the set of rows with BUFFER
• Export: Set CONSISTENT=N
• SQL*Loader: Set the COMMIT intervals with ROWS

4、小回滾段可能出現的問題
A、事務由於缺少回滾空間失敗
B、由於下列原因導致的“Snapshot too old”問題：
Block裡的事務列表被重新整理，block裡的SCN比列表Interested Transaction List（ITL）裡起始事務的SCN更新；
Rollback segment header裡的Transaction slot被重用；
回滾資料已經被重寫；

5、9i的自動回滾管理
Undo_managment指定了回滾空間的管理方式：Auto：自動管理；Manual：手工管理回滾段。
Undo_retention指定了回滾資料的保留期限；
Undo_tablespace指定了被使用的回滾表空間；

自動管理的表空間可以在常見資料庫的時候建立，也可以單獨建立。回滾表空間可以相互轉換（switch），但在某一時刻只能有一個回滾表空間處於活動狀態。回滾表空間處於非活動狀態時可以刪除，如果有對處於被刪除回滾表空間裡的已提交事務的查詢時，oracle會返回一個錯誤。

估計undo tablespace大小的公式：
Undo space = (undo_retention * (undo blocks per second * db_block_size)) + db_block_size;

可以使用下列的sql設定undo_retention和undo tablespace：
select (rd*(ups*overhead)+overhead) "bytes" from (select value rd from v$parameter where name ='undo_retention'),(select (sum(undoblks)/sum(((end_time-begin_time)*10800))) ups from v$undostat),(select value overhead from v$parameter where name='db_block_size');

其中：
Rd：undo_retention設定的時間；
Ups：undo blocks per second；
Overhead：rollback segment header；

六、Lock Contention

1、概念
DML事務使用row-level locks,查詢不會鎖定資料。鎖有兩種模式：exlusive、share。
鎖的型別：
• DML or data locks:
– Table-level locks（TM）
– 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、可能引起lock contention的原因
不必要的高層次的鎖；
長時間執行的transaction；
未提交的修改；
其他產品施加的高層次的鎖。

解決lock contention的方法：鎖的擁有者提交或回滾事務；殺死使用者會話。

3、死鎖
Oracle自動檢測和解決死鎖，方法是透過回滾引起死鎖的語句（statement），但是這條語句對應的transaction並沒有回滾，因此當收到死鎖的錯誤資訊後，應該去回滾改transaction的剩餘部分。

七、應用最佳化

1、概念
為了提高效能，可以使用下列資料訪問方法：
A、Clusters
B、Indexes
-B-tree（normal or reverse key）
-bitmap
-function-based
C、Index-organized tables
D、Materialized views

索引的層次越多，效率越低，如果索引中含有許多已刪除的行，這個索引也會變得低效，如果索引資料的15％已經被刪除，應該考慮重建索引。

2、應用問題
A、使用可宣告的約束而不是透過程式碼限制
B、程式碼共享
C、使用繫結變數而不是文字來最佳化共享sql
D、調整cursor_sharing的值（EXACT/SIMILAR/FORCE）

八、提升block的效率

1、避免動態分配的缺陷
建立本地管理的表空間；
合理設定segment的大小；
監控將要擴充套件的segment：
SELECT owner, table_name, blocks, empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < .1;

2、high water mark
記錄在segment header block中，在segment建立的時候設定在segment的起始位置，當記錄被插入的時候以5個block的增量增加，truncate可以重設high water mark的位置，但delete不能。
在full table scan中，oracle會讀取high water mark以下的所有的資料塊，所以high water mark以上的塊也許會浪費儲存空間，但不會降低效能。

可以透過下列方法收回表中high water mark以上的塊：
Alter table_name deallocate unused；
對於high water mark以下的塊：
使用import/export工具：export資料；drop或truncate表；import資料。或者利用alter table tanle_name move命令去移動表的儲存位置（此時需要重建索引）。

3、表統計
用analyize命令生成表統計，然後到dba_table查詢相關資訊。
ANALYZE TABLE ndls.t_wh_shipping_bill COMPUTE STATISTICS;
SELECT num_rows, blocks, empty_blocks as empty,avg_space, chain_cnt, avg_row_len FROM dba_tables WHERE owner ='NDLS' AND table_name='T_WH_SHIPPING_BILL';

Columns Description
NUM_ROWS Number of rows in the table
BLOCKS Number of blocks below the table high-water mark
EMPTY_BLOCKS Number of blocks above the table high-water mark
AVG_SPACE Average free space in bytes in the blocks below high-water mark
AVG_ROW_LEN Average row length, including row overhead
CHAIN_CNT Number of chained or migrated rows in the table

4、block size
透過下列方法可以最小化block的訪問次數：
使用更大的block size；緊密壓縮行；阻止行映象。後兩者存在衝突，越多的行被壓縮在一個block裡，越容易產生映象。Block size 在資料庫建立的時候設定，不能被輕易改變，是讀取資料檔案時最小的IO單元，大小範圍是2K－64K，應該設定成OS塊的整數倍，小於或等於OS IO時能讀取的儲存區域。

較小的block size的優點：極少block競爭；有利於較小的行和隨機訪問。缺點是存在相當高的成本，每個block的行數更少，可能需要讀取更多的index塊。Block size的選擇影響系統的效能，在一個OLTP環境中，較小的block size更合適，而在DSS環境中，適宜選擇較大的block size。

九、應用最佳化

1、概念
為了提高效能，可以使用下列資料訪問方法：
A、Clusters
B、Indexes
-B-tree（normal or reverse key）
-bitmap
-function-based
C、Index-organized tables
D、Materialized views

索引的層次越多，效率越低，如果索引中含有許多已刪除的行，這個索引也會變得低效，如果索引資料的15％已經被刪除，應該考慮重建索引。

2、應用問題
A、使用可宣告的約束而不是透過程式碼限制
B、程式碼共享
C、使用繫結變數而不是文字來最佳化共享sql
D、調整cursor_sharing的值（EXACT/SIMILAR/FORCE）

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