您的位置:首页 - 教程 - 数据库 - 正文
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.建议创建和删除某些索引、物化视图和物化视图日志以提高性能


评论: