1z0-024考点总结

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(GETHITRATIO90%,否则优化语句应用; 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

• ·OCP1Z0-025考试心得汇集
• ·1Z0-025经典考试回忆
• ·新大学日本语第三册(第2版)(附盘)|报价￥2
• ·OCPDBA学员一周考试情报
• ·OracleFAQ
• ·001考试笔记PartI
• ·水谷信子实用日语教程-读解日语(下)(附带)|
• ·001考试笔记PartII
• ·今天通过了026。谈谈感觉。
• ·我学Oracle和考OCP的经验