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.
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
select 'SQL_ID:' || sql_id || ', Bind:' || name || ', Captured:' || to_char(last_captured,'mm/dd/yy hh24:mi:ss') || ', Value:'||value_string, 0
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
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