100 Tech Tips, #14: New Explain feature in DB2 9.7: section explain & section actuals
Today's Technical Tip comes from a guest blogger, Angela Yang. Angela is an Advanced Technical Support Analyst for DB2. She has been working with DB2 for the past 8 years. Angela works out of IBM's Toronto Lab. Look for more blogs from Angela on other hot DB2 topics!
Section explain and section actuals are new Explain features that are useful for query performance problem determination. This blog introduced these two features and the usage of them through exercises.
Section explain is a new feature added in DB2 9.7. Using the regular explain mode, users need to re-run the query in explain mode. With section explain, users do not need to re-run the query, they can directly obtain the explain information from the section as long as the section is still available either in the memory or catalogs. This exercise is designed to help you become familiar with various explain stored procedures that allow explain the plan from section.
when you compare section explain and regular explain in this exercise, please keep in mind that not all regular explain information can be retrieved from section. Some non-critical information is not saved to avoid expanding the section too much.
Exercise 1: explain_from_section
The difference between section explain and regular explain is documented here.
- Issue an query from CLP, for example
db2 “select * from employee where hiredate > '10/10/2004'”
- Find the section executable_id for the statement
db2 select executable_id, substr(stmt_text,1,64) from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as t where stmt_text like'select * from employee%'
- Explain the plan from the section of the execution_id found in step 2)
db2 call explain_from_section(,'M','',-1,'',?,?,?,?,?)
- Verify the explain is from section.
db2exfmt -d sample -1 -o exfmt1
exfmt1 contains an entry “Explain level” right before the Access Plan section. This entry will only appear when the explain is from section.
Explain level: Explain from section
Exercise 2: explain_from_catalog
explain_from_catalog can be used to explain static statement which will have section stored in the catalog. We will use a simple embedded SQL application in this exercises.
- Create a file test.sqc contains the following lines, create table t1, t2
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL end DECLARE SECTION;
EXEC SQL select count(t1.c1) into :count from t1, t2 where t1.c2 = t2.c1 and mod(t1.c1,99999) = 10000;
- Create a static statement using embedded SQL
db2 prep test.sqc bindfile
This command will create a bind file test.bnd under the same directory.
- Use bind command to get a explain
db2 bind test.bnd explain all
db2exfmt -d sample -1 -o exfmt2
- Query the package name and schema for the static statement issued in test.sqc
db2 "select PKGSCHEMA, PKGNAME, STMTNO, SECTNO, SEQNO, substr(TEXT,1,200) from syscat.statements where text like 'select count(%'"
- Call explain_from_catalog, assuming DB2ADMIN is the current use id.
db2 "call explain_from_catalog('DB2ADMIN','TEST','',1,'DB2ADMIN',?,?,?,?,?)
db2exfmt -d sample -1 -o exfmt3
Compare the regular explain output exfmt2 and the section explain output exfmt3. Verify exfmt3 contains this entry “Explain level: Explain from section”.
This exercise is designed to help you get familiar with usingexplain_from_catalog. The practical scenario is that users may already have identified the problematic static query and would like to use explain_from_catalog to explain the static query.
Exercise 3: explain_from_data
The section data could be captured and stored in a monitor table. Please refer to the Info Center for this feature.
- Load the section into a table assuming sectdata is a captured section
db2 create table tab_v97section(c1 blob(2m))
db2 import from sectdata of del lobs from . insert into tab_v97section
- Invoke explain_from_data
db2 call explain_from_data( (select c1 from tab_v97section), '', null,'DB2ADMIN', ?,?,?,?,? )
- Verify the plan
db2exfmt -d sample -1 -o exfmt4
Verify exfmt4 contains this entry “Explain level: Explain from section”.
Note: exfmt4 doesn't report the original statement since the original statement is not stored in the section. The optimized statement is stored in the section.
Section actuals is a new feature added in V97FP1. Explain from section actuals will report actual rows returned from each operator in the plan. The optimizer estimates number of rows based on statistics. Users can compare the estimate rows and the actual rows, to see if any operator has estimated rows far off from the actual rows. If any case like that happens, it usually means the statistics need to be updated.
There are 2 ways to enable actuals collection. One way is to use a stored procedurewlm_set_conn_env. Another way is to use db cfg parm section_actuals which also requires certain work load management object. To create the required work load management object, certain licence is required.
In this exercise, we will use wlm_set_conn_env.
- script to collect actuals
create event monitor em1 for activities write to table activity, activityvals,activitystmt, control;
call wlm_set_conn_env(null, 'with details, section and valuesbase');
set event monitor em1 state 1;
select * from t1, t2 where t1.c2 = t2.c1 and mod(t1.c1,99999)=0;
set event monitor em1 state 0;
select appl_id, uow_id, activity_id, stmt_text from activitystmt_em1 wherestmt_text like 'select%';
- Invoke explain_from_activity
db2 call explain_from_activity( , , ,'EM1', 'DB2ADMIN', ?, ?, ?, ?, ? );
The following is an example. Use appl_id, uow_id, activity_id reported by the last queryin the script in step 1).
db2 "call explain_from_activity('*LOCAL.db2admin.101010232215', 144, 1,'EM1','DB2ADMIN',?, ?, ?, ?, ? )"
- Get explain output using db2exfmt
db2exfmt -d sample -1 -o exfmt5
- Compare the estimated and actual rows
Open file exfmt5, you will see the plan graph contains the actual rows for each operator. For example,
Note: explain_from_activity also get explain information from the section, exfmt5 contains the following entry above the plan graph.
Explain level: Explain from section
- In multiple database partition environment, the per db partition actual rows will be reported in plan details section like below. This details will not show in a serial mode. The per db partition actual rows are helpful for identifying data skew which means that data are not evenly distributed across database partitions. Data skew may affect the accuracy of the statistics, bad statistics could result in a sub-optimal access plan.