oracle performance tunning

1、Shared pool tunning
Shared pool的最佳化應該放在優先考慮，因為一個cache miss在shared pool中發生比在data buffer中發生導致的成本更高，由於dictionary資料一般比library cache中的資料在記憶體中儲存的時間長，所以關鍵是library cache的最佳化。[@more@]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;
C、library cache不夠大。
五、Rollback(undo) Segment 最佳化
1、概念
Transaction以輪循的方式使用rollback segment裡的extent，當前所在的extent滿時就移動到下一個extent。可能有多個transaction同時向同一個extent寫資料，但一個rollback segment block中只能儲存一個transaction的資料。

Oracle 在每個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指定了被使用的回滾表空間；

Oracle自動管理的表空間可以在常見資料庫的時候建立，也可以單獨建立。回滾表空間可以相互轉換（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。
5、PCTFREE、PCTUSED
1）PCTFREE、PCTUSED使你能控制一個segment裡所有資料塊裡free space的使用。
PCTFREE：一個資料塊保留的用於塊裡已有記錄的可能更新的自由空間佔block size的最小比例。
PCTUSED：在新記錄被插入block裡之前這個block可以用於儲存行資料和其他資訊的空間所佔的最小比率。

2）這兩個引數的使用
如果建立表的時候指定pctfree＝20％，oracle會在這個表的data segment的每個block都保留20％的空間用於已有記錄的更新。Block的已使用空間上升到整個block size的80％時，這個block將移出free list；在提交了delete、update之後，oracle server處理這條語句並檢查對應block的已使用空間是否低於PCTUSED，如果是，則這個block放進free list。

3）PCTFREE、PCTUSED的設定
• PCTFREE
– Default 10
– Zero if no UPDATE activity
– PCTFREE = 100 × upd / (average row length)
• PCTUSED
– Default 40
– Set if rows deleted
– PCTUSED = 100 – PCTFREE – 100 × rows × (average row length) / blocksize
其中，upd ： the average amount added by updates, in bytes。This is determined by subtracting the average row length of intercurrent average row length;
average row length：在執行了analyize命令之後，這個值可以從dba_tables中的avg_row_len列中獲得。
rows ： the number of rows to be deleted before free list maintenance occurs。

4）Delete、update可以增加block的自由空間，但是釋放出來的空間有可能是不連續的，oracle在下列情況下會對碎片進行整理：一個block有足夠的自由空間容納row piece，但是由於每個碎片都較小以至這個row piece不能存放在一個連續的section中。
6、Migration和Chaining


1）如果一行的資料太大以至一個單獨的block容納不下，會產生兩種現象：
A、Chaining：行資料太大以至一個空block容納不下，oracle會將這一行的資料存放在一個或多個block 組成的block chain中，insert、update都可能導致這個問題，在某些情況下row chaining是不能避免的。
B、Migration：一次update操作可能導致行資料增大，以至它所在的block容納不下，oracle server會去尋找一個有足夠自由空間容納整行資料的block，如果這樣的block存在，oracle server把整行移到新的block，在原位置儲存一個指向新存放位置的映象行，映象行的rowid和原來的rowid一致。
Chaining、Migration的弊端：insert、update的效能降低，索引查詢增加了IO次數。

2）檢測migration和chaining：
Analyize table table_name compute statistics；
Select num_rows,chain_cnt from dba_tables where table_name=’...’;
查詢映象行：
Analyize table table_name list chained rows；
Select owner_name,table_name,head_rowid from chained_rows where table_name=’...’;
產生Migration的原因可能是由於PCTFREE設定的太低以至沒有保留足夠的空間用於更新。
可以透過增加PCTFREE的值避免行映象產生。

3）消除映象行的步驟：
執行analyize table ... list chained rows;
複製映象行到另一個表tmp；
從源表中刪除這些行；
從tmp中將這些行插回到源表中。
指令碼：
/* Get the name of the table with migrated rows */
accept table_name prompt ’Enter the name of the table with migrated rows: ’
/* Clean up from last execution */
set echo off
drop table migrated_rows;
drop table chained_rows;
/* Create the CHAINED_ROWS table */
@?/rdbms/admin/utlchain
set echo on
spool fix_mig
/* List the chained & migrated rows */
analyze table &table_name list chained rows;
/* Copy the chained/migrated rows to another table */
create table migrated_rows as
select orig.* from &table_name orig, chained_rows cr
where orig.rowid = cr.head_rowid
and cr.table_name = upper(’&table_name’);
/* Delete the chained/migrated rows from the original table */
delete from &table_name
where rowid in ( select head_rowid from chained_rows );
/* Copy the chained/migrated rows back into the original table */
insert into &table_name select * from migrated_rows;
spool off
使用這個指令碼時，必須將涉及到的外來鍵約束去掉。
7、索引重組
在一個不穩定的表上建索引會影響效能，一個索引block只有完全空時才能進入free list，即使一個索引block裡只含有一個條目，它也必須被維護，因此索引需要進行階段性的重建。

1）檢查索引是否需要重組
A、收集一個index的使用統計
ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;
B、檢視收集的統計資料
SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;

Column Description
LF_ROWS Number of values currently in the index
LF_ROWS_LEN Sum in bytes of the length of all values
DEL_LF_ROWS Number of values deleted from the index
DEL_LF_ROWS_LEN Length of all deleted values

C、如果浪費超過20％則索引需要重建
ALTER INDEX acct_no_idx REBUILD;
D、或者對索引進行整理
Alter index acct_no_idx coalesce;

2）標記未使用的索引
A、 開始監測索引的使用
Alter index hr.emp_name_ix monitoring usage;
B、 停止監測索引的使用
Alter index hr.emp_name_ix nomonitoring usage;
C、 查詢索引的使用情況
Select index_name,used from v$object_usage;
刪除未使用過的索引，可以降低DML操作的成本，從而提升系統效能。

為了儘可能經濟的利用block，應對存在較多空block、映象行的表進行重建，對建立不穩定表上的索引應有規律的進行重建，並儘可能建立本地管理的表空間。
九、SQL最佳化
1、最佳化器模式
Oracle9i有兩種最佳化器模式可以選擇：
• Rule-based:
– Uses a ranking system
– Syntax- and data dictionary–driven
• Cost-based:
– Chooses least-cost path
– Statistics-driven
Rule-based模式滿足向後相容，而Cost-based模式中的成本大部分來自於邏輯讀的次數，推薦使用Cost-based模式。
2、固定optimizer plan
1）概念
對於每一個查詢，optimizer都會準備一個定義了操作執行順序和方法的操作樹（執行計劃），oracle server根據這個執行計劃執行語句。透過固定執行計劃，可以強制應用透過一種理想的方式訪問資料，並且一個穩定的執行計劃可以經歷資料庫的變化而保持不變。固定執行計劃透過建立stored outline實現，outline使用cost-based的optimizer，因為其由一系列的hints組成。
執行計劃的固定依賴於當判定一個查詢是否存在stored outline時查詢語句是否完全一致，與判定shared pool裡一個執行計劃是否可以重用時的匹配方式是一致的。
Outline被儲存在outln schema中。

2） 建立stored outline
alter session set CREATE_STORED_OUTLINES = train;
create or replace OUTLINE co_cl_join
FOR CATEGORY train ON
select co.crs_id, ...
from courses co，classes cl
where co.crs_id = cl.crs_id;

stored outline透過category組織，相同的sql語句可以在多個category同時擁有stored outline，如果categoey沒有指定，預設是default category。
當CREATE_STORED_OUTLINES等於true或category名時，oracle會為所有被執行的sql語句建立stored outline，也可以透過create outline手工建立。

3） 使用stored outline
將USE_STORED_OUTLINES設定為true或category名。
alter session set USE_STORED_OUTLINES = train;

當為一個查詢尋找stored outline時，查詢語句與stored outline裡的語句必須完全一致，在outline裡的hints也必須在查詢語句中出現。
3、private outline
Private outline是當前儲存的stored outline的副本，可以被編輯而不影響正在執行的系統，一個private outline只能被當前session看到，它的資料被儲存在當前被解析的schema裡。，知道顯示的將其公佈。
當USE_PRIVATE_OUTLINES=TRUE時，一個已有outline的sql被提交時，optimizer會檢查是否存在private outline，如果不存在，optimizer就不使用optimizer編譯語句，而不會去檢查公佈的stored outline。
4、在sql中使用hints
Create index gen_idx on customers(cust_gender);
Select /*+ index(customers gen_idx)*/
Cust_last_name,cust_street_address,cust_postal_code
From sh.customers where upper(gender)=’M’;
5、EXPLAIN PLAN
可以不透過tracing，需要建立plan_table表：
Sql>@oracle_home/rdbms/admin/utlxplan;
建立explain plan：
Explain plan for select last_name from hr.emp;
查詢plan_table中的explain plan，可以直接查詢，也可以透過指令碼utlxplx.sql(隱藏並行查詢資訊)、utlxplp.sql（顯示並行查詢資訊）查詢。
6、管理統計資訊
利用analyize命令收集或刪除資訊。
引數：
Compute：統計精確的資料；
Estimate：估計的統計資料。

各類統計資料的位置：
表：dba_tables;
索引：dba_indexes;
列：user_tab_col_statistics;
柱狀圖（histogram）詳細的描述了一個特定列中資料的分佈情況，可以透過analyize table ... for columns... 命令建立，儲存在dba_histogram/dba_tab_histograms中。
十、作業系統最佳化和使用資源管理器
1、作業系統最佳化
1）概念
作業系統最佳化時應該考慮的因素有：記憶體的使用；Cpu的使用；IO級別；網路流量。各個因素互相影響，正確的最佳化次序是記憶體、IO、CPU。
作業系統使用了虛擬記憶體的概念，虛擬記憶體使每個應用感覺自己是使用記憶體的唯一的應用，每個應用都看到地址從0開始的單獨的一塊記憶體，虛擬記憶體被分成4K或8K的page，作業系統透過MMU（memory management unit）將這些page與實體記憶體對映起來，這個對映關係透過page table控制。
Raw device是沒有檔案結構或目錄結構的磁碟或磁碟分割槽，由於它忽略了作業系統快取，在某些情況下可以顯著提升效能，但是在windows NT下，由於作業系統IO操作本身不使用檔案系統快取，所以raw device不能顯示效能上的優點。

2）Guideline
CPU的最高使用率：90％；
OS/USER程式數之比：40/60；
各個CPU的負載應該大致均衡。

3）伺服器安全性檢查
A、檢查UNIX系統使用者口令
檢查：/etc/passwd、/etc/shadow，UNIX密碼採用了shadow機制，安全效能高
建議：參考UNIX命令passwd，修改/etc/default/passwd檔案的某些設定如MAXWEEKS、MINWEEKS、PASSLENGTH使口令修改更加合理化。
建議：定期更改UNIX系統的如下使用者口令：
root、oraprod、applprod、appprod

B、檢查 Remote Login
啟動了rlogin，伺服器資料庫a、資料庫b、資料庫c，終端console1、console2、console3及T3形成相互非常信任的關係，使用者只要擁有一個伺服器的超級許可權就可以rlogin到.rhosts指明的任一主機而無需要口令。
建議：非常不安全，參考UNIX命令rlogin和/目錄下的檔案.rhosts。在正式環境伺服器和測試環境伺服器之間不要建立這種遠端信任的機制。

C、檢查FTP服務
檢查可以FTP到伺服器的使用者（/etc/ftpusers），註釋了root使用者，就是說使用者可以用root許可權FTP到伺服器上。許可權太大。
建議：把這種權力取消，將/etc/ftpusers中root的註釋符號（#）去掉，在列表中新增oraprod、applprod、appprod等使用者使之不能FTP伺服器。必要時（如上傳PATCH時）再開啟applprod的FTP許可權。
D、建議：UNIX系統管理員定期檢查/var/adm下的messages、sulog；/etc/syslog.conf 等資訊。檢查是否有非法使用者登陸UNIX。
建議：與UNIX工程師探討更好的監控方式

4）資料庫與應用產品安全性檢查
A、建議：修改oracle使用者根目錄下的.profile檔案，修改該檔案的許可權為500。即使得使用者登陸時並不執行和資料庫或應用相關的環境變數，增加安全性。
B、檢查資料庫DBA許可權的使用者密碼和應用系統使用者密碼：SYSTEM、APPS密碼都已經改變，SYS密碼還是初始安裝密碼Change_on_install
建議：立即修改SYS使用者密碼，定期更改APPS、SYSTEM、SYS密碼。
C、定期檢查並清除$ORACLE_HOME/admin/bdump目錄下的alert_PROD.log檔案和後臺程式trace檔案。定期清除$ORACLE_HOME/admin/udump目錄下的trc檔案。
D、建議：給應用產品登陸的使用者設定口令過期限制，如口令訪問次數限制或時間（天數）限制。
建議：不要給使用應用產品的使用者共享使用者名稱和口令，每個使用者分配一個應用產品使用者名稱。
建議：對有應用系統管理員許可權的使用者登記，不適合有系統管理員許可權的使用者要把許可權回收，統一管理。
E、定期檢查並清除與Apache Server有關的log檔案，目錄為:
/u01/prodora/iAS/Apache/Apache/logs/acccess_log、error_log
/u01/prodora/iAS/Apache/Jserv/logs/jserv.log、mod_jserv.log
F、定期檢查清除listener、tnsname的log檔案，檔案存放在:
/u01/prodora/8.0.6/network/admin/apps_prod.log、
/u01/proddb/8.1.7/network/admin/prod.log
/u01/proddb/8.1.7/network/log/listener.log、sqlnet.log…
G、資料庫控制檔案做多個映象，放在多個磁碟位置，提高安全性。

5）網路安全性檢查
檢查$ORACLE_HOME/dbs/initPROD.ora檔案
#remote_login_passwordfile=EXCLUSIVE
設定為REMOTE_LOGIN_PASSWORDFILE=NONE，不允許遠端客戶用INTERNAL方式登陸。
2、資源管理器（Resource Manager）
透過資源管理器可以管理混合工作負載，控制系統效能。資料庫資源管理器包括：
• Resource plans：包括 resource plan directives, 它指定了被分配到各個 resource consumer group的資源。
• Resource consumer groups：定義了具有類似資源使用需求的一組使用者。
• Resource plan directives：包括下列內容:為consumer groups 或 subplans 指定resource plans；在各個 consumer groups 或資源計劃的subplans 分配資源。