基于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

上一页    

时间: 2007-03-04

[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

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性能究极优化 下

正在看的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 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性能监控报告生成方法

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

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 性能优化建议小结

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

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 SQL性能优化系列学习二

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

ORACLE SQL语句优化技术要点解析

操作符优化: IN 操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格. 但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询. 由此可见用IN的SQL至少多了一个转换的过程.一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了. 推荐方案:

Oracle数据库性能优化技术开发者网络Oracle

正在看的ORACLE教程是:Oracle数据库性能优化技术开发者网络Oracle.介绍:细处着手,巧处用功.高手和菜鸟之间的差别就是:高手什么都知道,菜鸟知道一些.电脑小技巧收集最新奇招高招,让你轻松踏上高手之路.  摘要: Oracle数据库是当前应用最广泛的大型数据库之一,而其性优化直接关系到系统的运行效率.本文以数据库性能优化的基本原则为出发点,阐述了在数据库设计阶段如何避免竞争和如何优化数据访问,在数据库运行阶段如何从操作系统和数据库实例级别上调整内存和I/O来达到数据库性能优化的各种技

SQL性能优化之定位网络性能问题的方法(DEMO)

最近项目组同事跟我说遇到一个SQL性能问题,他说全表只有69条记录,客户端执行耗费了两分多钟,很不科学.我帮了分析出了原因并得到解决.下面小编安装类似表结构,构造了一个案例,测试截图如下所示: 这个表有13800KB(也就是13M多大小),因为该表将图片保存到数据库(Item_Photo字段为iamge类型),这个是历史原因,暂且不喷这种的设计.看来这个SQL执行时间长的性能问题不在于IO和SQL本身执行计划是否有问题,而是在网络数据传时间上(服务器与客户端位于异地,两地专线带宽6M,不过很多应

实例分析ORACLE数据库性能优化

ORACLE数据库的优化方式和MYSQL等很大的区别,今天通过一个ORACLE数据库实例从表格.数据等各个方便分析了如何进行ORACLE数据库的优化. tsfree.sql视图 这个sql语句迅速的对每一个表空间中的空间总量与每一个表空间中可用的空间的总量进行比较 表空间是数据库的逻辑划分,一个表空间只能属于一个数据库.所有的数据库对象都存放在指定的表空间中.但主要存放的是表, 所以称作表空间. SELECT FS.TABLESPACE_NAME "Talbspace", (DF.TO

Java虚拟机JVM性能优化(二):编译器

本文将是JVM 性能优化系列的第二篇文章(第一篇:传送门),Java 编译器将是本文讨论的核心内容. 本文中,作者(Eva Andreasson)首先介绍了不同种类的编译器,并对客户端编译,服务器端编译器和多层编译的运行性能进行了对比.然后,在文章的最后介绍了几种常见的JVM优化方法,如死代码消除,代码嵌入以及循环体优化. Java最引以为豪的特性"平台独立性"正是源于Java编译器.软件开发人员尽其所能写出最好的java应用程序,紧接着后台运行的编译器产生高效的基于目标平台的可执行代

详解MySQL性能优化(二)

接着上一篇学习:http://www.jb51.net/article/70528.htm 七.MySQL数据库Schema设计的性能优化 高效的模型设计 适度冗余-让Query尽两减少Join 大字段垂直分拆-summary表优化 大表水平分拆-基于类型的分拆优化 统计表-准实时优化 合适的数据类型 时间存储格式总类并不是太多,我们常用的主要就是DATETIME,DATE和TIMESTAMP这三种了.从存储空间来看TIMESTAMP最少,四个字节,而其他两种数据类型都是八个字节,多了一倍.而T

记一次成功的sql注入入侵检测附带sql性能优化

但是如果是让你接手一个二等残废的网站,并让你在上面改版,而且不能推翻式改版,只能逐步替换旧的程序,那么你会非常痛苦,例如我遇到的问题: 问题1. 老板对你说,以前刚做完网站好好了,没有出现木马,怎么你来了,就会出现木马,先别说了,赶紧解决问题,我彻底无语,但是如果争吵,其实证明你和老板一样无知,拿出证据和事实分析来让公司其他稍微懂技术的一起来证明,公司网站被挂马不是你来了的错. 如是我通过网站目录仔细排查将通过fck上传的网马删除并修补fck的上传漏洞并记下了这篇 Fckeditor使用笔记 ,