1. 场景切入一条SQL拖垮了生产库你只有五分钟定位先讲一个我上周实际经历的场面。业务方打电话来说财务月结报表页面打不开整个系统像死了一样监控大屏上数据库CPU直接飙到99%。我登录服务器一看活动会话数一百多个几乎全部卡在同一个等待事件上。这时候你根本没有时间慢慢分析第一反应必须是现在到底在跑什么SQL谁发起的跑了多久这就是Oracle 12c里查看执行过的SQL及当前正在执行的SQL这套查询能力的价值所在。无论你是DBA、运维开发还是后端工程师只要你跟Oracle数据库打交道总会遇到这样的时刻一条性能极差的SQL把数据库拖垮或者应用的慢查询需要追溯历史执行记录来做优化。掌握这套查询手段等于在事故现场多了个行车记录仪既能看清楚当前发生了什么也能复盘过去一段时间里到底哪些SQL在作妖。本文要聊的不只是一两条查询语句的堆砌而是把这套排查体系讲透当前正在执行的SQL怎么抓历史执行过的SQL能从哪些视角去查这些视图之间的差异在哪里以及在真实生产环境里你一定会踩到的坑。内容基于Oracle 12c版本但绝大多数视图和思路在11g、19c乃至21c上同样成立。适合谁看刚接触Oracle的新人可以用它建立起会话—SQL—统计数据这条基础排查链路有几年经验的老手也能从这里看到一些平时容易忽略的细节比如SQL文本截断、RAC多节点视角、绑定变量捕获时机这些坑。2. 正在执行的SQL怎么抓从会话到SQL文本的完整链路查正在执行的SQL本质上是查正在执行的会话。Oracle的会话和SQL是一对多的关系一个会话当前最多只有一个正在执行的SQL语句这个SQL的标识就是SQL_ID。所以完整的抓取链路分三步先定位会话再根据SQL_ID拿SQL文本最后拼出完整语句。2.1 第一步v$session定位会话拿到SQL_IDv$session是整条链路的地基。它记录了当前所有连接到数据库的会话信息包括用户名、状态、正在等待的事件、最后调用时间等等。执行下面这条查询就能把正在跑SQL的活跃会话抓出来SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.sql_child_number, s.event, s.wait_class, s.machine, s.program, s.module, s.action, s.last_call_et FROM v$session s WHERE s.status ACTIVE AND s.username IS NOT NULL ORDER BY s.last_call_et DESC;几个字段的含义你得先搞明白status ACTIVE表示会话当前正在执行操作而INACTIVE表示会话空闲正在等用户输入或者等下一个请求。注意ACTIVE不一定代表在跑SQL它也可能正在等待I/O或者锁但至少说明这个会话有动静。last_call_et是会话最后一次调用结束后到现在的秒数。这个值越大说明当前这条操作耗时越长——通常情况下这就是你最需要关注的那条SQL。event和wait_class告诉你这个会话卡在什么地方。如果是library cache lock那是并发解析问题如果是db file sequential read多半是索引扫描在读数据如果是CPU used by this session说明它在纯消耗CPU大概率是大查询或死循环。这条查询会返回多行怎么判断哪个才是罪魁祸首我的原则是看last_call_et最大且event严重异常的那一行。比如全部会话都卡在enq: TX - row lock contention上说明有锁没释放这时候顺着会话之间的阻塞关系追源头才有效光看SQL文本本身反而不一定能立刻发现问题。2.2 第二步SQL文本被截断了怎么办通过v$session拿到的只有sql_id不直接包含完整SQL文本。最直接的补全方式是关联v$sql或v$sqlareaSELECT s.sql_id, sq.sql_text FROM v$session s, v$sqlarea sq WHERE s.sql_id sq.sql_id AND s.sid sid;但这里有个非常经典的坑v$sqlarea里的sql_text字段最长只显示1000个字符1000字符不够用的SQL比比皆是尤其是那些拼接了几十个查询字段的报表SQL。一旦被截断你看到的语句是不完整的老天爷后半段可能正好藏着关键问题。正确的做法是用v$sqltext这个视图。它不是把SQL当成一整行存储而是把长SQL按64个字符一段拆成多片通过piece字段排序就能拼回完整文本SELECT sql_id, piece, sql_text FROM v$sqltext WHERE sql_id sql_id ORDER BY piece;如果你希望拼回的SQL保留原来的换行格式v$sqltext默认把换行替换成空格用v$sqltext_with_newlines替代即可。实际排障时我一般直接把SID传给脚本让数据库自动关联v$session和v$sqltext一次性输出完整语句SELECT sql_id, piece, sql_text FROM v$sqltext_with_newlines WHERE sql_id (SELECT sql_id FROM v$session WHERE sid sid AND serial# serial) ORDER BY piece;这段逻辑看着简单但很多人在现场一紧张就忘了还有分片这回事拿着截断后的SQL分析半天方向完全跑偏。记住只要SQL文本以一堆空格结尾、看不出完整语义先怀疑被截断立刻改用v$sqltext再查一遍。2.3 实测印证状态、等待事件和SQL文本要对着看工具给你的是孤立数据你得把它们串成一个完整画面。我举个真实例子之前排查一个夜间的批处理作业卡死问题查出来是这样一个会话status ACTIVElast_call_et已经6000多秒event显示的是enq: TX - index contentionSQL的文本片段里能看到INSERT INTO ...和CREATE INDEX的关键字样。三个信息放在一起解读一个运行了快两个小时的INSERT语句它在等待索引上的锁。为什么等因为另一个会话正在同一张表上做DDL操作比如加列、重建索引这个INSERT被DDL锁挡住了。顺着这个思路用v$lock一查果然找到了一个长时间未提交的DDL事务。如果只看SQL文本不看等待事件你只会觉得INSERT跑得慢锁定不了根因。所以我的建议是抓当前SQL不是单查一个视图而是用v$session拿到状态和等待事件用v$sqltext拿到完整语句再用v$lock确认锁的持有方三者交叉验证才能确定问题。3. 历史执行过的SQL共享池视角和AWR视角的取舍查看执行过的SQL其实是两个完全不同的需求。一种是查最近执行过、还留在内存里的SQL另一种是查过去任意时间段执行过的SQL统计。Oracle 12c给前者提供了v$SQL系列视图给后者提供了DBA_HIST_SQLSTAT这套AWR快照表。两条路线的数据来源、保留周期、能查到的字段差别很大用错了地方就会得出错误结论。3.1 v$sql和v$sqlarea还在共享池里的近期记忆先理解一个底层机制Oracle执行任何SQL时都会先做语法解析和优化生成执行计划存在共享池Shared Pool里。只要这个游标还没被挤出去你就能在v$sql或v$sqlarea里看到它的记录。这两个视图的区别很关键v$sqlarea按照SQL文本聚合同一个SQL_ID只显示一行取的是父游标Parent Cursor的汇总信息。适合看这条SQL整体的执行情况。v$sql子游标Child Cursor级别同一个SQL_ID可能有多行每行对应一个不同的执行计划版本或环境比如优化器参数变了、绑定变量长度变了各自有独立的执行次数、逻辑读、物理读统计。日常查看历史SQL使用v$sqlarea就够我用得最多的查询是这份SELECT sql_id, sql_text, executions, elapsed_time / 1000000 AS elapsed_sec, cpu_time / 1000000 AS cpu_sec, buffer_gets, disk_reads, rows_processed, first_load_time, last_load_time, last_active_time, optimizer_cost FROM v$sqlarea WHERE sql_text LIKE %业务表名% ORDER BY elapsed_time DESC;这份查询的输出要盯住几个字段elapsed_time的单位是微秒除以1000000才是秒。我见过太多人直接拿这个值跟秒做比较偏差百万倍差点误判问题严重程度。first_load_time和last_load_time都是VARCHAR2类型格式类似2024-01-15/10:30:00表示首次加载和最后一次执行的时间。last_active_time则是DATE类型表示游标最后一次活跃的时间。executions为0的SQL要注意说明它只是被解析了但没执行完或者只是被硬解析过。rows_processed能告诉你这个SQL平均每次处理多少行结合buffer_gets就能算出每行消耗多少逻辑读这是判断SQL是否高效的核心指标。典型场景业务凌晨跑批慢你想知道是不是某个新上线的SQL搅局。在v$sqlarea里按first_load_time筛出当天新出现的SQL再按elapsed_time排序基本一眼就能锁定嫌疑对象。3.2 DBA_HIST_SQLSTATAWR快照里的历史档案共享池是有限的内存SQL一多老游标很快就会被挤出。如果你想查的是上周三下午三点到底跑了哪些SQLv$sqlarea里大概率找不到了。这时候要靠AWRAutomatic Workload Repository的历史快照。Oracle每隔一段时间默认一小时会把数据库的性能数据快照下来其中就包括每条SQL的执行次数、耗时、资源消耗。DBA_HIST_SQLSTAT就是存储这些SQL统计的快照表它和DBA_HIST_SNAPSHOT通过snap_id和instance_number关联。查询方式如下SELECT sn.snap_id, sn.begin_interval_time, sn.end_interval_time, st.sql_id, st.executions_delta, st.elapsed_time_delta / 1000000 AS elapsed_sec_delta, st.cpu_time_delta / 1000000 AS cpu_sec_delta, st.buffer_gets_delta, st.disk_reads_delta, st.rows_processed_delta, st.optimizer_cost FROM dba_hist_sqlstat st JOIN dba_hist_snapshot sn ON st.snap_id sn.snap_id AND st.instance_number sn.instance_number WHERE st.sql_id sql_id ORDER BY sn.snap_id;注意字段名的后缀_delta快照表里存的是这个快照间隔内新增的数量是增量值。比如executions_delta表示两个快照点之间这个SQL执行了多少次elapsed_time_delta表示这段时间内累计消耗的秒数。理解了这点你就知道为什么同一个SQL_ID在DBA_HIST_SQLSTAT里会有多行——每个快照间隔都有独立记录。AWR数据默认保留8天由DBMS_WORKLOAD_REPOSITORY的RETENTION参数控制所以你能回溯的范围就是这几天。要查更长的历史只能靠提前导出AWR数据或搭建专门的性能历史库。一个很实用的场景没有监控工具的情况下业务反馈前天晚上系统卡了一下你可以先把时间范围圈出来找几个时间段的SNAP_ID然后查sqlstat里elapsed_time_delta最大的那些SQLSELECT st.snap_id, st.sql_id, ROUND(st.elapsed_time_delta / 1000000, 2) AS elapsed_sec, st.executions_delta FROM dba_hist_sqlstat st WHERE st.snap_id BETWEEN begin_snap AND end_snap ORDER BY st.elapsed_time_delta DESC FETCH FIRST 20 ROWS ONLY;3.3 两种历史视角的对比什么场景用哪个我整理了一份对照表帮你快速决策对比维度v$sqlarea / v$sqlDBA_HIST_SQLSTAT数据来源共享池中缓存的游标AWR定期快照保留时间不确定取决于游标是否被挤出默认约8天可配置时间粒度只有首次/最后执行时间按快照间隔统计是否能查SQL文本直接有SQL_TEXT字段需关联DBA_HIST_SQLTEXT是否能看执行计划可以DISPLAY_CURSOR可以DISPLAY_AWR适合场景正在排查、最近执行的SQL回溯历史性能、趋势分析实际使用时我通常是先v$后AWR的原则先查v$sqlarea如果SQL还在且统计数据只有几条够用如果已经被挤出去了再切AWR。反过来做长期优化分析时就只看AWR因为v$sql的游标来来回回统计根本不连续。这里还需要提一个查询文本的方法DBA_HIST_SQLSTAT不直接存SQL文本文本在DBA_HIST_SQLTEXT表里通过SQL_ID关联SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE sql_id sql_id;多说一句AWR里的SQL文本同样会被截断好在日常定位问题到SQL_ID级别通常就够了具体完整文本再用v$sqltext或去应用源码里对应。4. 进阶绑定变量、执行计划和SQL执行全生命周期串联抓到SQL_ID只是第一步。生产环境里随便一条SQL可能关联着几百个执行计划、几千次绑定变量变换你得学会把SQL_ID当作一把钥匙去打开周边一圈数据。4.1 绑定变量抓真实值v$sql_bind_capture这也是个高频需求。SQL文本里看到的是WHERE order_id :b1但业务方会追问到底哪个订单值导致查询慢。答案在v$sql_bind_capture它保存了游标执行时捕获到的绑定变量值SELECT name, position, datatype_string, value_string, last_captured FROM v$sql_bind_capture WHERE sql_id sql_id ORDER BY position;这条查询有两个值得注意的地方last_captured表示这个绑定值最近被捕获的时间结合v$sqlarea的last_active_time能判断数据的时效性。绑定变量不是每次执行都会重新捕获Oracle只在某些情况下比如游标首次生成、自适应游标发生变化时采集快照所以value_string可能滞后别把它当成100%的每次执行真实值。更精确的做法是查询v$active_session_history里的CURRENT_OBJ#配合其他信息或者开SQL Trace但日常定位到量级基本够用。典型场景一个按用户ID查询的业务SQL平时毫秒级返回某天突然超时。翻v$sql_bind_capture发现捕获到的value_string是一个异常巨大的ID马上意识到是数据倾斜问题——这个用户的订单量指数级大于其他用户索引失效全表扫描。没有绑定变量捕获功能你只能去应用日志里大海捞针。4.2 执行计划确认DBMS_XPLAN的两条路子执行计划是判断SQL性能问题的核心依据。根据游标是否还在共享池分两种查法。游标还在时的实时执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, 0, TYPICAL));其中第二个参数是子游标编号对应v$sql里的child_number通常填0即可。第三个参数控制显示格式可以改成ALLSTATS LAST来查看实际执行统计。游标已经被挤出共享池时改用AWR中保存的执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id));执行计划要看什么我的排序是先看Cost和Rows是否明显偏离实际再看Operation里有没有TABLE ACCESS FULL出现在大表上最后关心Predicate Information里的ACCESS和FILTER条件——ACCESS表示用到了索引匹配FILTER表示只是把数据捞出来后过滤两者性能天差地别。4.3 生命周期串联从快照到会话到SQL的无缝追溯12c里最有价值的排查路径是把几个视图串成一条时间线。举个例子某天下午3点整系统出现一次明显的性能抖动。第一步查DBA_HIST_ACTIVE_SESS_HISTORYASH历史会话表看看3点前后哪些会话处于活跃状态、在等什么事件SELECT sample_time, session_id, session_serial#, sql_id, event, wait_class, time_waited FROM dba_hist_active_sess_history WHERE sample_time BETWEEN TO_TIMESTAMP(2024-01-15 14:55:00, YYYY-MM-DD HH24:MI:SS) AND TO_TIMESTAMP(2024-01-15 15:05:00, YYYY-MM-DD HH24:MI:SS) ORDER BY sample_time;第二步找到time_waited最大的那几个会话拿到SQL_ID。第三步回DBA_HIST_SQLSTAT里看这个SQL_ID在这个时间段内的消耗再通过DISPLAY_AWR拿执行计划。第四步如果需要更精确的当时状态用DBA_HIST_ACTIVE_SESS_HISTORY里记录的session_id反查当时连接的应用信息。这一套串下来你就能回答一个完整的业务问题下午3点那一下卡顿到底是什么SQL在执行什么操作等待在哪个资源上连接来自哪个应用服务器。5. 我在生产环境踩过的坑权限、RAC和时间字段陷阱这套查询体系原理不复杂但实战中到处是细节。我自己就在这三个点上栽过跟头写出来给你省点时间。5.1 权限不够普通用户根本看不到v$视图别以为任何数据库账号都能查v$session。v$动态性能视图的底层是X$固定表普通业务账号默认只有访问数据字典的权限直接SELECT * FROM v$session会报ORA-00942: table or view does not exist。很多开发兄弟第一次遇到这个报错还以为是视图不存在其实是权限没给。解决方案有两种给账号授予SELECT ANY DICTIONARY系统权限这个权限够用且不过度。或者直接授予SELECT_CATALOG_ROLE角色。对应的授权语句GRANT SELECT ANY DICTIONARY TO your_user;顺带提醒如果你连AWR相关视图DBA_HIST_*都访问不了需要额外授予SELECT_CATALOG_ROLE或者GRANT ADVISOR一类的角色。DBA账号天然拥有这些权限所以很多DBA自测没问题换个账号就翻车这事我在测试环境里出过不止一次。5.2 RAC环境下单节点视图会骗人Oracle 12c的RAC集群环境里v$视图是节点本地视角。你在节点1上执行SELECT * FROM v$session只能看到节点1上的会话节点2上的会话一概看不到。事故排查时如果只查了当前登录的节点很可能漏掉真正的元凶。对策很简单RAC环境一律使用gv$开头视图。v$session对应的集群视图是gv$sessionv$sql对应gv$sqlv$sqltext对应gv$sqltext。多了一个inst_id字段表示节点编号查出的数据里务必用inst_id区分和过滤SELECT inst_id, sid, serial#, username, sql_id, event, last_call_et FROM gv$session WHERE status ACTIVE ORDER BY last_call_et DESC;RAC里还有一种情况需要注意同一个SQL_ID可能在不同节点上独立解析、独立缓存。所以查gv$sql时同一SQL_ID会有多行每行对应一个节点的子游标。对比不同节点的执行次数和耗时还能顺便发现负载是否倾斜的问题。5.3 时间字段的数据类型陷阱字符串和时间混用这是一个隐蔽却致命的坑。v$sql中的first_load_time、last_load_time是VARCHAR2类型而DBA_HIST_SNAPSHOT里的begin_interval_time是TIMESTAMP(6)类型。如果你在SQL里直接把两者比较或用同一种格式函数处理轻则查不出数据重则类型转换产生隐式转换导致严重性能问题。举个例子你想筛当天首次加载的SQL在v$sqlarea里直接用WHERE first_load_time SYSDATE是错的因为VARCHAR2和DATE比较会触发隐式转换等于对每一行做TO_DATE效率极低。正确写法是把字符串转成时间再比较WHERE TO_DATE(first_load_time, YYYY-MM-DD/HH24:MI:SS) TRUNC(SYSDATE)同样的道理AWR快照表里的begin_interval_time已经是时间类型直接用BETWEEN做范围过滤即可别画蛇添足转字符串。我的建议是先把每个视图的相关字段类型打印出来DESC命令再决定用哪种写法能少走一半弯路。5.4 历史SQL查不到那是保留策略和游标逐出在起作用新手经常遇到这种情况昨天明明跑过的SQL今天查v$sqlarea什么都搜不到查DBA_HIST_SQLSTAT也只有零星几条。这通常是两个因素叠加共享池里游标被其他SQL挤出了v$sql自然查不到。内存越小的库、业务越复杂的库游标存活时间越短。AWR快照保存时间超过了保留期默认8天前的历史数据已经被清理。实际工作中我见过一个非常典型的误判某客户查一条今天凌晨的慢SQL在v$sql里找不到就断定数据库没执行过这条SQL。后来翻了应用日志发现SQL确实跑了只是凌晨大批量作业把游标冲掉了。所以正确做法是先确定时间线再用AWR快照查询如果v$sql有记录则优先用v$sql数据粒度更好两条路互为补充而不是互证有无。另一个可能的坑是SQL被截断导致SQL_TEXT匹配不上。建议用sql_id精确匹配而不要用LIKE %关键字%去碰运气因为v$sqlarea里的文本可能只有前1000个字符关键内容被截你没看到条件自然匹配不到。6. 从手动查询到顺手可用的排障习惯这套查询我已经用了很多年逐渐沉淀成固定的排查套路。最后分享下我在实际运维里形成的操作习惯你可以直接抄。第一把高频查询脚本固化。下面这段是我在12c上最常用的一条几乎所有系统怎么又变慢了的问题都从它开始SELECT inst_id, sid, serial#, username, sql_id, event, wait_class, last_call_et, machine, program, module FROM gv$session WHERE status ACTIVE AND username NOT IN (SYS, SYSTEM) ORDER BY last_call_et DESC, event;拿到突出问题会话的inst_id、sid、serial#后直接喂给下面这段一次性输出完整SQL文本SELECT sql_id, piece, sql_text FROM gv$sqltext_with_newlines WHERE inst_id inst_id AND sql_id (SELECT sql_id FROM gv$session WHERE inst_id inst_id AND sid sid) ORDER BY inst_id, sql_id, piece;再配合执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id));三步下来90%的当前卡顿问题能定位到具体SQL和瓶颈环节。第二遇到历史性能回溯需求善用ASH先圈时间点。ASH能定位到秒级、会话级的活动历史比直接翻AWR的SQL统计快得多。先查ASH找到可疑SQL_ID再回DBA_HIST_SQLSTAT按快照确认统计逻辑最顺。第三给业务方交付结论时别只丢一个SQL_ID。把这三样东西打包完整SQL文本、执行计划截图、等待事件时间线。有这三样开发人员基本不用再追问就能自己定位是缺索引、统计信息过期还是SQL写法问题。我在实际操作中还有一个体会查询历史SQL和当前SQL看着像是个技术活其实更多是个习惯问题。养成一遇到异常先抓现场的习惯把当前会话、SQL文本、等待事件、执行计划这四个快照保存下来事后再慢慢分析。很多时候问题能不能解决取决于你手头有没有这套行车记录仪拍到关键帧。希望这套方法能让你下次面对生产事故时少一点慌乱多一点从容。