计算机专业文献翻译优调SQL过程

1、外文资料译文及原文院（系）：计算机学院专 业：计算机科学与技术班 级：2401102学 号：20023011059姓 名： 指导教师： 2005年6月简介有关如何调优数据库系统和应用程序的好的建议的来源有很多。比如oltp应用程序的db2调优技巧(以前在 ibm® db2® 开发者园地上发表)之类的文章通过使用事务和数据并行性以及分析查询方案，给出了从表空间和索引设计到缓冲池的内存分配等方面的建议。这些方面的内容是性能调优的基础知识。 但是，有关如何组织存储过程自身中的逻辑并着眼于其性能的专门建议却并不多见。本文就提供了这样一种建议。尽管本文着重于介绍 sql 过程，但是这

2、里所提供的大多数信息同样适用于用其它语言编写的在应用程序中或存储过程中嵌入的 sql 逻辑。背景知识和术语在深入研究详细问题之前，让我们先想想db2 中有关过程化 sql 的一些基本术语和概念。过程化 sql 构造（例如标量变量、if 语句和 while 循环）是在 db2 universal database (udb) v7 发行版中引入 db2 的。以前的 db2 发行版支持 c 和 java 作为存储过程的语言。v7 引入了 sql 存储过程，以及其它许多可以促进 oltp 应用程序开发的特性（例如临时表、应用程序保存点和标识列）。当创建 sql 过程时，db2 将过程主体中的 sql

3、 查询与过程逻辑区分开来。为了使性能最优，sql 查询被静态地编译成包中的节。（对于静态编译的查询而言，节主要是由 db2 优化器为该查询选择的存取方案构成的。包是节的集合。在过程的执行期间，每当控制从过程逻辑流向 sql 语句时，在 dll 和 db2 引擎之间就存在“上下文切换”。（在 db2 v8 中，sql 过程是在“不受保护的方式”下运行的，即与 db2 引擎在相同的寻址空间中。因此我们这里谈及的上下文切换并不是操作系统级别上的完全的上下文切换，而是指 db2 中层的更换。）减少频繁调用的过程（例如 oltp 应用程序中的过程）或者处理大量行的过程（例如执行数据清理的过程）中的上下文

4、切换次数，对它们的性能有显著的影响。本文中的几个技巧恰好旨在减少这些上下文切换。刚开始的时候（db2 通用数据库 v7 ga），只允许在 sql 过程中使用 sql 过程语言（通常称为 sql pl）。后来（在 db2 udb v7.2 中），在 sql 函数和触发器主体中开始支持该语言的子集。sql pl 的这个子集即所谓的 内联（inline）sql pl。“内联”一词突出显示了它与完整语言的重要区别。sql pl 过程是通过将其单独的 sql 查询静态地编译成包中的节实现的，而内联 sql pl 函数就象其名称所展示的，是通过将函数主体内联到使用它的查询中实现的。稍后我们将再看一下内联

5、sql pl 及其用法的一些示例。 从多个 sql 语句到一个 sql 表达式跟其它编程语言一样，sql 语言提供了两类条件构造：过程型（if 和 case 语句）和函数型（case 表达式）。在大多数环境中，可使用任何一种构造来表达计算，到底使用哪一种只是喜好问题。但是，使用 case 表达式编写的逻辑不但比使用 case 或 if 语句编写的逻辑更紧凑，而且更有效。使用 sql 的一次处理一个集合语义,诸如循环、赋值和游标之类的过程化构造允许我们表达那些只使用 sql dml 语句是不可能表达的计算。但是，当我们拥有一些可以随意使用的过程语句时，即使我们手头的计算实际上仅使用 sql dm

6、l 语句就可表达，但转换成过程语句还是有风险的。正如我们以前提到的，过程计算的性能与使用 dml 语句表达的同一个计算的性能相比会慢几个数量级。在研究改进现有过程逻辑的性能时，为消除游标循环而花费的任何时间都可能是值得的。改进游标性能如果存储过程中的逻辑确实需要游标，那么要使性能最优，请牢记下面这些内容。首先，请确保不使用高于您所需的隔离级别。隔离级别决定了 db2 对过程读取或更新的行应用的锁定的数量。隔离级别越高，db2 将执行的锁定越多，因此为同一资源而竞争的应用程序之间的并发就越少。例如，使用可重复读（repeatable read，rr）隔离级别的过程将形成对其读取的任何行的共享锁，

7、而使用游标稳定性（cursor stability，cs）的过程只会锁定任何可更新游标的当前行。可以使用 db2_sqlroutine_prepopts 注册表变量来指定 sql 过程的隔离级别。db2 中缺省的隔离级别是游标稳定性。但是，当然了，为了保持应用程序的正确性，有时需要使用可重复读。还需记住一件重要的事情，一旦创建了需要可重复读的过程，必须将 db2_sqlroutine_prepopts 重新设置回较低的隔离级别。在尝试改进游标性能时需要牢记的一个相关问题是游标的可更新能力。如果游标涉及的行是可以使用 insert 或 delete 语句中的 where current of 子

8、句进行更新或删除，那么它就是 可删除的。当游标可删除时，db2 必须获取行上的 互斥锁（与 共享锁相对），并且不能执行行分块。行上的互斥锁甚至可以防止其它应用程序读取该行（在互斥锁被释放之前，这些应用程序必须等待，除非它们的隔离级别是 ur），而行分块通过在一个操作中检索行块，从而减少了用于游标的数据库管理器开销。 只有不可删除的游标才可以进行行分块。这就是为什么让 db2 了解将如何使用游标是很重要的原因。通过在 select 语句中指定 for read only 子句，可以将游标显式地声明为不可删除，或者通过在 select 语句中使用 for update 子句将其声明为可删除。根据该

9、信息（并且还根据下面描述的 blocking 选项），db2 将确定是否将行分块用于给定的游标。缺省情况下，对于那些使用 for read only 子句定义的游标，db2 将始终使用行分块，除非指定了 blocking no 绑定选项。另一方面，如果使用了 blocking all 绑定选项，那么对于含混游标（既不是定义成 for read only 也不是定义成 for update 的游标），db2 将使用行分块。简而言之：如果可能，则在游标定义中使用 for read only 子句；如果您的过程包含含混游标，那么请使用 blocking all 绑定选项。要设置 blocking 绑

10、定选项的值，我们还可以使用 db2_sqlroutine_prepopts 注册表变量。在无副作用的情况下，请使用 sql 函数。正如我们在简介中提及的，sql 过程和 sql 函数是使用不同技术实现的。sql 过程中的查询是单独编译的，每个查询都成为包中的一个节。编译是在过程创建时进行的，直到重新创建过程或者直到重新绑定其相关的包时才重新编译这些查询。另一方面，sql 函数中的查询是一起编译的，就好像函数体是一个查询一样。每当编译一条使用 sql 函数的语句时，也会对 sql 函数进行编译。与 sql 过程中所发生的情况不同，sql 函数中的过程语句与数据流语句是在同一个层中执行的。因此，每

11、当控制从过程语句流向数据流语句或相反时，并不发生上下文切换。因为存在这些区别，所以当给定的过程代码段作为函数实现时的执行速度通常比作为过程实现时要快。但是，当然了，有一个小问题。函数只能包含那些不会改变数据库状态的语句（例如 insert、update 或 delete 语句是不允许的）。并且只允许完整 sql pl 语言的子集出现在 sql 函数中（不能是 call 语句、游标和条件处理）。尽管有这些限制，但大多数 sql 过程都可以在无副作用的情况下转换成 sql 函数。因此，正如本节标题所展示的，当您只是从数据库抽取数据而不执行任何更改时，请考虑使用 sql 函数而不是使用 sql 过程

12、。使用用于临时数据的临时表在 v7 中，db2 引入了临时表。对临时表的操作通常比对常规表的操作快。让我们看一些原因：· 首先，临时表的创建不会涉及向目录中插入项，并且临时表的使用也不会涉及对目录的访问；因此，不会有目录争用问题。 · 因为临时表只能由创建它们的应用程序访问，因此在其操作中不会涉及锁定问题。 · 如果指定了 not logged 选项，则不对临时表上的操作记录日志（当然，这样就不可能回滚更改）。因此，如果您的存储过程生成了大量临时数据，并只打算在数据库的一个会话中使用它们，那么请将这些数据存储进临时表，这样可以显著地改进性能。 在对 sql 过程中

13、的临时表进行任何应用之前，表定义在编译环境中必须是可用的。在执行了 connect reset 命令后，临时表将不复存在。在运行时，应用程序必须确保在执行使用临时表的首个查询之前该表是存在的。最后的这个观察引出了一个我们从未提及的要点：引用临时表的任何查询都将被动态地编译，即使该查询被写成静态的 sql。跟其它任何动态查询一样，在编译该查询之后，它将以已编译的形式保留在包高速缓存中。在下一次执行相同的查询时，仅当无法在高速缓存发现它时，db2 才重新编译它。如果您打算创建相对较大的临时表，并对这些表运行几个查询，请考虑定义索引并对它们运行 runstats（显然后者是填充了表后进行的）。有关在

14、 sql 过程中使用临时表的最后一个说明是：如果需要根据在同一个过程中创建的临时表返回结果集，那么必须在嵌套的复合语句中定义结果集。必须在嵌套的复合语句中定义结果集的理由是，declare global temporary table 是一个可执行语句，而可执行语句只能在声明语句（例如 declare cursor）之后编写。如果我们在游标定义之后在外部作用域中声明表，那么当编译 declare cursor 语句时，该表在编译环境中将不可用，因此编译会失败。关于作者gustavo arocena是 db2 sql 编译器方面的技术经理。他于 1998 年加入 ibm 多伦多实验室，目前负责

15、sql 过程和 sql 解析器的开发。gustavo 拥有多伦多大学计算机科学硕士学位，研究方向是数据库查询语言。 优调sql过程资深软件开发人员, ibm canada ltd. 2005 年 2 月普通 sql 过程如果您有 microsoft sql server 或 sybase 方面的背景知识，那么您就会很好地了解这些过程。除了一条 call 语句，这个范例的应用程序中将不存在任何 sql。通过这个范例所认识到的好处有两个：首先是封装。例如，如果查询十分复杂，就不应重复查询多次，而是应该将它存储在某一个地方。 其次是缓存。存储过程中的代码是预先编译的。而应用程序中的代码通常是动态代码

16、。 为了解决第一个问题，db2 支持内联 sql pl 的概念。db2 允许将简单逻辑或查询封装在 sql 函数中。当从调用者执行该 sql 函数时，其主体就是扩展到调用者中的宏。为了解决第二个问题，db2 使用了包缓存。该缓存不仅记住最近执行的过程，还记住了以前执行的语句。因此，在第一次编译 sql 语句之后，后来的调用只要继续执行相同的执行计划即可。真是令人印象深刻！仅仅通过使用内联 sql pl 就使所监控代码的运行速度提高了三倍。甚至连不返回结果集的简单 sql 过程也可以用 sql 函数取代。对于返回不止一个参数的过程，可以使用 sql 表函数，它返回一个包含一行的表 每个输出参数为

17、一列。对于包含一个输出的过程，可以只使用一个标量 sql 函数。注意，db2 v8.2 还支持在 sql 表函数中包含 update、delete、insert 和 merge。这意味着您甚至可以使用内联 sql pl 封装数据库更改。使用临时表sql 术语来说，declare 表示所定义对象的目录中将不包含条目。因此，它在定义上是私有的。与之相比，db2 udb for zos® 所支持的已创建的全局临时表（created global temporary table）是在目录中定义的。当然，虽然它们的内容是私有的，但其定义却不是私有的。具有声明对象的灵活性（只要用户临时表空间是立

18、即可用的，dba 就不会再关心该应用程序在做什么）也带来了一个不足：对于要进行编译的语句来说，这个对象必须存在。如果连接失败，或者该表已删除，那么每次重新声明这个表时，都需要重新编译引用 dgtt 的任何语句。declare 和 drop 之间的 insert 语句每次都要重新进行编译，因为 db2 无法知道该 dgtt 下次是否将具有相同的属性。实际上，declare 和 drop 之间有许多语句，都可用于处理该临时表结果集的格式，直到它最终满足要求为止。为了避免这种疯狂编译，将该临时表的声明移至一个单独过程中会更合适一些，该过程只在启动工作负载时执行一次。正如您可以在上面看到的，其结果是令

19、人震惊的。但这里将有更多要了解的东西。请观察取代 drop 语句的 delete 语句。它将总是因用户引起的错误而失败，然后，将由一个 continue 处理程序解决这个错误。这里发生了什么事？为了提高速度，需要优化临时表，因此，当插入行时，db2 不用费心在临时表中寻找空闲空间，而是将表行为替换为 append only。虽然常规的 delete 将删除这些行，但是它不会真正让 db2 回收这些空间。应用程序将继续消费越来越多的用户临时表空间。实际上，另外一个因素在这里提供了帮助。那就是，所有 dgtt 都被声明为 not logged。毕竟，您可以相当容易地重新构建临时表的内容。如果 no

20、t logged 表在执行数据修改语句期间碰到了执行错误，那么对于 db2 来说，就只有一个选择：清空（truncate）该表。而这就是 delete 语句所进行的工作。条件处理程序计算机语言有两种处理错误的常用方法。第一种方法要求程序在在进行每一个重大操作之后，检查错误。在 db2 中编写 c-udf 或 c 存储过程的开发人员已经学会在其代码的每条 exec sql 语句之后检查 sqlca。第二种方法就是具有专用的处理程序，“捕捉”各条语句所“抛出”的任何或特定错误条件。java 和 c+ 编程人员都熟悉这一概念。sql/psm 标准为 sql 过程语言定义了使用处理程序的第二种方法。然

21、而，通常当从 sybase 或 microsoft sql 服务器移植 tsql 过程时，或当仅仅对 sql pl 应用 c 技巧时，都可以避免更现代的处理程序方法，而支持更为传统的错误检查方法。这些情况下要做的事情就是定义一个通用的“万能（catch-all）”处理程序，用于在局部变量中保存所有错误信息。然后，使用过程体中的显式错误处理代码进行错误处理。具有讽刺意味的是，在许多情况下，程序甚至可能不在乎检查 select into 的结果是什么，因此，条件处理程序的工作是徒劳的。本例中，将完成以下几件事。首先，可以显式地检查 not found 警告（如果想这样做的话），而不必在局部变量中保

22、存 sqlcode 和 sqlstate。事实的确如此，因为该警告可用于 sql 过程中的下一条语句。其次，所有错误或意想不到的警告实际上应该由异常处理程序来处理。除了按照发明者所计划的方式来使用该语言之外，这样做可避免复制和重新设置局部变量。- 10 -introductionthere are many sources of advice available on how to tune database systems and applications. articles like db2 tuning tips for oltp applications, previously pub

23、lished on the ibm® db2® developer domain, provide advice on topics ranging from tablespace and index design to memory allocation for buffer pools, exploiting transaction and data parallelism and analyzing query plans. these topics are the abcs of performance tuning.however, specific advice

24、 on how to organize the logic in the stored procedures themselves with an eye on performance is not that common. that is the kind of advice i provide in this article. even though the article focuses on sql procedures, most of the information provided here is applicable to sql logic embedded in appli

25、cations or in stored procedures written in other languagesbackground and terminologybefore going into the details, let's first review some basic terminology and concepts related to procedural sql in db2. procedural sql constructs (such as scalar variables, if statements and while loops) were int

26、roduced in db2 with the release of db2 universal databasetm (udb) version 7. prior releases of db2 supported c and javatm as languages for stored procedures. version 7 introduced sql stored procedures. when an sql procedure is created, db2 separates the sql queries in the procedure body from the pro

27、cedural logic. to maximize performance, the sql queries are statically compiled into sections in a package. (for a statically compiled query, a section consists mainly of the access plan selected by the db2 optimizer for that query. a package is a collection of sections. for more information on pack

28、ages and sections, please refer to the db2 sql reference,volume1.) on the other hand, the procedural logic is compiled into a dll (dynamically linked library).during the execution of a procedure, every time control flows from the procedural logic to an sql statement, there is a "context switch&

29、quot; between the dll and the db2 engine. (in db2 v8, sql procedures run in "unfenced mode", i.e., in the same addressing space as the db2 engine. therefore the context switch we refer to here is not a full context switch at the operating system level, but rather a change of layer within d

30、b2.) reducing the number of context switches in procedures that are invoked very often (such as procedures in an oltp application) or that process large numbers of rows (for example, procedures that perform data cleansing) can have a noticeable impact on their performance. several of the tips in thi

31、s article aim precisely at reducing these context switches.the sql procedural language (commonly referred to as sql pl) was first allowed only in sql procedures (db2 universal database v7 ga). later on (db2 udb v7.2), a subset of the language started to be supported in sql functions and trigger bodi

32、es. this subset of sql pl is known as inline sql pl. the word "inline" highlights an important difference with the full language. whereas an sql pl procedure is implemented by statically compiling its individual sql queries into sections in a package, an inline sql pl function is implement

33、ed, as the name suggests, by inlining the body of the function into the query that uses it. we'll revisit inline sql pl later on, along with examples of its use.from multiple sql statements to a single sql expressionlike other programming languages, the sql language provides two types of conditi

34、onal constructs: procedural (if and case statements) and functional (case expressions). in most circumstances where either type can be used to express a computation, using one or the other is a matter of taste. however, logic written using case expressions is not only more compact, but also more eff

35、icient than logic written using case or if statementsexploit the set-at-a-time semantics of sql,procedural constructs such as loops, assignment and cursors allow us to express computations that would not be possible to express using just sql dml statements. but when we have procedural statements at

36、our disposal, there is a risk that we could turn to them even when the computation at hand can, in fact, be expressed using just sql dml statements. as we've mentioned earlier, the performance of a procedural computation can be orders of magnitude slower than the performance of an equivalent com

37、putation expressed using dml statements. when looking at improving the performance of existing procedural logic, any time spent in eliminating cursor loops will likely pay off.improve cursor performance.if the logic in your stored procedures does require cursors, here are a few things to keep in min

38、d to maximize their performance.first of all, make sure you don't use an isolation level higher than what you need. the isolation level determines the amount of locking that db2 applies on the rows that the procedure reads or updates. the higher the isolation level, the more locking db2 will per

39、form, and therefore, the less concurrency between applications competing for the same resources. for instance, a procedure using repeatable read (rr) isolation level will cause share locks on any row it reads, whereas a procedure using cursor stability (cs) will only lock the current row of any upda

40、table cursor. the isolation level for sql procedures can be specified with the db2_sqlroutine_prepopts registry variable.the default isolation level in db2 is cursor stability. but of course, to preserve the correctness of an application, it is sometimes necessary to use repeatable read. what is imp

41、ortant to remember is to revert db2_sqlroutine_prepopts back to a lower isolation level once procedures requiring repeatable read are created.the query above will execute with isolation level ur regardless the isolation level specified in db2_sqlroutine_prepopts.a related issue to keep in mind when

42、trying to improve cursor performance is cursor updatability. a cursor is deletable if the rows it ranges over can be updated or deleted using the where current of clause in update or delete statements. when a cursor is deletable, db2 has to obtain exclusive locks (as opposed to share locks) on rows,

43、 and cannot perform row blocking. an exclusive lock on a row prevents other applications from even reading the row (they have to wait until the lock is released, unless their isolation level is ur), whereas row blocking reduces database manager overhead for cursors by retrieving a block of rows in a

44、 single operation.row blocking can only happen for non-deletable cursors. that is why it is important to let db2 know how a cursor is going to be used. cursors can be explicitly declared as non-deletable by specifying the for read only clause in the select statement, or as deletable by using the for

45、 update clause in the select statement. based on this information (and also on the blocking option described below), db2 will decide whether to use row blocking for a given cursor or not.by default, db2 will always use row blocking for cursors defined using the for read only clause, unless the block

46、ing no bind option has been specified. on the other hand, db2 will use row blocking for ambiguous cursors (cursors that are not defined as either for read only or for update) if the blocking all bind option is used.to make a long story short: when possible, use the for read only clause in your curso

47、r definitions; if your procedures contain ambiguous cursors, use the blocking all bind option. to set the value of the blocking bind option, we also use the db2_sqlroutine_prepopts registry variable.in the absence of side-effects, use sql functions.as we mentioned in the introduction, sql procedures

48、 and sql functions are implemented using different technologies. queries in an sql procedure are compiled individually, each of them becoming a section in a package. the compilation occurs when the procedure is created, and the queries are not recompiled until the procedure is recreated or until its

49、 associated package is rebound.on the other hand, queries in sql functions are compiled together, as if the function body were a single query. the compilation occurs every time a statement that uses the function is compiled.unlike what happens in sql procedures, procedural statements in sql function

50、s are not executed in a different layer than dataflow statements. therefore, there is no context switch every time control flows from a procedural to a dataflow statement or vice versa.as a result of these differences, a given piece of procedural code will usually execute much faster when implemente

51、d as a function than when implemented as a procedure. but of course, there is a catch. functions can only contain statements that do not alter the state of the database (for example, insert, update or delete statements are not allowed). and also only a subset of the full sql pl language is allowed i

52、n sql functions (no call statements, no cursors, no condition handling).despite these restrictions, most sql procedures with no side-effects can be converted into sql functions.so, as the title of this section indicates, consider using sql functions instead of sql procedures when you're just ext

53、racting data from the database without performing any changes.use temporary tables for temporary datain version 7, db2 introduced temporary tables. operations on temporary tables are usually much faster than operations on regular tables. let's look at some of the reasons:to begin with, the creat

54、ion of a temporary table does not involve the insertion of entries in the catalogs, and use of the temporary table does not involve access to the catalogs either; therefore, there is no catalog contention. because temporary tables are only accessible to the application that created them, there is no

55、 locking involved in their manipulation. if the not logged option is specified, operations on temporary tables are not logged (at the expense, of course, of the possibility of rolling back changes). therefore, if your stored procedures generate large amounts of temporary data that are only meant to

56、be used within a session with the database, storing those data in temporary tables can result in significant performance gains.before any use of a temporary table in an sql procedure, the table definition must be available in the compilation environment.after the connect reset command, the temporary

57、 table will no longer exist. at runtime, the application will have to ensure that the table exists before the first query that uses it is executed. this last observation brings up an important point we haven't mentioned yet: any query that references a temporary table will be compiled dynamicall

58、y, even if the query is written as static sql. like any other dynamic query, after the query is compiled, it will stay in compiled form in the package cache. the next time the same query is executed, db2 will recompile it only if it is not found in the cache.if you plan to create relatively big temp

59、orary tables and run several queries on them, consider defining indexes and running runstats on them (the latter obviously after the table has been populated).one last comment on using temporary tables in sql procedures: if you need to return a result set based on a temporary table that is created in the same procedure, the result set must be defined in a nested compound statement.the reason the result se