PL/SQL性能优化

一:SQL性能优化原理

1.1sql处理体系结构

1.2执行计划

sql语句转换前的步骤:
1.语法检查:检查sql语句的拼写是否正确
2.语义分析:核实所有与数据字典不一致的表或列的名字
3.概要存储检查:检查数据字典,以确定该sql语句的概要信息是否已经存在
4.生产执行计划:使用CBO规则和数据字典中的统计表来决定最佳执行计划
5.生成二进制代码:基于执行计划,生成可执行代码

执行计划是oracle在执行每个sql语句时所采取的执行顺序.
执行计划包括:
1.语句所引用的表的顺序
2.语句所设计的表的访问方式
3.语句中连续操作所影响到的各表的连接方法

查看计划前授权:

--系统用户执行
SQL> @D:\Oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utlxplan.sql;  --本地电脑路径,执行utlxplan.sql
Table created
Executed in 0.047 seconds

SQL> grant plustrace to scott;  --授权
Grant succeeded
Executed in 0.009 seconds

--scott用户
SQL> @D:\Oracle\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utlxplan.sql;
Table created
Executed in 0.032 seconds

查看执行计划:

1.授权,同上
2.查看计划步骤:
     1.sql>set timing on;
     2.sql>set autotrace on;
     3.执行sql语句即可自动显示执行计划

3.autotrace其他命令解释:
      SET AUTOTRACE OFF 此为默认值，即关闭Autotrace
      SET AUTOTRACE ON EXPLAIN 只显示执行计划
      SET AUTOTRACE ON STATISTICS   只显示执行的统计信息
      SET AUTOTRACE ON 包含2,3两项内容
      SET AUTOTRACE TRACEONLY   与ON相似，但不显示语句的执行结果。
     

1.3共享池(Shared Pool)

首先: oracle的解析程序对sql语句的解析过程是相当复杂和消耗资源的过程,如果每条sql语句都经过解析就会影响性能
Oracle对SQL语句进行了概括和抽象,将SQL语句提炼为两部分:
1.sql语句静态部分,也就是sql语句本身的关键字,所涉及的表名称以及表的字段等
2.sql语句动态部分,也就是sql语句中的值(表里的数据)

静态部分: 固定,有限
动态部分:无限

优化方向:
1.动态部分对解析的影响较静态部分对解析的影响来说可忽略不计
2.如果静态部分一致,则不同的动态部分解析的结果基本一样

oracle会把sql语句缓存在共享池中,那么缓存哪些?哪些sql语句容易匹配得到?
1.绑定变量的sql,因为绑定变量就会只比较静态部分,而静态部分有限,更容易匹配一致的sql
2.如果不绑定变量,则比较动态和静态部分,而动态部分太多,变化大,不容易匹配

共享池大小设置:
1.太小不利于缓存sql,太大影响性能
2.版本11g后只需要设置sga_target,让oracle自己决定共享池的大小

共享池组成:
1.库缓存:存放最近执行的sql语句,存储过程,函数,解析树以及执行计划
2.数据字典缓存:存放sql执行过程中,所参照的数据字典的信息,包括sql语句所涉及的表名,表的列,权限信息等

1.4共享sql语句

当前执行的sql和共享的sql语句必须完全相同才会匹配
1.所有字符必须相同
2.所有字母的大小写必须相同
3.语句中所使用的空格必须相同

1.5优化器

优化器->查询优化器(查询最影响性能,查询包括select以及DML语句中的查询要求)
优化器分类:
1.RBO:规则优化器,采用启发式的方法或规则来生成执行计划,计划不一定是最优化的,所以 11g版本已经取消
2.CBO:成本优化器,计算执行计划的成本,选择成本最小的.

成本优化器的统计信息:
1.表统计:包括记录数,block数和记录平均长度
2.列统计:列中不同值得数量(NVD),空值得数量和数据分布直方图
3.索引统计:索引叶块的数量,索引的层数和聚集因子
4.系统统计:I/O性能和利用率与CPU性能和利用率

1.6CBO的内容

CBO = 查询转换器 + 评估器 + 计划生成器

1.6.1查询转换器
视图合并
谓词推进
非嵌套子查询
物化视图的查询重写

1.6.2评估器(计算下面三个值来评估总体成本)
选择性:0~1
基数:行数
成本:I/O , CPU,内存的数量

1.6.3计划生成器
生成多个计划,选择成本最小的那个

扩展:Hint

Hint具有最高优先级,可以通过Hint使优化器根据用户的需求来生成指定的执行计划
Hint分类:
优化模式: FIRST_ROWS(n) , ALL_ROWS 等
访问路径:INDEX , FULL , CLUSTER , INDEX_FFS等
查询转换:MERGE , USE_CONCAT , NO_EXPAND等
连接顺序:ORDERED , START
连接操作:USE_NL , USE_HASH , USE_MERGE等
并行执行:PARALLE , NOPARALLEL , PARALLEL_INDEX 等
其他类型:APPEND , UNNEST , CACHE等

用法: select /+ ALL_ROWS/ …

二:查询操作优化

查看执行计划的方式:
1.sql>set autotrace on;
2.explain plan for “sql语句”; + select * from table(dbms_xplan.display());

2.1使用CASE表达式替代多个查询

SQL> select count(1) from t2 where id<3;
  COUNT(1)
----------
         1
Executed in 0.052 seconds

SQL> select count(1) from t2 where id between 3 and 6;
  COUNT(1)
----------
         3
Executed in 0.066 seconds

SQL> select count(1) from t2 where id >6;
  COUNT(1)
----------
         0
Executed in 0.062 seconds


--替换成case
SQL> select count(case when id < 3 then 1 else null end) as low ,count(case when id between 3 and 6 then 1 else null end) as mid , count(case when id >6 then 1 else null end) as high from t2;
       LOW        MID       HIGH
---------- ---------- ----------
         1          3          0
Executed in 0.077 seconds

2.2避免使用’*’

2.3查询表顺序的影响

如果没有索引及oracle没有对表进行统计分析的情况下,oracle会按照表出现的顺序进行连接,由此因为表的顺序不对就会产生十分消耗服务器资源的数据交叉.

如果对表进行统计分析,oracle会自动先进行小表的连接,再进行大表的连接

2.4使用表别名(Alias)

当在sql语句中连接多个表时,应使用表的别名并把别名前缀于每个字段前,这样可以减少解析的时间,并减少同名字段引起的歧义.

2.5使用where代替having子句

因为having会在检索处所有数据后才对结果集进行过滤,可以通过where子句先限制数据量,减少开销
另外:一般的条件都应该写在where子句中

2.6减少对表的查询

在含有子查询的sql中减少对表的查询

--改前
select id,name from t1 where id=(select v_id from t2 where... ) and name=(select v_name from t2 where...)

--改后
select id,name from t1 where(id,name)=(select v_id,v_name from t2 where ...)

2.7where子句后面的条件顺序影响

where子句中第一个条件就应该先查询可能出现最少数据量的条件,可以减少后续其他匹配条件的操作数据量

2.8IN操作符

尽量不要使用IN操作符,因为 IN 的sql性能比较低.
oracle在使用in的sql时会试图将其转换成多个表的连接,如果转换不成功则先执行in里面的子查询,再查询外层记录,如果转换成功则直接采用多表连接方式查询

所以: 不要使用 in

2.9 NOT IN 操作符

强烈不推荐

因为不能使用索引

2.10 不等号 <> , !=

不等号永远不会使用索引,强烈不推荐

a <> 0 ;
改为:
a > 0 or a <0;

2.11 IS NULL 或 IS NOT NULL

也会停止索引,执行全表扫描
因为B-Tree索引是不索引空值的,可以在表设计的时候,使用 not null 约束索引列

改前: a is not null
改后:a >’’; 或者 a>0;

可以使用默认值代替null

2.12 >和< 操作符

可以使用索引,注意范围,不同范围的数据量差别可能很大

2.13 Like操作符

like的模糊匹配的第一个字符如果是 % 就不使用索引

改前: like “%abc%”; --不使用索引
改后:like “abc%” ; --使用索引

2.14 UNION操作符

union会去重
union all不会去重

如果保证不会重复的前提下,使用union all更好

2.15 用表连接替换EXISTS

通常下 连接 效率好过exists
改前:
select id,name from t1 where exists(select 1 from t2 where t1.id = t2.id);

改后:
select id,name from t1 join t2 on t1.id = t2.id;

2.16 使用DECODE 函数

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表
改前:
select count(1),sum(salary) from t1 where job=‘经理’;
select count(1),sum(salsry) from t1 where job=‘总监’;

改后:
select count(decode(job,‘经理’,1,null)) as count1 , count(decode(job,‘总监’,1,null)) as count2 ,
sum(decode(job,‘经理’,salary,null)) as sal1 , sum(decode(job,‘总监’,salary,null)) as sal2 from t1;

2.17 整合无关联的数据库访问

可能提高效率,但是可读性不高

三:其他操作优化

3.1删除重复记录

使用 ROWID

3.2用truncate 代替 delete

truncate只适用于删除全表,是DDL操作
因为truncate会立刻释放空间,而delete不会

3.3尽量多使用COMMIT

commit可以释放资源
1.回滚段用于恢复数据的信息
2.被程序语句获得的锁
3.REDO LOG BUFFER 中的空间
4.oracle 为管理上述三种资源中的内部花费

使用commit要考虑事务的完整性

四:使用绑定变量

绑定变量是基于会话的,如果重新建立会话就需要重新设置变量

sql>variable my_id varchar2(20)
sql>BEGIN 
     :my_id :='001';
     end;/

sql>select * from table where id=:my_id;  --绑定变量my_id

--即使修改变量的值还可以使用相同的语句来重复查询

五:利用索引

5.1优化器的所有扫描

所有不仅包含被索引的字段,还有rowid
如果检索在索引内则只检索索引表,不会检索表
如果检索表,会通过索引找到rowid再去检索表,找到具体行

索引扫描类型:
1.唯一索引
2.索引范围扫描
3.索引降序范围扫描
4.跳跃式索引扫描
5.全索引扫描
6.快速全索引扫描
7.索引连接

5.2使用索引的基本原则

对于从表中总行数查询2%~4%的数据,可以考虑创建索引
什么时候创建索引考量:
1.建立索引的目的就是要帮助查询,如果查询不到则没必要创建索引
2.需要平衡查询和DML需要,常用于子查询的表应建立索引
3.对于经常以查询关键字为基础的表,并且该表中的数据分布较均匀
4.以查询关键字为基础,表中的数据随机排序
5.包含的列数相对比较少的表
6.表中的大多数查询都包含相对简单的where子句
7.缓存命中率低,并且不需要操作系统缓存

以哪列数据作为索引原则:
1.选择where子句频繁使用的关键字作为索引列
2.选择sql语句中频繁用于进行表连接的关键字作为索引列
3.不要用那些频繁修改的列作为索引
4.把索引创建到不同的表空间中
5.用统一的extent大小,5个block倍数或者tablespace 指定的minimum extent的倍数
6.创建索引考虑用nologging参数,重建索引时也一样
7.创建索引时initrans值应该比相应的table的值更高一些
8.对常用sql语句的where条件中的列建立唯一索引或组合索引,组合条件查询中相应的组合索引更有效
9.对于组合索引,根据列的唯一概率安排索引顺序
10.如果一个列具有很低的数据基数,并且有空值,不应所为索引列
11.如果where语句中必须对查询列采用函数查询,最好建立函数索引
12.对于低基数集的列,并包含or等逻辑运算,考虑用bitmap索引,对于从大量行的表中返回大量的行时也可以考虑bitmap索引
13.避免在有大量并发DML运算的表中使用bitmap索引

符合索引原则:
1.符合索引的使用原则是第一个条件应该是符合索引的第一列,依此类推,否则符合索引不会被使用,所以符合所以不能替代多个单一索引
2.应该选择在where子句条件中频繁组合使用的关键字
3.如果几个查询都选择相同的关键字集合,那么可以考虑创建符合索引
4.对索引中的所有列执行搜索或仅对前几列执行搜索时,符合索引非常有效,仅对后面的任意列执行搜索时,符合索引则没有所用

5.3 监视索引使用情况

主键完整性约束而自动变成索引,其他索引如果不使用则会影响性能

sql> alter index [schema.]index_name  monitoring usage;
sql> select * from v$object_usage;

5.4 不能使用索引的情况

全表扫描的情况:
1.所查询的列无索引
2.需要返回所有行
3.需要检索表中绝大多数的数据
4.表非常小(表 小于 DB_FILE_MULTIBLOCK_READ_COUNT,则只需要一次I/O)
5.高并行度
设置高并行度: alter table table_name parallel(degree 10);
使用高并行度: /+ full(table_name) parallel(table_name degree)/
6.太旧的统计数据
7.对索引主列有条件限制,但使用了函数
8.带有 is null / is not null / != / <>/like “%…”