oracle 优化分组 sql语句,Oracle SQL语句之常见优化方法 <五>

0、低效SQL语句查询:

SELECT b.sql_text, --SQL內容

a.sid,

a.serial#,

a.status,

a.machine, --哪台机器運行的SQL

a.username, --用戶

a.module, --運行方式

a.action, --Responsibility

c.opname,

c.target,

c.message,

c.sofar totalwork * 100, --工作執行了百分之多少

c.elapsed_seconds, --己?用了多少(秒)

c.time_remaining, --剩多少(秒)

round(a.last_call_et 60 60,

2)

FROM v$session         a,

v$sqlarea         b,

v$session_longops c

WHERE a.sql_hash_value = b.hash_value(+)

AND a.sid = c.sid(+)

AND a.serial# = c.serial#(+);

1、Where中的优化：

对可以过滤数据最多的，放到后面，原理也是Oracle执行从下到上(从右到左)的顺序。

2、From字段中的优化：

Oracle安照从右到左的顺序加载表数据，应该把可以排除数据最多的表放到后面(基础表)。

比如，在关联查询中，把课程表放到后面，成绩表放到前面，因为课程表数据一般比较少，关联的时候可以快速的过滤掉一些成绩数据。

3、使用具体的列名替代 *：

这样可以省去从字典表中解析的过程 .Oracle执行sql语句过程：

在共享池中搜索sql语句是否已经存在,验证sql是否语法精确,执行数据字典验证表和列的定义,获取对象的分析锁，以便在语句的分析过程中对象的定义不会改变,检查用户是否具有相应的操作权限,确定语句最佳执行计划,将语句和执行方案保存到共享的sql区。

4、使用？参数形式：

尽量不要使用拼接字串的方式，因为带有？的参数形式会缓存分析结果，省去上面很多步骤。

5、使用decode函数：

decode不会重复扫描相同记录或重复连接相同的表，减少表的扫描次数。

6、使用truncate替代delete：

delete：逐条删除，并保存回滚还原点。DML(数据操作语言insert，update，delete，merge)

truncate不会保存回滚点，是DDL(数据定义语言，如drop，alter)

在oracle里DML是可以rollback的，而DDL是不可以的

如果数据量大，差别很大的。

7、尽量多用commit：

Oracle只有在commit后才会提交(区别于sqlserver)，如果没有提交，会在内存中保存很多数据。commit后释放的资源有：

回滚上段用于恢复数据库的记录信息,被程序语句获得的锁,

Redo log buffer中的空间,为管理上述3种资源的内部花费.

提交的频率和内存，缓存，索引等规模有关系。

8、尽量使用where语句替代having子句：

在分组或者配合group的时候会使用having。

where会直接过滤掉数据，使用having往往会配合group，检索出数据后，会带着数据进行排序、统计等。

9、on/where/having排序使用顺序：

on：做的是数据映射，在映射时，把没用的数据直接过滤掉了

where：先进行一个全表的搜索，之后再进行数据筛选

where又比having要快，having中带的垃圾数据同样也做了运算

10、减少对表的查询(特别是自查询)：

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

Select tab_name from tables where (tab_name,db_ver)=(select tab_name,db_ver from tab_columns where version=604)

11、一定使用表别名(特别是多表查询)：

减少对sql语句解析的时间并减少由多个表相同的Column名歧义引起的语法错误，如果不使用别名，会去字典表中查找，判断是否有列名歧义。

12、使用exists替代in

使用not exists替代not in：

Oracle采用命中即返回的方式，在多表链接查询时，如果使用in会导致子查询的表全表遍历，并排序、合并，这时候可以使用外链接或not  exists 替代。

13、识别低效率执行的语句：

各种sql优化的图形工具层出不穷，但可以写出自己的sql工具来解决问题

SELECT executions,

disk_reads,

buffer_gets,

round((buffer_gets - disk_reads) buffer_gets,

2) hit_radio,

round(disk_reads executions,

2) reads_per_run,

sql_text

FROM v$sqlarea

WHERE executions > 0

AND buffer_gets > 0

AND (buffer_gets - disk_reads) buffer_gets < 0.8

ORDER BY 4 DESC;

下期更精彩