对于一个PL/SQL应用程序来说,影响性能的原因是来自多方面的,比如不良的编程方法,数据库对象本身的构建不合理,表或索引的构建等都可能导致PL/SQL性能低下。
这里总结了一些常见的影响性能的原因以及提高性能的方案,可以把这些当成一种编程的习惯。
尽量使用存储过程,避免使用PL/SQL匿名块
存储过程创建后,Oracle会对其进行语法句法分析,以编译的额形式存储在数据库中,当客户端调用时,只需要发送一条调用指令,避免了匿名块在网上传送大量源代码,降低另外网络通信的负担,同时因为仅在创建时编译一次,因此提升了程序运行的性能。
编写共享SQL语句
Oracle在执行SQL语句时,在第一次解析后,会将SQL语句放在位于系统全局区SGA中的共享池中,这块内存区域可以被所有数据库用户共享,因此在执行一个SQL语句时,比如在PL/SQL语句中的游标执行SQL语句,如果Oracle检测到它和以前已运行过的语句相同,就会使用已经被解析的语句,使用最优的执行路径。
不过要注意,由于Oracle只对简单表进行缓存,因此对于多表连接查询并不适用。
Oracle在比较语句时,会进行字符级的比较,且大小写敏感,也就是说,SELECT * FROM emp;
和SELECT * FROM EMP;
不会进行共享。所以我们在编写SQL语句时,最好注意采用大小写一致约定,比如关键字、保留字大写,用户声明的 标识符小写。通过设计自己的编写约定并遵守这些约定,使要处理的语句与共享池中的相一致,有助于运行性能的提高。
使用BINARY_INTEGER
和PLS_INTEGER
声明整型
BINARY_INTEGER
和PLS_INTEGER
比NUMBER类型具有更高的性能。
在过程中传递大数据参数时使用NOCOPY编译提示
当创建过程或函数时,IN模式总是传递指针,而OUT和IN OUT传递的则是值的副本,也称为值传递。当涉及较大容量的参数传递时,会严重降低性能,此时应该考虑使用NOCOPY编译提示来按引用传递参数,参数的大小越大,效果就越明显。
看个例子:
DECLARE
TYPE test_tbl_type IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER; --定义索引表类型
test_tbl test_tbl_type; --定义索引表类型的变量
--定义内嵌子程序,在IN OUT参数中使用NOCOPY提示来按引用传递
PROCEDURE TEST (arg_cnt IN PLS_INTEGER, arg_tbl IN OUT NOCOPY test_tbl_type)
IS
BEGIN
FOR cnt_test IN test_tbl.FIRST .. arg_tbl.LAST --依序循环索引表
LOOP
arg_tbl (cnt_test) := arg_tbl (cnt_test) + arg_cnt; --为形式参数表赋值
END LOOP;
END;
BEGIN
FOR cnt IN 0 .. 10000
LOOP
test_tbl (cnt) := cnt; --初始一个较大的索引表
END LOOP;
FOR cnt IN 0 .. 10000
LOOP
TEST (cnt, test_tbl); --分10000次调用函数,用来测试性能
END LOOP;
END;
使用RETURNING获取返回值
在使用DML语句处理对象行的数据时,如果要获取行的返回值,应该使用RETURNING子句,以便减少对SQL的执行次数,提高执行的效率:
INSERT INTO ... VALUES(...) RETURNING COL1 INTO :COL1;
UPDATE ... SET ... RETURNING COL1 INTO :COL1;
DELETE FROM ... RETURNING COL1 INTO :COL1;
使用RETURNING不仅可以返回多列数据,也可以返回数据保存在数组等数据类型中:
RETURNING COL1, COL2 INTO :COL1, COL2;
RETURNING COL1 INTO :COL1_ARRAY;
相对于再次查询数据表来获取返回结果,使用RETURNING性能会比较好一点。
避免使用动态SQL语句
动态SQL语句虽然提供了编程上的灵活和便利,但是过多的使用动态SQL会严重降低PL/SQL应用程序的性能。如果不得不使用动态SQL,则应该总是使用本地动态SQL,即EXECUTE IMMEDIATE或OPEN FOR而不要使用DBMS_SQL
,因为DBMS_SQL
不光编写代码复杂,而且性能也不高。
尽量使用BULK批处理
如果操作涉及大量的数据,则可以通过把大量的数据进行一次性处理来提升性能,比如可以将数据放到索引表、嵌套表和变长数据中,通过FORALL或BULK COLLECT INTO等批处理语句,一次性处理大的数据量,提升性能。
看个例子:
DECLARE
TYPE emp_tbl_type IS TABLE OF emp%ROWTYPE
INDEX BY PLS_INTEGER; --定义索引表类型
emp_tbl emp_tbl_type; --定义索引表变量
CURSOR emp_cur
IS
SELECT *
FROM emp; --定义打开员工资料的游标
BEGIN
OPEN emp_cur; --打开游标
FETCH emp_cur
BULK COLLECT INTO emp_tbl; --批量提取游标数据
CLOSE emp_cur; --关闭游标
END;
避免低效的 PL/SQL流程控制语句
PL/SQL在处理逻辑表达式的时候,比如IF条件控制语句,使用的是最短路径的计算方式。也就是说应该总是将开销较低的判断语句放在前面。