db2执行计划

db2执行计划
在实际应用过程中，有一些db2工具可以提高我们的工作效率，db2_explain 就是其中一个，其目的可以查看sql的cost，查看sql是否有优化的余地以下是db2执行计划的一个小实验，可以让我们看出sql是否走索引了，索引是不是最优：
1. 在数据库中创建一张测试表，并创建一个存储过程
$ db2 -td@ -f crttab.sql
crttab.sql内容：
create table db.test1 (id int GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1 ) primary key not null, name char(200), code char(200), aa varchar(1000), bb varchar(1000) )
@
CREATE PROCEDURE sp_insert1 (IN count int)
LANGUAGE SQL
SPECIFIC sp_insert1
BEGIN
DECLARE i INTEGER DEFAULT 0;
while i<count
do
insert into db.test1(name,code,aa,bb) values( 'nameaaaaa' || char(i), 'codebbbb' || char(i),'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'bbbbbbbbbbbbbbbbbbbbbbbb' );
set i=i+1;
if mod(i,10000)=0 then
commit;
end if;
end while;
END
@
2. 插入测试数据
以下调用存储过程，插入2万行数据。

$ db2 "call sp_insert1(10000) "
$ db2 "call sp_insert1(10000) "
3. 创建explain表，
$ cd <insthome>/sqllib/misc
$ db2 -tvf EXPLAIN.DDL
（Windows环境，找到DB2安装位置，下有misc目录）
4. 编写SQL查询语句
执行以下sql语句
$ db2 "select * from db.test1 where name='nameaaaaa100' "
然后查看它的执行计划：
$ db2 explain plan for "select * from db.test1 where name='nameaaaaa100' "
对执行计划格式化：
$ db2exfmt -1 -d sample -o exfmt1.out
5. 然后加索引
$ db2 "Create index i1 on db.test1(name) "
再次查看执行计划，并格式化，结果保存到exfmt2.out ：
$ db2 explain plan for "select * from db.test1 where name='nameaaaaa100' "
$ db2exfmt -1 -d sample -o exfmt2.out
6. 然后收集统计信息
$ db2 runstats on table db.test1 with distribution and detailed indexes all
再次查看执行计划，并格式化，结果保存到exfmt3.out ：
$ db2 explain plan for "select * from db.test1 where name='nameaaaaa100' "
$ db2exfmt -1 -d sample -o exfmt3.out
7. 打开exfmt1.out, exfmt2.out和exfmt3.out
观察各次执行计划的cost，I/O和路径的差异。

你会发现加了索引要比没加cost 更低，同时我们可以看出查表时索引的走向，定位我们加的索引是否正确。

8. 关键性能指标（KPI）
写好的sql语句
-只返回需要的行，避免写select * from t1
-加过滤条件限制返回的行数
-避免笛卡尔乘积，select * from a,b
-使用参数化查询，where col1=?，减少编译时间
-避免对查询条件计算，where salary*2>xx 改为salary >xx/2
-使用for read only 或for fetch only
-使用for update of
-避免数字类型转换
-避免数据类型不匹配
-如果可能，尽量避免使用order by 和distinct
-尽量使用exists 而不是用in
-函数的效率很高，充分利用
-考虑到管理上的开销，应避免在索引中使用多于5个的列
索引对于提高sql 读，同时牺牲了写，索引要慎重
找到未用或低效率索引db2pd -d sample -tcbstats index
或者
select
substr(a.tabschema,1,20) as tabschema,
substr(a.tabname,1,20) as tabname,
substr(a.indname,1,20) as indexname,
a.fullkeycard as idxfullcard,
b.card as tablecard,
int(100*float(a.fullkeycard)/float(b.card))
from syscat.indexes a,syscat.tables b
where a.tabschema = b.tabschema
and a.tabname = b.tabname
and a.fullkeycard > 1
and a.tabschema <>'SYSIBM'
and b.card > 100
and a.uniquerule <>'U'
and int(100*float(a.fullkeycard)/float(b.card)) < 10
and a.tabname in(select c.tabname from sysibmadm.snaptab c order by c.rows_written desc fetch first 10 rows only)
order by 1,2,3;。