I worked with our support team to improve the performance of a PeopleSoft Financials update statement yesterday. The update statement had an index hint already in it but the index was not the best one of the available indexes.
Here is the original update statement:
UPDATE /*+ INDEX(B PSCPYMNT_VCHR_XREF) */
PS_PYMNT_VCHR_XREF B
SET BANK_SETID = :1,
BANK_CD = :2,
BANK_ACCT_KEY = :3,
PYMNT_METHOD = :4,
BANK_ACCT_SEQ_NBR = :5,
EFT_LAYOUT_CD = :6,
STL_THROUGH = :7
WHERE B.REMIT_SETID = 'XYZ'
AND B.REMIT_VENDOR = :8
AND B.PYMNT_SELCT_STATUS = 'N'
AND B.PYMNT_ID = ' '
AND B.BANK_ACCT_KEY NOT LIKE ('EXP%')
I listed out the columns for the indexes on the table using the “querytuning” part of my standard sql tuning scripts. (current version)
Here are the columns for the hinted index:
REMIT_SETID REMIT_VENDOR VNDR_LOC
The where clause includes only the first two columns.
But another similar index, PSEPYMNT_VCHR_XREF, exists with these columns:
REMIT_SETID REMIT_VENDOR PYMNT_SELCT_STATUS
The where clause has all three of these columns. So, why was the original query hinted this way? Does the E index not work better than the C index? I ran this query to see how selective the condition PYMNT_SELCT_STATUS = ‘N’ is.
>select PYMNT_SELCT_STATUS,count(*) 2 from PS_PYMNT_VCHR_XREF B 3 WHERE B.REMIT_SETID = 'XYZ' 4 AND B.REMIT_VENDOR = '12345678' 5 group by PYMNT_SELCT_STATUS; P COUNT(*) - ---------- C 5 N 979 P 177343 X 5485
I included the conditions on the first two columns that both indexes share, but removed the other conditions from the original update. A count on the number of rows that meet the conditions of only these two columns shows how many rows the original index will have to use to check the remaining where clause conditions.
I grouped by PYMNT_SELCT_STATUS to see how many rows met the condition PYMNT_SELCT_STATUS = ‘N’ and how many did not. Grouping on PYMNT_SELCT_STATUS shows how many rows the new index will use to check the remaining conditions in the where clause. I ran this query to see if the second index would use fewer rows than the first.
This query showed that only 979 of the over 180,000 rows met the condition. This made me think that the E index which includes PYMNT_SELCT_STATUS has a good chance of speeding up the original update. I ran a count with a hint forcing the C index and then again forcing the E index:
>
>set timing on
>
>select /*+ INDEX(B PSCPYMNT_VCHR_XREF) */ count(*)
2 from PS_PYMNT_VCHR_XREF B
3 WHERE B.REMIT_SETID = 'XYZ'
4 AND B.REMIT_VENDOR = '12345678'
5 AND B.PYMNT_SELCT_STATUS = 'N'
6 AND B.PYMNT_ID = ' '
7 AND B.BANK_ACCT_KEY NOT LIKE ('EXP%');
COUNT(*)
----------
982
Elapsed: 00:13:52.53
>
>select /*+ INDEX(B PSEPYMNT_VCHR_XREF) */ count(*)
2 from PS_PYMNT_VCHR_XREF B
3 WHERE B.REMIT_SETID = 'XYZ'
4 AND B.REMIT_VENDOR = '12345678'
5 AND B.PYMNT_SELCT_STATUS = 'N'
6 AND B.PYMNT_ID = ' '
7 AND B.BANK_ACCT_KEY NOT LIKE ('EXP%');
COUNT(*)
----------
982
Elapsed: 00:00:01.28
The original hint caused the select count(*) query to run in 13 minutes while the new hint caused it to run in 1 second. Clearly the new E index causes the query to run faster!
The developer that I was working with found the problem update statement in some PeopleCode and was able to edit the hint forcing it to use the better index. We migrated the modified code to production and the user was able to run the update statement without the web site timing out. Prior to the change the user was not able to complete the update because the SQL statement took so long it exceeded our application server timeout.
Bobby
