Oracle性能优化培训PPT

27
数据仓库系统
28
特点
• 批量数据装载 • 海量数据查询 • 大量全表扫描的使用
29
调优目的：快速的响应时间
30
调优分析
• 不用RAC • 主机资源
– 多CPU高主频 – 高I/o能力
• • • • •
大池日志缓冲区要大 关闭归档 少用约束条件 考虑使用预处理机制 考虑多层数据中心架构
31
•
103
防止索引失效
• • • • • • • 尽量让搜索表达式放在比较数值那边，可以让搜索走索引搜索，避免全表扫描 查询条件时，比较类型不相同，不走索引 类型转换时以转到数字优先，可显式把比较 数值转成与字段相同的类型 查询数据选取范围过大时自动进行全表扫描（一半范围不超过全范围的一半） 数据类型字段用like的时候会把字段先转成字符串再比较，导致不走索引 不等于符号（!=、<>）不走索引 可以用大于号加小于号一起使用 空值没有索引记录 isnull和notnull不走索引 可用特殊值代替空值 复合索引的时候，把查询中出现最多的字段作为前导字段
存储对象
• 仔细设置BLOCKSIZE和 DB_FILE_MULTIBLOCK_READ_COUNT • 确保区的大小是参数乘积的整数倍 • pctfree可以设为0 • 存储上I/O分散 • 基于冗余字段的多表联接，使用聚簇表 • 大量使用分区和分区索引
32
索引的使用
• 尽可能使用位图索引 • 对于按照主健执行的范围查询，使用索引 组织表 • 对非均匀分布的索引列一定要生成柱状图 （决定索引方法）
80
调优阶段
81
需求分析和架构设计
• 需求管理，答应需求时先评估性能影响 • 空间位置数据(orcale有专门的工具) • 入库数据类型分类
82
应用设计和开发
83
数据库架构设计
• 容灾 • 高可用 • 读写分离
84
应用模块设计
• 开发语言选择 • 应用算法选择 • 算法实现位置
85
ER模型设计
76
CBO（好）
• 根据资源消耗决定用什么方法查询(costbased) • 统计(statistics)驱动 默认每天晚上10点自动 增量收集信息
77
修改模式:alert session set optimerzmode=....
78
影响其他的index查询速度
79
索引等级
• 唯一索引 • 不唯索引
• 应用设计 – 设计调优 – 应用调优 • 数据库调优 – 内存调优 – I/O调优 – 表空间 – 锁竞争 • 系统调优 – 操作系统调优
6
基本步骤
7
主动式调优
8
1、了解业务特性和优化需求
9
主动式调优
10
OLTP系统
11
特点
• 高并发 • 高吞吐量 • 连续增加数据
12
调优目的
• 高可用性 • 高并发下的高速 • 并发性
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规范
38
等待事件
39
应用级
• SQL语句统计
40
业务级
• 客户反馈性能表象和业务运行特性
41
4、确定当前OS和DB性能瓶颈
42
5、分析原因
43
分析所有可能性
• 因素判定
– 深化分析，三级
44
6、确定合适的优化方案
45
应用存储优化
46
SQL语句优化
47
优化方案对象
48
考虑功能语言（SQL、PLSQL、 java、。。）
63
ORCAL
64
OLTP
65
数据仓库
66
数据库主机硬件
67
数据库部署
68
table
• 分区
– 数据量 – 访问特点
69
index
70
从表中提取少量数据时有效
71
取大部分数据时做全表扫描
72
不好影响
73
影响增删改速度
74
(<-选索引规则)影响其他查询
75
RBO
• • • • 影响全表扫描 根据规则决定用什么提取方法查询(Rule-based) 使用一个分级系统，语法(syntax)驱动和字典(dictionary)驱动 SQL执行选index规则 – 1、同级别=比较同级别索引：都选 – 2、同级别=不同级别索引：选高级别 – 3、不同级别比较：“=” > “>=” > “>” – 4、同级别范围，不同级别索引：选高级别 – 5、同级别范围，同级别索引：选最新创建的
34
3、收集当前系统性能信息
35
操作系统(io、内存、CPU、交 换区)
36
数据库
37
统计信息
• 动态性能视图
– 累积值 – 瞬时值
• utlbstat.sql、utlestat.sql、statspack(能找 出topsql：执行时间最长、消耗资源最多) • 10g以上工具：AWR、ADDM
– 所选择字段没有索引，会根据查询字段在索引 中找然后回表中根据rowid查记录
• fast index scan
– 所选择字段都建有index，可以只在索引中取数 据
• skip index scan
– 跃式扫描：根据有索引的字段跳跃查找没有索 引的查询字段
101
选择索引
• • • • • • • • 只有经常在表中查找少量数据的时候才建索引 经常作为查询条件的字段，唯一值较多的字段 重复性多的字段建索引没作用 全包括的组合索引级别高于单列索引，半包括的则低于单列 唯一性索引和字段的增删改操作会慢，如果不是查询字段，不适宜做索 引 经常做函数条件查询时可以建立 函数索引 查询记录数占总记录数超过2%的时候，不建议走索引查询 不在大字段上建索引
19
日志产生量控制
20
存储对象
21
减少动态分配
• 段、区、块
22
initrans要设置的足够高
23
块不宜过大，pctfree不能太小
24
wk.baidu.com
索引
• 不要太多， 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对) – 结论：在大表建索引、两表都有索引时查询从大到小
25
SQL优化
• 写好语句 • 选好算法 • 把数据放在最少的块上
26
应用调优
• • • • • • 减少事务并发量、控制事务大小 减少锁级别、锁并发量 控制触发器的个数和大小 使用约束条件而不使用应用代码来实现数据校验 控制约束条件，适当减少外键的使用 SQL解析 – 确保代码可共享 – 优化共享SQL、使用绑定变量而不是文本值（多用?变量可 减少sql解析时间） – 使用cursor_sharing参数
53
基本原则 (2)
• 减少结果集
– 尽量少用select *
• 减少数据包
– set array 5000(适合于大数据量的传输)
54
SQL解释
55
减少硬解析
• 采取统一的书写规则（要完全一样的SQL才会不重新解析） • 使用绑定变量（拼sql会因为参数的不同而导致重新解析） – 在唯一性字段上使用 – 数据分布不均匀的使用 – 分布均匀的字段可以使用 • 不能绑定变量的话使用代码共享 – SQL文字相同 – 执行用户相同 – 绑定数据类型相同 • 配置足够大的共享池
87
数据库配置
• 资源
– 存储资源 – 系统配置 – 网络资源
• 数据库创建
– 用户 – 表空间 –表 – 索引
88
部署应用
89
调优时间
90
预定义调优
• 从项目立项到上线前 • 性能测试 • 压力测试
91
主动式调优
• 周期性收集性能信息
– 分析系统调优
92
被动式调优
• 项目运行出现故障后
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 ... • 访问最少的块数 • 将数据块保留在内存中 • 响应时间 – 需要考虑全系统因素 • 提高吞吐量（单位时间的处理能力） • 提高负载量（并发访问量）
102
索引原理
• B-Tree索引 – 类似快速排序的原理 先放一层，然后当第一层放不下后，拆出第二层，第一 层改为存放中间值 到一层中间值放不下，再分第三层，第二层改为存放中间 值 – 查询时根据索引的值从顶层比较中间值往下找到叶子 再根据rowid去表中读取 数据 – 索引倾斜：新增数据都在某个索引分支下，造成该分支有很深的层 增加索引 查询的代价 – 会有行锁 – 多用在一般业务系统 存储参数 – 大索引使用大区 – 大索引考虑NOLOGGING
• 只拆可变字段和字段长度长冗余量大的 • 经常在一起访问的字段不要拆 • 不经常访问的字段要拆
86
系统开发
• 代码规范 • 数据规范
– 数据格式 – 在CBO模式下建立函数索引lower(index)
• SQL编码规范
– like比较只有在首字母确定时才能走索引 把最 主要查询的条件放在查询条件前面 – 只选择具体的有索引字段时可以进行快速全索 引扫描
• 避免排序
– 用union all代替union – 使用表的连接使用基于索引的访问 – 选择需要的列进行分析 – 对于大对象的分析使用estimate而不是 compute
59
7、分步实施
60
8、重新收集性能信息跟踪调优 结果
61
被动式调优
62
参与调优人员
• • • • • 业务专家+技术专家做需求控制 有丰富开发经验的DBA 系统管理员 系统架构 开发人员（规范）
56
软解析（好）
57
排序过程
• • • • 减少或避免排序 尽量在内存中排序 分散临时表空间的I/O 需要排序的操作 – 创建索引 – order by – 查询中的distinct – union\intersect 或 minus – 多表联合查询 – analyze命令
58
排序过程 (2)
– 数据库一个资源最多100个并发
13
调优分析
14
RAC的使用
15
单个数据库多实例
• 提高并发数
16
注意问题
• 共享存储必需要高速 • 数据库实例与共享存储的心跳线高速 • 数据库实例会有块锁
– 做业务分离
17
主机资源
• 多CPU • 高I/O • 高内存
18
缓存
• 数据缓存 • 数据共存 • 日志缓冲
– 运行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修改建议
数据库性能优化
1
优化方向
2
资源运用分散
• 时间分散
– 空闲时间做统计
• 位置分散
– 把数据导入前台运算 – 导到中间件运算
3
合理使用资源
• 合理
– 业务需求合理 – SQL算法合理
4
调优对象：整个应用系统
• • • • • • • 数据库 中间件 需求分析 模型设计 架构设计 代码 网络
5
影响性能因素
• •
95
优化工具 (2)
• SQL Access advisor
– 给出索引方面的建议
96
算法
97
数据访问方法
• 簇表(Clusters) • 索引组织表(index-organized tables)
98
索引
99
基于函数的Function based
100
访问方法
• index rowid scan