v$session介绍主要使用v$session查询在此之前对此时图进行简单介绍官网地址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关联v$process的addr字段可以通过这个字段查处当前session对应操作系统的那个进程的id。USER#: sessions user id。等于dba_users中的user_id。Oracle内部进程的user#为0。USERNAME: sessions username。等于dba_users中的username。Oracle内部进程的username为空。COMMAND: session正在执行的SQL Id。1代表create table3代表select。TADDR: 当前的transaction address。可以用来关联v$transaction的addr字段。LOCKWAIT: 可以通过这个字段查询出当前正在等待的锁的相关信息。sid lockwait与v$lock中的sid kaddr相对应。STATUS: 用来判断session状态。Active正执行SQL语句。Inactive等待操作。Killed被标注为删除。SERVER: server type (dedicated or shared)SCHEMA#: schema user id。Oracle内部进程的schema#为0。SCHEMANAME: schema username。Oracle内部进程的schemaname为sys。OSUSER: 客户端操作系统用户名。PROCESS: 客户端process id。MACHINE: 客户端machine name。TERMINAL: 客户端执行的terminal name。PROGRAM: 客户端应用程序。比如ORACLE.EXE (PMON)或者sqlplus.exeTYPE: session type (background or user)SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER: session正在执行的sql statement和v$sql中的address, hash_value, sql_id, child_number相对应。PREV_SQL_ADDR, PREV_HASH_VALUE, PREV_SQL_ID, PREV_CHILD_NUMBER: 上一次执行的sql statement。MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO: 应用通过DBMS_APPLICATION_INFO设置的一些信息。FIXED_TABLE_SEQUENCE: 当session完成一个user call后就会增加的一个数值也就是说如果session inactive它就不会增加。因此可以根据此字段的值变化来监控某个时间点以来的session的性能情况。例如一个小时以前某个session的FIXED_TABLE_SEQUENCE是10000而现在是20000则表明一个小时内其user call比较频繁可以重点关注此session的performance statistics。ROW_WAIT_OBJ#: 被锁定行所在table的object_id。和dba_objects中的object_id关联可以得到被锁定的table name。ROW_WAIT_FILE#: 被锁定行所在的datafile id。和v$datafile中的file#关联可以得到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。可以和v$session的paddr字段关联。PID: Oracle进程identifier。SPID: 操作系统进程identifier。USERNAME: 操作系统进程的用户名。并非Oracle用户名。SERIAL#:: process serial number。TERMINAL: 操作系统terminal identifier(e.g., computer name)。PROGRAM: 进程正在执行的程序(e.g., ORACLE.EXE (ARC0))和v$session中的program类似。BACKGROUND: 1代表oracle background processnull代表normal process。v$sqlV$SQL为共享池中每个独立的执行实例子游标提供一行记录主要用于执行计划分析和绑定变量问题排查。SQL_IDSQL 语句的唯一标识符是性能调优和关联其他视图的核心字段。CHILD_NUMBER子游标编号。当同一条 SQL 存在多个执行计划时通过此字段区分。CHILD_ADDRESS子游标在内存中的地址。PLAN_HASH_VALUE执行计划的哈希值。用于识别和对比 SQL 是否生成了不同的执行计划。EXECUTIONS该特定子游标自载入缓存库后的执行次数。BUFFER_GETS该子游标执行导致的逻辑读读内存次数。DISK_READS该子游标执行导致的物理读读磁盘次数。ELAPSED_TIME该子游标解析和执行消耗的总时间微秒。CPU_TIME该子游标解析和执行消耗的 CPU 时间微秒。IS_BIND_SENSITIVE / IS_BIND_AWARE指示游标是否对绑定变量敏感或已感知绑定变量用于排查自适应游标共享ACS相关问题。v$sqlareaV$SQLAREA将具有相同 SQL 文本相同SQL_ID的子游标合并统计主要用于评估 SQL 整体资源消耗和共享池健康度分析。SQL_ID父游标的 SQL 标识符。VERSION_COUNT缓存中该父游标下存在的子游标数量。如果此值过高通常意味着存在严重的游标共享问题如绑定变量类型不一致、优化器环境不同等。SHARABLE_MEM该 SQL 占用的共享内存总量。若存在多个子游标则为所有子游标共享内存之和。EXECUTIONS所有子游标执行该语句的总次数。BUFFER_GETS / DISK_READS所有子游标执行导致的逻辑读和物理读总次数。ELAPSED_TIME / CPU_TIME所有子游标消耗的总时间和 CPU 时间微秒。FIRST_LOAD_TIME该 SQL 首次被载入共享池的时间戳。LAST_LOAD_TIME该 SQL 查询计划最后一次被载入库缓存的时间。IS_OBSOLETE指示游标是否已废弃Y/N。当子游标数量过多时Oracle 会将该游标标记为废弃以保护共享池。核心区别对比表对比维度V$SQLV$SQLAREA数据粒度细粒度子游标级别粗粒度父游标级别记录行数相同 SQL 文本可能对应多条记录相同 SQL 文本仅对应一条记录统计方式单个执行实例的原始数据相同 SQL 文本所有实例的聚合汇总适用场景执行计划分析、绑定变量泄漏检测SQL 整体性能评估、共享池分析dba_tab_modificationsDBA_TAB_MODIFICATIONS是一个用于监控表数据变动情况的核心系统视图。它记录了数据库中所有表自上一次收集统计信息以来发生的 DML数据操作语言变更情况。出于保护数据库性能的考虑该视图中的数据不是实时更新的。Oracle 会将内存中的 DML 变动信息定期大约每 15 分钟由 SMON 后台进程刷入数据字典基表。如果您刚执行完 DML 操作想要立即在视图中看到最新变动需要手动执行以下存储过程来强制刷新内存中的监控信息EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;TABLE_OWNER被修改表的拥有者Schema 名称。TABLE_NAME被修改的表名。PARTITION_NAME被修改的分区名称如果是分区表。SUBPARTITION_NAME被修改的子分区名称。INSERTS自上次收集统计信息以来插入操作的近似行数。UPDATES自上次收集统计信息以来更新操作的近似行数。DELETES自上次收集统计信息以来删除操作的近似行数。TIMESTAMP该表最后一次被修改的时间戳。TRUNCATED自上次分析以来该表是否被 TRUNCATE 过YES 或 NO。DROP_SEGMENTS自上次分析以来被删除的分区或子分区段Segments的数量查看当前阻塞对象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会话执行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;总结--查看阻塞进程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; --检测数据库各实例session 阻塞 tree的sql 检测数据库各实例session 阻塞 tree的sql --由于使用到了 v$sesion 的 blocking_instance,blocking_session 这两列所以数据库的最低版本为oracle 10g。 ---适合单实例和多实实例数据库一个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分钟内的SQL执行情况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;