Oracle 12c SQL查询实战：从v$session到AWR追溯历史执行记录

刚接手一个Oracle 12c库最常被问到的问题就是“你帮我看看现在数据库里在跑什么SQL”或者“这个SQL昨天跑了多少次”说实话这类需求我处理过太多回了但每次在技术群里看到答案还是有人只会贴一个v$session的查询结果SQL文本截断了一半看着一头雾水。这篇文章就把12c环境下“当前正在执行的SQL”和“执行过的SQL”两条线彻底讲透所有脚本都是可以直接拿去跑的关键是每个脚本为什么这么写、查出来的字段怎么读我也一并说清楚保证你看完能真正上手而不是只会复制粘贴。1. 先把需求拆清楚两种“查SQL”根本不是一回事1.1 当前正在执行的SQL解决的是现场问题“正在执行的SQL”查的是数据库此时此刻的真实状态。典型场景就是业务方反馈系统变慢、某个页面一直在转圈、某张表锁住了这时候DBA的第一反应就是去看v$session里有哪些活跃会话这些会话正在执行什么SQL已经跑了多长时间卡在什么等待事件上。这类查询要求实时、准确而且往往要在业务还在跑的情况下介入所以脚本要轻、要快不能自己先拖垮数据库。很多人一上来就查v$sql的全表集合本身就大再来个全扫描那可真就是火上浇油了。正确做法是先从v$session这个“小入口”找到具体的会话再带着sql_id去关联其他视图从小结果集出发效率才有保障。1.2 执行过的SQL在不同语境下有三层含义“执行过的SQL”这个说法其实很含糊我这些年被问过太多次发现大家真正想要的往往是下面三种之一第一共享池里现在还缓存着的SQL。Oracle为了复用执行计划会把解析过的SQL文本、执行计划、对象权限这些放进共享池这部分能从v$sql和v$sqlarea里查。它反应的是最近一段时间的真实执行记录有执行次数、总耗时、逻辑读这些统计信息是性能分析最常用的来源。第二AWR快照里沉淀下来的历史SQL。共享池再大也有上限SQL被挤出缓存后就查不到了但AWR会按固定间隔把SQL的统计信息采样并落到数据库里通过dba_hist_sqltext和dba_hist_sqlstat能回溯好几天甚至更久。这解决的是“昨天凌晨那个慢SQL到底是什么”这类事后追溯的问题。第三某一个特定会话从头到尾执行过的完整SQL序列。这个就比较细了比如你要审计某个应用账号到底提交过哪些语句或者排查一个会话为什么报错那就得用10046事件跟踪或者审计功能去抓跟前面两种查法完全不同。搞清楚这三层区别你就知道为什么网上那些“一条SQL查历史”的帖子有时候根本不管用了——它们其实只覆盖了第一层。1.3 12c下SQL信息存放在哪共享池、V$视图与AWR的分工要理解哪些视图能查到什么先得明白Oracle的存储逻辑。SQL语句从客户端发过来经过语法解析、执行计划生成SQL文本和计划会缓存在共享池的库缓存Library Cache里这是内存结构速度最快但容量有限而且有淘汰机制。v$session、v$sql、v$sqltext这些视图本质上是内存中相关结构的“投影”查到的都是缓存里还活着的内容。会话一断开v$session里的记录就没了SQL被挤出共享池v$sql里也就查不到了。AWR则是另一条线。后台进程每隔一段时间默认一小时会做一次快照把当时系统里的关键统计信息、Top SQL等内容持久化保存下来默认保留8天。所以AWR是“抽样档案”不是全量流水线但也正是因为它落盘了才能扛得住共享池的淘汰和实例重启。一句话总结查当下看V$视图查历史翻AWR。这两条线你抓住12c里99%的SQL查询需求都有了解法。2. 查看当前正在执行的SQL三套组合拳2.1 标准打法v$session 联查 v$sqltext 拿完整文本这是我用得最多的一条脚本直接解决了“看到SQL但文本被截断”的痛点。先看整体语句SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.sql_child_number AS child, s.event, s.wait_class, s.sql_exec_start, ROUND((SYSDATE - s.sql_exec_start) * 86400, 1) AS exec_seconds, s.program, s.machine, t.sql_text FROM v$session s LEFT JOIN v$sqltext t ON t.sql_id s.sql_id AND t.child_number s.sql_child_number WHERE s.username IS NOT NULL AND s.type USER AND s.status ACTIVE ORDER BY exec_seconds DESC, s.sid, t.piece;这里有几个关键点要展开说。为什么要关联v$sqltext而不是直接取v$sql.sql_text因为v$sql里SQL_TEXT列只存前1000个字符一条超过1000字符的SQL就会在v$sql里被硬生生截断而v$sqltext会把SQL文本按片存储每行一片配合PIECE字段排序后拼起来才是完整内容。你以为看到了全貌其实只是冰山一角用这个视图才是正解。LEFT JOIN的意义在于极少数情况下会话正在解析或切换SQLv$sqltext里可能暂时关联不到对应行左连接能保证会话信息不丢。实际输出里如果你发现SQL_TEXT为空多半是会话正处于某个非SQL执行状态比如PL/SQL里做CPU计算这时用第3.2节的方法去看等待事件就能明白。字段解读也别漏。STATUS‘ACTIVE’表示这个会话正在消耗资源SQL_EXEC_START是当前SQL开始执行的时间用SYSDATE减一下就能算出已经跑了多久我上面乘86400转成秒方便排序。EVENT和WAIT_CLASS这两个字段特别有用它们告诉你这个“活跃”到底是在CPU上算还是在等磁盘读、等锁、等网络。很多新手以为ACTIVE就是在高效工作其实一个会话如果长时间停在‘buffer busy waits’上说明它一直在等内存队列这时候抓SQL只是第一步真正要解决的是并发和热块问题。2.2 拿到sql_id之后再用v$sql挖性能统计v$session只告诉你“正在执行什么”但要说这条SQL消耗了多少资源还得去v$sql里取累计统计。我常用的追击脚本是这样的SELECT sql_id, sql_text, executions, elapsed_time / 1000000 AS elapsed_sec, cpu_time / 1000000 AS cpu_sec, buffer_gets, disk_reads, rows_processed, last_active_time FROM v$sql WHERE sql_id sql_id;ELAPSED_TIME和CPU_TIME的单位都是微秒除以1000000才是秒这也是很多人查出来数字巨大吓一跳的原因。EXECUTIONS是这条SQL从进入共享池以来的累计执行次数如果这个值是0说明它刚被解析还没真正跑完一轮。BUFFER_GETS是逻辑读DISK_READS是物理读两者比值大说明数据基本都在内存命中比值小则说明频繁走物理IO该看看执行计划是不是出了问题。我一般会根据sql_id继续挖执行计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id sql_id, cursor_child_no 0, format ALLSTATS LAST));这里要注意的是12c中如果SQL是用并行执行器跑的DISPLAY_CURSOR的输出里会多出PX相关操作别被一大片执行计划吓到重点看第一列的Operation找全表扫描TABLE ACCESS FULL和大排序SORT ORDER BY这俩往往是性能黑洞。2.3 长事务的实时监控v$sql_monitor如果正在执行的SQL已经跑了很久v$session只能告诉你它还没结束但中间到底跑到哪一步了、每步消耗多长时间就得请出12c自带的实时SQL监控功能。v$sql_monitor视图就是干这个的SELECT sql_id, status, sql_text, elapsed_time / 1000000 AS elapsed_sec, cpu_time / 1000000 AS cpu_sec, physical_read_requests, username FROM v$sql_monitor WHERE status EXECUTING ORDER BY elapsed_time DESC;这个监控默认只对执行超过5秒且消耗资源的语句生效短小精悍的查询在里面是看不到的这是Oracle有意为之怕监控本身开销太大。一旦进入了监控范围还能直接生成一份可读的监控报告SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id sql_id, type TEXT, report_level ALL) FROM dual;这份报告会把SQL执行过程中每个操作步骤的实际行数、耗时、内存使用都列出来比执行计划里估计的数值靠谱得多。我最常用的场景是跑批任务挂住了用这条命令看看到底卡在哪个哈希连接上比一遍遍刷新v$session效率高得多。2.4 12c特有的坑多租户环境要看CON_IDRAC要上GV$12c引入了多租户架构后V$视图里多了CON_ID列。在PDB里查v$session通常只会看到当前PDB的会话在CDB根上查能看到所有PDB的但如果你不加过滤条件统计结果会混在一起业务归因就容易张冠李戴。所以我习惯在查询里加上WHERE s.con_id 0再按需配合SELECT con_id, name FROM v$containers;如果是RAC集群还得记得把单实例的V$换成GV$多了个INST_ID列用来区分是哪个节点上的会话。这里有个很容易犯的错你以为查到了SQL在跑实际上SQL只在节点2上执行节点1上你看到的只是一个会话状态排查问题时看错节点会浪费大量时间。3. 查看执行过的SQL从共享池到AWR的历史走廊3.1 第一站v$sql 和 v$sqlarea共享池里还热乎的记录共享池里只要SQL没被挤出内存它的执行历史就一直累积在v$sql里。这里最常用的需求是“找出过去一段时间最消耗资源的Top SQL”我一般这么写SELECT sql_id, SUBSTR(sql_text, 1, 100) AS sql_text_prefix, executions, elapsed_time / 1000000 AS elapsed_sec, cpu_time / 1000000 AS cpu_sec, buffer_gets, disk_reads, last_active_time FROM v$sql WHERE executions 0 AND last_active_time SYSDATE - INTERVAL 2 HOUR ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;这里用了12c新增的FETCH FIRST语法等价于老版本里的ROWNUM 10但语义更清晰。SUBSTR只是为了展示前100个字符实际分析时用sql_id去精确关联别让一长串SQL文本把屏幕刷爆。v$sqlarea和v$sql的差别在于v$sqlarea是每个SQL一条汇总记录而v$sql是同一个SQL可能因为文本格式、绑定变量等细微差异生成多个子游标所以v$sql里会出现一个SQL_ID多行的情况。做排行榜直接用v$sqlarea更干净做精确分析看v$sql更细。3.2 第二站dba_hist_sqltextAWR里沉淀的完整档案共享池里的SQL再牛也扛不住淘汰想查昨天甚至三天前的SQL执行情况就得进AWR。默认情况下每小时一次快照保留8天这个保留期可以通过修改AWR设置调整但生产环境我一般不建议拉太长磁盘开销和查询性能都要权衡。先看有哪些快照SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 20 ROWS ONLY;然后在快照区间里找SQL统计SELECT ss.snap_id, ss.sql_id, st.sql_text, ss.executions_delta, ss.elapsed_time_delta / 1000000 AS elapsed_sec_delta, ss.buffer_gets_delta, ss.disk_reads_delta FROM dba_hist_sqlstat ss JOIN dba_hist_sqltext st ON st.sql_id ss.sql_id AND st.dbid ss.dbid WHERE ss.begin_interval_time SYSDATE - 7 AND ss.executions_delta 0 ORDER BY ss.elapsed_time_delta DESC FETCH FIRST 20 ROWS ONLY;这里要注意字段名里带_DELTA的含义。AWR快照存的是两个快照之间这段区间的增量不是累计值。你看到EXECUTIONS_DELTA等于50意思是这个快照区间内执行了50次千万别当成总的执行次数。我见过有人拿着DELTA当总量分析最后得出的结论完全跑偏。DBA_HIST_SQLTEXT里存的是SQL完整文本这里没截断问题放心用。但要注意它和DBA_HIST_SQLSTAT通过SQL_ID和DBID关联DBID别漏了多租户环境下不同PDB的DBID不一样不加这个条件容易串数据。3.3 第三站用sql_id把现状和历史串成一条线我在实际分析中特别喜欢用一个“单点排查”思路拿到一个可疑的sql_id后把它从三个视角都看一遍。现状看v$sql统计历史看dba_hist_sqlstat执行计划历史看dba_hist_sqlplan。这样就能回答“这个SQL是不是一直这么慢”和“执行计划是不是最近变了”这两类问题。比如一条SQL今天突然慢了我第一步拿它的sql_id去v$sql看当前的执行计划的HASH值再去dba_hist_sqlplan查它前几天的计划HASH值如果两个值不一样说明执行计划变了那就要进一步看统计信息是不是过期了、是不是有新的索引被创建。如果HASH值一样但还是慢那就更可能是数据量变了或者系统资源被其他SQL抢占排查方向完全不同。这种“以sql_id为核心”的查法比漫无目的地翻日志高效得多。3.4 进阶需求单个会话完整SQL序列的抓取有一种需求上面所有视图都解决不了就是“这个会话从登录到退出到底执行了哪些SQL”。比如你要排查某个应用会话是不是执行了不该执行的DDL或者要复现一个报错。这时候就该开10046事件跟踪了-- 开启跟踪 EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE); -- 如果知道会话的SID可以在别的会话里操作 EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(123, 456, TRUE);跟踪文件会落在数据库的trace目录然后用工具解析tkproftkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1234.trc /tmp/sql_trace.txt生成的可读文件里能看到每条SQL的执行时间、物理读、逻辑读、执行计划。这种方式的代价是性能损耗生产环境开trace要谨慎一般只在问题窗口开几分钟抓完立刻关掉。我在实战里通常只对特定会话开绝不会全局开否则trace文件能把磁盘撑爆。如果你想看的不是“某个会话的SQL流”而是“某个SQL的绑定变量具体值”那就用v$sql_bind_captureSELECT sql_id, name, position, datatype_string, value_string, last_captured FROM v$sql_bind_capture WHERE sql_id sql_id ORDER BY position;这条在分析SQL里字面值被绑定变量替代后找不到具体值时特别有用。12c里默认捕获绑定变量但只有新解析的SQL进来才重新捕获所以VALUE_STRING可能是很久以前的旧值别太依赖这个字段的时间戳。4. 实操复盘一次CPU飙高后的SQL定位全过程4.1 现场还原和排查思路某公司生产库下午三点左右CPU突然飙到90%以上业务方反馈核心列表接口响应超过5秒监控平台弹了一堆告警。接手之后我没有先重启什么、清什么缓存而是按照现场→统计→历史的顺序来排查。现场就是“此刻数据库里到底在做什么”统计是“在跑的SQL消耗了哪些资源”历史是“这个SQL最近几天是不是一直这样”。三步走完问题基本就水落石出了。4.2 第一步按会话执行时长倒序锁住肇事会话登录后先执行第2.1节那条标准脚本把ACTIVE会话按已执行秒数倒序排出来。输出里前三行都是同一个SID的会话USERNAME是一个应用账号SQL_EXEC_START显示15分钟前就开始了EXEC_SECONDS接近900秒EVENT是‘ON CPU’说明它一直在CPU上跑不是卡在等待上。这个信息量很大如果一个会话长时间停在等待事件上问题可能是锁或IO但如果长时间停在ON CPU上说明它真的在密集计算SQL本身出问题的概率极高。4.3 第二步取SQL全文和资源统计拿到SQL_ID后先用v$sqltext拼完整文本发现是一条带三个嵌套子查询的UPDATE语句看起来是在做批量状态更新。接着用第2.2节的v$sql统计脚本查BUFFER_GETS已经累计到几千万DISK_READS才几百说明它一直在内存里做哈希连接CPU自然居高不下。再看看执行计划三个子查询里有两个走了全表扫描其中一张表有500多万行。问题就清楚了一次UPDATE把十几万行数据的关联计算全部压在了CPU上遇到数据量一涨现场直接爆掉。4.4 第三步回溯AWR确认它是“惯犯”还是“偶发”接下来用第3.2节的AWR脚本查这个SQL_ID过去7天的表现。结果让我很意外这条UPDATE过去一周每天下午两点半左右都会执行一次但ELAPSED_TIME_DELTA都在2秒以内唯独今天暴涨。既然执行计划和统计信息都没变那变量就在另一头这张表的日增数据量今天翻了好几倍而且子查询关联列上的索引因为数据倾斜失效了。这时候优化方向就明确了——要么调整SQL写法把子查询改成JOIN并强制走索引要么跟业务确认是不是数据导入脚本出了偏差把数据量异常的先处理好。这个案例说到底是三板斧的功劳先看现场抓会话再看统计锁SQL最后回溯历史找规律。任何一个环节缺失都可能让你在错误的方向上折腾半天。5. 常见问题与避坑实录5.1 问题速查表我把自己这些年踩过的坑整理成了一张表基本覆盖日常高频问题现象可能原因解决方向v$sql里SQL_TEXT只有1000字符视图字段限制改用v$sqltext按PIECE拼接v$sql查不到几天前的SQL共享池淘汰机制改查dba_hist_sqltextAWR里也查不到某条SQL快照未覆盖该SQL或保留期已过确认快照区间或提前手动创建快照同一SQL_ID出现多行有多个子游标检查SQL文本格式差异、绑定变量、PDB的CON_ID会话ACTIVE但event是空闲等待等待类型不同含义不同结合WAIT_CLASS和等待事件分析别只看ACTIVE查询权限报ORA-00942缺少视图授权按5.4清单GRANT相关视图权限12c多租户串数据未过滤CON_ID在CDB中按CON_ID区分PDBRAC环境查不到节点信息用的是V$而非GV$改用GV$视图并关注INST_ID5.2 SQL_TEXT被截断的完整解决方案这个问题几乎每周都会有人问。v$sql和v$sqlarea里的SQL_TEXT是VARCHAR2(1000)超过1000字符就会被截断而且截断点在中间看起来像残废的句子。解决方式就是v$sqltext它按64字节左右一片存储SQL_ID和CHILD_NUMBER相同的一组行拼起来就是全文SELECT sql_text FROM v$sqltext WHERE sql_id sql_id AND child_number child ORDER BY piece;如果你希望保留原始换行符方便阅读格式化过的SQL就用v$sqltext_with_newlines替换v$sqltext。这个视图在调试复杂报表SQL时特别好用尤其是带了一堆WITH子句的语句v$sql里那截断的1000字符根本没眼看。5.3 为什么历史SQL查不到这是机制问题不是Bug很多人在v$sql里翻三天前的SQL翻不到就以为数据库坏了。记住共享池是内存缓存只要新的SQL不断进来旧的SQL就会被挤出去这就是老化机制。v$sql存在的意义是复用执行计划不是给你当永久日志用。要看真正的历史只有AWR。但你也要理解AWR只是抽样默认一小时一次快照如果一条SQL执行时间很短、又恰好处在两次快照之间它可能根本不会被记录。所以遇到“时间点很敏感”的排查我习惯先手动创建一个快照EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();这样就把当前状态钉在共享池里等于上了个双保险。演练时可以先建快照复现完SQL再建快照两个快照之间的数据就是完整的复现窗口。5.4 权限自查清单查询V$视图和AWR表大多是动态性能视图不同账号权限差别很大。我整理了一个最小授权集GRANT SELECT ON sys.v_$session TO 某用户; GRANT SELECT ON sys.v_$sqltext TO 某用户; GRANT SELECT ON sys.v_$sql TO 某用户; GRANT SELECT ON sys.v_$sqlarea TO 某用户; GRANT SELECT ON sys.v_$sql_monitor TO 某用户; GRANT SELECT ON sys.v_$sql_bind_capture TO 某用户; GRANT SELECT ON sys.dba_hist_sqltext TO 某用户; GRANT SELECT ON sys.dba_hist_sqlstat TO 某用户; GRANT SELECT ON sys.dba_hist_snapshot TO 某用户;如果你的账号本身有DBA角色或者SELECT_CATALOG_ROLE这些视图基本都能查。我只是在帮开发同学开只读账号时才会精确到视图级别一个个授执行一次脚本一劳永逸。5.5 几个平时文档里不会写的经验第一查正在执行的SQL时如果你发现一条SQL已经执行很久且持续时间一直在涨先别急着在业务高峰期杀会话。先用第2.3节的SQL Monitor报告看看它到底在做什么如果已经接近尾声让它跑完可能比重启更快更安全重启后UNDO回滚的代价往往更大。我见过太多人看到慢SQL就kill session结果回滚时间比原执行时间还长得不偿失。第二顺手把AWR的快照频率调到30分钟不现实但针对重要时间窗口可以手动干预。比如你要做性能压测压测前一个快照、压测后一个快照中间数据一清二楚比事后从默认快照里翻要精确得多。第三很多人忽视LAST_ACTIVE_TIME这个字段。它不仅能帮你筛“最近跑过的SQL”还能确认一条SQL是不是真的还在被使用。如果你发现共享池里躺着一堆LAST_ACTIVE_TIME还在几周前的SQL说明业务已经不用它们了这类SQL占着库缓存空间可以考虑清掉或推动业务优化给新SQL腾地方。第四记录SQL时尽量用sql_id而不是文本内容。SQL_ID是Oracle根据SQL文本计算出的唯一标识同样的SQL在任何环境都能算出相同的SQL_ID拿它去跟执行计划、AWR、绑定变量关联效率比LIKE匹配文本高出一个量级。我平时都是随身带一个“三板斧”脚本文件会话实时查询、SQL统计查询、AWR历史查询一个文件搞定80%的排查场景。