Watching SQL Execute on Oracle – Part I

Do you have SQL running within your database? Of course you do.
This article is the first in a series to introduce you to a method of finding information
about the SQL your users are executing in your databases.

Your ability as a DBA to detect who is accessing the
database and the SQL they are executing is paramount in your ability to
properly manage and give feedback on the type of work your database is
requested to do. This article will begin our series with an introduction to
determining who is logged into your database and what SQL they are executing.
This article is not concerning itself with the tuning of SQL but rather a
primer so that you can get familiar with or re-acquainted with the underlying
tables within Oracle that give information on who and what is being done around
the SQL.

V$SESSION

The V$SESSION is often the jumping off place
to determine who is logged into the database and a high level overview of what
they are doing. In Table 1 I have given a subset of the columns
this view contains. While there are other columns in this view that relate to
operating system information and the application being executed, I am only
presenting those columns that give us the basis for our jumping off point to
determine that this is in fact an active user and also the columns we will need
later to join to the actual SQL being executed.

Table 1.

Limited V$SESSION
information

Column

Description

SADDR

Identifies a unique Oracle
session address

SID

Identifies a unique Oracle
session

USERNAME

The Oracle user (same as
from dba_users)

STATUS

Tells us the status of the
session. We will be concerned with ACTIVE sessions, those that are executing
SQL

PROCESS

This is the operating
system process id for the connection. Only given here as a reference so that
you can go look on the O/S side.

TYPE

The type of session
connected to the database

SQL_ADDRESS

Used with SQL_HASH_VALUE to identify the SQL statement that is currently
being executed.

SQL_HASH_VALUE

Used with SQL_ADDRESS to identify the SQL statement that is currently
being executed. This SQL_HASH_VALUE is unique, or should be unique, to the
same SQL statement no matter when it is executed. Thus ‘select * from dual’
will always produce the same SQL_HASH_VALUE.

In order to extract the information, you can issue a SQL
statement such as the following in Listing 1. This is a very simplistic
statement but gives us all the information we need to determine if the user is
actually executing SQL at the time. If a user has SQL that is executing, the
status column will be ACTIVE and the SQL_ADDR & SQL_HASH_VALUE will be
populated. I have given the output of two different executions of this
particular SQL against the V$SESSION view. The first shows where a user was
noticed logged into our database but is basically inactive and there is no SQL
address or hash value. The second execution of this SQL shows a user who is
actively executing SQL, denoted by the values in the address and hash value
columns.

Listing 1.

Extracting simplistic columns to show sessions that are
executing SQL


select sid,
to_char(logon_time,’MMDDYYYY:HH24:MI’) logon_time,
username,
type,
status,
process,
sql_address,
sql_hash_value
from v$session
where username is not null

Inactive session with no SQL executing
SID LOGON_TIME USERNAME TYPE STATUS PROCESS SQL_ADDR SQL_HASH_VALUE
—- ————– ——— —- ——– ———— ——– ————–
150 06252004:06:23 JKOOPMANN USER INACTIVE 3528:3036 00 0

Active session with SQL executing
SID LOGON_TIME USERNAME TYPE STATUS PROCESS SQL_ADDR SQL_HASH_VALUE
—- ————– ——— —- ——– ———— ——– ————–
150 06252004:06:23 JKOOPMANN USER ACTIVE 3528:3036 6879D780 2803425422

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