How to Find Bind Variable Values of Oracle SQL Query

A couple of ways to retrieve bind variables in real time or from history

Oracle database captures a SQL query bind variables and their  values periodically, and the default capture bind variables interval is 900 seconds(15 minutes), which is modifiable but facing performance concerns.

Parameter Description                 Session Value  Instance Value
------------------------------------- -------------- --------------- 
_cursor_bind_capture_interval              900           900
interval(in seconds) between two bind 
capture for a cursor
SQL>alter system set "_cursor_bind_capture_interval"=30 scope=both;

Oracle intends to minimize the impact on database performance, and makes the capturing interval a pretty big value( like 15 minutes). So it is not a very reliable method to retrieve bind variables from Oracle database views.

Please note you need have licenses of “Oracle Diagnostics & Tuning Packs” if you query dba_hist_****** and v$sql_monitor in this post.

There are a couple of ways to retrieve bind variables in real time or from history.

Subscribe to get access

Read more of this content when you subscribe today.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.