【Oracle九大效能檢視】之3.v$session_關於與其它效能檢視表聯合查問題SQL
1、V$SESSION表結構 原文出自飛鷹工作室
SQL> desc v$session;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(12)
MACHINE VARCHAR2(64)
TERMINAL VARCHAR2(16)
PROGRAM VARCHAR2(64)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
MODULE VARCHAR2(48)
MODULE_HASH NUMBER
ACTION VARCHAR2(32)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
PDML_STATUS VARCHAR2(8)
PDDL_STATUS VARCHAR2(8)
PQ_STATUS VARCHAR2(8)
CURRENT_QUEUE_DURATION NUMBER
CLIENT_IDENTIFIER VARCHAR2(64)
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_INSTANCE NUMBER
BLOCKING_SESSION NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
SERVICE_NAME VARCHAR2(64)
SQL_TRACE VARCHAR2(8)
SQL_TRACE_WAITS VARCHAR2(5)
SQL_TRACE_BINDS VARCHAR2(5)
2、相關SQL
根據lockwait欄位可以查詢當前正在等待的鎖的相關資訊:
select * from v$lock where kaddr in (select lockwait from v$session where sid=&sid);
(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;
根據PID查SQL相關資訊
select id,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') logon from v$session where paddr in ( select addr from v$process where spid in('&pid'));
根據PID查SQL語句
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='&spid' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;
得到程式的sid號:
select id,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') logon from v$session where paddr in ( select addr from v$process where spid in('&pid'));
得到session的sqltext語句:
select sql_text from v$sqltext_with_newlines where hash_value in (select SQL_HASH_VALUE from v$session where paddr in (select addr from v$process where spid= '&pid')) order by piece;
根據SID查得SQL
select sql_text
from v$sqltext a
where a.hash_value=(select sql_hash_value
from v$session b
where b.SID='&sid')
order by piece ASC
SQL> desc v$session;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(12)
MACHINE VARCHAR2(64)
TERMINAL VARCHAR2(16)
PROGRAM VARCHAR2(64)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
MODULE VARCHAR2(48)
MODULE_HASH NUMBER
ACTION VARCHAR2(32)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
PDML_STATUS VARCHAR2(8)
PDDL_STATUS VARCHAR2(8)
PQ_STATUS VARCHAR2(8)
CURRENT_QUEUE_DURATION NUMBER
CLIENT_IDENTIFIER VARCHAR2(64)
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_INSTANCE NUMBER
BLOCKING_SESSION NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
SERVICE_NAME VARCHAR2(64)
SQL_TRACE VARCHAR2(8)
SQL_TRACE_WAITS VARCHAR2(5)
SQL_TRACE_BINDS VARCHAR2(5)
2、相關SQL
根據lockwait欄位可以查詢當前正在等待的鎖的相關資訊:
select * from v$lock where kaddr in (select lockwait from v$session where sid=&sid);
(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;
根據PID查SQL相關資訊
select id,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') logon from v$session where paddr in ( select addr from v$process where spid in('&pid'));
根據PID查SQL語句
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='&spid' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;
得到程式的sid號:
select id,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') logon from v$session where paddr in ( select addr from v$process where spid in('&pid'));
得到session的sqltext語句:
select sql_text from v$sqltext_with_newlines where hash_value in (select SQL_HASH_VALUE from v$session where paddr in (select addr from v$process where spid= '&pid')) order by piece;
根據SID查得SQL
select sql_text
from v$sqltext a
where a.hash_value=(select sql_hash_value
from v$session b
where b.SID='&sid')
order by piece ASC
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23577591/viewspace-686967/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【Oracle九大效能檢視】之2.v$sqlarea_查效能SQLOracleSQL
- oracle效能檢視Oracle
- Oracle檢視:常用動態效能檢視Oracle
- 【SQL】Oracle查詢轉換之檢視合併SQLOracle
- 8.1關於動態效能檢視
- oracle-一些檢視效能相關的檢視Oracle
- Oracle日常效能檢視Oracle
- 【Oracle九大效能檢視】之6.v$process檢視Oracle
- oracle v$sysstat效能檢視Oracle
- 關於SGA的常用動態效能檢視
- oracle筆記整理10——效能調優之臨時表與物化檢視Oracle筆記
- Oracle中效能檢視V$SYSSTATOracle
- (轉)Oracle 動態效能檢視Oracle
- mysql 聯合表(federated)及檢視MySql
- 檢視SQL Server 效能優化工具SQLServer優化
- awr 中單個SQL 效能檢視SQL
- Oracle 常用效能檢視一覽表(10g)Oracle
- Oracle內聯檢視更新遇到的問題.Oracle
- Oracle日常效能檢視常用語句Oracle
- Oracle九大效能檢視 之一Oracle
- Oracle效能診斷檢視總結Oracle
- 動態效能檢視
- Oracle許可權(二)許可權相關的動態效能檢視與資料字典檢視Oracle
- (重要)關於效能的幾個主要動態檢視
- Oracle效能問題檢查 - 常用查詢指令碼(final)Oracle指令碼
- RMAN相關的動態效能檢視
- (轉)Oracle動態效能檢視學習之v$processOracle
- Oracle動態效能檢視學習之 V$ROLLSTAT -- 轉Oracle
- ASM動態效能檢視ASM
- 查詢基表的相關檢視
- 【TUNE_ORACLE】Oracle檢查點(二)檢查點效能Oracle
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- Oracle動態效能檢視學習之v$sqltext & v$sqlareaOracleSQL
- Oracle動態效能檢視之v$session_longops ztOracleSessionGo
- Data Guard相關的動態效能檢視
- Oracle檢視TOP SQLOracleSQL
- sql server 檢視tempdb使用的相關檢視SQLServer
- Oracle 記憶體使用建議效能檢視Oracle記憶體