PL/SQL性能优化技巧

最新推荐文章于 2019-05-10 17:50:09 发布

夜半一碗凉泡面

最新推荐文章于 2019-05-10 17:50:09 发布

阅读量236

点赞数 1

分类专栏： PL/SQL 文章标签： PL/SQL

PL/SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

参考资料：

https://blog.csdn.net/sinat_16382631/article/details/52734611
https://blog.csdn.net/dtjiawenwang88/article/details/74892245

1、理解执行计划

1-1.什么是执行计划
        Oracle 数据库在执行sql语句时，oracle的优化器会根据一定的规则确定sql语句的执行路径，以确保sql语句能以最优性能执行。在oracle数据库系统中为了执行sql语句，oracle可能需要实现多个步骤，这些步骤中的每一步可能是从数据库中物理检索数据行，或者用某种方法准备数据行，让编写sql语句的用户使用，oracle用来执行语句的这些步骤的组合被称为执行计划。
      当执行一个sql语句时oracle经过了4个步骤:
        ①.解析sql语句:主要在共享池中查询相同的sql语句，检查安全性和sql语法与语义。
        ②.创建执行计划及执行:包括创建sql语句的执行计划及对表数据的实际获取。
        ③.显示结果集:对字段数据执行所有必要的排序，转换和重新格式化。
        ④.转换字段数据:对已通过内置函数进行转换的字段进行重新格式化处理和转换。

1-2.查看执行计划
查看sql语句的执行计划，比如一些第三方工具需要先执行utlxplan.sql脚本创建explain_plan表。

SQL> conn system/123456 as sysdba  
-- 如果下面语句没有执行成功，可以找到这个文件，单独执行这个文件里的建表语句  
SQL> @/rdbms/admin/utlxplan.sql  
SQL> grant all on sys.plan_table to public;

        在创建表后，在SQL*Plus中就可以使用set autotrace语句来显示执行计划及统计信息。常用的语句与作用如下：
                set autotrace on explain:执行sql，且仅显示执行计划
                set autotrace on statistics:执行sql 且仅显示执行统计信息
                set autotrace on :执行sql，且显示执行计划与统计信息，无执行结果
                set autotrace traceonly:仅显示执行计划与统计信息，无执行结果
                set autotrace off:关闭跟踪显示计划与统计

比如要执行SQL且显示执行计划，可以使用如下的语句：

SQL> set autotrace on explain  
SQL> col ename format a20;  
SQL> select empno，ename from emp where empno=7369;

上面不一定可以执行成功，使用这个：explain plan for sql语句。

SQL> explain plan for  
  2  select * from cfreportdata where outitemcode='CR04_00160' and quarter='1' and month='2015';  
Explained  
  
SQL> select * from table(dbms_xplan.display);  
PLAN_TABLE_OUTPUT  
--------------------------------------------------------------------------------  
Plan hash value: 3825643284  
--------------------------------------------------------------------------------  
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C  
--------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT            |                 |     1 |   115 |     3  
|   1 |  TABLE ACCESS BY INDEX ROWID| CFREPORTDATA    |     1 |   115 |     3  
|*  2 |   INDEX RANGE SCAN          | PK_CFREPORTDATA |     1 |       |     2  
--------------------------------------------------------------------------------  
Predicate Information (identified by operation id):  
---------------------------------------------------  
   2 - access("OUTITEMCODE"='CR04_00160' AND "MONTH"='2015' AND "QUARTER"='1')  
       filter("MONTH"='2015' AND "QUARTER"='1')  
15 rows selected

PL/SQL DEVELOPER提供了一个执行计划窗口，如果在SQL Windows的窗口，按F8是执行该sql,按f5会显示该sql的执行计划。

1-3.理解执行计划

        1.全表扫描(full table scans):这种方式会读取表中的每一条记录，顺序地读取每一个数据块直到结尾标志，对于一个大的数据表来说，使用全表扫描会降低性能，但有些时候，比如查询的结果占全表的数据量的比例比较高时，全表扫描相对于索引选择又是一种较好的办法。
        2.通过ROWID值获取(table access by rowid)：行的rowid指出了该行所在的数据文件，数据块及行在该块中的位置，所以通过rowid来存取数据可以快速定位到目标数据上，是oracle存取单行数据的最快方法。
        3.索引扫描(index scan)：先通过索引找到对象的rowid值，然后通过rowid值直接从表中找到具体的数据，能大大提高查找的效率。

2.连接查询的表顺序

        默认情况下，优化器会使用all_rows优化方式，也就是基于成本的优化器CBO生成执行计划，CBO方式会根据统计信息来产生执行计划.
        统计信息给出表的大小，多少行，每行的长度等信息，这些统计信息起初在库内是没有的，是做analyee后才发现的，很多时候过期统计信息会令优化器做出一个错误的执行计划，因此应及时更新这些信息。
        在CBO模式下，当对多个表进行连接查询时，oracle分析器会按照从右到左的顺序处理from子句中的表名。例如：

select a.empno，a.ename，c.deptno，c.dname，a.log_action from emp_log a,emp b,dept c

在执行时，oracle会先查询dept表，根据dept表查询的行作为数据源串行连接emp表继续执行，因此dept表又称为基础表或驱动表。由于连接的顺序对于查询的效率有非常大的影响。因此在处理多表连接时，必须选择记录条数较少的表作为基础表，oracle会使用排序与合并的方式进行连接。比如先扫描dept表，然后对dept表进行排序，再扫描emp表，最后将所有检索出来的记录与第一个表中的记录进行合并。

如果有3个以上的表连接查询，就需要选择交叉表作为基础表。交叉表是指那个被其他表所引用的表，由于emp_log是dept与emp表中的交叉表，既包含dept的内容又包含emp的内容。

select a.empno，a.ename，c.deptno，c.dname，a.log_action from emp b,dept c,emp_log a;

附：典型SELECT语句完整的执行顺序：

        1）from子句组装来自不同数据源的数据；
        2）where子句基于指定的条件对记录行进行筛选；
        3）group by子句将数据划分为多个分组；
        4）使用聚集函数进行计算；
        5）使用having子句筛选分组；
6）计算所有的表达式；
        7）计算select的字段；
        8）使用order by对结果集进行排序。

3.指定where条件顺序

在查询表时，where子句中条件的顺序往往影响了执行的性能。默认情况下，oracle采用自下而上的顺序解析where子句，因此在处理多表查询时，表之间的连接必须写在其他的where条件之前，但是过滤数据记录的条件则必须写在where子句的尾部，以便在过滤了数据之后再进行连接处理，这样可以提升sql语句的性能。

SELECT a.empno, a.ename, c.deptno, c.dname, a.log_action, b.sal  
  FROM emp b, dept c, emp_log a  
 WHERE a.deptno = b.deptno 
   AND a.empno = b.empno 
   AND c.deptno IN (20, 30)

从SQL执行计划中可以看到const成本值为10。如果使用如下不好的查询方式，const成本值为32。

SELECT a.empno, a.ename, c.deptno, c.dname, a.log_action, a.mgr  
 FROM emp b, dept c, emp_log a  
WHERE c.deptno IN (20, 30) 
  AND  a.deptno = b.deptno

4.避免使用*符号

有时我们习惯使用*符号，如：

SELECT * FROM emp

Oracle在遇到*符号时，会去查询数据字典表中获取所有的列信息，然后依次转换成所有的列名，这将耗费较长的执行时间，因此尽量避免使用*符号获取所有的列信息。

5.使用decode函数

decode是Oracle才具有的一个功能强大的函数。
比如统计emp表中部门编号为20和部门编号为30的员工的人数和薪资汇总，如果不使用decode那么就必须用两条sql语句。

select count(*)，SUM(sal) from emp where deptno=20;  
union  
select count(*)，SUM(sal) from emp where deptno=30;

通过Union将两条SQL语句进行合并，实际上通过执行计划可以看到，SQL优化器对emp进行了两次全表扫描

而通过decode语句，可以在一个sql查询中获取到相同的结果，并且将两行结果显示为单行。

SELECT COUNT (DECODE (deptno, 20, 'X', NULL)) dept20_count,  
       COUNT (DECODE (deptno, 30, 'X', NULL)) dept30_count,  
         SUM (DECODE (deptno, 20, sal, NULL)) dept20_sal,  
         SUM (DECODE (deptno, 30, sal, NULL)) dept30_sal  
  FROM emp;

通过灵活的运用decode函数，可以得到很多意想不到的结果，比如在group by 或order by子句中使用decode函数，或者在decode块中嵌套另一个decode块。decode函数详解可见另一篇文章。

6.使用where而非having

        where子句和having子句都可以过滤数据，但是where子句不能使用聚集函数，如count max min avg sum等函数。因此通常将Having子句与Group By子句一起使用。
        注意：当利用Group By进行分组时，可以没有Having子句。但Having出现时，一定会有Group By
        需要了解的是，WHERE语句是在GROUP BY语句之前筛选出记录，而HAVING是在各种记录都筛选之后再进行过滤。也就是说HAVING子句是在从数据库中提取数据之后进行筛选的，因此在编写SQL语句时，尽量在筛选之前将数据使用WHERE子句进行过滤，因此执行的顺序应该总是这样。
        ①.使用WHERE子句查询符合条件的数据
        ②.使用GROUP BY子句对数据进行分组。
        ③.在GROUP BY分组的基础上运行聚合函数计算每一组的值
        ④.用HAVING子句去掉不符合条件的组。

例子：查询部门20和30的员工薪资总数大于1000的员工信息

select empno,deptno，sum(sal)   
  from emp group by empno，deptno  
having sum(sal) > 1000 and deptno in (20，30);

在having子句中，过滤出部门编号为20或30的记录，实际上这将导致查询取出所有部门的员工记录，在进行分组计算，最后才根据分组的结果过滤出部门 20和30的记录。这非常低效，好的算法是先使用where子句取出部门编号为20和30的记录，再进行过滤。修改如下：

select empno，deptno，sum(sal)   
  from emp where deptno in （20,30）  
group by empno，deptno having sum (sal) > 1000;

7.使用UNION而非OR

如果要进行OR运算的两个列都是索引列，可以考虑使用union来提升性能。
例子：比如emp表中，empno和ename都创建了索引列，当需要在empno和ename之间进行OR操作查询时，可以考虑将这两个查询更改为union来提升性能。

select empno，ename，job，sal from emp where empno > 7500 OR ename LIKE 'S%';

使用UNION：

select empno，ename，job，sal from emp where empno > 7500   
UNION  
select empno，ename，job，sal from emp where ename LIKE 'S%';

但这种方式要确保两个列都是索引列。否则还不如OR语句。

如果坚持使用OR语句：①.需要记住尽量将返回记录最少的索引列写在最前面，这样能获得较好的性能，例如empno > 7500 返回的记录要少于对ename的查询，因此在OR语句中将其放到前面能获得较好的性能。②.另外一个建议是在要对单个字段值进行OR计算的时候，可以考虑使用IN来代替。

8.使用exists而非IN

比如查询位于芝加哥的所有员工列表可以考虑使用IN

SELECT *  
  FROM emp  
 WHERE deptno IN (SELECT deptno  
                    FROM dept  
                   WHERE loc = 'CHICAGO');

替换成exists可以获取更好的查询性能：

SELECT a.*  
  FROM emp a  
 WHERE NOT EXISTS (SELECT 1  
                     FROM dept b  
                    WHERE a.deptno = b.deptno AND loc = 'CHICAGO');

同样的替换页发生在not in 和not exists之间，not in 子句将执行一个内部的排序和合并，实际上它对子查询中的表执行了一次全表扫描，因此效率低，在需要使用NOT IN的场合，应该总是考虑把它更改成外连接或NOT EXISTS。

SELECT *  
  FROM emp  
 WHERE deptno NOT IN (SELECT deptno  
                    FROM dept  
                   WHERE loc = 'CHICAGO');

9.避免低效的PL/SQL流程控制语句

PLSQL在处理逻辑表达式值的时候，使用的是短路径的计算方式。

DECLARE  
   v_sal   NUMBER        := &sal;                 --使用绑定变量输入薪资值  
   v_job   VARCHAR2 (20) := &job;                 --使用绑定变量输入job值  
BEGIN  
   IF (v_sal > 5000) OR (v_job = '销售')          --判断执行条件  
   THEN  
      DBMS_OUTPUT.put_line ('符合匹配的OR条件');  
   END IF;  
END;

首先对第一个条件进行判断，如果v_sal大于5000，就不会再对v_job条件进行判断，灵活的运用这种短路计算方式可以提升性能。应该总是将开销较低的判断语句放在前面，这样当前面的判断失败时，就不会再执行后面的具有较高开销的语句，能提升PL/SQL应用程序的性能。
举个例子，对于and逻辑运算符来说，只有左右两边的运算为真，结果才为真。如果前面的结果第一个运算时false值，就不会进行第二个运算。（与java中的 && 类似）

DECLARE  
   v_sal   NUMBER        := &sal;                 --使用绑定变量输入薪资值  
   v_job   VARCHAR2 (20) := &job;                 --使用绑定变量输入job值  
BEGIN  
   IF (Check_Sal(v_sal) > 5000) AND (v_job = '销售')          --判断执行条件  
   THEN  
      DBMS_OUTPUT.put_line ('符合匹配的AND条件');  
   END IF;  
END;

这段代码有一个性能隐患，check_sal涉及一些业务逻辑的检查，如果让check_sal函数的调用放在前面，这个函数总是被调用，因此处于性能方面的考虑，应该总是将v_job的判断放到and语句的前面。

DECLARE  
   v_sal   NUMBER        := &sal;                     --使用绑定变量输入薪资值  
   v_job   VARCHAR2 (20) := &job;                     --使用绑定变量输入job值  
BEGIN  
   IF (v_job = '销售') AND (Check_Sal(v_sal) > 5000)  --判断执行条件  
   THEN  
      DBMS_OUTPUT.put_line ('符合匹配的AND条件');  
   END IF;  
END;

10.避免隐式类型转换

Oracle数据表列类型是一种强类型数据结构，不同类型的数据有不同的操作使用方法。当不同类型的数据进行直接的操作，没有显式的进行转换时，我们称之为“隐式类型转换”。SQL隐式类型转换可以帮助SQL执行过程，很多时候会引起一些问题，因此在实际操作中应该尽量避免“隐式类型转换”。

数据准备：
创建一个测试表 t_test_char ，字段 c1 varchar2(20)，字段上创建了索引，向该表中添加了100条数据，内容为 1-100数字。
创建一个测试表 t_test4_number，字段 c1 number，字段上创建了索引，向该表中添加了100条数据，内容为 1-100数字。

select * from t_test_char where c1='11';   -- 结果：观察执行计划，使用了索引
select * from t_test_number where c1=11;   -- 结果：观察执行计划，不使用索引，全表扫描，执行的时候做的转换为 (TO_NUMBER("C1")=11)

select * from t_test_char where c1=11;     -- 结果：观察执行计划，使用了索引
select * from t_test_number where c1='11'; -- 结果：观察执行计划，使用了索引，转换为 "C1"=11

结论：
查询时，字符类型字段为查询条件赋值为数值类型时，隐式转换为该字段转换为数值型，不使用索引；
查询时，数值类型字段为查询条件赋值为字符类型时，隐式转换为将字符转换为数值，继续使用索引。

11.其他的一些优化技巧

1）SQL语句尽量用大写的；
        2）使用表的别名；
        3）减少访问数据库的次数；
        4）根据需要用UNION ALL替换UNION；
        5）用EXISTS替换DISTINCT；
6）用TRUNCATE替代DELETE；
        7）用>=替代>；
        8）避免在索引列上使用IS NULL和IS NOT NULL；
9）'!='不走索引。索引只能告诉我们什么存在于表中, 而不能告诉你什么不在表中；

10）'||'是字符连接函数，'+' 是数学函数，停用了索引；

11）删除重复记录；

--最高效的删除重复记录方法：
DELETE FROM TEMP E   
WHERE E.ROWID > (SELECT MIN(X.ROWID)   
          FROM TEMP1 X WHERE X.TEMP_NO = E.TEMP_NO);

12）避免使用耗费资源的操作；

带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作,而其他的至少需要执行两次排序。通常,带有UNION、MINUS、INTERSECT的SQL语句都可以用其他方式重写。

13）避免在索引列上使用计算：

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。

SELECT … FROM TEMP WHERE SAL * 12 > 25000; --低效
SELECT … FROM TEMP WHERE SAL > 25000/12; --高效

夜半一碗凉泡面

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录