Oracle 針對當前等待事件的trace步驟

這裡只總結最common的步驟
----------------------------------------

1.通過作業系統的命令找到系統資源的bottleneck，如:CPU, Memory, I/O, Network
  同時主要關注IOWait, PI/PO, Memory的使用情況

2.通過查詢v$sys_time_model來觀察資料庫系統整體在基於time的標準上整體的執行狀況.
SQL> select * from v$sys_time_model;

   STAT_ID STAT_NAME                           VALUE
---------- ------------------------------ ----------
3649082374 DB time                        9772556023
2748282437 DB CPU                            6045546
4157170894 background elapsed time          94505855
2451517896 background cpu time              22728949
4127043053 sequence load elapsed time           4797
1431595225 parse time elapsed               16369523
 372226525 hard parse elapsed time          15624250
2821698184 sql execute elapsed time       9780485995
1990024365 connection management call ela     333283
           psed time

1824284809 failed parse elapsed time            1633
4125607023 failed parse (out of shared me          0
           mory) elapsed time

3138706091 hard parse (sharing criteria)      294918
           elapsed time

 268357648 hard parse (bind mismatch) ela        412
           psed time

2643905994 PL/SQL execution elapsed time     2007111
 290749718 inbound PL/SQL rpc elapsed tim          0
           e

1311180441 PL/SQL compilation elapsed tim     910380
           e

 751169994 Java execution elapsed time             0
1159091985 repeated bind elapsed time          24014
2411117902 RMAN cpu time (backup/restore)          0

這裡我們看到在DB time中大部分的時間花在了sql execute elapsed time指標上，記錄下stat_id=2821698184

3.查詢v$sess_time_model檢視來獲得具體的session等待資訊.
SQL> select sid,stat_name,value from v$sess_time_model where stat_id=2821698184;

       SID STAT_NAME                           VALUE
---------- ------------------------------ ----------
       144 sql execute elapsed time       7122395220
       146 sql execute elapsed time                0
       148 sql execute elapsed time                0
       150 sql execute elapsed time            53570
       151 sql execute elapsed time             1387
       155 sql execute elapsed time                0
       158 sql execute elapsed time                0
       160 sql execute elapsed time                0
       161 sql execute elapsed time                0
       162 sql execute elapsed time                0
       163 sql execute elapsed time                0
       164 sql execute elapsed time                0
       165 sql execute elapsed time                0
       166 sql execute elapsed time                0
       167 sql execute elapsed time                0
       168 sql execute elapsed time                0
       169 sql execute elapsed time                0
       170 sql execute elapsed time                0

這裡我們又可以看到，大部分的時間花在了sid=144這個session中。執行時間=7122395220(微秒)
這麼長的執行時間，看看是否有等待發生

4.查詢v$session或v$session_wait檢視來獲得實際的執行事件event資訊。
SQL> select sid,event,wait_time,seconds_in_wait,state,wait_class from v$session_
wait where sid=144;

       SID EVENT                                                             WAI
T_TIME SECONDS_IN_WAIT STATE               WAIT_CLASS
---------- ---------------------------------------------------------------- ----
------ --------------- ------------------- -------------------------------------
---------------------------
       144 enq: TX - row lock contention
     0            7123 WAITING             Application

可以得知session 144正在經歷著鎖等待的事件。
wait_time 表示: A zero value means the session is currently waiting.
seconds_in_wait 表示: If WAIT_TIME = 0, then SECONDS_IN_WAIT is the seconds spent in the current wait condition. If WAIT_TIME > 0, then SECONDS_IN_WAIT is the seconds since the start of the last wait, and SECONDS_IN_WAIT - WAIT_TIME is the active seconds since the last wait ended

當然也可以查詢v$session_wait_class來查詢具體的wait_class資訊：
SQL> select wait_class_id,wait_class,total_waits,time_waited from v$session_wait
_class
  2  where sid=144;

WAIT_CLASS_ID WAIT_CLASS                                                       T
OTAL_WAITS TIME_WAITED
------------- ---------------------------------------------------------------- -
---------- -----------
   1893977003 Other
         2           0
   4217450380 Application
      2670      799742
   3386400367 Commit
         3           0
   2723168908 Idle
        37       13313
   2000153315 Network
        37           0
   1740759767 User I/O
        68          29

5.找到佔用系統資源特別大的Oracle的Session及執行的SQL語句。

利用系統程式的PID
-----------------
select a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text from v$session a, v$process b,v$sqltext c

where b.spid=&pid and b.addr=a.paddr and a.sql_address=c.address(+) order by c.piece;
在Oracle10g中，我們可以普遍採用SQL_ID來代替sql_address等資訊了

利用Oracle Session的SID
-----------------------
select sid,sql_text from v$session s,v$sql q
where sid = 144 and (q.sql_id=s.sql_id or q.sql_id = s.prev_sql_id);

查詢Top 10 SQL
--------------
select * from
(select parsing_user_id,executions,sorts,command_type,disk_reads,sql_text from v$sqlarea
order by disk_reads desc) where rownum < 10;

查詢I/O消耗過多的SQL
---------------------
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE, se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes
FROM v$session se, v$session_wait st,v$sess_io si,v$process pr
WHERE st.sid=se.sid AND st.sid=si.sid
AND se.PADDR=pr.ADDR
AND se.sid>6
AND st.wait_time=0
ORDER BY physical_reads DESC

查詢出SID後，獲得實際的SQL Text
select sql_address from v$session where sid=&sid;
select * from v$sqltext where address=&address;

6.針對SQL做相應的優化

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27500440/viewspace-1818372/，如需轉載，請註明出處，否則將追究法律責任。