使用db2top和db2advis生成索引建议(转)

字数 30670阅读 2052评论 0赞 0

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 
<CTRL+C> to cancel... [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.

运行环境数据库 windows Linux

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

使用db2top和db2advis生成索引建议(转)

添加新评论0 条评论

作者其他文章

相关文章

相关问题

相关资料