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

DB宝 2019-08-03
437
♣题目部分在Oracle中，如何使用STA来生成SQL Profile？

     
♣答案部分

利用STA对语句进行优化后，STA会对语句进行分析，采用最优的优化策略，并给出优化后的查询计划。可以按照STA给出的建议重写语句。但是，有些情况下，你可能无法重写语句（比如在生产环境中，SQL语句又在一个包中）。这个时候就可以利用Sql Profile，将优化策略存储在Profile中，Oracle在构建这条语句的查询计划时，就不会使用已有相关统计数据，而使用Profile的策略，生成新的查询计划。
第一步：给用户赋权限
  1[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba
  2
  3SQL*Plus: Release 11.2.0.4.0 Production on Wed May 25 16:47:29 2016
  4
  5Copyright (c) 1982, 2013, Oracle.  All rights reserved.
  6
  7
  8Connected to:
  9Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 10With the Partitioning, Real Application Clusters, OLAP, Data Mining
 11and Real Application Testing options
 12
 13SYS@dlhr> 
 14SYS@dlhr> 
 15SYS@dlhr> 
 16SYS@dlhr> GRANT CREATE ANY SQL PROFILE TO LHR;
 17
 18Grant succeeded.
 19
 20SYS@dlhr> GRANT DROP ANY SQL PROFILE TO LHR;
 21
 22Grant succeeded.
 23
 24SYS@dlhr> GRANT ALTER ANY SQL PROFILE TO LHR;
 25
 26Grant succeeded.
 27
 28SYS@dlhr> conn lhr/lhr
 29Connected.
 30LHR@dlhr> 
 31LHR@dlhr> select * from v$version;
 32
 33BANNER
 34--------------------------------------------------------------------------------
 35Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 36PL/SQL Release 11.2.0.4.0 - Production
 37CORE    11.2.0.4.0      Production
 38TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
 39NLSRTL Version 11.2.0.4.0 - Production
 40
 41LHR@dlhr> create table lhr.TB_LHR_20160525_01 as select * from dba_objects; 
 42
 43Table created.
 44
 45LHR@dlhr> create index lhr.TB_LHR_20160525_01_idx on TB_LHR_20160525_01(object_id);   
 46
 47Index created.
 48
 49LHR@dlhr> exec dbms_stats.gather_table_stats('lhr','TB_LHR_20160525_01',cascade=>true,degree=>4);   
 50
 51PL/SQL procedure successfully completed.
 52
 53LHR@dlhr> set autot on
 54LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
 55
 56  COUNT(*)
 57----------
 58         1
 59
 60
 61Execution Plan
 62----------------------------------------------------------
 63Plan hash value: 3612989399
 64
 65-----------------------------------------------------------------------------------------
 66| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
 67-----------------------------------------------------------------------------------------
 68|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |
 69|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |
 70|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |
 71-----------------------------------------------------------------------------------------
 72
 73Predicate Information (identified by operation id):
 74---------------------------------------------------
 75
 76   2 - filter("OBJECT_ID"=100)
 77
 78
 79Statistics
 80----------------------------------------------------------
 81          1  recursive calls
 82          0  db block gets
 83       1249  consistent gets
 84          0  physical reads
 85          0  redo size
 86        526  bytes sent via SQL*Net to client
 87        520  bytes received via SQL*Net from client
 88          2  SQL*Net roundtrips to/from client
 89          0  sorts (memory)
 90          0  sorts (disk)
 91          1  rows processed
 92LHR@dlhr> set autot off
 93LHR@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%' ;
 94
 95SQL_ID
 96-------------
 97SQL_TEXT
 98------------------------------------------------------------------------------------------------------------------------------------
 997jt1btjkcczb8
100select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100
101
1027suktf0w95cry
103EXPLAIN PLAN SET STATEMENT_ID='PLUS150249' FOR select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_L
104HR_20160525_01 where object_id = 100

复制
第二步：创建、执行优化任务
 1LHR@dlhr> DECLARE
 2  2      my_task_name VARCHAR2(30);
 3  3      my_sqltext       CLOB;
 4  4  BEGIN
 5  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  6      my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
 7  7                              sql_text          => my_sqltext,
 8  8                              user_name       => 'LHR',
 9  9                              scope           => 'COMPREHENSIVE',
10 10                              time_limit    => 60,
11 11                              task_name       => 'sql_profile_test',
12 12                              description => 'Task to tune a query on a specified table');
13 13      DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'sql_profile_test');
14 14  END;
15 15  /
16
17PL/SQL procedure successfully completed.

复制
或者也可以使用sqlid来生成优化任务，如下：
 1LHR@dlhr> DECLARE
 2  2    a_tuning_task VARCHAR2(30);
 3  3  BEGIN
 4  4    a_tuning_task := dbms_sqltune.create_tuning_task(sql_id    => '7jt1btjkcczb8',
 5  5                                                     task_name => 'sql_profile_test_SQLID');
 6  6    dbms_sqltune.execute_tuning_task(a_tuning_task);
 7  7  END;
 8  8  /
 9
10PL/SQL procedure successfully completed.

复制



第三步：查看优化建议

  1LHR@dlhr> set autot off
  2LHR@dlhr> set long 10000
  3LHR@dlhr> set longchunksize 1000
  4LHR@dlhr> set linesize 100
  5LHR@dlhr> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'sql_profile_test') from DUAL;
  6
  7DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
  8----------------------------------------------------------------------------------------------------
  9GENERAL INFORMATION SECTION
 10-------------------------------------------------------------------------------
 11Tuning Task Name   : sql_profile_test
 12Tuning Task Owner  : LHR
 13Workload Type      : Single SQL Statement
 14Scope              : COMPREHENSIVE
 15Time Limit(seconds): 60
 16Completion Status  : COMPLETED
 17Started at         : 05/25/2016 16:58:31
 18Completed at       : 05/25/2016 16:58:32
 19
 20-------------------------------------------------------------------------------
 21Schema Name: LHR
 22SQL ID     : 9kzm8scz6t92z
 23SQL Text   : select /*+no_index(TB_LHR_20160525_01
 24             TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
 25             where object_id = 100
 26
 27-------------------------------------------------------------------------------
 28FINDINGS SECTION (1 finding)
 29-------------------------------------------------------------------------------
 30
 311- SQL Profile Finding (see explain plans section below)
 32--------------------------------------------------------
 33  A potentially better execution plan was found for this statement.
 34
 35  Recommendation (estimated benefit: 99.83%)
 36  ------------------------------------------
 37  - Consider accepting the recommended SQL profile.
 38    execute dbms_sqltune.accept_sql_profile(task_name => 'sql_profile_test',
 39            task_owner => 'LHR', replace => TRUE);
 40
 41  Validation results
 42  ------------------
 43  The SQL profile was tested by executing both its plan and the original plan
 44  and measuring their respective execution statistics. A plan may have been
 45  only partially executed if the other could be run to completion in less time.
 46
 47                           Original Plan  With SQL Profile  % Improved
 48                           -------------  ----------------  ----------
 49  Completion Status:            COMPLETE          COMPLETE
 50  Elapsed Time (s):             .006278            .00004      99.36 %
 51  CPU Time (s):                 .003397           .000021      99.38 %
 52  User I/O Time (s):                  0                 0
 53  Buffer Gets:                     1249                 2      99.83 %
 54  Physical Read Requests:             0                 0
 55  Physical Write Requests:            0                 0
 56
 57DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
 58----------------------------------------------------------------------------------------------------
 59  Physical Read Bytes:                0                 0
 60  Physical Write Bytes:               0                 0
 61  Rows Processed:                     1                 1
 62  Fetches:                            1                 1
 63  Executions:                         1                 1
 64
 65  Notes
 66  -----
 67  1. Statistics for the original plan were averaged over 10 executions.
 68  2. Statistics for the SQL profile plan were averaged over 10 executions.
 69
 70-------------------------------------------------------------------------------
 71EXPLAIN PLANS SECTION
 72-------------------------------------------------------------------------------
 73
 741- Original With Adjusted Cost
 75------------------------------
 76Plan hash value: 3612989399
 77
 78-----------------------------------------------------------------------------------------
 79| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
 80-----------------------------------------------------------------------------------------
 81|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |
 82|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |
 83|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |
 84-----------------------------------------------------------------------------------------
 85
 86Predicate Information (identified by operation id):
 87---------------------------------------------------
 88
 89   2 - filter("OBJECT_ID"=100)
 90
 912- Using SQL Profile
 92--------------------
 93Plan hash value: 661515879
 94
 95--------------------------------------------------------------------------------------------
 96| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
 97--------------------------------------------------------------------------------------------
 98|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |
 99|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |
100|*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |
101--------------------------------------------------------------------------------------------
102
103Predicate Information (identified by operation id):
104---------------------------------------------------
105
106
107DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_PROFILE_TEST')
108----------------------------------------------------------------------------------------------------
109   2 - access("OBJECT_ID"=100)
110
111-------------------------------------------------------------------------------

复制

这里可以看到，在优化建议中给出了新的查询计划。现在，我们决定接受这个建议，并且不重写语句。
第四步：接受profile
 1LHR@dlhr> set autot on
 2LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
 3
 4  COUNT(*)
 5----------
 6         1
 7
 8
 9Execution Plan
10----------------------------------------------------------
11
12Plan hash value: 3612989399
13
14-----------------------------------------------------------------------------------------
15| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
16-----------------------------------------------------------------------------------------
17|   0 | SELECT STATEMENT   |                    |     1 |     5 |   351   (2)| 00:00:05 |
18|   1 |  SORT AGGREGATE    |                    |     1 |     5 |            |          |
19|*  2 |   TABLE ACCESS FULL| TB_LHR_20160525_01 |     1 |     5 |   351   (2)| 00:00:05 |
20-----------------------------------------------------------------------------------------
21
22Predicate Information (identified by operation id):
23---------------------------------------------------
24
25   2 - filter("OBJECT_ID"=100)
26
27
28Statistics
29----------------------------------------------------------
30          0  recursive calls
31          0  db block gets
32       1249  consistent gets
33          0  physical reads
34          0  redo size
35        526  bytes sent via SQL*Net to client
36        520  bytes received via SQL*Net from client
37          2  SQL*Net roundtrips to/from client
38          0  sorts (memory)
39          0  sorts (disk)
40          1  rows processed
41LHR@dlhr> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_test_SQLID', task_owner => 'LHR', replace => TRUE);
42
43PL/SQL procedure successfully completed.
44
45LHR@dlhr> set autot off
46LHR@dlhr>  SELECT e.task_name, b.name, d.sql_text, extractvalue(value(h), '.') as hints
47  2     FROM dba_sql_profiles d,
48  3           dba_advisor_tasks e,
49  4          SYS.SQLOBJ$DATA A,
50  5          SYS.SQLOBJ$ B,
51  6          TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),
52  7                                    '/outline_data/hint'))) h
53  8    where a.signature = b.signature
54  9      and a.category = b.category
55 10      and a.obj_type = b.obj_type
56 11      and a.plan_id = b.plan_id
57 12      and a.signature = d.signature 
58 13      and d.task_id=e.task_id
59 14      and d.name = 'SYS_SQLPROF_0154e728ad3f0000'
60 15     ;
61
62TASK_NAME                      NAME
63------------------------------ ------------------------------
64SQL_TEXT
65----------------------------------------------------------------------------------------------------
66HINTS
67----------------------------------------------------------------------------------------------------
68sql_profile_test               SYS_SQLPROF_0154e728ad3f0000
69select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
70where object_id = 100
71OPTIMIZER_FEATURES_ENABLE(default)
72
73sql_profile_test               SYS_SQLPROF_0154e728ad3f0000
74select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01
75where object_id = 100
76IGNORE_OPTIM_EMBEDDED_HINTS

复制
在这里用了包DBMS_SQLTUNE的另一个函数：ACCEPT_SQL_PROFILE。其中，参数task_name即我们创建的优化建议任务的名称，name是profile的名字，可以是任意合法名称。此外这个函数还有其他一些函数，下面是这个函数的原型：
 1DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
 2   task_name    IN  VARCHAR2,
 3   object_id    IN  NUMBER   := NULL,
 4   name         IN  VARCHAR2 := NULL,
 5   description  IN  VARCHAR2 := NULL,
 6   category     IN  VARCHAR2 := NULL;
 7   task_owner   IN VARCHAR2  := NULL,
 8   replace      IN BOOLEAN   := FALSE,
 9   force_match  IN BOOLEAN   := FALSE)
10 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的效果
 1LHR@dlhr> set autot on
 2LHR@dlhr> select /*+no_index(TB_LHR_20160525_01 TB_LHR_20160525_01_idx)*/count(*) from lhr.TB_LHR_20160525_01 where object_id = 100 ;
 3
 4  COUNT(*)
 5----------
 6         1
 7
 8
 9Execution Plan
10----------------------------------------------------------
11Plan hash value: 661515879
12
13--------------------------------------------------------------------------------------------
14| Id  | Operation         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
15--------------------------------------------------------------------------------------------
16|   0 | SELECT STATEMENT  |                        |     1 |     5 |     1   (0)| 00:00:01 |
17|   1 |  SORT AGGREGATE   |                        |     1 |     5 |            |          |
18|*  2 |   INDEX RANGE SCAN| TB_LHR_20160525_01_IDX |     1 |     5 |     1   (0)| 00:00:01 |
19--------------------------------------------------------------------------------------------
20
21Predicate Information (identified by operation id):
22---------------------------------------------------
23
24   2 - access("OBJECT_ID"=100)
25
26Note
27-----
28   - SQL profile "SYS_SQLPROF_0154e728ad3f0000" used for this statement
29
30
31Statistics
32----------------------------------------------------------
33          1  recursive calls
34          0  db block gets
35          2  consistent gets
36          0  physical reads
37          0  redo size
38        526  bytes sent via SQL*Net to client
39        520  bytes received via SQL*Net from client
40          2  SQL*Net roundtrips to/from client
41          0  sorts (memory)
42          0  sorts (disk)
43          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程序员面试笔试宝典》，作者：李华荣。