ITPub博客

SQL Profile(第二篇)

原创 作者:wei-xh 时间:2018-05-23 11:26:31 0 删除 编辑

通过SQL Tuning Advisor使用SQL profile

11GR2上SQL Tuning Advisor已经变得非常好用,我非常喜欢将一些非常复杂的SQL语句交给SQL Tuning Advisor来调优,几乎每次都不让我失望,通常调优结束后,SQL Tuning Advisor都会给你一些建议,例如建议你创建索引或者收集统计信息,或者建议你接受SQL Profile并且给出了接受SQL Profile后性能将得到的提升。本节将会给出一个示例来演示如何通过SQL Tuning Advisor来使用SQL Profile。首先我们需要构建一下需要用到的测试表:

SQL>CREATE TABLE test

  2   AS

  3   SELECT ROWNUM id,

  4          DBMS_RANDOM.STRING('A', 12) name,

  5          DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status

  6     FROM all_objects a,dba_objects b

  7    WHERE ROWNUM <= 50000;

 

Table created.

 

SQL>create index t_ind on t(status);

 

Index created.

 

SQL>begin

  2     dbms_stats.gather_table_stats(ownname          =>'test',

  3                                   tabname          => 'test',

  4                                   no_invalidate    => FALSE,

  5                                   estimate_percent => 100,

  6                                   force            => true,

  7                                   degree         => 5,

  8                              method_opt       => 'for all columns  size 1',

  9                                   cascade          => true);

 10   end;

 11   /

 

SQL>select status,count(*) from test group by status;

 

STATUS             COUNT(*)

---------------- ----------

Active                49900

Inactive                100

 

上面的代码做了下面几件事:

创建了一张测试表test,总记录数50000。

表上的字段status一共有2个唯一值:Active和Inactive,此字段有数据倾斜。

status上值为Active的值有49900个,占了表里绝大多数的记录,为Inactive的记录非常少,只有100个。

l status字段上有索引,分析了表的统计信息,但是status字段没有收集直方图。

我们来对status为Inactive的值做查询,由于status为Inactive的值非常少,因此走索引扫描性能更好,但是由于列上缺少直方图,因此执行计划会走全表扫描:

SQL>select count(name) from test where status='Inactive';

 

COUNT(NAME)

-----------

        100

 

SQL>select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------

SQL_ID  c37q7z5qjnwwf, child number 0

-------------------------------------

select count(name) from test where status='Inactive'

Plan hash value: 1950795681

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |       |       |    51 (100)|          |

|   1 |  SORT AGGREGATE    |      |     1 |    21 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST | 25000 |   512K|    51   (2)| 00:00:01 |

---------------------------------------------------------------------------

上面执行计划里显示的经过谓词过滤后的全表扫描返回的基数为25000,因为缺少直方图,因此优化器就简单的通过 基数=表的总记录数/status字段的唯一值数量=50000/2=25000来得出基数。我们来通过SQL Tuning Advisor分析一下这个SQL,看看优化器能不能识别到这是一个低效的执行计划,能否给出我们一些建议:

SQL>var c varchar2(100)

SQL>exec :c := dbms_sqltune.CREATE_TUNING_TASK(SQL_ID=>'c37q7z5qjnwwf')

 

PL/SQL procedure successfully completed.

 

SQL>exec dbms_sqltune.execute_tuning_task(task_name => :c)

 

PL/SQL procedure successfully completed.

 

SQL>select dbms_sqltune.report_tuning_task(:c) from dual;

 

DBMS_SQLTUNE.REPORT_TUNING_TASK(:C)

-------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : TASK_1112

Tuning Task Owner  : TEST

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status  : COMPLETED

Started at         : 08/01/2014 15:59:32

Completed at       : 08/01/2014 15:59:33

-------------------------------------------------------------------------------

Schema Name: TEST

SQL ID     : c37q7z5qjnwwf

SQL Text   : select count(name) from test where status='Inactive'

-------------------------------------------------------------------------------

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: 51.46%)

  ------------------------------------------

  - Consider accepting the recommended SQL Profile.

    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1112',

            task_owner => 'TEST', 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):              .00212           .000221      89.57 %

  CPU Time (s):                 .002099             .0002      90.47 %

  User I/O Time (s):                  0                 0

  Buffer Gets:                      210               102      51.42 %

  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: 1950795681

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    21 |    51   (2)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |    21 |            |          |

|*  2 |   TABLE ACCESS FULL| TEST |   100 |  2100 |    51   (2)| 00:00:01 |

---------------------------------------------------------------------------

2- Using SQL Profile

--------------------

Plan hash value: 4130896540

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |     1 |    21 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE              |       |     1 |    21 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |   100 |  2100 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |   100 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Dbms_sqltune包被用来创建调优任务、执行调优任务、查看调优结果,通过dbms_sqltune包的CREATE_TUNING_TASK函数来为SQL_ID为c37q7z5qjnwwfSQL创建了一个调优任务。然后通过execute_tuning_task过程来执行这个调优任务,任务运行后,优化器会利用动态采样等技术去验证评估内容与实际内容的差异,并且根据差异去调整执行计划。最后通过report_tuning_task来产生report查看调优的结果。调优结果里为我们提供了一个建议,建议我们采用一个SQL Profile,并且比对了采用SQL Profile后的性能提升,report的后面EXPLAIN PLANS SECTION部分展示了采用SQL Profile后,执行计划变为了索引扫描,而且基数的评估非常准确,从25000已经变为了100。

n Note当你运行SQL Tuning Advisor后,建议你接受一个SQL Profile,如果你想在接受SQL Profile前知道它到底为你提供了些什么,可以运行以下查询获得:

sys@DLSP>select                                              

  2   --    b.ATTR1        -- 10g 列     

  3         b.ATTR5        -- 11g 列     

  4   from                               

  5           wri$_adv_tasks     a,      

  6           wri$_adv_rationale b       

  7   where                              

  8           a.name = 'TASK_1112'       

  9   and     b.task_id = a.id           

 10   order by                           

 11           b.rec_id, b.id             

 12   ;                   

 

ATTR5

---------------------------------------------------------------------------

OPT_ESTIMATE(@"SEL$1", TABLE, "TEST"@"SEL$1", SCALE_ROWS=0.004)

OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "TEST"@"SEL$1", "T_IND", SCALE_ROWS=0.004)

OPTIMIZER_FEATURES_ENABLE(default)

 

wri$_adv_tasksname字段为任务名,在我们上面的例子里,可以通过print cSQLPLUS环境下获得任务名,也可以在dbms_sqltune.report_tuning_task(:c)的输出里找到任务名。

我们接受这个SQL Profile来看看再次查询是否能用到刚创建的SQL Profile:

SQL>execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1112',-

>             task_owner => 'TEST', replace => TRUE);

 

PL/SQL procedure successfully completed.

 

SQL>select count(name) from test where status='Inactive';

 

COUNT(NAME)

-----------

        100

 

SQL>select * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------

SQL_ID  c37q7z5qjnwwf, child number 0

-------------------------------------

select count(name) from test where status='Inactive'

 

Plan hash value: 4130896540

--------------------------------------------------------------------------------------

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          |

|   1 |  SORT AGGREGATE              |       |     1 |    21 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |   100 |  2100 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | T_IND |   100 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Note

-----

   - SQL Profile SYS_SQLPROF_01479094feeb0003 used for this statement

 

接受SQL Tuning Advisor提供的SQL Profile后,执行计划Note部分:- SQL Profile SYS_SQLPROF_01479094feeb0003 used for this statement,显示已经使用到了SQL Profile,注意由SQL Tuning Advisor产生的SQL Profile名称都是SYS_SQLPROF作为前缀,使用到SQL Profile后执行计划也已经从全表扫描变为了索引扫描。dbms_sqltuneaccept_sql_profile过程有多个参数可用,task_name、task_owner指创建SQL调优任务的任务名和所属用户,参数name和DESCRIPTIONSQL Profile的名字和对SQL Profile的描述,参数CATEGORY来指定创建的SQL Profile所属的类,默认的类为default。参数replace代表是否取代已有的SQL Profile,由于一个SQL只能有一个SQL Profile,不像Baseline,一个SQL可用有多个Baseline,因此如果一个SQL已经存在了SQL Profile,那么重新创建时,必须指定replace参数,设置为true,参数force_match指明了文本标准化的方式,默认为false。一旦接受SQL Profile,就可以通过视图dba_sql_profiles视图来查看SQL Profile的相关信息。因为SQL Profile并不属于某个用户,因此all_sql_profiles和user_sql_profiles视图都不可用。

如果一个SQL使用了SQL Profile,那么这个SQL的v$sql的sql_profile字段会显示使用到的SQL Profile的名字。下面的查询显示了系统中存在的SQL Profile和当前共享池中正在使用的SQL Profile的SQL。

SQL>select name, category, status, substr(sql_text,1,25) sql_text, force_matching

  2  from dba_sql_profiles

  3  where sql_text like nvl('&sql_text','%')

  4  and name like nvl('&name',name)

  5  order by last_modified

  6  ;

Enter value for sql_text:

Enter value for name:

 

NAME                           CATEGORY  STATUS   SQL_TEXT                   FORCE_

------------------------------ --------- -------- -------------------------  ------

profile_c99yw1xkb4f1u_dwrose   DEFAULT   ENABLED  select * from test         NO

profile_bhm28h5575bjy_dwrose   DEFAULT   ENABLED  select test2.object_name,  NO

profile_51k1ug4rwah2c_dwrose   DEFAULT   ENABLED  select distinct substr(ma  NO

profile_cm6stbx539mcz_dwrose   DEFAULT   ENABLED  select count(*) from tt    NO

profile_c37q7z5qjnwwf_dwrose   DEFAULT   ENABLED  select count(name) from t  NO

 

SQL>select sql_id,

  2         child_number cn,

  3         plan_hash_value plan_hash,

  4         sql_profile,

  5         executions execs,

  6         buffer_gets / decode(nvl(executions, 0), 0, 1, executions) avg_lio

  7    from v$sql s

  8   where upper(sql_text) like upper(nvl('&sql_text', sql_text))

  9     and sql_text not like '%from v$sql where sql_text like nvl(%'

 10     and sql_id like nvl('&sql_id', sql_id)

 11     and sql_profile is not null

 12   order by 1, 2, 3 ;

Enter value for sql_text:

 

SQL_ID          CN  PLAN_HASH SQL_PROFILE                    EXECS    AVG_LIO

-------------- --- ---------- ------------------------------ ----- ----------

c37q7z5qjnwwf    0 4130896540 profile_c37q7z5qjnwwf_dwrose       1        108

c37q7z5qjnwwf    1 4130896540 profile_c37q7z5qjnwwf_dwrose       2        105

我们根据SQL_PROFILE的命名知道,这些SQL Profile都不是SQL Tuning Advisor创建的,是我们手工创建的,因为SQL Tuning Advisor创建的SQL Profile都是以SYS_SQLPROF作为前缀的。

请登录后发表评论 登录
全部评论
ORACLE ACE-A,自07年接触ORACLE数据库,拥有7年ORACLE数据库运维经验,ACOUG,SHOUG核心成员,曾先后就职于东软集团股份有限公司、阿里巴巴(中国)网络科技有限公司,现就职于无锡新彩软件有限公司担任数据库架构师、DBA主管,负责公司数据库架构的设计、DB的规模化运维和性能调优。

注册时间:2009-07-04

  • 博文量
    426
  • 访问量
    2256841