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

Column

Description

SERIAL#

Along with SID uniquely identifies a session and the
objects it uses.

SQL_ID

An identifier to the SQL currently executing.

SQL_CHILD_NUMBER

A child number of a SQL statement that is currently being
executed

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

Column

Description

ADDRESS

This is the address to the
parent of this cursor/sql

HASH_VALUE

This is the hash value to
the parent statement in the library cache

NAME

The name of the bind
variable.

VALUE_STRING

Value of the bind variable

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles