Oracle - Collect Bind Value Samples

Description

Sometimes a SQL statement will execute differently based on the bind value that gets pass in. To collect a sampling of those bind values, this alert can be used to retrieve the values and store them in the DPA 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 DPA 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 as needed and paste in the attached query.

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