v$session介紹主要使用v$session查詢在此之前對此時圖進(jìn)行簡單介紹官網(wǎng)地址V$SESSION (oracle.com)v$sessionSADDR: session addressSID: session identifier常用于連接其它列。SERIAL#: sid 會重用但是同一個sid被重用時serial#會增加不會重復(fù)。AUDSID: audit session id?？梢酝ㄟ^audsid查詢當(dāng)前session的sid。select sid from v$session where audsiduserenv(sessionid);PADDR: process address關(guān)聯(lián)v$process的addr字段可以通過這個字段查處當(dāng)前session對應(yīng)操作系統(tǒng)的那個進(jìn)程的id。USER#: sessions user id。等于dba_users中的user_id。Oracle內(nèi)部進(jìn)程的user#為0。USERNAME: sessions username。等于dba_users中的username。Oracle內(nèi)部進(jìn)程的username為空。COMMAND: session正在執(zhí)行的SQL Id。1代表create table3代表select。TADDR: 當(dāng)前的transaction address?？梢杂脕黻P(guān)聯(lián)v$transaction的addr字段。LOCKWAIT: 可以通過這個字段查詢出當(dāng)前正在等待的鎖的相關(guān)信息。sid lockwait與v$lock中的sid kaddr相對應(yīng)。STATUS: 用來判斷session狀態(tài)。Active正執(zhí)行SQL語句。Inactive等待操作。Killed被標(biāo)注為刪除。SERVER: server type (dedicated or shared)SCHEMA#: schema user id。Oracle內(nèi)部進(jìn)程的schema#為0。SCHEMANAME: schema username。Oracle內(nèi)部進(jìn)程的schemaname為sys。OSUSER: 客戶端操作系統(tǒng)用戶名。PROCESS: 客戶端process id。MACHINE: 客戶端machine name。TERMINAL: 客戶端執(zhí)行的terminal name。PROGRAM: 客戶端應(yīng)用程序。比如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相對應(yīng)。PREV_SQL_ADDR, PREV_HASH_VALUE, PREV_SQL_ID, PREV_CHILD_NUMBER: 上一次執(zhí)行的sql statement。MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO: 應(yīng)用通過DBMS_APPLICATION_INFO設(shè)置的一些信息。FIXED_TABLE_SEQUENCE: 當(dāng)session完成一個user call后就會增加的一個數(shù)值也就是說如果session inactive它就不會增加。因此可以根據(jù)此字段的值變化來監(jiān)控某個時間點以來的session的性能情況。例如一個小時以前某個session的FIXED_TABLE_SEQUENCE是10000而現(xiàn)在是20000則表明一個小時內(nèi)其user call比較頻繁可以重點關(guān)注此session的performance statistics。ROW_WAIT_OBJ#: 被鎖定行所在table的object_id。和dba_objects中的object_id關(guān)聯(lián)可以得到被鎖定的table name。ROW_WAIT_FILE#: 被鎖定行所在的datafile id。和v$datafile中的file#關(guān)聯(lián)可以得到datafile name。ROW_WAIT_BLOCK#: Identifier for the block containing the row specified in ROW_WAIT_ROW#ROW_WAIT_ROW#: session當(dāng)前正在等待的被鎖定的行。LOGON_TIME: session logon timev$processADDR: process address?？梢院蛌$session的paddr字段關(guān)聯(lián)。PID: Oracle進(jìn)程identifier。SPID: 操作系統(tǒng)進(jìn)程identifier。USERNAME: 操作系統(tǒng)進(jìn)程的用戶名。并非Oracle用戶名。SERIAL#:: process serial number。TERMINAL: 操作系統(tǒng)terminal identifier(e.g., computer name)。PROGRAM: 進(jìn)程正在執(zhí)行的程序(e.g., ORACLE.EXE (ARC0))和v$session中的program類似。BACKGROUND: 1代表oracle background processnull代表normal process。v$sqlV$SQL為共享池中每個獨立的執(zhí)行實例子游標(biāo)提供一行記錄主要用于執(zhí)行計劃分析和綁定變量問題排查。SQL_IDSQL 語句的唯一標(biāo)識符是性能調(diào)優(yōu)和關(guān)聯(lián)其他視圖的核心字段。CHILD_NUMBER子游標(biāo)編號。當(dāng)同一條 SQL 存在多個執(zhí)行計劃時通過此字段區(qū)分。CHILD_ADDRESS子游標(biāo)在內(nèi)存中的地址。PLAN_HASH_VALUE執(zhí)行計劃的哈希值。用于識別和對比 SQL 是否生成了不同的執(zhí)行計劃。EXECUTIONS該特定子游標(biāo)自載入緩存庫后的執(zhí)行次數(shù)。BUFFER_GETS該子游標(biāo)執(zhí)行導(dǎo)致的邏輯讀讀內(nèi)存次數(shù)。DISK_READS該子游標(biāo)執(zhí)行導(dǎo)致的物理讀讀磁盤次數(shù)。ELAPSED_TIME該子游標(biāo)解析和執(zhí)行消耗的總時間微秒。CPU_TIME該子游標(biāo)解析和執(zhí)行消耗的 CPU 時間微秒。IS_BIND_SENSITIVE / IS_BIND_AWARE指示游標(biāo)是否對綁定變量敏感或已感知綁定變量用于排查自適應(yīng)游標(biāo)共享ACS相關(guān)問題。v$sqlareaV$SQLAREA將具有相同 SQL 文本相同SQL_ID的子游標(biāo)合并統(tǒng)計主要用于評估 SQL 整體資源消耗和共享池健康度分析。SQL_ID父游標(biāo)的 SQL 標(biāo)識符。VERSION_COUNT緩存中該父游標(biāo)下存在的子游標(biāo)數(shù)量。如果此值過高通常意味著存在嚴(yán)重的游標(biāo)共享問題如綁定變量類型不一致、優(yōu)化器環(huán)境不同等。SHARABLE_MEM該 SQL 占用的共享內(nèi)存總量。若存在多個子游標(biāo)則為所有子游標(biāo)共享內(nèi)存之和。EXECUTIONS所有子游標(biāo)執(zhí)行該語句的總次數(shù)。BUFFER_GETS / DISK_READS所有子游標(biāo)執(zhí)行導(dǎo)致的邏輯讀和物理讀總次數(shù)。ELAPSED_TIME / CPU_TIME所有子游標(biāo)消耗的總時間和 CPU 時間微秒。FIRST_LOAD_TIME該 SQL 首次被載入共享池的時間戳。LAST_LOAD_TIME該 SQL 查詢計劃最后一次被載入庫緩存的時間。IS_OBSOLETE指示游標(biāo)是否已廢棄Y/N。當(dāng)子游標(biāo)數(shù)量過多時Oracle 會將該游標(biāo)標(biāo)記為廢棄以保護(hù)共享池。核心區(qū)別對比表對比維度V$SQLV$SQLAREA數(shù)據(jù)粒度細(xì)粒度子游標(biāo)級別粗粒度父游標(biāo)級別記錄行數(shù)相同 SQL 文本可能對應(yīng)多條記錄相同 SQL 文本僅對應(yīng)一條記錄統(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ù)操作語言變更情況。出于保護(hù)數(shù)據(jù)庫性能的考慮該視圖中的數(shù)據(jù)不是實時更新的。Oracle 會將內(nèi)存中的 DML 變動信息定期大約每 15 分鐘由 SMON 后臺進(jìn)程刷入數(shù)據(jù)字典基表。如果您剛執(zhí)行完 DML 操作想要立即在視圖中看到最新變動需要手動執(zhí)行以下存儲過程來強(qiáng)制刷新內(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ù)量查看當(dāng)前阻塞對象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 ;查看當(dāng)前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字段中的命令即可。查看當(dāng)前被鎖對象并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進(jìn)程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é)--查看阻塞進(jìn)程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查詢在此之前對此時圖進(jìn)行簡單介紹官網(wǎng)地址V$SESSION (oracle.com)v$sessionSADDR: session addressSID: session identifier常用于連接其它列。SERIAL#: sid 會重用但是同一個sid被重用時serial#會增加不會重復(fù)。AUDSID: audit session id?？梢酝ㄟ^audsid查詢當(dāng)前session的sid。select sid from v$session where audsiduserenv(sessionid);PADDR: process address關(guān)聯(lián)v$process的addr字段可以通過這個字段查處當(dāng)前session對應(yīng)操作系統(tǒng)的那個進(jìn)程的id。USER#: sessions user id。等于dba_users中的user_id。Oracle內(nèi)部進(jìn)程的user#為0。USERNAME: sessions username。等于dba_users中的username。Oracle內(nèi)部進(jìn)程的username為空。COMMAND: session正在執(zhí)行的SQL Id。1代表create table3代表select。TADDR: 當(dāng)前的transaction address?？梢杂脕黻P(guān)聯(lián)v$transaction的addr字段。LOCKWAIT: 可以通過這個字段查詢出當(dāng)前正在等待的鎖的相關(guān)信息。sid lockwait與v$lock中的sid kaddr相對應(yīng)。STATUS: 用來判斷session狀態(tài)。Active正執(zhí)行SQL語句。Inactive等待操作。Killed被標(biāo)注為刪除。SERVER: server type (dedicated or shared)SCHEMA#: schema user id。Oracle內(nèi)部進(jìn)程的schema#為0。SCHEMANAME: schema username。Oracle內(nèi)部進(jìn)程的schemaname為sys。OSUSER: 客戶端操作系統(tǒng)用戶名。PROCESS: 客戶端process id。MACHINE: 客戶端machine name。TERMINAL: 客戶端執(zhí)行的terminal name。PROGRAM: 客戶端應(yīng)用程序。比如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相對應(yīng)。PREV_SQL_ADDR, PREV_HASH_VALUE, PREV_SQL_ID, PREV_CHILD_NUMBER: 上一次執(zhí)行的sql statement。MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO: 應(yīng)用通過DBMS_APPLICATION_INFO設(shè)置的一些信息。FIXED_TABLE_SEQUENCE: 當(dāng)session完成一個user call后就會增加的一個數(shù)值也就是說如果session inactive它就不會增加。因此可以根據(jù)此字段的值變化來監(jiān)控某個時間點以來的session的性能情況。例如一個小時以前某個session的FIXED_TABLE_SEQUENCE是10000而現(xiàn)在是20000則表明一個小時內(nèi)其user call比較頻繁可以重點關(guān)注此session的performance statistics。ROW_WAIT_OBJ#: 被鎖定行所在table的object_id。和dba_objects中的object_id關(guān)聯(lián)可以得到被鎖定的table name。ROW_WAIT_FILE#: 被鎖定行所在的datafile id。和v$datafile中的file#關(guān)聯(lián)可以得到datafile name。ROW_WAIT_BLOCK#: Identifier for the block containing the row specified in ROW_WAIT_ROW#ROW_WAIT_ROW#: session當(dāng)前正在等待的被鎖定的行。LOGON_TIME: session logon timev$processADDR: process address?？梢院蛌$session的paddr字段關(guān)聯(lián)。PID: Oracle進(jìn)程identifier。SPID: 操作系統(tǒng)進(jìn)程identifier。USERNAME: 操作系統(tǒng)進(jìn)程的用戶名。并非Oracle用戶名。SERIAL#:: process serial number。TERMINAL: 操作系統(tǒng)terminal identifier(e.g., computer name)。PROGRAM: 進(jìn)程正在執(zhí)行的程序(e.g., ORACLE.EXE (ARC0))和v$session中的program類似。BACKGROUND: 1代表oracle background processnull代表normal process。v$sqlV$SQL為共享池中每個獨立的執(zhí)行實例子游標(biāo)提供一行記錄主要用于執(zhí)行計劃分析和綁定變量問題排查。SQL_IDSQL 語句的唯一標(biāo)識符是性能調(diào)優(yōu)和關(guān)聯(lián)其他視圖的核心字段。CHILD_NUMBER子游標(biāo)編號。當(dāng)同一條 SQL 存在多個執(zhí)行計劃時通過此字段區(qū)分。CHILD_ADDRESS子游標(biāo)在內(nèi)存中的地址。PLAN_HASH_VALUE執(zhí)行計劃的哈希值。用于識別和對比 SQL 是否生成了不同的執(zhí)行計劃。EXECUTIONS該特定子游標(biāo)自載入緩存庫后的執(zhí)行次數(shù)。BUFFER_GETS該子游標(biāo)執(zhí)行導(dǎo)致的邏輯讀讀內(nèi)存次數(shù)。DISK_READS該子游標(biāo)執(zhí)行導(dǎo)致的物理讀讀磁盤次數(shù)。ELAPSED_TIME該子游標(biāo)解析和執(zhí)行消耗的總時間微秒。CPU_TIME該子游標(biāo)解析和執(zhí)行消耗的 CPU 時間微秒。IS_BIND_SENSITIVE / IS_BIND_AWARE指示游標(biāo)是否對綁定變量敏感或已感知綁定變量用于排查自適應(yīng)游標(biāo)共享ACS相關(guān)問題。v$sqlareaV$SQLAREA將具有相同 SQL 文本相同SQL_ID的子游標(biāo)合并統(tǒng)計主要用于評估 SQL 整體資源消耗和共享池健康度分析。SQL_ID父游標(biāo)的 SQL 標(biāo)識符。VERSION_COUNT緩存中該父游標(biāo)下存在的子游標(biāo)數(shù)量。如果此值過高通常意味著存在嚴(yán)重的游標(biāo)共享問題如綁定變量類型不一致、優(yōu)化器環(huán)境不同等。SHARABLE_MEM該 SQL 占用的共享內(nèi)存總量。若存在多個子游標(biāo)則為所有子游標(biāo)共享內(nèi)存之和。EXECUTIONS所有子游標(biāo)執(zhí)行該語句的總次數(shù)。BUFFER_GETS / DISK_READS所有子游標(biāo)執(zhí)行導(dǎo)致的邏輯讀和物理讀總次數(shù)。ELAPSED_TIME / CPU_TIME所有子游標(biāo)消耗的總時間和 CPU 時間微秒。FIRST_LOAD_TIME該 SQL 首次被載入共享池的時間戳。LAST_LOAD_TIME該 SQL 查詢計劃最后一次被載入庫緩存的時間。IS_OBSOLETE指示游標(biāo)是否已廢棄Y/N。當(dāng)子游標(biāo)數(shù)量過多時Oracle 會將該游標(biāo)標(biāo)記為廢棄以保護(hù)共享池。核心區(qū)別對比表對比維度V$SQLV$SQLAREA數(shù)據(jù)粒度細(xì)粒度子游標(biāo)級別粗粒度父游標(biāo)級別記錄行數(shù)相同 SQL 文本可能對應(yīng)多條記錄相同 SQL 文本僅對應(yīng)一條記錄統(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ù)操作語言變更情況。出于保護(hù)數(shù)據(jù)庫性能的考慮該視圖中的數(shù)據(jù)不是實時更新的。Oracle 會將內(nèi)存中的 DML 變動信息定期大約每 15 分鐘由 SMON 后臺進(jìn)程刷入數(shù)據(jù)字典基表。如果您剛執(zhí)行完 DML 操作想要立即在視圖中看到最新變動需要手動執(zhí)行以下存儲過程來強(qiáng)制刷新內(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ù)量查看當(dāng)前阻塞對象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 ;查看當(dāng)前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字段中的命令即可。查看當(dāng)前被鎖對象并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進(jìn)程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é)--查看阻塞進(jìn)程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;