Watching SQL Execute on Oracle - Part I - Page 2

V$SQLAREA

When Oracle executes a query, it places it in memory. This allows Oracle to reuse the same SQL if needed by the executing session at a latter date or by another user that may need the same SQL statement. Remember that one of the steps in Oracle is the assigning of a unique SQL_HASH_VALUE and SQL_ADDRESS to each SQL statement. By Oracle doing this, it provides us a method to determine who is executing what SQL based on the join columns from the V$SESSION of SQL_ADDRESS & SQL_HASH_VALUE to the V$SQLAREA view and columns ADDRESS and HASH_VALUE. Table 2 gives another short list of columns that are of concern when we start looking at the SQL being executed by the users. There are other "statistical" columns that are provided again in the view but I have purposely only provided the two statistical columns of CPU_TIME and ELAPSED_TIME because I am getting more and more convinced that it really does not matter if we do one zillion reads in a SQL statement, what matters is that we can produce a result set under our SLAs.

Table 2.
Limited V$SQLAREA columns

In the V$SQLAREA view there is not any direct indication of who is executing the particular SQL at any given time. To get this information we must join to the V$SESSION view on HASH_VALUE and ADDRESS. Listing 2 gives the SQL to do this join, shows the active SQL executing in your database and a sample output of a "dumb" query.

Listing 2
Extracting the active SQL a user is executing

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       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 USERNAME   OPTIMIZER_MODE  HASH_VALUE ADDRESS    CPU_TIME ELAPSED_TIME
---- --------- --------------- ---------- -------- ---------- ------------
150 JKOOPMANN  ALL_ROWS        2803425422 6879D780   11923758     12106196

SQL_TEXT
--------------------------------------------------
select a.table_name from dba_tables a,dba_tables b

V$SESS_IO

Often you may have an active session and actually show a valid SQL statement through the V$SESSION and V$SQLAREA views that seems to be taking very long. Users may be complaining that their query is "stuck" or not responsive. You as a DBA can validate that the SQL they are executing is actually doing something in the database and not "stuck" be simply querying the V$SESS_IO view to determine if the query is in fact "stuck" or is actually doing work within the database. Granted, this does not mean there isn't a tuning opportunity but you can at least show the SQL is working. Table 3 shows the V$SESS_IO view and the columns associated with it. As you can see, there is an Oracle session identifier (SID) that you can link back to the V$SESSION view for the active session. If the GETS, READS, or CHANGES columns continue to increase for a session, you can be assured that the SQL statement is not "stuck."

Table 3.
I/O values for a V$SESSION connection
This view lists I/O statistics for each user session.

A very simple join, depicted in Listing 3, to the V$SESSION view will give you the results to determine the i/o being done by active sessions. As an example, I have given two executions of this SQL to show the increase in i/o for my active session.

Listing 3
I/O being done by an active SQL statement

select sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
  from v$sess_io sess_io, v$session sesion
 where sesion.sid = sess_io.sid
   and sesion.username is not null

First poll for i/o
SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---- ---------- --------------- -------------- ------------- ------------------
 150          4          470149            446             4                  0

SQL> /
Second poll for i/o
 SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---- ---------- --------------- -------------- ------------- ------------------
 150          4         1002523            448             4                  0

V$SQLTEXT

If by chance the query shown earlier in the V$SQLAREA view did not show your full SQL text because it was larger than 1000 characters, this V$SQLTEXT view should be queried to extract the full SQL. It is a piece by piece of 64 characters by line, that needs to be ordered by the column PIECE. Table 5 shows the columns that are of concern to us and Listing 5 gives the SQL to extract the SQL based on active sessions in the V$SESSION view.

Table 5
V$SQLTEXT columns of concern for show the SQL text of an executing query

Listing 5
SQL to show the full SQL executing for active sessions

select sesion.sid,
       sql_text
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece

This article should have gotten you familiar with the basics around determining who and what is being executed within your database. It is only the surface to the vast amount of information available within these views and if you have not ventured into them lately you might want to list the contents out and see what you have been missing.

» See All Articles by Columnist James Koopmann