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竞争应 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应=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 PGA, MTS-->shared pool, 使用MTS的总内存=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'应 HWM
(9) REBUILD索引的一些特点
-- REBUILD比DROP/RECREATE速度快
-- UNRECOVERABLE 与 LOGGING 不兼容
-- 若delete entries占当前entries20%以上,则可重建索引
10. Optimizing Sort Operations 4
(1) 需要进行排序的操作有哪些?
-- Index creation -- 在building B-tree之前sort indexed values
-- Parallel insert operation involving index maintenance -- 并行插入操作涉及索引维护
-- ORDER BY or GROUP BY clauses
-- DISTINCT values selection -- 为排除重复行
-- UNION, INTERSECT, or MINUS operators -- 排除重复行
-- Sort-merge joins -- 执行一个涉及到两个表连接,而连接字段没有索引
(2) 参数SORT_AREA_RETAINED_SIZE的作用是什么?
-- 排序操作完成后保留的sort area大小
-- 大于该参数部分的内存释放返回给UGA
(3) 涉及sort信息的几个相关视图
-- V$SORT_SEGMENT 显示active individual sort segments上的用户数量,扩展extents的数量
-- V$SORT_USAGE 显示临时段正在使用的情况,和V$SESSOIN结合查询经常使用较大临时段的USER
-- V$SYSSTAT 显示所有sorts (memory),sorts (disk),sorts (rows)的数量(since instance startup),比例(1%(5%)
-- V$WAITSAT.COUNT中CLASS='undo header'的值0
-- V$SYSTEM_EVENT.EVENT='Undo Segment Tx Slot'(也可能是buffer cache过小)
(8) 为什么在设置回滚段存储参数时NEXT=INITIAL?
-- 因为回滚段采用循环使用的方式,大的事务可能用到小的回滚段,一段时间后将同样大小 ???
12. Monitoring and Detecting Lock Contention 5
(1) 死锁的特点和解决
-- 两个以上用户互相等待对方锁定的数据资源
-- Oracle自动检测deadlock并通过回滚检测出的deadlock的语句来解决死锁,但注意,仅回滚了语句而没有回滚该事务,返回ORA-00060错误
下一步应当手工回滚剩余的事务
-- DeadLock更多地发生在事务明确地覆盖了Oracle Server的缺省锁, 对分布式deadlock,处理同非分布式相同
-- DeadLock检测出后,会记录在USER_DUMP_DEST下的trace file中, 包括lock的row id
-- ALTER TABLE ... ENABLE/DISABLE TABLE LOCK; -- 在并行服务器中,允许/禁止DML,DDL,LOCK on a table
(2) Lock相关的两个表的用途
V$LOCK -- 显示回滚段和slot的数量以及修改的表的ID for 当前hold的lock
V$LOCKED_OBJECT -- 显示当前hold的lock的object ID
(3) 释放锁涉及的视图和命令
-- 查询V$SESSION获得: SID,SERIAL# / 引起竞争的row
-- ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
-- COMMIT & ROLLBACK
(4) 导致锁竞争的几种情况
-- users who do not regularly commit changes
-- developers who code unnecessarily long transactions
-- developers use unnecessarily high locking levels.
13. SQL Issues and Tuning Considerations for Different Applications 5
(1) Bitmap索引和B-tree索引的比较
B-tree Bitmap
-------------------------------------------------------------------------------------------
Suitable for high-cardinality columns Suitable for low-cardinality columns
Updates on keys relatively inexpensive Updates to key columns very expensive
Inefficient for queries using OR predicates Efficient for queries using OR predicates
Row-level locking Bitmap segment-level locking
More storage Less storage
Useful for OLTP Useful for DSS
适合查询表的一小部分(<5%)
(2) 优化器的稳定性特点
-- 重写sql
-- 创建包含hints的stored outline
-- 应用中强制访问路径
-- SQL语句尽量保持匹配
(3) 为什么应用SQL的tuning很重要?
-- 应用中的语句影响最大, 如果它包含低效率的SQL语句,那么底层的tuning没有什么效果
(4) OLTP和DSS的比较,理解什么样的应用属于什么样的系统
OLTP DSS
-------------------------------------------------------------------------------------------
Performs index searches More full table scans
Uses B-tree indexes Uses bitmap indexes
Uses reverse key indexes Uses IOT tables
Needs more, small rollback segments Fewer, large rollback segment
Should not use parallel query Employs parallel query for large operations
PCTFREE according to expected update activity PCTFREE can be set to 0
Shared code and bind variables Literal variables and hints
Uses ANALYZE indexes Histograms generation
(5) TKPROF的用途?
-- 格式化SQLTRACE session的output
(6) 使用cluster的好处?
-- 减少存储
-- 减少disk I/O和访问时间
(7) What should be the tuning priority for a DSS application?
-- Use the optimal access path in the execution plan.
(8) CBO的特点
-- 统计驱动
-- 优化器检查每个SQL,识别所有可能的访问路径并计算各自的成本,选择最少的逻辑读数量
(9) Reverse Key Index在什么情况下适用?
-- 一直顺序增长的(ever-increasing)key,如:订单号,对指定范围的区域扫描不合适
(10) IOT有哪些特点
-- 主键值没有重复存储(索引和表在一起),节省空间
-- 对等值或区间查询可提供快速的key-based访问
-- 存储整个row
-- 使用row overflow区存放nonkey值
(11) 对于混合系统的推荐设置
-- 配置不同的回滚段
-- 设置两套参数,放在不同的pfile中
-- MTS可在高峰时间使用(peak-time),not for DSS
14. Managing a Mixed Workload 2
(1) Resource Manager包含的内容:
-- Resource Plan
-- Resource consumer groups
-- Resource plan directives
(2) 数据库建立时的缺省资源计划为: SYSTEM_PLAN
(3) 初始化参数 RESOURCE_MANAGER_PLAN 设置的资源计划名称必须在数据库中存在,否则报错,需要shutdown,可用ALTER SYSTEM修改并立即生效
(4) 过程包DBMS_RESOURCE_MANAGER的几个过程
-- CREATE_PENDING_AREA() 一个instance同一时间点只能建立一个pending area
-- CREATE_CONSUMER_GROUP()
-- CREATE_PLAN()
-- VALIDATE_PENDING_AREA()
-- SUBMIT_PENDING_AREA()
-- GRANT_SWITCH_CONSUMER_GROUP() SET_INITIAL_CONSUMER_GROUP()
-- SWITCH_CURRENT_CONSUMER_GROUP() SWITCH_CONSUMER_GROUP_FOR_SESS() SWITCH_CONSUMER_GROUP_FOR_USER()
(5) 当前数据库RM设置的几个视图:
-- V$SESSION.RESOURCE_CONSUMER_GROUP 显示session的当前group
-- V$RSRC_PLAN 显示获得的资源计划
-- V$RSRC_CONSUMER_GROUP 包含资源消耗组的统计信息
15. Tuning with Oracle Expert 1
(1) In addition to the rules, what else is the basis for an Oracle Expert tuning session?
-- scope defined at the beginning of the tuning session
(2) Oracle Expert为生成建议需要收集的数据是什么?
-- data in the Schema class
(3) 如何执行Oracle Expert生成的调整建议?
-- 使用Oracle Expert生成的.ora参数文件替换init.ora文件来执行建议
-- 为执行推荐的SQL语句和结构, 检查Oracle Expert生成的.txt文件,将包含string TBS的地方替换成相近的信息
(4) 几个class的特性
-- Database class 从DBA_%,V$视图中收集数据
-- Instance class 高峰时期采集样本数据,推荐收集10个以上的样本,以获取更好的调整建议
-- Collection Schama class 用于优化访问schema的路径
– Storage optimization of tables, indexes, and clusters
– Index structuring
– Database file I/O distribution
– Table, index, and cluster placement
-- Environment class 不能自动收集,需要: 手工录入/.xdl导入
-- Workload class 描述数据库日常使用情况,事务请求等, 从:sql cache/sql history/.xdl from previous tuning session
(5) Oracle Expert提供的reports包括:
-- Session Data report 提供关于一个存在repository中的tuning session的收集数据的明细信息
-- Analysis report 描述Oracle Expert生成的建议的基本理由,它一直存在repository中,直至生成另一个分析报告
(6) Oracle Expert采集的数据类别: -- Instance/Application/Structure/Database/Schema/Environment/Workload
(7) Oracle Expert 的特性
1. 设定tuning session的范围
2. 采集数据: 获取综合的信息,包括database, instance, schema,environment, and workload.
3. Viewing and editing the collected data and rules -- The data is organized in a hierarchical format.
4. Analyzing the data to generate recommendations -- 在收集并按需要编辑了数据之后,Oracle Expert分析并生成调整建议
5. Reviewing the Oracle Expert recommendations -- 根据调整建议取舍某部分,重新生成新的调整建议报告
6. Generating scripts for implementing the recommendations -- 确定调整建议后生成参数文件和SQL脚本日后运行
16. Multithreaded Server Tuning Issues 2
(1) 几个相关视图
-- V$CIRCUIT virtual circuits的信息,用户通过调度进程和服务进程连接到数据库的信息
-- V$QUEUE 包含多线程信息队列的信息,用于检查共享服务上是否有竞争(sum(wait)/sum(totalq),单位1/100秒)
-- V$DISPATCHER 调度进程的信息,检查busy rate(sum(busy)/(sum(idle)+sum(busy))应小于50%,否则需增加dispacher)
-- V$SHARED_SERVER 正在运行的共享服务进程的信息
(2) MTS的特性
-- 提供一个共享进程的方式,但不能提高执行速度,可以支持更多的并发用户访问数据库
-- Shared servers use the UGA for sorts, 如果配置large pool,UGA is stored in the large pool
-- MTS is not intended for batch processing or decision support. 并非用于批量处理和DSS
(3) 限制Oracle server动态启动共享服务数量的两个参数是什么?
-- PROCESSES
-- MTS_MAX_SERVERS
(4) 参数MTS_SERVERS设置了shared servers的个数,当共享服务idle时能否删除?
-- 不能,只有MTS_SERVERS较小而Oracler Server自动启动的共享服务进程在idle时才会自动删除
17. Workshop 0
|
