How to make PSQuery issue literal values for prompt values (Doc ID 1915111.1)

Last updated on JANUARY 10, 2017

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.44 to 8.54 [Release 8.4]
Information in this document applies to any platform.

Goal

SQL queries generated by PS Queries are typically issued with bind variable as place holders for the prompted values.  This technique allows the optimizer to reuse an existing plan in an effort to reduce compile time.  Often the same PS Query is used to select vastly different sized data using different prompt values, e.g. different date ranges.  When this occurs, the plan compiled initially may perform poorly for the subsequent prompt values.

This document explains a way to make PSQuery issue literal values for the prompted values.  This technique is valuable in order to force the database optimizer to recompile the execution plan and thus avoid problems related to the use of bind variables that results in a single plan being used on a wide range of data volumes.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms