close

資料庫的效能不佳,很多時候是因為沒有效率的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

arrow
arrow
    文章標籤
    [學習筆記][Oracle]Database
    全站熱搜

    rickyju 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄