v$session介紹主要使用v$session查詢在此之前對此時圖進行簡單介紹官網(wǎng)地址V$SESSION (oracle.com)v$sessionSADDR: session addressSID: session identifier常用于連接其它列。SERIAL#: sid 會重用但是同一個sid被重用時serial#會增加不會重復。AUDSID: audit session id?？梢酝ㄟ^audsid查詢當前session的sid。select sid from v$session where audsiduserenv(sessionid);PADDR: process address關聯(lián)v$process的addr字段可以通過這個字段查處當前session對應操作系統(tǒng)的那個進程的id。USER#: sessions user id。等于dba_users中的user_id。Oracle內(nèi)部進程的user#為0。USERNAME: sessions username。等于dba_users中的username。Oracle內(nèi)部進程的username為空。COMMAND: session正在執(zhí)行的SQL Id。1代表create table3代表select。TADDR: 當前的transaction address?？梢杂脕黻P聯(lián)v$transaction的addr字段。LOCKWAIT: 可以通過這個字段查詢出當前正在等待的鎖的相關信息。sid lockwait與v$lock中的sid kaddr相對應。STATUS: 用來判斷session狀態(tài)。Active正執(zhí)行SQL語句。Inactive等待操作。Killed被標注為刪除。SERVER: server type (dedicated or shared)SCHEMA#: schema user id。Oracle內(nèi)部進程的schema#為0。SCHEMANAME: schema username。Oracle內(nèi)部進程的schemaname為sys。OSUSER: 客戶端操作系統(tǒng)用戶名。PROCESS: 客戶端process id。MACHINE: 客戶端machine name。TERMINAL: 客戶端執(zhí)行的terminal name。PROGRAM: 客戶端應用程序。比如ORACLE.EXE (PMON)或者sqlplus.exeTYPE: session type (background or user)SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER: session正在執(zhí)行的sql statement和v$sql中的address, hash_value, sql_id, child_number相對應。PREV_SQL_ADDR, PREV_HASH_VALUE, PREV_SQL_ID, PREV_CHILD_NUMBER: 上一次執(zhí)行的sql statement。MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO: 應用通過DBMS_APPLICATION_INFO設置的一些信息。FIXED_TABLE_SEQUENCE: 當session完成一個user call后就會增加的一個數(shù)值也就是說如果session inactive它就不會增加。因此可以根據(jù)此字段的值變化來監(jiān)控某個時間點以來的session的性能情況。例如一個小時以前某個session的FIXED_TABLE_SEQUENCE是10000而現(xiàn)在是20000則表明一個小時內(nèi)其user call比較頻繁可以重點關注此session的performance statistics。ROW_WAIT_OBJ#: 被鎖定行所在table的object_id。和dba_objects中的object_id關聯(lián)可以得到被鎖定的table name。ROW_WAIT_FILE#: 被鎖定行所在的datafile id。和v$datafile中的file#關聯(lián)可以得到datafile name。ROW_WAIT_BLOCK#: Identifier for the block containing the row specified in ROW_WAIT_ROW#ROW_WAIT_ROW#: session當前正在等待的被鎖定的行。LOGON_TIME: session logon timev$processADDR: process address?？梢院蛌$session的paddr字段關聯(lián)。PID: Oracle進程identifier。SPID: 操作系統(tǒng)進程identifier。USERNAME: 操作系統(tǒng)進程的用戶名。并非Oracle用戶名。SERIAL#:: process serial number。TERMINAL: 操作系統(tǒng)terminal identifier(e.g., computer name)。PROGRAM: 進程正在執(zhí)行的程序(e.g., ORACLE.EXE (ARC0))和v$session中的program類似。BACKGROUND: 1代表oracle background processnull代表normal process。v$sqlV$SQL為共享池中每個獨立的執(zhí)行實例子游標提供一行記錄主要用于執(zhí)行計劃分析和綁定變量問題排查。SQL_IDSQL 語句的唯一標識符是性能調(diào)優(yōu)和關聯(lián)其他視圖的核心字段。CHILD_NUMBER子游標編號。當同一條 SQL 存在多個執(zhí)行計劃時通過此字段區(qū)分。CHILD_ADDRESS子游標在內(nèi)存中的地址。PLAN_HASH_VALUE執(zhí)行計劃的哈希值。用于識別和對比 SQL 是否生成了不同的執(zhí)行計劃。EXECUTIONS該特定子游標自載入緩存庫后的執(zhí)行次數(shù)。BUFFER_GETS該子游標執(zhí)行導致的邏輯讀讀內(nèi)存次數(shù)。DISK_READS該子游標執(zhí)行導致的物理讀讀磁盤次數(shù)。ELAPSED_TIME該子游標解析和執(zhí)行消耗的總時間微秒。CPU_TIME該子游標解析和執(zhí)行消耗的 CPU 時間微秒。IS_BIND_SENSITIVE / IS_BIND_AWARE指示游標是否對綁定變量敏感或已感知綁定變量用于排查自適應游標共享ACS相關問題。v$sqlareaV$SQLAREA將具有相同 SQL 文本相同SQL_ID的子游標合并統(tǒng)計主要用于評估 SQL 整體資源消耗和共享池健康度分析。SQL_ID父游標的 SQL 標識符。VERSION_COUNT緩存中該父游標下存在的子游標數(shù)量。如果此值過高通常意味著存在嚴重的游標共享問題如綁定變量類型不一致、優(yōu)化器環(huán)境不同等。SHARABLE_MEM該 SQL 占用的共享內(nèi)存總量。若存在多個子游標則為所有子游標共享內(nèi)存之和。EXECUTIONS所有子游標執(zhí)行該語句的總次數(shù)。BUFFER_GETS / DISK_READS所有子游標執(zhí)行導致的邏輯讀和物理讀總次數(shù)。ELAPSED_TIME / CPU_TIME所有子游標消耗的總時間和 CPU 時間微秒。FIRST_LOAD_TIME該 SQL 首次被載入共享池的時間戳。LAST_LOAD_TIME該 SQL 查詢計劃最后一次被載入庫緩存的時間。IS_OBSOLETE指示游標是否已廢棄Y/N。當子游標數(shù)量過多時Oracle 會將該游標標記為廢棄以保護共享池。核心區(qū)別對比表對比維度V$SQLV$SQLAREA數(shù)據(jù)粒度細粒度子游標級別粗粒度父游標級別記錄行數(shù)相同 SQL 文本可能對應多條記錄相同 SQL 文本僅對應一條記錄統(tǒng)計方式單個執(zhí)行實例的原始數(shù)據(jù)相同 SQL 文本所有實例的聚合匯總適用場景執(zhí)行計劃分析、綁定變量泄漏檢測SQL 整體性能評估、共享池分析dba_tab_modificationsDBA_TAB_MODIFICATIONS是一個用于監(jiān)控表數(shù)據(jù)變動情況的核心系統(tǒng)視圖。它記錄了數(shù)據(jù)庫中所有表自上一次收集統(tǒng)計信息以來發(fā)生的 DML數(shù)據(jù)操作語言變更情況。出于保護數(shù)據(jù)庫性能的考慮該視圖中的數(shù)據(jù)不是實時更新的。Oracle 會將內(nèi)存中的 DML 變動信息定期大約每 15 分鐘由 SMON 后臺進程刷入數(shù)據(jù)字典基表。如果您剛執(zhí)行完 DML 操作想要立即在視圖中看到最新變動需要手動執(zhí)行以下存儲過程來強制刷新內(nèi)存中的監(jiān)控信息EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;TABLE_OWNER被修改表的擁有者Schema 名稱。TABLE_NAME被修改的表名。PARTITION_NAME被修改的分區(qū)名稱如果是分區(qū)表。SUBPARTITION_NAME被修改的子分區(qū)名稱。INSERTS自上次收集統(tǒng)計信息以來插入操作的近似行數(shù)。UPDATES自上次收集統(tǒng)計信息以來更新操作的近似行數(shù)。DELETES自上次收集統(tǒng)計信息以來刪除操作的近似行數(shù)。TIMESTAMP該表最后一次被修改的時間戳。TRUNCATED自上次分析以來該表是否被 TRUNCATE 過YES 或 NO。DROP_SEGMENTS自上次分析以來被刪除的分區(qū)或子分區(qū)段Segments的數(shù)量查看當前阻塞對象SELECT wsession.sid waiting_session, wsession.serial# serial, wsession.logon_time, wsession.blocking_session_status, wsession.event, wsession.username waiting_user, wprocess.pid wait_pid, nvl(obj.object_name, -) oname, nvl(obj.owner, -) owner, wsession.row_wait_block# row_lock, wsession.blocking_session holding_session, hprocess.pid hold_pid, hsession.username holding_user FROM v$session wsession JOIN v$session hsession ON wsession.blocking_session hsession.sid JOIN v$process wprocess ON wprocess.addr wsession.paddr JOIN v$process hprocess ON hprocess.addr hsession.paddr LEFT JOIN dba_objects obj ON obj.object_id wsession.row_wait_obj# WHERE wsession.blocking_session IS NOT NULL UNION SELECT wsession.sid waiting_session, wsession.serial# serial, wsession.logon_time, wsession.blocking_session_status, wsession.event, wsession.username waiting_user, wprocess.pid wait_pid, nvl(obj.object_name, -) oname, nvl(obj.owner, -) owner, wsession.row_wait_block# row_lock, nvl(wsession.blocking_session, 0) holding_session, nvl(hprocess.pid, 0) hold_pid, nvl(hsession.username, -) holding_user FROM v$session wsession LEFT JOIN v$session hsession ON wsession.blocking_session hsession.sid JOIN v$process wprocess ON wprocess.addr wsession.paddr LEFT JOIN v$process hprocess ON hprocess.addr hsession.paddr LEFT JOIN dba_objects obj ON obj.object_id wsession.row_wait_obj# WHERE wsession.sid IN ( SELECT blocking_session FROM v$session);查看歷史阻塞情況select * from (select t.machine, t.session_id, t.session_serial#, SUBSTR(numtodsinterval(t.WAIT_TIME, second), 12, 8) as wait_time, t.instance_number, t.sample_time, t.blocking_session, t.blocking_session_serial#, t.blocking_inst_id, t.session_state, t.event, t.sql_exec_start, t.sql_plan_operation, t.sql_id, t.sql_opcode, t.user_id, t.session_type, connect_by_isleaf as isleaf, CONNECT_BY_ROOT session_id rootsid, sys_connect_by_path(session_id, -) tree from dba_hist_active_sess_history t where t.sample_time to_date(2024-03-21 10:00:00, yyyy-mm-dd hh24:mi:ss) and t.sample_time to_date(2024-03-21 11:20:00, yyyy-mm-dd hh24:mi:ss) and t.session_type BACKGROUND and t.event is not NULL start with blocking_session is null connect by nocycle prior t.session_id t.blocking_session) where isleaf 1 -- and rootsid 5 ;查看當前DDL鎖對象并kill會話select ALTER SYSTEM DISCONNECT SESSION || b.SID || , || b.SERIAL# || IMMEDIATE; kills, a.type, a.owner, c.pid, b.SID, b.SERIAL#, b.OSUSER, b.MACHINE, b.PROGRAM, b.MODULE, b.ACTION, b.*, c.* From dba_ddl_locks a, v$session b, v$process c where a.session_id b.SID and b.paddr c.addr and a.name upper(pkg_scene_option)若需kill會話執(zhí)行kills字段中的命令即可。查看當前被鎖對象并kill會話select ALTER SYSTEM DISCONNECT SESSION || b.SID || , || b.SERIAL# || IMMEDIATE; kills, f.lock_type, c.pid, b.SID, b.SERIAL#, b.OSUSER, b.MACHINE, b.PROGRAM, b.MODULE, b.ACTION, e.owner, e.object_name, a.*, b.*, c.*, d.* From v$locked_object a, v$session b, v$process c, v$sql d, dba_objects e, dba_locks f where a.session_id b.SID and b.paddr c.addr and d.sql_id b.sql_id and a.object_id e.object_id and a.session_id f.session_id and b.sql_addressd.addressoracle rac查看死鎖并kill進程select kill -9 ||pc.SPID,a.*, row_number() over(partition by blocking_source order by LEVEL asc) as rn from (select * from (select inst_id, lpad( , (level - 1) * 2, ) || sid as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, level, blocking_session, (prior sid) as blocking_sid, (prior serial#) as blocking_serial#, (prior sql_id) as blocking_sql_id, sys_connect_by_path(sid, -) as blocking_path, connect_by_root(sid) as blocking_source from gv$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1 union select inst_id, to_char(sid) as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, 1, null, null, null, null, null, sid from gv$session where sid in (select blocking_source from (select wait_class#, username, level, connect_by_root(sid) as blocking_source from gv$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1)) a,GV_$PROCESS pc WHERE pc.ADDRa.PADDR;總結(jié)--查看阻塞進程SQL語句 --Blocking sessions SELECT /* RULE */ LPAD(---, DECODE(A.request, 0, 0, 5)) || A.SID SID, B.serial#, DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || Days || ) || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), SSSSS), HH24:MI:SS) LOGON, b.status, b.SCHEMANAME SCHEMA, DECODE(o.owner || . || o.object_name, ., NULL, o.owner || . || o.object_name) OBJECT, o.object_type, b.osuser, b.machine, b.module, b.program, DECODE(BLOCK, 0, NULL, BLOKER) || DECODE(request, 0, NULL, --WAITER) BLOKER, DECODE(A.lmode, 0, None, 1, Null, 2, Row-S (SS), 3, Row-X (SX), 4, Share, 5, S/Row-X (SSX), 6, Exclusive, A.lmode) lmode, DECODE(TRUNC(ctime / 86400), 0, TO_CHAR(TO_DATE(ctime, SSSSS), HH24:MI:SS), TRUNC(ctime / 86400) || Days || TO_CHAR(TO_DATE(ctime - (TRUNC(ctime / 86400)) * 86400, SSSSS), HH24:MI:SS)) TIME, A.TYPE, alter system kill session || || a.SID || , || b.serial# || || immediate; kill_session, DECODE(object_type, NULL, NULL, Dbms_Rowid.rowid_create(1, || row_wait_obj# || , || row_wait_file# || , || row_wait_block# || , || row_wait_row# || )) row_id FROM v$lock A, v$session b, dba_objects o WHERE A.SID b.SID AND (lmode 0 OR BLOCK 1) AND o.object_id() DECODE(b.ROW_WAIT_OBJ#, -1, NULL, b.ROW_WAIT_OBJ#) ORDER BY A.id1, A.request; --Tip: The last column of the query for blocked sessions is ROWID of the record!!!! --To find the exact locked row execute: --SELECT * FROM OBJECT WHERE ROWID DbmsRowid.rowid_create(1, 48804, 31, 114809, 210); --單實例阻塞會話查詢 --cascade blocking /*set lines 200 pages 100 col tree for a30 col event for a40*/ select * from (select alter system kill session || || a.SID || , || a.serial# || || immediate; kill_session, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(SID, -) tree, level as tree_level from v$session a start with a.blocking_session is not null connect by nocycle a.sid prior a.blocking_session) where isleaf 1 order by tree_level asc; --RAC實例阻塞會話查詢 --cascade blockinggv$session select * from (select a.inst_id, alter system kill session || || a.SID || , || a.serial# || || immediate; kill_session, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(a.SID || || a.inst_id, - ) tree, level as tree_level from gv$session a start with a.blocking_session is not null connect by (a.sid || || a.inst_id) prior (a.blocking_session || || a.blocking_instance)) where isleaf 1 order by tree_level asc; --alter system kill session 150,8742 immediate; select p.spid from gv$session s,gv$process p where s.sidxxx and s.paddrp.addr; --檢測數(shù)據(jù)庫各實例session 阻塞 tree的sql 檢測數(shù)據(jù)庫各實例session 阻塞 tree的sql --由于使用到了 v$sesion 的 blocking_instance,blocking_session 這兩列所以數(shù)據(jù)庫的最低版本為oracle 10g。 ---適合單實例和多實實例數(shù)據(jù)庫一個SQL搞定。 with/* materialized */ aa as ( select * from gv$session s where 11 ) select alter system kill session || t.SID || , || t.SERIAL# || immediate; as kill_SID, lpad(, 2 * level - 1) ||t.inst_sid as leveL_inst_sid, level level_flag, sys_connect_by_path(t.inst_sid,/) as inst_sid_path, connect_by_root t.inst_sid as root_inst_sid, connect_by_isleaf as node_isleaf, ########## as FLAGFLAG0, t.status, t.event#, t.event, t.program, t.module, t.action, t.client_identifier, t.OSUSER os_USER, t.machine os_machine, t.logon_time logon_time, t.last_call_et last_call_et, alter system kill session || t.SID || , || t.SERIAL# || immediate; as kill_SID, ########## as FLAGFLAG/*, t.* */ from ( select gs.inst_id||-||gs.sid as inst_sid, gs.blocking_instance||-||gs.blocking_session par_inst_sid, gs.* from aa gs where 11 and (gs.inst_id,gs.sid ) in ( --被阻塞者 select s.inst_id,s.sid from aa s where 11 and s.BLOCKING_SESSION is not null union all --阻塞者 select sw.inst_id,sw.sid from aa sw where 11 --and lower(sw.MACHINE) not like %ejb% and (sw.INST_ID,sw.SID) in ( select/* hash_sj*/ s.BLOCKING_INSTANCE, s.BLOCKING_SESSION from aa s where 11 and s.BLOCKING_SESSION is not null ) ) ) t where 11 connect by prior t.inst_sidt.par_inst_sid order siblings by t.inst_sid ; --查看 dba_hist_active_sess_history with/* materialized */ aa as ( select instance_number as inst_id, session_id as sid, session_serial# as SERIAL#, blocking_inst_id as blocking_instance, blocking_session, blocking_session_serial#, event, p1, p2, p3, sql_id from dba_hist_active_sess_history h where 11 and to_char(sample_time, yyyymmddhh24miss) between 20190808143109 and 20190808143109 order by sample_time ) select lpad(, 2 * level - 1) ||t.inst_sid as leveL_inst_sid, level level_flag, sys_connect_by_path(t.inst_sid,/) as inst_sid_path, connect_by_root t.inst_sid as root_inst_sid, connect_by_isleaf as node_isleaf, ########## as FLAGFLAG0, t.event, t.p1, t.p2, t.p3, t.sql_id, ########## as FLAGFLAG/*, t.* */ from ( select gs.inst_id||-||gs.sid as inst_sid, gs.blocking_instance||-||gs.blocking_session par_inst_sid, gs.* from aa gs where 11 and (gs.inst_id,gs.sid ) in ( --被阻塞者 select s.inst_id,s.sid from aa s where 11 and s.BLOCKING_SESSION is not null union all --阻塞者 select sw.inst_id,sw.sid from aa sw where 11 --and lower(sw.MACHINE) not like %ejb% and (sw.INST_ID,sw.SID) in ( select/* hash_sj*/ s.BLOCKING_INSTANCE, s.BLOCKING_SESSION from aa s where 11 and s.BLOCKING_SESSION is not null ) ) ) t where 11 connect by prior t.inst_sidt.par_inst_sid order siblings by t.inst_sid ; -- --1.no-rac -- select a.*, row_number() over(partition by blocking_source order by LEVEL asc) as rn from (select * from (select lpad( , (level - 1) * 2, ) || sid as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, level, blocking_session, (prior sid) as blocking_sid, (prior serial#) as blocking_serial#, (prior sql_id) as blocking_sql_id, sys_connect_by_path(sid, -) as blocking_path, connect_by_root(sid) as blocking_source from v$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1 union select to_char(sid) as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, 1, null, null, null, null, null, sid from v$session where sid in (select blocking_source from (select wait_class#, username, level, connect_by_root(sid) as blocking_source from v$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1)) a -- --2.rac -- select a.*, row_number() over(partition by blocking_source order by LEVEL asc) as rn from (select * from (select inst_id, lpad( , (level - 1) * 2, ) || sid as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, level, blocking_session, (prior sid) as blocking_sid, (prior serial#) as blocking_serial#, (prior sql_id) as blocking_sql_id, sys_connect_by_path(sid, -) as blocking_path, connect_by_root(sid) as blocking_source from gv$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1 union select inst_id, to_char(sid) as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, 1, null, null, null, null, null, sid from gv$session where sid in (select blocking_source from (select wait_class#, username, level, connect_by_root(sid) as blocking_source from gv$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1)) a歷史會話情況select t.machine, t.session_id, t.session_serial#, t.instance_number, t.sample_time, t.blocking_session, t.blocking_session_serial#, t.blocking_inst_id, t.session_state, t.event, t.sql_exec_start, t.sql_plan_operation, t.sql_id, t.sql_opcode, t.user_id, t.session_type from dba_hist_active_sess_history t where t.sample_time to_date(2023-02-20 08:30:00, yyyy-mm-dd hh24:mi:ss) and t.sample_time to_date(2023-02-20 10:30:00, yyyy-mm-dd hh24:mi:ss) and t.session_type BACKGROUND and t.event is not NULL and t.blocking_session is not null and t.sql_opcode 0 and t.user_id 0 order by t.sample_time desc;查看近1分鐘內(nèi)的SQL執(zhí)行情況SELECT sql_id, last_active_time, executions, sql_text AS sql_preview, ROUND(elapsed_time / NULLIF(executions, 0) / 1000000, 4) AS avg_elapsed_sec FROM v$sql WHERE last_active_time SYSDATE - INTERVAL 1 MINUTE AND executions 0 ORDER BY last_active_time DESC, avg_elapsed_sec desc; Oracle查看阻塞對象
v$session介紹主要使用v$session查詢在此之前對此時圖進行簡單介紹官網(wǎng)地址V$SESSION (oracle.com)v$sessionSADDR: session addressSID: session identifier常用于連接其它列。SERIAL#: sid 會重用但是同一個sid被重用時serial#會增加不會重復。AUDSID: audit session id?？梢酝ㄟ^audsid查詢當前session的sid。select sid from v$session where audsiduserenv(sessionid);PADDR: process address關聯(lián)v$process的addr字段可以通過這個字段查處當前session對應操作系統(tǒng)的那個進程的id。USER#: sessions user id。等于dba_users中的user_id。Oracle內(nèi)部進程的user#為0。USERNAME: sessions username。等于dba_users中的username。Oracle內(nèi)部進程的username為空。COMMAND: session正在執(zhí)行的SQL Id。1代表create table3代表select。TADDR: 當前的transaction address?？梢杂脕黻P聯(lián)v$transaction的addr字段。LOCKWAIT: 可以通過這個字段查詢出當前正在等待的鎖的相關信息。sid lockwait與v$lock中的sid kaddr相對應。STATUS: 用來判斷session狀態(tài)。Active正執(zhí)行SQL語句。Inactive等待操作。Killed被標注為刪除。SERVER: server type (dedicated or shared)SCHEMA#: schema user id。Oracle內(nèi)部進程的schema#為0。SCHEMANAME: schema username。Oracle內(nèi)部進程的schemaname為sys。OSUSER: 客戶端操作系統(tǒng)用戶名。PROCESS: 客戶端process id。MACHINE: 客戶端machine name。TERMINAL: 客戶端執(zhí)行的terminal name。PROGRAM: 客戶端應用程序。比如ORACLE.EXE (PMON)或者sqlplus.exeTYPE: session type (background or user)SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER: session正在執(zhí)行的sql statement和v$sql中的address, hash_value, sql_id, child_number相對應。PREV_SQL_ADDR, PREV_HASH_VALUE, PREV_SQL_ID, PREV_CHILD_NUMBER: 上一次執(zhí)行的sql statement。MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO: 應用通過DBMS_APPLICATION_INFO設置的一些信息。FIXED_TABLE_SEQUENCE: 當session完成一個user call后就會增加的一個數(shù)值也就是說如果session inactive它就不會增加。因此可以根據(jù)此字段的值變化來監(jiān)控某個時間點以來的session的性能情況。例如一個小時以前某個session的FIXED_TABLE_SEQUENCE是10000而現(xiàn)在是20000則表明一個小時內(nèi)其user call比較頻繁可以重點關注此session的performance statistics。ROW_WAIT_OBJ#: 被鎖定行所在table的object_id。和dba_objects中的object_id關聯(lián)可以得到被鎖定的table name。ROW_WAIT_FILE#: 被鎖定行所在的datafile id。和v$datafile中的file#關聯(lián)可以得到datafile name。ROW_WAIT_BLOCK#: Identifier for the block containing the row specified in ROW_WAIT_ROW#ROW_WAIT_ROW#: session當前正在等待的被鎖定的行。LOGON_TIME: session logon timev$processADDR: process address?？梢院蛌$session的paddr字段關聯(lián)。PID: Oracle進程identifier。SPID: 操作系統(tǒng)進程identifier。USERNAME: 操作系統(tǒng)進程的用戶名。并非Oracle用戶名。SERIAL#:: process serial number。TERMINAL: 操作系統(tǒng)terminal identifier(e.g., computer name)。PROGRAM: 進程正在執(zhí)行的程序(e.g., ORACLE.EXE (ARC0))和v$session中的program類似。BACKGROUND: 1代表oracle background processnull代表normal process。v$sqlV$SQL為共享池中每個獨立的執(zhí)行實例子游標提供一行記錄主要用于執(zhí)行計劃分析和綁定變量問題排查。SQL_IDSQL 語句的唯一標識符是性能調(diào)優(yōu)和關聯(lián)其他視圖的核心字段。CHILD_NUMBER子游標編號。當同一條 SQL 存在多個執(zhí)行計劃時通過此字段區(qū)分。CHILD_ADDRESS子游標在內(nèi)存中的地址。PLAN_HASH_VALUE執(zhí)行計劃的哈希值。用于識別和對比 SQL 是否生成了不同的執(zhí)行計劃。EXECUTIONS該特定子游標自載入緩存庫后的執(zhí)行次數(shù)。BUFFER_GETS該子游標執(zhí)行導致的邏輯讀讀內(nèi)存次數(shù)。DISK_READS該子游標執(zhí)行導致的物理讀讀磁盤次數(shù)。ELAPSED_TIME該子游標解析和執(zhí)行消耗的總時間微秒。CPU_TIME該子游標解析和執(zhí)行消耗的 CPU 時間微秒。IS_BIND_SENSITIVE / IS_BIND_AWARE指示游標是否對綁定變量敏感或已感知綁定變量用于排查自適應游標共享ACS相關問題。v$sqlareaV$SQLAREA將具有相同 SQL 文本相同SQL_ID的子游標合并統(tǒng)計主要用于評估 SQL 整體資源消耗和共享池健康度分析。SQL_ID父游標的 SQL 標識符。VERSION_COUNT緩存中該父游標下存在的子游標數(shù)量。如果此值過高通常意味著存在嚴重的游標共享問題如綁定變量類型不一致、優(yōu)化器環(huán)境不同等。SHARABLE_MEM該 SQL 占用的共享內(nèi)存總量。若存在多個子游標則為所有子游標共享內(nèi)存之和。EXECUTIONS所有子游標執(zhí)行該語句的總次數(shù)。BUFFER_GETS / DISK_READS所有子游標執(zhí)行導致的邏輯讀和物理讀總次數(shù)。ELAPSED_TIME / CPU_TIME所有子游標消耗的總時間和 CPU 時間微秒。FIRST_LOAD_TIME該 SQL 首次被載入共享池的時間戳。LAST_LOAD_TIME該 SQL 查詢計劃最后一次被載入庫緩存的時間。IS_OBSOLETE指示游標是否已廢棄Y/N。當子游標數(shù)量過多時Oracle 會將該游標標記為廢棄以保護共享池。核心區(qū)別對比表對比維度V$SQLV$SQLAREA數(shù)據(jù)粒度細粒度子游標級別粗粒度父游標級別記錄行數(shù)相同 SQL 文本可能對應多條記錄相同 SQL 文本僅對應一條記錄統(tǒng)計方式單個執(zhí)行實例的原始數(shù)據(jù)相同 SQL 文本所有實例的聚合匯總適用場景執(zhí)行計劃分析、綁定變量泄漏檢測SQL 整體性能評估、共享池分析dba_tab_modificationsDBA_TAB_MODIFICATIONS是一個用于監(jiān)控表數(shù)據(jù)變動情況的核心系統(tǒng)視圖。它記錄了數(shù)據(jù)庫中所有表自上一次收集統(tǒng)計信息以來發(fā)生的 DML數(shù)據(jù)操作語言變更情況。出于保護數(shù)據(jù)庫性能的考慮該視圖中的數(shù)據(jù)不是實時更新的。Oracle 會將內(nèi)存中的 DML 變動信息定期大約每 15 分鐘由 SMON 后臺進程刷入數(shù)據(jù)字典基表。如果您剛執(zhí)行完 DML 操作想要立即在視圖中看到最新變動需要手動執(zhí)行以下存儲過程來強制刷新內(nèi)存中的監(jiān)控信息EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;TABLE_OWNER被修改表的擁有者Schema 名稱。TABLE_NAME被修改的表名。PARTITION_NAME被修改的分區(qū)名稱如果是分區(qū)表。SUBPARTITION_NAME被修改的子分區(qū)名稱。INSERTS自上次收集統(tǒng)計信息以來插入操作的近似行數(shù)。UPDATES自上次收集統(tǒng)計信息以來更新操作的近似行數(shù)。DELETES自上次收集統(tǒng)計信息以來刪除操作的近似行數(shù)。TIMESTAMP該表最后一次被修改的時間戳。TRUNCATED自上次分析以來該表是否被 TRUNCATE 過YES 或 NO。DROP_SEGMENTS自上次分析以來被刪除的分區(qū)或子分區(qū)段Segments的數(shù)量查看當前阻塞對象SELECT wsession.sid waiting_session, wsession.serial# serial, wsession.logon_time, wsession.blocking_session_status, wsession.event, wsession.username waiting_user, wprocess.pid wait_pid, nvl(obj.object_name, -) oname, nvl(obj.owner, -) owner, wsession.row_wait_block# row_lock, wsession.blocking_session holding_session, hprocess.pid hold_pid, hsession.username holding_user FROM v$session wsession JOIN v$session hsession ON wsession.blocking_session hsession.sid JOIN v$process wprocess ON wprocess.addr wsession.paddr JOIN v$process hprocess ON hprocess.addr hsession.paddr LEFT JOIN dba_objects obj ON obj.object_id wsession.row_wait_obj# WHERE wsession.blocking_session IS NOT NULL UNION SELECT wsession.sid waiting_session, wsession.serial# serial, wsession.logon_time, wsession.blocking_session_status, wsession.event, wsession.username waiting_user, wprocess.pid wait_pid, nvl(obj.object_name, -) oname, nvl(obj.owner, -) owner, wsession.row_wait_block# row_lock, nvl(wsession.blocking_session, 0) holding_session, nvl(hprocess.pid, 0) hold_pid, nvl(hsession.username, -) holding_user FROM v$session wsession LEFT JOIN v$session hsession ON wsession.blocking_session hsession.sid JOIN v$process wprocess ON wprocess.addr wsession.paddr LEFT JOIN v$process hprocess ON hprocess.addr hsession.paddr LEFT JOIN dba_objects obj ON obj.object_id wsession.row_wait_obj# WHERE wsession.sid IN ( SELECT blocking_session FROM v$session);查看歷史阻塞情況select * from (select t.machine, t.session_id, t.session_serial#, SUBSTR(numtodsinterval(t.WAIT_TIME, second), 12, 8) as wait_time, t.instance_number, t.sample_time, t.blocking_session, t.blocking_session_serial#, t.blocking_inst_id, t.session_state, t.event, t.sql_exec_start, t.sql_plan_operation, t.sql_id, t.sql_opcode, t.user_id, t.session_type, connect_by_isleaf as isleaf, CONNECT_BY_ROOT session_id rootsid, sys_connect_by_path(session_id, -) tree from dba_hist_active_sess_history t where t.sample_time to_date(2024-03-21 10:00:00, yyyy-mm-dd hh24:mi:ss) and t.sample_time to_date(2024-03-21 11:20:00, yyyy-mm-dd hh24:mi:ss) and t.session_type BACKGROUND and t.event is not NULL start with blocking_session is null connect by nocycle prior t.session_id t.blocking_session) where isleaf 1 -- and rootsid 5 ;查看當前DDL鎖對象并kill會話select ALTER SYSTEM DISCONNECT SESSION || b.SID || , || b.SERIAL# || IMMEDIATE; kills, a.type, a.owner, c.pid, b.SID, b.SERIAL#, b.OSUSER, b.MACHINE, b.PROGRAM, b.MODULE, b.ACTION, b.*, c.* From dba_ddl_locks a, v$session b, v$process c where a.session_id b.SID and b.paddr c.addr and a.name upper(pkg_scene_option)若需kill會話執(zhí)行kills字段中的命令即可。查看當前被鎖對象并kill會話select ALTER SYSTEM DISCONNECT SESSION || b.SID || , || b.SERIAL# || IMMEDIATE; kills, f.lock_type, c.pid, b.SID, b.SERIAL#, b.OSUSER, b.MACHINE, b.PROGRAM, b.MODULE, b.ACTION, e.owner, e.object_name, a.*, b.*, c.*, d.* From v$locked_object a, v$session b, v$process c, v$sql d, dba_objects e, dba_locks f where a.session_id b.SID and b.paddr c.addr and d.sql_id b.sql_id and a.object_id e.object_id and a.session_id f.session_id and b.sql_addressd.addressoracle rac查看死鎖并kill進程select kill -9 ||pc.SPID,a.*, row_number() over(partition by blocking_source order by LEVEL asc) as rn from (select * from (select inst_id, lpad( , (level - 1) * 2, ) || sid as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, level, blocking_session, (prior sid) as blocking_sid, (prior serial#) as blocking_serial#, (prior sql_id) as blocking_sql_id, sys_connect_by_path(sid, -) as blocking_path, connect_by_root(sid) as blocking_source from gv$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1 union select inst_id, to_char(sid) as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, 1, null, null, null, null, null, sid from gv$session where sid in (select blocking_source from (select wait_class#, username, level, connect_by_root(sid) as blocking_source from gv$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1)) a,GV_$PROCESS pc WHERE pc.ADDRa.PADDR;總結(jié)--查看阻塞進程SQL語句 --Blocking sessions SELECT /* RULE */ LPAD(---, DECODE(A.request, 0, 0, 5)) || A.SID SID, B.serial#, DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || Days || ) || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), SSSSS), HH24:MI:SS) LOGON, b.status, b.SCHEMANAME SCHEMA, DECODE(o.owner || . || o.object_name, ., NULL, o.owner || . || o.object_name) OBJECT, o.object_type, b.osuser, b.machine, b.module, b.program, DECODE(BLOCK, 0, NULL, BLOKER) || DECODE(request, 0, NULL, --WAITER) BLOKER, DECODE(A.lmode, 0, None, 1, Null, 2, Row-S (SS), 3, Row-X (SX), 4, Share, 5, S/Row-X (SSX), 6, Exclusive, A.lmode) lmode, DECODE(TRUNC(ctime / 86400), 0, TO_CHAR(TO_DATE(ctime, SSSSS), HH24:MI:SS), TRUNC(ctime / 86400) || Days || TO_CHAR(TO_DATE(ctime - (TRUNC(ctime / 86400)) * 86400, SSSSS), HH24:MI:SS)) TIME, A.TYPE, alter system kill session || || a.SID || , || b.serial# || || immediate; kill_session, DECODE(object_type, NULL, NULL, Dbms_Rowid.rowid_create(1, || row_wait_obj# || , || row_wait_file# || , || row_wait_block# || , || row_wait_row# || )) row_id FROM v$lock A, v$session b, dba_objects o WHERE A.SID b.SID AND (lmode 0 OR BLOCK 1) AND o.object_id() DECODE(b.ROW_WAIT_OBJ#, -1, NULL, b.ROW_WAIT_OBJ#) ORDER BY A.id1, A.request; --Tip: The last column of the query for blocked sessions is ROWID of the record!!!! --To find the exact locked row execute: --SELECT * FROM OBJECT WHERE ROWID DbmsRowid.rowid_create(1, 48804, 31, 114809, 210); --單實例阻塞會話查詢 --cascade blocking /*set lines 200 pages 100 col tree for a30 col event for a40*/ select * from (select alter system kill session || || a.SID || , || a.serial# || || immediate; kill_session, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(SID, -) tree, level as tree_level from v$session a start with a.blocking_session is not null connect by nocycle a.sid prior a.blocking_session) where isleaf 1 order by tree_level asc; --RAC實例阻塞會話查詢 --cascade blockinggv$session select * from (select a.inst_id, alter system kill session || || a.SID || , || a.serial# || || immediate; kill_session, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(a.SID || || a.inst_id, - ) tree, level as tree_level from gv$session a start with a.blocking_session is not null connect by (a.sid || || a.inst_id) prior (a.blocking_session || || a.blocking_instance)) where isleaf 1 order by tree_level asc; --alter system kill session 150,8742 immediate; select p.spid from gv$session s,gv$process p where s.sidxxx and s.paddrp.addr; --檢測數(shù)據(jù)庫各實例session 阻塞 tree的sql 檢測數(shù)據(jù)庫各實例session 阻塞 tree的sql --由于使用到了 v$sesion 的 blocking_instance,blocking_session 這兩列所以數(shù)據(jù)庫的最低版本為oracle 10g。 ---適合單實例和多實實例數(shù)據(jù)庫一個SQL搞定。 with/* materialized */ aa as ( select * from gv$session s where 11 ) select alter system kill session || t.SID || , || t.SERIAL# || immediate; as kill_SID, lpad(, 2 * level - 1) ||t.inst_sid as leveL_inst_sid, level level_flag, sys_connect_by_path(t.inst_sid,/) as inst_sid_path, connect_by_root t.inst_sid as root_inst_sid, connect_by_isleaf as node_isleaf, ########## as FLAGFLAG0, t.status, t.event#, t.event, t.program, t.module, t.action, t.client_identifier, t.OSUSER os_USER, t.machine os_machine, t.logon_time logon_time, t.last_call_et last_call_et, alter system kill session || t.SID || , || t.SERIAL# || immediate; as kill_SID, ########## as FLAGFLAG/*, t.* */ from ( select gs.inst_id||-||gs.sid as inst_sid, gs.blocking_instance||-||gs.blocking_session par_inst_sid, gs.* from aa gs where 11 and (gs.inst_id,gs.sid ) in ( --被阻塞者 select s.inst_id,s.sid from aa s where 11 and s.BLOCKING_SESSION is not null union all --阻塞者 select sw.inst_id,sw.sid from aa sw where 11 --and lower(sw.MACHINE) not like %ejb% and (sw.INST_ID,sw.SID) in ( select/* hash_sj*/ s.BLOCKING_INSTANCE, s.BLOCKING_SESSION from aa s where 11 and s.BLOCKING_SESSION is not null ) ) ) t where 11 connect by prior t.inst_sidt.par_inst_sid order siblings by t.inst_sid ; --查看 dba_hist_active_sess_history with/* materialized */ aa as ( select instance_number as inst_id, session_id as sid, session_serial# as SERIAL#, blocking_inst_id as blocking_instance, blocking_session, blocking_session_serial#, event, p1, p2, p3, sql_id from dba_hist_active_sess_history h where 11 and to_char(sample_time, yyyymmddhh24miss) between 20190808143109 and 20190808143109 order by sample_time ) select lpad(, 2 * level - 1) ||t.inst_sid as leveL_inst_sid, level level_flag, sys_connect_by_path(t.inst_sid,/) as inst_sid_path, connect_by_root t.inst_sid as root_inst_sid, connect_by_isleaf as node_isleaf, ########## as FLAGFLAG0, t.event, t.p1, t.p2, t.p3, t.sql_id, ########## as FLAGFLAG/*, t.* */ from ( select gs.inst_id||-||gs.sid as inst_sid, gs.blocking_instance||-||gs.blocking_session par_inst_sid, gs.* from aa gs where 11 and (gs.inst_id,gs.sid ) in ( --被阻塞者 select s.inst_id,s.sid from aa s where 11 and s.BLOCKING_SESSION is not null union all --阻塞者 select sw.inst_id,sw.sid from aa sw where 11 --and lower(sw.MACHINE) not like %ejb% and (sw.INST_ID,sw.SID) in ( select/* hash_sj*/ s.BLOCKING_INSTANCE, s.BLOCKING_SESSION from aa s where 11 and s.BLOCKING_SESSION is not null ) ) ) t where 11 connect by prior t.inst_sidt.par_inst_sid order siblings by t.inst_sid ; -- --1.no-rac -- select a.*, row_number() over(partition by blocking_source order by LEVEL asc) as rn from (select * from (select lpad( , (level - 1) * 2, ) || sid as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, level, blocking_session, (prior sid) as blocking_sid, (prior serial#) as blocking_serial#, (prior sql_id) as blocking_sql_id, sys_connect_by_path(sid, -) as blocking_path, connect_by_root(sid) as blocking_source from v$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1 union select to_char(sid) as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, 1, null, null, null, null, null, sid from v$session where sid in (select blocking_source from (select wait_class#, username, level, connect_by_root(sid) as blocking_source from v$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1)) a -- --2.rac -- select a.*, row_number() over(partition by blocking_source order by LEVEL asc) as rn from (select * from (select inst_id, lpad( , (level - 1) * 2, ) || sid as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, level, blocking_session, (prior sid) as blocking_sid, (prior serial#) as blocking_serial#, (prior sql_id) as blocking_sql_id, sys_connect_by_path(sid, -) as blocking_path, connect_by_root(sid) as blocking_source from gv$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1 union select inst_id, to_char(sid) as sid, serial#, sql_id, paddr, username, event, machine, program, SUBSTR(numtodsinterval(seconds_in_wait, second), 12, 8) as wait_time, wait_class#, 1, null, null, null, null, null, sid from gv$session where sid in (select blocking_source from (select wait_class#, username, level, connect_by_root(sid) as blocking_source from gv$session start with blocking_session is null connect by (prior sid) blocking_session) where LEVEL 1)) a歷史會話情況select t.machine, t.session_id, t.session_serial#, t.instance_number, t.sample_time, t.blocking_session, t.blocking_session_serial#, t.blocking_inst_id, t.session_state, t.event, t.sql_exec_start, t.sql_plan_operation, t.sql_id, t.sql_opcode, t.user_id, t.session_type from dba_hist_active_sess_history t where t.sample_time to_date(2023-02-20 08:30:00, yyyy-mm-dd hh24:mi:ss) and t.sample_time to_date(2023-02-20 10:30:00, yyyy-mm-dd hh24:mi:ss) and t.session_type BACKGROUND and t.event is not NULL and t.blocking_session is not null and t.sql_opcode 0 and t.user_id 0 order by t.sample_time desc;查看近1分鐘內(nèi)的SQL執(zhí)行情況SELECT sql_id, last_active_time, executions, sql_text AS sql_preview, ROUND(elapsed_time / NULLIF(executions, 0) / 1000000, 4) AS avg_elapsed_sec FROM v$sql WHERE last_active_time SYSDATE - INTERVAL 1 MINUTE AND executions 0 ORDER BY last_active_time DESC, avg_elapsed_sec desc;