固定執行計劃-SQL PROFILE手工綁定

固定（穩定）執行計劃你的應用的功能時快時慢，變化比較大，功能的性能能夠保持一種穩定的狀態，ORACLE 固定執行計劃，採用以下這幾種方式 oracle 9i使用 Outline oracle 10g採用 sql profile oracle 11g增加了sql plan manage oracle ...

固定（穩定）執行計劃

你的應用的功能時快時慢，變化比較大，功能的性能能夠保持一種穩定的狀態，ORACLE 固定執行計劃，採用以下這幾種方式

oracle 9i使用 Outline
oracle 10g採用 sql profile
oracle 11g增加了sql plan manage

oracle 10g採用 sql profile ：兩種模式

從SQL語句歷史的執行計劃，找到一個合理的，進行綁定
還有一種無法從歷史的執行計劃找到合理的，只能手工構造進行綁定

提供腳本

create_sql_profile

提供綁定shared pool中已有的執行計劃中，找一個綁定或自己構造一個綁定

----------------------------------------------------------------------------------------
--
-- File name:   create_sql_profile.sql
--
-- Purpose:     Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
--
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for four values.
--
--              sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool),if sql_id is not shared pool,must be bulid sql plan
--
--              child_no: the child_no of the statement from v$sql
--
--              new_sql_id:需要綁定的SQL語句
--
--              profile_name: the name of the profile to be generated
--
--              category: the name of the category for the profile
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description:
--
--              Based on a script by Randolf Giest.
--
-- Mods:        This is the 2nd version of this script which removes dependency on rg_sqlprof1.sql.
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--

-- @rg_sqlprof1 '&&sql_id' &&child_no '&&new_sql_id' '&&category' '&force_matching'

set feedback off
set sqlblanklines on

accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept child_no -
       prompt 'Enter value for child_no (0): ' -
       default '0'
accept new_sql_id -
       prompt 'Enter value for new_sql_id: ' -
       default '0'
accept profile_name -
       prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (TRUE): ' -
       default 'TRUE'

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
l_profile_name varchar2(30);
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id = '&&sql_id'
and child_number = &&child_no
and other_xml is not null
)
) d;

select
sql_fulltext,
decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
into
cl_sql_text, l_profile_name
from
v$sqlarea
where
sql_id = '&&new_sql_id';

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&category',
name => l_profile_name,
force_match => &&force_matching
-- replace => true
);

  dbms_output.put_line(' ');
  dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
  dbms_output.put_line(' ');

exception
when NO_DATA_FOUND then
  dbms_output.put_line(' ');
  dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
  dbms_output.put_line('ERROR: sql_id: '||'&&new_sql_id'||' not found in v$sqlarea.');
  dbms_output.put_line(' ');

end;
/

undef sql_id
undef new_sql_id
undef child_no
undef profile_name
undef category
undef force_matching

set sqlblanklines off
set feedback on

create_sql_profile

CREATE_SQL_PROFILE_AWR

綁定AWR中歷史的計劃中其他一個

----------------------------------------------------------------------------------------
--
-- File name:   create_sql_profile_awr.sql
--
-- Purpose:     Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
--
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for five values.
--
--              sql_id: the sql_id of the statement to attach the profile to
--              (must be in the shared pool and in AWR history)
--
--              plan_hash_value: the plan_hash_value of the statement in AWR history
--
--              profile_name: the name of the profile to be generated
--
--              category: the name of the category for the profile
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description:
--
--              Based on a script by Randolf Giest.
--
-- Mods:        This is the 2nd version of this script which removes dependency on rg_sqlprof2.sql.
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--

-- @rg_sqlprof1 '&&sql_id' &&child_no '&&category' '&force_matching'

set feedback off
set sqlblanklines on

accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept plan_hash_value -
       prompt 'Enter value for plan_hash_value: '
accept profile_name -
       prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (FALSE): ' -
       default 'false'

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
l_profile_name varchar2(30);
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '&&sql_id'
and plan_hash_value = &&plan_hash_value
and other_xml is not null
)
) d;

select
sql_text,
decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||'&&plan_hash_value','&&profile_name')
into
cl_sql_text, l_profile_name
from
dba_hist_sqltext
where
sql_id = '&&sql_id';

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&category',
name => l_profile_name,
force_match => &&force_matching
-- replace => true
);

  dbms_output.put_line(' ');
  dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
  dbms_output.put_line(' ');

exception
when NO_DATA_FOUND then
  dbms_output.put_line(' ');
  dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Plan: '||'&&plan_hash_value'||' not found in AWR.');
  dbms_output.put_line(' ');

end;
/

undef sql_id
undef plan_hash_value
undef profile_name
undef category
undef force_matching

set sqlblanklines off
set feedback on

create_sql_profile_awr

sql_profile_hints

顯示sql profile中的HINT信息

----------------------------------------------------------------------------------------
--
-- File name:   profile_hints.sql
--
-- Purpose:     Show hints associated with a SQL Profile.
-
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for one value.
--
--              profile_name: the name of the profile to be modified
--
-- Description: This script pulls the hints associated with a SQL Profile.
--
-- Mods:        Modified to check for 10g or 11g as the hint structure changed.
--              Modified to join on category as well as signature.
--
--              See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--
set sqlblanklines on
set feedback off
accept profile_name -
       prompt 'Enter value for profile_name: ' -
       default 'X0X0X0X0'

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
version varchar2(3);
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin
 select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error
   'select attr_val as outline_hints '||
   'from dba_sql_profiles p, sqlprof$attr h '||
   'where p.signature = h.signature '||
   'and p.category = h.category  '||
   'and name like (''&&profile_name'') '||
   'order by attr#'
   bulk collect
   into ar_profile_hints;

elsif version = '11' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error
   'select hint as outline_hints '||
   'from (select p.name, p.signature, p.category, row_number() '||
   '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
   '      extractValue(value(t), ''/hint'') hint '||
   'from sqlobj$data sd, dba_sql_profiles p, '||
   '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
   '                               ''/outline_data/hint''))) t '||
   'where sd.obj_type = 1 '||
   'and p.signature = sd.signature '||
   'and p.category = sd.category '||
   'and p.name like (''&&profile_name'')) '||
   'order by row_num'
   bulk collect
   into ar_profile_hints;

end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('HINT');
  dbms_output.put_line('------------------------------------------------------------------------------------------------------------------------------------------------------');
  for i in 1..ar_profile_hints.count loop
    dbms_output.put_line(ar_profile_hints(i));
  end loop;
  dbms_output.put_line(' ');
  dbms_output.put_line(ar_profile_hints.count||' rows selected.');
  dbms_output.put_line(' ');

end;
/
undef profile_name
set feedback on

sql_profile_hints

一、SQL 綁定現有執行計劃

一個SQL存在多個執行計劃，選擇其中一個固定

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

SQL_ID  4hpk08j31nm7y, child number 0
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   228 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO"=30)
 
SQL_ID  4hpk08j31nm7y, child number 2
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     6 |   228 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=30)

語句綁定第一個子游標為固定的執行計劃：全表掃描

sys@GULL> @create_sql_profile
Enter value for sql_id: 4hpk08j31nm7y
Enter value for child_no (0): 0
Enter value for new_sql_id: 4hpk08j31nm7y
Enter value for profile_name (PROF_sqlid_planhash): 
Enter value for category (DEFAULT): 
Enter value for force_matching (TRUE): 
原值   19: sql_id = '&&sql_id'
新值   19: sql_id = '4hpk08j31nm7y'
原值   20: and child_number = &&child_no
新值   20: and child_number = 0
原值   27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
新值   27: decode('X0X0X0X0','X0X0X0X0','PROF_4hpk08j31nm7y'||'_'||plan_hash_value,'X0X0X0X0')
原值   33: sql_id = '&&new_sql_id';
新值   33: sql_id = '4hpk08j31nm7y';
原值   38: category => '&&category',
新值   38: category => 'DEFAULT',
原值   40: force_match => &&force_matching
新值   40: force_match => TRUE
原值   51:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
新值   51:   dbms_output.put_line('ERROR: sql_id: '||'4hpk08j31nm7y'||' Child: '||'0'||' not found in v$sql.');
原值   52:   dbms_output.put_line('ERROR: sql_id: '||'&&new_sql_id'||' not found in v$sqlarea.');
新值   52:   dbms_output.put_line('ERROR: sql_id: '||'4hpk08j31nm7y'||' not found in v$sqlarea.');
SQL Profile PROF_4hpk08j31nm7y_1404472509 created.

執行相同的SQL語句運行

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

SQL_ID  4hpk08j31nm7y, child number 0
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   228 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO"=30)
 
Note
-----
   - SQL profile PROF_4hpk08j31nm7y_1404472509 used for this statement

在Note信息中可以看到sql profile的信息， - SQL profile PROF_4hpk08j31nm7y_1404472509 used for this statement，說明已經強制使用了手工綁定的執行計劃，之後這個語句就一直採用全表掃描了，不會再走索引的訪問方式

二、SQL綁定AWR中的執行計劃

shared pool中之前沒有合適的執行計劃，你可以在awr（DBMS_XPLAN.DISPLAY_AWR）中查找歷史的執行計劃，查詢到了，採用create_sql_profile_awr這個過程來綁定

構造一個SQL語句兩個執行計劃，保存到AWR中

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

alter session set optimizer_index_cost_adj=500

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

查看AWR中的執行計劃

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

SQL_ID 4hpk08j31nm7y
--------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     6 |   522 |     2   (0)| 00:00:01 |
|   2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
SQL_ID 4hpk08j31nm7y
--------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| EMP  |     6 |   522 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement (level=2)

使SQL語句固定走索引的處理模式

SQL> set serveroutput on
SQL> @create_sql_profile_awr.sql
Enter value for sql_id: 4hpk08j31nm7y
Enter value for plan_hash_value: 1404472509
Enter value for profile_name (PROF_sqlid_planhash): 
Enter value for category (DEFAULT): 
Enter value for force_matching (FALSE): TRUE
原值   19: sql_id = '&&sql_id'
新值   19: sql_id = '4hpk08j31nm7y'
原值   20: and plan_hash_value = &&plan_hash_value
新值   20: and plan_hash_value = 1404472509
原值   27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||'&&plan_hash_value','&&profile_name')
新值   27: decode('X0X0X0X0','X0X0X0X0','PROF_4hpk08j31nm7y'||'_'||'1404472509','X0X0X0X0')
原值   33: sql_id = '&&sql_id';
新值   33: sql_id = '4hpk08j31nm7y';
原值   38: category => '&&category',
新值   38: category => 'DEFAULT',
原值   40: force_match => &&force_matching
新值   40: force_match => TRUE
原值   51:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Plan: '||'&&plan_hash_value'||' not found in AWR.');
新值   51:   dbms_output.put_line('ERROR: sql_id: '||'4hpk08j31nm7y'||' Plan: '||'1404472509'||' not found in AWR.');
SQL Profile PROF_4hpk08j31nm7y_1404472509 create

重新執行SQL語句並查看執行計劃

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID  4hpk08j31nm7y, child number 0
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     4 |   348 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=30)
 
Note
-----
   - SQL profile PROF_4hpk08j31nm7y_1404472509 used for this statement

note 信息中已經使用了sql profile，而且語句也是走索引

三、SQL綁定構造的執行計劃

shared pool和awr中沒有一個合適的，需要自己構造這個sql語句的執行計劃，進行偷梁換柱

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

SQL_ID  4hpk08j31nm7y, child number 0
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 3956160932
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   228 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTNO"=30)

可以構造一個走deptno索引的，在走索引的執行計划去替換全表

select /*+index(emp index_emp_deptno)*/ * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor(null,null))

SQL_ID  2hdyvqk9b09va, child number 0
-------------------------------------
select /*+index(emp index_emp_deptno)*/ * from  scott.emp  where 
deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |    10 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     6 |   228 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     6 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=30)

可以使用SQL_ID 2hdyvqk9b09va, child number 0，來替換之前SQL_ID 4hpk08j31nm7y, child number 0的執行計劃

sys@GULL> @create_sql_profile
Enter value for sql_id: 2hdyvqk9b09va
Enter value for child_no (0): 0
Enter value for new_sql_id: 4hpk08j31nm7y
Enter value for profile_name (PROF_sqlid_planhash): 
Enter value for category (DEFAULT): 
Enter value for force_matching (TRUE): 
原值   19: sql_id = '&&sql_id'
新值   19: sql_id = '2hdyvqk9b09va'
原值   20: and child_number = &&child_no
新值   20: and child_number = 0
原值   27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
新值   27: decode('X0X0X0X0','X0X0X0X0','PROF_2hdyvqk9b09va'||'_'||plan_hash_value,'X0X0X0X0')
原值   33: sql_id = '&&new_sql_id';
新值   33: sql_id = '4hpk08j31nm7y';
原值   38: category => '&&category',
新值   38: category => 'DEFAULT',
原值   40: force_match => &&force_matching
新值   40: force_match => TRUE
原值   51:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
新值   51:   dbms_output.put_line('ERROR: sql_id: '||'2hdyvqk9b09va'||' Child: '||'0'||' not found in v$sql.');
原值   52:   dbms_output.put_line('ERROR: sql_id: '||'&&new_sql_id'||' not found in v$sqlarea.');
新值   52:   dbms_output.put_line('ERROR: sql_id: '||'4hpk08j31nm7y'||' not found in v$sqlarea.');
SQL Profile PROF_2hdyvqk9b09va_3956160932 created.

再次查看原始語句的執行計劃

select * from  scott.emp  where deptno=30

select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))

SQL_ID  4hpk08j31nm7y, child number 0
-------------------------------------
select * from  scott.emp  where deptno=30
 
Plan hash value: 1404472509
 
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |       |       |    10 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP              |     6 |   228 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDEX_EMP_DEPTNO |     6 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("DEPTNO"=30)
 
Note
-----
   - SQL profile PROF_2hdyvqk9b09va_3956160932 used for this statement

偷梁換柱完成，操作起來也是很方便。

四、查看sql profile hint信息

SQL> @sql_profile_hints.sql
Enter value for profile_name: PROF_4hpk08j31nm7y_1404472509
原值   19:    'and name like (''&&profile_name'') '||
新值   19:    'and name like (''PROF_4hpk08j31nm7y_1404472509'') '||
原值   38:    'and p.name like (''&&profile_name'')) '||
新值   38:    'and p.name like (''PROF_4hpk08j31nm7y_1404472509'')) '||
HINT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))
6 rows selected.

下一篇講解一些用coe_xfr_sql_profile腳本去綁定執行計劃