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

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

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

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

执行结果如下：


其中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,                                             ---------->SQL ID，必填项
  plan_hash_value  IN NUMBER    := NULL,                           ----------->执行计划的HASN值(选填)
  scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,  ----------->任务类型，有limited和comprehensive两种
  time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,       ----------->此任务最长的执行时间
  task_name        IN VARCHAR2  := NULL,                           ----------->任务名
  description      IN VARCHAR2  := NULL)                             ----------->任务描述
RETURN VARCHAR2;

EXECUTE_TUNING_TASK                              #执行一个SQL调优任务

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

DROP_TUNING_TASK                                   #删除一个SQL调优任务

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


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

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
   task_name    IN  VARCHAR2,                                         -------------->执行优化的任务名
   object_id    IN  NUMBER   := NULL,                                -------------->对象编号，一般不填
   name         IN  VARCHAR2 := NULL,                               -------------->制定的sql_profile名称，如果不填则由系统指派
   description  IN  VARCHAR2 := NULL,                               -------------->该执行计划的描述信息
   category     IN  VARCHAR2 := NULL);                              ------------->需要与该SESSION的sqltune_category参数相匹配
   task_owner   IN VARCHAR2  := NULL,                             ------------->任务的所有者
   replace      IN BOOLEAN   := FALSE,                               ------------->如果此sql_profile已存在，则决定是否替换，默认值为不替换
   force_match  IN BOOLEAN   := FALSE,                           ------------->是否强制匹配此执行计划与所有HASH值相同的SQL，类似CURSOR_SHARING参数的FORCE
   profile_type IN VARCHAR2  := REGULAR_PROFILE);          ------------->sql_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包来进行管理。

查询优化器有时候会因为缺乏足够的信息，而对一条SQL语句做出错误的估计，生成糟糕的执行计划。而自动SQL调整通过SQL概要分析来解决这个问题，自动调整优化器会生成这条SQL语句的一个概要，称作SQL Profile。它由针对这条语句的一些辅助统计信息组成，通过采样和局部执行技术来确认，必要的话，会调整执行计划中的估计值。在SQL概要分析中，自 动调整优化器还可以通过一条SQL语句的执行历史信息来设置合适的优化器参数，比如将OPTIMIZER_MODE参数由ALL_ROWS改为 FIRST_ROWS。

换句话说，SQL概要是一个对象，它包含了可以帮助查询优化器为一个特定的SQL语句找到高效执行计划的信息。这些信息包括执行环境、对象统计和对查询优 化器所做评估的修正信息。它的最大优点之一就是在不修改SQL语句和会话执行环境的情况下影响查询优化器的决定。(《Oracle性能诊断艺术》)

SQL Profile中包含的并非单个执行计划的信息，必须注意的是，SQL Profile不会固定一个SQL语句的执行计划。当表的数据增长或者索引创建、删除，使用同一个SQL Profile的执行计划可能会改变，而储存在SQL Profile中的信息会继续起作用。然而，经过一段很长的时间之后，它的信息有可能会过时，需要重新生成。

SQL Profile的作用范围由CATEGORY属性来控制，这个属性决定了哪些用户会话可以应用这个概要。你可以从DBA_SQL_PROFILES中的 CATEGORY字段来查看这个属性。默认情况下，所有概要文件都创建为DEFAULT范畴，这意味着所有SQLTUNE_CATEGORY初始化参数为 DEFAULT的用户会话都可以使用这个概要。你可以修改这个属性，比如将其改为DEV，则SQLTUNE_GATEGORY参数为DEV的用户会话才能 使用它，利用这个功能，你可以在一个受限制的环境中来测试一个SQL Profile。

简单来说，Sql_Profile是用来影响数据库执行计划生成的一组信息文件的集合，可以在不改变原有SQL语句的前提下，达到类似HINTS改变其执行计划的目的。


创建一个sqltune的调优任务(即创建sql_profile的相关信息)

记住，DBMS_SQLTUNE.CREATE_TUNING_TASK是一个函数，必须要有返回值。
上面在定义了一个TASK后，可通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行此调优过程。


获取调优任务的详细信息

可通过dbms_sqltune.report_tuning_task，输入任务名，及可获取调优的相关信息，如下：

重点关注红字部分，即为SQL_TUNE任务给出的调优建议，我们看到对于上面的语句，ORACLE建议其并行执行，下面也列出来了当前的执行计划和调整后的执行计划。


对上面的SQL_PROFILE进行应用

查询已存在的SQL_PROFILE
select * from DBA_SQL_PROFILES;

DBA_SQL_PROFILES视图可查看当前系统中所有的SQL_PROFILES信息。


查询已存在的SQLTUNING TASK
select * from USER_ADVISOR_TASKS
USER_ADVISOR_TASKS视图可用来查看当前用户下所创建的调优任务


删除已应用的SQL_PROFILE

删除当前用户创建的SQLTUNING TASK