分类： Oracle
2008-05-21 11:10:21
10G New Features Series 2: Introduction to SQL Tuning Advisor
参考的文档:
Oracle10G Performance Tuning Guide
Oracle10G New Features
1. 简介
一直以来，如何有效的诊断和监控高负载的SQL对于DBA来说不是件容易的事情.但是从Oracle 10G 开始, ADDM的引入使得由数据库自身参与监控成为可能.尽管通过ADDM发现出的高负载的SQL可能只会占全部SQL的很小一部分比例,但是如何对这些语句进行调优依然需要经验的积累.
2. 手工调优SQL的挑战
对SQL语句手工调优需要很多的经验和技巧,例如:
. 对SQL语句本身进行优化以便获得更优的执行计划;
. 合理地调整数据读取方式(例如通过索引)以便能更快地访问数据;
. 合理的设计SQL实现方式以实现最优的架构(例如:使用静态SQL还是动态SQL)
当然,手工调优又是一件非常耗时的工作,因为:
1. 每条SQL可能都是唯一的,这意味着你需要分别优化不同的SQL;
2. 系统可能是很复杂的系统,SQL代码非常非常多;
3. SQL调整可能是一件永远看不到终点的任务,因为系统中的SQL workload可能经常在变化;
4. 一条原来运行效率很高的SQL, 因为数据的改变或者存取方式的改变 (例如删除了索引或者索引已经失效)可能会导致性能的降低.
...
3. 自动SQL调优
SQL Tuning Advisor (SQL调优顾问)是Oracle 10G中引入的新特性之一.设计它的目的就是为了代替传统的手工SQL调整(DBA和开发人员在工作中往往要花费很多时间和精力在SQL调整上).
SQL调优顾问处理的对象包括那些响应时间很慢或者是占用CPU/DISK很高的SQL.
SQ调优顾问收集这些SQL,并且给出自己的建议,它包括下面的部分:
. 怎样调整SQL的执行计划;
. 做出这条建议的理论原理;
. 优化后效率的提升幅度;
. 直接给出推荐使用的命令
用户可以有选择性地接受这些建议，然后去调优SQL.
随着SQL调优顾问的引入,你现在就可以让Oracle优化器来自动的为你调整SQL.
4. 概述: SQL调优顾问
首先，我们应该知道, SQl调优顾问只是调优过程的一个驱动程序. 它只是以一种内部的，特殊的方式调用CBO来分析以下存在的对象:
4.1 缺失或者失效的统计信息:
我们都知道，如果想让CBO有效的工作，就必须提供最新的统计信息. 如果一个SQL语句中引用的对象的统计信息不存在或者已经陈旧过时,它们就会被捕捉到, 并且作为建议的一部分;
4.2 SQL Profile(计划调整分析):
CBO为一些特定的SQL收集一些后台的信息作为SQL profile的组成部分,它包括:
. SQL语句的历史运行信息
. 实际运行代价和估计运行代价的比较
. 谓词选择
实际上,CBO会在后台运行一条SQL语句中的部分子句,以产生一个复杂的SQL profile,这样就使CBO能够根据这些附加的信息来为一个复杂的SQL产生更优的执行计划.
SQL Profile被存放在数据字典中,因此不需要应用程序的干预.
4.3 SQL访问路径的分析:
有些情况下, CBO 会推荐使用一个新的索引，这个索引能够提供更快的执行路径, SQL Access Advisor 可以完成这个工作.
4.4 重新构建SQL
CBO 会鉴定出哪些SQL语句的执行计划非常糟糕，并且给出相关的建议去重新调整. 推荐使用的SQL语句可能与原有的SQL语句非常相似, 因此DBA或者开发人员可以根据具体的应用来灵活的选择.
5. 怎样检测缺失或者陈旧的统计信息
使用从9i开始引入的DBMS_STATS包,例如:
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>'CUSTOMERS',estimate_percent=>dbms_stats.auto_sample_size);
精确的统计信息是CBO产生最优化执行计划的前提条件.从Oracle 10G开始引入了Automaic Statistics Gathering(初始化参数STATISTICS_TYPE设成BASIC或者ALL),如果没有激活这个特性, 统计信息可能就会丢失或者陈旧.
在统计检测模式下,Oracle优化器会执行一些额外的检查以便验证需要使用的统计信息,并且会适时的提醒去产生需要的统计信息.
6. More information about SQL profile
通常情况下,CBO会根据当前的统计信息为一条SQL语句产生一个最优的执行计划。随着数据的改变或者存取方式的改变，这个计划已经不是最优的执行计划了,由于没有可用的SQL Profile, CBO会一直认为当前的统计信息还是很精确的,从而做出了错误的选择. 而如果使用了SQL Profile, CBO会花费一些时间去根据这个SQL语句去搜集一些附加的信息,从而得到更精确的统计信息和产生更优的执行计划，这个过程可能会比较耗时,因为优化器可能要做下面的尝试:
. 执行原有SQL的一部分和使用动态抽样技术来收集更准确的统计信息;
. 进行更深层次的谓词选择: 例如分析列和列之间的相互关系，使用不同的JOIN 方式，以及使用更复杂的谓词(加入函数等等)
. 必要的时候，数据统计信息可能在统计检测模式下就已经产生了,这样就会避免统计信息失效或者陈旧对CBO带来的不良影响.
基于这些统计信息, 优化器就会根据SQL Profile来进行自我调整.
7. More information about SQL Analysis
简而言之,SQL Analysis的作用就是帮助DBA和开发人员捕捉那些低效率的SQL, 并且提供自己的修改建议.
例如, NOT EXISTS 和 NOT IN 的构造器很相似,但是不完全一样.因此，需要你自己去决定应该使用哪一个.也正是基于这样的考虑, CBO 不会自动替你重写整个查询语句而只是提供建议:
SQl Analysis能够检测以下类型的SQL:
1. 在查询中使用 NOT IN 而不是使用 NOT EXISTS
(说明: Oracle 10G中,如果相关表的相关列上有索引,CBO会为使用了 NOT IN的语句选择最优的查询计划,而不是简单的使用全表扫描)
2. 如果索引列上的数据类型不匹配，会引入更复杂的谓词来预防笛卡儿集的产生
8. 怎样去使用SQL Tuning Advisor
SQL Tuning Advisor 检测的SQL来源于很多部分:
. ADDM 检测到的高负荷的SQL
. 当前共享池中和游标池中的SQL
. AWR快照中反映的SQL: 缺省情况下,AWR会保持近一个星期的快照信息, 因此用户可以使用近七天中任何一个时间段的历史信息来选择需要调整的SQL语句.
. 用户自定义的workload: 用户可以创建自定义的workload,例如根据自己的需要来选择SQL语句,这些语句可能不是高负荷的，可能也不在当前的游标池中
. 对于多个SQL语句,用户可以创建SQL 优化集(SQL Tuning Set),一个STS 可以存储多个SQL语句以及它们的执行信息:
执行上下文: 包括绑定变量和具体方案的解析信息
执行的统计信息: 执行的次数，平均执行的时间
9. 在EM 10G 中使用SQL Advisor Analyzer
STEP 1: 从下面的页面找到有问题的SQL
. ADDM report page
. Top SQL page
. SQL Tuning Set (STS) page
STEP 2: 查看 SQL Tuning Advisor 提供的建议
STEP 3: 根据这些建议去实施
对于普通用户，需要以下权限:
ADVISOR
CREATE JOB
EXECUTE ON SYS.DBMS_SQLTUNE
如果需要创建STS,还需要ADMINISTER_TUNING_SET的权限
10. 调用DBMS_SQLTUNE包来使用SQL Advisor Analyzer
