Watching SQL Execute on Oracle - Part I - Page 2

July 1, 2004



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

Column

Description

SQL_TEXT

This is the first 1000 characters of the SQL being executed by the user. If more than 1000 characters, you should use V$SQL_TEXT which is described latter in the article.

OPTIMIZER_MODE

The optimizer mode being utilized by the query

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

CPU_TIME

The accumulated microseconds of CPU time used by the SQL

ELAPSED_TIME

The accumulated microseconds elapsed time used by the SQL

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.

Column

Description

SID

Identifies a unique Oracle session

BLOCK_GETS

Number of block gets done

CONSISTENT_GETS

Number of consistent gets done

PHYSICAL_READS

Number of physical reads done

BLOCK_CHANGES

Number of blocks that where changed

CONSISTENT_CHANGES

Number of consistent block changes done

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

Column

Description

ADDRESS

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

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.

PIECE

A sequential number used to piece individual parts of the SQL statement together

SQL_TEXT

The individual piece of SQL text

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








The Network for Technology Professionals

Search:

About Internet.com

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