1z0-024考点总结
添加时间: 2008-4-21 21:07:25 作者: Oracle指导 阅读次数:53 来源: http://www.d9soft.com
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) 条带化文件的参数和手工命令
-- DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE
-- CREATE TABLE / ALTER TABLE ALLOCATE
(7) 检测Full table scan的视图
-- V$SYSSTAT where name name like '%table scans%'
-- V$SESSION_LONGOPS
(8) 关于redo log的配置
-- 归档日志不能放在raw设备上
-- 定义多个归档进程并写在不同目标磁盘上
-- LGWR与ARCn分磁盘
(9) 决定参数DB_BLOCK_MAX_DIRTY_TARGET参数是否合适的事件和视图是什么?
-- V$SYSTEM_EVENT的"WRITE COMPLETE WAITS"事件
(10) I/O slaves是模拟异步I/O, 提供nonblocking I/O应用
9. Using Oracle Blocks Efficiently 4
(1) Row Chaining和Row Migration的特点
-- 行链接: Insert or Update引起,一个Row存在不同的block中, 可以通过设置大的DB_BLOCK_SIZE来减少行链接的发生,但无法避免(LOB)
-- 行迁移: Update引起,由于PCTFREE过小,ROWID未变, 可以通过增大PCTFREE的值减少行迁移的发生
-- 影响: 产生额外的I/O
(2) Block size大小的优缺点
-- Large: 有益于very large rows and sequential reads, 管理 代价小, 适合DSS
-- Small: 有益于small rows(减少block争用),random reads(内存中reuse机会少), 管理代价高, 适合OLTP
(3) 排除行迁移的步骤 eliminate row migration
1. ANALYZE TABLE ... LIST CHAINED ROWS;
2. Copy the rows to another table.
3. Delete the rows from the original table.
4. Insert the rows from step 2 back into the original table.
(4) 如何发现和 检查 行迁移/行链接 -- ANALYZE table COMPUTE STATISTICS;
(5) 分析索引的命令和视图?
-- ANALYZE INDEX index_name VALIDATE STRUCTURE;
-- INDEX_STATS
(6) 大的Extents的优缺点
-- 优点: 避免动态扩展,减少multiblock reads
-- 缺点: 不易获得连续的空间, 初始时浪费空间
-- 其它: 全表扫描和索引查询的性能不受extent的个数影响
(7) 什么时候需要考虑PCTFREE和PCTUSED的更改
-- PCTFREE: 需要经常更改的row,考虑增大,对DSS,可以考虑设置=0
-- PCTUSED: 可能需要删除的row,考虑增大
(8) HWM的特点
-- 初始时设置,存储在segment的header block中
-- ALTER TABLE DEALLOCATE UNUSED... 回收HWM之上的空间, TRUNCATE回收全部
-- 全表扫描从first block --> 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),比例(<5%)
(4) 计算并行查询所需要的内存数量的公式? -- SORT_AREA_SIZE * 2 * degree of parallelism
(5) 如何设定和更改SORT_AREA_SZIE的值?
-- INIT.ORA中定义
-- ALTER SESSION or ALTER SYSTEM DEFERRED
(6) 关于参数SORT_MULTIBLOCK_READ_COUNT
-- 缺省=2
-- 该参数意味着: 每次sort从a temporary segment中读取的block的number
-- 提高此值可以改善sort
11. Tuning Rollback Segments 4
(1) ORA-01555: snapshot too old 错误的原因
-- 正在查询的block已经被重用了,此block的SCN比查询开始时的SCN新
-- 在回滚段header中的transaction slot已经被重用了
-- 回滚段中的undo data在一个提交后被覆盖了(an Interested Transaction List (ITL) entry in a data block has been reused)
(2) 回滚段的特性
-- STORAGE: INITIAL=NEXT(128K分界),PCTINCREASE=0,MINEXTENTS=20,MAXEXTENTS=UNLIMITED,RBS表空间动态扩展
-- 循环方式,不能跨越,自动扩展,设定OPTIMAL回收
-- DELETE需要保存整个记录,INSERT只需ROWID,INDEX列需要old data,old index,new index
(3) 控制redo的命令? -- SET TRANSACTION USE ROLLBACK SEGMENT ...
(4) 回滚段调整的目标 Tuning Goals
1. 事务无需等待回滚段的访问,需要等待足够的回滚段
2. 在正常运行中,回滚段不要扩展
3. 无论是大的还是特殊的事务,都不应因回滚段空间的不足而失败
4. 数据读取总是能从read-consistent images中获取需要的内容
(5) OLTP事务的回滚段 管理
一般事务较小,所以分配小的回滚段
一般规则: 决定于并发工作量(一个concurrent job一个回滚段),一个RBS 4 个事务,最多200个user
(6) 查询哪个视图可以获得事务写入回滚段的字节数量? -- V$ROLLSTAT
(7) 标识回滚段header竞争的三个视图:
-- V$ROLLSTAT.WAITS/GETS>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()
上一篇文章: ORACLE考试 Oracle8i: 性能及其调优 大纲 下一篇文章: ORACLE常见错误代码的分析与解决
相关软件: