论文部分内容阅读
【摘要】数据库的性能是大型应用程序的性能瓶颈，介绍了Oracle数据库下存储过程及存储过程在大型应用程序设计中的应用，结合实例提出了Oracle平台下存储过程的优化方法。
【关键词】Oracle；存储过程；性能优化
1引言
在大型应用程序中，对数据的读取不仅频繁而且数据量极大，因此在大型应用程序设计中对数据库的访问成为了整个系统最大的性能瓶颈。存储过程作为数据库对象存储在数据库中，执行一次后，其编译好的代码存储在高速缓冲存储器中，再次使用时直接调用，无需再次编译即可执行，使执行速度大大提高。因此，使用存储过程完成对数据库的操作，可以有效提高数据存储和查询的速度。
大量使用存储过程也会带来一定的副作用，当存储过程中封装了大量的业务逻辑后，其执行过程对数据库服务器增加了沉重的压力，因此優化存储过程的性能就显得尤为重要。
2 Oracle存储过程介绍
存储过程是存储在数据库中的一段存储程序，首先编写PL/SQL程序完成特定的操作，并命名保存，当需要执行此操作时可以使用EXECUTE指令调用。存储过程以数据库为基础接收输入参数，并将结果返回给应用程序。相对于SQL语句，其网络通信流量更小，执行速度更快。而且当Oracle存储过程装载到内存中后，可被多个应用共享，节省内存，能有效提升应用系统的性能。在Oracle中创建存储过程的语句为：
CREATE OR REPLACE PROCEDURE 存储过程名字
(参数1 INNUMBER,
参数2 INNUMBER
…….
)AS
变量1 INTEGER:=0;
变量2 DATE;
BEGIN
END存储过程名字
3存储过程的应用
系统充分合理地利用存储过程，可以提高服务器吞吐能力，利用存储过程来从近50个表中获取数据组合成一个通用的表，产生近8万条记录，而所需时间约7秒钟，如果这种操作借助于较合理的动态网页可以将服务器开发提高到一个高度，充分利用存储过程，可以减轻网页设计带来的繁重处理，而使所编写的代码得到共享和合理的利用，并将代码藏于服务器的数据库内部，使得一些技术得到的保密，这也是存储过程的一大特色。
4存储过程优化方法
存储过程优化过程中首先要选用适合的ORACLE优化器，ORACLE的优化器共有3种:基于规则、基于成本、选择性[4]。在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。
4．1选择最有效率的表名顺序(基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
4．2 WHERE 子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.例如:
(低效)SELECT…FROMEMP E
WHERE SAL > 50000 AND JOB = 'MANAGER' AND 25 < (SELECT COUNT(*)
FROM EMP WHERE MGR=E.EMPNO);
(高效)SELECT … FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = 'MANAGER';
4．3 SELECT 子句中避免使用" * "
当你想在 SELECT 子句中列出所有的 COLUMN时，使用动态 SQL 列引用" * "是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE 在解析的过程中，会将" * "依次转换成所有的列名，这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。
4．4 用TRUNCATE替代DELETE
当删除表中的记录时，在通常情况下，回滚段用来存放可以被恢复的信息.如果你没有 COMMIT 事务，ORACLE会将数据恢复到删除之前的状态而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。
4．5 减少对表的查询
在含有子查询的SQL 语句中，要特别注意减少对表的查询。如
Update 多个 Column 的例子:
低效:UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM
EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM
EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
高效:UPDATE EMP SET (EMP_CAT,SAL_RANGE) = (SELECT MAX(CATEGORY)，
MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020
4．6 用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下， 使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率。
4．7 用 NOT EXISTS 替代 NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN 都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用 NOT IN ，我们可以把它改写成外连接或NOT EXISTS。相对来说，用 NOT EXISTS 替换 NOT IN 将更显著地提高效率。
5 总结
基于存储过程的应用模型与传统的数据应用模型相比，有优异的性能，因此在开发数据库应用(包括数据仓库、数据挖掘、大型OLTP)中，应考虑存储过程的作用，以提高应用的整体性能。
责任编辑：赵妍
【关键词】Oracle；存储过程；性能优化
1引言
在大型应用程序中，对数据的读取不仅频繁而且数据量极大，因此在大型应用程序设计中对数据库的访问成为了整个系统最大的性能瓶颈。存储过程作为数据库对象存储在数据库中，执行一次后，其编译好的代码存储在高速缓冲存储器中，再次使用时直接调用，无需再次编译即可执行，使执行速度大大提高。因此，使用存储过程完成对数据库的操作，可以有效提高数据存储和查询的速度。
大量使用存储过程也会带来一定的副作用，当存储过程中封装了大量的业务逻辑后，其执行过程对数据库服务器增加了沉重的压力，因此優化存储过程的性能就显得尤为重要。
2 Oracle存储过程介绍
存储过程是存储在数据库中的一段存储程序，首先编写PL/SQL程序完成特定的操作，并命名保存，当需要执行此操作时可以使用EXECUTE指令调用。存储过程以数据库为基础接收输入参数，并将结果返回给应用程序。相对于SQL语句，其网络通信流量更小，执行速度更快。而且当Oracle存储过程装载到内存中后，可被多个应用共享，节省内存，能有效提升应用系统的性能。在Oracle中创建存储过程的语句为：
CREATE OR REPLACE PROCEDURE 存储过程名字
(参数1 INNUMBER,
参数2 INNUMBER
…….
)AS
变量1 INTEGER:=0;
变量2 DATE;
BEGIN
END存储过程名字
3存储过程的应用
系统充分合理地利用存储过程，可以提高服务器吞吐能力，利用存储过程来从近50个表中获取数据组合成一个通用的表，产生近8万条记录，而所需时间约7秒钟，如果这种操作借助于较合理的动态网页可以将服务器开发提高到一个高度，充分利用存储过程，可以减轻网页设计带来的繁重处理，而使所编写的代码得到共享和合理的利用，并将代码藏于服务器的数据库内部，使得一些技术得到的保密，这也是存储过程的一大特色。
4存储过程优化方法
存储过程优化过程中首先要选用适合的ORACLE优化器，ORACLE的优化器共有3种:基于规则、基于成本、选择性[4]。在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描，必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。
4．1选择最有效率的表名顺序(基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表将被最先处理。在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
4．2 WHERE 子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.例如:
(低效)SELECT…FROMEMP E
WHERE SAL > 50000 AND JOB = 'MANAGER' AND 25 < (SELECT COUNT(*)
FROM EMP WHERE MGR=E.EMPNO);
(高效)SELECT … FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = 'MANAGER';
4．3 SELECT 子句中避免使用" * "
当你想在 SELECT 子句中列出所有的 COLUMN时，使用动态 SQL 列引用" * "是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE 在解析的过程中，会将" * "依次转换成所有的列名，这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。
4．4 用TRUNCATE替代DELETE
当删除表中的记录时，在通常情况下，回滚段用来存放可以被恢复的信息.如果你没有 COMMIT 事务，ORACLE会将数据恢复到删除之前的状态而当运用TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。
4．5 减少对表的查询
在含有子查询的SQL 语句中，要特别注意减少对表的查询。如
Update 多个 Column 的例子:
低效:UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM
EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM
EMP_CATEGORIES) WHERE EMP_DEPT = 0020;
高效:UPDATE EMP SET (EMP_CAT,SAL_RANGE) = (SELECT MAX(CATEGORY)，
MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020
4．6 用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下， 使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率。
4．7 用 NOT EXISTS 替代 NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN 都是最低效的(因为它对子查询中的表执行了一个全表遍历)。为了避免使用 NOT IN ，我们可以把它改写成外连接或NOT EXISTS。相对来说，用 NOT EXISTS 替换 NOT IN 将更显著地提高效率。
5 总结
基于存储过程的应用模型与传统的数据应用模型相比，有优异的性能，因此在开发数据库应用(包括数据仓库、数据挖掘、大型OLTP)中，应考虑存储过程的作用，以提高应用的整体性能。
责任编辑：赵妍