Watching SQL Execute on Oracle – Part II

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.

V$SQLAREA

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.

Listing 1

Extracting the active SQL a user is executing

select sesion.sid,
       sesion.serial#,
       sesion.username,
       sesion.sql_id,
       sesion.sql_child_number,
       optimizer_mode,
       hash_value,
       address,
       sql_text
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null
Active session and the SQL it is executing
SID SERIAL# USERNAME  SQL_ID        SQL_CHILD_NUMBER OPTIMIZER_MODE  HASH_VALUE ADDRESS
— ——- ——— ————- —————- ————–  ———- ——–
149       8 JKOOPMANN 5qk509xugpmpv                1     FIRST_ROWS  1962593979 69A7558C
SQL_TEXT
————————————————–
SELECT count(*) FROM sys.dba_tables WHERE owner = :1

Table 1.

Additional
V$SESSION column information

V$SQL_BIND_CAPTURE

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.

Table 2

V$SQL_BIND_CAPTURE
column information

Listing 2

Extracting the bind variable contents for SQL that is
executing

select sesion.sid,
       sesion.username,
       sesion.sql_id,
       sesion.sql_child_number,
       sql_bind_capture.name,
       sql_bind_capture.value_string
  from v$sql_bind_capture sql_bind_capture, v$session sesion
 where sesion.sql_hash_value = sql_bind_capture.hash_value
   and sesion.sql_address    = sql_bind_capture.address
   and sesion.username is not null
Active session and the SQL it is executing
 SID USERNAME     SQL_ID        SQL_CHILD_NUMBER NAME VALUE_STRING
—- ———— ————- —————- —- ————
 149 JKOOPMANN    5qk509xugpmpv                1 :1   ORDEROWNER