Yesterday, a good friend of mine asked this question pertaining to an issue that he saw at the client site. The query was being fired off by a UI screen in the application and the query that was being formed as part of the SQLBuilder (custom built ORM by the vendor) was not building up the query properly leading to very poor performance. The RDBMS that this client was using was Oracle however the same sets of issues apply to any RDBMS.
The query had 5 tables involved :
SELECT * FROM
(SELECT ROWNUM ROW_NUM, A.* FROM
PKT_HDR_INTRNL.PKT_CTRL_NBR AS A255, PKT_HDR.CO AS A3, PKT_HDR.DIV AS A4,
PKT_HDR.PKT_NBR AS A5, PKT_HDR.PKT_SFX AS A6, PKT_HDR.SHIPTO_NAME AS A11,
PKT_HDR.START_SHIP_DATE AS A60, PKT_HDR.STOP_SHIP_DATE AS A61,
PKT_HDR.SHIP_VIA AS A49, PKT_HDR_INTRNL.TOTAL_NBR_OF_UNITS AS A266,
PKT_HDR_INTRNL.STAT_CODE AS A262, PKT_HDR_INTRNL.VAS_INDIC AS A339,
PKT_HDR.WHSE AS A2, PKT_HDR_INTRNL.MAJOR_MINOR_PKT AS A275,
PKT_HDR.PACK_SLIP_TYPE AS A123, PKT_HDR.PKT_TYPE AS A82,
PKT_HDR.PKT_GENRTN_DATE_TIME AS A59, PKT_HDR.ORD_NBR AS A7,
PKT_HDR_INTRNL.PICK_WAVE_NBR AS A256, CHUTE_MASTER.CHUTE_ID AS A378,
PKT_HDR_INTRNL.WAVE_STAT_CODE AS A258, PKT_HDR.PKT_CTRL_NBR AS A1, ” AS
A419, ITEM_MASTER.SKU_ID AS A307, ITEM_MASTER.DIV AS A309,
PKT_DTL.CANCEL_QTY AS A159
FROM PKT_DTL, ITEM_MASTER, CHUTE_MASTER, PKT_HDR, PKT_HDR_INTRNL
PKT_HDR.WHSE = ’02’ AND
PKT_HDR_INTRNL.STAT_CODE >= 10 AND
PKT_HDR_INTRNL.STAT_CODE <= 60 AND
PKT_HDR.DIV = ’64’ AND
PKT_HDR.CO = ’88’ AND
ITEM_MASTER.CO = ’88’ AND
ITEM_MASTER.DIV = ’64’
ORDER BY PKT_HDR_INTRNL.PKT_CTRL_NBR ASC ) A
WHERE ROWNUM < 21 )
WHERE ROW_NUM >= 1
And here are the relationships:
PKT_HDR : PKT_HDR_INTRNL :: 1:1 (hard one to one because of the number of columns in each table, it was split up into two tables)
PKT_HDR : PKT_DTL :: 1:N (for every header, there can be 1 – N details)
ITEM_MASTER : PKT_DTL :: 1:N (every PKT_DTL will be associated with one and only one ITEM_MASTER record)
CHUTE_MASTER : PKT_HDR_INTRNL :: 1:N (0-N) (One chute can belong to more than one PKT record)
What my friend noticed was that the query was doing a FTS on PKT_DTL as soon as the column PKT_DTL.CANCEL_QTY was introduced in the Select list. It was not doing that if that column was omitted from the selection list. That is how this query got noticed. First thing to notice is the pagination logic in the query – we have discussed that before – the count stop key optimization.
It does have some functional flaws though before we get into the performance tuning side of things:
1) The query is doing a left outer join between PKT_HDR and PKT_DTL and then it is doing an INNER JOIN between PKT_DTL and ITEM_MASTER??
So, for records in the PKT_HDR table for which there are no details, when that INNER JOIN is done, those records will be filtered out. Either the join between PKT_HDR and PKT_DTL needs to be changed to an inner join or the join between PKT_DTL and ITEM_MASTER and the subsequent filters on ITEM_MASTER need to be in an outer join condition.
So, that is a functional issue.
2) Older join syntax of Oracle has been followed which is not an issue – just that it is not ANSI and many times leads to issues for Developers/DBAs who are not familiar with it. It is what probably lead to the issue mentioned in #1 above.
3) CHUTE_ID is the only column that is being displayed from that table. Since it is in an outer join condition with no filter condition, that join can be completely omitted and the CHUTE_ID from the child table itself can be used.
4) The DISTINCT is causing SORT and the distinct is needed only because the PKT_DTL join has been added to the query and no columns from the PKT_DTL table are being selected. Note that the PKT_DTL.CANCEL_QTY column was not always present – that was a modification done by the end user.
When he talked to the business folks, it turned out that the OUTER JOIN with PKT_DTL was not needed and they really needed to restrict based on the data from the detail records and then the ITEM_MASTER table. Once those changes were made, the execution plan was fine and went through the primary key join condition’s index for the filter criteria specified. This can still result into issues down the line since the client is using FIRST_ROWS_N optimization for fast retrieval of the records but the filter criteria values are dynamic and so it can happen that based on the status code values, they can end up qualifying for more than 40% of the PKT_DTL table. So, besides the changes from above, depending upon the flow, they needed to make sure that the selective criteria is passed in as well.