ORACLE性能优化.doc

ORACLE优化1 概述ORACLE数据库的优化是一门很大的学问，对于大数据量，访问频繁的系统，优化工作显的尤其重要。随着主机和数据库系统的发展，ORACLE数据库故障的可能性越来越低。尽管DBA手册上要求DBA每天检查系统日志、备份情况等，实际上很少有DBA能做到这一点，至少我本人就没有做到_。在这种情况下，DBA最可能遇到性能问题，比如用户反映操作执行很慢，经常出现等待等。由于ORACLE系统的灵活性、复杂性、性能问题的原因多样性以及ORACLE数据库的动态特性，优化成为ORACLE数据库管理中最困难的领域。我主要从优化的概念及手段方面讲一下如何使用工具对数据库系统进行优化。1.1 优化方向根据DBA手册，优化调整主要集中在以下几个方面：l 设计调整应用的设计是ORACLE性能中最重要的因素。l SQL调整因为对数据库的访问是通过SQL来实现的，所以这部分的调整最花时间。l 实例调整调整PGA、SGA等初始化参数。l 对象调整对表及索引的存储参数，如：pctfree、pctuse、freelists等。这些参数能对性能参数比较大的影响。l 服务器、网络及磁盘调整如果出现诸如CPU忙、内存交换频繁、磁盘I/O瓶颈等情况，我们只能检查服务器、网络及磁盘配置。下面我们看看DBA在这些方面所能做的工作：在设计阶段，DBA应该在需要分析文档出来后参与设计，和设计人员一起形成设计文档。在这个阶段，DBA就应该了解表之间的关系及各个功能的数据流向，避免设计不合理而导致出现复杂SQL的情况。在应用设施阶段，DBA应该根据应用的数据量，响应需要，容灾需求等提出数据库服务器的配置要求。在数据库服务器确定后，根据配置对其进行内存和存储方面的规划并安装实施。而操作系统的一些内核参数由相关厂商的系统工程师设置。其他的一些调整等数据库正式运行后使用SQL或工具对其进行调整，这也是DBA调整的主要内容，我们下面要讲的就是这些内容。1.2 调整方法在早期，DBA通过查询ORACLE提供的很多V$类视图来发现性能问题并进行调整，这需要DBA对ORACLE有很深入的了解；之后，ORACLE推出了集成在OEM中的优化包，可以通过可视化界面对数据库性能进行监控、优化；另外，ORACLE还提供了一个强大的工具STATSPACK，可以通过对一段时间内数据库活动信息的统计，找出数据库的性能瓶颈并对数据库的发展进行预测。我以后将从SQL优化、使用OEM中的优化包对数据库进行优化、使用STATSPACK对数据库进行优化这三方面进行讲解。使用V$类视图进行优化的方法大家可参考OCP培训文档，这里就不再重复。1.3 题外话写到这里我忍不住想讲点题外话，希望能对大家有所帮助。大家现在应该都了解了ORACLE技术的复杂性，一名合格的DBA必须学习了解很多知识，包括操作系统、存储方面。也许大家通过不断的努力，以后会成为一名优秀的DBA。但我们毕竟是做技术的人员，也就是说，在企业的管理上，我们最多能做的，对管理者来说，只能是参考的意见，决策并不在于我们。而很多优秀的技术人员最容易犯的一个毛病就是“持才傲物”，认为只要自己技术好，天下就是我的。但这样的人往往会在工作过程中碰壁。打个比方来说，DBA对公司将用什么服务器，用什么数据库做后台，有多少控制的能力呢？公司管理层可能有自己的考虑，如决定所有服务器向某一个公司购买，可以得到更大的折扣，减少技术上面打交到的支持厂商等等，我们只能接受公司的选择。聪明的做法是把各种配置的服务器可能会引起的问题事先在报告中提出，形成文档提交领导，做到有椐可查。这样将来即使出现问题也没有责任，反而由于你的预见而显示了自身的价值。如以前我规划的一个系统，用户要求高可用性及高安全性，我建议使用HP的双机及磁盘阵列。关于磁盘阵列的推荐中，我提供了两种方案：RAID 0+1和RAID 5，说明了两者均能保证数据的安全性（不会丢失数据），但发生磁盘损坏时使用RAID 5会造成整个系统的性能大大降低，故推荐RAID 0+1。但用户出于成本考虑选择了RAID 5，后由于操作不当使磁盘损坏，造成业务系统运行缓慢达3小时（硬盘恢复时间）。但由于事先说明，用户并没有抱怨我，而是后悔没有听我的建议。另一个建议是要让领导了解你的工作，这样他才能知道你的价值，对你的发展也会很有利。相对于设计、开发来说，DBA并不是一个容易出成果的岗位。如设计人员完成系统设计、开发人员开发完一个模块，这些工作领导都能看得到，而DBA的工作没有什么阶段性成果。有些领导并不是搞技术出生，或对DBA工作并不了解。如我以前的一个领导，认为DBA的工作就是保证数据库不出问题，觉得无关紧要。如何让领导了解你的工作呢？记工作日志很重要，把你处理问题过程及产生的效果都记录下来，记得要有数据。如使用OEM中的优化包查到一个SQL语句磁盘读取量大（如80M/次），分析发现执行了全表扫描，建立相关索引后磁盘读取量大大降低（如10M/次），执行时间由3分钟变为5秒。这些文档一来可以备以后查验（说不定几年后就可以整理出本书_），二来可以在工作报告中提交给领导（当然不指望他们能全看懂，但你的工作效果还是能看到的），以体现你的价值。2 SQL性能优化根据前面的描述，除了设计之外，SQL优化对数据库性能影响最大。我们首先讲一下SQL优化的原则。2.1 ORACLE优化器Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划。2.1.1 优化器的优化方式Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。B、CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。我们要明了,不一定走索引就是优的,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。2.1.2 优化器的优化模式(Optermizer Mode)在Oracle9i 之前的版本里，优化模式包括Rule,Choose,First rows,All rows这四种：RULE:不用多说,即走基于规则的方式。CHOOSE:这是我们应关注的,默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。FIRST_ROWS:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从而减少了响应时间。FIRST_ROWS优化的一个缺点是，这一算法没有指定数据行检索的范围。ALL_ROWS:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。在Oracle9i 之前的版本里，FIRST_ROWS优化是内部规则和代价的混合体，而Oracle9i 的FIRST_ROWS优化则完全是基于代价的。Oracle9i扩展了原来的FIRST_ROWS优化概念，增加优化模式如下：FIRST_ROWS_1、FIRST_ROWS_10、FIRST_ROWS_100、FIRST_ROWS_1000FIRST_ROWS_n优化会告诉查询优化器，让它选择一个能够把响应时间减到最小的查询执行计划，以首先产生查询结果的前n行。尽管Oracle宣称FIRST_ROWS_n 优化会让查询更快，但是要记住的是，Oracle9i CBO所做的一切只考虑了对前几行的访问，而牺牲了查询的代价。2.1.3 设定优化模式我们可以在系统级、会话级或语句级三个层次设置优化模式。2.1.3.1 系统级如果使用SPFILE，通过alter system set optimizer_mode=;来设定。如果是PFILE，直接修改初始化参数。此级别设定后，如果未在会话级或语句级设定优化模式，则使用此模式。2.1.3.2 会话级通过ALTER SESSION SET OPTIMIZER_MODE=; 来设定。此级别设定后，如果未在语句级设定优化模式，则此会话执行的所有查询均使用此模式。2.1.3.3 语句级仅用于查询语句，设置次语句的优化模式。当然也可以在子查询中使用，方法为在SELECT后加上/*+ RULE */，例：select /*+first_rows_10*/ * from emp_check;2.2 AUTOTRACE功能在这里介绍一下AUTOTRACE功能，它显示了ORACLE是怎么执行SQL的，是一个很好的工具，主要用于SQL调优。实现步骤：1.用SYS用户执行$ORACLE_HOME/sqlplus/admin/plustrce.sql，建立角色PLUSTRACE，并把此角色权限授予需要使用AUTOTRACE功能的用户。2.用要使用AUTOTRACE功能的用户执行$ORACLE_HOME/rdbms/admin/utlxplan.sql，创建计划表。用户在使用SQLPLUS时只要输入set autotrace on或set autotrace traceonly就可以看到SQL的执行计划了。我们可以用Oracle的AUTOTRACE功能看一下执行计划：SQLset autotrace traceonlySQLset timing onSELECT EMP_NAME 职员名称,DECODE(CHECK_LEVEL,S,优秀,A,一般,C,差) 考核结果FROM EMP_CHECK,EMP_RECWHERE EMP_REC.EMP_NO=EMP_CHECK.EMP_NO(+)AND CHECK_YEAR(+)=2004 AND CHECK_NUM(+)=1;已用时间: 00: 00: 00.01Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS (OUTER) 2 1 TABLE ACCESS (FULL) OF EMP_REC 3 1 TABLE ACCESS (BY INDEX ROWID) OF EMP_CHECK 4 3 INDEX (UNIQUE SCAN) OF PK_EMP_CHECK (UNIQUE)Statistics- 0 recursive calls 0 db block gets 13 consistent gets 0 physical reads 0 redo size 861 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed在这里我们可以看到SQL的执行情况，从而判断我们是否建立的合适的索引以及优化器是否使用了索引。2.3 使用索引索引 ( Index ) 是常见的数据库对象，建立索引的目的是为了提高记录的检索速度。它的设置好坏、使用是否得当，极大地影响数据库应用程序和 Database的性能。虽然有许多资料讲索引的用法， DBA 和 Developer 们也经常与它打交道，但还是有不少的人对它存在误解，因此我针对使用中的常见问题，做一些讲解。什么情况下会使用到索引？一般来说，对于单列索引，只要SELECT、DELETE、UPDATE语句的WHERE条件中有此列，就会使用此索引。对于多列索引，如职员考核表中的主键（EMP_NO、CHECK_YEAR、CHECK_NUM三个字段联合索引），只有当WHERE条件中包含索引中的前一个或几个列时才会用到索引。如EMP_NO条件，EMP_NO、CHECK_YEAR组合条件，EMP_NO、CHECK_YEAR、CHECK_NUM组合条件。而仅有CHECK_YEAR、CHECK_NUM条件，没有EMP_NO条件是不会使用索引的。因为此索引是先根据EMP_NO排序，EMP_NO相同的再根据CHECK_YEAR排序，EMP_NO、CHECK_YEAR相同的再根据CHECK_NUM排序。什么情况下应该为表建立索引？一般来说，满足下列条件的应该建立索引：列经常被用在WHERE条件中或联接条件中；列的数据比较分散，即重复值不多；列包含大量的空值；几个列经常一起用在WHERE条件或联接条件中（联合索引）；大部分的检索只返回大表中的小部分记录（2%-5%）。什么情况下不应该为表建立索引？不应该建立索引的情况有：表的数据量不大；列很少用在查询条件中；大部分的检索都返回大量的数据；表的UPFATE操作很频繁。但索引并非总是最佳选择，如果发现 Oracle 在有索引的情况下，没有使用索引，这并不是 Oracle 的优化器出错。在有些情况下， Oracle 确实会选择全表扫描（ Full Table Scan ） , 而非索引扫描（ Index Scan ）。这些情况通常有：1. 表未做统计, 或者统计陈旧，导致 Oracle 判断失误。 2. 根据该表拥有的记录数和数据块数，实际上全表扫描要比索引扫描更快。对第 1 种情况，最常见的例子，是以下这句 sql 语句：select count(*) from mytable;在未作统计之前，它使用全表扫描，需要读取6000多个数据块（一个数据块是 8k）,做了统计之后，使用的是FAST FULL SCAN只需要读取450个数据块。但是，统计太旧，也会导致 Oracle不使用索引。第2种情况就要复杂得多。一般概念上都认为索引比表快，比较难以理解什么情况下全表扫描要比索引扫描快。为了讲清楚这个问题，这里先介绍一下 Oracle 在评估使用索引的代价（ cost ）时两个重要的数据：CF(Clustering factor) 和 FF(Filtering factor)。假设有表mytables，有32万行数据，其主键myid的最小值是1，最大值是409654。CF: 所谓 CF,通俗地讲，就是读入的索引块和数据块的比。我们知道，ORACLE是以块为单位对数据进行读写的。CF受到索引中数据的排列方式影响，通常在索引刚建立时，CF比较小；在表经过大量的插入、修改后，CF就会越来越大。如SQL：Select * from mytables where myid between 5 and 10;开始满足条件的myid为5，7，10，其索引叶节点原来存放在1个块中，对应到数据块为2个，则CF值为0.5；在插入myid为6后，索引叶节点变为存放在2个块中，对应数据块变为3个，则CF值为0.67；如果再删除myid为10的记录，则索引叶节点仍为2个，而数据块也减少为2个，则CF值为1。为了便于理解，看下面的三张图：所以对应事务操作频繁的表，它的CF会一直变大，为了避免索引的失效，DBA应该定期重建这些表的索引（可以使用自动功能）。语句：ALTER INDEX 索引名 REBUILD;如果某个sql语句以前一直使用某索引，较长时间后不再使用，一种可能就是 CF 已经变得太大，需要重新整理该索引了。 FF: 所谓 FF, 就是该 sql 语句返回的结果集占总的数据量的百分比。FF是Oracle根据统计所做的估计。如果ORACLE估算出使用索引读的数据块数比全表扫描还要多的话，Oracle会执行全表扫描。考虑以下 sql 语句： Select * from mytables where myid=1; 和 Select * from mytables where myid=400000;这两句看似差不多的sql语句，对Oracle而言，却有巨大的差别。因为前者的FF是100% ，而后者的FF可能只有1% 。如果它的CF比较小，则Oracle会选择索引作为优化方式。索引的设置也很重要，我们看如下的SQL：select count(1) from mytabs where coid=130000 and issuedate = to_date (2001-07-20, yyyy-mm-dd);一开始，我们有两个单列索引：I_mytabs1(coid), I_mytabs2(issuedate), 下面是执行情况：COUNT(1) - 6427Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=384 Card=1 Bytes=11) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (BY INDEX ROWID) OF MYTABS (Cost=384 Card=126 Bytes=1386) 3 2 INDEX (RANGE SCAN) OF I_MYTABS2 (NON-UNIQUE) (Cost=11 Card=126) Statistics - .5054 consistent gets 2206 physical reads .可以看到，它读取了 7000 个数据块来获得所查询的 6000 多行。 现在，去掉这两个单列索引，增加一个复合索引 I_mytabs_test ( coid, issuedate), 重新执行，结果如下： COUNT(1) - 6436Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF I_MYTABS_TEST (NON-UNIQUE) (Cost=3 Card=126 Bytes=1386) Statistics - 283 consistent gets 76 physical reads 可以看到，这次只读取了 300 个数据块。 7000 块对 300 块，这就是在这个例子中，单列索引与复合索引的代价之比。这个例子提示我们， 在许多情况下，单列索引不如复合索引有效率。 可以说，在索引的设置问题上，其实有许多工作可以做。正确地设置索引，需要对应用进行总体的分析。我们还应尽量熟悉各种操作符对 Oracle 是否使用索引的影响。这里我只讲哪些操作或者操作符会显式（ explicitly ）地阻止 Oracle 使用索引。以下是一些基本规则：1.如果 f1 和 f2 是同一个表的两个字段，则 f1f2, f1=f22.f1 is null, f1 is not null, f1 not in, f1 !=, f1 like %pattern% ; 3.Not exist 4.某些情况下，f1 in 也会不用索引； 对于这些操作，别无办法，只有尽量避免。但是， Oracle 是否真正使用索引，使用索引是否真正有效，还是必须进行实地的测验。合理的做法是，对所写的复杂的 sql, 在将它写入应用程序之前，先在产品数据库上做一次 explain。explain会获得 Oracle 对该sql的解析（ plan ）,可以明确地看到 Oracle 是如何优化该 sql 的。 如果经常做 explain, 就会发现，喜爱写复杂的sql并不是个好习惯，因为过分复杂的sql其解析计划往往不尽如人意。事实上，如果库表结构设计的合理，一般情况下我们很少需要写复杂SQL。将复杂的sql拆开，有时候会极大地提高效率，因为能获得很好的优化。当然这已经是题外话了。2.4 SQL优化方式2.4.1 选择合理的表名顺序该方法只在基于规则的优化器中有效，基于代价时，ORACLE会根据表的物理大小,索引的状态,然后选用花费最低的执行路径.。ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表将被最先处理。在FROM子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表（放在最后）。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表，交叉表是指那个被其他表所引用的表。例：EMP表描述了LOCATION表和CATEGORY表的交集.SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN 将比下列SQL更有效率SELECT * FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 20002.4.2 WHERE子句中的连接顺序ORACLE采用自下而上的顺序解析WHERE子句。ORACLE总会在能使用索引的时候使用索引（除非表特别小），但如果有多个索引可供使用时，可能会因为顺序的不同导致效率的不同。考虑下面的SQL语句：SELECT .FROM EMP_RECWHERE EMAIL=liu.yuzhouand DEPT_NO=010201;如果在（DEPT_NO，EMAIL）上有联合索引，且DEPT_NO上有索引，则使用联合索引；如果在DEPT_NO和EMAIL都有索引，但EMAIL上是唯一性索引，则使用EMAIL上的索引；如果在DEPT_NO和EMAIL都有索引，且都为非唯一性索引，则先根据EMAIL检索记录，再根据DEPT_NO检索记录，然后取它们的交集。所以我们在写SQL语句时，就应该考虑索引的使用情况。如果无法使用索引，ORACLE会执行全表扫描，我们应将限制性强的条件放在后面。如我们知道一个部门大约有500人，而一个EMAIL大约对应5人，则我们应该将EMAIL=liu.yuzhou的条件放在后面。这样可以避免部分记录的二次比对。2.4.3 避免使用SELECT *当你想在SELECT子句中列出所有的COLUMN时，使用SELECT *是一个方便的方法。不幸的是，这是一个低效的方法。实际上，ORACLE在解析的过程中，会将* 依次转换成所有的列名，这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。2.4.4 删除重复记录最高效的删除重复记录方法 ( 因为使用了ROWID)DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID)FROM EMP XWHERE X.EMP_NO = E.EMP_NO);2.4.5 减少对表的查询例1： SELECT TAB_NAME FROM TABLESWHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604)ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNSWHERE VERSION = 604) 可改编为：SELECT TAB_NAME FROM TABLESWHERE (TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER)FROM TAB_COLUMNSWHERE VERSION = 604)例2：Update多个列的例子UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;可改编为：UPDATE EMP SET (EMP_CAT, SAL_RANGE)= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;2.4.6 使用存储函数提高效率我在PLSQL及相关对象(下)中已经讲过，这里不再重复。2.4.7 使用表的别名当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。Column歧义指的是由于SQL中不同的表具有相同的Column名，当SQL语句中出现这个Column时，SQL解析器无法判断这个Column的归属。2.4.8 用EXISTS替代IN在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接.在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。例：SELECT * FROM EMP (基础表)WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPTWHERE LOC = MELB) 可改编为: SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND EXISTS (SELECT X FROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB)2.4.9 用NOT EXISTS替代NOT IN在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。例： SELECT FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT=A); 改编方法一：高效SELECT FROM EMP A , DEPT BWHERE A.DEPT_NO = B.DEPT_NO(+) AND B.DEPT_CAT A;改编方法二：最高效SELECT FROM EMP EWHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A);2.4.10 用表连接替换EXISTS通常来说 , 采用表连接的方式比EXISTS更有效率 SELECT ENAME FROM EMP EWHERE EXISTS (SELECT X FROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);更高效的写法是： SELECT ENAME FROM DEPT D, EMP EWHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = A;但是很多情况下我们无法将EXISTS改编为连接，如DEPT_NO不唯一。2.4.11 用EXISTS替换DISTINCT当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换 。EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。例：找出有职员的部门SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP EWHERE D.DEPT_NO = E.DEPT_NO；可改编为: SELECT DEPT_NO,DEPT_NAMEFROM DEPT DWHERE EXISTS ( SELECT XFROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)；2.4.12 用索引提高效率索引是表的一个概念部分，用来提高检索数据的效率。通常，通过索引查询数据比全表扫描要快。同样在联结多个表时使用索引也可以提高效率。 另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。使用索引时也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4、5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。ORACLE对索引有两种访问模式：索引唯一扫描 ( INDEX UNIQUE SCAN)索引范围查询(INDEX RANGE SCAN)适用于两种情况: 1）基于一个范围的检索 2）基于非唯一性索引的检索在前面的课程中我们已经讲了一些使用索引的情况，下面再补充几种：当WHERE子句中有多个索引列, 且包含非“=“号时，ORACLE会放弃使用非“=“号的索引：例：DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引. SELECT ENAME FROM EMPWHERE DEPTNO 20 AND EMP_CAT = A; 这里只有EMP_CAT索引被用到,然后所有的记录将逐条与DEPTNO条件进行比较. 执行路径如下: TABLE ACCESS BY ROWID ON EMPINDEX RANGE SCAN ON CAT_IDX 这是因为 DEPTNO 20 的条件可能会检索出大量记录，而EMP_CAT = A 可能只检索出少量记录。合并操作不如上面的方式划算。当WHERE子句中有多个索引列, 且都为非“=“号时，ORACLE将只使用一个索引：例：DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引. SELECT ENAME FROM EMPWHERE DEPTNO 20 AND EMP_CAT A;这里, ORACLE只会用到其中一个索引（用哪个视优化模式、统计信息而定），执行路径如下（假使使用DEPTNO上的索引）：TABLE ACCESS BY ROWID ON EMPINDEX RANGE SCAN ON DEPT_IDX2.4.13 强制索引失效如果两个或以上索引具有相同的等级,而我们只想使用其中的一个(通过它,检索出的记录数量少)，我们可以使用下面的方法：SELECT ENAME FROM EMPWHERE EMPNO = 7935AND DEPTNO = 10AND EMP_TYPE=A;如果我们只想用到EMPNO上的索引（相对另外两个条件记录数量很少，做合并不划算），则可改编为：SELECT ENAME FROM EMPWHERE EMPNO = 7935AND DEPTNO + 0 = 10 /*DEPTNO上的索引将不会使用*/AND EMP_TYPE | = A /*EMP_TYPE上的索引将不会使用*/;同样，如果我们想使用某个列上的索引，则不能对此列做运算，例：SELECT FROM DEPTWHERE SAL * 12 25000; 不能使用SAL列上的索引，可改编为：SELECT FROM DEPT WHERE SAL 25000/12;2.4.14 用=替代如果DEPTNO上有一个索引，则：SELECT * FROM EMPWHERE DEPTNO =4;比下面的语句更有效率：SELECT * FROM EMPWHERE DEPTNO 3;两者的区别在于, 前者将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。2.4.15 用UNION替换OR通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描.。注意, 以上规则只针对多个索引列有效. 如果有列没有被索引, 查询效率可能会因为没有选择OR而降低。在下面的例子中, LOC_ID 和REGION上都建有索引，则:SELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE LOC_ID = 10UNIONSELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE REGION = “MELBOURNE”;比下面的语句效率高SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”;2.4.16 IN和OR下面两个查询等价： SELECT. FROM LOCATION WHERE LOC_ID = 10OR LOC_ID = 20;SELECTFROM LOCATION WHERE LOC_IN IN (10,20);在9i后，ORACLE可以根据优化模式选择合并方式或直接IN检索方式，我们会在演示中看到。2.4.17 避免在索引列上使用IS NULL和IS NOT NULL对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空。则记录存在于索引中。因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。下面的例子不使用索引： SELECT FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 可以改编如下（如果为字符，可根据情况写为a）： SELECT FROM DEPARTMENT WHERE DEPT_CODE =0;2.4.18 用UNION-ALL 替换UNION当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序，并将重复记录过滤掉。如果用UNION ALL替代UNION，这样排序就不是必要了，效率就会因此得到提高。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。因此还是要从业务需求分析使用UNION ALL的可行性。2.4.19 其他需注意的问题!= 将不使用索引。索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。基于成本的优化器(CBO, Cost-Based Optimizer)对索引的选择性进行判断来决定索引的使用是否能提高效率. 如果索引有很高的选择性, 那就是说对于每个不重复的索引键值,只对应数量很少的记录.比如, 表中共有100条记录而其中有80个不重复的索引键值. 这个索引的选择性就是80/100 = 0.8 . 选择性越高, 通过索引键值检索出的记录就越少. 如果索引的选择性很低, 检索数据就需要大量的索引范围查询操作和ROWID 访问表的操作. 也许会比全表扫描的效率更低. 带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。例如，一个UNION查询，其中每个查询都带有GROUP BY子句，GROUP BY会触发嵌入排序(NESTED SORT) ；这样，每个查询需要执行一次排序，然后在执行UNION时，又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行。嵌入的排序的深度会大大影响查询的效率。通常，带有UNION，MINUS，INTERSECT的SQL语句都可以用其他方式重写。3 OEM优化包OEM优化包提供了一系列的高级工具，用来调节数据库的性能。优化包在OEM界面的左下方，点开后如下图：主要包含Index Tuning Wizard、Oracle Expert、Outline Management、Reorg Wizard、SQL Analyze、Tablespace Map几个部分。3.1 SQL AnalyzeSQL Analyze 具有以下功能：提供TopSQL以标识消耗资源最多的语句。提供对过去在数据库中已运行的SQL语句，即SQL历史记录的访问。在不同优化模式下执行SQL语句并显示解释计划和统计信息以便于比较。自动检查SQL语句是否违背基本SQL设计法则，然后生成能纠正该问题的可选SQL。提供与SQL性能相关的初始化参数的便捷修改。使用SQL优化向导自动优化SQL语句。在资料档案库中保存SQL语句、执行计划和性能统计信息以便将来使用。3.1.1 初始化参数点击OEM主界面上的SQL Analyze，进入如下界面：这是一个浏览器形式的界面，左边顶层列出了可以管理的数据库，每个数据库打开后有三项：初始化参数、TopSQL和SQL历史记录。这里列出的参数会影响到内存和磁盘性能，分为两类：例程参数和会话参数。其中例程参数不能修改，而会话参数可以通过双击来动态修改，以便测试参数对性能的影响。各参数的含义我就不详细讲了，大家可参考OEM帮助文件。3.1.2 查看TopSQL如果是第一次启动SQL Analyze，则会自动出现此界面：这是TopSQL的显示选项窗口，如果已经使用过，则可以通过点击菜单中的“TopSQL-选项”弹出此窗口以选择显示设置。从“排序依据”下拉框选择根据那种资源消耗情况来进行统计。TopSQL 将根据此选项以降序列出包含在数据块缓冲池中的 SQL 语句。这些SQL信息存放在 V$SQLAREA 系统视图中。各常用选项的含义如下：磁盘读取数：表示此语句所有执行次数的磁盘读取量。缓冲区高速缓存命中率：(缓冲区获取 - 磁盘读取)/缓冲区获取，从低到高。缓冲区获取数：表示此语句所有执行次数的缓冲区获取量，是 CPU 利用率的度量单位。每次执行的磁盘读取数：表示此语句每次执行的磁盘读取量每次执行的缓冲区获取数：表示此语句每次执行的缓冲区获取量。每次执行的语法分析调用数：每次执行时对 SQL 语句进行语法分析的次数。理想情况下，对一条 SQL 语句应进行一次语法分析并执行多次，但某些前端应用程序在每次执行时都会重新进行语法分析。比率越接近 0 越好。比率大于或等于 1 表示进行了不必要的语法分析调用。每一行的缓冲区获取数：缓冲区获取数/已处理行数，越大说明得到数据的代价越大。排序次数：表示此语句所有执行次数的排序次数。已处理的行数：SQL语句执行后返回的总行数。语法分析调用：表示此语句所有执行次数的分析调用次数。执行次数：SQL语句执行次数。在“计数”框中选择想显示的SQL行数。在附加过滤器中，当你只对TopSQL的某个子集 (例如 UPDATE 语句) 感兴趣时，在“仅包含”字段中输入感兴趣的文本，SQL Analyze将只显示那些包含指定文本的TopSQL语句。显示递归 S