Using DBMS_XPLAN.DISPLAY_CURSOR to Identify Bind Values Used During Parse
(Doc ID 416324.1)
Last updated on OCTOBER 30, 2019
Applies to:Oracle Database - Enterprise Edition - Version 10.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document