How to obtain the variable value bound to an oraclesql statement

How to obtain the variable value bound to an oraclesql statement

When diagnosing SQL Performance problems, we sometimes need to obtain the actual value of the variable it binds, and then bring the actual value into the SQL statement, use the original SQL statement to form a select statement (with the where condition). Execute the statement to check the selectivity.

This document describes how to obtain the bound variable value. Thanks for the help of elders who have prepared this article.

This article applies to databases with oracle 10 Gb or later.

Alter session set nls_date_format = 'yyyy-mm-dd, hh24: mi: ss '; set linesize 400 col SQL _Id format a20 col name format a20 col datatype_string format a14 col value_string format a20 -- This SQL statement reads the bound variable value information from the memory. If it is not in the memory, select SQL _id, name, datatype_string, last_captured, value_string from v $ SQL _bind_capture where SQL _id = 'dxfcacn4t4ppw' order by LAST_CAPTURED, POSITION; -- This SQL statement reads binding variable value information from awr select instance_number, SQL _id, name, datatype_string, last_captured, value_string from dba_hist_sqlbind where SQL _id = 'hangzhou' order by LAST_CAPTURED, POSITION;