Oracle优化(恒生内部常规分享) 优化oracle sql 语句性能的方法

本次主要通过常用的语法、优化器以及函数使用方面进行回顾

对象命名规则

表：THISFUND

char/varchar2：c_ ; number: f_ ; date : d_ ; 序号/个数/天数：l_

索引：IDX_HISFUND_INITDATE

包：Pkg_pkgname

存储过程：Prc_procedurename

函数：func_functionname

触发器：Tri_triggername

视图：Vi_viewname

主键：Pk_primarykeyname

外键：Fk_foreignkeyname

序列：Seq_sequencename

Dblink: Dlk_dblinkname

变量命名规则

输入变量：iinputname

输出变量：ooutputname

内部变量：V_innername

游标命名：cursorname

为了增加代码的可读性，注释必不可少,建议对已发现的错误注释信息及时的修改

一、语法

* insert

禁止使用insert into table_name values(?,?,?) ,

应该指定插入的字段名,例如: insert into table_name (col1, col2,…) values(?,?,…)

* select

1.使用SELECT语句时，禁止使用select * ，应当指出具体查询的字段

2.尽量避免使用select子句。

* where

1.字符型字段（以“c_”开头）必须加单引号,避免where查询条件做隐式转换时后出现混乱

2.不等于统一使用"<>"，弃用!=

3.误区：尽量使用exists、not exists 替代in、not in???

在oracle 8i，9i+ rbo 时代，可以说着这样的，

但是在ORACLE 10g+cbo 开始，最终还是看解析计划如何执行来判断

(并不否认很多情况下exists 、not exists的性能都比in 、not in 好，可能是由于select的条件不一样了，index可能也不一样了， 导致cbo选择的执行计划更优)cost

4.ORACLE采用自下而上的顺序解析WHERE子句, 根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾，这样cbo可以得到更好的执行计划。

5.用>=替代>

* delete

1.>用truncate替代delete删除全表记录,

原因：

delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;

truncate时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复. 因此很少的资源被调用,执行时间也会很短.

2.>最高效的删除重复记录方法 (因为使用了ROWID)

DELETE FROM EMP E

WHERE E.ROWID >

(SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

* 索引

1.> 当返回结果在大概30%以内时，走索引效率较高，而数据量大于30%时，全表扫描更高效。

2.> 尽量避免使用有索引的字段进行计算，否则会导致优化器无法选择索引去执行sql语句。

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

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

3.> 查询中未使用条件限制，即便存在索引 ，SQL语句查询不会使用此索引。

4.> 索引太多会导致更新表的速度变慢; 特别是批处理时，性能下降尤其明显。

可以考虑先删除索引再执行插入脚本，然后再重新建立索引

5.> 定期的重构索引是由必要的。

语法：alter index ITRUSTCONTRACTDETAILS1 /*索引名称*/ rebuild;

* 多条记录的CRUD，建议使用批量提交，降低事务的提交频度

* SQL语句应使用绑定变量实现SQL语句的共享，以提高性能

注意：由于sql共享区的共享sql块采用严格匹配，空格不一样都不会实现共享；所以应使用sql美化器对所需要共享的sql进行美化。以实现共享（针对使用绑定变量的sql语句）

例如：

未使用绑定变量：

begin

for i in 1 .. 10000 loop

execute immediate 'insert into ta (l_serialno) values ('||i||')';

end loop;

commit;

end;

执行时间：9.688s

使用绑定变量：

begin

for i in 1 .. 10000 loop

execute immediate 'insert into ta (l_serialno) values (:v_serialno)' using i;

end loop;

commit;

end;

执行时间：0.188s

二：优化器

SQL分析和执行的优化工具，负责生成、制定SQL的执行计划。

* RBO: 基于规则的优化器。

适用版本：8i，9i+ （10g之前）

优化规则：

1> 选择from最后一张表作为基础表，基础表的选择原则为数据量最少的表。

2> 2个以上的表连接查询, 需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

例如：

高效： SELECT * FROM LOCATION L, CATEGORY C, EMP E

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN;

低效： SELECT * FROM EMP E, LOCATION L, CATEGORY C

WHERE E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

AND E.EMP_NO BETWEEN 1000 AND 2000

* CBO:基于代价的优化器.

规则：检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.

1. > 统计信息（Statistic ）

Statistic ：例如表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息Statistic 。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划优化器会根据对象的统计信息生成执行计划。如果统计信息比较陈旧，那么高效的执行计划将无从谈起。

收集方式：

1.使用gather_stats_job自动收集是在创建数据库时自动创建的，并由调度程序进行管理。他会收集数据库中优化程序统计信息缺失或已过时的所有对象的统计信息。

2.使用dbms_stats 程序包手动收集收集的是系统统计信息。(常用)

(语法：dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);)

3.通过设置数据库初始化参数进行收集。

4.通过从另一个数据库导入统计信息进行收集。

https://www.cnblogs.com/sunmengbbm/p/5775211.html

2. > 执行计划

>使用 EXPLAIN PLAN 分析SQL语句的执行计划：

EXPLAIN PLAN 可以在不执行SQL的情况下分析语句. 通过分析, 就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.

>语法：explain plan for select count(*) from test;

EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.

> pl/SQL Developer可直接使用F5进行查看执行计划

三：函数及关键字的使用

1.> 使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

低效：

SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%';

SELECT COUNT(*), SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%';

高效：

SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT,

COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT,

SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,

SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL

FROM EMP

WHERE ENAME LIKE ‘SMITH%';

2.> 用EXISTS替换DISTINCT

低效:

SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效:

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

3.>使用hints的方式建议cbo优化器走索引或者全表扫描

例如：

强制走全表：SELECT /*+ FULL(EMP) */

强制走索引: SELECT /*+ INDEX(tbl_name idx_name) */

4.> 物化视图

语法：SELECT /*+ materialize*/ A.a1, A.a2 from A

下面场景函数被执行了两次：

SELECT rp.*,

rp.LAST_VALUE - func_GSTl(rp.TYPE, rp.THISYEAR) AS LEFT_VALUE,

tm.RECORD_NAME,

tm.RECORD_UNIT

FROM TBL_RECORD_PERSONAL rp, TBL_M_TOTALRECORD tm

WHERE rp.RECORD_TYPE = tm.RECORD_TYPE

AND rp.LAST_VALUE - func_GSTl(rp.TYPE, rp.THISYEAR) AS LEFT_VALUE >0

在10g之后，with子句之后的子查询结果会被缓存下来，改成下面的书写方式效率依旧没有提高！

WITH V_TMP AS {

SELECT /*+ materialize*/

rp.*,

rp.LAST_VALUE - func_GSTl(rp.TYPE, rp.THISYEAR) AS LEFT_VALUE,

tm.RECORD_NAME,

tm.RECORD_UNIT

FROM TBL_RECORD_PERSONAL rp, TBL_M_TOTALRECORD tm

WHERE rp.RECORD_TYPE = tm.RECORD_TYPE

}

SELECT /*+ NO_MERGE(V_TMP) FULL(V_TMP) */ *

FROM V_TMP

WHERE LEFT_VALUE > 0;

换成使用物化视图的方式，花费时间减少一半：

WITH V_TMP AS {

SELECT /*+ materialize*/

rp.*,

rp.LAST_VALUE - func_GSTl(rp.TYPE, rp.THISYEAR) AS LEFT_VALUE,

tm.RECORD_NAME,

tm.RECORD_UNIT

FROM TBL_RECORD_PERSONAL rp, TBL_M_TOTALRECORD tm

WHERE rp.RECORD_TYPE = tm.RECORD_TYPE

}

SELECT /*+ NO_MERGE(V_TMP) FULL(V_TMP) */ *

FROM V_TMP

WHERE LEFT_VALUE > 0;

减少访问数据库的次数

低效：(访问了两次数据库)

SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342;

SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;

高效：（只访问了一次数据库）

SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE

FROM EMP A,EMP B

WHERE A.EMP_NO = 342 OR B.EMP_NO = 291;