【DB笔试面试608】在Oracle中，如何使用STA来生成SQL Profile？

• 2019 年 10 月 10 日
• 筆記

♣

题目部分

在Oracle中，如何使用STA来生成SQL Profile？

♣

答案部分

利用STA对语句进行优化后，STA会对语句进行分析，采用最优的优化策略，并给出优化后的查询计划。可以按照STA给出的建议重写语句。但是，有些情况下，你可能无法重写语句（比如在生产环境中，SQL语句又在一个包中）。这个时候就可以利用Sql Profile，将优化策略存储在Profile中，Oracle在构建这条语句的查询计划时，就不会使用已有相关统计数据，而使用Profile的策略，生成新的查询计划。

第一步：给用户赋权限

[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba    SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016    Copyright (c) 1982, 2013, Oracle.  All rights reserved.      Connected to:  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  With the Partitioning, Real Application Clusters, OLAP, Data Mining  and Real Application Testing options    SYS@dlhr>  SYS@dlhr>  SYS@dlhr>  SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR;    Grant succeeded.    SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR;    Grant succeeded.    SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR;    Grant succeeded.    SYS@dlhr> conn lhr/lhr  Connected.  LHR@dlhr>  LHR@dlhr> select * from v$version;    BANNER  --------------------------------------------------------------------------------  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  PL/SQL Release 11.2.0.4.0 - Production  CORE    11.2.0.4.0      Production  TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production  NLSRTL Version 11.2.0.4.0 - Production    LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects;    Table created.    LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id);    Index created.    LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4);    PL/SQL procedure successfully completed.    LHR@dlhr> set autot on  LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;      COUNT(*)  ----------           1      Execution Plan  ----------------------------------------------------------  Plan hash value: 3612989399    -----------------------------------------------------------------------------------------  | Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |  |   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |  |*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |  -----------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - filter("OBJECT_ID"=100)      Statistics  ----------------------------------------------------------            1  recursive calls            0  db block gets         1249  consistent gets            0  physical reads            0  redo size          526  bytes sent via SQL*Net to client          520  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  LHR@dlhr> set autot off  LHR@dlhr> SELECT v.SQL_ID, v.SQL_TEXT FROM v$sql v WHERE v.SQL_TEXT like '%no_index(TB_LHR_20160525_01%' and v.SQL_TEXT not like '%v$sql%' ;    SQL_ID  -------------  SQL_TEXT  ------------------------------------------------------------------------------------------------------------------------------------  7jt1btjkcczb8  select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100    7suktf0w95cry  EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L  HR_20160525_01 where object_id = 100  

第二步：创建、执行优化任务

LHR@dlhr> DECLARE    2      my_task_name VARCHAR2(30);    3      my_sqltext       CLOB;    4  BEGIN    5      my_sqltext := 'select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100';    6      my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(    7                              sql_text          => my_sqltext,    8                              user_name       => 'LHR',    9                              scope           => 'COMPREHENSIVE',   10                              time_limit    => 60,   11                              task_name       => 'sql_profile_test',   12                              description => 'Task to tune a query on a specified table');   13      DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');   14  END;   15  /    PL/SQL procedure successfully completed.  

或者也可以使用sqlid来生成优化任务，如下：

LHR@dlhr> DECLARE    2    a_tuning_task VARCHAR2(30);    3  BEGIN    4    a_tuning_task := dbms_sqltune.create_tuning_task(sql_id    => '7jt1btjkcczb8',    5                                                     task_name => 'sql_profile_test_SQLID');    6    dbms_sqltune.execute_tuning_task(a_tuning_task);    7  END;    8  /    PL/SQL procedure successfully completed.  

第三步：查看优化建议

LHR@dlhr> set autot off  LHR@dlhr> set long 10000  LHR@dlhr> set longchunksize 1000  LHR@dlhr> set linesize 100  LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;    DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')  ----------------------------------------------------------------------------------------------------  GENERAL INFORMATION SECTION  -------------------------------------------------------------------------------  Tuning Task Name   : sql_profile_test  Tuning Task Owner  : LHR  Workload Type      : Single SQL Statement  Scope              : COMPREHENSIVE  Time Limit(seconds): 60  Completion Status  : COMPLETED  Started at         : 05/25/2016 16:58:31  Completed at       : 05/25/2016 16:58:32    -------------------------------------------------------------------------------  Schema Name: LHR  SQL ID     : 9kzm8scz6t92z  SQL Text   : select /*+no_index(TB_LHR_20160525_01               TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01               where object_id = 100    -------------------------------------------------------------------------------  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.83%)    ------------------------------------------    - Consider accepting the recommended SQL profile.      execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',              task_owner => 'LHR', 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):             .006278            .00004      99.36 %    CPU Time (s):                 .003397           .000021      99.38 %    User I/O Time (s):                  0                 0    Buffer Gets:                     1249                 2      99.83 %    Physical Read Requests:             0                 0    Physical Write Requests:            0                 0    DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')  ----------------------------------------------------------------------------------------------------    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: 3612989399    -----------------------------------------------------------------------------------------  | Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |  |   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |  |*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |  -----------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - filter("OBJECT_ID"=100)    2- Using SQL Profile  --------------------  Plan hash value: 661515879    --------------------------------------------------------------------------------------------  | Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |  --------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |  |   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |  |*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |  --------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------      DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')  ----------------------------------------------------------------------------------------------------     2 - access("OBJECT_ID"=100)    -------------------------------------------------------------------------------  

这里可以看到，在优化建议中给出了新的查询计划。现在，我们决定接受这个建议，并且不重写语句。

第四步：接受profile

LHR@dlhr> set autot on  LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;      COUNT(*)  ----------           1      Execution Plan  ----------------------------------------------------------    Plan hash value: 3612989399    -----------------------------------------------------------------------------------------  | Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |  |   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |  |*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |  -----------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - filter("OBJECT_ID"=100)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets         1249  consistent gets            0  physical reads            0  redo size          526  bytes sent via SQL*Net to client          520  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);    PL/SQL procedure successfully completed.    LHR@dlhr> set autot off  LHR@dlhr>  SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints    2     FROM dba_sql_profiles d,    3           dba_advisor_tasks e,    4          SYS.SQLOBJ$DATA A,    5          SYS.SQLOBJ$ B,    6          TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),    7                                    '/outline_data/hint'))) h    8    where a.signature = b.signature    9      and a.category = b.category   10      and a.obj_type = b.obj_type   11      and a.plan_id = b.plan_id   12      and a.signature = d.signature   13      and d.task_id=e.task_id   14      and d.name = 'SYS_SQLPROF_0154e728ad3f0000'   15     ;    TASK_NAME                      NAME  ------------------------------ ------------------------------  SQL_TEXT  ----------------------------------------------------------------------------------------------------  HINTS  ----------------------------------------------------------------------------------------------------  sql_profile_test               SYS_SQLPROF_0154e728ad3f0000  select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01  where object_id = 100  OPTIMIZER_FEATURES_ENABLE(default)    sql_profile_test               SYS_SQLPROF_0154e728ad3f0000  select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01  where object_id = 100  IGNORE_OPTIM_EMBEDDED_HINTS  

在这里用了包DBMS_SQLTUNE的另一个函数：ACCEPT_SQL_PROFILE。其中，参数task_name即我们创建的优化建议任务的名称，name是profile的名字，可以是任意合法名称。此外这个函数还有其他一些函数，下面是这个函数的原型：

DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (     task_name    IN  VARCHAR2,     object_id    IN  NUMBER   := NULL,     name         IN  VARCHAR2 := NULL,     description  IN  VARCHAR2 := NULL,     category     IN  VARCHAR2 := NULL;     task_owner   IN VARCHAR2  := NULL,     replace      IN BOOLEAN   := FALSE,     force_match  IN BOOLEAN   := FALSE)   RETURN VARCHAR2;  

Description是profile的描述信息；task_owner是优化建议任务的所有者；replace为TRUE时，如果这个profile已经存在，就代替它；force_match为TURE时，表示与语句强制匹配，即强制使用绑定变量，和系统参数cursor_sharing设置为FORCE时类似，为FALSE时，与cursor_sharing设置为EXACT时类似，即完全匹配。

这里要特别提到的是category这个参数，你可以通过设置这个参数，制定特定会话使用这个profile。在10g中，每个会话都有一个新参数SQLTUNE_CATEGORY，他的默认值是DEFAULT。而我们在调用这个函数时，如果没有指定这个参数，那它的值也是DEFAULT，而如果我们给这个profile指定了一个其它的CATEGORY值，如FOR_TUNING，那么只有会话参SQLTUNE_CATEGORY也为FOR_TUNING时，才会使用这个porfile。为什么说这个参数很有用呢？试想一个这样的环境：你在一个生产系统上利用STA调优一条语句，STA已经给出了优化建议，但是你又不敢贸然实施它给出的建议（毕竟它只是机器嘛，不能完全信任），你就可以创建一个有特殊CATEGORY的profile，然后在你自己的会话中制定SQLTUNE_CATEGORY为这个特殊的CATEGORY，那就既可以看优化建议的实际效果又不影响生产环境。

此外可以通过视图DBA_SQL_PROFILES来查看已经创建的profile。

第五步：查看profile的效果

LHR@dlhr> set autot on  LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;      COUNT(*)  ----------           1      Execution Plan  ----------------------------------------------------------  Plan hash value: 661515879    --------------------------------------------------------------------------------------------  | Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |  --------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |  |   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |  |*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |  --------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("OBJECT_ID"=100)    Note  -----     - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement      Statistics  ----------------------------------------------------------            1  recursive calls            0  db block gets            2  consistent gets            0  physical reads            0  redo size          526  bytes sent via SQL*Net to client          520  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed  

从NOTE部分可以看到，语句采用了profile中的数据，创建了新的查询计划。并且在查询计划中还有一些附加信息，表明这个语句是采用了“SYS_SQLPROF_0154e728ad3f0000”这个Profile，而不是根据对象上面的统计数据来生成的查询计划。

但上述方法主要是依赖sql tuning advisor,如果它无法生成你想要的执行计划.你还可以通过手动的方式,通过sql profile把hint加进去.复杂的SQL的hint可以采用脚本coe_xfr_sql_profile.sql来产生原语句的outline data和加hint语句的outline data，然后替换对应的SYS.SQLPROF_ATTR，最后执行生成的sql就可以了。

使用PLSQL DEVELOPER 11查看执行计划，如下图，新版本的好处：

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。