SQL Profile学习总结

一直觉得sql profile这个点是我的知识盲区，终于下定决心深入学习研究一下，参考了多位大咖的博文，并研读了oracle官方文档，终于对这个知识点有了清晰的理解，现做一下总结记录，供需要的同仁参考。

一、SQL Profile概念与作用

SQLProfile是一个对象，可以理解为是sql的一种辅助信息集合，就像表或索引的统计信息一样。它包含了可以帮助查询优化器为一个特定的SQL语句找到高效执行计划的信息。这些信息包括执行环境、对查询优化器所做评估的修正信息等。

Oracle11g中，Sql profile在数据库中的存放位置：

SQL> select object_name,object_type from dba_objects where object_name like 'SQLOBJ%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SQLOBJ$                        TABLE
SQLOBJ$AUXDATA                 TABLE
SQLOBJ$DATA                    TABLE
SQLOBJ$DATA_PKEY               INDEX
SQLOBJ$_PKEY                   INDEX

Sql profile信息存放在sqlobj$、sqlobj$data、sqlobj$auxdata这三个基表中，oracle提供了视图dba_sql_profiles视图来查看sql profile的属性信息。

SQL> 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

通过此视图可以查看sql profile的name、category、sql_text、signature、force_matching、status等属性信息，如果是通过STA获得的，还会有调优任务属性信息。

Sql profile具体信息可以通过如下sql来查询

示例1：

SQL> SELECT extractValue(value(h), '.') AS hint

  FROM sys.sqlobj$data od,

       sys.sqlobj$ so,

       table(xmlsequence(extract(xmltype(od.comp_data),

                                 '/outline_data/hint'))) h

 WHERE so.name = 'coe_f1rzx3ybdpg12_1513984157'

   AND so.signature = od.signature

   AND so.category = od.category

   AND so.obj_type = od.obj_type

   AND so.plan_id = od.plan_id;  2    3    4    5    6    7    8    9   10  

 

HINT

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

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "T2"@"SEL$1")

END_OUTLINE_DATA

示例2：

SQL> SELECT extractValue(value(h), '.') AS hint

  FROM sys.sqlobj$data od,

       sys.sqlobj$ so,

       table(xmlsequence(extract(xmltype(od.comp_data),

                                 '/outline_data/hint'))) h

 WHERE so.name = 'SYS_SQLPROF_017c556e73f40002'

   AND so.signature = od.signature

   AND so.category = od.category

   AND so.obj_type = od.obj_type

   AND so.plan_id = od.plan_id;

 

HINT

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

OPT_ESTIMATE(@"SEL$58A6D7F6", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.01440028801)

OPTIMIZER_FEATURES_ENABLE(default)

SQL Profile的信息很像执行计划中outline data，可以说是Outline的进化。Outline能够实现的功能SQL Profile也完全能够实现，而SQL Profile具有Outline不具备的优点：①SQL Profile更容易生成、更改和控制。②SQL Profile在对SQL语句的支持上做得更好，也就是适用范围更广。

使用SQL Profile的两个目的：①稳定执行计划。②在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。

SQL Profile最大的优点是在不修改SQL语句和会话执行环境的情况下去优化SQL的执行效率，适合无法在应用程序中修改SQL时。

二、Sqlprofile使用

有两种生成SQL Profile的方法，分别是手动和采用STA来生成。

我认为手动生成的方式就是实现outline的功能，我们实际中通过加hint提示找到了高效的执行计划，可以使用手动生成sqlprofile的方式来稳定执行计划，这样可以避免应用来改变sql语句。

使用Sql Tune Advisor来生成sql profile，STA的工作模式为tune mode，它不同于normal mode，优化器会做额外的分析来确定是否可以提升正常模式下生成执行计划的性能，对于一条sql可能就会需要几分钟的时间，它的输出不只是一个执行计划，它包含一系列的措施来实现一个更优计划，同时会有两种执行计划的性能对比，高效执行计划的预期收益等信息。

（一）手动生成

使用sys.sqlprof_attr转化outline data中的hint信息，然后使用dbms_sqltune包的import_sql_profile方法，生成profile并绑定到sql上。

举例：

以oracle11.2.0.3版本为例。

创建测试表

create table t2 as select * from dba_objects;

create index idx_t2 on t2(owner,object_name);

生成并查看Cbo默认选择的执行计划

explain plan for select * from t2 where object_name=:a;

SELECT * FROM TABLE(dbms_xplan.display('','','outline'));

默认走索引扫描，我们加hint让优化器选择全表扫描。

Explain plan for select /*+full(t2) */ * from t2 where object_name=:a;

SELECT * FROM TABLE(dbms_xplan.display('','','outline'));

从outline data中获取有用的hint信息使优化器选择走全表扫描。

执行下面的存储过程实现绑定profile到sql。

declare

        v_hints sys.sqlprof_attr;

  begin

        v_hints := sys.sqlprof_attr('FULL(@"SEL$1" "T2"@"SEL$1")'); --从上面Outline Data部分获取到的HINT

        dbms_sqltune.import_sql_profile('select * from T2 where object_name= :a', ---SQL语句部分

                                 v_hints,

                                 'sqlprof_t2_object_name', --------自定义PROFILE 的名字

                               force_match => true);

  end;

  /

查看绑定sql profile后优化器选择的执行计划。

note部分说明使用上了sqlprofile。

上面使用sys.sqlprof_attr来构建hint信息比较麻烦，也容易出错。实际优化工作中年一般是使用MOS上提供的脚本coe_xfr_sql_profile.sql来生成sql profile，这个脚本的使用场景是同一个sql出现多个执行计划，想让sql固定执行某一个高效计划。使用此脚本来生成一个具体脚本coe_xfr_sql_profile_(sql_id)_（plan_hash_value).sql，再执行这个脚本来生成sql profile来固定执行计划。

 示例：

create table t1 as select * from dba_objects;

create index IDX_T1_TEMPORARY on t1(temporary);

exec dbms_stats.gather_table_stats(‘SCOTT’,’T1’,method_opt=>’for columns temporary size 2’);

var b varchar2(30);

exec :b:='N;

select * from t1 where temporary=:b;

exec :b:='Y';

执行三遍sql：select * from t1 where temporary=:b;

由于共享游标自适应特性，会产生不同的执行计划。

执行分别如下：

下面使用脚本coe_xfr_sql_profile.sql绑定执行计划。

Step1：执行脚本coe_xfr_sql_profile.sql

过程中需要输入两个参数，一个参数是sql_id,一个参数是要绑定执行计划的plan_hash_value,最后产生了具体的sql_profile脚本

 Step2：执行产生的sqlprofile脚本.

观察到定制的sqlprofile脚本里，就是构建了sys.sqlprof_attr类型的hint，然后调用存储过程dbms_sqltune.import_sql_profile。如果想使用其他sql的执行计划，比如加了hint提示的执行计划，需要将两个sql分别调用脚本coe_xfr_sql_profile.sql，将关于sys.sqlprof_attr的构建的hint替换一下即可达到目的。这样也是比较麻烦的，但是不易出错。oracle提供了另一个脚本coe_load_sql_profile.sql，此脚本具备绑定另一个sql的执行计划的功能，同时还会将sqlprofile打包导出，并提供导入解包脚本，将此sqlprofile放到另一个相似系统的功能，在此不再详细描述。

在调用dbms_sqltune.import_sql_profile时有一个参数force_match，为false时，会对所有exact_matching_signature相同的sql都有效，为true时，会对所有force_matching_signature相同的sql都有效。关于这两个signature何时会相同，请参考我的另一篇文章关于SQL语句之SIGNATURE的理解 - 墨天轮 (modb.pro)。

（二）采用STA生成

通过调用dbms_sqltune包的存储过程来完成。

举例：

　准备测试表与数据

create table t1 as select * from dba_objects where rownum<50000;

CREATE table t2 as select * from dba_objects;

create index idx_t2 on t2(object_id);

exec dbms_stats.gather_table_stats('SCOTT','T1',cascade => TRUE);

exec dbms_stats.gather_table_stats('SCOTT','T2',cascade => TRUE);

 var p1 varchar2(30);

EXEC :P1:='T1';

alter session set statistics_level=all;

执行sql语句

Select * from t1 inner join t2 on t1.object_id=t2.object_id where t1.object_name like %'||:p1||'%';

执行计划中两个表都采用了全表扫的访问方式，逻辑读消耗1793。

下面使用STA来对此sql做优化分析。

1. 调用dbms_sqltune包的create_tuning_task函数来创建调优任务，指定参数sql_id,为sql来创建。

var task_name varchar2(30);

SQL> exec :task_name:=dbms_sqltune.create_tuning_task(sql_id =>'f26pbsvgj9znu');

PL/SQL procedure successfully completed.

SQL> print task_name

TASK_NAME

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

TASK_435

2. 执行调优任务，参数为调优任务名称，其值为创建任务的返回值

SQL> exec dbms_sqltune.execute_tuning_task(:task_name);

PL/SQL procedure successfully completed.;

3. 查看生成的调优报告

set long 100000

col comments for a150

SELECT dbms_sqltune.report_tuning_task(:task_name)COMMENTS FROM dual;

 调优报告分为几个部分，general information section 对调优任务的基本信息做了介绍。Finding section列出推荐的sql profile信息，找到一个更好的执行计划，并与原始执行计划做对比，计算出预估提升的收益。同时给出了接受这个profile的语句。Explain plan section列出了两个执行计划信息。

通过使用STA调优，给出了一个sql profile信息。接受这个建议，就会创建sql profile对象，并绑定到此sql上。

execute dbms_sqltune.accept_sql_profile(task_name=>’TASK_443’,task_owner=’SCOTT’,,replace=>TRUE);

三、SQL Profile的管理

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

DBMS_SQLTUNE包管理SQL Profile，要实现对sql profile的管理，要求具备CREATE ANY SQL PROFILE、ALTER ANY SQL PROFILE、DROP ANY SQL PROFILE的权限。

1.创建STA推荐的sql profile，同时将之与sql进行绑定。

调用存储过程DBMS_SQLTUNE.ACCEPT_SQL_PROFILE

示例：

Exec dbms_sqltune.accept_sql_profile(:task_name,force_match=>TRUE,replace=>TRUE);

一般调优报告中会给出具体的调用语句。Force_match默认为false，如果要对sql进行强制匹配，就设置为true，就是相同force_matching_signature值的sql都可以使用此sql profile。

2.改变已存在的sql profile的属性

可以调用存储过程dbms_sqltune.alter_sql_profile改变sql profile的status、name、description、category属性。

Status可以为enable与disable，表示是否启用sql profile。

示例：

Exec dbms_sqltune.alter_sql_profile(name=>:profile_name,attribute_name=>’STATUS’,VALUE=>’DISABLED’)

值得注意的是：sql profile的应用可以指定到session级别,通过session的sqltune_category参数来控制。

修改sql profile的category属性，同时修改session的sqltune_category参数。

Exec dbms_sqltune.alter_sql_profile(name=>:profile_name,attribute_name=>’CATEGORY’,VALUE=>’TEST’);

ALTER SESSION SET sqltune_category=’TEST’;

这样sql profile就只在此session中生效。

3.删除sql profile

调用存储过程dbms_sqltune.drop_sql_profile来删除sqlprofile。

示例：

Exec dbms_sqltune.drop_sql_profile(:profile_name);

总结：

本文先介绍了sqlprofile的概念与作用，接着举例说明了生成sqlprofile的两种方式，最后对管理控制sqlprofile进行了说明。本人认为sql profile可以替代语句中hint提示，而且管理起来更方便，值得推广使用。