profile使用入门 :)

wangzhonnew · 发表于 2007-12-27 23:06

很多开发和数据库管理人员都在为优化器问题烦恼不已。尽管很多时候优化器问题都是可以通过常规手段解决的，但是在某些特殊情况下，或者紧急情况（没有时间完整地分析问题）下，用户可以使用profile暂时强制优化器使用某些特定的操作。。。
下面是一个step by step的例子，简单地说明了怎样强制优化器使用table scan
DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
level identifier "02010107".
Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".
Product is installed at "D:\PROGRA~1\IBM\SQLLIB\" with DB2 Copy Name
"DB2COPY1".

<---------------创建一个数据库
D:\TEMP\db2service.perf1>db2 create db sampel2DB20000I  The CREATE DATABASE command completed successfully.

D:\TEMP\db2service.perf1>db2 connect to sampel2

Database Connection Information

Database server       = DB2/NT 9.1.0
SQL authorization ID = TAOEWANG
Local database alias = SAMPEL2

<----------创建优化器系统表
D:\TEMP\db2service.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))"
DB20000I  The SQL command completed successfully.

D:\TEMP\db2service.perf1>cd ..

<----------创建用户表
D:\TEMP>db2 "create table mytable (name varchar(128), id integer, salary float,phone varchar(20))"
DB20000I  The SQL command completed successfully.

<-----------插入一些数据
D:\TEMP>db2 "insert into mytable values ('tao wang', 12345, 100, '123-456')"
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "insert into mytable values ('diablo2', 12346, 101, '123-457')"
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "insert into mytable values ('whiterain', 123, 102, '123-458')"
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "insert into mytable values ('ganquan', 1255, 104, '123-459')"
DB20000I  The SQL command completed successfully.

<---------别忘了runstats
D:\TEMP>db2 "runstats on table taoewang.mytable"
DB20000I  The RUNSTATS command completed successfully.

D:\TEMP>db2 "runstats on table taoewang.mytable for indexes all"
DB20000I  The RUNSTATS command completed successfully.

<-----------试试看
D:\TEMP>db2 "SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000"

NAME
                                             ID       SALARY
   PHONE
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------ --------------------
whiterain
                                                      123 +1.02000000000000E+002 123-458

  1 record(s) selected.

<--------创建explain表
D:\TEMP>cd D:\Program Files\IBM\SQLLIB\MISC

D:\Program Files\IBM\SQLLIB\MISC>db2 -tvf EXPLAIN.DDL
.....

D:\Program Files\IBM\SQLLIB\MISC>cd D:\temp

<----------看一看现在的访问计划
D:\TEMP>db2 set current explain mode explain
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604

D:\TEMP>db2 set current explain mode no
DB20000I  The SQL command completed successfully.

D:\TEMP>db2exfmt -d sampel2 -g TIC -w -1 -n % -s % -# 0 -o output.txt
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Binding package - Bind was Successful
Output is in output.txt.
Executing Connect Reset -- Connect Reset was Successful.

D:\TEMP>uedit32 output.txt

<-------------现在优化器用了index scan
Original Statement:
------------------
SELECT *
FROM TAOEWANG.MYTABLE
WHERE ID < 1000

Optimized Statement:
-------------------
SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS
      "PHONE"
FROM TAOEWANG.MYTABLE AS Q1
WHERE (Q1.ID < 1000)

Access Plan:
-----------
Total Cost: 7.56853
Query Degree: 1

         Rows
         RETURN
         ( 1)
         Cost
            I/O
            |
            1
         FETCH
         ( 2)
         7.56853
            1
      /----+---\
      1          4
   IXSCAN TABLE: TAOEWANG
   ( 3)       MYTABLE
0.00630865
      0
   |
      4
INDEX: TAOEWANG
   IX1

<---------创建一个xml文件，叫做a1.xml
a1.xml:
<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="9.1.0.0">
<STMTPROFILE ID="Use Table Scan instead of Index Scan">
<STMTKEY SCHEMA="TAOEWANG">
<![CDATA[SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000]]>
</STMTKEY>
<OPTGUIDELINES>
<TBSCAN TABLE="TAOEWANG.MYTABLE"/>
</OPTGUIDELINES>
</STMTPROFILE>
</OPTPROFILE>

<----------创建一个del文件,叫insert.del
insert .del:
"TAOEWANG", "PROF1", "a1.xml"

<--------用import把xml插入profile系统表
D:\TEMP>db2 import from insert.del of del modified by lobsinfile insert into systools.opt_profile
SQL3109N  The utility is beginning to load data from file "insert.del".

SQL3110N  The utility has completed processing.  "1" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "1" rows were processed from the input file.  "1" rows weresuccessfully inserted into the table.  "0" rows were rejected.

Number of rows read       = 1
Number of rows skipped    = 0
Number of rows inserted    = 1
Number of rows updated    = 0
Number of rows rejected    = 0
Number of rows committed = 1

<--------设置db2set env variable打开profile
D:\TEMP>db2set DB2_OPTPROFILE=YES

<--------重起实例让db2set生效
D:\TEMP>db2stop force
12/27/2007 08:54:45    0 0 SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

D:\TEMP>db2start
12/27/2007 08:54:48    0 0 SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

D:\TEMP>db2 connect to SAMPEL2

Database Connection Information

Database server       = DB2/NT 9.1.0
SQL authorization ID = TAOEWANG
Local database alias = SAMPEL2

D:\TEMP>db2 set current explain mode explain
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 set current schema taoewang
DB20000I  The SQL command completed successfully.

<---------设置需要使用的profile
D:\TEMP>db2 set current optimization profile='PROF1'
DB20000I  The SQL command completed successfully.

D:\TEMP>db2 "SELECT * FROM TAOEWANG.MYTABLE WHERE ID < 1000"
SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604

D:\TEMP>db2 set current explain mode no
DB20000I  The SQL command completed successfully.

D:\TEMP>db2exfmt -d sampel2 -g TIC -w -1 -n % -s % -# 0 -o output2.txt
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Connecting to the Database.
Connect to Database Successful.
Output is in output2.txt.
Executing Connect Reset -- Connect Reset was Successful.

<------现在用了tablescan了
D:\TEMP>uedit32 output2.txt
Profile Information:
--------------------
OPT_PROF: (Optimization Profile Name)
TAOEWANG.PROF1
STMTPROF: (Statement Profile Name)
Use Table Scan instead of Index Scan

Original Statement:
------------------
SELECT *
FROM TAOEWANG.MYTABLE
WHERE ID < 1000

Optimized Statement:
-------------------
SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS
      "PHONE"
FROM TAOEWANG.MYTABLE AS Q1
WHERE (Q1.ID < 1000)

Access Plan:
-----------
Total Cost: 7.56912
Query Degree: 1

   Rows
   RETURN
   ( 1)
   Cost
   I/O
   |
      1
   TBSCAN
   ( 2)
   7.56912
      1
   |
      4
TABLE: TAOEWANG
   MYTABLE

Extended Diagnostic Information:
--------------------------------

No extended Diagnostic Information for this statment.

如果用户希望在应用程序里面使用profile,可以使用下面的几种方法之一

对于cli应用程序中使用EXEC SQL  SET CURRENT OPTIMIZATION PROFILE = 'xxxxxxxxx';
也可以在db2cli.ini中指定CURRENTOPTIMIZATIONPROFILE='"SCHEMA"."PROFILE"'
对于stored procedure在bind的时候指定OPTPROFILE

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

[ 本帖最后由 wangzhonnew 于 2007-12-28 00:11 编辑 ]

unixnewbie · 发表于 2007-12-28 06:12

让我来设精吧。

myfriend2010 · 发表于 2007-12-28 08:46

......没听说过,看到了希望!

myfriend2010 · 发表于 2007-12-28 08:47

敬仰之!

diablo2 · 发表于 2007-12-28 09:09

不错

myfriend2010 · 发表于 2007-12-28 09:14

问一下哈!PROF1怎么理解?是不是代表某一个计划名称?!

也就是说:systools.opt_profile中,schema代表用户的schema;
name代表表的特定执行计划的名称,可以随意取,但和db2 set current optimization profile='PROF1'要一致~!
profile代表执行计划的哪个xml?
理解对不?

wangzhonnew · 发表于 2007-12-28 09:34

恩，是这样的，我用相同的颜色进行了标记

diablo2 · 发表于 2007-12-28 09:39

systools.opt_profile 需要自己创建?

wangzhonnew · 发表于 2007-12-28 09:43

恩，v9.5里面有一个stored procedure可以创建，忘了v9里有没有，去infocenter上看一下吧：）

diablo2 · 发表于 2007-12-28 10:11

V9也有的

SYSINSTALLOBJECTS

[精华] profile使用入门 :)