sqlprofile

1、对于执行计划变差的SQL，根据历史的执行计划，固定好的执行计划，从而达到优化的目的（dbms_sqltune.accept_sql_profile）
2、应用的SQL因为错加了hint导致走了差的执行计划，在不能修改应用中的SQL的情况下，构造好的执行计划，使SQL语句按指定的执行计划运行。（dbms_sqltune.import_sql_profile）


#看表的统计信息
select * from dba_tab_statistics where table_name='EMP';



##########################################################
exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task_2');

exec dbms_sqltune.drop_sql_profile('coe_3pbh60ggjzvkh_2949544139'); 

DECLARE
  my_task_name varchar2(30);
  my_sqltext clob;
 begin
      my_sqltext :='select /*+no_index(t1 PK_EMP) */ * from emp t1 where empno=7369';
      my_task_name :=dbms_sqltune.create_tuning_task(
                     sql_text =>my_sqltext,
                     user_name=>'SCOTT',
                     scope    =>'COMPREHENSIVE',
                     time_limit =>60,
                     task_name =>'my_sql_tuning_task_2',
                     description=>'task to tune a query on table t1'
      	             );

 end;
 /




begin
    dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2');
end;
/


select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual;


set long 10000
SYS@PROD>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_2
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 12/12/2018 03:40:31
Completed at       : 12/12/2018 03:40:31

-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 9tzxhjjyx02u9
SQL Text   : select /*+no_index(t1 PK_EMP) */ * from emp t1 where empno=7369

-------------------------------------------------------------------------------
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: 66.72%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_2', task_owner => 'SYS', 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):             .000021           .000008       61.9 %
  CPU Time (s):                   .0001                 0        100 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                        6                 2      66.66 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  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: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7369)

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

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7369)

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


可以看出使用SQL Profile 可以使用索引
接受建议:

########################################################################################################################
exec dbms_sqltune.accept_sql_profile(task_name=>'my_sql_tuning_task_2',task_owner=>'SYS',replace=>TRUE,force_match=>true); 

force_match=true --

explain plan for select /*+no_index(t1 PK_EMP) */ * from emp t1 where empno=7369;

select * from table(dbms_xplan.display(null,null,'advanced'));


SCOTT@PROD>select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T1@SEL$1
   2 - SEL$1 / T1@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("EMP"."EMPNO"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - access("EMPNO"=7369)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMPNO"[NUMBER,22], "T1"."ENAME"[VARCHAR2,10], "T1"."JOB"[VARCHAR2,9],
       "T1"."MGR"[NUMBER,22], "T1"."HIREDATE"[DATE,7], "T1"."SAL"[NUMBER,22],
       "T1"."COMM"[NUMBER,22], "T1"."DEPTNO"[NUMBER,22]
   2 - "T1".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

Note
-----
   - SQL profile "SYS_SQLPROF_01679efefd170000" used for this statement

46 rows selected.


Manaul;

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_0167b783c8740001');


SCOTT@PROD>explain plan for select /*+index(t1 PK_EMP) */ * from emp t1 where empno=7369;

Explained.

SCOTT@PROD>select  sql_text,sql_id,version_count from v$sqlarea where sql_text='select /*+index(t1 PK_EMP) */ * from emp t1 where empno=7369';

SQL_TEXT                                                                         SQL_ID        VERSION_COUNT
-------------------------------------------------------------------------------- ------------- -------------
select /*+index(t1 PK_EMP) */ * from emp t1 where empno=7369                     3pbh60ggjzvkh             1



SCOTT@PROD>select plan_hash_value from v$sql where sql_id='3pbh60ggjzvkh';

PLAN_HASH_VALUE
---------------
     2949544139



@/home/oracle/coe_xfr_sql_profile.sql

输入SQL_ID 和 SQL_HASH_VALUE

生成 coe_xfr_sql_profile_3pbh60ggjzvkh_2949544139.sql


然后执行@/home/oracle/coe_xfr_sql_profile_3pbh60ggjzvkh_2949544139.sql