基于Oracle的高性能动态SQL程序开发

正在看的ORACLE教程是:基于Oracle的高性能动态SQL程序开发。

　摘要：对动态SQL的程序开发进行了总结，并结合笔者实际开发经验给出若干开发技巧。

　　关键词：动态SQL，PL/SQL，高性能

　　1． 静态SQLSQL与动态SQL

　　Oracle编译PL/SQL程序块分为两个种：其一为前期联编（early binding），即SQL语句在程序编译期间就已经确定，大多数的编译情况属于这种类型；另外一种是后期联编（late binding），即SQL语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常，静态SQL采用前一种编译方式，而动态SQL采用后一种编译方式。

　　本文主要就动态SQL的开发进行讨论，并在最后给出一些实际开发的技巧。

　　2． 动态SQL程序开发

　　理解了动态SQL编译的原理，也就掌握了其基本的开发思想。动态SQL既然是一种”不确定”的SQL，那其执行就有其相应的特点。Oracle中提供了Execute immediate语句来执行动态SQL，语法如下：

Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;

　　对这一语句作如下说明：

　　1) 动态SQL是指DDL和不确定的DML（即带参数的DML）

　　2) 绑定参数列表为输入参数列表，即其类型为in类型，在运行时刻与动态SQL语句中的参数（实际上占位符，可以理解为函数里面的形式参数）进行绑定。

　　3) 输出参数列表为动态SQL语句执行后返回的参数列表。

　　4) 由于动态SQL是在运行时刻进行确定的，所以相对于静态而言，其更多的会损失一些系统性能来换取其灵活性。

　　为了更好的说明其开发的过程，下面列举一个实例：

　　设数据库的emp表，其数据为如下：

　　要求：

　　1．创建该表并输入相应的数据。

　　2．根据特定ID可以查询到其姓名和薪水的信息。

　　3．根据大于特定的薪水的查询相应的员工信息。

　　根据前面的要求，可以分别创建三个过程（均使用动态SQL）来实现：

　　过程一：

　　过程二：

　　过程三：

　　注意：在过程二中的动态SQL语句使用了占位符“:1“，其实它相当于函数的形式参数，使用”：“作为前缀，然后使用using语句将p_id在运行时刻将:1给替换掉，这里p_id相当于函数里的实参。另外过程三中打开的游标为动态游标，它也属于动态SQL的范畴，其整个编译和开发的过程与execute immediate执行的过程很类似，这里就不在赘述了。

[NextPage] 3． 动态SQL语句开发技巧

　　前面分析到了，动态SQL的执行是以损失系统性能来换取其灵活性的，所以对它进行一定程度的优化也是必要的，笔者根据实际开发经验给出一些开发的技巧，需要指出的是，这里很多经验不仅局限于动态SQL，有些也适用于静态SQL，在描述中会给予标注。

　　技巧一：尽量使用类似的SQL语句，这样Oracle本身通过SGA中的共享池来直接对该SQL语句进行缓存，那么在下一次执行类似语句时就直接调用缓存中已解析过的语句，以此来提高执行效率。

　　技巧二：当涉及到集合单元的时候，尽量使用批联编。比如需要对id为100和101的员工的薪水加薪10％，一般情况下应该为如下形式：

　　对于上面的处理，当数据量大的时候就会显得比较慢，那么如果采用批联编的话，则整个集合首先一次性的传入到SQL引擎中进行处理，这样比单独处理效率要高的多，进行批联编处理的代码如下：

　　这里是使用forall来进行批联编，这里将批联编处理的情形作一个小结：

　　1) 如果一个循环内执行了insert，delete，update等语句引用了集合元素，那么可以将其移动到一个forall语句中。

　　2) 如果select into，fetch into 或returning into 子句引用了一个集合，应该使用bulk collect 子句进行合并。

　　3) 如有可能，应该使用主机数组来实现在程序和数据库服务器之间传递参数。

　　技巧三：使用NOCOPY编译器来提高PL/SQL性能。缺省情况下，out类型和in out类型的参数是由值传递的方式进行的。但是对于大的对象类型或者集合类型的参数传递而言，其希望损耗将是很大的，为了减少损耗，可以采用引用传递的方式，即在进行参数声明的时候引用NOCOPY关键字来说明即可到达这样的效果。比如创建一个过程：

　　其中square为一个大的对象类型。这样只是传递一个地址，而不是传递整个对象了。显然这样的处理也是提高了效率。

　　4． 小结

　　本文对动态SQL的编译原理、开发过程以及开发技巧的讨论，通过本文的介绍后，相信读者对动态SQL程序开发有了一个总体的认识，为今后深入的工作打下一个良好的基础。

　　前面代码部分已经在下列环境中调试成功：

　　服务器端：UNIX+ORACLE9.2

　　客户端：WINDOWS2000 PRO+TOAD

上一页    

赞 (0)

相关推荐

  • Oracle SQL性能优化系列学习二

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习二.  4. 选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(

  • oracle 性能优化建议小结

    原则一:注意WHERE子句中的连接顺序: ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 尤其是"主键ID=?"这样的条件. 原则二: SELECT子句中避免使用 ' * ': ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 . 简单地讲,语句执行的时间越短越好(尤其对于系统的终端用

  • Oracle性能究极优化

    正在看的ORACLE教程是:Oracle性能究极优化. 增大 SGA 已经缓冲看来对于性能的提升并不显著,加载时间只提升了 1.73%.下面我们增加 SGA 重做日志的大小: DB3: Log Buffer Database Block Size 2K SGA Buffer Cache 128M SGA Shared Pool 128M SGA Redo Cache 16M Redo Log Files 16M Tablespaces Dictionary TPC Results Load Ti

  • Oracle SQL性能优化系列学习三

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习三.8. 使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 例如: SELECT COUNT(*),SUM(SAL) FROM EMP  WHERE DEPT_NO = 0020  AND ENAME LIKE 'SMITH%'; SELECT COUNT(*),SUM(SAL)  FROM EMP  WHERE DEPT_NO = 0030  AND ENAME LIKE 'SM

  • Oracle性能究极优化 下

    正在看的ORACLE教程是:Oracle性能究极优化 下. 我们有理由相信采用新的内核版本(2.2.16-3 smp)也应该有性能的提升: OS2: Newer minor version kernel TPC Results Load Time (Seconds) 9.40 Transactions / Second 11.522 目前已经有 2.4 版本的内核,和 2.2 相比,性能上有了很大的提升,我们采用 2.4.1 smp: OS3: Newer major version kerne

  • Oracle收购TimesTen 提高数据库软件性能

    正在看的ORACLE教程是:Oracle收购TimesTen 提高数据库软件性能.Oracle公司6月9日宣布同意收购TimesTen公司,TimesTen是一家私营软件企业,其产品能提高用于股市和机票预订等需要快速响应时间的数据库应用的性能. TimesTen的基础设施产品经常被Oracle用户使用,为Oracle的数据库提供高速缓冲. Oracle表示将把TimesTen的技术用于自己的数据库和中间件产品,为客户应用改善可用性和响应时间.TimesTen的产品还用于为实时记账和呼叫中心系统管

  • Oracle性能究极优化 上第1/2页

    正在看的ORACLE教程是:Oracle性能究极优化 上. Linux Journal 发表了一篇优化 Oracle 数据库的文章,感觉十分的有用.简要介绍其摘要和大家共同分享 Linux 在企业级数据库上应用. 目前,HP,Compaq,Dell,IBM 以及 Oracle 都在加快速度拥抱 Linux ,这个开放源码的操作系统.根据 eWeek 的统计,去年 Linux 服务器的销售量大约占据了 Compaq 的 30%,Dell 的 13.7%,IBM 的 13.5%.而且 IBM 200

  • oracle AWR性能监控报告生成方法

    oracle AWR性能监控报告生成方法

    目前相当一部分公司会用到oracle,在做性能测试的时候,对数据库的监控很重要,那么这里先介绍下如何生成oracle自带的awr监控报告,而具体报告的内容分析会放在后续的博客中. oracle性能分析入门学习中,遇到oracle数据库的性能问题,一般首要的步骤就是导出AWR的分析报告,awr报告是oracle自带的监控报告,会自带很多监控数据,那么本篇博客就是介绍如何导出awr报告 1.首先需要进入oracle用户,linux命令:su oracle,可以看到从root用户改为oracle用户

  • Oracle SQL性能优化系列学习一

    正在看的ORACLE教程是:Oracle SQL性能优化系列学习一.1. 选用适合的ORACLE优化器 ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO,

  • [Oracle] 分析AWR报告的方法介绍

    AWR的数据主要有两部分组成:1)保存在内存中的系统负载和性能统计数据,主要通过v$视图查询 :2)mmon进程定期以快照(snapshot)的方式将内存中的AWR数据保存到SYSAUX表空间中,主要通过DBA_*视图访问. 1. AWR快照的生成默认情况下,每隔一小时自动产生一个快照,保存最近7天的信息,可以通过以下语句查询:SQL>select SNAP_INTERVAL,RETENTION from dba_hist_wr_control; SNAP_INTERVAL       RETE

随机推荐