Many times, analysts from db2 support team ask the clients to gather the output of some count queries to verify cardinality estimates in an access plan. As well, one may have exhausted all the tuning needed for a query and is at a point further investigation into cardinality estimates is required. It is tedious work to create the count queries manually at different sections of the plan and could be prone to errors.
This technical note will show a step by step example on how to use the new functionality "section actuals" which is available as of V97 Fixpack 1 in order to verify Real Cardinality Estimates in an access plan.
Poor performing query.
Under or over-estimated cardinality estimates in an access plan that you find is potentially causing a poor performing query.
Diagnosing the problem
Here are the sequence of steps in order to get read time cardinality estimates using section acuals:
1. Enable Section Actuals
update db cfg for <DBNAME> using SECTION_ACTUALS BASE;
2. Create Workload for monitoring Activity.
connect to <DBNAME>;
create workload MYWORKLOAD current client_acctng('MYWORKLOAD') service class sysdefaultuserclass collect activity data on all database partitions with details, section;
-- The key here is to add the keyword "section" in bold above. You can alter your previous activity workload and add the section keyword as well. As well, we are monitoring activity for specific client accounting. You can use applname for monitor a specific application where the query is running for, etc.
3. (optional): Grant usage on this workload to public.
grant usage on workload MYWORKLOAD to public
4. Create Event monitor for activities
create event monitor MYMON for activities write to table;
-- This would create a table by the name ACTIVITYSTMT_MYMON recording activity details.
5. Collect Section Actuals for Statement of interest
delete from ACTIVITYSTMT_MYMON; -- Again, option
call wlm_set_client_info(null, null, null, 'MYWORKLOAD', null);
set event monitor MYMON state 1;
-- Your statement goes here. Below is an example of a select statement that we are interested in finding the real cardinalities versus the estimated.
select * from staff where dept=20;
set event monitor MYMON state 0;
call wlm_set_client_info(null, null, null, null, null);
6. Locate the Application, UOW and Activity ID for the data:
select appl_id, uow_id, activity_id, substr(stmt_text,1,80) as stmt from ACTIVITYSTMT_MYMON
APPL_ID UOW_ID ACTIVITY_ID STMT
-------------------------- ----------- ---------------- ------------------------------
*LOCAL.DB2INST1.100505151716 11 1 select * from staff where dept=20
7. Explain the data into Explain Tables using EXPLAIN_FROM_ACTIVITY stored procedure:
call explain_from_activity ('*LOCAL.DB2INST1.100505151716',11,1,'MYMON','DB2INST1',?,?,?,?,?)
Value of output parameters
Parameter Name : EXPLAIN_SCHEMA
Parameter Value : DB2INST1
Parameter Name : EXPLAIN_REQUESTER
Parameter Value : DB2INST1
Parameter Name : EXPLAIN_TIME
Parameter Value : 2010-05-05-22.214.171.1248725
Parameter Name : SOURCE_NAME
Parameter Value : SQLC2H20
Parameter Name : SOURCE_SCHEMA
Parameter Value : NULLID
Parameter Name : SOURCE_VERSION
Parameter Value :
Return Status = 0
8. Run db2exfmt to generate access plan:
db2exfmt –d sample -1 –o exfmt_activity.out
9. Examine the output of the output file, here we called it exfmt_activity.out
Explain level: Explain from section
Total Cost: 23.2614
Query Degree: 1
6.28 <<-- Estimated Number of Rows
4 <<-- Actual Rows
NA <<-- BASE TABLE CARDINALITY IS NOT COLLECTED
Resolving the problem
The above is just an example of how to verify real time cardinality estimates. For resolving the issue, the problem could be contributed due to many reasons, for example stale statistics (distribution statistics are missing or the statistics are not current), correlation where using column group stats, statistical views may help, etc.
How to collect optimizer data and steps to generate db2
Obtaining a section explain with actuals to investigate
Comparing real-time cardinality to the optimizer cardin
Influence query optimization with optimization profiles
Understand column group statistics in DB2