网志栈编辑推荐
Oracle泰斗、性能调整大师理查德?尼米克力作，国内Oracle一线调优专家精心翻译，涵盖11gR2版数据库和Exadata等**的热门话题，助你成为**的Oracle调优专家
；
内容简介
在Oracle认证大师Richard Niemiec的帮助下，系统地识别和排除数据库的性能问题：《Oracle Database 11g R2性能调整与优化》一书详细介绍了*的监控、故障排除和优化的方法，取自真实世界的案例研究和*实践遍及全书，从中您可以学会如何找出和解决瓶颈问题、如何配置存储设备、如何有效运行查询以及如何开发无错误的 SQL和PL/SQL代码。由Oracle出版社出版的这部书中还介绍了有关测试、报表和安全功能等方面的改进。
主要内容
● 正确运用 Oracle 11g R2数据库的索引和分区技术
● 运用V$视图和X$表来紧急处理数据库问题
● 使用Oracle Exadata和Oracle Exalogic弹性云
● 运用Oracle RAT来分布工作负载
● 有效地管理磁盘驱动器、磁盘阵列和内存
作者简介
Richard Niemiec是全球知名的IT专家，是Oracle ACE总监和全世界最早获得“Oracle认证大师”荣誉的六泰斗之一。
在过去的30年里，他为弘扬Oracle技术而积极发表演说，并屡屡被评为*演讲人：Oracle合作者/全球用户大会*演讲人(6次)，中西部用户大会*演讲人(10次)和Oracle全球大会*演讲人。
过去的25年间，他为很多名列财富500强的客户设计了系统架构并实施了优化，这些客户中包括ACT、玛氏公司、麦当劳、诺基亚、Navteq(MapQuest)、密西根大学、AT&；T和百事可乐。他既可以开展教学又可以提供咨询，在执行方向、数据库管理和架构、性能优化、项目管理和技术训练等方面的经验尤为丰富。
目录
第1章 11g R1 &； R2新特性介绍(针对DBA和开发者)
1.1 轮到Exadata出场了！
1.2 高级压缩
1.3 自动诊断信息库(Automatic Diagnostic Repository，ADR)
1.4 自动共享内存管理(Automatic Shared Memory Management，ASMM)的改进
1.5 ASM的改进
1.5.1 从ASM首选镜像读取
1.5.2 滚动升级/打补丁
1.5.3 更快的重新平衡
1.5.4 ASM磁盘组兼容性
1.5.5 ASMCMD命令扩展
1.6 自动SQL优化
1.7 数据卫士的增强
1.7.1 快照备用(Snapshot Standby)
免费在线读
2.1 索引基本概念
当访问表中数据时，Oracle提供了两种方式：从表中读取所有行(即全表扫描)，或者通过ROWID一次读取一行。当访问大数据量表中的少量行时，您可能想使用索引。例如，如果只访问大数据量表中5%的行，并且可以使用索引标识需要读取的数据块，这样花费的I/O较少。如果没有使用索引，就要读取表中所有的数据块。
索引对性能改进的程度取决于数据的选择性以及数据在表的数据块中的分布方式。如果数据选择性很好，那么表中将只有很少的行匹配一个索引值(例如护照号码)。Oracle将能够快速查询索引，找到匹配索引值的ROWID，从而快速查到表中相应的少量数据块。如果数据选择性不好(例如国家名)，那么对一个值索引可能返回许多ROWID，导致从表中查询许多不连续的数据块。
如果数据选择性很好，但是相关的行在表中的物理存储位置并不互相靠近，这种情况下，索引带来的好处就会大打折扣。如果匹配索引值的数据分散在表的多个数据块中，就必须从表中把每个数据块都选出来以得到需要的查询结果。在一些情况下，您会发现当数据分散在表的多个数据块中时，最好是不使用索引，而是选择全表扫描。执行全表扫描时，Oracle使用多块读取以快速扫描表。基于索引的读是单块读，因此在使用索引时的目标是减少完成查询所需的单块读的数量。
通过对Oracle一些可用选项的利用，比如分区、并行DML、并行查询以及调整db_file_multiblock_read_count进行更大的I/O操作，全表扫描和索引查找之间的平衡点正发生着改变。硬件更为快速，可以在磁盘的高速缓存中缓存更多的信息，内存也变得更为廉价。与此同时，Oracle已经增强的索引特性，包括了跳跃式扫描索引和其他内部操作用来减少返回数据所需的时间。
要诀
当升级Oracle版本时，确保测试应用程序中的查询以确定查询的执行路径是否仍然使用在升级之前使用的索引。看看执行计划是否改变，这种改变的效果是变得更好还是变得更差。
一般来说，索引能提高查询的性能。SELECT语句、UPDATE和DELETE命令的WHERE子句的性能(当访问的行较少时)可以借助索引获得提高。一般来说，增加索引会降低INSERT语句的性能(因为需要同时对表和索引进行操作)。索引列上的UPDATE操作将会比没有加索引慢很多，因为数据库必须管理对表和索引的改动。此外，大量行的DELETE操作将会由于表中存在索引而变慢。
一条删除表中一半数据的DELETE语句同时需要删除所有这些行对应的索引(这种情况是非常耗时的)。通常说来，在表上加一个索引都会使该表上INSERT操作的执行时间变成原来的三倍；再加一个索引就会再慢一倍；然而，一个由两列组成的索引并不比只有一个列的索引差很多。索引列的UPDATE和DELETE操作同样也会变慢。您需要平衡索引带来的查询性能的提升和对数据修改性能的影响。
通过查询DBA_INDEXES视图可获得一个表上所有索引的清单，也可以通过访问USER_ INDEXES视图查询当前方案(schema)的索引。查询ALL_INDEXES视图可以查看能够访问的所有表的索引。
例如，在一张随着Oracle产品一起提供的演示表emp上创建了两个索引。您可以对任何版本的演示表测试下面的查询：
create index emp_id1 on emp(empno, ename, deptno);
create index emp_id2 on emp (sal);
当执行这些命令时，数据库将在emp表上创建两个单独的索引。每个索引将包含emp表中的特定值以及匹配这些值的行的ROWID。如果需要查找sal值为1000的emp表中的记录，优化器就会使用emp_id2索引查找该值，并找到相关的ROWID，接着使用该ROWID在表中查找对应的行。
下面对USER_INDEXES的查询显示了emp表上新建的索引：
select table_name, index_name
from user_indexes
where table_name = EMP ;
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
EMP EMP_ID1
EMP EMP_ID2
输出显示有两个索引，但是没有显示每个索引中包含的列。为了获得当前用户的表中被索引的列的信息，可查询USER_IND_COLUMNS视图；DBA 可以通过访问DBA_IND_COLUMNS视图检索所有方案中被索引的列，而通过ALL_IND_COLUMNS视图则可以查看当前用户有权看到的所有表的被索引列。下面的例子就是通过USER_IND_COLUMNS视图获得当前用户的表中被索引的列的信息：
column index_name format a12
column column_name format a8
column table_name format a8
select table_name, index_name, column_name, column_position
from user_ind_columns
order by table_name, index_name, column_position;
TABLE_NA INDEX_NAME COLUMN_N COLUMN_POSITION
-------- ------------ -------- ---------------
EMP EMP_ID1 EMPNO 1
EMP EMP_ID1 ENAME 2
EMP EMP_ID1 DEPTNO 3
EMP EMP_ID2 SAL 1
emp表中有两个索引。第一个emp_id1是一个组合索引，它对empno、ename和deptno这几列进行索引；第二个索引emp_id2只对sal列进行索引。程序清单中的COLUMN_POSITION字段显示了组合索引中列的顺序，在本例中，按照empno、ename和deptno的顺序。
要诀
查询DBA_INDEXES和DBA_IND_COLUMNS可以检索到一个给定表的索引列表。对于当前用户的方案(schema)中的索引信息，只能使用USER_INDEXES和USER_IND_COLUMNS来查看。
2.2 不可视索引
决定对哪些列建立索引往往是很难的。主键是被自动索引的，外键也应该被索引，接下来怎么办？更困难的是决定删除某个可能不好的索引。每插入一条记录时，所有索引都必须更新。如果一个被索引的列被更新，该索引也要被更新。在不引起用户查询出现一系列全表扫描或笛卡儿连接的情况下，决定哪些索引需要被删除是很困难的，尤其是在需要调优的第三方应用程序代码不能改变的情况下。不可视索引是该问题一个可能的解决方案。Oracle允许您关闭索引(使其不可见)，但是索引上的维护工作还会继续(在任何DML操作(INSERT/UPDATE/DELETE))期间，因为您可能需要快速重新打开它。你可以通过下面的方法使索引可见或不可见：
ALTER INDEX idx1 INVISIBLE;
ALTER INDEX idx1 VISIBLE;
CREATE INDEX . . . INVISIBLE;
下面的查询显示在表emp的deptno列上新建一个不可视索引，在接下来的查询中，我们看不到那个索引：
create index dept_rich_inv_idx on dept_rich(deptno) invisible;
Index created.
select count(*)
from dept_rich
where deptno = 30;(索引不可见)
COUNT(*)
--------------
512
Execution Plan
----------------------------------------------------------
Plan hash value: 3024595593
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL | DEPT_RICH | 512 | 1024 | 4 (0) | 0:00:01 |
--------------------------------------------------------------------------------
仍然可以使用提示来强制这个索引的使用，在11gR2以前，可以使用INDEX提示，在11gR2中，可以使用USE_INVISIBLE_INDEXES提示，或者把初始化参数OPTIMIZER_USE_INVISIBLE_ INDEXES设置成true(详见附录A)：
select /*+ USE_INVISIBLE_INDEXES */ count(*)
from dept_rich
where deptno = 30;(通过提示强制使用索引)
COUNT(*)
--------
512
Execution Plan
----------------------------------------------------------
Plan hash value: 3699452051
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 1 (0) | 00:00:01|
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX RANGE SCAN| DEPT_RICH_INV_IDX | 512 | 1024 | 1 (0) | 00:00:01|
-----------------------------------------------------------------------------------
如果把索引设成可见，就不需要INDEX提示了：
alter index dept_rich_inv_idx visible;
Index altered.
select count(*)
from dept_rich
where deptno = 30;(索引可见)
COUNT(*)
--------
512
Execution Plan
----------------------------------------------------------
Plan hash value: 3699452051
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 1 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | INDEX RANGE SCAN| DEPT_RICH_INV_IDX | 512 | 1024 | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------------
在不将其变成不可见的前提下，也可以使用NO_INDEX提示来关闭一个索引，看看除此之外是否还使用其他索引(或者不使用索引)。换句话说，使用除了NO_INDEX中指定的索引之外的任何索引，下面是一个例子：
select /*+ no_index(dept_rich dept_rich_inv_idx) */ count(*)
from dept_rich
where deptno = 30; (强制不使用带提示的索引)
COUNT(*)
--------
512
Execution Plan
----------------------------------------------------------
Plan hash value: 3024595593
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL | DEPT_RICH | 512 | 1024 | 4 (0) | 0:00:01 |
--------------------------------------------------------------------------------
可以随时将这个索引设成不可见：
alter index dept_rich_inv_idx invisible;
Index altered.
可以通过USER_INDEXES或DBA_INDEXES视图来查看索引的可见性：
select index_name, visibility
from dba_indexes (或使用USER_INDEXES)
where index_name = DEPT_RICH_INV_IDX;
INDEX_NAME VISIBILITY
-------------------------------- ------------------
DEPT_RICH_INV_IDX INVISIBLE
要诀
通过使用不可视索引，您可以通过使索引不可见的方法，临时“关闭”它们来查看查询在没有它们时的性能。由于不可视索引依然被维护，因此如果有需要的话，可以轻易地把它们“打开”，也就是使它们可见。
2.3 组合索引
当某个索引包含多个列时，我们称这个索引为“组合索引”或“复合索引”(composite index)。Oracle 9i引入的索引跳跃式扫描增加了优化器在使用组合索引时的选择，所以您在选择索引中的列顺序时应该谨慎。一般来说，索引的第一列应该是最有可能在WHERE 子句中使用的列，并且也是索引中最具选择性的列。
在引入跳跃式扫描功能之前，只有当索引中的前导列也出现在WHERE子句中时，查询才能使用索引。考虑如下程序清单中的示例，其中表emp有一个在列empno、ename和deptno上的组合索引。注意第一部分是empno，第二部分则是ename，第三部分是deptno。如果没有跳跃式扫描功能，除非在WHERE子句中使用前导列(empno)，否则Oracle一般不会使用这个索引。
select job, empno
from emp
where ename = RICH;
因为ename不是索引的前导列，优化器可能不会选择使用该索引。随着在Oracle 9i中引入跳跃式扫描功能，即使在WHERE子句中没有指定empno值，优化器也可能会选择使用该索引。另外，优化器也可能会选择索引快速全扫描(Index Fast Full Scan)或全表扫描。
如果在WHERE 子句中使用索引的第三列，也会产生相同的情况：
select job, empno
from emp
where deptno = 30;
该例中，WHERE子句指定了索引中第三列的值，优化器可能选择使用索引跳跃式扫描、索引快速全扫描或全表扫描。创建这个索引时，您就为数据库在执行查询时提供更多的选择，从而有希望改进整体的性能。注意，用户的代码没有改变，但是优化器可以识别该索引，并且根据每种可行方法的预期成本作出决定。
在下面的示例中，使用了索引的一部分。前导列empno用作WHERE 子句中的限制条件，以便Oracle可以使用该索引。
select job, empno
from emp
where empno = RICH;
两种最常见的索引扫描方式是唯一扫描(Unique Scan)和范围扫描(Range Scan)。在唯一扫描中，数据库知道索引包含的每一个值都是唯一的。在范围扫描中，数据库将根据查询条件从索引中返回多个符合条件的值。在该例中，emp_id1和emp_id2索引都不是唯一索引。Oracle在返回数据的时候将使用范围扫描。使用CREATE UNIQUE INDEX命令可以创建唯一索引。
在创建主键约束或唯一性约束时，Oracle 将基于指定的列自动创建唯一索引(除非使用DISABLE子句创建约束)。如果创建多列的主键，Oracle 将创建组合索引，其中的列的排列顺序和创建主键时指定的列的顺序一致。
类似于在列emp_id1 和emp_id2上的索引，Oracle 具备通过每行的ROWID访问单行数据的能力。ROWID 其实就是直接指向单行的物理位置的指针。
要诀
避免将Oracle的ROWID硬编码到特定代码里。因为在不同版本的Oracle中，ROWID结构会有所不同，而且在将来的版本里可能还会有所改变。建议不要对ROWID进行硬编码。
Oracle Database 11gR2 性能调整与优化 pdf下载声明
本pdf资料下载仅供个人学习和研究使用，不能用于商业用途，请在下载后24小时内删除。如果喜欢，请购买正版
