兩隻小虎,一隻豬

資料庫的效能不佳,很多時候是因為沒有效率的SQL Statement 造成,

通常可以透過V$SQLAREA 來查詢SQL Statement, 不過V$SQLAREA 所查詢到的SQL Statement 有長度的限制,因此當SQL Statement 過長時就必須改查 V$SQLTEXT, 除了查詢目前正在執行的SQL Statment之外,還透過V$SESSION , V$SESS_IO 來知道一些其他的資訊

1. V$SESSION

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.

2. V$SQLAREA

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

3.V$SESS_IO

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

4.V$SQLTEXT

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

---

1. Create Function

create or replace function FULL_QUERY_SQLTEXT( 
                                          p_address in v$sqltext.address%type, 
                                          p_hash_value in v$sqltext.hash_value%type 
                                         )  return varchar2
as
            l_text  long;
begin
          for x in ( select sql_text from v$sqltext 
                         where address = p_address and hash_value = p_hash_value 
                         order by piece )
          loop
                  l_text := l_text || x.sql_text;
                  exit when length(l_text) > 10000;
          end loop;
          return substr( l_text, 1, 10000 );
  end;
/

2. Query Full SQL Statment

WITH BASE AS (
   SELECT   SE.SID , SE.SERIAL# , 
           TO_CHAR(SE.LOGON_TIME , 'YYYY-MM-DD HH24:MI' ) LOGON_TIME ,
            SE.USERNAME , SE.TYPE , SE.STATUS , SE.PROCESS,
            SE.SQL_ADDRESS , SE.SQL_HASH_VALUE ,
            SA.OPTIMIZER_MODE , SA.HASH_VALUE , SA.ADDRESS , 
            SA.CPU_TIME , SA.ELAPSED_TIME , SA.SQL_TEXT ,
            SI.BLOCK_GETS , SI.CONSISTENT_GETS , SI.PHYSICAL_READS ,
            SI.BLOCK_CHANGES , SI.CONSISTENT_CHANGES ,
            ST.ADDRESS AS ST_ADDRES , ST.HASH_VALUE AS ST_HASH_VALUE,  
            ST.PIECE , ST.SQL_TEXT 
   FROM     V$SESSION SE , V$SQLAREA SA , V$SESS_IO SI , V$SQLTEXT ST
   WHERE    SE.USERNAME IS NOT NULL AND SE.STATUS='ACTIVE' 
            AND SE.SQL_HASH_VALUE = SA.HASH_VALUE
            AND SE.SQL_ADDRESS =SA.ADDRESS
            AND SE.SID = SI.SID
            AND SE.SQL_HASH_VALUE = ST.HASH_VALUE
            AND SE.SQL_ADDRESS = ST.ADDRESS
   ORDER BY SE.SID , SE.SERIAL#  ,ST.PIECE                
)  
SELECT   DISTINCT SID,SERIAL# , LOGON_TIME , 
         USERNAME , PROCESS , CPU_TIME , ELAPSED_TIME ,
         BLOCK_GETS ,  CONSISTENT_GETS ,  PHYSICAL_READS ,
         BLOCK_CHANGES ,   CONSISTENT_CHANGES , 
         FULL_QUERY_SQLTEXT (ST_ADDRES,ST_HASH_VALUE) SQL_QUERY
FROM  BASE

Reference

http://www.databasejournal.com/features/oracle/article.php/3373701/Watching-SQL-Execute-on-Oracle---Part-I.htm
http://www.databasejournal.com/features/oracle/article.php/10893_3373701_2/Watching-SQL-Execute-on-Oracle---Part-I.htm