Oracle存储过程的编写经验与优化措施(分享)

一、前言:在经过一段时间的存储过程开发之后,写下了一些开发时候的小结和经验与大家共享,希望对大家有益。
二、适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。
三、介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。如果项目的SP较多,书写又没有一定的规范,将会影响以后的系统维护困难和大SP逻辑的难以理解,另外如果数据库的数据量大或者项目对SP的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。
四、内容:
1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databse.dbo.table_name”,因为sp_depends不能显示出该SP所使用的跨库table或view,不方便校验。
2、开发人员在提交SP前,必须已经使用set showplan on分析过查询计划,做过自身的查询优化检查。
3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点:
a) SQL的使用规范:
i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。
ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。
iii. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。
iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。
v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
vi. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。
vii. 尽量使用“>=”,不要使用“>”。 viii. 注意一些or子句和union子句之间的替换
ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。
x. 注意存储过程中参数和数据类型的关系。
xi. 注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级成表级锁。
b) 索引的使用规范:
i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。
ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引
iii. 避免对大表查询时进行table scan,必要时考虑新建索引。
iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。
v. 要注意索引的维护,周期性重建索引,重新编译存储过程。
c) tempdb的使用规范:
i. 尽量避免使用distinct、order by、group by、having、join、***pute,因为这些语句会加重tempdb的负担。
ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。
iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。
iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。
v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。
vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。
d) 合理的算法使用:
根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令:set statistics io on, set statistics time on , set showplan on 等。

(0)

相关推荐

  • oracle如何恢复被覆盖的存储过程

    如果你不小心覆盖了之前的存储过程,那得赶紧闪回,时长越长闪回的可能性越小.原理很简单,存储过程的定义就是数据字典,修改数据字典跟修改普通表的数据没有区别,此时会把修改前的内容放到undo中,我们可以根据这一点来进行闪回,所以我说要尽快,要不然找不回来了.下面我们来做一个实验: 1.在用户TEST下14:31下建立存储过程 复制代码 代码如下: create or replace procedure GG_TEST as l_cnt number; begin for i in 1 .. 1000

  • Oracle存储过程循环语法实例分析

    本文实例讲述了Oracle存储过程循环语法.分享给大家供大家参考,具体如下: 1.简单循环 语法 loop statements; end loop; 例子: counter := 0; loop counter := counter + 1; exit when counter = 5; end loop; 备注:exit语句立即结束循环,exit when 语句在指定条件出现时停止循环(可以出现在循环代码中的任意位置) 2.while循环 语法 while condition loop st

  • oracle存储过程中return和exit区别概述及测试

    复制代码 代码如下: create or replace procedure Test5(o_cellphone in varchar2) is v_cellphone cc_quiz_stat.cellphone %type; v_name cc_quiz_stat %rowtype; v_state cc_quiz_stat.state %type; begin declare cursor cur_cc is select * from cc_quiz_stat; cursor cur_j

  • Oracle存储过程返回游标实例详解

    有俩种方法: 一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为 in out 或out (1)声明个人系统游标.(推荐) 复制代码 代码如下: create or replace p_temp_procedure ( cur_arg out sys_refcursor; --方法1 ) begin open cur_arg for select * from tablename; end 调用 复制代码 代码如下: declare cur_calling sys_refcu

  • Oracle存储过程游标用法分析

    本文实例讲述了Oracle存储过程游标用法.分享给大家供大家参考,具体如下: 使用游标的5个步骤 1.声明一些变量用于保存select语句返回的指 2.声明游标,并指定select 语句 3.打开游标 4.从游标中获取记录 5.关闭游标 从游标中获取每一条记录可使用fetch语句.fetch语句将列的指读取到指定的变量中: 语法: fetch cursor_name into variable[, variable ...]; 例子: create or replace procedure se

  • oracle数据库中查看系统存储过程的方法

    复制代码 代码如下: select line,text from dba_source where name='PRO_E_F_ORDER_STAT'; select object_name,object_type from dba_objects where object_type='PROCEDURE';

  • oracle 存储过程详细介绍(创建,删除存储过程,参数传递等)

    oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数的查看,包,系统包 认识存储过程和函数 存储过程和函数也是一种PL/SQL块,是存入数据库的PL/SQL块.但存储过程和函数不同于已经介绍过的PL/SQL程序,我们通常把PL/SQL程序称为无名块,而存储过程和函数是以命名的方式存储于数据库中的.和PL/SQL程序相比,存储过程有很多优点,具体归纳如下: * 存储过程和函数以命名的数据库对象形式存储于数据库当中.存储在数据库中的优点是很明显的,因为代码不保存在本地,用户

  • Oracle显示游标的使用及游标for循环

    下面给大家介绍在什么情况下用隐式游标,什么情况下用显示游标: 1.查询返回单行记录时→隐式游标: 2.查询返回多行记录并逐行进行处理时→显式游标 --显示游标属性 declare CURSOR cur_emp IS SELECT * FROM emp; row_emp cur_emp%ROWTYPE; BEGIN OPEN cur_emp; FETCH cur_emp INTO row_emp; WHILE cur_emp%FOUND LOOP dbms_output.put_line(row_

  • oracle存储过程常用的技巧(详)

    我们在进行pl/sql编程时打交道最多的就是存储过程了.存储过程的结构是非常的简单的,我们在这里除了学习存储过程的基本结构外,还会学习编写存储过程时相关的一些实用的知识.如:游标的处理,异常的处理,集合的选择等等 1.存储过程结构 1.1 第一个存储过程 create or replace procedure proc1( p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2 )as v_name varchar2(20)

  • Oracle中 关于数据库存储过程和存储函数的使用

    存储过程和存储函数指存储在数据库中供所有用户程序调用的子程序叫存储过程.存储函数.存储过程没有返回值.存储函数有返回值 创建存储过程      用CREATE PROCEDURE命令建立存储过程和存储函数. 语法:create [or replace] PROCEDURE过程名(参数列表) AS         PLSQL子程序体: 存储过程示例:为指定的职工在原工资的基础上长10%的工资 /*为指定的职工在原工资的基础上长10%的工资,并打印工资前和工资后的工资*/SQL> create or

随机推荐