大型Oracle数据库的性能分析与优化

大型大型 Oracle 数据库的性能分析与优化数据库的性能分析与优化 一 摘要一 摘要 随着网络应用和电子商务的不断发展 各个站点的访问量越来越大 如何使有限的计 算机系统资源为更多的用户服务 如何保证用户的响应速度和服务质量 这些问题都属于 服务器性能优化的范畴 二 关键字二 关键字 Oracle 数据库 性能分析 性能优化 三 正文三 正文 1 优化策略 优化策略 为了保证 Oracle 数据库运行在最佳的性能状态下 在信息系统开发之前就应该考虑数 据库的优化策略 优化策略一般包括服务器操作系统参数调整 数据库参数调整 网络性 能调整 应用程序 SQL 语句分析及设计等几个方面 分析评价 Oracle 数据库性能主要有数据库吞吐量 数据库用户响应时间两项指标 数 据库用户响应时间又可以分为系统服务时间和用户等待时间两项 即 数据库用户响应时间 系统服务时间 用户等待时间 因此 获得满意的用户响应时间有两个途径 一是减少系统服务时间 即提高数据库 的吞吐量 二是减少用户等待时间 即减少用户访问同一数据库资源的冲突率 2 数据库性能优化包括如下几个部分 数据库性能优化包括如下几个部分 1 调整数据结构的设计 这一部分在开发信息系统之前完成 程序员需要考虑是否使用 Oracle 数据库的分区功能 对于经常访问的数据库表是否需要建立索引等 2 调整应用程序结构设计 这一部分也是在开发信息系统之前完成的 程序员在这一步需 要考虑应用程序使用什么样的体系结构 是使用传统的 Client Server 两层体系结构 还 是使用 Browser Web Database 的三层体系结构 3 调整数据库 SQL 语句 应用程序的执行最终将归结为数据库中的 SQL 语句执行 因此 SQL 语句的执行效率最终决定了 Oracle 数据库的性能 Oracle 公司推荐使用 Oracle 语句 优化器 Oracle Optimizer 和行锁管理器 Row Level Manager 来调整优化 SQL 语句 4 调整服务器内存分配 内存分配是在信息系统运行过程中优化配置的 数据库管理员根 据数据库的运行状况不仅可以调整数据库系统全局区 SGA 区 的数据缓冲区 日志缓冲 区和共享池的大小 而且还可以调整程序全局区 PGA 区 的大小 5 调整硬盘 I O 这一步是在信息系统开发之前完成的 数据库管理员可以将组成同一个 表空间的数据文件放在不同的硬盘上 做到硬盘之间 I O 负载均衡 6 调整操作系统参数 例如 运行在 Unix 操作系统上的 Oracle 数据库 可以调整 Unix 数据缓冲区的大小 每个进程所能使用的内存大小等参数 实际上 上述数据库优化措施之间是相互联系的 Oracle 数据库性能恶化的表现基本 上都是用户响应时间比较长 需要用户长时间的等待 而性能恶化的原因却是多种多样的 有时是多个因素共同造成了性能恶化的结果 这就需要数据库管理员有比较全面的计算机 知识 能够敏感地察觉到影响数据库性能的主要原因所在 3 性能优化工具 性能优化工具 Oracle 数据库常用的数据库性能优化工具有 1 Oracle 数据库在线数据字典 Oracle 在线数据字典能够反映出 Oracle 的动态运行情况 对于调整数据库性能是很有帮助的 2 操作系统工具 例如使用 Unix 操作系统的 Vmstat Iostat 等命令可以查看到系统级内 存和硬盘 I O 的使用情况 这些工具能够帮助管理员弄清楚系统瓶颈出现在什么地方 3 SQL 语言跟踪工具 SQL 语言跟踪工具可以记录 SQL 语句的执行情况 管理员可以使用虚 拟表来调整实例 并使用 SQL 语句跟踪文件调整应用程序性能 SQL 语言跟踪工具将结果 输出成一个操作系统 4 Oracle Enterprise Manager OEM 这是一个图形的用户管理界面 用户可以使用它 方便地进行数据库管理而不必记住复杂的 Oracle 数据库管理的命令 5 SQL 语言优化命令 使用这个命令可以帮助程序员写出高效的程序 4 系统性能评估 系统性能评估 1 在线事务处理信息系统 OLTP 这种类型的信息系统一般需要有大量的 Insert Update 操作 典型的系统包括民航机票发售系统 银行储蓄系统等 OLTP 系统需要保证数 据库的并发性 可靠性和最终用户的速度 这类系统使用的 Oracle 数据库需主要考虑以下 参数 数据库回滚段是否足够 是否需要建立 Oracle 数据库索引 聚集 散列 系统全局区 SGA 大小是否足够 SQL 语句是否高效 2 数据仓库系统 Data Warehousing 这种信息系统的主要任务是从 Oracle 的海量数据 中进行查询 以得到数据之间的某些规律 5 ORACLE 数据库优化设计方案数据库优化设计方案 1 数据库优化自由结构 OFA 数据库的逻辑配置对数据库性能有很大的影响 为此 ORACLE 公司对表空间设计提出了 一种优化结构 OFA 使用这种结构进行设计会大大简化物理设计中的数据管理 优化自由 结构 OFA 简单地讲就是在数据库中可以高效自由地分布逻辑数据对象 因此首先要对数据 库中的逻辑对象根据他们的使用方式和物理结构对数据库的影响来进行分类 这种分类包括 将系统数据和用户数据分开 一般数据和索引数据分开 低活动表和高活动表分开等等 数据库逻辑设计的结果应当符合下面的准则 把以同样方式使用的段类型存储在一起 按照标准使用来设计系统 存在用于例外的分离区域 最小化表空间冲突 将数据字典分 离 2 合理设计和管理表 a 利用表分区 分区将数据在物理上分隔开 不同分区的数据可以制定保存在处于不同磁盘上的数据 文件里 这样 当对这个表进行查询时 只需要在表分区中进行扫描 而不必进行 FTS Full Table Scan 全表扫描 明显缩短了查询时间 另外处于不同磁盘的分区也将 对这个表的数据传输分散在不同的磁盘 I O 一个精心设置的分区可以将数据传输对磁盘 I O 竞争均匀地分散开 b 避免出现行连接和行迁移 在建立表时 由于参数 pctfree 和 pctused 不正确的设置 数据块中的数据会出现行 链接和行迁移 也就是同一行的数据不保存在同一的数据块中 如果在进行数据查询时遇 到了这些数据 那么为了读出这些数据 磁头必须重新定位 这样势必会大大降低数据库 执行的速度 因此 在创建表时 就应该充分估计到将来可能出现的数据变化 正确地设 置这两个参数 尽量减少数据库中出现行链接和行迁移 c 控制碎片 碎片 fragmentation 是对一组非邻接的数据库对象的描述 碎片意味着在执行数据库 的功能时要耗费额外的资源 磁盘 I O 磁盘驱动的循环延迟 动态扩展 链接的块等 并浪费大量磁盘空间 当两个或多个数据对象在相同的表空间中 会发生区间交叉 在动 态增长中 对象的区间之间不再相互邻接 为了消除区间交叉将静态的或只有小增长的表 放置在一个表空间中 而把动态增长的对象分别放在各自的表空间中 在 create table create index create table space create cluster 时 在 storage 子句中的 参数的合理设置 可以减少碎片的产生 d 别名的使用 别名是大型数据库的应用技巧 就是表名 列名在查询中以一个字母为别名 查询速 度要比建连接表快 1 5 倍 e 回滚段的交替使用 由于数据库配置对应用表具有相对静止的数据字典和极高的事务率特点 而且数据库 的系统索引段 数据段也具有相对静止 并发现在应用中最高的负荷是回滚段表空间 把 回滚段定义为交替引用 这样就达到了循环分配事务对应的回滚段 可以使磁盘负荷很均 匀地分布 3 索引 Index 的优化设计 a 管理组织索引 索引可以大大加快数据库的查询速度 索引把表中的逻辑值映射到安全的 RowID 因 此索引能进行快速定位数据的物理地址 但是有些 DBA 发现 对一个大型表建立的索引 并不能改善数据查询速度 反而会影响整个数据库的性能 这主要是和 SGA 的数据管理方 式有关 ORACLE 在进行数据块高速缓存管理时 索引数据比普通数据具有更高的驻留权限 在进行空间竞争时 ORACLE 会先移出普通数据 对一个建有索引的大型表的查询时 索引 数据可能会用完所有的数据块缓存空间 ORACLE 不得不频繁地进行磁盘读写来获取数据 因此在对一个大型表进行分区之后 可以根据相应的分区建立分区索引 如果对这样大型 表的数据查询比较频繁 或者干脆不建索引 另外 DBA 创建索引时 应尽量保证该索引 最可能地被用于 where 子句中 如果对查询只简单地制定一个索引 并不一定会加快速度 因为索引必须指定一个适合所需的访问路径 b 聚簇的使用 Oracle 提供了另一种方法来提高查询速度 就是聚簇 Cluster 所谓聚簇 简单 地说就是把几个表放在一起 按一定公共属性混合存放 聚簇根据共同码值将多个表的数 据存储在同一个 Oracle 块中 这时检索一组 Oracle 块就同时得到两个表的数据 这样就 可以减少需要存储的 Oracle 块 从而提高应用程序的性能 4 使用最优的数据库连接和 SQL 优化方案 a 使用直接的 OLE DB 数据库连接方式 通过 ADO 可以使用两种方式连接数据库 一种是传统的 ODBC 方式 一种是 OLE DB 方 式 ADO 是建立在 OLE DB 技术上的 为了支持 ODBC 必须建立相应的 OLEDB 到 ODBC 的调 用转换 而使用直接的 OLE DB 方式则不需转换 从而提高处理速度 b 使用 Connection Pool 机制 在数据库处理中 资源花销最大的是建立数据库连接 而且用户还会有一个较长的连 接等待时间 解决的办法就是复用现有的 Connection 也就是使用 Connection Pool 对象 机制 Connection Pool 的原理是 IIS ASP 体系中维持了一个连接缓冲池 这样 当下一个 用户访问时 直接在连接缓冲池中取得一个数据库连接 而不需重新连接数据库 因此可 以大大地提高系统的响应速度 c 高效地进行 SQL 语句设计 通常情况下 可以采用下面的方法优化 SQL 对数据操作的表现 1 减少对数据库的查询次数 即减少对系统资源的请求 使用快照和显形图等分布式数 据库对象可以减少对数据库的查询次数 2 尽量使用相同的或非常类似的 SQL 语句进行查询 这样不仅充分利用 SQL 共享池中的 已经分析的语法树 要查询的数据在 SGA 中命中的可能性也会大大增加 3 限制动态 SQL 的使用 虽然动态 SQL 很好用 但是即使在 SQL 共享池中有一个完全相 同的查询值 动态 SQL 也会重新进行语法分析 4 避免不带任何条件的 SQL 语句的执行 没有任何条件的 SQL 语句在执行时 通常要进 行 FTS 数据库先定位一个数据块 然后按顺序依次查找其它数据 对于大型表这将是一 个漫长的过程 5 如果对有些表中的数据有约束 最好在建表的 SQL 语句用描述完整性来实现 而不是 用 SQL 程序中实现 6 可以通过取消自动提交模式 将 SQL 语句汇集一组执行后集中提交 程序还可以通过 显式地用 COMMIT 和 ROLLBACL 进行提交和回滚该事务 7 检索大量数据时费时很长 设置行预取数则能改善系统的工作表现 设置一个最大值 当 SQL 语句返回行超过该值 数值库暂时停止执行 除非用户发出新的指令 开始组织并 显示数据 而不是让用户继续等待 四 总结四 总结 Oracle 数据库的性能优化调整是一个系统工程 涉及的方面很多 数据库管理员需要 综合运用上面介绍的规律 认真分析 Oracle 在运行过程当中出现的各种问题 以保证 Oracle 数据库运行的高效率 还需要指出的是 上面给出的语句只是测得 Oracle 运行过 程的某一个时