Adding HINT to PeopleSoft Query using Query Manager

As an Oracle DBA administering a PeopleSoft database, we would like to ensure that all PeopleSoft queries (PRIVATE or PUBLIC) are optimized. There is always a possibility of introducing a HINT to tune the SQL generated by PeopleSoft in Query Manager. Here are the steps to add the HINT.

1. Create a new expression
2. Click“Add Field” and select the first field that is used in the query

3. Add your HINT before the field name in the expression

4. Click Ok
5. Select the Expression as a field in the query and move it as the first column
6. Deselect the first field A.SETID that has now moved to be the second column

7. Now, you have a query ready with your chosen HINT

Scenario with DISTINCTIf you have chosen DISTINCT in the query properties then your SQL will appear as shown below.

Oracle will ignore the HINT in this case because the DISTINCT precedes the HINT. To correct this behavior follow the below steps.

1. From the query properties uncheck “DISTINCT”

2. Edit the expression holding the HINT and add DISTINCT as shown below.

3. Now the query SQL will appear corrected.

 

Read More about  Peoplesoft