Oracle-优化SQL语句

建议不使用（*）来代替所有列名

用truncate代替delete

　　在SQL*Plus环境中直接使用truncate table即可；要在PL/SQL中使用，如：

创建一个存储过程，实现使用truncate命令动态删除数据表

create or replace procedure trun_table(table_deleted in varchar2) as

cur_name integer;

begin

cur_name:=dbms_sql.open_cursor;

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;

/

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

　　commit所释放的资源如下：

回滚段上用于恢复数据的信息，撤销表空间也只作短暂的保留

被程序语句获得的锁

redo log buffer中的空间

Oracle为管理上述资源的内部花费

尽量减少表的查询次数

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

用[not]exists 代替[not]in

表连接优化

驱动表的选择

　　驱动表是指最先被访问的表（通常以全表扫描的方式被访问）；通常只有from 表后都建立索引，优化器才能按照(每个表的物理大小、索引状态，选择花费最低的执行路径)驱动表的规则来对待

where子句的连接顺序

　　Oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前。那些可以过滤掉最大数据记录的条件必须写在where子句的末尾，也就是在表连接之前过滤掉的记录越多越好

合理使用索引

何时使用索引

　　对于只从总行数中查询2%-4%的表，可以考虑创建索引，创建索引的基本原则

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

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

3.表中的大多数查询都包含相对简单的where从句

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

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

选择复合索引主列

　　如果在建立索引时采用了几个列作为索引，则在使用时也要按照建立时的顺序来描述，也就是说，主列是最先被选择的列

避免全表扫描大表

　　会导致全表扫描的情况：

1.所查询的表没有索引

2.需要返回所有的行

3.带有like并使用%这样的语句

4.对索引主列由条件限制，但使用了函数，则Oracle使用全表扫描

5.is null is not null !=等子句

监视索引是否被引用

　　从Oracle9i开始，用户可以对索引进行监视，看看所创建的索引是否被使用

如：监视学生成绩表studentgrade的grade_index索引是否被使用

alter index grade_index monitoring usage;

select * from v$object_usage;

优化器的使用

优化器的概念

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

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

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

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

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

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

Oracle11g中SQL执行计划的管理

　　Oracle11g使用基于成本的优化方式（Cost_Based Optimization,CBD），这里的代价主要指CPU和内存，优化器主要参照的时表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在数据库内是没有的，时用户做analyze后才出现的。很多时候，过期统计信息会令优化器做出一个错误的执行计划，因此用户应及时更新这些消息。

数据库和SQL重演

　　对于数据库和SQL语句优化，Oracle11g中提出了两个新特性——数据库重演（Database Replay）和SQL重演（SQL Replay）

数据库重演

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

　　一般使用这一特性，会带来约5%性能负担

SQL重演

　　　　只捕获SQL，通过SQL负载，应用程序可以再现SQL影响

Flashback可以向后闪回；Replay可以向前推演。

Oracle性能顾问

　　Oracle数据库中有一位自动数据库诊断监控程序（ADDM）形式的助理DBA，这种机器人式的DBA会不知疲倦地反复搜索数据库性能统计，以标识瓶颈、分析SQL语句，并据此提供多种改进性能的建议。Oracle自动SQL调整解决方案包括SQL调优顾问（SQL Tunning Advisor）和SQL访问顾问（SQL Access Advisor），可以为应用程序提供综合、自动、具有成本效益的解决方案，减少SQL的调整时间和管理成本

SQL调优顾问

Oracle10g引入的，为了替代传统的手动SQL调整

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

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

1.这样调整SQL的执行计划

2.优化后效率的提升幅度

3.做出这条建议的理论原理

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

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

SQL访问顾问

　　设计目的是获得有关基于实际频率和使用类型（而非数据类型）进行分区、索引和创建物化视图以改进模式设计的建议。它的特点：

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

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

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