SQL PROFILE在ORACLE10g中引入,从官方文档和网络资料可以看出,SQL PROFILE的主要目的侧重于SQL优化,其底层的实现细节可能主要依赖于hint。与outline相比,个人人文SQLPROFILE侧重于为优化器提供辅助信息从而促使优化器选择最优的执行计划,而outline侧重于固化执行计划,两者的目的是有区别的。但是由于SQL PROFILE和OUTLINE的底层技术支持均为hint,因此sqlprofile也可以实现固化执行计划的目的。
我们知道oracle在执行sql语句时会尽力选择最优的执行计划,但是出于各个方面的平衡,oracle最终选择的执行计划未必是最优的。在这种情况下,oracle10g引入了自动调整优化器(AUTOMATIC TUNING OPTIMIZER),自动调整优化器作为优化器的一个扩展而存在。在正常环境下,oracle优化器会在最快的时间内获取执行计划,不会利用自动调整优化器。当我们需要调优某条sql语句时,我们将该语句提交给自动调优优化器,自动调优优化器会尽可能选择最优执行计划,而不会考虑选择该执行计划所花费的时间,甚至在某些情况下,自动调整优化器会进行what-if分析,或者实际的执行sql语句来验证执行计划的效率。
下面的图片展示了SQL PROFILE的生成和使用过程:
生成和管理SQL PROFILE可以通过DBMS_SQLTUNE或者企业管理器的图形界面,下面我们使用DBMS_SQLTUNE来演示SQL PROFILE的管理操作,聪明的你如果可以使用DBMS_SQLTUNE来管理SQLPROFILE,肯定也可以使用图形界面。
首先构建我们的测试环境
SQL> create table t1 as select object_id,object_name from dba_objects where rownum<=50000;
表已创建。
SQL> create table t2 as select * from dba_objects;
表已创建。
SQL> create index t2_idx on t2(object_id);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');
PL/SQL 过程已成功完成。
用到的脚本
SQL> !cat tune_last_statement.sql
declare
l_sql_id v$sql.sql_id%TYPE;
l_tuning_task varchar2(30);
begin
select sql_id into l_sql_id from v$sql where sql_text like 'select t1.*,t2.owner from t1,t2 where t1.object_name%' and rownum =1;
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
end
在原始状态下的sql语句执行过程
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' AND T1.object_id=t2.object_id;
已选择36行。
执行计划
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 370 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 2500 | 100K| 370 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 75000 | 69 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 75308 | 808K| 300 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
统计信息
----------------------------------------------------------
0 recursive calls
1 db block gets
317 consistent gets
0 physical reads
0 redo size
2141 bytes sent via SQL*Net to client
542 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed
这时候进行了全表扫描,逻辑读数量为317.
对sql语句进行调优
SQL> @tune_last_statement.sql
11 /
任务_309
查看调优报告:
SQL> select dbms_sqltune.report_tuning_task('任务_309') as re from dual;
RE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 任务_309
Tuning Task Owner : EASY
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 01/06/2014 23:36:02
Completed at : 01/06/2014 23:36:02
-------------------------------------------------------------------------------
Schema Name: EASY
SQL ID : 31hpx02tsna89
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
AND T1.object_id=t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划。
Recommendation (estimated benefit: 76.77%)
------------------------------------------
- 考虑接受推荐的 SQL 概要文件。
execute dbms_sqltune.accept_sql_profile(task_name => '任务_309', task_owner
=> 'EASY', replace => TRUE);
Validation results
------------------
已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,
则另一计划可能只执行了一部分。
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .022829 .013985 38.74 %
CPU Time (s): .022696 .013897 38.76 %
User I/O Time (s): 0 0
Buffer Gets: 1330 308 76.84 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 36 36
Fetches: 36 36
Executions: 1 1
Notes
-----
1. the original plan 的统计信息是 10 执行的平均值。
2. the SQL profile plan 的统计信息是 10 执行的平均值。
2- Alternative Plan Finding
---------------------------
通过搜索系统的实时和历史性能数据找到了此语句的某些替代执行计划。
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 1022743391 2014-01-06/22:56:07 0.022 Cursor Cache
Information
-----------
- 因为找不到原始计划的任何执行历史记录, SQL 优化指导无法确定这些执行计划中是否有一些执行计划优于原始计划。但是,
如果您知道某个替代计划优于原始计划, 可以为该替代计划创建 SQL 计划基线。这将指示 Oracle 优化程序在将来优先于任何其他选择来选取它。
execute dbms_sqltune.create_sql_plan_baseline(task_name => '任务_309',
owner_name => 'EASY', plan_hash_value => xxxxxxxx);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 370 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 36 | 1476 | 370 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 36 | 1080 | 69 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 75308 | 808K| 300 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 141 (1)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 36 | 1476 | 141 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 69 (2)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 1
------
Plan Origin :Cursor Cache
Plan Hash Value :1022743391
Executions :12
Elapsed Time :0.022 sec
CPU Time :0.019 sec
Buffer Gets :318
Disk Reads :0
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 5071 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2500 | 100K| 5071 (1)| 00:01:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2500 | 75000 | 69 (2)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
从报告文件可以看出,自动调优优化器为我们生成了更优的执行计划,下面我们只需要接受它即可。
SQL> exec dbms_sqltune.accept_sql_profile(task_name=>'任务_309',task_owner=>user,replace=>true,force_match=>true);
PL/SQL 过程已成功完成。
现在我们来看一下sql profile是否会其作用
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' AND T1.object_id=t2.object_id;
执行计划
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 141 (1)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 36 | 1476 | 141 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 69 (2)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_01436cac2c3c0000" used for this statement
看来sql profile发挥作用了,从执行计划和note部分都可以看出。在来看一下相似的语句
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' AND T1.object_id=t2.object_id;
执行计划
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 141 (1)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 36 | 1476 | 141 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 69 (2)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_01436cac2c3c0000" used for this statement
sql profile同样发挥了作用,这是为什么那?
大家可能注意到accept_sql_profile中的一个参数force_match,对! 正是这个参数导致sql profile可以影响相似的sql语句。在继续下面的讨论之前,先来看一下v$sql中的两个字段
SQL> select sql_text,to_char(exact_matching_signature),to_char(force_matching_signature) from v$sql where sql_text like ' select t1.*,t2.owner from t1,t2 %';
SQL_TEXT TO_CHAR(EXACT_MATCHING_SIGNATURE) TO_CHAR(FORCE_MATCHING_SIGNATURE)
---------------------------------------- ---------------------------------------- ----------------------------------------
select t1.*,t2.owner from t1,t2 where t 16954193673788994662 3960696072677096522
1.object_name like '%T2%' AND T1.object_
id=t2.object_id
select t1.*,t2.owner from t1,t2 where t 8975541025552400288 3960696072677096522
1.object_name like '%T1%' AND T1.object_
id=t2.object_id
然后我们看一下sqlprofile到底存储了什么内容?
SQL> l
1 SELECT so.signature,extractValue(value(h),'.') AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
4 WHERE so.name = 'SYS_SQLPROF_01436cac2c3c0000'
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8* AND so.plan_id = od.plan_id
SQL> /
SIGNATURE HINT
-------------------------- ------------------------------------------------------------------------------------------
3960696072677096522 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)
3960696072677096522 OPTIMIZER_FEATURES_ENABLE(default)
让我们删除sql profile,重新创建并设置force_match为false,
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_01436cac2c3c0000');
PL/SQL 过程已成功完成。
SQL> exec dbms_sqltune.accept_sql_profile(task_name=>'任务_309',task_owner=>user,replace=>true,force_match=>false);
PL/SQL 过程已成功完成。
SQL> set autotrace traceonly explain
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' AND T1.object_id=t2.object_id;
执行计划
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 1476 | 141 (1)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 36 | 1476 | 141 (1)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 36 | 1080 | 69 (2)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_01436d001cfe0001" used for this statement
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T2%' AND T1.object_id=t2.object_id;
执行计划
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 100K| 370 (1)| 00:00:05 |
|* 1 | HASH JOIN | | 2500 | 100K| 370 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 2500 | 75000 | 69 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 75308 | 808K| 300 (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T2%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
我们看到对于相似的sql语句,sql profile不会发生作用
在来看看关于signatrue的内容
SQL> select sql_text,to_char(exact_matching_signature),to_char(force_matching_signature) from v$sql where sql_text like 'select t1.*,t2.owner from t1,t2 %';
SQL_TEXT TO_CHAR(EXACT_MATCHING_SIGNATURE) TO_CHAR(FORCE_MATCHING_SIGNATURE)
---------------------------------------- ---------------------------------------- ----------------------------------------
select t1.*,t2.owner from t1,t2 where t1 8975541025552400288 3960696072677096522
.object_name like '%T1%' AND T1.object_i
d=t2.object_id
select t1.*,t2.owner from t1,t2 where t1 16954193673788994662 3960696072677096522
.object_name like '%T2%' AND T1.object_i
d=t2.object_id
SQL> l
1 SELECT so.signature,extractValue(value(h),'.') AS hint
2 FROM sys.sqlobj$data od, sys.sqlobj$ so,
3 table(xmlsequence(extract(xmltype(od.comp_data),'/outline_data/hint'))) h
4 WHERE so.name = 'SYS_SQLPROF_01436d001cfe0001'
5 AND so.signature = od.signature
6 AND so.category = od.category
7 AND so.obj_type = od.obj_type
8* AND so.plan_id = od.plan_id
SQL> /
SIGNATURE HINT
---------------------------------- ------------------------------------------------------------------------------------------
8975541025552400288 OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0144)
8975541025552400288 OPTIMIZER_FEATURES_ENABLE(default)
我想到这里大家应该都明白了,当force_match为true时,sql profile适合sql语句的force_match_signature项匹配的,而当force_match取值false时,sql profile是和sql语句的extract_match_signature相匹配的。
另为有一点,当我们创建sqlprofile后,可以放心删除tuning_task,已创建的sql profile不会受影响。