Oracle设计开发指南.doc

文档状态 草稿 正式发布 编辑当前版本 0 1 配置项名称配置项编号 部 门密 级 知识库知识库 OracleOracle 设计开发指南设计开发指南 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 2 of 65 版本历史版本历史 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 3 of 65 目目 录录 1 引言引言 6 1 1目的 6 1 2范围 约束及假设 6 1 3定义 首字母缩写词和缩略语 6 1 4阅读对象 6 1 5参考资料 6 1 6文档概述 6 2 数据库设计数据库设计 6 2 1逻辑设计 6 2 1 1表设计 6 2 1 2范式和冗余 7 2 1 3索引设计 7 2 1 4聚簇设计 8 2 1 5视图设计 8 2 2物理设计 9 2 2 1内存参数设计 9 2 2 2表容量估算 9 2 2 3索引容量估算 9 2 2 4存储参数设计 9 2 2 5表空间设计 9 2 2 6数据文件设计 9 2 2 7重做日志文件设计 9 2 2 8回滚段设计 9 2 3命名规范 9 2 3 1一般性命名规范 9 2 3 2对象命名规范 9 3 SQL 优化优化 11 3 1SQL 执行过程 11 3 1 1解析 SQL 11 3 1 2执行 SQL 11 3 1 3显示结果集 11 3 2SQL 优化器 12 3 2 1优化器的优化方式 12 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 4 of 65 3 2 2优化器的优化模式 12 3 3SQL 优化过程 12 3 3 1确定具有高影响力的 SQL 语句 12 3 3 2抽取和解释 SQL 语句 13 3 3 3调整 SQL 语句 13 3 4SQL 调整工具 13 3 4 1Oracle AutoTrace 13 3 4 2TKPROF 工具 15 3 4 3STATSPACK 工具 16 3 5ORACLE SQL 优化方法 32 3 5 1选用适合的 ORACLE 优化器 32 3 5 2访问 Table 的方式 32 3 5 3共享 SQL 语句 33 3 5 4选择最有效率的表名顺序 34 3 5 5WHERE 子句中的连接顺序 34 3 5 6SELECT 子句中避免使用 35 3 5 7减少访问数据库的次数 35 3 5 8使用 DECODE 函数来减少处理时间 36 3 5 9删除重复记录 37 3 5 10用 TRUNCATE 替代 DELETE 37 3 5 11HWM 对数据库性能的影响 37 3 5 12尽量多使用 COMMIT 38 3 5 13用 Where 子句替换 HAVING 子句 38 3 5 14减少对表的查询 39 3 5 15使用表的别名 Alias 39 3 5 16用 EXISTS 替代 IN 39 3 5 17用 NOT EXISTS 替代 NOT IN 40 3 5 18用表连接替换 EXISTS 40 3 5 19用 EXISTS 替换 DISTINCT 41 3 5 20识别 低效执行 的 SQL 语句 41 3 5 21提高 insert update 速度 41 3 6调整查询 42 3 6 1查询调整方法 42 3 6 2子查询调整方法 42 3 6 3使用 Oracle 并行查询发挥多 CPU 的威力 43 3 7排序优化 44 3 7 1Oracle SQL 排序原理 44 3 7 2需要进行排序的操作 45 3 7 3排序性能诊断 45 3 7 4监控临时表空间的使用情况 45 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 5 of 65 3 7 5排序优化 46 3 8调整表联接 46 3 8 1表联接类型 46 3 8 2Oracle 优化器联接原理 49 3 8 3联接优化 53 3 9调整 DML 语句 53 3 9 1Oracle 存储参数和 DML 性能 53 3 9 2空闲列表管理和 DML 性能 55 3 9 3索引和 DML 性能 56 3 10用索引调整 SQL 56 3 10 1Oracle 索引概述 56 3 10 2索引不被使用的场景 58 3 10 3索引使用方法 58 3 11分页优化技术 59 3 11 1分页查询概述 59 3 11 2单表分页查询 59 3 11 3联接分页查询 60 3 11 4UNION ALL 分页查询 61 3 11 5使用 ROW NUMBER 函数分页 64 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 6 of 65 OracleOracle 设计开发指南设计开发指南 1 1 引言引言 1 11 1 目的目的 1 21 2 范围 约束及假设范围 约束及假设 1 31 3 定义 首字母缩写词和缩略语定义 首字母缩写词和缩略语 定义 首字母缩写词和缩略语定义 首字母缩写词和缩略语描描 述述 1 41 4 阅读对象阅读对象 阅读对象阅读对象阅读建议阅读建议 港口信息化项目组所有成员 1 51 5 参考资料参考资料 名名 称称描描 述述 1 61 6 文档概述文档概述 2 2 数据库设计数据库设计 2 12 1 逻辑设计逻辑设计 2 1 12 1 1表设计表设计 1 选择合适的数据类型与长度 选择最合适的数据类型 避免数据类型转换 确定好字段的长度 避免浪费存储空间 若在创建 字段的时候 不能够确认字段长度的话 则最好采用变长的数据类型 禁止使用 LONG 和 LONG RAW 类 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 7 of 65 型 应使用 BLOB 或 CLOB 类型 2 确定表需要采用的完整性约束与默认值 在建立表之前 需要确定列的主键约束 外键约束 非空约束 唯一性约束和 CHECK 约束 需要 确认列的默认值 3 在设计大表时 把允许 NULL 的列放在表的后面 在数据库存储的时候 若字段为 NULL 则这个值根本没有存储 把允许 NULL 的字段放在表的末 尾 当数据库比较大或者空字段比较多的话 则可以大大的减少数据库的存储空间 Oracle 表列建立好之后 除非重新删除再建立 否则无法调整列的顺序 因此在创建表之前 要 对列 以及是否为空有一个明确的定义 只有如此 才能够合理安排列的顺序 4 确定表需要采用的类型 对于数据量比较大的表 Oracle 建议 单个表大小超过 2G 根据表数据的属性进行分区 以得 到较好的性能 如果表按某些字段进行增长 则采用按字段值范围进行范围分区 如果表按某个字段 的几个关键值进行分布 则采用列表分区 对于静态表 则采用 Hash 分区或列表分区 在范围分区中 如果数据按某关键字段均衡分布 则采用子分区的复合分区方法 表中数据多数情况下按主键列进行查询且更新不频繁应使用索引表或聚簇表 其他情况下使用普 通的堆表 5 每个表在创建时候 必须指定所在的表空间 不要采用默认表空间以防止表建立在系统表空间 上导致性能问题 对于事务比较繁忙的数据表 必须存放在该表的专用表空间中 表的数据和索引应 存放在不同的表空间中 大二进制数据列存储于独立表空间中 6 每个表均创建类型为 Sequence 的主键字段 为每个表增加如下字段 LAST UPDATE DATE LAST UPDATE USER SEQ CREATE DATE CREATE USER SEQ 为每个业务数据表增 加以下字段 COMPANY SEQ 租户序列号 为每个数据表增加删除标记字段 为业务数据表增加 5 8 个保留字段 2 1 22 1 2范式和冗余范式和冗余 如果没有性能上的原因 应该使用关系数据库理论 达到较高的范式 避免数据冗余 如果在数 据量上与性能上有一定要求 并且表的更新率较低 查询率较高 考虑到实现的方便性可以有适当的 数据冗余 2 1 32 1 3索引设计索引设计 序号序号规则规则 1 若表中可能有大量数据 并且需要要查询这些数据 则应在相关的列上建立索引 以加快查询 速度 但若表的数据不多 则不需建索引 对于大表 如果经常需要查询的数据超过 10 到 15 的话 没有必要创建索引 2 常规 OLTP 联机事务处理 应用 创建 B TREE 索引 3 需要降序搜索的列 可创建降序索引 4 主键值如果按顺序递增或递减 应该显式为主键列建立反向键索引 5 将子表的外键列的索引改为压缩型 6 如果查询条件中使用函数时 应创建基于函数的索引 7 在数据仓库和 DSS 决策支持系统 应用中 为低基数列创建位图索引 8 对于查询中需要作为查询条件的字段 可以考虑建立索引 最终根据性能的需要决定是否建立 索引 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 8 of 65 9 对于复合索引 索引字段顺序比较关键 把查询频率比较高的字段排在索引组合的最前面 10 在分区表中 尽量采用 Local 分区索引以方便分区维护 除非使用分区 Local 索引 否则在创 建索引段时候必须指定索引段的 TABLESPACE STORAGE 属性 2 1 42 1 4聚簇设计聚簇设计 Oracle支持两种类型的聚簇 索引聚簇和哈希聚簇 2 1 4 12 1 4 1使用索引聚簇使用索引聚簇 序号序号规则规则 1 考虑对经常在连接语句中访问的表建立聚簇 2 如果表只是偶尔被连接或者它们的公共列经常被修改 则不要使用聚簇表 修改记录的聚簇 键值比在非聚簇的表中修改此值要花费更多的时间 因为 Oracle 必须将修改的记录移植到其 他的块中以维护聚簇 3 如果经常需要在一个表上进行完全搜索 则不要聚簇这个表 对一个聚簇表进行完全搜索比在 非聚簇表上进行完全搜索的时间长 Oracle 可能要读更多的块 因为表是被一起存储的 4 如果经常从一个父表和相应的子表中查询记录 则考虑给 1 对多 1 关系创建聚簇表 子表记录存储在与父表记录相同的数据块中 因此当检索它们时可以同时在内存中 因此需 要 Oracle 完成较少的 I O 5 如果经常查询同一个父表中的多个子记录 则考虑单独将子表聚簇 这样提高了从相同的父 表查询子表记录的性能 而且也没有降低对父表进行完全搜索的性能 6 如果从所有有相同聚簇键值的表查询的数据超过一个或两个 Oracle 块 则不要聚簇表 要 访问在一个聚簇表中的记录 Oracle 读取所有包含那个记录值的全部数据块 如果记录占据了 多个数据块 则访问一个记录需要读的次数比一个非聚簇的表中访问相同的记录读的次数要多 2 1 4 22 1 4 2使用哈希聚簇使用哈希聚簇 序号序号规则规则 1 当经常使用有相同列的包含相等条件的查询子句访问表时 考虑使用哈希聚簇来存储表 使用 这些列作为聚簇键 2 如果可以确定存放具有给定聚簇键值的所有记录所需的空间 包括现在的和将来的 则将此 表以哈希聚簇存储 3 如果空间不够 并且不能为将要插入的新记录分配额外的空间 那么不要使用哈希聚簇 4 如果偶尔创建一个新的 很大的哈希聚簇来保存这样的表是不切实际的 那么不要用哈希聚簇 存储经常增长的表 5 如果经常需要进行全表搜索 并且必须要为表的预期增长中的哈希聚簇分配足够的空间 则不 要将此表以哈希聚簇存储 这样的完全检索必须要读分配给哈希聚簇的全部块 即使有些块 可能只包含很少的记录 单独地存储表将减少由完全的表检索读取的块的数量 6 如果你的应用程序经常修改聚簇键的值 则不要将表以哈希聚簇方式存储 2 1 52 1 5视图设计视图设计 序号序号规则规则 1 从一个或多个表中查询部分数据项 2 为简化查询 将复杂的检索或子查询通过视图实现 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 9 of 65 3 提高数据的安全性 只将需要查看的数据信息显示给权限有限的人员 4 视图中如果嵌套使用视图 级数不得超过 3 级 5 由于视图中只能固定条件或没有条件 所以对于数据量较大或随时间的推移逐渐增多的库表 不宜使用视图 可以采用实体化视图代替 6 除特殊需要 避免类似 Select from Table Name 而没有检索条件的视图 7 视图中尽量避免出现数据排序的 SQL 语句 2 22 2 物理设计物理设计 2 2 12 2 1内存参数设计内存参数设计 2 2 22 2 2表容量估算表容量估算 2 2 32 2 3索引容量估算索引容量估算 2 2 42 2 4存储参数设计存储参数设计 2 2 52 2 5表空间设计表空间设计 2 2 62 2 6数据文件设计数据文件设计 2 2 72 2 7重做日志文件设计重做日志文件设计 2 2 82 2 8回滚段设计回滚段设计 2 32 3 命名规范命名规范 2 3 12 3 1一般性命名规范一般性命名规范 项项命名规范命名规范 语言1 名使用英文单词 不使用复数 2 文单词使用同对象本身意义相对或相近的单词 选择最简单或最通用的单词 不能使 用毫不相干的单词来命名 3 一个单词不能表达对象含义时 用词组组合 如果组合太长时 采用简写或缩写 缩 写要基本能表达原单词的意义 4 出现对象名重名时 是不同类型对象时 加类型前缀或后缀以示区别 大小写名称一律大写 以方便不同数据库移植 以及避免程序调用问题 单词分隔命名的各单词之间使用下划线进行分隔 保留字命名不允许使用 SQL 保留字 表名长度表名 字段名 视图名长度应限制在 29 个字符内 含前缀 字段名称同一个字段名在一个数据库中只能代表一个意思 不同的表用于相同内容的字段应该采 用同样的名称和字段类型定义 2 3 22 3 2对象命名规范对象命名规范 对象名对象名命名规范命名规范 表名命名 数据库名简写 TBL 分类名 表友好名 范例 PUB TBL PM ROLE FUNCTION 公共库 权限管理 角色功能表 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 10 of 65 PUB 公共库简写 TBL 前缀 表示是 Table PM 权限管理 ROLE FUNCTION 角色功能表 表友好名 字段命名1 主键列 命名 表友好名 SEQ 省略前缀 数据库名简写 TBL 分类名 范例 ROLE FUNCTION SEQ 表 PUB TBL PM ROLE FUNCTION 的主键 2 外键列 命名 相关表主键名 省略前缀 数据库名简写 TBL 分类名 范例 表 ROLE FUNCTION 中的外键列 ROLE SEQ 是表 PUB TBL PM ROLE 的主键列名 3 一般字段 命名 字段友好名 范例 COMPANY EN NAME 公司英文名称 索引命名 IDX 表名 构成的字段名 范例 IDX PUB PM USER TYPE 为表 PUB PM USER 的 USER TYPE 字段创建的索引 视图命名 数据库名简写 VIEW 表 A 名 表 B 名 范例 PUB VIEW PM USER TYPE 存储过程命名 数据库名简写 PRC 存取过程特性名 范例 PUB PRC GET PERMISSION 序列命名 表名 SEQ 省略前缀 数据库名简写 TBL 分类名 范例 ROLE FUNCTION SEQ 表 ROLE FUNCTION 的主键 Sequence 公用表空间命名 TBS 存储的特性命名 范例 TBS PUBDATA 专用表空间命名 TBS 表名 NN NN 1 2 3 4 范例 TBS ORDER DATA 1 数据文件命名 表空间名 NN DBF NN 1 2 3 4 范例 TBS PUBDATA 1 普通变量命名 VAR 友好名 范例 VAR RESULT 游标变量命名 CUR 友好名 范例 CUR ORDER 记录型变量命名 REC 友好名 范例 REC ORDER 表类型变量命名 TBL 友好名 范例 TBL ORDER 触发器命名 TRG 友好名 范例 TRG ORDER 函数命名 FUNC 友好名 范例 FUNC MAPPING 包1 包头 命名 PKG HEAD 友好名 范例 PKG HEAD COUNT QRSYS 2 包体 命名 PKG BODY 友好名 范例 PKG BODY COUNT QRSYS Oracle 设计开发指南 SZEDI CO LTD 2008 Page 11 of 65 3 3 SQLSQL 优化优化 3 13 1 SQLSQL 执行过程执行过程 3 1 13 1 1解析解析 SQLSQL 解析步骤解析步骤说明说明 检查安全性这个步骤将进行安全性规则验证 以确保发出请求者已经经过授权可以察看数据 检查 SQL 语法这一步骤将检查 SQL 语句的语法 以确保语法的书写符合正确的格式 可能发生的查 询重新书写 在特定情况下 Oracle 在将 SQL 传递到优化器之前 将重新书写查询 更改 或使用 主机变量代替直接量 3 1 23 1 2执行执行 SQLSQL 执行步骤执行步骤说明说明 创建执行计划解析后的 SQL 被传递给 SQL 优化器 优化器检查 SQL 以及数据字典 然后产生相应的 内部执行计划 这个计划是机器级的代码 这些代码使用相应的 API 调用从磁盘取出 的数据 Oracle 在决定如何为查询服务时有很多的访问手段可以选择 程序将告知优 化器去选择速度最快或者是占用资源最少的访问手段 捆绑执行计划捆绑过程将在 SQL 语句中扫描捆绑变量 然后为每一个变量指定数值 执行执行计划Oracle 将解析树放入数据缓存 然后执行所有必要的磁盘输入输出 读取结果记录获取步骤为 SELECT 语句读取记录 通过使用数组方式的获取 每一次可以读取多个 记录 3 1 33 1 3显示结果集显示结果集 这一步骤将对字段数据执行所有必要的排序 转换和重新格式化 3 23 2 SQLSQL 优化器优化器 Oracle 在执行一个 SQL 之前 首先要分析一下语句的执行计划 然后再按执行计划去执行 分析语 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 12 of 65 句的执行计划的工作是由优化器 Optimizer 来完成的 不同的情况 一条 SQL 可能有多种执行计划 但 在某一时点 一定只有一种执行计划是最优的 花费时间是最少的 3 2 13 2 1优化器的优化方式优化器的优化方式 Oracle 的优化器共有两种的优化方式 即基于规则的优化方式 Rule Based Optimization 简称为 RBO 和基于成本的优化方式 Cost Based Optimization 简称为 CBO 1 RBO 方式 优化器在分析 SQL 语句时 所遵循的是 Oracle 内部预定的一些规则 比如我们常见 的 当一个 where 子句中的一列有索引时 优化器会选择按索引扫描 2 CBO 方式 依词义可知 它是看 SQL 语句的代价 Cost 这里的代价主要指 CPU 和内存 优化器 在判断是否用这种方式时 主要参照的是表及索引的统计信息 统计信息给出表的大小 有多少行 每行的长度等信息 这些统计信息起初在库内是没有的 是你在做 analyze 后才出现的 很多的时侯过 期统计信息会令优化器做出一个错误的执行计划 因些我们应及时更新这些信息 在 Oracle8 及以后的 版本 Oracle 列推荐用 CBO 的方式 CBO 的成本主要由物理 I O 组成 计算公式为 IO CPU 1000 Net I O 1 5 IO 表示物理 I O 请求 CPU 表示逻辑 I O 请求 Net I O 表示通过数据库链接访问远程数据库的 逻辑 I O 请求 CBO 会尝试计算所有可能执行的物理 I O 选择只需要最小物理 I O 的计划 3 2 23 2 2优化器的优化模式优化器的优化模式 优化模式包括 Rule Choose First rows All rows 这四种方式 1 Rule 基于规则的方式 2 Choose 默认的情况下 Oracle 用的便是这种方式 指的是当一个表或或索引有统计信息 则走 CBO 的方式 如果表或索引没有统计信息 表又不是特别的小 而且相应的列有索引时 那么就走索引 走 RBO 的方式 3 First Rows 它与 Choose 方式是类似的 所不同的是当一个表有统计信息时 它将以最快的方式 返回查询的最先的几行 从总体上减少了响应时间 4 All Rows 也就是我们所说的 Cost 的方式 当一个表有统计信息时 它将以最快的方式返回表的 所有的行 从总体上提高查询的吞吐量 没有统计信息则走基于规则的方式 使用下列 SQL 语句可以修改优化器模式 SQL alter system set optimizer mode First Rows scope both 3 33 3 SQLSQL 优化过程优化过程 3 3 13 3 1确定具有高影响力的确定具有高影响力的 SQLSQL 语句语句 在 V SQLAREA 视图中的 SQL 语句将根据下列参数进行级别排序 这些参数是按照重要性由高到低进 行排序的 1 Executions 执行 经常执行的 SQL 语句就应当越早进行调整 因为它们会对总体性能产生巨 大的影响 2 Disk reads 磁盘读取 高的磁盘读取量有可能表明查询导致了过多的输入输出量 3 Rows processed 记录处理 处理大量记录的查询会导致较高的输入输出量 同时在排序时有 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 13 of 65 可能对 TEMP 表空间产生影响 4 Buffer gets 缓冲区读取 高的缓存读取量有可能表明了一个高资源使用量的查询 5 Sorts 排序 排序会造成速度的明显降低 尤其是那些在磁盘 TEMP 表空间中进行的排序 V SQLAREA 视图提供了一种识别有潜在问题或者需要优化的 SQL 语句的方法 可以使用下列 SQL 语句找出执行开销高的语句 select b username username a disk reads reads a executions exec a disk reads decode a executions 0 1 a executions rds exec ratio mand type a sql text Statement from v sqlarea a dba users b where a parsing user id b user id and a disk reads 100000 order by a disk reads desc 3 3 23 3 2抽取和解释抽取和解释 SQLSQL 语句语句 在指定 SQL 语句后 需要对它进行 解释 以丰富现存的执行计划 并且对它进行调整来确定这 个执行计划是否可以改进 在 TOAD 和 PLSQL Developer 中 可以使用 Explain plan 查看 SQL 语句的执行计划 在 SQL Plus 中可以使用 Oracle AutoTrace 查看 SQL 语句的执行计划 还可以使用 Oracle 提供的 TKPROF 工具 STATSPACK 工具诊断和分析 SQL 语句可能存在的问题 3 3 33 3 3调整调整 SQLSQL 语句语句 我们将使用下列方法之一对拥有非优化执行计划的 SQL 语句进行调整 序号序号步骤步骤 1 添加 SQL 提示会修改执行计划 2 添加 B 树索引能够去掉全表扫描 3 重新书写 SQL 语句可以改变执行计划 尤其是在使用 RBO 时更改 from 子句中表出现的顺序时 4 为查询中 where 子句中低基数字段添加位图索引 5 在 PL SQL 中重新书写 SQL 通常可以提高性能 对于一个特定查询来说 这样做可以使性能提 高至少 20 倍 SQL 将被替换成 PL SQL 软件包的调用 其中这个软件包中包含执行查询所需 的存储过程 3 43 4 SQLSQL 调整工具调整工具 3 4 13 4 1OracleOracle AutoTraceAutoTrace AUTOTRACE 是一项 SQL Plus 功能 自动跟踪为 SQL 语句生成一个执行计划并且提供与该语句的 处理有关的统计 1 在开始菜单 运行中 输入 cmd 进入命令窗口 输入 sqlplus 命令 输入用户名 test 输入密码 test 连接成功 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 14 of 65 2 输入 set autotrace on 表示启用 autotrace 查看 sql 执行计划 输入 SQL 语句 在执行完 成后 先显示 SQL 执行结果 然后显示 SQL 执行计划 最后显示 SQL 统计信息 附 AUTOTRACE Statistics 常用列解释 序号序号列名列名解释解释 1db block gets 从 buffer cache 中读取的 block 的数量 2consistent gets 从 buffer cache 中读取的 undo 数据的 block 的数量 3physical reads 从磁盘读取的 block 的数量 4redo size DML 生成的 redo 的大小 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 15 of 65 5sorts memory 在内存执行的排序量 6sorts disk 在磁盘上执行的排序量 判断 SQL 效率高低除了通过时间来衡量 还应该通过执行 SQL 执行状态里面的逻辑读的数量来衡 量 逻辑读 db block gets consistent gets 3 4 23 4 2TKPROFTKPROF 工具工具 TKPROF 是一个用于分析 Oracle 跟踪文件并且产生一个更加清晰合理的输出结果的可执行工具 如果一个系统的执行效率比较低 一个比较好的方法是跟踪用户的会话并使用 TKPROF 工具的排序功能 格式化输出 从而找出有问题的 SQL 语句 TKPROF 的使用遵循以下几个步骤 1 在数据库级别上设置 TIMED STATISTICS 为 true 在 SQL Plus 中使用下列语句 SQL alter system set timed statistics true scope both TIMED STATISTICS 为 true 表示允许 sql trace 和其他一些动态性能视图收集与时间有关的参数 2 在 session 级别设置 trace 在 SQL Plus 中使用下列语句 SQL alter session set sql trace true 3 找到生成的 trace 文件 路径 Oracle 安装目录 admin 实例名 udump 文件格式为 实例名 ora 编号 trc 4 对 trace 文件使用 TKPROF 工具进行分析 Tkprof tracefile outfile explain user password options 一般来说 使用 TKPROF 得到的输出文件中包含 3 个部分 SQL 语句本身 相关的诊断信息 包括 CPU 时间 总共消耗时间 读取磁盘数量 逻辑读数量 以及查询中返回的记录数目等 例 在 SQL Plus 中输入下列语句 SQL host tkprof e oracle product 10 2 0 admin PIOrcl udump piorcl ora 388 trc c a txt Oracle 设计开发指南 SZEDI CO LTD 2008 Page 16 of 65 Tkprof 输出文件中主要性能指标的说明 列名列名描述描述 CALL 表示执行这条 SQL 的步骤 包括 Parse 解析 Execute 执行 Fetch 提 取数据 COUNT 这个数字说明 SQL 语句解析 执行或读取的次数 CPU 说明 CPU 为一个特定 SQL 语句执行各个步骤所花费的时间总量 ELAPSED 说明每个步骤从开始到结束所花费的时间总量 DISK 说明每个步骤从磁盘读取的 Oracle 数据块的数量 QUERY 以一致性模式从缓存区获得数据的次数 CURRENT 以当前模式从缓存区获得数据的次数 ROWS 返回调用或执行调用时 处理的数据行的数量 3 4 33 4 3STATSPACKSTATSPACK 工具工具 Oracle Statspack 从 Oracle8 1 6 开始被引入 Oracle 并马上成为 DBA 和 Oracle 专家用来诊断 数据库性能的强有力的工具 通过 Statspack 我们可以很容易的确定 Oracle 数据库的瓶颈所在 记录 数据库性能状态 也可以使远程技术支持人员迅速了解你的数据库运行状况 Statspack 的脚本位于 ORACLE HOME RDBMS ADMIN spcreate sql 3 4 3 13 4 3 1安装安装 STATSPACKSTATSPACK 1 为了能够顺利安装和运行 Statspack 你可能需要设置以下系统参数 job queue processes 为了能够建立自动任务 执行数据收集 该参数需要大于 0 你可以在初试化参数文件中修改该 参数 使该参数在重启后以然有效 该参数可以在系统级动态修改 重启后失效 在 SQL Plus 中输入 下列语句 SQL alter system set job queue processes 6 scope both timed statistics 收集操作系统的计时信息 这些信息可被用来显示时间等统计信息 优化数据库和 SQL 语句 要 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 17 of 65 防止因从操作系统请求时间而引起的开销 请将该值设置为 False 使用 statspack 收集统计信息时 建议将该值设置为 TRUE 否则收集的统计信息大约只能起到 10 的作用 将 timed statistics 设置 为 True 所带来的性能影响与好处相比是微不足道的 该参数使收集的时间信息存储在 V SESSTATS 和 V SYSSTATS 等动态性能视图中 Timed statistics 参数可以在实例级进行更改 在 SQL Plus 中输入 下列语句 SQL alter system set timed statistics true 2 创建 Statspack 需要使用的表空间和用户 CREATE SMALLFILE TABLESPACE STATSPACK DATA LOGGING DATAFILE E ORACLE PRODUCT 10 2 0 ORADATA PIORCL STATSPACK DATA ora SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO CREATE SMALLFILE TEMPORARY TABLESPACE STATSPACK TEMP TEMPFILE E ORACLE PRODUCT 10 2 0 ORADATA PIORCL STATSPACK TEMP ora SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M CREATE USER STATSPACK PROFILE DEFAULT IDENTIFIED BY STATSPACK DEFAULT TABLESPACE STATSPACK DATA TEMPORARY TABLESPACE STATSPACK TEMP ACCOUNT UNLOCK GRANT CONNECT TO STATSPACK GRANT DBA TO STATSPACK 3 安装 Statspack 安装 Statspack 需要拥有 SYSDBA connect as sysdba 权限的用户登陆 在 Oracle8 1 7 以后 版本中运行 spcreate sql 首先登陆到数据库 最好转到 ORACLE HOME RDBMS ADMIN 目录 这样我们 执行脚本就可以方便些 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 18 of 65 C cd E oracle product 10 2 0 db 1 RDBMS ADMIN C cd e E oracle product 10 2 0 db 1 RDBMS ADMIN sqlplus 输入用户名 sys as sysdba 密码不用输入 连接到 sqlplus SQL spcreate 输入 default tablespace 的值 statspack data 输入 temporary tablespace 的值 statspack temp 输入 perfstat 口令 statspack 执行完成 提示如下信息表示创建成功 3 4 3 23 4 3 2生成分析报告生成分析报告 1 用 statspack 用户帐号登录 SQL Plus 2 抓取快照 至少抓取 2 次以上 在 SQL Plus 中输入下列语句 Sql exec statspack snap 3 生成分析报告 在 SQL Plus 中输入下列语句 Sql spreport 按照提示 依次输入 begin snap end snap 和 report name 值 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 19 of 65 生成的分析报告位于 ORACLE HOME RDBMS ADMIN 目录下 3 4 3 33 4 3 3STATSPACKSTATSPACK 收集选项收集选项 Statspack 有两种类型的收集选项 级别 level 控制收集数据的类型 门限 threshold 设置收集的数据的阈值 1 级别 level Statspack 共有三种快照级别 默认值是 level 5 级别级别描述描述 level 0 一般性能统计 包括等待事件 系统事件 系统统计 回滚段统计 行缓存 SGA 会话 锁 缓冲池统计等等 level 5 增加 SQL 语句 除了包括 level0 的所有内容 还包括 SQL 语句的收集 收集 结果记录在 stats sql summary 中 level 10 增加子锁存统计 包括 level5 的所有内容 并且还会将附加的子锁存存入 stats lathc children 中 在使用这个级别时需要慎重 建议在 Oracle support 的指导下进行 可以通过 statspack 包修改缺省的级别设置 SQL execute statspack snap i snap level 0 i modify parameter true 如果你只是想本次改变收集级别 可以忽略 i modify parameter 参数 2 SQL execute statspack snap i snap level 10 快照门限 快照门限只应用于 stats sql summary 表中获取的 SQL 语句 因为每一个快照都会收集很多数据 每一行都代表获取快照时数据库中的一个 SQL 语句 所以 stats sql summary 很快就会成为 Statspack 中最大的表 门限存储在 stats statspack parameter 表中 让我们了结一下各种门限 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 20 of 65 门限门限描述描述 executions th 这是 SQL 语句执行的数量 默认值是 100 disk reads tn 这是 SQL 语句执行的磁盘读入数量 默认值是 1000 parse calls th 这是 SQL 语句执行的解析调用的数量 默认值是 1000 buffer gets th 这是 SQL 语句执行的缓冲区获取的数量 默认值是 10000 任何一个门限值超过以上参数就会产生一条记录 通过调用 statspack modify statspack parameter 函数我们可以改变门限的默认值 例如 SQL execute statspack modify statspack parameter i buffer gets th 100000 i disk reads th 100000 3 4 3 43 4 3 4STATSPACKSTATSPACK 分析报告详解分析报告详解 statspack 输出结果中必须查看的十项内容 序号序号内容内容 1 负载间档 Load profile 2 实例效率点击率 Instance efficiency hit ratios 3 首要的 5 个等待事件 Top 5 wait events 4 等待事件 Wait events 5 闩锁等待 6 首要的 SQL Top sql 7 实例活动 Instance activity 8 文件 I O File I O 9 内存分配 Memory allocation 10 缓冲区等待 Buffer waits 1 报表头信息 数据库实例相关信息 包括数据库名称 ID 版本号及主机等信息 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 21 of 65 STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC 2788086863 piorcl 1 28 1 月 10 08 0 10 2 0 1 0 NO 6 Host Name TYL Num CPUs 2 Phys Memory MB 2 047 Snapshot Snap Id Snap Time Sessions Curs Sess Comment Begin Snap 1 28 1 月 10 13 54 23 23 11 7 End Snap 12 28 1 月 10 14 11 26 23 13 8 Elapsed 17 05 mins Cache Sizes Begin End Buffer Cache 472M Std Block Size 8K Shared Pool Size 96M Log Buffer 6 860K 2 负载间档 该部分提供每秒和每个事物的统计信息 是监控系统吞吐量和负载变化的重要部分 Load Profile Per Second Per Transaction Redo size 3 866 73 19 390 49 Logical reads 78 81 395 22 Block changes 13 02 65 31 Physical reads 0 04 0 18 Physical writes 1 31 6 57 User calls 1 91 9 55 Parses 4 92 24 68 Hard parses 0 83 4 17 Sorts 3 86 19 33 Logons 0 04 0 20 Executes 12 01 60 25 Transactions 0 20 Blocks changed per Read 16 52 Recursive Call 98 41 Rollback per transaction 3 92 Rows per Sort 9 35 负载间档的统计指标的说明如下表 指标指标描述描述 Redo size 每秒产生的重做日志大小 单位字节 可标志数据变更频率 数据库任务 的繁重与否 Oracle 设计开发指南 SZEDI CO LTD 2008 Page 22 of 65 Logical reads 平次每秒产生的逻辑读 单位是 block block changes 每秒 block 变化数量 数据库事物带来改变的块数量 Physical reads 平均每秒数据库从磁盘读取的 block 数 Physical writes 平均每秒数据库写磁盘的 block 数 User calls 每秒用户 call 次数 Parses 和 Hard parses每秒大约 4 92 个解析 其中有 20 为硬解析 对于优化好的系统 运行了 好几天后 这一列应该达到 0 所有的 sql 在一段时间后都应该在共享池中 Sorts 每秒产生的排序次数 Executes 每秒执行次数 Transactions 每秒产生的事务数 反映数据库任务繁重与否 Blocks changed per Read 说明 83 48 的逻辑读是用于那些只读的而不是可修改的块 该系统只更新 16 52 的块 Rollback per transaction 事务回滚的百分比 计算公式为 Round User rollbacks user commits user rollbacks 4 100 本例中每 3 92 个事务导致一个 回滚 如果回滚率过高 可能说明数据库经历了太多的无效操作 过多的 回滚可能还会带来 Undo Block 的竞争 3 实例命中率 该部分可以提前找出 ORACLE 潜在将要发生的性能问题 很重要 Instance Efficiency Percentages Buffer Nowait 100 00 Redo NoWait 100 00 Buffer Hit 99 96 In memory Sort 100 00 Library Hit 85 61 Soft Parse 83 12 Execute to Parse 59 03 Latch Hit 99 98 Parse CPU to Parse Elapsd 91 11 Non Parse CPU 87 49 实例命中率的统计指标的说明如下表 指标指标描述描述 Buffer Nowait 在缓冲区中获取 Buffer 的未等待比率 Buffer Nowait 99 说明 有可能是 有热块 查找 x bh 的 tch 和 v latch children 的 cache buffers chains Redo NoWait 在 Redo 缓冲区获取 Buffer 的未等待比率 Buffer Hit 数据块在数据缓冲区中的命中率 通常应在 90 以上 否则 小于 95 需要 调整重要的参数 小于 90 可能是要加 db cache size 但是大量的非选择的 索引也会造成该值很高 大量的 db file sequential read 如果一个经常 访问的列上的索引被删除 可能会造成 buffer hit 显著下降 如果增加了索 引 但是它影响了 ORACLE 正确的选择表连接时的驱动顺序 那么可能会导致 buf