一. 序言
数据库的性能问题，一直以来都是困扰各个事业部的大问题。在性能问题中SQL语句的编写又是重中之重。因此，为提高各个事业部相应产品的执行效率，提高软件的性能，实现从功能要求到性能要求的转变，特编写此专题文章。
二. SQL的优化器执行分析
在ORACLE RDBMS SERVER软件的内部，对于SQL语句的执行有一个优化器（OPTIMIZER）对SQL语句的执行进行优化。在我们使用后面介绍的工具对SQL的执行路径进行查看的时候，系统显示出来的是由优化器给出的执行路径的解释方案，如果对优化器的解释方案不了解的话，就无法针对出现的问题进行SQL语句的调整。现把ORACLE8提供的优化器的执行解释方案公布如下。
1. 如何看SQL解释方案
Execution Plan
----------------------------------------------------------
1 SELECT STATEMENT Optimizer=CHOOSE (Cost=94Card=1)
2 SORT (AGGREGATE)
3 COUNT (STOPKEY)
4 INDEX (FULL SCAN) OF 'PK_TBI_TM' (UNIQUE)(Cost=94 Car
d=27164)
（图1）
图1为ORACLE对语句“select count(*) from tbi_tmwhere rownum<10”给出的一个执行的解释方案，那我们该如何看这个方案呢？我们在看这个执行方案的时候要遵循一个原则“由里到外，由高到低”，同时“由里到外”不能违反“由高到低”的原则。因此上述的语句的执行步骤顺序为：4、3、2；假设存在一个步骤5的话，而且它的层次同3一致，那么，上述语句的执行步骤顺序就会改为：4、3、5、2。在图1中还给出了执行了何种操作（例如：INDEX（FULL SCAN），COUNT (STOPKEY)等，具体的说明在下面进行解释），同时在最后还给出了执行的代价（COST）。
2. SQL解释方案介绍
2.1. 操作说明
系统中的全部操作可分为行操作或集（SET）操作。二者之间的比较可以对比如下：
行操作 | 集操作 |
一次执行一行 | 在行的结果集上执行 |
如果不涉及集操作将在FETCH级上操作 | 当光标打开时，在EXECUTE级上执行 |
在取最后一行之前，用户可以看到第一次的结果 | 用户不能看到第一次的结果，直到所有的行被取出和处理完 |
示例：全表扫描 | 示例：使用group by子句的全表扫描 |
对于行和集操作的分类如下(暂时列到ORACLE 8)：
行操作 | 集操作 |
And-equal,Bitmap,Concatenation,Count stopkey,Filter,For update,Index full scan,Index range scan,Index unique scan,Intersection,Merge join,Nested loops,Outer join,Projection,Sequence,Table access by rowid,Table access cluster,Table access full,Table access hash,Union,Union-all | Connect by,Count,Hash join,Minus,Remote, Sort aggregate,Sort group by,Sort join,Sort order by,Sort unique,View |
2.2. 具体操作解释
在上述的操作中有许多我们平时很少用到，因此就不一一介绍了，只介绍日常常用的一些操作：
2.2.1. 约定
Table_name | Column_name | Index_type |
Company | ||
Company_id | U_ind | |
City | Ind | |
State | Ind | |
Parent_company_id | Ind | |
Competitor | ||
Company_id | U_ind | |
Product_id | U_ind | |
Sales | ||
Company_id | U_ind | |
Period_id | U_ind |
2.2.2. AND-EQUAL
说明：用来合并由索引返回的值的排序列表。AND-EQUAL用于非唯一索引的合并和唯一索引的范围扫描。
例子：selectname,city,state from company where city=’Roanoke’ and state=’VA’
2.2.3. CONCATENATION
说明：用来执行结果集的unionall操作
例子：selectname,city,state from company where state=’TX’ and city in (‘Houston’,’Austin’,’Dallas’);
该例子最终可以该写成：selectname ,city,state from company where (state=’TX’ and city=’Houston’) or (state=’TX’and city=’Austin’ ) or (state=’TX’ and city=’Dallas’);
备注：有时候在语句比较复杂时，Oracle可能不会使用CONCATENATION操作，而是使用部分索引的范围扫描（Range Scan）。如果要强制使用CONCATENATION操作，就把语句写成最终的样子。
2.2.4. COUNT
说明：当使用伪列（Pseudo-column），并且指定RowNum的最大值时，执行Count（计数）。Count从它的子操作接收行并且增大RowNum的计数。
例子：selectname,state,rownum from company where city>’Roanoke’
2.2.5. COUNT STOPKEY
说明：当使用限定计数的RowNum伪列时，执行COUNT STOPKEY。它从先前的操作接收行并且增加计数，如果计数达到了临界值，就产生一个“NoMore Rows”条件，结束查询并把结果返回给用户。
例子：selectname,city,state,rownum from company where city>’Roanoke’ and rownum<10;
2.2.6. FILETER
说明：使用在当没有索引可以用来帮助评估时，FILTER执行一个where子句条件。当FILTER在一个解释方案里显示时，通常显示的是索引丢失或者存在的索引不能用的结果。
2.2.7. FOR UPDATE
说明：为所有能从select语句回复的行级别（row level）上加锁。
例子：select namefrom company where city>’Roanoke’ for update of name;
2.2.8. HASH JOIN
说明：它是行操作和集操作的混合。它在内存中创建其中一个表的位图，然后利用哈希（HASH）功能在第二个表里定位联结行，通过HASH JOIN（哈希联结）将表联结起来。
例子：selectcompany.name from company,sales where company.company_id=sales.company_id andsales.period_id=3 and sales.sales_total>1000;
注意：在这个例子中，sales表作为联结表被读到内存中，然后同company表中的记录进行逐行比较。能够使用到hash join的条件为在联结的表中，一张表比其他的联结表小的多，并且这些记录能够全部被读到内存中去，那么系统就会使用hash join而不是使用nested loops来进行连接。有时即使为联结提供了一个索引，hash join也许比nested loops联结跟可取。
2.2.9. INDEX RANGE SCAN
说明：它从索引里选择一定范围的值，索引既可以是唯一的也可以是复合的。当遇到下列条件时，使用它：
l 使用范围操作（如>or<）
l 使用between子句
l 使用有通配符的查找字符串（如：’b%’）
l 只使用复合索引的一部分
例子：selectname,city,state from company where city>’Roanoke’
注意：它的效率与两个因素有关：选择范围内的关键值的数目以及索引的条件，当关键数目越多，查找时间越长；分段越多，查找时间越长。
INDEX UNIQUE SCAN
说明：它从唯一索引中选择，是从已知字段里选择一行的最有效的方法。
例子：selectname,city,state from company where company_id=12345
2.2.10. MERGE JOIN
说明：它通过合并每个表中已排序的记录列表去联结数据表。它主要是针对大型批处理操作，但是对事务处理可能是无效的。当ORACLE实施联结而又不能使用索引时，就使用merge join。
例子：selectcompany.name from company ,sales where company.company_id+0=sales.company_id+0and sales.period_id=3 and sales.sales_total>1000
注意：需要值得注意的是它是针对集操作。对于它的执行计划，在执行完一个全表扫描之后还要进行一个sort join的排序操作，然后再进行merge join操作。
2.2.11. NESTED LOOPS
说明：在进行多表的联结时，如果有一个联结的列被索引过的时候，那么nested loops将起作用。
例子：selectcompany.name from company ,sales where company.company_id=sales.company_id andsales.period_id=3 and sales.sales_total>1000
注意：
l 在使用nested loops的时候，查询的驱动表的选择是很重要的，关于这点，这下面会作专题讨论。原则是如果是rule_based的情况下，驱动表将选择from语句的最后一个，如果是cost_based的情况下，驱动表的选择间考虑表的大小和索引的选择性。
l 在进行nested loops的操作时，首先作的是驱动表的全表扫描，然后才使用索引的扫描，然后才进行nestedloops操作。
2.2.12. OUTER JOIN
说明：它是nestedloops、hash join、merge join操作的一个选项。它死来自驱动表的行能够返回到调用查询，虽然在联结数据表里没有发现任何匹配的行。
例子：selectcompany.name from company ,sales where company.company_id=sales.company_id(+)and sales.period_id=3 and sales.sales_total>1000
2.2.13. SEQUENCE
说明：当通过nextval和currval来访问sequence时，使用sequence操作。
例子：selectseq_tbi_tm.nextval from dual;
2.2.14. SORT AGGREGATE
说明：每当对数据集的组操作功能在sql语句中出现但是没有groupby子句时，SORTAGGREGATE（聚集排序）就用来给结果排序和聚集。这些组函数包括：max、min、count、sum、avg
例子：selectsum(sales_total) from sales;
2.2.15. SORT GROUP BY
说明：在数据集中实现分组功能
例子：selectzip,count(*)from company group by zip;
2.2.16. SORT JOIN
说明：把用于MERGEJOIN操作的一个记录集排序，同mergejoin是同步出现的。
例子：selectcompany.name from company ,sales where company.company_id+0=sales.company_id+0and sales.period_id=3 and sales.sales_total>1000
2.2.17. SORT ORDER BY
说明：它用于给结果集排序，但是不去除重复记录。
例子：select namefrom company order by name;
2.2.18. SORT UNIQUE
说明：在由minus、intersection和union操作处理前，它用于给结果集排序并将重复记录去除。
例子：selectcompany_id from company minus select company_id from compeitor;
2.2.19. TABLE ACCESS BY ROWID
说明：基于所提供Rowid的操作，它从一个表返回一条记录。这是从一个表返回记录的最快的方法。
例子：select namefrom company where company_id=12345 and active_flag=’Y’;
2.2.20. TABLE ACCESS FULL
说明：当没有为行查找提供Rowid时，oracle将扫描给定表里的每一块，直到读完所有行。
例子：select*from company;
2.2.21. UNION
说明：它从两个或者跟多个查询结果返回行的单个集。为了执行这个操作，Oracle首先根据Select语句过滤所有的行，给它们排序，然后实行Union Merge。在Union操作期间，包含了Union all操作。
例子：selectcompany_id from company union select company_id from competitor;
注意：如果一个操作不考虑去除重复记录的话，请使用union all，它的执行效率比union高。
三. 编写高效的SQL：
在Oracle系统的性能提升过程中，SQL语句的优化的效果是最明显的。因此，编写高效的SQL语句对整个系统性能的提高是应用中效果最明显的，同时也是代价最低的。
一个高效的SQL语句基本上都是要利用到索引的，因此我们在谈SQL语句的同时也介绍一下索引。并且，任何语句都同回滚段都分不开，因此也会介绍一下回滚段的情况以及使用。
1. 什么是索引
1.1. 索引定义
索引本身也是一个数据库对象。对于在表的索引列上的每一值将包含一项（ENTRY），为行提供直接的快速存取。索引的存储参数的取值和使用同表的存储参数的使用一致。
1.2. 索引的组织存储形式
在Oracle系统中，索引的数据存储是以B+树的形式存在的。
需要值得注意是，在索引的数据项发生删除的时候，释放出来的空闲空间无法再次使用。
如果对索引数据发生频繁的update或者删除的时候，会导致索引失去平衡，或者导致索引的层次增加，会导致索引停滞（Index Stagnation）现象的出现。为解决该问题，可以考虑重建索引。重建索引可以使用Alter index ….Rebulid语句来实现，该功能可以使索引再次平衡，同时也可以对索引的存储地点以及存储策略进行修改。
从Oracle 8开始，索引的类型增加了两种，一为位图索引（Bitmap Index）主要针对重复值比较多的列而创建的索引，对它的逻辑操作的效果比较好；一为反向索引（Reverse Index）主要是为尽量保持索引的平衡而考虑创建，对该索引的查询只有使用“＝”才起作用，而对于范围内的查询（Range Scan）不起作用。
对于数据比较多的表创建索引的时候，从考虑效率角度出发，在创建索引的时候使用No Logging的参数来提高效率。该参数在创建索引的时候不产生重作日志，可以有效的提高效率，加快索引的产生。
1.3. 使用索引的优缺点
在下列情况下ORACLE可利用索引改进性能：按指定的索引列的值查找行，按索引列的顺序存取表。然而，索引虽然可以加快查询的速度，但是它可减慢INSERT、DELETE和UPDATE命令的速度，由于这些操作要影响索引列的值，ORACLE必须对索引数据和表数据进行维护。
1.4. 创建索引的限制
一个索引最多可包含16列（到Oracle 8开始为32列），索引项为每一列的数据值的连串，按指定的列的顺序连串。这一顺序对ORACLE如何使用该索引非常重要。ORACLE在一张表上可建立多个索引，索引的数目没有限制。但是应该注意增加索引会增加维护表所需的处理时间。建议只有当要索引的数据在表中所占的数据量，少于总数据量的15%时，使用索引才会提高查询的速度，否则对表的全表扫描的速度还比使用索引的速度快。
1.5. 创建索引遵循的原则
对于索引列的选择遵从下列的准则：
u 经常用于WHERE子句中使用的列考虑作索引的列。
u 经常用于SQL语句中连结表的列考虑作为索引的列。
u 在Cost_based的优化方式下，对于复合索引的主列的选择显的尤为重要。它的选择可以遵循以下原则：在Where条件中频繁使用的列为主列；对于数据最有选择性的列为主列。
u 一个索引列要具有好的选择性。一个索引的选择性（Selectivity）是对具有相同值的表中的百分比。一个索引的选择性好，就是很少行有相同值。可以通过用具有不同索引值的数目除表中的行数来决定索引的可选择性。可以用ANALYZE命令获取这些值。用这种方法计算的可选择性应该解释为百分比。
u 不要将具有很少不同值的列作为索引列。这样的列具有很差的选择性，并且不能优化性能，除非频繁选择的值比其他列值来更少出现。例如：在表中如果存在以YES或NO为值的列，那么尽量不要用该列作为索引列，因为用此列作为索引不会提高系统的性能，但是如果在表中YES的值很少出现，而在运用中又经常以YES值为查询，则以该列为索引可能会改进系统的性能。从Oracle 8开始可以考虑创建Bitmap Index来解决一些问题。
u 不要将频繁修改的列作为索引列。因为修改索引列的UPDATE语句和修改索引表的INSERT和DELETE语句将比没有索引要用更多的时间。这样的语句必须修改索引中的数据，还要修改表中的数据。
u 不要将只出现在带函数或操作符的WHERE子句中的列作为索引列。使用带索引列的函数（不是MIN或MAX函数）或操作符的WHERE语句并不使用索引的存取路径。
u 在大量并发INSERT、UPDATE和DELETE语句存取父表和子表的情况下，考虑对参考完整性约束的外部键作索引。也就是说对表的外键在可能的情况下创建索引以加快操作的速度，改进性能。因为如果对于一张表而言，如果在它的外键上没有索引的话，那么在对它操作的同时，ORACLE将对此外键对应的主表加一个表级锁，以保证数据的完整性和一致性。如果在外键上有索引的的话，那么它就可以通过索引找到主表的ROWID，只对主表加一个行级锁。
1.6. 如何选择单列索引还是复合索引
1.6.1. 索引选择性的测量
所谓索引选择性是指索引中有多少不同的值同表中记录数目的比值，不同的值越多，代表选择性越高，最高的选择性为1（例如唯一性索引）。
对于索引选择性的测量可以通过不同的方法来进行测量。一种是通过手工的方式作，一种是通过自动的方式来作。
自动方式来作的是通过对表进行Analyze之后，然后通过查看相应数据字典的值来计算。对索引的不同值可以通过USER_INDEXES.DISTINCT_KEYS查到，表的全部记录数可以通过USER_TABLES.NUM_ROWS查到。在创建索引之前，可能需要查看一下列的不同值的数目，它可以通过USER_TAB_COLUMNS.NUM_DISTINCT查到。
手工方式，就是对相应的索引列进行DISTINCT操作，然后得到不同值的数目。然后对表进行COUNT(*)函数操作，得到表的记录数目，把两者数据进行比较，就得到索引的选择性。
1.6.2. 单列索引和复合索引的选择
对于复合索引而言，主列（第一列）的选择性从很大程度上决定了复合索引的选择性。我们还是举前面的例子来说明如何选择索引的创建。假设我们在Where条件中经常用City=’xxx’and state=’xxx’为条件的话，这时有两种选择，一种是方案1，一种是方案3。假设复合索引同单独索引的选择性差不多的话，那么选择方案3就会有一个AND_EQUAL的操作，会导致比方案1多将近2/3的索引操作，因此需要考虑方案1。又假设在系统中有大量的基于State的Where语句操作，这时还是有两种方案一是方案2，一是方案3。此时如果基于State列上的索引的数据量不大的话，而且相应的附加开销不多的话，基于同上面相同的理由，可以选择方案2。不过如果单列索引同复合索引的选择性相差很大的话又另当别论了。
附：
方案 | 索引#1 | 索引#2 |
1 | City,state | None |
2 | City,state | State |
3 | City | State |
2. 编写利用索引的SQL语句
在日常的应用中，索引是否引用是关系到该SQL语句效率的一个非常关键的因素。在日常应用中应该注意到如果在SQL语句中出现下列语法，则系统无法利用到索引：
u COLUMN1>COLUMN2
u COLUMN1<COLUMN2
u COLUMN1>=COLUMN2
u COLUMN1<=COLUMN2
其中COLUMN1和COLUMN2在同一张表中。
u COLUMN IS NULL
u COLUMN IS NOTNULL
u COLUMN NOT IN
u COLUMN !=EXPR
u COLUMN LIKE‘%ANYTHING’
在上述条件中不管COLUMN上是否有建索引，SQL语句都不会利用索引。
u EXPR是一个表达式，它用运算符或函数操作在该列上，不管列上是否有索引，例如：EXPR*COLUMN=ANYTHING，则不能利用索引。
u NOT EXISTSSUBQUERY
u 不包含未被索引的列的任何条件
u 在LIKE表达式中如果模糊的列为数字或日期的话，那么该语句同样无法利用索引。
u 在ORACLE内部的本身的数据转换（DATA CONVERSION）将引起ORACLE索引列的不被使用。
在日常SQL语句使用时还应该注意使用索引的原则：
u 在使用复合索引时，应该注意是否有利用到该索引的前导部分（LeadingPosition），在使用该复合索引时只有使用到该索引的前导部分，该索引才能被利用，否则就根本无法发挥索引作用。例如：用户在一张表的三个列（X，Y，Y）张建立一复合索引，该复合索引的顺序为XYZ。在SQL语句的WHERE条件中使用XY，XYZ，XZ等都可利用到索引，但是如果使用YZ，Z等就无法利用到索引。如果对索引的所有列都有引用的话，则无所谓对字段位置的使用了。
u 对于复合索引，对于主列的操作是否使用索引同样受上面的条件限制。
u 在SQL语句中尽量避免不要使用不等或者NULL。
3. 回滚段的作用：介绍回滚段的原理，使用时要注意的事项，从它入手如何提高SQL的效率，使用固有的ORACLE提供的PACKAGE
具体内容详见相应的回滚段的专题介绍。
4. SQL语句HINT的说明
作为一个应用软件的设计者或者编程人员，应该比Oracle数据库的优化器更清楚数据的分布情况。特别是一个SQL语句有多种执行路径的时候，优化器的选择也许不是最优的，这时就可以通过在语句中使用Hint来指定优化器使用哪种的执行路径。
4.1. 在哪些语句中允许使用Hint
并不是任何场合下都允许使用Hint的，只有符合下列情况之一，才有可能使用Hint：
² 一个简单的SELECT,UPDATE或者DELETE语句
² 一个主查询或者一个复杂语句的子查询
² 一个复杂查询的一部分
4.2. Hint的书写格式说明
Hint的书写格式如下：
(delete,select,update) /*+Hint*/
或者
(delete,select,update) --+Hint
Hint的写法为在几个关键字之后使用SQL语句的注释符（/*Hint*/;--+Hint）来写Hint。此外，如果Hint书写有问题的话，Oracle并不会报错，而是继续使用优化器的执行路径来作。
4.3. Hint的分类使用说明
4.3.1. 优化方式选择
该类型的Hint等价于在Init参数中使用Optimizer_mode指定优化的方式或者等同对于某一个Session使用Alter session …set optimizer_mode的功能。
内容包括：All_rows,First_rows,Choose,Rule。
4.3.1.1. ALL_ROWS
语法格式为：/*+ALL_ROWS*/
该Hint显式的选择Cost_based的方式来优化SQL语句块。它从最小化总体的资源消耗的方面考虑优化，也就是达到整体的输出时间最少。
4.3.1.2. FIRST_ROWS
语法格式为：/*+FIRST_ROWS*/
该Hint显式的选择Cost_based的方式来优化SQL语句块。它从最佳反馈时间方面来考虑优化，也就是要求使第一条的记录返回时间最短。
该Hint是优化器有如下选择：
² 如果有一个索引可以使用的话，那么优化器将可能会使用索引代替全表扫描。
² 如果有一个索引可以使用的话，那么优化器将可能会在对于一个NESTEDLOOPS操作中关联到一个相关的INNER表时，使用NESTED LOOPS JOIN代替SORT-MERGE JOIN操作。
² 如果有一个索引可以使用的话，那么优化器将可能会使用它来在一个ORDERBY操作中避免产生一个SORT操作。
该Hint在什么情况下会被忽略：
² 在UPDATE或者DELETE语句中会被忽略
² 在下列的SELECT语句中该Hint也会被忽略：数据集操作（UNION,INTERSECT,MINUS,UNIONALL），GROUP BY子句，FOR UPDATE子句，GROUP函数，DISTINCT操作。由于上述操作都需要对全部的数据进行检索，同该Hint的作用（以最短的时间返回第一条记录）相冲突。
4.3.1.3. CHOOSE
语法格式为：/*+CHOOSE*/
该Hint使优化器在COST_BASED和RULE_BASED的优化模式中选择一种模式。如果在一个语句中关联到的表中有一个表有统计数据，那么优化器就选择COST_BASED的优化模式同时以最佳的输出时间为优化目的（等同于ALL_ROWS）。反之就使用RULE_BASED的优化模式。
4.3.1.4. RULE
语法格式为：/*+RULE*/
该Hint显式的指定优化器使用RULE_BASED模式。
4.3.2. 数据访问方式选择
该类型的Hint包括下列内容：FULL,ROWID,CLUSTER,HASH,HASH_AJ,INDEX,INDEX_ASC,INDEX_COMBINE,INDEX_DES,INDEX_FFS,MERGE_AJ,AND_EQUAL,USE_CONCAT
4.3.2.1. FULL
语法格式如下：/*+FULL(table_name)*/
该Hint显式的指定优化器对指定table_name进行全表扫描。
例子：select /*+full(a) Don’t use the index on ACCNO*/ accno,bal
from accounts a
where accno=xxxx;
从上述例子中我们可以对整个Hint的写法有一个更深刻的认识：“Don’t use the index onACCNO”为整个Hint的注释说明，由于在语句的From子句中对表起了一个别名，因此在Hint中的table_name就不能使用真正的表名来代替，而必须使用表的别名。
4.3.2.2. ROWID
语法格式如下：/*+ROWID(table_name)*/
该Hint显式的指定优化器对指定的table_name的访问通过Rowid来进行。
4.3.2.3. CLUSTER
语法格式如下：/*+CLUSTER(table_name)*/
该Hint显式的指定优化器对指定的table_name的访问通过Cluster Scan来完成。
4.3.2.4. HASH
语法格式如下：/*+HASH(table_name)*/
该Hint显式的指定优化器选择HashScan对一个指定的表进行访问。该操作只能对存储在Cluster上表起作用。
4.3.2.5. HASH_AJ
语法格式如下：/*+HASH_AJ*/
该Hint把一个Not in子查询转换成对一个指定表的hash anti_join访问。
4.3.2.6. HASH_SJ
语法格式如下：/*+HASH_SJ*/
该Hint把一个Exists子查询转换成对一个指定表的Hash semi_join的访问。
4.3.2.7. INDEX
语法格式如下：/*+INDEX(table_name index_name)*/
该Hint显式的选择对一个指定表的访问通过指定的索引来进行。
对于该Hint需要注意的几点是：
² 如果在Hint中指定了一个唯一的索引的话，那么优化器就会通过该索引来进行数据检索，而不会考虑全表扫描或者通过其他的索引来进行检索。
² 如果在Hint中指定了多个索引的话，那么优化器就会在通过对指定的索引列表进行依次比较，最终选定代价最低的索引来进行检索。优化器不会选择进行全表扫描也不会使用不在Hint中出现的索引列表中的索引。
² 如果在Hint中没有指定索引名字的话，优化器会通过对该表上全部的索引进行依次比较，然后最终选定最低的代价的索引进行检索。优化器不会考虑进行全表扫描。
4.3.2.8. INDEX_ASC
语法格式如下：/*+INDEX_ASC(table_name index_name)*/
该Hint显式的对一个指定的表通过索引进行检索。如果该语句通过索引进行范围检索的话，通过该Hint对该索引的值进行升序检索。此类提示对于基于索引的MIN函数比较有效，只需要作一次查询就可以了。
4.3.2.9. INDEX_COMBINE
语法格式如下：/*+INDEX_COMBINE(table_name index_name)*/
该Hint主要针对指定表上的bitmap_index。
4.3.2.10. INDEX_DESC
语法格式如下：/*+INDEX_DESC(table_name index_name)*/
该Hint的整体作用同INDEX_ASC类似，不过是由ASC改为DESC。此类提示对于基于索引的MAX函数比较有效，只需要作一次查询就可以了。
4.3.2.11. INDEX_FFS
语法格式如下：/*+INDEX_FFS(table_name index_name)*/
该Hint通过对Index的全索引扫描代替全表扫描。
4.3.2.12. MERGE_AJ
语法格式如下：/*+MERGE_AJ*/
该Hint把一个Not in子查询转换成对一个指定表的merge anti_join访问。
4.3.2.13. MERGE_SJ
语法结构如下：/*+MERGE_SJ*/
该Hint把一个Exists子查询转换成对一个指定表的merge semi_join的访问。
4.3.2.14. AND_EQUAL
语法结构如下：/*+AND_EQUAL(table_name index_name index_name…)*/
该Hint显式的选择一个执行计划，对在不同的单列上的索引的检索结果进行合并。在该Hint中对于index_name至少是2个，但是不能超过5个。
4.3.2.15. USE_CONCAT
语法结构如下：/*+USE_CONCAT*/
该Hint的作用为把一些OR语句操作强制转化为Union all的集操作。
4.3.3. 联结顺序
该类型的Hint内容包括：ORDERED,STAR。
4.3.3.1. ORDERED
语法结构如下：/*+ORDERED*/
该Hint使Oracle按照From子句中给出的表的顺序进行联结。
例子：select /*+ordered*/tab1.col1,tab2.col2,tab3.col3
from tab1,tab2,tab3
where tab1.col1=tab2.col1 andtabl2.col1=tab3.col1
在此例子中，由于添加了Ordered的Hint，Oracle是把TAB1和TAB2两张表进行Join，然后把Join的结果同TAB3进行再次关联。
4.3.3.2. STAR
语法结构如下：/*+STAR*/
该Hint主要是用在对于数据仓库这一类数据量比较大的查询。
4.3.4. JOIN操作
4.3.5. 并行操作
该类型的Hint内容包括：PARALLEL,NOPARALLEL,APPEND,NOAPPEND,PARALLEL_INDEX,NOPARALLEL_INDEX。
鉴于目前这方面的内容使用的比较少，在此次的文章中暂时不讨论这一类型的Hint的作用和使用。
4.3.6. 其他
该类型的Hint内容包括：CACHE,NOCACHE,MERGE,NO_MERGE,PUSH_JOIN_PRED,NO_PUSH_JOIN_PRED,PUSH_SUBQ,STAR_TRANSFORMATION。
4.3.6.1. CACHE
语法格式如下：/*+CACHE(table_name)*/
大家都知道Oracle对于在buffer中的数据是根据Lru的算法来进行查找的。并且在Lru队列中存在两端，一端为Lru，一端为Mru，对数据的查找和匹配是从Mru开始的。Cache的作用就是使数据Load到Mru上去，使Oracle能够在最短的时间内遍历要查抄的数据，能够在最短的时间内得到结果。该Hint能够覆盖在建表的时候的默认的Cache参数的选项的作用。对于该Hint适合用于对于比较小的表的全表扫描时使用。
例子：select /*+FULL(scott_emp) CACHE(scott_emp)*/ ename
FROM scott.scott_emp;
4.3.6.2. NOCACHE
语法结构如下：/*+NOCACHE(table_name)*/
该Hint的作用同CACHE的作用相反，它把数据Load到Lru队列的Lru一端。
对于其他的Hint由于日常使用的不多在这里就不一一介绍了，在日后的文章中有机会在介绍了。
5. 编写SQL语句的技巧
5.1. 基本原则
² 写相同的SQL语句，保证程序能够利用共享池的内容，加快程序的执行速度。
² 写能够利用索引并且能够符合基于代价的优化条件的SQL语句。
² 尽量利用PL/SQL的特性减少网络的传输。
² 把能够写在服务器端的程序尽可能的不要写在CLIENT端，以减少网络的传输和提高系统的编译速度。
² 良好的代码书写习惯
² 能够不使用SELECT *的尽量不要使用，除了是为了防止出现在增加字段的时候不需要修改程序外，也是为了保证性能。
5.2. 尽量避免全表扫描
在日常编写SQL语句时，除非迫不得已，不然尽量避免下列情况，因为下列情况会导致，系统进行全表扫描来查找数据。
1、该表上没有任何索引。
2、一个查询语句中没有任何限制条件，也就是没有Where子句。
3、对于索引的不适当使用也会导致全表扫描。关于该方面的内容在前面的章节中已经讨论过了。
4、在进行多张表的联结操作中，如果在from子句中有，但是在where子句中没有用到的表也会导致对该表的全表扫描。
5.3. 适当索引CONNECT BY操作
5.4. 限制对远程表的访问
5.5. 使用UNION ALL代替UNION
UNION ALL和UNION的操作的最大的区别在于，UNION操作为了保证数据的唯一性，把返回的数据集中的重复的数据去除了。因此，在UNION的执行步骤中包含了SORTUNIQUE的操作。从上个章节的介绍中，我们已经知道了，对于任何一个UNION操作都包含了UNIONALL的操作。并且，从两者的解释计划中，大家可以看到在进行多表的JOIN操作时，UNION是对相应的表进行全表扫描之后再进行UNION ALL的操作，然后进行SORT UNIQUE最后进行NESTED LOOPS，而对于UNION ALL则是先返回相应的小的数据集，然后再进行UNION ALL的操作。
因此，如果对于返回的数据集不强调数据的唯一性的话，请试用UNION ALL代替UNION。
5.6. 管理好多表之间的联结
5.7. 管理好包括视图的查询语句
5.8. 优化子查询
5.9. 不要在SQL查询中使用PL/SQL功能调用
ORACLE公司提供的PL/SQL有着通用SQL无法提供的功能，对简化程序的编写有着不可多得的有点，因此深为大家所喜欢。
举例来说，大家有时候可能想把数据中的小写数字转换成大写数字，于是，编写了一个函数，实现了该功能，然后在平时在SQL语句中调用该函数来实现数字转换。大大简化了程序的编写，而且也提高了软件的可读性。
不过，假如从软件的运行效率方面来考虑的话，这种在SQL语句中混合使用PL/SQL的方式是不提倡的。有一个使用数据表明，同样功能的语句，如果是SQL语句调用PL/SQL的方式的话，速度会比正常的慢44倍左右，我们日常中的数值可能会低于这个数字，不过也都有10倍以上。
因此，总结一句就是不要为了贪图编写方便，而在SQL查询中使用PL/SQL功能调用。
5.10. 在关键应用中使用OCI代替PL/SQL、PRO*C
Oracle提供了三种对数据库操作和管理的方式，那就是OCI、PRO*C、PL/SQL。在这三种方式中OCI能够更直接调用操作系统的API，因此能够更直接的到达系统的内核，所以速度比PRO*C、PL/SQL都快。并且，OCI比起其余另外两种方式在HA方面的性能表现的更好。因此，建议对于所有的会话关键型应用程序使用OCI。
5.11. 在OLAP应用中使用复合的KEYS/STAR查询
四. 日常SQL TUNING工具的使用
1. 基础准备工作
在Oracle的Rdbms系统中，对于系统的优化的选择有两种方式，一种是Cost_based（基于代价），一种是Rule_based（基于规则）。在我们现在的系统中大部分是使用Cost_based的优化策略。如果采用Cost_based的优化策略的话，因此它的一切工作都是以统计值为基础。因此，对于相应的对象必须进行分析（Analyze），这样在相应的系统视图中才有统计值，才能真正使用Cost_based的优化。
在下面介绍的工具中，都需要看一个东西，那就是执行计划。正如其他的Oracle系统数据一样，执行计划也是存储于表中，这张表就是Plan_table。在正常的时候，这张表是不存在，必须人工创建。创建它的方法如下：
在需要进行分析的用户下，执行UTLXPLAN.SQL。该脚本存放在$ORACLE_HOME/RDMBS/ADMIN目录下。对于，安装DEVELOPER/2000的用户可在\ORAWIN95\RDBMS73\ADMIN下找到。该脚本建立一张名为PLAN_TABLE的表。用户也可根据自己的需要，把该表的表名改为用户需要的名称。如果，该用户无建表权限，则可在某一用户下建立该表，通过该用户对其余用户进行授权。需要授的权限包括：SELECT，DELETE，INSERT等权限。
我们在日常编写应用程序的过程中，可以尝试着从以下几种方法来查看，自己使用的SQL语句的效率是否最优。
l EXPLAIN PLAN：一种最原始的查看SQL语句的执行计划的方法。
l SET AUTOTRACE：一种增强型的EXPLAIN PLAN，可以帮助大家省去许多烦琐的过程。
l TKPROF：一个应用级的跟踪工具，可以明确标明系统的CPU消耗时间，IO的情况以及语句的执行计划等信息。
2. EXPLAIN PLAN的使用
它的语法结构如下：explain plan set statementid='text' for statement
其中对于statementid为用户任意设置的值，而statement为今后在系统中要执行的语句，也就是当前要分析的语句。
经过EXPLAIN PLAN分析出来的结果存放在PLAN_TABLE中，想对分析出来的结果进行分析，就必须对PLAN_TABLE中的数据进行分析。一般情况下，用如下语句对分析出来的结果进行分析：
select id,parent_id,position,operation,options,object_name,object_type,costfrom plan_table;
3. SET AUTOTRACE 的使用
在ORACLE中提供了一更方便、更有效的数据存取路径解释方案，它就是在SQL*PLUS中设置AUTOTRACE ON，通过该方法，可以得到该语句的执行计划和相应的统计数据，这些数据包括(For Example)：
15 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
284 bytes sent via SQL*Net to client
244 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
要完成上述功能就必须先完成如下事情：以SYS用户登录，执行c:\orawin95\plus33\plustrce.sql通过该SQL语句创建PLUSTRACE这一ROLE，同时在SYS用户下把该ROLE授权给PUBLIC，使一般用户都可使用到该ROLE。
对于它有如下用法：set autotrace on(off/trace)
4. TKPROF的使用
1) 在使用TKPROF前要作的准备工作
针对不同环境下对不同的跟踪要求需作的准备工作不同：
u 对于需要进行分阶段跟踪的用户可通过如下操作进行跟踪，使本身处在受跟踪的环境下，该命令如下：alter session setsql_trace=true,如果想把该跟踪屏蔽，则命令如下：alter session set sql_trace=false或可以通过PACKAGE DBMS_SESSION.SET_SQL_TRACE(ACTION)
u 对于DBA用户如果想使某一用户的的处理受跟踪，使用的命令如下：dbms_system.set_sql_trace_in_session(sid,serial#,action);其中sid,serial通过V$SESSION这一VIEW得到数据， 写法如下:selectsid,serial# from v$session where username=‘要跟踪的用户名’，action为TRUE、FALSE，如果actio为TRUE则要进行跟踪，如果为FALSE则要取消跟踪。
u 如果想对在用的每一个用户都进行性能跟踪的话，那么进行如下处理，在INITSID_NAME.ORA中增加如下参数：
TIMED_STATISTICS:该项的值可设置为TRUE、FALSE，通过该参数可以启用在性能跟踪时的时间统计，平常系统在不作性能跟踪时，最好不要打开该参数，因为该参数会造成对低层操作的额外定时调用，会导致系统性能的部分下降。
MAX_DUMP_FILE_SIZE:设置系统TRACE文件的大小，该单位以操作系统的BLOCK（块）进行计算，默认为500。
USER_DUMP_DEST:决定系统的TRACE文件最终存储的路径。
SQL_TRACE:通过对该参数设置TRUE或FALSE使系统的跟踪可用或不可用。
2) 如何使用TKPROF
通过1）步骤产生的文件的命名规则如下，该文件以ORA打头、TRC为文件的扩展名。总的来讲，该TRACE文件的以下列形式构成:ORA_SPID.TRC。TKPROF的命令行格式如下：
tkproftrace_file_name output_file_name insert=sql_trace_file_name sys=yes/noexplain=username/password
其中，TRACE_FILE_NAME指通过跟踪产生的TRC文件，OUTPUT_FILE_NAME指TKPROF分析后产生的分析文件的名称。INSERT子句代表用户经过分析之后形成脚本，其中SQL_TRACE_FILE_NAME代表用户分析后形成的用户SQL脚本的文件名称。SYS=YES代表是对系统语句进行分析，SYS=NO代表不对系统语句进行分析。EXPLAIN子句代表是否对TRC文件中语句进行并列出相应语句的执行计划。
3) 如何对TKPROF产生的结果进行分析
对于每一个SQL语句，每一行相对应于三阶段，此三阶段为PARSE，EXECUTE，FETCH，此三阶段的分别含义为：
PARSE：此步骤将SQL语句翻译成一个执行计划。此步骤包括对正确的安全认可检查，以及表、列和其它要参考的对象的存在性的检查。
EXECUTE：此步骤为ORACLE对语句的实际执行。对于UPDATE，DELETE和INSERT语句，此步骤修改数据。对于SELECT语句，此步骤识别要选择的行。
FETCH：此步骤返回由查询返回的行。此步骤只对SELECT语句有效。
对于每一个分析出来的文件，在其开头部分都有如下一段一段提示：
count =number of times OCIprocedure was executed
cpu =cpu time in seconds executing
elapsed =elapsed time in seconds executing
disk =number of physical reds of buffers from disk
query =number of buffers gotten ofr consistent read
current =number of buffers gotten in currentmode(usually for update)
rows =number of rows processed by the fetch or execute call
上述的提示中提到的关键字是今后在对系统进行分析时，要用到的所有语法分析，所有执行和所有取数的组合统计，具体的中文含义如下：
² Count 一个语句被语法分析、执行和取数的次数。
² Cpu 对语句的所有语法分析，执行或取数调用的总CPU时间（以秒计）。
² Elapsed 对语句的所有语法分析，执行或取数调用的总耗费时间（以秒计）。
² Disk 对所有语法分析，执行或取数调用的，从磁盘数据文件上数据块物理读的总数。
² Query 对所有语法分析，执行或取数调用的，以一致方式检索的缓冲区总数，对查询来说，通常用一致性方式检索缓冲区。
² Current 以当前方式检索的缓冲区总数。对INSERT，UPDATE和DELETE语句通常用当前方式检索缓冲区。
Query和current的和是存取的缓冲区的总数。
² Rows 由SQL语句处理的总行数。对于SELECT语句而言，返回的行数出现在取数的步骤，对UPDATE、DELETE和INSERT语句而言，处理的行数出现在执行步骤。
一. 序言
数据库的性能问题，一直以来都是困扰各个事业部的大问题。在性能问题中SQL语句的编写又是重中之重。因此，为提高各个事业部相应产品的执行效率，提高软件的性能，实现从功能要求到性能要求的转变，特编写此专题文章。
二. SQL的优化器执行分析
在ORACLE RDBMS SERVER软件的内部，对于SQL语句的执行有一个优化器（OPTIMIZER）对SQL语句的执行进行优化。在我们使用后面介绍的工具对SQL的执行路径进行查看的时候，系统显示出来的是由优化器给出的执行路径的解释方案，如果对优化器的解释方案不了解的话，就无法针对出现的问题进行SQL语句的调整。现把ORACLE8提供的优化器的执行解释方案公布如下。
1. 如何看SQL解释方案
Execution Plan
----------------------------------------------------------
1 SELECT STATEMENT Optimizer=CHOOSE (Cost=94Card=1)
2 SORT (AGGREGATE)
3 COUNT (STOPKEY)
4 INDEX (FULL SCAN) OF 'PK_TBI_TM' (UNIQUE)(Cost=94 Car
d=27164)
（图1）
图1为ORACLE对语句“select count(*) from tbi_tmwhere rownum<10”给出的一个执行的解释方案，那我们该如何看这个方案呢？我们在看这个执行方案的时候要遵循一个原则“由里到外，由高到低”，同时“由里到外”不能违反“由高到低”的原则。因此上述的语句的执行步骤顺序为：4、3、2；假设存在一个步骤5的话，而且它的层次同3一致，那么，上述语句的执行步骤顺序就会改为：4、3、5、2。在图1中还给出了执行了何种操作（例如：INDEX（FULL SCAN），COUNT (STOPKEY)等，具体的说明在下面进行解释），同时在最后还给出了执行的代价（COST）。
2. SQL解释方案介绍
2.1. 操作说明
系统中的全部操作可分为行操作或集（SET）操作。二者之间的比较可以对比如下：
行操作 | 集操作 |
一次执行一行 | 在行的结果集上执行 |
如果不涉及集操作将在FETCH级上操作 | 当光标打开时，在EXECUTE级上执行 |
在取最后一行之前，用户可以看到第一次的结果 | 用户不能看到第一次的结果，直到所有的行被取出和处理完 |
示例：全表扫描 | 示例：使用group by子句的全表扫描 |
对于行和集操作的分类如下(暂时列到ORACLE 8)：
行操作 | 集操作 |
And-equal,Bitmap,Concatenation,Count stopkey,Filter,For update,Index full scan,Index range scan,Index unique scan,Intersection,Merge join,Nested loops,Outer join,Projection,Sequence,Table access by rowid,Table access cluster,Table access full,Table access hash,Union,Union-all | Connect by,Count,Hash join,Minus,Remote, Sort aggregate,Sort group by,Sort join,Sort order by,Sort unique,View |
2.2. 具体操作解释
在上述的操作中有许多我们平时很少用到，因此就不一一介绍了，只介绍日常常用的一些操作：
2.2.1. 约定
Table_name | Column_name | Index_type |
Company | ||
Company_id | U_ind | |
City | Ind | |
State | Ind | |
Parent_company_id | Ind | |
Competitor | ||
Company_id | U_ind | |
Product_id | U_ind | |
Sales | ||
Company_id | U_ind | |
Period_id | U_ind |
2.2.2. AND-EQUAL
说明：用来合并由索引返回的值的排序列表。AND-EQUAL用于非唯一索引的合并和唯一索引的范围扫描。
例子：selectname,city,state from company where city=’Roanoke’ and state=’VA’
2.2.3. CONCATENATION
说明：用来执行结果集的unionall操作
例子：selectname,city,state from company where state=’TX’ and city in (‘Houston’,’Austin’,’Dallas’);
该例子最终可以该写成：selectname ,city,state from company where (state=’TX’ and city=’Houston’) or (state=’TX’and city=’Austin’ ) or (state=’TX’ and city=’Dallas’);
备注：有时候在语句比较复杂时，Oracle可能不会使用CONCATENATION操作，而是使用部分索引的范围扫描（Range Scan）。如果要强制使用CONCATENATION操作，就把语句写成最终的样子。
2.2.4. COUNT
说明：当使用伪列（Pseudo-column），并且指定RowNum的最大值时，执行Count（计数）。Count从它的子操作接收行并且增大RowNum的计数。
例子：selectname,state,rownum from company where city>’Roanoke’
2.2.5. COUNT STOPKEY
说明：当使用限定计数的RowNum伪列时，执行COUNT STOPKEY。它从先前的操作接收行并且增加计数，如果计数达到了临界值，就产生一个“NoMore Rows”条件，结束查询并把结果返回给用户。
例子：selectname,city,state,rownum from company where city>’Roanoke’ and rownum<10;
2.2.6. FILETER
说明：使用在当没有索引可以用来帮助评估时，FILTER执行一个where子句条件。当FILTER在一个解释方案里显示时，通常显示的是索引丢失或者存在的索引不能用的结果。
2.2.7. FOR UPDATE
说明：为所有能从select语句回复的行级别（row level）上加锁。
例子：select namefrom company where city>’Roanoke’ for update of name;
2.2.8. HASH JOIN
说明：它是行操作和集操作的混合。它在内存中创建其中一个表的位图，然后利用哈希（HASH）功能在第二个表里定位联结行，通过HASH JOIN（哈希联结）将表联结起来。
例子：selectcompany.name from company,sales where company.company_id=sales.company_id andsales.period_id=3 and sales.sales_total>1000;
注意：在这个例子中，sales表作为联结表被读到内存中，然后同company表中的记录进行逐行比较。能够使用到hash join的条件为在联结的表中，一张表比其他的联结表小的多，并且这些记录能够全部被读到内存中去，那么系统就会使用hash join而不是使用nested loops来进行连接。有时即使为联结提供了一个索引，hash join也许比nested loops联结跟可取。
2.2.9. INDEX RANGE SCAN
说明：它从索引里选择一定范围的值，索引既可以是唯一的也可以是复合的。当遇到下列条件时，使用它：
l 使用范围操作（如>or<）
l 使用between子句
l 使用有通配符的查找字符串（如：’b%’）
l 只使用复合索引的一部分
例子：selectname,city,state from company where city>’Roanoke’
注意：它的效率与两个因素有关：选择范围内的关键值的数目以及索引的条件，当关键数目越多，查找时间越长；分段越多，查找时间越长。
INDEX UNIQUE SCAN
说明：它从唯一索引中选择，是从已知字段里选择一行的最有效的方法。
例子：selectname,city,state from company where company_id=12345
2.2.10. MERGE JOIN
说明：它通过合并每个表中已排序的记录列表去联结数据表。它主要是针对大型批处理操作，但是对事务处理可能是无效的。当ORACLE实施联结而又不能使用索引时，就使用merge join。
例子：selectcompany.name from company ,sales where company.company_id+0=sales.company_id+0and sales.period_id=3 and sales.sales_total>1000
注意：需要值得注意的是它是针对集操作。对于它的执行计划，在执行完一个全表扫描之后还要进行一个sort join的排序操作，然后再进行merge join操作。
2.2.11. NESTED LOOPS
说明：在进行多表的联结时，如果有一个联结的列被索引过的时候，那么nested loops将起作用。
例子：selectcompany.name from company ,sales where company.company_id=sales.company_id andsales.period_id=3 and sales.sales_total>1000
注意：
l 在使用nested loops的时候，查询的驱动表的选择是很重要的，关于这点，这下面会作专题讨论。原则是如果是rule_based的情况下，驱动表将选择from语句的最后一个，如果是cost_based的情况下，驱动表的选择间考虑表的大小和索引的选择性。
l 在进行nested loops的操作时，首先作的是驱动表的全表扫描，然后才使用索引的扫描，然后才进行nestedloops操作。
2.2.12. OUTER JOIN
说明：它是nestedloops、hash join、merge join操作的一个选项。它死来自驱动表的行能够返回到调用查询，虽然在联结数据表里没有发现任何匹配的行。
例子：selectcompany.name from company ,sales where company.company_id=sales.company_id(+)and sales.period_id=3 and sales.sales_total>1000
2.2.13. SEQUENCE
说明：当通过nextval和currval来访问sequence时，使用sequence操作。
例子：selectseq_tbi_tm.nextval from dual;
2.2.14. SORT AGGREGATE
说明：每当对数据集的组操作功能在sql语句中出现但是没有groupby子句时，SORTAGGREGATE（聚集排序）就用来给结果排序和聚集。这些组函数包括：max、min、count、sum、avg
例子：selectsum(sales_total) from sales;
2.2.15. SORT GROUP BY
说明：在数据集中实现分组功能
例子：selectzip,count(*)from company group by zip;
2.2.16. SORT JOIN
说明：把用于MERGEJOIN操作的一个记录集排序，同mergejoin是同步出现的。
例子：selectcompany.name from company ,sales where company.company_id+0=sales.company_id+0and sales.period_id=3 and sales.sales_total>1000
2.2.17. SORT ORDER BY
说明：它用于给结果集排序，但是不去除重复记录。
例子：select namefrom company order by name;
2.2.18. SORT UNIQUE
说明：在由minus、intersection和union操作处理前，它用于给结果集排序并将重复记录去除。
例子：selectcompany_id from company minus select company_id from compeitor;
2.2.19. TABLE ACCESS BY ROWID
说明：基于所提供Rowid的操作，它从一个表返回一条记录。这是从一个表返回记录的最快的方法。
例子：select namefrom company where company_id=12345 and active_flag=’Y’;
2.2.20. TABLE ACCESS FULL
说明：当没有为行查找提供Rowid时，oracle将扫描给定表里的每一块，直到读完所有行。
例子：select*from company;
2.2.21. UNION
说明：它从两个或者跟多个查询结果返回行的单个集。为了执行这个操作，Oracle首先根据Select语句过滤所有的行，给它们排序，然后实行Union Merge。在Union操作期间，包含了Union all操作。
例子：selectcompany_id from company union select company_id from competitor;
注意：如果一个操作不考虑去除重复记录的话，请使用union all，它的执行效率比union高。
三. 编写高效的SQL：
在Oracle系统的性能提升过程中，SQL语句的优化的效果是最明显的。因此，编写高效的SQL语句对整个系统性能的提高是应用中效果最明显的，同时也是代价最低的。
一个高效的SQL语句基本上都是要利用到索引的，因此我们在谈SQL语句的同时也介绍一下索引。并且，任何语句都同回滚段都分不开，因此也会介绍一下回滚段的情况以及使用。
1. 什么是索引
1.1. 索引定义
索引本身也是一个数据库对象。对于在表的索引列上的每一值将包含一项（ENTRY），为行提供直接的快速存取。索引的存储参数的取值和使用同表的存储参数的使用一致。
1.2. 索引的组织存储形式
在Oracle系统中，索引的数据存储是以B+树的形式存在的。
需要值得注意是，在索引的数据项发生删除的时候，释放出来的空闲空间无法再次使用。
如果对索引数据发生频繁的update或者删除的时候，会导致索引失去平衡，或者导致索引的层次增加，会导致索引停滞（Index Stagnation）现象的出现。为解决该问题，可以考虑重建索引。重建索引可以使用Alter index ….Rebulid语句来实现，该功能可以使索引再次平衡，同时也可以对索引的存储地点以及存储策略进行修改。
从Oracle 8开始，索引的类型增加了两种，一为位图索引（Bitmap Index）主要针对重复值比较多的列而创建的索引，对它的逻辑操作的效果比较好；一为反向索引（Reverse Index）主要是为尽量保持索引的平衡而考虑创建，对该索引的查询只有使用“＝”才起作用，而对于范围内的查询（Range Scan）不起作用。
对于数据比较多的表创建索引的时候，从考虑效率角度出发，在创建索引的时候使用No Logging的参数来提高效率。该参数在创建索引的时候不产生重作日志，可以有效的提高效率，加快索引的产生。
1.3. 使用索引的优缺点
在下列情况下ORACLE可利用索引改进性能：按指定的索引列的值查找行，按索引列的顺序存取表。然而，索引虽然可以加快查询的速度，但是它可减慢INSERT、DELETE和UPDATE命令的速度，由于这些操作要影响索引列的值，ORACLE必须对索引数据和表数据进行维护。
1.4. 创建索引的限制
一个索引最多可包含16列（到Oracle 8开始为32列），索引项为每一列的数据值的连串，按指定的列的顺序连串。这一顺序对ORACLE如何使用该索引非常重要。ORACLE在一张表上可建立多个索引，索引的数目没有限制。但是应该注意增加索引会增加维护表所需的处理时间。建议只有当要索引的数据在表中所占的数据量，少于总数据量的15%时，使用索引才会提高查询的速度，否则对表的全表扫描的速度还比使用索引的速度快。
1.5. 创建索引遵循的原则
对于索引列的选择遵从下列的准则：
u 经常用于WHERE子句中使用的列考虑作索引的列。
u 经常用于SQL语句中连结表的列考虑作为索引的列。
u 在Cost_based的优化方式下，对于复合索引的主列的选择显的尤为重要。它的选择可以遵循以下原则：在Where条件中频繁使用的列为主列；对于数据最有选择性的列为主列。
u 一个索引列要具有好的选择性。一个索引的选择性（Selectivity）是对具有相同值的表中的百分比。一个索引的选择性好，就是很少行有相同值。可以通过用具有不同索引值的数目除表中的行数来决定索引的可选择性。可以用ANALYZE命令获取这些值。用这种方法计算的可选择性应该解释为百分比。
u 不要将具有很少不同值的列作为索引列。这样的列具有很差的选择性，并且不能优化性能，除非频繁选择的值比其他列值来更少出现。例如：在表中如果存在以YES或NO为值的列，那么尽量不要用该列作为索引列，因为用此列作为索引不会提高系统的性能，但是如果在表中YES的值很少出现，而在运用中又经常以YES值为查询，则以该列为索引可能会改进系统的性能。从Oracle 8开始可以考虑创建Bitmap Index来解决一些问题。
u 不要将频繁修改的列作为索引列。因为修改索引列的UPDATE语句和修改索引表的INSERT和DELETE语句将比没有索引要用更多的时间。这样的语句必须修改索引中的数据，还要修改表中的数据。
u 不要将只出现在带函数或操作符的WHERE子句中的列作为索引列。使用带索引列的函数（不是MIN或MAX函数）或操作符的WHERE语句并不使用索引的存取路径。
u 在大量并发INSERT、UPDATE和DELETE语句存取父表和子表的情况下，考虑对参考完整性约束的外部键作索引。也就是说对表的外键在可能的情况下创建索引以加快操作的速度，改进性能。因为如果对于一张表而言，如果在它的外键上没有索引的话，那么在对它操作的同时，ORACLE将对此外键对应的主表加一个表级锁，以保证数据的完整性和一致性。如果在外键上有索引的的话，那么它就可以通过索引找到主表的ROWID，只对主表加一个行级锁。
1.6. 如何选择单列索引还是复合索引
1.6.1. 索引选择性的测量
所谓索引选择性是指索引中有多少不同的值同表中记录数目的比值，不同的值越多，代表选择性越高，最高的选择性为1（例如唯一性索引）。
对于索引选择性的测量可以通过不同的方法来进行测量。一种是通过手工的方式作，一种是通过自动的方式来作。
自动方式来作的是通过对表进行Analyze之后，然后通过查看相应数据字典的值来计算。对索引的不同值可以通过USER_INDEXES.DISTINCT_KEYS查到，表的全部记录数可以通过USER_TABLES.NUM_ROWS查到。在创建索引之前，可能需要查看一下列的不同值的数目，它可以通过USER_TAB_COLUMNS.NUM_DISTINCT查到。
手工方式，就是对相应的索引列进行DISTINCT操作，然后得到不同值的数目。然后对表进行COUNT(*)函数操作，得到表的记录数目，把两者数据进行比较，就得到索引的选择性。
1.6.2. 单列索引和复合索引的选择
对于复合索引而言，主列（第一列）的选择性从很大程度上决定了复合索引的选择性。我们还是举前面的例子来说明如何选择索引的创建。假设我们在Where条件中经常用City=’xxx’and state=’xxx’为条件的话，这时有两种选择，一种是方案1，一种是方案3。假设复合索引同单独索引的选择性差不多的话，那么选择方案3就会有一个AND_EQUAL的操作，会导致比方案1多将近2/3的索引操作，因此需要考虑方案1。又假设在系统中有大量的基于State的Where语句操作，这时还是有两种方案一是方案2，一是方案3。此时如果基于State列上的索引的数据量不大的话，而且相应的附加开销不多的话，基于同上面相同的理由，可以选择方案2。不过如果单列索引同复合索引的选择性相差很大的话又另当别论了。
附：
方案 | 索引#1 | 索引#2 |
1 | City,state | None |
2 | City,state | State |
3 | City | State |
2. 编写利用索引的SQL语句
在日常的应用中，索引是否引用是关系到该SQL语句效率的一个非常关键的因素。在日常应用中应该注意到如果在SQL语句中出现下列语法，则系统无法利用到索引：
u COLUMN1>COLUMN2
u COLUMN1<COLUMN2
u COLUMN1>=COLUMN2
u COLUMN1<=COLUMN2
其中COLUMN1和COLUMN2在同一张表中。
u COLUMN IS NULL
u COLUMN IS NOTNULL
u COLUMN NOT IN
u COLUMN !=EXPR
u COLUMN LIKE‘%ANYTHING’
在上述条件中不管COLUMN上是否有建索引，SQL语句都不会利用索引。
u EXPR是一个表达式，它用运算符或函数操作在该列上，不管列上是否有索引，例如：EXPR*COLUMN=ANYTHING，则不能利用索引。
u NOT EXISTSSUBQUERY
u 不包含未被索引的列的任何条件
u 在LIKE表达式中如果模糊的列为数字或日期的话，那么该语句同样无法利用索引。
u 在ORACLE内部的本身的数据转换（DATA CONVERSION）将引起ORACLE索引列的不被使用。
在日常SQL语句使用时还应该注意使用索引的原则：
u 在使用复合索引时，应该注意是否有利用到该索引的前导部分（LeadingPosition），在使用该复合索引时只有使用到该索引的前导部分，该索引才能被利用，否则就根本无法发挥索引作用。例如：用户在一张表的三个列（X，Y，Y）张建立一复合索引，该复合索引的顺序为XYZ。在SQL语句的WHERE条件中使用XY，XYZ，XZ等都可利用到索引，但是如果使用YZ，Z等就无法利用到索引。如果对索引的所有列都有引用的话，则无所谓对字段位置的使用了。
u 对于复合索引，对于主列的操作是否使用索引同样受上面的条件限制。
u 在SQL语句中尽量避免不要使用不等或者NULL。
3. 回滚段的作用：介绍回滚段的原理，使用时要注意的事项，从它入手如何提高SQL的效率，使用固有的ORACLE提供的PACKAGE
具体内容详见相应的回滚段的专题介绍。
4. SQL语句HINT的说明
作为一个应用软件的设计者或者编程人员，应该比Oracle数据库的优化器更清楚数据的分布情况。特别是一个SQL语句有多种执行路径的时候，优化器的选择也许不是最优的，这时就可以通过在语句中使用Hint来指定优化器使用哪种的执行路径。
4.1. 在哪些语句中允许使用Hint
并不是任何场合下都允许使用Hint的，只有符合下列情况之一，才有可能使用Hint：
² 一个简单的SELECT,UPDATE或者DELETE语句
² 一个主查询或者一个复杂语句的子查询
² 一个复杂查询的一部分
4.2. Hint的书写格式说明
Hint的书写格式如下：
(delete,select,update) /*+Hint*/
或者
(delete,select,update) --+Hint
Hint的写法为在几个关键字之后使用SQL语句的注释符（/*Hint*/;--+Hint）来写Hint。此外，如果Hint书写有问题的话，Oracle并不会报错，而是继续使用优化器的执行路径来作。
4.3. Hint的分类使用说明
4.3.1. 优化方式选择
该类型的Hint等价于在Init参数中使用Optimizer_mode指定优化的方式或者等同对于某一个Session使用Alter session …set optimizer_mode的功能。
内容包括：All_rows,First_rows,Choose,Rule。
4.3.1.1. ALL_ROWS
语法格式为：/*+ALL_ROWS*/
该Hint显式的选择Cost_based的方式来优化SQL语句块。它从最小化总体的资源消耗的方面考虑优化，也就是达到整体的输出时间最少。
4.3.1.2. FIRST_ROWS
语法格式为：/*+FIRST_ROWS*/
该Hint显式的选择Cost_based的方式来优化SQL语句块。它从最佳反馈时间方面来考虑优化，也就是要求使第一条的记录返回时间最短。
该Hint是优化器有如下选择：
² 如果有一个索引可以使用的话，那么优化器将可能会使用索引代替全表扫描。
² 如果有一个索引可以使用的话，那么优化器将可能会在对于一个NESTEDLOOPS操作中关联到一个相关的INNER表时，使用NESTED LOOPS JOIN代替SORT-MERGE JOIN操作。
² 如果有一个索引可以使用的话，那么优化器将可能会使用它来在一个ORDERBY操作中避免产生一个SORT操作。
该Hint在什么情况下会被忽略：
² 在UPDATE或者DELETE语句中会被忽略
² 在下列的SELECT语句中该Hint也会被忽略：数据集操作（UNION,INTERSECT,MINUS,UNIONALL），GROUP BY子句，FOR UPDATE子句，GROUP函数，DISTINCT操作。由于上述操作都需要对全部的数据进行检索，同该Hint的作用（以最短的时间返回第一条记录）相冲突。
4.3.1.3. CHOOSE
语法格式为：/*+CHOOSE*/
该Hint使优化器在COST_BASED和RULE_BASED的优化模式中选择一种模式。如果在一个语句中关联到的表中有一个表有统计数据，那么优化器就选择COST_BASED的优化模式同时以最佳的输出时间为优化目的（等同于ALL_ROWS）。反之就使用RULE_BASED的优化模式。
4.3.1.4. RULE
语法格式为：/*+RULE*/
该Hint显式的指定优化器使用RULE_BASED模式。
4.3.2. 数据访问方式选择
该类型的Hint包括下列内容：FULL,ROWID,CLUSTER,HASH,HASH_AJ,INDEX,INDEX_ASC,INDEX_COMBINE,INDEX_DES,INDEX_FFS,MERGE_AJ,AND_EQUAL,USE_CONCAT
4.3.2.1. FULL
语法格式如下：/*+FULL(table_name)*/
该Hint显式的指定优化器对指定table_name进行全表扫描。
例子：select /*+full(a) Don’t use the index on ACCNO*/ accno,bal
from accounts a
where accno=xxxx;
从上述例子中我们可以对整个Hint的写法有一个更深刻的认识：“Don’t use the index onACCNO”为整个Hint的注释说明，由于在语句的From子句中对表起了一个别名，因此在Hint中的table_name就不能使用真正的表名来代替，而必须使用表的别名。
4.3.2.2. ROWID
语法格式如下：/*+ROWID(table_name)*/
该Hint显式的指定优化器对指定的table_name的访问通过Rowid来进行。
4.3.2.3. CLUSTER
语法格式如下：/*+CLUSTER(table_name)*/
该Hint显式的指定优化器对指定的table_name的访问通过Cluster Scan来完成。
4.3.2.4. HASH
语法格式如下：/*+HASH(table_name)*/
该Hint显式的指定优化器选择HashScan对一个指定的表进行访问。该操作只能对存储在Cluster上表起作用。
4.3.2.5. HASH_AJ
语法格式如下：/*+HASH_AJ*/
该Hint把一个Not in子查询转换成对一个指定表的hash anti_join访问。
4.3.2.6. HASH_SJ
语法格式如下：/*+HASH_SJ*/
该Hint把一个Exists子查询转换成对一个指定表的Hash semi_join的访问。
4.3.2.7. INDEX
语法格式如下：/*+INDEX(table_name index_name)*/
该Hint显式的选择对一个指定表的访问通过指定的索引来进行。
对于该Hint需要注意的几点是：
² 如果在Hint中指定了一个唯一的索引的话，那么优化器就会通过该索引来进行数据检索，而不会考虑全表扫描或者通过其他的索引来进行检索。
² 如果在Hint中指定了多个索引的话，那么优化器就会在通过对指定的索引列表进行依次比较，最终选定代价最低的索引来进行检索。优化器不会选择进行全表扫描也不会使用不在Hint中出现的索引列表中的索引。
² 如果在Hint中没有指定索引名字的话，优化器会通过对该表上全部的索引进行依次比较，然后最终选定最低的代价的索引进行检索。优化器不会考虑进行全表扫描。
4.3.2.8. INDEX_ASC
语法格式如下：/*+INDEX_ASC(table_name index_name)*/
该Hint显式的对一个指定的表通过索引进行检索。如果该语句通过索引进行范围检索的话，通过该Hint对该索引的值进行升序检索。此类提示对于基于索引的MIN函数比较有效，只需要作一次查询就可以了。
4.3.2.9. INDEX_COMBINE
语法格式如下：/*+INDEX_COMBINE(table_name index_name)*/
该Hint主要针对指定表上的bitmap_index。
4.3.2.10. INDEX_DESC
语法格式如下：/*+INDEX_DESC(table_name index_name)*/
该Hint的整体作用同INDEX_ASC类似，不过是由ASC改为DESC。此类提示对于基于索引的MAX函数比较有效，只需要作一次查询就可以了。
4.3.2.11. INDEX_FFS
语法格式如下：/*+INDEX_FFS(table_name index_name)*/
该Hint通过对Index的全索引扫描代替全表扫描。
4.3.2.12. MERGE_AJ
语法格式如下：/*+MERGE_AJ*/
该Hint把一个Not in子查询转换成对一个指定表的merge anti_join访问。
4.3.2.13. MERGE_SJ
语法结构如下：/*+MERGE_SJ*/
该Hint把一个Exists子查询转换成对一个指定表的merge semi_join的访问。
4.3.2.14. AND_EQUAL
语法结构如下：/*+AND_EQUAL(table_name index_name index_name…)*/
该Hint显式的选择一个执行计划，对在不同的单列上的索引的检索结果进行合并。在该Hint中对于index_name至少是2个，但是不能超过5个。
4.3.2.15. USE_CONCAT
语法结构如下：/*+USE_CONCAT*/
该Hint的作用为把一些OR语句操作强制转化为Union all的集操作。
4.3.3. 联结顺序
该类型的Hint内容包括：ORDERED,STAR。
4.3.3.1. ORDERED
语法结构如下：/*+ORDERED*/
该Hint使Oracle按照From子句中给出的表的顺序进行联结。
例子：select /*+ordered*/tab1.col1,tab2.col2,tab3.col3
from tab1,tab2,tab3
where tab1.col1=tab2.col1 andtabl2.col1=tab3.col1
在此例子中，由于添加了Ordered的Hint，Oracle是把TAB1和TAB2两张表进行Join，然后把Join的结果同TAB3进行再次关联。
4.3.3.2. STAR
语法结构如下：/*+STAR*/
该Hint主要是用在对于数据仓库这一类数据量比较大的查询。
4.3.4. JOIN操作
4.3.5. 并行操作
该类型的Hint内容包括：PARALLEL,NOPARALLEL,APPEND,NOAPPEND,PARALLEL_INDEX,NOPARALLEL_INDEX。
鉴于目前这方面的内容使用的比较少，在此次的文章中暂时不讨论这一类型的Hint的作用和使用。
4.3.6. 其他
该类型的Hint内容包括：CACHE,NOCACHE,MERGE,NO_MERGE,PUSH_JOIN_PRED,NO_PUSH_JOIN_PRED,PUSH_SUBQ,STAR_TRANSFORMATION。
4.3.6.1. CACHE
语法格式如下：/*+CACHE(table_name)*/
大家都知道Oracle对于在buffer中的数据是根据Lru的算法来进行查找的。并且在Lru队列中存在两端，一端为Lru，一端为Mru，对数据的查找和匹配是从Mru开始的。Cache的作用就是使数据Load到Mru上去，使Oracle能够在最短的时间内遍历要查抄的数据，能够在最短的时间内得到结果。该Hint能够覆盖在建表的时候的默认的Cache参数的选项的作用。对于该Hint适合用于对于比较小的表的全表扫描时使用。
例子：select /*+FULL(scott_emp) CACHE(scott_emp)*/ ename
FROM scott.scott_emp;
4.3.6.2. NOCACHE
语法结构如下：/*+NOCACHE(table_name)*/
该Hint的作用同CACHE的作用相反，它把数据Load到Lru队列的Lru一端。
对于其他的Hint由于日常使用的不多在这里就不一一介绍了，在日后的文章中有机会在介绍了。
5. 编写SQL语句的技巧
5.1. 基本原则
² 写相同的SQL语句，保证程序能够利用共享池的内容，加快程序的执行速度。
² 写能够利用索引并且能够符合基于代价的优化条件的SQL语句。
² 尽量利用PL/SQL的特性减少网络的传输。
² 把能够写在服务器端的程序尽可能的不要写在CLIENT端，以减少网络的传输和提高系统的编译速度。
² 良好的代码书写习惯
² 能够不使用SELECT *的尽量不要使用，除了是为了防止出现在增加字段的时候不需要修改程序外，也是为了保证性能。
5.2. 尽量避免全表扫描
在日常编写SQL语句时，除非迫不得已，不然尽量避免下列情况，因为下列情况会导致，系统进行全表扫描来查找数据。
1、该表上没有任何索引。
2、一个查询语句中没有任何限制条件，也就是没有Where子句。
3、对于索引的不适当使用也会导致全表扫描。关于该方面的内容在前面的章节中已经讨论过了。
4、在进行多张表的联结操作中，如果在from子句中有，但是在where子句中没有用到的表也会导致对该表的全表扫描。
5.3. 适当索引CONNECT BY操作
5.4. 限制对远程表的访问
5.5. 使用UNION ALL代替UNION
UNION ALL和UNION的操作的最大的区别在于，UNION操作为了保证数据的唯一性，把返回的数据集中的重复的数据去除了。因此，在UNION的执行步骤中包含了SORTUNIQUE的操作。从上个章节的介绍中，我们已经知道了，对于任何一个UNION操作都包含了UNIONALL的操作。并且，从两者的解释计划中，大家可以看到在进行多表的JOIN操作时，UNION是对相应的表进行全表扫描之后再进行UNION ALL的操作，然后进行SORT UNIQUE最后进行NESTED LOOPS，而对于UNION ALL则是先返回相应的小的数据集，然后再进行UNION ALL的操作。
因此，如果对于返回的数据集不强调数据的唯一性的话，请试用UNION ALL代替UNION。
5.6. 管理好多表之间的联结
5.7. 管理好包括视图的查询语句
5.8. 优化子查询
5.9. 不要在SQL查询中使用PL/SQL功能调用
ORACLE公司提供的PL/SQL有着通用SQL无法提供的功能，对简化程序的编写有着不可多得的有点，因此深为大家所喜欢。
举例来说，大家有时候可能想把数据中的小写数字转换成大写数字，于是，编写了一个函数，实现了该功能，然后在平时在SQL语句中调用该函数来实现数字转换。大大简化了程序的编写，而且也提高了软件的可读性。
不过，假如从软件的运行效率方面来考虑的话，这种在SQL语句中混合使用PL/SQL的方式是不提倡的。有一个使用数据表明，同样功能的语句，如果是SQL语句调用PL/SQL的方式的话，速度会比正常的慢44倍左右，我们日常中的数值可能会低于这个数字，不过也都有10倍以上。
因此，总结一句就是不要为了贪图编写方便，而在SQL查询中使用PL/SQL功能调用。
5.10. 在关键应用中使用OCI代替PL/SQL、PRO*C
Oracle提供了三种对数据库操作和管理的方式，那就是OCI、PRO*C、PL/SQL。在这三种方式中OCI能够更直接调用操作系统的API，因此能够更直接的到达系统的内核，所以速度比PRO*C、PL/SQL都快。并且，OCI比起其余另外两种方式在HA方面的性能表现的更好。因此，建议对于所有的会话关键型应用程序使用OCI。
5.11. 在OLAP应用中使用复合的KEYS/STAR查询
四. 日常SQL TUNING工具的使用
1. 基础准备工作
在Oracle的Rdbms系统中，对于系统的优化的选择有两种方式，一种是Cost_based（基于代价），一种是Rule_based（基于规则）。在我们现在的系统中大部分是使用Cost_based的优化策略。如果采用Cost_based的优化策略的话，因此它的一切工作都是以统计值为基础。因此，对于相应的对象必须进行分析（Analyze），这样在相应的系统视图中才有统计值，才能真正使用Cost_based的优化。
在下面介绍的工具中，都需要看一个东西，那就是执行计划。正如其他的Oracle系统数据一样，执行计划也是存储于表中，这张表就是Plan_table。在正常的时候，这张表是不存在，必须人工创建。创建它的方法如下：
在需要进行分析的用户下，执行UTLXPLAN.SQL。该脚本存放在$ORACLE_HOME/RDMBS/ADMIN目录下。对于，安装DEVELOPER/2000的用户可在\ORAWIN95\RDBMS73\ADMIN下找到。该脚本建立一张名为PLAN_TABLE的表。用户也可根据自己的需要，把该表的表名改为用户需要的名称。如果，该用户无建表权限，则可在某一用户下建立该表，通过该用户对其余用户进行授权。需要授的权限包括：SELECT，DELETE，INSERT等权限。
我们在日常编写应用程序的过程中，可以尝试着从以下几种方法来查看，自己使用的SQL语句的效率是否最优。
l EXPLAIN PLAN：一种最原始的查看SQL语句的执行计划的方法。
l SET AUTOTRACE：一种增强型的EXPLAIN PLAN，可以帮助大家省去许多烦琐的过程。
l TKPROF：一个应用级的跟踪工具，可以明确标明系统的CPU消耗时间，IO的情况以及语句的执行计划等信息。
2. EXPLAIN PLAN的使用
它的语法结构如下：explain plan set statementid='text' for statement
其中对于statementid为用户任意设置的值，而statement为今后在系统中要执行的语句，也就是当前要分析的语句。
经过EXPLAIN PLAN分析出来的结果存放在PLAN_TABLE中，想对分析出来的结果进行分析，就必须对PLAN_TABLE中的数据进行分析。一般情况下，用如下语句对分析出来的结果进行分析：
select id,parent_id,position,operation,options,object_name,object_type,costfrom plan_table;
3. SET AUTOTRACE 的使用
在ORACLE中提供了一更方便、更有效的数据存取路径解释方案，它就是在SQL*PLUS中设置AUTOTRACE ON，通过该方法，可以得到该语句的执行计划和相应的统计数据，这些数据包括(For Example)：
15 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
284 bytes sent via SQL*Net to client
244 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
要完成上述功能就必须先完成如下事情：以SYS用户登录，执行c:\orawin95\plus33\plustrce.sql通过该SQL语句创建PLUSTRACE这一ROLE，同时在SYS用户下把该ROLE授权给PUBLIC，使一般用户都可使用到该ROLE。
对于它有如下用法：set autotrace on(off/trace)
4. TKPROF的使用
1) 在使用TKPROF前要作的准备工作
针对不同环境下对不同的跟踪要求需作的准备工作不同：
u 对于需要进行分阶段跟踪的用户可通过如下操作进行跟踪，使本身处在受跟踪的环境下，该命令如下：alter session setsql_trace=true,如果想把该跟踪屏蔽，则命令如下：alter session set sql_trace=false或可以通过PACKAGE DBMS_SESSION.SET_SQL_TRACE(ACTION)
u 对于DBA用户如果想使某一用户的的处理受跟踪，使用的命令如下：dbms_system.set_sql_trace_in_session(sid,serial#,action);其中sid,serial通过V$SESSION这一VIEW得到数据， 写法如下:selectsid,serial# from v$session where username=‘要跟踪的用户名’，action为TRUE、FALSE，如果actio为TRUE则要进行跟踪，如果为FALSE则要取消跟踪。
u 如果想对在用的每一个用户都进行性能跟踪的话，那么进行如下处理，在INITSID_NAME.ORA中增加如下参数：
TIMED_STATISTICS:该项的值可设置为TRUE、FALSE，通过该参数可以启用在性能跟踪时的时间统计，平常系统在不作性能跟踪时，最好不要打开该参数，因为该参数会造成对低层操作的额外定时调用，会导致系统性能的部分下降。
MAX_DUMP_FILE_SIZE:设置系统TRACE文件的大小，该单位以操作系统的BLOCK（块）进行计算，默认为500。
USER_DUMP_DEST:决定系统的TRACE文件最终存储的路径。
SQL_TRACE:通过对该参数设置TRUE或FALSE使系统的跟踪可用或不可用。
2) 如何使用TKPROF
通过1）步骤产生的文件的命名规则如下，该文件以ORA打头、TRC为文件的扩展名。总的来讲，该TRACE文件的以下列形式构成:ORA_SPID.TRC。TKPROF的命令行格式如下：
tkproftrace_file_name output_file_name insert=sql_trace_file_name sys=yes/noexplain=username/password
其中，TRACE_FILE_NAME指通过跟踪产生的TRC文件，OUTPUT_FILE_NAME指TKPROF分析后产生的分析文件的名称。INSERT子句代表用户经过分析之后形成脚本，其中SQL_TRACE_FILE_NAME代表用户分析后形成的用户SQL脚本的文件名称。SYS=YES代表是对系统语句进行分析，SYS=NO代表不对系统语句进行分析。EXPLAIN子句代表是否对TRC文件中语句进行并列出相应语句的执行计划。
3) 如何对TKPROF产生的结果进行分析
对于每一个SQL语句，每一行相对应于三阶段，此三阶段为PARSE，EXECUTE，FETCH，此三阶段的分别含义为：
PARSE：此步骤将SQL语句翻译成一个执行计划。此步骤包括对正确的安全认可检查，以及表、列和其它要参考的对象的存在性的检查。
EXECUTE：此步骤为ORACLE对语句的实际执行。对于UPDATE，DELETE和INSERT语句，此步骤修改数据。对于SELECT语句，此步骤识别要选择的行。
FETCH：此步骤返回由查询返回的行。此步骤只对SELECT语句有效。
对于每一个分析出来的文件，在其开头部分都有如下一段一段提示：
count =number of times OCIprocedure was executed
cpu =cpu time in seconds executing
elapsed =elapsed time in seconds executing
disk =number of physical reds of buffers from disk
query =number of buffers gotten ofr consistent read
current =number of buffers gotten in currentmode(usually for update)
rows =number of rows processed by the fetch or execute call
上述的提示中提到的关键字是今后在对系统进行分析时，要用到的所有语法分析，所有执行和所有取数的组合统计，具体的中文含义如下：
² Count 一个语句被语法分析、执行和取数的次数。
² Cpu 对语句的所有语法分析，执行或取数调用的总CPU时间（以秒计）。
² Elapsed 对语句的所有语法分析，执行或取数调用的总耗费时间（以秒计）。
² Disk 对所有语法分析，执行或取数调用的，从磁盘数据文件上数据块物理读的总数。
² Query 对所有语法分析，执行或取数调用的，以一致方式检索的缓冲区总数，对查询来说，通常用一致性方式检索缓冲区。
² Current 以当前方式检索的缓冲区总数。对INSERT，UPDATE和DELETE语句通常用当前方式检索缓冲区。
Query和current的和是存取的缓冲区的总数。
² Rows 由SQL语句处理的总行数。对于SELECT语句而言，返回的行数出现在取数的步骤，对UPDATE、DELETE和INSERT语句而言，处理的行数出现在执行步骤。
共同学习，写下你的评论
评论加载中...
作者其他优质文章
