使用db2top和db2advis生成索引建议
unix/linux环境下都有db2top这个工具，windows下没有
运行环境
AIX 7.1 + DB2 9.7.0.6
1.创建$HOME目录下的.db2toprc文件，增加内容如下，否则db2top运行时会报警
$ cat ~/.db2toprc
streamsize=2000M
2.运行db2top采集数据，间隔15秒，持续时间20分钟，生成跟踪文件
db2top -d mydb -C -i 15 -m 20
3.使用db2top对跟踪文件进行分析
db2top -d mydb -f db2snap-mydb-AIX64.bin -b l -A -L
4.在myuser下创建explain table
cd /home/MYUSER/sqllib/misc
db2 connect to mydb user myuser using mypass
db2 -tf EXPLAIN.DDL
5.使用db2advis对SQL文件进行分析，生成索引建议
db2advis -d mydb -i ALL.sql -noxml -user myuser/mypass -schema myuser
步骤2-5的运行记录：
步骤2
$ db2top -d mydb -C -i 15 -m 20
[17:16:33] Starting DB2 snapshot data collector, collection every 15 second(s), max duration 20 minute(s), max file growth/hour 100.0M, hit
[17:16:33] Writing to 'db2snap-mydb-AIX64.bin', should I create a named pipe instead of a file [N/y]? n
[17:16:38] Creating 'db2snap-mydb-AIX64.bin' as a normal file
[17:16:53] 1.7M written, time 20.283, 315.4M/hour
[17:18:09] 3.5M written, time 95.585, 133.4M/hour
[17:19:09] 5.2M written, time 155.821, 122.7M/hour
[17:20:09] 11.6M written, time 216.337, 193.9M/hour
[17:23:55] 13.4M written, time 442.204, 109.7M/hour
[17:25:10] 15.3M written, time 517.495, 106.6M/hour
[17:26:11] 17.1M written, time 577.729, 106.9M/hour
[17:28:26] 20.8M written, time 713.314, 105.2M/hour
[17:30:42] 24.5M written, time 848.855, 104.1M/hour
[17:32:57] 28.2M written, time 984.388, 103.2M/hour
[17:34:58] 31.8M written, time 1104.858, 103.9M/hour
[17:36:43] Max duration reached, 33.7M bytes, time was 1210.271...
[17:36:43] Snapshot data collection stored in 'db2snap-mydb-AIX64.bin'
Exiting...
步骤3
$ db2top -d mydb -f db2snap-mydb-AIX64.bin -b l -A -L
Analyzing objects doing the most 'Cpu%_Total' in function 'Sessions'
*** End of input stream reached, size was 35381589...
--
-- Top twenty performance report for 'Sessions' between 17:18:54 and 17:35:58
-- Sort criteria 'Cpu%_Total'
--
Rank Application_Handle(Stat) Percentage fromTime toTime sum(Cpu%_Total)
----- ------------------------------ ----------- -------- --------- ------------------------------
1 2998 26.8995% 17:18:54 17:35:58 107114
2 2010 25.1130% 17:18:54 17:35:58 100000
3 64360 24.8757% 17:18:54 17:35:58 99055
4 3420 23.1118% 17:24:55 17:35:58 92031
5 64784 0.0000% 17:18:54 17:35:58 0
6 64629 0.0000% 17:18:54 17:35:58 0
7 60282
0.0000% 17:18:54 17:35:58 0
8 60281 0.0000% 17:18:54 17:35:58 0
9 60225 0.0000% 17:18:54 17:35:58 0
10 60230 0.0000% 17:18:54 17:35:58 0
11 3399 0.0000% 17:33:43 17:35:58 0
12 2687 0.0000% 17:18:54 17:35:58 0
13 2895 0.0000% 17:18:54 17:35:58 0
14 3449 0.0000% 17:30:27 17:35:58 0
15 1296 0.0000% 17:18:54 17:35:58 0
16 2847 0.0000% 17:18:54 17:35:58 0
17 3058 0.0000% 17:18:54 17:35:58 0
18 2864 0.0000% 17:18:54 17:35:58 0
19 1916 0.0000% 17:18:54 17:35:58 0
20 3392 0.0000% 17:19:54 17:35:58 0
--
-- Performance report, breakdown by 300 seconds
--
fromTime sum(Cpu%_Total) Percentage Top Five in 300 seconds interval
-------- ------------------------------ ---------- +----------------------------------------------+
17:18:54 99100 24.8870% |Rank|Percentage|Application_Handle(Stat) |
- - | 1| 99.9546%|64360 |
- - | 2| 0.0454%|2998 |
- - | 3| 0.0000%|64784 |
- - | 4| 0.0000%|64629 |
- - | 5| 0.0000%|60282 |
17:24:55 100000 25.1130% +----+----------+------------------------------+
- - | 1| 100.0000%|2010 |
- - | 2| 0.0000%|64784 |
- - | 3| 0.0000%|64629 |
- - | 4| 0.0000%|60282 |
- - | 5| 0.0000%|60281
|
17:30:27 199100 50.0000% +----+----------+------------------------------+
- - | 1| 53.7765%|2998 |
- - | 2| 46.2235%|3420 |
- - | 3| 0.0000%|64784 |
- - | 4| 0.0000%|64629 |
- - | 5| 0.0000%|60282 |
17:35:58 0 0.0000% +----+----------+------------------------------+
- - | 1| 0.0000%|64784 |
- - | 2| 0.0000%|64629 |
- - | 3| 0.0000%|60282 |
- - | 4| 0.0000%|60281 |
- - | 5| 0.0000%|60225 |
+----------------------------------------------+
--
-- Performance report, breakdown by 0.5 hour
--
fromTime sum(Cpu%_Total) Percentage Top Five in 0.5 hour interval
-------- ------------------------------ ---------- +----------------------------------------------+
17:18:54 398200 100.0000% |Rank|Percentage|Application_Handle(Stat) |
- - | 1| 26.8995%|2998 |
- - | 2| 25.1130%|2010 |
- - | 3| 24.8757%|64360 |
- - | 4| 23.1118%|3420 |
- - | 5| 0.0000%|64784 |
+----------------------------------------------+
Exiting...
步骤4
$ db2 connect to mydb user myuser using mypass
Database Connection Information
Database server = DB2/AIX64 9.7.6
SQL authorization ID = MYUSER
Local database alias = MYDB
$ db2 -tf EXPLAIN.DDL
******* IMPORTANT **********
USAGE: db2 -tf EXPLAIN.DDL
******* IMPORTANT **********
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SQL
command completed successfully.
...
步骤5
$ cat ALL.sql
select * from ( select rownumber() over(order by job0_.DUEDATE_ asc) as rownumber_,
job0_.ID_ as ID1_111_, job0_.VERSION_ as VERSION3_111_, job0_.DUEDATE_ as DUEDATE4_111_,
job0_.PROCESSINSTANCE_ as PROCESSI5_111_, job0_.TOKEN_ as TOKEN6_111_,
job0_.TASKINSTANCE_ as TASKINST7_111_, job0_.ISSUSPENDED_ as ISSUSPEN8_111_,
job0_.ISEXCLUSIVE_ as ISEXCLUS9_111_, job0_.LOCKOWNER_ as LOCKOWNER10_111_,
job0_.LOCKTIME_ as LOCKTIME11_111_, job0_.EXCEPTION_ as EXCEPTION12_111_,
job0_.RETRIES_ as RETRIES13_111_, job0_.NAME_ as NAME14_111_, job0_.REPEAT_ as REPEAT15_111_,
job0_.TRANSITIONNAME_ as TRANSIT16_111_, job0_.ACTION_ as ACTION17_111_,
job0_.GRAPHELEMENTTYPE_ as GRAPHEL18_111_, job0_.GRAPHELEMENT_ as GRAPHEL19_111_, job0_.NODE_ as NODE20_111_,
job0_.SMS_ as SMS21_111_, job0_.CLASS_ as CLASS2_111_ from MYUSER.JBPM_JOB job0_
where (job0_.LOCKOWNER_ is null or job0_.LOCKOWNER_='x') and job0_.RETRIES_>0 and job0_.ISSUSPENDED_<>1
order by job0_.DUEDATE_ asc ) as temp_ where rownumber_ <= 9
;
$ db2advis -d mydb -i ALL.sql -noxml -user db2inst1/db2inst1 -schema shoaex
execution started at timestamp 2013-05-14-17.16.37.209276
found [1] SQL statements from the input file
found [1] SQL statements from the input file
Recommending indexes...
total disk space needed for initial set [ 0.009] MB
total disk space constrained to [ 7.947] MB
Trying variations of the solution set.
1 indexes in current solution
[ 0.0117] timerons (without recommendations)
[ 0.0105] timerons (with current solution)
[10.39%] improvement
--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 0.009MB
CREATE INDEX "MYUSER "."IDX1305140908520" ON "MYUSER "."JBPM_JOB"
("DUEDATE_" ASC, "RETRIES_" ASC, "CLASS_" ASC, "SMS_"
ASC, "NODE_" ASC, "GRAPHELEMENT_" ASC, "GRAPHELEMENTTYPE_"
ASC, "ACTION_" ASC, "TRANSITIONNAME_" ASC, "REPEAT_"
ASC, "NAME_" ASC, "EXCEPTION_" ASC, "LOCKTIME_" ASC,
"ISEXCLUSIVE_" ASC, "TASKINSTANCE_" ASC, "TOKEN_"
ASC, "PROCESSINSTANCE_" ASC, "VERSION_" ASC, "ID_"
ASC, "ISSUSPENDED_" ASC, "LOCKOWNER_" ASC) ALLOW REVERSE
SCANS COLLECT SAMPLED DETAILED STATISTICS;
COMMIT WORK ;
--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================
-- RUNSTATS ON TABLE "MYUSER "."JBPM_JOB" FOR SAMPLED DETAILED INDEX "MYUSER "."IDX_JOB_TSKINST" ;
-- COMMIT WORK ;
--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "MYUSER "."IDX_JOB_PRINST";
-- DROP INDEX "MYUSER "."IDX_JOB_TOKEN";
-- ===========================
--
14 solutions were evaluated by the advisor
DB2 Workload Performance Advisor tool is finished.