西瓜狼的profile使用入門

很多開發和資料庫管理人員都在為最佳化器問題煩惱不已。儘管很多時候最佳化器問題都是可以透過常規手段解決的，但是在某些特殊情況下，或者緊急情況（沒有時間完整地分析問題）下，使用者可以使用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.

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.

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

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

a1.xml
a1.xml:












insert.del
insert .del:
"TAOEWANG", "PROF1", "a1.xml"

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

D:\TEMP>db2set DB2_OPTPROFILE=YES

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.

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.

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了事~~~