Oracle 11g_优化SQL语句（13）

1.常规SQL语句优化

应用系统的性能优化包括对SQL语句，Oracle系统，操作系统等的调整。其中工作量最大的就是SQL语句的调整。

1.1 建议不用 "*" 来代替所有列名

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

1.2 用TRUNCATE 代替 DELETE

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

TRUNCATE [TABLE | CLUSTER] schema.[table_name][cluster_name][DROP | REUSE STORAGE]
table_name: 要清空的表明。
cluster_name: 要清空的簇名。
DROP | REUSE STORAGE: 表示保留被删除的空间以供该表的新数据使用或回收空间，默认为DROP STORAGE,
即收回被删除的空间系统。

在 SQL*Plus 环境下，直接采用TRUNCATE TABLE命令即可，若要在PL/SQL中使用，只能采用动态语句实现。

**** 创建一个存储过程，实现使用TRUNCATE命令动态删除数据表，代码：

SQL> create or replace procedure trun_table(table_deleted in varchar2) as		-- 实现清空指定的表
		cur_name integer;		-- 定义内部变量，存储打开的游标
	begin
		cur_name:=dbms_sql.open_cursor;	-- 打开游标
		
		/* 执行truncate table table_deleted命令，从而清空指定的表 */
		dbms_sql.parse(cur_name,'truncate table'||table_deleted||'drop storage',dbms_sql.native);
		dbms_sql.close_cursor(cur_name);
	exception
		when others then dbms_sql.close_cursor(cur_name);
		raise;
	end trun_table;
	/

	过程已创建。

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

在PL/SQL块中，经常将几个相互关联的DML语句写在一个BEGIN...END块中，建议在每个块的END前面使用COMMIT语句，这样就可以实现对象DML语句的及时提交，同时也释放事务所占的用的资源。

	COMMIT语句所释放的资源如下：

	** 回滚段上用于恢复数据的信息，撤销表空间也只做短暂的保留。
	** 被程序语句获得的锁。
	** 重做日志缓冲区(Redo Log Buffer)中的空间。
	** Oracle为管理上述资源的内部花费。

1.4 尽量减少表的查询次数

在含有子查询的SQL语句中，要特别注意减少对表的查询，代码：

	第一种：低效率的SQL查询语句。
	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');

	第二种：对上面的代码进行适当修改，高效率的SQL查询语句如下：
	SQL> select empno, ename, job from 
		where deptno in(select deptno from dept where loc='BEIJING' OR loc='NEW YORK');

在第一种查询语句中，要对dept表执行两遍查询，而在第二种查询中，仅对dept表执行一遍查询。在大量数据的情况下，显然第二种查询要比第一种查询快得多。

1.5 用[NOT] EXISTS代替[NOT] IN

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

第一种：低效率的NOT IN子句
SQL> select empno,ename,from emp
		where empno not in(select deptno from dept where loc='BEIJING');

第二种：高效率的EXISTS子句
SQL> select empno,ename from emp
		where exists(select deptno from dept where loc!='BEIJING');

在SQL语句中，很多资料都建议最好不用[NOT] IN。那么，[NOT] IN在何处可以用呢？这里要一分为二地看[NOT]IN，即当[NOT]IN后面跟子查询，并且查询地结果较多时，不宜使用[NOT]IN；但如果[NOT]IN后面地括号内是列表(可枚举的几个)或子查询所满足地结果集很小时，也是可以使用的。

2. 表连接优化

在关系数据库中，各表之间存在一定地关系。无论在数据库中采用哪种优化模式运行，在SQL语句中表的连接方法都可能对性能产生很大的影响。

2.1 驱动表的选择

驱动表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问)。Oracle 11g优化器会检查SQL语句中的每个表的物理大小，索引状态，然后选用花费最低的执行路径。

*** 从Students表和Department表查询学生信息，代码：
	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关键字后面的为驱动表的规则来对待。

2.2 WHERE子句的连接顺序

    Oracle采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其它WHERE条件之前。

    那些可以过滤最大数据记录的条件必须写在WHERE子句的末尾，也就是在表进行连接操作之前，过滤到的记录越多越好。

3. 合理使用索引

创建主键和唯一索引的主要目的除了数据的完整性和一致性之外，还具有提高查询速度的作用。此外，创建一般索引的目的就是为了提高查询速度。

3.1 何时使用索引

在利用索引的情况下，由于只从表中选择部分行，所以能够提高查询速度。对于只从总行数中查询2%~4%的表，可以考虑创建索引。下面是创建索引的基本原则：

    （1）已查询关键字为基础，表中的行随机排序。
	（2）包含的列数相对比较少的表。
	（3）表中的大多数查询都包含相对简单的WHERE从句。
	（4）经常以查询关键字为基础的表，并且该表中的行遵从均匀分布。
	（5）缓存命中率低，并且不需要操作系统权限。

3.2 索引列和表达式的选择

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

	（1）WHERE从句频繁使用的关键字。
	（2）SQL语句中频繁使用进行表连接的关键字。
	（3）可选择性高(重复性少的)关键字。
	（4）对于取值较少的关键字或表达式，不要采用标准的B树索引，可以考虑建立位图索引。
	（5）不要将那些频繁修改的列作为索引列。
	（6）不要使用包含操作符或函数的WHERE从句中关键字作为索引列，如果需要的话，可以考虑建立函数索引。
	（7）如果大量并发的INSERT, UPDATE, DELETE语句访问了父表或者子表，则考虑使用完整性约束的外部键作为索引。
	（8）在选择索引列时，还要考虑该索引所引起的INSERT, UPDATE, DELETE操作是否值得。

3.3 选择复合索引列

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

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

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

	索引已创建。

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

	这里的索引complex_index是一个复合索引，它包含column1, column2, column3三个列。由于本索引被建立成复合型，所以在查询语句中要带有WHERE...AND从句才能使用该复合索引。

在选择复合索引的关键字时，要遵循下列的原则：

	（1）如果某些关键字在WHERE从句中的使用频率较高，则考虑创建索引。
	（2）如果某些关键字在WHERE从句中的使用频率相当，则创建索引时考虑按照从高到低的顺序来说明关键字。
	（3）如果几个查询都选择相同的关键字集合，则考虑创建组合索引。
	（4）创建索引以后使得WHERE从句所使用的关键字能够组成前导部分。
	（5）应该选择在WHERE从句条件中频繁使用的关键字，并且这些关键字由AND操作符连接。

3.4 避免全表扫描大表

在应用程序中，除了一些必要的情况，如统计月报数据，打印所有清单等任务可以允许使用全表扫描外，一般都应尽量避免对大表进行全表扫描。全表扫描是指不加任何条件或没有使用索引的查询语句。一些情况Oracle可以使用全表扫描。

    （1）所查询的表没有索引。
	（2）需要返回所有的行。
	（3）带LIKE并使用'%'这样的语句就是全表扫描。
	（4）对索引主列有条件的限制，但使用了函数，则Oracle使用全表扫描，代码：
		SQL> select * from emp where substr(ename,1,1)='M';

	（5）带有IS NULL, IS NOT NULL 或 !=等子句也导致全表扫描，代码: 
		SQL> select * from emp where job!='MANAGER';

3.5 监视索引是否被使用

除了主键是完整性约束而自动变为索引外，创建普通索引的目的就是为了提高查询速度。如果我们创建了索引而索引没有被使用，那么这些不被使用的索引将起到阻碍性能的作用。

为了辨别索引是否被使用，从Oracle 9i 版本开始，用户可以对索引进行监视，通常使用ALTER INDEX...MONITORING USAGE语句，其具体语法格式如下：

ALTER INDEX schema.index_name MONITORING USAGE;

**** 监视学生成绩表studentgrade的grade_index索引是否被使用，步骤及代码：


（1）设置监视索引grade_index，代码：
SQL> alter index grade_index monitoring usage;

索引已更改。

（2）检查索引使用情况
SQL> select * from v$object_usage;


(3)在第2步检查中，如果发现索引grade_index在限定的时间内得不到使用(即USE列的值为NO)，则建议使用DROP INDEX语句删除掉，代码：
SQL> drop index grade_index;

4. 优化器的使用

Oracle优化器(Optimizer)的使用方法，是程序员对应用程序优化时的检查工具。

4.1 优化器的概念

尽管现在计算机的CPU速度达到GB级，但在处理数据库时还是显得不够。在大型应用系统中，存在许多消耗CPU大量时间的SQL语句，但是却很少被程序员本人发现。

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

（1）语法检查：检查SQL语句的拼写是否正确。
（2）语义分析：核实所有与数据字典不一致的表和列的名称。
（3）概要存储检查：检查数据字典，以确定该SQL语句的概要是否已经存在。
（4）生成执行计划：使用基本成本优化规则和数据字典的统计表来决定最佳执行计划。
（5）建立二进制代码：基于执行计划，Oracle生成了二进制执行代码。

为了使每条SQL语句的性能能达到优化，建议用户对所有的SQL语句执行EXPLAIN PLAN命令，并查看输出结果，然后对性能低下的SQL语句进行调整。

通过设置执行计划(即：EXPLAIN PLAN命令)，可以了解Oracle在执行SELECT, DELETE, INSERT, UPDATE语句时的情况。所谓SQL语句执行计划就是Oracle优化器在执行每个SQL语句时所采用的执行顺序。执行计划包括以下的几个方面。

A. 语句所引用的表的顺序。
B. 语句所涉及的表的访问方法。
C. 语句中连接操作所影响的各表的连接方法。

Oracle 11g 数据库在执行SQL语句时，都是使用基于代价(或者成本)的优化器--这个代价(或者成本)就是指占用一定的系统资源。

4.2 运行EXPLAIN PLAN

执行计划是Oracle系统在执行SQL语句时的一种执行策略。那么用户如何看到这些执行计划的内容呢？

为了得到Oracle产生的执行计划的报告，必须创建一个表来存放系统检查SQL语句执行计划时所产生的结果，建议在用户的帐户下执行UTLXPLAN脚本来完成plan_table表的创建，该表存放执行计划的信息。

当运行了UTLXPLAN.SQL这个脚本后，Oracle在用户的帐户下创建了table_name表。该表用于存放用户希望产生的SQL语句的执行计划。那么，如何才能使系统生成某个SQL语句的执行计划呢？这就是EXPLAIN PLAN命令的工作，其语法格式如下：

EXPLAIN PLAN [SET statement_id [=] <string literal>]
[INTO <table_name>]
FOR <sql_statment>

4.3 Oracle 11g中SQL执行计划的管理

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

Oracle 11g使用基于代价的优化方式(Cost_Based Optimization, CBO)，这里的代价主要指CPU和内存，优化器主要参照的是表及索引的统计信息。

统计信息给出表的大小，有多少行，每行的长度等信息。

这些统计信息起初在数据库内是没有的，是用户在做ANALYZE(统计分析)后才发现的。很多时候，过期统计信息会令优化器做出一个错误的执行计划，因此用户应及时更新这些数据。

说明：不一定用索引就是最优的，例如一个表只有两行数据，一次IO就可以完成全表的检索，而此时用索引，则需要两次IO，这时对这表做全表扫描就是最优的。

5. 数据库和SQL重演

对于数据库和SQL语句的优化，Oracle 11g提出了一个新特性---那就是数据库重演(Database Replay)和SQL重演(SQL Replay)。

5.1 数据库重演

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

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

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

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

5.2 SQL重演

    数据库重演的简化版本就是SQL重演(SQL Replay)，即只捕获SQL负载，通过SQL负载，应用程序可以再现SQL负载。

6. Oracle的性能顾问

Oracle 数据库中有一个自动数据库诊断监控程序(ADDM)形式的助理DBA，这种机器人式的DBA会不知疲倦地反复搜索数据库系统统计，以标识瓶颈，分析SQL语句，并据此提供多种改进性能的建议。

Oracle自动SQL调整解决方案包括SQL调优顾问(SQL Tuning Advisor)和SQL访问顾问(SQL Access Advisor),可以为应用程序提供综合，自动，具有成本效益的解决方案，减少SQL的调整时间和管理成本。

6.1 SQL调优顾问

SQL调优顾问(SQL Tuning Advisor)是Oracle 10g中引入的，设计它的目的就是为了替代传统的手工SQL调整。SQL调优顾问处理的对象包括那些响应时间很慢或者是占用CUP/Disk很高的SQL。SQL调优顾问收集这些SQL，并且给出自己的建议，它包括下面的部分。
（1）怎样优化SQL的执行计划。
（2）优化后效率的提升幅度。
（3）做出这条建议的理论原理。
（4）直接给出推荐使用的命令。

用户可以有选择性地接收这些建议，然后去调优SQL。随着SQL调优顾问地引入，用户现在就可以让Oracle优化器来自动调整SQL。

6.2 SQL访问顾问

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

（1）分析整个负载而不仅仅是单独的SQL语句。
（2）使访问结构设计更加清晰，以优化应用程序性能。
（3）建议创建和删除某些索引，物化视图和物化视图日志以提高性能。

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