凌晨两点值班电话把我从梦里拽醒业务系统卡死订单提交不进去。远程登录数据库主机我做的第一件事不是重启实例而是先查当前有哪些SQL正在执行。做过Oracle DBA的都清楚这种场景下最要紧的是快速定位那几条把资源吃满的SQL——它们是什么内容、跑了多久、卡在什么等待事件上。等处理完回头复盘时你会发现真正帮你把时间省下来的其实是那套“查当前SQL 查历史SQL”的组合拳。这篇文章就围绕这套组合拳展开在Oracle 12c环境下怎么把“正在执行的SQL”和“执行过的SQL”用最快、最准确的方式捞出来。不管是应对生产事故、处理慢SQL还是事后审计排查这套方法都能直接用。我会从最常用的v$session讲起再逐步深入到AWR、dba_hist_*系列视图最后用一个真实案例把这套知识串起来。12c引入的多租户架构CDB/PDB也顺带说清楚避免你踩到“查到一堆SQL却分不清是哪个库”的坑。1. 为什么“查SQL”是Oracle运维的第一反应先聊一个基本认知数据库的性能问题不管表象是CPU飙高、磁盘IO打满还是会话堆积根源绝大多数都能落到SQL头上——要么是一条SQL写得有毛病要么是执行计划走偏要么是并发会话因为锁互相卡死。所以排查链路的第一步永远是“先看清到底是谁在跑、跑的是哪条SQL”。这个阶段要先搞清楚Oracle在内存里到底存了哪些“与SQL相关的信息”。一次SQL执行本质上是会话Session在共享池Shared Pool中申请一个游标Cursor然后解析、执行、返回结果。整个过程涉及的关键位置有三个会话层v$session记录谁正在跑、跑多久了、当前在等什么。游标层v$sql / v$sqlarea / v$sqltext记录SQL文本本身以及累计执行次数、消耗的CPU和IO。历史层dba_hist_sqlstat / dba_hist_sqltext / dba_hist_active_sess_history记录已经被共享池淘汰、但被AWR快照定时抓下来的历史SQL。为了方便记忆我通常把“要回答的问题”和“该去查哪个视图”对应起来要回答的问题首选视图关键字段现在有哪些SQL正在跑v$sessionsql_id, sql_exec_start, event, wait_class这条SQL的完整文本v$sqltext / v$sql.sql_fulltextpiece, sql_text这条SQL累计消耗了多少资源v$sql / v$sqlareaelapsed_time, cpu_time, disk_reads, buffer_gets一小时前跑了哪些SQL、耗时多少dba_hist_sqlstat dba_hist_sqltextexecutions_delta, elapsed_time_delta某个历史时刻CPU被谁占满dba_hist_active_sess_historysample_time, sql_id, session_state这张表建立的是全局映射关系后面几章的内容就是把这些视图一个个用熟。还有一个容易被忽略的常识查“当前正在执行”和查“执行过”是两个不同层面的需求。当前的靠v$session这种动态性能视图秒级刷新历史的靠共享池残留和AWR快照存在一定的丢失窗口。理解了这个边界你就不会在v$sql里查不到一条三天前的SQL时慌神。2. 正在执行的SQLv$session视图的正确打开方式2.1 一线上抄起来就用的“抓现行”脚本生产环境出问题时我最先执行的永远是下面这条。它能把当前所有活跃会话以及各自正在执行的SQL_ID一次性列出来附带等待事件和已执行时长SET LINESIZE 200 COL username FORMAT A15 COL event FORMAT A30 COL sql_id FORMAT A13 COL sql_exec_start FORMAT A20 SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.sql_id, s.sql_child_number, s.sql_exec_start, s.last_call_et, s.status, s.event, s.wait_class, s.blocking_session FROM v$session s WHERE s.type USER AND s.username IS NOT NULL AND s.status ACTIVE ORDER BY s.last_call_et DESC;带条件字段的原因很简单生产库动辄几百上千个会话其中大量是休眠状态INACTIVE滤掉之后剩下的才是你要关注的。解释几个核心字段status ACTIVE表示这个会话当前是活跃的注意“活跃”包含两种情形一种是真的在CPU上运算另一种是正在等待某个事件比如等锁、等IO。这两种状态都要关注但含义完全不同。sql_exec_start本轮SQL语句开始执行的时间。这个字段是10g之后才引入的12c里仍然是判断“正在执行”最直接的时间戳。last_call_et距会话最后一次调用到现在过去的秒数。对ACTIVE会话来说这个值约等于当前SQL已经跑了多久。如果一条SQL的last_call_et已经几千秒大概率是出问题了。blocking_session如果这个字段不为空说明当前会话正在被另一个会话阻塞。顺着这个值去查那个会话在干什么就能找到锁的源头。2.2 拿到SQL_ID之后怎么拼出完整的SQL文本v$session里只有SQL_ID没有完整SQL文本。拿到SQL_ID后的第一步是去看完整语句。这里有两个常用入口很多人分不清我放在一起对比-- 方法一v$sqltext分片存储按piece拼 SELECT sql_text FROM v$sqltext WHERE sql_id sql_id ORDER BY piece;-- 方法二v$sql.sql_fulltextCLOB直接返回完整文本 SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SELECT sql_fulltext FROM v$sql WHERE sql_id sql_id AND rownum 1;为什么要分成两个因为SQL文本在共享池里是以64字节一片的形式碎片化存放的v$sqltext直接反应存储原貌按piece排序就能拼出完整的语句而v$sql.sql_fulltext是Oracle把碎片拼好后的字段类型是CLOB读起来更省事。但要注意v$sql.sql_fulltext在SQL*Plus里默认不显示必须先执行SET LONG。我个人的习惯是短SQL直接用v$sqltext因为输出就是干净的纯文本长SQL或者涉及绑定变量多的场景用sql_fulltext配合DBMS_LOB.SUBSTR截取前4000字符也方便。另外v$sql里还有个sql_text字段但它只有1000字节遇到超长SQL会被截断别只盯着它看。2.3 别把“挂着”当成“在跑”这一步最容易翻车。很多DBA看到statusACTIVE就直接认为CPU是被这条SQL吃掉的这是误区。举个例子SELECT sid, serial#, username, sql_id, event, wait_class, state FROM v$session WHERE type USER AND status ACTIVE;如果返回的event是enq: TX - row lock contentionwait_class是Application那这条会话压根没在消耗CPU它在等一把锁。这时候你要做的不是杀会话而是沿blocking_session往上找看谁持有锁不放。反过来如果event是直接路径读这类IO事件说明它是在正常干活只是IO模型或者执行计划有问题。所以我的判断标准是组合拳statusACTIVE sql_exec_start不为空 wait_class不是Idle才说明这条SQL确实处于执行过程中。12c的v$session里还有一个sql_exec_id字段同一时刻同一会话可能会把一条SQL拆成多个执行阶段配合sql_exec_start就能看到一个执行的边界。快速确认锁阻塞源头还可以用这条SELECT sid, serial#, username, sql_id, event, sql_exec_start FROM v$session WHERE blocking_session IS NOT NULL;查到阻塞源后如果那个会话event是“SQL*Net message from client”意味着它上一个操作做完后一直没提交应用进程又没继续发来新请求——典型的“跑完不提交锁不释放”现场。这种问题往往不是SQL本身能解决的要回到应用层去补事务超时和提交策略。3. 执行过的SQL共享池里还能捞出来的那些3.1 为什么“执行过”不等于“还查得到”先说一个很多人踩过的坑跑到v$sql里查一条几天前执行过的SQL结果空空如也于是怀疑被恶意清掉了。其实不是而是共享池的容量有限Oracle用LRU最近最少使用算法来管理游标。新SQL不断进来老SQL的游标和文本就会被淘汰腾出空间给新语句。共享池就像一个只保留“最近常用菜式”的后厨工作台不常用的菜谱会被清走。所以v$sql、v$sqlarea、v$sqltext这些视图能查到的只是“目前还在共享池里的SQL”。换句话说你查“执行过”的SQL实际是在查“还没被挤出去”的SQL。对忙碌的生产库来说一条SQL几分钟前还在跑可能几分钟后就被挤掉了这完全正常。要想查更长时间的历史就得用第4章的AWR和dba_hist_*系列。3.2 v$sql和v$sqlarea的实用查询模板先说v$sql和v$sqlarea的区别v$sql是“子游标”级别同一个SQL_ID因为绑定变量、执行计划等差异可能会存在多个子游标每个子游标一个child_numberv$sqlarea是“父游标”级别把同一个SQL_ID的多个子游标汇总成一行。做Top SQL排序时我通常用v$sqlarea不会被多个child_number刷屏SELECT sql_id, executions, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec, ROUND(cpu_time / 1000000, 2) AS cpu_sec, disk_reads, buffer_gets, SUBSTR(sql_text, 1, 80) AS sql_text_80 FROM v$sqlarea WHERE executions 0 ORDER BY elapsed_time DESC FETCH FIRST 20 ROWS ONLY;注意elapsed_time和cpu_time的单位是微秒除以1000000才是秒这个细节我见过不少人栽过。12c支持FETCH FIRST语法比老版本的ROWNUM写法简洁多了这也算是12c带来的一点点小确幸。如果你想按表名或者关键字模糊搜索最常用的是这种SELECT sql_id, executions, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec, sql_text FROM v$sqlarea WHERE sql_text LIKE %T_ORDER% AND sql_text NOT LIKE %v$sqlarea% AND sql_text NOT LIKE %v$sql%;最后两个NOT LIKE很关键否则你会看到你自己执行的这条查询也匹配进去了因为它里面含着T_ORDER这个字符串。模糊搜索在共享池里代价不低生产环境建议加上schema过滤parsing_schema_name和时间窗口避免把整个共享池扫一遍。3.3 用v$sql_monitor看正在跑的大SQL12c里还有个宝藏视图v$sql_monitor它是Oracle SQL监控特性的对外窗口。Oracle默认会监控消耗超过一定阈值的SQL比如执行时间超过5秒并把执行过程中的资源消耗、等待、行源统计实时记录下来。查正在执行的大SQL用它比v$session信息更丰富SELECT sql_id, status, SUBSTR(sql_text, 1, 60) AS sql_text_60, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec, ROUND(cpu_time / 1000000, 2) AS cpu_sec, buffer_gets, physical_read_bytes, sql_exec_start, sql_exec_id FROM v$sql_monitor WHERE status EXECUTING ORDER BY elapsed_time DESC;status字段有EXECUTING、DONE、FAILED等取值EXECUTING就是还在跑的。v$sql_monitor还有个兄弟视图v$sql_plan_monitor能看到这条大SQL当前执行到计划里的哪一步对分析“卡在排序还是卡在嵌套循环”非常有帮助。4. 被共享池淘汰的SQLAWR与dba_hist_*才是真正的历史仓库4.1 12c默认的快照策略与修改方法上一章说了共享池里的SQL会流失那要查更久之前的历史怎么办靠AWRAutomatic Workload Repository。12c里默认STATISTICS_LEVEL为ALLAWR快照每60分钟自动采集一次保留8天。也就是说只要系统没有手动关闭快照你至少能往前翻8天的SQL历史。对于绝大多数排查需求8天足够覆盖“昨天那几秒到底跑了什么”这种问题。生产环境如果想让快照更密集、保留更久可以直接调整BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention 43200, -- 保留30天单位分钟 interval 30); -- 每30分钟采集一次 END; /interval的单位是分钟最小可以设为10分钟但设置太密会加重AWR本身的写入负担常规生产30分钟已经算激进。我一般只在关键业务窗口临时调密平时用默认60分钟。4.2 dba_hist_sqlstat与dba_hist_sqltext配合查询AWR快照把SQL的累计统计信息存进了dba_hist_sqlstatSQL文本存进了dba_hist_sqltext。注意这里的字段大多是DELTA结尾含义是“相邻两个快照之间的增量”。比如executions_delta就是这半个小时内多执行了几次elapsed_time_delta是这半个小时内累计花了多少微秒单位同样是微秒SELECT snap_id, executions_delta, ROUND(elapsed_time_delta / 1000000, 2) AS elapsed_sec, ROUND(cpu_time_delta / 1000000, 2) AS cpu_sec, disk_reads_delta, buffer_gets_delta, rows_processed_delta FROM dba_hist_sqlstat WHERE sql_id sql_id AND instance_number 1 ORDER BY snap_id;很多新手把elapsed_time_delta当成单次执行耗时这是典型的理解偏差。正确的用法是拿它除以executions_delta得到该快照间隔内单次执行的平均耗时这样才能判断SQL是不是在这个时间段突然变慢。SQL文本则从dba_hist_sqltext拿这里存储的是CLOB比v$sql里1000字节的sql_text完整得多适合保存超长SQLSELECT sql_text FROM dba_hist_sqltext WHERE sql_id sql_id;你可能想问为什么AWR里的SQL文本更完整因为AWR保存时用了专门的大对象存储不像共享池游标那样有64字节分片和1000字节截断的限制所以拿到这来读历史SQL是性价比最高的路径。4.3 用ASH和AWR报告回看“某一时刻到底在跑什么”AWR快照是每30或60分钟的一次“断面”如果想看更细粒度的历史比如19:23那一秒CPU为什么爆掉就要用ASH——Active Session History。12c里对应的历史视图是dba_hist_active_sess_history它在每个采样点上记录所有活跃会话的一次快照粒度默认1秒SELECT sample_time, session_id, session_serial#, sql_id, session_state, event FROM dba_hist_active_sess_history WHERE sample_time SYSDATE - 1 AND sql_id sql_id ORDER BY sample_time;通过这个视图你能还原一条SQL在过去24小时里被采样到多少次、每次都卡在什么等待事件上。如果session_state是ON CPU说明它当时正在消耗CPU如果是WAITING看event就知道是在等锁还是等IO。AWR报告依然是查历史SQL绩效最成熟的途径。执行?/rdbms/admin/awrrpt.sql按提示选择时间范围报告里“SQL Statistics”小节会列出该时间段内的Top SQL包含执行次数、平均耗时、命中率、物理读等指标。我个人的使用习惯是先看AWR报告选定时间窗口和疑似SQL_ID再用dba_hist_sqlstat精细化验证最后用ASH确认具体一秒的行为。5. 12c多租户下的特殊细节PDB里查SQL的坑5.1 12c的CON_ID与v$containers12c最大的架构变化是引入了CDB/PDB多租户。这意味着同一套实例里可能装了好几个业务数据库PDB而v$session、v$sql这些动态性能视图在CDB根库CDB$ROOT查询时会把所有PDB的会话和SQL全部混在一起呈现。每个视图里都有一列con_id表示这条信息属于哪个容器。如果忽略它你会看到两个PDB里各有一条SQL_ID完全相同的业务SQL统计还混在一起根本无法区分是谁的问题。查容器名用v$containersSELECT con_id, name, open_mode FROM v$containers;典型输出就是CON_ID为1的CDB$ROOT以及若干个业务PDB。11g时代的单实例库没有这一层概念这也是12c DBA和旧版DBA的一个明显认知分水岭。5.2 CDB根库里的查询姿势在CDB$ROOT里做Top SQL分析时我习惯显式把CON_ID和容器名列出来避免误判SELECT s.sql_id, c.name AS con_name, s.executions, ROUND(s.elapsed_time / 1000000, 2) AS elapsed_sec, SUBSTR(s.sql_text, 1, 60) AS sql_text_60 FROM v$sqlarea s LEFT JOIN v$containers c ON s.con_id c.con_id WHERE s.executions 0 ORDER BY s.elapsed_time DESC FETCH FIRST 10 ROWS ONLY;如果业务跑在PDB里连接PDB实例去查询时视图会自动限制只返回当前PDB的内容相对省心。但要注意很多DBA习惯直接连CDB$ROOT做全局运维这时候不加CON_ID过滤就是最大的坑。5.3 历史数据里的CON_ID处理dba_hist_sqlstat在CDB根库里同样包含多个PDB的数据查历史SQL必须关联v$containersSELECT hs.snap_id, c.name AS con_name, hs.sql_id, hs.executions_delta, ROUND(hs.elapsed_time_delta / 1000000, 2) AS elapsed_sec FROM dba_hist_sqlstat hs LEFT JOIN v$containers c ON hs.con_id c.con_id WHERE hs.sql_id sql_id ORDER BY hs.snap_id;我在真实环境里就栽过一次A、B两个PDB跑着同一套业务代码SQL_ID完全相同但A库数据量大走全表扫描B库走索引。在CDB根库查dba_hist_sqlstat时没过滤CON_ID两条路径的数据混在一起看起来就像同一批SQL时而快时而慢排查了好久才发现是PDB隔离问题。6. 一个真实案例从会话卡死到锁定的完整排查链路最后一个部分我用一次真实的生产故障把前面所有内容串起来。某天下午3点业务方反馈“出库单保存失败”操作员描述是“界面一直转圈等几分钟后报超时”。我到现场后的排查链路是这样的。先抓正在执行的会话SET LINESIZE 200 SELECT sid, serial#, username, sql_id, sql_exec_start, last_call_et, event, wait_class, blocking_session FROM v$session WHERE type USER AND status ACTIVE;结果里有一行非常扎眼sid287一条UPDATE语句event是enq: TX - row lock contentionwait_class是Applicationlast_call_et已经1200多秒。这显然不是在正常执行而是在等锁。顺着sql_id去v$sqltext里拼出完整文本确认是对T_ORDER表某一行做的更新。接着查谁阻塞了它SELECT sid, serial#, username, sql_id, event, sql_exec_start FROM v$session WHERE status ACTIVE AND type USER;发现另一个会话sid302正握着一把锁它自己的SQL是对相同订单号做INSERT但event是SQL*Net message from client——意思就是应用发完这条INSERT后一直没提交也没继续发消息就这么干挂着。几乎所有行锁等待的现场都是这个套路一头是等锁的UPDATE一头是拿到锁但不提交的INSERT/UPDATE。光看当前还不够为了确认这条UPDATE是不是一直这么慢我查了dba_hist_sqlstat里这个SQL_ID的历史表现。数据出来之后前三天它的elapsed_time_delta基本都在几百毫秒到一两秒之间唯独当天下午出现了跨越多个快照的异常陡增。再用dba_hist_active_sess_history确认异常窗口期这个会话的session_state几乎全是WAITINGevent集中在enq: TX - row lock contention根本不是CPU或IO的问题。处理办法倒是简单联系业务确认后把持有锁的sid302会话杀掉让它的事务回滚sid287的UPDATE随即恢复执行几秒完成。真正有价值的是复盘应用层缺了事务超时和提交机制两个业务操作同时改同一行订单暴露了并发控制缺陷。这类问题处理多了我现在的习惯很固定手机里备好两个SQL脚本一个查v$session抓现行一个查dba_hist_sqlstat看历史。任何SQL异常现场先解决“当前谁在跑”再用“历史它跑得怎么样”来判断是偶发还是趋势性问题。12c的锁问题往往不只是SQL本身的问题抓SQL只是第一步后续还要结合应用事务设计和锁等待来一起复盘不然下次换个SQL_ID同样的坑还会再来。