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中创建

存储: 专用服务器-->PGA, MTS-->shared 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) 条带化文件的参数和手工命令