Oracle的优化器

sql优化第一步：搞懂Oracle中的SQL的执行过程

从图中我们可以看出SQL语句在Oracle中经历了以下的几个步骤：

语法检查：检查SQL拼写是否正确，如果不正确，Oracle会报语法错误。
语义检查：检查SQL中的访问对象是否存在。比如我们在写SELECT语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证SQL语句没有错误。
权限检查：看用户是否具备访问该数据的权限。
共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存SQL语句和该语句的执行计划。Oracle通过检查共享池是否存在SQL语句的执行计划，来判断进行软解析，还是硬解析。
优化器：优化器中进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
执行器：当有了解析树和执行计划之后，就知道了SQL该怎么被执行，这样就可以在执行器中执行语句了。
共享池是Oracle中的术语，包括了库缓存，数据字典缓冲区等。**库缓存区，主要缓存SQL语句和执行计划。**数据字典缓冲区存储的是Oracle中的对象定义，比如表、视图、索引等对象。当对SQL语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。

硬解析和软解析
软解析：在共享池中，Oracle首先对SQL语句进行Hash运算，然后根据Hash值在库缓存（Library Cache）中查找，如果存在SQL语句的执行计划，就直接拿来执行，直接进入“执行器”的环节

硬解析：如果没有找到SQL语句和执行计划，就会进入“优化器”这个步骤，Oracle就需要创建解析树进行解析，生成执行计划

如何避免硬解析
在Oracle中，绑定变量是它的一大特色。绑定变量就是在SQL语句中使用变量，通过不同的变量取值来改变SQL的执行结果。

这样做的好处是能提升软解析的可能性，不足之处在于可能会导致生成的执行计划不能优化，因此是否需要绑定变量还需要视情况而定。
举个例子，我们可以使用下面的查询语句：

SQL> select * from student where student_id = 100;
1.
你也可以使用绑定变量，如：

SQL> select * from student where student_id = :student_id;
1.
这两个查询语句的效率在Oracle中是完全不同的。

如果在查询过student_id = 100以后，我们还需要继续查询101、102等学生的信息，那么我们每次使用第一种查询方式的时候，都会创建一个新鞋的查询解析。

使用第二种方式的时候，第一次查询之后，会在共享池中存在这类查询的执行计划，也就是上面提到的软解析。

绑定变量的方式可以减少硬解析，减少Oracle的解析工作量。但是因为参数不同，可能会导致SQL的执行效率不同，同时SQL优化也会比较困难。

sql优化第二步：看懂优化方式及其运行逻辑

Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。

相信你一定会用Pl/sql Developer、Toad等工具去看一个语句的执行计划,不过你可能对Rule、Choose、First rows、All rows这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,执行计划就变了?

1、优化器的优化方式

Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。

A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。

B、CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。

我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。

新版本的oracle逐渐抛弃对Rule方式的支持,即使是Rule方式,最后sql执行效率的衡量标准都是,sql执行消耗了多少资源?对代价(COST)的优化方式,需要表,索引的统计信息,需要每天多表和索引进行定时的分析,但是统计信息也是历史的,有时候也不一定是最优的,统计信息等于就是一个人的经验,根据以前的经验来判断sql该怎么执行(得到优化的sql执行路径),所以具体优化执行的时候,先手工分析sql,看是用RBO方式消耗大,还是CBO消耗大;DBA的工作就是要根据当前oracle的运行日志,进行各种调整,使当前的oracle运行效率尽量达到最优.可以在运行期间,采用hint灵活地采用优化方式.

2、优化器的优化模式(Optermizer Mode)

优化模式包括Rule,Choose,First rows,All rows，FIRST_ROWS_n这五种方式,也就是我们以上所提及的。如下我解释一下：
一、CHOOSE
这个是Oracle的默认值。采用这个值时，Oracle即可以采用基于规则RBO，也可以采用基于代价CBO，到底使用那个值，取决于当前SQL的被访问的表中是不是有可以使用的统计信息。
如果有多个被访问的表，其中有一个或多个有统计信息，那么Oralce会对没有统计信息的表进行采样统计（即不全部采样），统计完成后，使用基于代价的优化方法CBO。
如果所有被访问的表都没有统计信息，Oracle就会采用基于规则的优化方法RBO。
二、ALL_ROWS
优化器将寻找能够在最短的时间内完成语句的执行计划。
不管是不是有统计信息，全部采用基于成本的优化方法CBO。
设置为这种CBO模式以后，将保证消耗的所有计算资源最小，尽管有时查询结束以后没有结果返回。all_rows的优化模式更倾向于全表扫描，而不是全索引扫描和利用索引排序，因此这种优化模式适合于数据查看实时性不是那么强的数据仓库、决策支持系统和面向批处理的数据库（batch-oriented databases）等。
三、FIRST_ROWS
CBO模式，使用成本和试探法相结合的方法，查找一种可以最快返回前面少数行的方法；这个参数主要用于向后兼容。
在oracle 9i之后这一选项已经过时，出于向后兼容的目的保留了这一选项，该选项的作用在于寻找能够在最短的时间内返回结果集的第一行的执行计划。这一规则倾向于促使优化器使用索引访问路径，偶尔会出现非常不恰当的访问路径。
设置为这种CBO模式以后，SQL语句返回结果的速度会尽可能的快，而不管系统全部的查询是否会耗时较长或者耗系统资源过多。由于利用索引会使查询速度加快，所以first_rows 优化模式会在全表扫描上进行索引扫描。这种优化模式一般适合于一些OLTP系统，满足用户能够在较短时间内看到较小查询结果集的要求。
四、FIRST_ROWS_N
不管是不是有统计信息，全部采用基于成本的优化方法CBO，并以最快的速度，返回前N行记录。
N的值可以为1，10，100，1000，优化器首先通过彻底分析第一个连接顺序来估计返回行的总数目。这样就可以知道查询可能获得的整个数据集的片段，并重新启动整个优化过程，其目标在于找到能够以最小的资源消耗返回整个数据片段的执行计划。
Oracle 9i 对一些预期返回结果集的数据量小的SQL语句优化模式进行了加强，增加了四个参数值：first_rows_1、first_rows_10、first_rows_100、first_rows_1000。CBO通过first_rows_n中的 n 值，决定了返回结果集数量的基数，我们可能仅仅需要查询结果集中的一部分，CBO就根据这样的n 值来决定是否使用索引扫描。
五、RULE
这个参数正好和ALL_ROWS相反，不管是不是统计信息，全部采用基于规则rbo的优化方法。
基于规则的优化器模式，RBO，是早期Oracle版本使用过的一种优化模式。由于RBO不支持自1994年Oracle版本的新特性，如bitmap indexes，table partitions，function-based indexes等，所以在以后Oracle版本中已经不再更新RBO，并且也不推荐用户使用RBO这种优化模式了。

First Rows和All Rows是有冲突的.如果想最快第返回给用户,就不可能传递更多的结果,这就是First Rows返回最先检索到的行(或记录);而All Rows是为了尽量将所有的结果返回给用户,由于量大,用户就不会很快得到返回结果.就象空车能跑得很快,重装车只能慢慢地跑;

3、如何设定选用哪种优化模式

查看参数
show parameter optimizer_mode

修改参数
alter session set optimizer_mode='CHOOSE'

修改方式

a、Instance级别 

我们可以通过在init<SID>.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。 
init.ora和init<SID>.ora都在$ORACLE_HOME/dbs目录下,可以用find $ORACLE_HOME -name init*.ora查看该目录下的init文件.
init.ora是对全体实例有效的;init<SID>.ora只对指定的实例有效.

B、Sessions级别 

通过SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;来设定。 将覆盖init.ora,init<sid>.ora设定的优化模式,也可以在sql语句中采用hint强制选定优化模式.如下:

C、语句级别 

这些需要用到Hint,比如: 
SQL> SELECT /*+ RULE */ a.userid, 
2 b.name, 
3 b.depart_name 
4 FROM tf_f_yhda a, 
5 tf_f_depart b 
6 WHERE a.userid=b.userid; 
在这儿采用hint,强制采用基于规则(rule)的优化模式;
hint语法,/*+开头,*/结尾,中间填写强制采用的优化模式.

4、为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢？如何解决呢 ？

A、不走索引大体有以下几个原因 
你在Instance级别所用的是all_rows的方式 
你的表的统计信息(最可能的原因) 
你的表很小,上文提到过的,Oracle的优化器认为不值得走索引。 
B、解决方法 
可以修改init<SID>.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint. 
删除统计信息 
SQL>analyze table table_name delete statistics; 
表小不走索引是对的,不用调的。

或者用一下方法试试：
第一，我们要确定数据库运行在何种优化模式下，相应的参数是：optimizer_mode。可在 sqlplus中运行“show parameter optimizer_mode”来查看。oracle v7以来缺省的设置应是“choose”，即如果对已分析的表查询的话选择 cbo，否则选择 rbo。如果该参数设为“rule”，则不论表是否分析过，一概选用 rbo，除非在语句中用 hint强制。

第二，检查被索引的列或组合索引的首列是否出现在 pl/sql语句的 where子句中，这是“执行

计划”能用到相关索引的必要条件。

第三，看采用了哪种类型的连接方式。oracle的共有 sort merge join（smj）、hash join（hj）和 nested loop join（nl）。在两张表连接，且内表的目标列上建有索引时，只有nested loop才能有效地利用到该索引。smj即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。hj由于须做 hash运算，索引的存在对数据查询速度几乎没有影响.

第四，看连接顺序是否允许使用相关索引。假设表 emp的 deptno列上有索引，表 dept的列deptno上无索引，where语句有 emp.deptno=dept.deptno条件。在做 nl连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。

第五，是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。

第六，索引列是否函数的参数。如是，索引在查询时用不上。

第七，是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，oracle会自动将字符型用 to_number()函数进行转换，从而导致第六种现象的发生。

第八，是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用 sql语句“analyze table xxxx compute statistics for all indexes;”。oracle掌握了充分反映实际的统计数据，才有可能做出正确的选择.

第九，索引列的选择性不高。我们假设典型情况，有表 emp，共有一百万行数据，但其中的emp.deptno列，数据只有 4种不同的值，如 10、20、30、40。虽然 emp数据行有很多，oracle缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种 deptno值各有 25万数据行与之对应。

假设 sql搜索条件 deptno=10，利用 deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，oracle理所当然对索引“视而不见”，认为该索引的选择性不高。但我们考虑另一种情况，如果一百万数据行实际不是在 4deptno值间平均分配，其中有 99万行对应着值 10，5000行对应值 20，3000行对应值 30，2000行对应值 40。在这种数据分布图案中对除值为 10外的其它 deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用 analyze语句对该列建立直方图，对该列搜集足够的统计数据，使 oracle在搜索选择性较高的值能用上索引。

第十，索引列值是否可为空（null）。如果索引列值可以是空值，在 sql语句中那些需要返回 null值的操作，将不会用到索引，如 count（*），而是用全表扫描。这是因为索引中存储值不能为全空。

第十一，看是否有用到并行查询（pqo）。并行查询将不会用到索引。

第十二，看 pl/sql语句中是否有用到 bind变量。由于数据库不知道 bind变量具体是什么值，在做非相等连接时，如“<”，“>”,“like”等。oracle将引用缺省值，在某些情况下会对执行计划造成影响。如果从以上几个方面都查不出原因的话，我们只好用采用在语句中加 hint的方式强制oracle使用最优的“执行计划”。

hint采用注释的方式，有行注释和段注释两种方式。

如我们想要用到 a表的 ind_col1索引的话，可采用以下方式：

“select /*+ index（a ind_col1）*/ * from a where col1 = xxx;”

注意，注释符必须跟在 select之后，且注释中的“+”要紧跟着注释起始符“/*”或“–”，否则

hint就被认为是一般注释，对 pl/sql语句的执行不产生任何影响。

5、其它相关

A、如何看一个表或索引是否是统计信息 

SQL>SELECT * FROM user_tables 
WHERE table_name=<table_name> 
AND num_rows is not null; 

SQL>SELECT * FROM user_indexes 
 WHERE table_name=<table_name> 
AND num_rows is not null; 

b、如果我们采用CBO的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划。 
SQL> ANALYZE TABLE table_name COMPUTE STATISTICS; 
SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS; 

所以查询优化是提高数据库性能和减少查询响应时间的关键。在Oracle数据库中，有许多技巧可以用来优化查询操作。下面将介绍八个提升查询效率的秘诀。

1、使用合适的索引：
1）了解查询的访问模式，使用适当的索引类型（B树索引、位图索引等）。
2）对经常使用的查询字段创建索引，以加快检索速度。
3）避免过多的索引，因为索引的增加会导致写操作的开销。

2、编写高效的查询语句：
1）避免使用SELECT *，只选择需要的列，减少磁盘I/O。
2）使用合适的WHERE子句，尽量减少结果集的大小。
3）使用合适的连接方式（INNER JOIN、LEFT JOIN等），确保连接的正确性和高效性。
使用查询的where语句逻辑和索引关系

sql语句编写注意问题:
下面就某些 sql语句的 where子句编写中需要注意的问题作详细介绍。在这些 where子句中，即使某些列存在索引，但是由于编写了劣质的 sql，系统在运行该 sql语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。

1)is null与 is not null
不能用 null作索引，任何包含 null值的列都将不会被包含在索引中。任何在 where子句中使用 is null或 is not null的语句优化器是不允许使用索引的。

2)语句中的函数
语句中如有函数操作如（upper,substr等）时，优化器是不会使用索引的，应尽量少用或不用。

3)带通配符（%）的 like语句
如果通配符（%）在搜寻词首出现，oracle系统不会使用索引。在很多情况下可能无法避免这种情况，然而当通配符出现在字符串其他位置时，优化器就能利用索引。
在下面的查询中索引得到了使用：
select * from user_m where loginid like ‘r%’;

4)order by语句
order by语句决定了 oracle如何将返回的查询结果排序。order by语句对要排序的列没有什么特别的限制。但任何在 order by语句的非索引项或者有计算表达式都将降低查询速度,应尽量少用。
order by 条件要与where中条件一致，否则order by不会利用索引进行排序**
-- 不走age索引
SELECT * FROM t order by age;
-- 走age索引
SELECT * FROM t where age > 0 order by age;

5)not
在查询时经常在 where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用 and（与）、or（或）以及 not（非）。not可用来对任何逻辑运算符号取反。not运算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询 where子句中(!=也不行)
显式地加入 not词，not仍在运算符中，见下例：
select * from employee where salary<>3000;
对这个查询，可以改写为不使用 not：select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许对
salary列使用索引，而第一种查询则不能使用索引。

6)in和 exists
在 where子句中使用子查询。在 where子句中可以使用两种格式的子查询。第一种格式是使用in操作符：第二种格式是使用 exist操作符：第二种格式要远比第一种格式的效率高。应尽可能使用 not exists来代替 not in。

7)条件的顺序问题
条件列和索引列的顺序要保持对应

8)使用 union、intersect、minus;消除对大型表行数据的顺序存取
对连接的列进行索引，还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的 where子句强迫优化器使用顺序存取。下面的查询将强迫对 orders表执行顺序操作：
select＊ from orders where (customer_num=104 and order_num>1001) or
order_num=1008
虽然在 customer_num和 order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：
select＊ from orders where customer_num=104 and order_num>1001 union
select＊ from orders where order_num=1008

9)尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。**如下：
SELECT * FROM t WHEREid = 1 OR id = 3
优化方式：可以用union代替or。如下
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

10)尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。**
可以将表达式、函数操作移动到等号右侧。如下：
-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

11)当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。**如下：
SELECT username, age, sex FROM T WHERE 1=1
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

这样就能利用索引路径处理查询。

3、使用优化器提示（Hint）：
1）根据具体情况，使用Hint指令告诉优化器如何执行查询。
2）例如，使用INDEX hint指定使用某个索引，或者使用LEADING hint指定连接的顺序。

4、统计信息的准确性：
1）收集和更新表的统计信息，以便优化器可以更好地选择执行计划。
2）使用ANALYZE命令或DBMS_STATS包来收集和更新统计信息。

5、使用分区表：（这里其实内存表更快，千万级大概快20%，但是会大量占用内存影响其他程序的运行）
1）对大型表进行分区，可以提高查询效率，并且简化数据维护操作。
2）分区表可以根据某个列的值将数据划分为多个子表，从而减少查询范围。

6、使用临时表和内存表：
1）对于复杂的查询，可以使用临时表或内存表来暂存中间结果，以加快查询速度。
2）临时表和内存表不会写入磁盘，因此访问速度更快。

7、批量提交和绑定变量：
1）对于大批量的插入、更新或删除操作，使用批量提交（Bulk Insert）可以显著提高性能。
2）、使用绑定变量而不是直接拼接SQL语句，可以减少SQL解析的开销，提高查询效率。

8、使用合适的并行化策略：
1）、对于大型查询或涉及大量数据的操作，可以使用并行执行（Parallel Execution）来加速查询。
2）、通过设置适当的并行度，可以充分利用服务器的多核处理能力。

除了以上的八个秘诀，还可以通过调整数据库参数、优化硬件配置、使用数据库缓存和内存管理等方式进一步提升查询性能。然而，需要根据具体情况和业务需求进行优化，避免过度优化导致其他问题。

在进行查询优化时，建议使用数据库性能监控工具进行实时监测和性能分析，以便及时发现潜在的问题并采取相应的优化措施。此外，定期进行数据库维护和性能调优也是必要的，以保证数据库的高效稳定运行。

总结起来，Oracle数据库查询优化需要综合考虑索引的使用、查询语句的编写、统计信息的准确性、分区表的使用、临时表和内存表的利用、批量提交和绑定变量、并行化策略等方面。通过合理的优化手段，可以显著提升数据库查询效率和性能，满足业务需求。