性能优化手册。

问题现象

业务查询条件对索引列套了函数，走全表扫描，10 万行表查询从毫秒级变成数秒。

业务场景

CRM 系统中客户表 t_customer(cust_id, cust_name, id_card, create_time)，运营需要按客户姓名模糊查找。开发写成 UPPER(cust_name) LIKE 'ZHANG%' 以忽略大小写，上线后列表页加载超过 4 秒。

SELECT * FROM t_customer WHERE UPPER(cust_name) LIKE 'ZHANG%';

-- 方案一：建函数索引（推荐，业务写法不变）
CREATE INDEX idx_cust_name_up ON t_customer(UPPER(cust_name));

-- 方案二：数据入库时统一清洗为小写，查询直接走普通索引
SELECT * FROM t_customer WHERE cust_name LIKE 'zhang%';

-- 确认是否走索引
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 反复执行后看真实统计
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 查看是否存在可用函数索引
SELECT index_name, column_expression FROM user_ind_expressions;
-- 索引类型可另查 USER_INDEXES.INDEX_TYPE

问题现象

字段是 VARCHAR2，传参传了数字；字段是 DATE，传参传了字符串，索引静默失效。

业务场景

订单表 t_order(order_id VARCHAR2(32) PRIMARY KEY, cust_id VARCHAR2(20), amount NUMBER)。前端下单接口传入的 cust_id 被框架反序列化成数字 1000001，SQL 里绑成了 NUMBER 类型。查该客户订单时全表扫描。

-- cust_id 是 VARCHAR2，传入 NUMBER → Oracle 对列做 TO_NUMBER 转换
SELECT * FROM t_order WHERE cust_id = 1000001;

-- 另一种：绑定变量类型不匹配
-- JDBC 中 setInt(1, 1000001) 而列是 VARCHAR2

-- 保证参数类型与列类型一致
SELECT * FROM t_order WHERE cust_id = '1000001';

-- JDBC 中显式使用 setString
-- ps.setString(1, "1000001");
-- 或由框架统一对齐参数类型

-- V$SQL_BIND_CAPTURE 查看绑定变量的真实类型
SELECT sql_id, name, datatype_string, value_string
FROM v$sql_bind_capture WHERE sql_id = '&sql_id';

-- 执行计划中若出现 INTERNAL_FUNCTION 包住索引列，即为隐式转换
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

问题现象

建了复合索引却用不上，或只用了前导列导致过滤不充分。

业务场景

物流系统轨迹表 t_track(track_id, waybill_no, scan_time, city_code, status)，业务有两个典型查询：①按运单号+扫描时间查轨迹；②按城市+状态查异常件。开发建了一个索引 idx_a(waybill_no, scan_time, city_code, status)。查询②完全用不上。

-- 查询②：前导列 waybill_no 未参与条件，索引无法定位
SELECT * FROM t_track WHERE city_code = '310100' AND status = 'EXCEPTION';
-- 结果：INDEX SKIP SCAN 或 FULL TABLE SCAN

-- 按真实访问路径分别建索引，前导列选区分度高且必用的列
CREATE INDEX idx_track_waybill ON t_track(waybill_no, scan_time);   -- 查询①
CREATE INDEX idx_track_city_status ON t_track(city_code, status, scan_time); -- 查询②

-- 若查询②还带时间范围，把时间放最后做范围扫描
SELECT * FROM t_track
 WHERE city_code = '310100' AND status = 'EXCEPTION'
   AND scan_time >= SYSDATE - 1;

-- 检查索引前导列与选择性
SELECT index_name, column_position, column_name FROM user_ind_columns
WHERE table_name='T_TRACK' ORDER BY index_name, column_position;

-- 查看列的选择性（越接近 1 越适合做前导列）
SELECT COUNT(DISTINCT city_code)/COUNT(*) sel_city, COUNT(DISTINCT waybill_no)/COUNT(*) sel_wb
FROM t_track;

问题现象

在状态、性别、是否删除这类列上建索引，优化器根本不用，反而拖慢 DML。

业务场景

工单表 t_ticket(ticket_id, status, is_deleted, create_time)。status 取值只有 5 种（待处理/处理中/已完成/已关闭/已取消），且 90% 的数据是「已完成」。开发给 status 单独建了索引，结果查询仍然全表扫描，同时插入性能下降。

CREATE INDEX idx_ticket_status ON t_ticket(status);
-- 查询仍走全表扫描，且每次 INSERT/UPDATE 都要维护这个无用索引

-- 方案一：组合索引，把低区分度列作为后续列
CREATE INDEX idx_ticket_dept_status ON t_ticket(dept_id, status, create_time);

-- 方案二：若只关心少量「活跃」数据，建函数索引（部分索引效果）
CREATE INDEX idx_ticket_active ON t_ticket(CASE WHEN status IN ('待处理','处理中') THEN status END);
SELECT * FROM t_ticket
 WHERE CASE WHEN status IN ('待处理','处理中') THEN status END IS NOT NULL;

-- 方案三：数据量不大就别建，直接全表扫描更快

-- 找出从未被使用过的索引
SELECT index_name, table_name FROM v$object_usage;  -- 需先 ALTER INDEX ... MONITORING USAGE

-- 或从游标缓存反查索引使用情况
SELECT * FROM v$sql_plan WHERE object_name = 'IDX_TICKET_STATUS' AND object_type='INDEX';

问题现象

一个表挂了十几个索引，批量导入时慢得离谱，单条 INSERT 也要几十毫秒。

业务场景

数据中台宽表 dw_order_detail 有 42 个字段，历史上不同需求方各自加索引，累计 14 个索引。每日凌晨跑 200 万行批量导入，耗时从 8 分钟涨到 55 分钟，导致下游 ETL 全部延迟。

-- 历史遗留，无人清理
CREATE INDEX idx1 ...; CREATE INDEX idx2 ...; ... CREATE INDEX idx14 ...;
-- 每个索引都要在导入时更新 B 树、生成 redo/undo

-- 1) 开启监控，观察一个业务周期
ALTER INDEX idx_dw_01 MONITORING USAGE;
-- 2) 一个周期后查看使用情况
SELECT index_name, used, start_monitoring, end_monitoring FROM v$object_usage;
-- 3) 确认无用后逐个清理
DROP INDEX idx_dw_09;
-- 4) 批量导入前后可临时设为不可用再重建（谨慎评估）
-- ALTER INDEX idx_dw_10 UNUSABLE;  -- 导入后 REBUILD

SELECT table_name, COUNT(*) idx_cnt FROM user_indexes
GROUP BY table_name HAVING COUNT(*) > 8 ORDER BY 2 DESC;

-- 索引占用空间排名
SELECT segment_name, ROUND(bytes/1024/1024,1) mb FROM user_segments
WHERE segment_type='INDEX' ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;

问题现象

ORDER BY 仍然产生 SORT ORDER BY，排序成为瓶颈。

业务场景

消息中心 t_message(msg_id, user_id, create_time, content)，列表页按用户查询最近消息：WHERE user_id=? ORDER BY create_time DESC。索引建的是 idx_user(create_time, user_id)，排序无法消除。

CREATE INDEX idx_msg_a ON t_message(create_time, user_id);
SELECT * FROM t_message WHERE user_id = 1001 ORDER BY create_time DESC;
-- 执行计划出现 SORT ORDER BY，大量临时表空间 I/O

-- 把「等值条件列」放前，「排序列」紧跟其后，即可消除排序
CREATE INDEX idx_msg_b ON t_message(user_id, create_time DESC);
SELECT * FROM t_message WHERE user_id = 1001 ORDER BY create_time DESC;
-- 执行计划变为 INDEX RANGE SCAN DESCENDING，无 SORT

-- 确认计划中无 SORT ORDER BY，且是 INDEX RANGE SCAN DESCENDING
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 查看排序相关统计
SELECT name, value FROM v$sysstat WHERE name LIKE 'sorts%';

问题现象

索引被误判为「碎片严重」而频繁重建，白天重建导致业务抖动。

业务场景

运维手册要求每月重建所有索引。某次白天对 8000 万行的 t_order 执行 ALTER INDEX REBUILD，锁等待导致线上订单创建接口大面积超时。

-- 盲目重建，且未评估是否真的需要
ALTER INDEX idx_order_cust REBUILD;
-- 高峰期执行，且 ONLINE 未加，阻塞 DML

-- 1) 先判断 B 树是否真的失衡（BLEVEL 与碎片率）
SELECT index_name, blevel, leaf_blocks, num_rows, distinct_keys,
       ROUND((del_lf_rows/decode(lf_rows,0,1,lf_rows))*100,2) del_pct
FROM index_stats;  -- 需先 ANALYZE INDEX idx_order_cust VALIDATE STRUCTURE;

-- 2) 确需重建时，加 ONLINE 避免锁表，并放在低峰窗口
ALTER INDEX idx_order_cust REBUILD ONLINE;

-- 3) 或改用 coalesce 收缩碎片（更轻量）
ALTER INDEX idx_order_cust COALESCE;

ANALYZE INDEX idx_order_cust VALIDATE STRUCTURE;
SELECT name, height, blocks, lf_blks, del_lf_rows, lf_rows FROM index_stats;
-- 也可见 v$segment_statistics 观察逻辑读变化趋势

问题现象

SQL 只查几个字段，却因回表产生大量随机 I/O。

业务场景

报表按商品统计销量，SQL 只用到 sku_id 与 qty：SELECT sku_id, SUM(qty) FROM t_order_item WHERE sku_id IN (...) GROUP BY sku_id。索引 idx_sku(sku_id) 命中后每行都要回表取 qty，随机读很多。

CREATE INDEX idx_item_sku ON t_order_item(sku_id);
SELECT sku_id, SUM(qty) FROM t_order_item WHERE sku_id = 'S001' GROUP BY sku_id;
-- 回表取 qty，随机 I/O 大

-- 把查询涉及的列全部放进索引，形成覆盖索引，无需回表
CREATE INDEX idx_item_sku_qty ON t_order_item(sku_id, qty);
SELECT sku_id, SUM(qty) FROM t_order_item WHERE sku_id = 'S001' GROUP BY sku_id;
-- 计划显示 INDEX RANGE SCAN，无 TABLE ACCESS BY INDEX ROWID

-- 计划中若没有 TABLE ACCESS BY INDEX ROWID，即为覆盖索引生效
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 观察 consistent gets 是否显著下降

问题现象

主表删除或更新主键时，子表因缺少外键索引而长时间锁表。

业务场景

订单表 t_order 与订单明细 t_order_item 通过 order_id 关联，且有外键约束。清理历史订单时若子表 order_id 无索引，Oracle 可能扫描子表并对其加表级锁，从而影响明细表上的并发 DML。

-- 子表无外键索引，删除父行时需全表扫描子表校验外键
ALTER TABLE t_order_item ADD CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES t_order(order_id);
-- order_id 上没有索引

-- 子表外键列建立索引
CREATE INDEX idx_item_order ON t_order_item(order_id);

-- 大批量删除改为分区级操作更稳妥
ALTER TABLE t_order DROP PARTITION p_2024q1 UPDATE GLOBAL INDEXES;

-- 找出缺失索引的外键
SELECT c.table_name, c.constraint_name, cc.column_name
FROM user_constraints c JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
WHERE c.constraint_type = 'R'
  AND NOT EXISTS (SELECT 1 FROM user_ind_columns i
                  WHERE i.table_name = cc.table_name AND i.column_name = cc.column_name);

问题现象

只用一个字段却查出全部大字段，网络传输与内存开销巨大。

业务场景

商品详情表 t_product(product_id, name, price, detail_clob, spec_json, image_blob, ...) 其中 detail_clob 平均 200KB。列表页只需商品名与价格，开发写了 SELECT * FROM t_product WHERE category_id = ?，一次返回 50 条，单次响应体超过 10MB。

SELECT * FROM t_product WHERE category_id = 'C001' AND rownum <= 50;

SELECT product_id, name, price
  FROM t_product
 WHERE category_id = 'C001'
   AND rownum <= 50;

-- 如确实需要大字段，单独接口按需加载

-- 查看 SQL 级 I/O 与返回行数
SELECT sql_id, executions, rows_processed, buffer_gets, disk_reads, elapsed_time/1000 ms
FROM v$sql WHERE sql_text LIKE '%t_product%' ORDER BY elapsed_time DESC;
-- 客户端侧观察响应体大小

问题现象

共享池被大量相似 SQL 撑爆，CPU 高企，出现 library cache 争用。

业务场景

Java 服务查询订单，SQL 由字符串拼接产生：WHERE order_id = 'A001'、WHERE order_id = 'A002'… 每天产生数十万条仅字面量不同的 SQL，共享池内存持续增长直至 ORA-04031。

-- Java 中字符串拼接（伪代码）
String sql = "SELECT * FROM t_order WHERE order_id = '" + id + "'";
-- 每条 SQL 都是一次硬解析，共享池被无数相似语句占满

-- 使用绑定变量
SELECT * FROM t_order WHERE order_id = ?;
-- JDBC
PreparedStatement ps = conn.prepareStatement("SELECT * FROM t_order WHERE order_id = ?");
ps.setString(1, id);

-- 动态统计信息场景可谨慎使用自适应游标共享（ACS）
-- 谨慎使用字面量：CURSOR_SHARING=FORCE 仅作应急，长期不推荐

-- 解析比例：软解析/执行 应接近 1
SELECT name, value FROM v$sysstat WHERE name IN
 ('parse count (total)','parse count (hard)','execute count','session cursor cache hits');

-- 找出字面量 SQL 导致的重复
SELECT sql_text, COUNT(*) FROM v$sql
WHERE executions = 1 GROUP BY sql_text HAVING COUNT(*) > 50;

-- 强制游标共享（应急）
ALTER SYSTEM SET cursor_sharing = FORCE;

问题现象

NOT IN 遇 NULL 返回空结果或无法用反连接，大表关联极慢。

业务场景

风控系统需要找出「从未下过单的客户」：客户表 t_customer 200 万行，订单表 t_order 8000 万行，t_order.cust_id 允许为 NULL（历史脏数据）。开发写 NOT IN，结果返回 0 行且执行 40 分钟。

-- 1) 语义错误：子查询含 NULL 时 NOT IN 恒为 UNKNOWN，结果为空
SELECT * FROM t_customer
 WHERE cust_id NOT IN (SELECT cust_id FROM t_order);

-- 2) 即使无 NULL，NOT IN 也常无法走 HASH ANTI JOIN，性能差

-- 用 NOT EXISTS（可走 HASH ANTI JOIN）
SELECT c.* FROM t_customer c
 WHERE NOT EXISTS (SELECT 1 FROM t_order o WHERE o.cust_id = c.cust_id);

-- 若确需 NOT IN，先排除 NULL
SELECT * FROM t_customer
 WHERE cust_id NOT IN (SELECT cust_id FROM t_order WHERE cust_id IS NOT NULL);

-- 存在性判断优先 EXISTS（找到即停，可走 SEMI JOIN）
SELECT c.* FROM t_customer c
 WHERE EXISTS (SELECT 1 FROM t_order o WHERE o.cust_id = c.cust_id);

-- 确认是否走了 (HASH) ANTI JOIN / SEMI JOIN
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 计划中应出现 HASH JOIN ANTI 或 HASH JOIN SEMI

问题现象

WHERE 中大量 OR 导致索引无法使用，退化为全表扫描。

业务场景

客服工单列表需要同时展示「我负责的」和「我参与协作的」：ticket(owner_id, collaborator_id, status, create_time)。开发写 WHERE owner_id = ? OR collaborator_id = ?，两个列各有索引但都用不上。

SELECT * FROM t_ticket
 WHERE owner_id = 1001 OR collaborator_id = 1001
   AND status = 'OPEN';

-- 方案一：UNION ALL 拆分，各自走索引（注意 OR 与 AND 的优先级需加括号）
SELECT * FROM t_ticket WHERE owner_id = 1001 AND status='OPEN'
UNION ALL
SELECT * FROM t_ticket WHERE collaborator_id = 1001 AND status='OPEN'
  AND owner_id <> 1001;  -- 去重避免重复行

-- 方案二：同一列的多个等值条件直接改 IN
SELECT * FROM t_ticket WHERE status IN ('OPEN','PROCESSING');

-- 方案三：建组合索引让 OR 转成 INDEX JOIN / CONCATENATION
CREATE INDEX idx_ticket_owner_st ON t_ticket(owner_id, status);
CREATE INDEX idx_ticket_collab_st ON t_ticket(collaborator_id, status);

-- 观察是否发生 OR 展开（CONCATENATION）
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 也可用提示强制展开：/*+ USE_CONCAT */

问题现象

WHERE create_time + 1 > SYSDATE 这类写法让索引失效。

业务场景

对账系统筛选近 7 天数据，开发写成 WHERE create_time + 30 >= SYSDATE（时间字段以天为单位加 30），索引完全用不上，全表扫描 5000 万行。

SELECT * FROM t_settle WHERE create_time + 30 >= SYSDATE;
-- 等价于对列做算术运算，索引失效

-- 把运算移到等号右边，保持列裸用
SELECT * FROM t_settle WHERE create_time >= SYSDATE - 30;

-- 日期做减法对比时同理
-- 差：WHERE TRUNC(create_time) = TRUNC(SYSDATE)
-- 好：WHERE create_time >= TRUNC(SYSDATE) AND create_time < TRUNC(SYSDATE) + 1

EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 检查谓词部分是否有 INTERNAL_FUNCTION 包裹列

问题现象

深翻页（第 1000 页）越来越慢，后面几页几乎打不开。

业务场景

日志查询页支持翻到很后面，SQL 为 SELECT * FROM t_log ORDER BY log_id OFFSET 1000000 ROWS FETCH NEXT 20 ROWS ONLY。翻到后期每页耗时 8 秒以上。

-- 深偏移：数据库必须生成并丢弃前 100 万行
SELECT * FROM t_log ORDER BY log_id
 OFFSET 1000000 ROWS FETCH NEXT 20 ROWS ONLY;

-- 方案一：键集分页（Keyset Pagination），用上一页最后一条的键做条件
SELECT * FROM t_log
 WHERE log_id > :last_id_of_prev_page
 ORDER BY log_id
 FETCH NEXT 20 ROWS ONLY;

-- 方案二：若必须跳页，先用索引定位主键再回表
SELECT * FROM t_log t
 WHERE t.log_id IN (
   SELECT log_id FROM (SELECT log_id FROM t_log ORDER BY log_id)
    WHERE rownum <= 1000020
   MINUS
   SELECT log_id FROM (SELECT log_id FROM t_log ORDER BY log_id)
    WHERE rownum <= 1000000
 );

-- 方案三：Oracle 12c+ 用 ROW LIMITING + 覆盖索引先行定位

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 关注 plan 中的 VIEW / COUNT STOPKEY 行与 A-Rows 的差距（A-Rows 远大于返回行数即为浪费）

问题现象

同一个大表被反复扫描多次，一份数据算了三四遍。

业务场景

经营看板需要基于同一份「上月有效订单」分别算销售额、订单数、客单价、同比。开发写了 4 条独立 SQL，各自带完整的过滤条件，同一天量级的大表被扫了 4 遍。

SELECT SUM(amount) FROM t_order WHERE status='PAID' AND create_time >= :start AND create_time < :end;
SELECT COUNT(*) FROM t_order WHERE status='PAID' AND create_time >= :start AND create_time < :end;
SELECT AVG(amount) FROM t_order WHERE status='PAID' AND create_time >= :start AND create_time < :end;
-- 同一份数据被扫描 4 次

-- 一次扫描，四指标同时产出
SELECT SUM(amount) total_amt,
       COUNT(*) order_cnt,
       AVG(amount) avg_amt,
       COUNT(DISTINCT cust_id) cust_cnt
  FROM t_order
 WHERE status = 'PAID'
   AND create_time >= :start AND create_time < :end;

-- 若多个指标来自不同聚合粒度，用 WITH 组织并加物化提示
WITH base AS (
  SELECT /*+ MATERIALIZE */ dept_id, cust_id, amount
    FROM t_order
   WHERE status='PAID' AND create_time >= :start AND create_time < :end
)
SELECT d.dept_id, SUM(b.amount) FROM base b JOIN t_dept d ON b.dept_id = d.dept_id GROUP BY d.dept_id;

-- 观察计划中同表被扫描的次数
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 统计执行前后的 consistent gets 差值
SELECT name, value FROM v$mystat WHERE name='consistent gets';

问题现象

UNION 做了无谓的排序去重，大结果集性能很差。

业务场景

跨月账单合并查询，把 12 个月的账单表结果拼起来展示。开发用 UNION（不带 ALL），Oracle 对所有行做 SORT UNIQUE，临时表空间告警。

SELECT * FROM t_bill_202601
UNION
SELECT * FROM t_bill_202602;
-- UNION 隐含 DISTINCT，触发排序去重，即使数据本就不重复

-- 确定无重复（或业务允许重复）时用 UNION ALL
SELECT * FROM t_bill_202601
UNION ALL
SELECT * FROM t_bill_202602;

-- 若确实需要去重，也尽量先缩小数据量再去重
SELECT * FROM (SELECT ... WHERE rownum <= 10000)
UNION
SELECT * FROM (SELECT ... WHERE rownum <= 10000);

-- 计划中若出现 SORT UNIQUE / HASH UNIQUE 即为 UNION 去重开销
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 监控临时表空间使用
SELECT * FROM v$tempseg_usage;

问题现象

只看 EXPLAIN PLAN 的估算计划，看不到真实行数与耗时，优化方向全靠猜。

业务场景

某查询 EXPLAIN PLAN 显示走索引，实际线上却跑了 20 秒。原因是优化器估算返回 10 行，实际返回 200 万行，索引路径在真实数据分布下反而是最差选择。

-- 只有估算，看不到真实执行数据
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 1) 执行前的估算计划
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 2) 执行后的真实计划（关键！需先执行过该 SQL）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id          => '&sql_id',
  cursor_child_no => 0,
  format          => 'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +COST +BYTES'
));

-- 3) 从 SQL 文本反查 sql_id
SELECT sql_id, child_number, executions, buffer_gets, elapsed_time/1000 ms
  FROM v$sql WHERE sql_text LIKE '%关键字%' ORDER BY last_active_time DESC;

-- 4) 实时监控正在执行的 SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_ACTIVE_SESSION_PLAN('&sql_id'));
-- 或
SELECT * FROM v$sql_monitor WHERE sql_id = '&sql_id';

-- 关注 A-Rows 与 E-Rows 差异超过 10 倍的步骤，即为基数估算错误
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 查看计划中某步的 I/O 热点
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +IOSTATS'));

问题现象

优化器选错连接方式或索引，根因是估算行数与实际相差几个数量级。

业务场景

订单与商品关联查询，优化器估算 t_order 过滤后剩 500 行（实际 180 万行），于是选了 NESTED LOOPS，导致内层循环 180 万次，执行 15 分钟。

-- 计划中可见：E-Rows=500 而 A-Rows=1800000
-- 优化器基于错误的基数选择了 NESTED LOOPS

-- 1) 先收集准确统计信息（最常见解法）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'T_ORDER',
    cascade          => TRUE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => 4);
END;
/

-- 2) 多列相关性导致估算偏差时，建组合列统计信息（12c+）
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'T_ORDER', '(status, create_time)') FROM dual;

-- 3) 临时兜底：用提示强制正确的连接方式（需评估长期可维护性）
SELECT /*+ USE_HASH(o p) */ ... FROM t_order o JOIN t_product p ON o.sku_id = p.sku_id;

-- 4) 极难修正的可用 SQL PLAN BASELINE / SQL PROFILE 固定计划
DECLARE
  l_sql_id VARCHAR2(30);
BEGIN
  l_sql_id := DBMS_SQLTUNE.CREATE_SQLSET(...);
END;
/

-- 对比估算与实际
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 查看统计信息新鲜度
SELECT table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics WHERE table_name='T_ORDER';
-- 查看列统计与直方图
SELECT column_name, num_distinct, density, histogram FROM user_tab_col_statistics WHERE table_name='T_ORDER';

问题现象

同一条 SQL 有时秒级返回，有时十几秒，性能忽好忽坏。

业务场景

订单查询 WHERE status = :1 AND create_time >= :2，status 的取值分布极不均匀：'已完成' 占 85%，'待处理' 只占 0.5%。优化器窥探第一次执行传入的 '待处理'，选择了索引计划并固化，之后所有查询（包括占比 85% 的已完成）都复用这个计划，导致大批量场景极慢。

-- 单一计划被错误固化
-- 表现为：偶发慢查询，且慢的总是特定业务入口
-- 检查发现同一 sql_id 有多个 child cursor，但共享了不合适的计划

-- 1) 确认是否存在 ACS（自适应游标共享）多子游标
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, plan_hash_value, executions
  FROM v$sql WHERE sql_id = '&sql_id';

-- 2) 优先保证统计信息准确 + 直方图存在（让 CBO 有正确输入）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDER',
    method_opt => 'FOR COLUMNS status SIZE 254', degree => 4);
END;
/

-- 3) 12c+ 启用自适应执行计划（让优化器运行时切换连接方式）
ALTER SYSTEM SET optimizer_adaptive_plans = TRUE;
ALTER SYSTEM SET optimizer_adaptive_statistics = TRUE;

-- 4) 对关键 SQL 固化经过验证的计划，避免计划漂移
-- 从共享池加载计划到 SPM
DECLARE
  l_plans PLS_INTEGER;
BEGIN
  l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id');
END;
/

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, is_shareable, plan_hash_value
FROM v$sql WHERE sql_id = '&sql_id' ORDER BY child_number;

-- 查看 SPM 已固定的计划
SELECT sql_handle, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE '%T_ORDER%';

问题现象

看不懂计划里的 NESTED LOOPS / HASH JOIN / MERGE JOIN，不知道哪个好。

业务场景

订单与客户关联查询：大订单表（5000 万行）关联小客户表（20 万行）。优化器选了 NESTED LOOPS 且以订单表为驱动，导致内层探测 5000 万次。

-- 大表驱动 + 内层无高效索引 = NESTED LOOPS 灾难
-- 计划片段：
-- |* 3 |  NESTED LOOPS               |  5000万 |   2h|  ...
-- |* 4 |   TABLE ACCESS FULL T_ORDER|  5000万 | 15m |
-- |* 5 |   INDEX RANGE SCAN IDX_CUST|      1  |     |

-- 方案一：让大表走 HASH JOIN（一次哈希，线性代价）
SELECT /*+ USE_HASH(o c) */ o.order_id, c.cust_name
  FROM t_order o JOIN t_customer c ON o.cust_id = c.cust_id
 WHERE o.create_time >= TRUNC(SYSDATE) - 1;

-- 方案二：若必须 NESTED LOOPS，确保小表驱动 + 大表被驱动侧有索引
SELECT /*+ LEADING(c o) USE_NL(o) INDEX(o idx_order_cust) */ ...
  FROM t_customer c JOIN t_order o ON c.cust_id = o.cust_id
 WHERE c.cust_type = 'VIP';

-- 看每步的 A-Rows 与 A-Time，找出代价集中点
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 查看连接列是否有索引
SELECT index_name, column_name FROM user_ind_columns WHERE table_name IN ('T_ORDER','T_CUSTOMER');

问题现象

问题发生时没抓到现场，事后无从下手。

业务场景

生产环境每天 20:00~21:00 报表任务拖慢系统，但等运维发现时任务已结束，v$sql 里的信息可能已被挤出共享池。

-- 事后只看 v$sql，慢 SQL 可能已不在缓存中
SELECT * FROM v$sql ORDER BY elapsed_time DESC;

-- 1) 生成 AWR 报告，按 DB Time 排序定位 TOP SQL
-- sqlplus 中执行
-- @?/rdbms/admin/awrrpt.sql

-- 2) 直接查 AWR 视图找 TOP 消耗 SQL
SELECT * FROM (
  SELECT sql_id, ROUND(elapsed_time_delta/1e6,1) elapsed_s,
         ROUND(cpu_time_delta/1e6,1) cpu_s,
         executions_delta execs, buffer_gets_delta, disk_reads_delta
    FROM dba_hist_sqlstat
   WHERE snap_id BETWEEN &snap_begin AND &snap_end
   ORDER BY elapsed_time_delta DESC
) WHERE rownum <= 20;

-- 3) 用 SQL Monitor 看长任务的历史执行详情（保留时间较长）
SELECT sql_id, status, elapsed_time/1e6 s, sql_text
  FROM v$sql_monitor WHERE elapsed_time > 60e6 ORDER BY elapsed_time DESC;

-- 4) 用 ASH 分析某时段等待构成
SELECT event, COUNT(*) FROM v$active_session_history
 WHERE sample_time BETWEEN TO_DATE('2026-09-21 20:00','YYYY-MM-DD HH24:MI') AND TO_DATE('2026-09-21 21:00','YYYY-MM-DD HH24:MI')
 GROUP BY event ORDER BY 2 DESC;

-- 确认 AWR 快照是否存在且间隔合理
SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot ORDER BY snap_id DESC FETCH FIRST 10 ROWS ONLY;
-- 确认 SQL Monitor 保留策略
SELECT * FROM dba_hist_wr_control;

问题现象

统计信息过期或从未收集，CBO 基于错误数据做决策。

业务场景

数据仓库新增了 12 个业务表，开发建表后只顾着跑 ETL，忘了收集统计信息。一个月后报表 SQL 全部走全表扫描 + NESTED LOOPS，跑批时间从 30 分钟涨到 6 小时。

-- 建表后从未收集统计信息
-- user_tab_statistics 中 last_analyzed 为 NULL
SELECT table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics;

-- 1) 确认自动收集任务是否开启
SELECT client_name, status FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection';

-- 2) 手动收集（按需指定采样比例、并行度、级联索引）
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname     => 'DW_USER',
    cascade     => TRUE,          -- 同时收集索引统计
    degree      => 8,             -- 并行度，大表显著加速
    granularity => 'AUTO',        -- 分区表自动选择粒度
    options     => 'GATHER STALE');
END;
/

-- 3) 锁定不需变更的小表统计信息，避免反复收集
EXEC DBMS_STATS.LOCK_TABLE_STATS('DW_USER', 'T_DIM_CITY');

-- 4) 收集前备份，出问题可回滚
EXEC DBMS_STATS.RESTORE_TABLE_STATS('DW_USER', 'T_ORDER', SYSTIMESTAMP - 1/24);

-- 检查统计信息新鲜度与是否过期
SELECT table_name, num_rows, blocks, last_analyzed, stale_stats, stattype_locked
  FROM user_tab_statistics WHERE object_type='TABLE' ORDER BY last_analyzed NULLS FIRST;

-- 检查索引统计
SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor, last_analyzed
  FROM user_indexes WHERE table_name = 'T_ORDER';

问题现象

数据严重倾斜的列没有直方图，优化器按平均值估算，误差巨大。

业务场景

电商订单表 t_order 的 status 列：'已完成' 占 88%，'待支付' 占 3%，'退款中' 占 0.2%。开发查询「退款中」订单（只需返回 4 万行中的 80 行），优化器估算要返回 8000 行，选择了全表扫描；查询「已完成」时又反之。

-- status 列无直方图，CBO 按 1/num_distinct 平均估算
-- 查询稀有值 '退款中' 也走全表扫描

-- 1) 对倾斜列收集直方图（高度均衡直方图，SIZE 254 为上限）
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => USER,
    tabname     => 'T_ORDER',
    method_opt  => 'FOR COLUMNS SIZE 254 status',
    cascade     => TRUE);
END;
/

-- 2) 确认直方图已生成
SELECT column_name, num_distinct, density, histogram, num_buckets
  FROM user_tab_col_statistics
 WHERE table_name = 'T_ORDER' AND column_name = 'STATUS';

-- 3) 数据分布已变或倾斜不明显时，删除直方图避免误导
EXEC DBMS_STATS.DELETE_COLUMN_STATS(USER, 'T_ORDER', 'STATUS', col_stat_type => 'HISTOGRAM');

SELECT column_name, num_distinct, density, histogram, num_buckets
FROM user_tab_col_statistics WHERE table_name='T_ORDER';

-- 查看直方图明细（11g+）
SELECT * FROM user_tab_histograms WHERE table_name='T_ORDER' AND column_name='STATUS' ORDER BY endpoint_number;

问题现象

索引看着很完美，优化器却坚决不用，因为它知道回表代价太高。

业务场景

订单表按创建时间顺序插入，但查询条件常用 cust_id。索引 idx_cust(cust_id) 的逻辑读很好，但 clustering_factor 接近表行数（5000 万），说明同一 cust_id 的数据行在物理上散落在全表。优化器算出回表代价极高，宁可全表扫描。

-- 关注 clustering_factor：越接近表行数说明索引与表物理顺序越不一致
SELECT index_name, num_rows, leaf_blocks, distinct_keys, clustering_factor
  FROM user_indexes WHERE table_name = 'T_ORDER';
-- clustering_factor ≈ 50,000,000（等于表行数）→ 回表代价被判定为最高

-- 方案一：提高查询的选择性，减少回表行数（配合其他列建组合索引）
CREATE INDEX idx_order_cust_time ON t_order(cust_id, create_time);

-- 方案二：覆盖索引，彻底避免回表
CREATE INDEX idx_order_cust_amt ON t_order(cust_id, order_id, amount, status);

-- 方案三：若表本身按 cust_id 组织更合理，用 IOT 或重排物理存储
-- CREATE TABLE t_order_iot (...) ORGANIZATION INDEX;

SELECT index_name, num_rows, distinct_keys, clustering_factor, leaf_blocks FROM user_indexes WHERE table_name='T_ORDER';
-- 重建为有序组织后对比 clustering_factor 变化
-- ALTER TABLE t_order MOVE; 然后重新收集统计信息

问题现象

升级数据库或收集统计信息后，原本很快的 SQL 突然变慢，计划无故改变。

业务场景

核心交易 SQL 在数据库从 11g 升级到 19c 后性能劣化 20 倍。原因是新版本优化器改进了代价模型，选了另一套计划，而这套计划在当前数据特征下并不好。

-- 升级后计划漂移，无任何保护机制
-- 表现为：升级期间核心接口大面积超时

-- 1) 提前捕获已有稳定计划作为基线
DECLARE
  l_cnt PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
             sql_id          => '&sql_id',
             plan_hash_value => &good_plan_hash);
  DBMS_OUTPUT.PUT_LINE('loaded ' || l_cnt);
END;
/

-- 2) 或从 AWR 加载历史好计划
DECLARE
  l_cnt PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR(
             begin_snap => &snap_begin,
             end_snap   => &snap_end,
             basic_filter => 'sql_id = ''&sql_id''');
END;
/

-- 3) 查看并验证基线状态
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
  FROM dba_sql_plan_baselines WHERE sql_text LIKE '%T_ORDER%';

-- 4) 确认计划被固定使用
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('&sql_handle'));

SELECT sql_handle, plan_name, enabled, accepted, fixed, reproduced, origin
FROM dba_sql_plan_baselines WHERE sql_text LIKE '%关键字%';

-- 查看某 SQL 的计划演化历史
SELECT sql_id, plan_hash_value, cost, elapsed_time/1e6 s, created
FROM dba_hist_sql_plan WHERE sql_id = '&sql_id' ORDER BY created;

问题现象

单表数据量到数亿行，报表按天查询却要扫全表。

业务场景

交易流水表 t_txn 已积累 8 亿行。日报需要查当日数据：WHERE txn_time >= TRUNC(SYSDATE)。未分区时全表扫描，单次查询超过 10 分钟，且历史数据归档困难（DELETE 会产生海量 undo 并撑爆归档日志）。

CREATE TABLE t_txn (
  txn_id NUMBER, txn_time DATE, amount NUMBER, ...
);   -- 无分区
SELECT SUM(amount) FROM t_txn WHERE txn_time >= TRUNC(SYSDATE);
-- 全表扫描 8 亿行；归档靠 DELETE 按时间删历史数据

-- 按日/月范围分区，并启用间隔分区自动创建新分区
CREATE TABLE t_txn (
  txn_id   NUMBER,
  txn_time DATE NOT NULL,
  amount   NUMBER
)
PARTITION BY RANGE (txn_time)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_init VALUES LESS THAN (DATE '2026-01-01')
);

-- 查询自动触发分区裁剪（PARTITION RANGE ITERATOR / SINGLE PARTITION）
SELECT SUM(amount) FROM t_txn WHERE txn_time >= TRUNC(SYSDATE);

-- 本地索引，避免全局索引维护成本
CREATE INDEX idx_txn_time ON t_txn(txn_time) LOCAL;

-- 归档改为分区级操作，秒级完成且不产生大量 undo
ALTER TABLE t_txn DROP PARTITION p_202501 UPDATE GLOBAL INDEXES;

-- 确认分区裁剪生效（计划中应有 PARTITION RANGE ALL/ITERATOR/SINGLE）
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 查看分区信息与行数分布
SELECT partition_name, num_rows, high_value, last_analyzed
FROM user_tab_partitions WHERE table_name = 'T_TXN' ORDER BY partition_position;

问题现象

分区表上建了全局索引，DROP PARTITION 代价高昂甚至失败。

业务场景

分区表 t_txn 上建了全局唯一索引 idx_txn_id(txn_id)。每次做分区归档 DROP PARTITION 时，Oracle 必须更新全局索引中所有受影响条目，导致操作耗时 40 分钟且产生大量 I/O；改用 UPDATE GLOBAL INDEXES 后依然很慢。

-- 全局索引 + DROP PARTITION（不加 UPDATE GLOBAL INDEXES 会使索引 UNUSABLE）
CREATE UNIQUE INDEX idx_txn_id ON t_txn(txn_id);   -- 全局索引
ALTER TABLE t_txn DROP PARTITION p_202501;
-- 索引变为 UNUSABLE，全局索引需重建，耗时几十分钟

-- 方案一：查询条件含分区键时，优先建本地索引
CREATE INDEX idx_txn_time_amt ON t_txn(txn_time, amount) LOCAL;

-- 方案二：确需全局唯一索引时，DROP PARTITION 务必带 UPDATE GLOBAL INDEXES
ALTER TABLE t_txn DROP PARTITION p_202501 UPDATE GLOBAL INDEXES;

-- 方案三：用 GLOBAL PARTITIONED（Hash 全局分区）索引分散维护成本
CREATE UNIQUE INDEX idx_txn_id_gp ON t_txn(txn_id) GLOBAL PARTITION BY HASH(txn_id) PARTITIONS 16;

-- 查看索引是本地还是全局
SELECT index_name, partitioning_type, locality, status FROM user_part_indexes WHERE table_name='T_TXN';

-- 检查是否存在 UNUSABLE 分区索引
SELECT index_name, partition_name, status FROM user_ind_partitions WHERE status <> 'USABLE';
SELECT index_name, status FROM user_indexes WHERE status <> 'VALID';

问题现象

表已分区但查询没带分区键，分区裁剪失效，性能与未分区无异。

业务场景

按 txn_time 分区的流水表，运营按「卡号」查询某卡最近流水：WHERE card_no = ?。card_no 不是分区键，优化器无法裁剪，必须扫描所有 60 个分区，再在每个分区内找索引，性能反而比未分区表更差（要合并 60 份结果）。

SELECT * FROM t_txn WHERE card_no = '6222001';
-- 未限定分区键，可能访问全部 60 个分区

-- 方案一：查询条件补上分区键，实现「分区裁剪 + 索引过滤」
SELECT * FROM t_txn
 WHERE card_no = '6222001'
   AND txn_time >= SYSDATE - 7          -- 加分区键
   AND txn_time <  SYSDATE;

-- 方案二：用全局索引支撑纯 card_no 查询
CREATE INDEX idx_txn_card ON t_txn(card_no, txn_time) ONLINE;

-- 方案三：若业务总是按卡查询近期数据，考虑按卡号哈希分区（但会失去时间归档优势）
-- PARTITION BY HASH(card_no) PARTITIONS 32;

-- 重点看计划中 PARTITION 相关行：ALL 表示未裁剪
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 加 +PARTITION 格式可看每个分区的访问情况
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +PARTITION'));

问题现象

只收集了表级统计信息，分区级统计缺失，分区裁剪后估算不准。

业务场景

分区表收集统计信息时只用了默认粒度，分区级统计信息从未更新。查询单月数据时优化器按表级平均行数估算该分区，偏差 20 倍，选择了错误的连接方式。

-- 默认 granularity=AUTO 在部分场景可能只收集全局统计
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_TXN');
END;
/

-- 明确指定粒度：GLOBAL 表级、PARTITION 分区级、AUTO 自动、ALL 全部
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => USER,
    tabname     => 'T_TXN',
    granularity => 'AUTO',        -- 有分区统计则收集分区级 + 全局级
    cascade     => TRUE,          -- 索引统计
    degree      => 8,
    method_opt  => 'FOR ALL COLUMNS SIZE AUTO');
END;
/

-- 增量统计（11g+）：只收集变化分区，大分区表效率极高
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(USER, 'T_TXN', 'INCREMENTAL', 'TRUE');
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_TXN', granularity => 'AUTO');
END;
/

-- 查看分区级统计
SELECT partition_name, num_rows, last_analyzed, stale_stats
  FROM user_tab_partitions WHERE table_name='T_TXN' ORDER BY partition_position;

SELECT partition_name, num_rows, last_analyzed, stale_stats FROM user_tab_partitions WHERE table_name='T_TXN';

-- 查看是否启用增量统计
SELECT * FROM user_tab_stat_prefs WHERE table_name='T_TXN';

问题现象

用 INSERT SELECT 追加千万级数据，耗时长且产生巨量日志。

业务场景

每日需要把 ODS 层的 4000 万行数据灌入分区表的新分区。用 INSERT INTO ... SELECT 需要 25 分钟，并产生 40GB 归档日志，导致归档空间告警。

-- 逐行插入，全程生成 redo/undo
INSERT /*+ APPEND */ INTO t_txn
SELECT * FROM stg_txn WHERE stat_date = TRUNC(SYSDATE) - 1;
COMMIT;
-- 25 分钟，40GB redo

-- 1) 建与目标分区结构完全一致（含约束、索引）的临时表
CREATE TABLE t_txn_tmp AS SELECT * FROM t_txn WHERE 1=0;
-- 2) 直接路径插入临时表（可并行、nologging）
ALTER TABLE t_txn_tmp NOLOGGING;
INSERT /*+ APPEND PARALLEL(8) */ INTO t_txn_tmp
SELECT * FROM stg_txn WHERE stat_date = TRUNC(SYSDATE) - 1;
COMMIT;
-- 3) 建好本地索引
CREATE INDEX idx_txn_tmp_time ON t_txn_tmp(txn_time) LOCAL;
-- 4) 分区交换：元数据级操作，秒级完成
ALTER TABLE t_txn EXCHANGE PARTITION p_20260921
  WITH TABLE t_txn_tmp INCLUDING INDEXES WITHOUT VALIDATION;

-- 确认交换成功，分区行数正确
SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name='T_TXN';

-- 注意：WITHOUT VALIDATION 跳过数据校验但也不更新索引，需确认数据已规范
-- 交换后原临时表变为空分区结构，需检查数据是否读不到
SELECT COUNT(*) FROM t_txn_tmp;

问题现象

SELECT 列表中的标量子查询被逐行执行，形成隐蔽的循环。

业务场景

客户列表需要显示每个客户的订单总数与最近下单时间：SELECT c.cust_id, (SELECT COUNT(*) FROM t_order o WHERE o.cust_id = c.cust_id) cnt, (SELECT MAX(create_time) FROM t_order o WHERE o.cust_id = c.cust_id) last_t FROM t_customer c。查询 1 万个客户，每个客户触发 2 次子查询，共 2 万次索引探测 + 聚合。

SELECT c.cust_id, c.cust_name,
       (SELECT COUNT(*) FROM t_order o WHERE o.cust_id = c.cust_id) order_cnt,
       (SELECT MAX(o.create_time) FROM t_order o WHERE o.cust_id = c.cust_id) last_order_t
  FROM t_customer c
 WHERE c.status = 'ACTIVE';

-- 改为一次聚合 + 左外连接
SELECT c.cust_id, c.cust_name,
       NVL(o.order_cnt, 0) order_cnt,
       o.last_order_t
  FROM t_customer c
  LEFT JOIN (
    SELECT cust_id, COUNT(*) order_cnt, MAX(create_time) last_order_t
      FROM t_order
     GROUP BY cust_id
  ) o ON o.cust_id = c.cust_id
 WHERE c.status = 'ACTIVE';

-- 计划中若出现类似 FILTER 且子计划被执行多次，即为逐行执行
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 关注子计划的 Starts 列（次数）是否等于外层行数

问题现象

UPDATE ... WHERE EXISTS 更新百万行，执行数小时。

业务场景

给所有「VIP 客户」的订单打标：UPDATE t_order o SET tag = 'VIP' WHERE EXISTS (SELECT 1 FROM t_customer c WHERE c.cust_id = o.cust_id AND c.level = 'VIP')。涉及 800 万订单行，执行 3.5 小时。

UPDATE t_order o
   SET o.tag = 'VIP'
 WHERE EXISTS (SELECT 1 FROM t_customer c
                WHERE c.cust_id = o.cust_id AND c.level = 'VIP');
-- 每行都要探测子查询，且 UPDATE 产生大量 undo

-- 方案一：用 MERGE 一次完成，可并行、可利用哈希连接
MERGE /*+ PARALLEL(8) */ INTO t_order o
USING (SELECT cust_id FROM t_customer WHERE level = 'VIP') c
   ON (o.cust_id = c.cust_id)
WHEN MATCHED THEN UPDATE SET o.tag = 'VIP';

-- 方案二：若只有少量 VIP 客户，先取出清单再 IN，让优化器用索引
UPDATE t_order o SET tag='VIP'
 WHERE o.cust_id IN (SELECT cust_id FROM t_customer WHERE level='VIP');

-- 方案三：超大表分批更新，避免长事务与 undo 膨胀
BEGIN
  LOOP
    UPDATE t_order SET tag='VIP'
     WHERE tag IS NULL AND cust_id IN (SELECT cust_id FROM t_customer WHERE level='VIP')
       AND rownum <= 50000;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
END;
/

-- 观察 MERGE 是否使用 HASH JOIN 及并行
EXPLAIN PLAN FOR MERGE ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 监控 undo 使用
SELECT ROUND(SUM(bytes)/1024/1024/1024,2) gb FROM dba_undo_extents WHERE status='ACTIVE';

问题现象

为了去重写了三层嵌套 DISTINCT，代价极高。

业务场景

统计「有哪些客户下过单」：SELECT DISTINCT cust_id FROM t_order。8000 万行去重后只剩 30 万客户。DISTINCT 对全部 8000 万行做 HASH UNIQUE，占用大量 PGA。

SELECT DISTINCT cust_id FROM t_order;
-- 对 8000 万行做 HASH UNIQUE

-- 更糟：多层 DISTINCT 嵌套
SELECT DISTINCT cust_id FROM (SELECT DISTINCT cust_id, status FROM t_order);

-- 方案一：有索引时直接走索引快速全扫，天然有序去重
SELECT /**/ cust_id FROM t_order;  -- 计划为 INDEX FAST FULL SCAN，若需去重加 GROUP BY
SELECT cust_id FROM t_order GROUP BY cust_id;   -- 可利用索引有序性

-- 方案二：用 EXISTS 做半连接，遇到第一条即返回
SELECT c.cust_id FROM t_customer c
 WHERE EXISTS (SELECT 1 FROM t_order o WHERE o.cust_id = c.cust_id);

-- 方案三：确需去重时，减少参与去重的列与行数
SELECT DISTINCT cust_id FROM (SELECT cust_id FROM t_order WHERE create_time >= SYSDATE - 30);

-- 计划中避免出现 HASH UNIQUE / SORT UNIQUE 作用于大结果集
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 监控 PGA 使用
SELECT name, value/1024/1024 mb FROM v$sysstat WHERE name LIKE 'workarea%';

问题现象

排序数据量超过 PGA 限制，落到临时表空间做磁盘排序，性能断崖式下降。

业务场景

对账任务对 1.2 亿行流水按金额排序：SELECT * FROM t_txn ORDER BY amount。排序数据约 12GB，PGA 无法容纳，全部溢出到临时表空间，跑了 40 分钟且临时表空间爆满导致其他会话报 ORA-01652。

SELECT * FROM t_txn ORDER BY amount;
-- 12GB 排序数据全部溢出磁盘，临时表空间告警

-- 方案一：确认是否真的需要全局排序（很多场景只需要 TOP-N）
SELECT * FROM (SELECT txn_id, amount FROM t_txn ORDER BY amount DESC)
 WHERE rownum <= 100;
-- 或用 12c+ 语法，只排序前 100 行
SELECT txn_id, amount FROM t_txn ORDER BY amount DESC FETCH FIRST 100 ROWS ONLY;

-- 方案二：只查需要的列（减少排序数据量，利于覆盖索引）
CREATE INDEX idx_txn_amt ON t_txn(amount, txn_id);
SELECT txn_id, amount FROM t_txn ORDER BY amount;
-- 走索引扫描，完全无排序

-- 方案三：适度增大排序区，让排序在内存完成
ALTER SESSION SET sort_area_size = 104857600;  -- 仅专用服务器模式，通常优先调 PGA
ALTER SYSTEM SET pga_aggregate_target = 8G;

-- 查看排序是否溢出磁盘
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)','sorts (disk)','sorts (rows)');

-- 查看临时表空间实时使用
SELECT s.sid, u.tablespace, u.blocks*8/1024 mb, u.segtype
FROM v$sort_usage u JOIN v$session s ON u.session_addr = s.saddr;

-- 计划中若出现 TEMP 表空间相关或 sort 步骤 A-Time 极高，即为磁盘排序

问题现象

同一业务键有多条历史记录，取最新一条时写了复杂的自连接。

业务场景

客户信息表 t_cust_info 中同一 cust_id 因多次变更存在多条历史记录（按 update_time 区分），需要取每个客户的最新一条。开发写了自关联 MAX 子查询，8000 万行表执行 25 分钟。

SELECT a.* FROM t_cust_info a
 WHERE a.update_time = (SELECT MAX(b.update_time)
                          FROM t_cust_info b WHERE b.cust_id = a.cust_id);
-- 每行触发一次子查询 + 聚合

-- 方案一：ROW_NUMBER 分析函数，一次排序解决（推荐）
SELECT * FROM (
  SELECT a.*,
         ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY update_time DESC) rn
    FROM t_cust_info a
) WHERE rn = 1;

-- 方案二：KEEP DENSE_RANK 聚合，配合索引可直接走索引扫描
SELECT cust_id,
       MAX(cust_name)    KEEP (DENSE_RANK LAST ORDER BY update_time) cust_name,
       MAX(cust_level)   KEEP (DENSE_RANK LAST ORDER BY update_time) cust_level,
       MAX(update_time)  last_update
  FROM t_cust_info
 GROUP BY cust_id;

-- 方案三：建组合索引支撑
CREATE INDEX idx_cust_info ON t_cust_info(cust_id, update_time DESC);

-- 关注计划中的窗口排序步骤与 A-Time 分布
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
-- 确认索引方向：DESC 索引可避免倒序排序
SELECT index_name, column_name, descend FROM user_ind_columns WHERE table_name='T_CUST_INFO';

问题现象

DELETE 千万行数据，undo 段爆满、归档暴增、回滚噩梦。

业务场景

合规要求清理 3 年前的日志表数据，共 1.5 亿行。执行 DELETE FROM t_log WHERE create_time < ADD_MONTHS(SYSDATE, -36)，运行 4 小时后 undo 表空间耗尽，被迫中断且回滚又花了 2 小时。

DELETE FROM t_log WHERE create_time < ADD_MONTHS(SYSDATE, -36);
-- 单事务删除 1.5 亿行：undo 爆满、长事务阻塞、回滚代价极高

-- 方案一（最优）：分区表直接 DROP PARTITION
ALTER TABLE t_log DROP PARTITION p_2022 UPDATE GLOBAL INDEXES;

-- 方案二：非分区表分批删除 + 定期提交
DECLARE
  v_rows PLS_INTEGER := 1;
BEGIN
  WHILE v_rows > 0 LOOP
    DELETE FROM t_log
     WHERE create_time < ADD_MONTHS(SYSDATE, -36)
       AND rownum <= 20000;      -- 每批 2 万行
    v_rows := SQL%ROWCOUNT;
    COMMIT;                      -- 及时释放 undo
    DBMS_LOCK.SLEEP(0.1);        -- 限流，给其他会话让路
  END LOOP;
END;
/

-- 方案三：CTAS 重建（适合删除比例很大时）
CREATE TABLE t_log_new AS SELECT * FROM t_log WHERE create_time >= ADD_MONTHS(SYSDATE, -36);
-- 然后改名替换

-- 监控 undo 使用情况
SELECT s.sid, s.username, t.used_ublk*8/1024 mb, t.used_urec
FROM v$transaction t JOIN v$session s ON t.ses_addr = s.saddr ORDER BY 3 DESC;

-- 查看长事务
SELECT sid, start_time, used_ublk FROM v$transaction ORDER BY start_time;

问题现象

PL/SQL 循环中逐行 DML，上下文切换成为瓶颈。

业务场景

日终任务需要把 200 万条 ODS 数据加工后写入目标表。开发写了游标 FOR 循环，每条执行一次 INSERT，运行 52 分钟，且 PL/SQL 引擎与 SQL 引擎反复切换消耗大量 CPU。

BEGIN
  FOR r IN (SELECT * FROM stg_order WHERE batch_date = TRUNC(SYSDATE)) LOOP
    INSERT INTO t_order_result(order_id, amount, calc_time)
    VALUES (r.order_id, r.amount * 1.06, SYSDATE);   -- 每行一次插入
  END LOOP;
  COMMIT;
END;
/

DECLARE
  TYPE t_rec IS RECORD (order_id t_order_result.order_id%TYPE,
                        amount   t_order_result.amount%TYPE);
  TYPE t_tab IS TABLE OF t_rec INDEX BY PLS_INTEGER;
  l_data t_tab;
  CURSOR c IS SELECT order_id, amount * 1.06 amount
                FROM stg_order WHERE batch_date = TRUNC(SYSDATE);
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT 5000;   -- 每批 5000 条
    EXIT WHEN l_data.COUNT = 0;
    FORALL i IN 1 .. l_data.COUNT
      INSERT INTO t_order_result(order_id, amount, calc_time)
      VALUES (l_data(i).order_id, l_data(i).amount, SYSDATE);
    COMMIT;                                        -- 分批提交
  END LOOP;
  CLOSE c;
END;
/

-- 对比优化前后的 context switch 次数（自身会话）
SELECT name, value FROM v$mystat WHERE name IN
 ('sql execute count','user commits');

-- 用 DBMS_PROFILER 或 DBMS_HPROF 做 PL/SQL 性能剖析，定位热点行
-- 监控 PGA 使用，确认批大小合理
SELECT name, ROUND(value/1024/1024,1) mb FROM v$sysstat WHERE name LIKE 'workarea%';

问题现象

循环内嵌套查询，形成 N+1 问题。

业务场景

生成客户对账单，需要遍历 5 万客户，每个客户再查订单明细。循环内查询导致 5 万次 SQL 往返，耗时 28 分钟。

BEGIN
  FOR c IN (SELECT cust_id FROM t_customer WHERE status = 'ACTIVE') LOOP
    -- 循环内查询：5 万次 SQL 执行
    FOR o IN (SELECT * FROM t_order WHERE cust_id = c.cust_id) LOOP
      -- 处理
      NULL;
    END LOOP;
  END LOOP;
END;
/

-- 方案一：一次 JOIN 取回全部数据，程序内分组处理
BEGIN
  FOR r IN (SELECT c.cust_id, o.order_id, o.amount, o.create_time
              FROM t_customer c
              JOIN t_order o ON c.cust_id = o.cust_id
             WHERE c.status = 'ACTIVE'
             ORDER BY c.cust_id) LOOP
    -- 按 cust_id 变化切分并处理
    NULL;
  END LOOP;
END;
/

-- 方案二：BULK COLLECT 一次性取回集合再遍历
DECLARE
  TYPE t_tab IS TABLE OF t_order%ROWTYPE;
  l_all t_tab;
BEGIN
  SELECT * BULK COLLECT INTO l_all
    FROM t_order o
   WHERE EXISTS (SELECT 1 FROM t_customer c
                  WHERE c.cust_id = o.cust_id AND c.status='ACTIVE');
  FOR i IN 1 .. l_all.COUNT LOOP
    NULL;   -- 纯内存遍历，无额外 SQL
  END LOOP;
END;
/

-- 统计某段时间内 SQL 执行次数
SELECT name, value FROM v$sysstat WHERE name = 'execute count';

-- 会话级观察
SELECT sid, sql_exec_start, sql_id, event FROM v$session WHERE status = 'ACTIVE';

-- 用 DBMS_HPROF 定位循环内 SQL 热点

问题现象

日志表写入阻塞主事务，或长事务中日志堆积。

业务场景

批处理过程中需要写审计日志，日志写入与主业务在同一事务中，导致事务过大、undo 膨胀，且一旦主事务回滚，日志全部丢失，无法排查问题。

BEGIN
  FOR r IN (...) LOOP
    -- 业务处理
    UPDATE t_biz SET ...;
    INSERT INTO t_audit_log VALUES (...);   -- 同一事务，日志随主事务回滚
  END LOOP;
  COMMIT;
END;
/

-- 用自治事务独立提交日志，不影响主事务
CREATE OR REPLACE PROCEDURE sp_log(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO t_audit_log(log_time, msg) VALUES (SYSDATE, p_msg);
  COMMIT;                       -- 独立提交
END;
/

BEGIN
  FOR r IN (...) LOOP
    UPDATE t_biz SET ...;
    sp_log('处理完成 ' || r.id);   -- 日志独立提交，不受主事务影响
  END LOOP;
  COMMIT;
END;
/

-- 若日志量极大，改用异步写入：先入队，由后台作业批量落库
-- DBMS_SCHEDULER 定时消费队列

-- 观察 commit 次数（自治事务会显著增加）
SELECT name, value FROM v$sysstat WHERE name IN ('user commits','user rollbacks');

-- 监控日志表写入频率与量级
SELECT TRUNC(log_time,'HH24') hh, COUNT(*) FROM t_audit_log GROUP BY TRUNC(log_time,'HH24') ORDER BY 1 DESC;

问题现象

只知道系统慢，不知道慢在哪里。CPU？I/O？锁？

业务场景

业务反馈「系统整体变慢」，但开发逐个优化 SQL 没有效果。实际上瓶颈是 db file sequential read 等待占比 75%，说明是索引回表引起的随机 I/O 过多。

-- 漫无目的地逐个优化 SQL
SELECT * FROM v$sql ORDER BY elapsed_time DESC;
-- 缺少「系统整体在等什么」的视角

-- 1) 看当前实例的等待事件构成（按会话累计）
SELECT event, total_waits, ROUND(time_waited_micro/1e6,1) total_s,
       ROUND(average_wait*10,2) avg_ms, wait_class
  FROM v$system_event
 WHERE wait_class <> 'Idle'
 ORDER BY time_waited_micro DESC FETCH FIRST 20 ROWS ONLY;

-- 2) 看实时正在等的会话
SELECT s.sid, s.serial#, s.sql_id, s.event, s.wait_class,
       s.seconds_in_wait, s.blocking_session
  FROM v$session s
 WHERE s.status = 'ACTIVE' AND s.wait_class <> 'Idle'
 ORDER BY s.seconds_in_wait DESC;

-- 3) 看某段时间的等待构成（更精确）
SELECT session_state, event, COUNT(*) samples,
       ROUND(COUNT(*)*100/SUM(COUNT(*)) OVER (), 1) pct
  FROM v$active_session_history
 WHERE sample_time > SYSDATE - 30/1440
 GROUP BY session_state, event
 ORDER BY samples DESC FETCH FIRST 15 ROWS ONLY;

-- 4) 看 CPU 与 I/O 的总体比例
SELECT stat_name, value FROM v$sys_time_model
 WHERE stat_name IN ('DB CPU','DB time','background elapsed time');

-- 常用等待事件速查
-- db file sequential read      索引/回表随机读 → 减少回表、优化索引
-- db file scattered read        全表扫描多块读 → 检查索引、分区裁剪
-- log file sync                 提交等待 → 减少提交次数、调整 redo 大小
-- enq: TX - row lock contention 行锁争用 → 缩短事务、调整访问顺序
-- enq: TX - index contention    索引块争用 → 反向索引或调整序列
-- library cache lock/pin        硬解析争用 → 使用绑定变量
-- direct path read/write        直接路径 I/O → 并行操作或排序溢出

问题现象

问题只在特定时刻短暂出现，事后无法复现。

业务场景

每天 10:00 前后系统出现 2~3 分钟卡顿，业务人员抱怨但运维查的时候已经恢复，v$sql 里看不出异常。

-- 只查当前状态，问题时段已过
SELECT * FROM v$session WHERE status='ACTIVE';
-- 拿不到历史现场

-- ASH 按秒采样，保留最近约 1 小时的内存数据（AWR 中保留更久）
-- 定位问题时段的主导等待与 TOP SQL
SELECT TO_CHAR(sample_time,'HH24:MI') t,
       event, COUNT(*) samples
  FROM v$active_session_history
 WHERE sample_time BETWEEN TO_DATE('2026-09-22 09:55','YYYY-MM-DD HH24:MI')
                        AND TO_DATE('2026-09-22 10:05','YYYY-MM-DD HH24:MI')
 GROUP BY TO_CHAR(sample_time,'HH24:MI'), event
 ORDER BY t, samples DESC;

-- 找出问题时段最耗时的 SQL
SELECT sql_id, COUNT(*) samples,
       ROUND(COUNT(*)*100/SUM(COUNT(*)) OVER (), 1) pct
  FROM v$active_session_history
 WHERE sample_time BETWEEN :t1 AND :t2
   AND session_state = 'ON CPU'
 GROUP BY sql_id ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY;

-- 按阻塞链找到源头会话（谁堵住了谁）
SELECT blocking_session, session_id, COUNT(*) blocks, event
  FROM v$active_session_history
 WHERE blocking_session IS NOT NULL AND sample_time BETWEEN :t1 AND :t2
 GROUP BY blocking_session, session_id, event ORDER BY blocks DESC;

-- 确认 ASH 采样是否正常
SELECT COUNT(*), MIN(sample_time), MAX(sample_time) FROM v$active_session_history;

-- AWR 中查询历史 ASH（保留更长时间）
SELECT * FROM dba_hist_active_sess_history WHERE sample_time > SYSDATE - 7;

问题现象

某会话长时间不返回，其他会话排队等待。

业务场景

订单系统批量更新时，多个并发任务同时更新同一批订单，出现大量 enq: TX - row lock contention 等待，接口超时率飙升。

-- 只看业务慢，不知被谁阻塞
SELECT * FROM v$session WHERE status = 'ACTIVE';
-- 缺少阻塞关系分析

-- 1) 查看当前阻塞链（谁阻塞了谁）
SELECT s1.sid blocked_sid, s1.username blocked_user, s1.sql_id blocked_sql,
       s2.sid blocking_sid, s2.username blocking_user, s2.sql_id blocking_sql,
       s1.event, s1.seconds_in_wait wait_s
  FROM v$session s1
  JOIN v$session s2 ON s1.blocking_session = s2.sid
 WHERE s1.blocking_session IS NOT NULL
 ORDER BY s1.seconds_in_wait DESC;

-- 2) 查看被锁对象与行
SELECT do.object_name, lo.session_id, lo.oracle_username, lo.locked_mode,
       lo.os_user_name, lo.process
  FROM v$locked_object lo JOIN dba_objects do ON lo.object_id = do.object_id;

-- 3) 查看等待者的具体等待对象（12c+）
SELECT * FROM v$wait_chains;

-- 4) 应急处理：终止阻塞源（需 DBA 评估）
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 5) 根治方向：调整事务顺序、缩短事务、加 SKIP LOCKED 做并行消费
SELECT * FROM t_order_job
 WHERE status = 'PENDING'
   AND rownum <= 100
   FOR UPDATE SKIP LOCKED;    -- 跳过已被锁定的行，避免争抢

-- 确认锁等待是否消除
SELECT event, COUNT(*) FROM v$session WHERE event LIKE 'enq: TX%' GROUP BY event;

-- 查看死锁统计
SELECT name, value FROM v$sysstat WHERE name = 'enqueue deadlocks';

-- 会话级观察等待时长
SELECT sid, event, seconds_in_wait FROM v$session WHERE event LIKE 'enq: TX%';

问题现象

高频提交导致 log file sync 等待成为首要瓶颈。

业务场景

数据同步任务逐条 INSERT 后立即 COMMIT，每秒提交数百次，redo 写入与等待同步成为瓶颈，任务整体吞吐上不去，DB Time 中 log file sync 占 40%。

BEGIN
  FOR r IN (...) LOOP
    INSERT INTO t_sync VALUES (...);
    COMMIT;              -- 每行一次提交
  END LOOP;
END;
/

-- 改为批量提交，把提交次数降到合理量级
DECLARE
  TYPE t_tab IS TABLE OF t_sync%ROWTYPE;
  l_buf t_tab;
BEGIN
  SELECT * BULK COLLECT INTO l_buf FROM stg_sync;
  FORALL i IN 1 .. l_buf.COUNT
    INSERT INTO t_sync VALUES l_buf(i);
  COMMIT;                 -- 整批一次提交
END;
/

-- 或分批提交（每 5000~10000 行一次），兼顾吞吐与资源峰值
-- 同时检查 redo 日志大小与 IO 能力
-- SELECT group#, bytes/1024/1024 mb, status FROM v$log;

-- 查看提交次数与 log file sync 等待
SELECT name, value FROM v$sysstat WHERE name IN ('user commits','user rollbacks');
SELECT event, total_waits, ROUND(time_waited_micro/1e6,1) s
  FROM v$system_event WHERE event = 'log file sync';

-- 查看 redo 日志配置
SELECT group#, thread#, bytes/1024/1024 mb, status, archived FROM v$log;

问题现象

内存不足导致大量物理读与排序溢出，内存过大浪费资源。

业务场景

数据库服务器 64GB 内存，SGA 只配了 4GB。运行报表时数据字典与索引缓存命中率低，物理读频繁；排序区太小导致磁盘排序，DB Time 中 I/O 时间占比极高。

-- 初始配置偏小，未随业务增长调整
SELECT component, current_size/1024/1024 mb, min_size, max_size
  FROM v$memory_dynamic_components;
-- shared pool 与 buffer cache 都很小，命中率低

-- 方案一：12c+ 使用自动内存管理（AMM），让实例自动调配
ALTER SYSTEM SET memory_target = 32G SCOPE = SPFILE;
ALTER SYSTEM SET memory_max_target = 32G SCOPE = SPFILE;
-- 重启实例生效

-- 方案二：SGA 自动 + PGA 自动（更可控，11g+ 推荐）
ALTER SYSTEM SET sga_target = 24G SCOPE = SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 8G SCOPE = SPFILE;

-- 方案三：确认缓冲区命中率，判断是否需要继续调大
SELECT name, value FROM v$sysstat
 WHERE name IN ('db block gets','consistent gets','physical reads','session logical reads');

-- 缓冲区命中率计算（应高于 95%，OLTP 场景通常 >99%）
SELECT ROUND((1 - (phy.value / (cur.value + con.value))) * 100, 2) hit_pct
  FROM v$sysstat cur, v$sysstat con, v$sysstat phy
 WHERE cur.name = 'db block gets'
   AND con.name = 'consistent gets'
   AND phy.name = 'physical reads';

-- 各内存组件当前分配
SELECT component, ROUND(current_size/1024/1024,1) mb, ROUND(user_specified_size/1024/1024,1) user_mb
  FROM v$memory_dynamic_components ORDER BY current_size DESC;

-- PGA 使用情况
SELECT name, ROUND(value/1024/1024,1) mb FROM v$pgastat WHERE name IN
 ('aggregate PGA target parameter','aggregate PGA auto target','total PGA allocated','over allocation count');

-- 命中率
SELECT ROUND((1 - phys.value/(cur.value+con.value))*100,2) pct FROM v$sysstat cur, v$sysstat con, v$sysstat phys
 WHERE cur.name='db block gets' AND con.name='consistent gets' AND phys.name='physical reads';

问题现象

该并行的没并行，不该并行的乱并行拖垮系统。

业务场景

数仓按天聚合 3 亿行流水，单线程跑 45 分钟。开发直接在 OLTP 库的同一条 SQL 上加 PARALLEL(16)，结果 16 个并行进程抢占 CPU 与 I/O，导致交易库响应时间暴涨，被业务投诉。

-- 在 OLTP 实例上盲目并行，且写死在 SQL 里
SELECT /*+ PARALLEL(t, 16) */ dept_id, SUM(amount)
  FROM t_txn t
 WHERE txn_time >= TRUNC(SYSDATE) - 1
 GROUP BY dept_id;
-- 16 个并行进程消耗全部 CPU，拖垮交易业务

-- 1) 确认实例的并行度上限与资源约束
SELECT name, value FROM v$parameter WHERE name IN
 ('parallel_max_servers','parallel_min_servers','cpu_count','parallel_degree_limit','parallel_degree_policy');

-- 2) 大表汇报类操作显式指定合适的并行度（不超过 CPU 核数的一半）
SELECT /*+ PARALLEL(t, 4) */ dept_id, SUM(amount)
  FROM t_txn t
 WHERE txn_time >= TRUNC(SYSDATE) - 1
 GROUP BY dept_id;

-- 3) 更推荐用资源管理器限制并行消费者组的资源，隔离报表与交易
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan             => 'OLTP_PLAN',
    group_or_subplan => 'REPORT_GROUP',
    mgmt_p1          => 30,          -- 最多占 30% CPU
    parallel_degree_limit_p1 => 4);
END;
/

-- 4) 表级设置并行度（影响所有访问该表的语句）
ALTER TABLE t_txn PARALLEL 4;
ALTER TABLE t_txn NOPARALLEL;   -- 用完及时关闭

-- 查看并行执行情况与等待
SELECT * FROM v$pq_sesstat ORDER BY statistic;
SELECT event, COUNT(*) FROM v$session WHERE event LIKE 'PX%' OR event LIKE '%parallel%' GROUP BY event;

-- 查看当前并行进程数
SELECT COUNT(*) slaves FROM v$px_process;

-- 确认并行参数配置
SELECT name, value FROM v$parameter WHERE name LIKE 'parallel%';

问题现象

同一个高频查询反复计算，但数据变化不频繁。

业务场景

门户首页的统计看板每 5 秒刷新一次，每次都全量计算「本月累计交易额」，消耗大量 CPU。实际数据每分钟才更新一次。

-- 高频重复计算，完全不做缓存
SELECT SUM(amount) FROM t_order WHERE create_time >= TRUNC(SYSDATE,'MM');
-- 每 5 秒执行一次全量聚合

-- 方案一：结果集缓存（11g+，适合读多写少的重复查询）
SELECT /*+ RESULT_CACHE */ SUM(amount)
  FROM t_order WHERE create_time >= TRUNC(SYSDATE,'MM');

-- 需要时先确保结果集缓存已启用
SELECT name, value FROM v$parameter WHERE name = 'result_cache_mode';
ALTER SYSTEM SET result_cache_mode = MANUAL;    -- MANUAL 时仅带提示的语句缓存
-- ALTER SYSTEM SET result_cache_max_size = 512M;

-- 方案二：物化视图 + 定时刷新（数据变化周期较长时更合适）
CREATE MATERIALIZED VIEW mv_month_amount
  REFRESH FAST ON DEMAND
  ENABLE QUERY REWRITE
AS
SELECT TRUNC(create_time,'MM') stat_month, SUM(amount) total_amt, COUNT(*) cnt
  FROM t_order GROUP BY TRUNC(create_time,'MM');

-- 由调度任务每分钟刷新
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => 'JOB_MV_REFRESH',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_MVIEW.REFRESH(''MV_MONTH_AMOUNT'', ''F''); END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
    enabled    => TRUE);
END;
/

-- 查看结果集缓存使用情况
SELECT name, value FROM v$result_cache_statistics;

-- 查看缓存的依赖对象与命中情况
SELECT * FROM v$result_cache_objects;

-- 物化视图刷新状态
SELECT mview_name, last_refresh_type, last_refresh_date, staleness FROM user_mviews;

问题现象

事务中包含用户交互或远程调用，持锁时间长，阻塞其他会话。

业务场景

订单创建流程在事务中先插入订单，再调用第三方支付网关（超时 30 秒），然后更新库存。期间订单行与库存行一直持锁，并发下单时大量等待。

BEGIN
  INSERT INTO t_order(...) VALUES (...);           -- 持锁开始
  -- 调用支付网关（阻塞 30 秒）
  SELECT http_post(...) FROM dual;
  UPDATE t_stock SET qty = qty - 1 WHERE ...;      -- 持锁继续
  COMMIT;
END;
/

-- 把外部调用挪到事务之外，事务只做数据库操作
DECLARE
  v_pay_result VARCHAR2(20);
BEGIN
  -- 1) 事务外先调支付网关
  v_pay_result := http_post_order(...);

  -- 2) 事务内只做数据库操作，尽快提交
  INSERT INTO t_order(...) VALUES (...);
  UPDATE t_stock SET qty = qty - 1 WHERE sku_id = :sku AND qty >= 1;
  COMMIT;

  -- 3) 事务外写日志/发消息
  sp_log('order created');
END;
/

-- 更新库存务必带条件，避免更新 0 行还占锁
-- UPDATE t_stock SET qty = qty - 1 WHERE sku_id = :sku AND qty >= 1;

-- 查看长事务
SELECT s.sid, s.username, t.start_time, t.used_ublk*8/1024 mb_undo,
       ROUND((SYSDATE - t.start_time)*24*3600,1) dur_s
  FROM v$transaction t JOIN v$session s ON t.ses_addr = s.saddr
 ORDER BY t.start_time;

-- 查看锁等待队列
SELECT * FROM v$lock WHERE type IN ('TX','TM') AND request > 0;
SELECT sid, event, seconds_in_wait FROM v$session WHERE event LIKE 'enq%' ORDER BY seconds_in_wait DESC;

问题现象

高并发插入时序列或索引块出现争用，成为写入瓶颈。

业务场景

高并发订单写入使用序列 SEQ_ORDER 生成主键，且主键索引为普通 B 树。并发插入时大量出现 enq: SQ - contention 与 enq: TX - index contention 等待，TPS 上不去。

-- 序列缓存小，且索引为普通 B 树，同一方向持续插入会在索引右侧形成热点块争用
CREATE SEQUENCE seq_order START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE UNIQUE INDEX pk_order ON t_order(order_id) ONLINE;
-- 出现 enq: SQ - contention 与 index contention

-- 1) 加大序列缓存，减少序列争用（RAC 环境可加 ORDER 或 NOORDER 权衡）
CREATE SEQUENCE seq_order
  START WITH 1 INCREMENT BY 1
  CACHE 10000            -- 一次缓存 1 万个值
  NOORDER;               -- RAC 下不强制全局有序，性能更好

-- 2) 主键索引改为反向索引或哈希分区索引，分散右侧热点
CREATE UNIQUE INDEX pk_order ON t_order(order_id) REVERSE ONLINE;
-- 或对索引做哈希全局分区
-- CREATE UNIQUE INDEX pk_order ON t_order(order_id) GLOBAL PARTITION BY HASH(order_id) PARTITIONS 16;

-- 3) 12c+ 可用 IDENTITY 列简化写法（注意热点问题仍需索引优化）
ALTER TABLE t_order ADD (id NUMBER GENERATED BY DEFAULT AS IDENTITY);

-- 查看序列与索引争用等待
SELECT event, total_waits, ROUND(time_waited_micro/1e6,1) s
  FROM v$system_event WHERE event LIKE 'enq: SQ%' OR event LIKE '%index contention%';

-- 查看序列缓存设置
SELECT sequence_name, cache_size, order_flag, last_number FROM user_sequences WHERE sequence_name='SEQ_ORDER';

-- 查看索引 leaf 块分布是否热点集中
ANALYZE INDEX pk_order VALIDATE STRUCTURE;
SELECT name, height, lf_blks, lf_rows FROM index_stats;

问题现象

用轮询等待其他会话完成，浪费 CPU。

业务场景

作业依赖前置任务完成，开发用 WHILE 循环 + DBMS_LOCK.SLEEP 每 100 毫秒查一次状态表，轮询频繁且延迟高。

-- 高频轮询，既浪费 CPU 又有延迟
LOOP
  SELECT status INTO v_status FROM t_job WHERE job_id = 1;
  EXIT WHEN v_status = 'DONE';
  DBMS_LOCK.SLEEP(0.1);      -- 每 100ms 轮询一次
END LOOP;

-- 方案一：用 DBMS_SCHEDULER 链（Chain）表达任务依赖，由调度器驱动
BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN(chain_name => 'CHAIN_DAILY_ETL');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CHAIN_DAILY_ETL','STEP1','PKG_ETL.LOAD_ODS');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CHAIN_DAILY_ETL','STEP2','PKG_ETL.LOAD_DW');
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CHAIN_DAILY_ETL','TRUE','START STEP1');
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CHAIN_DAILY_ETL','STEP1 COMPLETED','START STEP2');
  DBMS_SCHEDULER.ENABLE('CHAIN_DAILY_ETL');
END;
/

-- 方案二：确需等待时，用事件通知或加长睡眠间隔
LOOP
  SELECT status INTO v_status FROM t_job WHERE job_id = 1;
  EXIT WHEN v_status IN ('DONE','FAILED');
  DBMS_LOCK.SLEEP(5);        -- 间隔拉长到 5 秒，代价可忽略
END LOOP;

-- 方案三：用 DBMS_ALERT 或高级队列做事件驱动（真正无轮询）
-- DBMS_ALERT.SIGNAL('JOB_DONE', '1');  -- 完成方发送信号

-- 确认后台作业与调度链状态
SELECT job_name, enabled, state, run_count, failure_count FROM user_scheduler_jobs;
SELECT chain_name, rule_name, condition FROM user_scheduler_chain_rules;

-- 查看作业运行历史
SELECT job_name, log_date, status, run_duration FROM user_scheduler_job_run_details ORDER BY log_date DESC FETCH FIRST 20 ROWS ONLY;

问题现象

给大表加字段或改结构时锁表，业务中断。

业务场景

需要给 8000 万行的订单表新增一个字段并建索引，运维在业务时段直接执行 ALTER TABLE，导致表被排他锁定，订单接口全部超时，持续 8 分钟。

-- 业务时段直接执行，锁表且无并行
ALTER TABLE t_order ADD (ext_info VARCHAR2(500));
CREATE INDEX idx_order_ext ON t_order(ext_info);
-- 期间表被锁定，DML 全部阻塞

-- 1) 加字段带 ONLINE，避免锁表（11g+）
ALTER TABLE t_order ADD (ext_info VARCHAR2(500)) ONLINE;
-- 注意：加带默认值的 NOT NULL 列在 11g 需特殊处理，12c+ 优化为元数据操作

-- 2) 建索引明确 ONLINE + 并行，放在低峰窗口
CREATE INDEX idx_order_ext ON t_order(ext_info)
  ONLINE PARALLEL 4;
-- 建完恢复并行度设置（并行度会持久在索引上）
ALTER INDEX idx_order_ext NOPARALLEL;

-- 3) 收集新列的统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDER', method_opt => 'FOR COLUMNS SIZE AUTO ext_info');
END;
/

-- 4) 表移动/重定义用 DBMS_REDEFINITION 在线完成
-- EXEC DBMS_REDEFINITION.START_REDEF_TABLE(...);

-- 确认 DDL 是否在线执行（查看 DDL 锁）
SELECT s.sid, s.username, l.type, l.lmode, l.request, o.object_name
  FROM v$lock l JOIN dba_objects o ON l.id1 = o.object_id
  LEFT JOIN v$session s ON l.sid = s.sid
 WHERE l.type = 'TM' AND o.object_name = 'T_ORDER';

-- 查看正在执行的 DDL 进度（长操作）
SELECT sid, opname, ROUND(sofar/totalwork*100,1) pct, time_remaining s
  FROM v$session_longops WHERE totalwork > 0 AND sofar <> totalwork;

-- 查看索引是否带并行度
SELECT index_name, degree, status FROM user_indexes WHERE table_name='T_ORDER';

问题现象

需要改列类型、加分区、改存储结构，普通 DDL 无法在线完成。

业务场景

订单表原本是非分区表，已有 3 亿行，业务要求改为按时间分区且不能停机。直接重建表需要数小时停机，业务不接受。

-- 方案一（不可行）：停机重建
CREATE TABLE t_order_new PARTITION BY RANGE(create_time) (...) AS SELECT * FROM t_order;
-- 需要停业务，且 3 亿行导出导入数小时

-- 方案二：普通 ALTER 不支持在线改分区（部分场景）
ALTER TABLE t_order MODIFY PARTITION BY RANGE(create_time) (...);   -- 大表代价极高且有锁

-- 使用 DBMS_REDEFINITION 在线重定义（业务全程可用）
-- 1) 检查表是否可在线重定义
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T_ORDER', DBMS_REDEFINITION.CONS_USE_PK);
END;
/

-- 2) 创建中间表（新结构：分区 + 目标列定义）
CREATE TABLE t_order_int (
  order_id   NUMBER,
  create_time DATE NOT NULL,
  amount     NUMBER(18,2)
)
PARTITION BY RANGE (create_time)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION p_init VALUES LESS THAN (DATE '2024-01-01'));

-- 3) 启动重定义（后台同步变更数据）
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_ORDER', 'T_ORDER_INT');
END;
/

-- 4) 复制依赖对象（索引、约束、触发器、授权）
DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname => USER, orig_table => 'T_ORDER', int_table => 'T_ORDER_INT',
    num_errors => l_num_errors);
END;
/

-- 5) 同步增量，然后切换（瞬间完成）
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'T_ORDER', 'T_ORDER_INT');
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T_ORDER', 'T_ORDER_INT');
END;
/

-- 6) 收集统计信息并清理中间表
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORDER');
END;
/
DROP TABLE t_order_int PURGE;

-- 查看重定义状态
SELECT * FROM dba_redefinition_objects WHERE object_name = 'T_ORDER';
-- 确认中间表同步情况
SELECT * FROM v$online_redef WHERE object_name = 'T_ORDER';
-- 完成后确认表已分区
SELECT table_name, partitioned, partition_count FROM user_tables WHERE table_name='T_ORDER';

问题现象

缺乏历史对比，性能劣化无法及时发现，等到业务投诉才处理。

业务场景

数据库已稳定运行两年，某次发布后性能逐步劣化，但由于没有基线，无法判断「现在到底是不是比之前慢了」。

-- 只在出问题时才查性能
SELECT * FROM v$sysstat WHERE name = 'parse count (hard)';
-- 无从判断是否劣化

-- 1) 定期采集性能快照基线（建议每日低峰执行，存储历史）
CREATE TABLE perf_baseline (
  snap_date    DATE,
  metric_name  VARCHAR2(100),
  metric_value NUMBER,
  note         VARCHAR2(200)
);

CREATE OR REPLACE PROCEDURE sp_collect_baseline IS
BEGIN
  INSERT INTO perf_baseline
  SELECT TRUNC(SYSDATE), name, ROUND(value,2), 'sysstat'
    FROM v$sysstat
   WHERE name IN ('physical reads','consistent gets','db block gets',
                  'parse count (total)','parse count (hard)','user commits');

  INSERT INTO perf_baseline
  SELECT TRUNC(SYSDATE), 'DB CPU(s)', ROUND(value/1e6,2), 'sys_time_model'
    FROM v$sys_time_model WHERE stat_name = 'DB CPU';

  COMMIT;
END;
/

-- 2) 定期巡检关键项：无主键表、无索引外键、未用索引、失效对象、统计信息过期
SELECT table_name, num_rows FROM user_tables t
 WHERE NOT EXISTS (SELECT 1 FROM user_constraints c
                    WHERE c.table_name = t.table_name AND c.constraint_type = 'P');

-- 3) 对比基线与当前，识别劣化趋势
SELECT metric_name, snap_date, metric_value,
       LAG(metric_value) OVER (PARTITION BY metric_name ORDER BY snap_date) prev_val
  FROM perf_baseline ORDER BY metric_name, snap_date DESC;

-- 4) 建立自动巡检作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_DAILY_BASELINE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sp_collect_baseline; END;',
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0',
    enabled         => TRUE);
END;
/

-- 查看 AWR 基线
SELECT baseline_name, baseline_type, start_snap_id, end_snap_id FROM dba_hist_baseline;
-- 巡检：统计信息过期对象
SELECT table_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics
 WHERE object_type='TABLE' AND (last_analyzed IS NULL OR stale_stats = 'YES');
-- 巡检：失效对象
SELECT object_name, object_type, status FROM user_objects WHERE status <> 'VALID';