AutoTrace是分析SQL的执行计划、执行效率的工具。

1.安装AUTOTRACE

SQL> connect / as sysdba

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant select,update,insert,delete on plan_table to public;

Grant succeeded.

SQL> @?/sqlplus/admin/plustrce.sql

SQL>grant plustrace to public.

2.使用AutoTrace

首先需要在环境设定中启用Autotrace

SQL>SET AUTOTRACE ON

我们就可以看到我们SQL的执行计划，执行成本（PHYSICAL READ/CONSISTENT READ...)

加上SET Timing On或者Set Time On，我们可以得到很多我们需要的数据。

常见的出错信息

SQL> set autotrace on;
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用STATISTICS报告时出错

解决方法：
赋予当前用户PLUSTRACE角色，再给PLUSTRACE赋予几个视图select权限
SQL> grant select on v_$sesstat to plustrace;
授权成功。
SQL> grant select on v_$statname to plustrace;
授权成功。
SQL> grant select on v_$session to plustrace;
授权成功。

I swear to protect the weak and helpless.

I swear to love and protect my homeland.

I swear to fight our enemies to the death.

I swear that I will never lie and will always be true to my word.

I swear to be forthright and generous to all.

I swear to always fight for good, justice and freedom.

“如果浪费了半小时时间，我就觉得很惭愧。后来我看到很多人不珍惜时间的时候，我就觉得这样的人真没出息。时间是自己的，你到一个公司打工的时候，偷懒，老板没有看见，就觉得自己又蒙了一下，玩猫和老鼠的游戏，真是没有必要。公司所付的那么一点钱，就买下了你一个月的青春？学会的东西首先是自己的，其次才是公司的。没有多少人真正计算过自己一个小时值多少钱。”

好玩的TD

VR Defender Y3k

选择合适的优化器目标

默认情况下，CBO以最佳吞吐量为目标，这意味着Oracle使用尽可能少的资源去处理被语句访问到的所有行；当然CBO也可以用最快的响应速度来优化SQL，这意味着Oracle用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句来说可能消耗更多的资源。

优化器产生的执行计划会因"优化器目标"的不同而不同。如果以最佳吞吐量为目标，结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。

例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。

选择优化器目标要以你的应用为基础，一般规则是：

1、 对于批处理应用，以最佳吞吐量为优化目标为好。例如Oracle报表应用程序。

2、 对于交互式应用，以最快响应速度为优化目标为好。例如SQLPLUS的查询。

影响优化器优化目标的因素主要有：

1、 OPTIMIZER_MODE初始化参数。

2、 数据字典中的CBO统计数据。

3、 用来改变CBO优化目标的Hints。

OPTIMIZER_MODE初始化参数

这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下：

Value Description
CHOOSE
此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。

1、 如果在被访问的表中，至少有一个表在数据字典中有可用的统计信息存在，则优化器使用基于成本的方法。

2、 如果在被访问的表中，只有部分表在数据字典中有可用的统计信息，优化器仍然会使用基于成本的方法，但是优化器必须为无统计信息的表利用一些内部信息去尝试其他的统计，比如分配给这些表的数据块的数量等，这可能会导致产生不理想的执行计划。

3、 如果在被访问的表中，没有一个表在数据字典中有统计信息，则优化器使用基于规则的方法。

ALL_ROWS
不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞吐量为优化目标。

FIRST_ROWS_n
不论是否有统计信息存在，优化器都使用基于成本的方法，并以最快的速度返回前n行数据集，n可以是1,10,100,1000。

FIRST_ROWS
优化器使用成本与试探法混合的方式，去寻找一个可以最快返回前面少数行的执行计划。

注：CBO使用试探法产生的执行计划，其成本可能会比不使用试探法要大。FIRST_ROWS可用于向后兼容和计划稳定性。

RULE
不论是否有统计信息存在，优化器都会使用基于规则的方法。

你可以在SESSION中改变CBO优化目标：ALTER SESSION SET OPTIMIZER_MODE。例如：

1、在初始化参数文件中加入如下语句，可以在实例级改变CBO优化目标：

OPTIMIZER_MODE=FIRST_ROWS_1

2、下面的语句可以改变当前SESSION的CBO优化目标：

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1

可以改变CBO优化目标的Hints

使用如下Hints可以单独为具体的SQL指定CBO优化目标，SQL语句中Hints能够覆盖OPTIMIZER_MODE初始化参数。

l FIRST_ROWS(n)，n为任意正整数。

l FIRST_ROWS

l ALL_ROWS

l CHOOSE

l RULE

数据字典中的CBO统计信息

CBO使用的统计信息存放于数据字典中，你可以使用DBMS_STATS包或ANALYZE语句以精确的方式或估算的方式来统计对象的物理存储特征和数据分布情况。

注意：

Oracle公司建议使用DBMS_STATS包来代替ANALYZE语句收集统计信息。DBMS_STATS包可以并行的收集统计信息，可以为分区对象收集全局统计信息，以及使用其他方式优化收集操作。

但是，收集和基于成本优化器无关的信息必须用ANALYZE而不是DBMS_STATS，比如：

l 使用VALIDATE或LIST CHAINED ROWS 子句。

l 收集freelist块的信息。

CBO如何对SQL做最快响应的优化

OPTIMIZER_MODE被设置成FIRST_ROWS_n、FIRST_ROWSS，或者SQL语句中使用了FIRST_ROWS(n)、FIRST_ROWS提示，CBO都会对SQL做最快响应的优化。

这非常适用于联机用户，像通过Oracle Forms或Web访问的用户。联机用户的特点是只对前面少数行感兴趣，很少看整个查询的结果，特别是在查询结果巨大的情况下。对于这样的用户，优化SQL使前面少数行尽可能快速的返回是有意义的，即使产生整个查询结果的时间并不理想。

CBO在做这种优化时，会产生一个处理第一行或前面少数行消耗成本最低的执行计划。CBO有两种用来产生最快响应速度的方法，一个是旧方法一个是新方法。旧的方法就是用FIRST_ROWS提示或初始化参数，这种方法CBO会使用成本和规则混合的方式来产生一个计划。Oracle保留这种方法是为了向后兼容。

新方法FIRST_ROWS_n或FIRST_ROWS(n)提示，是完全基于成本的。如果n值较小，CBO倾向于产生一个包含嵌套循环连接和索引查询的执行计划；如果n值较大，则CBO倾向于产生一个包含散列连接和全表扫描的执行计划。

理解基于成本的优化器

CBO根据可用的访问路径和表、索引等对象的统计信息来确定当前SQL的哪个执行计划是最高效的或成本最低的；同时CBO也会考虑Hints的建议。

CBO执行下列步骤：

1、 优化器根据可用的访问路径和Hints为SQL语句产生一组潜在的执行计划。

2、 优化器根据数据字典的统计信息评估每个计划的成本。

成本就是一个评估值，它与SQL语句按照某个计划执行所消耗的计算机资源是成正比的。优化器基于对计算机资源(I/O、CPU、内存)的评估，计算访问路径和连接顺序的成本。

3、 优化器对比执行计划的成本，从而选择一个成本最低的执行计划。

CBO包含下列组件：

l 查询变换器（Query Transformer）

l 评估器（Estimator）

l 计划生成器（Plan Generator）

如下图所示：

查询变换器

被解析器解析过的查询语句进入查询变换器，表现出来的是一组查询块(query block)，这些查询块之间是相互关联的或者是嵌套的，查询的形式决定这些查询块相互之间如何被关联。查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。查询变换器使用四种不同的查询变换技术：

l 视图合并（View Merging）

l 谓词推进（Predicate Pushing）

l 非嵌套子查询（Subquery Unnesting）

l 物化视图的查询重写（Query Rewrite with Materialized Views）

最终应用于查询的也可以是以上四种变换技术的任意组合。

视图合并

查询中的每个视图都会被解析器扩展到一个独立的查询块中，这个查询块本质上是用来描述视图定义的，是视图的结果。优化器的一个任务就是去分析这个独立视图查询块(view query block)并产生一个视图子计划(subplan)，然后优化器在产生整个查询执行计划的同时使用视图子计划来处理剩余的查询部分。由于视图是被独立在整个查询之外被优化的，因此这种技术常常会导致一个不良执行计划的产生。

查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。绝大多数类型的视图是可以被合并的。在一个视图被合并后，它原有的视图查询块被包含到查询块中，也就是说视图查询块不存在了，因此也不再需要产生一个子计划。

谓词推进

对于那些不能合并的视图，查询变换器能够将相关的谓词从查询块中推进到视图查询块中。由于被推进的谓词能够用来访问索引或者用于过滤，这个技术通常可以改进那些不能被合并的视图子计划。

非嵌套的子查询

和视图一样，子查询也是用一个独立的查询块来代表的。子查询是被嵌套在主查询或其他子查询之中的，计划产生器在找到一个成本最低的执行计划之前被迫要试验所有可能的计划。由嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除，经由查询转换器过滤之后绝大多数的子查询都会被转换为非嵌套的，然后这些非嵌套的子查询产生独立的子计划，这些子计划按照一种高效的方式进行排列，从而提高了整个查询计划的执行速度。

物化视图的查询重写

物化视图就是把一个查询的结果事先固化存储在一个表里，当发现和物化视图一致的查询语句就将相应的项用物化视图来重写。由于绝大多数的查询结果都事先计算好了，因此这种技术可以极大的提高查询速度。查询转换器负责查找和用户查询相关的所有物化视图，用其中的一个或多个来重写查询。利用物化视图来重写查询也是基于成本的，如果不使用物化视图的成本更低一些，则不会去使用物化视图。

评估器

评估器会产生下列三个度量值：

l 选择性（Selectivity）

l 基数（Cardinality）

l 成本（Cost）

这些值是相互关联的，一个值由其他值导出，评估器的最终目标是评估计划的总体成本。如果有统计信息可用，评估器使用统计信息来计算这些值，统计信息可以提高其精确度。

选择性

这里的第一个度量值——选择性，表示所选择的行与行集的比值。所谓行集可以是表、视图，或者是一个连接或GROUP BY操作的中间结果。选择性与查询中的谓词有关，比如last_name='Smith'，或者一个联合谓词last_name='Smith' and job_type='Clerk'。一个谓词充当着一个过滤器的角色，在行集中过滤了一定量的行，谓词的选择性是一个比值，它表示一个行集经过谓词的过滤后剩下的行占原有行集的比例。其值在0.0和1.0之间，0.0表示在行集中没有行被选择；1.0表示行集中的所有行都被选择了。

如果没有可用的统计信息，评估器为选择性赋予一个内部的缺省值，这个内部缺省值随着谓词的不同而不同。例如：等式谓词（last_name='Smith'）的内部缺省值低于范围谓词（last_name>'Smith'），评估器会假定等式谓词返回的行数小于范围谓词。

当存在可用的统计信息，评估器将使用统计信息来估算选择性。例如：对于一个等式谓词（last_name='Smith'），选择性的值是distinct last_name的倒数即：（1/count(distinct last_name)）。但是如果在last_name字段上存在直方图（histogram），则选择性值为：count（last_name）where last_name='Smith' / count（last_name）where last_name is not null。可见在数据倾斜的字段上应用直方图能够帮助CBO进行准确的选择性评估。

基数

基数就是行集中行的数量。基数分为：

l 基础基数（Base cardinality）：就是基表中的行数。基础基数在表分析期间获得。如果表没有可用的统计信息，则评估器利用表中区（extents）的数量来估算基础基数。

l 有效基数（Effective cardinality）：就是从基表中选择的行数。有效基数与具体的谓词和字段有关。有效基数是根据基础基数和作用于该表的所有谓词的选择性得出的，如果没有谓词作用于该表，则有效基数就等于基础基数。

l 连接基数（Join cardinality）：就是两个行集在连接之后产生的行数。连接就是由两个行集产生的笛卡尔积，再由连接谓词过滤结果。因此，连接基数是两个行集基数与连接谓词选择性的乘积。

l Distinct基数（Distinct cardinality）：就是一个行集的字段distinct之后的行数。一个行集的distinct基数是基于字段中的数据的。例如：一个拥有100行的行集，如果一个字段distinct之后还剩下20行，则distinct基数就为20。

l Group基数（Group cardinality）：就是一个行集在应用GROUP BY之后产生行的数量。Group基数依赖于每个组中字段的distinct基数和行集的行数。

GROUP基数例子：

假如对一个有100行的行集group by colx，colx字段的distinct基数是30，则Group基数为30。但是如果group by colx,coly呢？coly字段的distinct基数是60，这种情况下Group基数大于max(colx distinct基数，coly distinct基数)，而小于min(colx distinct基数*coly distinct基数，行集的行数)，用公式表示出来如下：

group cardinality lies between

max ( dist. card. colx , dist. card. coly )

and

min ( (dist. card. colx * dist. card. coly) , num rows in row set )

对于上面的例子Group基数大于max（30，60）而小于min（30*60，100），也就是Group基数位于60和100之间。

成本

成本是用来描述工作单元或资源使用的。CBO是用磁盘I/O、CPU和内存的使用情况来作为工作单元的，因此CBO使用的成本可以描述为，在一次操作的执行过程中所用的磁盘I/O数量以及CPU和内存的总使用量。这里的操作可以是扫描一张表、通过索引访问表、连接两个表、或者一个行集的排序。一个查询计划的成本就是运行这个查询并产生结果的同时需要的工作单元的数量。

访问路径（access path）决定着在基表中获得数据所需要的工作单元数量。访问路径可以是表扫描（table scan）、快速全索引扫描（fast full index scan）、索引扫描（index scan）等。在表扫描或快速全索引扫描期间，多个块可以在一次I/O中获得，因此表扫描或快速全索引扫描的成本依赖于被扫描的块数和多块读取的数量。索引扫描的成本依赖于B树的深度、被扫描的索引页块数量、和用ROWID获取的行数，使用ROWID获取行的成本倚赖于索引聚集因子（clustering factor）。

尽管聚集因子是索引的一个属性，它实际也关系到表数据块中被索引的字段值。一较低的聚集因子表明行被集中在表的少数块里，相反一个较高的聚集因子表明行被随机分散到表的数据块中。因此，聚集因子过高意味着通过范围扫描用ROWID获取行成本会较高，因为需要访问表中过多的块才能返回数据。

聚集因子对成本的影响

假设环境如下：

l 一个表有9行数据。

l 在col1上有一个非唯一索引

l distinct col1值是A、B、C

l 这个表占据三个Oracle块

第一种情况：索引聚集因子低，如下图：

Block 1 Block 2 Block 3
------- ------- --------
A A A B B B C C C
索引字段相同的值都在同一个物理块中，这种情况下做范围扫描返回col1=A的所有的行成本就很低，因为只需要在表中读取一个块就可以返回数据。

第二种情况：索引聚集因子高，如下图：

Block 1 Block 2 Block 3
------- ------- --------
A B C A B C A B C
索引字段相同的值被分散存储到表中的块，这时要得到col1=A的行则要读取三个块。

联合单独访问两个表的成本就是连接的成本，在一个连接中分为内行集和外行集。

l 嵌套循环连接（nested loop join）：对于外行集中的每一行都要在内行集寻找全部与它匹配的行，然后连接。因此，在嵌套循环连接中外行集有多少行，内行集就被访问多少次。成本计算公式如下：

cost = outer access cost + (inner access cost * outer cardinality)

l 排序合并连接（sort merge join）：如果两个行集的连接键是无序的，则进行排序。成本计算公式如下：

cost = outer access cost + inner access cost + sort costs (if sort is used)

l 散列连接（hash join）：内部行集被散列到内存中，并用连接键建立一个散列表，然后探测外部行集并连接与之匹配的行。如果内部行集非常大，则只会把一部分散列到内存中，这叫做一个散列分区。此时，内存中的散列分区探测外部行集并连接所有匹配的行，重复这个过程直到用完内部行集的所有分区。成本计算公式如下：

cost = (outer access cost * num of hash partitions) + inner access cost

计划生成器

由于不同的访问路径、连接方式和连接顺序可以任意组合，以不同的方式访问和处理数据，但可以产生同样的结果，因此一个SQL可能存在大量不同的计划。计划生成器的主要作用正是为查询试验出所有这些可能存在的计划，并选择一个其中成本最低的。

连接顺序就是不同的连接项（如，表）以一定的顺序被访问和连接在一起。例如：有一个连接按照t1、t2、t3的顺序，则t1是第一个被访问的，然后是t2，访问t2的同时与t1做连接并产生连接后的结果，最后t3被访问，t3的数据与t1和t2产生的中间结果做连接。

在建立一个查询的计划之前要先为每个被嵌套的子查询和未合并的视图建立子计划，每个嵌套的子查询和未合并的视图都是独立的查询块，这些查询块以自底向上的顺序进行优化，也就是最里层的查询块最先优化并产生子计划，最外层的查询块最后优化。

计划生成器通过试验不同的访问路径、连接方式和连接顺序去探测各种计划，对于一个查询来说可能存在的计划与FROM字句后面的连接项是成比例的，并以指数增长。然而实际上计划生成器很少会试验所有的可能存在的计划，如果它发现当前计划的成本已经很低了，它将停止试验，相反当前计划的成本如果很高它将继续试验其他计划，因此如果计划生成器一开始就能够找到一个成本较低的计划则会大量减少时间，计划生成器通常按照连接项有效基数由小到大的顺序排列初使连接,。

理解执行计划

Oracle用来运行一个语句的步骤就叫做执行计划（execution plan），执行计划包含了语句所涉及的每个表的访问路径和连接顺序。

执行计划概述

使用EXPLAIN PLAN语句可以查看优化器所选择的执行计划，下面看一个例子：

1、创建PLAN_TABLE，用来存放执行计划的描述信息：

CONNECT HR/your_password
@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL

Table created.

当然你也可以改变PLAN_TABLE的名字。

注意：

Oracle公司建议你在做完数据库版本升级之后删除PLAN_TABLE然后再重建，因为字段可能会有所变化，这可能会导致脚本失效或TKPROF失效。

当然你也可以改变PLAN_TABLE的名字。

2、运行执行计划：

EXPLAIN PLAN FOR
SELECT e.employee_id, j.job_title, e.salary, d.department_name
FROM employees e, jobs j, departments d
WHERE e.employee_id < 103
AND e.job_id = j.job_id
AND e.department_id = d.department_id;

用下面的语句可以指定PLAN_TABLE的名字：

EXPLAIN PLAN
INTO my_plan_table
FOR
YOUR_SQL;

3、显示执行计划信息：

这里可以用以下两个脚本

UTLXPLS.SQL - 显示计划表信息，以串行的方式处理。

UTLXPLP.SQL - 显示计划表信息，以并行的方式处理。

@$ORACLE_HOME/rdbms/utlxplp.sql;

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)|
| 1 | NESTED LOOPS | | 3 | 189 | 10 (10)|
| 2 | NESTED LOOPS | | 3 | 141 | 7 (15)|
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)|
| 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)|
|* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)|
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPLOYEE_ID"<103)
5 - access("E"."JOB_ID"="J"."JOB_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

SQL Scratchpad的图形界面显示：

该图中每个图标左上角的数字就是执行的顺序号，利用图形工具很容易看出执行计划的执行顺序。 但是很多情况下我们没用配置图形工具的环境，而且图形工具消耗资源，也不太稳定，很多人也不太喜欢用图形工具。如果利用上面运行脚本的方法在sqlplus中获得执行计划，对于新手来说不容易看出执行的顺序，通常我们用下面的方法来获得执行计划：

1、conn /as sysdba;

2、@$ORACLE_HOME/sqlplus/admin/plustrce.sql

3、grant plustrace to public

4、@$ORACLE_HOME/rdbms/admin/utlxplan.sql

5、create public synonym plan_table for plan_table;

6、grant all on plan_table to public ;

-------------------------------------------------------------------

以上步骤只需配置一次即可。

7、conn username/password;

8、set autotrace traceonly（如果想看到结果集：set autotrace on，关闭：set autotrace off）

9、set timing on（如果想同时看到语句执行的时间）

10、运行你的SQL

关于如何看执行计划的顺序，请看下面的例子：

set autotrace traceonly

select ename,dname
from emp, dept
where emp.deptno=dept.deptno
and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');

15 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248)
1 0 HASH JOIN (Cost=3 Card=8 Bytes=248)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)
3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)

用这种方法产生的执行计划会有两列数字，第一列是statement_id，第二列是child_id。我们可以从statement_id=0处开始读，当该句有子句则先执行子句，该句就为其子句的父句；如果该句有多个子句，则子句的执行顺序是从上到下；子句执行完执行父句。

在这个例子中，statement_id=0有一个子句statement_id=1，因此在执行statement_id=0之前要执行statement_id=1，但statement_id=1有两个子句分别为statement_id=2和3，因此这两句要最先执行。此执行计划的执行顺序为：2——>3——>1——>0

后面的cost说明该SQL是基于成本优化的，如果没有cost则是基于规则的。

通过这个例子可以知道学会看执行计划也不是什么难事，关键是要理解执行计划，学会如何优化执行计划，下面将继续讨论。

理解CBO访问路径

访问路径就是从数据库中检索数据的方式。通常来说，检索一个表中少量的数据行应该使用索引访问，但是检索大量数据时全表扫描可能优于索引。
全表扫描（Full Table Scans）

全表扫描将读取HWM之下的所有数据块，访问表中的所有行，每一行都要经WHERE子句判断是否满足检索条件。当Oracle执行全表扫描时会按顺序读取每个块且只读一次，因此如果能够一次读取多个数据块，可以提高扫描效率，初始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取数据块的最大数量。

优化器何时会使用全表扫描

在以下情况中优化器会使用全表扫描：

1、无可用索引

如下面例子：
SELECT last_name, first_name
FROM employees
WHERE UPPER(last_name)='TOM'
last_name字段有索引，但在查询中使用了函数，因此该查询不会使用索引。如果想让这个查询走索引，则需要建立函数索引create index ind_upper_lastname on last_name (upper(last_name))。特别要注意的是隐式转换，比如colx字段是varchar2型但存放数字：where colx=123456，这时会发生隐式转换TO_NUMBER(colx)，此时colx上的索引也会失效。

2、大量数据

如果优化器认为查询将会访问表中绝大多数的数据块，此时就算索引是可用的也会使用全表扫描。

3、小表

如果一个表HWM之下的数据块比DB_FILE_MULTIBLOCK_READ_COUNT要少，只需要一次I/O就能扫完，则使用全表扫描要比使用索引的成本低，此时会使用全表扫描。
如果有这样小表访问频率又高，通常把它固定在内存中为好alter table table_name storage(buffer_pool keep)。

4、并行

如果在表一级设置了较高的并行度，如alter table table_name parallel(degree 10)，通常会使CBO错误的选择全表扫描。通常不建议在表级的设置并行。
并行查询通常可以提高全表扫描的性能，建议在语句级用HINTS来实现并行，如/*+full(table_name) parallel(table_name degree)*/。

5、全表扫描hints

如果想强制优化器使用全表扫描可以用提示FULL。

I/O是针对数据块的而不是行

Oracle的I/O是针对数据块的，因此被访问的数据块所占的百分比将影响CBO是否选择全表扫描。通常一个数据块中存储着多条记录，被请求的记录要么聚集在少数几个块中，要么分散在大量的数据块中。

HWM(High Water Mark)

HWM是全表扫描范围的标记，每个全表扫描都要读到HWM位置。当表analyze之后可以在DBA_TABLES.BLOCKS查到HWM，当表被drop、truncate或者move之后，HWM将会被重置。需要注意的是，当一个表被大量删除记录之后，HWM下面的大量数据块是空的，此时若对此表进行全表扫描，Oracle仍然会读到HWM位置，会对全表扫描的性能产生极坏的影响。

Rowid扫描

Rowid就是一个记录在数据块中的位置，由于指定了记录在数据库中的精确位置，因此rowid是检索单条记录的最快方式。
如果通过rowid来访问表，Oracle首先需要获得被检索记录的rowid，Oracle可以在WHERE子句中得到rowid，但更多的是通过索引扫描来获得，然后Oracle基于rowid来定位被检索的每条记录。

优化器何时使用Rowid

并不是每个索引扫描都伴随着rowid的访问，如果索引中包含了被访问的所有字段，则不再需要通过rowid来访问表。

注意：

Rowid是Oracle表示数据存储的内部方法，它可能会由于版本的改变而改变。不推荐通过在WHERE中指定rowid来访问数据，因为行迁移和行链接会导致rowid变化，exp/imp也会使rowid变化。

索引扫描

索引不仅包含被索引字段的值，还包含表中行的位置标识rowid，如果语句只检索索引字段，Oracle直接从索引中读取该值而不去访问表，如果语句通过索引检索其他字段值，则Oracle通过rowid访问表中记录。

索引扫描类型：

索引唯一扫描（Index Unique Scans）
索引范围扫描（Index Range Scans）
索引降序范围扫描（Index Range Scans Descending）
索引跳跃扫描（Index Skip Scans）
全索引扫描（Full Scans）
快速全索引扫描（Fast Full Index Scans）
索引连接（Index Joins）
位图连接（Bitmap Joins）

1、索引唯一扫描

这种扫描通常发生在对一个主键字段或含有唯一约束的字段指定相等条件时，只有单行记录被访问。

2、索引范围扫描

索引范围扫描是检索数据的常用方式，返回的数据返照索引字段升序排列，字段值相同的则按照rowid升序排列。如果在语句中指定了order by字句，而且排序字段是索引字段时Oracle将忽略order by子句。

例如：
SQL> select * from t;
COLX COLY
--------------- ---------------
1 3
1 2
1 1
1 0
SQL> create index ind_t on t(coly);
SQL> set autotrace on
SQL> select * from t where coly>0;
COLX COLY
--------------- ---------------
1 1
1 2
1 3
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)

没有使用order by结果集已经是按coly升序排列的。
SQL> set autotrace traceonly
SQL> select * from t where coly>0 order by coly;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)
可以看到执行计划中无SORT 步骤，说明Oracle忽略了order by子句。

3、索引降序范围扫描

如果在order by中指定了索引是降序排列的，或者使用了index_desc提示，Oracle可能会使用索引降序范围扫描。
例如：
SQL> select /*+index_desc(t ind_t)*/colx,coly from t where coly<3;
COLX COLY
--------------- ---------------
1 2
1 1
1 0
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=104)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=4 Bytes=104)
2 1 INDEX (RANGE SCAN DESCENDING) OF 'IND_T' (NON-UNIQUE) (Cost=2 Card=1)

4、索引跳跃式扫描
跳跃式扫描发生在复合索引中，它在逻辑上将索引分离为较小的子索引，当复合索引的某一个字段不在查询中指定时，它将被跳过，从而提高索引扫描的效率。可以使用index_ss提示强制使用跳跃扫描。

举个例子：
SQL> select* from employees;

SEX EMPLOYEE_ID ADDRESS
---- --------------- --------------------
F 98 ABC
F 100 ABC
F 102 ABC
F 104 ABC
M 101 ABC
M 103 ABC
M 105 ABC
SQL> create index ind_sex_empid on employees(sex,employee_id);
索引结构如下图所示：

SQL>set autotrace traceonly
SQL>select/*+index_ss(employees ind_sex_empid)*/* from employees where employee_id=101;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=3 Card=1 Bytes=11)
2 1 INDEX (SKIP SCAN) OF 'IND_SEX_EMPID' (NON-UNIQUE) (Cost=2 Card=1)

5、全索引扫描

如果要使用全索引扫描必须满足两个条件，一是查询涉及的字段都包含在索引中，二是至少一个索引字段具有非空属性。由于索引键的数据是有序的，因此全索引扫描可以用消除排序操作。全索引扫描只需要一次I/O。
select empno,ename from big_emp order by empno,ename;

Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)

1 0 INDEX (FULL SCAN) OF ‘BE_IX' (Cost=2 Card=1)
6、快速全索引扫描

快速全索引扫描只访问索引本身，而不去访问表，因此只有查询涉及的字段都包含在索引中时才会使用快速全索引扫描。如果想使用快速全索引扫描查询所涉及的字段必须全部包含在索引中，而且索引中至少有一个字段具有非空属性。满足条件后可以使用index_ffs提示来强制使用快速全索引扫描，快速全索引扫描只适用于CBO。
快速全索引扫描并不能消除排序操作，因为索引键中的数据没有被排序。不同于全索引扫描，快速全索引扫描是通过多块读取的方式来读取整个索引的，并可以设置并行方式。

7、索引连接

只有查询涉及的所有字段都包含在索引中，才会使用索引连接，此时只通过访问索引就能获得所有需要的数据，而不用访问表。索引连接只适用于CBO，且不能消除排序操作。
可以通过index_join提示来强制使用索引连接。

8、位图连接

位图连接使用一个位图作为键，然后通过映射函数将比特位转换为rowid。只有Oracle9i企业版才支持位图索引和位图索引连接。
Sample Table Scans

Sample table scan是随机检索表中的数据，当FROM后面有SAMPLE或SAMPLE BLOCK子句时，会执行Sample table scan。
如：SELECT * FROM employees SAMPLE BLOCK (1);

CBO如何选择访问路径

CBO首先检查WHERE子句中的条件以及FROM子句，确定有哪些访问路径是可用的。然后CBO使用这个访问路径产生一组可能的执行计划，再通过索引、表的统计信息评估每个计划的成本，最后优化器选择成本最低的一个。

例1：
SELECT *
FROM employees
WHERE last_name = 'JACKSON';
如果last_name具有唯一约束或者主键约束，优化器了解到只有一行数据被返回，这种情况下查询具有很强的选择性，优化很可能走唯一索引扫描。

例2：
还是上面的语句，如果last_name不具有唯一约束或主键约束，优化器使用USER_TAB_COLUMNS.NUM_DISTINCT和USER_TABLES.NUM_ROWS的统计信息来评估查询的选择性，估算last_name为jackson的记录占了employees表的比例。

例3：
SELECT *
FROM employees
WHERE employee_id < 7500;
评估这个查询的选择性时优化器使用WHERE子句中的边界值7500和employee_id字段的USER_TAB_COLUMNS.HIGH_VALUE、USER_TAB_COLUMNS.LOW_VALUE，优化器假定在最小值和最大值之间employee_id是平均分布的，优化器确定值小于7500的百分比，然后把这个值作为这个查询的选择性。

例4：
SELECT *
FROM employees
WHERE employee_id < :e1;
优化器并不知道e1的值，绑定变量的值每次运行都可能不同，因此优化器不能使用前面的方法来评估含有绑定变量的查询的选择性，在这种情况下优化器会使用内部缺省值试探着估算一个选择性。

例5：
SELECT *
FROM employees
WHERE employee_id BETWEEN :low_e AND :high_e;
优化器会将这句改写为：
employee_id >= :low_e
employee_id <= :high_e
然后优化器仍然是用内部缺省值来试探着为其评估一个选择性。

例6：
SELECT *
FROM employees
WHERE employee_id BETWEEN 7500 AND 7800;
优化器会改写为：
employee_id >= 7500
employee_id <= 7800
优化器为每个条件独立的评估选择性（S1和S2），然后用下列公式计算BETWEEN的选择性：S=ABS（S1+S2-1）

理解连接

CBO如何运行连接语句

为一个连接语句选择一个执行计划，优化器必须做出下列相关决策：
1、 访问路径

优化器必须给连接语句中的每个表选择一个可用来检索数据的路径。

2、连接方法

Oracle必须为每对行源执行连接操作，连接的方法包括嵌套循环、排序合并、散列连接、笛卡尔积等。

3、连接顺序

如果需要连接的表多于两个，Oracle先连接其中两个表然后将其连接的结果与下一个表做连接，直到所有的表都被连接。

CBO如何选择连接方法

优化器评估每个连接方法的成本，然后选择成本最低的一个。如果一个返回多行的连接，优化器将考虑如下三个因素：
当返回大量的结果集（大于1万行），嵌套循环连接是效率很低的，优化器可能不会选择它。嵌套循环的成本主要在把外表中所有被选择的行与内表匹配的过程，CBO的连接顺序可以用ORDERED提示来改变。嵌套循环连接的成本计算公式：
cost = access cost of A + (access cost of B * number of rows from A)
如果你使用CBO，当返回大量结果集时使用散列连接效率是非常高的。散列连接的成本计算公式：
cost = (access cost of A * number of hash partitions of B) + access cost of B
如果你使用RBO，当返回大量结果集时排序合并连接的效率比较高。排序合并连接的成本主要在于把所有行源读到内存中，进行排序的过程，多块读取对排序合并连接会有所帮助。排序合并连接的成本计算公式：
cost = access cost of A + access cost of B + (sort cost of A + sort cost of B)
当数据是预先排序的，后面两个排序成本为0。

CBO如何运行Anti-joins

SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700);
优化器缺省是用嵌套循环来处理anti-joins的，但是如果使用了MERGE_AJ、HASH_AJ、NL_AJ提示，NOT IN能够被转换为一个排序合并或hash anti-join。

CBO如何运行Semi-joins

SELECT * FROM departments
WHERE EXISTS
(SELECT * FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500);
优化器缺省也是用嵌套循环来执行EXISTS的，也同样可以通过MERGE_SJ、HASH_SJ、NL_SJ提示来调整。通常建议把NOT IN用EXISTS来改写，但是当NOT IN的子查询中包含OR分支时不能转为EXISTS。

嵌套循环连接

对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1万不适合），要把返回子集较小表的作为外表（CBO默认外表是驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED提示来改变CBO默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO执行嵌套循环连接。

散列连接

散列连接是CBO做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O的性能。
也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散列连接HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY为AUTO，然后调整PGA_AGGREGATE_TARGET即可。

排序合并连接

通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接。
以下情况Oracle可能会选择使用排序合并连接：
l 两个表做非等值连接
l OPTIMIZER_MODE被设置成RULE
l HASH_JOIN_ENABLE设置成FALSE
l 已经事先排过序，优化器认为使用排序合并连接的成本要比散列连接低。
l HASH_AREA_SIZE和SORT_AREA_SIZE设置太小，优化器认为散列连接成本过高。

外连接

不论是嵌套循环外连接还是散列外连接，CBO不会根据成本去选择连接顺序，被驱动的表总是含有（+）的一方。
SQL> select /*+ordered use_nl(t1 t2)*/ t1.msisdn,t2.msisdn from t1,t2 where t1.msisdn(+)=t2.msisdn;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=100 Card=99 Bytes=2178)
1 0 NESTED LOOPS (OUTER) (Cost=100 Card=99 Bytes=2178)
2 1 INDEX (FULL SCAN) OF 'IND_T2' (NON-UNIQUE) (Cost=1 Card=99 Bytes=1089)
3 1 INDEX (RANGE SCAN) OF 'IND_T1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=11)

虽然使用了ORDERED提示，试图以t1作为驱动表，可是由于是外连接，仍然是以t2作为驱动表。
换成散列连接也是一样：
SQL> select /*+ordered use_hash(t1 t2)*/ t1.msisdn,t2.msisdn from t1,t2 where t1.msisdn(+)=t2.msisdn;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=99 Bytes=2178)
1 0 HASH JOIN (OUTER) (Cost=4 Card=99 Bytes=2178)
2 1 INDEX (FULL SCAN) OF 'IND_T2' (NON-UNIQUE) (Cost=1 Card=99 Bytes=1089)
3 1 INDEX (FAST FULL SCAN) OF 'IND_T1' (NON-UNIQUE) (Cost=2 Card=999 Bytes=10989)
此时，外表为t1，内表为t2，连接保留t1表与t2不匹配的行，然后用t1构建散列表，最后由t2表去探测t1生成的散列表。

Full Outer Joins

select t1.msisdn,t2.msisdn
from t1
full outer join t2
on t1.msisdn=t2.msisdn
order by t2.msisdn

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1000 Bytes=36000)
1 0 SORT (ORDER BY) (Cost=15 Card=1000 Bytes=36000)
2 1 VIEW (Cost=6 Card=1000 Bytes=36000)
3 2 UNION-ALL
4 3 NESTED LOOPS (OUTER) (Cost=2 Card=999 Bytes=21978)
5 4 INDEX (FAST FULL SCAN) OF 'IND_T1' (NON-UNIQUE) (Cost=2 Card=999 Bytes=10989)
6 4 INDEX (RANGE SCAN) OF 'IND_T2' (NON-UNIQUE)
7 3 HASH JOIN (ANTI) (Cost=4 Card=1 Bytes=22)
8 7 INDEX (FULL SCAN) OF 'IND_T2' (NON-UNIQUE) (Cost=1 Card=99 Bytes=1089)
9 7 INDEX (FAST FULL SCAN) OF 'IND_T1' (NON-UNIQUE) (Cost=2 Card=999 Bytes=10989)
从执行计划看，实际上就是做了两个外连接，一个是t1.msisdn=t2.msisdn(+)走嵌套循环，一个是t1.msisdn(+)=t2.msisdn走散列连接，然后再UNION-ALL两个行集。
结果像下面这样：

T1.MSISDN T2.MSISDN
------------- -----------
10 10
20 201
20 202
30 114
30 115
30 116
...
270
280
178
207

一些和优化器相关的初始化参数

1、OPTIMIZER_FEATURES_ENABLE
每个版本的Oracle优化器特性都不相同，特别是做了版本升级以后一定要修改这个参数才可以使用仅被该版本支持的优化器特性。
可以赋予它的值如：9.2.0、9.0.2、9.0.1、8.1.7、8.1.6等。

2、CURSOR_SHARING
这个参数会将SQL语句中的常量用变量来替换，存在大量常量的OLTP系统可以考虑启用这个参数。但是有一点要明白，绑定变量虽然可以使大量的SQL重用，减少分析时间，但是当数据分布发生变化后，CBO为绑定变量SQL生成的执行计划可能不是最优（不会考虑具体的变量值）。通常OLTP系统适用于绑定变量，OLTP系统特点是，SQL运行频繁且时间相对较短，SQL的分析时间比重较大。如果在DSS系统中，SQL运行时间长，相比之下分析时间微不足道，好的执行计划才是最重要的，因此DSS系统绑定变量要另行考虑。

3、HASH_AREA_SIZE
这是散列连接时散列表的存放区域，如果使用散列连接这个参数值不能过小，如果散列表不能完全存放于内存中，对散列连接性能的影响很大。如果是9i建议启动工作区自动管理，然后设置PGA_AGGREGATE_TARGET。

4、SORT_AREA_SIZE
内存排序区的大小，如果排序时内存区不够会写如磁盘。9i同样建议启动工作区自动管理，然后设置PGA_AGGREGATE_TARGET。

5、HASH_JOIN_ENABLED
如果启用这个参数，CBO在考虑连接方法的时候将会考虑散列连接。

6、OPTIMIZER_INDEX_CACHING
这个参数表示被缓存的索引块所占百分比，可选值的范围是0-100。这个值会影响嵌套循环连接，如果这个值设得较高，CBO将更倾向使用嵌套循环。

7、OPTIMIZER_INDEX_COST_ADJ
优化器利用这个参数（是个百分比）把索引扫描的成本转换为等价的全表扫描的成本，然后与全表扫描的成本进行比较。缺省值100，表示索引扫描成本与全表扫描成本等价。可选值范围是0-10000。

8、OPTIMIZER_MAX_PERMUTATIONS
这个初始参数用来设定优化器最多考虑多少种连接顺序，优化器不断的产生可能的表的连接的排列，直到排列数达到参数optimizer_max_permutations为止。一旦优化器停止产生新的可能连接排列，它将会从中选择出成本最小的排列。

9、DB_FILE_MULTIBLOCK_READ_COUNT
这个参数表示在全表扫描或索引快速全扫描时一次I/O读的连续数据块数量（block#连续，且一次I/O不能超过extent）。

10、OPTIMIZER_MODE
优化器模式，也是优化器的优化目标。值为：RULE、CHOOSE、ALL_ROWS、FIRST_ROWS_n、FIRST_ROWS。

11、PARTITION_VIEW_ENABLED
如果设置为TRUE, 该优化器将跳过分区视图中未被请求的分区，该参数还能更改基于成本的优化程序从基础表统计信息计算分区视图统计信息的方式。

12、QUERY_REWRITE_ENABLE
如果设置为TRUE，优化器将利用可用的物化视图来重写SQL。

查找所有user1下的index
select * from user_indexes;

重建index
alter index myindex rebuild;

用动态sql产生alter语句：
spool e:\doc\sql\index.sql;
select 'alter index '|| index_name ||' rebuild;' from user_indexes;
spool off

@e:\doc\sql\index.sql;

select a.object_name objectname,b.session_id,c.serial#,c.program program,c.username username,c.command,c.machine machine,c.lockwait from all_objects a,v$locked_object b,v$session c where a.object_id =b.object_id and c.sid=b.session_id

今天，一个平时没多少访问量的站点数据库连接池在几分钟内爆满，导致不能正常访问

# netstat

tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:3024 TIME_WAIT

tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:3034 TIME_WAIT

tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:4342 TIME_WAIT

tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:1524 TIME_WAIT

……

netstat发现大量类似的连接信息，特征是：
1、都来自123.114.11.31
2、都处于TIME_WAIT状态
3、每个端口都不同，在1000-5000间浮动

用netstat -p查看连接的pid，再用ps -aux，看到pid对应是resin的进程

怀疑遭到攻击

用iptables封掉该ip
# /sbin/iptables -A INPUT -s 123.114.17.31 -j REJECT

再用netstat查看

tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:1626 LAST_ACK -tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:1115 LAST_ACK - tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:2139 LAST_ACK -tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:2118 LAST_ACK - tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:1606 LAST_ACK - tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:4934 LAST_ACK - tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:1092 LAST_ACK - tcp 1 11209 HOST.localdomai:http ::ffff:123.114.17.31:1356 LAST_ACK -

所有的TIME_WAIT变成了LAST_ACK,同时数据库连接池也恢复正常，网站恢复访问。