[轉] V$session 表的妙用

select machine,username,count(*)  from v$session 
group by machine,username
order by  machine
統計每個客戶端，每個使用者的連結數
ccc023 - 193
server - 169
ecard-web - 179
1. sid,serial#
通過sid我們可以查詢與這個session相關的各種統計資訊,處理資訊.
a. select * from v$sesstat where sid = :sid;
查詢使用者相關的各種統計資訊.
select a.sid,a.statistic#,b.name,a.value
from v$sesstat a,v$statname b
where a.statistic# = b.statistic#
and a.sid = :sid;

b. 查詢使用者相關的各種io統計資訊
select * from v$sess_io where sid = :sid;

c. 查詢使用者想在正在開啟著的遊標變數.
select * from v$open_cursor where sid = :sid;

d. 查詢使用者當前的等待資訊. 以檢視當前的語句為什麼這麼慢/在等待什麼資源.
select * from v$session_wait where sid = :sid ;

e. 查詢使用者在一段時間內所等待的各種事件的資訊. 以瞭解這個session所遇到的瓶頸^_^
select * from v$session_event where sid = :sid;

f. 還有, 就是當我們想kill當前session的時候可以通過sid,serial#來處理.
alter system kill session ':sid,:serail#';

2. paddr.欄位, process addr, 通過這個欄位我們可以檢視當前程式的相關資訊, 系統程式id,作業系統使用者資訊等等.
select a.pid,a.spid,b.name,b.description,a.latchwait,a.latchspin,a.pga_used_mem,a.pga_alloc_mem,a.pga_freeable_mem,a.pga_max_mem
from v$process a,v$bgprocess b
where a.addr = b.paddr(+)
and a.addr = :paddr

3. command 欄位, 表明當前session正在執行的語句的型別.請參考reference.

4. taddr 當前事務的地址,可以通過這個欄位檢視當前session正在執行的事務資訊, 使用的回滾段資訊等^_^
select b.name rollname,a.*
from v$transaction a,v$rollname b
where a.xidusn = b.usn
and a.addr = '585EC18C';

5. lockwait欄位, 可以通過這個欄位查詢出當前正在等待的鎖的相關資訊.
select *
from v$lock
where (id1,id2) = (
        select id1,id2 from v$lock where kaddr = '57C68C48'
)

6. (sql_address,sql_hash_value) (prev_sql_addr,prev_hash_value) 根據這兩組欄位, 我們可以查詢到當前session正在執行的sql語句的詳細資訊.

select * from v$sqltext where address = :sql_address and hash_value = :sql_hash_value;

7.ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
可以通過這幾個欄位查詢現在正在被鎖的表的相關資訊.^_^
a. 首先得到被鎖的的資訊
select * from dba_objects where object_id = :row_wait_obj#;
b. 根據row_wait_file#可以找出對應的檔案的資訊.
select * from v$datafile where file# = :row_wait_file#.
c. 在根據以上四個欄位構造出被鎖的欄位的rowid資訊.
select dbms_rowid.ROWID_CREATE(1,:row_wait_obj#,:row_wait_file#,:row_wait_block#,:row_wait_row#) from dual;

8. logon_time 當前session的登入時間.
9. last_call_et 該session idle的時間, 每3秒中更新一次^_^
10.status 值有兩個：active，inactive。
acitve表示會話的sql沒有執行完。
11、記錄客戶端登入ip

建立觸發器
create or replace trigger on_logon_trigger
after logon on database
begin
    dbms_application_info.set_client_info(sys_context( 'userenv', 'ip_address' ) );
end;

用select client_info from v$session;查詢客戶端ip


(1)-V$SESSION_WAIT

　　這是一個尋找效能瓶頸的關鍵檢視。它提供了任何情況下session在資料庫中當前正在等待什麼(如果session當前什麼也沒在做，則顯示它最後的等待事件)。當系統存在效能問題時，本檢視可以做為一個起點指明探尋問題的方向。

　　V$SESSION_WAIT中，每一個連線到例項的session都對應一條記錄。

V$SESSION_WAIT中的常用列



l         SID: session標識

l         EVENT: session當前等待的事件，或者最後一次等待事件。

l         WAIT_TIME: session等待事件的時間(單位，百分之一秒)如果本列為0，說明session當前session還未有任何等待。

l         SEQ#: session等待事件將觸發其值自增長

l         P1, P2, P3: 等待事件中等待的詳細資料

l         P1TEXT, P2TEXT, P3TEXT: 解釋說明p1,p2,p3事件

附註：

1.State欄位有四種含義﹕

(1)Waiting：SESSION正等待這個事件。

(2)Waited unknown time：由於設定了timed_statistics值為false，導致不能得到時間資訊。表示發生了等待，但時間很短。

(3)Wait short time：表示發生了等待，但由於時間非常短不超過一個時間單位，所以沒有記錄。

(4)Waited knnow time：如果session等待然後得到了所需資源，那麼將從waiting進入本狀態。

2.Wait_time值也有四種含義：

(1)值>0：最後一次等待時間(單位：10ms)，當前未在等待狀態。

(2)值=0：session正在等待當前的事件。

(3)值=-1：最後一次等待時間小於1個統計單位，當前未在等待狀態。

(4)值=-2：時間統計狀態未置為可用，當前未在等待狀態。

3.Wait_time和Second_in_wait欄位值與state相關:

(1)如果state值為Waiting，那麼wait_time值無用。Second_in_wait值是實際的等待時間(單位：秒)。

(2)如果state值為Wait unknow time，那麼wait_time值和Second_in_wait值都無用。

(3)如果state值為Wait short time，那麼wait_time值和Second_in_wait值都無用。

(4)如果state值為Waiting known time，那麼wait_time值就是實際等待時間(單位：秒)，Second_in_wait值無用。

V$SESSION_WAIT中的連線列

Column        View                     Joined Column(s)

SID              V$SESSION          SID

示例：

1.列出當前系統的等待事件

SELECT event,

       sum(decode(wait_time,0,1,0)) "Curr",

       sum(decode(wait_time,0,0,1)) "Prev",

      count(*)"Total"

FROM v$session_wait GROUPBY event ORDERBYcount(*);

EVENT                                             Prev       Curr       Tot

---------------------------------------------       ----        -----       -----

PL/SQL lock timer                             0            1            1

SQL*Net more data from client           0            1            1

smon timer                                        0            1            1

pmon timer                                        0            1            1

SQL*Net message to client                  2            0            2

db file scattered read                           2            0            2

rdbms ipc message                            0            7            7

Enqueue                                           0            12           12

pipe get                                             0            12           12

db file sequential read                          3            10           13

latch free                                          9            6            15

SQL*Net message from client             835        1380       2215

這個按事件和wait_time的分組查詢列出下列的資訊：

l         多數的session都是空閒事件如：SQL*Net message from client, pipe get, PMON timer等。

l         session的cpu佔用可以通過上次session的非等待事件大致算出，除此問題外：看起來多數session沒有在等待什麼事情(難道他們都在幹活？)但其最後等待事件都是SQL*Net message from client。

2.列出指定ID的等待事件

select * from v$session_wait where sid=100;

3.應用p1,p2,p3進行等待事件的分析

v$session_wait檢視的列代表的緩衝區忙等待事件如下：

P1—與等待相關的資料檔案的全部檔案數量。

P2—P1中的資料檔案的塊數量。

P3—描述等待產生原因的程式碼。

例：select p1 "File #", p2 "Block #", p3 "Reason Code"

　　from v$session_wait

　　where event = 'buffer busy waits';

如果以上查詢的結果顯示一個塊在忙等待，以下的查詢將顯示這一塊的名稱和型別：

select owner, segment_name, segment_type

　from dba_extents

　where file_id = &P1　and &P2 between block_id and block_id + blocks -1;

　　我們也可以查詢dba_data_files以確定等待的檔案的file_name，方法是使用v$session_wait中的P1。

　　從v$session_wait中查詢P3(原因編碼)的值可以知道session等待的原因。原因編碼的範圍從0到300，下列為部分編碼所代表的事項：

0 塊被讀入緩衝區。

100 我們想要NEW(建立)一個塊，但這一塊當前被另一session讀入。

110 我們想將當前塊設為共享，但這一塊被另一session讀入，所以我們必須等待read()結束。

120 我們想獲得當前的塊，但其他人已經將這一塊讀入緩衝區，所以我們只能等待他人的讀入結束。

130 塊被另一session讀入，而且沒有找到其它協調的塊，所以我們必須等待讀的結束。緩衝區死鎖後這種情況也有可能產生。所以必須讀入塊的CR。

200 我們想新建立一個block，但其他人在使用，所以我們只好等待他人使用結束。

210 Session想讀入SCUR或XCUR中的塊，如果塊交換或者session處於非連續的TX模式，所以等待可能需要很長的時間。

220 在緩衝區查詢一個塊的當前版本，但有人以不合法的模式使用這一塊，所以我們只能等待。

230 以CR/CRX方式獲得一個塊，但塊中的更改開始並且沒有結束。

231 CR/CRX掃描找到當前塊，但塊中的更改開始並且沒有結束。

(2)-V$SESSION_EVENT

　　本檢視記錄了每個session的每一項等待事件。由上文所知V$SESSION_WAIT顯示了session的當前等待事件，而V$SESSION_EVENT則記錄了session自啟動起所有的事件。

V$SESSION_EVENT中的常用列

l         SID：session標識

l         EVENT：session等待的事件

l         TOTAL_WAITS：此session當前事件的總等待數

l         TIME_WAITED：此session總等待時間(單位，百分之一秒)

l         AVERAGE_WAIT：此session當前事件平均等待時間(單位，百分之一秒)

l         TOTAL_TIMEOUTS：等待超時次數

其它用法與V$SESSION_WAIT相似，不詳述了

附註：

Oracle的等待事件是衡量Oracle執行狀況的重要依據及指標。等待事件的概念是在Oracle7.0.1.2中引入的，大致有100個等待事件。在Oracle 8.0中這個數目增加到了大約150個，在Oracle8i中大約有200個事件,在Oracle9i中大約有360個等待事件。主要有兩種類別的等待事件，即空閒(idle)等待事件和非空閒(non-idle)等待事件。