profile使用入门 :)--pub精华!

很多开发和数据库管理人员都在为优化器问题烦恼不已。尽管很多时候优化器问题都是可以通过常规手段解决的，但是在某些特殊情况下，或者紧急情况（没有时间完整地分析问题）下，用户可以使用profile暂时强制优化器使用某些特定的操作。。。ITPUB个人空间z$}/_c/~kFX {1_ D
下面是一个step by step的例子，简单地说明了怎样强制优化器使用table scanITPUB个人空间C&d9D!ez"W7\[ dW?
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
O5G
\o\V%\0level identifier "02010107".
/i6|1t;GW0dB
vo0Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".ITPUB个人空间 T;W0uBo!nZ5u.r
Product is installed at "D:PROGRA~1IBMSQLLIB" with DB2 Copy NameITPUB个人空间vJ7y p NZ
"DB2COPY1".ITPUB个人空间L+M`&Pm'_
ITPUB个人空间rN6I7['m _ a1waU
<---------------创建一个数据库
yYIv~0D:TEMPdb2service.perf1>db2 create db sampel2DB20000I The CREATE DATABASE command completed successfully.ITPUB个人空间P;LIDwK;G IV t

6Lb!d;L,w D0D:TEMPdb2service.perf1>db2 connect to sampel2ITPUB个人空间hTVb?_"kl]t

d2Ur"Px}W"c0 Database Connection InformationITPUB个人空间 n~FsFP'k&Q~

/O/A'M/O%Om2p.G0Database server = DB2/NT 9.1.0ITPUB个人空间 a meI3ct/@5m.HE
SQL authorization ID = TAOEWANGITPUB个人空间#_f-ZHa0aW U+cR6A(Pd
Local database alias = SAMPEL2ITPUB个人空间*r'H{/zl[q
ITPUB个人空间5}'v!Z#S,fnn]d] Q
<----------创建优化器系统表
"]*ZC'W8@qwT?5wS0D:TEMPdb2service.perf1>db2 "create table systools.opt_profile (schema VARCHAR(128) not null, name varchar(128) not null, profile blob (2M) not null, primary key (schema, name))"ITPUB个人空间SB6^Z[ aA
DB20000I The SQL command completed successfully.ITPUB个人空间4L]I*}:C.b
ITPUB个人空间p1Z5j?6]Ss:ic
D:TEMPdb2service.perf1>cd ..

|5ZZr0Pn Fr0
\4c
z,v?h+mN0<----------创建用户表
+V[1? cA0D:TEMP>db2 "create table mytable (name varchar(128), id integer, salary float,phone varchar(20))"
_"xU#AI:pp0DB20000I The SQL command completed successfully.ITPUB个人空间1aJ2bi1zXUl:i
ITPUB个人空间&I(e.Vv)d;K"nP
<-----------插入一些数据
TTHC0M&Yw+Ac0D:TEMP>db2 "insert into mytable values ('tao wang', 12345, 100, '123-456')"ITPUB个人空间ZX B7Pwb9y@
DB20000I The SQL command completed successfully.
(]!C.og w&f@%]0ITPUB个人空间Y]g@/o2b"Q
D:TEMP>db2 "insert into mytable values ('diablo2', 12346, 101, '123-457')"ITPUB个人空间(jZ#g2eUJ0~D
DB20000I The SQL command completed successfully.ITPUB个人空间1\5dzsrc Up
ITPUB个人空间\&C [!S l{w|%N
D:TEMP>db2 "insert into mytable values ('whiterain', 123, 102, '123-458')"
$_Fr!k7O'ln0DB20000I The SQL command completed successfully.
MW[/BRm&lYM0
fuO3p8?k0F0D:TEMP>db2 "insert into mytable values ('ganquan', 1255, 104, '123-459')"
ne)JS0a
u0DB20000I The SQL command completed successfully.
Bb*Ov/M6G_*R"sR,n0
t4K|C5U5{.p0<---------别忘了runstats
6gR/d W'`z)rf eGsm0D:TEMP>db2 "runstats on table taoewang.mytable"ITPUB个人空间4lm,A%t b&t8c
DB20000I The RUNSTATS command completed successfully.
t7F&]d gQ1`0ITPUB个人空间EU[`8E1eS
D:TEMP>db2 "runstats on table taoewang.mytable for indexes all"ITPUB个人空间.m(R:QV.F
DB20000I The RUNSTATS command completed successfully.
E8] r5hoz x5a0
6m!fm:@RAx:S1g0ITPUB个人空间3W/gEXS [G.g s
<-----------试试看ITPUB个人空间-C1V(X\&hv.oc3j S|9xx
D:TEMP>db2 "SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000"
$P;@"jMX#q3r0
t q#V
Lv&l~0NAMEITPUB个人空间 S3BN2za8j/v&c/c"j
ID SALARY
Ihl[5g?0 PHONEITPUB个人空间~fIs5@-^"zl
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------ --------------------ITPUB个人空间
r*\N-_0KJd
whiterain
.w-fxD;c6h?t0 123 +1.02000000000000E+002 123-458ITPUB个人空间jeG|EQy+i;M

Wc9zT^Z T6IZ0 1 record(s) selected.
?tf%vF6A0ITPUB个人空间LaS$cEq%S
<--------创建explain表ITPUB个人空间#iHE6rzi
D:TEMP>cd D:Program FilesIBMSQLLIBMISCITPUB个人空间e,Tu0d2J~;|

B!s!n9g,_-j]0D:Program FilesIBMSQLLIBMISC>db2 -tvf EXPLAIN.DDL
:AN,a(k['a ~"j+A0.....
U&}K Y]$?X0ITPUB个人空间'S7dF!TE0K jI
D:Program FilesIBMSQLLIBMISC>cd D:tempITPUB个人空间RB#r)NZI5~}YP#~

uFn~!J u-e0<----------看一看现在的访问计划ITPUB个人空间D`Y6Mpb+jRQ#N
D:TEMP>db2 set current explain mode explain
"_^?%Bg0DB20000I The SQL command completed successfully.ITPUB个人空间#kis`,v+?J
ITPUB个人空间d-~afBk+C
D:TEMP>db2 "SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000"
g8~b&c d2[7w7]0SQL0217W The statement was not executed as only Explain information requestsITPUB个人空间XOXiy!S
are being processed. SQLSTATE=01604ITPUB个人空间7}{&p q%u6[ dv(\
ITPUB个人空间q4h5pyZn&cN`3RG
D:TEMP>db2 set current explain mode no
MT1i(zs }*\ ]}~R0DB20000I The SQL command completed successfully.
.?Hx6`T*s0ITPUB个人空间9Ia0]:Fit
D:TEMP>db2exfmt -d sampel2 -g TIC -w -1 -n % -s % -# 0 -o output.txtITPUB个人空间f,^dGt~
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006ITPUB个人空间1yS:~SBn[Q
Licensed Material - Program Property of IBM
/n&\NJ;k+`0IBM DATABASE 2 Explain Table Format ToolITPUB个人空间 N@5HF|[I
ITPUB个人空间
p[1P"]['p!Go
Connecting to the Database.ITPUB个人空间g~2qE XAZ
Connect to Database Successful.ITPUB个人空间q#~+MoqxV6d
Binding package - Bind was Successful
2i^[~"G
U!S,b0Output is in output.txt.
\ZX \ C\0Executing Connect Reset -- Connect Reset was Successful.ITPUB个人空间!UcSlW1^.i y
ITPUB个人空间KmoT$M8kY
D:TEMP>uedit32 output.txtITPUB个人空间'?^,_uO h

/Y ] h%m;d*@F-fm0<-------------现在优化器用了index scan
.Pc,v1D.h5y0Original Statement:
i&S^6x\^!hfQ0------------------ITPUB个人空间Ao5| z/b6M]
SELECT * ITPUB个人空间-v i o5?-u7H
FROM TAOEWANG.MYTABLE ITPUB个人空间(\~ NS-?V
WHERE ID < 1000
j U/vN3^8yn0ITPUB个人空间&Yx)SoF~:^5W+@
ITPUB个人空间p["n8`,MS-d
Optimized Statement:ITPUB个人空间U]3}-m0\q
-------------------ITPUB个人空间_;MN.EC.dE?y
SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS ITPUB个人空间#n%X u*Zz{F
"PHONE"
.b}t5KX-L0FROM TAOEWANG.MYTABLE AS Q1 ITPUB个人空间0[t/pQC|&uR-nQ
WHERE (Q1.ID < 1000)ITPUB个人空间&},w/~ zmWp$E;r,K;j9S(N

.b0`YZEt5W0Access Plan:ITPUB个人空间+\5Z#Y nbfT7t ll
-----------ITPUB个人空间+XLk] V!YE
Total Cost: 7.56853ITPUB个人空间3lG9[c!Fr R4a;\p8_v
Query Degree: 1ITPUB个人空间f$|[]S

ODx"hW z0 Rows
:L[Kjf:c_O0 RETURN
+} RxhVeS Mq0 ( 1)ITPUB个人空间b5zl4|z+r|
Cost ITPUB个人空间f9q f ?}*lhf,a
I/O ITPUB个人空间&u!dJ:F&PG!FK]
|
0LFt6D-Y7MveE.R0 1 ITPUB个人空间1v xP-y#X
FETCH
^8gi,eQ_0 ( 2)
(^m _H%G'a2o\5u0 7.56853
f1P&qU)@,@/f5]0 1
Q7D4F3} pT0 /----+---
:_kL6qGg J1_.W]0 1 4
8LYd7Bf6p0 IXSCAN TABLE: TAOEWANG
#z%[ HKN8K_P.j0 ( 3) MYTABLEITPUB个人空间S \tD`g6|)P
0.00630865 ITPUB个人空间6f;Pz)i9PK'@|
0
6X%q_5P0y6^$u-g
a d0 |
HMc(W$Qo"Hg~0 4
Q?W9q9N)w&C,X0INDEX: TAOEWANGITPUB个人空间:E(_H&?s$[
IX1ITPUB个人空间?a8B9aoc

7fu)@,KL)^#H0<---------创建一个xml文件，叫做a1.xmlITPUB个人空间{U$bF(c;A
a1.xml:ITPUB个人空间I'_M.s{ ^/@
<?xml version="1.0" encoding="UTF-8"?>ITPUB个人空间m2F4aG7qc
<OPTPROFILE VERSION="9.1.0.0">
w!Y#^RLM4^C0<STMTPROFILE ID="Use Table Scan instead of Index Scan">ITPUB个人空间)vnZz;}$mfo|
<STMTKEY SCHEMA="TAOEWANG">ITPUB个人空间%WBq9R} _|
<![CDATA[SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000]]>ITPUB个人空间tj| bO*d(X
</STMTKEY>ITPUB个人空间5hEU0Y:b*?r r!nj
<OPTGUIDELINES>
fh9A!v6v@ aIr0<TBSCAN TABLE="TAOEWANG.MYTABLE"/>ITPUB个人空间 n&e:}oO$H$u7~p5?6^1V
</OPTGUIDELINES>ITPUB个人空间Q,DQV*xD%Fj!i9fa
</STMTPROFILE>ITPUB个人空间X:JU.v7H _H
</OPTPROFILE>ITPUB个人空间MB'i"]/?'|"zj

YxRi4oOlL u0<----------创建一个del文件,叫insert.delITPUB个人空间D%_
}Y{1C5I
insert .del:
8i6}r? i6W/B a~m0"TAOEWANG", "PROF1", "a1.xml"
U8@cB([qx#l x`0
@4Q
XdD ]0<--------用import把xml插入profile系统表
*zS/H8|`t"zK
A4A9C9E0D:TEMP>db2 import from insert.del of del modified by lobsinfile insert into systools.opt_profileITPUB个人空间$^c'nz;NWSf
SQL3109N The utility is beginning to load data from file "insert.del".ITPUB个人空间8K*Pi,O"MH-B
ITPUB个人空间2U\.hI0YrEVX!~ H
SQL3110N The utility has completed processing. "1" rows were read from the
(l#`Pl!d0input file.
/kf%az3dgR0
MPl-P5jH0SQL3221W ...Begin COMMIT WORK. Input Record Count = "1".ITPUB个人空间NCid1E2pp3A

s)E*g*}x5XKM5r6MB%{0SQL3222W ...COMMIT of any database changes was successful.
0@uw7Y$E1^W0ITPUB个人空间O#~Sz~kP
SQL3149N "1" rows were processed from the input file. "1" rows weresuccessfully inserted into the table. "0" rows were rejected.ITPUB个人空间p.Il+O]0{8o
ITPUB个人空间j[6Mn.fR8tp

9~N2y:@T Q~0Number of rows read = 1
\&^#k(ml"M
l8V3Y8l5s0Number of rows skipped = 0ITPUB个人空间-I+t h.QWLz&V X+Y
Number of rows inserted = 1ITPUB个人空间r7{
}f(M!{
Number of rows updated = 0ITPUB个人空间V4YJF5|MK)i0}
Number of rows rejected = 0
JG/^*xw1D5@%G0Number of rows committed = 1ITPUB个人空间V4YC OizN.Uca
ITPUB个人空间u,]YyR'vF
<--------设置db2set env variable打开profile
"E1|8ym*i)L(V y
L5ap0D:TEMP>db2set DB2_OPTPROFILE=YESITPUB个人空间W#];F'BW/],F|@`
ITPUB个人空间Pkg^:Y!`%mn&H
<--------重起实例让db2set生效ITPUB个人空间T%k!~!iP rI
D:TEMP>db2stop force
9]#vA K7~012/27/2007 08:54:45 0 0 SQL1064N DB2STOP processing was successful.ITPUB个人空间{p$YP!r
c*n3h1\#D
SQL1064N DB2STOP processing was successful.ITPUB个人空间tt(\(Rv9@]

&V lTj+S{5G\K0D:TEMP>db2startITPUB个人空间-S t/MA/w
12/27/2007 08:54:48 0 0 SQL1063N DB2START processing was successful.ITPUB个人空间c+T9BRsVSo
SQL1063N DB2START processing was successful.ITPUB个人空间Xi$IMo|F$_
ITPUB个人空间ZK1eu"C5A2c
D:TEMP>db2 connect to SAMPEL2
:b@0Gatp0ITPUB个人空间:W"hdkE6alH5^CE
Database Connection InformationITPUB个人空间k il#nx8L{
ITPUB个人空间$k~7lGz*tr
Database server = DB2/NT 9.1.0ITPUB个人空间%W-K$e"QtS~ h5s
SQL authorization ID = TAOEWANG
D*t:MK[9RCMc0Local database alias = SAMPEL2
$l$@;i)G0E1m0N8X^v0
i2J$aocv(ETL0
id{a7|h?`9N0D:TEMP>db2 set current explain mode explain
U [lPt`M/Jr0DB20000I The SQL command completed successfully.
q+V3lxA)x$V1G0
*}&l }*{c+i0D:TEMP>db2 set current schema taoewang
'h3l4hV:o*_IvK~c0DB20000I The SQL command completed successfully.
a2eq"`#S zy0
]2B)e"\5hT+\0<---------设置需要使用的profileITPUB个人空间!jHBe1Y0[K~j7R(a
D:TEMP>db2 set current optimization profile='PROF1'ITPUB个人空间U:}}UX{S'Z$vJ~
DB20000I The SQL command completed successfully.ITPUB个人空间]@"f F5IR8G

$jr)O!|S,g0D:TEMP>db2 "SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000"ITPUB个人空间:obG6eXW)grf
SQL0217W The statement was not executed as only Explain information requestsITPUB个人空间\~9OK?'G
are being processed. SQLSTATE=01604ITPUB个人空间'v_yl:C'g ^8{I

yA%gH pA v0D:TEMP>db2 set current explain mode no
|(~g;_
W9w#]0DB20000I The SQL command completed successfully.
N? s1o,ym0
[a`+z8cb5Ad0D:TEMP>db2exfmt -d sampel2 -g TIC -w -1 -n % -s % -# 0 -o output2.txt
%?&V1Fk$g+m0DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006ITPUB个人空间.E:\ |$S3\,l+K d
Licensed Material - Program Property of IBMITPUB个人空间Gu5j%FZW e
IBM DATABASE 2 Explain Table Format Tool
A8wl;mpF
H0
#U9nV?Up0Connecting to the Database.
wFz7j"q,@7W!N0Connect to Database Successful.
],ye%{B1D&~D6A/a0Output is in output2.txt.
(] M2|J7IQ0Executing Connect Reset -- Connect Reset was Successful.
Cd-L|-y[;y0ITPUB个人空间/D YETK6rv/M
<------现在用了tablescan了
`)x|t
ke0D:TEMP>uedit32 output2.txt
y o\B&CLo&DM0Profile Information:ITPUB个人空间6C$k(Msjl
--------------------ITPUB个人空间0x+k,b N,n
OPT_PROF: (Optimization Profile Name)
+S.J0H8rdee.tiLY0 TAOEWANG.PROF1ITPUB个人空间2v(~$hI'KK1D
STMTPROF: (Statement Profile Name)ITPUB个人空间 u.z]2t1i(S.{
Use Table Scan instead of Index Scan
w |.Z8kZ/|0ITPUB个人空间 Y'j(TI:PWQ)V0y
Original Statement:
8J+I-n.z!PU2e0------------------

Hw@-a+e+o/j'za0SELECT *
/t,e:g"kQ0O0FROM TAOEWANG.MYTABLE ITPUB个人空间2L.W
m e4\(B:l"k7`
WHERE ID < 1000
0MvVn,NQ6n]0ITPUB个人空间i#b4wl6_+TTQz

AAZO(ya P0Optimized Statement:
*a/b4|C;lBw6Cx0-------------------ITPUB个人空间EzEj[x K T
SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS ITPUB个人空间n O bQB3l
"PHONE"
5B$^iu!a2D0FROM TAOEWANG.MYTABLE AS Q1
+CQf&y,\%]0WHERE (Q1.ID < 1000)
KcQ(w]K0ITPUB个人空间7N0@)s"BUA/G9Cc
Access Plan:
y6SwWz(B-ahU0-----------
dh+s'^*R0 Total Cost: 7.56912ITPUB个人空间o]$L,u!f0Y#z
Query Degree: 1
N*F;GF:J T)_J+t k0ITPUB个人空间R,]7x;B(Yu+h
Rows ITPUB个人空间 i/ef:D j ]w
RETURNITPUB个人空间%|:T:c6A2U0Wz/L3rZ
( 1)ITPUB个人空间/^Q
d.Q*i(v0{u
Cost
#K/L.`of0 I/O
V`:n:VXa D;v0 |
$OUcn;Za+Df0 1 ITPUB个人空间2z"RjTl!q(r
TBSCANITPUB个人空间/}0LfC%}&y8al
( 2)ITPUB个人空间MK'Z%}:e
7.56912 ITPUB个人空间QRD#~$H
1
\M*@AT7iOt#o0 |
Pr*ye"_B0 4 ITPUB个人空间d3TU*LO O
BQl3e
TABLE: TAOEWANG
Ys"U@b{0 MYTABLEITPUB个人空间-djGra&wZe1M

Ld]bmu0
(yNl6Wl3T~'h0
L%f0B@#Yy$@;D0
5DB:Y2Y:G/ag0Extended Diagnostic Information:
(oewN5d'^0--------------------------------ITPUB个人空间h0sR\T,Jlu0X

1s*t@P#\*ee0No extended Diagnostic Information for this statment.ITPUB个人空间2HM.P? O

!N&PsL Jm-U%qkc0如果用户希望在应用程序里面使用profile,可以使用下面的几种方法之一
:~#x _j'i0ITPUB个人空间Ys?,N:gPl
I-[
对于cli应用程序中使用EXEC SQL SET CURRENT OPTIMIZATION PROFILE = 'xxxxxxxxx';ITPUB个人空间;G`}%B gR
也可以在db2cli.ini中指定CURRENTOPTIMIZATIONPROFILE='"SCHEMA"."PROFILE"'
gO ]S |kt4PL!q%e0对于stored procedure在bind的时候指定OPTPROFILEITPUB个人空间%``IU+Sd @0C?

!a-s$YO$k9d9F:{Fu#E0再次强调,profile不是万能药,只是止痛药~~~只有在万不得已的情况下才应该使用profile暂时指定用户需要的操作.一般来说,对于优化器问题用户应该尽量找到root cause,而不是简单地指定一个profile了事~~~