oracle中SQL语句执行与优化

SQL语句大体执行流程图：

当用户提交SQL后，Oracle执行的总体步骤如下：

1.检查：验证语句的语法是否正确、检验提及的对象是否存在、确认该对象的用户权限。

2.解析：首先在共享池（shared pool）中看这个语句是不是执行过，其原理是检查共享池中是否有和该语句相同的hash值。如果有，则说明执行过，Oracle将取回之前解析的信息并重用，这种类型的解析被称为软解析。如果没有，Oracle将执行所有的工作来为当前语句来为当前语句生成执行计划，并将它存入缓存以便重用，这种解析称为硬解析，硬解析耗费的资源比软解析多得多，因为硬解析需要收集收集它的所有信息，比如查询列与表中的数据、对象间的依赖、索引以及生成执行计划等。

3.缓存：将要操作的表数据行、索引条目等加入缓冲区缓存。

4.绑定：若SQL语句使用了绑定变量，则扫描绑定变量的声明，给绑定变量赋值，将变量值带入计划。

5.执行：按执行计划执行SQL，产生执行结果集合并返回给用户。（select语句还有提取步骤）

Oracle实例和数据库关系图（上面提到的内存区可以在下图找到）：

SGA-共享池：

共享池是Oracle缓存程序数据的地方。执行过的每一句SQL语句在共享池中都有解析后的内容，共享池中存储这些语句的地方称为库高速缓存。还有Oracle使用的系统参数也存放在共享池中一块称为数据字典高速缓存的区域。当然，还有一些其它数据保存在SGA共享池中如上图所示（共享池），它能被所有用户会话（session）共享。

由于分配给共享池的内存区域是有限的，所以当新语句执行时，原先加载的语句就不能长时间放在其中，它使用最近最少使用算法（Least Recently Used，LRU）根据语句被使用的频繁程度来决定语句在共享池中保留的时间。

当写是SQL语句时，为了最高效使用共享池，语句需要被共享，即返回相同结果集的尽量写相同的SQL。若写的每一句SQL都是不同的，则每次都要硬解析，这要耗费巨大代价，对响应时间的影响也越大。

SGA-缓冲区缓存：

缓存区是系统全局内存区域（SGA，上图的系统共享区）最大的部分之一。它用来存储数据库块。块是Oracle操作的最小单位，它可以存储表数据行、索引条目、排序的临时数据等。

SQL语句优化建议：

1.规范SQL语句格式，对于返回相同结果集的尽量写相同的SQL语句，这样可以减少SQL的硬解析。

2.对于经常查询的列可以使用索引，但不要用含空值的列做索引。因为通常的索引是B树，是有序的，空值无法确定顺序。

3.尽量使用变量传值，尽量减少常量直接拼接SQL。

4.在where子句中尽量不使用is null和is not null，否则优化器不使用索引而用全表扫描。

5.不要用索引和别的列合并在一起查，否则就不会用索引查而用全表查。

6.选择最有效的表顺序，选择记录条数最少的表放在最后，当有三个表以上的连接查询时，将引用最多的表放在最后，解析器是从FROM右边向左边解析表的。

7.数据库采用自右而左的顺序解析WHERE子句，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右，例如： where (emp.deptno = dept.deptno) and (emp.sal > 1500)

8. SELECT子句中避免使用*号，数据库在解析的过程中,会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

9.当要全表删除数据时，用TRUNCATE替代DELETE。

10.尽量多使用COMMIT，因为COMMIT会释放回滚点。

11.用WHERE子句替换HAVING子句，因为WHERE先执行，HAVING后执行，即先过滤。

12.多使用内部函数提高SQL效率，因为内部函数或存储过程都是编译好的。

13.整合简单,无关联的数据库访问，如果有几个简单的数据库查询语句，可以把它们整合到一个查询中（即使它们之间没有关系），这样可以减少数据库的访问次数。

另外：

SQL Plus查看SQL的执行计划和统计信息：set autotrace trace exp stat

PL/SQL Developer查看执行计划：选择SQL语句后按F5或者右键选择解释计划