做数据库运维的人多少都被这几个词绕晕过processes、job_queue_processes 和物化视图。它们单看都是老熟人可一旦串在一起出问题排查路径就变得相当绕。前阵子又碰到一个现场说好的定时刷新物化视图数据却停在三天前不动了告警日志里刷出一片 ORA-12012值班的哥们第一反应是刷新脚本写错了折腾了大半天才发现根子其实在参数上。这事儿挺典型processes 是实例能承载的进程总数job_queue_processes 决定有多少工人去跑作业而物化视图的自动刷新恰恰就是把活派给作业队列去干的。三者是一条链上蚂蚱任何一环收紧最后都会让物化视图数据变陈旧。这篇就按我自己的排查习惯把这条链从头到尾拆一遍给做运维、数据仓库、报表开发的朋友一份能直接抄作业的参考。1. 三个参数为什么总被绑在一起1.1 一个典型的物化视图停摆现场场景大概是这样的某业务库有一批物化视图配置成每天早上六点自动刷新给下游报表用。某天开始报表数据连续几天没更新。登进去看 DBA_MVIEWSLAST_REFRESH_DATE 停在某个时间点再也不往前走了STALENESS 那一列显示成 STALE 甚至 UNUSABLE。第一反应是刷新脚本有问题去翻 DBA_JOBS 和 DBA_SCHEDULER_JOBS发现作业本身好好的NEXT_DATE 一直在往后滚可 LAST_DATE 就是不动FAILURES 也未必往上涨。这时候去看告警日志和作业运行明细往往能看到一类熟悉的味道ORA-12012后面跟着一句 error on auto execute of job。意思是系统想自动执行这个作业但没执行成功。再往深挖可能是作业根本没被调度起来也可能是调度起来之后执行中途被打断。前者通常指向 job_queue_processes 太小或者干脆是 0后者常常指向 processes 这类实例级上限被顶满导致作业进程起不来。我见过最隐蔽的一种情况是 job_queue_processes 设得挺大processes 也看着够用但库上跑了一大堆并行查询PX 进程把 processes 的额度吃光了物化视图刷新的作业排到队尾迟迟没进程可用。表面上参数都对实际就是没资源。所以这三个东西不能分开看必须放进同一张资源账本里算。1.2 把依赖链条拆开看很多人对物化视图自动刷新有个误解以为数据库内部有个专门的管家定时去刷。实际不是。Oracle 里物化视图的自动刷新本质上是把一个刷新动作包装成一个数据库作业交给作业调度系统去执行。链条大致是这样的第一层是 processes。它定义整个数据库实例最多能同时存在多少个操作系统进程包括后台进程、服务器进程、并行执行进程以及作业进程。第二层是 job_queue_processes。它决定实例里同时能有多少个作业执行进程老版本是 Jnnn12.2 之后底层换成作业从属进程。作业调度系统拿到待执行的作业就得从这个池子里分配进程去干活。第三层才是物化视图。刷新动作被注册成作业排队等着被作业进程认领执行。用一句话概括processes 是工厂的总用电额度job_queue_processes 是这条产线上的工人编制物化视图刷新是从产线上走下来的产品。总用电不够工人再多也开不了机工人编制为零产品永远出不来工人够用但被别的产线抢了电也得排队。把这条链条记住后面排查就有方向感了物化视图数据不动先看作业有没有在跑再看作业主力job_queue_processes够不够、开没开最后看整机资源processes有没有被吃满。三步走下来八九不离十。提示物化视图的自动刷新依赖数据库作业系统这一点是理解所有相关问题的钥匙。凡是作业系统被限制或资源不足物化视图的自动就会变成不动。2. processes 参数实例进程额度的总闸2.1 它到底在管什么PROCESSES 是实例级静态参数定义的是这个 Oracle 实例允许同时存在的操作系统进程上限。注意这里的操作对象是进程不是会话。一个会话通常对应一个服务器进程但进程的消耗远不止会话那一份。具体来说下面这些都要从 PROCESSES 的额度里扣后台进程。比如 PMON、SMON、DBWn、LGWR、CKPT、ARCn、MMON、MMNL 等等一个正常的单实例库后台进程加起来十几到几十个不等RAC 环境更多。服务器进程。每个用户连接对应一个服务器进程这是消耗的大头。并行执行进程。PX 进程跑并行查询、并行 DML、并行建索引时大量产生。作业进程。这就是 job_queue_processes 对应的那部分。很多人以为 PROCESSES 只影响连接数其实并行查询和作业进程都算在里面。这就是为什么连接数没超怎么还报进程不够的情况经常发生——额度被 PX 或者作业进程悄悄吃掉了。理解这一点是算准 PROCESSES 的前提。2.2 默认值与容量推算不同版本 PROCESSES 的默认值不太一样SESSIONS 默认值又是根据 PROCESSES 推导出来的公式是 SESSIONS (1.1 × PROCESSES) 5。| 版本 | 默认 PROCESSES | 推导默认 SESSIONS | | Oracle 9i / 10g / 11g | 150 | 170 | | Oracle 12c / 18c / 19c | 300 | 335 |默认值对小型库够用但一旦上连接池、跑并行、还有一堆物化视图作业150 这种默认值很快就见底。我在估算时习惯用下面这个思路而不是拍脑袋需要设置的 PROCESSES ≥ 后台进程数 峰值并发服务器进程数 峰值并行进程数 作业进程数 预留缓冲。举个例子某报表库后台进程约 40 个业务峰值连接 300偶尔跑并行取数峰值并行进程约 60物化视图作业进程配置 job_queue_processes20。那么 40 300 60 20 420再加 10% 到 20% 缓冲PROCESSES 至少按 500 来配而不是贴着 420 配。为什么不贴着配因为进程申请是动态的几个大查询同时并发峰值会瞬间冲高贴着配必翻车。注意PROCESSES 是静态参数改完要重启实例才生效。这一点跟后面要讲的 job_queue_processes 完全相反一个要重启一个能在线改别搞混。2.3 改大之后不能只看数据库调大 PROCESSES 只解决了数据库这一头操作系统那一头没跟上一样白搭。改完之后至少要同步检查几件事操作系统对 oracle 用户的进程数限制。如果用的是系统级 ulimitnproc 或 max user processes 这道坎没过数据库说能开 500 个进程系统只让开 300照样报错。内存。每个服务器进程都要占 PGA进程数上去PGA 总占用会跟着涨要确认没有触及物理内存红线。如果是容器或虚拟化环境还要确认宿主机层面的进程上限。我踩过一次坑就是把 PROCESSES 从 300 调到 800重启后看着一切正常结果业务高峰期反而更容易报进程相关的错误。排查发现是操作系统的用户级进程数没放开等于数据库和系统在互相打架。所以每次调这个参数我都写成固定动作数据库参数、操作系统限制、内存余量三样一起过一遍。3. job_queue_processes作业调度的工人编制3.1 作业队列进程是干什么的JOB_QUEUE_PROCESSES 控制实例中同时可以有多少个作业执行进程。所有通过 DBMS_JOB旧接口和 DBMS_SCHEDULER新接口提交的作业最终都要靠这些进程去执行。物化视图的自动刷新、统计信息的定时收集、各种自定义的定时批处理全都在这个池子里排队。这个参数最要命的一点是它允许设成 0。一旦设成 0作业调度基本等于停摆所有依赖作业系统的自动化动作全部卡住。物化视图不会自动刷新定时统计信息不会收集自定义批处理作业也不会执行。偏偏这个参数在有些环境里会被顺手调成 0比如为了临时降低系统负载或者某些模板化的初始化脚本里就写死了 0事后没人改回来。我处理过的物化视图停摆问题里有小一半都能追到这一条。3.2 12c 前后这个参数的变化job_queue_processes 的默认值和底层实现在 12c 前后有明显差异这点值得单独拿出来说。| 版本区间 | 默认值 | 取值范围 | 底层实现说明 | | 9i / 10g / 11g | 10 | 0 - 1000 | 作业队列进程 Jnnn直接受该参数控制 | | 12.1 | 1000 | 0 - 1000 | 仍以作业队列进程为主 | | 12.2 及以后 | 1000 | 0 - 1000兼容保留 | 底层切换为作业从属进程参数主要为兼容旧行为 |11g 时代默认只有 10 个作业进程如果库上挂了几十个物化视图刷新作业加上统计信息收集作业就开始排队。排队的后果不一定报错而是该刷的没按时刷从表面看就是数据陈旧。到了 12c默认值抬到 1000宽松了很多但 12.2 之后底层换成了作业从属进程进程命名也从 Jnnn 变成了另一套体系排查时看 V$PROCESS 里的进程名会和老版本对不上这点初次遇到容易懵。需要强调的是即便在 12.2 之后JOB_QUEUE_PROCESSES 设成 0 仍然会阻断作业执行。所以新版不用管这个参数是错的它依然是个开关。3.3 这个值到底设多少合适给一个我常用的判断逻辑绝对不能是 0。生产库上把这个参数设成 0等于主动关掉所有自动化作业除非有明确的临时目的并记得改回来。先数作业量。用查询统计一下当前库上活跃的作业数量尤其是物化视图刷新、统计信息收集这类固定消耗。按并发作业峰值来配而不是按作业总数来配。多数作业是错峰执行的不需要每个作业配一个进程。留出冗余。一般我会按刷新高峰时段内可能同时触发的作业数 × 1.5来估。| 场景 | 建议 job_queue_processes | | 小型库作业很少 | 保持默认或 10 - 20 | | 有中量物化视图定时刷新 | 20 - 50 | | 大量物化视图、多个刷新组集中刷新 | 50 - 100 甚至更高 | | 明确要临时停掉所有作业 | 0必须记录并尽快恢复 |这个参数是动态的可以在线调整改完立即生效不用重启-- 查看当前值 SHOW PARAMETER job_queue_processes; -- 在线调整立即生效并写入参数文件 ALTER SYSTEM SET job_queue_processes 50 SCOPE BOTH;提示调大这个参数时别忘了同步核对 PROCESSES。作业进程也占 PROCESSES 额度如果 JOB_QUEUE_PROCESSES 从 10 一口气提到 100而 PROCESSES 没留出这 90 的余量要么新作业进程起不来要么挤占服务器进程两头不讨好。4. 物化视图刷新机制拆解4.1 物化视图的自动从哪来先把概念捋清楚。物化视图是把查询结果物理存下来的一张表比普通视图多的就是数据落盘这件事带来的性能收益。代价是数据会过期得靠刷新机制把它跟基表对齐。刷新有两大触发方式ON COMMIT。基表一提交物化视图就跟着更新。这种方式要求基表上建了物化视图日志而且刷新方式必须是 FAST否则会直接报错。它的好处是数据几乎实时坏处是给基表的每一次提交都增加了额外开销。ON DEMAND。不跟着基表走靠手动或者定时来刷新。定时刷新从使用者角度看是自动的但底层就是把刷新动作注册成一个作业交给作业调度系统。关键就在 ON DEMAND 的定时刷新。它不是数据库里有个内建的定时器直接触发而是通过作业系统排队执行。也就是说只要作业系统出问题ON DEMAND 的自动刷新就会停。ON COMMIT 因为走的是提交时的同步动作一般不依赖作业进程所以受影响程度小一些但也不是完全无关某些场景下维护动作仍会用到调度资源。4.2 刷新方式的选择与代价物化视图的刷新方式有 FAST、COMPLETE、FORCE 三种刷新方法又有 ON COMMIT 和 ON DEMAND组合起来决定行为。| 刷新方式 | 含义 | 前提条件 | 代价 | | FAST | 只把基表的变化增量同步过来 | 基表要有物化视图日志 | 低但依赖日志 | | COMPLETE | 清空后重新算一遍 | 无特殊前提 | 高尤其是大表 | | FORCE | 优先 FAST不行就 COMPLETE | 视情况 | 不确定取决于能否走 FAST |我个人的习惯是能 FAST 就 FAST实在不行再用 COMPLETE极少用 FORCE。原因是 FORCE 的行为不可预测平时都走 FAST 很快某天日志出点什么问题它偷偷切成 COMPLETE一个大表全量刷新直接把资源吃满这种平时没事、偶发雪崩最难排查。宁可让 FAST 明确失败报错也不要让 FORCE 悄悄降级。创建物化视图时的典型写法CREATE MATERIALIZED VIEW mv_sales_agg BUILD IMMEDIATE REFRESH FAST ON DEMAND START WITH SYSDATE NEXT SYSDATE 1 AS SELECT dept_id, SUM(amount) total_amount FROM sales GROUP BY dept_id;其中 START WITH 和 NEXT 决定了它按什么节奏自动刷新这个节奏最终落地成作业。4.3 刷新组和批量管理当一个系统里物化视图数量多了之后逐个管理很累这时会用到刷新组。DBMS_REFRESH 可以把多个物化视图塞进一个组统一按一个节奏刷新这样可以控制并发、减少作业数量。-- 创建一个刷新组每小时刷新一次 BEGIN DBMS_REFRESH.MAKE( name refresh_group_1, list , next_date SYSDATE, interval SYSDATE 1/24 ); END; /刷新组的好处是把一堆刷新动作合并到少量作业里减轻作业系统的压力。但要注意刷新组本身也是作业一样依赖 job_queue_processes 和 processes。组内物化视图一次性刷新如果都走 COMPLETE资源峰值会很高我一般会错峰或者给组内刷新加入顺序和分批。4.4 怎么查看刷新状态和作业排查这类问题时下面几条查询基本是标配建议收进自己的工具箱。-- 看物化视图的刷新状态和最后刷新时间 SELECT owner, mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date, staleness FROM dba_mviews ORDER BY last_refresh_date; -- 看物化视图的刷新时间明细 SELECT owner, mview_name, last_refresh_date, staleness FROM dba_mview_refresh_times; -- 看旧接口作业的状态 SELECT job, schema_user, last_date, next_date, broken, failures FROM dba_jobs; -- 看新接口调度作业的状态 SELECT owner, job_name, enabled, state, last_start_date, next_run_date FROM dba_scheduler_jobs; -- 看作业最近一次运行的错误信息 SELECT job_name, status, error#, actual_start_date, additional_info FROM dba_scheduler_job_run_details ORDER BY actual_start_date DESC;物化视图的 STALENESS 列特别有用FRESH 表示和基表对齐STALE 表示已经过期UNUSABLE 表示刷新过程中出了问题、增量刷新已不可用。看到 UNUSABLE基本意味着 FAST 走不通了得先解决物化视图日志的问题。5. 实战一次物化视图集体停摆的排查5.1 现象描述再回到开头那个现场把它完整走一遍。现象是一批物化视图刷新时间统一停在一个时间点往后不再更新下游报表数据陈旧作业列表里 NEXT_DATE 一直在滚但 LAST_DATE 不动告警日志里能看到 ORA-12012。这种集体停摆比单个物化视图停更有指向性。单个停可能是物化视图自身或日志问题集体停大概率是公共资源的问题——作业系统、进程额度、或者作业并发被限制。排查要从这个判断出发先看公共资源。5.2 排查路径我一般按这个顺序走第一确认作业系统是不是活着。SHOW PARAMETER job_queue_processes;如果返回 0基本当场破案。不是 0 就继续往下。第二看作业进程实际有没有起来。SELECT name, description, paddr FROM v$bgprocess WHERE name LIKE J% OR name LIKE S%; SELECT COUNT(*) FROM dba_scheduler_running_jobs;老版本看 Jnnn 进程新版本作业从属进程可能以别的名字出现。如果参数值非 0但进程数量远小于参数值说明进程起不来要怀疑 processes 额度。第三核对 processes 的实际占用。SHOW PARAMETER processes; SELECT COUNT(*) FROM v$process; SELECT program, COUNT(*) FROM v$process GROUP BY program ORDER BY COUNT(*) DESC;把 v$process 里的进程按 program 分组一眼就能看出额度被谁占了。如果看到大量并行进程就知道是被 PX 吃掉了如果服务器进程接近上限就是连接数问题。第四看作业的运行明细和错误。SELECT job_name, status, error#, additional_info FROM dba_scheduler_job_run_details WHERE status FAILED ORDER BY actual_start_date DESC FETCH FIRST 20 ROWS ONLY;结合起来看就能判断是没被调度起来还是调度起来后失败。5.3 根因与修复那次现场的结论是作业系统参数没设成 0作业进程也能起来一部分但 processes 被业务的并行取数查询占满导致物化视图刷新作业排到队尾一直抢不到进程时间一长越堆越多一部分作业因为长时间没执行而在日志里报出 ORA-12012。修复动作分三步短期先把 PROCESSES 调大并同步放开操作系统限制让堆积的作业能跑起来中期把业务的并行度收一收避免少数大查询独占额度长期把物化视图刷新作业和业务高峰错开同时给关键刷新作业设置合理的优先级。修复后观察 DBA_MVIEWS 的 LAST_REFRESH_DATE 不再停滞报表数据恢复。整个过程最费时间的其实不是修复而是定位——因为一开始没人把这三个参数串起来想才会在单个物化视图上反复绕圈。注意排查这类问题最忌讳盯着单个物化视图死磕。只要出现一批物化视图同时停就该立刻转向公共资源排查作业参数、进程额度、并行占用一项项过。6. 参数配置与避坑清单6.1 常见问题速查把踩过的坑整理成一张表遇到类似现象可以直接对号。| 现象 | 可能原因 | 排查方向 | 处理建议 | | 物化视图数据陈旧LAST_REFRESH_DATE 不动 | job_queue_processes 为 0 | 查参数值 | 改为非 0 并核对 processes | | 一批物化视图同时停摆 | processes 额度被占满 | 查 v$process 分组 | 调大 processes 并收并行 | | 作业 NEXT_DATE 滚动但 LAST_DATE 不动 | 作业一直没被调度执行 | 查作业进程数 | 检查 job_queue_processes 和资源 | | 告警日志出现 ORA-12012 | 作业自动执行失败 | 查作业运行明细 | 按错误号具体分析 | | 报错进程数达到上限 | processes 或系统限制过小 | 查参数和 OS 限制 | 两边同步放开 | | 定时刷新偶发变慢或雪崩 | FORCE 刷新降级为 COMPLETE | 看刷新类型 | 改用明确 FAST 并监控日志 | | 刷新报物化视图日志问题 | 日志缺失或失效 | 查基表 mlog | 重建物化视图日志 |6.2 我自己总结的几条经验第一条processes 一定要留冗余。别贴着计算出来的人数去配宁可多留 20%。进程申请是动态的峰值说来就来贴着配迟早要还。第二条动 processes 之前先看操作系统。数据库参数和 OS 限制是两道闸只开一道等于没开。我见过太多参数改了没用的情况根子都在 OS 那一层。第三条job_queue_processes 永远不要设 0 当临时降载手段。真要降载去限制具体作业的执行时间或者频率别一刀切把整个作业系统关了。这个参数一旦忘了改回来物化视图、统计信息、所有批处理全停损失远大于那点临时负载。第四条能用 FAST 就别用 FORCE。FORCE 的自动降级行为很隐蔽平时看不出来出事就是大事。宁可让 FAST 明确报错你至少知道有问题。第五条物化视图刷新要有错峰意识。把所有物化视图的 NEXT 时间都设成同一个整点等于人为制造一个资源峰值。按业务重要性错开或者用刷新组分批刷压力会平缓很多。第六条监控里加上物化视图的 staleness。别等业务来投诉才发现数据陈旧。搞个定时任务每天扫一遍 DBA_MVIEWS凡是 STALE 超过预期时间的就发提醒把问题消灭在投诉之前。最后再分享一个我常用来快速定位的小技巧当不确定问题出在作业系统还是单个物化视图时先手动执行一次刷新看看。BEGIN DBMS_MVIEW.REFRESH(MV_SALES_AGG, C); END; /如果手动刷新能成功说明物化视图本身和基表、日志都没毛病问题十有八九在自动调度的作业环节如果手动刷新也报错那就顺着错误号去查物化视图和基表本身。这一招能帮你快速把问题范围缩小一半省下大量来回折腾的时间。根据我这些年的实际使用排查效率提升最明显的往往不是多复杂的工具而是这种把问题一刀切两半的土办法。