Using DBMS_XPLAN.DISPLAY_CURSOR to Identify Bind Values Used During Parse (Doc ID 416324.1)

Last updated on OCTOBER 30, 2019

Applies to:

Goal

In 9.2 and later versions, the cost-based optimizer peeks bind variables when parsing a query and, taking the peeked values and available statistics into consideration, generates an execution plan which is optimal for the bind values used at that time. However, this same execution plan may not be so optimal for subsequent executions of the same cursor with different bind values. Because of cursor sharing, the customer may see very different performance for the same query depending on the bind values used when it was parsed and the bind values used on subsequent executions.

For this reason, it is often necessary to see the execution plan along with the bind values peeked when the query was parsed to review and diagnose query tuning problems. Views such as v$sql_bind_data and v$sql_bind_capture periodically capture the bind values used when the query is executed and so will not retain the bind values used at parse time. This note shows how, in 10.2 and greater, to display the execution plan including peeked values for bind variables.

Solution

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