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
文章標籤
全站熱搜
留言列表
學習筆記 (8)
