SQL Profiles-AUTO

谷梁俊楚

2023-12-01

http://www.laoxiong.net/sql-profiles-part.html
http://www.laoxiong.net/sql-profiles-partii.html

SQL Profiles可以说是Outlines的进化。Outlines能够实现的功能SQL Profiles也完全能够实现，而SQL Profiles具有Outlines不具备的优化，个人认为最重要的有2点：
SQL Profiles更容易生成、更改和控制。
SQL Profiles在对SQL语句的支持上做得更好，也就是适用范围更广。

使用SQL Profiles无非是两个目的：
锁定或者说是稳定执行计划。
在不能修改应用中的SQL的情况下使SQL语句按指定的执行计划运行。

在我看来，SQL Profile就是为某一SQL语句提供除了系统统计信息、对象（表和索引等）统计信息之外的其他信息，比如运行环境、额外的更准确的统计信息，以帮助优化器为SQL语句选择更适合的执行计划.

创建测试表

YALI@fyl>create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
YALI@fyl>create table t2 as select * from dba_objects; 
YALI@fyl>create index t2_idx on t2(object_id); 
YALI@fyl>exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');
YALI@fyl>exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');
执行一条测试语句
YALI@fyl>set autotrace traceonly
YALI@fyl>select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
36 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2500 |   100K|   333   (1)| 00:00:04 |
|*  1 |  HASH JOIN         |      |  2500 |   100K|   333   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 75000 |    71   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 65483 |   703K|   262   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1188  consistent gets
          0  physical reads
          0  redo size
       1882  bytes sent via SQL*Net to client
        441  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed

首先从执行计划可以看到，这条SQL语句在2个表上都是全表扫描。在第1个表T1上，有 like ‘%T1%’这样的条件，导致只能全表扫描，这没有问题。但是第2个表，也是全表扫描，这里有没有问题呢？或者说是有没有优化的余地，答案显然是肯定的。
这里的问题在于执行计划ID=1的那一行，Oracle优化器评估T1 like ‘%T1%’返回的结果行数为2500行，即T1表总行数的5%，如果2个表采用index range scan+nested loop连接，oracle评估的成本会高于full table scan+hash join。下面可以看到Oracle优化器评估的index range_scan+nested loop的成本：

YALI@fyl>select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id; 

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |  2500 |   100K|  5072   (1)| 00:01:01 |
|   1 |  NESTED LOOPS                |        |       |       |            |          |
|   2 |   NESTED LOOPS               |        |  2500 |   100K|  5072   (1)| 00:01:01 |
|*  3 |    TABLE ACCESS FULL         | T1     |  2500 | 75000 |    71   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        308  consistent gets
          0  physical reads
          0  redo size
       1882  bytes sent via SQL*Net to client
        441  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         36  rows processed
从执行计划可以看到Oracle优化器评估的成本为5072，远远高于原来的333。加了HINT之后实际的逻辑读只有308，低于原始SQL的1188。所以这里可以看出来，由于Oracle优化器过高地估计了T1表经过like操作过滤返回的行数，也就过高地估计了nest loop的成本，最终也就选择了不是最优的执行计划。

下面我们用Oracle的SQL Tuning Advisor来尝试这条SQL：

var tuning_task varchar2(100);  
DECLARE  
  l_sql_id v$session.prev_sql_id%TYPE;  
  l_tuning_task VARCHAR2(30);  
BEGIN  
  l_sql_id:='4zbqykx89yc8v';  
  l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);  
  :tuning_task:=l_tuning_task;  
  dbms_sqltune.execute_tuning_task(l_tuning_task);  
  dbms_output.put_line(l_tuning_task);  
END;      
/  
  
PL/SQL procedure successfully completed.

YALI@fyl>print tuning_task

TUNING_TASK
----------------------
TASK_241

set long 20000
SELECT dbms_sqltune.report_tuning_task('TASK_241') FROM dual;  --也可以查询USER_ADVISOR_LOG

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_241
Tuning Task Owner  : YALI
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 07/10/2014 14:09:56
Completed at       : 07/10/2014 14:10:00


DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
-----------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: YALI
SQL ID     : 4zbqykx89yc8v
SQL Text   : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
             and t1.object_id=t2.object_id

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)

为此语句找到性能

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
-------------------------------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 74.41%)
  ------------------------------------------
  推荐的sql
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_241',
            task_owner => 'YALI', replace => TRUE);

  Validation results
  ------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
-----------------------------------------------------------------------------
  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(us):               62350             54735      12.21 %
  CPU Time(us):                   59691             52492      12.06 %
  User I/O Time(us):                  0                 0
  Buffer Gets:                     1066               269      74.76 %

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
--------------------------------------------------------------------------
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                    36                36
  Fetches:                           36                36
  Executions:                         1                 1

  Notes
  -----
  1. The original plan was first executed to warm the buffer cache.

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
-------------------------------------------------------------------------------
  2. Statistics for original plan were averaged over next 9 executions.
  3. The SQL profile plan was first executed to warm the buffer cache.
  4. Statistics for the SQL profile plan were averaged over next 9 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    36 |  1476 |   333   (1)| 00:00:04 |
|*  1 |  HASH JOIN         |      |    36 |  1476 |   333   (1)| 00:00:04 |
|*  2 |   TABLE ACCESS FULL| T1   |    36 |  1080 |    71   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 65483 |   703K|   262   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
---------------------------------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)

2- Using SQL Profile
--------------------
Plan hash value: 1022743391

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   143   (1)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |       |       |            |          |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   143   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    71   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_241')
--------------------------------------------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

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

结果看起来非常棒，SQL Tuning Advisor为我们找到了理想的执行计划，T1表上经过谓词过滤后返回的行数评估为29，相当地精确。我们要做的就是Accept SQL Profile，接受这个SQL Profile。
YALI@fyl> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_241',task_owner => 'YALI', replace => TRUE);   ---可以加上force_match => TRUE

PL/SQL procedure successfully completed.

那么我们再执行SQL看看：
YALI@fyl> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    36 |  1476 |   143   (1)| 00:00:02 |
|   1 |  NESTED LOOPS                |        |       |       |            |          |
|   2 |   NESTED LOOPS               |        |    36 |  1476 |   143   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T1     |    36 |  1080 |    71   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
              NULL)
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - SQL profile "SYS_SQLPROF_01471efdf4870000" used for this statement   ---使用sql profile
   
从执行计划的”Note“一节也可以看到，SQL Profile起作用了。SQL Profile的名字为”SYS_SQLPROF_01471efdf4870000″。

YALI@fyl>select name,category,signature,type,status,force_matching from dba_sql_profiles;

NAME                           CATEGORY                        SIGNATURE TYPE    STATUS   FOR  --不强制匹配
------------------------------ ------------------------------ ---------- ------- -------- ---
SYS_SQLPROF_01471efdf4870000   DEFAULT                        8.9755E+18 MANUAL  ENABLED  NO
值得注意的是，Sql profiles的force_match属性是不能更改的，只能在创建时指定，如果要更改，则只能重新创建改Sql Profile。
一些复杂的SQL，我经常会先通过SQL Tuning Advisor来分析一下，看能不能让Oracle自已找出一个更好的执行计划。
我们来看看，SQL Profiles实际上是些什么：
10g 查看sys.sqlprof$attr  11g查看SYS.SQLOBJ$  SYS.SQLOBJ$DATA  SYS.SQLOBJ$AUXDATA
YALI@fyl>SELECT SIGNATURE,COMP_DATA  FROM SYS.SQLOBJ$DATA;

 SIGNATURE COMP_DATA
---------- --------------------------------------------------------------------------------
8.9755E+18 <outline_data><hint><![CDATA[OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_R
           OWS=0.0144)]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE(default)]]></hint>
           </outline_data>
只是SQL Profiles中的Hint，没有指定SQL使用哪个索引，也没有指定表的连接方法和连接顺序。这里只指定了T1表评估返回的行数，与原始的评估返回的行数的放大缩小的倍数。2500*0.0144正好为36。这里就是告诉Oracle优化器，T1表经过谓语过滤后返回行数应该为评估的0.01161091426倍。从这里可以看出，SQL Profiles并不会锁定SQL的执行计划，只是提供了更多、更准确的统计信息给优化器

看下面测试：
SYS@fyl>exec dbms_stats.set_table_stats('YALI','T1',numrows=>5000000); 
YALI@fyl>select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3600 |   144K|   383  (14)| 00:00:05 |
|*  1 |  HASH JOIN         |      |  3600 |   144K|   383  (14)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| T1   |  3600 |   105K|   121  (43)| 00:00:02 |
|   3 |   TABLE ACCESS FULL| T2   | 65483 |   703K|   262   (1)| 00:00:04 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
              NOT NULL)

Note
-----
   - SQL profile "SYS_SQLPROF_01471efdf4870000" used for this statement
   
将T1表的统计信息中的表行数改为500万，Oracle就会评估为返回5000000*5%*0.0144=3600行。这里执行计划又变回为full scan+hash join。可以看到，虽然SQL Profile起作用了，但是并没有锁定执行计划。
小结：本文简单介绍了什么是SQL Profiles及其作用，如何使用SQL Tuning Advisor来生成SQL Profile，以及生成的SQL Profile产生的Hint。同时也介绍了SQL的signature。
还原T1表数据
SYS@fyl> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014723fc6f0d0000');
SYS@fyl> exec dbms_stats.set_table_stats('YALI','T1',numrows=>50000);

SQL Profiles-AUTO

相关阅读

相关文章

相关问答

相关文档