%FirstRows MetaSQL uses FIRST_ROWS instead of FIRST_ROWS(n) hint on Oracle

More MetaSQL madness. The following SQL was identified as a long running query from a PeopleTools SQL trace. Note the FIRST_ROWS hint.

PSAPPSRV.904 1-22827 13.52.01 0.000 Cur#2.904.CRPRD01 RC=0 Dur=0.000 COM Stmt=SELECT /*+ FIRST_ROWS */ CASE_ID, BUSINESS_UNIT, RC_VERTICAL, SETID_CUSTOMER, BO_ID_CUST, ROLE_TYPE_ID_CUST, BO_NAME, BO_ID_CONTACT, ROLE_TYPE_ID_CNTCT, BO_NAME_2, ROLE_TYPE_DESCR, CM_TYPE_ID, PROFILE_CM_SEQ, RC_PURP_TYPE_DESCR, SITE_ID, DESCR, PERSON_PIN, SIN, CASE_CONTACT, RC_CONTACT_NAME, SETID_ENTL, SETID_PRODUCT, SETID_PROV_GRP, PROVIDER_GRP_ID, SECURE_CASE_FLG, CUST_STATUS, EMPL_STATUS, EMPLID, SETID_DEPT, DEPTID, RC_DEPT_DESCR, SETID_LOCATION, LOCATION, RC_LOC_DESCR, PHYSICAL_LOCATION, RC_PHONE, EXTENSION, EMAIL_ADDR, RC_SUMMARY, RC_STATUS, RC_SHORT_DESCR, TO_CHAR(CREATION_DATE,'YYYY-MM-DD'), TO_CHAR(CLOSED_DATE,'YYYY-MM-DD'), COMPETENCY, START_DT, END_DT, RBTACCTID, RBTACCTNO, RCTINSTPHONENO, BO_NAME_3, BO_ID_SITE, ROLE_TYPE_ID_SITE, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, ADDRESS1_AC, ADDRESS2_AC, ADDRESS3_AC, ADDRESS4_AC, CITY, CITY_AC, STATE, POSTAL, COUNTRY, BO_ID_AGR, ROLE_TYPE_ID_AGR
FROM PS_RC_CASE_HD_VW2
WHERE BUSINESS_UNIT = 'HRSUK'
AND RC_CONTACT_NAME = 'Hpolite'
AND RC_VERTICAL = 'HD'
AND MARKET = 'HHD'
ORDER BY CASE_ID DESC

The SQL Statement comes from a SQL exec in the DERIVEDRCSEARCH.SEARCH_BUTTON.FieldChange peoplecode, but it contains the %FirstRows metaSQL which controls how the rows are fetched.

…%FirstRows(" String(&rc_case_qry_rows + 51) ") …

The %FirstRows metaSQL is introducing the /*+ FIRST_ROWS */ hint on Oracle. On Microsoft SQLServer it evaluates to TOP(n), causing the query to return only the first n rows of the data set. This is a change in behaviour introduced in PeopleTools 8.46 prior to which this macro evaluated to a blank string when PeopleSoft runs on an Oracle database.

The solution to my performance problem was simply to remove the hint. The following table shows timings in SQL*Plus for the statement with and without the hints.

Hint	Execution Time(UAT system)	Execution Time (PRD system)
First Rows	69s	37.08s
No hint, any FIRST_ROWS(n), or ALL_ROWS	5s	<1s

This hint is deprecated in Oracle 9i having been replaced with FIRST_ROWS(n). FIRST_ROWS is that it uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. Oracle’s Performance Tuning and Planning manual was that “Using heuristics sometimes leads the CBO to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic. FIRST_ROWS is available for backward compatibility and plan stability”.

The problem is that the FIRST_ROWS hint includes some rules that override the usual cost-based behaviour, including that an index can be used to avoid a sort operation, no matter how expensive the path may be. In most situations FIRST_ROWS is simply an inappropriate hint in Oracle 9i.

I have found two support cases (200991709 and 200769258) where this hint was causing a problem, and it is probably in response to these, that there is now a workaround available in PeopleTools 8.46. A new parameter OracleDisableFirstRowsHint has been added to the Database Options section of both the Application Server configuration file (psappsrv.cfg) and the Process Scheduler configuration file (psprcs.cfg). This flag defaults to 0, and should be set to 1 in order to supress this hint.

However, PeopleTools 8.46 is not certified on any release of Oracle prior to 9.2.0.6.0, and the FIRST_ROWS hint was deprecated in Oracle 9i when it was replaced by FIRST_ROWS(n). MetaSQLs are there to produce appropriate platform specific code. It would be much better if this MetaSQL generates a FIRST_ROWS(n) hint in the first place, perhaps resulting in this

…Stmt=SELECT /*+ FIRST_ROWS(100)*/ …