oracle tune

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[@more@]