Oracle性能調整的十大要點

    一SGA

    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需要重新解析

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

    ) v$librarycache中reloads/pins的比率應該小於％如果大於％應該增加參數shared_pool_size的值
    Select sum(pins) executionssum(reloads) cache missessum(reloads)/sum(pins) from v$librarycache;

    reloads/pins>%有兩種可能一種是library cache空間不足一種是sql中引用的對象不合法

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

    ）將大的匿名pl/sql代碼塊轉換成小的匿名pl/sql代碼塊調用存儲過程

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

    ）保留大的對象在shared pool中大的對象是造成內存碎片的主要原因為了騰出空間許多小對象需要移出內存從而影響了用戶的性能因此需要將一些常用的大的對象保留在shared pool中下列對象需要保留在shared pool中
    a 經常使用的存儲過程
    b 經常操作的表上的已編譯的觸發器
    c Sequence因為Sequence移出shared pool後可能產生號碼丟失
    查找沒有保存在library cache中的大對象
    Select * from v$db_object_cache where sharable_mem> and type in (PACKAGEPROCEDUREFUNCTIONPACKAGE BODY) and kept=NO;
    將這些對象保存在library cache中
    Execute dbms_shared_poolkeep(package_name);
    對應腳本dbmspoolsql

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

    ）Dictionary cache的 優化
    避免出現Dictionary cache的misses或者misses的數量保持穩定只能通過調整shared_pool_size來間接調整dictionary cache的大小

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

Buffer Cache

    ）granule大小的設置db_cache_size以字節為單位定義了default buffer pool的大小
    如果SGA<Mgranule=M否則granule＝M即需要調整sga的時候以granule為單位增加大小並且sga的大小應該是granule的整數倍

    ) 根據v$db_cache_advice調整buffer cache的大小
    SELECT size_for_estimatebuffers_for_estimateestd_physical_read_factorestd_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<=

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

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

    SELECT NAMEvalue FROM v$sysstat WHERE NAME IN (session logical readsphysical readsphysical reads directphysical reads direct(lob));

    Cache hit ratio=(physical readsphysical reads directphysical reads direct (lob))/session logical reads;

    Select (phyvaluedirvaluelobvalue)/logvalue from v$sysstat log v$sysstat phy v$sysstat dir v$sysstat LOB where logname=session logical reads and phyname=physical reads and dirname=physical reads direct and lobname=physical reads direct (lob);

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

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

其他SGA對象

    ）redo log buffer
    對應的參數是log_buffer缺省值與 OS相關一般是K檢查v$session_wait中是否存在log buffer waitv$sysstat中是否存在redo buffer allocation retries

    A檢查是否存在log buffer wait
    Select * from v$session_wait where event=log buffer wait ;
    如果出現等待一是可以增加log buffer的大小也可以通過將log 文件移到訪問速度更快的磁盤來解決

    BSelect namevalue from v$sysstat where name in (redo buffer allocation retriesredo entries)
    Redo buffer allocation retries接近小於redo entries 的％如果一直在增長表明進程已經不得不等待redo buffer的空間如果Redo buffer allocation retries過大增加log_buffer的值

    C檢查日志文件上是否存在磁盤IO競爭現象
    Select eventtotal_waitstime_waitedaverage_wait from v$system_event where event like log file switch completion%;
    如果存在競爭可以考慮將log文件轉移到獨立的更快的存儲設備上或增大log文件

    D檢查點的設置是否合理
    檢查alertlog文件中是否存在checkpoint not complete
    Select eventtotal_waitstime_waitedaverage_wait from v$system_event where event like log file switch (check%;

    如果存在等待調整log_checkpoint_intervallog_checkpoint_timeout的設置

    E檢查log archiver的工作
    Select eventtotal_waitstime_waitedaverage_wait from v$system_event where event like log file switch (arch%;

    如果存在等待檢查保存歸檔日志的存儲設備是否已滿增加日志文件組調整log_archiver_max_processes

    FDB_block_checksum=true因此增加了性能負擔（為了保證數據的一致性oracle的寫數據的時候加一個checksum在block上在讀數據的時候對checksum進行驗證）

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

二數據庫配置和IO問題

    降低磁盤的IO
    分散磁盤的IO
    表空間使用本地管理
    將文件分散到不同的設備上
    ）將數據文件與日志文件分開
    ）減少與服務器無關的磁盤IO
    ）評估裸設備的使用
    ）分割表數據

    表空間的使用

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

    下列數據庫對象應該有單獨的表空間
    數據字典回滾段索引臨時段表大對象

    檢查IO統計數據
    Select phyrdsphywrtsdname from v$datafile dv$filestat f where ffile#=dfile# order by dname;
    檢查最有可能引起磁盤IO瓶頸的文件

    分割文件
    可以通過RAID和手工進行
    Alter table table_name allocate extent (datafile fiile_name size M);
    但手工操作工作量很大

    優化全表掃描操作

    ）檢查有多少全表發生
    Select namevalue 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相關）

    ）查看full table scan操作
    Select sidserial#opnametargetto_char(start_timeHH:MI:SS) start(sofar/totalwork)* percent_complete from v$session_longops;
    通過v$session_longops裡的sql_hash_value與v$sqltext關聯可以查詢導致full table scan的sql
    Checkpoint
    Checkpoint進行的操作DBWn進行IO操作CKPT更新數據文件頭和控制文件
    經常進行Checkpoint的結果減少恢復所需的時間降低了系統運行時的性能

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

    調節Checkpoint次數的辦法
    增大日志文件增加日志組以增加覆蓋的時間間隔

    日志文件

    建立大小合適的日志文件以最小化競爭
    提供足夠的日志文件組以消除等待現象
    將日志文件存放在獨立的能快速訪問的存儲設備上（日志文件可以創建在裸設備上）日志文件以組的方式組織管理每個組裡的日志文件的內容完全相同

    歸檔日志文件
    如果選擇歸檔模式必須要有兩個或兩個以後的日志組當從一個組切換到另一個組時會引起兩種操作DBWn進行Checkpoint一個日志文件進行歸檔

    歸檔有時候會報錯
    ARCBeginning to archive log# seq#
    Current log# seq# ……
    ARC: Failed to archive log# seq#
    ARCH: Completed to archiving log# seq#
    建議init參數修改如下
    log_archive_max_processes=
    #log_archive_dest = /u/prodarch
    log_archive_dest_ = location=/u/prodarch MANDATORY
    log_archive_dest_state_ = enable

    log_archive_dest_ = location=/u/prodarch OPTIONAL reopen= （或其它目錄）
    log_archive_dest_state_ = enable
    log_archive_min_succeed_dest=

    log_archive_dest_state_ = DEFER
    log_archive_dest_state_ = DEFER
    log_archive_dest_state_ = DEFER

三優化排序操作

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

     PGAprogram global area為單個進程（服務器進程或後台進程）保存數據和控制信息的內存區域PGA與進程一一對應且只能被起對應的進程讀寫PGA在用戶登錄數據庫創建會話的時候建立

    有關排序空間自動管理的兩個參數
    Pga_aggregate_target: MG等於分配給oracle instance的所有內存減去SGA後的大小
    Workarea_size_policy: auto/manual只有Pga_aggregate_target已定義時才能設置為auto
    這兩個參數會取代所有的*_area_size參數

    措施

    盡可能避免排序盡可能在內存中排序分配合適的臨時空間以減少空間分配調用

    需要進行排序的操作
    A創建索引
    B涉及到索引維護的並行插入
    Corder by或者group by（盡可能對索引字段排序）
    DDistinct
    Eunion/intersect/minus
    Fsortmerge join
    Ganalyze命令（僅可能使用estamate而不是compute）

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

    Sort（disk）/ Sort（memory）<%如果超過％增加sort_area_size的值
    SELECT diskValue diskmemValue mem(diskValue/memValue)* ratio FROM v$sysstat diskv$sysstat mem WHERE memNAME=sorts (memory) AND diskNAME=sorts (disk);

    監控臨時表空間的使用情況及其配置
    Select tablespace_namecurrent_userstotal_extentsused_extentsextent_hitsmax_used_blocksmax_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

    臨時表空間的配置
    Ainitial/next設置為sort_area_size的整數倍允許額外的一個block作為segment的header
    Bpctincrease=
    C基於不同的排序需要建立多個臨時表空間
    D將臨時表空間文件分散到多個磁盤上
 

四診斷latch競爭

    概念
    Latch是簡單的低層次的序列化技術用以保護SGA中的共享數據結構比如並發用戶列表和buffer cache裡的blocks信息一個服務器進程或後台進程在開始操作或尋找一個共享數據結構之前必須獲得對應的latch在完成以後釋放latch不必對latch本身進行優化如果latch存在競爭表明SGA的一部分正在經歷不正常的資源使用

    ）Latch的作用
    A序列化訪問保護SGA中的共享數據結構保護共享內存的分配
    B序列化執行避免同時執行某些關鍵代碼避免互相干擾

    ）Latch請求的兩種類型
    Awillingtowait請求的進程經過短時間的等待後再次發出請求直到獲得latch
    Bimmediate如果沒有獲得latch請求的進程不等待而是繼續處理其他指令
    檢查Latch競爭
    檢查latch free是不是主要的wait event
    Select * from v$system_event order by time_waited;

    檢查latch的使用情況
    Select * from v$latch:
    與willingtowait請求有關的列getsmissessleepswait_timecwait_timespin_gets
    與immediate請求有關的列immediate_getsimmediate_misses

    Gets: number of successful willingtowait requests for a latch;
    Misses: number of times an initial wilingtowait request was unsuccessful;
    Sleeps: number of times a process waited after an initial willingtowait request;
    Wait_time: number of milliseconds waited after willingtowait request;
    Cwait_time: a measure of the cumulative wait time including the time spent spinning and sleepingthe 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= order by upper(sql_text);
    B共享sql被重新編譯考慮library cache的大小是否需要調整
    SELECT sql_textparse_callsexecutions FROM v$sqlarea where parse_calls>;
    Clibrary cache不夠大

五Rollback(undo) Segment 優化

    概念
    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

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

    下列字段數值如果大於則表明在rollback segment header上存在競爭
    Av$rollstat 中的waits
    Bv$waitstat中的undo header行
    Cv$system_event中的undo segment tx slot事件

    消耗更少的rollback segment
    ）如果是刪除表裡所有的數據盡可能使用trauncate而不是delete
    ）在應用中允許用戶有規律的提交盡可能不用長事務
    ）&#; Import
    – Set COMMIT = Y
    – Size the set of rows with BUFFER
    &#; Export: Set CONSISTENT=N
    &#; SQL*Loader: Set the COMMIT intervals with ROWS

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

    i的自動回滾管理
    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_timebegin_time)*))) ups from v$undostat)(select value overhead from v$parameter where name=db_block_size);

    其中
    Rdundo_retention設置的時間
    Upsundo blocks per second
    Overheadrollback segment header

六Lock Contention

    概念
    DML事務使用rowlevel locks查詢不會鎖定數據鎖有兩種模式exlusiveshare
    鎖的類型
    &#; DML or data locks:
    – Tablelevel locks（TM）
    – Rowlevel locks（TX）
    &#; DDL or dictionary locks
    一個transaction至少獲得兩個鎖一個共享的表鎖一個專有的行鎖Oracle server將所有的鎖維護在一個隊列裡隊列跟蹤了等待鎖的用戶申請鎖的類型以及用戶的順序信息
    Lock在下列情況會釋放commitrollbackterminated（此時由pmon清理locks）Quiesced database一個數據庫如果除了sys和system之外沒有其他活動session這個數據庫即處於quiesced狀態活動session是指這個session當前處於一個transaction中或一個查詢中一個fetch中或正占有某種共享資源

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

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

    死鎖
    Oracle自動檢測和解決死鎖方法是通過回滾引起死鎖的語句（statement）但是這條語句對應的transaction並沒有回滾因此當收到死鎖的錯誤信息後應該去回滾改transaction的剩余部分

七應用優化

    概念
    為了提高性能可以使用下列數據訪問方法
    AClusters
    BIndexes
    Btree（normal or reverse key）
    bitmap
    functionbased
    CIndexorganized tables
    DMaterialized views

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

    應用問題
    A使用可聲明的約束而不是通過代碼限制
    B代碼共享
    C使用綁定變量而不是文字來優化共享sql
    D調整cursor_sharing的值（EXACT/SIMILAR/FORCE）

    八提升block的效率

    避免動態分配的缺陷
    創建本地管理的表空間
    合理設置segment的大小
    監控將要擴展的segment
    SELECT owner table_name blocks empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < ;

    high water mark
    記錄在segment header block中在segment創建的時候設定在segment的起始位置當記錄被插入的時候以個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命令去移動表的存儲位置（此時需要重建索引）

    表統計
    用analyize命令生成表統計然後到dba_table查詢相關信息
    ANALYZE TABLE ndlst_wh_shipping_bill COMPUTE STATISTICS;
    SELECT num_rows blocks empty_blocks as emptyavg_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 highwater mark
    EMPTY_BLOCKS Number of blocks above the table highwater mark
    AVG_SPACE Average free space in bytes in the blocks below highwater mark
    AVG_ROW_LEN Average row length including row overhead
    CHAIN_CNT Number of chained or migrated rows in the table

    block size
    通過下列方法可以最小化block的訪問次數
    使用更大的block size緊密壓縮行阻止行鏡像後兩者存在沖突越多的行被壓縮在一個block裡越容易產生鏡像Block size 在數據庫創建的時候設定不能被輕易改變是讀取數據文件時最小的IO單元大小范圍是K－K應該設置成OS塊的整數倍小於或等於OS IO時能讀取的存儲區域

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

九應用優化

    概念
    為了提高性能可以使用下列數據訪問方法
    AClusters
    BIndexes
    Btree（normal or reverse key）
    bitmap
    functionbased
    CIndexorganized tables
    DMaterialized views

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

    應用問題
    A使用可聲明的約束而不是通過代碼限制
    B代碼共享
    C使用綁定變量而不是文字來優化共享sql
    D調整cursor_sharing的值（EXACT/SIMILAR/FORCE）

    八提升block的效率

    避免動態分配的缺陷
    創建本地管理的表空間
    合理設置segment的大小
    監控將要擴展的segment
    SELECT owner table_name blocks empty_blocks FROM dba_tables WHERE empty_blocks / (blocks+empty_blocks) < ;

    high water mark
    記錄在segment header block中在segment創建的時候設定在segment的起始位置當記錄被插入的時候以個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命令去移動表的存儲位置（此時需要重建索引）

    表統計
    用analyize命令生成表統計然後到dba_table查詢相關信息
    ANALYZE TABLE ndlst_wh_shipping_bill COMPUTE STATISTICS;
    SELECT num_rows blocks empty_blocks as emptyavg_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 highwater mark
    EMPTY_BLOCKS Number of blocks above the table highwater mark
    AVG_SPACE Average free space in bytes in the blocks below highwater mark
    AVG_ROW_LEN Average row length including row overhead
    CHAIN_CNT Number of chained or migrated rows in the table

    block size
    通過下列方法可以最小化block的訪問次數
    使用更大的block size緊密壓縮行阻止行鏡像後兩者存在沖突越多的行被壓縮在一個block裡越容易產生鏡像Block size 在數據庫創建的時候設定不能被輕易改變是讀取數據文件時最小的IO單元大小范圍是K－K應該設置成OS塊的整數倍小於或等於OS IO時能讀取的存儲區域

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

    ）PCTFREEPCTUSED使你能控制一個segment裡所有數據塊裡free space的使用
    PCTFREE一個數據塊保留的用於塊裡已有記錄的可能更新的自由空間占block size的最小比例
    PCTUSED在新記錄被插入block裡之前這個block可以用於存儲行數據和其他信息的空間所占的最小比率

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

    ）PCTFREEPCTUSED的設定
    &#; PCTFREE
    – Default
    – Zero if no UPDATE activity
    – PCTFREE = × upd / (average row length)
    &#; PCTUSED
    – Default
    – Set if rows deleted
    – PCTUSED = – PCTFREE – × rows × (average row length) / blocksize
    其中upd the average amount added by updates in bytesThis 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

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

    Migration和Chaining

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

    ）檢測migration和chaining
    Analyize table table_name compute statistics
    Select num_rowschain_cnt from dba_tables where table_name=;
    查詢鏡像行
    Analyize table table_name list chained rows
    Select owner_nametable_namehead_rowid from chained_rows where table_name=;
    產生Migration的原因可能是由於PCTFREE設置的太低以至沒有保留足夠的空間用於更新
    可以通過增加PCTFREE的值避免行鏡像產生

    ）消除鏡像行的步驟
    運行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 origrowid = crhead_rowid
    and crtable_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
    使用這個腳本時必須將涉及到的外鍵約束去掉

    索引重組

    在一個不穩定的表上建索引會影響性能一個索引block只有完全空時才能進入free list即使一個索引block裡只含有一個條目它也必須被維護因此索引需要進行階段性的重建

    ）檢查索引是否需要重組
    A收集一個index的使用統計
    ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;
    B查看收集的統計數據
    SELECT NAME(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 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如果浪費超過％則索引需要重建
    ALTER INDEX acct_no_idx REBUILD;
    D或者對索引進行整理
    Alter index acct_no_idx coalesce;

    ）標記未使用的索引
    A 開始監測索引的使用
    Alter index hremp_name_ix monitoring usage;
    B 停止監測索引的使用
    Alter index hremp_name_ix nomonitoring usage;
    C 查詢索引的使用情況
    Select index_nameused from v$object_usage;
    刪除未使用過的索引可以降低DML操作的成本從而提升系統性能

    為了盡可能經濟的利用block應對存在較多空block鏡像行的表進行重建對建立不穩定表上的索引應有規律的進行重建並盡可能創建本地管理的表空間
九SQL優化

    優化器模式

    Oraclei有兩種優化器模式可以選擇
    &#; Rulebased:
    – Uses a ranking system
    – Syntax and data dictionary–driven
    &#; Costbased:
    – Chooses leastcost path
    – Statisticsdriven
    Rulebased模式滿足向後兼容而Costbased模式中的成本大部分來自於邏輯讀的次數推薦使用Costbased模式

    固定optimizer plan

    ）概念
    對於每一個查詢optimizer都會准備一個定義了操作執行順序和方法的操作樹（執行計劃）oracle server根據這個執行計劃執行語句通過固定執行計劃可以強制應用通過一種理想的方式訪問數據並且一個穩定的執行計劃可以經歷數據庫的變化而保持不變固定執行計劃通過創建stored outline實現outline使用costbased的optimizer因為其由一系列的hints組成
    執行計劃的固定依賴於當判定一個查詢是否存在stored outline時查詢語句是否完全一致與判定shared pool裡一個執行計劃是否可以重用時的匹配方式是一致的
    Outline被保存在outln schema中

    ） 創建stored outline
    alter session set CREATE_STORED_OUTLINES = train;
    create or replace OUTLINE co_cl_join
    FOR CATEGORY train ON
    select cocrs_id
    from courses coclasses cl
    where cocrs_id = clcrs_id;

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

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

    當為一個查詢尋找stored outline時查詢語句與stored outline裡的語句必須完全一致在outline裡的hints也必須在查詢語句中出現

    private outline

    Private outline是當前保存的stored outline的副本可以被編輯而不影響正在運行的系統一個private outline只能被當前session看到它的數據被保存在當前被解析的schema裡知道顯示的將其公布
    當USE_PRIVATE_OUTLINES=TRUE時一個已有outline的sql被提交時optimizer會檢查是否存在private outline如果不存在optimizer就不使用optimizer編譯語句而不會去檢查公布的stored outline

    在sql中使用hints
    Create index gen_idx on customers(cust_gender);
    Select /*+ index(customers gen_idx)*/
    Cust_last_namecust_street_addresscust_postal_code
    From shcustomers where upper(gender)=M;

    EXPLAIN PLAN
    可以不通過tracing需要建立plan_table表
    Sql>@oracle_home/rdbms/admin/utlxplan;
    建立explain plan
    Explain plan for select last_name from hremp;
    查詢plan_table中的explain plan可以直接查詢也可以通過腳本utlxplxsql(隱藏並行查詢信息)utlxplpsql（顯示並行查詢信息）查詢

    管理統計信息
    利用analyize命令收集或刪除信息
    參數
    Compute統計精確的數據
    Estimate估計的統計數據

    各類統計數據的位置
    表dba_tables;
    索引dba_indexes;
    列user_tab_col_statistics;
    柱狀圖（histogram）詳細的描述了一個特定列中數據的分布情況可以通過analyize table for columns 命令創建保存在dba_histogram/dba_tab_histograms中

十操作系統優化和使用資源管理器

    操作系統優化
    ）概念
    操作系統優化時應該考慮的因素有內存的使用Cpu的使用IO級別網絡流量各個因素互相影響正確的優化次序是內存IOCPU
    操作系統使用了虛擬內存的概念虛擬內存使每個應用感覺自己是使用內存的唯一的應用每個應用都看到地址從開始的單獨的一塊內存虛擬內存被分成K或K的page操作系統通過MMU（memory management unit）將這些page與物理內存映射起來這個映射關系通過page table控制
    Raw device是沒有文件結構或目錄結構的磁盤或磁盤分區由於它忽略了操作系統緩存在某些情況下可以顯著提升性能但是在windows NT下由於操作系統IO操作本身不使用文件系統緩存所以raw device不能顯示性能上的優點

    ）Guideline
    CPU的最高使用率％
    OS/USER進程數之比/
    各個CPU的負載應該大致均衡

    ）服務器安全性檢查
    A檢查UNIX系統用戶口令
    檢查/etc/passwd/etc/shadowUNIX密碼采用了shadow機制安全性能高
    建議參考UNIX命令passwd修改/etc/default/passwd文件的某些設置如MAXWEEKSMINWEEKSPASSLENGTH使口令修改更加合理化
    建議定期更改UNIX系統的如下用戶口令
    rootoraprodapplprodappprod

    B檢查 Remote Login
    啟動了rlogin服務器數據庫a數據庫b數據庫c終端consoleconsoleconsole及T形成相互非常信任的關系用戶只要擁有一個服務器的超級權限就可以rlogin到rhosts指明的任一主機而無需要口令
    建議非常不安全參考UNIX命令rlogin和/目錄下的文件rhosts在正式環境服務器和測試環境服務器之間不要建立這種遠程信任的機制

    C檢查FTP服務
    檢查可以FTP到服務器的用戶（/etc/ftpusers）注釋了root用戶就是說用戶可以用root權限FTP到服務器上權限太大
    建議把這種權力取消將/etc/ftpusers中root的注釋符號（#）去掉在列表中添加oraprodapplprodappprod等用戶使之不能FTP服務器必要時（如上傳PATCH時）再打開applprod的FTP權限
    D建議UNIX系統管理員定期檢查/var/adm下的messagessulog/etc/nf 等信息檢查是否有非法用戶登陸UNIX
    建議與UNIX工程師探討更好的監控方式

    ）數據庫與應用產品安全性檢查
    A建議修改oracle用戶根目錄下的profile文件修改該文件的權限為即使得用戶登陸時並不執行和數據庫或應用相關的環境變量增加安全性
    B檢查數據庫DBA權限的用戶密碼和應用系統用戶密碼SYSTEMAPPS密碼都已經改變SYS密碼還是初始安裝密碼Change_on_install
    建議立即修改SYS用戶密碼定期更改APPSSYSTEMSYS密碼
    C定期檢查並清除$ORACLE_HOME/admin/bdump目錄下的alert_PRODlog文件和後台進程trace文件定期清除$ORACLE_HOME/admin/udump目錄下的trc文件
    D建議給應用產品登陸的用戶設置口令過期限制如口令訪問次數限制或時間（天數）限制
    建議不要給使用應用產品的用戶共享用戶名和口令每個用戶分配一個應用產品用戶名
    建議對有應用系統管理員權限的用戶登記不適合有系統管理員權限的用戶要把權限回收統一管理
    E定期檢查並清除與Apache Server有關的log文件目錄為:
    /u/prodora/iAS/Apache/Apache/logs/acccess_logerror_log
    /u/prodora/iAS/Apache/Jserv/logs/jservlogmod_jservlog
    F定期檢查清除listenertnsname的log文件文件存放在:
    /u/prodora//network/admin/apps_prodlog
    /u/proddb//network/admin/prodlog
    /u/proddb//network/log/listenerlogsqlnetlog…
    G數據庫控制文件做多個鏡像放在多個磁盤位置提高安全性

    ）網絡安全性檢查
    檢查$ORACLE_HOME/dbs/initPRODora文件
    #remote_login_passwordfile=EXCLUSIVE
    設置為REMOTE_LOGIN_PASSWORDFILE=NONE不允許遠程客戶用INTERNAL方式登陸
    資源管理器（Resource Manager）
    通過資源管理器可以管理混合工作負載控制系統性能數據庫資源管理器包括
    &#; Resource plans包括 resource plan directives 它指定了被分配到各個 resource consumer group的資源
    &#; Resource consumer groups定義了具有類似資源使用需求的一組用戶
    &#; Resource plan directives包括下列內容:為consumer groups 或 subplans 指定resource plans在各個 consumer groups 或資源計劃的subplans 分配資源

From:http://tw.wingwit.com/Article/program/Oracle/201311/17520.html