Retrieving Bind Values from SQL Monitor in EM12c Release 4

I know others may want to know how to do this and I had challenges until Gagan Chawla, a teammate from Oracle, was kind enough to point out how to still get to this hidden, little gold nugget, so I’m posting it here for others!

Up till database plug-in 12.1.0.5, while in SQL Monitor SQL ID details page, you could click on a button called View Report and quickly view a large amount of valuable data about a SQL statement that had executed.  One of the sections in this report was binds, which listed what values were being passed for the bind variables.

If you are investigating a performance issue for the execution of a SQL statement, having bind values can give you a significant advantage.  It can tell you:

1. Is the value outside the min/max value on an existing histogram.
2. Do statistics lean towards another value being more prevalent.
3. Is the value passed in not in the correct format.
4. Does the value searched impact due it’s different from the values known and/or counts expected are off.

There are a number of other reasons, but to have this data and to have it easily accessible at your fingertips is very beneficial to the person trouble shooting.

Post the database plug-in, the feature is no longer where it once was.  From the SQL Monitoring, Monitored SQL Executions, if you were to a SQL ID of interest, you would then go to the SQL Details page.

There is a new report called “SQL Details Active Report“, but it doesn’t contain the bind values data.  This report is still very, very valuable:

It shows all the above data, along with a wait event vs. all resource usage graph at the bottom of the report.  You can save or mail the report and all it’s relevant data.  It would still be nice to have the previous report with the bind values that was once available from the details page and you can get to it, but you just need to make a few more clicks.

Go back to the main Monitored SQL Executions page and locate the SQL that you are interested in:

Bring your cursor to the status column for that SQL ID and double click.  This will take you the the Monitored SQL Executions, SQL Detail Page and on the right hand side, you will see the View Report button.

This button will bring you to the previous SQL ID Details report that includes the bind data.  Another thing to remember is that you must also be viewing a database that supports the feature, which means Oracle 11.2 or higher.