Watching SQL Execute on Oracle - Part I - Page 2
July 1, 2004
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.
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.
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."
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.
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.
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.