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

Demystifying PSPROJECTITEM -Part 2

Continuing from my previous post, here are the steps to load the upg.sqc data relevant for our manipulation into the Oracle database.

1. Create record HX_COMP_TBL in App Designer as shown below.

2. Build/create the table
3. Add it to Query Security if you need to access it using PeopleSoft Query Manager.
4. Create Download upg_sqc.txt file
5. Create SQL*Loader control file as shown below.
load data
infile ‘/export/home/npai/psprojectitem/upg_sqc.txt’
into table PS_HX_CMP_TBL
fields terminated by ” ” optionally enclosed by ‘”‘
(OBJ_DESCR2, OBJNAME, VAL1)
6. Execute sqlldr command to load the file
sqlldr sysadm/sysadm@ftest control=sqlld.ctl
7. Use it in query through Query Manager or SQLPLUS

Read More about  Peoplesoft