本文介绍了Oracle中SQL Profile和SPM（SQL Plan Management）如何用于绑定和稳定执行计划。SQL Profile分为Automatic和Manual类型，提供执行计划调整。SPM则是主动稳定执行计划的手段，通过SQL plan baseline确保最优执行。文章详细阐述了两种方法的原理、操作步骤，并提及了相关参数的设置与使用。
点击上方"蓝字"
关注我们，享更多干货！
在Oracle中时长会出现一直运行正常的SQL突然运行很慢的问题。检查发现执行计划发生了改变，这时候就需要绑定执行计划。在Oracle 10g以后的版本中，可以使用SQL Profile或SPM（11g以后）来绑定执行计划。
1、SQL Profile
Oracle 10g中的SQL Profile是Oracle 9i中的stored outline的进化升级版，相比stored outline有以下优点：
更容易生成、更改和控制。
对SQL语句的支持做的更高，适用范围广。
SQL Profile有两种类型：
①Automatic类型
②Manual类型。
1.Automatic类型
Automatic类型的SQL Profile起始就是针对目标SQL的一些额外的调整信息，这些信息存储在数据字典中，有了Automatic类型的SQL Profile之后Oracle就会根据它目标SQL所涉及的统计信息等内容做相应的调整。
从某种意义上说Automatic类型的SQL Profile并不能完全起到稳定目标SQL执行计划的作用，但是的确可以用来调整执行计划。
缺点：只能用于单条语句固定执行计划，并不能广泛应用于生产中。
SQL> create table tab_1(id number);
Table created.
SQL> declare
2 begin
3 for i in 1..10000
4 loop
5 insert into tab_1 values(i);
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(1) from tab_1;
COUNT(1)
----------
10000
SQL> create index id_ind on tab_1(id);
Index created.
SQL> conn / as sysdba
Connected.
SQL> exec dbms_stats.gather_table_stats('scott','tab_1');
PL/SQL procedure successfully completed.
SQL> conn scott/scott
Connected.
SQL> select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
--发现id=100执行计划为 TABLE ACCESS FULL
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6dmxvq99wtg3h, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 2157271952
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_1 | 1 | 4 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@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" "TAB_1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
42 rows selected.
--创建一个SQL_TUNNING
SQL>declare
2 my_task_name varchar2(30);
3 sql_text clob;
4 begin
5 sql_text :='select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100';
6 my_task_name := dbms_sqltune.create_tuning_task(
7 sql_text =>sql_text,
8 user_name =>'SCOTT',
9 scope => 'COMPREHENSIVE',
10 time_limit =>60,
11 task_name =>'my_sql_tunning',
12 description=>'task to tune a query on tabe tab_1');
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
--执行SQL_TUNNING
SQL> begin
2 dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tunning');
3 end;
4 /
PL/SQL procedure successfully completed.
--查看SQL_TUNNING结果
SQL> set long 9000;
SQL> set longchunksize 1000;
SQL> set linesize 800;
SQL> select dbms_sqltune.report_tuning_task('my_sql_tunning') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNNING')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tunning
Tuning Task Owner : SCOTT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/02/2019 05:08:52
Completed at : 05/02/2019 05:08:56
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : 7494g56v1caaz
SQL Text : select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans p below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
--为此句找到了性能更好的执行计划。
Recommendation (estimated benefit: 90.91%)
------------------------------------------
- Consider accepting the recommended SQL profile.
--考虑接受推荐的SQL概要文件
execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tunning',
task_owner => 'SCOTT', 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): .00019 .000016 91.57 %
CPU Time (s): .000199 0 100 %
User I/O Time (s): 0 0
Buffer Gets: 22 2 90.9 %
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: 2157271952
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB_1 | 1 | 4 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
2- Using SQL Profile
--------------------
Plan hash value: 1273297064
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| ID_IND | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=100)
-------------------------------------------------------------------------------
--执行接受更优的执行计划
SQL> execute dbms_sqltune.accept_sql_profile(task_name=>'my_sql_tunning',task_owner=>'SCOTT',replace=>TRUE);
PL/SQL procedure successfully completed.
SQL> select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
--再次查看执行计划，发现执行计划已经更改
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bu4t0t209yx51, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 1273297064
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| ID_IND | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@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(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
Note
-----
- SQL profile SYS_SQLPROF_016a75433f370000 used for this statement
46 rows selected.
--当换了id值之后发现之前绑定的执行计划已经失效。
SQL> select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=200;
ID
----------
200
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bsxxutpyyphsd, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=200
Plan hash value: 2157271952
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_1 | 1 | 4 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@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" "TAB_1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=200)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
42 rows selected.2.Manual类型
Manual类型的SQL Profile本质上就是一堆hint的组合，它可以在不更改SQL文本的前提下启到很大的稳定目标SQL执行计划的作用。
步骤：
①找出问题SQL并记录SQL_ID和PLAN_HASH_VALUE，使用hint优化该SQL，记录优化好的SQL_ID和PLAN_HASH_VALUE。
②运行coe_xfr_sql_profile.sql，将原SQL生成的文本记录文本A，优化后SQL生成的文本记录文本B，将文本B中的hint部分“偷梁换柱”到文本A中，并将文本A中force_match参数改为TRUE。
③运行修改后的文本A，再次执行原SQL确认执行计划。做到了不修改SQL文本固定执行计划。
承接上述实验，删除上述automatic类型的Profile，SYS_SQLPROF_016a75433f370000。
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_016a75433f370000');
PL/SQL procedure successfully completed.
--再次查看执行计划，发现已经是全表扫描。记录sql_id和plan_hash_value。
SQL> select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bu4t0t209yx51, child number 0
-------------------------------------
select /*+ no_index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 2157271952
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| TAB_1 | 1 | 4 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@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" "TAB_1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
42 rows selected.
--对当前有问题的SQL进行hint优化，记录优化后的sql_id和plan_hash_value。
SQL> select /*+ index(tab_1 id_ind) */ * from tab_1 where id=100;
ID
----------
100
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9bb97qbdhpf3a, child number 0
-------------------------------------
select /*+ index(tab_1 id_ind) */ * from tab_1 where id=100
Plan hash value: 1273297064
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| ID_IND | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TAB_1@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(@"SEL$1" "TAB_1"@"SEL$1" ("TAB_1"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22]
42 rows selected.
--查看问题SQL优化前后的SQL_ID和PLAN_HASH_VALUE，此为测试环境，生产环境中应先记录原SQL的sql_id和PLAN_HASH_VALUE。
SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%* from tab_1 where id=100%';
SQL_TEXT SQL_ID VERSION_COUNT
------------------------------------------------------------ -----------
最低0.47元/天 解锁文章
扫一扫
728
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
