本文介绍Oracle数据库中的SQL性能优化方法，包括AUTOTRACE的使用、SQL调优顾问的应用及实时SQL监视的功能。通过这些工具可以有效提升SQL执行效率。
目录：
配套视频课程
SQL*PLUS下使用AUTOTRACE
1.AUTOTRACE简介
AUTOTRACE是SQL*Plus的一项功能，其作用是自动跟踪SQL语句，为SQL 语句生成一个
执行计划并且提供与
该语句的处理有关的统计信息。
SQL*Plus AUTOTRACE 可以用来替代 SQL Trace 使用，AUTOTRACE 的好处是不必设置跟踪文件的格式，并且它将自动为 SQL 语句显示执行计划。AUTOTRACE与执行计划的区别是AUTOTRACE 分析和执行语句；而EXPLAIN PLAN仅分析语句，而不负责执行语句。
AUTOTRACE在SQL*PLUS下执行，使用AUTOTRACE不会产生跟踪文件。
2.配置AUTOTRACE
(1).确保表PLAN_TABLE已经创建，如果没有则如下创建：
(2).确保角色plustrace已经创建，如果没有则如下创建：
3.使用AUTOTRACE
4. AUTOTRACE设置命令
| 序号 | 命令 | 解释 |
| 1 | SET AUTOTRACE OFF | 此为默认值，即关闭Autotrace |
| 2 | SET AUTOTRACE ON | 产生结果集和解释计划并列出统计 |
| 3 | SET AUTOTRACE ON EXPLAIN | 显示结果集和解释计划不显示统计 |
| 4 | SETAUTOTRACE TRACEONLY | 显示解释计划和统计，尽管执行该语句但将看不到结果集 |
| 5 | SET AUTOTRACE TRACEONLY STATISTICS | 只显示统计 |
参见每个设置的现场举例
5. AUTOTRACE STATISTICS含义
| 序号 | 列名 | 解释 |
| 1 | recursive call | 递归调用SQL的个数；Oracle在执行这个SQL的时候，有时候会生成很多额外的SQL语句，这个就称为递归调用 |
| 2 | db block gets | 从buffer cache中读取的block的数量 |
| 3 | consistent gets | 从buffer cache中读取的undo数据的block的数量 |
| 4 | physical reads | 从磁盘读取的block的数量 |
| 5 | redo size | DML生成的redo的大小 |
| 6 | sorts (memory) | 在内存执行的排序量 |
| 7 | sorts (disk) | 在磁盘上执行的排序量,如果memory空间使用不足，是会使用disk的空间的 |
| 8 | bytes sent via SQL*Net to client | 利用sql*net传入到client的字节数； |
| 9 | bytes received via SQL*Net from client | 利用sql*net传出client的字节数； |
使用SQL调优顾问
删除掉bigtab和smalltab上的所有索引：
既然上述语句性能很差，那么怎么优化呢？幸运的是Oracle提供一个工具称为SQL调优顾问。从Oracle 10g起，可以使用SQL调优顾问 (SQL Tuning Advisor ，STA)来获得一个性能很差的语句的优化结果。STA的特点是简单、智能，DBA只需要调用函数就可以给出一个性能很差的语句的优化结果，从而做到有的放矢！
使用DBMS_SQLTUNE包来创建优化任务并阅读优化建议：
创建完成后验证是否完成：
创建完成后验证是否完成：
单击该条目查看优化结果
也可以通过SQL语句来查看结果，此方法是我们最喜欢的方法：
进行优化：
优化后在使用autotrace:
优化后在使用autotrace:
结论：
| 项目 | 优化前 | 优化后 | 倍数 |
| 从持久层获取consistent gets(从buffer cache中读取的undo数据的block的数量) | 21688 | 6950 | 3 |
| 物理读physical reads(从磁盘读取的block的数量) | 21589 | 6928 | 3 |
| 递归调用recursive call | 0 | 1 | N/A |
| 时间(毫秒) | 2070 | 1078 | 2 |
使用STA能快速定位性能瓶颈，从而为性能优化提供了准确的依据！
实时SQL监视
实时SQL监视(real-time SQL Monitorning)是Oracle 11g的另外一个新功能，其作用是允许用户监视正在执行的SQL。默认情况下，当使用并行查询、或者当SQL执行的CPU或I/O超过5秒钟时会自动启动。
也可以使用优化提示强制使用实时SQL监视功能，如下：
select
/*+ monitor */ count(*)
from bigtab a, smalltab b
where a.object_name=b.table_name
如果要强制不使用实时SQL监视功能，则也可以使用优化提示：
select
/*+ no_monitor */ count(*)
from bigtab a, smalltab b
where a.object_name=b.table_name
与实时SQL监视相关的系统视图包括：
uV$SQL_MONITOR
uV$SQL_PLAN_MONITOR
uV$ACTIVE_SESSION_HISTORY
uV$SESSION
uV$SESSION_LONGOPS
uV$SQL
uV$SQL_PLAN
对于刚刚监视的结果，可以使用DBMS包读取：
select dbms_sqltune.report_sql_monitor from dual;
1.实时SQL监视示例1－执行超过5秒的SQL
在OEM中查看监视结果，选择“性能”->“其它监视链接”->“SQL Monitoring”：
查看具体的监视报告(图形化)：
单击“文本报告”，则：
使用DBMS包查看监视结果：
2.实时SQL监视示例2－使用优化提示强制监视
结论：
1.实时SQL监视通过OEM查看其监视报告时，具有更好的图形化的展示效果，因此更加直观
2.如果监视的SQL语句发现具有全表扫描等执行计划的特征，或者CPU时间和I/O时间比较长，则可以与SQL调优顾问接合起来，不但能获知性能瓶颈，而且能获得Oracle推荐的优化策略。
3.实践中，程序员往往不加思考的按照自己的理解和经验编写SQL，此举在90%的项目中存在，从而造成项目产品投用后很快就产生各种性能瓶颈，正确的做法应该是
在准备好足够的测试数据，并且监视每一条SQL并在开发的初始阶段即 优化之。
习题
1.在对索引的限制中，关于NOT和不等于的限制在11g数据库CBO模式下还存在吗，为什么？在RBO模式下呢？
2.如果某个索引中的列具有可空属性，则Oracle执行类似 is null时不会使用索引，其原因是什么？
3.Oralce具有那三种访问路径，其中最快的两种是什么？
4.什么情况下应该使用复合索引，此时使用复合索引比使用多个单个索引具有哪些优势？
5.分别配置并使用SQL优化常用的三种工具：Autotrace、调优顾问和实时监视顾问，复习本课的举例来加深理解。
转载请注明私塾在线【
http://sishuok.com/forum/blogPost/list/6415.html】
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
