干DBA这行基本每天都会遇到类似的问题业务反馈某功能页面转圈应用侧查不到具体卡点数据库连接池被打满所有会话都在等锁或者凌晨一个批处理跑了三个小时DBA却不知道它到底在执行哪条SQL。这类问题的排查入口几乎都指向同一个动作——把数据库正在执行的SQL、以及最近执行过的SQL捞出来。Oracle 12c下做这件事说难不算难说简单也埋着不少坑。最典型的就是v$sql和v$session这些动态性能视图的定位完全不同一个保存的是共享池里的游标统计一个记录的是会话当前状态。如果不先搞清楚两者的差别经常会遇到“明明有SQL在执行v$sql里却查不到”“刚抓到一条SQL转眼又没了”这种让人抓狂的情况。这篇文章会把执行过的SQL和正在执行的SQL两条查询路径分别拆开讲结合12c新增的SQL监控能力顺便把锁阻塞、长SQL诊断、多租户环境下的权限和过滤坑一起理清楚。不管是刚入门的运维还是有几年经验的DBA应该都能从中拿走几条可以直接用的查询脚本。1. 先把需求拆清楚历史SQL和当前SQL是两回事很多人第一次接触这个主题会下意识觉得“查SQL嘛不就是select * from v$sql”。但实际操作几次就会明白v$sql记录的并不是“某次执行”而是共享池里被缓存的游标及其聚合统计。换句话说一条相同的SQL被一百个会话执行v$sql里通常只有一行记录的是这一百次的累计数据。而你真正想看的“当前正在执行的那些SQL”其实是一个一个会话的实时状态这属于v$session的范畴。1.1 两类查询的本质差别用个生活化的类比帮助理解。v$sqlarea和v$sql像是博客平台的访问日志只记录了最近被访问过的页面标题、访问次数、平均耗时而且热门内容才会被留下访问量低的会慢慢被挤出缓存。v$session则像实时视频监控每一个会话就是一只摄像头你能看到这台机器现在正在跑什么任务、状态是active还是inactive、等了多久、在等哪个资源。目标不同查询思路就完全不同查执行过的SQL方向是“内存游标缓存 AWR历史快照”核心视图是v$sqlarea、v$sql、dba_hist_sqltext、dba_hist_sqlstat。查正在执行的SQL方向是“会话实时状态 SQL文本分片”核心视图是v$session、v$sqltext、v$process12c下还能加上v$sql_monitor。这个差别听起来简单但很多排查弯路都是从这里开始的。比如有人想定位当前CPU占用最高的SQL直接去v$sqlarea里按disk_reads排序结果看到的全是历史累积量而不是当下正在跑的语句。正确的做法应该是先从操作系统层找到高CPU的进程PID关联v$process再关联v$session最后拿到sql_id去v$sqltext里拼出完整语句。1.2 涉及的核心动态性能视图把常用的视图按用途整理一下后续所有查询脚本都会围绕它们展开视图用途关键点v$session会话级实时状态sql_id是当前正在执行的SQLprev_sql_id是刚执行完的上一条v$sqltextSQL文本分片一个SQL被拆成多行靠sql_id和piece拼接v$sqlarea / v$sql共享池中已解析SQL的统计v$sqlarea按sql_id聚合v$sql粒度到child游标v$process后台进程信息配合操作系统PIDspid定位会话v$session_longops执行超过6秒的长时间操作自动记录全表扫描、排序、hash join等长操作v$sql_monitor / v$sql_plan_monitor12c实时SQL监控能看到正在执行SQL的实时IO、内存和计划执行进度dba_hist_sqltext / dba_hist_sqlstatAWR历史SQL文本与统计超过快照保留期的SQL就不存在了从上往下看前几个解决“现在发生了什么”最后两个解决“过去发生了什么”。很多刚接触Oracle的开发者习惯只查v$sql一旦SQL因为shared pool内存不足被挤出缓存就什么都查不到了这时候还得靠AWR的历史数据兜底。1.3 为什么不能只靠v$sql解决所有查询再说一次这个高频误区。v$sql记录的是“解析过的SQL文本和统计信息”它回答的是“这条SQL被跑过多少次、总共消耗多少资源”而不是“现在哪个会话正在跑它”。如果你需要定位某个具体业务连接当前执行的语句v$sql提供不了。另外v$sql里的数据是内存快照会随着游标老化、shared pool收缩、flush shared_pool等操作清空。一次长查询的SQL在刚执行完时查v$sql能看到过了几个小时再查可能已经不在里面。所以“查看执行过的SQL”严格来说有两条时间线短周期看内存视图长周期看AWR。如果两个方向都查不到那就基本可以确定SQL已经不在数据库的任何缓存里了。2. 查执行过的SQL从共享池内存到AWR历史这一节说的是“过去时”。实际运维中这个需求通常来自两类场景一是刚发生故障想知道某个异常SQL到底是谁发起的二是做性能优化统计一段时间内哪几条SQL消耗资源最大。两类场景的查询路径略有差异但起点都是先拿sql_id或SQL文本片段。2.1 先从共享池查v$sqlarea与v$sql的差别v$sqlarea和v$sql看起来像双胞胎实际粒度不一样。v$sqlarea按sql_id聚合一条SQL即使有多个执行计划版本也只在视图里显示一行v$sql则按sql_id child_number拆成多行同一个sql_id下可能有多个子游标对应不同schema、不同绑定变量长度、不同优化器环境。日常做top SQL统计优先用v$sqlarea就够了select sql_id, sql_text, executions, elapsed_time / 1000000 as elapsed_secs, cpu_time / 1000000 as cpu_secs, buffer_gets, disk_reads, rows_processed, parsing_schema_name, last_active_time from v$sqlarea where sql_text not like %from v$sqlarea% and parsing_schema_name is not null order by elapsed_time desc fetch first 20 rows only;这里有个细节sql_text是varchar2最多只能显示SQL的前1000字符。很多线上业务SQL动辄两三千字符查出来的文本会被截断。想看完整文本得换成v$sql里的sql_fulltext列它是CLOB类型select sql_id, dbms_lob.substr(sql_fulltext, 4000, 1) as full_text from v$sql where sql_id 你的sql_id;注意dbms_lob.substr的第二个参数是截取长度第三个参数是起始位置。如果SQL超过4000字符需要分段截取后拼接或者直接取前4000字符定位问题。大部分场景下定位一条SQL靠前500个字符就够了真正的瓶颈判断还要结合执行计划。2.2 会话刚执行完的SQL从prev_sql_id入手还有一种很尴尬的场景你打开v$session视图发现目标会话的sql_id是null感觉什么都没查出来但其实它刚执行完上一条SQL还没发起新的语句。此时关键字段是prev_sql_id和prev_exec_start。select sid, serial#, username, sql_id as current_sql_id, prev_sql_id, prev_exec_start, last_call_et, status from v$session where sid 1234;拿到prev_sql_id后再去v$sql里查完整文本。这个技巧在处理“用户反馈刚才执行了一条SQL然后会话就卡住了”这类问题特别有用。很多应用连接池里的连接长时间保持INACTIVE状态不代表它什么都没干也许刚刚跑完一条大查询把客户端和数据库之间的缓冲占住了。只看current_sql_id会漏掉大量线索。2.3 再到AWR历史里捞dba_hist_sqltext与dba_hist_sqlstat当SQL已经不在内存视图里或者你想看过去一周的统计趋势就要转向AWR。12c默认保留8天历史快照默认每小时采集一次但不是每条SQL都会进AWR。AWR只会记录每个快照区间内消耗资源靠前的SQL默认是Top 30级别可以在dbms_workload_repository.modify_snapshot_settings里调整topnsql参数。查AWR里有没有某条SQL方式很简单select sql_id, dbms_lob.substr(sql_text, 4000, 1) as sql_text from dba_hist_sqltext where sql_id 你的sql_id;如果想按时间段找消耗最大的SQL则要先查dba_hist_sqlstat这个视图保存了每个快照区间内的SQL执行统计。配合dba_hist_snapshot可以算出具体时间段select s.sql_id, s.snap_id, s.executions_delta, s.elapsed_time_delta / 1000000 as elapsed_secs_delta, s.buffer_gets_delta, s.disk_reads_delta from dba_hist_sqlstat s join dba_hist_snapshot sn on s.snap_id sn.snap_id where sn.begin_interval_time systimestamp - interval 1 day order by s.elapsed_time_delta desc fetch first 20 rows only;使用AWR需要注意如果快照间隔超过保留窗口或者快照被手动删除数据就真的没了。生产环境建议保证snapshot间隔不超过30分钟这样即便内存视图被清空至少还能回溯故障前30分钟内的top SQL。我自己遇到过一次共享池被异常flush所有v$sql数据清零最后全靠AWR里的历史采样恢复了当时的问题SQL这种经历一次就能记住快照的重要性。2.4 12c多租户环境下的查询区别12c开始引入CDB/PDB架构执行过的SQL查询多了一个维度到底在哪个容器里跑的如果以CDB公共用户登录v$sqlarea里会包含所有PDB的SQL注意看con_id字段如果以某个PDB的普通用户登录你只会看到自己PDB范围内的数据。select con_id, sql_id, sql_text, executions, elapsed_time / 1000000 as elapsed_secs from v$sqlarea where con_id 0 order by elapsed_time desc;在CDB层面还可以用gv$视图查看RAC所有实例的数据。另外dba_hist_sqltext在CDB下存储时同样带con_id查询历史SQL时要额外加条件否则会出现同样sql_id对应多个PDB的文本实际内容却不同容易误判。3. 当前正在执行的SQL从会话盯到语句把“现在时”查询讲透。这部分是线上故障排查的高频操作核心是先用v$session定位会话再关联v$sqltext取文本还有v$process帮助从操作系统进程反查。3.1 最常用的查询v$session联合v$sqltext先看当前所有活跃会话的基础信息select s.sid, s.serial#, s.username, s.status, s.sql_id, s.sql_child_number, s.event, s.wait_class, s.last_call_et, s.program from v$session s where s.status ACTIVE and s.username is not null order by s.last_call_et desc;status为ACTIVE的会话代表正在执行操作。last_call_et表示最后一次调用距今的秒数这个值大的activesession往往就是卡住的元凶。不过要特别提醒有些SQL会在会话和数据库之间来回交互应用端把结果集拉回客户端时会话状态可能是INACTIVE但SQL实际上还没处理完。所以只查ACTIVE会漏后面专门讲这个坑。拿到sql_id之后再去v$sqltext查询完整SQL文本。v$sqltext把一条长SQL拆成多行存储每行是一个piece并且可能分布在多个row里。拼装时要按piece排序select t.sql_id, t.piece, t.sql_text from v$sqltext t where t.sql_id 你的sql_id order by t.sql_id, t.piece;如果嫌分片看麻烦直接用v$sql的sql_fulltext更快。但v$sqltext有一个好处即使SQL还在硬解析过程中只要游标还未完全建立v$sqltext里也可能有部分文本而v$sql必须等完整解析结束才能查到。所以当你想抓“正在解析但还没执行完”的SQL时v$sqltext是更可靠的来源。3.2 从操作系统进程反查SQL这是线上定位高CPU问题最常用的手段。业务侧告诉我某个应用节点CPU被打满但数据库里看不出来哪个会话异常。此时去操作系统执行top命令看到一堆oracle进程挑出CPU最高的PID然后反查数据库select p.spid, s.sid, s.serial#, s.username, s.sql_id, s.status, s.event, s.program from v$process p join v$session s on s.paddr p.addr where p.spid 操作系统PID;拿到sql_id后再去v$sqltext拼文本。这个反查链路是每个DBA都应该刻在肌肉记忆里的操作。需要注意如果操作系统PID是tomcat或java进程的PID说明问题在应用侧应用程序和数据库之间的会话可能是空闲着等待客户端指令的此时数据库侧查不到高性能消耗是正常的别在数据库里死磕。3.3 不依赖sql_id的文本获取有时候v$session里sql_id是null或者sql_id对应的SQL已经不在v$sql里了但会话确实还在跑。这种情况下可以退而求其次查v$sqltext关联v$session直接按sid过滤select s.sid, t.sql_text from v$session s, v$sqltext t where s.sql_id t.sql_id and s.sid 你的sid order by s.sid, t.piece;但如果v$session的sql_id为空这个关联也查不到。此时能看的只有wait event和last_call_et大多数情况说明SQL还未生成sql_id比如正在等待客户端发送网络数据包。不要试图从数据库侧抓一个实际不存在的SQL。3.4 执行超过6秒的长操作和12c实时SQL监控12c里有个专治“长SQL诊断”的功能——SQL Monitor。它会在SQL执行期间实时记录IO、CPU、等待事件还能配合v$sql_plan_monitor看到计划中每个步骤的实际执行行数和耗时。触发SQL监控的条件一般是并行执行、或执行时间超过5秒的SQL。查询正在被监控的SQLselect sql_id, sql_exec_id, sql_exec_start, status, cpu_time, elapsed_time, reads, writes, username from v$sql_monitor where status EXECUTING order by sql_exec_start desc;这个视图的信息密度比v$sqlarea高得多因为它是执行实例级别的实时监控。拿到sql_id后还可以直接生成监控报告select dbms_sqltune.report_sql_monitor( sql_id 你的sql_id, type TEXT ) from dual;报告里能看到SQL从开始到现在的每一阶段执行进度包括计划中每个操作符消耗的时间、返回的行数、等待事件。在12c环境里做慢SQL根因分析这比单纯看v$session_event高效太多。当然如果SQL执行时间太短监控报告生成不出来因为还没到触发阈值就被执行完了。4. 12c下的组合查询锁、阻塞、等待事件和SQL连着看多数时候用户反馈的问题不是“SQL慢”而是“卡住不动”。这时候光看一条SQL文本用处不大更关键的是查出它在等什么、被谁阻塞了。4.1 从阻塞会话一路挖到阻塞SQLv$session里有个特别有用的字段blocking_session记录了当前会话被哪个SID阻塞。如果值为空说明会话没被锁如果非空被阻塞的会话很可能在等待锁资源。select blocking_session, sid, serial#, username, sql_id, event, wait_class, seconds_in_wait from v$session where blocking_session is not null;拿到blocking_session的SID后再去查这个阻塞者的SQLselect s.sid, s.serial#, s.username, t.sql_text from v$session s join v$sqltext t on s.sql_id t.sql_id and t.piece 0 where s.sid 阻塞者的SID;很多时候阻塞者本身没有执行SQL而是在等另一个锁这就是经典的锁链。处理这种问题不要只杀第一眼看到的会话要顺着blocking_session一层一层往上查。12c里还可以配合v$lock视图看锁类型配合dba_objects看具体锁的是哪张表。真正定位到最上层的会话后和业务确认是否可以被kill再用alter system kill session sid,serial#结束。4.2 等待事件和SQL文本一起看数据库性能问题的定位很大程度上是等待事件的定位。把等待事件和SQL文本放一起效率会提高很多select s.sid, s.serial#, s.username, s.event, s.wait_class, s.seconds_in_wait, s.sql_id, dbms_lob.substr(v.sql_fulltext, 1000, 1) as sql_text from v$session s left join v$sql v on v.sql_id s.sql_id where s.type USER and s.status ACTIVE order by s.seconds_in_wait desc;观察等待事件时要结合等待时长。如果seconds_in_wait很大但event是普通的SQL*Net message from client说明会话在等客户端发消息正常情况下业务应用都是这种状态不需要处理。如果是enq: TX row lock contention、library cache pin这些就说明出问题了得继续深挖。4.3 从执行计划反推正在执行的SQL拿到sql_id后打印它在内存里的执行计划是判断SQL瓶颈的常用手段。dbms_xplan.display_cursor可以从共享池直接拉出某个sql_id对应游标的执行计划select * from table(dbms_xplan.display_cursor(sql_id 你的sql_id, format ALLSTATS LAST));如果SQL还在执行format可以加ALLSTATS、IOSTATS、MEMSTATS这些选项能看到实际执行统计。对于正在跑的SQL计划里的第二行“A-Rows”和“A-Time”会持续变化不过dbms_xplan取的是最后一次执行或当前执行到此刻的统计快照。对于已经执行完的SQL加LAST只能看到最后一步的统计。如果SQL还没解析完display_cursor可能查不到计划此时v$sql_plan里也可能还没有完整计划数据建议配合v$sql_monitor看执行进度。4.4 实时生成SQL监控报告长SQL诊断强烈建议用DBMS_SQLTUNE.REPORT_SQL_MONITOR它可以生成文本、HTML、Active report三种格式。Active report是HTML格式可以在浏览器里交互查看对开发人员理解执行计划很有帮助。select dbms_sqltune.report_sql_monitor( sql_id 你的sql_id, type HTML, report_level ALL ) as report_text from dual;输出的report_text是CLOB放到web页面里打开就行。对于还在执行中的长SQL这个报告会持续更新直到执行结束。用这个功能排查“为什么跑了半小时还没结束”比一遍遍刷新v$session直观得多。5. 高频踩坑实录为什么经常查不到想看的SQL最后这部分没有章法可讲全是实战里一个坑一个坑踩过来的。把最常见的几个情况列出来遇到类似现象可以直接对着排查。5.1 权限不足导致什么都查不到别笑这个问题真的高频。数据库普通账号默认没有v$动态性能视图的查询权限报错是ORA-00942: table or view does not exist。给应用账号开权限时要授权的是V_$视图而不是v$视图因为v$本身是同义词grant select on v_$session to your_user; grant select on v_$sql to your_user; grant select on v_$sqltext to your_user;如果不想逐个授权直接授权dba角色最省事但生产环境肯定不建议最小权限原则还是要遵守。另外查dba_hist_*视图需要select_catalog_role或对应的SELECT权限不然会报表不存在。5.2 SQL文本只显示到1000字符v$sqlarea的sql_text字段是varchar2(1000)超过1000字符的部分被截断。这时候看v$sql的sql_fulltext或者从v$sqltext把所有piece拼起来。拼接时可以这样select sql_id, listagg(sql_text, ) within group (order by piece) as full_text from v$sqltext where sql_id 你的sql_id group by sql_id;listagg在字符串特别长时可能报ORA-22905因为v$sqltext是按存储行的SIZE计算的通常不会超过4000字符但在12c下保险起见还是用dbms_lob.substr从v$sql取。拼接前记得确认piece顺序v$sqltext里的行是按piece值的从小到大排列的直接按这个排序拼接就是原始顺序。5.3 会话状态不是ACTIVE就查不到这是最容易被误解的地方。v$session里status为INACTIVE的会话同样可能正在执行SQL只是它执行的SQL正在等待客户端响应或者是PL/SQL块中嵌套了DBMS_OUTPUT等待返回。很多锁等待的阻塞者会话状态就是INACTIVE因为它的SQL已经执行完但事务没有提交锁一直不释放。所以排查锁问题时千万不能只查ACTIVE会话。更合理的方式是查所有sql_id非空的会话select sid, serial#, username, status, sql_id, event, last_call_et from v$session where type USER and (sql_id is not null or prev_sql_id is not null) and username is not null order by last_call_et desc;这样连“刚执行完但还没提交”的会话也能看到。5.4 两次查询间隙SQL就变了v$session的sql_id是最新开始执行的SQL。如果一条SQL执行速度极快你前一秒刚查到sql_id后一秒再去v$sqltext关联时会话可能已经开始执行下一跳SQLsql_id已经变了。所以抓取正在执行的SQL时尽量一次把v$session和v$sqltext查出来不要分两步走中间少留间隔。如果确实要连续采样可以写个循环脚本每隔几百毫秒抓一次但别抓太久免得影响生产。5.5 后台进程和系统任务刷屏查询结果如果混入大量后台进程比如LGWR、DBWR或者一些SYS用户的内部任务噪音很大。加个过滤条件and s.type USER and s.username is not null这样可以剔除后台进程。但也要记住有些用户进程的username是application schema名而不是实际人账号所以不要完全排除任何非sys用户。5.6 v$sqlarea与AWR数据对不上经常有人发现v$sqlarea里某条SQL执行了1000次dba_hist_sqlstat里却显示几百次。这不奇怪。AWR按快照采样只记录每个区间内的top SQL同时v$sqlarea里累计的elapsed_time是从实例启动或游标进入shared pool起到现在的总和而AWR的delta是快照区间内的增量。所以两边数据本来就不应该完全一致做对比时要明确统计口径别拿这两个数推来推去最后得出错误结论。5.7 flush shared pool的代价网上不少教程为了清空游标缓存建议执行alter system flush shared_pool。这条命令确实会把v$sql、v$sqlarea里的数据全部清空但代价是所有SQL都要重新硬解析。在OLTP生产环境硬解析风暴会让CPU瞬间冲高严重时直接引发连接池超时。除非你有非常明确的理由否则不要在生产库随便flush。真想清某条特定SQL可以查出来之后用dbms_shared_pool.purge精准清理而不是全局清库。6. 自动化采样用最简单的方式建立自己的SQL历史很多情况下运维团队抱怨“AIX上AWR只有8天更早的SQL查不到”“内存视图被flush过数据没了”。其实可以自己写一个轻量级的定时采样程序周期性抓取v$session和v$sqlarea的关键信息存入一张历史表。成本极低但能极大提升故障追溯能力。6.1 自建SQL采样表在DBA专用的schema下建两张表一张存会话采样快照一张存SQL统计快照create table t_sql_snapshot ( snap_time timestamp, sql_id varchar2(13), sql_text clob, executions number, elapsed_time number, buffer_gets number, disk_reads number, parsing_user varchar2(30) );然后写一个简单的PL/SQL块每10分钟抓一次v$sqlarea里的top SQL插入这张表。这样即使v$sql被清空自己这张表里仍然保有历史记录。会话级采样同理可以存sid、serial、username、event、sql_id这几个核心字段用于事后复盘“那个时间点数据库到底发生了什么”。6.2 周期性抓取当前执行SQL更细致一点的做法是每30秒抓一次所有ACTIVE会话的SQL文本存入一张会话级SQL表。这样当业务投诉“某时刻点应用很慢”时能直接把那个时间点的会话快照调出来看到底有几条SQL在跑、等了什么事件。实现起来也很简单一个dbms_scheduler定时任务就够采样表加个主键和索引注意清理过期数据保留30天到90天足够。我自己在几个生产系统上是这么做的配合AWR一起用之后性能问题的回溯效率明显提升。如果公司有统一监控平台优先往平台里送指标如果只是个人运维机写个shell脚本crontab调用sqlplus导数据完全够用。最后再分享一个小习惯排查问题时先把“时间线”理清楚。故障从几点开始的、持续多久、期间有哪些会话状态变化、对应哪些SQL执行所有这些线索别只靠脑子记。动态性能视图的数据随时会变难得抓到一条关键SQL随手把sql_id、sid、时间戳记下来可能就是你下班回家后还能继续分析问题的唯一凭据。