Oracle9i 性能調整與優化(2)
2024-08-29 13:39:24
供稿：網友
第七課 調整磁盤I/O的性能 哪些操作會導致磁盤I/O： 將Buffer cache中的內容寫到數據文件。 寫回退段。 將數據文件的內容讀到Buffer cache中。 將Redo log Buffer中的內容寫到online redo log中。 將online Redo log中的內容歸檔到 archive log中。 1. 調整表空間和數據文件 測量數據文件I/O：使用V$filestat. 使用STATSPACK來測量數據文件I/O. 使用REPORT.TXT來測量數據文件I/O. 使用Performance Manager（I/O）來測量數據文件I/O. 建議： 不要在SYSTEM表空間存放用戶數據。 將 I/O操作均分到幾個數據文件上。（監視數據文件的I/O操作數。） 使用本地治理的表空間。 將數據庫文件與其它程序的文件分開。 使用分區表和分區索引。 將大表放在單獨的表空間。 創建單獨的回退表空間。 創建一個或多個臨時表空間。 不要將聯機重做日志文件和歸檔聯機重做日志文件放在同一個設備上。 至少將一個控制文件放在一個單獨的設備上。 檢查V$sysstat中的‘table scans（long tables）’。 使用init.ora參數DB_FILE_MULTIBLOCK_READ_COUNT（默認為16）來優化表掃描。 2. 調整DBW0性能 監視DBWR0性能。 使用V$system_event監視下列事件：buffer busy waits、free buffer waits、 db file parallel write、write complete waits、 使用init.ora參數DBWR_IO_SLAVES（優化磁盤I/O）、 DB_WRITER_PROCESSES（默認為1，優化Buffer Cache的內部治理）。 假如DBWR_IO_SLAVES設為非0值，DB_WRITER_PROCESSES的值無效。 3.調整段I/O 避免動態空間分配。 表的有關存儲特性 空閑百分比（PCTFREE）：每個對象數據塊中為今后更新該對象而保留的空間的百分比。可以輸入0到99之間的值。默認值為10%. 已用百分比（PCTUSED）：Oracle數據庫為該對象的每個數據塊保留的已用空間的最小百分比。當一個塊的已用空間低于“已用百分比”的值時，則該塊將成為插入行的目標。可以輸入1到99之間的值。默認值為40%. 最小數量：創建段時已分配的總區數。默認值為1.可以輸入1或大于1的值。 SQL> alter table emp allocate extent ； 事務處理數量 初始值：在分配給該對象的每個數據塊內分配給事務處理條目的初始數量。可以輸入1或2（對于簇和索引）到255之間的值。 最大值：可同時更新分配給對象的數據塊的并行事務處理的最大數量。可以輸入1到255之間的值。 自由表 列表：表、簇或索引的每個自由表組的自由表數量。可以輸入1或大于1的值。默認值為1. 組：表、簇或索引的自由表組的數量。可以輸入1或大于1的值。默認值為1. 緩沖池。 行轉移（更新行時超過塊的可用空間）和行鏈接（行的大小超過塊的大小）的概念。 使用V$sysstat來監視行轉移和行鏈接：table fetch continued row. SQL>analyze table emp compute statistics； 使用DBA_TABLES來查詢統計信息。 SQL> alter table emp deallocate unused； SQL> alter table scott.emp move tablespace users； 表的高水位標志High Water Mark（HWM）。 4.調整排序IO 哪些SQL語句需要排序操作：order by、group by、selec distinct、union、 intersect、minus、analyze、create index、聯接。 V$sysstat.內存排序和磁盤排序（臨時表空間中）。 監視排序性能（內存排序比例>95%）。 使用init.ora參數SORT_AREA_SIZE（512K）、SORT_AREA_RETAINED_SIZE、 pga_aggregate_target、WORKAREA_SIZE_POLOCY. 使用Performance Manager（數據庫例程）來監視排序。 如何避免排序：SQL語法、正確索引、創建索引、ANALYZE. v$sort_segment、v$sort_usage.
使用Tablespace Map. 使用 Reorg Wizard. 5. 優化回退段 一個回退段的區間可以分配給多個事務，回退段的一個數據塊只能分配給一個事務。 測量回退段事務表的爭用 select * from V$system_event where event like ‘%undo%’； 回退段事務表的等待時間應接近于0. select * from V$waitstat； V$rollstat 回退段事務表訪問的成功率應>95%. 回退段區間爭用 V$waitstat、V$sysstat. 回退段事務環繞（Wrap）：一個事務占用的回退段從一個區間擴展到另一個區間。 回退段的動態區間分配 V$system_event. 使用V$rollstat來監視回退段的使用情況。 使用Performance Manager（后臺進程）來測量回退段。 提高回退段的性能 Oracle9i中的撤消表空間。 建議：每四個事務使用一個回退段，最多不超過20個回退段。 會退段的區間大小512k，最小區間數20. 明確分配回退段給事務。 SQL> set transaction use rollback segment rbs01； 最小化回退段活動：EXPORT、IMPORT、SQL* Loader時加commit=y參數。 Oracle9i中的撤消表空間。 第八課 調整閂（latch）和鎖定（lock） 1. 調整閂（latch） 閂可以作為內存性能的另一個指標。 1.閂：等待閂和立即閂（V$lacth.共239個）。 數據庫中是否存在閂爭用V$system_event（"latch free"）。 幾個重要的閂：shared pool、library cache、cache buffers lru chain、 cache buffers chains、redo allocation、redo copy. select * from V$latch where misses！=0； 2.自由列表：V$system_event（"buffer busy waits"）。 V$waitstat. 測量哪些段存在自由列表爭用：dba_segments、V$session_wait. alter table scott.emp storage （freelists 5）； 自動段空間治理的表空間。 2. 調整鎖定 DML鎖（TM）和DDL鎖（TX） 鎖定模式： RX：對表UPDATE、INSERT、DELETE時獲得。 RS：對表SELECT … FOR UPDATE時獲得。 S： LOCK TABLE EMP IN SHARE MODE； 可以是多個用戶獲得。 SRX： LOCK TABLE EMP IN SHARE ROW EXCLUSIVE MODE； 只能是一個用戶獲得。 外鍵約束時的鎖定。死鎖。 用Lock Monitor監視鎖定。 第九課 調整操作系統 1. 調整操作系統 觀察內存和CPU利用率（<90%）。 2. 使用Resource Manager 資源使用者組。一個用戶可以是多個資源使用者組的成員，但一次只有一個組是活動的。 確定用戶的CPU利用率。（v$sesstat和v$sysstat） 資源計劃：由資源計劃指令組成。一次只能有一個資源計劃是活動的（V$rsrc_plan）。 alter system set resource_manager_plan=system_plan； select username，RESOURCE_CONSUMER_GROUP from V$session； 子計劃。 資源計劃調度。 使用Expert進行優化 第一步：創建一個優化會話（tuning session）。 第二步：確定優化范圍（scope）。 第三步：收集數據。 第四步：復查（review）已收集到的數據。 第五步：生成建議案。建議報告。 第六步：創建實現建議所需的SQL腳本。
