Watching SQL Execute on Oracle - Part II
July 9, 2004
Do you have SQL running within your database? Of course you do. This article is the second in a series to introduce you to how you can extract more information about the SQL executing in your databases.
In the first of this series we looked at how to determine if sessions were running SQL from the V$SESSION & V$SESS_IO views and then how to extract the particular SQL from the V$SQLAREA & V$SQL_TEXT views. This article will take us a bit further and look deeper into SQL statements that have bind variables. We will then turn our attention to how to extract the execution plan of running (NOT static) SQL statements and how to get some better statistics about those SQL statements after they have run. Again, as with the last article, this article is not concerning itself with the tuning of SQL but rather a primer on where to get the information about your SQL statements. I have only scratched the surface of the views that are in this article and I encourage you to at least describe the views through SQL*Plus to get a glimpse of the information they contain.
This view was covered in the first part of this series and I would encourage you to go back and take a look at it. In the last article, the SQL_TEXT was just straight SQL. In this article we are going to concern ourselves with SQL that has a bind variable in it. If you look at the SQL presented in Listing 1, it is the same in part I of this series but the output shows the SQL with a bind variable. I have also added the columns V$SESSION.SERIAL#, V$SESSION.SQL_ID, and V$SESSION.SQL_CHILD_NUMBER to the query. These additional columns and their descriptions have been given in Table 1 and their use will be shown latter in this article.
When looking at the SQL statement through V$SQLAREA we only see the bind variable. If we wanted to see the contents of the variable in past versions of Oracle we would have had to issue a trace. This is no longer the case and we can use the V$SQL_BIND_CAPTURE view to look at the value hidden from normal eyes. Be aware that the ability to see the bind variable contents is contigent upon you setting the STATISTICS_LEVEL to ALL. Table 2 shows a subset of the columns available to us in the V$SQL_BIND_CAPTURE view and their descriptions. Listing 2 shows the SQL you can use to join the V$SESSION and V$SQL_BIND_CAPTURE views together to look at the contents of bind variables for currently executing SQL.