ORACLE性能优化之SQL优化-优化器
Oracle9i优化器介绍
By Davis
E-Mail:todavis@https://www.360docs.net/doc/aa11898458.html,
Blog:https://www.360docs.net/doc/aa11898458.html,
选择合适的优化器目标
默认情况下，CBO 以最佳吞吐量为目标，这意味着Oracle 使用尽可能少的资源去处理被语句访问到的所有行；当然CBO 也可以用最快的响应速度来优化SQL，这意味着Oracle
用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句
来说可能消耗更多的资源。
优化器产生的执行计划会因―优化器目标‖的不同而不同。如果以最佳吞吐量为目标，
结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。
例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。
选择优化器目标要以你的应用为基础，一般规则是：
1、对于批处理应用，以最佳吞吐量为优化目标为好。例如Oracle 报表应用程序。
2、对于交互式应用，以最快响应速度为优化目标为好。例如SQLPLUS 的查询。
影响优化器优化目标的因素主要有：
1、OPTIMIZER_MODE 初始化参数。
2、数据字典中的CBO 统计数据。
3、用来改变CBO 优化目标的Hints。
OPTIMIZER_MODE初始化参数
这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下：
Value CHOOSE
ALL_ROWS
Description
此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。
1、如果在被访问的表中，至少有一个表在数据字典中有可用的统计
信息存在，则优化器使用基于成本的方法。
2、如果在被访问的表中，只有部分表在数据字典中有可用的统计信
息，优化器仍然会使用基于成本的方法，但是优化器必须为无统
计信息的表利用一些内部信息去尝试其他的统计，比如分配给这
些表的数据块的数量等，这可能会导致产生不理想的执行计划。
3、如果在被访问的表中，没有一个表在数据字典中有统计信息，则
优化器使用基于规则的方法。
不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞
1
你可以在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 初始化参数。
● FIRST_ROWS(n)，n 为任意正整数。
● FIRST_ROWS
● ALL_ROWS
● CHOOSE
● RULE
数据字典中的CBO统计信息
CBO 使用的统计信息存放于数据字典中，你可以使用DBMS_STATS 包或ANALYZE
语句以精确的方式或估算的方式来统计对象的物理存储特征和数据分布情况。
注意：
Oracle 公司建议使用DBMS_STATS 包来代替ANALYZE 语句收集统计信息。
DBMS_STATS 包可以并行的收集统计信息，可以为分区对象收集全局统计信息，以及使用
其他方式优化收集操作。
但是，收集和基于成本优化器无关的信息必须用ANALYZE 而不是DBMS_STATS，比
如：
● 使用VALIDATE 或LIST CHAINED ROWS 子句。
● 收集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 包含下列组件：
● 查询变换器（Query Transformer）
● 评估器（Estimator）
● 计划生成器（Plan Generator）
如下图所示：
（基于成本的优化器组件）
查询变换器
被解析器解析过的查询语句进入查询变换器，表现出来的是一组查询块(query block)，这些查询块之间是相互关联的或者是嵌套的，查询的形式决定这些查询块相互之间如何被关联。查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。查询变换器使用四种不同的查询变换技术：
● 视图合并（View Merging）
● 谓词推进（Predicate Pushing）
● 非嵌套子查询（Subquery Unnesting）
● 物化视图的查询重写（Query Rewrite with Materialized Views）
最终应用于查询的也可以是以上四种变换技术的任意组合。
视图合并
查询中的每个视图都会被解析器扩展到一个独立的查询块中，这个查询块本质上是用来
描述视图定义的，是视图的结果。优化器的一个任务就是去分析这个独立视图查询块(view query block)并产生一个视图子计划(subplan)，然后优化器在产生整个查询执行计划的同时使
用视图子计划来处理剩余的查询部分。由于视图是被独立在整个查询之外被优化的，因此这种技术常常会导致一个不良执行计划的产生。
查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。绝大多数类
型的视图是可以被合并的。在一个视图被合并后，它原有的视图查询块被包含到查询块中，也就是说视图查询块不存在了，因此也不再需要产生一个子计划。
谓词推进
对于那些不能合并的视图，查询变换器能够将相关的谓词从查询块中推进到视图查询块中。由于被推进的谓词能够用来访问索引或者用于过滤，这个技术通常可以改进那些不能被
合并的视图子计划。
非嵌套的子查询
和视图一样，子查询也是用一个独立的查询块来代表的。子查询是被嵌套在主查询或其
他子查询之中的，计划产生器在找到一个成本最低的执行计划之前被迫要试验所有可能的计
划。由嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除，经由查询转换
器过滤之后绝大多数的子查询都会被转换为非嵌套的，然后这些非嵌套的子查询产生独立的
子计划，这些子计划按照一种高效的方式进行排列，从而提高了整个查询计划的执行速度。
物化视图的查询重写
物化视图就是把一个查询的结果事先固化存储在一个表里，当发现和物化视图一致的查
询语句就将相应的项用物化视图来重写。由于绝大多数的查询结果都事先计算好了，因此这
种技术可以极大的提高查询速度。查询转换器负责查找和用户查询相关的所有物化视图，用
其中的一个或多个来重写查询。利用物化视图来重写查询也是基于成本的，如果不使用物化
视图的成本更低一些，则不会去使用物化视图。
评估器
评估器会产生下列三个度量值：
● 选择性（Selectivity）
● 基数（Cardinality）
● 成本（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）where last_name=‘Smith‘ / count（last_name）where last_name is not null。可见
在数据倾斜的字段上应用直方图能够帮助CBO 进行准确的选择性评估。
基数
基数就是行集中行的数量。基数分为：
● 基础基数（Base cardinality）：就是基表中的行数。基础基数在表分析期间获得。如
果表没有可用的统计信息，则评估器利用表中区（extents）的数量来估算基础基数。
● 有效基数（Effective cardinality）：就是从基表中选择的行数。有效基数与具体的谓
词和字段有关。有效基数是根据基础基数和作用于该表的所有谓词的选择性得出
的，如果没有谓词作用于该表，则有效基数就等于基础基数。
● 连接基数（Join cardinality）：就是两个行集在连接之后产生的行数。连接就是由两
个行集产生的笛卡尔积，再由连接谓词过滤结果。因此，连接基数是两个行集基数
与连接谓词选择性的乘积。
● Distinct 基数（Distinct cardinality）：就是一个行集的字段distinct 之后的行数。一个
行集的distinct 基数是基于字段中的数据的。例如：一个拥有100 行的行集，如果
一个字段distinct 之后还剩下20 行，则distinct 基数就为20。
● 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 获取行成本会较高，因为需要访问表中过多的块才能返回数据。
聚集因子对成本的影响
假设环境如下：
● 一个表有9 行数据。
● 在col1 上有一个非唯一索引
● distinct col1 值是A、B、C
● 这个表占据三个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 的行则要读取三个块。
联合单独访问两个表的成本就是连接的成本，在一个连接中分为内行集和外行集。
● 嵌套循环连接（nested loop join）：对于外行集中的每一行都要在内行集寻找全部与
它匹配的行，然后连接。因此，在嵌套循环连接中外行集有多少行，内行集就被访
问多少次。成本计算公式如下：
cost = outer access cost + (inner access cost * outer cardinality)
● 排序合并连接（sort merge join）：如果两个行集的连接键是无序的，则进行排序。
成本计算公式如下：
cost = outer access cost + inner access cost + sort costs (if sort is used)
● 散列连接（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 可以在
位被检索的每条记录。
优化器何时使用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)
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：
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 可能会选择使用排序合并连接：
● 两个表做非等值连接
● OPTIMIZER_MODE 被设置成RULE
● HASH_JOIN_ENABLE 设置成FALSE
● 已经事先排过序，优化器认为使用排序合并连接的成本要比散列连接低。
● 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
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 两个行集。
OracleSQL的优化
Oracle SQL的优化 标签：oraclesql优化date数据库subquery 2009-10-14 21:18 18149人阅读评论(21) 收藏举报分类： Oracle Basic Knowledge（208） SQL的优化应该从5个方面进行调整： 1.去掉不必要的大型表的全表扫描 2.缓存小型表的全表扫描 3.检验优化索引的使用 4.检验优化的连接技术 5.尽可能减少执行计划的Cost SQL语句： 是对数据库(数据)进行操作的惟一途径； 消耗了70%~90%的数据库资源；独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低； 可以有不同的写法；易学，难精通。 SQL优化： 固定的SQL书写习惯，相同的查询尽量保持相同，存储过程的效率较高。 应该编写与其格式一致的语句，包括字母的大小写、标点符号、换行的位置等都要一致 ORACLE优化器： 在任何可能的时候都会对表达式进行评估，并且把特定的语法结构转换成等价的结构，这么做的原因是 要么结果表达式能够比源表达式具有更快的速度 要么源表达式只是结果表达式的一个等价语义结构 不同的SQL结构有时具有同样的操作（例如： = ANY (subquery) and IN (subquery)），ORACLE会把他们映射到一个单一的语义结构。 1 常量优化： 常量的计算是在语句被优化时一次性完成，而不是在每次执行时。下面是检索月薪大于2000的的表达式： sal > 24000/12
sal > 2000 sal*12 > 24000 如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。 优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal上有索引，第一和第二就可以使用，第三就难以使用。 2 操作符优化： 优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。 例如：优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH' 优化器只能转换涉及到可变长数据类型的表达式，前一个例子中，如果ENAME 字段的类型是CHAR(10)，那么优化器将不做任何转换。 一般来讲LIKE比较难以优化。 其中： ~~IN 操作符优化： 优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。 例如，优化器会把表达式ename IN ('SMITH','KING','JONES')替换为 ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘ oracle 会将 in 后面的东西生成一存中的临时表。然后进行查询。 如何编写高效的SQL: 当然要考虑sql常量的优化和操作符的优化啦，另外，还需要： 1 合理的索引设计： 例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：语句A SELECT count(*) FROM record WHERE date >'19991201' and date <'19991214‘and amount >2000 语句B
2020年(Oracle管理)如何优化SQL语句以提高Oracle执行效率
（Oracle管理）如何优化SQL语句以提高Oracle执 行效率
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)： Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表drivingtable)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表。 （2）WHERE子句中的连接顺序： Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。（3）SELECT子句中避免使用‘*’： Oracle在解析的过程中,会将‘*’依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。 （4）减少访问数据库的次数： Oracle在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等。（5）在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200。 （6）使用DECODE函数来减少处理时间： 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。 （7）整合简单,无关联的数据库访问： 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)。 （8）删除重复记录： 最高效的删除重复记录方法(因为使用了ROWID)例子：DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)
oraclesql优化笔记
基本的Sql 编写注意事项 尽量少用IN 操作符，基本上所有的IN 操作符都可以用EXISTS 代替。 不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。 Oracle 在执行IN 子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS:匕NOT IN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。 不用“<>”或者“ !=”操作符。对不等于操作符的处理会造成全表扫描，可以用“ <” or “>”代替。 Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL这样就可以用其他操作来取代判断NULL的操作。 当通配符“ %”或者“ _”作为查询字符串的第一个字符时，索引不会被使用。 对于有连接的列“ || ”，最后一个连接列索引会无效。尽量避 免连接，可以分开连接或者使用不作用在列上的函数替代。 如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。 Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。 对数据类型不同的列进行比较时，会使索引失效。
用“ >=”替代“ >”。 UNION操作符会对结果进行筛选，消除重复，数据量大的情况 下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。 Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。（只在采用RBO 优化时有效，下文详述） Order By 语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By 语句中使用表达式。 不同区域出现的相同的Sql 语句，要保证查询字符完全相同， 以利用SGA共享池，防止相同的Sql语句被多次分析。多利用内部函数提高Sql 效率。 当在Sql 语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。 需要注意的是，随着Oracle 的升级，查询优化器会自动对Sql 语句进行优化，某些限制可能在新版本的Oracle 下不再是问题。尤其是采用CBO （Cost-Based Optimization ，基于代价的优化方式）时。 我们可以总结一下可能引起全表扫描的操作：
OracleSQL性能优化方法
OracleSQL性能优化方法 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访咨询Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序． (6) 7SELECT子句中幸免使用’*’ (6) 8减少访咨询数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访咨询 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14运算记录条数 (9) 15用Where子句替换HA VING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11) 18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16)
1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以及储备参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存体会，从而更好地进行数据库的调优。 数据库性能调优的方法 ●调整内存 ●调整I/O ●调整资源的争用咨询题 ●调整操作系统参数 ●调整数据库的设计 ●调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建立分区一样以关键字为分区的标志，也能够以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时能够进行讲明： create table TABLENAME(
Oracle_SQL规范与优化
1.性能优化 ●【规则6】尽量避免相同语句由于书写格式的不同，而导致多次语法分析。 ●【规则7】尽量使用共享的SQL语句，也就是说，在SQL中尽量采用绑定变量的方式， 而不是常量； ●【规则8】尽量不使用“SELECT *”这样的语句，即使需要查询表中的所有行，也需列 出所有的字段名； ●【规则9】尽量避免4个以上表的链表操作，例如：A = B and B = C and C = D，如果业务 上需要，可以考虑通过中间表的方式进行变通； ●【规则9】大量的排序操作影响系统性能，所以尽量减少order by和group by排序操作。 如必须使用排序操作，请遵循如下规则： （1）排序尽量建立在有索引的列上。 （2）如结果集不需唯一，使用union all代替union。 ●【规则10】系统可能选择基于规则的优化器，所以将结果集返回数据量小的表作为驱 动表（from后边最后一个表）。 说明：驱动表的选择和很多的因素有关系，不仅仅是表的顺序，这点仅做参考，不过养成这个习惯有助于以后进行SQL的优化。 ●【规则11】索引的使用。 （1）尽量避免对索引列进行计算。 （2）尽量注意比较值与索引列数据类型的一致性，避免使用数据库的类型自动转换功能 （3）对于复合索引，SQL语句必须使用主索引列 （4）索引中，尽量避免使用NULL。 （5）对于索引的比较，尽量避免使用NOT=（!=） （6）查询列和排序列与索引列次序保持一致 ●【规则12】查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面。 ●【规则13】使用%TYPE、%ROWTYPE方式声明变量，使变量声明的类型与表中的保持同 步 ●【规则14】在IF/ELSE查询中，使用DECODE ●【规则15】在SQL 中使用WHERE 子句过滤数据，而不是在程序中到处使用它进行过 滤 ●【规则16】执行动态SQL，建议用execute immediate SQL子句； ●【规则17】尽量避免使用union，若需要排重，建议使用from 子句把查询结果union all 起来后，再通过group by 排重， 如： SELECT id FROM ( SELECT id FROM a UNION ALL SELECT id
Oracle中sql优化原则
Oracle中sql优化原则 公布时刻：2006.05.17 01:31来源：不详作者：kingshare 1。差不多检验的语句和已在共享池中的语句之间要完全一样 2。变量名称尽量一致 3。合理使用外联接 4。少用多层嵌套 5。多用并发 语句的优化步骤一样有： 1。调整sga区，使得sga区的是用最优。 2。sql语句本身的优化，工具有explain,sql trace等 3。数据库结构调整 4。项目结构调整 写语句的体会： 1。关于大表的查询使用索引 2、少用in,exist等 3、使用集合运算 １．关于大表查询中的列应尽量幸免进行诸如 Ｔｏ＿ｃｈａｒ，ｔｏ＿ｄａｔｅ，ｔｏ＿ｎｕｍｂｅｒ 等转换 ２．有索引的尽量用索引，有用到索引的条件写在前面 如有可能和有必要就建立一些索引 ３．尽量幸免进行全表扫描，限制条件尽可能多，以便更快 搜索到要查询的数据 如何让你的SQL运行得更快 交通银行长春分行电脑部 任亮 ---- 人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。笔者在工作实践中发觉，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个方面分不进行总结： ---- 为了更直观地讲明咨询题，所有实例中的SQL运行时刻均通过测试，不超过１秒的均表示为（< 1秒）。
---- 测试环境-- ---- 主机：HP LH II ---- 主频：330MHZ ---- 内存：128兆 ---- 操作系统：Operserver5.0.4 ----数据库：Sybase11.0.3 一、不合理的索引设计 ----例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情形： ---- 1.在date上建有一非个群集索引 select count(*) from record where date > 19991201 and date < 19991214and amount > 2000 (25秒) select date,sum(amount) from record group by date (55秒) select count(*) from record where date > 19990901 and place in (BJ,SH) (27秒) ---- 分析： ----date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范畴查找时，必须执行一次表扫描才能找到这一范畴内的全部行。 ---- 2.在date上的一个群集索引 select count(*) from record where date > 19991201 and date < 19991214 and amount > 2000 （14秒） select date,sum(amount) from record group by date （28秒） select count(*) from record where date > 19990901 and place in (BJ,SH)（14秒） ---- 分析： ---- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范畴查找时，能够先找到那个范畴的起末点，且只在那个范畴内扫描数据页，幸免了大范畴扫描，提高了查询速度。 ---- 3.在place，date，amount上的组合索引 ---- 4.在date，place，amount上的组合索引 ---- 5.总结： ---- 缺省情形下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立
ORACLE性能优化之SQL优化-优化器
Oracle9i优化器介绍 By Davis E-Mail:todavis@https://www.360docs.net/doc/aa11898458.html, Blog:https://www.360docs.net/doc/aa11898458.html, 选择合适的优化器目标 默认情况下，CBO 以最佳吞吐量为目标，这意味着Oracle 使用尽可能少的资源去处理被语句访问到的所有行；当然CBO 也可以用最快的响应速度来优化SQL，这意味着Oracle 用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句 来说可能消耗更多的资源。 优化器产生的执行计划会因―优化器目标‖的不同而不同。如果以最佳吞吐量为目标， 结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。 例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。 选择优化器目标要以你的应用为基础，一般规则是： 1、对于批处理应用，以最佳吞吐量为优化目标为好。例如Oracle 报表应用程序。 2、对于交互式应用，以最快响应速度为优化目标为好。例如SQLPLUS 的查询。 影响优化器优化目标的因素主要有： 1、OPTIMIZER_MODE 初始化参数。 2、数据字典中的CBO 统计数据。 3、用来改变CBO 优化目标的Hints。 OPTIMIZER_MODE初始化参数 这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下： Value CHOOSE ALL_ROWS Description 此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。 1、如果在被访问的表中，至少有一个表在数据字典中有可用的统计 信息存在，则优化器使用基于成本的方法。 2、如果在被访问的表中，只有部分表在数据字典中有可用的统计信 息，优化器仍然会使用基于成本的方法，但是优化器必须为无统 计信息的表利用一些内部信息去尝试其他的统计，比如分配给这 些表的数据块的数量等，这可能会导致产生不理想的执行计划。 3、如果在被访问的表中，没有一个表在数据字典中有统计信息，则 优化器使用基于规则的方法。 不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞 1
oracle sql性能优化题目
1.下面哪些是sql语句处理过程ABCD （A）分析（B）优化（C）行资源生成（D）执行 2.sql语句在分析过程中要进行哪些操作？ABC （A）语法分析（B）语义分析（C）如果是DML，还有共享池检查（D）优化 3.下面对索引的描述哪些是正确的ABCD （A）类似书的目录结构 （B）可以提高sql的查询速度 （C）会降低insert、update、delete的速度 （D）与所索引的表是互相独立的物理结构 （E）储存null 4.索引有哪几种扫描方式ABCDE （A）唯一索引扫描（B）索引范围扫（C）索引跳跃扫描（D）索引全扫描（E）索引快速扫描 5.下列哪些属于索引的类型：ABCD （A）B-tree索引（B）函数索引（C）全局索引（D）本地索引 6.下列对建立索引说法正确的是：AD （A）where后面的条件具备建立索引的先天条件 （B）索引的列越多越好 （C）所有的列都可以建立索引 （D）哪个列能快速定位数据，那么那个列就是建立索引的列 7.一般来说2张表连接有哪几种方式？ABC （A）NESTED LOOPS（B）HASH JOIN（C）SORT MERGE JOIN（D）FULL JOIN 8.对NESTED LOOPS表连接来说，下面哪些说法是正确的：AC （A）drivingrowsource(外部表)比较小 （B）只能用于等值连接中 （C）innerrowsource(内部表) 有高选择率的索引
（D）连接之前需要排序 9.sql 在数据库共享池中能否共享的说法哪些是正确的？ACD （A）sql必须是同一个用户执行的 （B）执行的sql不区分大小写 （C）执行的sql所处的当时的数据库环境必须是一样的 （D）同样的sql生成的HASH值一定是一样的 10.以下对绑定变量的说法正确的是：ABD （A）绑定变量能减少硬解析的次数 （B）绑定变量有的时候会引起执行计划的错误选择 （C）绑定变量不会带来性能问题 （D）对数据分布很不均匀的列不适合使用绑定变量 12.下面哪些方法可以取得sql的执行计划ABCD （A）PL/SQL DEVELOP 按F5 （B）Dbms_xplan.display_cursor （C）查询视图v$sql_plan （D）SET AUTOTRACE ON 18.下面哪些sql的写法是可能会造成性能问题的（where条件的字段均有索引）？ABCD （A）SELECT * FROM T_NULL WHERE OBJECT_ID ISNULL; （B）SELECT * FROM A_PAY_FLOW WHERE C.SETTLE_MODE=NVL(:B1,C.SETTLE_MODE) ; （C）SELECT * FROM A_CASHCHK WHERE TO_CHAR(RELATE_NO)=TO_CHAR(:B4); （D）SELECTCOUNT(*) FROM S_REGION_OUTGE WHERESYSDATE-A.START_TIME<=30; 19.下面哪些sql的写法是可能会造成性能问题的（where条件的字段均有索引）？ABCDE （A）SELECTCOUNT(*) FROM O_ORG WHERE SCCIFSTATORG(ORG_NO, ‘02’) =1; （B）SELECT T1.OWNER, T1.OBJECT_ID, F_GETNAME(OBJECT_ID) FROM T_FROM1 T1 WHERE OBJECT_ID <2000; （C）SELECT * FROM S_APP WHERE CONS_NAME LIKE‘%’||:2||’%’ （D）SELECTMIN(OBJECT_ID),MAX(OBJECT_ID) FROM T1; （E）INSERTINTOTABLESELECT XXXX FROM DUAL;
Oracle数据库性能优化(碎片整理)
1系统问题 XX公司BI系统上线运行以来，客户反映系统目前存在着下面的几个问题，涉及到数据库和ETL. 问题一：表空间增长太快，每个月需增加3—5G空间。 问题二：ETL JOB会经常导致数据库产生表空间不足错误。 2系统优化分析 2.1分析思路 要解决表空间的问题，我们必须搞清楚下面几个问题： 思路一：真正每个月数据仓库增量是多少空间？ 目的：得出一个正确的月表空间增长量。 思路二：目前的数据仓库表空间是是如何分布的。 目的：找出那些对象是最占空间，分析其合理性。 2.2分析过程 要得到真实的数据分布必须对表进行分析，首先需要对数据仓库的oracle数据库进行表分析，。执行下面脚本可以对数据库进行表分析。 脚本一 analyze table SA_IMS_PRODUCT_GROUP compute statistics; analyze table SA_CONSUMP_ACT_DEL compute statistics; analyze table SA_FINANCE_ACT compute statistics;
analyze table SA_CONSUMP_TGT_DEL compute statistics; analyze table SA_FACT_IS compute statistics; analyze table SA_CPA compute statistics; analyze table SA_REF_TERR_ALIGNMENT_DEL compute statistics; analyze table SA_IMS_MTHLC_BK compute statistics; analyze table SA_IMS_CHPA compute statistics; analyze table SA_FINANCE_PNL compute statistics; analyze table SA_CUST_TARG_SEG compute statistics; analyze table SA_CONSUMP_ACT compute statistics; analyze table SA_FINANCE_BS compute statistics; analyze table SA_FINANCE_BGT_QTY compute statistics; analyze table SA_CONSUMP_ACT0423 compute statistics; analyze table SA_CALLS compute statistics; analyze table SA_COMPANY_DAILY_SALES_ALL compute statistics; analyze table SA_IMS_MTHLC compute statistics; analyze table SA_IMS_MTHUS compute statistics; analyze table SA_CONSUMP_TGT compute statistics; analyze table TEST_TABLE compute statistics; analyze table SA_DOCTOR_CYCLE_EXTRACT compute statistics; analyze table SA_EXCHANGE_ACT compute statistics; analyze table SA_IMS_MTHST compute statistics; analyze table SA_FINANCE_CONCUR_DETAIL compute statistics; analyze table WK_SA_CPA compute statistics; analyze table SA_REF_TERR_ALIGNMENT compute statistics; analyze table SA_CONSUMP_TGT0316 compute statistics; analyze table SA_CUSTOMER compute statistics; analyze table SA_CUST compute statistics; analyze table SA_HKAPI compute statistics; analyze table SA_CONSUMP_TGT_AMT compute statistics; analyze table SA_CUST0423 compute statistics; analyze table SA_COMMUNITY_TGT compute statistics; analyze table SA_CM_WORKING_DATE compute statistics; analyze table SA_CM_IN_MARKET_SALES_CU compute statistics; analyze table SA_DASH_SFE compute statistics; analyze table SA_CPA_TERR compute statistics; analyze table IDX_SA_CUST compute statistics; analyze table SA_REF_EMP_TERR compute statistics; analyze table SA_CM_IN_MARKET_SALES_OCM compute statistics; analyze table SA_COMPANY_MONTHLY_SALES compute statistics; analyze table SA_MAP_YEARMONTH_RATE compute statistics; analyze table SA_FINANCE_ACT_BPCS_TEST compute statistics; analyze table SA_REF_EMP_TERR0413 compute statistics; analyze table SA_FINANCE_ACT_BPCS compute statistics; analyze table IDX$$_143D0001 compute statistics;
oracle数据库sql优化
1.1 使用绑定变量(已测试) 动态sql使用绑定变量 begin for i in 1..100000 loop execute immediate'insert into t values(:x)'using i;--绑定变量 end loop; end ; 所用时间：3.562 s begin for i in 1..100000 loop execute immediate'insert into t values('||i||')';--未使用绑定变量 end loop; end ; 所用时间：55.781s 1.2 WHERE子句中的连接顺序(已测试) 所用时间：0.015s SELECT * FROM EMP E WHERE SAL > 1000 AND JOB = 'MANAGER' AND 1 < (SELECT COUNT(*) FROM EMP WHERE MGR = E.EMPNO); 所用时间：0.001s SELECT * FROM EMP E WHERE 1 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 1000 AND JOB = 'MANAGER';
1.3 SELECT子句中避免使用‘ * ’ (已测试) 当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用‘*’ 是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中，会将‘*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。 1.4 减少访问数据库的次数(已测试) 当执行每条SQL语句时， ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。 例如，以下有三种方法可以检索出雇员号等于0342或0291的职员。 方法1 (最低效) 分两个查询 SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291; 方法2 (次低效) 用游标 DECLARE CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO; BEGIN OPEN C1(342); FETCH C1 INTO …,..,.. ; OPEN C1(291); FETCH C1 INTO …,..,.. ; CLOSE C1; END; 方法3 (高效) 作为两个表查最高效 SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342