Automatic SQL Tuning and SQL Profiles

SQL Profiles是在Oracle 10g中就引入的，并且通过dbms_sqltune包或EM来进行管理是自动SQL调整进程的一部分。

自动SQL调整
查询优化器会有时会因为缺少信息而对语句的一个属性产生不精确的评估，进而导致低效的执行计划传统上来说，用户不得不通过手动增加hint到代码中来修改这个剖从而指导优化器产生正确的决定。对于第三方应用程序，改变应用代码是不现实的。

自动SQL调整使用SQL Profile来处理这个问题。自动调整优化器将会为SQL语句创建一个SQL Profile，它是由语句相关的辅助统计信息组成。查询优化器在正常模式下对基数，选择率与成本的评估有时会有严重的差异而导致生成低效的执行计划。SQL Profile通过收集额外的信息，比如使用抽样与特定的执行技术来调整评估可以用来解决这个问题。

在自动SQL调整时，优化器也会使用SQL语句的历史执行信息来合理设置优化器参数，比如改变optimizer_mode，将其参数从all_rows改成first_rows。

分析报告的输出建议接受SQL Profile。一旦接受SQL Profile它将会永久存储在数据字典中。一个SQL Profile被指定到一个特定的查询。如果接受，优化器在正常模式下使用SQL Profile中的信息与常规的数据库统计信息来对SQL进行解析。SQL Profile所提供的额外信息可以用来生成更好的执行计划。

SQL Profile
SQL Profile是存储在数据字典中的信息集合能让查询优化器为SQL语句创建一个最优执行计划。SQL Profile包含了自动SQL调整所发现的低效优化器评估的修正。这种信息能提高优化器对基数与选择率评估精确度，从而导致优化器选择一个更好的执行计划。

SQL Profile不包含单独的执行计划，当选择执行计划时优化器有以下信息源:
1.环境，包含数据库配置，绑定变量值，统计信息，数据集等等
2.SQL Profile所提供了附加统计信息

重要的是SQL Profile不会冻结一个SQL语句的执行计划，这一点与stored outlines不一样。当表记录增加或增加与删除索引时，使用相同的SQL Profile执行计划也会发生改变。当数据分布或相关语句的访问路径发生改变，SQL Profile中存储的信息仍然与SQL语关联。然而，随着时间的推移，SQL Profile的内容将会过时并且不得不重新生成。可以再次执行自动SQL调整来重新生成SQL Profile。

如何控制SQL Profile的使用范围
SQL Profile的使用范围可以由CATEGORY属性来进行控制。这个属性决定那个用户会话可以应用这个SQL Profile。可以通过查询dba_sql_profiles视图的category列来查看SQL Profile的category属性

SQL> select name,category from dba_sql_profiles;   NAME                           CATEGORY ------------------------------ ------------------------------ SYS_SQLPROF_0152b233d518c007   DEFAULT SYS_SQLPROF_015470e31c248001   DEFAULT coe_bcyatm4910qb1_725332378    DEFAULT coe_3yy1wbuvsxm93_1849931106   DEFAULT SYS_SQLPROF_0152b11b33e6c006   DEFAULT coe_a69pw2vj989zm_3709683508   DEFAULT SYS_SQLPROF_0151ed60f3d28000   DEFAULT coe_6rfqq1bjwcdx9_1360313219   DEFAULT SYS_SQLPROF_0152b33048a8c009   DEFAULT coe_36cbabzyq13gy_1849931106   DEFAULT SYS_SQLPROF_015470e298fd0000   DEFAULT SYS_SQLPROF_0152b0a82393c003   DEFAULT SYS_SQLPROF_0152ba15c21e800b   DEFAULT coe_6rfqq1bjwcdx9_1360313219_1 DEFAULT 

缺省情况下，所有SQL Profile都是创建在DEFAULT目录中。这意味着当sqltune_category设置为default时所有的用户会话都能使用这个SQL Profile。

SQL> show parameter sqltune_category   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ sqltune_category                     string      DEFAULT  

通过修改SQL Profile的category属性，可以决定那个会话将使用所创建的SQL Profile。例如，通过将一个SQL Profile的category属性设置为DEV，那么只有当sqltune_category设置为DEV时这些会话才能使用这个SQL Profile。所有其它的会话将不能访问这个SQL Profile并且SQL语句的执行计划将不会受这个SQL Profile的影响。这种技术能在SQL Profile被其它会话使用之前让你在一个受限的环境下测试SQL Profile。

SQL Profile可以应用的语句类型
.select语句
.update语句
.insert语句(只包含select子句)
.delete语句
.create table语句(只包含as select子句)
.merge语句(update或insert操作)

SQL Profile的管理
SQL Profile可以通过EM或dbms_sqltune来进行管理

为了使用dbms_sqltune来管理SQL Profile，用户必须有create any sql_profile,drop any sql_profile与alter any sql_profile系统权限。

接受SQL Profile
使用dbms_sqltune.accept_sql_profile过程来接受由SQL调整指导所创建的SQL Profile。

DECLARE  my_sqlprofile_name VARCHAR2(30);  BEGIN  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (   task_name => 'my_sql_tuning_task',  name => 'my_sql_profile');  END; 

my_sql_tuning_task是SQL调整任务的名称，可以查询dba_sql_profiles视图来查看SQL Profile的信息

修改SQL Profile
使用dbms_sqltune.alter_sql_profile过程可以用来修改现有SQL Profile的status,name,description与category属性

BEGIN  DBMS_SQLTUNE.ALTER_SQL_PROFILE(  name => 'my_sql_profile',   attribute_name => 'STATUS',   value => 'DISABLED');  END;  / 

在这个例子中，要修改名为my_sql_profile的SQL Profile，将它的status属性修改为disable这将意味着这个SQL Profile将不能在SQL编译时使用了。

删除SQL Profile
可以使用dbms_sqltune.drop_sql_profile过程来删除SQL Profile

begin  DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');  end;  / 

下面介绍使用SQL Profile来优化SQL语句的例子
会话1

SQL> create table test(n number);  Table created.  SQL> declare   2             begin   3              for i in 1 .. 10000 loop   4                  insert into test values(i);   5                  commit;   6              end loop;   7             end;   8   /  PL/SQL procedure successfully completed.  SQL> create index test_idx on test(n);  Index created.  SQL> exec dbms_stats.gather_table_stats('','TEST');  PL/SQL procedure successfully completed.  SQL> set autotrace on SQL> select /*+ no_index(test test_idx) */ * from test where n=1;           N ----------          1   Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020  -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 | --------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter("N"=1)   Statistics ----------------------------------------------------------           1  recursive calls           0  db block gets          23  consistent gets           0  physical reads           0  redo size         415  bytes sent via SQL*Net to client         419  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed 

会话2
创建一个SQL自动调整任务并运行报告调整任务并接受建议的SQL Profile

SQL> declare   2     my_task_name VARCHAR2(30);   3     my_sqltext CLOB;   4     begin   5        my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';   6        my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(   7        sql_text => my_sqltext,   8        user_name => 'SCOTT',   9        scope => 'COMPREHENSIVE',  10        time_limit => 60,  11        task_name => 'my_sql_tuning_task_1',  12        description => 'Task to tune a query on a specified table');  13   end;  14   /  PL/SQL procedure successfully completed.  SQL>  begin   2   DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');   3   end;   4   /  PL/SQL procedure successfully completed.  SQL> set long 10000 SQL> set longchunksize 1000 SQL> set linesize 100 SQL> set heading off SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL; set heading on GENERAL INFORMATION SECTION ------------------------------------------------------------------------------- Tuning Task Name   : my_sql_tuning_task_1 Tuning Task Owner  : SYS Workload Type      : Single SQL Statement Scope              : COMPREHENSIVE Time Limit(seconds): 60 Completion Status  : COMPLETED Started at         : 05/27/2016 16:58:11 Completed at       : 05/27/2016 16:58:28  ------------------------------------------------------------------------------- Schema Name: SCOTT  SQL ID     : d4wgpc5g0s0vu SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1  ------------------------------------------------------------------------------- FINDINGS SECTION (1 finding) -------------------------------------------------------------------------------  1- SQL Profile Finding (see explain plans section below) --------------------------------------------------------   A potentially better execution plan was found for this statement.    Recommendation (estimated benefit: 90.91%)   ------------------------------------------    - Consider accepting the recommended SQL profile.     execute dbms_sqltune.accept_sql_profile(task_name =>             'my_sql_tuning_task_1', task_owner => 'SYS', replace => TRUE);    Validation results   ------------------   The SQL profile was tested by executing both its plan and the original plan   and measuring their respective execution statistics. A plan may have been   only partially executed if the other could be run to completion in less time.                             Original Plan  With SQL Profile  % Improved                            -------------  ----------------  ----------   Completion Status:            COMPLETE          COMPLETE    Elapsed Time (s):             .003464           .000405       88.3 %   CPU Time (s):                 .003399           .000299       91.2 %   User I/O Time (s):                  0                 0   Buffer Gets:                       22                 2       90.9 %   Physical Read Requests:             0                 0   Physical Write Requests:            0                 0   Physical Read Bytes:                0                 0   Physical Write Bytes:               0                 0   Rows Processed:                     1                 1   Fetches:                            1                 1   Executions:                         1                 1    Notes    -----   1. Statistics for the original plan were averaged over 10 executions.   2. Statistics for the SQL profile plan were averaged over 10 executions.  ------------------------------------------------------------------------------- EXPLAIN PLANS SECTION -------------------------------------------------------------------------------  1- Original With Adjusted Cost ------------------------------ Plan hash value: 1357081020  --------------------------------------------------------------------------  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 | --------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter("N"=1)  2- Using SQL Profile --------------------  Plan hash value: 2882402178  ----------------------------------------------------------------------------- | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 | -----------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - access("N"=1)   -------------------------------------------------------------------------------  SQL> DECLARE   2   my_sqlprofile_name VARCHAR2(30);   3   begin   4   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (   5   task_name => 'my_sql_tuning_task_1',   6   name => 'my_sql_profile',   7   force_match => true,   8   replace =>true );   9   end;  10  /  PL/SQL procedure successfully completed.  

会话1

SQL> set autotrace on SQL> select /*+ no_index(test test_idx) */ * from test where n=1;           N ----------          1   Execution Plan ---------------------------------------------------------- Plan hash value: 2882402178  ----------------------------------------------------------------------------- | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 | |*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 | -----------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - access("N"=1)  Note -----    - SQL profile "my_sql_profile" used for this statement   Statistics ----------------------------------------------------------           8  recursive calls           0  db block gets          13  consistent gets           1  physical reads           0  redo size         415  bytes sent via SQL*Net to client         419  bytes received via SQL*Net from client           2  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)           1  rows processed 

可以看到虽然我们指定了no_index来让优化器不使用索引test_idx,但由于使用了SQL Profile还是使用索引test_idx,通过SQL Profile改变了SQL语句的执行计划。