IT运维管理体系和规范,it运维管理系统介绍
1001
2022-10-13
【DB2】绑定执行计划(真心不好用)
How do we use Optimizer Profile in DB2 to overwrite access plan?
Question & Answer
Question
We have a query want to use table scan instead of index scan regardless how many rows in the table. But DB2 optimizer may choose different access plan after executing Runstats to the table. We heard DB2 has a feature called Optimization Profile to manually specify the access plan for queries, is there any step by step instruction how to use it?
Answer
Here is a simple instruction for how to use Optimization Profile.The testing environment is on Windows, but the steps should be platform independent.DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" withlevel 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".1) Creating a databaseD:\TEMP>db2 create db sampel2DB20000I The CREATE DATABASE command completed successfully.D:\TEMP>db2 connect to sampel2 Database Connection InformationDatabase server = DB2/NT 9.1.0SQL authorization ID = DB2INST1Local database alias = SAMPEL22) Creating a profile tableD:\TEMP>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.3) Creating a user table for testD:\TEMP>db2 "create table mytable (name varchar(128), id integer, salary float,phone varchar(20))"DB20000I The SQL command completed successfully.D:\TEMP\>db2 "create index IX1 on mytable (id)"DB20000I The SQL command completed successfully.4) Inserting some data for testD:\TEMP>db2 "insert into mytable values ('Tom', 12345, 100, '123-456')"DB20000I The SQL command completed successfully.D:\TEMP>db2 "insert into mytable values ('Bob', 12346, 101, '123-457')"DB20000I The SQL command completed successfully.D:\TEMP>db2 "insert into mytable values ('Jeffery', 123, 102, '123-458')"DB20000I The SQL command completed successfully.D:\TEMP>db2 "insert into mytable values ('Jackie', 1255, 104, '123-459')"DB20000I The SQL command completed successfully.5) Executing runstatsD:\TEMP>db2 "runstats on table db2inst1.mytable"DB20000I The RUNSTATS command completed successfully.D:\TEMP>db2 "runstats on table db2inst1.mytable for indexes all"DB20000I The RUNSTATS command completed successfully.6) Run the query and see the resultD:\TEMP>db2 "SELECT * FROM DB2INST1.MYTABLE WHERE ID < 1000"NAME ID SALARY PHONE---------------------------------------------------------------------------Jeffery 123 +1.02000000000000E +002 123-458 1 record(s) selected.6) Creating explain tableD:\TEMP>cd D:\Program Files\IBM\SQLLIB\MISCD:\Program Files\IBM\SQLLIB\MISC>db2 -tvf EXPLAIN.DDLD:\Program Files\IBM\SQLLIB\MISC>cd D:\temp7) Generating the current access planD:\TEMP>db2 set current explain mode explainDB20000I The SQL command completed successfully.D:\TEMP>db2 "SELECT * FROM DB2INST1.MYTABLE WHERE ID < 1000"SQL0217W The statement was not executed as only Explain information requests are being processed. SQLSTATE=01604D:\TEMP>db2 set current explain mode noDB20000I The SQL command completed successfully.D:\TEMP>db2exfmt -d sampel2 -g TIC -w -1 -n % -s % -# 0 -o output.txtDB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006Licensed Material - Program Property of IBMIBM DATABASE 2 Explain Table Format ToolConnecting to the Database.Connect to Database Successful.Binding package - Bind was SuccessfulOutput is in output.txt.Executing Connect Reset -- Connect Reset was Successful.8) In output.txt file, we can see optimizer picked up IXSCANOriginal Statement:------------------SELECT * FROM DB2INST1.MYTABLE WHERE ID < 1000Optimized Statement:-------------------SELECT Q1.NAME AS "NAME", Q1.ID AS "ID", Q1.SALARY AS "SALARY", Q1.PHONE AS "PHONE"FROM DB2INST1.MYTABLE AS Q1WHERE (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: DB2INST1 ( 3) MYTABLE 0.00630865 0 | 4INDEX: DB2INST1 IX19) Next, we are going to create several files to enable Optimization Profile.9.1) Creating a file called a1.xml, and copy the following lines into the file. Note the statement in the XML file must be exactly same as the query going to be used by application, and the version parameter in
发表评论
暂时没有评论,来抢沙发吧~