企业官网建设流程全解析

v$session介绍

主要使用v$session查询，在此之前对此时图进行简单介绍：

官网地址：V$SESSION (oracle.com)

==============================================================

v$session

==============================================================

SADDR: session address

SID: session identifier，常用于连接其它列。

SERIAL#: sid 会重用，但是同一个sid被重用时，serial#会增加，不会重复。

AUDSID: audit session id。可以通过audsid查询当前session的sid。select sid from v$session where audsid=userenv('sessionid');

PADDR: process address，关联v$process的addr字段，可以通过这个字段查处当前session对应操作系统的那个进程的id。

USER#: session's user id。等于dba_users中的user_id。Oracle内部进程的user#为0。

USERNAME: session's username。等于dba_users中的username。Oracle内部进程的username为空。

COMMAND: session正在执行的SQL Id。1代表create table，3代表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.exe

TYPE: 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 time

===========================================================

v$process

============================================================

ADDR: 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 process，null代表normal process。

===========================================================

v$sql

============================================================

V$SQL为共享池中每个独立的执行实例（子游标）提供一行记录，主要用于执行计划分析和绑定变量问题排查。

SQL_ID：SQL 语句的唯一标识符，是性能调优和关联其他视图的核心字段。

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$sqlarea

============================================================

V$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 会将该游标标记为废弃以保护共享池。

核心区别对比表

===========================================================

dba_tab_modifications

============================================================

DBA_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_address=d.address

oracle 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.ADDR=a.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 blocking@gv$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.sid=xxx and s.paddr=p.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 1=1 ) 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 1=1 and (gs.inst_id,gs.sid ) in ( --被阻塞者 select s.inst_id,s.sid from aa s where 1=1 and s.BLOCKING_SESSION is not null union all --阻塞者 select sw.inst_id,sw.sid from aa sw where 1=1 --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 1=1 and s.BLOCKING_SESSION is not null ) ) ) t where 1=1 connect by prior t.inst_sid=t.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 1=1 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 1=1 and (gs.inst_id,gs.sid ) in ( --被阻塞者 select s.inst_id,s.sid from aa s where 1=1 and s.BLOCKING_SESSION is not null union all --阻塞者 select sw.inst_id,sw.sid from aa sw where 1=1 --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 1=1 and s.BLOCKING_SESSION is not null ) ) ) t where 1=1 connect by prior t.inst_sid=t.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执行情况：