Posted by Sometimes just using the index advisor and a bit of common sense, you can address SQL performance issues. But other times, you have to dig a bit deeper.
I recently found the following query and 300 of its closest cousins (differing only in the length of the second in-list) were eating 30% of the CPU time used by queries on a rather busy database.
SELECT DISTINCT
ATTRDICTSRCHCONF.SRCHFIELDNAME
,ATTRVALDESC.VALUE
,ATTR.STOREENT_ID
,ATTRVALDESC.SEQUENCE
,ATTRVALDESC.IMAGE1
,ATTRVALDESC.IMAGE2
FROM
ATTRDICTSRCHCONF
, ATTR
, ATTRVALDESC
WHERE
ATTRDICTSRCHCONF.ATTR_ID=ATTR.ATTR_ID
AND ATTR.ATTR_ID=ATTRVALDESC.ATTR_ID
AND ATTRDICTSRCHCONF.ATTR_ID IS NOT NULL
AND ATTR.FACETABLE=1
AND ATTR.STOREENT_ID IN (?, ?)
AND ATTRVALDESC.LANGUAGE_ID=?
AND ATTRDICTSRCHCONF.SRCHFIELDNAME IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
I am told that the query is a base query for WebSphere Commerce, so we are unable to actually change the query itself. I’ve already created custom indexes to optimize it from an index perspective. What I also discovered was that it was getting 2 sort overflows for every execution. As Scott Hayes always says, sort is a 4-letter word. But the sorts it is doing is first of the two values in the shorter in-list, and second of the results set at the end to apply the DISTINCT. I realize that both in-lists and distincts can be a necessary thing, but from a performance perspective I hate them both. It appeared to me that the sort heap should be more than large enough to handle the data it expects to be sorting, which indicated to me that it might be getting more rows than it expects.
Here’s the explain plan of the query before changing anything:
Total Cost: 334.805
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
3329.03
TBSCAN
( 2)
334.805
43.0587
|
3329.03
SORT
( 3)
334.565
43.0587
|
3329.03
NLJOIN
( 4)
329.122
43.0587
/-----------+------------\
6.19457 537.41
HSJOIN IXSCAN
( 5) ( 12)
58.4248 43.7122
7.64554 5.7168
/---------+----------\ |
439 23.5083 825462
NLJOIN IXSCAN INDEX: WSCOMUSR
( 6) ( 11) IDX_ATTRVALDESC_NU01
24.1519 34.2427 Q7
3.16206 4.48347
/----+----\ |
2 219.5 484
TBSCAN IXSCAN INDEX: WSCOMUSR
( 7) ( 10) IDX_ATTRDICTSRCHCONF_NU01
0.00205565 12.08 Q9
0 1.58103
| |
2 1666
SORT INDEX: WSCOMUSR
( 8) IDX_ATTTR_NU05
0.00147033 Q8
0
|
2
TBSCAN
( 9)
4.31014e-05
0
|
2
TABFNC: SYSIBM
GENROW
Q1
Looking at the query and the list of similar queries, I came up with the folowing statistical view to help db2 estimate the results better:
create view stats_search_view_01 as (
select
ATTRDICTSRCHCONF.SRCHFIELDNAME
,ATTRVALDESC.VALUE
,ATTR.STOREENT_ID
,ATTRVALDESC.SEQUENCE
,ATTRVALDESC.IMAGE1
,ATTRVALDESC.IMAGE2
FROM
ATTRDICTSRCHCONF
, ATTR
, ATTRVALDESC
WHERE
ATTRDICTSRCHCONF.ATTR_ID=ATTR.ATTR_ID
AND ATTR.ATTR_ID=ATTRVALDESC.ATTR_ID
AND ATTRDICTSRCHCONF.ATTR_ID IS NOT NULL
AND ATTR.FACETABLE=1
)
And followed it up with the following:
db2 alter view stats_search_view_01 enable query optimization db2 runstats on table wscomusr.stats_search_view_01 with distribution db2rbind sample -l rbind.out all db2 flush package cache dynamic
And here’s what the explain plan looks like after adding the statistical view:
Total Cost: 470.019
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
12141.6
TBSCAN
( 2)
470.019
104.007
|
12141.6
SORT
( 3)
469.146
104.007
|
12141.6
NLJOIN
( 4)
446.43
104.007
/-----------+------------\
6.19457 1960.03
HSJOIN IXSCAN
( 5) ( 12)
58.4248 62.6581
7.64554 15.5558
/---------+----------\ |
439 23.5083 825462
NLJOIN IXSCAN INDEX: WSCOMUSR
( 6) ( 11) IDX_ATTRVALDESC_NU01
24.1519 34.2427 Q7
3.16206 4.48347
/----+----\ |
2 219.5 484
TBSCAN IXSCAN INDEX: WSCOMUSR
( 7) ( 10) IDX_ATTRDICTSRCHCONF_NU01
0.00205565 12.08 Q9
0 1.58103
| |
2 1666
SORT INDEX: WSCOMUSR
( 8) IDX_ATTTR_NU05
0.00147033 Q8
0
|
2
TBSCAN
( 9)
4.31014e-05
0
|
2
TABFNC: SYSIBM
GENROW
Q1
Hang on, the timerons went UP! But that’s actually what I want – note too that for exactly the same query with everything else being exactly the same, it now expects 12141.6 rows instead of the 3329.03 in the original explain plan. I’m going to be watching to see if this query is still associated with sort overflows. I may also have to tweak the sortheap up a bit.
I know that the most accurate estimates could come from eliminating the parameter markers, but for an e-commerce database, there’s only so far to go with that – plus it’s written by IBM through WebSphere Commerce, so I have very little control over the actual SQL. The result sets probably vary drastically in size depending on the values provided for the parameter markers, so I may not be able to eliminate all sort overflows, but I should be able to get a bit closer.
Note that when a statistical view is used, you’ll see notes like this in the explain plan:
Diagnostic Identifier: 1
Diagnostic Details: EXP0147W The following statistical view was used
by the optimizer to estimate cardinalities:
"WSCOMUSR"."STATS_SEARCH_VIEW_01".
Diagnostic Identifier: 2
Diagnostic Details: EXP0148W The following MQT or statistical view was
considered in query matching: "WSCOMUSR".
"STATS_SEARCH_VIEW_01".
In DB2 9.1 we had created statistical views to help the database optimize queries on our union all views, however when we tried to drop the tables contained in the views, we ran into issues since dropping the statistical views left still a packed descriptor.
we had to use db2cat to identify the packed descriptor, recreate the statistical view,
alter … disable query optimization, and then drop it again.
https://www-304.ibm.com/support/docview.wss?uid=swg1IZ39997
https://www-304.ibm.com/support/docview.wss?uid=swg1IZ40001
I think the feature could help us, since we are in 9.7 now, however I’m still reluctant to risk it again.
9.1 was the first release where it was fully implemented. I’d give it a try again if I were you – often there are issues like that when a feature is first implemented, and then they go away in later releases. I have not, however, tried dropping one lately, so I couldn’t say for sure.
Thanks for explaining the things with detail example.
Hi Ember,
I have some doubt related to monitoring elements DIRECT_READ_TIME and POOL_READ_TIME fetched from the table MON_GET_PKG_CACHE_STMT. Which one indicates the time for which DB2 waits for data to be fetched from disk to Bufferpool?
Suvradeep Sensarma
It depends on which part of db2 you’re talking about “waiting”. If you’re talking about agents directly performing work for applications, then DIRECT_READ_TIME is the time spent reading data from disk, by the agents, into the bufferpool. This is usually the one you might be more worried about. I think that POOL_READ_TIME would be the time spent prefetching data into the buffer pools, which is usually a bit less of a concern. However, I am pretty sure that POOL_READ_TIME includes DIRECT_READ_TIME since all pages read go through the buffer pool unless they are LOBS. Usually the I/O details are calculated in terms of time per page. See http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.mon.doc/doc/r0001241.html?cp=SSEPGG_10.5.0 for more details.