中午十二点刚过运维群就弹出一条提醒生产库CPU又飙到99%了几个报表卡得动不了让DBA赶紧看一眼。这种开局凡是碰过Oracle的人都不会陌生。接到报障之后真正第一步要做的事情不是改参数也不是翻日志而是先把“当前正在执行的SQL有哪些”这个问题回答清楚。Oracle 12c的视图体系非常成熟v$session、v$sql、v$sqltext再加上AWR的dba_hist_sqltext、dba_hist_sqlstat基本就能把正在跑的SQL和历史执行过的SQL一网打尽。这篇东西不打算讲太多理论重点放在我从日常运维里反复验证过的查询脚本、排查顺序和踩过的坑上。如果你刚接触Oracle 12c或者正被一个“查不到SQL”的问题卡住照着下面的思路走大概率能省下半天时间。先说清楚一点我所有脚本都在Oracle 12c Release 2环境实测过单实例和RAC都适用19c上面跑也没问题老版本11g只有个别语法需要微调文中我会专门指出来。1. 先搞清楚要查哪种“执行过的SQL”1.1 “当前正在执行”和“已执行过”在Oracle里是两类数据很多新手一上来就盯着v$sql不放觉得只要SQL执行过就一定能在这里查到结果经常扑个空。问题不在于Oracle没记录而在于搞混了“当前正在执行”和“历史执行过”这两种完全不同的数据形态。“当前正在执行的SQL”指的是此时此刻某个会话正在运行的那条语句Oracle在v$session里记录了每个会话当前的sql_id、SQL开始执行时间、等待事件、锁对象等维度。而“执行过的SQL”是历史语义如果语句还在共享池里可以从v$sql或v$sqlarea查到文本、执行次数、消耗时间、读写块数如果语句已经被挤出共享池就只能指望AWR快照里的dba_hist_sqltext和dba_hist_sqlstat。这两类数据对应完全不同的排障场景。比如生产库突然CPU飙升我需要知道是哪个会话正在跑什么这时候看v$session关联v$sql就行如果业务方告诉我说“昨天下午有个让库变慢的查询”那多半要去AWR或v$sql里翻统计信息。搞清楚这层逻辑后面查询才不会被误导。1.2 看SQL之前先圈定场景在实际工作里我一般把需求分成四类。第一类是性能故障响应目标是找“当前正在跑的、消耗资源最多的SQL”第二类是锁等待分析目标是从v$session找到阻塞源头再看阻塞源头在跑什么SQL第三类是常规调优需要从v$sql里按elapsed_time、buffer_gets、disk_reads排序找出哪些SQL值得优化第四类是合规审计需要确认某段时间内是否执行过特定SQL通常查AWR历史。场景不一样SQL写法也不一样但底层都绕不开那几张视图。这就像工具箱里既有钳子又有螺丝刀看起来都是“查SQL”实际上用法完全不同。下面我就把四个场景的查询脚本和判断要点拆开写。2. 查当前正在执行的SQL会话视图一抓一个准2.1 核心视图v$session、v$sql、v$sqltextv$session是Oracle所有会话的实时快照每条记录代表一个会话。它里面的sql_id字段表示这个会话当前正在执行的SQLprev_sql_id表示上一次执行的SQL。通过sql_id再去关联v$sql或者v$sqltext就能拿到完整的SQL文本。v$sqltext这个视图很多人不熟悉。它的作用是把一条完整的SQL按每行最多64个字符拆成多片用piece字段排序。为什么要拆因为一条SQL文本可以很长而v$sql里的sql_text字段只保留截断后的部分想拿到完整文本就必须从v$sqltext读取。数据库会话执行一条超长SQL时v$session里的sql_id照样能记录下来因此v$sqltext是追完整文本的关键。v$sql和v$sqltext的区别还要多说一句。v$sql里每个游标占一行包含父游标和子游标信息能查到执行次数、物理读、逻辑读、CPU时间等一堆统计v$sqltext里只有sql_id、piece和sql_text三件套没有统计信息。所以查询时先通过v$session拿sql_id再分别从v$sql拿统计、从v$sqltext拿文本这就是最标准的套路。2.2 一条查询打遍全场定位所有活跃会话的SQL我平时最常用的是下面这条SQL它能把当前所有非后台、有用户名、并且正在运行的SQL连同会话状态一起捞出来。SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.sql_child_number, s.sql_exec_start, s.event, s.wait_class, s.module, q.sql_text FROM v$session s LEFT JOIN v$sql q ON s.sql_id q.sql_id AND s.sql_child_number q.child_number WHERE s.type ! BACKGROUND AND s.username IS NOT NULL AND s.sql_id IS NOT NULL ORDER BY s.sql_exec_start;这条SQL跑出来后status是ACTIVE的会话就是正在做实际工作的。sql_exec_start告诉你这条SQL从什么时候开始执行event和wait_class则能反映它是等在CPU上还是等在I/O上还是等在锁上。如果status是INACTIVE但sql_id有值说明它刚刚执行完一条SQL正停在客户端等待下一步指令这时候要结合prev_sql_id一起看。这里有个细节值得留意。v$session和v$sql关联的时候必须同时带上child_number因为同一父游标会因为绑定变量、环境设置不同派生出多个子游标而v$sql里同一sql_id可能有多行。只按sql_id关联容易出现重复行带上child_number才准确。2.3 SQL太长被截断怎么办v$sqltext帮你拼回完整文本当在v$sql里拿到的sql_text不够完整时用v$sqltext来拼接。先把上面查询里的sql_id拿出来然后执行SELECT piece, sql_text FROM v$sqltext WHERE sql_id sql_id ORDER BY piece;输出结果建议在SQL*Plus里先执行set long 20000或者在PL/SQL Developer里把输出窗口拉宽。很多工具默认只显示一行的一部分看起来像“只查到半句话”其实就是显示问题不是数据缺失。如果你习惯用SQL*Plus还可以用这个写法直接把拼接结果合并成一行SELECT LISTAGG(sql_text, ) WITHIN GROUP (ORDER BY piece) AS full_sql FROM v$sqltext WHERE sql_id sql_id;不过要注意SQL文本特别长时LISTAGG拼接后的输出也可能被工具截断。通常我更推荐直接扫v$sqltext的多行结果肉眼拼接几行也就十几秒还能顺便看到脚本注释之类的信息。2.4 常用字段速查表我把常看的字段整理成了一张表方便现场排障时快速回忆。字段含义使用场景s.sid, s.serial#会话ID和序列号kill会话时必须要用这对组合s.sql_id当前正在执行的SQL标识关联v$sql、v$sqltexts.prev_sql_id上一次执行的SQL标识会话空闲时查它刚干过什么s.sql_exec_start当前SQL开始执行时间判断是不是已经耗了很久s.event当前等待事件看是等锁、等I/O还是等CPUs.wait_class等待大类快速给问题分类q.elapsed_time游标累计执行耗时微秒判断SQL本身有多重这张表不能解决所有问题但日常排障基本够用。真到了分析根因阶段还得把执行计划调出来这一步放到后面讲。3. 查已执行过的SQL内存游标和历史快照两手抓3.1 v$sqlarea和v$sql共享池里的“旧账”数据库把SQL文本和相关统计放在共享池里只要游标还没被挤出就能从v$sql或v$sqlarea查。v$sqlarea按SQL文本聚合把文本相同但child_number不同的游标统计合并成一行。v$sql则是细分到子游标适合做更精确的统计。如果业务反馈“这个查询好慢”我一般先用v$sql按累计执行时间取Top NSELECT sql_id, child_number, sql_text, executions, elapsed_time / 1000000 AS elapsed_sec, cpu_time / 1000000 AS cpu_sec, buffer_gets, disk_reads, rows_processed FROM v$sql WHERE executions 0 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;FETCH FIRST是12c才有的语法如果还在用11g要改成ROWNUM写法。elapsed_time单位是微秒除以1000000就变成秒。executions代表这个游标被执行的次数elapsed_time通常表示累计值想判断单次平均耗时可以用elapsed_time/executions这才是优化时更倾向参考的指标。用相同思路还能排查逻辑读高、物理读高、Rows处理异常的SQL只要把ORDER BY换成buffer_gets、disk_reads或者rows_processed就行。这类查询属于最常用的“过去执行SQL”排查方式前提是游标还在共享池里。3.2 游标被挤出共享池AWR历史视图救场生产库的共享池一直被各种SQL冲刷老游标很快就会被替换出去。业务说“昨天下午3点有一条SQL很慢”这时候v$sql里多半已经查不到了。别慌只要AWR快照还保留着那段时间dba_hist_sqltext就能找到完整文本。SELECT snap_id, sql_id, sql_text FROM dba_hist_sqltext WHERE sql_id sql_id;注意dba_hist_sqltext只有SQL文本没有统计信息。统计信息放在dba_hist_sqlstat里它记录每条SQL在快照期间执行了多少次、耗了多少时间、读写多少块。把两个视图关联起来就能还原一段历史里SQL的表现。我这里常用的一条查询是按快照时间段过滤出最耗时的历史SQLSELECT h.snap_id, h.sql_id, h.executions_delta, h.elapsed_time_delta / 1000000 AS elapsed_sec, h.buffer_gets_delta, h.disk_reads_delta, t.sql_text FROM dba_hist_sqlstat h, dba_hist_sqltext t WHERE h.sql_id t.sql_id AND h.snap_id BETWEEN start_snap AND end_snap ORDER BY h.elapsed_time_delta DESC;看到executions_delta为0或者很小但elapsed_time_delta很大的SQL基本可以锁定为慢查询的重点对象。AWR的默认保留期通常是8天具体取决于你的保留策略超出保留期的数据就真没了所以遇到重大问题时我会第一时间把相关快照导出来归档。3.3 拿到了sql_id怎么把执行计划一起调出来只看SQL文本还不够优化还要看执行计划。当前游标和执行完但仍在内存里的游标我都用同一个函数SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, ALLSTATS LAST));如果游标已经被挤出共享池那就从AWR的历史执行计划里取SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id, NULL, NULL, ALL));这里有个小经验DISPLAY_CURSOR的第三个参数不写默认只显示普通计划加上ALLSTATS能看到实际的行数和缓冲读出这对判断“SQL为什么慢”非常关键。如果没加ALLSTATS计划里predicate、cost这些信息照样有但看不到实际执行次数和内存使用情况。3.4 从时间维度判断SQL到底执行过没有有时候需求是“我要知道这条SQL到底跑没跑过”。这时候除了dba_hist_sqltext还有一个更细的视图dba_hist_active_sess_history它是ASH的历史数据记录的是采样点的活跃会话状态。如果一条SQL偶然执行了一次恰好没被采样那它在ASH里就查不到。这不代表它没执行过只说明Oracle没有捕捉到。同理如果sql_id来自业务的某个慢日志但v$sql和AWR里都找不到也要注意“查不到”不代表“没执行”可能是游标已经被彻底清掉也可能是文字写法不同导致生成了不同sql_id。4. 实战复盘一次生产库卡顿的完整排查4.1 现象与初步判断某个周五早上业务同事在群里反馈“订单报表打不开数据库CPU 99%”。我登录服务器先用top看了进程负载又用vmstat确认没有大量swap判断问题很可能出在数据库内部不是OS层面。接着开始查v$session。这个环节最忌讳的就是没有方向乱查。我一般先看有无长时间ACTIVE的会话再看event字段是否有TX、TM这类锁等待事件。如果event是“cursor: pin S wait on X”或者“library cache lock”说明问题出在SQL解析和缓存竞争如果event是“db file sequential read”说明大概率在走索引但等待I/O如果是“direct path read temp”可能SQL在做排序。4.2 逐层定位正在执行的SQL用前面那条v$session查询跑出来发现有一条SQL的status是ACTIVEsql_exec_start已经过去17分钟event显示“PX Deq: Execute Reply”wait_class是Idle但会话仍然是ACTIVE。这种情况多见于并行查询父SQL本身未必是问题源头真正要查的是并行子进程发起的SQL。于是我再用gv$session按inst_id分组把所有有sql_id的会话捞出来终于看到几个进程都在等同一个sql_idg3xyz123abc。这一步我踩过坑。早期一看到ACTIVE就以为找到了凶手结果发现只是并行Slave在等待调度罪魁祸首是发起并行的大SQL。所以看到带PX字样的等待事件一定要扩大范围把所有相关会话都列出来别急着下结论。4.3 结合执行计划确认问题拿到sql_id之后我直接用DBMS_XPLAN.DISPLAY_CURSOR查执行计划发现计划里出现了一个HASH JOIN RIGHT OUTER并且其中一个驱动表在最后一步有“TEMP TABLE TRANSFORMATION”说明Oracle把公共子查询物化到了临时表随后又和另外一张大表做哈希连接。操作类型本身不算错但结合统计信息和实际行数看物化后的临时表估算行数只有几百实际却有上百万CBO被统计信息给骗了。为了进一步确认我对比了计划里的A-Rows和E-Rows。A-Rows严格大于E-Rows两三个数量级基本可以断定是基数估算偏差导致执行计划不优。这也是我建议每次看计划都要带ALLSTATS的原因没有实际执行行数光看COST很难找到真问题。4.4 事后追查历史SQL和统计信息问题处理完后我并没有马上收工。我查了dba_hist_sqlstat找到这条SQL最近几天的执行趋势发现它在一天前的快照里平均单次执行只要0.3秒当天的执行时间突然涨到40秒。这个变化说明不是SQL脚本本身就是很差而是统计信息或者数据分布发生了变化。顺着这个思路我检查了表的分区和统计信息收集时间发现这个表在当天凌晨做过大批量数据清理却没有重新收集统计信息CBO用的还是老基数。于是我用DBMS_STATS重新收集了统计信息再把SQL重新硬解析执行时间回到秒级。整个过程里v$session、v$sql、DBMS_XPLAN、dba_hist_sqlstat缺一不可它们分别承担了“定位会话—找出SQL—看计划—看历史”四件事。5. 常见问题和避坑记录5.1 为什么刚执行完就查不到SQL不少同事会拿一个业务日志里的SQL去v$sql查结果一条都查不到。原因通常有三个。第一SQL的文本和共享池里的游标不完全一致哪怕多了一个空格、大小写不同sql_id都会不同第二游标已经被挤出共享池时间太长第三查询用的账号没有权限看到的动态视图数据不完整。针对第二点我的建议是把v$sql和dba_hist_sqltext联合起来查别只盯一个视图。针对第一点如果想按文本模糊匹配v$sql中sql_text用INSTR做包含查询SQL*Plus下先set long 20000再输出但别指望完全一致更可靠的办法还是先从应用端拿准确文本。日常排障真的很少需要逐字匹配文本多数情况凭sql_id就能把问题串起来。5.2 v$sql里sql_text是截断的长SQL怎么办v$sql.sql_text最多只保留1000字节的截断信息长SQL会在这里被拦腰切断。如果怀疑某个超长SQL是问题SQL直接用v$sqltext取完整文本。注意v$sqltext里的piece从0开始每片约60多字节。我曾见过有人拿v$sql.sql_text去做存储过程匹配结果因截断导致判断错误必须引以为戒。5.3 12c多租户环境CDB里查和PDB里查结果不一样12c引入了容器数据库。在CDB根容器里用SYS登录v$session能看到所有PDB的会话但sql_id对应的SQL文本可能出现在v$sql里也可能只在某个PDB的共享池里。一般我建议在具体PDB下执行查询SQL需要先切到那个PDB比如ALTER SESSION SET CONTAINERPDB1。若在根容器里查注意用con_id过滤别把不同PDB的SQL混在一起。AWR视图在CDB和PDB下的差异更大。dba_hist_sqltext在根容器下能看到全库的数据在PDB下通常只能看到本PDB相关的快照。跨PDB分析时先确定“分析目标属于哪个PDB”否则容易拿到错误结论。5.4 没有DBA权限的开发者怎么查很多开发同学没有SELECT ANY DICTIONARY权限直接查v$session会报权限不足。如果权限卡得严建议找DBA开一个只读角色比如SELECT_CATALOG_ROLE或者单独授权v$session、v$sql、v$sqltext的SELECT权限。这些视图本质上来自底层数据字典普通业务账号默认是看不到的。如果连授权的流程都没有退一步的办法是通过应用自己的监控工具、慢查询日志、或者AWR报告获取SQL信息。Oracle Enterprise Manager里也能直接看到当前会话和SQL监控图形界面适合非DBA看但上生产环境多数人还是会用SQL*Plus所以我还是建议至少把v$session这套命令熟记下来。5.5 12c里的几个小坑最后补充几个我在12c环境里实际遇到的小问题。第一v$sql和dba_hist_sqltext里sql_id一样但SQL文本可能因为版本不同而有细微差别做精确匹配时谨慎一点。第二FETCH FIRST语法在11g不可用老环境用ROWNUM。第三DBMS_XPLAN.DISPLAY_CURSOR如果sql_id传错返回的是空结果不报错容易让人摸不着头脑可以先查v$sql确认一下sql_id和child_number再调用。按照我自己的习惯这几个查询早就存成了一个排障脚本文件名就叫check_running_sql.sql里面把v$session活跃会话、最长执行时间、锁等待、Top耗时的历史SQL都串在一起遇到问题直接跑跑完再根据输出决定下一步。这套流程用过无数次比翻告警日志快得多。如果你也经常被类似问题困扰不妨先把第2和第3小节里那几条查询改成自己的常用脚本后面会省很多事。