从db2 9.7以后，出现了一个命令db2caem，它可以用于在你的执行计划中在估计行数旁边显示实际返回的行数
如果估计行数和实际行数相差甚大，可能是
1. 统计信息过老
2. 由于表的数据分布不均匀，可能需要调整做runstats时候NUM_QUANTILES或NUM_FREQUVAULES的值。
3. 有时你可能需要一个优化概要文件来指导DB2做出正确的优化
db2caem使用非常简单
db2expln -d bludb -f workload.sql -t -z ";" -g > explain.out
db2caem -d
例如，用下面一个很简单的SQL，你想看看它的执行计划
$ cat query1.sql
select count(1) from db2inst1.EMP with ur;
$ mkdir query1.caem
$ db2caem -d SAMPLE -tbspname USERSPACE1 -sf query1.sql -o query1.caem
____________________________________________________________________
_____ D B 2 C A E M _____
DB2 Capture Activity Event Monitor data tool
I B M
The DB2CAEM Tool is a utility for capturing the activity event
monitor data with details, section and values, as well as actuals.
____________________________________________________________________
________________________________________________________________________________
DBT7041I "db2caem": The db2caem utility is connecting to the following database: "SAMPLE".
DBT7038I "db2caem": The db2caem utility successfully connected to the following database: "SAMPLE".
DBT7042I "db2caem": The SQL statement "select count(1) from db2inst1.EMP with ur" is being issued.
DBT7043I "db2caem": The db2caem utility is disconnecting from the following database: "SAMPLE".
DBT7039I "db2caem": The db2caem utility successfully disconnected from the following database: "SAMPLE".
DBT7000I db2caem completed. Output path: "/home/db2inst1/query1.caem/DB2CAEM_2016-10-31-16.44.23.572315".
$
-d SAMPLE -> 这个SQL要在SAMPLE上执行
-sf query1.sql -> 指定你要看的SQL
-o query1.caem -> 这个是自己命名的目录，需要提前建好，用来放输出文件
-tbspname USERSPACE1 -> 这个工具会临时创建一个事件监控器，这个表空间是用来指定事件监控表的放置位置。
$ pwd
/home/db2inst1/query1.caem/DB2CAEM_2016-10-31-16.44.23.572315
$ ls
EXPORTS db2caem.exfmt.1 db2caem.log db2caem_options.in
查看文件db2caem.exfmt.1
$ more db2caem.exfmt.1
DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 10.05.7
FORMATTED ON DB: SAMPLE
SOURCE_NAME: SYSSH200
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2016-10-31-16.44.26.701130
EXPLAIN_REQUESTER: DB2INST1
Database Context:
----------------
Parallelism: None
CPU Speed: 2.834065e-07
Comm Speed: 100
Buffer Pool size: 5000
Sort Heap size: 256
Database Heap size: 4502
Lock List size: 8119
Maximum Lock List: 98
Average Applications: 1
Locks Available: 254611
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Uncommitted Read
---------------- STATEMENT 1 SECTION 4 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select
count(1)
from
db2inst1.EMP
with ur
Optimized Statement:
-------------------
SELECT
Q3.$C0
FROM
(SELECT
COUNT(1)
FROM
(SELECT
$RID$
FROM
DB2INST1.EMPLOYEE AS Q1
) AS Q2
) AS Q3
Explain level: Explain from section
Access Plan:
-----------
Total Cost: 46653
Query Degree: 1
Rows
Rows Actual
RETURN
( 1)
Cost
I/O
|
1
1
GRPBY
( 2)
46653
NA
|
213874
213874
TBSCAN
( 3)
46637.3
NA
|
213874
NA
TABLE: DB2INST1
EMPLOYEE
Q1
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 46653
Cumulative First Row Cost: 46652.7
Estimated Bufferpool Buffers: 52736
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v10.5.0.7 : special_35315
EXECUTID: (Executable ID)
000000010000000000000000000000EF00000000000220161031164425021279
HEAPUSE : (Maximum Statement Heap Usage)
144 Pages
PLANID : (Access plan identifier)
0d156c580c82508d
SEMEVID : (Semantic environment identifier)
0000000000000001
STMTHEAP: (Statement heap size)
8192
STMTID : (Normalized statement identifier)
b7f27b75ff473614
Input Streams:
-------------
3) From Operator #2
Estimated number of rows: 1
Actual number of rows: 1
2) GRPBY : (Group By)
Cumulative Total Cost: 46653
Cumulative First Row Cost: 46652.7
Estimated Bufferpool Buffers: 52736
Arguments:
---------
AGGMODE : (Aggregation Mode)
COMPLETE
GROUPBYC: (Group By columns)
FALSE
GROUPBYN: (Number of Group By columns)
0
ONEFETCH: (One Fetch flag)
FALSE
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 213874
Actual number of rows: 213874
Output Streams:
--------------
3) To Operator #1
Estimated number of rows: 1
Actual number of rows: 1
3) TBSCAN: (Table Scan)
Cumulative Total Cost: 46637.3
Cumulative First Row Cost: 6.77983
Estimated Bufferpool Buffers: 52736
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT NONE
TBISOLVL: (Table access Isolation Level)
UNCOMMITTED READ
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
1) From Object DB2INST1.EMPLOYEE
Estimated number of rows: 213874
Column Names:
------------
+Q1.$RID$
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 213874
Actual number of rows: 213874
Objects Used in Access Plan:
---------------------------
Schema: DB2INST1
Name: EMPLOYEE
Type: Table
Last statistics update: 2016-10-31-15.58.56.340904
Number of rows: 213874
Number of buffer pool pages: 52736
Distinct row values: No
Tablespace name: USERSPACE1
Tablespace overhead: 6.725000
Tablespace transfer rate: 0.040000
Prefetch page count: 32
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
Runtime statistics for objects Used in Access Plan:
-----------------------------------------------------
Schema: DB2INST1
Name: EMPLOYEE
Type: Table
Member 0
---------
Metrics
--------------------
rows_reads:213874
object_data_l_reads:52736
object_data_p_reads:118
object_data_lbp_pages_found:52618
object_data_gbp_indep_pages_found_in_lbp:52618
object_data_caching_tier_gbp_indep_pages_found:52618
$