Custom Alert - Collect Bind Values

Description

Sometimes a SQL statement will execute differently based on the bind value that gets pass in. To configure Ignite to collect a sampling of those bind values, this alert can be used to reterive the values and store them in the Ignite alert tables. This alert will never send an email, but is used to collect the data only. At the bottom of this page is a query that can be used to report on this information from the Ignite alert tables.

Alert Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

Alert Name: Collect Bind Values

Execution Interval: 15 minutes  -- the v$sql_bind_capture table only gets populated once every 15 minutes or so

Notification Text: Empty

SQL Statement:

select 'SQL_ID:' || sql_id || ', Bind:' || name || ', Captured:' || to_char(last_captured,'mm/dd/yy hh24:mi:ss') || ', Value:'||value_string, 0

from v$sql_bind_capture

where sql_id in ('c13sma6rkr27c', '8dq0v1mjngj7t', '7hk2m2702ua0g')

Note: Modify the where clause to include the SQL statements you are interested in.

Execute Against: Monitored Instance

Units: Empty

High Threshold: Min -2, Max -1 -- alert will not fire, but use these as a defaults

SQL Statement to Report on Bind Values

Note: this query retrieves data collected by this alert for the last 14 days. If the alert name was changed from the default listed above, change the "where a.alertname" part of this query as well.

select ah.dbname instance_name, ah.actiondate timestamp,

       ahr.parametername bind_info, ahr.levelvalue bind_value

from con_alert a

inner join con_alert_history ah on ah.alertid=a.id

inner join con_alert_history_results ahr on ahr.historyid=ah.historyid

where a.alertname='Collect Bind Values'

and ah.actiondate >= current_timestamp - 14.0

order by ah.dbname, ah.actiondate, ahr.parametername