Applying Hints to Objects inside Database Views

Occasionally, I need to apply a hint to a table within a view to achieve a particular execution plan. I can't put the hint into the view because it is not appropriate for all queries that use the view. However, I can give the query block an explicit name using the QB_NAME hint, and then refer to the named query block.

When I do this, I give the query block the same name as the record in PeopleSoft. This example uses record VAT_TX_AP_I_VW that corresponds to a database view PS_VAT_TX_AP_I_VW. Therefore, I named the query block VAT_TX_AP_I_VW. This naming convention is helpful if the view name is dynamically generated in an Application Engine step (see previous blog posting on Hinting Dynamically Generated SQL in Application Engine).

CREATE VIEW PS_VAT_TX_AP_I_VW ... AS
SELECT /*+QB_NAME(VAT_TX_AP_I_VW)*/ B.BUSINESS_UNIT
,B.VOUCHER_ID
,B.UNPOST_SEQ
,B.APPL_JRNL_ID
...
FROM PS_VOUCHER A
,PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.VOUCHER_ID = B.VOUCHER_ID
AND B.VAT_DISTRIB_STATUS IN (' ', 'N', 'R', 'M')
AND B.DST_ACCT_TYPE IN ('VIR', 'VIWR', 'VIIR', 'VICR')
AND B.PRIMARY_LEDGER = 'Y' 

I need to use a hint to force this use of this index on the table PS_VCHR_ACCTG_LINE.

CREATE INDEX PSHVCHR_ACCTG_LINE ON PS_VCHR_ACCTG_LINE
(VAT_DISTRIB_STATUS, BUSINESS_UNIT, VOUCHER_ID); 

Now I can add a hint to the query that calls the view and apply the hint to a table within the view.

SELECT /*+INDEX(@VAT_TX_AP_I_VW B@VAT_TX_AP_I_VW (PS_VCHR_ACCTG_LINE.VAT_DISTRIB_STATUS))*/ C.VAT_ENTITY,
A.COUNTRY_VAT_RPTG, 0, 0, A.BUSINESS_UNIT, A.VOUCHER_ID, A.UNPOST_SEQ, A.APPL_JRNL_ID,
A.POSTING_PROCESS, A.PYMNT_CNT, A.VOUCHER_LINE_NUM, A.DISTRIB_LINE_NUM, A.DST_ACCT_TYPE,
A.CF_BAL_LINE_NUM, A.LEDGER, A.TAX_AUTHORITY_CD, 50717653, 'N'
FROM PS_VAT_TX_AP_I_VW A,
PS_VAT_UPD_BU_TAO B
, PS_VAT_ENT_BU_GL C
WHERE B.PROCESS_INSTANCE = 50717653
AND B.REQUEST_NBR = 1
AND B.VAT_SOURCE_DEFN = 'AP_VOUCHER'
AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.BUSINESS_UNIT_GL = C.BUSINESS_UNIT

The execution plan shows that the index dictated by the hint was used. There are additional benefits. I can also see the query block name in various places in the extended execution plan, and it helps interpretation.

-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |   157 |     5   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                    |     1 |   157 |     5   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                    |     1 |   138 |     4   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN        |                    |     1 |    66 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN           | PS_VAT_UPD_BU_TAO  |     1 |    52 |     0   (0)| 00:00:01 |
|   5 |     BUFFER SORT                |                    |     9 |   126 |     1   (0)| 00:00:01 |
|   6 |      INDEX FULL SCAN           | PS_VAT_ENT_BU_GL   |     9 |   126 |     1   (0)| 00:00:01 |
|   7 |    INLIST ITERATOR             |                    |       |       |            |          |
|*  8 |     TABLE ACCESS BY INDEX ROWID| PS_VCHR_ACCTG_LINE |     1 |    72 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | PSHVCHR_ACCTG_LINE |   117 |       |     3   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID  | PS_VOUCHER         |     1 |    19 |     1   (0)| 00:00:01 |
|* 11 |    INDEX UNIQUE SCAN           | PS_VOUCHER         |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

It is now clear which objects in the execution plan come from the view. This can be helpful when the same table appears in more than one place.

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$71CBF28F
4 - SEL$71CBF28F / B@SEL$1
6 - SEL$71CBF28F / C@SEL$1
8 - SEL$71CBF28F / B@VAT_TX_AP_I_VW
9 - SEL$71CBF28F / B@VAT_TX_AP_I_VW
10 - SEL$71CBF28F / A@VAT_TX_AP_I_VW
11 - SEL$71CBF28F / A@VAT_TX_AP_I_VW

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$71CBF28F" "A"@"VAT_TX_AP_I_VW")
USE_NL(@"SEL$71CBF28F" "B"@"VAT_TX_AP_I_VW")
USE_MERGE_CARTESIAN(@"SEL$71CBF28F" "C"@"SEL$1")
LEADING(@"SEL$71CBF28F" "B"@"SEL$1" "C"@"SEL$1" "B"@"VAT_TX_AP_I_VW" "A"@"VAT_TX_AP_I_VW")
INDEX_RS_ASC(@"SEL$71CBF28F" "A"@"VAT_TX_AP_I_VW" "PS_VOUCHER")
INDEX_RS_ASC(@"SEL$71CBF28F" "B"@"VAT_TX_AP_I_VW" ("PS_VCHR_ACCTG_LINE"."VAT_DISTRIB_STATUS"
 "PS_VCHR_ACCTG_LINE"."BUSINESS_UNIT" "PS_VCHR_ACCTG_LINE"."VOUCHER_ID"))
INDEX(@"SEL$71CBF28F" "C"@"SEL$1" ("PS_VAT_ENT_BU_GL"."VAT_ENTITY"
 "PS_VAT_ENT_BU_GL"."BUSINESS_UNIT"))
INDEX(@"SEL$71CBF28F" "B"@"SEL$1" ("PS_VAT_UPD_BU_TAO"."PROCESS_INSTANCE"
 "PS_VAT_UPD_BU_TAO"."REQUEST_NBR" "PS_VAT_UPD_BU_TAO"."VAT_SOURCE_DEFN"
 "PS_VAT_UPD_BU_TAO"."BUSINESS_UNIT"))

OUTLINE(@"VAT_TX_AP_I_VW")
OUTLINE(@"SEL$1")
MERGE(@"VAT_TX_AP_I_VW")
OUTLINE_LEAF(@"SEL$71CBF28F")
ALL_ROWS
OPT_PARAM('_optimizer_cost_based_transformation' 'off')
OPT_PARAM('_unnest_subquery' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

This technique is not infallible. I have had cases where query transformation rendered the QB_NAME invalid.

Deferred Segment Creation in PeopleSoft

This note has been in my to do folder for a while since I found these two excellent blog entries about Deferred Segment Creation by Chistian Antognini.

• http://antognini.ch/2009/09/deferred-segment-creation/
• http://antognini.ch/2010/10/deferred-segment-creation-as-of-11-2-0-2/

They made me think about the use of this feature in PeopleSoft. In most PeopleSoft systems there are lots of empty tables, sometimes because not all the modules delivered in the database are in use, but also because not all the temporary table instances have ever been used. This blog entry from Tom Kyte feels very close to home

• http://tkyte.blogspot.com/2011/02/deferred-segment-creation.html

Deferred segment creation is available from Oracle 11gR1. From Oracle 11.2.0.2 this becomes the default, so there is no need to reconfigure anything in PeopleSoft in order use this Oracle feature. You may want to drop empty segments created under previous versions of the database.

More Process Priority Levels for the Process Scheduler

This note started out as an idea for how to add more priority levels to the PeopleSoft Process Scheduler to improve control over prioritisation of processes in a complex batch. While testing I found some interesting behaviour that I had not expected. I will also explain how I did some of my tests, because they are easily reproducible.

More Priority Levels
By default, PeopleSoft process can be given priority 1 (low), 5 (medium) and 9 (high). If the Process Scheduler is too busy to start all the processes scheduled to be started, it starts the higher priority ones in preference to the lower priority.

That’s fine, but sometimes in a very complex batch environment 3 process levels are not enough, and it would be useful to have more priority levels so that it is possible to define the hierarchy of processing to a finer level.

There does not appear to be anything special about the priorities delivered by PeopleSoft. They do not appear to be hard coded anywhere within the SQL submitted Process Scheduler. As you might expect, some of the queries are sorted in descending order of priority.

The priority of a process in the Process Scheduler is defined by the single character field PRCSPRIORITY on the record PRCSDEFN. All you have to do is define addition translate values

The new priorities can be assigned via the delivered components.

I started with the intention of applying the new priority level to processes through the delivered component. However, I encountered some quirky behaviour from the Process Scheduler and Master Process Scheduler. There is something happening inside the programs rather than the SQL where they do not always take the process with the highest priority if that priority is not one of the delivered values. Therefore, I do not recommend using the new priorities on process definitions.

However, Server Categories also have priorities. Testing has shown that the priority of the Process Category take precedence over the priority on the Process Definition.

The new server categories should be defined in the Process Category Administration page of the Process Scheduler System Settings component

Then priorities and maximum concurrencies can be assigned to the categories in each Process Scheduler Server definition.

Testing the Additional Priorities
I have (with some assistance) constructed a simple test harness. I have an Application Engine program, AE_SLEEP, that does nothing, but sleeps for a period of time by calling the Java Sleep() method in a PeopleCode step.

Local JavaObject &Obj;Local ProcessRequest &RQST;MessageBox(0, "", 0, 0, "Sleep Begin for " | GFC_SLEEP_AET.DURATION.Value | " seconds ");&Obj = CreateJavaObject("java.lang.Thread");&Obj.start();&Obj.sleep(GFC_SLEEP_AET.DURATION.Value * 1000);MessageBox(0, "", 0, 0, "Sleep END");…

I have a number of other Application engine programs that have different priorities, or that are in categories with different priorities, that call AE_SLEEP.

Finally there is a process AE_SLEEP_RUN which submits requests for the other processes based on a control table. In this test I will be setting up some test data to demonstrate how the Process Scheduler behaves, in the next article, I will be modelling the behaviour of a real batch load from a real system

TRUNCATE TABLE ps_gfc_sleep_rc/INSERT INTO ps_gfc_sleep_rc(run_cntl_id, prcstype, prcsname, offset_amount, duration, rundttm, last_run_cntl_id, next_run_cntl_id)SELECT level, 'Application Engine', 'AE_SLEEP'||level, 5*level, 175, NULL, ' ', ' 'FROM dual CONNECT BY level <= 9/COMMIT/ALTER SESSION SET NLS_DATE_FORMAT='hh24:mi:ss';set lines 120COLUMN run_cntl_id FORMAT a8 heading 'Run|Control'COLUMN last_run_cntl_id FORMAT a8 heading 'Last|Run|Control'COLUMN next_run_cntl_id FORMAT a8 heading 'Next|Run|Control'COLUMN prcsinstance FORMAT 99999 HEADING 'PRCS|INSTANCE'COLUMN offset_amount FORMAT 9999 HEADING 'Offset|Amount'COLUMN rownum FORMAT 99 HEADING 'Row|#'COLUMN prcstype FORMAT a18COLUMN prcsname FORMAT a12COLUMN prcsprty FORMAT 9 HEADING 'PRCS|PRTY'COLUMN prcscategory FORMAT a11 HEADING 'PRCS|CATEGORY'COLUMN serverassign FORMAT a6 HEADING 'SERVER|ASSIGN'COLUMN runstatus FORMAT a6 HEADING 'RUN|STATUS'SELECT run_cntl_id, prcsname, offset_amount, duration, rundttm FROM ps_gfc_sleep_rc/

So the AE_SLEEP_RUN will schedule one process every 5 seconds. The lowest priority processes are scheduled first.

Run                            OffsetControl  PRCSNAME     RUNDTTM  Amount   DURATION-------- ------------ -------- ------ ----------1        AE_SLEEP1                  5        1752        AE_SLEEP2                 10        1753        AE_SLEEP3                 15        1754        AE_SLEEP4                 20        1755        AE_SLEEP5                 25        1756        AE_SLEEP6                 30        1757        AE_SLEEP7                 35        1758        AE_SLEEP8                 40        1759        AE_SLEEP9                 45        175

I can query what actually happened with the following SQL.

SELECT rownum, a.* FROM (SELECT SYSDATE, q.prcsinstance, q.prcsname, q.prcsprty, q.rundttm, r.begindttm, r.enddttm, q.serverassign, q.runstatus--, r.prcscategoryFROM psprcsrqst r, psprcsque qWHERE r.prcsinstance = q.prcsinstanceAND r.prcsname like 'AE_SLEEP_'AND r.prcsinstance > (     SELECT MAX(prcsinstance) FROM psprcsrqst      WHERE prcsname = 'AE_SLEEP_RUN' AND runstatus = '9')ORDER BY r.begindttm, q.prcsprty DESC, q.rundttm, r.prcsinstance) a/

In all of the examples below, the first three low priority process run first because they are scheduled to run and therefore start before the higher priority processes are scheduled. The Process Scheduler is then blocked until all the other processes are due to have run, and so priority determines while processes the scheduler starts next.

However, due to a quirk in Process Scheduler, the processes are not assigned to the Process Scheduler in time order. In the following example AE_SLEEP5 was executed before AE_SLEEP6.

Row           PRCS  # SYSDATE   INSTANCE PRCSNAME     RUNDTTM  BEGINDTTM ENDDTTM--- --------- -------- ------------ -------- --------- --------  1 13:14:17      4583 AE_SLEEP1    13:03:27 13:03:55  13:07:08  2 13:14:17      4584 AE_SLEEP2    13:03:32 13:03:55  13:07:08  3 13:14:17      4585 AE_SLEEP3    13:03:37 13:03:55  13:07:08  4 13:14:17      4591 AE_SLEEP9    13:04:07 13:07:12  13:10:27  5 13:14:17      4587 AE_SLEEP5    13:03:47 13:07:13  13:10:27  6 13:14:17      4588 AE_SLEEP6    13:03:52 13:07:13  13:10:27  7 13:14:17      4586 AE_SLEEP4    13:03:42 13:10:31  13:13:46  8 13:14:17      4589 AE_SLEEP7    13:03:57 13:10:47  13:14:01  9 13:14:17      4590 AE_SLEEP8    13:04:02 13:10:47  13:14:01

But if the non-standard priority jobs are put in a different category, they are executed in the right order. It would appear that there is some logic in the Master Process Scheduler (and not in the SQL queries it submits) that works category by category.

Row              PRCS              PRCS                            SERVER RU  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN ST PRCSCATEGORY--- -------- -------- ------------ ---- -------- -------- -------- ------ -- ------------  1 17:44:21     4681 AE_SLEEP5       5 17:31:10 17:31:24 17:34:39 PSNT   9  Default  2 17:44:21     4682 AE_SLEEP5       5 17:31:13 17:31:24 17:34:39 PSNT   9  Default  3 17:44:21     4683 AE_SLEEP5       5 17:31:16 17:31:40 17:34:56 PSNT   9  Default  4 17:44:21     4690 AE_SLEEP7       7 17:32:43 17:34:43 17:37:57 PSNT   9  Priority 9  5 17:44:21     4689 AE_SLEEP7       7 17:32:44 17:34:43 17:37:57 PSNT   9  Priority 9  6 17:44:21     4688 AE_SLEEP7       7 17:32:45 17:34:59 17:38:14 PSNT   9  Priority 9  7 17:44:21     4687 AE_SLEEP7       7 17:32:46 17:38:01 17:41:16 PSNT   9  Priority 9  8 17:44:21     4684 AE_SLEEP5       5 17:31:19 17:38:01 17:41:16 PSNT   9  Default  9 17:44:21     4685 AE_SLEEP5       5 17:31:22 17:38:17 17:41:33 PSNT   9  Default 10 17:44:21     4686 AE_SLEEP5       5 17:31:25 17:41:20 17:44:20 PSNT   9  Default

If there are two new priorities in the new category, then they do not run in the right order.

Row              PRCS              PRCS                            SERVER RUN  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN STATUS PRCSCATEGORY--- -------- -------- ------------ ---- -------- -------- -------- ------ ------ ------------  1 19:01:58     4716 AE_SLEEP5       5 18:06:58 18:07:26 18:10:40 PSNT   9      Default  2 19:01:58     4718 AE_SLEEP5       5 18:07:04 18:07:26 18:10:40 PSNT   9      Default  3 19:01:58     4719 AE_SLEEP5       5 18:07:07 18:07:26 18:10:40 PSNT   9      Default  4 19:01:58     4723 AE_SLEEP6       6 18:08:25 18:10:44 18:13:43 PSNT   9      Priority 9  5 19:01:58     4722 AE_SLEEP6       6 18:08:30 18:10:44 18:13:43 PSNT   9      Priority 9  6 19:01:58     4717 AE_SLEEP5       5 18:07:01 18:10:44 18:13:43 PSNT   9      Default  7 19:01:58     4724 AE_SLEEP7       7 18:08:40 18:14:02 18:17:00 PSNT   9      Priority 9  8 19:01:58     4725 AE_SLEEP7       7 18:08:45 18:14:02 18:17:00 PSNT   9      Priority 9  9 19:01:58     4720 AE_SLEEP5       5 18:07:10 18:14:02 18:17:00 PSNT   9      Default 10 19:01:58     4721 AE_SLEEP5       5 18:07:13 18:17:18 18:20:17 PSNT   9      Default

However, if I create a separate category for each priority, and assign the priority to that category in the server definition, then the processes run in the desired order.

Row              PRCS              PRCS                            SERVER RUN  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN STATUS PRCSCATEGORY--- -------- -------- ------------ ---- -------- -------- -------- ------ ------ ------------  1 19:38:00     4740 AE_SLEEP5       5 19:25:10 19:25:23 19:28:23 PSNT   9      Default  2 19:38:00     4741 AE_SLEEP5       5 19:25:13 19:25:39 19:28:38 PSNT   9      Default  3 19:38:00     4742 AE_SLEEP5       5 19:25:16 19:25:39 19:28:38 PSNT   9      Default  4 19:38:00     4748 AE_SLEEP7       7 19:26:52 19:28:41 19:31:41 PSNT   9      Priority 7  5 19:38:00     4749 AE_SLEEP7       7 19:26:57 19:28:58 19:31:56 PSNT   9      Priority 7  6 19:38:00     4747 AE_SLEEP6       6 19:26:37 19:28:58 19:31:56 PSNT   9      Priority 6  7 19:38:00     4746 AE_SLEEP6       6 19:26:42 19:32:00 19:34:59 PSNT   9      Priority 6  8 19:38:00     4743 AE_SLEEP5       5 19:25:19 19:32:16 19:35:15 PSNT   9      Default  9 19:38:00     4744 AE_SLEEP5       5 19:25:22 19:32:16 19:35:16 PSNT   9      Default 10 19:38:00     4745 AE_SLEEP5       5 19:25:25 19:35:02 19:37:59 PSNT   9      Default

Conclusion
The Process Scheduler does not reliably take process priorities into account when scheduling processes and so I do not recommend using the additional priorities on processes, just the three delivered priorities (1, 5 and 9).

However, all the priorities do work properly on process categories. The priority of the server class does not appear to have any effect (I have not produced results for those tests here).

The priority of the category to which a process belongs takes precedence over the priority of the process. Thus a low priority process in a high priority category will be executed by the Process Scheduler in preference to a high priority process in a low priority category. Naturally if two processes are in the same category, or categories of equal priority, the higher priority process will be scheduled first.

Acknowledgements
My thanks to Andy Mason of Business Integrations for contributing to the initial concept and the Java code to make Application Engine sleep and assistance with the test harness.

Further Reading
I have published a paper on my website that describes how to extend this technique to model a real production batch, and predict the impact of Process Scheduler configuration changes.

Unlocking Temporary Table Instances from Deleted Process Requests

In a previous blog entry, I discussed how to detect whether an Application Engine process had run out of non-shared Temporary Table instances by querying the message log table for the entries written when this happens.

This can happen because processes that have locked all available non-shared table instances are currently processing. However, if a restartable Application Engine program terminates with an error, the tables remain locked to that process instance so that it can be restarted. If that process instance is neither cancelled nor restarted so that it subsequently runs to success then the tables will remain locked, and in time the Process Scheduler archive process may purge the process request record.

However, that archive process does not unlock the temporary table instances (by deleting the registration of the table with the instance from the record AETEMPTBLMGR), and once the process request has been deleted it cannot be cancelled in the Process Monitor. Therefore, there is no way for an operator to unlock the tables via the web interface.  Over time this can build up and new processes are forced to use the shared instance.  In my experience this is more common on development and test environments than production.

The problem is that is a silent and insidious problem that can gradually degrade batch performance.  Forcing processes onto the shared instance forces them to delete rather than truncate working storage tables.  So there is additional redo, and high water marks are not reset.  Concurrent processes can contend as they share the same physical table.  There may be more work to preserve read consistency, and more activity on the undo segment.

The way to prevent this problem is, of course, to make sure you have sufficient instance of temporary table, but before you create new instances check that there an no existing instances that should not still be locked. 

Update 7.3.2011: Phil's comment below correctly reminds me that PeopleSoft provided a way to remove locks without issuing SQL directly at PeopleTools -> Application Engine -> Review Temp Table Usage.  This component clears records from PS_AETEMPTBLMGR, PS_AERUNCONTROL and PS_AERUNCONTROLPC.  This feature is also described at PeopleSoft Tips & Notes: PS_AERUNCONTROL.

However, you can only remove locks for one process instance at a time.  If the problem has got out of hand, you might still chose to delete the rows from these three table where there is no longer a process request record. 

DELETE FROM ps_aetemptblmgr t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/

DELETE FROM ps_aeruncontrol t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/

DELETE FROM ps_aeruncontrolpc t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/ 

Finding Unnecessary Effective Date Processing in PS/Query

In PeopleSoft for the Oracle DBA (Ch11, p309) I commented on how PS/Query will automatically add effective date criteria to and EFFDT column, even if it is not a key column. 

PS/Query does warn you that it has added the criteria, but it won't warn that the column is not a key column

If EFFDT is not part of the key, then it is unlikely that you will need this processing.  It is unlikely to change the result of the query, and it incurs additional work. 

Below is an example of the code added to a query by this one criteria. The effective date sub-query will be correlated by the key columns prior to the EFFDT column. If EFFDT is not part of the key, then all the key columns will be used for the correlation conditions. There is only one row for each EMPLID and EMPL_RCD, and the effective date sub-queries will find and return that row, and the result set will remain the same.

...
AND ( a.effdt = 
        (SELECT MAX(a_ed.EFFDT) FROM ps_employees a_ed
        WHERE a.emplid = a_ed.emplid 
          AND a.empl_rcd = a_ed.empl_rcd 
          AND a_ed.EFFDT <= SYSDATE) 
AND a.effseq = 
        (SELECT MAX(a_es.EFFSEQ) FROM ps_employees a_es
        WHERE a.emplid = a_es.emplid 
          AND a.empl_rcd = a_es.empl_rcd 
          AND a.effdt = a_es.EFFDT) )
...

But how prevalent is this problem?  Like most things in PeopleSoft, Queries are stored in the PeopleTools tables.  And it is possible to construct a query to identify queries with effective date criteria on EFFDT columns that are not part of the key on their record.

SELECT  c.oprid, c.qryname, r.recname, r.corrname, f.fieldname 
--,     useedit, bitand(d.useedit,1) 
FROM    psqrycriteria c /*query crieria*/
,       psqryrecord r   /*records in queries*/
,       psqryfield f    /*fields in a queries*/
,       psrecfielddb d  /*fields on records, with sub-records fully expanded*/
WHERE   c.condtype BETWEEN 20 AND 25 /*effdt criteria, so no need to specify column name*/
AND     c.lcrtfldnum = f.fldnum 
-- 
AND     r.oprid = c.oprid 
AND     r.qryname = c.qryname 
AND     r.selnum = c.selnum 
-- 
AND     f.oprid = c.oprid 
AND     f.qryname = c.qryname 
AND     f.selnum = c.selnum 
-- 
AND     f.oprid = r.oprid 
AND     f.qryname = r.qryname 
AND     f.selnum = r.selnum 
AND     f.recname = r.recname 
-- 
AND     d.recname = f.recname 
AND     d.fieldname = f.fieldname 
AND     BITAND(d.useedit,1) = 0 /*a non-key field*/ 

ORDER BY 1,2 
/

On my HR demo database the following delivered queries have effective date criteria on EFFDT columns that are no key columns.

OPRID    QRYNAME                        RECNAME         CORRN FIELDNAME
-------- ------------------------------ --------------- ----- ---------
         JPM_INACTIVE_CONTENT_ITEMS     JPM_JP_CRITM_VW A     EFFDT
         JPM_PERS_PROFILE               JPM_JP_CRITM_VW F     EFFDT
         JPM_PROFILE                    JPM_JP_CRITM_VW F     EFFDT
         NOT001                         FPAEEFLAT_TBL   A     EFFDT

Now, it is possible to review these queries and manually remove the effective date processing if that is appropriate.