Using a Statistical View to Improve Query Performance

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".

You may also like...

5 Responses

  1. raf mathijs says:

    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.

    • Ember Crooks says:

      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.

  2. Nitesh says:

    Thanks for explaining the things with detail example.

  3. Suvradeep Sensarma says:

    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

    • Ember Crooks says:

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *