Oracle性能优化培训PPT

•
103
防止索引失效
• • • • • • • 尽量让搜索表达式放在比较数值那边，可以让搜索走索引搜索，避免全表扫描 查询条件时，比较类型不相同，不走索引 类型转换时以转到数字优先，可显式把比较 数值转成与字段相同的类型 查询数据选取范围过大时自动进行全表扫描（一半范围不超过全范围的一半） 数据类型字段用like的时候会把字段先转成字符串再比较，导致不走索引 不等于符号（!=、<>）不走索引 可以用大于号加小于号一起使用 空值没有索引记录 isnull和notnull不走索引 可用特殊值代替空值 复合索引的时候，把查询中出现最多的字段作为前导字段
• 应用设计 – 设计调优 – 应用调优 • 数据库调优 – 内存调优 – I/O调优 – 表空间 – 锁竞争 • 系统调优 – 操作系统调优
6
基本步骤
7
主动式调优
8
1、了解业务特性和优化需求
9
主动式调优
10
OLTP系统
11
特点
• 高并发 • 高吞吐量 • 连续增加数据
12
调优目的
• 高可用性 • 高并发下的高速 • 并发性
• 避免排序
– 用union all代替union – 使用表的连接使用基于索引的访问 – 选择需要的列进行分析 – 对于大对象的分析使用estimate而不是 compute
59
7、分步实施
60
8、重新收集性能信息跟踪调优 结果
61
被动式调优
62
参与调优人员
• • • • • 业务专家+技术专家做需求控制 有丰富开发经验的DBA 系统管理员 系统架构 开发人员（规范）
• •
95
优化工具 (2)
• SQL Access advisor
– 给出索引方面的建议
96
算法
97
数据访问方法
• 簇表(Clusters) • 索引组织表(index-organized tables)
98
索引
99
基于函数的Function based
100
访问方法
• index rowid scan
– 所选择字段没有索引，会根据查询字段在索引 中找然后回表中根据rowid查记录
• fast index scan
– 所选择字段都建有index，可以只在索引中取数 据
• skip index scan
– 跃式扫描：根据有索引的字段跳跃查找没有索 引的查询字• • • • 只有经常在表中查找少量数据的时候才建索引 经常作为查询条件的字段，唯一值较多的字段 重复性多的字段建索引没作用 全包括的组合索引级别高于单列索引，半包括的则低于单列 唯一性索引和字段的增删改操作会慢，如果不是查询字段，不适宜做索 引 经常做函数条件查询时可以建立 函数索引 查询记录数占总记录数超过2%的时候，不建议走索引查询 不在大字段上建索引
19
日志产生量控制
20
存储对象
21
减少动态分配
• 段、区、块
22
initrans要设置的足够高
23
块不宜过大，pctfree不能太小
24
索引
• 不要太多， B-tree比bitmap更好 • 有规则的重复索引 – 索引是有序的，索引字段上更新会导致索引松散 • 外键字段上考虑建索引 – a no b no SM – a no b idx NL(a大b小错/a小b大对) – a idx b idx NL(a大b小 from a.b对) – 结论：在大表建索引、两表都有索引时查询从大到小
76
CBO（好）
• 根据资源消耗决定用什么方法查询(costbased) • 统计(statistics)驱动 默认每天晚上10点自动 增量收集信息
77
修改模式:alert session set optimerzmode=....
78
影响其他的index查询速度
79
索引等级
• 唯一索引 • 不唯索引
存储对象
• 仔细设置BLOCKSIZE和 DB_FILE_MULTIBLOCK_READ_COUNT • 确保区的大小是参数乘积的整数倍 • pctfree可以设为0 • 存储上I/O分散 • 基于冗余字段的多表联接，使用聚簇表 • 大量使用分区和分区索引
32
索引的使用
• 尽可能使用位图索引 • 对于按照主健执行的范围查询，使用索引 组织表 • 对非均匀分布的索引列一定要生成柱状图 （决定索引方法）
49
考虑SQL的重用
50
优化SQL的书写
51
调优其他部分以选择合理执行 计划
52
基本原则
• 降低逻辑读 – select department_id, job_id, count(*) from emploee rollup(department_id,job_id) – rollup((a,b)) : ab all – rollup(a,(b,c)): abc a all – rollup(a,b,c): abc ab a all – cube(a,b,c):abc ab bc ac a b c all • 合理使用索引 • 使用合理的表连接方式 • 减少不必要的排序 • 减少重分析 – SQL规范
33
2、建立合理的性能优化目标
• 减少或消除等待事件 – where (a,b)=(select a,b from tab where id=10) – where (manager_id,department_id) in (select managerid,department_id from emp where emp_id in (100,200)) – update ... • 访问最少的块数 • 将数据块保留在内存中 • 响应时间 – 需要考虑全系统因素 • 提高吞吐量（单位时间的处理能力） • 提高负载量（并发访问量）
87
数据库配置
• 资源
– 存储资源 – 系统配置 – 网络资源
• 数据库创建
– 用户 – 表空间 –表 – 索引
88
部署应用
89
调优时间
90
预定义调优
• 从项目立项到上线前 • 性能测试 • 压力测试
91
主动式调优
• 周期性收集性能信息
– 分析系统调优
92
被动式调优
• 项目运行出现故障后
27
数据仓库系统
28
特点
• 批量数据装载 • 海量数据查询 • 大量全表扫描的使用
29
调优目的：快速的响应时间
30
调优分析
• 不用RAC • 主机资源
– 多CPU高主频 – 高I/o能力
• • • • •
大池日志缓冲区要大 关闭归档 少用约束条件 考虑使用预处理机制 考虑多层数据中心架构
31
80
调优阶段
81
需求分析和架构设计
• 需求管理，答应需求时先评估性能影响 • 空间位置数据(orcale有专门的工具) • 入库数据类型分类
82
应用设计和开发
83
数据库架构设计
• 容灾 • 高可用 • 读写分离
84
应用模块设计
• 开发语言选择 • 应用算法选择 • 算法实现位置
85
ER模型设计
– 运行SQL调优
93
结束时间
• 调优到用户满意为止 • 调优前先与客户沟通获取期望值
94
优化工具
• • • • autotrace – set autotrace on/trace/exp ->然后执行语句 可查看SQL执行计划 EXPLAIN PLAN SQLtrace – alter seesion set SQL_Trace = true 会生成统计文件 TKPROE – 可以从SQL_Trace生成的文件中找出执行花费最高的SQL D:\oracle\product\10.2.0\admin\ORCL3\udump – alter session set events '10046' trace name context forever, level '8' SQL*plus autotrace SQL Tuning advisor – 做SQL调优 – 强大，能给出SQL修改建议
53
基本原则 (2)
• 减少结果集
– 尽量少用select *
• 减少数据包
– set array 5000(适合于大数据量的传输)
54
SQL解释
55
减少硬解析
• 采取统一的书写规则（要完全一样的SQL才会不重新解析） • 使用绑定变量（拼sql会因为参数的不同而导致重新解析） – 在唯一性字段上使用 – 数据分布不均匀的使用 – 分布均匀的字段可以使用 • 不能绑定变量的话使用代码共享 – SQL文字相同 – 执行用户相同 – 绑定数据类型相同 • 配置足够大的共享池
数据库性能优化
1
优化方向
2
资源运用分散
• 时间分散
– 空闲时间做统计
• 位置分散
– 把数据导入前台运算 – 导到中间件运算
3
合理使用资源
• 合理
– 业务需求合理 – SQL算法合理
4
调优对象：整个应用系统
• • • • • • • 数据库 中间件 需求分析 模型设计 架构设计 代码 网络
5
影响性能因素
56
软解析（好）
57
排序过程
• • • • 减少或避免排序 尽量在内存中排序 分散临时表空间的I/O 需要排序的操作 – 创建索引 – order by – 查询中的distinct – union\intersect 或 minus – 多表联合查询 – analyze命令