How to use Section Actuals to Verify Real Cardinality Estimates

Technote (troubleshooting)


Problem(Abstract)


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.

Symptom

Poor performing query.

Cause

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-11.20.14.668725

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
Access Plan:
-----------
Total Cost: 23.2614
Query Degree: 1

Rows
Rows Actual
RETURN
( 1)
Cost
I/O
|
6.28 <<-- Estimated Number of Rows
4 <<-- Actual Rows
TBSCAN
( 2)
23.2614
NA
|
157
NA <<-- BASE TABLE CARDINALITY IS NOT COLLECTED
TABLE: DB2INST1
STAFF

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.


Related information

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

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows
Performance - Optimizer

Software version:

9.7

Operating system(s):

Platform Independent

Software edition:

Enterprise Server

Reference #:

1431680

Modified date:

2011-03-24

Translate my page

Machine Translation

Content navigation