通过案例学调优之-凯发k8官方网

通过案例学调优之--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_profilesname                                                              null?    type----------------------------------------------------------------- -------- --------------------------------------------name                                                              not null varchar2(30)category                                                          not null varchar2(30)signature                                                         not null numbersql_text                                                          not null clobcreated                                                           not null timestamp(6)last_modified                                                              timestamp(6)description                                                                varchar2(500)type                                                                       varchar2(7)status                                                                     varchar2(8)force_matching                                                             varchar2(3)task_id                                                                    numbertask_exec_name                                                             varchar2(30)task_obj_id                                                                numbertask_fnd_id                                                                numbertask_rec_id                                                                number16: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用户执行sql 17:19:56 scott@ prod >create table test (n number); table created.17:20:16 scott@ prod >begin 17:20:24   2  for i in 1..100000 loop 17: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 on 17:23:43 scott@ prod >select * from test where n=100;n ----------100 elapsed: 00:00:00.01 execution 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 calls0  db block gets3  consistent gets0  physical reads0  redo size415  bytes sent via sql*net to client419  bytes received via sql*net from client2  sql*net roundtrips to/from client0  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 ----------100 elapsed: 00:00:00.01 execution 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 calls0  db block gets191  consistent gets0  physical reads0  redo size415  bytes sent via sql*net to client419  bytes received via sql*net from client2  sql*net roundtrips to/from client0  sorts (memory)0  sorts (disk)1  rows processed2、通过sys用户建立sql profile（使用dbms_sqltune包） 17:28:26 sys@ prod >declare 17:29:34   2 17:29:34   3    my_task_name varchar2(30); 17:29:34   4 17:29:34   5    my_sqltext clob; 17:29:34   6 17:29:34   7    begin 17:29:34   8 17:29:34   9       my_sqltext := 'select /*  no_index(test test_ind) */ * from test where n=100'; 17:29:34  10 17:29:34  11       my_task_name := dbms_sqltune.create_tuning_task( 17:29:34  12 17:29:34  13       sql_text => my_sqltext, 17:29:34  14 17:29:34  15       user_name => 'scott', 17:29:34  16 17:29:34  17       scope => 'comprehensive', 17:29:34  18 17:29:34  19       time_limit => 60, 17:29:34  20 17:29:34  21       task_name => 'my_tun1', 17:29:34  22 17:29:34  23       description => 'task to tune a query on a specified table'); 17:29:34  24 17:29:34  25  end; 17:29:35  26  / pl/sql procedure successfully completed.建立调优任务： 17:29:37 sys@ prod >begin 17:30:39   2 17:30:39   3  dbms_sqltune.execute_tuning_task( task_name => 'my_tun1'); 17:30:39   4 17:30:39   5  end; 17:30:39   6 17:30:39   7  / pl/sql procedure successfully completed.查看调优task： 17:32:47 sys@ prod >set long 1000 17:33:17 sys@ prod >set longchunksize 1000 17:33:24 sys@ prod >set linesize 100 17:33:32 sys@ prod >select dbms_sqltune.report_tuning_task('my_tun1') from dual dbms_sqltune.report_tuning_task('my_tun1') ---------------------------------------------------------------------------------------------------- general information section ------------------------------------------------------------------------------- tuning task name   : my_tun1 tuning task owner  : sys workload type      : single sql statement scope              : comprehensive time limit(seconds): 60 completion status  : completed started at         : 11/07/2014 17:30:41 completed at       : 11/07/2014 17:30:49 ------------------------------------------------------------------------------- schema name: scott sql id     : b1wdr0b0qzsbg sql 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 >declare 17:41:13   2 17:41:13   3  my_sqlprofile_name varchar2(30); 17:41:13   4 17:41:13   5  begin 17:41:13   6 17:41:13   7  my_sqlprofile_name := dbms_sqltune.accept_sql_profile ( 17:41:13   8 17:41:13   9  task_name => 'my_tun1', 17:41:13  10 17:41:13  11  name => 'my_sqlprofile',force_match=>false); 17:41:13  12 17: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 statement ora-06512: at "sys.dbms_sqltune_internal", line 16259 ora-06512: at "sys.prvt_sqlprof_infra", line 31 ora-06512: at "sys.dbms_sqltune", line 7133 ora-06512: at line 717:44:28 sys@ prod >declare 17:46:00   2 17:46:00   3  my_sqlprofile_name varchar2(30); 17:46:00   4 17:46:00   5  begin 17:46:00   6 17:46:00   7  my_sqlprofile_name := dbms_sqltune.accept_sql_profile ( 17:46:00   8 17:46:00   9  task_name => 'my_tun1', 17:46:00  10 17:46:00  11  name => 'my_sqlprofile',force_match=>false,category=>'scott'); 17:46:00  12 17: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   default select /*  no_index(test test_ind) */ * from test where n=100 enabled my_sqlprofile                  scott select /*  no_index(test test_ind) */ * from test where n=100 enabled删除存在的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 >declare 17:56:13   2 17:56:13   3  my_sqlprofile_name varchar2(30); 17:56:13   4 17:56:13   5  begin 17:56:13   6 17:56:13   7  my_sqlprofile_name := dbms_sqltune.accept_sql_profile ( 17:56:13   8 17:56:13   9  task_name => 'my_tun1', 17:56:13  10 17:56:13  11  name => 'my_sqlprofile'); 17:56:13  12 17: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 a20 18:01:55 sys@ prod >r1* select name,category,sql_text,task_exec_name ,status from dba_sql_profiles name                 category   sql_text -------------------- ---------- -------------------------------------------------- task_exec_name                 status ------------------------------ -------- my_sqlprofile        default    select /*  no_index(test test_ind) */ * from testwhere n=100 exec_427                       enabled3、以scott用户的身份进行验证 18:01:55 sys@ prod >conn scott/tiger connected. 18:02:43 scott@ prod >set autotrace on 18:02:46 scott@ prod > select /*  no_index(test test_ind) */ * from test where n=100;n ----------100 elapsed: 00:00:00.05 execution 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 statement statistics ----------------------------------------------------------790  recursive calls0  db block gets168  consistent gets6  physical reads116  redo size415  bytes sent via sql*net to client419  bytes received via sql*net from client2  sql*net roundtrips to/from client13  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失效！

转载于:https://blog.51cto.com/tiany/1574132

与50位技术专家面对面20年技术见证，附赠技术全景图

总结

以上是凯发k8官方网为你收集整理的通过案例学调优之--sql profile的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得凯发k8官方网网站内容还不错，欢迎将凯发k8官方网推荐给好友。