步骤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' --
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 ;
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
添加新评论0 条评论