使用DB2look 重新创建优化器访问计划(3)

　2007-07-31 16:20:53　来源：WEB开发网　闂傚倸鍊搁崐椋庢濮橆兗缂氱憸宥堢亱闂佸湱铏庨崰鏍不椤栫偞鐓ラ柣鏇炲€圭€氾拷

闂傚倸鍊搁崐椋庣矆娓氣偓楠炲鏁撻悩鎻掔€梺姹囧灩閻忔艾鐣烽弻銉︾厵闁规鍠栭。濂告煕鎼达紕校闁靛洤瀚伴獮鎺楀箣濠靛啫浜鹃柣銏⑶圭壕濠氭煙閻愵剚鐏辨俊鎻掔墛缁绘盯宕卞Δ鍐冣剝绻涘畝濠佺敖缂佽鲸鎹囧畷鎺戭潩閹典焦鐎搁梻浣烘嚀閸ゆ牠骞忛敓锟�

婵犵數濮烽弫鍛婃叏椤撱垹绠柛鎰靛枛瀹告繃銇勯幘瀵哥畼闁硅娲熷缁樼瑹閳ь剙岣胯鐓ら柕鍫濇偪濞差亜惟闁宠桨鑳堕崝锕€顪冮妶鍡楃瑐闁煎啿鐖奸崺濠囧即閵忥紕鍘梺鎼炲劗閺呮稒绂掕缁辨帗娼忛埡浣锋闂佽桨鐒﹂幑鍥极閹剧粯鏅搁柨鐕傛嫹闂傚倸鍊搁崐椋庢濮橆兗缂氱憸宥堢亱闂佸湱铏庨崰鏍不椤栫偞鐓ラ柣鏇炲€圭€氾拷　　闂傚倸鍊搁崐鐑芥嚄閼哥數浠氱紓鍌欒兌缁垶銆冮崨鏉戠厺鐎广儱顦崡鎶芥煏韫囨洖校闁诲寒鍓熷铏圭磼濡搫顫岄梺鍦拡閸嬪棝鎯€椤忓浂妯勯梺鍝勬湰濞叉ḿ鎹㈠┑濠勭杸闁哄洨濮烽悰銉╂⒒娴ｅ搫甯跺鐟帮攻缁傚秴饪伴崼姘ｅ亾閺冨牆绀冩い蹇庣娴滈箖鏌ㄥ┑鍡涱€楀褜鍠栭湁闁绘ɑ鐟ョ€氼喚绮绘ィ鍐╃厱妞ゆ劑鍊曢弸搴ㄦ煟韫囧鍔滈柕鍥у瀵潙螣閸濆嫬袝婵＄偑鍊戦崹娲偡閳哄懎绠栭柍鈺佸暞閸庣喖鏌曢崶褍绨婚柟鍑ゆ嫹

核心提示： db2 get db cfg for sample > dbcfg_sample.outDatabase heap (4KB) (DBHEAP) = 600SQL statement heap (4KB) (STMTHEAP) = 2048Number of frequent val

db2 get db cfg for sample > dbcfg_sample.out
Database heap (4KB) (DBHEAP) = 600
SQL statement heap (4KB) (STMTHEAP) = 2048
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

确保在修改数据库管理器配置（dbm cfg）之后停止并启动该实例。

对于 sample 数据库，按下列方式对 ORG 和 SALES 表运行 runstats：

db2 connect to sample
db2 runstats on table <schema>.org with distribution
and indexes all
db2 runstats on table <schema>.sales with distribution
and indexes all
db2 terminate

现在，通过执行 EXPLAIN.DDL 文件生成 EXPLAIN 表，

该文件在 sqllibmisc 目录下：

db2 connect to sample
db2 -tvf <intall path>EXPLAIN.DDL
db2 terminate

在名为 query.sql 的文件中保存下列命令：

connect to sample
set current explain mode explain
select * from org a, staff b where
a.deptnumb=b.dept and b.dept=15
set current explain mode no
terminate

现在，按下列方式执行该文件：

db2 -tvf query.sql

上面将仅仅以解释模式编译查询。您将在屏幕上看到：

C:>db2 -tvf query.sql
connect to sample
Database Connection Information
Database server = DB2/NT 8.2.1
SQL authorization ID = SKAPOOR
Local database alias = SAMPLE
set current explain mode explain
DB20000I The SQL command completed
successfully.
select * from org a, staff b where
a.deptnumb=b.dept and b.dept=15
SQL0217W The statement was not executed
as only Explain information requests
are being processed. SQLSTATE=01604
set current explain mode no
DB20000I The SQL command completed successfully.
C:>db2 terminate
DB20000I The TERMINATE command completed successfully.