企业官网建设流程全解析

中午十二点刚过，运维群就弹出一条提醒：生产库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$sqltext

v$session是Oracle所有会话的实时快照，每条记录代表一个会话。它里面的sql_id字段表示这个会话当前正在执行的SQL，prev_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 常用字段速查表

我把常看的字段整理成了一张表，方便现场排障时快速回忆。

这张表不能解决所有问题，但日常排障基本够用。真到了分析根因阶段，还得把执行计划调出来，这一步放到后面讲。

3. 查已执行过的SQL：内存游标和历史快照两手抓

3.1 v$sqlarea和v$sql：共享池里的“旧账”

数据库把SQL文本和相关统计放在共享池里，只要游标还没被挤出，就能从v$sql或v$sqlarea查。v$sqlarea按SQL文本聚合，把文本相同但child_number不同的游标统计合并成一行。v$sql则是细分到子游标，适合做更精确的统计。

如果业务反馈“这个查询好慢”，我一般先用v$sql按累计执行时间取Top N：

SELECT 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的表现。

我这里常用的一条查询，是按快照时间段过滤出最耗时的历史SQL：

SELECT 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是ACTIVE，sql_exec_start已经过去17分钟，event显示“PX Deq: Execute Reply”，wait_class是Idle，但会话仍然是ACTIVE。这种情况多见于并行查询，父SQL本身未必是问题源头，真正要查的是并行子进程发起的SQL。于是我再用gv$session按inst_id分组，把所有有sql_id的会话捞出来，终于看到几个进程都在等同一个sql_id：g3xyz123abc。

这一步我踩过坑。早期一看到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 CONTAINER=PDB1。若在根容器里查，注意用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小节里那几条查询改成自己的常用脚本，后面会省很多事。