【Oracle数据库指南】第45篇：Oracle SQL优化基础

上一篇【第44篇】Oracle性能监控——关键指标与工具下一篇【第46篇】# Oracle内存与参数调优摘要SQL优化是Oracle DBA和开发人员必须掌握的核心技能。一条低效的SQL不仅自身执行慢还会消耗大量CPU、I/O和内存资源影响整个数据库的性能。本文从执行计划的生成与解读入手深入讲解Oracle优化器的工作原理全面介绍EXPLAIN PLAN、SQL Trace与TKPROF、Hint提示、绑定变量等优化工具和技术通过实战案例帮助读者掌握SQL调优的系统方法。一、Oracle优化器基础1.1 CBO基于代价的优化器Oracle从9i起默认使用CBOCost-Based Optimizer基于代价的优化器。CBO通过估算不同执行计划的代价CPU、I/O消耗选择代价最低的执行计划。CBO的工作依赖统计信息表的行数、列的分布、索引的选择性等系统统计信息I/O速度、CPU速度等优化器参数OPTIMIZER_MODE、DB_FILE_MULTIBLOCK_READ_COUNT等-- 查看优化器模式SHOWPARAMETER optimizer_mode;-- OPTIMIZER_MODE参数说明-- ALL_ROWS默认最优总体吞吐量适合批处理-- FIRST_ROWS_n快速返回前n行适合OLTP交互查询1.2 统计信息的重要性统计信息是CBO做出正确决策的基础。统计信息过旧或缺失会导致执行计划错误。-- 收集表统计信息DBMS_STATS是标准方法BEGINdbms_stats.gather_table_stats(ownnameSCOTT,tabnameEMP,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,cascadeTRUE);END;/-- 查看表的统计信息上次更新时间SELECTtable_name,num_rows,blocks,last_analyzedFROMdba_tablesWHEREownerSCOTTORDERBYlast_analyzed;二、执行计划分析2.1 EXPLAIN PLANEXPLAIN PLAN是分析SQL执行计划最基本的工具无需实际执行SQL。-- 生成执行计划EXPLAINPLANFORSELECTe.empno,e.ename,d.dnameFROMemp eJOINdept dONe.deptnod.deptnoWHEREe.sal2000;-- 查看执行计划SELECT*FROMTABLE(dbms_xplan.display(table_namePLAN_TABLE,statement_idNULL,formatALL-- ALL包含详细信息行数估算、谓词信息));典型执行计划输出Plan hash value: 1546239280 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 276 | 5 (20)| 00:00:01 | | 1 | HASH JOIN | | 6 | 276 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 | |* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 180 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | IDX_SAL | 6 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information: 3 - filter(E.DEPTNOD.DEPTNO) 4 - access(E.SAL2000)2.2 执行计划中的关键操作操作类型说明性能影响TABLE ACCESS FULL全表扫描小表OK大表需关注TABLE ACCESS BY INDEX ROWID通过ROWID访问行效率高INDEX UNIQUE SCAN唯一索引扫描返回单行最优INDEX RANGE SCAN索引范围扫描效率好INDEX FULL SCAN全索引扫描比全表扫描通常更好NESTED LOOPS嵌套循环连接适合小数据集关联HASH JOIN哈希连接适合大数据集关联MERGE JOIN排序合并连接适合已排序数据SORT ORDER BY排序操作关注是否产生磁盘排序读取执行计划的顺序从内到外、从右到左先执行缩进最深的节点。2.3 执行计划中的问题识别-- 查看带实际执行信息的执行计划需先执行SQL-- 首先执行目标SQLSELECT*FROMempWHEREsal2000;-- 从游标缓存中查看实际执行计划SELECTsql_idFROMv$sqlWHEREsql_textLIKE%SELECT * FROM emp WHERE sal 2000%;SELECT*FROMTABLE(dbms_xplan.display_cursor(sql_idyour_sql_id,cursor_child_no0,formatALLSTATS LAST-- 显示实际行数vs估算行数));ALLSTATS输出中的关键列E-Rows优化器估算的行数A-Rows实际返回的行数若两者相差悬殊说明统计信息不准确三、SQL Trace与TKPROF3.1 SQL TraceSQL Trace对SQL执行过程进行详细追踪记录解析时间、执行时间、等待事件等。-- 方式一对当前会话开启TraceALTERSESSIONSETSQL_TRACETRUE;ALTERSESSIONSETTIMED_STATISTICSTRUE;-- 执行需要追踪的SQL-- ...ALTERSESSIONSETSQL_TRACEFALSE;-- 方式二对特定会话开启TraceDBA操作-- 先找到目标会话的SID和SERIAL#SELECTsid,serial#, username FROM v$session WHERE username SCOTT;-- 开启该会话的TraceEXECUTEdbms_system.set_sql_trace_in_session(sid45,serial12345,sql_traceTRUE);-- 方式三使用DBMS_SESSIONALTERSESSIONSETEVENTS10046 trace name context forever, level 12;-- Level说明-- 1: 基本trace-- 4: 包含绑定变量值-- 8: 包含等待事件-- 12: 同时包含绑定变量和等待事件最推荐查找Trace文件位置-- 查看trace文件目录SHOWPARAMETER user_dump_dest;-- 查看当前会话的trace文件名SELECTVALUEFROMv$diag_infoWHEREnameDefault Trace File;3.2 TKPROF分析Trace文件TKPROF将原始trace文件转化为可读的分析报告。# 基本用法tkprof /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_12345.trc\/tmp/output.txt# 高级选项tkprof /path/to/trace.trc /tmp/output.txt\explainscott/tiger\# 显示执行计划sortprsela\# 按解析时间排序print20# 只显示前20条SQL# 常用sort选项# exeela按执行经过时间排序最常用# prsela按解析时间排序# fchela按取数时间排序# execpu按执行CPU时间排序# exedsk按磁盘读排序找I/O大户TKPROF报告关键指标SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno d.deptno AND e.sal 2000 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.01 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.02 15 89 0 6 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.01 0.03 15 89 0 6 Misses in library cache during parse: 0 -- 0表示软解析 Optimizer mode: ALL_ROWS Rows examined: 6, disk reads: 15, consistent gets: 89四、SQL Hint提示4.1 Hint的作用与注意事项Hint是Oracle特有的注释语法用于指导优化器使用特定的执行计划。⚠️使用原则Hint是建议而非命令优化器有权忽略无效的Hint不要作为长期解决方案首先应解决统计信息和SQL写法问题业务代码中滥用Hint会带来维护难题4.2 常用Hint分类访问路径Hint-- 强制全表扫描小表或测试用SELECT/* FULL(e) */*FROMemp eWHEREempno7788;-- 强制使用特定索引SELECT/* INDEX(e idx_emp_sal) */*FROMemp eWHEREsal2000;-- 强制使用索引快速全扫描SELECT/* INDEX_FFS(e pk_emp) */COUNT(*)FROMemp e;-- 强制不使用索引SELECT/* NO_INDEX(e idx_emp_sal) */*FROMemp eWHEREsal2000;连接顺序Hint-- 强制指定连接顺序先emp再deptSELECT/* LEADING(e d) */e.ename,d.dnameFROMemp e,dept dWHEREe.deptnod.deptno;-- 使用有序连接按FROM子句顺序SELECT/* ORDERED */e.ename,d.dnameFROMemp e,dept dWHEREe.deptnod.deptno;连接方式Hint-- 强制使用嵌套循环SELECT/* USE_NL(d) */e.ename,d.dnameFROMemp e,dept dWHEREe.deptnod.deptno;-- 强制使用哈希连接SELECT/* USE_HASH(d) */e.ename,d.dnameFROMemp e,dept dWHEREe.deptnod.deptno;-- 强制使用排序合并SELECT/* USE_MERGE(d) */e.ename,d.dnameFROMemp e,dept dWHEREe.deptnod.deptno;并行查询Hint-- 开启并行查询大表分析查询SELECT/* PARALLEL(t, 8) */COUNT(*)FROMbig_table t;-- 串行执行禁用并行SELECT/* NO_PARALLEL(t) */*FROMbig_table t;其他常用Hint-- 缓存小表反复使用的小表SELECT/* CACHE(d) */*FROMdept d;-- 优化目标HintSELECT/* ALL_ROWS */*FROMemp;-- 吞吐量优先SELECT/* FIRST_ROWS(10) */*FROMemp;-- 快速返回前10行-- 追加模式INSERT时绕过Buffer CacheINSERT/* APPEND */INTOhistory_tableSELECT*FROMcurrent_table;五、绑定变量5.1 为什么要用绑定变量每次SQL执行Oracle都要经历**解析Parse→执行Execute→取数Fetch**三个阶段。解析分为硬解析Hard Parse首次解析生成全新执行计划消耗大量资源软解析Soft Parse在库缓存中找到相同SQL的执行计划复用之不用绑定变量的问题-- 以下三条SQL文本不同每条都触发硬解析SELECT*FROMempWHEREempno7788;SELECT*FROMempWHEREempno7900;SELECT*FROMempWHEREempno7369;使用绑定变量后-- PL/SQL方式DECLAREv_empno NUMBER :7788;BEGINEXECUTEIMMEDIATESELECT * FROM emp WHERE empno :empnoUSINGv_empno;END;/-- 应用程序Java JDBC示例PreparedStatement psconn.prepareStatement(SELECT * FROM emp WHERE empno ?);ps.setInt(1,7788);ResultSet rsps.executeQuery();5.2 监控硬解析问题-- 检查当前硬解析率告警阈值硬解析率 5%SELECTROUND(hp/(hpsp)*100,2)hard_parse_ratio,hp hard_parses,sp soft_parsesFROM(SELECTSUM(DECODE(name,parse count (hard),value,0))hp,SUM(DECODE(name,parse count (total),value,0))-SUM(DECODE(name,parse count (hard),value,0))spFROMv$sysstatWHEREnameIN(parse count (hard),parse count (total)));-- 找出未使用绑定变量的高频SQL共享游标少的SQLSELECTforce_matching_signature,COUNT(*)versions,sql_textFROMv$sqlWHEREforce_matching_signature!0GROUPBYforce_matching_signature,sql_textHAVINGCOUNT(*)20-- 同一逻辑SQL有超过20个变体ORDERBYversionsDESCFETCHFIRST10ROWSONLY;5.3 游标共享Cursor Sharing对于无法修改应用代码的情况可以使用CURSOR_SHARING参数强制绑定变量-- 查看当前设置SHOWPARAMETER cursor_sharing;-- 修改为FORCE将字面量替换为绑定变量ALTERSYSTEMSETcursor_sharingFORCE;-- 或仅对当前会话ALTERSESSIONSETcursor_sharingFORCE;-- CURSOR_SHARING参数值-- EXACT默认只共享完全相同的SQL-- SIMILAR类似SQL共享已废弃-- FORCE强制替换字面量大量硬解析时的应急方案⚠️ CURSOR_SHARINGFORCE 可能影响某些SQL的执行计划不是银弹需在测试环境验证后使用。六、SQL优化实战案例案例一消除全表扫描原始SQL执行时间45秒SELECT*FROMordersWHEREorder_dateSYSDATE-30ANDstatusPENDING;执行计划TABLE ACCESS FULL | ORDERS | 5000000 rows scanned分析与优化-- 步骤1确认列的选择性SELECTCOUNT(DISTINCTstatus)||/||COUNT(*)stat_ratioFROMorders;-- 结果5/5000000status选择性低不适合单列索引-- 步骤2确认order_date的选择性SELECTCOUNT(*)FROMordersWHEREorder_dateSYSDATE-30;-- 结果15000行选择性很好-- 步骤3创建复合索引高选择性列在前CREATEINDEXidx_orders_date_statusONorders(order_date,status);-- 步骤4验证新执行计划EXPLAINPLANFORSELECT*FROMordersWHEREorder_dateSYSDATE-30ANDstatusPENDING;SELECT*FROMTABLE(dbms_xplan.display);优化后执行计划INDEX RANGE SCAN | IDX_ORDERS_DATE_STATUS | 15000 rows TABLE ACCESS BY INDEX ROWID | ORDERS | 2000 rows returned执行时间从45秒降至0.3秒。案例二隐式类型转换导致索引失效问题SQL-- 假设PHONE_NUMBER列类型为VARCHAR2SELECT*FROMcustomersWHEREphone_number13800138000;-- ^ 数值类型将触发隐式转换问题原因Oracle会在VARCHAR2列上隐式应用TO_NUMBER函数导致索引失效。修复方案-- 确保类型匹配SELECT*FROMcustomersWHEREphone_number13800138000;案例三子查询改写为JOIN低效写法相关子查询逐行执行SELECTempno,ename,sal,(SELECTdnameFROMdeptWHEREdeptnoe.deptno)dept_nameFROMemp e;高效写法JOIN一次执行SELECTe.empno,e.ename,e.sal,d.dnameFROMemp eJOINdept dONe.deptnod.deptno;七、SQL优化检查清单检查项优化建议是否有全表扫描检查是否缺少索引或索引未被使用索引是否被使用检查列上是否有函数、隐式转换、前导通配符统计信息是否新鲜最近一次ANALYZE时间是否2周是否使用绑定变量检查v$sql中是否有大量相似SQL连接方式是否合理大表用HASH JOIN小表驱动用NESTED LOOPS连接顺序是否合理选择性最高的条件/表作为驱动表是否有不必要的排序检查SORT ORDER BY和SORT GROUP BYSELECT *是否必要按需取列减少数据传输量IN子句是否过长IN超过1000个值改用临时表JOIN是否有隐式转换WHERE条件的数据类型与列类型一致八、总结SQL优化是一门理论与实践并重的技艺先理解再优化通过EXPLAIN PLAN和SQL Trace了解SQL的真实执行路径统计信息是基础保持统计信息及时更新让优化器做出正确决策索引是双刃剑合理的索引是性能的关键但过多的索引会拖慢DML操作绑定变量是底线消除硬解析是提升高并发OLTP性能的最简单手段慎用HintHint解决的是当下的问题治标不治本上一篇【第44篇】Oracle性能监控——关键指标与工具下一篇【第46篇】# Oracle内存与参数调优参考资料Oracle Database Performance Tuning Guide 11g Release 2Oracle Database SQL Tuning Guide 11g Release 2Oracle Database ReferenceVS Q L , V SQL, VSQL,VSYSSTAT等视图说明Jonathan Lewis《Cost-Based Oracle Fundamentals》