Oracle動態效能檢視學習筆記(7)_v$session

目錄

參考文件<>Chapter 24

##################################################################
1 Overview
##################################################################
This view has one row for every session connected to the database instance.
The sessions include user sessions, as well as background processes.

v$session is basically an information view used for finding the SID or SADDR of a user.

##################################################################
2 Userful Columns for v$session
##################################################################
1) STATUS:
Active: executing a SQL statement(waiting for/ using a resource)
正在執行語句，可能在等待或使用資源

Inactive: waiting for more work(???)

Killed: marked to be killed.
被標誌為killed

2) SID: Session identifier.

3) SERIAL#: Counter, which is incremented each time a SID is reused by another session.
(When a session ends and another session starts and uses the same SID)
會話重用次數？不是。

SQL> select sid from v$mystat where rownum =1;

       SID
----------
        26

SQL> select sid, serial# from v$session where sid=26;

       SID    SERIAL#
---------- ----------
        26        442

SQL> disc
Not logged on

SQL> conn gzdc/gzdc2009@testdb
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as gzdc

SQL> select sid, serial# from v$session where sid=26;

       SID    SERIAL#
---------- ----------
        26        444

SQL> select sid, serial# from v$session where sid=26;

       SID    SERIAL#
---------- ----------
        26        444

SQL> disc
Not logged on

SQL> conn gzdc/gzdc2009@testdb
Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as gzdc

SQL> select sid, serial# from v$session where sid=26;

       SID    SERIAL#
---------- ----------
        26        450

只是一個標識數。

4) AUDSID: Auditing session ID uniquely identifies a session over the life of a database.
It si also useful when finding the parallel query slaves for a query coordinator.(during the PQ execution they have

the same AUDSID)
審計會話ID，在整個資料庫的生命週期中有唯一性，同一個並行程式的幾個會話具有相同的AUDSID.

5) USERNAME: The Oracle user name for the connected session

Client Information

1) OSUSER: Operating system user name for the client process.
客戶端的作業系統使用者名稱

2) MACHINE: Machine where the client process is executing

3) TERMINAL: Terminal where the client process is running

4) PROCESS: Process ID of the client process(pid:ppid)
客戶端父子程式ID號

5) PROGRAM: Client program being executed by the client process
客戶端程式名

##################################################################
3 Join Columns for v$session
##################################################################
1) v$session.sid = v$session_wait.sid, v$sesstat.sid, v$lock.sid, v$session_event.sid, v$open_cursor.sid

2) v$session.sql_hash_value = v$sqltext.hash_value, v$sqlarea.hash_value, v$sql.hash_value;

3) v$session.sql_address = v$sqltext.address, v$sqlarea.address, v$sql.address;

4) v$session.taddr = v$transaction.addr

5) v$session.paddr = v$process.addr

##################################################################
4 示例
##################################################################
1) Finding Your Session

SELECT SID, OSUSER, USERNAME, MACHINE, PROCESS FROM V$SESSION WHERE audsid = userenv('SESSIONID');

2) Finding a Session When the Machine Is Known

SELECT SID, OSUSER, USERNAME, MACHINE, TERMINAL FROM V$SESSION WHERE machine like 'WORKGROUP\MOUSE-PC%';

SQL中如何轉義/,
不需要轉義。
當欄位中包含%時。
select * from procedure_log where errmsg like 'xxx\%xxx' escape '\';

##################################################################
4 問題
##################################################################

Oracle動態效能檢視學習筆記(7)_v$session

相關文章