Measuring Network Latency with the PeopleSoft Performance Monitor Transaction 116

One of the things I am using this blog for is as an addendum to the book. When I wrote the chapter about performance monitor, I had never used it in a real production scenario. Since publication, I have learnt a lot more about it. One of the things that I missed was transaction 116, 'Redirected round trip time (network latency)'.

When you log into the PIA the browser, the browser is redirected to another page. I understand this transaction to measure the time taken for the login page with the redirection tag to go from the web server to browser, to be processed on the browser, and for the browser to return the page to which it was redirected. Thus it measures the latency of a return trip on the network. This and PeopleSoft Ping are the only metrics that PeopleSoft generates that indicate the performance of network between the web server and a user's workstation.

This transaction holds various pieces of information about the user's PC, including its IP address (although that might be the address of a firewall or load balancer), the user's Session ID on the web server, and the user agent information, from which you can determine the browser and version, and the operating system of their workstation.

So now, if you know where a user is physically located, you may be able to detect a correlation between location and the round trip during. One limitation is that this transaction is only collected when a user logs in, and so you may not collect data points when you need them.

Migrating DDL Overides with Application Designer

Here is a simple tip, but I (probably because I don't often migrate PeopleSoft projects myself) think it is rather significant.

Ideally, if you a setting non-default storage options such as PCTFREE on tables or indexes, of if you are compressing indexes, then you should use DDL Overrides in PeopleSoft Application Designer to manage these options so that when you build a build script, it contains the correct options. Otherwise, these settings could be lost when a build scripts is run.

However, but default the Application Designer does migrate these DDL overrides. When you are about to copy a project select the copy options, and under the 'General Options' tab select the 'Take DDL from Source' radio button.

Unfortunately, Application Designer does not remember this option, and you need to remember to set it every time you migrate a project.

So now you can put DDL overrides into development environemt and migrate them through into production

Performance Tuning the Performance Monitor

I am a big fan of the PeopleSoft Performance Monitor, but the more often I use it I discover it has a few rough edges. Some of the analytics components do not perform well when their is either a large amount of data in the PSPMTRANSHIST table, or when there are a lot of agents in a system. So I have used the Performance Monitor to trace the Performance Monitor
components.

You have to configure it to self-monitor, ignoring all the warning messages.

But, you are not interested in the sampled statistics on this database, so disable monitoring by setting the filter level on all agents to standby.

Instead enable Performance Trace with either Verbose or Debug
The component trace indicates which analytic queries take the most time.

I have changed and added the following indexes to the performance tables.

CREATE INDEX PSDPSPMEVENTHIST ON PSPMEVENTHIST
(PM_EVENT_DEFN_SET, PM_EVENT_DEFN_ID, PM_AGENTID, PM_AGENT_DTTM)
TABLESPACE PSINDEX ... PCTFREE 1 COMPRESS 3
/
CREATE INDEX PSCPSPMTRANSHIST ON PSPMTRANSHIST
(
PM_TRANS_DEFN_SET,PM_TRANS_DEFN_ID, PM_PERF_TRACE,PM_METRIC_VALUE7
/*,PM_MON_STRT_DTTM */
) TABLESPACE PSINDEX ... PCTFREE 1 COMPRESS 4
/
CREATE INDEX PSDPSPMTRANSHIST ON PSPMTRANSHIST
(PM_TOP_INST_ID, PM_TRANS_DEFN_SET, PM_TRANS_DEFN_ID)
TABLESPACE PSINDEX ... PCTFREE 1
COMPRESS 3
/

The Performance Monitor tables (PSPMTRANSHIST, PSPMTRANSARCH, PSPMEVENTHIST, PSPMEVENTARCH) are never updated. The performance collator application server process (PSPPMSRV) inserts data into the performance monitor tables, and later the performance monitor archive Appliction Engine process (PSPM_ARCHIVE) will insert them into the %ARCH tables, and delete them from the %HIST tables. Therefore, it is sensible to pack the data by minimising free space in these tables, and so reduce both logical and phyiscal I/O. So
I have reduced the free space in the data blocks (PCTFREE) to 0% on tables and indexes, and increased PCTUSED to 99%.

I have also found it beneficial to collect histograms on certain columns

BEGIN
sys.dbms_stats.gather_table_Stats
(ownname => 'SYSADM'
,tabname => 'PSPMTRANSHIST'
,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt => 'FOR COLUMNS PM_PERF_TRACE, PM_TRANS_DEFN_ID,
pm_metric_value7'
,cascade => TRUE
);
END;
/

If you have performance problems with Performance Monitor, remember that you can also use Performance Monitor to analyse its own analytics.

Using DDL Triggers to protect database objects not managed by Application Designer

Sometimes it is necessary create certain database objects manually, and not manage them via the Application Designer. However, this can introduce some management problems. When an object is altered in the Application Designer, it is then necessary to build an alter script that may rebuild the whole table. It is easy for the additional objects to be accidentally lost. There are two main scenarios.

• Additional indexes and Function based indexes: During the course of performance tuning it is often necessary to build additional indexes. Ideally these indexes should be added via the Application Designer. However, a DBA might add an index directly to the production environment, and it may take time to get a project moved into that environment. The other scenario, is that the Application Designer cannot build function-based indexes. These are particularly useful to build upper case indexes on columns, to support case-insensitive searching.
• PeopleSoft delivers a mechanism to build triggers to perform DML auditing. However, if you then rebuild the underlying table, then the trigger will be lost, and there is nothing PeopleSoft to warn you of this, or to audit this situation.

My solution to both scenarios is to create a DDL trigger to prevent accidental ALTER and DROP commands on objects not defined by PeopleTools. When you do want to alter this objects you can then disable this trigger. So you now have a way of controlling when you can and can't drop additional triggers and indexes. You still have to remmeber to switch this trigger back on again!

CREATE OR REPLACE TRIGGER t_lock 
BEFORE DROP OR ALTER
ON SYSADM.SCHEMA
DECLARE
 l_generate_message EXCEPTION;
 l_recname VARCHAR2(15 CHAR);
 l_msg VARCHAR2(100 CHAR) := 'No Message.';
 l_msg2 VARCHAR2(100 CHAR) := 'Cannot '||ora_sysevent||' '||lower(ora_dict_obj_type)||' '||ora_dict_obj_owner||'.'||ora_dict_obj_name;
BEGIN
 IF ora_dict_obj_type = 'TABLE' THEN
  SELECT r.recname
  INTO   l_recname
  FROM   psrecdefn r
  WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = ora_dict_obj_name
  ;

  BEGIN
   SELECT 'Trigger '||t.trigger_name||' exists on table '||ora_dict_obj_name||'.'
   INTO   l_msg
   FROM   all_triggers t
   WHERE  ROWNUM = 1
   AND    t.table_name    = ora_dict_obj_name
   AND    t.table_owner   = ora_dict_obj_owner
   AND    t.trigger_name != 'PSU'||l_recname
   ;
   RAISE l_generate_message;
  EXCEPTION
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

  BEGIN
   SELECT 'Index '||i.index_name||' on table '||ora_dict_obj_name||' is managed outside PeopleTools.'
   INTO   l_msg
   FROM   all_indexes i
   WHERE  ROWNUM = 1
   AND    i.table_name  = ora_dict_obj_name
   AND    i.table_owner = ora_dict_obj_owner
   AND NOT EXISTS(
    SELECT 'x'
    FROM   psrecdefn r, psrecfielddb p, psrecfield f, psindexdefn j
    WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
    AND    r.recname = p.recname
    AND    p.fieldname = f.fieldname 
    AND    (f.recname = p.recname_parent
     OR     (MOD(f.useedit/1,2)=1 /*key*/ 
     OR     MOD(f.useedit/2,2)=1 /*dup*/ 
     OR     MOD(f.useedit/16,2)=1 /*alt*/))
    AND    j.recname = p.recname_parent
    AND    'PS'||j.indexid||r.recname = i.index_name
   )
   ;
   RAISE l_generate_message;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

 ELSIF ora_dict_obj_type = 'TRIGGER' THEN

  BEGIN
   SELECT 'Trigger '||t.trigger_name||' exists on PeopleSoft record '||r.recname||'.'
   INTO   l_msg
   FROM   all_triggers t, psrecdefn r
   WHERE  ROWNUM = 1
   AND    t.trigger_name = ora_dict_obj_name
   AND    t.owner        = ora_dict_obj_owner
   AND    t.table_owner  = ora_dict_obj_owner
   AND    DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = t.table_name
   AND    t.trigger_name != 'PSU'||r.recname
   ;
   RAISE l_generate_message;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

 ELSIF ora_dict_obj_type = 'INDEX' THEN

  BEGIN
   SELECT 'Index '||i.index_name||' on table '||ora_dict_obj_name||' is managed outside PeopleTools.'
   INTO   l_msg
   FROM   all_indexes i
   WHERE  ROWNUM = 1
   AND    i.index_name = ora_dict_obj_name
   AND    i.owner      = ora_dict_obj_owner
   AND NOT EXISTS(
    SELECT 'x'
    FROM   psrecdefn r, psrecfielddb p, psrecfield f, psindexdefn j
    WHERE  DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) = i.table_name
    AND    r.recname = p.recname
    AND    p.fieldname = f.fieldname 
    AND    (f.recname = p.recname_parent
     OR     (MOD(f.useedit/1,2)=1 /*key*/ 
     OR     MOD(f.useedit/2,2)=1 /*dup*/ 
     OR     MOD(f.useedit/16,2)=1 /*alt*/))
    AND    j.recname = p.recname_parent
    AND    'PS'||j.indexid||r.recname = i.index_name
   )
   ;
   RAISE l_generate_message;
  EXCEPTION 
   WHEN NO_DATA_FOUND THEN 
    NULL;
  END;

 END IF;

EXCEPTION
 WHEN NO_DATA_FOUND THEN 
  NULL;
 WHEN l_generate_message THEN
  Raise_application_error(-20042,'T_LOCK: '||l_msg||'  '||l_msg2);
END;
/

Notes:

• The trigger performs a number of test queries on PeopleTools or Oracle Catalogue views. There are different queries depending upon the type of object being ALTERed or DROPped. If a query returns a row then there is a problem, and an exception is raised. The queries also generate part of the error message that is returned.
• The trigger only affects operations on tables that are specified in PSRECDEFN as type 0 or 7 records. I have not added an handling for the additional instances of a PeopleSoft temporary table.
• The ora_% variables are described in the RDBMS documentation in Application Developer's Guide - Fundamentals. In the 9.2 documentation this is in chapter 16. Working with System Events Event Attribute Functions

So here are a few tests. I have created a function based index and a two triggers on PS_RT_RATE_TBL. I have chosen this table for the example because it also has a PeopleSoft generated trigger for Mobile agents.

CREATE INDEX DMK_RT_RATE_TBL 
ON PS_RT_RATE_TBL(UPPER(RT_RATE_INDEX));
Index created.
CREATE OR REPLACE TRIGGER pstrt_rate_tbl
AFTER INSERT OR UPDATE OR DELETE
ON ps_rt_rate_tbl
FOR EACH ROW
BEGIN
 raise_application_error(-20042,'No DML on PS_RT_RATE_TBL');
END;
/
Trigger created.
CREATE OR REPLACE TRIGGER psurt_rate_tbl
AFTER INSERT OR UPDATE OR DELETE
ON ps_rt_rate_tbl
FOR EACH ROW
BEGIN
 raise_application_error(-20042,'No DML on PS_RT_RATE_TBL');
END;
/
Trigger created.

So the t_lock trigger prevents me from droping or altering either the function based index or the table.

DROP TABLE PS_RT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on table PS_RT_RATE_TBL. Cannot DROP table
SYSADM.PS_RT_RATE_TBL
ORA-06512: at line 99

ALTER TABLE PS_RT_RATE_TBL RENAME to DMK
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on table PS_RT_RATE_TBL. Cannot ALTER table
SYSADM.PS_RT_RATE_TBL
ORA-06512: at line 99

DROP INDEX DMKZRT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Index DMKZRT_RATE_TBL is managed outside PeopleTools. Cannot DROP index
SYSADM.DMKZRT_RATE_TBL
ORA-06512: at line 99 

But I can drop any other index on the table that is maintained by PeopleTools.

DROP INDEX PS_RT_RATE_TBL
Index dropped. 

I can't alter any trigger on the table except the PSU trigger that is created by Application Designer for objects that are maintained by mobile agents.

ALTER TRIGGER PSTRT_RATE_TBL DISABLE
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on PeopleSoft record RT_RATE_TBL. Cannot ALTER trigger
SYSADM.PSTRT_RATE_TBL
ORA-06512: at line 99

DROP TRIGGER PSTRT_RATE_TBL
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20042: T_LOCK: Trigger PSTRT_RATE_TBL exists on PeopleSoft record RT_RATE_TBL. Cannot DROP trigger
SYSADM.PSTRT_RATE_TBL
ORA-06512: at line 99

ALTER TRIGGER PSURT_RATE_TBL DISABLE;
Trigger altered.

DROP TRIGGER PSURT_RATE_TBL;
Trigger dropped.

%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)*/ …

%CurrentDateIn MetaSQL prevents Oracle CBO from correctly evaluating selectivity of predicate (up to Oracle 9i)

I thought this might be an interesting subject for the first post to a new blog.

Update 6.2.2009: The problem described in this posting is not an issue in Oracle 10g - see Changes in Calculation of Predicate Selectivity in Oracle 10g

I recently discovered the Oracle Cost Based Optimizer choosing a poor execution plan for a particular critical SQL statement in a CRM system because the expansion of the %CurrentDateIn macro is excessively complicated. The problem occurs in a delivered view PS_RBC_PACKAGE_VW and and a custom view PS_XX_RBCPKCLTR_VW. The views both contain a pair of date conditions, which are coded in line with PeopleSoft standards.

AND A.FROM_DATE <= %CurrentDateIn AND A.TO_DATE >= %CurrentDateIn
AND E.FROM_DATE <= %CurrentDateIn AND E.TO_DATE >= %CurrentDateIn

On an Oracle RDBMS, this expands to

AND A.FROM_DATE <= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’) AND A.TO_DATE >= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’)
AND E.FROM_DATE <= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’) AND E.TO_DATE >= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’)

With the result that this statement took over 15 seconds to execute. However, if the view is recoded as follows

AND A.FROM_DATE <= TRUNC(SYSDATE) AND A.TO_DATE >= TRUNC(SYSDATE)
AND E.FROM_DATE <= TRUNC(SYSDATE) AND E.TO_DATE >= TRUNC(SYSDATE)

Then the execution time fell to less than 1 seconds.

To explain why, I shall use a very simple example that is easy to reproduced. In the following script, I have created a table with a 1000 rows and a few columns. Column B is just for padding so that the rows are not unrealistically small. Columns C, D and E contain some dates. The data is evenly distributed in C and D. In E the distribution is deliberately uneven, there are more dates further in the past.

DROP TABLE t1;
CREATE TABLE t1
(a NUMBER NOT NULL
,b VARCHAR2(2000) NOT NULL
,c DATE NOT NULL
,d DATE NOT NULL
,e DATE NOT NULL);

INSERT INTO t1
SELECT rownum
, RPAD(TO_CHAR(TO_DATE(rownum,'J'),'Jsp'),1000,'.') padd
, SYSDATE-rownum+4.2
, SYSDATE-rownum+42
, SYSDATE-sqrt(rownum)+4.2
FROM dba_objects
WHERE rownum <= 1000 ; 

Lets look at the execution plans of a few simple SQLs. Both of the following queries return 4 rows, but the cardinality is very different. In the first statement I have used just the simple TRUNC(SYSDATE), the optimizer has correctly worked out that the query will return 4 rows. However, in the second I have used the expansion of the %CurrentDateIn macro. Because the predicate contains a function, the optimizer uses a hard coded guess that the selectivity of the condition is 5% of the table.

SELECT * FROM t1 WHERE c > TRUNC(SYSDATE);
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=4 Bytes=4096)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=4 Bytes=4096)

SELECT * FROM t1 WHERE c > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=50 Bytes=51200)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=50 Bytes=51200)

If I repeat the queries on column D, the cardinality goes up to 41 (it will in fact return 42 rows, but its close), but the cardinality is still 50.

SELECT * FROM t1 WHERE d > TRUNC(SYSDATE);
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=41 Bytes=41984)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=41 Bytes=41984)

SELECT * FROM t1 WHERE d > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=50 Bytes=51200)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=50 Bytes=51200)

If I now try column E, the calculated cardinality the calculated is now 115, although the queries actually return 22 rows. The optimizer makes a mistake because the data values are not evenly distributed between the high and low values for the column.

SELECT * FROM t1 WHERE e > TRUNC(SYSDATE);
Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=115 Bytes=117760)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=115 Bytes=117760)

SELECT * FROM t1 WHERE e > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');

Execution Plan
----------------------------------------------------------
  0 SELECT STATEMENT Optimizer=CHOOSE (Cost=257 Card=50 Bytes=51200)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=257 Card=50 Bytes=51200)

So this test illustrates that the expansion of %CurrentDateIn prevents Oracle’s CBO from evaluating the selectivity correctly, and that this causes it to use a guess that is sometimes too high, and sometimes too low, but always incorrect. In some cases this will lead to an inappropriate execution plan. If TRUNC(SYSDATE) were used, the optimizer would have better information about the SQL and be able to make better decisions.
I say that this is a bug in PeopleTools. There is, of course, a simple workaround. Simply code TRUNC(SYSDATE) in the application as a customisation where necessary. However, there is no reason why the expansion of this metaSQL could not be changed, and delivered as a PeopleTools patch.