sql profile: 如何使用我们自定义的sql 执行计划来固定原sql执行计划 及 如何优化非绑定变量sql

##测试环境搭建：
SQL> create table test (n number );
Table created.

SQL> declare
          begin
           for i in 1 .. 10000 loop
               insert into test values(i);
               commit;
           end loop;
          end;
/
PL/SQL procedure successfully completed.

SQL> create index test_idx on test(n);
Index created.


##SQL> exec dbms_stats.gather_table_stats('','TEST');
##PL/SQL procedure successfully completed.


---刷新内存中中执行计划，避免干扰
SQL>alter system flush shared_pool;

原始sql执行计划如下，走 INDEX RANGE SCAN ，假设现在我们希望让该sql 执行计划走全表扫描

set autotrace on
SQL>select * from test where n=1;
                   N
--------------------
                   1
Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
        173  recursive calls
          0  db block gets
        146  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
          1  rows processed




SQL>select * from test where n=2;
                   N
--------------------
                   2
Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N"=2)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed




####我们手动调整后的sql 语句，让其走全表扫描：


SQL>select /*+ no_index(test test_idx) */ * from test where n=1;
                   N
--------------------
                   1

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         49  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


------现在我们使用coe_load_sql_profile.sql 来更改原来走索引范围扫描sql的执行计划

首先先获取相应sql 的 sql id 信息：
SQL>select sql_id ,sql_text from v$sql where sql_text like '%n=1%';

SQL_ID
---------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
apxdba96mcfqt
select /*+ no_index(test test_idx) */ * from test where n=1

gkqwyzq133fbs
select * from test where n=1


！！！！可选操作
在执行脚本前我们需要修改coe_load_sql_profile.sql中一个选项：
force_match => FALSE 
/* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
缺省为false，这里建议设置成true，如果该sql 没有使用绑定变量就可以使用这个参数来优化一类literal sql语句执行计划


SQL> conn system/oracle
Connected.
SQL> @coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: gkqwyzq133fbs
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: apxdba96mcfqt

     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
          1357081020                 .005


Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 1357081020

Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "gkqwyzq133fbs"
MODIFIED_SQL_ID: "apxdba96mcfqt"
PLAN_HASH_VALUE: "1357081020"


SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for modified SQL_ID &&modified_sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>
SQL>SET ECHO OFF;
0001 BEGIN_OUTLINE_DATA
0002 IGNORE_OPTIM_EMBEDDED_HINTS
0003 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
0004 DB_VERSION('11.2.0.3')
0005 ALL_ROWS
0006 OUTLINE_LEAF(@"SEL$1")
0007 FULL(@"SEL$1" "TEST"@"SEL$1")
0008 END_OUTLINE_DATA
dropping staging table "STGTAB_SQLPROF_GKQWYZQ133FBS"
creating staging table "STGTAB_SQLPROF_GKQWYZQ133FBS"
packaging new sql profile into staging table "STGTAB_SQLPROF_GKQWYZQ133FBS"


PROFILE_NAME
------------------------------
GKQWYZQ133FBS_1357081020
SQL>REM
SQL>REM SQL Profile
SQL>REM ~~~~~~~~~~~
SQL>REM
SQL>SELECT signature, name, category, type, status
  2    FROM dba_sql_profiles WHERE name = :name;


           SIGNATURE NAME                              CATEGORY             TYPE           STATUS
-------------------- ------------------------------------------------------------------------------------------ 
 5913879575249888386 GKQWYZQ133FBS_1357081020          DEFAULT              MANUAL         ENABLED
SQL>SELECT description
  2    FROM dba_sql_profiles WHERE name = :name;


DESCRIPTION
---------------------------------------------------------------------------------------------------------------
ORIGINAL:GKQWYZQ133FBS MODIFIED:APXDBA96MCFQT PHV:1357081020 SIGNATURE:6074333813144582766 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;


****************************************************************************
* Enter SYSTEM password to export staging table STGTAB_SQLPROF_gkqwyzq133fbs
****************************************************************************
Export: Release 11.2.0.3.0 - Production on Wed Jun 21 15:54:19 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password: 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported


About to export specified tables via Conventional Path ...
. . exporting table   STGTAB_SQLPROF_GKQWYZQ133FBS          1 rows exported
Export terminated successfully without warnings.

If you need to implement this Custom SQL Profile on a similar system,
import and unpack using these commands:

imp SYSTEM file=STGTAB_SQLPROF_gkqwyzq133fbs.dmp tables=STGTAB_SQLPROF_gkqwyzq133fbs ignore=Y

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => 'GKQWYZQ133FBS_1357081020',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_gkqwyzq133fbs',
staging_schema_owner => 'SYSTEM' );
END;
/

updating: coe_load_sql_profile_gkqwyzq133fbs.log (deflated 78%)
updating: STGTAB_SQLPROF_gkqwyzq133fbs.dmp (deflated 89%)
  adding: coe_load_sql_profile.log (deflated 62%)


deleting: coe_load_sql_profile.log
coe_load_sql_profile completed.
SQL>




---测试我们替换后sql profile效果：


SQL>select * from test where n=1;
                   N
--------------------
                   1
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |   208 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |    16 |   208 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=1)
Note
-----
   - SQL profile "GKQWYZQ133FBS_1357081020" used for this statement--------我们修改过的sql profile
Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         29  consistent gets
          1  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

查看是否可以优化其他变换n 条件后sql？？？？

当 n 的条件变更后查看是否还是会使用我们设置过的sql profile
SQL>select * from test where n=2;
                   N
--------------------
                   2
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |   208 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |    16 |   208 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=2)
Note
-----
   - SQL profile "GKQWYZQ133FBS_1357081020" used for this statement--------我们修改过的sql profile
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


再换几组数据测试测试：
SQL>select * from test where n=3;
                   N
--------------------
                   3
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |   208 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |    16 |   208 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=3)
Note
-----
   - SQL profile "GKQWYZQ133FBS_1357081020" used for this statement--------我们修改过的sql profile
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>select * from test where    n=10;    
                   N
--------------------
                  10
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    16 |   208 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |    16 |   208 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=10)
Note
-----
   - SQL profile "GKQWYZQ133FBS_1357081020" used for this statement--------我们修改过的sql profile
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         24  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

          1  rows processed

让我们看下原sql语句改变sql文本后的执行计划
SQL>select /* lixora*/ * from test where    n=10;    
                   N
--------------------
                  10
Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   1 - access("N"=10)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
        519  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>