Oracle 11g系统调优之dbms_sqltune包的使用

前沿：随着数据库版本的提升，Oracle也提供了越来越多的性能诊断工具，针对SQL的调优，DBMS_SQLTUNE就是其中一个比较优秀的包。
DBMS_SQLTUNE最开始是在10G里面出现，11G里面则对其进行了加强，使得其更加符合实际需求。

1.查找系统可能存在问题的SQL

一般什么样的SQL可能会存在性能问题呢？
我们第一时间能想到的肯定是执行时间很长的SQL、其次是IO很高的SQL，这里就针对执行时间很长的SQL来做测试。
获取类似的SQL有多种方法，AWR、ADDR、动态视图等，这里我们就通过动态视图v$session_longops来获取，因为这个视图里面的语句是最近执行的，有比较强的及时性。

以下语句可查询最近数据库中执行时间比较长的SQL，包括执行时间。

点击(此处)折叠或打开

1. select tt1.sql_text,tt1.sql_fulltext,tt2.sql_id,tt2.sums
   
2. from v$sqlarea tt1,
   
3. (select sql_id,sum(elapsed_seconds) as sums
   
4. from v$session_longops where opname=\'Table Scan\'
   
5. group by sql_id
   
6. ) tt2
   
7. where tt1.sql_id=tt2.sql_id
   
8. order by tt2.sums desc;

执行结果如下：


其中SUMS列为此SQL执行的总时间，上面我主要选取了‘Table Scan’这个类型的操作作为主要的时间损耗，从实际上来看也是如此，表扫描的方式直接关系SQL执行的效率，
表扫描占整个SQL执行时间的比重最大。

从上面，我们选取一条SQL，ID为“3c3ch9a4xdwn1”作为需要优化的SQL。


2.DBMS_SQLTUNE包
DBMS_SQLTUNE包提供了很多的子程序来对SQL进行诊断和对执行计划进行处理，这里我们只是简单的测试一下DBMS_SQLTUNE的调优功能，主要涉及到3个子过程。

DBMS_SQLTUNE.CREATE_TUNING_TASK    #创建一个SQL调优任务

DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id           IN VARCHAR2,                                             ----------&gtSQL ID，必填项
  plan_hash_value  IN NUMBER    := NULL,                           -----------&gt执行计划的HASN值(选填)
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,  -----------&gt任务类型，有limited和comprehensive两种
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,       -----------&gt此任务最长的执行时间
  task_name        IN VARCHAR2  := NULL,                           -----------&gt任务名
  description      IN VARCHAR2  := NULL)                             -----------&gt任务描述
RETURN VARCHAR2;

EXECUTE_TUNING_TASK                              #执行一个SQL调优任务

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name         IN VARCHAR2,                                      ------------&gt任务名
   execution_name    IN VARCHAR2               := NULL,         ------------&gt执行时的名称，可为空
   execution_params  IN dbms_advisor.argList   := NULL,        ------------&gt执行参数，默认可为空
   execution_desc    IN VARCHAR2               := NULL);         ------------&gt执行描述

DROP_TUNING_TASK                                   #删除一个SQL调优任务

DBMS_SQLTUNE.DROP_TUNING_TASK(
 task_name         IN VARCHAR2);                                      -------------&gt任务名


ACCEPT_SQL_PROFILE                               #接受及应用一个SQL_PROFILE执行计划给某条SQL

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name    IN  VARCHAR2,                                         --------------&gt执行优化的任务名
   object_id    IN  NUMBER   := NULL,                                --------------&gt对象编号，一般不填
   name         IN  VARCHAR2 := NULL,                               --------------&gt制定的sql_profile名称，如果不填则由系统指派
   description  IN  VARCHAR2 := NULL,                               --------------&gt该执行计划的描述信息
   category     IN  VARCHAR2 := NULL);                              -------------&gt需要与该SESSION的sqltune_category参数相匹配
   task_owner   IN VARCHAR2  := NULL,                             -------------&gt任务的所有者
   replace      IN BOOLEAN   := FALSE,                               -------------&gt如果此sql_profile已存在，则决定是否替换，默认值为不替换
   force_match  IN BOOLEAN   := FALSE,                           -------------&gt是否强制匹配此执行计划与所有HASH值相同的SQL，类似CURSOR_SHARING参数的FORCE
   profile_type IN VARCHAR2  := REGULAR_PROFILE);          -------------&gtsql_profile的类型，默认为REGULAR_PROFILE，可修改为PX_PROFILE，表示此执行计划变更为并行执行


DROP_SQL_PROFILE                                     #删除一个SQL_PROFILE的应用，让系统自动选择

DBMS_SQLTUNE.DROP_SQL_PROFILE (
   name          IN  VARCHAR2,
   ignore        IN  BOOLEAN  := FALSE);



3.具体演示过程

SQL Profile是10g中的新特性，作为自动SQL调整过程的一部分，由Oracle企业管理器来管理。除了OEM，SQL Profile可以通过DBMS_SQLTUNE包来进行管理。

查