一、先把慢翻译成资源语言性能问题的第一性原理其实只有一句话当某种系统资源被耗尽或被过度争用系统就无法继续扩展消除资源冲突系统才能回到业务要求的水位。由此推出调优的两条通用目标——要么减少资源消耗要么缩短单次操作的耗时。这个定义之所以有用是因为它把模糊的慢翻译成了可定位的问题到底是哪个资源被抢爆了从 Oracle 的时间模型看这一点可以被量化。数据库层面的总工作量是DB Time而DB Time ≈ CPU Time Non-Idle Wait Time 平均响应时间 ≈ DB Time / 执行次数也就是说用户感受到的慢在数据库内部只可能来自两处在 CPU 上烧掉的时间或者在某个资源队列里等待的时间。等待时间之所以会出现是因为资源容量小于并发需求请求开始排队——这正是瓶颈的物理含义。所以遇到慢第一步不是猜而是先回答CPU 密集还是等待密集等待又压在哪个资源上。一个两分钟的资源层快扫目的不是确诊而是排除SELECT metric_name, ROUND(value, 1) AS current_val FROM v$sysmetric WHERE metric_name IN (Host CPU Utilization (%), CPU Usage Per Sec, User I/O Wait Time, SQL Service Response Time);判断口径Host CPU Utilization (%)持续高于 85%、User I/O Wait Time频繁尖峰资源瓶颈方向基本坐实。反过来指标都正常但用户还在喊慢——瓶颈大概率不在资源层而在 SQL 或应用层。此时扩容是无效动作加机器解决不了一条 SQL 写得烂的问题。原理小结瓶颈 被过度使用的资源等待 资源排队。定位顺序应是先看时间花在哪CPU vs 等待→ 再看等待压在哪个资源。二、官方三大根因三类病三套处方Oracle 把典型的性能问题成因归为三类。这个分类的真正价值不在于全面而在于三类根因对应三套完全不同的处方归错类后面每一步都白做。2.1 缺少 SQL 访问结构——为什么能提升数个数量级原理CBO基于代价的优化器本身不会创造访问路径它只能在已经存在的访问路径集合里按代价模型挑最便宜的一个。访问结构B*Tree 索引、位图索引、物化视图、分区等决定的是候选路径的集合本身。当一条 SQL 的 WHERE / JOIN 列上没有合适索引时可选路径里就只剩全表扫描或全表扫描 排序优化器再聪明也只能选它一旦补上合适的访问结构复杂度可能从 O(全表行数) 降到 O(命中行数 × log N)这是算法级、数量级的差异而不是参数微调能带来的百分比级差异。所以最优访问结构可把 SQL 性能提升数个数量级这句话本质是改变了问题的计算复杂度。判断标准Top SQL 的 WHERE / JOIN 列无对应索引或大表被反复全表扫描。索引是否真的被用上可以直接监控-- 对怀疑没被用上的索引开启监控 ALTER INDEX schema.idx_name MONITORING USAGE; -- 跑一天业务后看哪些索引根本没被访问过 SELECT index_name, table_name, used FROM sys.v$object_usage WHERE used NO;注该索引使用监控视图在 12c 起已被标记为废弃仅为向后兼容保留生产环境建议改用USER_OBJECT_USAGE/DBA_OBJECT_USAGE系列视图。2.2 优化器统计信息过期——为什么昨天还好今天突然变慢原理CBO 的整个决策链是统计信息 → 基数Cardinality估算 → 选择率 → 代价 → 执行计划。统计信息描述的是表里大概有多少行、列值怎么分布、有多少不同值优化器据此估算每一步会产出多少行、要读多少块。基数估算错了代价就错代价错了就会选错计划。而统计信息是快照不是实时数据。当自动或手动的统计维护跟不上 DML 对数据的变更时快照就失真了——表实际已经膨胀或分布已经倾斜优化器却还在按旧画像估代价。这就是同一条 SQL 昨天还好、今天突然慢最常见的机理。一个实用的过期信号是自上次收集以来表变了多少SELECT table_name, inserts, updates, deletes, truncated, partition_name FROM dba_tab_modifications WHERE table_name YOUR_TABLE ORDER BY (inserts updates deletes) DESC;判断标准增量上万级就应认真评估手动收集。19c 的自动统计任务默认在维护窗口跑但大表没收集完、窗口失败的情况并不少见不能假设它一定成功。延伸原理对数据分布不均匀的列直方图Histogram是关键——没有直方图优化器只能用平均值估算倾斜列上必然估歪。这也是绑定变量窥探Bind Peeking问题的根源11g 之后由自适应游标共享Adaptive Cursor Sharing在一定程度上缓解但它自身也可能带来新的计划管理复杂度。2.3 硬件问题——为什么它表现为全体慢而不是某条 SQL 慢原理内存、I/O、CPU 的物理上限是共享的所有会话共同竞争同一份硬件资源。因此硬件瓶颈的指纹是弥散性的不是某一条 SQL 异常而是所有请求一起变慢并且OS 层指标通常先于数据库指标报警。在数据库内部也能间接观察 OS 的 CPU 忙碌率SELECT ROUND(100 * b.busy / (b.busy i.idle), 1) AS cpu_busy_pct FROM (SELECT value busy FROM v$osstat WHERE stat_name BUSY_TIME) b, (SELECT value idle FROM v$osstat WHERE stat_name IDLE_TIME) i;三大根因的处方分野根因典型指纹处方方向缺访问结构少数 SQL 慢、大表全扫补索引 / 物化视图统计过期同一条 SQL 计划突变收集统计、管理直方图硬件顶不住全体变慢、OS 先报警扩容 / 分流 / 换 I/O 策略最怕归错类硬件顶不住却去建索引统计过期却去换磁盘。三、SQL 层的人为慢优化器救不了的四类结构和统计都正常SQL 还是慢——问题出在 SQL 自身。共同点是SQL 在做不必要的工作而优化器只能在合法计划里挑最便宜的救不了从根上就写错的语句。① 笛卡尔积连接忘记写 join 条件时结果集是两表行数的乘积。优化器不会报错它会老老实实生成MERGE CARTESIAN计划然后资源消耗指数级放大。全库扫这类计划SELECT sql_id, COUNT(*) AS cartesian_steps FROM v$sql_plan WHERE operation MERGE AND options CARTESIAN GROUP BY sql_id ORDER BY cartesian_steps DESC FETCH FIRST 10 ROWS ONLY;② UNION 误用UNION隐含一次SORT UNIQUE去重会带来额外排序、临时空间和 CPU 开销两个分支已天然不重复时UNION ALL才是等价且更省的写法。③ 相关子查询被反复执行子查询对外层查询的每一行执行一次代价随外层行数线性放大本该用一次连接或半连接解决。④ Hint 把大表钉成驱动表强制了错误的连接顺序让优化器无法选择更优计划。第二类游标与共享池使用不当——为什么它对性能有数量级影响这里必须讲清硬解析的原理一条 SQL 提交到 Oracle 后先在库缓存Library Cache里按 SQL 文本的哈希值查找父游标。命中→ 走软解析直接复用已生成的执行计划未命中→ 触发硬解析语法分析 → 语义分析 → 优化器代价估算 → 生成执行计划。全过程要分配内存、频繁访问共享内存区域并持有 library cache latch / shared pool latch。硬解析的昂贵不在于单次耗时而在于它既烧 CPU又占用闩锁、降低并发。在高并发下闩锁争用会把解析变成整个系统的排队点。由此推出两个结论不使用游标→ 反复解析不使用绑定变量→ 每条字面量不同的 SQL 都是不同的文本各自硬解析完全不可扩展。绑定变量为什么能治它WHERE id :b1让不同取值共享同一段 SQL 文本从而共享同一个父游标把大量硬解析降级为软解析。对动态拼字面量 SQL 的应用要保持警惕——此时加大 shared pool 只是延后发作根治要改应用、上绑定变量。第三类坏 SQL——官方定义很直白使用的资源超出了应用需求所需的 SQL。ADDM 能识别谁在烧资源SQL Tuning Advisor 能给出改进建议但它们识别的是高负载 SQL写法本身的问题仍然需要人来看。第四类过度 I/O 的执行计划诱因——SQL 优化不当、缺索引、表并行度过高使优化器倾向扫描、统计不准以及一个容易被忽视的参数DB_FILE_MULTIBLOCK_READ_COUNT。原理该参数控制单次 I/O 能读取的最大块数它直接参与 CBO 对全表扫描的代价估算。全表扫描走多块读参数越大优化器估算出的全表扫描 I/O 代价越低就越倾向选择全表扫描。默认是自动计算手动调大过头等于人为给全表扫描打折反而把优化器带偏。此外多块读不会跨越 extent 边界——extent 大小若不是该参数的整数倍多块读效率会打折。四、应用设计与迁移杠杆最大的两层官方明确指出应用对可扩展性的影响最大。数据库层做得再好应用设计烂性能天花板就是低的。三种典型失效模式糟糕的模式设计→ 产生不可扩展的昂贵 SQL糟糕的事务设计→ 引发锁与串行化问题糟糕的连接管理→ 响应时间差、系统不可靠。物理实现也可能成为短板带着糟糕的 I/O 策略上生产、生产与测试环境执行计划不同、内存密集型应用不假思索地大量分配内存、内存泄漏给虚拟内存子系统施压。几个值得记住的设计红旗表设计复杂到没人能完全理解SQL 长到任何优化器都无法实时有效优化同一列被反复建索引在线用户查询缺少合适限定条件数据库调用被多层软件抽象得太深。还有一类最坑人的假性慢——部署与迁移错误。很多应用资源消耗过高原因仅仅是拥有表的 schema 没有从开发环境或旧版本成功迁移索引缺失、统计信息错误于是执行计划不理想、响应性能差。这类错误 ADDM 不能直接检测但会以高负载 SQL的形式凸显出来。迁移后应当主动核对对象与统计-- 对象清点索引数量和测试环境对得上吗 SELECT object_type, COUNT(*) AS cnt FROM dba_objects WHERE owner APP_SCHEMA GROUP BY object_type ORDER BY cnt DESC; -- 统计核对大面积过期或缺失的表有多少 SELECT COUNT(*) AS stale_or_missing FROM dba_tab_statistics WHERE owner APP_SCHEMA AND (last_analyzed SYSDATE - 7 OR last_analyzed IS NULL);判断标准生产库比测试库少索引、统计大面积过期——先补迁移再谈调优否则是在错误的地基上排查 SQL。官方建议迁移已知性能的应用时用DBMS_STATS导出 schema 统计信息以保持计划稳定。五、两条方法论铁律铁律一症状 ≠ 问题许多性能统计只指示症状不足以直接支撑修复。最典型的例子慢的物理 I/O 通常由磁盘配置不当引起但也可能是调优糟糕的 SQL 在这些磁盘上发出了大量不必要的物理 I/O。同一个症状两种完全不同的处方。原理等待事件反映的是进程卡在哪但卡住的原因可能来自上游SQL 请求量也可能来自下游硬件能力。因此正确顺序是先用等待事件形成瓶颈假设再用其他统计数据交叉验证。-- 第一步负载特征自实例启动累计 SELECT name, value FROM v$sysstat WHERE name IN (physical read total bytes,physical writes total bytes,session logical reads); -- 第二步Top 磁盘读 SQL确认 IO 是否由 SQL 驱动 SELECT sql_id, SUM(disk_reads) AS total_disk_reads FROM v$sql GROUP BY sql_id ORDER BY total_disk_reads DESC FETCH FIRST 5 ROWS ONLY;验证结果分岔物理读总量大且集中在少数 SQL→ 是 SQL 在狂读优化 SQL物理读分散、单条 SQL 都不高→ 磁盘配置或 I/O 策略的嫌疑上升。两步做完处方才开得准。铁律二修复范围必须匹配问题范围原理问题的作用域决定方案的作用域。数据库级的问题要用数据库级方案shared pool 太小导致游标快速老化、引发大量硬解析——用初始化参数加大 shared pool 即可且惠及所有会话而单条 SQL 的问题就该用 SQL 级方案改写、补索引、加 Hint不能用全局参数去陪跑。用错范围的代价拿数据库级参数去治单条 SQL是浪费拿单条 SQL 的改写去治全局闩锁争用是隔靴搔痒。六、小结一张可复用的排查决策图把全文压缩成一条链路慢 → 是资源被争用/耗尽DB Time CPU Wait ↓ 资源层快扫 → 排除还是坐实CPU 高I/O 尖峰还是指标正常 ↓ 三大根因归类 → 缺访问结构 / 统计过期 / 硬件顶不住 ↓ SQL 层自省 → 笛卡尔积UNION相关子查询硬解析/绑定变量多块读参数 ↓ 应用与迁移 → 模式/事务/连接设计索引与统计是否真的迁过来了 ↓ 方法论收口 → 症状≠问题假设交叉验证修复范围问题范围一句话记住红利不在参数里而在 SQL 和应用设计里实例调优通常不到 10%隔离并修复应用低效可达 100%。先假设、再交叉验证先看清范围、再开处方。