执行计划从入门到会看——全表扫描、索引、HASH JOIN一图讲清问一个DBA这个SQL为什么慢他90%的回复始于你先看一下执行计划。这篇文章用社保系统的真实SQL从EXPLAIN PLAN到读懂执行计划输出不背书就看会。文章目录执行计划从入门到会看——全表扫描、索引、HASH JOIN一图讲清一、一句话理解执行计划二、生成执行计划三、看懂一个执行计划输出四、认得几种访问方式五、社保系统的三个诊断案例六、两个最有用的HINT七、检查某条SQL实际走了什么路径一、一句话理解执行计划执行计划 Oracle告诉你这个SQL我打算怎么跑的路线图。你的SQL是声明式的——你只说了要什么。Oracle拿到SQL后做的第一件事是选择一条访问路径——先走哪张表、用什么索引、怎么JOIN。执行计划就是这张路线图。二、生成执行计划-- 方法一EXPLAIN PLAN DBMS_XPLANEXPLAINPLANFORSELECTa.xm,b.yf,c.ylbf01FROMPERSON_INFO a,PAYMENT_HISTORY b,BANK_RECEIPT cWHEREa.id_cardb.id_cardANDb.unit_idc.unit_idANDb.pay_year1991;SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY());-- 方法二查看正在执行的真实执行计划更可靠SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID,NULL,ALLSTATS LAST));三、看懂一个执行计划输出---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 500 | 25000 | 152 | | 1 | HASH JOIN | | 500 | 25000 | 152 | | 2 | TABLE ACCESS FULL | PAYMENT_HISTORY | 500 | 10000 | 46 | | 3 | HASH JOIN | | 1000 | 30000 | 106 | | 4 | TABLE ACCESS FULL | PERSON_INFO | 1000 | 15000 | 36 | | 5 | INDEX FAST FULL SCAN | BANK_RECEIPT_DWBH | 5000 | 75000 | 70 | ----------------------------------------------------------------------------从下往上读从里往外读Step 5: 全索引扫描 BANK_RECEIPT_DWBH 索引 Step 4: 全表扫描 PERSON_INFO Step 3: 把 Step4 和 Step5 的结果做 HASH JOIN Step 2: 全表扫描 PAYMENT_HISTORY Step 1: 把 Step2 和 Step3 的结果做 HASH JOIN Step 0: 返回结果四、认得几种访问方式操作含义好坏TABLE ACCESS FULL全表扫描——从第一行读到最后一页大表上是坏事INDEX UNIQUE SCAN唯一索引扫——直接定位一行最优INDEX RANGE SCAN范围索引扫——按范围定位正常INDEX FULL SCAN全索引扫——遍历索引比全表扫好但不如范围扫INDEX FAST FULL SCAN多块读全索引扫当索引包含所需全部列时出现TABLE ACCESS BY INDEX ROWID通过索引找到ROWID再回表正常HASH JOIN哈希连接小表建哈希表大表探测两张大表JOIN时用NESTED LOOPS嵌套循环——外表的每行去查内表外表少行有索引时用MERGE JOIN排序合并连接两表已排序时用五、社保系统的三个诊断案例案例一全表扫描 PAYMENT_HISTORY398MBSELECT*FROMPAYMENT_HISTORYWHEREid_card460027660601003ANDpay_year1991;执行计划显示TABLE ACCESS FULL——没有走索引。查原因id_card列虽然建了索引但索引建的是id_cardunit_idpay_year的联合索引。单独查SFZH时前置列的DW位没有被提供——索引失效退化为全表扫描。修法改SQL加上DWBH条件或者建一个独立索引CREATE INDEX idx_h0_sfzh ON PAYMENT_HISTORY(id_card)。案例二NESTED LOOPS 跑了三分钟SELECTa.xm,b.ylbf01FROMPERSON_INFO a,PAYMENT_HISTORY bWHEREa.id_cardb.id_cardANDb.pay_year1991;执行计划显示NESTED LOOPS——外表扫描 PERSON_INFO 130万行每行去查一次内表 PAYMENT_HISTORY。130万次随机IO跑十分钟。修法给两个表分别加/* USE_HASH(a b) */HINT让Oracle改用HASH JOIN——先对两张表的sfzh分别建哈希表一次全表扫描匹配完成。耗时降到2秒。案例三INDEX FAST FULL SCAN 不走但全表扫描走了执行计划选择了TABLE ACCESS FULL而不是INDEX FAST FULL SCAN——因为SELECT里要的列太多了索引只覆盖了5列但SQL要了20列。Oracle算了一笔账回表20次的总成本高于直接全表扫描。修法不要SELECT *只查需要的那几列——索引覆盖的列数量 SELECT列数时Oracle会自动选索引。六、两个最有用的HINT-- 强制走指定的索引SELECT/* INDEX(PAYMENT_HISTORY idx_h0_sfdwnf) */*FROMPAYMENT_HISTORYWHERE...;-- 强制HASH JOINSELECT/* USE_HASH(a b) */a.xm,b.ylbf01FROMPERSON_INFO a,PAYMENT_HISTORY bWHERE...;HINT不是长久之计——索引建对了比HINT管用。HINT是用来临时应急的。七、检查某条SQL实际走了什么路径-- 查缓存中的执行计划SELECTSQL_ID,CHILD_NUMBER,PLAN_HASH_VALUEFROMV$SQLWHERESQL_TEXTLIKE%PAYMENT_HISTORY%;-- 看统计信息实际跑了多少次、产生多少IOSELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID,NULL,ALLSTATS LAST));E-Rows是Oracle估计的行数A-Rows是实际跑出来的行数。如果差距超过10倍——统计信息过期了需要重新收集EXECDBMS_STATS.GATHER_TABLE_STATS(,PAYMENT_HISTORY);✅ 亮点用社保系统的398MB大表做真实案例——全表扫描、NESTED LOOPS、索引覆盖失败——每条都有SQL和修法。扩展方向SQL Profile自动优化、自适应执行计划、12c的自动索引特性。