SQL执行计划与优化器Oracle是怎么决定该怎么查的从一个实际场景说起你写了一条SQLSELECTe.employee_name,d.dept_nameFROMemployees e,departments dWHEREe.dept_idd.dept_idANDe.salary50000;Oracle要执行它但有很多种方式先扫描employees表过滤工资再去departments表匹配先连接两张表再过滤工资用索引还是全表扫描用嵌套循环连接还是哈希连接优化器Optimizer就是做这个决策的。它会分析所有可能的执行路径估算成本选一条它认为最快的。执行计划就是优化器选出来的那条路径。理解执行计划是DBA调优的最核心技能——你不看执行计划就调优等于闭着眼睛开车。两代优化器RBO vs CBORBORule-Based Optimizer—— 基于规则Oracle 10g之前的默认模式。它有一套固定的优先级规则1. 单行通过ROWID访问 ← 最快 2. 单行通过唯一索引 3. 组合索引完全匹配 4. 范围扫描 ... 15. 全表扫描 ← 最慢RBO的问题它不看数据。一张表有100行和1000万行RBO做出的决策可能完全一样。这在复杂场景下经常选错执行路径。CBOCost-Based Optimizer—— 基于成本10g之后的唯一选择。CBO的核心逻辑收集统计信息表有多少行列的值分布如何索引的选择性怎样估算每条路径的成本CPU消耗 I/O消耗选择成本最低的路径关键点CBO的决策质量完全依赖于统计信息的准确性。如果统计信息过期了表已经从100行增长到100万行但统计信息还是100行的CBO就会做出错误决策。[!important] 实战经验你在工作中遇到某条SQL突然变慢第一件事应该检查统计信息是否过期。很多时候跑一次DBMS_STATS.GATHER_TABLE_STATS就能解决问题。如何查看执行计划方法一EXPLAIN PLANEXPLAINPLANFORSELECT*FROMemployeesWHEREdept_id10;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY);这是预估的执行计划不需要真正执行SQL。方法二查看实际执行计划SELECT/* GATHER_PLAN_STATISTICS */*FROMemployeesWHEREdept_id10;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,ALLSTATS LAST));这是真实执行后的计划包含实际的行数、时间等信息。[!tip] 建议预估计划和实际计划可能不一样。调优时尽量看实际执行计划因为它反映了真实情况。方法三通过V$SQL查历史SQL的执行计划-- 先找到SQL_IDSELECTsql_id,sql_textFROMv$sqlWHEREsql_textLIKE%employees%;-- 再看执行计划SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id_here,NULL,ALLSTATS));这个在排查线上问题时最常用——你不需要重新执行SQL直接看内存里缓存的执行计划。读懂执行计划来看一个实际的执行计划输出------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5 | | | 1 | NESTED LOOPS | | 10 | 720 | 5 | 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 520 | 3 | 00:00:01 | |* 3 | INDEX RANGE SCAN | EMP_DEPT_IX | 10 | | 1 | 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 20 | 1 | 00:00:01 | |* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 | 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information: 3 - access(E.DEPT_ID10) 5 - access(D.DEPT_IDE.DEPT_ID)怎么读核心原则执行顺序不是从上到下而是最缩进的先执行同级从上到下。上面这个计划的执行顺序是Id 3用EMP_DEPT_IX索引扫描找到dept_id10的行对应的ROWIDId 2通过ROWID回表从EMPLOYEES取完整数据Id 5对每一行结果用DEPT_PK索引查DEPARTMENTS表Id 4通过ROWID回表从DEPARTMENTS取数据Id 1嵌套循环连接组合结果Id 0返回最终结果关注什么列含义看什么Operation具体操作是全表扫描还是索引扫描Rows预估行数预估和实际差距大吗差距大说明统计信息有问题Cost优化器估算的成本哪个步骤成本最高Bytes预估数据量是否处理了过多数据[!warning] 重要Rows列是最需要关注的。如果预估是10行但实际执行了100万行那优化器的决策一定是错的——它以为数据很少所以选了嵌套循环但实际数据量巨大应该用哈希连接。核心访问路径1. 全表扫描TABLE ACCESS FULL读取表的所有数据块从头到尾。不一定是坏事小表或者需要返回大量数据时全表扫描可能比走索引更快什么时候是问题大表但只需要几行数据时全表扫描就很浪费2. 索引唯一扫描INDEX UNIQUE SCAN通过唯一索引精确定位一行。最快的索引访问方式。-- 典型场景主键查找SELECT*FROMemployeesWHEREemployee_id100;3. 索引范围扫描INDEX RANGE SCAN通过索引找到一个范围内的多行。-- 典型场景SELECT*FROMemployeesWHEREdept_id10;SELECT*FROMemployeesWHEREhire_dateBETWEEN2020-01-01AND2020-12-31;4. 索引全扫描INDEX FULL SCAN扫描索引的所有叶子节点按顺序。比全表扫描快因为索引比表小。5. 索引快速全扫描INDEX FAST FULL SCAN类似索引全扫描但不按顺序读取可以并行。当查询的列全部在索引中覆盖索引时出现。6. 回表TABLE ACCESS BY INDEX ROWID通过索引找到ROWID后再去表里取完整行。[!note] 关键理解索引扫描 回表是一个组合操作。如果索引扫描返回了大量ROWID每个都要回表一次I/O反而可能比全表扫描还多。这就是为什么当查询需要返回大比例数据时Oracle宁可全表扫描。通常超过表数据的5%-15%时优化器就倾向于全表扫描。核心连接方式当SQL涉及多表关联时Oracle有三种主要连接方式1. 嵌套循环连接NESTED LOOPS原理像两层for循环对于驱动表的每一行: 去被驱动表中查找匹配的行适合驱动表结果集很小被驱动表有高效索引不适合两张表都很大结果集多2. 哈希连接HASH JOIN原理把较小的表读入内存建哈希表扫描大表对每一行算哈希值去匹配适合两张表都比较大等值连接不适合内存不够小表放不进PGA非等值连接、、LIKE[!note] 联系前文还记得01篇讲的PGA吗哈希连接的哈希表就是在PGA中构建的。如果PGA不够哈希表会溢出到临时表空间磁盘性能暴跌。所以PGA_AGGREGATE_TARGET参数对哈希连接的性能有直接影响。3. 排序合并连接SORT MERGE JOIN原理对两张表分别排序像拉链一样合并适合非等值连接或者数据已经有序不适合排序成本高时大数据量、无索引辅助排序三种连接对比场景最佳连接原因小表驱动大表大表有索引NESTED LOOPS索引查找效率高两个大表等值连接HASH JOIN建哈希比嵌套循环高效非等值连接SORT MERGE / NESTED LOOPS哈希连接不支持非等值数据本身有序SORT MERGE免去排序成本统计信息CBO的命脉前面反复提到统计信息现在来说清楚它到底是什么。表统计信息NUM_ROWS表有多少行BLOCKS占用多少个数据块AVG_ROW_LEN平均行长度列统计信息NUM_DISTINCT去重后有多少个不同的值LOW_VALUE/HIGH_VALUE最小值和最大值NUM_NULLS多少个NULL直方图Histogram值的分布情况索引统计信息BLEVEL索引树的层级LEAF_BLOCKS叶子节点数CLUSTERING_FACTOR聚簇因子——衡量索引顺序和表的物理存储顺序有多匹配[!important] 聚簇因子这个值经常被忽视但极其重要。如果聚簇因子接近表的行数说明索引顺序和数据存储顺序差异很大每次索引回表都可能读不同的数据块I/O代价极高。反之如果接近数据块数说明排列很紧凑回表效率高。收集统计信息-- 收集表统计信息包含索引和列统计BEGINDBMS_STATS.GATHER_TABLE_STATS(ownnameSCOTT,tabnameEMPLOYEES,estimate_percentDBMS_STATS.AUTO_SAMPLE_SIZE,method_optFOR ALL COLUMNS SIZE AUTO,cascadeTRUE);END;/-- 收集整个Schema的统计信息BEGINDBMS_STATS.GATHER_SCHEMA_STATS(ownnameSCOTT,estimate_percentDBMS_STATS.AUTO_SAMPLE_SIZE);END;/查看统计信息-- 查看表统计SELECTtable_name,num_rows,blocks,avg_row_len,last_analyzedFROMuser_tablesWHEREtable_nameEMPLOYEES;-- 查看列统计SELECTcolumn_name,num_distinct,num_nulls,histogramFROMuser_tab_col_statisticsWHEREtable_nameEMPLOYEES;-- 查看索引统计SELECTindex_name,blevel,leaf_blocks,clustering_factorFROMuser_indexesWHEREtable_nameEMPLOYEES;Oracle自动收集机制Oracle有一个自动任务AUTO_TASK会在维护窗口默认是晚上10点到凌晨2点的工作日周末全天自动收集过期的统计信息。但有些场景需要你手动处理大批量数据加载后分区表新增分区后统计信息被锁定的表实战一个调优案例场景某条查询突然从0.1秒变成了30秒。排查步骤第一步看执行计划变了没-- 通过AWR找到之前的执行计划SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id_here));-- 对比现在的SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id_here));发现之前用的是INDEX RANGE SCANNESTED LOOPS现在变成了TABLE ACCESS FULLHASH JOIN。第二步为什么计划变了-- 检查统计信息最后更新时间SELECTtable_name,num_rows,last_analyzedFROMuser_tablesWHEREtable_nameIN(EMPLOYEES,DEPARTMENTS);发现EMPLOYEES表的last_analyzed是3个月前当时只有1万行现在已经有500万行了。第三步更新统计信息BEGINDBMS_STATS.GATHER_TABLE_STATS(SCOTT,EMPLOYEES,cascadeTRUE);END;/第四步验证重新执行SQL执行计划恢复正常查询回到0.1秒。[!note] 经验总结SQL性能突变的排查顺序执行计划是否变化统计信息是否过期数据量是否暴增是否有锁等待或资源竞争系统资源CPU、I/O、内存是否有瓶颈思考题你有新的理解吗oo你工作中遇到过SQL突然变慢的情况吗回想一下当时的排查过程是什么样的现在看最可能的原因是什么提示结合执行计划变化和统计信息来分析。假设一张表有1000万行你要查其中的10行数据。走索引一定比全表扫描快吗如果索引的聚簇因子很高接近行数会怎样提示想想索引扫描 回表的I/O模式。为什么哈希连接只支持等值连接不支持、这样的非等值连接提示想想哈希表的查找原理。在你的工作环境中统计信息是自动收集的还是手动收集的有没有遇到过因为统计信息不准确导致的问题下一篇预告深入Oracle索引——B-Tree索引的内部结构、不同索引类型的选择、索引设计原则以及那些以为建了索引就万事大吉的常见误区。