Using DBMS_XPLAN.DISPLAY_CURSOR to Identify Bind Values Used During Parse
Last updated on SEPTEMBER 29, 2017
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Information in this document applies to any platform.
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.
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