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.

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







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers