%CurrentDateIn MetaSQL prevents Oracle CBO from correctly evaluating selectivity of predicate.

The cost based optimiser chooses a poor execution plan for a particular critical SQL statement in CRM because the expansion of the %CurrentDateIn macro is excessively complicated. The problem occurs in a delivered view PS_RBC_PACKAGE_VW and a related 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 second.

There is, of course, a simple workaround. TRUNC(SYSDATE) is functionally equivalent to

TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')

and can simply be coded into the application as a customisation where necessary.   

http://www.go-faster.co.uk/PSFT.metasql.20060425.pdf