数据库突然跑慢了开发在群里喊“我的报表SQL怎么用了十几分钟”而你登录数据库后第一件事往往就是看看这个会话现在到底在跑什么跑完的那些SQL又留下了哪些痕迹。这个话题我在Oracle 12c环境里用了好几年几乎每周都会碰到今天就把“查看执行过的SQL”和“当前正在执行的SQL”这两类需求一次性讲清楚顺便把可以直接抄走的脚本整理出来。Oracle 12c最让我喜欢的一点就是它的动态性能视图体系已经非常成熟从实时会话到历史SQL从文本到执行统计基本都能通过V$视图或AWR/ASH找到答案。V$SESSION、V$SQLTEXT、V$SQL、V$SQLAREA、V$ACTIVE_SESSION_HISTORY、DBA_HIST_SQLTEXT这几张视图基本覆盖了从“现在正发生”到“过去发生过”的全过程。这篇文章适合DBA、运维、开发以及正在做SQL优化的人看不管你是刚接触Oracle还是已经写过不少SQL里面提到的排查思路和脚本都能直接用。1. 先分清你要查的是哪一类SQL1.1 “正在执行”和“执行过”是两种完全不同的思路很多新手一上来就纠结“为什么我在V$SQL里查不到那条正在跑的SQL”其实根因是把实时状态和历史缓存搞混了。当前正在执行的SQL指的是某个会话此刻正在数据库上运行的那条语句。这类信息主要存在于V$SESSION里它记录了每个会话当前的状态、SQL_ID、等待事件、执行开始时间等。配合V$SQLTEXT或V$SQL就能把SQL文本捞出来。它的特点是“实时”会话一旦结束这条记录可能就看不到了。已经执行过的SQL是另一回事。Oracle在SQL执行完以后并不会立刻把语句和统计信息扔掉而是会在共享池里缓存一段时间。此时去查V$SQL或V$SQLAREA能看到SQL文本、执行次数、耗时、逻辑读这些信息。如果SQL已经被挤出共享池或者你想看更长时间之前的表现就得借助AWR快照里的DBA_HIST_SQLTEXT和DBA_HIST_SQLSTAT或者ASH采样数据。拿生活里的事打个比方V$SESSION像监控摄像头只在当时拍下画面V$SQL像行车记录仪里最近几天的录像过一阵就可能被覆盖AWR则像定期归档的监控记录能查到很久以前的情况。所以先想清楚“你是要抓现行还是要翻旧账”后面的查询语句自然就不会选错。1.2 Oracle 12c里监控SQL的核心入口有哪些我用一张表把主要视图的定位列清楚后面每一步都会用到它们。视图/数据源主要作用保留情况常用场景V$SESSION当前会话状态含SQL_ID、等待事件、执行开始时间实时会话结束后不保留会话详情抓正在执行的会话V$SQLTEXTSQL文本按片存储共享池内存存续期间保留拼接长SQL查看文本V$SQL游标级别的SQL统计可以看单条SQL、子游标共享池内存存续期间保留分析SQL执行次数、耗时、资源消耗V$SQLAREA按SQL文本聚合的统计视图同上快速找TOP SQL、按条件过滤V$ACTIVE_SESSION_HISTORY当前实例内存中的ASH采样内存中默认约1小时会写入AWR查最近一小时某时刻在跑什么DBA_HIST_SQLTEXTAWR持久化的SQL文本随AWR保留默认8天左右查历史SQL文本DBA_HIST_SQLSTATAWR持久化的SQL执行统计同上做历史TOP SQL分析这些视图在12c多租户环境下要注意容器范围。在PDB里查通常只能看到该PDB相关的会话和SQL如果需要看整个CDB层面的信息就要连到CDB$ROOT去查或者使用GV$开头的全局视图。1.3 动手前先把权限准备好如果使用普通业务账号执行这些查询经常会碰到ORA-00942: table or view does not exist。V$开头的动态性能视图底层实现是V_$开头的视图普通用户默认没有访问权限。最简单的办法是给账号授DBA角色但生产环境不建议这么干。更合理的方式是按需授权比如GRANT SELECT ON V_$SESSION TO my_user; GRANT SELECT ON V_$SQL TO my_user; GRANT SELECT ON V_$SQLTEXT TO my_user; GRANT SELECT ON V_$SQLAREA TO my_user; GRANT SELECT ON V_$ACTIVE_SESSION_HISTORY TO my_user;如果是查DBA_HIST开头的AWR视图普通账号通常也需要相应权限比如SELECT_CATALOG_ROLE或者单独授权。别小看这一步我在实际中见过好几次“SQL写得没问题但就是报错”的情况最后都卡在权限上。2. 抓现行查看当前正在执行的SQL2.1 一条SQL直接捞出正在跑的SQL最基础、也最常用的查询是把V$SESSION和V$SQLTEXT关联起来。V$SESSION负责找会话V$SQLTEXT负责把SQL文本按片拼出来。SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.sql_exec_start, q.piece, q.sql_text FROM v$session s LEFT JOIN v$sqltext q ON q.sql_id s.sql_id WHERE s.type ! BACKGROUND AND s.sql_id IS NOT NULL ORDER BY s.sid, s.serial#, q.piece;这里我特意加了一个LEFT JOIN而不是INNER JOIN是为了避免某些会话虽然存在但没有SQL文本时整个会话被过滤掉。V$SQLTEXT里的SQL文本是分片存储的字段PIECE表示片段序号所以查询结果里同一条SQL会显示成多行这是正常的。真正要整行看的时候把SQL_TEXT拼起来即可。执行后你大概率会看到类似下面的结果某个SID下PIECE从0、1、2依次排列片段连起来就是完整语句。筛选时按SID定位会话再按PIECE排序效果最直观。2.2 根据SQL_ID反查完整SQL和统计信息如果已经拿到了目标会话的SQL_ID下一步通常是想看完整SQL文本以及它的执行历史。短SQL直接查V$SQL就行SELECT sql_id, sql_text FROM v$sql WHERE sql_id 5ksp7x4p9x0xt;V$SQL里有一个SQL_FULLTEXT字段是CLOB类型存的是完整文本。SQL文本太长时在SQL*Plus里需要先设置SET LONG 2000000 SET LONGCHUNKSIZE 2000000 SET PAGESIZE 0否则你看到的可能是一个“clob truncated”或者被截断的内容。在PL/SQL Developer、DBeaver这类图形工具里一般直接在结果网格里双击就能看到完整内容。如果V$SQL里刚好没有这条记录别急再用V$SQLTEXT按SQL_ID查一遍SELECT sql_text FROM v$sqltext WHERE sql_id 5ksp7x4p9x0xt ORDER BY piece;2.3 SQL_ID为NULL的会话卡在哪了查V$SESSION时可能会发现某些活跃会话的SQL_ID是NULL。这个情况很常见遇到时不要慌通常代表当前会话并没有在执行一条独立的SQL语句而是卡在别的地方。常见原因有两个一是会话正在执行PL/SQL块比如一个存储过程或匿名块V$SESSION上的SQL_ID可能显示为外层块的SQL_ID而不是正在执行的内部单条SQL二是会话正在等待某个资源例如等待行锁、等待某个日志切换、等待其它会话提交事务此时当前操作可能还没有形成完整的SQL执行上下文。当SQL_ID是NULL时我会换一个思路先看这个会话在等什么。事件名称往往比SQL文本更容易暴露问题。例如enq: TX - row lock contention表示在等行锁Buffer Busy Wait表示在等数据块访问log file sync表示在等日志写盘。SELECT sid, event, wait_class, wait_time, seconds_in_wait, state, blocking_session FROM v$session WHERE sid 1234;再把BLOCKING_SESSION的消息带出来看看是谁锁住了它。很多时候“查正在执行的SQL”只是第一步真正有价值的是顺着SQL找到等待链再找到源头会话。2.4 12c下的活跃会话实时监控如果数据库突然变慢你往往想一眼看到“现在所有活跃会话都在干什么”。下面这个脚本我常用来做快速体检SELECT s.sid, s.serial#, s.username, s.module, s.event, s.wait_class, s.sql_id, ROUND(s.sql_exec_start - SYSDATE, 2) AS exec_minutes, q.sql_text FROM v$session s LEFT JOIN v$sql q ON q.sql_id s.sql_id WHERE s.status ACTIVE AND s.type ! BACKGROUND ORDER BY sql_exec_start ASC;12c的V$SESSION里有一个SQL_EXEC_START字段这个字段很好用能直接看到当前SQL是什么时候开始执行的。按它排序就能把最老、撑得最久的语句排在最上面。不过要注意如果你正在跑这条查询查询本身也会出现在结果里这是正常的。如果只想关注“真正干活”的会话可以把过滤条件改成WAIT_CLASS NOT IN (Idle)把空闲等待剔除掉。生产库上跑这个的时候尽量挑非业务高峰否则一次全表扫描V$SESSION也不便宜虽然平时影响不大。3. 翻旧账查看已经执行过的SQL3.1 V$SQL与V$SQLAREA怎么选执行过的SQL并不会立刻消失Oracle会把它们和对应的统计信息缓存在共享池里。V$SQL和V$SQLAREA就是用来查这些缓存的。V$SQL是游标级别的数据同一条SQL文本因为绑定变量值、执行计划、NLS参数等差异可能产生多个子游标也就是多条记录。如果你只关心SQL文本层面的聚合表现直接看V$SQLAREA更方便它把相同文本的SQL合并成一行并汇总执行次数和资源消耗。实际使用时找TOP SQL是最常见的需求。比如查过去一段时间内按总耗时排序的TOP 10 SQLSELECT sql_id, sql_text, executions, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec, ROUND(cpu_time / 1000000, 2) AS cpu_sec, disk_reads, buffer_gets, rows_processed, first_load_time, last_active_time FROM v$sql WHERE executions 0 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;这个写法用到了12c开始支持的FETCH FIRST语法只有在12c及以上版本才能直接跑。如果数据库是11g或更早版本需要改成ROWNUM 10的写法。时间单位方面V$SQL里的ELAPSED_TIME、CPU_TIME默认单位是微秒除以1000000才是秒我经常看到有人把数字读错这里提醒一下。如果想找某类特征SQL比如全表扫描严重的SQL可以按DISK_READS排序如果想看逻辑读偏高的按BUFFER_GETS排序。不同场景换不同的排序键即可。3.2 从V$SQLTEXT把完整SQL拼出来V$SQLAREA和V$SQL里都有SQL_TEXT字段但早版本中SQL_TEXT列默认只取SQL的前1000个字符。想要完整文本在12c中我一般这么处理先用SQL_ID到V$SQL里查SQL_FULLTEXTSELECT sql_fulltext FROM v$sql WHERE sql_id 5ksp7x4p9x0xt;如果这条SQL已经不在共享池里就用V$SQLTEXT按片段拼SELECT LISTAGG(sql_text, ) WITHIN GROUP (ORDER BY piece) AS full_sql FROM v$sqltext WHERE sql_id 5ksp7x4p9x0xt;我个人不喜欢在命令行工具里强制输出超长CLOB更习惯把SQL_FULLTEXT导出到文件里再分析比如在SQL*Plus里用SPOOL命令SPOOL sql_text.txt SET LONG 2000000 SET PAGESIZE 0 SELECT sql_fulltext FROM v$sql WHERE sql_id 5ksp7x4p9x0xt; SPOOL OFF这样得到的文本干净、完整后续丢到PL/SQL Developer或者文本编辑器里对比修改都方便。3.3 V$SQL里查不到时用AWR历史快照共享池的空间是有限的SQL执行完很久之后可能会被Oracle按LRU算法换出。这时候再看V$SQL或V$SQLAREA就会扑空。要查这种“过去式”SQL必须看AWR快照。AWR默认每小时生成一次快照12c里默认保留8天。快照相关的数据分布在几类表里其中DBA_HIST_SQLTEXT保存SQL文本DBA_HIST_SQLSTAT保存每个快照周期内的SQL执行统计DBA_HIST_SNAPSHOT保存快照时间。举个例子我想查最近一天内哪个SQL累计消耗时间最多SELECT st.sql_id, st.sql_text, h.snap_id, h.executions_delta, ROUND(h.elapsed_time_delta / 1000000, 2) AS elapsed_sec, h.buffer_gets_delta, h.disk_reads_delta FROM dba_hist_sqlstat h JOIN dba_hist_sqltext st ON st.sql_id h.sql_id JOIN dba_hist_snapshot s ON s.snap_id h.snap_id WHERE s.begin_interval_time SYSDATE - 1 AND h.elapsed_time_delta 0 ORDER BY h.elapsed_time_delta DESC FETCH FIRST 20 ROWS ONLY;这里有几处容易踩坑的地方。第一DBA_HIST_SQLSTAT里的字段名大多带_DELTA后缀表示这个快照周期内的增量不是累计总量。第二SNAP_ID必须关联快照时间表才能判断时间范围。第三如果快照间隙把某些SQL的增量给抹掉了那也没办法Oracle只会统计快照覆盖到的区间。想单独查某个SQL_ID的文本可以直接用DBA_HIST_SQLTEXTSELECT sql_text FROM dba_hist_sqltext WHERE sql_id 5ksp7x4p9x0xt;AWR里的SQL_TEXT同样是以CLOB保存工具里看的时候注意截断问题。3.4 ASH按秒级采样看某个时间点的会话SQL如果说AWR是每隔一小时拍一张全景照片那么ASH就是每一秒为活跃会话做一次快照。12c中内存里V$ACTIVE_SESSION_HISTORY会保留最近一段时间的采样数据并且会定期把数据刷到DBA_HIST_ACTIVE_SESS_HISTORY表里保留时间与AWR一致。ASH非常适合回答这类问题“今天凌晨3点CPU突然飙高那时候到底哪些SQL在跑”内存里的最近一小时可以直接查SELECT sql_id, TO_CHAR(sample_time, hh24:mi) AS sample_minute, COUNT(*) AS sample_cnt FROM v$active_session_history WHERE sample_time SYSDATE - INTERVAL 1 HOUR AND sql_id IS NOT NULL GROUP BY sql_id, TO_CHAR(sample_time, hh24:mi) ORDER BY sample_cnt DESC FETCH FIRST 10 ROWS ONLY;想查更早时间点就用DBA_HIST_ACTIVE_SESS_HISTORY把时间条件改到对应区间就行。这个方法在复盘故障时特别好用因为我不用依赖当时有人在现场盯着数据库只要ASH采样开着事后一样能还原现场。4. 实操过程从定位会话到拿到完整SQL4.1 一次完整的慢SQL排查演示我拿一个真实场景来说说整套流程怎么走。业务侧反馈“保存单据非常慢”后台日志里记录了一个等待事件但没有给出具体SQL。这时候我先查当前哪些会话处于ACTIVE状态并直接关联SQL文本SELECT s.sid, s.serial#, s.username, s.event, s.wait_class, s.sql_id, s.sql_exec_start, q.piece, q.sql_text FROM v$session s LEFT JOIN v$sqltext q ON q.sql_id s.sql_id WHERE s.type ! BACKGROUND AND s.status ACTIVE ORDER BY s.sql_exec_start ASC;很快发现一个SID为321的会话WAIT_EVENT是enq: TX - row lock contentionSQL_ID是8b4u0d2y1k9x7执行开始时间已经过去8分钟。这说明它被锁堵住了不是SQL本身慢。接着查是谁锁住了它SELECT sid, serial#, username, blocking_session, event, wait_class FROM v$session WHERE sid 321;查出BLOCKING_SESSION为452。再去看452这个会话正在执行什么SELECT sid, sql_id, sql_exec_start, event FROM v$session WHERE sid 452;如果452这个会话还在执行同一条慢SQL那就继续看它的执行计划SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(sql_id 8b4u0d2y1k9x7, format ALLSTATS LAST));整个链路跑下来问题点一般就浮出水面了可能是452那个会话长时间未提交也可能是它执行了全表扫描拖慢了行锁释放。这套流程不需要额外工具SQL*Plus或者任何数据库客户端都能做。4.2 常用脚本模板直接抄把前面零散的查询整理成几个可以直接复制使用的模板我日常就是靠这几个脚本轮换着查。模板一实时监控正在执行的SQLSELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.sql_exec_start, q.sql_text FROM v$session s LEFT JOIN v$sqltext q ON q.sql_id s.sql_id WHERE s.type ! BACKGROUND AND s.username IS NOT NULL AND s.sql_exec_start IS NOT NULL ORDER BY s.sql_exec_start ASC, q.piece;模板二查看共享池中执行次数最多的SQLSELECT sql_id, sql_text, executions, ROUND(elapsed_time / 1000000, 2) AS elapsed_sec, ROUND(cpu_time / 1000000, 2) AS cpu_sec, buffer_gets, disk_reads FROM v$sql WHERE executions 0 ORDER BY executions DESC FETCH FIRST 20 ROWS ONLY;模板三从AWR查最近一天最耗时的SQLSELECT st.sql_id, st.sql_text, h.executions_delta, ROUND(h.elapsed_time_delta / 1000000, 2) AS elapsed_sec, h.buffer_gets_delta, h.disk_reads_delta FROM dba_hist_sqlstat h JOIN dba_hist_sqltext st ON st.sql_id h.sql_id JOIN dba_hist_snapshot s ON s.snap_id h.snap_id WHERE s.begin_interval_time SYSDATE - 1 AND h.executions_delta 0 ORDER BY h.elapsed_time_delta DESC FETCH FIRST 20 ROWS ONLY;模板四查某个时刻ASH里所有活跃SQLSELECT sql_id, COUNT(*) AS sample_cnt, ROUND(COUNT(*) * 10 / 1000, 2) AS active_secs FROM dba_hist_active_sess_history WHERE sample_time BETWEEN TO_TIMESTAMP(2024-11-10 03:00:00, yyyy-mm-dd hh24:mi:ss) AND TO_TIMESTAMP(2024-11-10 03:05:00, yyyy-mm-dd hh24:mi:ss) AND sql_id IS NOT NULL GROUP BY sql_id ORDER BY sample_cnt DESC FETCH FIRST 10 ROWS ONLY;这里COUNT(*)乘以10是大概估算活跃时长因为ASH默认每10秒采样一次一条记录大约代表一个会话在对应10秒内处于采样时刻的活跃状态具体数值只能当参考不能当精确统计。4.3 12c里的高能工具SQL Monitor别忽略查SQL这件事12c还有一个被低估的功能SQL Monitor。它不是一张视图那么简单而是Oracle对特定的、较长时间运行的SQL自动开启的监控机制。默认情况下执行时间超过5秒或者并行执行的SQL会被监控监控到的信息比V$SESSION更丰富包括实时执行计划、活动行数、等待占比、内存消耗等。先看哪些SQL正在被监控SELECT sql_id, sql_text, elapsed_time / 1000000 AS elapsed_sec, status, plan_operation FROM v$sql_monitor WHERE status EXECUTING;拿到SQL_ID以后生成一份可读的监控报告SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(sql_id 8b4u0d2y1k9x7, type TEXT) AS report FROM dual;REPORT_SQL_MONITOR的TYPE参数还可以填HTML和ACTIVEACTIVE格式是一个可交互的HTML页面能展开执行计划节点看指标。排查长时间运行的SQL时SQL Monitor往往比传统V$SESSION更直观因为它直接告诉你每个执行计划步骤处理了多少行、消耗了多少时间。4.4 RAC环境别忘记用GV$视图我见过不少朋友在RAC环境里查V$SESSION结果发现查不到目标会话其实是查的实例不对。V$开头的视图只反映当前所连接实例的信息RAC下每个实例都有自己独立的V$SESSION。这时候需要用GV$开头的视图或者直接在SQL里把GV_$视图作为数据源。一个简单的替代方法就是把所有V$字样改成GV$后再查询。GV$SESSION会自动多出一个INST_ID列代表实例编号。比如SELECT inst_id, sid, serial#, username, sql_id, sql_exec_start FROM gv$session WHERE type ! BACKGROUND ORDER BY sql_exec_start ASC;在Oracle 12c中GV$视图同样存在普通用户如果被授权访问V_$SESSION默认情况下GV_$SESSION也能访问因为授权通常是成对出现的。如果权限没给全手动授权时记得把两个都加上。5. 常见问题与排查技巧实录5.1 权限不足看不到别的会话典型报错是ORA-00942或者ORA-01031。09开头多半是视图不存在10开头多半是权限不够。解决办法在1.3节已经提过生产环境建议最小化授权。我实际遇到比较多的情况是开发同学只有业务账号想看会话监控又不想要走DBA权限最后我用一个折中方案创建一个只读监控账号把V_$SESSION、V_$SQL等几个必要视图单独授权只开放SELECT权限不给其它系统权限。如果用的是12c多租户架构还要注意容器范围。在PDB里授权时默认只在当前PDB生效在CDB$ROOT里创建公共用户并授权才能跨PDB看到全局数据。不同项目权限策略不一样但记住“容器影响可见范围”这点就够了。5.2 在V$SQL里按文本搜不到SQL最常见的原因是SQL已经被共享池换出尤其是那些执行频率不高、或已经很久没再执行的语句。另一个可能原因是共享池压力大时Oracle会优先换出不常用的子游标。这时候再怎么按V$SQL查都查不到只能去AWR里翻。还有一种情况是SQL文本匹配问题。一条SQL里有多个空格、换行、大小写差异直接用SQL_TEXT LIKE %关键词%搜索会漏掉。遇到这种场景优先用SQL_ID精确匹配或者用UPPER(SQL_TEXT) LIKE过滤并且把空格折叠后再匹配。SELECT sql_id, sql_text FROM v$sql WHERE UPPER(REPLACE(sql_text, , )) LIKE %FROMTABLE1%;这种模糊搜索在数据量大时会很慢所以我还是建议尽量先通过V$SESSION或ASH拿到SQL_ID再去查文本。5.3 SQL_ID显示为PL/SQL块内部SQL找不到执行存储过程时V$SESSION.SQL_ID可能显示为整个PL/SQL块的SQL_ID而不是过程内部某一条DML语句的SQL_ID。这种现象在12c里也有尤其当过程内部通过动态SQL执行语句时更明显。想要拿到过程内部真正在执行的SQL有几种办法。一是用SQL Monitor因为它监控的是实际执行的活动二是通过第三方工具做10046事件跟踪三是从V$SQL里按过程ID过滤子游标但这个方法比较复杂。日常排障我更推荐SQL Monitor直接看当前正在监控哪个SQL_ID再生成报告直观多了。5.4 长SQL显示不全看着像被截断SQL文本本身不太可能会被Oracle截断被截断的一般是客户端工具或SQL*Plus的输出。V$SQL_TEXT列在早期版本里是VARCHAR2类型只存前1000个字符但SQL_FULLTEXT是CLOB能存完整内容。V$SQLTEXT虽然分片但片与片之间通过PIECE字段排序拼起来就是完整的。在SQL*Plus里最常见的问题是没设置LONG导致CLOB显示不出来。设置方法前面已经说了这里再提醒一次SET LONG 2000000和SET LONGCHUNKSIZE 2000000两个都要设置。图形工具里如果发现单元格显示不全通常是界面显示限制把单元格双击打开或导出即可。5.5 查询时把视图当普通表性能差到没法忍V$视图本质上也是由内部X$表聚合出来的在会话数多、SQL统计信息大的时候查询V$SQL、V$SQLAREA本身可能就不慢但如果你反复全表扫描或者做大量LIKE匹配也会拖累数据库。特别是V$SQL这种动辄几万条记录的视图直接在SQL_TEXT上做LIKE %xxx%会把所有文本都扫一遍查询可能都要几秒甚至更久。我的经验是先缩小范围。能指定SID就指定SID能指定SQL_ID就指定SQL_ID能限制时间段就限制时间段。需要反复监控时直接把结果导到一个临时表里再二次过滤比每次实时查V$视图稳妥。5.6 一个很实用的日常习惯最后分享一个我自己的做法周一早上我会把V$SQL里执行次数超过100次的SQL批量导出到一张个人维护的小表记录SQL_ID、SQL_TEXT、EXECUTIONS、ELAPSED_TIME快照和日期。这样下次再有人问“上周哪条SQL跑得最猛”我直接查小表不用再去翻内存可能已经被清掉的V$SQL。这个小习惯帮过我很多次尤其是做日报、周报和故障复盘的时候。别小看这一步把“临时查”变成“定期收”数据库优化才真正有数据支撑而不是每次都在事故发生后靠回忆判断。