oracle效能調整筆記[zt]

ref: http://www.itpub.net/422928.html

[@more@]

第二章 調整的資訊來源 Alert Log 檔案中記錄的與效能有關的資訊： 1. ORA-01652，不能擴充套件臨時段； 2. ORA-01653，不能擴充套件表段； 3. ORA-01650，不能擴充套件回滾段； 4. ORA-01631，到達了表的最大的Extents； 5. 檢查點未完成（Checkpoint not complete）； 6. 執行緒推進日誌序列（Thread n Advanced to Log Sequence n）； 7. ORA-01555，快照過舊。 後臺跟蹤檔案 自動生成 相關引數：BACKUPGROUND_DUMP_DEST 事件跟蹤檔案 設定後生成 相關引數：EVENT， BACKUPGROUND_DUMP_DEST， USER_DUMP_DEST 使用者跟蹤檔案 相關引數：SQL_TRACE，USER_DUMP_DEST， MAX_DUMP_FILE_SIZE 啟用使用者跟蹤的三種方法： 1. 例項層次的跟蹤：配置引數SQL_TRACE = TRUE | FALSE，然後重啟例項； 2. 使用者層次的自行實置：Alter session set sql_trace = true | false； 3. 使用者層次的DBA設定：Exec dbms_system.set_sql_trace_in_session(SID, SERIAL#, TRUE | FALSE) V$檢視和DBA_檢視 區別： 1. V$檢視通常是單數，DBA檢視通常是複數，例如V$DATAFILE與DBA_DATA_FILES； 2. 當資料庫處於Nomout或者Mount時，許多V$檢視已經是可用的，而DBA檢視必須在資料庫處於Open時才可用； 3. V$檢視查詢出來的資料多小寫，DBA檢視查詢出的資料通常大寫，所以在寫WHERE條件時需特別小心； 4. V$檢視中包含的是自例項啟動以來的動態資料，在資料庫關閉後會消失，查詢V$檢視時必須關注時效性，DBA檢視中包含的是靜態資料； 5. V$檢視的基表是X$表，X$表是存在於記憶體中的虛表，DBA檢視的基表是資料字典表，如SYS.OBJ$， SYS.FILE$等，這兩種基表都很少有文件。 最常用的V$檢視： 表名 描述 V$SGASTAT 顯示SGA元件大小的資訊 V$EVENT_NAME 顯示當前版本的所有等待事件 V$SYSTEM_EVENT 自例項啟動已來的等待事件 V$SESSION_EVENT 目前連線會話的等待事件 V$SESSION_WAIT 目前連線會話正在發生的等待事件 V$STATNAME 顯示當前版本的所有統計名稱 V$SYSSTAT 自例項啟動以來的統計 V$SESSTAT 目前連線會話的統計 V$SESSION 目前連線會話的資訊 V$WAITSTAT 塊競爭的統計 最常用的DBA檢視（有些欄位的值需要分析表或索引後才會有值）： 表名 描述 DBA_TABLES 表的儲存，統計等 DBA_INDEXES 索引的儲存，統計等 INDEX_STATS 索引的深度和鍵值的離散度等 DBA_DATA_FILES 資料檔案的命名，位置，大小 DBA_SEGMENTS 段的相關資訊 DBA_HISTOGRAMS 表，索引的柱狀圖定義資訊 Oracle 提供的指令碼和包 下面提到的指令碼都位於%ORACLE_HOME%RDBMSADMIN目錄下，有些只需執行一次，有些需要每次都執行。 · UTLBSTAT.SQL 和 UTLESTAT.SQL 這些指令碼的功能絕大部分已被STATSPACK所取代。 · STATSPACK ? 配置STATSPACK：在SQL*Plus下執行spcreate.sql，這個指令碼執行時呼叫另外三個指令碼（spcusr.sql，spctab.sql，spcpkg.sql），執行過程中會詢問perfstat方案的密碼，預設表空間和臨時表空間，可以透過這三個指令碼的執行日誌（spcusr.lis，spctab.lis，spcpkg.lis）檢視配置是否成功。 ? perfstat方案的預設表空間需要有足夠的空間來容納目前的物件和將來的快照資料； ? 檢視日誌如果發現有錯誤，執行spdrop.sql來清除已建立的物件，然後重新執行spcreate.sql； ? 配置成功後生成快照有兩種方法：手工執行exec statspack.snap; 執行spauto.sql指令碼，建立job來定時生成快照； ? 生成效能報表：執行spreport.sql指令碼，指令碼執行過程中要求輸入三個引數：開始快照號，結束快照號，報表名；兩次選擇的快照號需要是在例項的一次執行期間的不同點的快照，否則就沒有參考意義； ? statspack報表含有各個方面的效能資料，如何理解這些資料貫穿整本書。

第三章SQL 調整 1.SQL效能測量工具 TKPROF 全名為Trace Kernel Profile，用來格式化跟蹤檔案，是一個命令列工具，基本的格式如下： tkprof tracefile outputfile … 可選引數及說明如下： Option Description EXPLAIN 為每個SQL語句產生執行計劃，指定使用者方案和密碼，使用其中的PLAN_TABLE表 TABLE 當EXPLAIN選項生效時可用，指定替代PLAN_TABLE的表 SYS 啟用該選項時不包含遞迴呼叫 SORT 指定SQL語句的排序方式 RECORD 指定檔名，將SQL語句寫入，排除遞迴的SQL PRINT 限定只輸出指定數量的分析SQL語句 INSERT 指定一個檔名，容納相關的插入SQL語句，將分析的SQL及統計插入TKPROF_TABLE AGGREGATE 指定多使用者共用的SQL語句統計方式 WAITS 指定是否統計跟蹤檔案中的等待事件 SORT選項可分三類，解析選項，執行選項和取資料選項（PRS,EXE,FCH），根椐這三種操作佔用相關資源來排序。 TKPROF針對SQL的統計也分為三類：解析，執行，取數，具體的統計選項見下表： 統計名稱 統計描述 Count Parse,Execute,Fetch發生的次數 CPU 佔用CPU秒 Elapsed 經過秒 Disk 從磁碟讀取的資料塊數 Query 從SGA中讀取的回滾段塊數 Current 從SGA中讀取的一致性資料塊數 Rows 執行INSERT，UPDATE，DELETE影響的行數或者FETCH返回的行數 從TKPROF的統計結果觀察，發現下列特徵的SQL可能需要調優： 1. 消耗過度的CPU資源； 2. 花費太長的時間在Parse，Execute，Fetch階斷； 3. 從磁碟讀資料塊太多而從SGA中讀塊很少； 4. 存取太多的資料塊（從SGA或者磁碟）而返回的行數很少； TOP SQL 這個工具從Enterprise Manager Console中啟動，有與TKPROF類似的統計，用於確定需要調整的SQL語句。 EXPLAIN PLAN 用EXPLAIN PLAN FOR sql語句產生執行計劃，然後再從PLAN_TABLE中查詢； 執行計劃中各步驟的執行順序遵照如下原則： 1. 從上到下； 2. 從右到左（或者說，從縮排最多的到縮排最少的）； 3. 索引操作不參與上面的規則，索引從屬於表操作，先於表操作。 執行計劃的詳盡解釋超出本書範圍，需要參考其它資料； AUTOTRACE AUTOTRACE綜合了TKPROF和EXPLAIN PLAN FOR的功能； 配置AUTOTRACE包括以下步驟： 1. 保證需要AUTOTRACE功能的使用者下有PLAN_TABLE或者有該表的全域性同義詞和那個基表的存取許可權； 2. 建立PLUSTRACE角色，並將該角色許可權賦給相應的使用者，或者相應會話有這個角色對應的許可權； 3. 相關指令碼：%ORACLE_HOME%rdbmsadminutlxplan.sql，%ORACLE_HOME%sqlplusadminplustrce.sql AUTOTRACE的特點： 1. 只能在SQL*Plus的會話中執行； 2. 產生執行計劃前會實際執行SQL，這點與EXPLAIN PLAN FOR不同； 3. 在發出SQL之前，需執行SET AUTOTRACE ON語句。 SET AUTOTRACE語句各選項的意義： 選項 描述 ON 顯示查詢結果，執行計劃，統計 ON STATISTICS 顯示查詢結果，統計 ON EXPLAIN 顯示查詢結果，執行計劃 TRACEONLY 顯示執行計劃，統計 TRACEONLY STATISTICS 顯示統計 OFF 關掉AUTOTRACE STATSPACK STATSPACK中也包含幾種方法排序的SQL，通常只顯示排序值大於一定閥值的SQL，這些閥值可能改變： SQL型別 說明 SQL ordered by Gets 根椐邏輯I/O來排序 SQL ordered by Reads 根椐物理I/O來排序 SQL ordered by Executions 根椐執行次數來排序 SQL (Executions) ordered by Parse Calls 根椐分析次數來排序 第三章SQL 調整 2. 理解ORACLE最佳化器 · 最佳化器的職責是從多行種執行路徑中選擇一種最優的執行路徑； · 有兩種最佳化模式：RBO（基於規則），CBO（基於成本）； · RBO根椐一系列規則來確定執行計劃，不考慮表的大小，欄位的集勢等統計資料，主要用於早期的版本或者新版本的遞迴SQL； · CBO會考查表或索引的統計，然後比較不同執行計劃的IO成本，CPU成本，臨時表空間的需求，得出一個綜合成本最小的執行計劃； · CBO考查的統計包括：表或索引的大小，表或索引的行數，表或索引的資料塊數，錶行的長度，索引欄位的集勢等； · 預設情況下，字典裡並不包含表或索引的統計，這些資料是在分析表，索引，方案或整個資料庫的時候寫進字典裡的。 · 分析可以使用兩個方式：COMPUTE STATISTICS（整體分析）， ESTIMATE STATISTICS（樣本分析，可以用SAMPLE子句指定樣本行數或者比例）； · 可以分析欄位的柱狀圖以指示最佳化器欄位資料的離散分佈狀況，ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name SIZE integer_value(1-254)，預設分析選項下只儲存欄位的最大最小值，最佳化器假設欄位值是均勻分佈的，在某些不均衡的情況下，優估器可能產生效率極低的執行計劃，因此分析柱狀圖顯得相當重要； · 可以用包程式來分析整個方案或者整個資料庫，DBMS_UTILITY， DBMS_STATS； · 在ORACLE9中推薦使用DBMS_STATS，它有以下新特性： v 可以在分析前備份現有的統計，用於當更新統計後CBO效能反而下降的情形下恢復以前的統計； v 樣本分析時隨機取資料塊，而不只是資料行； v 可以在並行模式下收集統計； v DBMS_STATS.GATHER_SCHEMA_STATS可以定期自動收集高變更的表的統計，也可以用來自動收集柱狀圖的統計並自動決定切片數以及哪些欄位需要柱狀圖統計； v DBMS_STATS.GATHER_SYSTEM_STATS可以用來收集系統的CPU和IO負載統計，為CBO決策提供參考，避免系統產生CPU或IO瓶頸； v 可以用於將生產環境的統計轉移到開發環境，這一點對於從開發環境調優生產環境相當重要。 · 統計結果存放在下面一些字典裡面：DBA_TABLES， DBA_INDEXES， DBA_TAB_COL_STATISTICS， DBA_HISTOGRAMS等。 設定最佳化器模式 · 可以在下面三種級別配置最佳化器模式：例項級，會話級，語句級，優先順序從低到高； · 設定最佳化器行為版本差異的9i新引數：OPTIMIZER_FEATURES_ENABLE； · OPTIMIZER_MODE初始化引數確定例項中所有會話預設的最佳化模式，可選值有RULE， CHOOSE（預設值）， FIRST_ROWS， FIRST_ROWS_n， ALL_ROWS（與CHOOSE似乎沒有區別？）； · 會話級的最佳化模式設定用ALTER SESSION SET OPTIMIZER_MODE = mode，可選項同上; · 在SQL語句中內嵌提示/*+ mode */ 可用來指示當前語句的最佳化模式，mode選項可用上面提到的除CHOOSE外的其它四種，另外還有四十多種提示可用； · 在RULE模式下，如果SQL涉及到下面的特性時還是會用CBO：分割槽表和分割槽索引，索引組織表，反向索引，基於函式的索引，點陣圖索引，查詢重寫，物化檢視； · 在非RULE的其它四種模式下，如果涉及到的所有的表或者索引的統計都不存在，使用RBO模式，其它情況下使用CBO； · FIRST_ROWS， FIRST_ROWS_n 最佳化響應時間， ALL_ROWS最佳化吞吐量； 第三章SQL 調整 3. 穩定執行計劃 改善應用程式效能 包括兩個方面：改進執行路徑（穩定執行計劃，物化檢視）和最小化IO（索引，分割槽， 簇）； 穩定執行計劃 · 可以用儲存在OUTLN方案的公共大綱或者儲存在本方案的私有大綱來穩定某些SQL語句的執行計劃，這樣這些語句的執行計劃就不會因為統計資料或者會話最佳化模式的改變而發生改變； · 建立大綱時可以指定類別，預設的類別是DEFAULT； · 建立大綱時，是將SQL當前的執行計劃和SQL文字一起儲存起來； · 啟用大綱有三種方式，SQL文字和大綱完全一致時才會使用大綱中儲存的執行計劃； ? 在引數檔案中加上USE_STORED_OUTLINES=TRUE ? ALTER SYSTEM SET USE_STORED_OUTLINES={TRUE | FALSE | category_name}; ? ALTER SESSION SET USE_STORED_OUTLINES={TRUE | FALSE | category_name}; · 當SQL語句執行時，ORACLE確定執行計劃是按下面的順序進行的，檢查共享池中是否有可用的大綱 à 檢查字典中是否有可用的大綱à 檢查共享池中是否有可共用的已解析SQL à 考查最佳化模式和相關統計產生並選擇最優的執行計劃，前面三種情形下都有現成的執行計劃； · 相關的資料字典檢視有：DBA_OUTLINES， DBA_OUTLINE_HINTS。 第三章SQL 調整 4. 物化檢視 · 物化檢視用資料段儲存預連線，預彙總的查詢資料，物化檢視可以有索引，也可以分割槽，物化檢視主要應用於資料倉儲和決策支援系統； · 建立物化檢視時需考慮以下因素： 1. 確定檢視語句，是何種連線，何種彙總；可以藉助Summary Advisor來確定最佳的物化檢視SQL語句，並檢查已建立物化檢視的使用情況； 2. 確定重新整理方式：NEVER REFRESH（不重新整理），REFREST FAST（藉助物化檢視日誌，只檢查自上次重新整理後改變了的資料來進行重新整理）， REFRESH COMPLETE（先清除，再重灌資料）， REFRESH FORCE（先試圖用FAST方式重新整理，如果失敗再用COMPLETE方式重新整理，這是預設的重新整理方式）； 3. 確定重新整理時機：ON COMMIT（事務提交時重新整理）， ON DEMAND（用DBMS_MVIEW.REFRESH， DBMS_MVIEW.REFRESH_DEPENDENT， DBMS_MVIEW.REFRESH_ALL_MVIEWS來手工重新整理）， By Time（用START WITH 和 NEXT 子句建立的job來定時自動重新整理）； 4. 建立方式：BUILD IMMEDIATE（立即生成資料）， BUILD DEFERRED（下一次重新整理時生新資料）， ON PREBUILD TABLE（不建立新的資料段，用已存在的含有當前物化檢視資料的表來代替）； 5. ENABLE | DISABLE QUERY REWRITE指定是否啟用當前物化檢視用於查詢重寫，啟用該選項時，系統會檢查以保證查詢的可確定性（不允許有如序列數，USER， DATE等不確定的返回值），DISABLE時物化檢視照樣可以被重新整理； · 與物化檢視生效相關的設定 1. 初始化引數JOB_QUEUE_PROCESSES設定大於零，物化的自動重新整理操作需要JOB QUEUE程式來執行； 2. 初始化引數OPTIMIZER_MODE要設成某種CBO最佳化模式； 3. 使用者會話有QUERY_REWRITE（最佳化器能將查詢重寫到本方案物化檢視）或GLOBAL_QUERY_REWRITE（最佳化器能將查詢重寫到其它方案的物化檢視）系統許可權； 4. 初始化引數QUERY_REWRITE_ENABLED 指示最佳化器是否動態重寫查詢來使用物化檢視，這個引數可以在四個級別上進行設定（引數檔案，ALTER SYSTEM， ALTER SESSION， HINTS）； 5. 初始化引數QUERY_REWRITE_INTEGRITY 指示最佳化器在不同的資料一致性情況下決定是否使用物化檢視來重寫查詢，ENFORCED（只有在能確保資料一致的前提下才使用物化檢視）， TRUSTED（資料不一定一致，只要有用維度物件定義的關係存在，就可使用物化檢視）， STALE_TOLERATED（資料不一致，也沒有相關的維度定義時仍可使用物化檢視），這個引數可以在三個級別上進行設定（引數檔案，ALTER SYSTEM， ALTER SESSION）； 第三章 SQL 調整 5.索引 索引 ORACLE 9i 中有六種索引：二叉樹索引，壓縮的二叉樹索引，點陣圖索引，基於函式的索引，反向索引，索引組織表； 二叉樹索引 · 二叉樹索引將索引欄位值和ROWID一起儲存樹狀結構中，適用於只存取表中總記錄的5%以下的查詢； · 出現在SQL的WHERE條件中，集勢高的欄位適於當作索引欄位； · 隨著基表資料的不斷增加，索引塊會不斷分裂以保持二叉樹的平衡，樹的層次（從根結點訪問到葉結點要經過的資料塊數，DBA_INDEXES.BLEVEL）也不斷增加，層次大於4的索引宜重建； · 基表資料刪除時，索引項也隨之刪除，但葉塊上的空間並不能被重用，除非該葉塊上所有的索引項都被刪除，當刪除項佔所有項超過20%時，這個索引也需要重建（ANALYZE INDEX … VALIDATE STRUCTURE;分析索引後檢視index_stats.del_lf_rows_len 和 index_stats.lf_rows_len 可以知道被刪除的索引項佔用的空間和所有葉行佔用的空間）； · 有三種重建索引的方法： 1. 先刪除再重建；這種方法耗費最多的資源，是早期版本的唯一方法； 2. ALTER INDEX … REBUILD; 這種方法高效快速，但需要額外的磁碟空間；用這種方法可以指定許多選項如ONLINE（線上重建可減少鎖爭用）， TABLESPACE（移動段到其它表空間）， COMPUTE STATISTICS（統計）， PARALLEL（並行）， NOLOGGING（儘可能少地產生日誌）； 3. ALTER INDEX … COALESCE; 這種方法快速，無需額外空間，鎖爭用也少，缺點是選項少。 壓縮的二叉樹索引 · 壓縮的二叉樹索引對重複的索引鍵值只儲存一次，後跟所有的有這個鍵值行的ROWID； · 有兩種方法建立： 1. CREATE INDEX … COMPRESS; 2. 先建立普通索引，再更改：CREATE INDEX …; ALTER INDEX … COMPRESS; 點陣圖索引 · 點陣圖索引適於建立在資料相對穩定的表的低集勢欄位上； · 點陣圖索引針對每一個鍵值建立一個二進位制點陣圖，點陣圖中的每位對應表中的一行，1表示這一行是當前鍵值，0表示這一行為其它鍵值； · 與二叉樹索引相比，點陣圖索引佔的空間很少，如果WHERE條件中出現同一表的多個點陣圖索引欄位時，無論是AND或是OR操作，都能用點陣圖合併操作快速地定位到ROWID； · 當基表上有DML操作時，整個點陣圖都會被鎖住，但是，每一個DML操作，點陣圖只需更新一次； · 相關引數： 1. SORT_AREA_SIZE 建立索引排序點陣圖欄位和ROWID時會用到； 2. PGA_AGGREGATE_TARGET 建立點陣圖索引以及點陣圖合併時會用到； 3. CREATE_BITMAP_AREA_SIZE， BITMAP_MERGE_AREA_SIZE 這是8i的兩個與點陣圖建立和合並有關引數，在9i建議不再使用。 基於函式的索引 · 當欄位以函式表示式的形式出現在WHERE條件中，基於這些欄位的索引不會被最佳化器用到，除非是基於這些欄位的函式索引； · 要建立基於函式的索引需要QUERY REWRITE 或 GLOBAL QUERY REWRITE系統許可權，有CREATE ANY INDEX許可權是不行的； · 要想最佳化器使用基於函式的索引，OPTIMIZER_MODE必須設成某種CBO模式，QUERY_REWRITE_ENABLED須設成TRUE（這點從試驗中無法證明，設成FALSE似乎也能用到函式索引）。 反向索引 · 反向索引是建立在索引欄位值的反向值上的，可以使相臨的記錄在索引段裡變得離散，可以減少某些DML操作引發的塊競爭； · 反向索引多建立在用序列號生成的欄位上，這樣的欄位在普通索引中容易形成更多的層（設計過一些試驗，但一直未觀察到這種現象）； · 有兩種產生方法：CREATE INDEX … REVERSE; ALTER INDEX … REVERSE; · 反向索引適於WHERE中的等於或不等於的比較，對於範圍查詢（>,< between）是無能為力的。 索引組織表 · 前面五種索引的基表資料儲存是隨機的，這種表稱為堆表； · 索引組織表的資料儲存在二叉樹索引中，所以，如果透過主鍵來存取資料，索引組織表能更快地返回資料，因為不需要先取索引塊再取資料塊，可以直接從索引中返回資料； · 建立索引組織表時必須指定一個主鍵欄位，用這個作為索引； · 索引組織表上不能建唯一約束，也不能將索引組織表建立在簇上面； · 索引組織表的語法要點： ? ORGANIZATION INDEX 指明當前表是索引組織表； ? PCTTHRESHOD 後跟一個0到50的數，預設值為50，指明容納一行資料可用使塊空間的百分比； ? INCLUDING 後跟一個欄位，如果資料行的長度超過了PCTTHRESHOLD指定的可用空間，從這個欄位之後將資料行分為兩段，後面的部分放入溢位段中； ? OVERFLOW TABLESPACE 指定溢位段所在的表空間； ? MAPPING TABLE 當在索引組織表上建立點陣圖索引時建立關聯的對映表；堆表點陣圖索引的每個位對應到表的一個ROWID，索引組織表的ROWID會隨著索引的分裂而發生改變，如果和堆表一樣處理，點陣圖索引很容易就失效或者維護成本很高，對映表就是為解決這個問題而引入的，對映表存放索引組織表的ROWID和邏輯行間的對應關係，索引點陣圖中的位對應到邏輯行；dba_indexes.pct_direct_access可以用來指示對映表GUESS的效率，這個值大於30時推薦重建點陣圖索引；一個索引組織表只有一個對映表。 · 透過dba_tables.iot_name， dba_tables.iot_type 可以檢視到索引組織表的溢位表段，對映表段； · 索引組織表的相關段的段名都是由系統生成的，這些段名的共同特性是SYS_IOT_XXX_YYY， XXX in （TOP 索引段， OVER 溢位表段， MAP 對映表段），YYY是索引組織表的OBJECT_ID。 標識未使用過的索引 · 索引建立後是否使用是由最佳化器來控制的，某些索引可能不會使用到，這樣的索引不僅加重了DML操作的負擔，也佔用空間；可以用下面的方法找出這樣的索引，然後刪除； ? 找出某個或某些被懷疑的索引； ? ALTER INDEX index_name MONITORING USAGE; ? 在資料庫經歷一定時間的活動後再執行: ALTER INDEX index_name NOMONITORING USAGE; ? 查詢v$object_usage.index_name, v$object_usage.used，可以得知被監控的索引在這段時間內是否被使用到。 第三章 SQL 調整 6.分割槽 · 分割槽表是將大表的資料分成稱為分割槽的許多小的子集，9i提供四種分割槽方法：範圍分割槽，列表分割槽，雜湊分割槽和混合分割槽； · 範圍分割槽是根椐分割槽鍵的不同取值範圍來劃分子集的，關鍵字RANGE， VALUES LESS THAN； · 列表分割槽是根椐分割槽鍵的一些離散的取值來劃分子集的，關鍵字LIST， VALUES； · 雜湊分割槽是應用雜湊演算法將分割槽鍵對應到某個子集中去，關鍵字HASH， PARTITIONS； · 混合分割槽只能有兩層，第一層是範圍分割槽，第二層可以是列表分割槽或者雜湊分割槽； · 範圍分割槽和列表分割槽中，如果插入記錄的分割槽鍵沒有對應的容納分割槽，會產生ORA-14400； · update操作如果會使記錄從一個分割槽遷移到另一個分割槽，且分割槽表的ROW MOVEMENT屬性是DISABLE，會產ORA-14402； · 分割槽表上的索引有兩大類：普通的二叉樹索引，分割槽索引，下面講到的都是分割槽索引： · 按索引分割槽和表分割槽間的對應關係可以分為區域性索引和全域性索引； ? 區域性索引的索引分割槽和表分割槽間是一一對應的，全域性索引則相反； ? 區域性索引的分割槽方法可以用上面提到四種的任何一種，全域性索引的分割槽方法只有範圍分割槽（而且最高的分割槽必須用MAXVALUE來定義）； ? ORACLE自動維護區域性索引的分割槽，當表分割槽被合併，分裂或刪除時，關聯的索引分割槽也會被合併，分裂或刪除；對分割槽表執行管理操作時會使其上的全域性索引失效； ? 建在分割槽表的點陣圖索引必須是區域性分割槽索引； ? ORACLE推薦儘可能地使用區域性索引； · 按索引欄位和分割槽鍵間的關係分為字首索引和非字首索引； ? 字首索引最前面的欄位是分割槽鍵欄位，非字首索引相反； · 在這兩種分類方法的四種組合中，只有三種有效（區域性字首索引，區域性非字首索引，全域性字首索引），不存在全域性非字首索引； · 分割槽表給CBO帶來很多選項，如分割槽排除，並行分割槽連線等。 第三章 SQL 調整 7.簇 · 簇是一個或多個表的組合，這些表的資料儲存在相同的資料塊中，當透過簇鍵查詢這些表時，只需讀一個資料塊就能返回連線的多個表的資料； · 有兩種簇，索引簇和雜湊簇； · 符合下面的條件時適合使用簇： ? 表初始裝載以後，很少或者沒有DML發生； ? 針對每一條主表記錄，從表中的記錄數大致相同並且範圍可知； ? 更多地是對簇中的表一起查詢，較少單獨查詢（適於索引簇）； ? 查詢WHERE條件中使用針對簇鍵的相等匹配（適於雜湊簇）。 · 先建立簇，建簇時系統分配簇段，再在表建立時將表指定到簇，建表時不能指定表空間，所需空間從簇段中分配； · 建簇時需指定簇鍵，不管有多個表或者多少條記錄，同一鍵值在簇中只儲存一次； · 建簇時指定SIZE關鍵值，這個值定義與一個簇鍵關聯的記錄佔用的空間之和； · 索引簇的關鍵字是INDEX（這是預設值），雜湊簇的關鍵字是HASHKEYS（這個子句後跟一個整數指定簇的雜湊值個數，雜湊值是簇鍵值透過簇雜湊函式計算後的返回值，雜湊值相同的行會一起儲存）； · 索引簇在建簇後必須再建簇鍵欄位的索引，這時系統再分配一個索引段（不明白為什麼系統不把這兩步聯起來，而要這樣多此一舉？）；雜湊簇上不能建針對簇鍵的索引； · 針對索引簇表的查詢和普通表相似，可能也是先索引再簇；針對雜湊簇表的查詢，如果WHERE條件中用到鍵欄位的相等匹配，用與插入時相同的雜湊演算法處理這個鍵值可以得出查詢資料的位置，只需一個IO即可取得需要的資料； · 使用簇時必須小心規劃和測試，否則反而會給效能帶來負面影響。 來自http://www.itpub.net/422928,2.html

第四章 調整共享池 1.理解共享池 理解共享池 共享池是SGA的一部分，用來快取SQL和PL/SQL語句，共享池用LRU演算法來管理。 語句快取的好處 · ORACLE執行SQL語句時，先將SQL語句的字串透過一個雜湊演算法得出一個雜湊值，然後檢查共享池中是否已存在這個雜湊值，若有就用已快取的執行計劃來執行這個語句（CACHE HIT 快取命中），若沒有（CACHE MISS 快取缺失）則需進行解析，解析需要完成下面的工作： ? 語法檢查； ? 語義檢查，看參考物件是否存在，型別是否正確； ? （如果是CBO最佳化模式）收集參考物件的統計； ? 檢查使用者的許可權是否足夠； ? 從許多可能的執行路徑中選擇一條作為執行計劃； ? 生成語句的編譯版本（P-CODE）。 · 解析是一個昂貴的操作，因為過程中需要消耗許多資源； · 最大化CACHE HIT是調整共享池的目標 共享池的元件 Library Cache （庫快取） · 庫快取用來存放最近執行過的SQL和PL/SQL（包括過程，函式，包，觸發器，匿名塊和JAVA類），包括以下元件：語句文字，雜湊值，P-CODE，相關的統計，執行計劃； · 與庫快取相關的動態效能檢視如下表： 檢視名 描述 V$SQL 快取SQL的統計（IO，記憶體等），是根椐SQL文字和執行計劃來分組的 V$SQLAREA 快取SQL的統計（IO，記憶體等），是根椐SQL文字來分組的 V$SQLTEXT 快取SQL的完整文字，可能包括多行 V$SQL_PLAN 快取SQL執行計劃的詳細內容，相同的SQL文字可能有不同的執行計劃 Data Dictionary Cache （資料字典快取） · 在處理SQL或PL/SQL語句時，系統要檢查資料字典以確定需要的表是否存在，欄位名與資料型別是否正確，使用者是否有足夠的許可權，這部分資料快取在共享池的資料字典快取中，這塊記憶體區域同樣用LRU演算法來管理； · Oracle將資料字典快取與庫快取分開的原因是： ? 用與庫快取獨立的LRU演算法來管理字典資料，可以使得這部分資料在記憶體中保持更長的時間； ? 後續使用者發出與以前使用者類似的語句時，也能在資料字典快取中找到需要的資料。 User Global Area （使用者全域性域） · 只有在共享伺服器選項下，共享池中才存在UGA； · UGA用來快取會話資訊（在共享伺服器選項下，一個會話的多條SQL或者PL/SQL語往往是由不同的伺服器程式來完成的，因此有必要將會話資訊存放在共享的位置；在專用伺服器配置下，使用者會話資訊存放在PGA中）。

2.測量共享池的效能

測量共享池的效能
· 共享池的效能主要用庫快取命中率和資料字典快取命中率來測量，前者更重要，Oracle建議在未調整好庫快取之前，不必費力去調整資料字典快取。

測量庫快取的效能
· 庫快取的效能指標來自V$LIBRARYCACHE檢視，這個檢視的主要欄位描述如下：
欄位名 描述 可接受值(OLTP)
NAMESPACE 主要的型別有SQL AREA (SQL語句), TABLE/PROCEDURE (儲存過程或函式), BODY (包體), TRIGGER (觸發器)
GETS 解析次數
GETHITS 解析時發現語句的解析版本已存在於庫快取中（解析命中）的次數
GETHITRATIO 解析命中率 大於90%
PINS 執行次數
PINHITS 執行命中次數
PINHITRATIO 執行命中率 大於90%
RELOADS 重新解析的次數，重新解析的原因是已快取的解析版本老化或失效 sum(reloads)/sum(pins) < 1%
INVALIDATIONS 語句快取失效的次數，失效的原因是依賴物件被修改刪除或者編譯

· STATSPACK中存放關於庫快取效能的資料有兩個地方：
? Instance Efficiency Percentages (Target 100%)（例項命中率） – Library Hit %；
? Library Cache Activity for DB（庫快取活動）。

測量資料字典快取的效能
· 資料字典快取效能指標來自V$ROWCACHE檢視，用下面的查詢來獲得命中率：
select 1 – (sum(getmisses) / sum(gets)) from v$rowcache;可以接受的命中率是85%以上；
· STATSPACK中關於資料字典快取效能的資料存放在Dictionary Cache Stats for DB（資料字典快取統計）。

3.改進共享池的效能

改進共享池的效能
改進共享池的效能就是提高庫快取和資料字典快取的命中率，有以下一些方法：

增大共享池
· 增大共享池能減慢庫快取和資料字典快取被LRU演算法移出的速度，從而提高命中率；
· 庫快取命中率和資料字典快取命中率通常都趨向於一致，極少出現一高一低的情形；
· 共享池的大小由初始化引數SHARED_POOL_SIZE來確定；
· 已使用庫快取的大小可以透過sum(V$DB_OBJECT_CACHE.SHARABLE_MEM)（非SQL物件）和 sum(V$SQLAREA.SHARABLE_MEM)（SQL語句） 來查詢；
· 可以用ALTER SYSTEM SET SHARED_POOL_SIZE = XX; 來動態更改共享池的大小，但需保證更改後SGA的大小不大於SGA_MAX_SIZE，否則會現ORA-04033的錯誤；
· 也可以透過關機更改初始引數SHARED_POOL_SIZE再重啟來手工更改共享池的大小，這種方法適於同時改大SGA_MAX_SIZE這個引數；
· 無統計資料時，可以按照下面的策略來為ORACLE伺服器分配記憶體：
? 作業系統（NT需要的較UNIX多），ORACLE後臺程式，伺服器程式，其它非ORACLE程式共需記憶體= 伺服器總記憶體 * 45%（1G以下時） or 25~40%（1G以上時）；
? TSGA（SGA總記憶體） = 伺服器總記憶體 * 55%（1G以下時） or 60~75%（1G以上時）；
? TSGAI（單個例項的SGA記憶體） = TSGA / 伺服器上的例項數；
? 共享池 = TSGAI * 45%；
? 資料快取 = TSGAI * 45%；
? 日誌快取 = TSGAI * 10% （10%的日誌快取通常都偏大，可待有統計資料後視情形將多出的部分分給共享池，資料快取或者大池）；
? 若需配置大池和JAVA池時，從本例項SGA總記憶體中分配。

為大的PL/SQL語句留空間；
· 執行大的PL/SQL程式時，為了獲得足夠的快取空間，LRU演算法會移出許多已解析的語句，爾後這些剛被移出的語句可能又要重新裝入，這樣就降低的庫快取的命中率，為了避免這樣的情形，可以設定共享池保留區給大的PL/SQL物件使用；
· 引數SHARED_POOL_RESERVED_SIZE用來指定共享池保留區的大小，最大可指定到共享池的一半，預設值是共享池的5%，ORACLE建議從共享池的10%開始調整；
· 可以從V$DB_OBJECT_CACHE查到目前快取物件及其使用空間（OWNER, NAME, SHARABLE_MEM）；
· 可以用V$SHARED_POOL_RESERVED檢視來監控保留區的使用情況以決定正確的保留區大小：
? REQUESTS 從保留區中請求空間的次數；
? REQUEST_MISSES 保留區無空間可用而需要從非保留區清洗物件的請求次數；這個值為零或者一直很穩定表示保留區空間設定偏大；
? FREE_SPACE 保留區未用的空間；這個空間大於保留區的一半時表示保留區空間設定偏大；
? REQUEST_FAILURES 請求無法滿足，出現ORA-04031錯誤的次數； 這個值非零或者穩定增加時表示保留區太小；
? 隱含引數_shared_pool_reserve_min_alloc（4400）確定請求保留區的起始大小；
? 調整保留區的目標是使REQUEST_MISSES, REQUEST_FAILURES接近零。
· 可以用DBMS_SHARED_POOL.ABORT_REQUEST_THRESHOLD這個包過程設定一個閥值，大於這個值的庫快取請求將會失敗。

Keep PL/SQL
· 將重用率高的PL/SQL程式碼KEEP在庫快取中可以提高命中率，這個過程又稱Pinning，由DBMS_SHARED_POOL.KEEP過程來完成，這些物件存放在共享池保留區中；
· 清洗共享池語句（ALTER SYSTEM FLUSH SHARED_POOL;）並不會清洗Pinned物件，但是系統重啟後，這些物件將不再是Pinned。
· Keep相關：
? DBMS_SHARED_POOL這個包並沒有在執行catproc.sql時安裝，需執行一次dbmspool.sql這個指令碼；
? 可以用這個包中的KEEP, UNKEEP來pin 和unpin儲存物件；
? 可從V$DB_OBJECT_CACHE.KEEP這個欄位得知哪些物件已被Pinned。
· 要確定哪些物件適於KEEP，可以啟用審計功能來檢視是哪些物件被頻繁地呼叫，要KEEP匿名的PL/SQL程式比較麻煩，建議將經常使用且長度超過500個字元的匿名塊寫成過程和包再進行KEEP；
· 因為重啟後所有的KEEP都將失效，所以將KEEP的動作放在系統觸發器中（AFTER STARTUP ON DATABASE）是較合適的。

程式碼重用；
· 確定是否需要對語句進行（硬）解析時，是先比較語句的雜湊值，下面的兩種方法有助於獲得相同的雜湊值，從而可以實現重用程式碼，提高命中率：
? 開發組的所有成員都使用相同的編碼規範（包括大小寫，空格，換行等）；
? 使用繫結變數（提高命中率的同時可能會產生不夠好的執行計劃，因為最佳化器不知道變數的確定值，在有欄位的柱狀圖統計資料時也不能夠利用）。

調整相關初始化引數。
OPEN_CURSORS
· 這個引數指定每個使用者會話能開啟的遊標個數；
· 增大這個值可以減少重新解析會話曾開啟的語句的機會，提高命中率，但需要更大的共享池空間。

CURSOR_SPACE_FOR_TIME
· 這個引數設為真時，只有當所有引用共享SQL的遊標都關閉後，LRU才有可能移出這個SQL所佔的快取空間來重用，預設值是FALSE；
· 只有在共享池足夠大的情況下才能考慮設為真，設為真時可以減少重解析，提高命中率，加快遊標的執行（空間換時間）。

SESSION_CACHED_CURSORS
· 這個引數指定會話能夠快取遊標的個數，預設值是零；
· 如果會話反覆執行某些語句，設定該值大於零能提高遊標的執行速度，這些快取也是用LRU演算法來管理的。

CURSOR_SHARING
· 這個引數決定什麼樣的SQL語句能夠共享遊標，有三個取值：FORCE, SIMILAR, EXACT；
? FORCE 兩個語句的差異只在字面值，這些差異不會改變語句的含義時可共享遊標；
? SIMILAR兩個語句的差異只在字面值，這些差異不會改變語句的含義和執行計劃時可共享遊標（這個值是在9i 中引入，如果有差異的欄位上有柱狀圖統計，這時執行計劃會改變，不能共享遊標，行為同於EXACT，如果差異欄位上沒有柱狀圖統計，執行計劃相同，這時的行為同於FORCE）；
? EXACT 兩個語句必須精確匹配才能共享遊標，這是預設值。

第五章 調整資料快取 1.理解資料快取

理解資料快取
· 資料快取是SGA的一部分，用於存放使用者最近存取過的段的資料塊的副本，這些段可能是資料段，索引段，簇段，LOB段，LOB索引段，回滾段，臨時段，資料快取單元的大小與資料庫塊大小一致。
· 資料快取用下面的方法進行管理：

LRU列表
· 在Oracle執行SQL語句的過程中，相關的段資料要複製到SGA的資料快取中來，這個操作由使用者的伺服器程式來執行；
· 與共享池類似，資料快取也是由LRU演算法來管理的，當資料快取的快取塊被填滿而又有新的快取塊請求時，LRU將最近最少使用的快取塊老化出去，而保留最近經常使用的快取塊，當一個使用者發現要讀取的資料塊已在之前由其它使用者讀入時，就可以節約很多時間，因為從記憶體中讀取比從磁碟中讀取要快上數千倍；
· LRU演算法管理一個LRU列表，這個列表類似於一個輸送帶，伺服器程式將剛剛存取過的資料塊放在輸送帶的開始端，隨著更多的塊被讀入，先前讀入的塊向輸送帶末端移動，如果在到達末端之前某個塊被再一次存取，則這個塊又被移到開始端，否則就會從輸送帶末端跌落（快取塊被老化）；
· LRU對全表掃描時讀入快取塊的管理與上面的有些不同，這些塊一讀入就被放在LRU列表的末端，這樣可以避免對大表全表掃描時把資料快取中的所有快取塊都清洗出去；
· LRU演算法管理下的快取塊有四種狀態：
? Free 自資料庫啟動以來尚未被使用過的快取塊；
? Pinned 正在被伺服器程式使用的快取塊；
? Clean 曾被使用過且可立即被重用的快取塊，讀入後未經修改或者最近一次修改已被寫回磁碟，快取版本與檔案資料塊一致；
? Dirty 曾被使用過且不能立即被重用的快取塊，讀入後作過修改且最近一次修改未被寫回磁碟，快取版本與檔案資料塊不一致；
· 管理髒塊時會用到一個髒塊列表（Dirty List，又名寫列表），這個列表被檢查點佇列使用，用於跟蹤所有的髒塊，以第一次修改時間排序，髒塊由DBW0程式寫回磁碟。

使用者伺服器程式
· 當需要讀取某個資料塊時，伺服器程式先到資料快取中檢視該塊是否已存在，若沒有找到，就需要將資料塊從資料檔案讀到資料快取中來，這首先要在資料快取中找到一個可用的快取塊來容納資料塊的副本，這個過程中伺服器程式可能要和LRU列表以及髒列表打交道：
? 在LRU列表上查詢可用塊的時候，伺服器程式將查到的髒塊從LRU列表移到髒列表；
? 隨著髒塊的加入，髒列表不斷變長，當長度超過某個預定義的長度時，觸發DBW0將髒列表上的髒塊寫回磁碟；
? 如果伺服器程式在LRU列表查詢很多的塊（超過某個閥值）都沒能找到可用的塊時，觸發DBW0程式，將髒塊直接從LRU列表寫回磁碟。
· 如果伺服器程式發現要找的塊已在資料快取中，但塊的版本要晚於一致性讀需要的版本時（版本更早時可直接使用），伺服器程式在資料快取中建立一個新塊利用回滾段資料回滾到需要的版本（如果是在序列化事務中，且造成更晚版本的已提交的修改不是由當前事務造成的話，則報錯）。

資料庫寫入程式（DBW0）
· DBW0程式負責將資料快取中的髒塊寫回磁碟，這個操作在下面的情形下發生：
? 伺服器程式不斷地將髒塊從LRU列表移到髒列表，當髒列表的長度達到閥值時，DBW0將髒列表上的髒塊寫回磁碟；
? 伺服器程式在LRU列表上檢查太多的塊都沒能找到一個可用塊的時候，DBW0直接從LRU列上將髒塊寫回磁碟；
? DBW0程式每三秒被啟用一次，將LRU列表上的髒塊移到髒列表，若髒列表長度達到閥值時，從髒列表上將髒塊寫回磁碟；
? 檢查點發生時，DBW0將髒塊從LRU列表移到髒列表，再從髒列表寫髒塊回磁碟；
? 資料庫關閉時（不包括Shutdown Abort），DBW0將所有髒塊寫回磁碟；
? 表空間熱備前，DBW0將屬於這個表空間的所有髒塊從LRU列表移到髒列表，然後從髒列表將髒塊寫回磁碟；
? 表空間離線時（Normal，Temporary），DBW0將屬於這個表空間的所有髒塊從LRU移到髒列表，然後從髒列表將髒塊寫回磁碟；
? 刪除段時，DBW0先將這個段的髒塊寫回磁碟。

第五章 調整資料快取 2.測量資料快取的效能

資料快取命中率
· 資料快取命中率由下面四個來自V$SYSSTAT的統計值計算得來：
? physical reads 從磁碟讀取的塊數；
? physical reads direct 從磁碟讀取不進入資料快取的資料塊數；
? physical reads direct (lob) 從磁碟讀取不進入資料快取的LOB物件塊數；
? session logical reads 從資料快取中讀取的塊數，包括當前讀（Current, db_block_gets）和一致性讀(consistent gets)；
· 命中率 = 1- ((physical reads – physical reads direct – physical reads direct (lob)) / session logical reads)，調整好的OLTP系統，資料快取命中率應該在90%以上；
· 查詢語句如下：
select 1 – ((physical.value – direct.value – lobs.value) / logical.value)
from v$sysstat physical,
v$sysstat direct,
v$sysstat lobs,
v$sysstat logical
where physical.name = ‘physical reads’
and direct.name = ‘physical reads direct’
and lobs.name = ‘physical reads direct (lob)’
and logical.name = ‘session logical reads’;

資料快取等待
· free buffer inspected 使用者伺服器程式為了將資料從磁碟讀到資料快取，在資料快取中查詢可用的塊，在找到可用塊之前共查詢過的快取塊數；
· free buffer waits 使用者伺服器程式為了將資料從磁碟讀到某個髒塊，需等待DBW0將這個髒塊寫回磁碟；
· buffer busy waits 使用者伺服器程式要存取某個快取塊，如果有其它會話正在使用它，必須等待其完成，可能的原因有：
? 並行插入時表的Free lists不足引發段頭等待（改用LMT可消除此影響）；
? 併發事務較多時表的InitTrans太小引發事務等待（增大表的InitTrans）；
? 回滾段不足時引發undo header等待（增加回滾段的個數）；
· 一起查詢時用下面的語句：
select name,value from v$sysstat where name = ‘free buffer inspected’
union
select event,total_waits from v$system_event where event in (‘free buffer waits’,’buffer busy waits’);

Statspack中存放資料快取效能資料的位置
· Instance Efficiency Percentages (Target 100%)（例項命中率） – Buffer Hit %；
· Instance Activity Stat (例項的活動統計) – free buffer inspected， physical reads， physical reads direct， physical reads direct (lob)， session logical reads；
· Buffer Pool Statistics （資料快取統計） -- free buffer waits, buffer busy waits。

第五章 調整資料快取 3.增大資料快取

改進資料快取的效能
改進資料快取的效能有下面一些方法：增大資料快取，用多個資料快取池，將小表CACHE到資料快取，繞過快取池，正確地使用索引

增大資料快取
· 改進資料快取效能最簡單的方法就是增大這塊記憶體，加大資料快取後，快取塊就可以在LRU列表上呆更多的時間，從而提高命中率，free buffer inspected， buffer busy waits， free buffer waits這三個統計值也會相應降低；
· 與資料快取相關的初始引數有下面的一些：
? DB_BLOCK_SIZE 主塊位元組數，在資料庫建立時指定，不能更改，SYSTEM和TEMP表空間只能使用主塊建立，後面提到的調整方法主要是針對主塊快取池來進行討論的；
? DB_CACHE_SIZE 指定預設快取池的大小，不能為零，用於存放主塊；
? DB_KEEP_CACHE_SIZE 指定保持快取池的大小，預設值為零，用於存放主塊；
? DB_RECYCLE_CACHE_SIZE 指定回收快取池的大小，預設值為零，用於存放主塊；
? DB_nK_CACHE_SIZE 預設值為零，這些引數指定用於容納不同於主塊大小的資料塊的快取空間，建立其它表空間時可以使用與主塊不同的塊大小（先設定好對應引數再建立表空間，存在對應的表空間時，這些引數不能再更改回預設值），不要更改與主塊大小相同的那個引數的預設值。
· 動態修改資料快取的大小；可以用ALTER SYSTEM命令動態修改資料快取的大小，但必須記住下面三條規則：
? 最終生效的大小是最小粒度的整數倍；
? 共享池，資料快取，日誌快取之和不能大於SGA_MAX_SIZE的值；
? DB_CACHE_SIZE不能指定為零。
· 手工更改資料快取的大小：關閉例項，更改引數，再重啟，這種方法適於同時更改SGA_MAX_SIZE；
· 確定資料快取的合適大小：用Buffer Cache Advisory
? 設定引數DB_CACHE_ADVISE的值為ON（設定為OFF時關掉ADVISORY，設為READY時預先給ADVISORY分配記憶體，但不分配CPU，以防將該引數改為ON時出現記憶體不足的錯誤）；
? 待ADVISORY執行一段時間（30分鐘以上）後，再查詢V$DB_CACHE_ADVICE，相關欄位的意義如下表：
ü ID 快取池編號
ü NAME 快取池的名字
ü BLOCK_SIZE 塊大小
ü ADVISE_STATUS 引數的狀態：ON， OFF， READY
ü SIZE_FOR_ESTIMATE 評估快取大小（M）
ü ESTD_PHYSICAL_READS 評估的物理讀
? 評估的物理讀趨於穩定時的評估快取就是最合適的大小。

第五章 調整資料快取 4.用多個資料快取池

用多個資料快取池
· 預設情況下，資料快取中只存在一個（預設）快取池，有可能出現這樣的情形，某個使用者程式偶爾存取一個很少使用的大表（非全表掃描），將快取中其它使用者經常使用的快取塊擠出，為了解決這個問題，可以定義多個快取池，將段明確地指定給某個快取池；
· 可以建立三種快取池，根椐段被存取頻率的不同將段指定給不同的池：
? Keep Pool 用於存放你想在快取中保留時間儘可能長的段；
? Recycle Pool 用於存放你想在快取中保留時間儘可能短的段；
? Default Pool 存放不同於上面的其它的段；
· 與8i不同，9i各個池的記憶體分配是相互獨立的；
· 確定哪些段適於存放在哪個快取池中；
? 要確定段與快取池的對應關係，需要對應用程式，段及段的存取方式有一個整體的瞭解；
? 瞭解目前已快取的段的資訊有助於確定段與快取池的關係，V$BH， V$CACHE中都含有快取的詳細資料，下面兩個查詢作用類似，都能確定已快取塊的段名，所屬使用者名稱，段型別及快取塊數：
ü Select Obj.Owner,
Obj.Object_Name,
Obj.Object_Type,
Count(Distinct Bh.Block#) "Num of Buffers"
From V$bh Bh, Dba_Objects Obj
Where Bh.Objd = Obj.Object_Id
And Owner <> ’SYS’
Group By Obj.Owner, Obj.Object_Name, Obj.Object_Type;
ü Select u.Username,
c.Name,
c.Kind,
Count(Distinct c.Block#) "Num of Buffers"
From V$cache c, Dba_Users u
Where c.Owner# = u.User_Id
And u.Username <> ’SYS’
Group By u.Username, c.Name, c.Kind
? 推薦經常使用且小於預設快取池10%的段置於Keep池中；
? 推薦只由個別事務使用且大於預設快取池200%的段置於Recycle池中；
· 確定每個池的合適大小；在確定了段和各個池的對應關係後，可以透過DBA_SEGMENTS.BLOCKS查出對應池的段塊之和，計算出各個池佔用比例，再根椐快取池的總大小可以確定每個池的大小；在末經精確計算的情況下，ORACLE推薦先按下面的比例為各個池分配空間：Default Pool 60%， Keep Pool 30%， Recycle Pool 10%。
· 建立快取池；根椐前面提到的方法，動態或手工更改這三個引數（DB_CACHE_SIZE， DB_KEEP_CACHE_SIZE， DB_RECYCLE_CACHE_SIZE），可以建立快取池；
· 將段指定給快取池；
? 可以在建立段時指定快取池：Create … STORAGE ( BUFFER_POOL pool);
? 也可以更改段的快取池屬性：Alter … STORAGE ( BUFFER_POOL pool);
? 上面的pool可選值為KEEP， RECYCLE， DEFAULT（預設值）；
? DBA_SEGMENTS.BUFFER_POOL欄位可以查到段的快取池屬性；
? 更改段的快取池屬性並不會立即生效，需等到段下一次從磁碟載入快取池時才能生效。
· 監控各個快取池的效能；
? V$BUFFER_POOL效能檢視： Name（快取池的名字）， Block_Size（池的塊大小，Byte）， Current_Size（快取池的當前大小，M）。
? V$BUFFER_POOL_STATISTICS，包含每個快取池的相關統計，可用來計算單獨快取池的命中率，重要的欄位如下：
ü NAME
ü DB_BLOCK_GETS
ü CONSISTENT_GETS
ü PHYSICAL_READS
ü FREE_BUFFER_INSPECTED
ü BUFFER_BUSY_WAITS
ü FREE_BUFFER_WAITS
ü Select Name "Buffer Pool",
1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets)) "Hit Ratio"
From V$buffer_Pool_Statistics
Where Db_Block_Gets + Consistent_Gets > 0;
ü 經過調整後各個快取池的命中率，Keep池的應該很高，Recycle池的應該很低，Default池應該為70~80%。
· 命中率高並不總是代表效能好；
? 單純的快取命中率高並不代表快取的效能很好，在RBO下，不正確的建立索引可能導致效能低下而快取命中率卻相當高，比如在一個經常使用表的低集勢欄位上建二叉樹索引就可能出現這種情形，系統不停而低效地掃描索引，導致出現虛高的快取命中率；
? 透過分析V$BH，V$CACHE的查詢結果，結合對應用程式工作方式的理解，可以發現這種情況；
? 按下面的處理可以消除這種可能：
ü 收集方案的統計並使用基於CBO的最佳化器；
ü 建立在Where條件中使用率高的欄位的柱狀圖統計；
ü 正確地使用多個快取池。

第五章 調整資料快取 5.其它提高資料快取效能的方法

將小表CACHE到資料快取
· 在多個快取池的配置下，每個快取池都是由獨立的LRU演算法來管理的，通常情況下剛存取的塊都是放在LRU列表的開始端，而FTS（全表掃描）例外，剛讀入的塊放在LRU列表的末端；
· 上述演算法會產生如下一些困境：CBO在讀取一些小表或者讀取表中的較多資料時會使用FTS，這時，即使這些表使用頻率很高，也會因為上面提到的原因而很快被老化，在下次用到時又得重新讀入；
· 為了解決這個難題，引進了Cache表的概念，將表設為Cache後，對錶的FTS不再是將塊放在LRU列表的末端，而是置於LRU列表的開始端；
· 有下面三種方法將表設為Cache：
ü 建立時指定CACHE關鍵字：Create table … cache;
ü 更改時指定CACHE關鍵字：Alter table … cache;
ü 查詢時指定CACHE hint：select /*+ cache */ * from …;
· 從DBA_TABLES.CACHE欄位可以查到由前兩種方法設定的表的CACHE屬性。

繞過快取池
· 下面兩種特定的操作可以繞過快取池，讀入的資料塊不進入資料快取，而存放在記憶體中由使用者伺服器程式控制的其它區域（UGA，PGA？）：
ü Sort Direct Writes（第八章調整IO時會講到）；
ü Parallel DML（由多個伺服器程式來執行一個DML操作，只有當系統中有多個CPU，多個IO子系統以及足夠記憶體時才可考慮作這樣的設定，並行DML超出本書討論的範圍）。

正確地使用索引
· 不正確的全表掃描會極大地降低資料快取的命中率，建立正確的索引後可以提高資料快取效能；
· 建議儘可能地在外來鍵上建立索引，這可以提高多表連線的效率。

第六章 調整SGA的其它區域 1.共享伺服器的概念

第六章 調整SGA的其它區域
· 已討論過的共享池，資料快取和下一章將要討論的日誌快取是每一個ORACLE環境下的必備元件，這一章討論SGA的另外三個可選元件：共享伺服器，大池，Java池。

共享伺服器的概念
· 每一個Oracle應用程式有兩個與之相聯的程式，一個是執行在客戶端機器或者應用伺服器上的使用者程式，一個是執行在Oracle伺服器上的伺服器程式；
· 預設的專用伺服器配置下，每一個伺服器程式只專職於處理一個使用者會話的請求，在Unix上，伺服器程式是一個可用ps命令觀測到的單獨的程式，在Windows系統上，伺服器程式是一個執行於oracle.exe程式中的執行緒；
· 另有一種配置稱為共享伺服器，少數一些伺服器程式為所有的使用者程式服務，在9i以前的版本，這種配置也稱為MTS（多執行緒伺服器）；
· 專用伺服器和共享伺服器的差別有點類似於外出時坐專車和打計程車的區別，專用伺服器能更有效地實現使用者程式和例項間的通訊，但資源利用率低，共享伺服器資源利用率高，在相同的CPU和記憶體條件下可以支援更多的併發使用者，但配置不當時會引起使用者程式較長時間的等待從而降低系統的效能和響應速度。

第六章 調整SGA的其它區域 2.共享伺服器的架構

共享伺服器的架構
共享伺服器配置包含以下元件：
· 使用者程式（User Process） 使用者程式可能由客戶端軟體產生，也可能透過中間元件產生，用於管理到Oracle伺服器的連線；
· 網路監聽程式（Oracle Net Listen Process） 監聽程式用於監聽來自使用者程式的對資料庫的查詢或DML請求，並將這些請求分配給當前比較清閒的某個排程程式；
· 排程程式（Dispatcher Process） 排程程式是Oracle的後臺程式，用於接受來自使用者程式的請求並返回結果，最多隻能有五個同時執行的排程程式，在Unix上可以用ps命令看到，用Unix kill命令殺掉一個排程程式時會同時斷開這個程式負責的所有使用者程式；
· 請求佇列（Request Queue） 請求佇列用於存放排程程式接受到的請求，存在於SGA中，一個例項只有一個；
· 共享伺服器程式（Shared Server Process） 共享服務程式是Oracle的後臺程式，用於與SGA互動來處理使用者程式的請求，功能與專用伺服器類似（用於解析SQL語句，將資料塊從磁碟讀入資料快取，等等），伺服器程式的最大數量由作業系統指定；
· 響應佇列（Response Queue） 響應佇列用於存放共享伺服器程式處理的結果，存在於SGA中，每個排程程式對應一個，排程程式將這些結果返回請求的使用者程式；
· 必須滿足下麵條件時才可配置成共享伺服器：
? 眾多的應用程式使用者； 一般併發使用者數在200以上時需要考慮，具體要看伺服器的CPU和記憶體資源；
? 事務時間短；長事務會造成少數使用者獨佔伺服器程式，而使其它使用者長時間地等待，不適於配置成共享伺服器。
? 不連續的事務；使用者活動間有時常的停頓，通常OLTP事務就是如此，這時伺服器程式能為其它使用者提供服務。

第六章 調整SGA的其它區域 3.配置共享伺服器

配置共享伺服器
· 配置共享伺服器時需要正確地設定下面幾個初始化引數，這些引數用於指定例項啟動時產生的排程程式和共享伺服器程式的數量，以及執行期間如何管理這些程式；
? DISPATCHERS 指定例項啟動時產生的對應網路協議的排程程式數量，可選值是0-5；
? MAX_DISPATCHERS 指定例項允許的最大排程程式數，預設值為5，排程程式的增減必須由管理員手工指定（Alter system set dispatchers = ?）；
? SHARED_SERVERS 指定例項啟動時產生的共享伺服器程式數量，設為零時禁用共享伺服器選項，最小值為1，最大值依賴於作業系統；
? MAX_SHARED_SERVERS 指定例項允許的共享伺服器的最大數量，最大值依賴於作業系統，伺服器程式可由PMON後臺程式根椐系統負載來動態增減；
? CIRCUITS 用於指定網路連線的虛擬電路最大數量；
? PROCESSES 用於指定例項允許的程式的最大數量；
· 管理員執行管理操作時不能使用共享伺服器，必須啟動專用的伺服器程式。

第六章 調整SGA的其它區域 4.測量共享伺服器的效能

測量共享伺服器的效能
· 測量共享伺服器配置的效能可用到下面一些效能檢視：
? V$SHARED_SERVER 共享伺服器程式的詳細統計，包括每個程式的空閒和繁忙時間；
? V$QUEUE 包含請求佇列和響應佇列的資訊；
? V$SHARED_SERVER_MONITOR 關於共享伺服器程式的合計資訊，包含經由PMON啟動和終止的共享伺服器程式數，共享伺服器程式的高水位，併發會話的最大數量，虛抑電路的最大數量；
? V$DISPATCHER 排程程式的詳細統計，包括每個程式的空閒和繁忙時間；
? V$DISPATCHER_RATE 包含排程程式服務的歷史和實時的統計；
? V$CIRCUIT 包含連線路徑（使用者程式和排程程式，共享伺服器程式的關係）的統計，
· 測量共享伺服器程式的效能
? 共享伺服器程式的忙百比例：
Select Name,
Decode(Busy + Idle, 0, 0, Round((Busy / (Busy + Idle)) * 100, 4)) "busy_rate"
From V$shared_Server
Where Status != ’QUIT’;

? 使用者請求在請求佇列中的平均等待時間(百分之一秒)
Select Decode(Totalq,
0,
’TOTALQ IS ZERO’,
Round(Sum(Wait) / Sum(Totalq), 4)) "AVG SHARED_SERVER WAIT"
From V$queue
Where Type = ’COMMON’
Group By Totalq;

? 當上面的數值很大或者不斷上升時需要考慮增加SHARED_SERVERS的值；

· 測量排程程式的效能
? 排程時程的忙百分比（當這個比例超過50時，需要增加DISPATCHERS 的值）：
Select Name "Dispatcher",
Network,
(Round(Sum(Busy) / (Sum(Busy) + Sum(Idle)), 4)) * 100 "Busy_Rate"
From V$dispatcher
Group By Name, Network;

? 使用者請求等待排程程式排程的平均時間(百分之一秒)
Select Decode(Totalq,
0,
’TOTALQ IS ZERO’,
Round(Sum(Wait) / Sum(Totalq), 4)) "AVG DISPATCHER WAIT"
From V$dispatcher d, V$queue q
Where d.Paddr = q.Paddr
And q.Type = ’DISPATCHER’
Group By Totalq;

? 排程程式服務的連線數量（當前，歷史最高以及差異）
Select Name,
Cur_In_Connect_Rate,
Max_In_Connect_Rate,
Max_In_Connect_Rate - Cur_In_Connect_Rate "VARIANCE"
From V$dispatcher_Rate;

· 查詢共享伺服器環境下的累積活動：
Select * From V$shared_Server_Monitor;
· 查詢使用者程式，排程程式和共享伺服器程式間的對應關係：
Select s.Username, d.Name "DISPATCHER", Ss.Name "SHARED_SERVER"
From V$circuit c, V$session s, V$dispatcher d, V$shared_Server Ss
Where c.Saddr = s.Saddr
And c.Dispatcher = d.Paddr
And c.Server = Ss.Paddr;

第六章 調整SGA的其它區域 5.改進共享伺服器的效能

改進共享伺服器的效能
· 共享伺服器環境下的效能調整方向是，在現有的資源配置下支援更多的使用者，而不是一定要如何提高系統的吞吐量或響應時間；
· 效能問題表現在以下三個方面：相關的SGA元件配置不充分，共享伺服器程式太少，排程程式太少；
· 相關的SGA元件配置
? 共享伺服器配置下，使用者的會話與遊標資訊是存放於UGA中的（專用伺服器配置下存放於PGA中），UGA通常存在於共享池中，共享池的主要功能是用來快取SQL語句及資料字典的，這就要求共享池有足夠的空間，不致於因UGA的消耗而降低系統的庫快取命中率和字典快取命中率；
? 下面的查詢語句可以看到系統UGA佔用的空間，這個空間佔用是動態變化的：
Select Sum(s.Value) "Total UGA Bytes"
From V$sesstat s, V$statname n
Where s.Statistic# = n.Statistic#
And n.Name = ’session uga memory max’;
? 在配置了大池後，部分UGA資料會從SGA中移到大池中；
· 有三種改變共享伺服器程式數量的方法：
? PMON後臺程式能夠根椐系統的負載動態增減伺服器程式的數量；
? 管理員動態增加伺服器程式的數量：Alter system set shared_servers = xx;
? 管理員手工增加伺服器程式的數量：更改初始引數shared_servers的值，再重啟；
· 改變排程程式數量的方法只有兩種
? 管理員動態增加排程程式的數量：Alter system set dispatchers = ‘tcp,5’;
? 管理員手工增加排程程式的數量：更改初始引數dispatchers的值，再重啟；
? 使用者程式在其生命週期裡只和一個排程程式發生聯絡，新加入的排程程式只能為在它之後產生的使用者程式服務；
? Oracle推薦配置是，每個排程程式服務250個左右的併發使用者連線；

第六章 調整SGA的其它區域 6.大池的概念

大池的概念
· 共享池除了用於快取SQL外，還有用於容納下面的一些特殊用途的資料：DBW0附屬程式，UGA，RMAN，Parallel Query，當這些選項被使用後，共享池的命中率可能會下降很多，為了解決這個問題，Oracle提供一個新的記憶體區域來存放這些資料，這就是大池；
· 用LARGE_POOL_SIZE這個引數來配置大池，這個引數預設值是零，可選值在600K到2G之間，一旦配置了大池，Oracle會自動使用大池來快取上面提到的資料；
· PARALLEL_AUTOMATIC_TUNING置為真時，大池的空間是動態的，由系統控制；
· 可以使用下面的查詢看到大池已使用和未使用的記憶體：Select * From V$sgastat Where Pool = ’large pool’;
· 持續觀察上面的查詢，如果發現未使用記憶體很大或者不斷增加，表示大池可能分配了太多的記憶體，如果未使用記憶體很小或者不斷減少，表示可能需要加大大池的記憶體。

第六章 調整SGA的其它區域 7.Java池的概念

Java池的概念
· 在Oracle中配置Java環境時有以下一些引數：
? SHARED_POOL_SIZE JVM快取在共享池中；
? JAVA_POOL_SIZE 快取與JAVA相關的會話資料，預設值20M，取值範圍是1M到1G；（Oracle推薦，對於有JAVA的應用，將這個值設到50M或者更大）
? JAVA_SOFT_SESSIONSPACE_LIMIT 當某個JAVA程式請求的記憶體超過這個限制時，會寫一條訊息到使用者跟蹤檔案，預設值是0，最大值是4G；
? JAVA_MAX_SESSIONSPACE_LIMIT 當JAVA程式請求的記憶體超過這個引數的限制時，返回ora-29554的錯誤，預設值是0，最大值是4G；
· 測量JAVA池的效能有下面兩種方法：
? Select * From V$sgastat Where Pool = ’java pool’; 觀察這個查詢，如果發現未使用記憶體很大或者不斷增加，表示JAVA池可能分配了太多的記憶體，如果未使用記憶體很小或者不斷減少，表示可能需要加大JAVA池的記憶體。
? 觀察Statspack中的SGA breakdown difference，裡面有JAVA池free memory的起始值和終止值，如果終止值總是很小或者接近零，表示JAVA池可能太小了；
· 改進JAVA池的效能主要是增大JAVA_POOL_SIZE這個引數，這個引數不能動態調整。