Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jul 1, 2004

Watching SQL Execute on Oracle - Part I

By James Koopmann

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


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date