sql profile简介

程序员文章站 2022-06-10 16:15:07

...

关于绑定sql的执行计划个人一直用的是oracle的sql plan baseline，其实oracle 10g就推出了sql profile来绑定执行计划，下面来简单的了解下oracle的sql profile的功能。 sql profile大体是实现两个功能： 1绑定现有sql的执行计划 2 在不修改代码的情况下使目

关于绑定sql的执行计划个人一直用的是oracle的sql plan baseline，其实oracle 10g就推出了sql profile来绑定执行计划，下面来简单的了解下oracle的sql profile的功能。

sql profile大体是实现两个功能：
1绑定现有sql的执行计划
2 在不修改代码的情况下使目标sql语句按照执行的执行计划执行，这两个功能sql plan baseline也是可以实现的，而比sql profile更加优秀的就是sql plan baseline还能够在sql运行是生成更优秀的执行计划基线，我们可以演练这个新的sql plan baseline来决定是否采用这个sql plan baseline。

sql profile有两种类型：一种是automatic类型，另一种是manual类型

automatic类型的sql profile是针对目标sql获取到一些额外的调整信息（类似oracle的动态采样来采集额外的信息），这些信息存储在数据字典中，当有了automatic类型的sql profile后，优化器产生执行计划时会根据目标sql所涉及统计信息等内容做相应的调整来保证选择最优的执行计划。

需要注意的automatic的sql profile并不是像stored outlines、sql plan baseline那样锁定目标sql的执行计划，automatic的sql profile在原则上只是提供了一些额外的统计信息，这些额外的统计信息必须于原目标sql的涉及的相关统计内容一起作用才能得到新的执行计划，如果原sql的统计信息等内容发生重大变化，即使原有的automatic类型的sql profile没有改变，该sql的执行计划也可能会发生变化，接下来xiaoyu会贴出部分case以供大家参考。

SQL>create table t_auto01 as select * from dba_objects;
SQL>create index ind_objectid on t_auto01(object_id);
SQL> select max(object_id) from t_auto01;

MAX(OBJECT_ID)
--------------
87823
SQL>update t_auto01 set object_id=100000 where object_id SQL>commit;
SQL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T_AUTO01',cascade=>true);

SQL>set autotrace traceonly;
SQL> select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where object_id=100000;

86366 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86348 | 8095K| 1388 (1)| 00:00:17 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 86348 | 8095K| 1388 (1)| 00:00:17 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 86348 | | 153 (0)| 00:00:02 |
--------------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=100000)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12806 consistent gets
0 physical reads
0 redo size
9767726 bytes sent via SQL*Net to client
63850 bytes received via SQL*Net from client
5759 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86366 rows processed

用STA生成automatic sql profile

SQL> select sql_id from v$sql where sql_text like 'select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where object_id=100000%';

SQL_ID
-------------
5tvdfn4y8z5gg

declare
my_task_name varchar2(30);
begin
my_task_name:=dbms_sqltune.create_tuning_task(
sql_id=>'601ccgpfh9jcv',
scope=>'COMPREHENSIVE',
task_name=>'my_tuning_task'
);
dbms_sqltune.execute_tuning_task(task_name=>'my_tuning_task');
end;
/

SQL> set long 100000
SQL> select dbms_sqltune.report_tuning_task(task_name=>'my_tuning_task') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(TASK_NAME=>'MY_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 12/09/2014 19:01:37
Completed at : 12/09/2014 19:01:37

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 5tvdfn4y8z5gg
SQL Text : select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where
object_id=100000

-------------------------------------------------------------------------------
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: 10.83%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_tuning_task',
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): .018247 .007709 57.75 %
CPU Time (s): .002199 .000699 68.21 %
User I/O Time (s): 0 0
Buffer Gets: 1386 1236 10.82 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 86366 86366
Fetches: 86366 86366
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: 4224651809

--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 86348 | 8095K| 1388 (1)
| 00:00:17 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 86348 | 8095K| 1388 (1)
| 00:00:17 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 86348 | | 153 (0)
| 00:00:02 |
--------------------------------------------------------------------------------
------------

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

2 - access("OBJECT_ID"=100000)

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

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86348 | 8095K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 86348 | 8095K| 336 (1)| 00:00:05 |
------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=100000)

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

antomatic sql profile已经生效：

SQL>execute dbms_sqltune.accept_sql_profile(task_name => 'my_tuning_task’, task_owner => 'SYS', replace => TRUE);
SQL> select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where object_id=100000;

86366 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86348 | 8095K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 86348 | 8095K| 336 (1)| 00:00:05 |
------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=100000)

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

即使此时sql text有出现大小写、空格类、换行等的变化，sql profile依然可以生效

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01 where object_id=100000;

86366 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86348 | 8095K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 86348 | 8095K| 336 (1)| 00:00:05 |
------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=100000)

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

即使表发生了ddl，只要不影响原来的sql执行，相应的sql profile依然生效。

SQL> alter table t_auto01 add edate date;

Table altered.

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01 where object_id=100000;

86366 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86348 | 8095K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 86348 | 8095K| 336 (1)| 00:00:05 |
------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=100000)

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

如果我们具体谓词条件对应的具体值出现了变化，此时sql profile是没有办法生效的。

SQL> select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where object_id=1000001;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 1 | 96 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=1000001)

当然如果sql text出现了类似下列的变更，同样sql profile也无法生效。

SQL> select /*+index(t_auto01 ind_objectid)*/* from t_auto01 a where object_id=100000;

86366 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86348 | 8095K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 86348 | 8095K| 336 (1)| 00:00:05 |
------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=100000)

那么接下来还有一个问题，就是如果我们的应用程序没有写bind value，但是我们要绑定一系列的值都走同一类的profile，其实这个也比较容易，oracle提供的dbms_sqltune.accept_sql_profile中有个参数是force_match参数，该参数默认是false，当修改为true后，谓词的具体值即使发生了变化，sql profile依然生效。

我们发现此时加上了force_matching=>true参数后，谓词具体对应值变化的sql走了一个新的sql profile SYS_SQLPROF_014a323f8ddc0001，而且这个sql profile并不走SYS_SQLPROF_014a3230758f0000的全表扫描的执行计划，而是走更加高效的index range scan，这里需要明确加上force_match=>true新生成的sql profile不一定跟之前的sql profile执行计划一样，因为automatic sql profile仅仅只是一些额外的统计信息来保证生成更准确高效的执行计划。

同样还需要注意的即使是同一个sql profile也可能会产生不同的执行计划：

SQL> exec dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_014a323f8ddc0001’);
SQL> exec dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_014a3230758f0000’);
SQL> exec dbms_sqltune.drop_tuning_task(task_name=>'my_tuning_task');

重新生成一个sql profile
SQL> declare
2 my_task_name varchar2(30);
3 begin
4 my_task_name:=dbms_sqltune.create_tuning_task(
5 sql_id=>'601ccgpfh9jcv',
6 scope=>'COMPREHENSIVE',
7 task_name=>'my_tuning_task'
8 );
9 dbms_sqltune.execute_tuning_task(task_name=>'my_tuning_task');
10 end;
11 /

PL/SQL procedure successfully completed.

SQL> set long 199999
SQL> select dbms_sqltune.report_tuning_task(task_name=>'my_tuning_task') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(TASK_NAME=>'MY_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 12/09/2014 19:57:36
Completed at : 12/09/2014 19:57:36

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 601ccgpfh9jcv
SQL Text : select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01 where
object_id=100000

-------------------------------------------------------------------------------
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: 10.82%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_tuning_task',
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): .021363 .00914 57.21 %
CPU Time (s): .021396 .009198 57.01 %
User I/O Time (s): 0 0
Buffer Gets: 1385 1236 10.75 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 86371 86371
Fetches: 86371 86371
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: 4224651809

--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 86363 | 8096K| 1386 (1)
| 00:00:17 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 86363 | 8096K| 1386 (1)
| 00:00:17 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 86363 | | 152 (0)
| 00:00:02 |
--------------------------------------------------------------------------------
------------

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

2 - access("OBJECT_ID"=100000)

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

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86363 | 8096K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 86363 | 8096K| 336 (1)| 00:00:05 |
------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=100000)

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

force_match方式accept sql profile：

SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'my_tuning_task',task_owner => 'SYS', replace => TRUE,force_match=>true);

PL/SQL procedure successfully completed.

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01 where object_id=100000;

86371 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86363 | 8096K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 86363 | 8096K| 336 (1)| 00:00:05 |
------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=100000)

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

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
6913 consistent gets
1 physical reads
0 redo size
4045316 bytes sent via SQL*Net to client
63861 bytes received via SQL*Net from client
5760 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86371 rows processed

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01 where object_id=1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 1 | 96 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=1)

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

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

更新t_auto01表的数据全部为object_id=1，造成数据倾斜

SQL> update t_auto01 set object_id=1;
86371 rows updated.
SQL> commit;
Commit complete.

这里的sql profile对应的执行计划依然是index range scan

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01 where object_id=1;

86371 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 1 | 96 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJECTID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=1)

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12957 consistent gets
0 physical reads
0 redo size
9768433 bytes sent via SQL*Net to client
63861 bytes received via SQL*Net from client
5760 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86371 rows processed

重新收集下统计信息：

SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T_AUTO01',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01 where object_id=1;

86371 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86363 | 8096K| 336 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 86363 | 8096K| 336 (1)| 00:00:05 |
------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=1)

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

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12957 consistent gets
0 physical reads
0 redo size
9768433 bytes sent via SQL*Net to client
63861 bytes received via SQL*Net from client
5760 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86371 rows processed

sql profle对应的执行计划变成了full table scan。

这里我们需要明确的是如果重新收集了统计信息，automatic的sql profile的执行计划是有可能会发生变化的。

由于automatic sql profile只是一些额外的统计信息，这个将导致如果sql涉及的表统计信息发生变化，automatic的sql profile将无法绑定目标sql的执行计划。

下面来介绍下manual类型的sql profile，manual sql profile实际上就是一堆hint的组合，它能很好的绑定目标sql的执行计划，这个跟automatic sql profile是不相同的。

manual类型的sql profile同样可以在不改变目标sql的sql文本情况下调整其执行计划，

SQL> create table t_manual01 as select * from dba_objects;

Table created.

SQL> create index ind_manual_objid on t_manual01(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T_MANUAL01',cascade=>true);

PL/SQL procedure successfully completed.

此时由于全部object_id为1，全表扫描将更加适合

SQL> select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 371934742

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_MANUAL01 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_MANUAL_OBJID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=1)

SQL> select /*+full(t_manual01)*/* from t_manual01 where object_id=1;

86320 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1705140427

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_MANUAL01 | 1 | 98 | 344 (1)| 00:00:05 |
--------------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=1)

SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like 'select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ------------------------------------------------------------
5usjcvmsxj6mb 371934742 select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1

SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like 'select /*+full(t_manual01)*/* from t_manual01 where object_id=1%';

SQL_ID PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ------------------------------------------------------------
fp5ng25383cvk 1705140427 select /*+full(t_manual01)*/* from t_manual01 where object_id=1

SQL> select * from table(dbms_xplan.display_cursor('5usjcvmsxj6mb',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5usjcvmsxj6mb, child number 0
-------------------------------------
select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where
object_id=1

Plan hash value: 371934742

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T_MANUAL01 | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_MANUAL_OBJID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

1 - SEL$1 / T_MANUAL01@SEL$1
2 - SEL$1 / T_MANUAL01@SEL$1

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T_MANUAL01"@"SEL$1" ("T_MANUAL01"."OBJECT_ID"))
END_OUTLINE_DATA
*/

SQL> select * from table(dbms_xplan.display_cursor('fp5ng25383cvk',null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fp5ng25383cvk, child number 0
-------------------------------------
select /*+full(t_manual01)*/* from t_manual01 where object_id=1

Plan hash value: 1705140427

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 344 (100)| |
|* 1 | TABLE ACCESS FULL| T_MANUAL01 | 1 | 98 | 344 (1)| 00:00:05 |
--------------------------------------------------------------------------------

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

1 - SEL$1 / T_MANUAL01@SEL$1

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

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T_MANUAL01"@"SEL$1")
END_OUTLINE_DATA
*/

这种移花接木的基本思路为：
1 coe_xfr_sql_profile.sql脚本生成目标sql的manual sql profile脚本
2 针对目标sql添加hint，直到sql能走出你需要的执行计划，针对这个sql用coe_xfr_sql_profile.sql脚本生成manual sql profile脚本
3 用修改后的sql生成的manual sql profile脚本中的outline data部分替换掉目标sql对应的manual sql profile脚本
4 执行目标sql的manual sql profile脚本生成manual sql profile

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 5usjcvmsxj6mb

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
371934742 .052

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 371934742

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "5usjcvmsxj6mb"
PLAN_HASH_VALUE: "371934742"

SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&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 SQL_ID &&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>SET TERM OFF;

Execute coe_xfr_sql_profile_5usjcvmsxj6mb_371934742.sql
on TARGET system in order to create a custom SQL Profile
with plan 371934742 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: fp5ng25383cvk

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1705140427 .06

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1705140427

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "fp5ng25383cvk"
PLAN_HASH_VALUE: "1705140427"

SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&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 SQL_ID &&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>SET TERM OFF;

Execute coe_xfr_sql_profile_fp5ng25383cvk_1705140427.sql
on TARGET system in order to create a custom SQL Profile
with plan 1705140427 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

生成两个sql_id脚本后

[oracle@redhat_ora11g ~]$ vi coe_xfr_sql_profile_5usjcvmsxj6mb_371934742.sql
。。。
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+index(t_manual01 ind_manual_objid)
*/* from t_manual01 where object_id=1
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "T_MANUAL01"@"SEL$1" ("T_MANUAL01"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_5usjcvmsxj6mb_371934742',
description => 'coe 5usjcvmsxj6mb 371934742 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
。。。

[oracle@redhat_ora11g ~]$ more coe_xfr_sql_profile_fp5ng25383cvk_1705140427.sql
。。。
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+full(t_manual01)
*/* from t_manual01 where object_id=1
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T_MANUAL01"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_fp5ng25383cvk_1705140427',
description => 'coe fp5ng25383cvk 1705140427 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
。。。

force_match => FALSE根据实际情况修改，默认为false，如果修改为true，谓词的具体值发生变化，sql profile依然生效。

将目标sql也就是coe_xfr_sql_profile_5usjcvmsxj6mb_371934742.sql脚本中的如下部分
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "T_MANUAL01"@"SEL$1" ("T_MANUAL01"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');

替换为coe_xfr_sql_profile_fp5ng25383cvk_1705140427.sql脚本中的如下部分
。。。
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "T_MANUAL01"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
。。。

这里也很好理解，其实manual sql profile也就是Outline Data的信息，manual sql profile就是用Outline Data来固化sql的执行计划。

替换完毕后再次执行脚本就将目标sql绑定了一个manual sql profile

SQL> @coe_xfr_sql_profile_5usjcvmsxj6mb_371934742.sql
...
SQL> DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 BEGIN
5 sql_txt := q'[
6 select /*+index(t_manual01 ind_manual_objid)
7 */* from t_manual01 where object_id=1
8 ]';
9 h := SYS.SQLPROF_ATTR(
10 q'[BEGIN_OUTLINE_DATA]',
11 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
12 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
13 q'[DB_VERSION('11.2.0.4')]',
14 q'[ALL_ROWS]',
15 q'[OUTLINE_LEAF(@"SEL$1")]',
16 q'[FULL(@"SEL$1" "T_MANUAL01"@"SEL$1")]',
17 q'[END_OUTLINE_DATA]');
18 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
19 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
20 sql_text => sql_txt,
21 profile => h,
22 name => 'coe_5usjcvmsxj6mb_371934742',
23 description => 'coe 5usjcvmsxj6mb 371934742 '||:signature||'',
24 category => 'DEFAULT',
25 validate => TRUE,
26 replace => TRUE,
27 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
28 END;
29 /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;
SQL> select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1;

86320 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1705140427

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 344 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| T_MANUAL01 | 1 | 98 | 344 (1)| 00:00:05 |
--------------------------------------------------------------------------------

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

相关标签： sql profile 简介关于绑定 sql 执行计划

上一篇：有关Menu的文章推荐10篇

下一篇： sqlite里面支持像sqlserver的like的用法吗？

sql profile简介

sql中groupby的rollup、Cube、Sets用法对比详解

Oracle的pl/sql编程基础

css3动画简介以及动画库animate.css的使用_html/css_WEB-ITnose

SQL Server数据库实现自动备份的实例解决方法

SQL Server报错汇总

MySql的sql语句涉及group/sum/limit/结果集多字段,如何写

SQL Server 安全性管理的途径

MySQL 最高速清空数据表数据的 SQL 语句

sql话语正确但找不到记录

PHP+Mysql实现多关键字与多字段生成SQL语句的函数