我来说说 监控 oracle session

This I will highlight the oracle session monitor:

  I will do a workshop to begin this:

Step1:

  Open one session to update one row data:
 
SQL> select * from t1;

        ID
----------
      1981
      2012

SQL> update t1 set id=20000 where id=1981;

已更新 1 行。

Step2:
 Open another session to update the same row:
 
 SQL> update t1 set id=10000 where id=1981;
...(waiting...)

Step3:
  Open one session to review the session_wait view:
 
  SQL> select sid,wait_class from v$session_wait;

       SID WAIT_CLASS
---------- ----------------------------------------------------------------
       134 Idle
       136 Idle
       138 Idle
       141 Idle
       143 Idle
       147 Idle
       148 Idle
       149 Application
       150 Network
       151 Idle
       156 Idle

       SID WAIT_CLASS
---------- ----------------------------------------------------------------
       157 Idle
       158 Idle
       159 Idle
       160 Idle
       161 Idle
       162 Idle
       163 Idle
       164 Idle
       165 Idle
       166 Idle
       167 Idle

       SID WAIT_CLASS
---------- ----------------------------------------------------------------
       168 Idle
       169 Idle
       170 Idle

已选择25行。

SQL> select sid,EVENT from v$session_wait WHERE SID=149;

       SID EVENT
---------- ----------------------------------------------------------------
       149 enq: TX - row lock contention
      
Step 4:
  Let rollback in session 150, the session : 149 will commit automatic:
 
  SQL> SELECT *FROM V$MYSTAT WHERE ROWNUM <2;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       150          0          1

SQL> ROLLBACK;

回退已完成。

SQL> update t1 set id=10000 where id=1981;

已更新 1 行。

SQL> SELECT * FROM V$MYSTAT WHERE ROWNUM <2;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       149          0          1
      
Step5:

  Let us look into the review : v$session_wait:
  SQL> select sid,wait_class from v$session_wait;

       SID WAIT_CLASS
---------- ----------------------------------------------------------------
       134 Idle
       136 Idle
       138 Idle
       141 Idle
       143 Idle
       147 Idle
       148 Idle
       149 Idle
       150 Network
       151 Idle
       156 Network

       SID WAIT_CLASS
---------- ----------------------------------------------------------------
       157 Idle
       158 Idle
       159 Idle
       160 Idle
       161 Idle
       162 Idle
       163 Idle
       164 Idle
       165 Idle
       166 Idle
       167 Idle

       SID WAIT_CLASS
---------- ----------------------------------------------------------------
       168 Idle
       169 Idle
       170 Idle

已选择25行。

The waiting session will missing.

Step6:
  If we want to analyze the wait event, what I can do?
 
  1:The Oracle provide us one dictinary:
   v$session_wait_history;
   (this dictionary will only keep latest 10 records)
 
  2:Oracle also provide another dictionry:
    v$active_session_history
   
  3:If the Database down or restart, you can view the dictionary:
  dba_hist_active_sess_history
  (the head with "dba" can write down by hard disk, the other are call from memory)
 
Step7:
  You can also use EM to monitor the database session.