优化sql语句

认识Oracle的执行过程

Oracle优化法则---漏斗法则

常规SQL语句优化

　　1、建议不用“*”来代替所有列名

　　SELECT语句中可以用“*”来列出某个表的所有列名，但是这样的写法对Oracle系统来说会存在解析的动态问题。Oracle系统会通过查询数据字典来将“*”转换成表的所有列名，这自然会消耗系统时间。建议用户在写SELECT语句时，采用与访问表有关的实际列名。

　　2、用TRUNCATE代替DELETE

　　当使用DELETE删除表中的数据行时，Oracle会使用撤销表空间（UNDO TABLESPACE）来存放恢复的信息。在这期间，如果用户没有发出COMMIT语句，而是发出ROLLBACK语句，Oracle系统会将数据恢复到删除之前的状态。当用户使用TRUNCATE语句对表的数据进行删除时，系统不会将被删除的数据写到回滚段（或撤销表空间）中，速度当然要快得多。所以当希望对表或者簇中的所有行全部删除时，采用TRUNCATE命令更加有效，其语法格式如下：

Truncate [table | cluster] schema.[table_name] [cluster_name] [drop | reuse storage]

　　3、在确保完整性的情况下多用COMMIT语句

　　在PL/SQL块中，经常将几个相互联系的DML语句写在一个BEGIN…END块中，建议在每个块的END前面使用COMMIT语句，这样就可以实现对DML语句的及时提交，同时也释放事务所占用的资源。
COMMIT所释放的资源如下。
　　1）回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留
　　2）被程序语句获得的锁
　　3）redo log buffer中的空间
　　4）Oracle为管理上述资源的内部花费

　　4、尽量减少表的查询次数

　　低效率的SQL查询语句：

select empno,ename,job from emp
where deptno in (select deptno from dept where loc = 'BEIJING')
or deptno in (select deptno from dept where loc = 'NEW YORK');
--要对dept表执行两遍的查询

　　对上面的代码进行适当修改，高效率的SQL查询语句：

select empno,ename,job from emp
where deptno in (select deptno from dept where loc = 'BEIJING' or loc = 'NEW YORK')

　5、子查询建议使用 [NOT] EXISTS 代替 [NOT] IN

　　在子查询中，[not] in子句将执行一个内部的排序与合并，无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 

--低效SQL
SELECT empno,ename,job FROM EMP WHERE  DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = 'NANJING')
--高效SQL
SELECT empno,ename,job FROM EMP WHERE EXISTS (SELECT deptno FROM DEPT WHERE LOC != 'NANJING') 

　　一分为二看[not] in，当[not] in后面跟子查询，并且查询的结果集较多时，不宜使用[not] in；如果[not] in后面的括号内时列表或子查询所满足结果集很少时，也是可以使用的。

　　6、用>=替代>

--低效:
   SELECT empno,ename,job FROM EMP WHERE DEPTNO > 3

--高效:
   SELECT empno,ename,job FROM EMP  WHERE DEPTNO >= 4

　　DEPTNO > 3时ORACLE会先找出为3的记录索引再进行比较，而DEPTNO >= 4时ORACLE则直接找到=4的记录索引

　　7、用in代替or

-- 低效
select.. from emp where empno = 10 or empno = 20 or empno = 30

-- 高效:
select.. from emp where empno in (10,20,30);

　　8、避免使用耗费资源的操作

　　带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.

　　例如,一个UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.

　　9、order by

　　任何在Order by语句的非索引项或者有计算表达式都将降低查询速度

　　经常用于排序的字段应加上索引

　　10、避免在索引列上使用计算

　　WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．

--低效：
SELECT … FROM  EMP WHERE SAL * 12 > 25000;

--高效:
SELECT … FROM EMP WHERE SAL > 25000/12;

　　11、通过使用>=、<=等，避免使用NOT命令

select * from employee where salary <> 3000; --不能使用索引

对这个查询，可以改写为不使用NOT：

select * from employee where salary<3000 or salary>3000; --允许Oracle对salary列使用索引

　　12、字符型字段的引号

　　比如表中PHONE_NO字段是CHAR型,而且创建有索引，但在WHERE条件中忘记了加引号，就不会用到索引。

　　WHERE PHONE_NO=‘13920202022’

　　WHERE PHONE_NO=13920202022

　　13、避免在索引列上使用IS NULL和IS NOT NULL 

　　14、模糊查询

　　下面模糊查询也将导致全表扫描：  
　　select id from t where name like '%abc%'  

表连接优化

　　1、驱动表的选择

　　驱动表（Driving Table）是指被最先访问的表（通常以全表扫描的方式被访问）。Oracle优化器会检查SQL语句中的每个表的物理大小、索引状态，然后选用花费最低的执行路径，接下来，我们分析下面的一个例子。

select s.Name,d.Dept_Name
from Department d ,Students s
where d.Dept_No = s.Dept_No;

　　在上面的代码中，假设在Students表的dept_no列创建了索引，而在Department表的dept_no列没有索引。由于Department最先被访问（紧随from其后），这样Department表将被作为查询中的驱动表，由此可见，只有两个表都建立有索引，优化器才能按照紧随from关键字后面的驱动表的规则（form最后的表）来对待。

　　2、WHERE子句中的连接顺序： 
　　ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

 合理使用索引

　　创建索引的基本原则：

　　1）以查询关键字为基础，表中的行随机排序。

　　2）包含的列数相对比较少的表。

　　3）表中的大多数查询都包含相对简单的WHERE从句。

　　4）对于经常以查询关键字为基础的表，并且该表中的行遵从均匀分布

　　5）缓存命中率低，并且不需要操作系统权限。

　　在创建索引时，选择列和表达式是非常重要的，下面是创建索引时选择索引列的原则：

　　1）WHERE从句频繁使用的关键字。

　　2）SQL语句中频繁由于进行表连接的关键字。

　　3）可选择性高（重复性少的）关键字。

　　4）对于取值较少的关键字或表达式，不要采用标准的B树索引，可以考虑建立位图索引。

　　5）不要将那些频繁修改的列作为索引列。

　　6）不要使用包含操作符或函数的WHERE从句中的关键字作为索引列，如果需要的话，可以考虑建立函数索引。

　　7）如果大量并发的INSERT、UPDATE、DELETE语句访问了父表或者子表，则考虑使用完整性约束的外部键作为索引。

　　8）在选择索引列时，还要考虑该索引所引起的INSERT、UPDATE、DELETE操作是否值得。

　　选择复合索引主列　　

　　多列索引也叫做复合索引，复合索引有时比单列索引有更好的性能。如果在建立索引时采用了几个列作为索引，则在使用时也要按照建立时的顺序来描述，也就是说，主列是最先被选择的列。

       例如，为tb_test表创建一个复合索引complex_index，该索引包括column1、column2、column3个列（并且建立顺序亦此），如果把这3列作为where查询条件，那么这3个列的最优排序方式如下：

create index complex_index on tb_test(column1,column2,column3)

select * from tb_test where column1 > 0 and column2 > 0 and column3 < 0

　　避免全表扫描大表

　　监视索引是否被使用

　　　1、设置监视索引 ALTER INDEX ... MONITORING USAGE

　　　2、检查索引使用情况 SELECT * FROM V$OBJECT_USAGE;

　　   3、如索引在限定的时间内得不到使用，建议使用drop index 删除该索引

优化器的使用

 　　Oracle优化器在处理每一个SQL语句准备执行之前，都需要进行许多步骤才能使SQL语句成为可执行的语句。

　　1）语法检查：检查SQL语句的拼写是否正确。

　　2）语义分析：核实所有与数据字典不一致的表和列的名字。

　　3）概要存储检查：检查数据字典，以确定该SQL语句的概要是否已经存在。

　　4）生成执行计划：使用基于成本的优化规则和数据字典的统计表来决定最佳执行计划。

　　5）建立二进制代码：基于执行计划，Oracle生成了二进制执行代码。

　　在PL/SQL Developer中写好一段SQL代码后，按F5，PL/SQL Developer会自动打开执行计划窗口，显示该SQL的执行计划。

 

根据Operation缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行）

ID: 一个序号，但不是执行的先后顺序。执行的先后根据缩进来判断。

Operation： 当前操作的内容。

Rows： 当前操作的Cardinality，Oracle估计当前操作的返回结果集。

Cost（CPU）：Oracle 计算出来的一个数值（代价），用于说明SQL执行的代价。

Time：Oracle 估计当前操作的时间。

在看执行计划的时候，除了看执行计划本身，还需要看谓词和统计信息。 通过整体信息来判断SQL效率。

Access :

• 通过某种方式定位了需要的数据，然后读取出这些结果集，叫做Access。
• 表示这个谓词条件的值将会影响数据的访问路劲（表还是索引）。

Filter：

• 把所有的数据都访问了，然后过滤掉不需要的数据，这种方式叫做filter 。
• 表示谓词条件的值不会影响数据的访问路劲，只起过滤的作用。

在谓词中主要注意access，要考虑谓词的条件，使用的访问路径是否正确。

表访问的几种方式：（非全部）

• TABLE ACCESS FULL（全表扫描）
• TABLE ACCESS BY ROWID（通过ROWID的表存取）
• TABLE ACCESS BY INDEX SCAN（索引扫描）

索引扫描又分五种：

• INDEX UNIQUE SCAN（索引唯一扫描）
• INDEX RANGE SCAN（索引范围扫描）
• INDEX FULL SCAN（索引全扫描）
• INDEX FAST FULL SCAN（索引快速扫描）
• INDEX SKIP SCAN（索引跳跃扫描）

表连接的几种方式：

• SORT MERGE JOIN（排序-合并连接）
• NESTED LOOPS（嵌套循环）
• HASH JOIN（哈希连接）
• CARTESIAN PRODUCT（笛卡尔积）

HASH JOIN的三种模式：

• OPTIMAL HASH JOIN
• ONEPASS HASH JOIN
• MULTIPASS HASH JOIN

Oracle数据库和SQL重演

　　Database Replay是指在产品环境的数据库上捕获所有负载，并可以将之传送至备份的（Standby）数据库或有备份恢复的测试库上，在测试环境中重演主库的环境，这使得升级或软件更新可以进行预先的“真实”测试，或者可以通过测试环境完全再现真实环境的负载及运行情况。

  这是Oracle向后追溯能力的又一增强，在Oracle 10g中，我们知道Oracle通过V$SESSION_WAIT_HISTORY视图、ASH特性等，实现将数据库的等待时间向后追溯。现在通过Databse Replay特性，Oracle可以将整个数据库的负载捕获、记录并实现Replay，也就是增强了整个数据库的向后追溯能力。

  这一特性提供了再现现场能力，极大地丰富了用户发现并解决数据库问题的手段，将为数据库管理带来更多的方便之处。

  当然使用这一特性会带来一定的性能负担，Oracle说这一负担在5%左右。

　 这一特性的简化版本就是SQL Replay，即只捕获SQL负载，通过SQL负载，应用程序可以再现SQL影响，如下图所示。

Oracle性能顾问

SQL调优顾问

　　SQL Tuning Advisor（SQL调优顾问）是Oracle 10g中引入的，设计它的目的就是为了替代传统的手工SQL调整。

　　SQL调优顾问处理的对象包括那些响应时间很慢或者是占用CPU/DISK很高的SQL。

　　SQL调优顾问收集这些SQL，并且给出自己的建议，它包括下面的部分：

　　　　1）怎样调整SQL的执行计划。

　　　　2）优化后效率的提升幅度。

　　　　3）做出这条建议的理论原理。

　　　　4）直接给出推荐使用的命令。

      用户可以有选择性地接收这些建议，然后去调优SQL。

 

　　随着SQL调优顾问的引入，用户现在就可以让Oracle优化器来自动地调整SQL。

--授权
grant administer any sql tuning set to scott;
grant advisor to scott;
grant create any sql profile to scott;
grant alter any sql profile to scott;
grant drop any sql profile to scott;

--创建任务
declare
tuning_task_name VARCHAR2(30);
tuning_sqltext CLOB;
begin
 tuning_sqltext := 'select job from emp';--注意，这里不支持*,要写上具体的字段名
 tuning_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text  => tuning_sqltext,
    user_name => 'SCOTT',
    scope  => 'COMPREHENSIVE',
    time_limit => 60,
    task_name => 'sql_trace_20131124',
    description => 'EMP SELECT TUNE');
end;

--所有已经创建任务的查看
select * from user_advisor_log;

-- 任务的执行
exec dbms_sqltune.execute_tuning_task(task_name => 'sql_trace_20131124');

--任务执行后状态的检查
 select * from user_advisor_tasks t where t.task_name = 'sql_trace_20131124'

--最终报告的生成
select dbms_sqltune.report_tuning_task('sql_trace_20131124') from dual;

--任务的删除
exec dbms_sqltune.drop_tuning_task('sql_trace_20131124');

SQL访问顾问

　　SQL访问顾问（SQL Access Advisor）的设计目的是获得有关基于实际频率和使用类型（而非数据类型）进行分区、索引和创建物化视图以改进模式设计的建议。这与SQL Tuning Advisor提供有关查询、调整及在流程中延长整个优化过程的建议有所不同，它的特点如下：

　　1）分析整个负载而不仅仅是单独的SQL语句。

　　2）使访问结构设计更加清晰，以优化应用程序性能。

　　3）建议创建和删除某些索引、物化视图和物化视图日志以提高性能。

      Oracle 11g的SQL访问顾问除了可以像在Oracle 10g中一样分析索引、物化视图等，还可以分析表和查询以识别可能的分区策略，这在进行最佳模式设计时可以提供很大的帮助。在Oracle 11g中，SQL访问顾问可以提供与整个负载相关的建议，包括考虑创建成本和维护访问结构等。