Theoretical framework

The 3003 record from the Database Monitor table shows that the database optimizer has decided to place selected rows into a temporary space and sort them. The presence of a 3003 record does not necessarily indicate poor performance. The optimizer selected a query sort because it is either cheaper than the alternative indexed methods or it is forced to do so, for example when UNION is used or ORDER BY uses columns from more than one table.

Indexes can still be used to select or join rows before the sort occurs. The 3006 record does not indicate that the ODP is nonreusable.

Sort buffers are refilled and sorted at open time, even in reusable ODP mode.

Sorting might increase the open (OP) time/cost since sorting is often performed at open (OP) time. This means that it might take some time to return the first row in the result set to the end user.

High elapsed times for a query sort might indicate a large answer set. In this case, the sort outperforms index usage (the situation in most cases). You should not attempt to build indexes for queries with large result sets, unless you are going to add selection criteria to the SQL statement’s WHERE clause to further reduce the result set.

If the answer set is small, but the optimizer does not have the right indexes available to know that, creating indexes over selection columns can help by giving the optimizer statistics and

an alternative method of accessing the data. This is possible only if the optimizer is not forced to use the sort (that is via a UNION or ORDER BY on columns from more than one table).

Look at which queries involve the use of a query sort. You can do a query sort by using the query shown in Example 6-28.

Example 6-28 Use of a query sort WITH sorts AS (

SELECT qqjfld, qqucnt FROM mydbmon

WHERE qqrid=3003 )

SELECT SUM(qqi6) "Total Time" , COUNT(*) "Nbr Times Run", a.qqucnt, qq1000 FROM mydbmon a, sorts b

WHERE qqrid=1000 AND a.qqjfld=b.qqjfld and a.qqucnt=b.qqucnt AND qqc21 IN ('OP','SI','UP','IN','DL')

GROUP BY a.qqucnt, qq1000 ORDER BY "Total Time" DESC;

Figure 6-24 shows the output of the query in Example 6-28.

Figure 6-24 Use of a query sort

This query uses the following columns:

򐂰 QQRID

Our common table expression sort contains only row type 3003, which has information specific to SQL statements using query sorts.

򐂰 QQJFLD and QQUCNT

The QQJFLD and QQUCNT are join fields required to uniquely identify the SQL statement in the Database Monitor file.

򐂰 QQC21

Since we are joining common table expression sorts back to the 1000 record in the Database Monitor file, we must ensure that we only join to 1000 records that can cause

query sorts to occur. This is accomplished by verifying that QQC21 field operation is either open, select, update, delete, or insert. We need to include last three operations because they might have subselects or correlated subqueries.

򐂰 QQI6

This column indicates a table scan operation cumulative elapse time, in microseconds for each individual query. Since we use it as a cost indicator, we have ordered the output in descending order based on this value.

The query that we have outlined so far is insufficient in helping us to decide if building an index or modifying the SQL statement is desired. Therefore, we revise the query (see Example 6-29) to include data that is necessary to make the decision if any action is possible.

Example 6-29 Including data showing possible action

WITH sorts AS (SELECT qqjfld, qqucnt FROM mydbmon WHERE qqrid=3003 ),

summation AS (SELECT SUM(qqi6) "Total Time" , COUNT(*) "Nbr Times Run", a.qqjfld, a.qqucnt, qq1000 FROM mydbmon a, sorts b WHERE qqrid=1000 AND a.qqjfld=b.qqjfld AND a.qqucnt = b.qqucnt and qqc21 IN ('OP','SI','UP','IN','DL') GROUP BY a.qqjfld, a.qqucnt, qq1000), fetches AS (SELECT a.qqjfld, a.qqucnt, integer(avg(a.qqi3)) "Rows Fetched" FROM mydbmon a, summation b WHERE qqrid=1000 AND a.qqjfld=b.qqjfld and a.qqucnt = b.qqucnt AND qqc21 = 'FE' GROUP BY a.qqjfld, a.qqucnt) SELECT b."Total Time", b."Nbr Times Run",

a.qqrcod "Reason Code", a.qqi7 "Reason subcode for Union", a.qqrss "Number of rows sorted", c."Rows Fetched",

a.qqi1 "Size of Sort Space", a.qqi2 "Pool Size",

a.qqi3 "Pool ID", a.qvbndy "I/O or CPU bound", a.qqucnt, b.qq1000 FROM summation b LEFT OUTER JOIN fetches c ON b.qqjfld = c.qqjfld AND

b.qqucnt = c.qqucnt INNER JOIN mydbmon a

ON b.qqjfld = a.qqjfld AND b.qqucnt = a.qqucnt WHERE a.qqrid = 3003 ORDER BY b."Total Time" DESC;

Figure 6-25 shows the output from the query in Example 6-29.

Figure 6-25 Include data showing action possible

This query uses the following columns:

򐂰 QQRCOD

This column indicates the reason for choosing the query sort technique. The value in this column helps to identify whether the sort required of the query optimizer determined that the cost of the sort is better than any other implementation (such as an index).

If you can change the SQL statement itself, any reason code is available for optimization efforts. Or perhaps you cannot change the SQL statement (that is to optimize the third-party ERP application) and can only build indexes and change other environmental factors to help performance (that is, increase the pool size). In this case, focus your optimization efforts on query sorts with reason code F7 (optimizer chose sort rather than index due to performance reasons) and F8 (optimizer chose sort to minimize I/O wait time).

For a detailed description of each reason code, search on Database Monitor: DDS in the V5R3 iSeries Information Center.

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp

򐂰 QQI7

This column indicates the reason subcode for the UNION clause. If the query sort reason code lists F5 (UNION was specified for query), this column contains one of two subcodes.

A value of 51 means that there is also an ORDER BY in the statement. A value of 52 means that the query specifies UNION ALL rather than simply UNION.

򐂰 QQRSS

This column tells us the number of rows that are contained in the sort space. You can use this value, along with the reason code, to determine if the indexed approach is possible and possibly cheaper (for a small result set). Compare the value of QQRSS with the value of QQI3 from the corresponding 1000 FE record for this open to determine the number of rows that were fetched from the sort space.

If the number of rows in sort space is large, but the actual number of rows fetched is small, consider adding OPTIMIZE FOR n ROWS to the query to help the optimizer make a better decision.

Building a more perfect index for the selection criteria might also help the optimizer make a better decision and use index for the implementation method rather than a query sort.

򐂰 QQI3 from row type 1000 operation id FE (fetch)

This column tells us the number of rows that were fetched from the sort space to satisfy a user request. As described in the QQRSS column description, the value in this column is used to gauge whether more information is required by the query optimizer to make better costing decisions.

򐂰 QQI1

This column indicates the size of the sort space.

򐂰 QQI2

This column indicates the pool size.

򐂰 QQI3 from row type 3003

This column indicates the pool ID.

򐂰 QVBNDY

This column contains a flag that indicates whether the query sort is CPU or I/O bound.

We have taken the base query and modified it to include more information about the query sort implementation. This additional information helps you make more intelligent decisions

when deciding to optimize SQL statements using query sorts as the implementation method.

The most valuable new columns indicate a reason code and the number of actual rows fetched for the query.

Changing your SQL statement or adding the OPTIMIZE FOR x ROWS syntax is most likely to help alleviate issues that pertain to long query sort times. For highly selective queries where sort space is disproportionately larger than actual rows fetched, building a more perfect index might help the optimizer.