More MetaSQL madness. The following SQL was identified as a long running query from a PeopleTools SQL trace. Note the FIRST_ROWS hint.
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.
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.
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
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
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)*/ …
Posts
2 comments :
Hi David,
We are also facing similar kind of issue in our system.
Customer search by name on 360 page and Case is timing out. There are no logs available. Can you please confirm following on your issue:
When you used to run those queries manually with /*+ FIRST_ROWS */ Hint then also it used to timeout ?
Because in our case when we run these queries in backend manually with /*+ FIRST_ROWS */ hint it executes within fraction of second.
Thanks
This blog post is over 11 years old, and things change and move on. PeopleTools no longer inserts the FIRST_ROWS hint. The OracleDisableFirstRowsHint parameter to suppress the hinting behaviour has also disappeared from the configuration files. Though I can not now remember when that happened.
The FIRST_ROWS* hints effectively force an indexed look up of the data. However, FIRST_ROWS is deprecated, you should use FIRST_ROWS(n). If you need a hint you will either have to inject it into the code, or use an Oracle SQL profile or SQL patch.
Post a Comment