db2应用程序性能优化-sql语句

语句的优化并发性的优化系统的统计信息DB2的优化技术课程内容内容语句的优化SELECT的优化INSERT的优化TPC-C的SQL案例并发性的优化系统的统计信息应用的优化优化SELECT语句（1）仅指定需要的列。尽管避免SELECT

*，因为会导致不必要的处理和返回不需要的列。通过谓词限定结果集当需要的行数大大小于可能返回的总行数时，指定

OPTIMIZE

FOR子句。此子句影响

方案的选择以及在通信缓冲区中分块的行数。当要检索的行数很小时，仅指定

OPTIMIZE

FOR

k

ROWS

子句。不需要

FETCH

n

ROWS

ONLY

子句。但是，如果

n

值很大，并且想快速获取前

k

行而对后续

k

行进行可能的延迟，则同时指定两个子句。通信缓冲区的大小基于

n和

k

中的较小者。下列示例显示两个子句：

SELECT

EMPNAME,

SALARY

FROM

EMPLOYEEORDER

BY

SALARY

DESC

FETCH 100

ROWS

ONLY

OPTIMIZE

FOR20

ROWS优化SELECT语句（2）合理使用FOR

READ

ONLY,FOR

UPDATE尽可能避免数字数据类型转换。当比较值时，使用有相同数据类型的项可能更有效。如果需要转换，则可能由于精度受限制使结果确，并且由于运行时转换而使性能降低。如果可能的话，使用下列数据类型：对于较短的列，尽量使用字符而不是可变字符尽量使用整数，而不是浮点数或小数尽量使用日期时间，而不是字符尽量使用数字，而不是字符要减小排序操作发生的可能性，省略诸如DISTINCT

或ORDER

BY

的子句或操作（如果这种操作不是必需的话）。要检查一个表中是否存在某些行，选择某一单行。打开游标并访存一行，或执行单行（SELECT

INTO）选择。记住，如果发现多行，则要检查是否有SQLCODE-811

错误。除非您知道表很小，否则，不要使用下列语句来检查非零值：SELECT

COUNT(*)FROMTABLENAME对于大表，对所有行计数会影响性能。优化SELECT语句（3）如果更新活动较少且表较大，则在频繁用作谓词的列上定义索引。如果同一列出现在多个谓词子句中，则考虑使用IN

列表。对于配合主变量使用的大型IN

列表，循环主变量的子集可能会提高性能。优化多表关联SELECT语句（4）在连接谓词中的列上定义索引，可以更有效地处理连接。索引也会改善UPDATE

和DELETE

语句的性能，这些语句包含

几个表的SELECT

语句。如果可能的话，避免将表达式或OR

子句与连接谓词一起使用，因为数据库管理器不能使用某些连接技术。因此，可能未选择最有效的连接方法。在一个分区数据库环境中，如果可能的话，确保在连接列上对已连接的两个表进行了分区。查询重写举例(1):IF替换成CASEIF改写成CASE在大多数环境中，可使用任何一种构造来表达计算，到底使用哪一种只是喜好问题。但是，使用CASE

表达式编写的逻辑不但比使用CASE或IF

语句编写的逻辑更紧凑，而且更有效。改写前：IF

(Price

<=

MaxPrice)

THENp(Id,

Val)

VALUES(Oid,

Price);INSERT

INTOELSEINSERT

INTOEND

IF;p(Id,

Val)

VALUES(Oid,

MaxPrice);改写后：INSERT

INTOVALUES(Oid,CASEp(Id,

Val)WHEN

(Price

<=

MaxPrice)

THEN

PriceELSE

MaxPriceEND);查询重写举例(2):IF替换成CASEIF改写成CASE在大多数环境中，可使用任何一种构造来表达计算，到底使用哪一种只是喜好问题。但是，使用CASE

表达式编写的逻辑不但比使用CASE或IF

语句编写的逻辑更紧凑，而且更有效。改写前：p;DECLARE

cur1

CURSOR

FOR

SELECT

col1,

col2

FROMOPEN

cur1;FETCH

cur1

INTO

v1,

v2;WHILE

SQLCODE

<>

100

DOIF

(v1

>

20)

THENINSERT

INTOtab_selVALUES

(20,

v2);ELSEINSERT

INTOtab_selVALUES

(v1,

v2);END

IF;FETCH

cur1

INTO

v1,

v2;END

WHILE;更好的改写：INSERT

INTO

tab_sel

(SELECT

(CASEWHEN

col1

>

20

THEN

20ELSE

col1END),col2FROM

p);查询重写举例(3):IF替换成CASEIF改写成CASE在大多数环境中，可使用任何一种构造来表达计算，到底使用哪一种只是喜好问题。但是，使用CASE

表达式编写的逻辑不但比使用CASE

或IF

语句编写的逻辑更紧凑，而且更有效。改写前：p;DECLARE

cur1

CURSOR

FOR

SELECT

col1,

col2

FROMOPEN

cur1;FETCH

cur1

INTO

v1,

v2;WHILE

SQLCODE

<>

100

DOIF

(v1

>

20)

THENINSERT

INTO

tab_default

VALUES(20,

v2);ELSEINSERT

INTOtab_selVALUES

(v1,

v2);END

IF;FETCH

cur1

INTO

v1,

v2;END

WHILE;改写后：INSERT

INTO

tab_sel

(SELECT

col1,

col2FROM

pWHERE

col1

<=

20);INSERT

INTO

tab_default(SELECT

col1,

col2FROM

pWHERE

col1

>

20);DB2的查询重写举例(1):操作合并操作合并要构造查询以便它具有最少的操作数（尤其是SELECT

操作），SQL

和XQuery

编译器重写查询来合并查询操作。以下示例举例说明可合并的一些操作：示例1－视图合并使用视图的SELECT

语句可限制表的连接顺序并可引入表的冗余连接。如果在查询重写期间合并视图，则可撤销这些限制。如：CREATE

VIEW

EMP_EDUCATION

(EMPNO, NME,

LASTNAME,

EDLEVEL)

AS

SELECTEMPNO, NME,

LASTNAME,

EDLEVEL

FROM

EMPLOYEE

WHERE

EDLEVEL

>

17

;CREATE

VIEW

EMP_SALARIES

(EMPNO, NAME,

LASTNAME,

SALARY)

AS

SELECT

EMPNO,NME,

LASTNAME,

SALARY

FROM

EMPLOYEE

WHERE

SALARY

>

35000

;现在，假定您执行以下查询，来列示既受过高等教育而且工资又超过$35,000

的职员：SELECT

E1.EMPNO,

E1. NME,

E1.LASTNAME,E1.EDLEVEL,

E2.SALARY

FROMEMP_EDUCATION

E1,EMP_SALARIES

E2

WHERE

E1.EMPNO

=E2.EMPNO;在查询重写期间，可合并这两个视图来创建以下查询：SELECT

E1.EMPNO,

E1. NME,

E1.LASTNAME,

E1.EDLEVEL,

E2.SALARY

FROM

EMPLOYEEE1,

EMPLOYEE

E2

WHERE

E1.EMPNO

=

E2.EMPNO

ANDE1.EDLEVEL

>

17

AND

E2.SALARY

>35000;DB2的查询重写举例(1):操作合并操作合并要构造查询以便它具有最少的操作数（尤其是SELECT

操作），SQL

和XQuery

编译器重写查询来合并查询操作。以下示例举例说明可合并的一些操作：示例2

－子查询至连接的变换如果某个SELECT

语句包含子查询，则可以限制表的顺序处理的选择。SELECT

EMPNO, NME,

LASTNAME,

PHONENO

FROM

EMPLOYEE

WHERE

WORKDEPT

IN(SELECT

DEPTNO

FROM

DEPARTMENT

WHERE

DEPTNAME

='OPERATIONS')并将它转换为如下形式的连接查询：SELECT

DISTINCT

EMPNO, NME,

LASTNAME,

PHONENO

FROM

EMPLOYEE

EMP,DEPARTMENT

DEPT

WHEREEMP.WORKDEPT

=

DEPT.DEPTNO

AND

DEPT.DEPTNAME

='OPERATIONS'一般情况下，连接的执行效率

查询高很多。DB2的查询重写举例(2):减少分区广播在分区数据库环境中，SQL

编译器可重写以下查询：查找正在从事程序设计项目并被少付工资的所有职员。SELECT

P.PROJNO,

E.EMPNO,

E.LASTNAME,

E.

NAME,E.SA M

AS

COMPENSATIONFROM

EMPLOYEE

E,

PROJECT

PWHERE

P.EMPNO

=E.EMPNO

AND

P.PROJNAME

LIKE

'%PROGRAMMING%'AND

E.SA M

<

(SELECT

AVG(E1.SALAFROM

EMPLOYEE

E1,

PROJECT

P1

WHERE

P1.PROJNAME

LIKE

'%PROGRAMMING%'AND

P1.PROJNO

=

A.PROJNO

AND

E1.EMPNO

=

P1.EMPNO)M)因为此查询是相关的，而且因为EMPLOYEE

不可能根据PROJNO

来分区，因此需要将PROJECT表广播至每个数据库分区进行Join计算。此外，子查询将不得不求值多次。DB2的查询重写举例(2):减少分区广播SQL

和XQuery

编译器可按如下所示重写查询：确定从事程序设计项目的职员的单值列表，并将其称为DIST_PROJS。它必须是相异的，才能确保只对每个项目执行一次

：WITH

DIST_PROJS(PROJNO,

EMPNO)

AS(SELECT

DISTINCT

PROJNO,

EMPNO

FROM

PROJECT

P1WHERE

P1.PROJNAME

LIKE

'%PROGRAMMING%')使用从事程序设计项目的职员的单值列表，将该列表与职员表连接，以获得每个项目的平均赔偿额AVG_PER_PROJ：P)

AS

(SELECTP2.PROJNO,M)

FROM

EMPLOYEE

E1,

DIST_PROJS

P2

WHERE

E1.EMPNO=AVG_PER_PROJ(AVG(E1.SALAP2.EMPNO

GROUP

BY

P2.PROJNO)因此新查询将是：SELECT

P.PROJNO,

E.EMPNO,

E.LASTNAME,

E. NAME,

E.SA M

ASCOMPENSATION

FROM

PROJECT

P,

EMPLOYEE

E,

AVG_PER_PROG

A

WHERE

P.EMPNO

=E.EMPNOANDP.PROJNAME

LIKE

'%PROGRAMMING%'

ANDP.PROJNO

=

A.PROJNO

ANDE.SA M<重写的查询计算每个项目的PP（AVG_PRE_PROJ），然后将结果广播至包含EMPLOYEE

表的所有数据库分区。DB2的查询重写举例(3):OR改写成IN假定一个

OR

子句根据同一列将两个或

个简单的等式谓词相连，如以下示例所示：SELECT

*

FROM

EMPLOYEE

WHERE

DEPTNO

=

'D11'

OR

DEPTNO

=

'D21'

OR

DEPTNO

=

'E21'如果DEPTNO

列上没有索引，将OR

子句转换为以下IN

谓词允许更有效地处理该查询：SELECT

*

FROM

EMPLOYEE

WHERE

DEPTNO

IN

('D11',

'D21',

'E21')注:在某些情况下，数据库管理器可以将一个IN

谓词转换为一组OR

子句，以便可执行索引OR

运算。优化INSERT性能：INSERT的执行过程在客户机准备语句。对于动态SQL，在语句执行前就要做这一步，此处的性能是很重要的；对于静态SQL，这一步的性能实际上关系不大，因为语句的准备是事先完成的。在客户机，将要

的行的各个

列值组装起来，发送到

DB2

服务器。DB2

服务器确定将这一行

到哪一页中。DB2

在用于该页的缓冲池中预留一个位置。如果DB2

选定的是一个已有的页，那么就需要读磁盘；如果使用一个新页，则要在表空间（如果是

SMS，也就是系统管理

的表空间）中为该页物理地分配空间。

了新行的每一页最后都要从缓冲池写入到磁盘。在目标页中对该行进行格式化，并获得该行上的一个X（exclusive，独占的）行锁。将反映该insert

的一条记录写入到日志缓冲区中。最后提交包含该insert

的事务，如果这时日志缓冲区中的记录还没有被写入日志文件的话，则将这些记录写到日志文件中。此外，还可能发生很多类型的附加处理，这取决于数据库配置，例如，索引或触发器的存在。这种额外的处理对于性能来说也是意义重大的，

在后面会

到。优化INSERT性能：批量INSERT的替代方案1. Load

from

cursordeclare

staffcursor

cursor

forselect

*

from

staff;load

from

staffcursor

of

cursor

insert

into

myschema.new_staff;这两行可以用下面一行替代：insert

into

myschema.new_staff

select

*

from

staff2.

CLI中的Array

Insert/*

allocate

a

buffer

to

hold

data

to

insert*/iBufferSize

=

strlen(SAMPLE_DATA)

*

ARRAY_SIZE

+sizeof(SQLINTEGER)

*

ARRAY_SIZE;pTempBuffer

=

(char

*)malloc(iBufferSize);memset(pTempBuffer,

0,

iBufferSize);/*

initialize

thearray

of

rows

*/………/*

insert

the

data

*/for

(iLoop=0;

iLoop<NUM_ITERATIONS;

iLoop++){printf(" Inserting

%drows..\n",

ARRAY_SIZE);cliRC=

SQLExecute(hstmt);STMT_HANDLE_CHECK(hstmt,

hdbc,

cliRC);}优化INSERT性能(1):语句的准备1.insert

into

mytable

values(1,'abc')insertintomytablevalues(2,'def')等等，换成带有参数标记的语句，一次准备，重复执行，这样做是十分可取的：insert

into

mytable

values

(?,

?)优化INSERT性能(2):发送列值到服务器三行：INSERT在VALUES

子句中包含多行的内容。例如，下面的语句将INTO

mytable

VALUES

(1,

'abc'),

(2,

'def'),

(3,

'ghi')在CLI

中使用数组 （array

insert）。如果可能的话，让客户机与要存取的数据库使用相同的代码页，以避免在服务器上的转换代价。在某些情况下，CLI

会自动执行数据类型转换，但是这样同时也会带来看不见的（小小的）性能损耗。因此，尽量使

值直接处于与相应列对应的格式。优化INSERT性能(3):空白页的选择ALTER

TABLE

APPEND

ON聚簇索引会减慢INSERT的速度ALTER

TABLE

PCTFREE优化INSERT性能(4):缓冲池,IO的考虑尽量使用RAW

DEVICE减少索引的数量调整数据库参数CHNGPGS_THRESH调整NUM_IOCLEANERS优化INSERT性能(5):锁和日志的考虑可能，使用表锁LOCK

TABLEALTER

TABLE

<name>

NOT

LOGGED

INITIALLY调整LOGBUFSZ注意日志的IO效率，将日志与数据

到不同的设备上优化INSERT性能(5):事务提交的频度1.

合理设置COMMIT的频度优化INSERT性能(6):其它的考虑因素1. 减少索引的个数(或者考虑批量INSERT后重建索引)约束会影响INSERT性能触发器会影响INSERT性能IDENTITY字段和SEQUENCE会影响INSERT性能MQT会影响INSERT性能DPF环境下，考虑Buffer

Insert将LOB和LONG改为VARCHARINSERT语句中查询子句的执行效率TPC-C的SQL案例表：仓库（warehouses）地区（districts）产品（items）客户（customers）组成。在每个WAREHOUSE

中的STOCK（库存）里有100,000

个ITEM。每个仓库为10个DISTRICT

服务。每个地区为3000

名CUSTOMER

服务，这些客户ORDER（订购）新产品。每份ORDER（订单）最多可以由15

种不同产品组成，称作

ORDER_LINE（订购项）。在给订单发货之前，订单作为一个NEW_ORDER进行排队，仓库中每种产品的库存要进行调整……在DELIVERY（发货）时，要向客户。在PAYMENT（支付）时，该事务被存档在HISTORY（历史）中，收入要记帐。TPC-C的SQL案例NEW

ORDER

事务（本地）NEW

ORDER

事务处理来自一个客户的新订单。这里需要做各种不同的事情：获取下一个订单id和客户所在地区的地区税率。增加客户所在地区的下一个订单id，以便用于随后的事务。对于客户订购的每种产品：从ITEM

表检索产品的名称、价格和描述。从STOCK

表检索该产品的地区信息和剩下的库存水平。将该产品的库存水平减去订购的数量。如果库存低于阈值，则应该订购货物（通过简单地增加库存值来实现）。将获得的相关数据，包括总价格，到ORDER_LINE

中。将订单

ORDERS

和NEW_ORDER

表。从CUSTOMER

检索客户名、折扣、信用信息。从WAREHOUSE

检索销售税。考虑折扣和税，计算总价格。TPC-C的SQL案例SQL

1获取下一个订单id

和客户所在地区的地区税率。增加客户所在地区的下一个订单id，以便用于随后的事务。SELECT

D_TAX,

D_NEXT_O_IDINTO

:dist_tax

,

:next_o_idFROM

OLD

TABLE(

UPDATE

DISTRICTSET

D_NEXT_O_ID

=

D_NEXT_O_ID

+

1WHERE

D_W_ID

=

:w_id

6AND

D_ID

=

:d_id

7

)

AS

OTTPC-C的SQL案例SQL

2WITH

DATA

AS

(

SELECT

O_ID

,

D_ID

,

W_ID

,

OL_NUMBER

,

I_ID,

W_ID

AS

I_SUPPLY_W_ID

,

0

ASOL_DELIVERY_D

,

I_QTY

,

(

I_PRICE

*

I_QTY

)TYAS

TOTAL_PRICE

,

OL_DIST_INFO

,

I_PRICE,

I_NAME,

I_DATA,

S_DATA

,

S_FROM(

SELECT

:next_o_id

as

O_ID

,

:w_id

AS

W_ID

,

:d_id

as

D_ID

,

OL_NUMBER

,

I_ID

,

I_QTYFROMTABLE(

VALUES

(

1

,

:id0,:ol_:id3

,

:ol_:id7

,

:ol_ty3

)

,

(

5

,

:id4

,

:ol_ty7

)

,

(

9

,

:id8

,

:ol_ty0)

,

(

2

,

:id1

,

:ol_ty4

)

,

(

6

,

:id5,

:ol_ty8

)

,

(

10

,

:id9,

:ol_ty1)

,

(

3

,

:id2

,

:ol_ty5

)

,

(

7

,

:id6

,

:ol_ty9

)

,

(

11

,

:id10

,

:ol_ty2

)

,

(

4

,ty6

)

,

(

8

,ty10

)

,(

12

,

:id11

,

:ol_ty11)

,

(

13

,

:id12

,

:ol_ ty12

)

,

(

14

,

:id13

,

:ol_ty13

)

,

(

15

,

:id14

,:ol_ ty14

)

)AS

X

(

OL_NUMBER

,

I_ID

,

I_QTY

)

)

AS

ITEMLIST

,TABLE(

NEW_OL_LOCAL(

I_ID

,

I_QTY

,

W_ID,

O_ID

,

D_ID

,

SMALLINT(OL_NUMBER)))

ASNEW_OL_LOCAL

WHERE

NEW_OL_LOCAL.I_PRICE

IS

NOT

NULL)SELECT

I_PRICE

,

I_NAME

,

I_DATA

,

OL_DIST_INFO

,

S_DATA

,

S_

TYFROM

NEW

TABLE(

INSERT

INTO

ORDER_LINE

(

OL_O_ID

,

OL_D_ID

,

OL_W_ID,

OL_NUMBER

,

OL_I_ID

,OL_SUPPLY_W_ID,

OL_DELIVERY_D,

OL_ TY

,

OL_AMOUNT,

OL_DIST_INFO)

INCLUDE

(I_PRICE

INTEGER,

I_NAME

CHAR(24),

I_DATA

VARCHAR(50),

S_DATA

VARCHAR(50),

S_

TYSMALLINT

)SELECT

O_ID

,

D_ID

,

W_ID,OL_NUMBER

,

I_ID

,

I_SUPPLY_W_ID,

OL_DELIVERY_D

,

I_QTY,

TOTAL_PRICE,

OL_DIST_INFO

,

I_PRICE

,

I_NAME,

I_DATA

,S_DATA

,

S_

TYFROM

DATA)

AS

INSTPC-C的SQL案例CREATE

FUNCTION

NEW_OL_LOCAL(

I_IDSQL

2

的表函数INTEGER,I_QTY,W_ID,O_ID,D_IDSMALLINT

INTEGER

INTEGER

SMALLINT,

OL_NUMBER

SMALLINT)RETURNS

TABLE(

I_PRICE

INTEGER,I_NAME,I_DATACHAR(24)

VARCHAR(50),

OL_DIST_INFO

CHAR(24),

S_DATA

VARCHAR(50),

S_ TY

SMALLINT)SPECIFIC

NEW_OL_LOCALMODIFIES

SQL

DATADETERMINISTIC

NO

EXTERNAL

ACTION

LANGUAGESQLVAR

BEGIN

ATOMICDECLARE

I_PRICE

DECLARE

I_NAME

DECLARE

I_DATAINTEGER

;CHAR(24)

;

VARCHAR(50)

;DECLARE

OL_DIST_INFO

CHAR(24)

;

DECLARE

S_DATA VARCHAR(50)

;

DECLARE

S_ TY

SMALLINT

;SET

(

I_PRICE

,

I_NAME

,

I_DATA

)=

(

SELECTI_PRICE,

I_NAME,

I_DATA

FROM

ITEMWHERE

ITEM.I_ID

=NEW_OL_LOCAL.I_ID)

;TY)SET

(

OL_DIST_INFO,

S_DATA

,

S_=

(SELECT

OL_DIST_INFO,

S_DATA,

S_

TYFROM

NEW

TABLE

(

UPDATESTOCKINCLUDE

(

OL_DIST_INFO

CHAR(

24

)

)SET

S_ TY

=TY

-

NEW_OL_LOCAL.I_QTY

>=

10TY

-

NEW_OL_LOCAL.I_QTYTY

-

NEW_OL_LOCAL.I_QTY

+

91CASE

WHENS_

THENS_ELSES_

END, T

= T

+

1,S_YTD =

S_YTD

+

NEW_OL_LOCAL.I_QTY,

OL_DIST_INFO

=CASE

D_IDWHEN

1

THEN

S_DIST_01

WHEN

2

THEN

S_DIST_02

WHEN

3

THEN

S_DIST_03

WHEN

4

THEN

S_DIST_04

WHEN

5

THEN

S_DIST_05

WHEN

6

THEN

S_DIST_06

WHEN

7

THEN

S_DIST_07

WHEN

8

THEN

S_DIST_08

WHEN

9

THEN

S_DIST_09

WHEN

10

THEN

S_DIST_10

ENDWHERE

S_I_ID

=

NEW_OL_LOCAL.I_IDAND

S_W_ID

=NEW_OL_LOCAL.W_ID)AS

U);RETURN

VALUES(

VAR.I_PRICE,

VAR.I_NAME,VAR.I_DATA,VAR.OL_DIST_INFO,VAR.S_DATA,VAR.S_

TY);ENDTPC-C的SQL案例SQL

3SELECT

W_TAX,

C_DISCOUNT,

C_LAST,

C_CREDIT

2

INTO

:ware_tax,:c_discount,

:c_last,:c_credit

3FROMTABLE

(

NEW_WH

(

:next_o_id

4

,

:w_id

5

,

:d_id

6

,

:c_id

7

,

:o_entry_d

8

,

:inputItemCount

9

,

:allLocal10

)

11

)

AS

NEW_WH_TABLECREATE

FUNCTION

NEW_WH

(

O_ID

INTEGER

2

,

W_ID

INTEGER

3

,

D_ID

SMALLINT

4

,

C_IDINTEGER

5

,

O_ENTRY_D

BIGINT

6

, T

SMALLINT

7

,O_ALL_LOCAL

SMALLINT

8

)

9RETURNS

TABLE

(

W_TAX

INTEGER

10

,

C_DISCOUNT

INTEGER

11

,C_LAST

VARCHAR(16)

12

,OC_CREDIT

CHAR(2)

13

)

14

SPECIFIC

NEW_WH

15

MODIFIES

SQL

DEXTERNALACTION

LANGUAGE

SQL

16

VAR:

BEGIN

ATOMIC

17

DECLARE

C_DISCOUNTINTEGER

;18

DECLARE

C_LAST

VARCHAR(16)

;19

DECLARE

C_CREDIT

CHAR(2)

;

20DECLARE

W_TAX

INTEGER

;

21

22

INSERT

23

INTO

NEW_ORDER

(

NO_O_ID,

NO_D_ID,NO_W_ID

)

24

VALUES

(

O_ID

25

,

D_ID

26

,

W_ID

27

)

28

;

29

INSERT

30

INTO

ORDERS

(

O_C_ID,O_ENTRY_D

,

O_CARRIER_ID

,VALUES

(

C_ID

,

O_ENTRY_D

,

0

,T

31

,

O_ALL_LOCAL

,

O_ID

,

O_W_ID

,

O_D_ID

)

32T

33

,

O_ALL_LOCAL

,

O_ID

,

W_ID

,D_ID

)34

;

35

SET(

C_DISCOUNT,

C_LAST,

C_CREDIT

)

36

=

(

SELECT

C_DISCOUNT,

C_LAST,

C_CREDIT

37

FROMCUSTOMER

38

WHERE

C_ID

=

NEW_WH.C_ID

39

AND

C_W_ID

=

W_ID

40

AND

C_D_ID

=

D_ID

41)

42

;

43

SET

W_TAX

44

=

(

SELECT

W_TAX

45

FROM

WAREHOUSE

46

WHERE

W_ID

=NEW_WH.W_ID

47

)

48

;49

RETURN

VALUES

(

W_TAX

,

C_DISCOUNT

,

C_LAST

,

C_CREDIT

)

;50END内容语句的优化并发性的优化并发性和

级并发性和锁系统的统计信息应用的优化并发性和级别级别丢失更新未

的数据不可重复读幻像读现象可重复读（RR）无无无无读稳定性（RS）无无无有游标稳定性（CS）无无有有未

的读（UR）无有有有并发性和级别：未读(UR)并发性和

级别：

游标稳定性(CS)Concurrent

read

with

two

applications

in

cursor

stability

isolation

level并发性和

级别：

游标稳定性(CS)Reading

and

updating

data

with

cursor

stability

isolation

level并发性和级别：读稳定性(RS)并发性和级别：可重复读(RR)级的选择应用程序类型需要高数据稳定性不需要高数据稳定性读写事务(OLTP应用)RSCS只读事务(OLAP应用)RR或RSUR级的指定在预编译或绑定时：对于用受支持的编译语言编写的应用程序，使用命令行处理器PREP

或BIND

命令的ISOLATION

选项。也可以使用PREP或

BIND

API

来指定

级别。如果在预编译时创建绑定文件，则

级别

在绑定文件中。如果在绑定时不指定

级别，则缺省值是预编译期间使用的

级别。级别，则使用游标稳定性的缺省值。如果不指定注:要确定程序包的级别，执行下列查询：SELECT

ISOLATION

FROMSYSCAT.PACKAGES

WHERE

PKGNAME=„

XX‟AND

PKGSCHEMA=„YYYYYYYY‟其中XX

是程序包的名称，而YYYYYYYY

是程序包的模式名。这两种名称都必须大写。在语句级别：

使用

WITH

子句。语句级别的

级别覆盖为出现语句所在的程序包指定的

级别。可以指定下列

SQL

语句的

级别：SELECT,SELECT

INTO,带Where的DELETE,INSERT,带Where的UPDATE,DECLARE

CURSOR下列情况适用于为语句指定的

级别：在子查询上不能使用

WITH

子句WITH

UR

选项只适用于只读操作。在其他情况下，语句自动从UR

更改为CS。运行时从CLI

或ODBC：使用CHANGE

ISOLATION

LEVEL

命令。对于DB2

调用级接口（DB2

CLI），可以将级别更改为DB2

CLI

配置的一部分。在运行时，将SQLSetConnectAttr

函数与SQL_ATTR_TXN_ISOLATION

属性配合使用，来设置由ConnectionHandle

的当前连接的事务

级别。也可以在db2cli.ini

文件中使用TXNISOLATION

关键字。当在运行时使用JDBC

或SQLJ

时：注:JDBC

和SQLJ

是通过DB2®

上的CLI

实现的，这意味着db2cli.ini

设置可能影响使用JDBC

和SQLJ

编写和运行的内容。在SQLJ中，使用SQLJ

概要文件定制程序（db2sqljcustomize

命令）来创建程序包。对于当前会话中的动态

SQL：

使用

SET

CURRENT

ISOLATION语句来设置在会话中发出的动态SQL

语句的

级别。DB2的锁以下因素会影响DB2对于锁的选择：级别优化器选择的

方案表的LOCKSIZE

属性锁所使用的内存量(LOCKLIST等)对DB2的锁分析－案例(步骤1)CREATE

TABLE

MAIN_TABLE(

MAIN_JOIN_COLUMNVARCHAR(10)

NOT

NULL

,MAIN_DATA_COLUMN

VARCHAR(20)

NOT

NULL

)

IN

USERSPACE1

;ALTER

TABLEMAIN_TABLE

ADD

CONSTRAINT

MAINPKEY

PRIMARY

KEY(MAIN_JOIN_COLUMN,

MAIN_DATA_COLUMN);CREATE

TABLE

CHILD_TABLE(

CHILD_JOIN_COLUMN

VARCHAR(10)

NOT

NULL

,CHILD_DATA_COLUMN

VARCHAR(20)

NOT

NULL

)

IN

USERSPACE1;ALTER

TABLE

CHILD_TABLE

ADDCONSTRAINT

CHILDPKEY

PRIMARY

KEY(CHILD_JOIN_COLUMN);对DB2的锁分析－案例(步骤2)第

接：db2

+c“insert

into

main_table

values

('1','deadlock

1')

”db2

+c

“selectmain_table.main_join_column,child_table.child_data_columnfrom

main_table,

child_tablewhere

main_table.main_join_column

=child_table.child_join_columnand

main_table.main_data_column

='deadlock

1'

”被锁定等待，为什么？第二个连接：db2

+c

“insertinto

main_table

values

('1','deadlock

2')

”对DB2的锁分析－案例(步骤3)/home/lorysa

$db2pd

-db

locktest

-locks

show

detail

Locks:Address

TranHdl

Lockname

Type

Mode

Sts

Owner

Dur t

Att

Rlse

0x402C07E0

3000200020000000A0000000052

Row

..X

G

3

1

0

8

0x40

TbspaceID

2

TableID

2

RecordID

0xA

0x402C02E0

2000200020000000A0000000052

Row

.NS

W

3

1

0

0

0x0

TbspaceID

2

TableID

2

RecordID0xA

0x402C03A8

2以下语句可得到锁定的表名/home/lorysa

$db2

"select

substr(tabschema,1,9)

as

tabschema,

substr(tabname,1,12)

astabname,tableid,

tbspaceid

from

syscat.tables

where

tbspaceid

=

2

and

tableid

=

2"TABSCHEMA

TABNAME

TABLEID

TBSPACEID

-LORYSA

MAIN_TABLE

2

2

1

record(s)selected.00000052

Row

..X

G

2

1

0

8

0x40

TbspaceID

2

TableID

2

RecordID

0x9锁等待解释：agent

with

TranHdl

2:

X

lock

GRANTED

on

main

table

row

(0;9)

(fromagent

with

TranHdl

3:

X

lock

GRANTED

on

main

table

row

(0;A)

(fromagent

with

TranHdl

2:

NS

lock

WAITING

on

main

table

row

(0;A)

(from

select)mitted

insert)mitted

insert)对DB2的锁分析－案例(步骤4)SELECTmain_table.main_join_column,child_table.child_data_columnFROM

main_table,

child_tableWHERE

main_table.main_join_column=

child_table.child_join_columnAND

main_table.main_data_column

='deadlock

1„未runstats之前的执行计划对DB2的锁分析－案例(步骤5)SELECT

main_table.main_join_column,child_table.child_data_columnFROM

main_table,

child_tableWHEREmain_table.main_join_column

=child_table.child_join_columnAND

main_table.main_data_column

='dearuns虽然对DB2的锁分析－案例(问题的答案)方法1：调整Main_table上的索引键值顺序，改成(MAIN_DATA_COLUMN+MAIN_JOIN_COLUMN)，这将使查询使用索引避免表扫瞄方法2：DB2SET

MITTED=ON方法3：DB2SET

DB2_SKIPINSERTED=ON避免锁等待的方法1：尽量用短事务2：合理设置

级别3：设置DB2_SKIPINSERTED，DB2_SKIPDELETED,MITTED4：适当使用表锁

5：合理设置lock_list，避免锁升级6：收集统计信息7：合理建立索引，避免表扫瞄内容语句的优化并发性的优化系统的统计信息基本信息分布信息应用的优化收集统计信息（例1)示例1：收集所有列上的统计信息RUNSTATS

ONTABLE

db2admin.department

ON

ALL

COLUMNS这等同于：RUNSTATSONTABLE

db2admin.department示例

2：收集单个列上的

统计信息RUNSTATS

ON

TABLE

db2admin.department

ON

COLUMNS(deptno,

deptname)短语“关键列（keycolumn）”表示构成表上所定义索引的列。如果没有索引存在，这条命令不会收集任何列的统计信息。例

3：收集关键列上的

统计信息RUNSTATSON

TABLE

db2admin.department

ON

KEY

COLUMNS示例

4：收集关键列上和一个非关键列上的

统计信息RUNSTATS

ON

TABLE

db2admin.department

ONKEY

COLUMNS

AND

COLUMNS(deptname)示例

5：收集表和索引上的

统计信息，不包含分布统计信息RUNSTATS

ON

TABLE

db2admin.department

AND

INDEXES

ALL示例

6：收集表上的

统计信息以及索引上的详细统计信息，不包含分布统计信息RUNSTATSONTABLE

db2admin.department

AND

DETAILED

INDEXES

ALL示例

7：只收集

3

个指定索引上的

统计信息（不含表统计信息）RUNSTATS

ON

TABLEdb2admin.department

FORINDEXES

db2admin.INX1,

db2admin.INX2,

db2admin.INX3示例

8：只收集所有索引上的

统计信息RUNSTATS

ON

TABLE

db2admin.department

FORINDEXES

ALL收集统计信息（例2)示例

9：收集表和索引上的

统计信息，包含分布统计信息RUNSTATS

ON

TABLEdb2admin.departmentWITH

DISTRIBUTION

AND

INDEXES

ALL示例

10：收集表上的

统计信息以及索引上的详细统计信息，包含分布统计信息RUNSTATS

ON

TABLEdb2admin.department

WITH

DISTRIBUTION

AND

DETAILED

INDEXES

ALL示例

11：收集选定列中包含分布的

统计信息RUNSTATS

ON

TABLE

db2admin.department

WITHDISTRIBUTION

ON

COLUMNS

(deptno,

deptname)示例

12：只收集表上的

统计信息，包含deptno

和

deptname

上的基本列统计信息以及

mgrno

和admrdept

上的分布统计信息RUNSTATS

ON

TABLE

db2admin.department

ON

COLUMNS(deptno,

deptname)WITH

DISTRIBUTION

ON

COLUMNS(mgrno,

admrdept)示例13：收集构成索引的所有列以及两个非索引列中包含分布的

统计信息RUNSTATS

ON

TABLE

db2admin.department

WITHDISTRIBUTIONON

KEY

COLUMNS

ANDCOLUMNS

(admrdept,

location)收集统计信息（例3)示例

14：收集包含分布统计信息的

统计信息RUNSTATS

ON

TABLEdb2admin.departmentWITH

DISTRIBUTION

AND

INDEXES

ALL将

num_freqvaluesparameter

设置为

10，

num_ les

parameter

设置为

20，因为在命令行上没有指定

num_freqvalues

和num_ les

参数。示例

15：仅收集表上的

统计信息，其中使用指定的num_freqvalues以及从数据库配置设置选择num_ les

收集所有列上的分布统计信息RUNSTATS

ON

TABLEdb2admin.department

WITH

DISTRIBUTION

DEFAULT

NUM_FREQVALUES

40将

num_freqvalues

参数设置为

40，

num_ les

参数设置为

20。示例

16：收集表上的

统计信息，包含列deptno

和deptname

上的分布统计信息。单独为

deptname

列设置分布统计信息的范围，

而deptno

列使用公共的默认值。并且还为两个索引IDX1

和IDX2

收集

统计信息。RUNSTATS

ON

TABLE

db2admin.department

WITH

DISTRIBUTIONON

COLUMNS

(deptno,

deptna

M_FREQVALUES

50NUM_ LES

100)

DEFAULT

NUM_FREQVALUES

5NUM_ LES

10

AND

INDEXES

db2admin.IDX1,

db2admin.IDX2对于deptname

列，num_freqvalues

是50，num_对于deptno

列，num_freqvalues

5，num_les

是100。les

是10。示例

18：收集所有索引以及列deptno

和deptname

上的

统计信息。deptno

列的

num_freqvalues

和num_ les

值将从默认值中获得。RUNSTATS

ON

TABLE

db2admin.department

WITH

DISTRIBUTIONON

COLUMNS

(deptno,

deptnaM_FREQVALUES

20NUM_LES

0

ANDINDEXES

ALLles

是40。les

是0。LES

40)

DEFAULT

NUM_FREQVALUES

0NUM_对于deptname

列，num_freqvalues

是20，num_对于deptno

列，num_freqvalues

是0，num_其他所有列不包含任何统计信息。收集统计信息：num_freqvalues高频值统计信息示例如果高频值统计信息是可用的，优化器可以使用这些统计信息来选择适当的

方案，如下所示：如果

KEY

是

N

个最高频值的其中一个，则优化器使用

在该

中的

KEY

的频率。如果KEY

不是N

个最高频值的其中一个，则优化器在假定（COLCARD-N）个非高频值具有均匀分布的前提下估计满足该谓词的行数。即，估计行数为：CARD-NUM_FREQ_ROWS

(2)

COLCARD

-

N其中，CARD

是表中的行数，COLCARD

是列的基数，NUM_FREQ_ROWS

是具有N

个最高频值的其中一个最高频值的总行数。例如，考虑具有如下数据值和频率的一列（C1）：数据值频率12233404451如果仅基于最高频值（即，N=1）的高频值统计信息可用，则对于此列，表中的行数为50，列基数为5。对于谓词C1=3，刚好有40

行满足它。如果优化器假定数据均匀分布，则它估计满足该谓词的行数为50/5=10，错误率为-75%。如果优化器可以使用高频值统计信息，则该行数估计为40，且没有错误。考虑另一个示例，其中有2

行满足谓词C1=1。在没有高频值统计信息的情况下，满足谓词的行数估计为10，错误率为400%。您可使用以下公式来计算估计错误率（百分比）：估计行数-实际行数

X

100

使用高频值统计信息（N=1），优化器将使用以上给出的公式实际行数收集统计信息（例4)示例

19：收集捕获了列组的

统计信息RUNSTATS

ON

TABLE

db2admin.department

ON

COLUMNS

((deptno,

deptname),

deptname,

mrgno,

(admrdept,

location))示例

20：收集所有列上的

统计信息并指定

VARCHAR

列上的

LIKE

统计信息RUNSTATS

ON

TABLE

db2admin.department

ON

ALL

COLUMNS

and

COLUMNS

(deptname

LIKE

STATISTICS)示例

27：收集两个索引上的详细

统计信息，但对每个索引条目使用抽样来代替执行详细的计算RUNSTATS

ON

TABLE

db2admin.department

AND

SAMPLED

DETAILEDINDEXES

ALL示例28：收集索引上的详细抽样统计信息和表的分布统计信息RUNSTATS

ONTABLEdb2admin.department

WITH

DISTRIBUTIONON

KEY

COLUMNSAND

SAMPLED

DETAILED

INDEXES

ALL收集统计信息（例5)带有行级贝努里（Bernoulli）抽样的RUNSTATS行级贝努里（Bernoulli）抽样利用sargable（search+argument-able谓词是一个可以由数据管理器来评估的谓词）谓词获得百分之P

的表行样本，在样本中包含每一行的概率是P/100，而不包含它的概率则是1-P/100。例如，对于

10%

贝努里（Bernoulli）抽样，将会选择

10%（10/100）的行，而

90%（1-10/100）的行。在贝努里（Bernoulli）抽样中，每一页将会

一次

I/O，因为要扫描该表。将生成一个随机数来确定是否选择一行（类似于以

P/100

的概率扔钱币）。即使每一页都发生一次I/O，

仍然节省了处理数据所需的

CPU

时间。在行级贝努里（Bernoulli）抽样中，需要 每一个数据页。然而，它仍然可以带来极大的性能提高，因为

RUNSTATS

是CPU

密集的。如果索引是可用的，那么就会改进抽样。如果数据是群集的，它还可以提供更准确的统计信息（所获得的样本更好地代表了整个表数据）。示例29：收集统计信息，包含10%

行上的分布统计信息RUNSTATS

ON

TABLE

db2admin.department

WITH

DISTRIBUTIONTABLESAMPLE

BERNOULLI

(10)收集统计信息（例6)带有系统页级抽样的

RUNSTATS系统页级抽样与行级抽样类似，除了抽样的对象是页面而不是行。以P/100

的概率选择每一页，而以

1

-

P/100

的概率

页的选择。在所选中的每一页中，要选择所有的行。系统页级抽样优于全表扫描或贝努里（Bernoulli）抽样的地方是它节省了

I/O。抽样页也是预取的，所以该方法将比行级贝努里（Bernoulli）抽样更快。与不进行抽样相比，页级抽样极大地提高了性能。RUNSTATSrepeatable子句允许通过

RUNSTATS

语句生成相同的样本，只要表数据没有发生更改。为了指定该选项，用户还必须提供一个整数，以表示将用于生成样本的

（seed）。通过使用相同的 ，可以生成相同的样本。总之，统计信息的准确性取决于抽样率、数据倾斜（data

skew）以及用于数据抽样的数据群集。示例31：收集10%的数据页上的索引统计信息和表统计信息。请注意，只对表数据页进行抽样，而不是索引页。本例中，10%

的表数据页用于表统计信息的收集，而对于索引统计信息，将使用所有的索引页。RUNSTATS

ONTABLE

db2admin.department

AND

INDEXES

ALL

TABLESAMPLE

SYSTEM

(10)内容语句的优