oracle10G数据库性能监控与优化

1、性能监控与优化ORACLE10G 数据库 联通事业部-系统平台-刘旭请献上你的问题！ 为什么sql在测试库很快，上了生产却很慢？ 为什么迁移数据这么久？ 为什么只更新一条记录等了5分钟还在更新？ 为什么BSS BOSS系统又堵了？ 为什么数据库有如此多的活动session？ 为什么count(*)一张小表这么慢培训目的： 能够确保更新到生产的sql语句高效 能够通过v$session视图定位数据库的繁忙程度 能够通过AWR报告准确定位系统的性能瓶颈，通过反复优化循序渐进的提高bss、boss系统在数据库上的性能培训目标： 短时间内（5-10分钟）快速定位数据库性能瓶颈（具体到sql） 通过AW

2、R分析过去某个时间段数据库的性能瓶颈 熟知在数据迁移过程中如何提高性能，从而缩短数据迁移时间 熟知数据库各种等待事件背后的原理。交通事故？交通事故？车多缓行？车多缓行？数据库性能突降（阻塞） 阻塞的标志字段（blocking_session） 如果blocking_session的状态是活动（active） 优化语句或根据当时情况决定是否kill 如果blocking_session的状态是不活动的（inactive） 超过一定时间，kill掉，找原因(是jdbc、tuxedo、还是终端工具操作忘记commit) 动态视图：v$session blocking_session=v$sessio

3、n中(sid)模拟阻塞数据库性能突降（无阻塞，只是活动session多） 查询v$session中event字段(都有哪些等待事件)l latch: cache buffers chainsl read by other sessionl db file scattered readl enq: SQ - contentionl log file syncl enq: TX - row contentionl PX Deq: Execution Msglatch: cache buffers chains(热块)极短的时间内对少量数据块进行了过于频繁的并发访问低效的SQL内存 磁盘 内存哈希表

4、遍历表PCTFREE表分区read by other session 当一个会话试图修改一个数据块，但这个数据块正在被另一个会话修改时。 当一个会话需要读取一个数据块，但这个数据块正在被另一个会话从磁盘读取到内存中时Oracle 操作的最小单位是块（Block），即使你要修改一条记录，也需要对这条记录所在的这个数据块做操作。 当你对这个数据块做修改时，其他的会话将被阻止对这个数据块上的数据做修改（即使其他用户修改的不是当前用户修改的数据），但是可以以一致性的方式读取这个数据块（from undo）。当前的用户修改完这个数据块后，将会立即释放掉加在这个数据块上的排他锁，这样另一个会话就可以继续修

5、改它 什么是全表扫描 db file scattered read（全表扫描） 全表扫描 = 慢吗？ 只有20条数据为什么count(*) 需要20秒甚至更久？顺序地读取分配给表的每个数据块，直到读到表的最高水线处。一个多块读操作可以使一次I/O能读取多块数据块(db_file_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作 enq: SQ - contention（序列CACHE）赋予了CACHE 属性的Sequence

6、调用nextval 期间，许多会话同时访问nextval 值。需要将数据字典信息物理修改后，再次执行CACHE 的工作select sequence_val from dual并发log file sync-事务commit 当一个会话发出一个commit命令时，LGWR进程会将这个事务产生的redo log从log buffer里面写到磁盘上。 会话发出的commit指令后，需要等待LGWR将这个事务产生的redo 成功写入到磁盘之后，才可以继续进行后续的操作，这个等待事件就叫作log file sync。当系统中出现大量的log file sync等待事件时，应该检查数据库中是否有用户在做

7、频繁的提交操作。enq: TX - row contention 表索引多吗？ 更改相同的值吗？ （blocking_session） delete、update、insert频繁吗？PX Deq: Execution Msg 表或索引的degree属性 查询指定并行（select /*+ parallel(a 6)*/） from tab a ;等待事件（总结）l latch: cache buffers chainsl read by other sessionl db file scattered readl enq: SQ - contention cache参数设置l log fil

8、e sync 事务频繁度、存储性能l enq: TX - row contention pctfree参数 SQL语句慢RAC Cache Fusion（内存拷贝）-补充v$event_name什么是 Cache Fusion9i之前有Cache Fusion吗应用分开终端用户避免交叉连接不同实例 相关的等待事件回顾等待事件大数据量迁移例句：insert /*+append*/ into tf_f_user partition(p1) select /*+parallel(a 6)*/* from tf_f_userdblink a 开始迁移数据 关闭数据库归档 关闭数据库自动分析 数据表空间

9、足够大 回滚段表空间足够大 迁移前索引失效 迁移后索引有效 手工表分析 启用数据库自动分析 开启数据库归档 ORA-01555：查询结果被覆盖了！查询语句太慢？undo太小？回滚段(undo) ORA-01555 回滚段（undo）原理：没有commitinsert ：存放数据的rowid（反操作delete）update：存放update前的数据delete ：存放整条语句(反操作insert)已经commitundo将已经提交的数据标记为可以被覆盖压测时数据库性能 数据库中所有序列的cache值足够大 enq: SQ - contentionalter sequence owner.seq

10、uence_name cache 4000;表和索引的degree 改成1(测试) PX Deq: Execution Msg create table tf_f_user (.) parallel 6; alter table tf_f_user parallel 6; alter index owner.index_name rebuild parallel 6; create index owner.index_name paralllel 6;高水位标记(High Water Mark) create table t_a as select * from dba_objects;ins

11、ert into t_a select * from t_a(反复多次保证足够的数据量);commit;select count(*) from t_a ;delete from t_a;commt;select count(*) from t_a; 降低高水位标记 alter table t_a move tablespace users ; 重建索引 alter index idx_name rebuild ; alter index idx_name noparallel ;索引的性能快1、搜索索引中列中的ROWID2、根据ROWID定位到具体的数据块3、把数据块中所需的数据返回ROWI

12、Dselect rowid,a.* from UCR_BUSI_97.tf_b_trade a where rownum 2-3-43-4select * from table where col_name=张三select * from table where col_name=:v_name1次10次1000次1百万次1千万次.表分析 开启、关闭自动分析 不分析会发生什么？ 什么时间？谁分析？ HINT 提示 与统计信息SQL优化-建议预防是更好的优化方式where条件中所有字段的不同值dba_tab_col_statisticsselect column_name,count(*) fr

13、om table_name group by column_name确保表的分析时间为最新dba_tables(last_analyzed)尽可能少建单个索引，合并成符合索引在生产环境中查看执行计划的cost总值(table access full、index range scan 对应的csot)sql上线前需dba和开发人员集合业务特点、执行频率、执行时间、语句本身给出建议割接期间数据库需要做什么 停止数据库自动分析 数据迁移完毕后，分析被大批量insert、update、delete的表 启用数据库自动分析 编译数据库中所有的失效对象 确保数据库中表空间有足够的空余空间（system、

14、sysaux、undo、临时表空间）AWR:Automatic Workload Repository 什么是AWR? AWR和STATSPACK的关系？boss1.0boss2.0bss4.0bss4.2AWR: 疑问 AWR采样频率和保留时间-保留3天= 3*24*60dbms_workload_repository.modify_snapshot_settings(retention = 4320,interval = 30) AWR生成的数据保存在哪个表空间？ AWR相关的数据字典 （dba_hist_active_sess_history、dba_hist*） 怎样生成AWR$ORA

15、CLE_HOME/rdbms/admin/awrrpt.sql或Toad工具AWR:解读Elapsed: 数据库运行时间（分钟）DB Time: 反映了数据库的繁忙程度 7763/(179cpu个数)/100%AWR:解读Buffer Cache: 允许加载到内存中的数据库Shared Pool Size: 数据字典+最近解析（或编译）后的SQL、PLSQLAWR:解读% Blocks changed per Read：说明10.65%的逻辑读是用于那些只读的而不是可修改的块，该系统只更新90%的块。 Rollback per transaction %：事务回滚的百分比AWR:解读In-me

16、mory Sort %：在内存中的排序率。 Library Hit %：主要代表sql在共享区的命中率，通常在95%以上，否则需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。 Soft Parse %：近似看作sql在共享区的命中率，小于95%,需要考虑到绑定，如果低于80%，那么就可能sql基本没有被重用。 Execute to Parse %：一个语句执行和分析了多少次的度量。在一个分析，然后执行语句，且再也不在同一个会话中执行它的系统中，这个比值为0。AWR:解读Buffer Nowait %：在缓冲区中获取Buffer的未等待比率，Buffer Nowait99%，否则存在严重的性能问题，比如绑定等会影响该参数。 Parse CPU to Parse Elapsd %：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。如果该比率为100%，意味着CPU时间等于经过的时间，没有任何等待。 % Non-Parse CPU：太低表示解析消耗时间过多。与PARSE_CPU相比，如果TOT_CPU很高，这个比值将接近100