通过案例学调优之--SQL Profile

通过案例学调优之--SQL Profile

一、什么是SQL Profile（概要）

        SQL Profile在性能优化中占有一个重要的位置。

       MOS里这么描述SQL Profile：

       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的用户会话都可以使用这个概要。你可以修改这个属性，比如将其改为SCO，则SQLTUNE_GATEGORY参数为SCO的用户会话才能使用它，利用这个功能，你可以在一个受限制的环境中来测试一个SQL Profile。

16:42:03 SYS@ prod >desc dba_sql_profiles Name                                                              Null?    Type ----------------------------------------------------------------- -------- -------------------------------------------- NAME                                                              NOT NULL VARCHAR2(30) CATEGORY                                                          NOT NULL VARCHAR2(30) SIGNATURE                                                         NOT NULL NUMBER SQL_TEXT                                                          NOT NULL CLOB CREATED                                                           NOT NULL TIMESTAMP(6) LAST_MODIFIED                                                              TIMESTAMP(6) DESCRIPTION                                                                VARCHAR2(500) TYPE                                                                       VARCHAR2(7) STATUS                                                                     VARCHAR2(8) FORCE_MATCHING                                                             VARCHAR2(3) TASK_ID                                                                    NUMBER TASK_EXEC_NAME                                                             VARCHAR2(30) TASK_OBJ_ID                                                                NUMBER TASK_FND_ID                                                                NUMBER TASK_REC_ID                                                                NUMBER 16:50:43 SYS@ prod >select name, CATEGORY,sql_text,status from dba_sql_profiles;no rows selected

       SQL Profile可以作用在如下表达式中：SELECT; UPDATE; INSERT(在包含SELECT子句的情况下); DELETE; CREATE TABLE(包含SELECT子句的情况下); MERGE(UPDATE或INSERT操作)。

二、SQL Profile的管理

Oracle执行SQL语句的步骤如下：

1. 用户传送要执行的SQL语句给SQL引擎

2. SQL引擎要求查询优化器提供执行计划

3. 查询优化取得系统统计信息、SQL语句引用对象的对象统计信息、SQL概要和构成执行环境的初始化参数

4. 查询优化器分析SQL语句并产生执行计划

5. 将执行计划传递给SQL引擎

6. SQL引擎执行SQL语句

 SQL Profile可以由OEM来管理，也可以通过DBMS_SQLTUNE包来手动使用。

（1）、使用OEM时步骤如下：

1. 在Performance页面，点击Top Activity。出现了Top Activity页面

2. 在Top SQL下面，点击正在使用SQL Profile的SQL表达式的SQL ID链接，会出现一个SQL Details页面

3. 点击Plan Control选项卡，在SQL Profiles and Outlines下面会显示一个SQL profile的列表

4. 选择你想要管理的SQL Profile，可以做如下操作：启用或禁用、移除

5. 会出现一个确认的页面，点击Yes继续，No取消

（2）、使用DBMS_SQLTUNE包

如果使用DBMS_SQLTUNE包，你需要CREATE ANY SQL_PROFILE、DROP ANY SQL_PROFILE还有ALTER ANY SQL_PROFILE的系统权限。

1）、创建sql profile

使用DBMS_SQLTUNE.ACCEPT_SQL_PROFILE过程来接受并创建SQL Tuning Advisor建议的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;

这个过程的传入参数中有一个可选参数force_match，默认为FALSE。当设置为FALSE时，不区分空白和大小写，为TRUE时，空白、大小写和字面量都不区分。通过企业管理器来接受SQL概要时，这个参数在ORACLE11g中才可以设置。

2）、修改SQL Profile

可以修改STATUS、NAME、DESCRIPTION和CATEGORY属性

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

3）、删除SQL Profile

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

对我们来说，重点在于创建SQL Profile时的my_sql_tuning_task上，它通过函数create_tuning_task来创建，执行这个函数需要传递下面的参数之一：SQL语句文本、存储在共享池中的SQL语句引用(sql_id)、存储在自动工作量资料库中的SQL语句引用(sql_id)、SQL调优集名称。

比如利用sql_id来创建tuning_task，我们可以这么运行 declare tuning_task varchar2(30); begin   tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'bfb9vn0gh3z0t');   dbms_output.put_line(tuning_task); end;

 什么是SQL调优集(tuning set)？简单来讲，SQL调优集是存储一系列SQL语句及其相关信息的对象集合，这些信息包括执行环境、运行统计和可选的执行计划。

下面引用MOS提供的一个示例来演示一下这个过程

案例分析：

1、scott用户执行sql17:19:56 SCOTT@ prod >create table test (n number);Table created.17:20:16 SCOTT@ prod >begin17:20:24   2  for i in 1..100000 loop17:20:36   3  insert into test values (i);17:20:47   4  commit;17:20:49   5  end loop;17:20:52   6  end;17:20:53   7  /PL/SQL procedure successfully completed.17:22:02 SCOTT@ prod >create index test_ind on test(n);Index created.17:22:55 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'TEST' ,cascade=>true);PL/SQL procedure successfully completed.17:23:15 SCOTT@ prod >set autotrace on17:23:43 SCOTT@ prod >select * from test where n=100;         N----------       100Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 3357096749-----------------------------------------------------------------------------| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT |          |     1 |     5 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     5 |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("N"=100)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          3  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          对此sql建立sql profile：17:24:02 SCOTT@ prod >select /*+ no_index(test,test_ind) */ * from test where n=100;         N----------       100Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 1357081020--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     5 |    69   (2)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| TEST |     1 |     5 |    69   (2)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("N"=100)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets        191  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、通过sys用户建立sql profile（使用dbms_sqltune包）17:28:26 SYS@ prod >declare17:29:34   217:29:34   3    my_task_name VARCHAR2(30);17:29:34   417:29:34   5    my_sqltext CLOB;17:29:34   617:29:34   7    begin17:29:34   817:29:34   9       my_sqltext := 'select /*+ no_index(test test_ind) */ * from test where n=100';17:29:34  1017:29:34  11       my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(17:29:34  1217:29:34  13       sql_text => my_sqltext,17:29:34  1417:29:34  15       user_name => 'SCOTT',17:29:34  1617:29:34  17       scope => 'COMPREHENSIVE',17:29:34  1817:29:34  19       time_limit => 60,17:29:34  2017:29:34  21       task_name => 'my_tun1',17:29:34  2217:29:34  23       description => 'Task to tune a query on a specified table');17:29:34  2417:29:34  25  end;17:29:35  26  /PL/SQL procedure successfully completed.建立调优任务：17:29:37 SYS@ prod >begin17:30:39   217:30:39   3  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_tun1');17:30:39   417:30:39   5  end;17:30:39   617:30:39   7  /PL/SQL procedure successfully completed.查看调优task：17:32:47 SYS@ prod >set long 100017:33:17 SYS@ prod >set longchunksize 100017:33:24 SYS@ prod >set linesize 10017:33:32 SYS@ prod >SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tun1') from dualDBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUN1')----------------------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name   : my_tun1Tuning Task Owner  : SYSWorkload Type      : Single SQL StatementScope              : COMPREHENSIVETime Limit(seconds): 60Completion Status  : COMPLETEDStarted at         : 11/07/2014 17:30:41Completed at       : 11/07/2014 17:30:49-------------------------------------------------------------------------------Schema Name: SCOTTSQL ID     : b1wdr0b0qzsbgSQL Text   : select /*+ no_index(test test_ind) */ * from test where n=100-------------------------------------------------------------------------------DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUN1')----------------------------------------------------------------------------------------------------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: 99.41%)  17:34:58 SYS@ prod >execute dbms_sqltune.accept_sql_profile(task_name =>'my_tun1',task_owner => 'SYS', replace => TRUE);PL/SQL procedure successfully completed.建立sql profile：17:39:22 SYS@ prod >DECLARE17:41:13   217:41:13   3  my_sqlprofile_name VARCHAR2(30);17:41:13   417:41:13   5  begin17:41:13   617:41:13   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (17:41:13   817:41:13   9  task_name => 'my_tun1',17:41:13  1017:41:13  11  name => 'my_sqlprofile',force_match=>false);17:41:13  1217:41:13  13  end;17:41:15  14  /DECLARE*ERROR at line 1:ORA-13830: SQL profile with category DEFAULT already exists for this SQL statementORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16259ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31ORA-06512: at "SYS.DBMS_SQLTUNE", line 7133ORA-06512: at line 717:44:28 SYS@ prod >DECLARE17:46:00   217:46:00   3  my_sqlprofile_name VARCHAR2(30);17:46:00   417:46:00   5  begin17:46:00   617:46:00   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (17:46:00   817:46:00   9  task_name => 'my_tun1',17:46:00  1017:46:00  11  name => 'my_sqlprofile',force_match=>false,CATEGORY=>'scott');17:46:00  1217:46:00  13  end;17:46:01  14  /PL/SQL procedure successfully completed.17:53:49 SYS@ prod >select name, CATEGORY,sql_text,status from dba_sql_profiles;NAME                           CATEGORY------------------------------ ------------------------------SQL_TEXT----------------------------------------------------------------------------------------------------STATUS--------SYS_SQLPROF_0149899c759a0000   DEFAULTselect /*+ no_index(test test_ind) */ * from test where n=100ENABLEDmy_sqlprofile                  SCOTTselect /*+ no_index(test test_ind) */ * from test where n=100ENABLED删除存在的sql profile：17:53:51 SYS@ prod >exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'SYS_SQLPROF_0149899c759a0000');PL/SQL procedure successfully completed.17:55:20 SYS@ prod >exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'my_sqlprofile');PL/SQL procedure successfully completed.重新建立sql profile：17:55:35 SYS@ prod >DECLARE17:56:13   217:56:13   3  my_sqlprofile_name VARCHAR2(30);17:56:13   417:56:13   5  begin17:56:13   617:56:13   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (17:56:13   817:56:13   9  task_name => 'my_tun1',17:56:13  1017:56:13  11  name => 'my_sqlprofile');17:56:13  1217:56:13  13  end;17:56:16  14  /PL/SQL procedure successfully completed.Elapsed: 00:00:00.04查看sql profile：18:01:48 SYS@ prod >col name for a2018:01:55 SYS@ prod >r  1* select name,CATEGORY,SQL_TEXT,TASK_EXEC_NAME ,STATUS from dba_sql_profilesNAME                 CATEGORY   SQL_TEXT-------------------- ---------- --------------------------------------------------TASK_EXEC_NAME                 STATUS------------------------------ --------my_sqlprofile        DEFAULT    select /*+ no_index(test test_ind) */ * from test                                where n=100EXEC_427                       ENABLED3、以scott用户的身份进行验证18:01:55 SYS@ prod >conn scott/tigerConnected.18:02:43 SCOTT@ prod >set autotrace on18:02:46 SCOTT@ prod > select /*+ no_index(test test_ind) */ * from test where n=100;         N----------       100Elapsed: 00:00:00.05Execution Plan----------------------------------------------------------Plan hash value: 3357096749-----------------------------------------------------------------------------| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT |          |     1 |     5 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     5 |     1   (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("N"=100)Note-----   - SQL profile "my_sqlprofile" used for this statementStatistics----------------------------------------------------------        790  recursive calls          0  db block gets        168  consistent gets          6  physical reads        116  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         13  sorts (memory)          0  sorts (disk)          1  rows processed

可以看出即使使用了‘no_index'的hint，sql执行计划仍使用index 访问。  使用了  SQL profile "my_sql_profile" used for this statement  

由这个例子我们可以发现，在必要情况下，SQL Profile可以让hint失效！

本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1574132