DBA Workshop.ppt

Statspack报告—构成内容 Cache size Load profile Instance efficiency Percentages Top timed event Top SQL Instance Activity statistic tablespace IO stats for DB file IO stats for DB buffer pool statistics for DB PGA Aggr Target Stats for DB 生成报告 — Context/Cache Sizes DB Name DB Id Instance Inst Num Release Cluster Host HAW1 haw1 1 .0 NO HAWKING Snap Id Snap Time Sessions Curs/Sess Begin Snap: 32 24-Oct-02 16:45:54 10 5.2 End Snap: 33 24-Oct-02 16:46:32 10 5.2 Elapsed: 0.63 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 36M Std Block Size: 8K Shared Pool Size: 12M Log Buffer: 512K 生成报告 — Load Profile 根据v$sysstat 得到的instance负载 与之前的报告进行比较，预先发现问题- IO是否显著增加了? 随软硬件配置不同而变化，无固定上限– 粗略规范为 Logical reads 10,000 per 100MHz CPU per second Physical reads 100 per disk per second Hard parses, soft parses 100, 300 per second 着重检查parse (可调整 cursor_sharing and session_cached_cursors参数) 和 IO 生成报告 — Instance Efficiency Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %:100.00 Redo NoWait %:100.00 Buffer Hit %: 99.98 In-memory Sort %: 99.48 Library Hit %: 76.14 Soft Parse %: 5.00 Execute to Parse %: 4.37 Latch Hit %:100.00 Parse CPU to Parse Elapsd %: 97.73 % Non-Parse CPU: 23.35 Underlined items have good corresponding wait events Shared Pool Statistics Begin End Memory Usage %: 94.08 93.54 % SQL with executions1: 76.37 54.90 % Memory for SQL w/exec1: 62.10 61.01 生成报告 — Instance Efficiency 如果统计收集时间过短，或者数据库重启，可能得到错误的命中率 粗略的命中率范围 90-100% Buffer/redo nowaits, Latch, Sorts 50-100% Library Cache 0-100% Parse, Buffer Hit 结合命中率和等待事件进行分析 Shared pool usage 应稳定在 80-90%