自动创建SQL_profile 改变和稳定 SQL 执行计划

时间:2021-05-28 04:31:12
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000; 
 
SQL> create table t2 as select * from dba_objects;
 
SQL> create index t2_idx on t2(object_id); 
 
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');
 
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');
 
---对这条SQL 进行创建SQL_profile
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;
 
SQL> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4zbqykx89yc8v, child number 0
-------------------------------------
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and
t1.object_id=t2.object_id
 
Plan hash value: 1838229974
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   210 (100)|          |
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |  2500 |    97K|   210   (2)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |  2500 | 72500 |    54   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50605 |   543K|   155   (1)| 00:00:02 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
 
 
22 rows selected.
 
---创建sql_profile
SQL> var tuning_task varchar2(1000);
 
SQL> 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.
 
SQL> print tuning_task; 
 
TUNING_TASK
--------------------------------------------------------------------------------
TASK_179
 
 
 
SQL> SELECT dbms_sqltune.report_tuning_task('TASK_179') FROM dual; 
 
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_179
Tuning Task Owner                 : CQWR
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 12/29/2014 09:58:05
Completed at                      : 12/29/2014 09:58:05
Number of SQL Profile Findings    : 1
 
-------------------------------------------------------------------------------
Schema Name: CQWR
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)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
 为此语句找到了性能 
  Recommendation (estimated benefit: 52.34%)
  ------------------------------------------
  -考虑接受推荐的 SQL
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_179', replace
            => TRUE);
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    23 |   920 |   210   (2)| 00:00:03 |
|*  1 |  HASH JOIN         |      |    23 |   920 |   210   (2)| 00:00:03 |
|*  2 |   TABLE ACCESS FULL| T1   |    23 |   667 |    54   (2)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 50605 |   543K|   155   (1)| 00:00:02 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
 
2- Using SQL Profile
--------------------
Plan hash value: 3787413387
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |    23 |   920 |   100   (1)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    11 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |        |    23 |   920 |   100   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL        | T1     |    23 |   667 |    54   (2)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter("T1"."OBJECT_NAME" LIKE '%T1%')
   4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
 
-------------------------------------------------------------------------------
 
接受这个SQL Profile
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_179', replace=> TRUE ,force_match=>true);
 
--查看 已存在的 SQL Profile
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;
 
SQL>--删除SQL Profile
exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');
 
结论:对SQL语句,去掉重复的空格(不包括字符常量),将大小写转换成相同,比如均为大写(不包括字符常量)后,
如果SQL相同,那么SQL语句的exact_matching_signature就是相同的。
对SQL语句,去掉重复的空格(不包括字符常量),
将大小写转换成相同,比如均为大写(不包括字符常量),然后去掉SQL中的常量,如果SQL相同,
那么SQL语句的force_matching_signature就是相同的。
但是例外的情况是:如果SQL中有绑定变量,force_matching_signature
就会与exact_matching_signature一样的生成标准。