Oracle 優化經典

Oracle 優化經典

1. Course Intraduction 0
2. Tuning overview 1

(1) 調整的先後次序

1. Tune the design. -- Application designers

2. Tune the application. -- Application developers

3. Tune memory. --|

4. Tune I/O. |-- DBA

5. Tune contention. |

6. Tune the operating system. --|

(2) 調整的基點和主要的可測量的目標

Database hit percentages 命中率是base line

SQL statements access the smallest possible number of Oracle blocks 訪問儘可能少的資料塊

response time, database availability, memory utilization(頁面交換)等

3. Oracle Alert and Trace Files 2

(1) USER_DUMP_DEST和BACKGROUD_DUMP_DEST

USER_DUMP_DEST: SQL_TRACE, DEAD LOCK, 使用者session中sql語句的執行情況

BACKGROUD_DUMP_DEST: Alert.log, 系統後臺程式的錯誤資訊

(2) Alert.log檔案的特性和內容

-- 啟動時不存在則自動建立,存在BACKGROUD_DUMP_DEST路徑下

-- 檔案尺寸一直增長,需要人工清除

-- 包含內容有:

Internal errors (ORA-600), and block corruption errors (ORA-1578)

影響資料庫結構,引數的操作,以及命令:CREATE DATABASE, STARTUP, SHUTDOWN, ARCHIVE LOG,RECOVER

例項啟動時的非預設引數 -- 啟動時寫入 控制檔案和線上表空間備份

未完成的檢查點

(3) SQL_TRACE設定的兩個級別

Instance 引數中設定

Session ALTER SESSION SET SQL_TRACE=TRUE / DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,TRUE/FALSE)

(4) 引數MAX_DUMP_FILE_SIZE限制User Trace檔案的O/S Blocks

4. Utilities and Dynamic Performance Views 3

(1) Anylyze執行之後查詢資訊的字典:

DBA_TABLES and DBA_TAB_COLUMNS table storage within extents and blocks

DBA_HISTOGRAMS and INDEX_HISTOGRAM data about nonindexed and indexed column data distribution.

DBA_INDEXES and INDEX_STATS data about index within extents and blocks and indexation usefulness.

(2) 關於Latch

Latch的型別: Willing-To-Wait 和 Immediate Gets/Misses/Sleeps的意義

Reports.txt中記錄的Latch的hit ratio應>=99%,即redo latch競爭應<=1%

DBA可調整的Latch爭用區域: Redo allocation latch/Redo copy latch/LRU latch

(3) 動態檢視

特點: 是X$表和V$_的同義詞,屬於sys,在V$FIXED_TABLE中,NOMOUNT和MOUT時可以查詢,TIMED_STATISTICS=TRUE記錄WAIT_TIME

主要的檢視

%EVENT 事件類檢視 包含wait for event的統計 -- 注意%WAIT, wait for events or resource的資訊

-- V$SYSTEM_EVENT , V$SESSION_EVENT

%STAT 統計類檢視 V$SYSSTAT(Instance啟動以來的統計)

%CACHE 記憶體方面的的統計檢視,主要有V$LIBRARYCACHE和V$ROWCACHE(資料字典的...),V$DB_OBJECT_CACHE

PX% 並行處理的資訊

V$SESSION 記錄所有使用者連線的type(BACKGROUND/USER)

V$LOCK 和 V$LATCH的區別: 前者是現有鎖資訊, 後者是鎖爭用統計資訊

V$SYSTEM_EVENT(所有session從Instance啟動以來)/V$SESSION_EVENT(每個session)/V$SESSION_WAIT(當前活動session)

V$SEESION_WAIT.WAIT_TIME -- 要獲取WAIT_TIME的值,必須要將TIMED_STATISTICS=TRUE(動態可改)

> 0 The session's last wait time

= 0 The session is currently waiting

= -1 The value was less than 1/100 of a second

= -2 The system cannot provide timing information -- TIMED_STATISTICS=FALSE

(4) 關於UTLBSTAT and UTLESTAT 工具

特點: 需要SYSDBA,建立了一些統計表和檢視並在結束時除,DEFFERENCE記錄開始和結束時統計的差異,report.txt

TIMED_STATISTICS=TRUE,統計期間發生中斷需要重新執行Report.txt的內容Library Cache 涉及SQL,PL/SQL語句執行System 涉及buffer cache和邏輯讀寫Wait events 涉及等待的CPU時間

Latch 涉及記憶體中鎖的爭用,redo allocation/redo copy/LRU

Rollback contention 涉及undo header, 等待rollback header中的事務slot Buffer Busy Wait 涉及data block,segment header,undo header爭用

Dictionary cache 涉及資料字典的get/miss

I/O 涉及資料檔案的讀寫

Period of measurement UTLBSTAT,UTLESTAT開始和結束的時間

5. Tuning the Shared Pool 7

(1) 關於Shared Pool

特性: SHARED_POOL_SIZE決定大小, library cache + data dictionary cache + UGA + large pool

調整Shared Pool的原因: shared pool的miss比database buffer cache的miss影響大,library cache首要

(2) 關於Large objects

特性: use LRU

tuning: generic code/bind variable/防止空間不足age out而reload/防止object更改而re-parse

大的匿名塊->小的過程/pin/reserve space for large objects

keep: 方法: DBMS_SHARED_POOL.KEEP / .UNKEEP

需要keep的object: 常用的包/常用的trigger/sequence

使用: Instance啟動時keep防止碎片/ALTER SYSTEM FLUSH SHARED_POOL來flush shared pool(no keep)

檢視: V$LIBRARYCACHE(GETHITRATIO>90%,否則優化語句應用; reloads/pins應<=1%)

V$SGASTAT/V$SQLAREA/V$SQLTEXT/V$DB_OBJECT_CACHE(過程等佔用記憶體)

引數: OPEN_CURSORS/SESSION_CACHED_CURSORS

CURSOR_SPACE_FOR_TIME -- 除非RELOADS in V$LIBRARYCACHE一直為0,否則保持預設值:false

預留空間: V$SHARED_POOL_RESERVED

當REQUEST_FAILURES大於0並且不斷增長(ORA-4031),可以相應增大SHARED_POOL_RESERVED_SIZE & SHARED_POOL_SIZE的值

REQUEST_MISS = 0並且不再增長或FREE_MEMORY>=50%*SHARED_POOL_RESERVED_SIZE,考慮減少reserved size

SHARED_POOL_RESERVED_SIZE初始為SHARED_POOL_SIZE的10%

(3) 關於資料字典cache

特性: 啟動時任何sql語句都將導致cache miss, GETMISSES幾乎不可能為0

調整: 調整SHARED_POOL_SIZE的大小而間接地調整dictionary cache

report.txt中: GET_MISS/GET_REQS < 15%

字典: V$ROWCACHE -- SUM(GETMISSES)/SUM(GETS) < 15%, 否則應增大SHARED_POOL_SIZE

(4) 關於UGA

包括: User Session Data(sort area & private SQL area) 和 Cursor State, MTS中建立

儲存: 專用伺服器--&gtPGA, MTS--&gtshared pool, 使用MTS的總記憶體<=使用專用伺服器記憶體

調整: 查閱檢視V$MYSTAT,( V$STATNAME, V$SESSTAT 查詢space usage for MTS user),計算: SUM(value)

(5) 關於Large Pool

特性: LARGE_POOL_SIZE需明確設定,若未設定使用shared pool分配

The Oracle library cache and buffer cache will never allocate memory from the large pool

用途: I/O服務程式/oracle備份回覆/MTS/並行操作(PARALLEL_AUTOMATIC_TUNING=TRUE)

字典: v$sgastat

6. Tuning the Buffer Cache 7

(1) 關於命中率

公式：Hit Ratio = 1 – (physical reads/(db block gets + consistent gets)) -- 分母是request的總數,包括記憶體和磁碟讀取

-- 因為這些統計資料是例項啟動後收集的,所以不要啟動後立刻進行計算,因為這時buffer cache可能是空的檢視: V$SYSSTAT( name,value)

指標: Hit Ratio應>=90%, 否則需要增加DB_BLOCK_BUFFERS

(2) 關於 Multiple Buffer Pools

-- There are at least 50 blocks per latch

-- 總數不能超過DB_BLOCK_BUFFERS 和 DB_BLOCK_LRU_LATCHES ,否則mount時候出錯

-- 三種

KEEP: 儲存最有可能重用的object

RECYCLE: 儲存很少被重用的object

DEFAULT: 始終存在,大小等於單個buffer cache, 尺寸定義=DB_BLOCK_BUFFERS-其它buffer

-- V$BUFFER_POOL_STATISTICS: consistent gets statistics for multiple buffer caches

-- 如何計算KEEP buffer pool的資料

ANALYZE ... ESTIMATE STATISTICS

獲取objects的大小:將DBA_TABLES, DBA_INDEXES, and DBA_CLUSTERS中的blocks相加

(3) 關於LRU Latches

特性: 每個latche最少控制50個buffer, 最小=1,預設=1/2*CPU, 對每個DBWn程式有一個Latch,命中率應>=99%

檢視: V$LATCH and V$LATCHNAME

引數: DB_BLOCK_LRU_LATCHES(單cpu系統中,不要超過CPU,在多buffer pool中)

(4) 關於 Free List

特性: freelist決定哪個block可以用於insert

檢視: V$SESSION_WAIT class = 'segment header'

DBA_SEGMENTS segment當前存在的freelist的數量

V$WAITSTAT SELECT class, count, time FROM v$waitstat WHERE class = 'segment header';

V$SYSTEM_EVENT event='buffer busy waits'

解決競爭的三個步驟:

1. 查詢V$SESSION_WAIT,獲取FILE, BLOCK, and ID

2. 查詢DBA_SEGMENTS和V$SESSION_WAIT,獲取發生競爭的segment資訊

3. 重建object,增加freelist

(5) V$CACHE 與 V$BUFFER_POOL 的區別

-- V$CACHE: 監控每個object佔用的buffer pool block數量/由catparr.sql建立/用於OPS

to determine the number of blocks in the RECYCLE buffer pool

-- V$BUFFER_POOL: Describes multiple buffer pools

(6) 關於table cache的說法

目標: 對未cache的table進行full scan,block都在MRU端,可以cache table放在LRU端

方式: 1. Create a table using the CACHE clause -- create table ....cache/nocache

2. Alter a table using the CACHE clause -- alter table ... cache/nocache

3. Code the CACHE hint clause into a query -- 查詢中使用cache提示

注意: 過多的cache table可能使buffer cache過分擁擠(overcrowd)

7. Tuning the Redo Log Buffer 3

(1) 怎樣設定redo log file可以加快ARCHIVELOG MODE下資料庫的恢復速度?

-- Create small redo log files 增加歸檔的次數

(2) 關於調整redo log buffer

V$SESSION_WAIT: 事件: 'Log Buffer Space'說明空間等待(寫入log buffer比LGWR寫出快)

解決: 增加引數LOG_BUFFER,將log buffer移到更快的disk

V$SYSSTAT 事件: 'redo buffer allocation retries'說明新的entries寫入覆蓋已寫入disk的entries的空間等待

'redo log space requests'說明活動log file寫滿,等待Oracle server磁碟空間分配

'redo entries'上述'redo buffer allocation retries'/ 'redo entries'應<=1%

解決: 增加log buffer/improve checkpoint 或歸檔程式

V$SYSTEM_EVENT 事件: 'log file switch completion'說明LOG SWITCH的wait

事件: alert.log檔案中有"CHECKPOINT NOTCOMPLETE.",說明LGWR等待DBWn完成一個CHECKPOINT

'Log File Switch (Checkpoint Incomplete)'

解決: 調整引數LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT

Check the size and number of redo log groups

(3) 關於NOLOGGING

可以使用NOLOGGING模式的SQL語句: CTAS/CREATE INDEX/ALTER INDEX REBUILD

DRICT LOAD Sqlloader direct path: Set the NOLOGGING attribute.

(4) REDO LOG BUFFER的特性

-- 引數LOG_BUFFER決定尺寸,大小必須是OS block size的倍數,一般為最大block size的4倍

-- Frequent COMMIT statements lead to a smaller buffer size requirement

-- Larger redo log buffer sizes reduce log file I/O

-- The tuning goal for the redo log buffer is to ensure that there is sufficient space for the server processes.

-- redo log buffer 分配記憶體過多將減少分配給其他areas的記憶體

(5) V$SESSION_WAIT中欄位 SECONDS_IN_WAIT 的資訊說明了什麼?

-- 指明等待空間的時間(由於log switch未發生) -- buffer填充比LGWR寫出快

-- 也指明瞭redo log file所在的disk I/O競爭

8. Database Configuration and I/O Issues 7

(1) Alert.log file 出現資訊:"Checkpoint not complete; unable to allocate file."意味著什麼?

-- LGWR waited for the checkpoint to finish.

(2) 影響Full table scan I/O的引數是什麼?

-- DB_FILE_MULTIBLOCK_READ_COUNT

(3) Local Managed tablespace的特性和優點

-- 沒有字典, 很少出現一致性問題, 可以有上千個extent而不涉及效能問題不必重組, Extent分配資訊存在表空間本地(bitmap)

(4) 檢視V$FILESTAT的用途

-- 監控每個磁碟檔案的disk I/O 活動情況和和物理讀寫情況

(5) 關於表空間的一些特點

-- 表和索引分表空間存放,使用者不指定表空間(包括臨時表空間)將使用system表空間

-- RBS僅用於存放rollback segment

-- system表空間僅包含屬於sys使用者的objects,其它使用者應不允許在system表空間中建立object

(6) 條帶化檔案的引數和手工命令

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