Oracle 11g R2 DBA 操作指南 林树泽 编著 清华大学出版社 内 容 简 介 Oracle 数据库是一款优秀且应用广泛的关系数据库管理系统。本书全面、详细地讲解了 Oracle 11g R2 数 据库管理技术，是学习 Oracle 数据库管理的实用教材和参考书。 本书共分 26 章，通过几百个范例详尽讲解了 Oracle 11g R2 数据库安装与卸载、SQL 语言、各种数据库 对象、数据库备份与恢复、用户与系统管理、企业管理器（EM），以及数据库性能优化等技术。书中每章的 内容不仅概念清晰、操作步骤明了、示例丰富，而且更侧重于满足实际工作的需要。 本书适合 Oracle 数据库系统管理的初学者以及 Oracle 11g R2 DBA 参考。书中的内容覆盖了 OCA 考试的 所有知识点，同样适合参加 OCA 考试的读者。 本书封面贴有清华大学出版社防伪标签，无标签者不得销售 版权所有，侵权必究 侵权举报电话：010-62782989 13701121933 图书在版编目（CIP）数据 Oracle 11g R2 DBA 操作指南/林树泽，卢芬编著. －北京：清华大学出版社，2013 ISBN 978-7-302-33910-6 I. ①O… II. ①林… III. ①关系数据库系统－指南 IV. ①TP311.138-62 中国版本图书馆 CIP 数据核字（2013）第 220416 号 责任编辑：夏非彼 封面设计：王 翔 责任校对：闫秀华 责任印制： 出版发行：清华大学出版社 网 址：http://www.tup.com.cn，http://www.wqbook.com 地 址：北京清华大学学研大厦 A 座 邮 编：100084 社 总 机：010-62770175 邮 购：010-62786544 投稿与读者服务：010-62776969，c-service@tup.tsinghua.edu.cn 质量反馈：010-62772015，zhiliang@tup.tsinghua.edu.cn 印 刷 者： 装 订 者： 经 销：全国新华书店 开 本：190mm×260mm 印 张：36.75 字 数：940 千字 版 次：2013 年 10 月第 1 版 印 次：2013 年 10 月第 1 次印刷 印 数：1~3000 定 价：85.00 元 产品编号：046901-01 前 言 本书是一本介绍如何学习 Oracle 11g R2 数据库的入门书籍。Oracle 数据库已经成为当今市场 的主流数据库产品。目前 Oracle 家族中已经不仅仅限于数据库这一产品，还包括操作系统、中间 件等，Oracle 已经收购了 MySQL、SUN 等，其市场份额远远超过其他任何数据库产品，国内几乎 所有大型企业，以及政府部门、军方都在使用 Oracle 数据库。 此领域市场职位缺口巨大，尤其是中高端人才更是紧缺。众多初学者都想掌握 Oracle 数据库 技术，并迈入 Oracle 高端人才行列，这就产生了对入门技术的庞大需求，而 Oracle 也顺应此潮流 推出了 OCA 认证体制，本书覆盖 OCA 考试的绝大部分知识点，也是初学者必须掌握的基础知识。 本书结合笔者多年的 Oracle 数据库学习和维护经验，希望它可以成为 Oracle 数据库初学者的必备 书籍，也希望准备 OCA 考试的人员把它作为一本很好的参考教材，同时书中对于知识点的介绍更 多的是站在系统的全局角度考虑，读者可以从中获得全新的认识和实践体验。 本书内容 本书是一本全面讲述 Oracle 11g R2 数据库系统管理的图书，之所以选择 Oracle 11g R2 这个版 本是因为 Oracle 已经不再提供对 Oracle 10g 的技术支持，Oracle 11g R2 版本是其目前支持的版本， 这个版本的技术支持延续到 2017 年。本书开篇针对初学者非常详细地介绍了如何安装和配置 Oracle 数据库，这样读者能快速配置一个学习环境。通过本书的学习，读者能够全面地掌握 Oracle 数据库的体系架构，以及数据库管理、数据库安全、数据库优化和高可用等 DBA 所需要掌握的知 识和技能。通过本书的学习和实践，相信读者能够完成基本的数据库管理工作，成为一名合格的 Oracle 数据库初级管理员。 全书共分 26 章，包括 Oracle 数据库基础、数据库启动与关闭、Oracle 数据库体系结构、SQL 语言概述、数据字典、网络连接管理、内存管理、用户管理和资源文件、管理控制文件、重做日志 管理、管理归档日志、表空间和数据文件、UNDO 表空间管理、事务管理、角色管理、表管理、 索引管理、系统和对象权限管理、视图、序列号和同义词、RMAN 备份与恢复数据库、闪回技术、 手工管理的备份恢复、企业管理器（EM）、数据库优化等内容。 本书特点  知识体系涵盖 Oracle DBA 管理应掌握的各方面知识，覆盖了跨平台下 Oracle DBA 管理 应具备的各方面知识和技能。  内容全面而深入，实例得当，切中 DBA 关注要点，描述内容由浅而深，详尽细致，能够 帮助读者较好地掌握 DBA 知识和技能。  注重实践和应用，从数据库管理、开发、安全、性能优化和高可用等数据库管理员最关 注的方面做了详尽的描述，使读者在实际应用时能够快速上手，并且在遇到问题时能够 Oracle 数据库管理之道 II 在本书中获得学习参考。可谓一册在手，管理无忧。  实例详尽、图文并茂、示例清晰、所有案例均在实践环境中经过检验。 本书可以作为 Oracle 11g R2 数据库操作手册，可供 Oracle 数据库管理员、Oracle 数据库应用 开发人员、Oracle 数据仓库工程师使用，还可以作为 Oracle 技术支持和培训机构、高等院校数据 库课程的参考教材。 参与本书写作的除了林树泽、卢芬外，还有历铁帅、何会军、李渊、陈玉等人，他们为本书 的创作做了大量的工作，在此表示衷心的感谢。 编 者 2013 年 8 月 目 录 第 1 章 Oracle 数据库基础.............................................................................................. 1 1.1 什么是关系数据库................................................................................................................................. 1 1.1.1 关系数据库模型 ........................................................................................................................ 1 1.1.2 关系数据模型的创始人 ............................................................................................................ 2 1.2 Oracle 数据库发展简史 ......................................................................................................................... 3 1.2.1 公司之初 .................................................................................................................................... 3 1.2.2 Oracle 数据库的发展历程......................................................................................................... 3 1.3 数据库 RDBMS ...................................................................................................................................... 4 1.4 SQL 语言简介......................................................................................................................................... 5 1.4.1 SQL 语言概述............................................................................................................................ 5 1.4.2 SQL 语句的操作........................................................................................................................ 6 1.5 本章小结................................................................................................................................................. 8 第 2 章 Oracle 11g 数据库初体验 ................................................................................... 9 2.1 安装数据库的环境要求......................................................................................................................... 9 2.2 Windows 环境下 Oracle 11g 的安装步骤............................................................................................. 9 2.3 SQL Plus 工具以及 scott 用户............................................................................................................. 16 2.4 Linux 环境下 Oracle 11g 的安装步骤................................................................................................. 20 2.4.1 安装前的配置任务 .................................................................................................................. 20 2.4.2 安装数据库软件 ...................................................................................................................... 24 2.4.3 启动监听 .................................................................................................................................. 33 2.4.4 使用 DBCA 图形化工具建库 ................................................................................................. 36 2.5 测试到数据库的连接........................................................................................................................... 42 2.6 删除数据库软件................................................................................................................................... 43 2.7 本章小结............................................................................................................................................... 43 第 3 章 数据库的启动与关闭......................................................................................... 44 3.1 启动数据库............................................................................................................................................. 44 3.1.1 数据库启动过程 ...................................................................................................................... 44 3.1.2 数据库启动到 NOMOUNT 状态............................................................................................ 45 3.1.3 数据库启动到 MOUNT 状态.................................................................................................. 49 3.1.4 数据库启动到 OPEN 状态...................................................................................................... 50 Oracle 数据库管理之道 IV 3.2 关闭数据库........................................................................................................................................... 51 3.2.1 数据库关闭过程 ...................................................................................................................... 51 3.2.2 数据库关闭的几个参数及其含义 .......................................................................................... 53 3.3 本章小结............................................................................................................................................... 54 第 4 章 Oracle 数据库体系结构..................................................................................... 55 4.1 Oracle 体系结构概述 ........................................................................................................................... 55 4.2 Oracle 数据库体系结构 ....................................................................................................................... 55 4.2.1 Oracle 服务器和实例............................................................................................................... 56 4.2.2 Oracle 数据库物理结构（文件组成）................................................................................... 58 4.2.3 参数文件、密码文件和归档日志文件 .................................................................................. 58 4.3 数据库连接（connection）与会话（session）.................................................................................. 58 4.3.1 数据库连接（connection）..................................................................................................... 58 4.3.2 会话（session）....................................................................................................................... 59 4.4 Oracle 数据库内存结构 ....................................................................................................................... 60 4.4.1 共享池（Shared pool） ........................................................................................................... 60 4.4.2 数据库高速缓冲区（Database buffer cache）....................................................................... 62 4.4.3 重做日志高速缓冲区（Redo buffer cache） ......................................................................... 64 4.4.4 大池（Large pool）和 Java 池（Java pool）......................................................................... 65 4.4.5 流池（Streaming pool）.......................................................................................................... 66 4.4.6 PGA（进程全局区）和 UGA（用户全局区）..................................................................... 66 4.4.7 如何获得内存缓冲区的信息 .................................................................................................. 68 4.5 Oracle 服务器进程和用户进程 ........................................................................................................... 68 4.6 Oracle 数据库后台进程 ....................................................................................................................... 69 4.6.1 系统监控进程（SMON）....................................................................................................... 69 4.6.2 进程监控进程（PMON）....................................................................................................... 70 4.6.3 数据库写进程（DBWR） ...................................................................................................... 70 4.6.4 重做日志写进程（LGWR）................................................................................................... 71 4.6.5 归档日志进程（ARCH） ....................................................................................................... 72 4.6.6 校验点进程（Checkpoint process）....................................................................................... 74 4.7 本章小结............................................................................................................................................... 74 第 5 章 SQL语言概述................................................................................................... 75 5.1 SQL 语句分类....................................................................................................................................... 75 5.2 SQL 的查询语句................................................................................................................................... 76 5.2.1 SELECT 语句的语法及书写要求........................................................................................... 76 5.2.2 简单查询 .................................................................................................................................. 77 目 录 V 5.2.3 特定的列查询 .......................................................................................................................... 78 5.2.4 WHERE 子句 ........................................................................................................................... 79 5.2.5 列标题的默认显示格式 .......................................................................................................... 79 5.2.6 在 SQL 语句中使用列的别名................................................................................................. 80 5.2.7 算数运算符及使用 .................................................................................................................. 81 5.2.8 DISTINCT 运算符 ................................................................................................................... 81 5.2.9 连接（contatenation）运算符及使用..................................................................................... 83 5.3 书写规范............................................................................................................................................... 83 5.4 单行函数............................................................................................................................................... 84 5.4.1 字符型单行函数 ...................................................................................................................... 84 5.4.2 数字型单行函数 ...................................................................................................................... 88 5.4.3 日期型单行函数 ...................................................................................................................... 89 5.5 空值（NULL）和空值处理函数 ........................................................................................................ 92 5.5.1 什么是空值 .............................................................................................................................. 92 5.5.2 NVL 函数和 NVL2 函数......................................................................................................... 94 5.5.3 NULLIF 函数 ........................................................................................................................... 96 5.5.4 COALESCE 函数..................................................................................................................... 97 5.6 条件表达式........................................................................................................................................... 97 5.6.1 CASE 表达式 ........................................................................................................................... 97 5.6.2 DECODE 函数 ......................................................................................................................... 98 5.7 分组函数............................................................................................................................................... 99 5.7.1 AVG 和 SUM 函数................................................................................................................ 100 5.7.2 MAX 和 MIN 函数 ................................................................................................................ 100 5.7.3 COUNT 函数.......................................................................................................................... 101 5.7.4 GROUP BY 子句 .................................................................................................................. 101 5.7.5 分组函数的嵌套使用 ............................................................................................................ 102 5.7.6 HAVING 子句........................................................................................................................ 102 5.8 数据操纵语言（DML） .................................................................................................................... 103 5.8.1 INSERT 语句 ......................................................................................................................... 103 5.8.2 UPDATE 语句........................................................................................................................ 105 5.8.3 DELETE 语句 ........................................................................................................................ 107 5.9 本章小结............................................................................................................................................... 107 第 6 章 数据字典......................................................................................................... 108 6.1 数据字典中的内容............................................................................................................................. 108 6.2 使用和操作数据字典视图................................................................................................................. 108 6.3 数据字典视图分类............................................................................................................................. 109 Oracle 数据库管理之道 VI 6.4 使用数据字典视图............................................................................................................................. 113 6.5 动态性能视图及使用......................................................................................................................... 115 6.6 本章小结............................................................................................................................................. 118 第 7 章 网络配置管理.................................................................................................. 119 7.1 Oracle 的网络连接 ............................................................................................................................. 119 7.2 服务器端监听器配置......................................................................................................................... 120 7.2.1 动态注册 ................................................................................................................................ 122 7.2.2 静态注册 ................................................................................................................................ 124 7.2.3 连接测试 ................................................................................................................................ 127 7.2.4 监听程序管理 ........................................................................................................................ 129 7.3 客户端配置......................................................................................................................................... 130 7.3.1 本地命名 ................................................................................................................................ 130 7.3.2 简单命名 ................................................................................................................................ 131 7.4 Oracle 数据库服务器支持的两种连接方式 ..................................................................................... 132 7.4.1 服务器进程 ............................................................................................................................ 133 7.4.2 共享连接 ................................................................................................................................ 133 7.4.3 共享连接涉及初始化参数 .................................................................................................... 134 7.4.4 共享连接的工作过程 ............................................................................................................ 134 7.4.5 共享连接的配置 .................................................................................................................... 135 7.4.6 共享连接的一些问题 ............................................................................................................ 139 7.4.7 专有连接 ................................................................................................................................ 139 7.5 数据库驻留连接池............................................................................................................................. 140 7.5.1 DRCP 的工作原理................................................................................................................. 140 7.5.2 如何配置 DRCP..................................................................................................................... 141 7.6 本章小结............................................................................................................................................. 144 第 8 章 内存管理......................................................................................................... 145 8.1 内存架构............................................................................................................................................. 145 8.1.1 PGA 概述 ............................................................................................................................... 146 8.1.2 SGA 概述 ............................................................................................................................... 147 8.1.3 UGA 概述............................................................................................................................... 150 8.2 内存管理............................................................................................................................................. 150 8.2.1 配置内存组件 ........................................................................................................................ 150 8.2.2 SGA 与 PGA 的自动调整 ..................................................................................................... 152 8.2.3 配置数据库 smart flash 缓存................................................................................................. 153 8.3 本章小结............................................................................................................................................. 154 目 录 VII 第 9 章 用户管理和资源文件....................................................................................... 155 9.1 创建用户............................................................................................................................................. 155 9.1.1 初试创建新用户 .................................................................................................................... 155 9.1.2 创建用户语法及参数含义 .................................................................................................... 157 9.1.3 改变用户参数 ........................................................................................................................ 158 9.2 删除用户............................................................................................................................................. 160 9.3 用户和数据库模式............................................................................................................................. 161 9.4 用户管理中的重要文件—概要文件............................................................................................. 162 9.4.1 什么是概要文件 .................................................................................................................... 162 9.4.2 使用资源管理和口令管理的概要文件步骤 ........................................................................ 162 9.4.3 使用概要文件管理会话资源 ................................................................................................ 163 9.4.4 口令管理参数以及含义 ........................................................................................................ 164 9.4.5 创建口令管理的概要文件 .................................................................................................... 167 9.5 修改和删除概要文件......................................................................................................................... 168 9.6 本章小结............................................................................................................................................. 169 第 10 章 控制文件和数据库启动 ................................................................................. 170 10.1 控制文件和数据库启动概述........................................................................................................... 170 10.2 如何获得控制文件的信息............................................................................................................... 171 10.3 控制文件的内容............................................................................................................................... 172 10.3.1 控制文件中所存的内容 ...................................................................................................... 172 10.3.2 如何查看控制文件中所存内容的记录信息 ...................................................................... 172 10.4 存储多重控制文件........................................................................................................................... 174 10.4.1 多重控制文件 ...................................................................................................................... 174 10.4.2 移动控制文件 ...................................................................................................................... 175 10.4.3 添加控制文件 ...................................................................................................................... 178 10.5 备份和恢复控制文件....................................................................................................................... 179 10.5.1 控制文件的备份 .................................................................................................................. 179 10.5.2 控制文件的恢复 .................................................................................................................. 180 10.6 本章小结........................................................................................................................................... 183 第 11 章 重做日志管理................................................................................................ 184 11.1 Oracle 为何引入重做日志 ............................................................................................................... 184 11.2 读取重做日志文件信息................................................................................................................... 185 11.2.1 v$log 视图 ............................................................................................................................ 185 11.2.2 v$logfile 视图....................................................................................................................... 186 11.2.3 判断是否归档 ...................................................................................................................... 187 Oracle 数据库管理之道 VIII 11.2.4 设置数据库为归档模式 ...................................................................................................... 187 11.3 重做日志组及其管理....................................................................................................................... 188 11.3.1 添加重做日志组 .................................................................................................................. 188 11.3.2 删除联机重做日志组 .......................................................................................................... 190 11.4 重做日志成员及维护....................................................................................................................... 192 11.4.1 添加重做日志成员 .............................................................................................................. 192 11.4.2 删除联机重做日志成员 ...................................................................................................... 193 11.4.3 重设联机重做日志的大小 .................................................................................................. 194 11.5 清除联机重做日志........................................................................................................................... 197 11.6 日志切换和检查点事件................................................................................................................... 197 11.7 归档重做日志................................................................................................................................... 198 11.8 本章小结........................................................................................................................................... 198 第 12 章 管理归档日志................................................................................................ 199 12.1 归档模式........................................................................................................................................... 199 12.2 设置归档模式................................................................................................................................... 200 12.3 设置归档进程与归档目录............................................................................................................... 201 12.4 维护归档目录................................................................................................................................... 207 12.5 本章小结........................................................................................................................................... 211 第 13 章 表空间与数据文件管理 ................................................................................. 212 13.1 Oracle 数据库的逻辑结构 ............................................................................................................... 212 13.2 表空间的分类以及创建表空间....................................................................................................... 214 13.3 表空间磁盘管理的两种方案........................................................................................................... 217 13.3.1 数据字典管理的表空间磁盘管理 ...................................................................................... 217 13.3.2 本地管理的表空间磁盘管理 .............................................................................................. 217 13.4 创建表空间....................................................................................................................................... 217 13.4.1 创建数据字典管理的表空间 .............................................................................................. 218 13.4.2 创建本地管理的表空间 ...................................................................................................... 219 13.4.3 创建还原表空间 .................................................................................................................. 220 13.4.4 创建临时表空间 .................................................................................................................. 222 13.4.5 默认临时表空间 .................................................................................................................. 224 13.4.6 创建大文件表空间 .............................................................................................................. 226 13.5 表空间管理....................................................................................................................................... 229 13.5.1 脱机管理 .............................................................................................................................. 229 13.5.2 只读管理 .............................................................................................................................. 231 13.6 表空间和数据文件管理................................................................................................................... 234 目 录 IX 13.6.1 修改表空间大小 .................................................................................................................. 234 13.6.2 修改表空间的存储参数 ...................................................................................................... 237 13.6.3 删除表空间 .......................................................................................................................... 238 13.6.4 迁移数据文件 ...................................................................................................................... 238 13.6.5 数据字典和本地管理的表空间 .......................................................................................... 241 13.7 本章小结........................................................................................................................................... 242 第 14 章 UNDO 表空间管理........................................................................................ 243 14.1 引入还原段的作用........................................................................................................................... 243 14.2 还原段如何完成读一致性............................................................................................................... 244 14.2.1 Oracle 如何实现读一致性................................................................................................... 244 14.2.2 读一致性的进一步复杂化分析 .......................................................................................... 244 14.2.3 读一致性的具体步骤 .......................................................................................................... 245 14.3 还原段的实例恢复与事务回滚....................................................................................................... 246 14.4 UNDO SEGMENT 的选择算法....................................................................................................... 246 14.5 讨论 undo_retention 参数................................................................................................................. 246 14.6 还原段分类....................................................................................................................................... 248 14.7 Oracle 的自动还原段管理 ............................................................................................................... 249 14.8 创建还原表空间............................................................................................................................... 250 14.9 维护还原表空间............................................................................................................................... 251 14.10 切换还原表空间............................................................................................................................. 253 14.10.1 UNDO 表空间切换示例.................................................................................................... 253 14.10.2 UNOD 表空间切换涉及状态............................................................................................ 254 14.10.3 删除 UNDO 表空间示例................................................................................................... 254 14.11 dba_undo_extents 数据字典........................................................................................................... 256 14.12 本章小结......................................................................................................................................... 257 第 15 章 事务（Transaction） .................................................................................... 258 15.1 Oracle 事务的由来 ........................................................................................................................... 258 15.2 什么是事务....................................................................................................................................... 258 15.3 事务的特点....................................................................................................................................... 259 15.4 事务控制........................................................................................................................................... 259 15.4.1 使用 COMMIT 的显式事务控制........................................................................................ 259 15.4.2 使用 ROLLBACK 实现事务控制....................................................................................... 261 15.4.3 程序异常退出对事务的影响 .............................................................................................. 262 15.4.4 使用 AUTOCOMMIT 实现事务的自动提交..................................................................... 264 15.5 本章小结........................................................................................................................................... 265 Oracle 数据库管理之道 X 第 16 章 角色管理....................................................................................................... 266 16.1 什么是角色....................................................................................................................................... 266 16.2 创建角色........................................................................................................................................... 267 16.3 修改角色........................................................................................................................................... 269 16.4 赋予角色权限................................................................................................................................... 270 16.5 赋予用户角色................................................................................................................................... 271 16.6 默认角色........................................................................................................................................... 274 16.7 禁止和激活角色............................................................................................................................... 277 16.8 回收和删除角色............................................................................................................................... 279 16.9 Oracle 预定义的角色 ....................................................................................................................... 281 16.10 本章小结......................................................................................................................................... 283 第 17 章 管理和维护表................................................................................................ 284 17.1 Oracle 基本的数据存储机制—表 ............................................................................................... 284 17.1.1 数据的存储类型 .................................................................................................................. 284 17.1.2 行 ID（ROWID）................................................................................................................ 286 17.2 创建表............................................................................................................................................... 287 17.2.1 Oracle 创建表的规则........................................................................................................... 287 17.2.2 创建普通表 .......................................................................................................................... 287 17.2.3 创建临时表 .......................................................................................................................... 289 17.3 段空间管理....................................................................................................................................... 291 17.4 理解高水位线（HWM）................................................................................................................. 292 17.5 理解行迁移....................................................................................................................................... 292 17.6 创建索引组织表（IOT） ................................................................................................................ 293 17.6.1 IOT 表的结构....................................................................................................................... 293 17.6.2 创建 IOT 表.......................................................................................................................... 294 17.7 表参数以及参数维护....................................................................................................................... 295 17.8 维护列............................................................................................................................................... 297 17.9 删除和截断表................................................................................................................................... 302 17.10 本章小结......................................................................................................................................... 304 第 18 章 索引 .............................................................................................................. 305 18.1 索引的概念....................................................................................................................................... 305 18.2 Oracle 实现数据访问的方法 ........................................................................................................... 305 18.2.1 全表扫描（FULL TABLE SCAN)...................................................................................... 306 18.2.2 通过行 ID（ROWID）........................................................................................................ 306 目 录 XI 18.2.3 使用索引 .............................................................................................................................. 307 18.3 索引扫描类型................................................................................................................................... 308 18.3.1 索引唯一扫描（INDEX UNIQUE SCAN)......................................................................... 308 18.3.2 索引范围扫描（INDEX RANGE SCAN) .......................................................................... 309 18.3.3 索引全扫描（INDEX FULL SCAN).................................................................................. 309 18.3.4 索引快速扫描（INDEX FAST FULL SCAN) ................................................................... 310 18.4 限制索引使用的情况....................................................................................................................... 310 18.4.1 使用不等于运算符 .............................................................................................................. 310 18.4.2 使用 IS NULL 或 IS NOT NULL....................................................................................... 311 18.4.3 使用函数 .............................................................................................................................. 312 18.4.4 比较不匹配的数据类型 ...................................................................................................... 313 18.5 集群因子........................................................................................................................................... 314 18.6 二元高度........................................................................................................................................... 314 18.7 直方图............................................................................................................................................... 315 18.8 建立索引........................................................................................................................................... 316 18.9 查看索引........................................................................................................................................... 319 18.10 B 树索引 ......................................................................................................................................... 320 18.10.1 B 树索引的工作原理......................................................................................................... 320 18.10.2 B 树索引的注意事项......................................................................................................... 321 18.11 位图索引......................................................................................................................................... 321 18.11.1 位图索引的使用讨论 ........................................................................................................ 321 18.11.2 创建位图索引 .................................................................................................................... 322 18.11.3 B 位图索引的插入问题..................................................................................................... 323 18.12 Hash 索引........................................................................................................................................ 324 18.13 反向键索引..................................................................................................................................... 326 18.14 基于函数的索引............................................................................................................................. 326 18.15 监控索引的使用............................................................................................................................. 328 18.16 重建索引......................................................................................................................................... 329 18.17 维护索引......................................................................................................................................... 331 18.18 删除索引......................................................................................................................................... 332 18.19 本章小结......................................................................................................................................... 333 第 19 章 系统和对象权限管理..................................................................................... 334 19.1 权限的概念和分类........................................................................................................................... 334 19.2 系统权限........................................................................................................................................... 334 19.3 授予用户系统权限........................................................................................................................... 335 Oracle 数据库管理之道 XII 19.4 SYSDBA 和 SYSOPER 系统特权................................................................................................... 339 19.5 回收用户系统权限........................................................................................................................... 340 19.6 授予对象权限................................................................................................................................... 343 19.7 回收对象权限................................................................................................................................... 345 19.8 本章小结........................................................................................................................................... 346 第 20 章 视图 .............................................................................................................. 347 20.1 什么是视图....................................................................................................................................... 347 20.2 创建视图........................................................................................................................................... 347 20.3 使用视图的 WITH 子句................................................................................................................... 350 20.4 视图的修改....................................................................................................................................... 352 20.5 Oracle 的视图管理 ........................................................................................................................... 354 20.5.1 通过数据字典查询视图 ...................................................................................................... 354 20.5.2 Oracle 视图查询的内部过程............................................................................................... 354 20.6 视图 DML 操作的限制 .................................................................................................................... 355 20.6.1 简单视图 .............................................................................................................................. 355 20.6.2 复杂视图 .............................................................................................................................. 355 20.7 视图的优点....................................................................................................................................... 356 20.8 删除视图........................................................................................................................................... 356 20.9 物化视图........................................................................................................................................... 357 20.9.1 什么是物化视图 .................................................................................................................. 357 20.9.2 查询重写的概念 .................................................................................................................. 357 20.9.3 物化视图的同步 .................................................................................................................. 358 20.9.4 创建物化视图 ...................................................................................................................... 359 20.9.5 物化视图的使用环境 .......................................................................................................... 361 20.10 本章小结......................................................................................................................................... 362 第 21 章 序列号和同义词 ............................................................................................ 363 21.1 什么是序列号................................................................................................................................... 363 21.2 创建和使用序列号........................................................................................................................... 363 21.3 修改序列号....................................................................................................................................... 366 21.4 删除序列号....................................................................................................................................... 369 21.5 什么是同义词................................................................................................................................... 369 21.6 创建公有同义词............................................................................................................................... 370 21.7 创建私有同义词............................................................................................................................... 371 21.8 删除同义词....................................................................................................................................... 372 目 录 XIII 21.9 切换用户模式................................................................................................................................... 373 21.10 本章小结......................................................................................................................................... 373 第 22 章 RMAN 备份与恢复数据库 ............................................................................. 374 22.1 RMAN 概述...................................................................................................................................... 374 22.2 RMAN 的独特之处.......................................................................................................................... 374 22.3 RMAN 系统架构详解...................................................................................................................... 375 22.4 快闪恢复区（flash recovery area）................................................................................................. 376 22.4.1 修改快闪恢复区大小 .......................................................................................................... 376 22.4.2 解决快闪恢复区的空间不足问题 ...................................................................................... 378 22.5 建立 RMAN 到数据库的连接......................................................................................................... 379 22.6 RMAN 的相关概念与配置参数...................................................................................................... 380 22.7 RMAN 备份控制文件...................................................................................................................... 382 22.8 RMAN 实现脱机备份...................................................................................................................... 384 22.9 RMAN 联机备份.............................................................................................................................. 385 22.9.1 联机备份前的准备工作 ...................................................................................................... 385 22.9.2 联机备份整个数据库 .......................................................................................................... 387 22.9.3 联机备份一个表空间 .......................................................................................................... 390 22.9.4 联机备份一个数据文件 ...................................................................................................... 391 22.9.5 RMAN 备份坏块处理方式 ................................................................................................. 392 22.10 RMAN 的增量备份........................................................................................................................ 393 22.11 快速增量备份................................................................................................................................. 395 22.12 在映像副本上应用增量备份......................................................................................................... 396 22.13 创建和维护恢复目录..................................................................................................................... 398 22.14 RMAN 的脚本管理........................................................................................................................ 401 22.15 使用 RMAN 非归档模式下的完全恢复....................................................................................... 403 22.15.1 控制文件、数据文件以及重做日志文件丢失的恢复 .................................................... 403 22.15.2 只有数据文件丢失的恢复 ................................................................................................ 407 22.15.3 联机重做日志文件和数据文件损坏的恢复 .................................................................... 409 22.15.4 如何将数据文件恢复到其他磁盘目录下 ........................................................................ 412 22.16 使用 RMAN 归档模式下的完全恢复........................................................................................... 412 22.16.1 非系统表空间损坏的恢复 ................................................................................................ 412 22.16.2 系统表空间损坏的恢复 .................................................................................................... 415 22.16.3 所有数据文件丢失的恢复 ................................................................................................ 416 22.17 RMAN 实现数据块恢复................................................................................................................ 416 22.18 RMAN 的备份维护指令................................................................................................................ 420 Oracle 数据库管理之道 XIV 22.18.1 RMAN 的 VALIDATE BACKUPSET 指令..................................................................... 420 22.18.2 RMAN 的 RESTORE…VALIDATE 指令 ....................................................................... 421 22.18.3 RMAN 的 RESTORE…PREVIEW 指令.......................................................................... 422 22.18.4 RMAN 的 LIST 指令......................................................................................................... 423 22.18.5 RMAN 的 REPORT 指令.................................................................................................. 426 22.19 本章小结......................................................................................................................................... 427 第 23 章 Oracle 闪回技术............................................................................................ 428 23.1 理解闪回级别................................................................................................................................... 428 23.2 闪回数据库....................................................................................................................................... 428 23.2.1 闪回数据库概述 .................................................................................................................. 428 23.2.2 启用闪回数据库 .................................................................................................................. 429 23.2.3 关闭闪回数据库 .................................................................................................................. 432 23.2.4 闪回数据库方法 .................................................................................................................. 434 23.2.5 使用闪回数据库 .................................................................................................................. 434 23.2.6 监控闪回数据库 .................................................................................................................. 437 23.2.7 使用闪回数据库的限制 ...................................................................................................... 438 23.3 闪回删除........................................................................................................................................... 439 23.3.1 闪回删除原理 ...................................................................................................................... 439 23.3.2 回收站的使用 ...................................................................................................................... 440 23.3.3 恢复删除的表 ...................................................................................................................... 442 23.3.4 恢复多个同名的表 .............................................................................................................. 446 23.3.5 应用 Purge 永久删除表....................................................................................................... 448 23.4 闪回表............................................................................................................................................... 450 23.5 闪回版本查询................................................................................................................................... 453 23.6 闪回事务查询................................................................................................................................... 454 23.7 闪回查询........................................................................................................................................... 455 23.8 复原点技术....................................................................................................................................... 456 23.9 本章小结........................................................................................................................................... 457 第 24 章 手工管理的备份恢复..................................................................................... 459 24.1 备份恢复的概念............................................................................................................................... 459 24.1.1 物理备份 .............................................................................................................................. 459 24.1.2 逻辑备份 .............................................................................................................................. 460 24.1.3 冷备份与热备份 .................................................................................................................. 460 24.1.4 数据库恢复 .......................................................................................................................... 460 24.2 非归档模式下的冷备与恢复........................................................................................................... 461 目 录 XV 24.2.1 冷备的步骤 .......................................................................................................................... 462 24.2.2 冷备下的恢复 ...................................................................................................................... 464 24.2.3 缺少重做日志文件的恢复方法 .......................................................................................... 466 24.3 归档模式与非归档模式................................................................................................................... 468 24.3.1 设置数据库的归档模式 ...................................................................................................... 468 24.3.2 设置归档进程相关参数 ...................................................................................................... 470 24.3.3 管理归档文件和归档目录 .................................................................................................. 471 24.4 手工热备数据库的步骤................................................................................................................... 474 24.5 热备过程中对数据库崩溃的处理方法........................................................................................... 477 24.6 热备的原理....................................................................................................................................... 480 24.7 备份控制文件................................................................................................................................... 481 24.8 介质恢复的原理............................................................................................................................... 484 24.9 归档模式下的完全恢复................................................................................................................... 489 24.9.1 数据文件在有备份情况下的恢复 ...................................................................................... 490 24.9.2 数据文件在无备份情况下的恢复 ...................................................................................... 493 24.9.3 系统表空间数据文件损坏的完全恢复 .............................................................................. 495 24.9.4 当前 UNDO 表空间损坏的完全恢复................................................................................. 497 24.9.5 非当前 UNDO 表空间损坏的完全恢复............................................................................. 499 24.10 何时使用不完全恢复..................................................................................................................... 502 24.10.1 不完全恢复的场合 ............................................................................................................ 502 24.10.2 不完全恢复的类型 ............................................................................................................ 502 24.11 所有控制文件丢失的恢复方法..................................................................................................... 503 24.11.1 使用备份的控制文件 ........................................................................................................ 503 24.11.2 重建控制文件 .................................................................................................................... 507 24.12 本章小结......................................................................................................................................... 511 第 25 章 OEM管理与使用 .......................................................................................... 512 25.1 OEM 架构 ......................................................................................................................................... 512 25.2 OEM 的安装 ..................................................................................................................................... 513 25.2.1 第一种安装方式 .................................................................................................................. 513 25.2.2 第二种安装方式 .................................................................................................................. 514 25.2.3 第三种安装方式 .................................................................................................................. 516 25.3 OEM 的启动与关闭 ......................................................................................................................... 518 25.4 OEM 监控数据库运行 ..................................................................................................................... 520 25.4.1 Home 目录............................................................................................................................ 521 25.4.2 Performance 部分................................................................................................................. 522 Oracle 数据库管理之道 XVI 25.4.3 Availablity 部分 ................................................................................................................... 523 25.4.4 Server 部分........................................................................................................................... 528 25.4.5 Schema 部分......................................................................................................................... 530 25.4.6 Data Movement 部分............................................................................................................ 531 25.4.7 Software and Support 部分................................................................................................... 531 25.5 本章小结........................................................................................................................................... 533 第 26 章 Oracle 数据库实例优化................................................................................. 534 26.1 详解 SGA 与实例优化..................................................................................................................... 534 26.2 将程序常驻内存............................................................................................................................... 539 26.2.1 创建软件包 DBMS_SHARED_POOL................................................................................ 539 26.2.2 将程序常驻内存的过程 ...................................................................................................... 541 26.2.3 从 DBMSPOOL 脚本理解软件包 DBMS_SHARED_POOL............................................ 543 26.3 将数据常驻内存............................................................................................................................... 545 26.3.1 再论数据块缓存池 .............................................................................................................. 545 26.3.2 将数据常驻内存的过程 ...................................................................................................... 546 26.3.3 将常驻内存的程序恢复为默认缓冲池 .............................................................................. 549 26.4 优化重做日志缓冲区....................................................................................................................... 550 26.4.1 深入理解重做日志缓冲区的工作机制 .............................................................................. 550 26.4.2 重做日志缓冲区相关的等待事件 ...................................................................................... 552 26.4.3 设置重做日志缓冲区大小 .................................................................................................. 554 26.5 优化共享池（Shared Pool）............................................................................................................ 556 26.5.1 库高速缓存 .......................................................................................................................... 556 26.5.2 使用绑定变量 ...................................................................................................................... 556 26.5.3 调整 CURSOR_SHARING 参数......................................................................................... 559 26.5.4 设置共享池的大小 .............................................................................................................. 560 26.6 优化数据库高速缓存（DB Cache）............................................................................................... 561 26.6.1 调整数据库缓冲区大小 ...................................................................................................... 561 26.6.2 使用缓冲池 .......................................................................................................................... 563 26.7 优化 PGA 内存................................................................................................................................. 565 26.8 本章小结........................................................................................................................................... 568 第 2 章 ◄ Oracle 11g数据库初体验 ► 学习 Oracle 数据库需要理论与实践并重，边看书边实践是最快的学习方法，所以在学习 之前读者首先需要学会安装 Oracle 11g 数据库软件并创建数据库，学会使用基本的数据库管 理工具访问数据库。在 Oracle 的不同数据版本中，数据库软件对于计算机硬件的要求不同， 基本趋势是版本越高对计算机硬件要求越高，数据库管理越趋于自动化，主要体现在对内存 和 CPU 的要求。本章我们介绍安装数据库软件的操作系统需求以及安装过程，演示在 Windows 系统和 Linux 系统上安装 Oracle 11g 数据库的系统需求以及创建过程。最后介绍如 何删除数据库软件。 2.1 安装数据库的环境要求 数据库软件是运行在操作系统上的，它毕竟要消耗操作系统的各种资源如内存、CPU 以及 I/O 等，所以在安装 Oracle 数据库软件之前最好阅读相关随机文档，获得该软件对于操作系统的要求。 然后再安装数据库软件。 Oracle 数据库软件可以安装在 Windows、Linux、HP-UNIX、AIX 等不同操作系统平台上。同 样对于各自的平台，不同版本的 Oracle 数据库软件对于系统硬件要求不同，下面是 Oracle 11g Release 11.2.0.1.0 数据库在 WINDOWS 32-Bit 系统上对于硬件的需求：  物理内存（RAM）最小为 1GB  虚拟内存为物理内存的 2 倍  高级安装的磁盘空间为 5.15GB  Intel 兼容处理器  Video adapter 256 colors  显示器分辨率最小为 1024×768 笔者的电脑是 2GB 内存，操作系统为 Windows XP，要安装 Oracle 11g Release11.2.0.1.0 版本 的数据库软件，下面我们给出具体的安装过程。 2.2 Windows环境下Oracle 11g的安装步骤 将下载的软件解压到一个目录下，默认在解压目录创建 database 文件夹，所有解压后的文件 Oracle 11g R2 DBA 操作指南 10 都放在这个文件夹里。启动 Universal Installer，如图 2-1 所示。 图 2-1 启动 Universal Installer 然后会弹出对话框开始数据库软件的安装之旅，这个步骤是安装选项，可以省略，但是在安 装生成数据库时，最好填写完整然后点单击下一步，如图 2-2 所示。 图 2-2 配置安全更新 接下来就是弹出如图 2-3 所示的对话框，提示有 3 个选项：创建和配置数据库、仅安装数据库 软件以及升级现有数据库，显然，第一次安装数据库，我们选择前两项的一个，选择第一个即创建 并配置数据库选项，这个选择的结果是整个安装过程包括两个部分，一个是安装数据库软件即 RDBMS，然后是通过 DBCA 来创建数据库。至于第二个选项是只安装数据库软件，然后再通过工 具或者人工方式创建数据库，第二个选项我们在 Linux 环境下 Oracle 11g 的安装中再介绍。 第 2 章 Oracle 11g 数据库初体验 11 图 2-3 安装选项 在选择创建并配置数据库之后，单击下一步按钮，如图 2-4 所示。要求选择是桌面类还是服务 器类，其说明已经很详细了，这里我们选择桌面类。 图 2-4 选择系统类 然后单击下一步，如图 2-5 所示，这个是典型安装，要求我们填写相关目录、选择字符集以及 数据库类型，并设置数据库口令，这个口令需要一定的复杂度，要求有字母带小写、数字不少于 8 个字符。在打开这个对话框时，其实除了管理口令外其他都是默认选项，对于 Oracle 基目录、软 件位置和数据库文件位置，安装软件会自动探测一个满足硬盘大小需求的磁盘，自动填写以上目录。 对于字符集默认是选择与操作系统一致的字符集。在典型安装这个步骤中，除了管理口令，我们都 选择默认安装。 Oracle 11g R2 DBA 操作指南 12 图 2-5 典型安装 在确认典型安装的内容后，单击下一步，就进入先决条件检查阶段，这个翻译有点拗口，其 实就是检查当前操作系统的环境是否满足 Oracle11g 软件的安装需求，如果不满足会具体说明，此 时需要重新对系统作出调整，以满足要求。如图 2-6 所示。 图 2-6 先决条件检查 经过检查，确实发现笔者的操作系统有不满足 Oracle 11g 安装需求的项目，即空闲空间不满 足要求，如图 2-7 所示。 第 2 章 Oracle 11g 数据库初体验 13 图 2-7 先决条件检查结果 在删除笔者 C 盘下部分文件后，释放部分空间，然后重新检查先决条件，发现没有不满足 Oracle 11g 安装的项目，则单击下一步继续安装。弹出如图 2-8 所示对话框，开始安装数据库软件。 图 2-8 安装数据库产品 其实，这个步骤包含几个过程，复制文件、安装程序文件和数据库配置，这个配置实际就是 安装两个工具，一个是 NETCA，一个是 DBCA，如图 2-9 所示。 Oracle 11g R2 DBA 操作指南 14 图 2-9 安装 Oracle 工具 第一个工具 NETCA（Oracle Net Configuration Assistant）用于配置网络、启动监听等，第二个 工具 DBCA（Oracle Database Configuration Assistant）用于安装数据库，这两个工具安装后会自动 后台运行，首先自动配置监听（在默认端口 1521），配置网络设置（NETCA 文件），然后启动 DBCA 建库，此时会弹出建库对话框，如图 2-10 所示。 图 2-10 使用 DBCA 建库 在数据库创建成功后，上述窗口退出，自动弹出如图 2-11 所示对话框，提示数据库成功安装， 并清晰说明数据库信息、加密密钥文件位置、Database Control URL（EM 企业管理器）以及用户名 和密码信息。 第 2 章 Oracle 11g 数据库初体验 15 图 2-11 成功创建数据库信息 此时监听已经启动，监听器在通过网络访问数据库服务器时使用，客户端通过连接工具或者 程序连接数据库，监听负责将用户连接请求交给数据库服务器进程。下面我们查看当前的监听器状 态，如例子 2-1 所示。 例子 2-1 检查监听器状态 D:\>lsnrctl status LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 03-12 月-2012 11:30:58 Copyright (c) 1991, 2010, Oracle. All rights reserved. 正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) LISTENER 的 STATUS ------------------------ 别名 LISTENER 版本 TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production 启动日期 03-12 月-2012 10:14:48 正常运行时间 0 天 1 小时 16 分 13 秒 跟踪级别 off 安全性 ON: Local OS Authentication SNMP OFF 监听程序参数文件 F:\app\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora 监听程序日志文件 f:\app\oracle\diag\tnslsnr\oracle-linshuze\listener\alert\log.xml 监听端点概要... Oracle 11g R2 DBA 操作指南 16 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))) 服务摘要.. 服务 "CLRExtProc" 包含 1 个实例。 实例 "CLRExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序... 服务 "orcl" 包含 1 个实例。 实例 "orcl", 状态 READY, 包含此服务的 1 个处理程序... 服务 "orclXDB" 包含 1 个实例。 实例 "orcl", 状态 READY, 包含此服务的 1 个处理程序... 命令执行成功 2.3 SQL Plus工具以及scott用户 既然成功安装了数据库，就需要维护和管理它，Oracle 提供了一个工具 SQL*Plus 来完成数据 库的管理和维护任务，虽然 Oralce 较高版本的数据库软件中提供了大量的图形化管理手段，但是 学会使用 SQL*Plus 工具仍然是 DBA 的一项重要基本功，因为有时图形化工具或许不能使用，而 SQL*Plus 则往往工作很好。 在 Oracle 11g 中可以如图 2-12 所示打开 SQL*Plus 工具。 图 2-12 打开 SQL*Plus 单击图 2-12 中所示的“SQL Plus”选项，弹出如图 2-13 所示的对话框。此时提示“请输入用 户名：”，在后面输入 scott，此时会继续提示“输入口令”，如图 2-14 所示，在口令中输入 tiger， 因为是本机上安装的数据库可以不考虑“主机字符串操作”。在输入用户名和口令后，按“回车” 键，执行结果如图 2-15 所示。 图 2-13 SQL*Plus 对话框 第 2 章 Oracle 11g 数据库初体验 17 图 2-14 使用 SCOTT 用户登录数据库 在图 2-15 中提示错误（ERROR），错误信息是“the account is locked”，显然数据库告知用 户 SCOTT 被锁定了。只有解锁后才可以使用该用户登录数据库。 图 2-15 无法使用锁定的用户登录数据库 在图 2-11 中可以看到除了那 4 个用户外其他所有用户都已经锁定了，所以我们使用 SYSTEM 用户登录，密码为 Oracle1234，我们再次输入用户名和密码，如图 2-16 所示，数据连接成功，进 入“SQL>”状态。此时，我们就可以使用 SQL*Plus 完成数据库的维护工作了，因为在以后的学 习中，我们需要使用 SCOTT 用户来学习使用各种 SQL 语句，所以先解锁 SCOTT 用户，在解锁 SCOTT 用户时，使用数据库维护指令 alter user scott，如图 2-17 所示。 Oracle 11g R2 DBA 操作指南 18 图 2-16 用 SYSTEM 用户登录数据库 图 2-17 解锁 SCOTT 用户 图 2-17 所示，我们输入的指令执行成功，修改 SCOTT 用户密码为 oracle，并解锁账户。提示 “用户已更改”，接下来就可以使用 SCOTT 用户了。 在安装数据库时，用户 SCOTT 是默认安装的而且密码默认为 tiger，其实 SCOTT 是 Oracle 数 据库公司早期的一个程序员，而 tiger 则是他的一只宠物猫的名字，或许是为了记住这位早期优秀 的数据库程序员而一直保留了这个用户，读者可以使用该用户下的几个表学习 Oracle 数据库 SQL 语句。 我们就是在 SQL*Plus 中输入各种类型的 SQL 语句或者执行某些.SQL 脚本（由多行 SQL 语句 组成）来维护和管理数据库。如果读者在使用 SCOTT 用户学习时出现一些意外情况，如删除了数 据无法恢复（没有学习备份前或没做任何备份表），或者该用户被他人删除等，可以使用一个脚本 来恢复。该脚本文件位于$ORACLE_HOME\RDBMS\ADMIN 下，脚本文件名为 scott.sql。 $ORACLE_HOME 为本机安装 Oracle 数据库的主目录，即 F:\app\oracle\product\11.2.0\dbhome_1， 第 2 章 Oracle 11g 数据库初体验 19 所以执行脚本文件的指令如下所示。 SQL> @ F:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN\scott.sql 我们再提供一种启动 SQL*Plus 的方法，并使用解锁的 SCOTT 用户登录。具体步骤如下： 打开一个 DOS 窗口，如图 2-18 所示。 图 2-18 打开 DOS 窗口 在图 2-18 中输入“sqlplus /nolog”来启动 SQL*Plus 工具，如图 2-19 所示。 图 2-19 启动 SQL*Plus 工具 使用 CONNECT 指令使 SCOTT 用户登录数据库，如图 2-20 所示。 图 2-20 SCOTT 用户登录数据库 Oracle 11g R2 DBA 操作指南 20 2.4 Linux环境下Oracle 11g的安装步骤 2.4.1 安装前的配置任务 在安装 Oracle 11g 数据库之前需要预配置任务，这些配置任务对操作系统内核、包、内存以 及用户组和用户等有具体要求，下面我们按照具体步骤，一步步实现安装前的预配置任务。 1. 以 ROOT 用户登录 ROOT 用户具有最高的权限，使用 ROOT 用户登录系统。 2. 硬件需求 内存最少 1GB。内存大小与 SWAP 区设置有关。 （1）确定内存大小。 [root@localhost oracle]# grep MemTotal /proc/meminfo MemTotal: 1034496 kB 当前机器是虚拟机，host 机器的内存是 2GB，所以分配了 1GB 内存给虚拟机用。 （2）内存与 SWAP 区查看其大小。 [root@localhost oracle]# free total used free shared buffers cached Mem: 1034496 285568 748928 0 17352 166988 -/+ buffers/cache: 101228 933268 Swap: 1020116 0 1020116 （3）11g 的 AMM 管理需要更多的共享内存。 在 11g 中如果设置了参数 memory_target 实现自动内存管理，此时设置的共享内存要大于参数 memory_target 或者 memory_max_target 参数设置的值，否则在启动时会导致 ORA-00845 错误。下 面查看共享内存大小。 [root@localhost oracle]# df -h /dev/shm/ Filesystem Size Used Avail Use% Mounted on none 506M 0 506M 0% /dev/shm （4）确定处理器架构与 Oracle 数据库软件相匹配。 [root@localhost oracle]# uname -m i686 Linux x86 机器上对于其他磁盘空间的需求如下所示。 Installation Type Requirement for Software Files (GB) Enterprise Edition 3.95 Standard Edition 3.88 Installation Type Disk Space for Data Files (GB) Enterprise Edition 1.7 Standard Edition 1.5 第 2 章 Oracle 11g 数据库初体验 21 安装软件所需空间和安装数据文件所需空间的最少值如上述所示。所以在初次安装 Oracle 11g 数据库作为学习库时，至少满足上述要求。有条件的话尽量使用更大的磁盘空间，毕竟当前的硬盘 价格很廉价。 3. 软件需求 其实这个步骤可以在安装数据库软件时再进行，因为 Oracle Universal Installer 会自动测试软件 环境是否满足要求。这里我们先完成各种软件配置，了解所需的具体配置内容。 （1）Oracle 11g R2 支持的操作系统。 On Linux x86: – Asianux 2 Update 7 – Asianux 3 – Oracle Enterprise Linux 4 Update 7 – Oracle Enterprise Linux 5 Update 2 – Red Hat Enterprise Linux 4 Update 7 – Red Hat Enterprise Linux 5 Update 2 – SUSE Linux Enterprise Server 10 SP2 – SUSE Linux Enterprise Server 11 查看 Linux 的版本。 [root@localhost oracle]# cat /proc/version Linux version 2.6.9-78.ELsmp (brewbuilder@hs20-bc2-3.build.redhat.com) (gcc version 3.4.6 20060404 (Red Hat 3.4.6-10)) #1 SMP Wed Jul 9 15:39:47 EDT 2008 （2）内核需求 On Asianux 2, Oracle Enterprise Linux 4, and Red Hat Enterprise Linux 4: 2.6.9 or later 通过如下指令查看当前的系统是否满足 Oracle 11g 对操作系统内核要求。 [root@localhost oracle]# uname -r 2.6.9-78.ELsmp （3）包需求 操作系统：Asianux 2、Oracle Enterprise Linux 4 和 RedHat Enterprise Linux 4 对应的包如下所 示。 binutils-2.15.92.0.2 compat-libstdc++-33-3.2.3 elfutils-libelf-0.97 elfutils-libelf-devel-0.97 gcc-3.4.6 * gcc-c++-3.4.6* glibc-2.3.4-2.41* glibc-common-2.3.4 glibc-devel-2.3.4 * glibc-headers-2.3.4 * libaio-devel-0.3.105 libaio-0.3.105 libgcc-3.4.6 Oracle 11g R2 DBA 操作指南 22 libstdc++-3.4.6 libstdc++-devel-3.4.6 make-3.80 numactl-0.6.4.i386 pdksh-5.2.14 sysstat-5.0.5 unixODBC-2.2.11 unixODBC-devel-2.2.11 这些包在安装光盘中，是必须安装的，下面在虚拟机环境下访问虚拟光驱中的包，并安装， 如例子 2-2 所示。 例子 2-2 强制安装 RPM 包 glibc-headers-2.3.4-2.41.i386.rpm。 [root@myoracle RPMS]# rpm -ivh glibc-headers-2.3.4-2.41.i386.rpm --nodeps --force warning: glibc-headers-2.3.4-2.41.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e Preparing... ########################################### [100%] 1:glibc-headers ########################################### [100%] 这里我们使用了--nodeps --force 选项， 这样就可以回避包之间的依赖性，可 以 直接安装成功。 例子 2-3 安装 RPM 包 binutils-2.15.92.0.2 [root@localhost RPMS]# rpm -ivh binutils-2.15.92.0.2-25.i386.rpm warning: binutils-2.15.92.0.2-25.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e Preparing... ########################################### [100%] package binutils-2.15.92.0.2-25 is already installed 4. 创建所需的操作系统用户组和用户 使用 root 用户登录操作系统，创建安装 Oracle11g 数据库所需的用户组与用户。创建一个 Oracle 用户作为安装软件的所有者，该用户必须将 Oracle Inventory Group 作为其第一个用户组。这样 Oracle 用户就可以向中心目录写数据。Oracle 用户同时必须拥有 grid infrastructure home 的 OSDBA 组，这样数据库实例就可以登录到 ASM（Oracle 的自动存储管理），并且将 OSOPER 作为第二个 用户组。 （1）下面我们创建这些用户组。 例子 2-4 创建各类用户组 创建 OSDBA 用户组 DBA。 # /usr/sbin/groupadd -g 502 dba 创建 Oracle Inventory 用户组 oinstall. # /usr/sbin/groupadd oinstall 创建 OSASM 用户组 # /usr/sbin/groupadd -g 504 asmadmin 创建 OSDBA 用户组 # /usr/sbin/groupadd -g 506 asmdba 第 2 章 Oracle 11g 数据库初体验 23 （2）创建 Oracle 软件拥有者（软件用户）。 例子 2-5 创建软件用户 # /usr/sbin/useradd -u 502 -g oinstall -G dba,asmdba,[oper] oracle 介绍参数。 -u :specifies the user ID. -g :specifies the primary group -G :specifies the secondary groups,which must include the OSDBA group, and, if required, the OSOPER and ASMDBA groups 例子 2-6 创建用户密码 创建用户密码： # passwd oracle 如果 Oracle 软件用户已经存在，或许需要修改使得所属的首选组和次选组分别为 oinstall 和 osdba(dba)，如下所示。 # /usr/sbin/usermod -g oinstall -G dba,asmdba[,oper] oracle （3）修改 Oracle 软件安装用户的 Shell 限制。 打开的最大文件描述符 nofile 65536 单个用户可获得的最大进程数 nproc 16384 进程堆栈区的最大尺寸 stack 10240 在文件/etc/security/limits.conf 中增加如下参数设置。 oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 在文件/etc/pam.d/login 中增加如下内容。 session required pam_limits.so （4）配置内核参数。 编辑文件/etc/sysctl.conf，增加如下参数。 fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 536870912 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586 net.ipv4.tcp_wmem = 262144 262144 262144 net.ipv4.tcp_rmem = 4194304 4194304 4194304 输入下面指令使得参数生效。 Oracle 11g R2 DBA 操作指南 24 # /sbin/sysctl -p 可以通过如下指令确定参数正确。 /sbin/sysctl -a --输出文件内容。 5. 所需的软件目录 （1）Oracle Base 目录 它指 Oracle 软件的顶层目录，Oracle 推荐的为/mount_point/app/software_owner ，其中 mount_point 指挂接点目录，software_owner 指软件用户，如 Oracle，这样其 Base 目录为： /u01/app/oracle （2）Oracle Inventory 目录 Oracle Inventory 目录存储安装的软件目录，是一个 Oracle software installations 共享的目录， Oracle Universal Installer 创建这个目录，但是要求该目录具有安装软件的用户、用户组以及读写权 限。在安装时，如果没有写权限会报错，读者可以根据错误再回头修改目录权限。 （3）Oracle Home 目录是安装数据库软件的目录 Oracle 推荐的 Oracle Home 目录格式： oracle_base/product/11.2.0/dbhome_1 如 /u01/app/oracle/product/11.2.0/dbhome_1。下面我们依次创建这些目录，并修改环境变量， 使得这些变量生效。 例子 2-7 创建 Oracle base 目录 # mkdir -p /u01/app/oracle # chown -R oracle:oinstall /u01/app/oracle # chmod -R 775 /u01/app/oracle 然后设置 ORACLE_BASE 环境变量 vi .bash_profile。 6. 创建数据库文件目录和快速恢复区目录 数据库文件目录: # mkdir /mount_point/oradata # chown oracle:oinstall /mount_point/oradata # chmod 775 /mount_point/oradata The default location for Database file directory is $ORACLE_BASE/oradata. 快速恢复区目录 (fast recovery area): # mkdir /mount_point/recovery_area # chown oracle:oinstall /mount_point/recovery_area # chmod 775 /mount_point/recovery_area 上面我们就完成了操作系统需要的预配置任务，下面我们就可以进入数据库软件的安装了。 2.4.2 安装数据库软件 当然安装软件需要下载相应版本的 Oracle 数据库管理软件，这些软件 Oracle 都是免费下载使 第 2 章 Oracle 11g 数据库初体验 25 用的，只要不是用于商业用途。读者只要登录 Oracle 官方网站，在下载（download）页面中选择 需要的软件即可，这些软件都是压缩的文件，Oracle 11g 有两个压缩文件，解压后这些文件会解压 到名为 database 的目录下。下面我们就安装解压后的文件。 安装 Oracle 11g 数据软件，如图 2-21 所示。 [oracle@myoracle data]$ cd database [oracle@myoracle database]$ ls doc install response rpm runInstaller sshsetup stage welcome.html [oracle@myoracle database]$ ./runInstaller Starting Oracle Universal Installer... Checking Temp space: must be greater than 80 MB. Actual 4188 MB Passed Checking swap space: must be greater than 150 MB. Actual 502 MB Passed Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-11-21_ 06-41-18AM. Please wait ...[oracle@myoracle database]$ 图 2-21 安装选项 单击下一步，或者直接单击回车键，就进入下一个页面，如图 2-22 所示。 Oracle 11g R2 DBA 操作指南 26 图 2-22 选择只安装数据库软件 此时，有 3 个选项，即创建并配置一个数据库、安装数据库软件以及升级数据库，我们选择 第二个仅安装数据库软件，之后再创建数据库，这样的安排步骤对初学者是有益的。 单击下一步，选择单实例数据库，如图 2-23 所示。 图 2-23 选择数据库安装类型 这里有两个选项，一个单实例数据库，一个是 RAC 数据库，显然，这里是单实例数据库，RAC 简单讲是多实例数据库环境，读者了解即可，这里我们选择第一个然后回车。 第 2 章 Oracle 11g 数据库初体验 27 选择产品语言。 该语言是数据库软件运行的语言环境，我们选择 English，学习数据库大家还是习惯英语环境 为好，如图 2-24 所示。 图 2-24 选择产品语言 单击下一步。选择数据库版本，此时提供了企业版、标准版。我们选择企业版，如图 2-25 所示。 图 2-25 选择数据库版本 单击回车，选择数据库软件的安装目录，如图 2-26 所示。 Oracle 11g R2 DBA 操作指南 28 图 2-26 选择安装目录 这里的 Oracle Base 是数据库软件的安装顶层目录，软件目录为 RDBMS 软件的安装目录。这 些目录都是在环境变量中读取的。 创建 Inventory 目录，如图 2-27 所示。 图 2-27 选择 Inventory 目录 这个目录有数据库软件自动安装，不需要提前设置。该目录所属的用户组为 oinstall。此时要 求改用户必须具有对/u01/app 目录的读写权限，如果读者没有设置会报错。修改方式如下： [root@myoracle ~]# chown -R oracle:oinstall /u01/app/ [root@myoracle ~]# chmod -R 755 /u01/app 第 2 章 Oracle 11g 数据库初体验 29 选择 OSDBA 用户组，如图 2-28 所示。 图 2-28 选择 OSDBA 用户组 预检查的过程，如果有需要的包没有安装的情况会提示，如图 2-29 所示。 图 2-29 先决条件检查 不满足安装要求的系统包或者操作系统资源信息会做如图 2-30 所示的提示。 Oracle 11g R2 DBA 操作指南 30 图 2-30 预安装检测结果 图 2-30 中提示系统的物理内存以及 SWAP 区的大小不满足要求，因为是虚拟环境我们暂时忽 略这些，在实际系统中一定不能出现这样的问题，要配置足够的内存并设置足够大的 SWAP 区。 接下来是全局设置信息和 Inventory 信息，如图 2-31 所示。 图 2-31 安装信息汇总 安装数据库软件。此时进入安装数据库软件的步骤，单击图 2-32 中的 Finish 按钮，开始 安装数据库软件。 第 2 章 Oracle 11g 数据库初体验 31 图 2-32 开始安装数据库软件 单击 Finish 按钮后该页面不发生变化，稍等片刻进入下个界面。 安装过程包括 4 个部分，它们组成了整个软件的安装过程，如图 2-33 所示。在这个步骤 中如果 Link binaries 子步骤出现错误，一般是因为部分系统包没有安装成功。 图 2-33 安装数据库软件过程 这个安装过程会经历几个步骤，任何一个步骤出现错误都会有提示，读者也可以通过日志文 件监控安装的详细过程。 Oracle 11g R2 DBA 操作指南 32 tail –f /u01/app/oraInventory/logs/installActions2012-11-21_06-48-23AM.log 执行脚本，如图 2-34 所示。 图 2-34 执行脚本 在安装 RDBMS 软件完毕后，会要求执行两个脚本，注意此时的脚本需要使用 ROOT 用户登 录。 例子 2-8 执行脚本 orainstRoot.sh [root@myoracle ~]# cd /u01/app/oraInventory/ [root@myoracle oraInventory]# ls ContentsXML logs oraInst.loc oui install.platform oraInstaller.properties orainstRoot.sh [root@myoracle oraInventory]# ./orainstRoot.sh Changing permissions of /u01/app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /u01/app/oraInventory to oinstall. The execution of the script is complete. 例子 2-9 执行脚本 root.sh [root@myoracle dbhome_1]# pwd /u01/app/oracle/product/11.2.0/dbhome_1 [root@myoracle dbhome_1]# ./root.sh Running Oracle 11g root.sh script... The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/11.2.0/dbhome_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... 第 2 章 Oracle 11g 数据库初体验 33 Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root.sh script. Now product-specific root actions will be performed. Finished product-specific root actions. 当安装完成后，会显示如图 2-35 所示的对话框，提示 Oracle 软件安装成功。 图 2-35 安装成功 2.4.3 启动监听 监听是在数据库服务器端执行的一个进程，用于完成远程用户对数据库服务器的连接，在安 装数据库过程中如果需要安装 EM（企业管理器），会要求监听已经启动，否则在安装过程中会提 示错误。 使用 NETCA 启动监听配置，如图 2-36 所示。 图 2-36 监听配置 Oracle 11g R2 DBA 操作指南 34 增加监听器，监听的名称，这里采用默认命名，如图 2-37 所示。 图 2-37 增加监听 选择监听使用的协议，使用 TCP 传输控制协议，如图 2-38 所示。 图 2-38 选择协议 选择监听使用的监听端口，监听毕竟是一个应用服务，我们这里使用默认值，如图 2-39、 图 2-40 所示。 图 2-39 配置监听 第 2 章 Oracle 11g 数据库初体验 35 图 2-40 监听配置完成 单击 Finish 完成退出 NETCA，如图 2-41 所示。 图 2-41 退出 NETCA 如下是 NETCA 在交互窗口执行的内容。 例子 2-10 NETCA 输出 [oracle@myoracle ~]$ netca Oracle Net Services Configuration: Configuring Listener:LISTENER Listener configuration complete. Oracle Net Listener Startup: Running Listener Control: /u01/app/oracle/product/11.2.0/dbhome_1/bin/lsnrctl start LISTENER Listener Control complete. Listener started successfully. Oracle Net Services configuration successful. The exit code is 0 下面我们查看监听状态。 例子 2-11 显示监听状态 [oracle@myoracle ~]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 25-NOV-2012 11:37:37 Oracle 11g R2 DBA 操作指南 36 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myoracle)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production Start Date 25-NOV-2012 11:35:36 Uptime 0 days 0 hr. 2 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/myoracle/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myoracle)(PORT=1521))) The listener supports no services The command completed successfully 以上输出很详细，读者需要自己阅读，如监听名称、监听文件，监听日志、监听连接设置。 从最后的输出说明监听正常。 2.4.4 使用 DBCA 图形化工具建库 DBCA（DataBase Configuration Assistant）是 Oracle 数据库软件自带的配置助手，用于创建数 据库、删除数据库、数据库配置任务。它是一个图像化工具，是初学者创建数据库的最简单的方法。 因为我们在环境变量 PATH 中增加了$ORACLE_HOME/bin，所以在系统中输入 DBCA 指令就会启 动 DBCA 配置助手，按照图形化的提示，一步步创建 Oracle 数据库，如图 2-42~图 2-57 所示。 [oracle@myoracle ~]$dbca 图 2-42 启动 DBCA 第 2 章 Oracle 11g 数据库初体验 37 图 2-43 选择创建数据库 图 2-44 选择数据库类型 图 2-45 设置 Oracle SID Oracle 11g R2 DBA 操作指南 38 图 2-46 选择配置 EM 图 2-47 配置密码（Oracle） 在图 2-47 中，我们设置的密码是 oracle，提示没有满足 Oracle 要求的密码复杂度，我们按照 错误提示的要求修改密码。下面重新输入密码（Oracle1234）如图 2-48 所示。 图 2-48 密码验证通过 第 2 章 Oracle 11g 数据库初体验 39 单击下一步如下所示。 图 2-49 选择数据文件目录/u01/app/oracle/oradata 图 2-50 选择闪回恢复区目录 图 2-51 选择 SMPLE SCHEMAS Oracle 11g R2 DBA 操作指南 40 图 2-52 配置初始化参数（会根据系统资源自动配置） 图 2-53 字符集选择 AL32UTF8 图 2-54 数据库文件的存储设置 第 2 章 Oracle 11g 数据库初体验 41 图 2-55 创建数据库 图 2-56 创建过程 图 2-57 安装完毕 此时，数据库安装完成，这个对话界面提供了数据库的详细信息如数据库名、SID、服务器参 数文件、启动 EM 的 URL 以及用户状态以及密码信息。 Oracle 11g R2 DBA 操作指南 42 2.5 测试到数据库的连接 在数据库安装完毕后，需要测试到数据库的连接，我们在安装数据库软件的操作系统上使用 SQL*Plus 工具来连接到数据库，此时为了直接连接到我们刚刚创建的数据库，重新编辑.bash_profile 文件，修改内容如例子 2-12 所示。 例子 2-12 修改.bash_profile 参数 # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export ORACLE_SID=orcl PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin export PATH unset USERNAME 在原有内容的基础上增加 export ORACLE_SID=orcl 变量并使该文件生效，如下所示。 [oracle@myoracle ~]$ . .bash_profile 下面我们使用 SYS 用户登录来测试连接功能，此时必须使用 SYSDBA 角色。 例子 2-13 测试数据库连接 [oracle@myoracle ~]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 25 12:17:37 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> 然后查询动态性能视图 v$database，查看数据库的当前状态信息，如例子 2-14 所示。 例子 2-14 查询当前数据库的状态 SQL> select name ,to_char(created,'yyyy-mm-dd hh24:mi:ss') created,log_mode,open_mode from v$database; NAME CREATED LOG_MODE OPEN_MODE ------------------------ ---------------------------- -------------------- ORCL 2012-11-25 11:59:32 NOARCHIVELOG READ WRITE 第 2 章 Oracle 11g 数据库初体验 43 显然，输出说明，数据库名为 ORCL，打开模式为读、写模式，数据库处于非归档模式。通 过动态性能视图 v$database 的查询结果，说明当前使用 SQL*Plus 到数据库服务器的连接成功。 到目前为止，我们已经成功安装了数据库软件，创建了一个数据库，并启动了监听功能，我 们就有了一个学习 Oracle 11g 数据库的环境。在以后的学习中就以这个环境为基础，所 以 希望读者 认真完成安装过程。 2.6 删除数据库软件 Oracle 数据库的卸载在 11g 之前比较麻烦，存在卸载不干净的问题，这样会导致第二次安装的失 败，随着 Oracle 升级到 11g，数据库卸载有了极大改善。因为它提供了一个卸载脚本，只要在执行该 脚本前停止所有 Oracle 服务即可。该脚本名为 deinstall.bat。默认在目录$ORACLE_HOME/deinstall/下， 运行该脚本即可自动完成数据库的卸载任务，最后只要手动删除相关文件夹即可。 下面是手工删除数据库软件的过程。之前如果建库成功，可以先使用 DBCA 删除数据库，再 使用如下方式手工删除数据库软件。这个步骤比较繁琐，需要细心删除相关的文件和注册信息。 打开注册表（运行 regedit），删除如下要求删除的注册内容。  删除 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE 目录。  删除 HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Service 中所有以 Oracle 或者 OraWeb 开头的键。  删除 HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Eventlog/application 中所有以 Oracle 开头的键。  删除 HKEY_CLASSES_ROOT 目录中所有以 Ora、Oracle、实例名或 EnumOra 为前缀的键。  删除 HKEY_CURRENT_USER/SOFTWARE/Microsoft/windows/CurrentVersion /Explorer/ MenuOrder/Start Menu/Programs 中所有以 Oracle 开头的键。  删除 HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI 中除了 Microsoft ODBC for Oracle 键以外的、所有含 Oracle 的键。  删除环境变量中的 PATH CLASSPATH 中包含 Oracle 的键。  删除开始/程序中所有 Oracle 的图标。  删除所有与 Oracle 相关的目录。 2.7 本章小结 本章我们主要介绍了如何在 Windows 系统和 Linux 系统上安装 Oracle 11g 数据库软件，并创 建数据库。介绍了我们在本书中会多次用到的 SCOTT 用户。对 于 Windows 下安装数据库软件比较 简单，只要读者的计算机硬件满足要求即可，而对于 Linux 系统则需要考虑诸如操作系统版本、内 核参数、系统架构等要求，需要根据具体的操作系统而有所区别，本章最后给出了在 RedHat Enterprise Linux AS.V4.0.UPDATE.7 上安装 Oracle 11g 数据库软件的详细过程供读者参考。 第 6 章 ◄ 数据字典 ► 数据字典是在数据库创建时，由 Oracle 数据库服务器自动创建的一个额外的对象，这些对 象存放在数据文件中，这些对象包括基表和数据字典视图，其中基表在 Oracle 数据库服务器使 用 CREATE DATABASE 时创建，因为基表中的数据格式是无法直接阅读的，所以 Oracle 使用 数据字典视图收集基表的信息，该数据字典视图是可读的，对 DBA 更有使用价值，数据字典 视图通过 catalog.sql 脚本文件创建。那么数据字典中到底存储了哪些信息，如何使用和操作数 据字典视图以及对 DBA 来讲有哪些常用的数据字典视图，我们将在接下来的几节依次讲解。 6.1 数据字典中的内容 数据字典是很重要的数据库对象之一，它在数据库创建时由数据库服务器创建，记录了数据 库创建的信息，各种对象的信息等，下面列出数据字典中包含的内容。  所有的模式（用户）对象的定义，这些对象包括表、视图、索引、族、同义词、序列号、 存储过程、函数触发器等。  数据库的逻辑结构和物理结构，如数据文件和重做日志文件的信息等。  所有模式对象被分配多少存储空间，以及当前使用的空间。  默认列的值。  对象完整性约束信息。  用户信息。  用户或角色的特权信息。  审计信息，如哪个用户具有访问或者修改某些模式对象的权利。 6.2 使用和操作数据字典视图 数据字典视图由 Oracle 数据库服务器自动创建并维护，也就是说只有 Oracle 服务器可以修改 数据字典中的数据，在数据库运行期间，数据库结构或其他对象的变化信息会及时地记录在数据字 典基表中，通过动态性能视图具有用户可以查看可读的数据字典基表中的信息。 举例：使用 GRANT 语句对一个用户赋予一定的权限时，数据库服务器会记录用户权限的变 化。如果使用 ALTER DATABASE 移动了控制文件的存储目录，则在数据字典中也会记录下这个 第 6 章 数据字典 109 变化。从两个例子可以看出，当使用 SQL 语句操纵使得某些数据库结构或对象发生变化时，这些 都会引起数据库服务器修改数据字典信息。 对于访问数据字典信息需要一定的授权，不同的用户对数据字典的访问有一定的区别，有些 只有 DBA 用户具有可访问的权利，对于普通用户就无法查看 DBA 用户查看的数据字典。 数据字典有 3 个基本应用，即在什么场合需要使用数据字典。Oracle 和普通用户都会使用数 据字典，使用场合说明如下：  Oracle 数据库服务器用来寻找用户信息、模式对象信息（如表、索引、触发器等）和存储 结构。  当使用 DDL 语句时，会触发 Oracle 服务器修改数据字典。  普通用户或者 DBA 用户使用数据字典获得关于数据库的信息，如数据文件的存储位置、 数据库实例名、参数文件中的参数值、控制文件的信息等。 数据字典是 Oracle 数据库服务器创建和维护的，在数据库运行过程中，数据库服务器不 断地更改数据字典，任何其他用户都没有修改数据字典的权利。并且有些视图所有用户都 可以访问，而有些数据字典视图只对 DBA 用户开放。 6.3 数据字典视图分类 数据字典视图分为 3 类，这些视图都是静态视图（静态的含义是这些视图在数据库运行期间 不会发生变化，除非执行 ANALYZE 指令☺），这 3 类数据字典视图以不同的前缀区分彼此。数 据字典名和对数据字典的解释如下所示。  DBA_***：该视图包含数据库中整个对象的信息，以 DBA 为前缀的视图只能由管理员查 询，不要在这些视图上创建同义词。  ALL_***：该 视图包含某个用户所能看到的全部数据库信息，包括当前用户所拥有的模式 对象和用户可以访问的其他公共对象，还有通过授权或授予角色可以访问的模式对象。  USER_***：该视图包含当前用户访问的数据库对象信息，它反映了数据库中某个用户的 全部情况，该类视图隐含了 owner 信息，其全部内容为以 ALL 为前缀的视图的子集。 上述的***号表示数据库模式对象，如表 TABLE、索引 INDEX、视图 VIEW 对象、 OBJECTS 等。 下面，我们分别用具体的例子演示上面 3 种视图。 例子 6-1 使用 DBA 用户连接数据库 SQL> conn /as sysdba 已连接。 查看具有 DBA 前缀的视图时，会输出整个数据库的全局视图，但是这个视图只有具有 DBA 权限的用户才可以访问。如果某个用户具有 SELECT ANY TABLE 的权利，也可以查询具有 DBA Oracle 11g R2 DBA 操作指南 110 前缀的数据字典视图。在例子 6-1 中，我们成功登录数据库。使用例子 6-2 查看 dba_objects 视图的 列定义。 例子 6-2 查看 dba_objects 视图结构 SQL> DESC dba_objects; 名称 是否为空? 类型 ------------------------------------------------------- ---------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(18) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(6) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) 在确定了 dba_objects 视图中列的定义后，就可以使用例子 6-3 来查询数据字典视图 dba_objects 的内容，不过查询结果有大量输出，所以要采用一些限制条件来减少输出，并且为了输出更易于阅 读需要事先做一些格式化工作。 例子 6-3 通过数据字典视图 dba_objects 查看 SCOTT 用户的数据库对象信息 SQL> col owner for a20 SQL> col object_name for a40 SQL> select owner,object_name,created 2 from dba_objects 3 where owner='SCOTT'; OWNER OBJECT_NAME CREATED -------------------------------------------------------- -------------- SCOTT PK_DEPT 02-4 月 -10 SCOTT DEPT 02-4 月 -10 SCOTT EMP 02-4 月 -10 SCOTT PK_EMP 02-4 月 -10 SCOTT BONUS 02-4 月 -10 SCOTT SALGRADE 02-4 月 -10 已选择 6 行。 当然，上述查询使用 ALL_OBJECTS 视图也可以实现，DBA 用户可以访问所有的数据库信息。 为了查看 all_objects 的信息，我们先查该视图的列定义，如例子 6-4 所示。 例子 6-4 查看 all_objects 结构信息 SQL> DESC all_objects; 名称 是否为空? 类型 ----------------------------------------- -------- ----------------- 第 6 章 数据字典 111 OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(18) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(6) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) 观察例子 6-2 和例子 6-4 会发现二者输出是一样的，也就是说 DBA_***视图和 ALL_***视图 具有相同的列定义。 我们使用 SCOTT 用户登录数据库，如例子 6-5 所示。 例子 6-5 使用 SCOTT 用户登录数据库 SQL> conn scott/tiger; 已连接。 我们先使用例子 6-6 查询当前 SCOTT 用户的 all_objects 表中有多少个 owner，名字是什么。 例子 6-6 当前 SCOTT 用户的 all_objects 表中 owner 名字 SQL> select distinct(owner) 2 from all_objects; OWNER -------------------- CTXSYS MDSYS OLAPSYS ORDPLUGINS ORDSYS PUBLIC SCOTT SYS SYSTEM WKSYS 已选择 10 行。 我们发现数据字典视图 all_objects 中共有 10 个 owner，其中一个 owner 为 SCOTT，虽然我们 使用 SCOTT 用户登录，但是该用户可以访问其他用户的部分对象信息。为了减少输出，我们用例 子 6-7 说明如何查询 all_objects 表中特定 owner 的信息。 例子 6-7 查询 all_objects 表中特定 owner 的信息 SQL> select owner,object_name,created from all_objects where owner='SCOTT'; Oracle 11g R2 DBA 操作指南 112 OWNER OBJECT_NAME CREATED ------------------------------ ------------------------------ -------------- SCOTT PK_DEPT 02-4 月 -10 SCOTT DEPT 02-4 月 -10 SCOTT EMP 02-4 月 -10 SCOTT PK_EMP 02-4 月 -10 SCOTT BONUS 02-4 月 -10 SCOTT SALGRADE 02-4 月 -10 已选择 6 行。 输出结果说明成功查询 all_objects 视图中用户 SCOTT 的信息，包括 SCOTT 用户下的对象名 和对象创建时间。 如果在 SCOTT 用户模式下查询 dba_objects 视图就会出错，因为该用户不具备 DBA 权限，也 没有 SELECT ANY TABLE 的权限。查询结果如例子 6-8 所示。 例子 6-8 在 SCOTT 用户模式下查询 dba_objects 视图 SQL> select owner,object_name,created 2 from dba_objects 3 where owner = 'SCOTT'; from dba_objects * ERROR 位于第 2 行: ORA-00942: 表或视图不存在 下面演示如何查看 USER_***数据字典视图，此时还是使用 SCOTT 用户登录数据库系统。查 看 user_objects 的定义，如例子 6-9 所示。 例子 6-9 查看静态数据字典 user_objects 的定义 SQL> desc user_objects; 名称 是否为空? 类型 ----------------------------------------- -------- ------------------ OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(18) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(6) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) 比较例子 6-2、例子 6-4 和例子 6-9 发现例子 6-9 中视图 user_objects 的列没有 OWNER。其实， 这个问题也容易理解，因为 USER_***视图是当前用户的数据库对象信息，既然是当前用户自然不 需要有 OWNER 选项了，而 DBA_***视图和 ALL_***视图包括不同用户的对象信息，使用 OWNER 列可以区别不同用户的对象信息。我们使用例子 6-10 查询 SCOTT 用户的对象信息。 第 6 章 数据字典 113 例子 6-10 查询 SCOTT 用户的对象信息 SQL> col object_name for a20 SQL> l 1 select object_name,object_type,created 2* from user_objects SQL> / OBJECT_NAME OBJECT_TYPE CREATED ------------------------------------- -------------------------------- SALGRADE TABLE 02-4 月 -10 BONUS TABLE 02-4 月 -10 PK_EMP INDEX 02-4 月 -10 EMP TABLE 02-4 月 -10 DEPT TABLE 02-4 月 -10 PK_DEPT INDEX 02-4 月 -10 已选择 6 行。 在该例子中，我们查询了当前用户的对象名 OBJECT_NAME，对象类型 OBJECT_TYPE 和对 象创建时间 CREATED，其实，这个输出结果中的对象名和例子 6-7 相同。 6.4 使用数据字典视图 数据字典视图是静态视图，在数据库重新启动前，静态数据字典中的信息是不会变化的。有 一些数据字典视图对于 DBA 很重要，下面依次介绍这些数据字典视图。 user_tables 视图，该视图可以查看当前用户所有拥有的表，如例子 6-11 所示。 例子 6-11 查看当前用户所有拥有的表 SQL> conn scott/tiger 已连接。 SQL> select table_name 2 from user_tables; TABLE_NAME ------------------------------ BONUS DEPT EMP SALGRADE 已选择 8 行。 user_indexes 数据字典视图，查看当前用户创建的索引，索引在某种程度上可以加快查询的速 度，如例子 6-12 所示。 例子 6-12 查看当前用户创建的索引 SQL> select index_name 2 from user_indexes; Oracle 11g R2 DBA 操作指南 114 INDEX_NAME ------------------------------ PK_DEPT PK_EMP SYS_C003123 SYS_C003126 SYS_C003129 SYS_C003130 已选择 6 行。 user_views 数据字典视图，查看当前用户拥有的视图，如例子 6-13 所示。 例子 6-13 查看当前用户拥有的视图 SQL> select view_name 2* from user_views VIEW_NAME ------------------------------ STUDENT_SCORE VIEW_DEPT user_catalog 视图，该视图包含当前用户的所有表的名字和类型。 例子 6-14 查询该视图的结构 SQL> desc user_catalog; 名称 是否为空? 类型 ----------------------------------------- ---------------- TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) 输出结果说明，该视图有两列，一个为 TABLE_NAME，该列不能为空，是变长字符类型， 一个为 TABLE_TYPE，该列可以为空，是变长字符类型。 例子 6-15 查询用户 SCOTT 的所有表名和类型 SQL> select * 2 from user_catalog; TABLE_NAME TABLE_TYPE ------------------------------------------------------- BONUS TABLE DEPT TABLE DEPT_TEMP TABLE EMP TABLE EMP_TEMP TABLE ORD TABLE ORD_ORDNO SEQUENCE PRODUCT TABLE SALGRADE TABLE SUPPLIER TABLE 第 6 章 数据字典 115 已选择 10 行。 dba_users 视图，查看数据库系统上何时创建了多少个用户，如例子 6-16 所示。 例子 6-16 查看数据库系统上创建的用户信息 SQL> select username,created from dba_users; USERNAME CREATED ------------------------------ -------------- MGMT_VIEW 02-4 月 -10 SYS 02-4 月 -10 SYSTEM 02-4 月 -10 DBSNMP 02-4 月 -10 SYSMAN 02-4 月 -10 SCOTT 02-4 月 -10 OUTLN 02-4 月 -10 ………… 6.5 动态性能视图及使用 在 6.3 节中，我们讲了静态数据字典视图，Oracle 还维护了另一类非常重要的数据字典视图： 动态性能视图。动态性能视图只存在于运行的数据库中，它是一组虚表，通 常也把这组表称为动态 性能表（dynamic performance table）。 数据库的动态性能视图只有管理员用户可以查询，而其他普通用户不需要查询这些虚表中的 信息。管 理 员可以在动态性能视图上创建视图，并将访问权限授予其他用户。任何用户都无法修改 或删除动态性能视图，所以有时这些动态性能视图也被称为固定视图（fixed view）。 SYS 用户拥有所有的动态性能视图，这些动态性能视图以 v$为前缀，如 v$controlfile 包含了 控制文件存储目录和文件名信息，v$datafile 包含了数据库文件信息，v$fixed_table 视图包含了当前 所有动态性能视图。 如果用户想知道当前运行的数据库中所有的动态性能视图，可以使用 v$fixed_table 实现，不 过一般该视图会输出大量的记录，不方便阅读，最好使用 spool 工具存储输出结果，再分析存储的 输出信息。 为了更充分理解动态性能视图的作用和使用，我们给出如下的例子说明，这些例子也是在实 际工作中经常使用的，对于 DBA 维护数据库很实用。 例子 6-17 查询和日志文件相关的信息 SQL> conn /as sysdba 已连接。 SQL> select * 2 from v$fixed_table 3 where name like 'V$LOG%'; NAME OBJECT_ID TYPE TABLE_NUM -------------------------------------------- ---------- ----- ---------- Oracle 11g R2 DBA 操作指南 116 V$LOGFILE 4294960936 VIEW 66636 V$LOG 4294961049 VIEW 66636 V$LOGHIST 4294961061 VIEW 66636 V$LOG_HISTORY 4294961066 VIEW 66636 V$LOGMNR_CONTENTS 4294961641 VIEW 66636 V$LOGMNR_LOGS 4294961643 VIEW 66636 V$LOGMNR_DICTIONARY 4294961646 VIEW 66636 V$LOGMNR_PARAMETERS 4294961646 VIEW 66636 V$LOGMNR_LOGFILE 4294961643 VIEW 66636 V$LOGMNR_PROCESS 4294961646 VIEW 66636 V$LOGMNR_TRANSACTION 4294961649 VIEW 66636 NAME OBJECT_ID TYPE TABLE_NUM --------------------------------------------- ---------- ----- ---------- V$LOGMNR_REGION 4294961633 VIEW 66636 V$LOGMNR_CALLBACK 4294961636 VIEW 66636 V$LOGMNR_SESSION 4294961640 VIEW 66636 V$LOGSTDBY_COORDINATOR 4294961666 VIEW 66636 V$LOGSTDBY_APPLY 4294961668 VIEW 66636 V$LOGSTDBY 4294961611 VIEW 66636 V$LOGSTDBY_STATS 4294961614 VIEW 66636 已选择 18 行。 该例子查询出了所有和日志文件相关的动态性能视图，如果想了解日志文件的详细信息我们 可以使用 v$log 视图和 v$logfile 视图，如例子 6-18 和例子 6-19 所示。 例子 6-18 查看日志组状态信息 SQL> select group#,members,archived,status 2 from v$log; GROUP# MEMBERS ARC STATUS ---------- ---------- --- ---------------- 1 1 NO CURRENT 2 1 NO INACTIVE 3 1 NO INACTIVE 该例子的作用是查看当前正在使用的重做日志组，STATUS 为 CURRENT 说明该日志组正在 使用中，STATUS 为 INACTIVE 说明当前数据库系统没有使用该重做日志组。上例说明当前有 3 个重做日志组，第一个日志组正在使用中。 例子 6-19 查看重做日志文件信息 SQL> col member for a40 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------- 3 ONLINE F:\APP\ORACLE\ORADATA\ORCL\REDO03.LOG NO 2 ONLINE F:\APP\ORACLE\ORADATA\ORCL\REDO02.LOG NO 1 ONLINE F:\APP\ORACLE\ORADATA\ORCL\REDO01.LOG NO 第 6 章 数据字典 117 视图 v$logfile 用户查看当前数据库系统的重做日志组的日志成员的存储目录、文件名和状态 信息。该输出说明当前的日志组 1 正在使用中。 例子 6-20 为一个联合查询，查询当前正在使用的重做日志文件的信息 SQL> select l.group#,l.archived,l.status,lf.type,lf.member 2 from v$log l,v$logfile lf 3 where l.group# = lf.group# 4 and l.status = 'CURRENT'; GROUP# ARC STATUS TYPE MEMBER ------------ --- -------------------------------------------------------- 1 NO CURRENT ONLINE F:\APP\ORACLE\ORADATA\ORCL\REDO01.LOG 从 v$log 视图和 v$logfile 视图的联合查询可以看出，当前数据库正在使用的日志文件组为 group1，数据库运行在非归档模式，该日志组有一个日志成员，存储目录为 F:\APP\ORACLE\ ORADATA\ORCL\，文件名为 REDO01.LOG。 例子 6-21 通过 v$instance 视图查看实例信息 SQL> col instance_name for a20 SQL> col host_name for a10 SQL> select instance_name,host_name,version,startup_time,logins 2* from v$instance INSTANCE_N HOST_NAME VERSION STARTUP_TIME LOGINS --------------------------------------------- -------------------------- lin LINSHUZE-6F360C 11.2.0.1.0 12-12 月-12 ALLOWED 上述输出说明，当前的实例名为 lin，主机名为 LINSHUZE-6F360C，版本号为 11.2.0.1.0，实 例的启动时间为 12-12 月-12。 例子 6-22 查看当前数据库的信息 SQL> col name for a10 SQL> select name,created,log_mode 2 from v$database; NAME CREATED LOG_MODE ------------------------------------------------------------------------ LIN 12-12 月 -12 ARCHIVELOG 输出结果说明，该数据库名字为 LIN，数据库创建时间为 12-12 月 -12，该数据库运行在非归 档模式。 总之，动态性能视图很好地反映了当前数据库的运行状态信息，对于数据库性能调优和判断 系统瓶颈提供信息支持，通 过 动 态性能视图还可以查看控制文件的信息，数据文件的信息和表空间 信息等，DBA 用户经常使用的动态性能视图，这里不再一一介绍，在以后的章节中会继续使用各 种动态性能视图，本节只起到引导作用，希望读者能够理解动态性能视图的概念和应用。 Oracle 11g R2 DBA 操作指南 118 6.6 本章小结 数据字典是数据库中非常重要数据库对象，它在创建数据库时创建，其中记录了数据库创建 信息和各种对象的信息。由于基表的内容无法阅读，所以 Oracle 提供了数据字典视图，该视图是 基于基表数据的。数据字典视图是静态视图，在数据库启动后就无法改变。Oracle 数据库还维护了 动态性能视图，这些视图以 v$为前缀，动态性能视图反映了数据库当前的运行状态和各种对象的 活跃信息，动态性能视图是 DBA 进行故障判断和数据库性能调优的重要依据。 第 18 章 ◄ 索 引 ► 18.1 索引的概念 索引是 Oracle 的一个对象，是否使用索引由 Oracle 决定，索引中存储了特定列的排序数据， 实现对表的快速访问。使用索引可以很快查找到建立索引时列的值所在的行，而不必对表实现全表 扫描，所以适当地使用索引可以减少磁盘 I/O 量。在开始我们给出索引的特点总结，这样读者在接 下来使用索引时，脑子中就有一个局限，不要认为使用索引就是好事☺。 索引的特点：  对于具有只读特性或较少插入、更新或删除操作的大表通常可以提高查询速度。  可以对表的一列或多列建立索引。  建立索引的数量没有限制。  索引需要磁盘存储，需要 Oracle 自动维护。  索引对用户透明，是否使用索引是 Oracle 决定的。 读者都有这样的体验，如果去图书馆借书，首先会到查询机前检索需要的书籍，然后确认该 书的具体位置，通常该位置确定该书在图书室的哪个区域的哪个书架。这 样 只 要 径直到具体的地点 去找这本书，很快就可以找到。但是试想，如果你面对国家图书馆新馆的阅览室，四处都是高大的 书架，那种茫然是可以想象的。Oracle 使用索引的目的也是为了迅速地找到需要的数据，当然任何 事物都有两面，建立索引可以迅速找到需要的数据，但是在某些情况下也带来性能开销，我们在本 章将分析索引的建立和使用维护，并给出索引使用的一些建议。 18.2 Oracle实现数据访问的方法 Oracle 的 RDBMS 在访问数据时使用最根本的 3 种访问方法：  全表扫描； Oracle 11g R2 DBA 操作指南 306  通过 ROWID；  使用索引。 当 Oracle 决定使用索引时会使用 ROWID 来访问数据，当没有索引或者不选择使用索引时就 使用全表扫描的方式。Oracle 中扫描数据（数据访问路径）的方法。 18.2.1 全表扫描（FULL TABLE SCAN) 在使用全表扫描时，Oracle 读取表中所有的行，此时通过多块读操作可以大大减少 IO 的次数， 利用多块读可以大大提高全表扫描的速度，只有在全表扫描的情况下才能使用多块读。在较大的表 上不建议使用全表扫描，如 果 读取表的数据总量超过 5%~10%，那么通常进行全表扫描。并 行 查询 可能会使得我们的路径选择采用全表扫描。 即使在表上创建了索引，是 否 使用该索引也由 Oracle 根据 CBO 优化器计算的结果选择，用户 无法干预（当然 DBA 可以修改参数或者 SQL 语句）。 例子 18-1 使用 explian plan 解释执行计划 18.2.2 通过行 ID（ROWID） 对于表对象，在向表中插入数据时隐含会创建该行的 ROWID，ROWID 是数据行所存储的数 据块地址，这样就以最快的速度找到该行数据。如例子 8-2 所示，查询行 ID，我们通过比较看全 表扫描和通过行 ID 哪个更快。 ROWID 指出了数据文件、块号、行号，通过 ROWID 是 Oracle 数据库中读取单行数据最快速 的方法。这种方法不会采用多块读，而是会采用单块读的方式。 第 18 章 索 引 307 例子 18-2 通过执行计划确定单块读 18.2.3 使用索引 通过索引找到数据行的 ROWID、然后通过 ROWID 直接到表中查找数据，这种方式称为索引 查找或者索引扫描。因为一个 ROWID 对应一个数据行，因此这种方式采用的也是单块读。在索引 中，除了存储每个索引值，还存储相应的 ROWID，索引扫描分为两步： （1）扫描索引得到相应的 ROWID。 （2）通过找到的 ROWID 从表中读取相应的数据。 每次采用的都是单块 I/O 读，因为索引小而且经常使用，因此通常被 cache 到内存中，因此第 一步通常是逻辑读（数据可以从内存中得到），因为表数据比较大，因此第二步读通常是物理读， 因此性能较低。 索引改进性能的程度取决于两个因素：  数据的选择性；  表数据在数据块上的分布。 如果选择性很高（例如身份证号码），那么根据索引值返回的 ROWID 很少，如果选择性很低 （例如国家）则返回的 ROWID 很多，那么索引的性能将会大大降低（返回的数据少，I/O 压力小）。 如果选择性很高，但是相关的行在表中的存储位置并不互相靠近，则会进一步减少索引的益 处，如果匹配索引值的数据分散在表的多个数据块中，则必须从表中选择多个单独的块以满足查询， 基于索引的读取是单块读取，如 果 使用全表扫描，使用的是多块读取以快速扫描表，因此全表扫描 不见得比索引扫描速度慢。关键看数据对象数据块的分散程度。 当访问的行数较少时，SELECT、UPDATE、DELETE+WHERE 条件可以从索引中得到更多的 好处。一般来说，增加索引会带来 insert 语句性能的下降，如果根据未索引列 update 索引列，那么 Oracle 11g R2 DBA 操作指南 308 也会带来性能的降低。大 量的 delete 也会因为索引的存在而导致性能降低。因此我们要分析具体的 情况，判断索引和 DML 语句之间的关系。 例子 18-3 通过执行计划判断索引的使用 访问路径走的是主键索引，因此是 INDEX UNIQUE SCAN，首先是索引扫描、然后是根据索 引查找到的 ROWID 进行表的访问。 18.3 索引扫描类型 Oracle 在使用索引扫描数据时，根据索引的类型、数据分布以及 SQL 的谓词等确定索引的扫 描类型。索引扫描的 4 种类型。  索引唯一扫描（INDEX UNIQUE SCAN)；  索引范围扫描（INDEX RANGE SCAN)；  索引全扫描（INDEX FULL SCAN)；  索引快速扫描（INDEX FAST FULL SCAN)。 下面分别通过例子介绍什么条件下发生此种类型的索引扫描。 18.3.1 索引唯一扫描（INDEX UNIQUE SCAN) 索引唯一扫描指通过唯一键、主键，Oracle 通常返回一个数据行，因此采用的是索引唯一扫 描。 第 18 章 索 引 309 例子 18-4 索引唯一扫描 18.3.2 索引范围扫描（INDEX RANGE SCAN) 当发生如下情况时 Oracle 将使用索引范围扫描。  在唯一键上使用 range 操作符（>、<、<>、>=、<=、between）；  在组合索引上，只使用部分列进行查询，导致查询出多行；  对非唯一索引列上进行的查询。 例子 18-5 索引范围扫描 18.3.3 索引全扫描（INDEX FULL SCAN) 当使用索引全扫描时，查询出的数据必须全部从索引中得到，如例子 18-6 所示。 Oracle 11g R2 DBA 操作指南 310 例子 18-6 索引全扫描 18.3.4 索引快速扫描（INDEX FAST FULL SCAN) 索引快速扫描扫描索引块中的所有数据块，这点与 INDEX FULL SCAN 相似，但是索引快速 扫描不进行数据的排序，在这种方式下，可以使用多块读功能、也可以使用并行读功能，最大化数 据的吞吐量。 例子 18-7 索引快速扫描 18.4 限制索引使用的情况 限制索引的使用，很多情况下即使创建了索引，也会导致索引不能使用，如使用了函数而没 有创建基于该函数的索引。 我们下面来研究一下使用 where 但是阻止 Oracle 使用索引的几种情况。 18.4.1 使用不等于运算符 使用不等于运算符（<>、!=），在 where 中使用这些不等于条件，将会使索引失效。 第 18 章 索 引 311 例子 18-8 不等运算使得索引失效 当分析表的时候，Oracle 收集表中数据分布的相关统计信息，通过使用这种分析，基于成本 的优化器可以决定在 where 子句中对一些值使用索引，而对其他的值不使用索引。因此不是说在一 个列上建立了索引就一直会使用索引。根据不同值，优化器都会确定是否使用索引。 18.4.2 使用 IS NULL 或 IS NOT NULL 在 where 子句中使用 IS NULL 或者 IS NOT NULL 同样会限制索引的使用。如果被索引的列在 某些行中存在 NULL 值，在 索引列中就不会有相应的条目。位图索引对于 NULL 列也会进行记录， 因此位图索引对于 NULL 搜索通常较为快速。 例子 18-9 NULL 判断影响索引使用 在例子 18-9 中，我们基于表 EMP 的 SAL 列创建了 B 树索引。下面在查询谓词中使用 is NULL 来查询。 Oracle 11g R2 DBA 操作指南 312 例子 18-10 不走索引走全表扫描 此时使用了全表扫描，因此建议对列加上 NOT NULL 或者 DEFAULT ，防止 NULL 值的出 现，从而导致该列上的索引不能使用。 18.4.3 使用函数 如果不使用基于函数的索引（后面会讲到），那么在 SQL 语句的 where 子句中对存在索引的 列使用函数时，会使优化器忽略掉这些索引。 一些常见的函数如 trunc、substr、to_date、to_char、instr 等，都可能会使索引失效。 例子 18-11 函数使用使得索引失效 分析函数的使用复制了索引的使用，此时 SQL 查询走全表扫描。 解决方案： （1）使用基于函数的索引。 （2）灵活书写 SQL，避免在索引列上使用 SQL 函数。 第 18 章 索 引 313 例子 18-12 修改 SQL 防止函数对索引的影响。 此时，通过灵活一变，SQL 语句的访问路径变成了走索引。为了走索引，就不要在 where 子 句中的索引列上使用函数。 18.4.4 比较不匹配的数据类型 这个是比较难于发现的问题。Oracle 不会对不匹配的数据类型报错，Oracle 会隐式地把 VARCHAR2 列的数据类型转换成要被比较的数值型数据类型（这是一个例子，还存在其他的数据 类型转换）。 例子 18-13 隐式转换影响索引使用。 这里自动加了一个函数 to_number(zip)，对于不匹配的数据类型，Oracle 隐式地加上一个转换 函数。为什么不加载到 100043 上面，因为这是一个常量，常量是不能改变的。 解决方案是灵活地使用，在常量上面加上一个’’，表示这是一个字符常量，这样字符常量 就和 ZIP VARCHAR2 一致了。索引列的数据类型和常量类型要求一致。 Oracle 11g R2 DBA 操作指南 314 例子 18-14 使用字符常量解决隐式转换。 18.5 集群因子 优化器决定是否使用索引的两个关键因素。  选择性：选择性高意味着不同的值越多，使用索引时返回的数据量越少。  集群因子：集群因子是索引与他所基于的表相比较得出的有序性度量，它用于检查在索引 访问之后执行的表查找的成本。使用索引访问数据之后的表查找成本计算依据。 什么是集群因子呢？集群因子的含义是如果通过一个索引扫描一张表，需要访问表的数据块 的数量。集群因子计算的方法如下： （1）扫描一个索引。 （2）比较某行的 ROWID 和前一行的 ROWID，如果这两个 ROWID 不属于同一个数据块， 那么集群因子增加 1。 （3）整个索引扫描完毕后，就得到了该索引的集群因子。 如果集群因子接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。如果集群因 子接近于行的数量，那说明这张表不是按索引字段顺序存储的。在计算索引访问成本的时候，这 个 值十分有用。集群因子乘以选择性参数（selectivity）就是访问索引的开销。 如果这个统计数据不能真实反映出索引的真实情况，那么可能会造成优化器错误的选择执行 计划。另外如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字 段的顺序重新组织，可以提高该表的访问性能。 18.6 二元高度 索引查找分为两个过程： （1）根据树进行定位、找出 ROWID（索引查找）。 （2）根据 ROWID 找出表中的数据行（表数据查找）。 进行索引查找的时候，首先从树根开始读数据，通过中间节点，最后定位到叶节点，整个过 程只能进行单数据块的读取，如图 18-1 所示。 第 18 章 索 引 315 表中的数据 根节点 中间节点中间节点 叶节点 叶节点叶节点叶节点 索引 表 图 18-1 索引查询数据示意图 在图 18-1 中，索引的二元高度是 3，读取一行数据经过了四次数据块的读取，其中三次是索 引，一次是表数据。 例子 18-15 查询索引的二元高度。 查找每个索引的二元高度，这个索引只占用了一个数据块，因此二元高度是 0。 二元高度主要随着表中索引列的非 NULL 值以及索引列中值的宽度而变化。如果索引列上大 量的行被删除，那么他的二元高度不会降低，重 建 索引会降低二元高度，如果一个索引中被删除的 行接近 20～25%，重建索引会减低二元高度。二元高度对索引的性能影响不是很大，但是在可能 的情况下，降低二元高度还是有必要的。 18.7 直方图 在分析表和索引时，直方图用于记录数据的分布。通过获取该信息，基于成本的优化器就可 以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受 索引的限制，我们可以在表的任何列上构建直方图（一般是在表的索引列上构建直方图）。 构建直方图最主要的原因就是：帮助优化器在表中数据严重倾斜时做出更好的规划。如果一 Oracle 11g R2 DBA 操作指南 316 个表中的列上（通常是索引列）数据发生严重的倾斜，那么在这个列上建立直方图将非常地有意义。 这样优化器就知道什么时候该使用索引，什么时候不该使用索引了。 例子 18-16 收集表 employees 包含直方图的统计信息 在 hr 用户下面的 employees 表的 job_id 列上建立了一个直方图。这个直方图有 10 个存储桶， 如图 18-2 所示。 图 18-2 直方图 将整个列的 100 行数据均分成 10 个存储桶，每个桶中存储 10 个数据行。然后写出每个桶中 的数据范围。我们发现 1430 这个数值对应的行号数非常多，大约 73 行，占到了 73%。因此当我 们使用 where id=1403 的时候，优化器不会走索引。 默认情况下，Oracle 的直方图会产生 75 个存储桶，可以把存储桶的 size 限制在 1～254 之间。 18.8 建立索引 Oracle 建立索引使用 CREATE INDEX 指令，我们通过例子 18-17 说明如何建立索引，此时我 们使用 SCOTT 用户的表 EMP 作为例子，我们假设该表是很大的表，且用户经常使用用户名查询 该表中的数据。 第 18 章 索 引 317 例子 18-17 对 EMP 表建立索引 SQL> conn scott/oracle 已连接。 SQL> create index emp_ename_idx 2 on emp(ename); 索引已创建。 此时，对表 EMP 的列 ENAME 建立了索引，我们知道索引是需要存储空间的，也就是索引也 占用磁盘空间的。那么索引存储在哪个表空间，以及如何查看已经建立的索引的信息呢？Oracle 使用 USER_INDEXES 数据字典实现，如例子 18-18 所示。 例子 18-18 使用 USER_INDEXES 数据字典查询索引信息 SQL> col index_name for a20 SQL> col index_type for a10 SQL> col table_name for a20 SQL> col tablespace_name for a20 SQL> select index_name,index_type,table_name,tablespace_name 2* from user_indexes INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME ---------------------------- -------------------- --------------------------------- ------------------------------ PK_EMP NORMAL EMP USERS EMP_ENAME_IDX NORMAL EMP USERS PK_DEPT NORMAL DEPT USERS 使用数据字典 USER_INDEXES 可以详细地查看当前用户所拥有的索引信息，如上输出所示， 我们刚刚建 立 的 索引名 字 INDEX_NAME 为 EMP_ENAME_IDX ，该索引所 依赖的 表 TABLE_NAME 为 EMP，存储在用户表空间 USERS 中。这样通过数据字典视图 USER_INDEXES 可以很清楚地知道关于当前的所有索引信息。 读者在索引维护中需要知道索引所对应的表空间的信息，因为需要了解该表空间所在的磁盘 I/O 或该磁盘的使用情况来平衡磁盘读写。 例子 18-19 查看索引所对应的表空间信息 SQL> col "索引名" for a118 SQL> col "索引对应的表空间名" for a20 SQL> col "索引对应的磁盘文件" for a40 SQL> select a.index_name " 索引名" ,a.tablespace_name " 索引对应的表空间名 " ,b.file_name "索引对应的磁盘文件" 2 from dba_indexes a,dba_data_files b 3 where a.index_name like 'EMP%' 4* and a.tablespace_name = b.tablespace_name 索引名 索引对应的表空间名 索引对应的磁盘文件 -------------------------------- ---------------------------------------- EMP_ENAME_IDX USERS F:\ORACLE\PRODUCT\10.2.0\ORADATA\LEEJIA\ USERS01.DBF Oracle 11g R2 DBA 操作指南 318 下面我们再创建一个索引，在表 EMP 的列 ENAME 和 SAL 上创建多列索引，并且指定表空 间。首先创建一个索引表空间。 例子 18-20 创建索引表空间 SQL> conn system/oracle 已连接。 SQL> create tablespace index_tbs 2 datafile 'd:/index/index_tbs1.dbf' 3 size 100M 4 autoextend on; 表空间已创建。 此处创建表空间 INDEX_TBS 的目的就是存放索引，下面演示如何创建对表的多列创建索引， 并且指定表空间。 例子 18-21 创建表多列索引 SQL> create index emp_ename_sal_idx 2 on emp(ename,sal) 3 tablespace index_tbs; 索引已创建。 我们依旧查看是否成功创建索引，使用数据字典 USER_INDEXES，如例子 18-22 所示。 例子 18-22 查看多列索引 EMP_ENAME_SAL_IDX 的信息 SQL> col index_name for a20 SQL> run 1 select index_name,table_name,tablespace_name 2 from user_indexes 3* where index_name like 'EMP%' INDEX_NAME TABLE_NAME TABLESPACE ------------------------------------ -------------------- ----------------- EMP_ENAME_IDX EMP USERS EMP_ENAME_SAL_IDX EMP INDEX_TBS 从输出可以看出索引 MP_ENAME_SAL_IDX 是建立在表 EMP 上的，而且其存储表空间为 INDEX_TBS 。但是如何 查 看 一个索引是建立 在 表 的 哪 几 列 上 呢？答案是 使用数据字典 USER_IND_COLUMNS。 在直观理解了如何创建单列索引和多列索引后，我们给出创建索引的语法格式。 CREATE [UNIQUE|BITMAP] INDEX [schema.] index_name ON [schema.]table_name (column_name[DESC]ASC][, column_name[DESC]ASC]]………) [REVERSE] [TABLESPACE tablespace_name] [PCTFREE n] [INITRANS n] [MAXTRANS n] 第 18 章 索 引 319 [instorage state] [LOGGING|NOLOGGING] [NOSORT] 下面解释各个参数的含义：  UNIQUE：说明该索引是唯一索引。  BITMAP：创建位图索引。  DESC|ASC：说明创建的索引为降序或升序排列。  REVERSE：说明创建反向键索引。  TABLESPACE：说明要创建的索引所存储的表空间。  PCTFREE：索引块中预先保留的空间比列。  INITRANS：每一个索引块中分配的事务数。  MAXTRANS：每一个索引块中分配的最多事务数。  INSTORAGE STATE：说明索引中区段 EXTENT 如何分配。  LOGGING|NOLOGGING：说明“要记录|不记录”索引相关的操作，并保存在联机重做日 志中。  NOSORT：不需要在创建索引时再按键值进行排序。 18.9 查看索引 数据字典 USER_IND_COLUMNS 使得我们可以很方便的查找一个索引所对应的列的信息。在 前面已经建立 了两个索引，一个是单列 索引 EMP_ENAME_IDX ，一个是多列索引 EMP_ENAME_SAL_IDX。我们给出示例 18-23 查询索引相关的列的信息。 例子 18-23 查询与索引列相关的信息 SQL> col column_name for a40 SQL> select index_name,table_name,column_name 2 from user_ind_columns 3* where index_name like 'EMP%' INDEX_NAME TABLE_NAME COLUMN_NAME -------------------------------------------------------------------------- EMP_ENAME_IDX EMP ENAME EMP_ENAME_SAL_IDX EMP SAL EMP_ENAME_SAL_IDX EMP ENAME 输 出结果说明了索引对 应 的 表名，和索引是基于哪 些列创建的，如输 出所示索引 EMP_ENAME_SAL_IDX 是基于表 EMP 的两列 SAL 和 ENAME 创建的。 当然数据字典 USER_INDEXES 也可以很方便地查看索引信息，我们再给出一个例子。 例子 18-24 使用数据字典 USER_INDEXES 查看索引信息 SQL> col dropped for a10 SQL> run 1 select index_name,table_name,table_owner,dropped,tablespace_name Oracle 11g R2 DBA 操作指南 320 2 from user_indexes 3* where index_name like 'EMP%' INDEX_NAME TABLE_NAME TABLE_OWNE DROPPED TABLESPACE -------------------------------------------------------------------------- EMP_ENAME_IDX EMP SCOTT NO USERS EMP_ENAME_SAL_IDX EMP SCOTT NO INDEX_TBS 在查看了我们创建的两个索引的信息，以索引 EMP_ENAME_SAL_IDX 为例，该索引对应的 表为 EMP，该表所属的用户为 SCOTT，并且 DROPPED 为 NO，其存储表空间为 INDEX_TBS。 这里 DROPPED 的含义是，该索引是否是被删除的一个标记。在 Oracle 11g 中，当删除一个对象时， 先把该对象放入垃圾箱而不是立即从库中删除，如果删除一个对象该对象就标记 DROPPED 为 YES。 其实在前面创建的索引是 B 树索引，B 树索引是 Oracle 默认的索引类型。在了解了如何创建 索引和查看索引后，我们想知道是如何创建的索引类型，或希望了解除了 B 树索引外还有哪些索 引类型，以及这些索引的原理和优缺点，这就是下面几节要详细讨论的内容。 18.10 B树索引 B树索引是 Oracle 默认的索引类型，研究 B 树索引也可以帮助理解位图索引和反向键索引， 所以本节花较多篇幅讲解 B 树索引。 18.10.1 B 树索引的工作原理 叶子节点包含索引的实际值和该索引条目的行 ID 即 ROWID。B 树索引的结构如图 18-3 所示。 图 18-3 B 树索引结构图 如图 18-3 所示，B 树索引结构有 3 个基本组成部分：根节点、分支节点和叶子节点。其中根 节点位于索引结构的最顶端，而叶子节点位于索引结构的最底端，中间为分子节点。 在叶子节点中存储了实际的索引列的值和该列所对应的记录的行 ID，即 ROWID，ROWID 是 唯一的 Oracle 指针，指向该行的物理位置，使用 ROWID 是 Oracle 数据库中访问行最快的方法。 第 18 章 索 引 321 叶子节点其实是一个双向链表，每个叶子节点包含一个指向下一个和上一个叶子点的指针，这 样 在 一定范围内便利索引以搜索需要的记录。 每个分支节点又包含其他分支节点，Oracle 设计的 B 树索引结构保证了 B 树索引从根到叶子 都有相等的分支节点，保证了 B 树索引的平衡，这样就不会因为基表的数据插入后删除操作造成 B 树索引变得不平衡，从而影响索引的性能。并且如果一个叶子节点为空，则 Oracle 会释放该空 间用于它处。 下面给出一个使用 B 树索引的搜索过程，如要查找 Larry，则在根节点转向中间的分支节点， 然后继续搜索其下的分支节点，发现需要继续转到它的中间那个子分支节点即 K-M 节点，然后在 叶子节点中找到所需要的列的值及其该列所对应的行 ID。从而找到更多的需要的数据。 Oracle 创建的普通索引如果没有说明类型就是 B 树索引。 18.10.2 B 树索引的注意事项 B 树索引在 Oracle 中是一个通用索引，创 建 索引的时候默认就是 B 树索引。可以是单列索引， 也可以是组合索引（最多可以多达 32 个列），对于 B 树索引，我们需要关注他的二元高度（blevel）。 B 树索引保存了在索引列上有值的每个数据行的 ROWID。 Oracle 不会对索引列上包含 NULL 值的行进行索引，如果索引是一个组合索引，而其中列上 包含 NULL 值，这一行会包含于索引列中。 18.11 位图索引 位图索引是 Oracle 11g Enterprise Edition 支持的索引机制。位图索引使用位图标识被索引的 列值，它 适用于没有大量更新任务的数据仓库，因为使用位图索引时，每个位图索引项与表中大量 的行有关联，当表中有大量数据更新、删除和插入时，位图索引相应地需要做大量更改，而且索引 所占用的磁盘空间也会明显增加，并 且索引在更新时受影响的索引需要锁定，所以位图索引不适合 于有大量更新操作的 OLTP 系统，虽然可以通过重建索引类位图索引，但是对于有大量更新操作的 表最好不选择使用位图索引。 18.11.1 位图索引的使用讨论 位图发挥最大威力的场合是：当一个表中包含了多个位图索引，Oracle 就可以合并从每个位 图索引得到的结果集，快速删除不必要数据。对于较低基数的位图索引来说，位图索引的尺寸远远 小于 B 树索引，因此可以大大减少 I/O 的数量。 对于位图索引的列，列值的数量要求较少或者中等（索引列基数较小）。如列的基数是 4， Oracle 为每个唯一键创建一个位图，然后把与键值所关联的 ROWID 保存为位图。最多可以包括 30 列。 对于非常大的表来说，在多个低基数列上建立位图索引是一个很好的选择。对于位图索引来 说，即使从表中读取很多行，也会使用位图索引。例如在一个 sex 列上建立索引，每次从表中读取 Oracle 11g R2 DBA 操作指南 322 半数的数据行，但是还是会使用位图索引。 18.11.2 创建位图索引 下面我们以一个 SQL 查询为例子，解释位图索引的过程，该语句为： SELECT EMPNO,ENAME,SAL FROM EMP WHERE JOB = ‘SALESMAN’; 上述查询语句的目的是在 EMP 表中查询工作岗位是 SALESMAN 的员工的员工号，姓名和薪 水，此时假设已经在 EMP 表的 JOB 列建立了位图索引，其结构如图 18-4 所示。 图 18-4 位图索引结构图 在该索引图中，共用 5 类 JOB，每类 JOB 对应 14 个比特位（对应 14 行记录），其中某行的 在该列的值与 JOB 值对应则使用比特 1 表示，如 JOB = ‘CLERK’，第一行在该列对应的值是 CLERK，就用比特 1 表示。否则用比特 0 表示，其他 JOB 类类似。 图 18-5 是位图索引操作的逻辑视图，通过该视图读者可以更清楚地了解 SQL 语句执行时，如 何使用位图索引。 图 18-5 位图索引逻辑结构图 通过位图索引扫描 JOB=‘CLERK’对应的位图记录，找到值为 1 的行记录，即找到需要查找 第 18 章 索 引 323 的数据。 下面给出一个例子来创建位图索引，如例子 18-25 所示。 例子 18-25 创建位图索引 SQL> create bitmap index emp_job_bitmap_idx 2 on emp(job); 索引已创建。 此时，我们成功创建了位图索引 EMP_ENAME_BITMAP_IDX。该索引基于表 EMP 的 ENAME 列创建。下面我们通过例子 18-26 查看该索引信息，主要是关注其类型标识。 例子 18-26 查看位图索引 SQL> col index_name for a218 SQL> col index_type for a118 SQL> col table_name for a10 SQL>select index_name,index_type,table_name,status 2 from user_indexes 3* where index_name like 'EMP%' INDEX_NAME INDEX_TYPE TABLE_NAME STATUS ------------------------------------------------- ---------------- EMP_JOB_BITMAP_IDX BITMAP EMP VALID 例子的输出说明创建的位图索引信息，其中 INDEX_TYPE 为 BITMAP，说明这是一个位图索 引。 18.11.3 B 位图索引的插入问题 当使用位图索引的插入数据时，以下 3 点需要注意：  位图索引在批处理（单用户）操作中加载表（插入操作）方面通常比 B 树做得好。  当有多个会话同时向表中插入数据行时不应该使用位图索引。  当每条记录都增加一个新值时，B-树索引要比位图索引快 3 倍。 上面查询语句例子中，4 个低基数列分别建立了位图索引。Oracle 会使用这 4 个位图索引对数 据进行筛选，计算出需要读取的数据行和数据块，然后进行读取。在这个过程中会涉及位图的计算。 Oracle 11g R2 DBA 操作指南 324 在 B 树索引中，可以实现行级锁定，但是在位图索引中，因为对 ROWDI 进行压缩存放（一 个 ROWID 范围+位图），因此每次锁定的都是整个的 ROWID 范围，因此对表中的位图索引列进 行更新的时候，并发性很差，容易导致死锁。SELECT 语句不会受到这种锁定问题的影响。 位图索引有很多的限制：  基于规则的优化器不会考虑位图索引。  当执行 alter table 语句并修改包含位图索引列时，会使得位图索引失效。  位图索引不包含任何列数据，不能用于任何类型的完整性检查，例如主键、唯一键约束。  位图索引不能被声明为唯一索引。  位图索引的最大长度为 30。 不要在繁忙的 OLTP 系统中使用位图索引 18.12 Hash索引 使用 Hash 索引必须要使用 Hash Cluster。我们首先来看一下 Cluster 表的结构，如图 18-6 所示。 图 18-6 簇表结构 第 18 章 索 引 325 在存储数据时，所有与这个集群键相关的行都存储在一个数据块上。如果数据都存储在同一 个数据块上，并且将 Hash 索引作为 where 子句的确切匹配条件，Oracle 就可以通过执行一个 Hash 函数和一个 I/O 来访问数据。如果通过一个二元高度是 3 的 B 树索引来访问数据则需要在检索数据 时使用 4 个 I/O，如图 18-7 所示。 图 18-7 查询过程变换为等价查询，然后匹配 Hash 列和确切的值，最后基于 Hash 函数确定行的物理 存储位置。 例子 18-27 创建 Cluster 表 create cluster credit_cluster(card_no varchar2(16),transdate date sort) hashkeys 10000 hash is ora_hash(card_no) size 2186; create table credit_orders(card_no varchar2(16),transdate date,amount number) cluster credit_cluster(card_no,transdate); 这里的集群键列为 card_no、transdate，而 Hash 列为 card_no。 Hash 索引可能是访问数据库中数据的最快方法，但是有自身的缺点：  集群键上不同值的数目必须在创建 Hash 集群之前就需要确定，需要在创建 Hash 集群的 时候指定这个值，低估了集群键的不同值的数字可能会导致集群的冲突（两个集群键有相 同的 Hash 值）。  一旦这个值设置过低，需要重建 Cluster。  Hash 集群还可能浪费空间，如果无法确定需要多少空间来维护某个集群键上的所有行， 就可能造成空间浪费。  如果不能为集群的未来增长分配好附加的空间，Hash 集群可能就不是最好的选择。  如果应用程序经常在集群上进行全表扫描，Hash 集群可能不是最好的选择，由于需要为 未来增长分配好集群的剩余空间，全表扫描可能非常地消耗资源，Hash 索引非常适合数 据仓库（相对静态值）。 第 26 章 ◄ Oracle数据库实例优化 ► 在理解了 Oracle 数据库的架构、各组件作用以及基本的管理任务后，就可以进一步通过优 化来深入理解各组件的作用了。本章我们介绍 Oracle 数据库实例优化，Oracle 实例由内存组件 和相关的后台进程组成，这些内存组件提高了数据库系统的运行，而后台进程负责管理系统或 者内存组件，使得整个数据库系统协调一致地工作，但是 Oracle 数据库的实例并不满足所有的 生产数据库系统的需求，即使使用自动内存管理，Oracle 也只是提出一个建议。本章我们讨论 在 Oracle 实例优化中最典型的 SGA 组件优化方法。 26.1 详解SGA与实例优化 Oracle 的 SGA 是指系统全局区，它是数据库运行期间使用的一段公有内存，即所有使用数据 库的用户都可以访问这部分内存，它包括共享池、重做日志缓冲区、数据库缓存高速缓冲区、Java 池、大池以及流池。 因为优化 SGA 就是调整这些数据库组件的参数，这些组件是实例优化的操作对象，从而提高 系统的运行效率，如提高用户查询的响应时间等。图 26-1 是 SGA 的组成示意图。 图 26-1 Oracle 的 SGA 组成图 这里我们解释图 26-1 中各个组件的作用以及涉及的参数，这样读者在修改上述组件时就更有 针对性，做到“有的放矢”。  数据库缓冲区：该区域存放用户从数据库中读取的数据，在用户查找数据库时会首先在数 据库缓存中搜索，如 果 没 有 才会读数据库文件。所以该区域不能设置得过小，不然频繁的 第 26 章 Oracle 数据库实例优化 535 读取数据文件会增大查询时间，因为磁盘 I/O 是耗时的行为。  重做日志缓冲区：该缓冲区放置用户改变的数据，所有变化了的数据和回滚需要的数据都 暂时保存在重做日志缓冲区中。涉及的参数为 log_buffer。如下所示我们查询重做日志缓 冲区的大小。 例子 26-1 查询重做日志缓冲区的大小 SQL> show parameter log_buffer; NAME TYPE VALUE -------------------------------- -------------------- ---------------- log_buffer integer 260242640  共享池：共享池包括数据字典高速缓存和库高速缓存，库高速缓存存放 Oralce 解析的 SQL 语句、PL/SQL 过程、包以及各种控制结构如锁、库缓冲句柄等。而数据字典高速缓存保 存执行 sql 语句所需的各种数据字典定义、如表和列的定义、用户访问表的权限等。  Java 池：执行 java 代码的区域。它为 Oracle 数据库中运行的 JVM（Java 虚拟机）分配的 一段固定大小的内存。  大池：该内存区提供大型的内存分配，在共享服务器连接模式下提供会话区，在使用 RMAN 备份时也使用该内存区作为磁盘 I/O 的数据缓冲区。  流池：该区域称为流内存，是为 Oracle 流专用的内存池，流是 Oracle 数据库中的一个数 据共享，其大小可以通过参数 streams_pool_size 动态调整。 在 Oracle11g 以及更高版本中，SGA 的中内存参数可以动态修改，但是总的内存大小受到参 数 SGA_MAX_SIZE 的限制。在安装数据库时，这个参数的值是默认的，而实际的生产数据库往往 需要重新设置一个新值，以利用操作系统中充足的内存资源。我们查看参数 SGA_MAX_SIZE 的值， 如例子 26-2 所示。 例子 26-2 查看参数 SGA_MAX_SIZE 的值 SQL> show parameter sga_max_size; NAME TYPE VALUE ---------------------------- ------------------- ------------ sga_max_size big integer 576M 我们继续查看 SGA 信息，如例子 26-3 所示，查看当前数据库的 SGA 信息。 例子 26-3 查看 SGA 信息 SQL> show sga; Total System Global Area 603979776 bytes Fixed Size 170380 bytes Variable Size 222301108 bytes Database Buffers 373293056 bytes Redo Buffers 7135232 bytes 上述输出的第一个参数 Total System Global Area 其实和例子 26-2 中查到的是一个数据，二者 Oracle 11g R2 DBA 操作指南 536 大小相等，如下所示。 SQL> select 576*1024*1024 bytes 2 from dual; BYTES ----------------- 26039797726 下面我们调整 SGA 的最大尺寸，目的是增大 Oracle 在整个内存中所占的比例，但是不能太大， 一般可以设置为当前内存大小的一半即可。我们修改参数 SGA_MAX_SIZE 以修改 SGA 的尺寸。 如例子 26-4 所示。 例子 26-4 修改 SGA_MAX_SIZE 参数 SQL> alter system set sga_max_size= 700M scope = spfile; 系统已更改。 例子 26-4 中，我们把 SGA_MAX_SIZE 改为了 700M，下面我们查询这次修改，如例子 26-5 所示。 例子 26-5 查询参数 SGA_MAX_SIZE 修改结果 SQL> show parameter sga_max_size; NAME TYPE VALUE --------------------------------------------- ------------------- ------- sga_max_size big integer 576M 观察 VALUE 的值发现该值为 576MB，没 有 修改，其实需要向读者说明参数 SGA_MAX_SIZE 是静态参数，需要重启数据库后方可生效。我们先不关闭数据库，继续对 SGA 的优化。 我们查看在 Oracle 的静态参数中有哪些和 SGA 相关，如例子 26-6 所示。 例子 26-6 查看和 SGA 相关的静态参数 SQL> show parameter sga; NAME TYPE VALUE --------------------------------------- ------------------- ------------- lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 576M sga_target big integer 576M 下面我们依次介绍参数 LOCK_SGA、PRE_PAGE_SGA 和 SGA_TARGET，看这些参数对于优 化 SGA 有什么作用。  LOCK_SGA 的含义及优化 该参数的作用是将 SGA 锁定（lock）在物理内存内，这样就不会发生 SGA 使用虚拟内存的情 况，显然这样可以提高数据的读取速度，记住磁盘 I/O 操作永远是尽量避免或减少的。该参数的默 第 26 章 Oracle 数据库实例优化 537 认值为 FALSE，即不将 SGA 锁定在内存中。下面，我们修改参数 LOCK_SGA 为 TRUE，如例子 26-7 所示。 例子 26-7 设置参数 LOCK_SGA 为 TRUE SQL> alter system set lock_sga = true scope = spfile; 系统已更改。 该参数是静态参数，需要重启数据库才可生效。  PRE_PAGE_SGA 的含义及优化 该参数的作用是启动数据库实例时，将整个 SGA 读入物理内存，对于内存充足的系统而言， 这样显然可以提高系统运行效率。我们修改该参数为 TRUE，如例子 26-8 所示。 例子 26-8 设置参数 PRE_PAGE_SGA 为 TRUE SQL> alter system set pre_page_sga= true scope = spfile; 系统已更改。 下面我们关闭数据库并重启数据库，如例子 26-9 所示。 例子 26-9 关闭并重启数据库 SQL> connect system/oracle@orcl as sysdba 已连接。 SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE 例程已经启动。 Total System Global Area 734003200 bytes Fixed Size 171172 bytes Variable Size 356518044 bytes Database Buffers 369098752 bytes Redo Buffers 7135232 bytes 数据库装载完毕。 数据库已经打开。 现在，我们查看刚才修改的 3 个参数：SGA_MAX_SIZE、LOCK_SGA、PRE_PAGE_SGA， 如例子 26-10 所示。 例子 26-10 查看与 SGA 相关的参数修改结果 SQL> show parameter sga; NAME TYPE VALUE ------------------------------------------- ------------------- -------- lock_sga boolean TRUE Oracle 11g R2 DBA 操作指南 538 pre_page_sga boolean TRUE sga_max_size big integer 576M sga_target big integer 576M 从 上 述输出可以看 出 参 数 LOCK_SGA 和 PRE_PAGE_SGA 的 值 都为 TRUE ， 参 数 SGA_MAX_SIZE 的值也修改为 700MB。读者或许会问，参数 SGA_TARGET 是什么作用呢？  SGA_TARGET 的含义及优化 在 Oracle10g 11g 以及以上的版本中，提供了内存的自动管理，在 Oracle 11g 中更是实现了 SGA 和 PGA 的自动管理，只要设置 memory_target 与 memory_max_size 两个参数即可，这样 Oracle 可 以根据业务需要和服务器自身的软硬件环境自动调整一些内存参数。如果不设置这两个参数，也可 以设置 SGA 和 PGA 各自自动管理，兼容 10g 中的内存管理方式。参数 SGA_TARGET 决定是否使 用 SGA 自动管理，该参数的默认值和系统的 SGA_MAX_SIZE 一样大，当该参数值不为 0 时，则 启动 SGA 的自动管理，该参数可以动态修改，下面我们修改该参数的值为 700MB，如例子 26-11 所示。 例子 26-11 修改参数 SGA_TARGET 的值 SQL> alter system set sga_target = 700M; 系统已更改。 读者可以自行查看修改结果，这里不再给出查询结果， 既然 SGA 可以自动管理，但不是所有的内存组件都可以自动调整，那么那些 SGA 的内存是 否可以自动调整呢，下面是 SGA 可以自动调整的内存组件。  共享池。  Java 池。  大池。  数据库缓冲区。  流池。 这些组件的尺寸不需要用户干预，其值自动设置为 0。我们使用视图 v$parameter 查看这些自 动调整的内存组件的信息。 例子 26-12 查看自动调整的内存组件的信息 SQL> col name for a30 SQL> col value for a20 SQL>select name,value,isdefault 2 from v$parameter 3 where name in ('shared_pool_size','large_pool_size', 4* 'java_pool_size') NAME VALUE ISDEFAULT ------------------------------------------------------------------------- shared_pool_size 0 TRUE large_pool_size 0 TRUE 第 26 章 Oracle 数据库实例优化 539 java_pool_size 0 TRUE 虽然这些参数是可以自动调整的，但是用户依然可以使用 ALTER SYSTEM SET 指令修改内 存组件的尺寸，如下例所示，修改 java_pool_size 的值为 10MB。 SQL> alter system set java_pool_size = 10MB; 系统已更改。 下面我们查询修改结果，如例子 26-13 所示。 例子 26-13 查询参数 java_pool_size 的修改结果 SQL> show parameter java_pool_size; NAME TYPE VALUE -------------------------------------------------------------- ---------- java_pool_size big integer 12M 显然参数 java_pool_size 的值不再是 0，而 VALUE 值变为 12M。读者或许会问前面设置该参 数值为 10M，怎么变成了 12M 呢，其实 Oracle 会针对系统自身情况做一些调整。这是数据库自己 的行为，读者不必太在意。 26.2 将程序常驻内存 在 Oracle 数据库中有一个软件包 DBMS_SHARED_POOL，它提供过程 KEEP 和 UNKEEP， 将用户经常使用的程序如存储过程、触发器、序列号、游标，以及 JAVA SOURCE 等数据库对象 长期保存在一个内存结构中，这个内存区就是共享池（shared pool）。对于用户频繁使用的这些数 据库对象而言，将 其 常驻内存可以减少磁盘 I/O 从而减少用户的响应时间。本节我们先讲解几个数 据块缓冲池，分 别解释他们的作用以及使用时机，然后介绍如何将一个存储过程常驻内存，最后介 绍创建软件包 DBMS_SHARED_POOL 的 dbmspool.sql 过程，从而更清楚地理解软件包中各种过程 的作用以及参数含义。 26.2.1 创建软件包 DBMS_SHARED_POOL 在 Oracle 数据库中，软件包 DBMS_SHARED_POOL 不是默认安装的，所以需要执行一个.sql 脚本文件来创建该软件包。它有两个经常使用的过程：KEEP 和 UNKEEP。KEEP 过程实现将程序 常 驻 内存，而 UNKEEP 将 指 定的程序清除出内存。如果读者开始就尝 试执行软件包 DBMS_SHARED_POOL 的 KEEP 过程，则会提示错误。 首先使用 DBA 用户登录数据库。 例子 26-14 登录数据库并执行 KEEP 过程 SQL> connect system/oracle@orcl as sysdba 已连接。 SQL> execute dbms_shared_pool.keep('HR.SECURE_DML'); BEGIN dbms_shared_pool.keep('HR.SECURE_DML'); END; Oracle 11g R2 DBA 操作指南 540 * 第 1 行出现错误: ORA-06550: 第 1 行, 第 7 列: PLS-00201: 必须声明标识符 'DBMS_SHARED_POOL.KEEP' ORA-026550: 第 1 行, 第 7 列: PL/SQL: Statement ignored 显然，提示执行失败，这说明没有可用的软件包，需要手工创建该软件包，如例子 26-15 所示。 该软件包在笔者的电脑上位于目录 F:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN 下，脚 本文件名为 dbmspool.sql。其实在这个目录下还有很多其他脚本文件，如我们熟悉的创建 SCOTT 用户的脚本文件 scott.sql。 例子 26-15 创建 DBMS_SHARED_POOL 软件包 SQL> connect system/oracle@orcl as sysdba 已连接。 SQL>@F:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\dbmspool.sql; 程序包已创建。 授权成功。 视图已创建。 程序包体已创建。 从输出可以看出，此时成功创建软件包，并且在执行脚本文件 dbmspool.sql 的过程中，实现了 授权和视图创建，并同时创建了过程 KEEP 和 UNKEEP，（当然还有其他过程）我们接下来会进 行更详细的介绍。 如果用户在 SCOTT 用户或其他非 SYSTEM 用户下登录数据库并且尝试创建软件包 DBMS_SHARED_POOL，会提示出错，如下所示。 SQL> connect scott/tiger@orcl 已连接。 SQL> F:\app\Administrator\product\11.1.0\db_1\RDBMS\ADMIN\dbmspool.sql; 程序包已创建。 授权成功。 from dba_object_size * 第 4 行出现错误: ORA-01031: 权限不足 警告: 创建的包体带有编译错误。 第 26 章 Oracle 数据库实例优化 541 显然从输出可以看出当前用户缺少足够的权限，只要使用 SYSTEM 用户登录且赋予 DBA 角 色即可。 在成功创建软件包 DBMS_SHARED_POOL 后就可以使用它的过程 KEEP 来将程序常驻内存了。 26.2.2 将程序常驻内存的过程 软件包DBMS_SHARED_POOL是过程的集合，包含常用的KEEP和UNKEEP过程，使用KEEP 过程将用户频繁使用的程序常驻共享池中，使用 UNKEEP 将指定的程序从共享池中清除。我们先 选择并查看用户 HR 的一个过程。 首先使用 SYSTEM 用户登录数据库。 SQL> connect system/oracle@orcl 已连接。 然后，通过数据字典 DBA_OBJECTS 查询用户 HR 的一个存储过程，我们假设该存储过程是用 户程序频繁调用的过程，然后将其常驻内存，如例子 26-16 所示，先查找用户 HR 的一个存储过程。 例子 26-16 查看用户 HR 拥有的存储过程 SQL> select object_name,object_type 2 from dba_objects 3 where object_type ='PROCEDURE' 4 and owner ='HR'; OBJECT_NAME OBJECT_TYPE ---------------------------------- ------------------------------ SECURE_DML PROCEDURE ADD_JOB_HISTORY PROCEDURE 从查找结果可以看出，用户 HR 有两个存储过程，一个为 SECURE_DML，一个为 ADD_JOB_HISTORY。我们将过程 SECURE_DML 常驻内存，或许读者想知道如何查看该过程的 内容，毕竟对过程的功能了解得越多就越能理解为什么将其常驻内存，为此 Oralce 提供了数据字 典 DBA_SOURCE(USER_SOURCE)。 例子 26-17 查看过程 SECURE_DML 的内容 SQL> SET LINE 100 SQL>select line,text 2 from dba_source 3* where name ='SECURE_DML' LINE TEXT ---------- -------------------------------------------------------------- 1 PROCEDURE secure_dml 2 IS 3 BEGIN 4 IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00' 5 OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN 6 RAISE_APPLICATION_ERROR (-20205, 7 'You may only make changes during normal office hours'); Oracle 11g R2 DBA 操作指南 542 8 END IF; 9 END secure_dml; 已选择 9 行。 该过程的作用很简单，就是判断某种状态下的系统时间如果不在 8 点到 18 点之间或者日期为 周六或周日就提示错误'You may only make changes during normal office hours'。好了，我们不过多分 析这个过程，读者只需要知道数据字典 dba_source 的作用即可。下面演示如何将过程 secure_dml 常驻内存，如例子 26-18 所示。 例子 26-18 将过程 secure_dml 常驻内存 SQL> EXECUTE dbms_shared_pool.keep('HR.SECURE_DML'); PL/SQL 过程已成功完成。 输 出提示已经成功 创建 PL/SQL 过程，为了确认创建结果，我们使用数据字典 V$db_object_cache。它的作用是存储关于数据库对象在缓存中的信息。 例子 26-19 查看用户 HR 的存储过程是否保存在共享池中 SQL> col name for a20 SQL> select name,namespace,sharable_mem,executions,kept 2 from v$db_object_cache 3* where owner ='HR' NAME NAMESPACE SHARABLE_MEM EXECUTIONS KEP -------------------- ---------------------- ---------- ---------- ------- SECURE_DML TABLE/PROCEDURE 1737 0 YES 此时，输出说明用户 HR 的数据库对象即存储过程 SECURE_DML，已经保存在共享池中，因 为 KEPT 列的值为 YES。 既然可以使得一个程序常驻内存，同样有方法将其从内存清除，现在我们使用软件包 DBMS_SHARED_POOL 的 UNKEEP 过程将用户 HR 的过程 SECURE_DML 清除出内存。 例子 26-20 将用户 HR 的过程 SECURE_DML 清除出内存 SQL> EXECUTE dbms_shared_pool.unkeep('HR.SECURE_DML'); PL/SQL 过程已成功完成。 在执行清除任务后，我们再次使用数据字典 v$db_object_cache 来查看清除结果，如例子 26-21 所示。 例子 26-21 查看是否从内存清除过程 SECURE_DML SQL> select name,namespace,sharable_mem,executions,kept 2 from v$db_object_cache 3 where owner ='HR'; NAME NAMESPACE SHARABLE_MEM EXECUTIONS KEP -------------------- ------------------------ ------------------ -------- SECURE_DML TABLE/PROCEDURE 1737 0 NO 第 26 章 Oracle 数据库实例优化 543 如果将实现了常驻内存的程序从内存清除，该程序的记录仍然在数据字典 v$db_object_cache 中，只是 KEPT 列的值为 NO。如果从没有将一个程序常驻内存，则在 数据字典 v$db_object_cache 中不存在该程序的任何记录。 26.2.3 从 DBMSPOOL 脚本理解软件包 DBMS_SHARED_POOL 上面我们使用软件包 DMBS_SHARED_POOL 将用户 HR 的一个过程 SECURE_DML 常驻内 存，同时又使用了软件包过程 UNKEEP 将该过程从内存清除。那么软件包 DBMS_SHARED_POOL 到底是如何创建的呢？我们来分析脚本文件 dbmspool.sql，从而可以更清楚地理解软件包的作用， 以及其中包含的过程的含义。以下从脚本文件中截取部分内容详细说明。 create or replace package dbms_shared_pool is ------------ -- OVERVIEW -- -- This package provides access to the shared pool. This is the -- shared memory area where cursors and PL/SQL objects are stored. 这部分说明该软件包的作用是提供对共享池的访问，使得游标或者 PL/SQL 对象（如存储过程、 函数等）可以存储在共享池中。 该软件包中还包括 4 个函数，我们先介绍 KEEP 函数和 UNKEEP 函数。 关于 KEEP 函数脚本中的内容如下所示。 procedure keep(name varchar2, flag char DEFAULT 'P'); -- Keep an object in the shared pool. Once an object has been keeped in -- the shared pool, it is not subject to aging out of the pool. This -- may be useful for certain semi-frequently used large objects since -- when large objects are brought into the shared pool, a larger -- number of other objects (much more than the size of the object -- being brought in, may need to be aged out in order to create a -- contiguous area large enough. -- WARNING: This procedure may not be supported in the future when -- and if automatic mechanisms are implemented to make this -- unnecessary. 该函数的作用就是将一个数据库对象常驻共享池，使得频繁访问的数据库对象如大对象等提 高用户的访问响应时间，提高访问速度。该函数有两个参数。  第一个参数 name。 该参数用来说明数据库对象的名字，这些数据库对象可以是 PL/SQL 过程、触发器、序列号， 以及 JAVA 对象。如果是 PL/SQL 过程可以使用“模式名.过程名”的方式指定特定模式的数据库 过程名，如 scott.hispackage。  第二个参数 flag。 该参数的作用是说明要常驻的数据库对象的类型，默认类型为包、过程或函数。否则需要使 Oracle 11g R2 DBA 操作指南 544 用一个字符变量说明对象类型。字符值与其代表的对象类型的对应关系如下所示。 Value Kind of Object to keep ----- ---------------------- P package/procedure/function Q sequence R trigger T type JS java source JC java class JR java resource JD java shared data C cursor 关于 UNKEEP 过程的内容如下所示。 procedure unkeep(name varchar2, flag char DEFAULT 'P'); -- Unkeep the named object. -- WARNING: This procedure may not be supported in the future when -- and if automatic mechanisms are implemented to make this -- unnecessary. -- Input arguments: -- name -- The name of the object to unkeep. See description of the name -- object for the 'keep' procedure. -- flag -- See description of the flag parameter for the 'keep' procedure. -- Exceptions: -- An exception will raised if the named object cannot be found. 因为有了对于 KEEP 过程的详细说明，而过程 UNKEEP 的参数含义和 KEEP 过程的相同，所 以不再重述 UNKEEP 过程的参数含义。 在该脚本文件中还有一条重要的授权语句，如下所示。 grant execute on dbms_shared_pool to execute_catalog_role 将对软件包 DBMS_SHARED_POOL 的执行权利赋予角色 EXECUTE_CATALOG_ROLE，而 我们当前的 SYSTEM 用户具有 DBA 权限所以自动具有角色 EXECUTE_CATALOG_ROLE，也可 以通过数据字典查询当前用户具有的角色，如例子 26-22 所示。 例子 26-22 查看当前用户的角色信息 SQL> select * 2 from dba_roles 3 where role like 'EXECUTE%'; ROLE PASSWORD ---------------------------------------------- ------------------------- EXECUTE_CATALOG_ROLE NO 例子 26-22 说明了角色 EXECUTE_CATALOG_ROLE 的存在，所以当前用户在创建了软件包 DBMS_SHARED_POOL 后就可以使用它了。 其实，笔者是希望读者在使用脚本文件时一定要仔细阅读脚本文件的内容，这样就可以从本 第 26 章 Oracle 数据库实例优化 545 质上理解一个软件包的作用和其中包含的其他过程。 26.3 将数据常驻内存 在生产数据库中，为了提高用户的访问速度，对于经常使用的表，可以使其常驻内存中，避 免了对该表访问时产生频繁的磁盘 I/O 行为，这样可以提高用户的访问响应时间，虽然造成一定的 内存占用，但 是 使用内存访问确实提高了访问的响应时间，在某种程度上是有效的。而当不需要频 繁访问该表时，DBA 可以将其从内存中清除。本节我们再次学习 Oralce 的各种数据块的缓存池， 通过分析了解将数据常驻内存的必要性和可行性。然后给出一个具体的例子将 SCOTT 用户的 EMP 表和一个索引常驻内存。 26.3.1 再论数据块缓存池 在 Oracle 数据库体系结构的介绍中，读者已经知道在数据库块写到磁盘文件之前，或者从磁 盘文件读取数据之后，首先需要将数据块缓存在数据库高速缓存中，所以需要适当设置该缓冲区的 大小以满足用户需求。在 Oracle 8 之后的版本中，用户可以把 SGA 中段的已缓存块放在 3 个缓冲 池中。  默认池（default pool）：所有的段都放在这个池中，即原先的缓冲区池，如果没有指定数 据的缓存位置，默认将数据缓存在这个池中。  保持池（keep pool）：对于用户频繁访问的数据（如表或索引等数据库对象的数据块）可 以放置在这个候选的缓冲区池中。放在默认池中的数据块，虽然可以频繁访问，但 是 这 些 段数据会老化而退出默认池，所以最好放置在保持池中，使得数据可以长久保存。  回收池（recycle pool）：对于随机访问的大段可以放在这个缓冲区池中，因为大的数据段 会很快老化退出缓冲池，导致缓冲区的频繁刷新输出，所以需要将随机访问的大段放置在 这个缓冲区池中。 在 Oracle 数据库中保持池和回收池都是用户管理的，即这两个池的大小需要手工配置，而默 认池是自动管理的，在 SGA 中分配。我们通过以下指令可以查看这些保持池的大小信息，如例子 26-23 所示。 例子 26-23 查看保持池的大小信息 SQL> show parameter keep NAME TYPE VALUE ---------------------------------------- ------------------ ------------- buffer_pool_keep string control_file_record_keep_time integer 7 db_keep_cache_size big integer 0 从输出可以看出，对于手动配置的缓冲池保持池的大小，对应的参数 db_keep_cache_size 的值 为 0。 Oracle 11g R2 DBA 操作指南 546 在没有设置保持池和回收池前，在数据库中只使用默认池作为数据块的缓冲池。我们可以通 过例子 26-24 查询当前数据库所使用的数据库块的缓冲池。 例子 26-24 查看当前库的数据块的缓冲池 SQL> select id,name,block_size,buffers 2 from v$buffer_pool; ID NAME BLOCK_SIZE BUFFERS ---------- --------------------- ------------------------ ---------------- 3 DEFAULT 8192 47904 显然，当前数据库只用一个默认的数据块缓冲池，在手工设置保持池后才会显示保持池的作 为数据块缓冲池的信息。 在优化时，我们需要根据实际的需求，将用户经常使用的表或者索引放在保持池 keep pool 中， 接下来我们介绍如何设置保持池的大小，以及如何将数据表和索引常驻内存（保持池）中。 26.3.2 将数据常驻内存的过程 我们将用户 SCOTT 的 SALGRADE 表以及表 EMP 建立的基于函数的索引 SCOTT_EMP_ INCOME_IDX 常驻保持池中。 通过上节讨论的各种数据块的缓冲池知道，保持池的大小需要手工设置，显然这个尺寸是多 少应该基于常驻保持池中的数据的大小，因为我们要将一个表以及索引保存在保持池中，所以需要 先确认这些数据库对象的大小，如例子 26-25 所示。 例子 26-25 查看表 SALGRADE 和索引 SCOTT_EMP_INCOME_IDX 的块大小 SQL> col segment_name for a20 SQL>select segment_name,segment_type,blocks 2 from dba_segments 3 where owner ='SCOTT' 4* and segment_name in ('SALGRADE','SCOTT_EMP_INCOME_IDX') SEGMENT_NAME SEGMENT_TYPE BLOCKS -------------------------------------------------------- ---------------- SALGRADE TABLE 8 SCOTT_EMP_INCOME_IDX INDEX 8 表 SALGRADE 和索引 SCOTT_EMP_INCOME_IDX 的大小都为 8 个数据库块大小。读者需要 注意数据字典 dba_segments 是静态数据字典，如果需要获得最新的段统计信息，需要使用 ANALYZE 指令收集统计信息，如例子 26-26 所示。 例子 26-26 收集表和索引的最新统计信息 SQL> analyze index scott.SCOTT_EMP_INCOME_IDX compute statistics; 索引已分析 SQL> analyze table scott.salgrade compute statistics; 第 26 章 Oracle 数据库实例优化 547 表已分析。 那么在确认了表和索引的占用的数据块数后，数据库块大小是多少呢？我们通过例子 26-27 查询库块大小。 例子 26-27 查询的数据库块大小 SQL> show parameter db_block_size; NAME TYPE VALUE -------------------------------------- ------------------ -------------- db_block_size integer 8192 从输出看出当前数据库的数据块大小为 8KB。所以通过这些数据（索引和表的占用的数据块 数和数据块大小）可以计算当前表和索引常驻内存需要多大，如例子 26-28 所示。 例子 26-28 计算要保存的表和索引的大小 SQL> select (8+8)*8 Kbytes from dual; KBYTES ---------- 17 我们需要 17KB 的保持池大小，在确认了要保存的数据的大小后，就可以手工设置保持池的大 小了，如例子 26-29 所示。 例子 26-29 设置保持池的大小 SQL> connect system/oracle@orcl as sysdba 已连接。 SQL> alter system set db_keep_cache_size = 10MB; 系统已更改。 我们将保持池的大小设置为 10MB，这样可以充分满足我们要存储的包 SALGRADE 和索引 SCOTT_EMP_INCOME_IDX 大小。我们接着查看该当前数据库中数据块的缓冲池信息，如例子 26-30 所示。 例子 26-30 查询当前库的数据块的缓冲池信息 SQL> select id,name,block_size,buffers 2 from v$buffer_pool; ID NAME BLOCK_SIZE BUFFERS ---------- -------------------- ---------- ---------- 1 KEEP 8192 1497 3 DEFAULT 8192 46407 从输出可以看出，多了一个缓冲池 keep，该池的数据块大小为 8KB，缓冲区大小为 1497 个数 据库块大小，即 10MB。 下面我们就可以将索引和数据表设置为常驻保持池中了。在设置之前，我们先看看表 SALGRADE 和索引 SCOTT_EMP_INCOME_IDX 当前存放在什么缓冲池中，如例子 26-31 所示。 Oracle 11g R2 DBA 操作指南 548 例子 26-31 查看表 SALGRADE 当前的存放在什么缓冲池中 SQL> connect scott/tiger@orcl 已连接。 SQL> select table_name,tablespace_name,buffer_pool 2 from user_tables 3 where table_name ='SALGRADE'; TABLE_NAME TABLESPACE_NAME BUFFER_POOL ------------------------------------------------------------------------- SALGRADE USERS DEFAULT 显示当前的表 SALGRADE 放在默认缓冲池中，因为 BUFFER_POOL 的值为 DEFAULT。下 面再查看索引 SCOTT_EMP_INCOME_IDX 的缓存池。 例子 26-32 查看索引 SCOTT_EMP_INCOME_IDX 的缓存池 SQL> select index_name,table_name,buffer_pool 2 from user_indexes 3 where index_name ='SCOTT_EMP_INCOME_IDX'; INDEX_NAME TABLE_NAME BUFFER_POOL ------------------------------------------------- ----------------------- SCOTT_EMP_INCOME_IDX EMP DEFAULT 显示当前的索引 SCOTT_EMP_INCOME_IDX 放在默认缓冲池中，因为 BUFFER_POOL 的值 也为 DEFAULT。下面我们将表和索引分别设置为常驻保持池中。 例子 26-33 将表 SALGRADE 设置为常驻内存 SQL> alter table salgrade 2 storage (buffer_pool keep); 表已更改。 现在我们通过数据字典 user_tables 查看表 salgrade 的缓冲池信息，看是否修改为常驻在保持池 中，如例子 26-34 所示。 例子 26-34 查看表 SALGRADE 的缓冲池 SQL> select table_name,tablespace_name,buffer_pool 2 from user_tables 3 where table_name ='SALGRADE'; TABLE_NAME TABLESPACE_NAME BUFFER_POOL ---------------------------------------------- ------------------------- SALGRADE USERS KEEP 从列 BUFFER_POOL 的值为 KEEP 可以知道，表 SALGRADE 已经设置为常驻内存（保持池） 中了。下面我们设置索引常驻内存。 第 26 章 Oracle 数据库实例优化 549 例子 26-35 将索引 SCOTT_EMP_INCOME_IDX 设置为常驻内存 SQL> alter index scott_emp_income_idx 2 storage(buffer_pool keep); 索引已更改。 现在使用数据字典 USER_INDEXES 查看对索引 SCOTT_EMP_INCOME_IDX 的缓冲池的修改 结果，如例子 26-36 所示。 例子 26-36 查看索引 SCOTT_EMP_INCOME_IDX 的缓冲池 SQL> select index_name,table_name,buffer_pool 2 from user_indexes 3 where index_name = 'SCOTT_EMP_INCOME_IDX'; INDEX_NAME TABLE_NAME BUFFER_POOL --------------------------------------------------- --------------------- SCOTT_EMP_INCOME_IDX EMP KEEP 显然，从列 BUFFER_POOL 的值为 KEEP 知道，索引 SCOTT_EMP_INCOME_IDX 已经设置 为常驻内存了。 26.3.3 将常驻内存的程序恢复为默认缓冲池 我们将用户 SCOTT 的 SALGRADE 表 以及表 EMP 中建立 的基于函 数的索引 SCOTT_EMP_INCOME_IDX 常驻保持池中。在不需要频繁访问这些表或索引时，可以将其恢复为 默认缓冲池，这样就可以释放一部分内存，给其他频繁访问的数据使用。下面先演示如何将表 SALGRADE 恢复为默认缓冲池。 例子 26-37 将表 SALGRADE 恢复为默认缓冲池 SQL> alter table salgrade 2 storage(buffer_pool default); 表已更改。 接着可以查看修改结果，确认是否将表 SALGRADE 的缓冲池设置为默认缓冲池，如下例所示。 例子 26-38 查看表 SALGRADE 的缓冲池信息 SQL> select table_name,tablespace_name,buffer_pool 2 from user_tables 3 where table_name ='SALGRADE'; TABLE_NAME TABLESPACE_NAME BUFFER_POOL --------------------------------------------------- --------------------- SALGRADE USERS DEFAULT 输出说明已经将表 SALGRADE 常驻内存改为使用默认缓冲区，因为 BUFFER_POOL 的值已 经为 DEFAULT，以后对表 SALGRADE 的访问将把表数据读入默认缓冲区。 接下来将索引 SCOTT_EMP_INCOME_IDX 从常驻内存改为使用默认缓冲池，如下例所示。 Oracle 11g R2 DBA 操作指南 550 例子 26-39 将索引 SCOTT_EMP_INCOME_IDX 恢复为默认缓冲池 SQL> alter index scott_emp_income_idx 2 storage (buffer_pool default); 索引已更改。 其实，与设置为常驻内存不同的是，STORAGE 子句中的一个参数，将设置常驻内存的 KEEP 参 数 改 为 DEFAULT 就 修改了 索引的 缓冲池设置。此时，我们成功 将 索引 SCOTT_EMP_INCOME_IDX 的缓冲池设置为默认缓冲池。 显然此时，保持池依然占用内存，但是其中已经没有了数据，那么如何释放保持池中的内存 呢？我们使用 ALTER SYSTEM 指令来回收这段内存，如下例所示。 例子 26-40 回收保持池中的内存 SQL> connect system/oracle@orcl as sysdba 已连接。 SQL> alter system set db_keep_cache_size = 0; 系统已更改。 此时，我们不再使用保持池作为缓冲池，可以使用数据字典 v$buffer_pool 来验证。 例子 26-41 查看与数据库相关的缓冲池信息 SQL> select id,name,block_size,buffers 2 from v$buffer_pool; ID NAME BLOCK_SIZE BUFFERS ---------- --------------------------------------------- ---------------- 3 DEFAULT 8192 47904 显然此时只有默认缓冲池可以使用，说明保持池已经不再有效。 26.4 优化重做日志缓冲区 重做日志缓冲区是一段临时存储重做数据的内存区，用户所有变化了的数据前项和修改后的 数据都保存在重做日志缓冲区中，由 LGWR 进程负责写入重做日志文件。在优化时，需要考虑该 内存区的大小，以及 LGWR 的写速度和重做日志文件所在磁盘的争用等。本节我们首先重述重做 日志文件的工作机制，理 解 和 重 做日志相关的等待事件，最后给你相应地解决问题的思路，达到优 化重做日志缓冲区的作用。 26.4.1 深入理解重做日志缓冲区的工作机制 在 SGA 中重做日志缓冲区一般是最小的一个内存结构，在用户对数据库做更改时，重做日志 缓冲区为所有修改数据的服务器进程共享使用，这些服务器进程负责将更改数据的原始值和修改后 的新值以及事务 ID 写入重做日志缓冲区，而 LGWR 进程负责将重做日志缓冲区中的数据写入重做 第 26 章 Oracle 数据库实例优化 551 日志文件，Oracle 的重做日志组是循环使用的，当覆盖以前的重做日志文件时，如果数据库处于归 档模式则自动启动归档进程，ARCH 负责将被覆盖的重做日志文件的内容复制到归档日志文件， 图 26-2 是以上行为的示意图。 重做日志缓冲区 服务器进程 LGWR 重做日志文件 归档文件 ARCH 图 26-2 重做日志缓冲区以及相关进程的工作示意图 我们看到图 26-2 是一个静态图，其实在数据库内部上述活动是个十分活跃的行为，服务器进 程修改数据库中的数据或表结构，不 断地将相关的重做数据写入重做日志缓冲区，而重做日志缓冲 区在一定的条件下，比如每 3 秒钟，将其中的重做数据写入重做日志文件，而当重做日志文件切换 时（无论是用户主动切换，还是数据库自己的行为）导致归档进程 ARCH 启动，把重做日志文件 中的数据读入归档文件，然后数据库才可以继续使用相关的重做日志文件，在这个过程中无论哪里 出现问题都会导致一些等待事件，如果是频繁发生的等待事件，就会影响系统的性能，比如重做日 志缓冲区太小，而服务器进程写入速度又比 LGWR 写出的速度快就会出现 log buffer space 等待时 间，此时就需要 DBA 主动采取优化了。 为了更清楚地理解在用户修改一行数据时与重做日志相关的一系列行为，我们给出一个过程 分析。 用户发出一条更新的 SQL 语句，该语句是某个事务的一部分，Oracle 为该事务分配了唯 一的事务号。 服务器进程负责将需要的数据、索引和还原数据读入内存，并将要更新的行加锁。 服务器进程获得重做复制闩锁（闩锁实现对重做日志缓冲区的串行使用），该闩锁是服务 器进程访问重做日志缓冲区的第一步。此时如果没有其他的闩锁可用，则别的服务器进程无法使用 重做日志缓冲区。 服务器进程获得重做分配闩锁从而获得在重做日志缓冲区中的预留空间，此时释放重做分 配闩锁。 服务器进程利用重做复制闩锁把重做项（更新数据的原始值、操作类型、事务号等信息） 写入重做日志缓冲区，然后释放重做日志复制闩锁。 服务器进程把还原信息写入与该事务相关的还原段，还原段在用户使用 ROLLBACK 指令 时使用。 服务器进程更新锁住的数据，将回滚所需的原始值和对数据所做的修改都写入数据库高速 缓冲区。然后数据库高速缓冲区中的这些数据被标记为脏数据，因为目前内存和外存中的数据不一 致。 在深入地了解了重做日志缓冲区的工作机制和过程后，我们分析 LGWR 进程何时将重做日志 缓冲区的重做数据写入重做日志文件，理解这些内容对于优化重做日志缓冲区是很有必要的。以下 列出的是 LGWR 把重做日志缓冲区写入重做日志文件的条件。  每隔 3 秒钟。  事务被提交时。 Oracle 11g R2 DBA 操作指南 552  当重做日志缓冲区的记录的变化的数据量超过 1MB。  当重做数据的大小为重做日志缓冲区大小的 1/3 时。这里需要说明，并不是重做日志缓冲 区永远不会填到超过其 1/3 容量，而是说明当重做数据量达到其容量的 1/3 这个阀值时， LGWR 进程会写出重做日志缓冲区中的数据，而剩下的 2/3 的数据可以供其他服务器进程 使用。  检验点发生时。  当 DBWR 进程将数据库高速缓冲区中的数据写到数据文件前。 下面我们查看重做日志缓冲区的尺寸，如下例所示。 例子 26-42 查看重做日志缓冲区的尺寸 SQL> show parameter log_buffer; NAME TYPE VALUE ----------------------------------------- ------------------- ---------- log_buffer integer 7024640 26.4.2 重做日志缓冲区相关的等待事件 如果需要优化重做日志缓冲区，必须首先确认发生了与重做日志缓冲区相关的等待事件，否 则不应该随便调整重做日志缓冲区的尺寸。读者可以通过相关等待（WAIT）视 图和事件（EVENT） 视图，确认等待事件以及该事件涉及的文件和会话，如下所示。 例子 26-43 通过数据字典视图查看会话等待事件 SQL> col event for a35 SQL> col username for a10 SQL> select sw.sid,s.username,sw.event,sw.wait_time 2 from v$session s,v$session_wait sw 3 where sw.event not like 'rdbms%' 4 and sw.sid = s.sid 5* order by sw.wait_time,sw.event SID USERNAME EVENT WAIT_TIME ---------- ---------- ----------------------------------- ---------- 154 SYSTEM SQL*Net message to client -1 149 Streams AQ: qmn coordinator idle wa 0 it 144 Streams AQ: qmn slave idle wait 0 147 Streams AQ: waiting for time manage 0 ment or cleanup tasks 159 jobq slave wait 0 170 pmon timer 0 164 smon timer 0 已选择 7 行。 第 26 章 Oracle 数据库实例优化 553 虽然出现了等待事件，但是若该等待事件没有影响系统使用或系统性能就不要轻易去优化。 下面我们分析和重做日志缓冲区相关的等待事件，以及事件发生的原因分析，一旦找到等待 事件并知道该事件发生的相关原因，就可以实现优化工作了。  Log buffer space：该事件说明缺少重做日志的缓冲区空间，造成该等待事件的原因一般 是服务器进程写入重做日志缓冲区的速度高于 LGWR 将重做日志缓冲区写出的速度，也 有可能是重做日志文件所在磁盘设备速度慢或者存在设备争用，造成 LGWR 进程无法及 时将重做日志缓冲区中的重做数据写入重做日志文件。 优化方法：调整重做日志缓冲区尺寸，或者将重做日志数据文件迁移到高速磁盘上，或 者 为了解决争用，将重做日志文件和数据库数据文件以及归档文件放在不同的磁盘上。  Log file parallel write：该事件的含义是日志文件并行写等待，是在将重做日志缓冲区中 的重做数据写入磁盘引起的等待事件。造成该事件的原因一般是联机重做日志文件所在的 设备速度慢或者存在磁盘争用。 优化方法：将重做日志文件和数据库数据文件以及归档文件放在不同的磁盘上。以 及将重 做日志文件放置在高速盘上。  Log file single write：该等待事件仅与写日志文件头块有关，表示检查点中的等待。  Log file switch(archiving needed) ：该等待事件的含义是日志文件切换等待，对于处于归 档模式的数据库而言，当日志组写满后，在日志切换时，如果需要覆盖先前的日志，而该 日志需要归档进程写入归档文件，由于写入归档文件需要时间，而 LGWR 进程需要将重 做日志缓冲区中的数据写入重做日志文件，而归档未完成需要等待，在此期间就产生了 Log file switch 事件，该等待事件的原因一般是 I/O 问题、ARCH 归档进程跟不上 LGWR 日志写进程的速度或者日志组太少引起的。 优化方法：启用多个归档 ARCH 进程或 I/O 从进程（slave process），将归档的文件和数 据文件或重做日志文件放置在不同的磁盘上，减少磁盘争用以减少 ARCH 归档进程的归档 事件或者增加重做日志组。  Log file switch(checkpoint incomplete) ：该 事件是由于日志切换太频繁引起的，由于频繁 地切换重做日志文件，造成检验点的排队。发生该等待事件的原因一般是重做日志缓冲区 空间太小或者重做日志组太少。 优化方法：增加重做日志组或者增加重做日志缓冲区的尺寸。  Log file sync：当 用 户 提 交时，重做日志缓冲区中的数据会一次全部写到重做日志文件中， 此时发生的 LGWR 的写出等待就是 log file sync 等待。造成该等待原因一般是放置联机重 做日志文件的磁盘存在争用或者磁盘速度慢。 优化方法：将重做日志文件和数据文件或归档重做日志文件放置在不同的磁盘上，以 减少 数据库中的各种文件之间的 I/O 争用，同时可以把重做日志文件放在高速磁盘上，以 减少 将重做数据写入重做日志文件的时间。  Latch free：该等待事件的含义是当前的服务器进程需要某个闩锁，比如等待共享池的库 高速缓存闩锁。如果发生该等待事件也可以通过数据字典 v$latch 查看相关的闩锁命中率， Oracle 11g R2 DBA 操作指南 554 如下例所示。 例子 26-44 查询与闩锁 LATCH 相关的信息 SQL> select latch#,name,gets,misses,1-(misses/(gets+misses)) "gets rate" 2 from v$latch 3 where misses>1; LATCH# NAME GETS MISSES gets rate -------------------------------------- ---------- ---------- ---------- 180 dummy allocation 12605 2 .998755445 121 checkpoint queue latch 24231 9 .999267713 213 shared pool 96480 112 .998840484 ………………. 215 library cache lock 45617 3 .999934239 199 row cache objects 80118 9 .9998872678 214 library cache 135162 59 .99952632677 146 redo writing 26541 115 .9827223526 216 library cache pin 7765 3 .99995883 19 enqueue hash chains 69022 5 .9999275265 302 session state list latch 1602 5 .99268882612 已选择 18 行。 可以通过以上所示的 get rate 来进一步确认闩锁的命中率。 在上述与重做日志缓冲区有关的等待事件的分析中，我们详细分析了造成这种等待事件的原 因，读 者 或 许可以体会到，理解重做日志缓冲区的工作机制，以及 它 涉及的各种进程对于理解这些 等待事件是很有好处的，我们知道一个事件的含义，又可以分析事件发生的原因，对解决这个等待 事件也就有了相应的解决思路，其实我们已经给出了解决等待事件的思路和优化方法，只是在具体 操作时读者需要回顾以前章节的内容，如迁移数据文件、创建重做日志组以及向重做日志组中添加 日志文件。 26.4.3 设置重做日志缓冲区大小 在发生与重做日志缓冲区相关的等待事件时，或者在 DML 操作频繁的生产数据库中，往往需 要增加重日志缓冲区的尺寸。下面我们介绍如何修改重做日志缓冲区的大小并使其生效，如 下 例所 示，先查看重做日志缓冲区的大小。 例子 26-45 查看重做日志缓冲区的大小 SQL> show parameter log_buffer; NAME TYPE VALUE --------------------------------- ------------------- ------------ log_buffer integer 7024640 从输出可以看出该重做日志缓冲区的大小为 7 024 640B，所以在设置该参数的值时，也必须 使用字节数据来修改该参数。为了更容易理解，我们将字节数据转换成 MB 的形式，并使用数据 字典视图 v$parater，如下例所示。 第 26 章 Oracle 数据库实例优化 555 例子 26-46 使用数据字典视图 v$parameter 查看重做缓冲区的大小 SQL> col name for a20 SQL> select name,value/(1024*1024) "MB 节" 2 from v$parameter 3* where name ='log_buffer' NAME MB ---------------------------------------- ---------- log_buffer 6.269921875 我们可以看到当前数据库的重做日志缓冲区的尺寸为 7MB。由于参数 LOG_BUFFER 是静态 参数，所以设置该参数后必须重新启动数据库才可以生效，下面我们增大重做日志缓冲区的尺寸， 设置为 10MB。但是需要将 10MB 转换成字节（10×1024×1024=10 485 760B），如下例所示。 例子 26-47 设置重做日志缓冲区大小为 10MB SQL> alter system set log_buffer = 10485760 scope = spfile; 系统已更改。 为了值得设置的重做日志缓冲区参数生效，必须关闭数据库重新启动。下面我们关闭数据库 并重新启动数据库。 例子 26-48 关闭并重启数据库 SQL> connect system/oracle@orcl as sysdba 已连接。 SQL> shutdown immediate; 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE 例程已经启动。 Total System Global Area 734003200 bytes Fixed Size 171172 bytes Variable Size 197134492 bytes Database Buffers 52478000 bytes Redo Buffers 11329536 bytes 数据库装载完毕。 数据库已经打开。 重新打开数据库后，我们修改过的重做日志缓冲区的大小生效。我们查询重做日志缓冲区的 修改结果，如下例所示。 例子 26-49 查询重做日志缓冲区的尺寸 SQL> show parameter log_buffer; NAME TYPE VALUE ------------------------------- ------------------ ------------ Oracle 11g R2 DBA 操作指南 556 log_buffer integer 11154432 我们看到此时 LOG_BUFFER 的 VALUE 为 11 154 432 字节，已经不是以前的 7 024 640 字节， 说明我们所做的修改生效。 我们设置的该缓冲区的大小为 10MB，但是这里的数值为 10.263726953MB，是因为 Oracle 会根据系统情况做一些调整。 26.5 优化共享池（Shared Pool） 在 Oracle 数据库系统架构中，共享池由两部分组成：库高速缓存和数据字典高速缓存。其中 库高速缓存存放 SQL 语句的正文、编译后的代码以及最终的执行计划，而数据字典高速缓存存放 与 SQL 语句操作相关的数据库对象，如表、索引、列以及其他对象的定义和权限信息。 对于库高速缓存而言，重用 SQL 语句可以减少硬解析的时间，从而减少 SQL 语句的响应时间。 而数据字典高速缓存则减少了对 SQL 语句涉及的数据库对象和权限定义的磁盘访问，也减少了 SQL 语句的响应时间。对共享池的优化目的就是在不影响性能的条件下提高 SQL 代码以及数据字 典的使用率。 26.5.1 库高速缓存 库高速缓存存放 SQL 语句的正文、编译后的代码，以及最 终的执行计划。而在 SQL 语句的处 理步骤中，对 SQL 语句的解析是最耗费时间的。解析需要经过 SQL 语句的语法语义分析、基于优 化模式选择优化方案，以及执行最终执行计划，这种解析我们称为硬解析。而如果有相同的 SQL 语句的执行计划已经缓存在库高速缓冲区中，此时就只执行一个软分析，软分析的含义通过 SQL 语句的正文比对找到该语句的最终执行计划。所以，我们要尽量减少硬分析的发生。 26.5.2 使用绑定变量 Oracle 如何判断两个 SQL 语句是相同的呢？Oracle 对语句相同的判断条件很苛刻，要求两个 语句的正文必须一样，这包括空格以及字母的大小写情况。下面所示的两个语句 Oracle 认为是不 同的。 SQL> select ename,sal,mgr 2 from scott.emp; SQL> select ename,sal,MGR 2 from scott.emp; 二者的区别在于 MGR 的大小写不同，虽然二者的查询结果相同，但是 Oracle 认为这两个语 句是不同的，对于第二个语句会发生硬解析，大量硬解析会造成内存、CPU 以及闩的争用。 在具有如下相似的查询时，读者最好使用绑定变量，使用绑定变量 Oracle 认为使用相同条件 但是不同参数值的 SQL 语句是相同的，减少硬解析的发生，如下所示。 SQL> select ename,sal,mgr from scott.emp where sal>5000; 第 26 章 Oracle 数据库实例优化 557 SQL> select ename,sal,mgr from scott.emp where sal>2000; SQL> select ename,sal,mgr from scott.emp where sal>3000; 对于上述的查询，Oraclee 认为这是 3 个独立的查询，因为 3 个语句的正文并不完全相同，所 以此时，这样的查询我们最好使用绑定变量，如下所示。 例子 26-50 使用绑定变量 SQL> select ename,sal,mgr from scott.emp where sal>&salary 输入 salary 的值: 5000 原值 1: select ename,sal,mgr from scott.emp where sal>&salary 新值 1: select ename,sal,mgr from scott.emp where sal>5000 未选定行 SQL> select ename,sal,mgr from scott.emp where sal>&salary 输入 salary 的值: 2000 原值 1: select ename,sal,mgr from scott.emp where sal>&salary 新值 1: select ename,sal,mgr from scott.emp where sal>2000 ENAME SAL MGR ---------- ---------- ---------- JONES 2975 6839 BLAKE 750 7839 CLARK 2450 7839 SCOTT 3000 7566 KING 5000 FORD 3000 7566 已选择 6 行。 例子 26-50 通过绑定变量&salary 来代替具体的值，在查询时刻再输入具体的值，这样 Oracle 就认为使用相同的 SQL 语句，即 select ename,sal,mgr from scott.emp where sal>&salary，这样就极 大地减少了硬解析的数量。 我们上面已经提过了硬分析和软分析的概念，这里再详细分析并给出一个示例说明，硬分析 的过程包括 SQL 语句的语义和语法分析、检查对象和用户权限等信息，基于优化方式生成最终执 行计划，最后将执行计划存在库高速缓存，显然整个过程占用大量的 CPU 时间，并会引起闩争用 （因为此时的库高速缓存被占用），势必会增加 SQL 语句的响应时间。 软分析则没有 SQL 语句的语法和语义分析和基于优化方式生成执行计划的过程，比对 SQL 语 句的正文（通过对语句的散列实现），发现库高速缓存中的相同 SQL 语句，以及执行该语句的执 行计划。显然软分析减少了 CPU 的计算时间，又减少了闩的争用。下面我们通过启动 SQL Trace 跟踪一个 SQL 查询语句，查看是否发生硬解析。具体步骤如下所示。 清空共享池（目的是方便对 SQL 语句的分析）。 SQL> alter system flush shared_pool; 系统已更改。 启动会话级的 SQL 追踪功能。 Oracle 11g R2 DBA 操作指南 558 SQL> alter session set sql_trace=true; 会话已更改。 执行 SQL 查询语句并通过 TKPROF 解释该追踪文件。 例子 26-51 执行 SQL 查询语句 SQL> select ename,sal,mgr from scott.emp where sal>4000; ENAME SAL MGR ---------- ---------- ---------- KING 5000 我们开始将共享池清空，这样执行的 SQL 查询语句必须被硬解析，我们打开会话追踪文件， 发现上述语句的解析过程，如下所示。 PARSING IN CURSOR #3 len=50 dep=0 uid=0 oct=3 lid=0 tim=542979326426 hv=268121321 ad='22d049b8' select ename,sal,mgr from scott.emp where sal>4000 END OF STMT PARSE #3:c=187500,e=304194,p=0,cr=1726,cu=1261,mis=1,r=0,dep=0,og=2,tim=54297932642 EXEC #3:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=5429793715 FETCH #3:c=0,e=426,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=2,tim=5429793808 FETCH #3:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=2,tim=5429794112 STAT #3 id=1 cnt=1 pid=0 pos=1 obj=51151 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=39 us)' 在上述追踪文件中参数 mis 对应的值说明是否发生硬解析，如果该参数值为 1 说明发生了硬 解析，因为此时的库高速缓存丢失一次命中。 下面是我们在执行一个类似的查询，看是否发生了硬解析，会使得读者对使用绑定变量有更 深入的理解。 例子 26-52 执行一个与例子 26-51 类似的查询 SQL> select ename,sal,mgr from scott.emp where sal>3000; ENAME SAL MGR ---------- ---------- ---------- KING 5000 此时，我们再查看追踪文件，相关内容如下所示。 PARSING IN CURSOR #2 len=50 dep=0 uid=0 oct=3 lid=0 tim=26111445032 hv=269262689503 ad='22d42677' select ename,sal,mgr from scott.emp where sal>3000 END OF STMT PARSE #2:c=152626,e=2264526,p=0,cr=19,cu=226,mis=1,r=0,dep=0,og=2,tim=26111445023 EXEC #2:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=26111445200 第 26 章 Oracle 数据库实例优化 559 FETCH #2:c=0,e=926,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=2,tim=26111445387 FETCH #2:c=0,e=20,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=2,tim=2611144262269 STAT #2 id=1 cnt=1 pid=0 pos=1 obj=51151 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=83 us)' 从输出可以看出此时的 mis 值为 1，说明此时使用了硬解析，因为两个 SQL 语句的正文并不 相同。下面，我们再一次输入 select ename,sal,mgr from scott.emp where sal>3000 语句，继续跟踪文 件，如下所示。 PARSING IN CURSOR #2 len=50 dep=0 uid=0 oct=3 lid=0 tim=82689398915 hv=269262689503 ad='22da97d8' select ename,sal,mgr from scott.emp where sal>3000 END OF STMT PARSE #2:c=0,e=126,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=826893989026 EXEC #2:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=82689399144 FETCH #2:c=0,e=132,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=2,tim=826893993261 FETCH #2:c=0,e=19,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=2,tim=82689399790 STAT #2 id=1 cnt=1 pid=0 pos=1 obj=51151 op='TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=119 us)' 从跟踪文件的输出看出，参数 mis=0 说明此时没有硬分析。 26.5.3 调整 CURSOR_SHARING 参数 该参数默认值为 EXACT，意思是只有正文完全相同的 SQL 语句才可以重用，该值也是参数 CURSOR_SHARING 的默认值，如下例所示。 例子 26-53 查看参数 CURSOR_SHARING 的默认值 SQL> show parameter cursor_sharing; NAME TYPE VALUE ------------------------------------ ----------- --------- cursor_sharing string EXACT 如果缺少绑定变量，而系统中有大量只有字面值不同的 SQL 语句，此时可以通过更改参数 CURSOR_SHARING 的 值 为 FORCE 来 缓 解 SQL 语句的硬解析 开 销 ，如果更改参数 CURSOR_SHARING 的值为 FORCE，则强制共享使用只有字面值不同的 SQL 语句。下面我们修 改参数值。 例子 26-54 将参数 CURSOR_SHARING 的参数为 FORCE SQL> alter system set cursor_sharing=force scope=both; 系统已更改。 Oracle 11g R2 DBA 操作指南 560 26.5.4 设置共享池的大小 在 Oracle 10g 及以上版本中，共享池的大小由 SGA 自动调整，只要设置了 SGA_TARGET 参 数，其他 SGA 组件的大小都有 Oracle 自己调整。如果是自动的 SGA 调整，我们查询共享池的大 小，如下例所示。 例子 26-55 查询共享池的大小 SQL> show parameter shared_pool_size; NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------------ shared_pool_size big integer 0 此时输出参数 shared_pool_size 的值为 0，因为此时是自动的 SGA 管理，所以 Oracle 不会显示 该参数的值，在需要的时候会从 SGA 自动分配。 在安装数据库时，我们即可以选择自动 SGA 管理，此时需要告诉让 SGA 占有操作系统内存 的比例或设置相应的值，如图 26-3 所示。 图 26-3 设置 SGA 自动管理 在安装数据库时也可以手工设置 SGA 各内存组件的大小，如图 26-4 所示。 图 26-4 手工设置 SGA 组件大小 第 26 章 Oracle 数据库实例优化 561 对于手工设置 SGA 大小的方式，在数据库运行期间也可以使用 ALTER SYSTEM SET 指令修 改共享池的大小，如下例所示。 例子 26-56 手工设置共享池的大小 SQL> alter system set shared_pool_size=200M scope =both; 系统已更改。 在 SGA 自动调整的条件下，也可以使用上述指令手工设置共享池的大小。 修改后，我们查询修改结果，验证修改是否成功，如下例所示。 例子 26-57 查询共享池大小是否修改成功 SQL> show parameter shared_pool_size; NAME TYPE VALUE ------------------------------------------------------------------------- shared_pool_size big integer 200M 26.6 优化数据库高速缓存（DB Cache） 数据库高速缓存中存储了最近从数据文件读入的数据块信息或用户更改后需要写回数据库的 数据信息，此时这些没有提交给数据库的更改后的数据称为脏数据。当用户执行查询语句如 select * from dept 时，如果用户查询的数据块在数据库高速缓存中，Oracle 就不必从磁盘读取，而是直接 从数据库高速缓存中读取，显然从内存读取的速度要快很多，这些缓存的数据由 LRU 算法管理。 通过数据库高速缓存提高了用户的查询速度，减少了用户查询的响应时间。Oracle 使用 LRU 算法 管理库缓冲区，把最近没被使用的数据库从库高速缓冲中删除，为其他的查询数据块保留空间。所 以应该尽量设置数据库高速缓存要大些，这样就为用户提供更多的可查询缓存数据，但是 SGA 的 大小是受限的，毕竟还有其他内存组件需要空间，过大的数据库高速缓存也是不可取的，本节我们 讨论如何优化数据库高速缓存。 26.6.1 调整数据库缓冲区大小 数据库缓冲区存储用户读取或要修改的数据，设置合适的缓冲区大小可以提高缓冲区的命中 率，提高用户数据的响应时间。如下例所示，计算数据库缓冲区命中率之前需要知道几个缓冲区值。 例子 26-58 查询相关缓冲区缓存数据 SQL> select name,value 2 from v$sysstat 3 where name in ('db block gets from cache', 4 'consistent gets from cache', 5 'physical reads cache'); Oracle 11g R2 DBA 操作指南 562 NAME VALUE ---------------------------------------------------------------- ---------- db block gets from cache 47486 consistent gets from cache 329934 physical reads cache 9813 我们使用数据字典视图 v$sysstat 查询了 3 个与计算数据库缓冲区命中率相关的缓冲区缓存数 据，下面详细分析一下与这 3 个缓存区相关的 3 个术语：  db block gets：DB 块获取，在数据库高速缓冲区中如果存在被更改的数据，而此数据在 其他用户访问时已经提交，也就是用户访问的数据在数据库缓冲区中是最新的版本，这样 的数据块读称为 db block gets，即 DB 块获取。  consistent gets：一致获取，在数据库高速缓冲区中如果存在被更改的数据，而此数据在其 他用户访问时还没有提交，也就是用户访问的数据在数据库缓冲区中是脏数据，这样的数 据不会被访问，此时用户只能使用回滚段中的记录，这样的数据块读称为 consistent gets， 即一致获取。  physical reads：物理读，在数据库高速缓冲区中没有用户要访问的数据，需要从磁盘读取 该数据块，这样的数据读取称为 physical reads，即物理读。 DB 块获取和一致获取都是逻辑读，在计算数据库高速缓冲区的命中率时，需要使用物理读和 逻辑读的比值作为参考。即，命中率=1-物理读/逻辑读。 逻辑读= db block gets from cache + consistent gets from cache= 377420。 物理读= 9813。 所以此时数据库高速缓冲区命中率为 p = 1-physical reads cache/( db block gets from cache + consistent gets from cache) = 1-9813/377420=0.973999788. 显然这样的库缓冲区的命中率是不错的。 命中率并不能说明此时的数据库性能就一定很好，还需要具体分析等待事件，如果此时的 命中率很高，但是系统存在大量的等待事件，如数据文件离散度等，可能存在大量的全表 扫描，就需要进一步分析等待事件和相关的 SQL 语句。 如果对自己的应用环境非常熟悉，可以采用手工设置数据库高速缓冲区的尺寸，此时在数据 库系统的典型运行阶段查询命中率，反复调整直到命中率满足要求。如下例所示，修改库高速缓冲 区大小。 例子 26-59 修改库高速缓冲区大小 SQL> alter system set db_cache_size=192M; 系统已更改。 第 26 章 Oracle 数据库实例优化 563 26.6.2 使用缓冲池 如果用户访问使用了多个表，并且这些表都相当大，此时这些表只有部分保存在库高速缓冲 区中，库缓冲区中的大表数据的存在会降低数据库缓冲区的命中率，而如果这样的大表不是用户频 繁访问的对象，就是可以使用回收缓冲池（recycle pool）来存放，这样的数据库对象会被覆盖。 如果用户访问对多个小表全表扫描，且这样的行为频繁发生，就可以将这些小表保存在保留 池（keep pool）中，这样的数据库对象不会被覆盖掉。 对于其他既没有存储在回收缓冲池，也没有存储在保留池中的数据库对象，默认存储在数据 库缓冲区的默认池（default pool）中。 缓存表的尺寸不要超过缓冲区大小的 10%。 下面是将数据对象存储在缓冲池中的语法。 SQL> create index t_idx Storage (buffer_pool keep); SQL> alter table t_name storage(buffer_pool recycle); SQL> alter index t_idx storage(buffer_pool keep); 下面，我们给出一个例子说明如何创建一个索引的同时将该索引调入保留池。 例子 26-60 创建索引并将其缓存在保留池 SQL> create index scott_emp_ename 2 on scott.emp(ename) 3 storage (buffer_pool keep); 索引已创建。 为了验证保存结果，我们通过数据字典 USER_INDEXES 视图查看 BUFFER_POOL 列的值， 如果该值为 KEEP 则说明索引已经保存在保留池中。 例子 26-61 查询索引 SCOTT_EMP_ENAME 是否缓存在保留池中 SQL> col buffer_pool for a15 SQL> run 1 select index_name,table_name,buffer_pool 2 from user_indexes 3* where index_name='SCOTT_EMP_ENAME' INDEX_NAME TABLE_NAME BUFFER_POOL ---------------------------------------- --------------------------------------------------- ------------------------ SCOTT_EMP_ENAME EMP KEEP 从输出看出，索引 SCOTT_EMP_ENAME 已经缓存在保留池中，因为列 BUFFER_POOL 的值 为 KEEP。 如果是表，则可以通过数据字典 USER_TABLES 来查看，该视图的 CACHE 列说明该表是否被缓 存，BUFFER_POOL 列说明该表缓存的缓冲池名称。下面我们将表 EMP 放入回收池。如下所示。 Oracle 11g R2 DBA 操作指南 564 例子 26-62 将表 EMP 缓存在回收池 SQL> alter table scott.emp 2 storage (buffer_pool recycle); 表已更改。 此时，我们将 SCOTT 用户的 EMP 表存储在回收池中。下面通过数据字典 DBA_TABLES 查 看更改结果，如下例所示。 例子 26-63 查看表 EMP 的缓存信息 SQL> select table_name,cache,buffer_pool 2 from dba_tables 3 where table_name='EMP'; TABLE_NAME CACHE BUFFER_POOL ----------------------------------------------- ------------------------- EMP Y RECYCLE 列 CACHE 说明表 EMP 已经缓存在缓冲区中，而列 BUFFER_POOL 说明对应的缓冲区为回收 池 RECYCLE POOL。 我们也可以使用 CACHE 关键字，在创建一个表时，说明如果该表被缓存在默认池中，如下例 所示。 例子 26-64 使用 CACHE 关键字将表缓存在默认池中 SQL> create table test 2 as 3 select * 4 from scott.dept 5 cache; 表已创建。 下面，通过数据字典 USER_TABLES 查看表 TEST 的缓存结果，如下例所示。 例子 26-65 查看表 TEST 的缓存信息 SQL> select table_name,cache,buffer_pool 2 from user_tables 3 where table_name='TEST'; TABLE_NAME CACHE BUFFER_POOL --------------------------------------------------- ------------------ --------------- TEST N DEFAULT 此时，我们发现表 TEST 会使用 DEFAULT 缓冲池，但是目前没有被缓存，所以，还需要使 用如下指令启动表的缓存。 例子 26-66 将表 TEST 缓存在默认池中 SQL> alter table test cache ; 第 26 章 Oracle 数据库实例优化 565 表已更改。 我们继续通过数据字典 USER_TABLES 查看表 TEST 的缓存结果，如下例所示。 例子 26-67 查看表 TEST 的缓存信息 SQL> select table_name,cache,buffer_pool 2 from user_tables 3 where table_name='TEST'; TABLE_NAME CACHE BUFFER_POOL -------------------------------------- ------------------ --------------- TEST Y DEFAULT 此时的列 CACHE 的值为 Y，说明已经将该表加载到缓冲区，此缓冲区为 DEFAULT 缓冲区。 26.7 优化PGA内存 PGA 是程序全局区，该区域在数据库会话专有连接模式下是私有区域，服务器进程和用户进 程一一对应，用户进程单独使用 PGA，在共享服务器会话连接模式下，一个服务器进程对应多个 用户进程，PGA 是多个用户进程共享使用。PGA 主要用作大规模的数据排序，如用户输入的 SQL 语句有 GROUP BY 或 ORDER BY 子句等操作。 所谓的 PGA 优化就是将这些大规模的数据排序放在 PGA 中运行，而不是使用虚拟内存而占 用操作系统的交换区（SWAP AREA）。这样就要求合理地设置 PGA 的大小，从而使得排序区符 合系统需要，在 Oracle 9i 之前，该排序区由参数 SORT_AREA_SIZE 决定，通过手工设置该参数， 反复调整以满足系统需要。可以通过如下的方式查看该参数的值。 例子 26-68 查看 PGA 中排序区的大小 NAME TYPE VALUE --------------------------------------------------------------- ---------- sort_area_size integer 65536 如果处于大规模排序的需要，可以调整该参数为更大的值，但是问题是到底多少是最好的。 显然如果分配过多，则造成系统的其他组件的内存不足，如果过少又不能满足排序的需要。在 Oracle 9i 及以上版本（Oracle 10g、Oracle 11g）中支持 PGA 排序区的自动调整功能。但是该自动调整有 一个限制就是必须给出一个排序区的值，排序区会根据排序需要而在这个值内自动调整。该值由参 数 PGA_AGGREGATE_TARGET 决定，同时为了启动 PGA 排序区的自动管理必须设置参数 WORKAREA_SIZE_POLICY 为 AUTO，这也说明要设置 PGA 排序区的自动管理必须配置两个参 数，即 PGA_AGGREGATE_TARGET 和 WORKAREA_SIZE_POLICY。下面我们查看系统上这两 个参数的值。 例子 26-69 查看 PGA 的排序区是否为自动管理 SQL> col name for a20 SQL> col value for a30 SQL> select name,value,isdefault Oracle 11g R2 DBA 操作指南 566 2 from v$parameter 3* where name in ('pga_aggregate_target','workarea_size_policy') NAME VALUE ISDEFAULT ---------------------------------------------- --------------------- pga_aggregate_target 200278016 FALSE workarea_size_policy AUTO TRUE 从例 26-69 的输出可以看出参数 WORKAREA_SIZE_POLICY 是默认的参数，因为其 ISDEFAULT 的值为 TRUE，而参数 PGA_AGGREGATE_TARGET 是需要设置的，它不是系统的 默认参数，因为 ISDEFAULT 的值为 FALSE。 下面我们分析一下当前数据库的 PGA 状态，如下例所示。 例子 26-70 通过数据字典视图 v$pgastat 查询 PGA 状态信息 SQL> col name for a40 SQL> run 1 select * 2* from v$pgastat NAME VALUE UNIT ----------------------------------------------------- ------------ aggregate PGA target parameter 200278016 bytes aggregate PGA auto target 16797888 bytes global memory bound 40054784 bytes total PGA inuse 19604480 bytes total PGA allocated 42294272 bytes maximum PGA allocated 90947584 bytes total freeable PGA memory 0 bytes process count 21 max processes count 33 PGA memory freed back to OS 0 bytes total PGA used for auto workareas 0 bytes NAME VALUE UNIT --------------------------------------------- ---------- ------------ maximum PGA used for auto workareas 3831808 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed 101315584 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent recompute count (total) 869 已选择 19 行。 需要注意，以上输出中记录 PGA 状态的 3 个参数，即 aggregate PGA target parameter、aggregate PGA auto target 和 cache hit percentage。其含义如下所示。  aggregate PGA target parameter：用户设置的当前 PGA 的内存总和。  aggregate PGA auto target：Oracle 为 PGA 的排序区分配的内存大小，显然其值不能超过 第 26 章 Oracle 数据库实例优化 567 PGA 内存的总和。  cache hit percentage：说明排序区在 PGA 的排序区完成的比例，100 percent 表示全部排序 都在 PGA 的排序区内完成，所以 Oracle 自动分配的排序区的尺寸是合理的。 我们可以使用以下方式监控 PGA 的排序区是否合理，其目的是观察参数 cache hit percentage 的值，如 果 是 100 percent 则认为 Oracle 根据系统状态设置的 PGA 的排序区是合理，否则就需要增 加参数 PGA_AGGREGATE_TARGET 的值，以增加为 PGA 的排序区添加内存的需要。如下例所 示，监控 PGA 的排序区。 例子 26-71 查看在 PGA 的排序区进行排序的百分比 SQL> select * 2 from v$pgastat 3 where name like 'cache%'; NAME VALUE UNIT ---------------------------------------------------- ---------- --------- cache hit percentage 100 percent 显然其中的 VALUE 值为 100，所以当前的排序行为都在 PGA 的排序区内完成。如果出现部 分排序不在 PGA 的排序区完成，即 VALUE 的值小于 100，则需要考虑适当增加 PGA 的内存总和。 如下例所示，通过设置参数 PGA_AGGREGATE_TARGET 来调整 PGA 的内存总和。 例子 26-72 调整 PGA 的内存大小 SQL> alter system set pga_aggregate_target = 76M; 系统已更改。 我们再查看修改结果，如下所示。 例子 26-73 查看参数 PGA_AGGREGATE_TARGET 的修改结果 SQL> show parameter pga NAME TYPE VALUE ---------------------------------------- ------------------- ------------ pga_aggregate_target big integer 76M 我们再次查看此时 PGA 的状态信息，看 Oracle 是为 PGA 的排序区分配了多少内存，如下例 所示。 例子 26-74 查看 PGA 的大小以及 PGA 中排序区的大小 SQL> select * 2 from v$pgastat 3 where name in ('aggregate PGA target parameter', 4 'aggregate PGA auto target'); NAME VALUE UNIT -------------------------------------------- ---------------- ----------- aggregate PGA target parameter 78435456 bytes Oracle 11g R2 DBA 操作指南 568 aggregate PGA auto target 22379217 bytes 我们看 到此时的 PGA 的 总 和为 76MB ，同时自动分配给 PGA 的 排序区的 尺寸为 213.424805MB，可见随着 PGA 的总内存的增加 Oracle 会自动增加其为排序区的分配的内存容量。 读者也可以通过数据字典视图 v$pga_target_advice 获得 PGA 的排序区进行排序行为更详细的 信息。 例子 26-75 查询 PGA 的排序区排序的详细信息 SQL> select pga_target_for_estimate/(1024*1024) as estd_target, 2 estd_pga_cache_hit_percentage ,estd_overalloc_count 3 from v$pga_target_advice; ESTD_TARGET ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT ------------------------------------------------------------------------- 32 100 0 64 100 0 17 100 0 192 100 0 76 100 0 307.199219 100 0 358.399414 100 0 409.599609 100 0 460.799805 100 0 512 100 0 768 100 0 ESTD_TARGET ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT ------------------------------------------------------------------------- 1024 100 0 1536 100 0 2048 100 0 已选择 14 行。 从以上输出看出，Oracle 给出了一系列的对 PGA 的估计值，并且给出了每个估计值状态下排 序在 PGA 的排序区完成的比率，由参数 ESTD_PGA_CACHE_HIT_PERCENTAGE 决定。由于笔 者的计算机上没有任何排序行为，所以对于 PGA 的每一个估计值所有的排序都在 PGA 的内存中完 成。如果在生产数据库系统上，参数 ESTD_PGA_CACHE_HIT_PERCENTAGE 的值一般不会都是 100，这样可以根据这个参数的提示，来确定手动设置的 PGA 内存总和是否合适，合适的含义是要 求所有的排序行为都在 PGA 的内存中完成，这样通过内存中排序减少了由于排序内存不足而造成 的 I/O 开销。 26.8 本章小结 Oracle 实例是一个非常重要的概念，它包含一组 Oracle 数据库相关的内存组件和一些后台进 程。在实例优化中，我们先介绍了将程序或者数据常驻内存，这对于用户经常使用的程序如存储过 第 26 章 Oracle 数据库实例优化 569 程或函数、用户频繁访问的表等是十分有效的。 Oracle 实例的内存组件包括数据库高速缓存、共享池、重做日志缓冲区、PGA，以及大池和 Java 池。本章我们讨论了共享池的优化、重做日志缓冲区的优化、数据库高速缓存的优化，以及 PGA 的优化。无论是哪个组件的优化，我们都首先需要理解这些组件的作用，然后学会如何调整 这些组件的参数，在介绍如何优化时，我们也按照这样的逻辑来设计，希望读者学习时要时时回忆 该组件的作用，这对于学习优化是十分重要的。