本文探讨了SQL优化的重要性，特别是SQL语句对数据库性能的影响，以及分析SQL执行计划的必要性。讲解了explainplanfor、autotrace和statistics_level=all等查看方式，并详细解析了PL/SQL工具中的执行计划分析，包括Cost、连接方式和表访问策略。最后，强调了结合实际测试确定优化方案的重要性。
为什么要进行SQL优化
应用程序运行缓慢的原因通常分为两个方面：
源代码和SQL语句。对应用程序的优化也通常针对源代码和SQL语句。源代码的优化，一方面可能会涉及对程序逻辑的改变，这在时间成本和风险上代价很高，尤其是正在使用的系统；另一方面对数据库系统性能的提升收效有限。SQL优化，无论是时间成本、风险、提升收效都明显好于源代码优化，原因如下：
1、SQL语句是对数据库进行操作的唯一途径，应用程序的执行最终还是SQL语句的执行，SQL语句的效率对数据库系统的性能起到决定性作用。2、SQL语句独立于程序设计逻辑，对SQL语句进行优化不会影响程序逻辑，相对于源代码的优化，SQL优化在时间成本和风险上代价都较低。3、SQL语句可以有不同的写法，不同的下发在性能上的差异可能很大。
为什么要分析SQL执行计划
执行计划是一条查询语句在ORACLE中的执行过程或访问路径的描述。如果要优化SQL，通常先分析SQL的执行计划，查看SQL的每一步执行是否存在问题，通过执行计划定位性能问题，然后通过建立索引，修改SQL解决问题。
SQL执行计划常用的查看方式
1. explain plan for
(1)使用方法：解释执行计划explain plan for SQL，显示执行计划select * from table(dbms_xplan.display)，在sql*plus或者PL/SQL Developer打开的command window中，执行如下命令：
1）explain plan for select * from dual; +回车
2）select * from table(dbms_xplan.display); +回车
(2)优点：无需真正执行，快捷方便。 (3)缺点：没有输出相关统计信息，无法判断处理了多少行，无法判断表执行了多少次。
2. set autotrace on (1)使用方法：首先必须采用用Oracle的sqlplus登陆sys账号 ，产生结果集和解释计划并列出统计set autotrace on，执行SQL语句并输出执行计划SQL (2)优点：可以输出运行时的相关统计信息；虽然要等语句执行完输出执行计划，但是可以用traceonly开关来控制返回结果不打屏输出。 (3)缺点：必须要等SQL执行完，才出结果；无法看到表被访问了多少次。
3. statistics_level=all
(1)使用方法：收集与SQL执行相关的详细统计信息alter session set statistics_level=all，执行SQL语句SQL，输出执行计划select * from table(dbms_xplan.display_cursor(null,null,‘allstats last’))，如下图所示。 (2)优点：可以清晰的从starts得出表被访问了多少次；可以从E-Rows和A-Rows得到预测的行数和真实的行数，从而能够准确判断Oracle评估是否准确；虽然没有准确输出相关统计信息，但是执行计划中的Buffers是真实的逻辑读的数值。 (3)缺点：必须要等SQL执行完，才出结果；无法控制结果打屏输出；看不出递归调用，看不出物理读的数值。 4. dbms_xplan.display_cursor (1)使用方法：执行SQL，查询SQL对应的sql_id，即select SQL_TEXT,SQL_ID from v$sql where sql_text like ‘SQL%’ order by last_load_time，输出执行计划select * from table( dbms_xplan.display_cursor(‘sql_id’) )，如下图所示。 (2)优点：知道sql_id即可得到执行计划，无需执行；可得到真实的执行计划。(3)缺点：没有输出运行的统计相关信息，无法判断处理了多少行，无法判断表被访问了多少次。每种查看方式侧重点不同，可以根据实际需要选用不同的查看方式。下面着重介绍explain plan for方式分析执行计划，PL/SQL中快捷键F5功能就是封装了explain plan for，且PL/SQL的可视化效果更好，因此以PL/SQL为例进行分析。
使用PL/SQL工具进行SQL优化
在PL/SQL中写好一段SQL后，按F5，PL/SQL会自动打开执行计划窗口，显示该SQL的执行计划。
执行计划的常用列字段解释：
Description：SQL的执行顺序
Id：执行编号
Object owner：对象的所有者
Object name：对象名称
Cost：成本花费
Cardinality：结果集需要扫描的行数
Bytes：执行该步骤返回的字节数
1、查看总Cost，获取资源耗费的总体成本 执行Id为0所对应的Cost值，反应了运行这段SQL的总体估计成本，单看这个总成本没有实际意义，但是可以拿它和逻辑相同不同执行计划的SQL的总体Cost进行比较，Cost低的执行计划性能高些。 2、查看Description，按照从左到右，从上至下的方法，了解执行计划的执行顺序 执行计划按照层次逐步缩进，从左至右看，缩进最多的最先执行，缩进量相同，则按照从上而下的方法判断执行顺序。每一个执行步骤都对应一个Cost，可从单步Cost的高低，以及单步对应的Cardinality，来分析表的访问方式，判断连接顺序和连接方式是否合理。 3、分析表的访问方式 表的访问方式在Description列可以查看。通常有三种访问方式：全表扫描(TABLE ACCESS FULL)、ROWID扫描(TABLE ACCESS BY INDEX ROWID)和索引扫描(TABLE ACCESS SCAN)。如果表上存在选择性很好的索引，却走了全表扫描，而且是大表的全表扫描，就说明表的访问方式可能存在问题；如果大表没有合适的索引而走了全表扫描，就需要分析能否建立索引或者选择更合适的表连接方式和连接顺序提高效率。 4、分析表的连接方式和连接顺序 表的连接顺序就是以哪张表作为驱动表来连接其他表的先后访问顺序。 表的连接方式(Description列)有四种连接方式：嵌套循环(NESTED LOOPS)、哈希连接(HASH JOIN)、排序-合并连接(SORT MERGE JOIN)和迪科尔积(CARTESIAN PRODUCT)。常见的是嵌套循环和哈希连接。 嵌套循环：最简单的连接方式。ORACLE会检索驱动表的数据，一条一条的代入被驱动表，查找满足WHERE条件的所有数据，因此被驱动表中可用索引的选择性越好，嵌套循环连接的性能就越高。 哈希连接：先将驱动表的数据按照条件字段以散列的方式放入内存，然后在内存中匹配满足条件的行。哈希连接在表的数据量较大，表中没有合适的索引可用时比嵌套循环的效率要高。
小 结
执行计划只是SQL运行前可能的执行方式，实际运行时可能因为软硬件环境的不同而有所改变，且Cost高的执行计划，实际运行起来速度不一定差，我们平时要结合执行计划和实际测试的运行时间来确定一个执行计划的好坏。多数情况下，表的连接使用的都是嵌套循环，尤其是在索引可用性好的情况下，嵌套循环是最好的。当ORACLE访问的数据表大时，索引的成本较高或者没有合适索引时，可以使用哈希连接来提高效率。排序-合并连接性能最差，但是存在排序需求或者存在非等值连接无法使用哈希连接时，排序-合并连接的效率会相对哈希连接或嵌套循环要好。
扫一扫
1831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
