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

Last updated on AUGUST 11, 2022

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.