[20130123]spm与sql profile的主要区别在那里.txt
SPM是11G的新特性，而10g下sql profile一定程度代替store ountlined来稳定执行计划，oracle为什么11G下还要推出SPM？
两者区别主要在那里呢？
我举一个例子来说明:
1.建立测试环境:
select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
create table t1 as select mod(rownum,100) id ,lpad('x',80,'x') name from dual connect by level
insert into t1 values (100,lpad('y',80,'y')) ;
commit ;
create index i_t1_id on t1(id);
exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');
create table t2 as select mod(rownum,100) id ,lpad('x',80,'x') name from dual connect by level
insert into t2 values (100,lpad('y',80,'y')) ;
commit ;
create index i_t2_id on t2(id);
exec dbms_stats.gather_table_stats(user,'T2',method_opt=>'for all columns size 1');
--我没有建立直方图！
--t1,t2信息一样，id数值是从0-99均匀分布，而id=100仅仅1个,理论讲上面使用索引效果更加。但是由于索引的聚集因子很大，几乎接
--近记录大小。这样oracle认为数据很离散，执行计划并不会使用索引 [注意：我并没有建立直方图。]
SQL> select clustering_factor from dba_indexes where wner=user and index_name in ('I_T1_ID','I_T2_ID');
CLUSTERING_FACTOR
-----------------
10001
10001
--看看一下语句的sql语句:
select * from t1 where id=100;
SQL> select * from t1 where id=100;
ID NAME
---------- --------------------------------------------------
100 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
SQL> @dpc ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c9bya8ajdfsg3, child number 0
-------------------------------------
select * from t1 where id=100
Plan hash value: 3617692013
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
|* 1 | TABLE ACCESS FULL| T1 | 99 | 53 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
--可以发现oracle认为数据很离散，但是系统并不知道id=100很少[并没有建立直方图]，仅仅1条，按照道理使用索引效率很高，而实际
--的执行计划是全表扫描.
2.测试使用sql profile来优化看看:
DECLARE
ret_val VARCHAR2(4000);
BEGIN
ret_val := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'c9bya8ajdfsg3',
plan_hash_value => NULL,
scope => 'COMPREHENSIVE',
time_limit => 1800,
task_name => 'test',
description => 'study');
Dbms_Sqltune.EXECUTE_TUNING_TASK('test');
END;
select Dbms_Sqltune.REPORT_TUNING_TASK('test', 'TEXT', 'all') report from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : test
Tuning Task Owner : SCOTT
Tuning Task ID : 8871
Workload Type : Single SQL Statement
Execution Count : 1
Current Execution : EXEC_9150
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 01/23/2013 09:45:04
Completed at : 01/23/2013 09:45:04
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : c9bya8ajdfsg3
SQL Text : select * from t1 where id=100
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划。
Recommendation (estimated benefit: 98.46%)
------------------------------------------
- 考虑接受推荐的 SQL 概要文件。
execute dbms_sqltune.accept_sql_profile(task_name => 'test', task_owner
=> 'SCOTT', replace => TRUE);
Validation results
------------------
已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,
则另一计划可能只执行了一部分。
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time(us): 832 72 91.34 %
CPU Time(us): 899 100 88.87 %
User I/O Time(us): 0 0
Buffer Gets: 131 2 98.47 %
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. original plan 已首先执行以预热缓冲区高速缓存。
2. original plan 的统计信息是后面的 9 执行的平均值。
3. SQL profile plan 已首先执行以预热缓冲区高速缓存。
4. the SQL profile plan 的统计信息是后面的 9 执行的平均值。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99 | 8316 | 53 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 99 | 8316 | 53 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,80]
2- Original With Adjusted Cost
------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 53 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 84 | 53 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,80]
3- Using SQL Profile
--------------------
Plan hash value: 1111474805
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 84 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=100)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "T1"."NAME"[VARCHAR2,80]
2 - "T1".ROWID[ROWID,10], "ID"[NUMBER,22]
-------------------------------------------------------------------------------
--可以发现提示使用索引效果更加,执行如下来稳定计划.
execute dbms_sqltune.accept_sql_profile(task_name => 'test', task_owner=> 'SCOTT', replace => TRUE);
SQL> column signature format 999999999999999999999
SQL> set serveroutput on
SQL> exec print_table('select * from dba_sql_profiles');
NAME : SYS_SQLPROF_013c6516f3f90000
CATEGORY : DEFAULT
SIGNATURE : 10770288213799319469
SQL_TEXT : select * from t1 where id=100
CREATED : 2013-01-23 09:47:48.000000
LAST_MODIFIED : 2013-01-23 09:47:48.000000
DESCRIPTION :
TYPE : MANUAL
STATUS : ENABLED
FORCE_MATCHING : NO
TASK_ID : 8871
TASK_EXEC_NAME : EXEC_9150
TASK_OBJ_ID : 1
TASK_FND_ID : 1
TASK_REC_ID : 1
-----------------
--查询获得sql profile 的hint。10G使用如下命令。
SELECT attr_val
FROM sys.sqlprof$ p, sys.sqlprof$attr a
WHERE p.sp_name = 'SYS_SQLPROF_013c6516f3f90000'
AND p.signature = a.signature
AND p.category = a.category;
--注意11GR2查询如下:
SELECT EXTRACTVALUE (VALUE (h), '.') AS hint
FROM SYS.sqlobj$data od, SYS.sqlobj$ so, TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint'))) h
WHERE so.NAME = 'SYS_SQLPROF_013c6516f3f90000'
AND so.signature = od.signature
AND so.CATEGORY = od.CATEGORY
AND so.obj_type = od.obj_type
AND so.plan_id = od.plan_id;
HINT
-------------------------------------------------------------------------------------
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0100989901)
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "T1"@"SEL$1", "I_T1_ID", SCALE_ROWS=0.0100989901)
OPTIMIZER_FEATURES_ENABLE(default)
--可以发现sql profile实际上使用的提示如上。SCALE_ROWS=0.0100989901,表T1的总记录10001条，id不同的键值是0-100,有101个。
--如果执行如下：
select /*+ gather_plan_statistics
OPT_ESTIMATE(@"SEL$1", TABLE, "T1"@"SEL$1", SCALE_ROWS=0.0100989901)
OPT_ESTIMATE(@"SEL$1", INDEX_SCAN, "T1"@"SEL$1", "I_T1_ID", SCALE_ROWS=0.0100989901) */
* from t1 where id=42;
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9aytbsm99q6d9, child number 0
-------------------------------------
select /*+ gather_plan_statistics OPT_ESTIMATE(@"SEL$1", TABLE,
"T1"@"SEL$1", SCALE_ROWS=0.0100989901) OPT_ESTIMATE(@"SEL$1",
INDEX_SCAN, "T1"@"SEL$1", "I_T1_ID", SCALE_ROWS=0.0100989901) */ * from
t1 where id=42
Plan hash value: 1111474805
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 100 |00:00:00.01 | 103 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 2 (0)| 100 |00:00:00.01 | 103 |
|* 2 | INDEX RANGE SCAN | I_T1_ID | 1 | 1 | 1 (0)| 100 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=42)
--不加提示看看：
select /*+ gather_plan_statistics */ * from t1 where id=42;
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7utu76jbuat60, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t1 where id=42
Plan hash value: 3617692013
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 53 (100)| 100 |00:00:00.01 | 147 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 99 | 53 (0)| 100 |00:00:00.01 | 147 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=42)
--可以明显看出提示控制了记录返回的行数。
3.再使用SPM看看：
SQL> alter session set optimizer_capture_sql_plan_baselines=true ;
SQL> select * from t2 where id = 100 ;
SQL> select * from t2 where id = 100 ;
SQL> alter session set optimizer_capture_sql_plan_baselines=false ;
SQL> select sql_handle, plan_name, sql_text,enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME SQL_TEXT ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ -------------------------------------------------------- --- --- --- -------------- -----------------------
SYS_SQL_a69226d598cc47a0 SQL_PLAN_ad4j6uqccsjx0b860bcf2 select * from t2 where id = 100 YES YES NO AUTO-CAPTURE 12002698655729207200
SQL> select * from t2 where id = 100 ;
ID NAME
---------- --------------------------------------------------
100 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
SQL> @dpc
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 0n657fnzb9fm0, child number 1
-------------------------------------
select * from t2 where id = 100
Plan hash value: 1513984157
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 53 (100)|
|* 1 | TABLE ACCESS FULL| T2 | 99 | 53 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
--执行计划是全表扫描，并没有选择索引。SPM更多的是稳定计划。它并不知道有很好的执行计划。
--抽取提示看看:
SQL> EXEC dbms_spm.create_stgtab_baseline('stage1');
SQL> variable v_basenum number ;
SQL> EXEC :v_basenum := dbms_spm.pack_stgtab_baseline('stage1', sql_handle => 'SYS_SQL_a69226d598cc47a0');
SQL> column comp_data format a100
SQL> select comp_data from stage1 where sql_handle='SYS_SQL_a69226d598cc47a0';
COMP_DATA
----------------------------------------------------------------------------------------------------
1")]]>
![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.1')]]>
SQL> SELECT EXTRACTVALUE (VALUE (h), '.') AS hint
FROM stage1, TABLE (XMLSEQUENCE (EXTRACT (XMLTYPE (stage1.comp_data), '/outline_data/hint'))) h
WHERE sql_handle = 'SYS_SQL_a69226d598cc47a0';
HINT
-------------------------------------
FULL(@"SEL$1" "T2"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
6 rows selected.
--可以发现SPM记录的提示是执行计划，而sql profile记录的提示记录的返回信息。并没有记录执行计划。
4.这样按照上面的提示,如果改变T1表的相关统计信息，执行计划一样会变化：
SQL> select num_rows from dba_tables where table_name='T1';
NUM_ROWS
----------
10001
SQL> exec dbms_stats.set_table_stats(ownname=>user,tabname=>'T1',numrows=>1000001);
PL/SQL procedure successfully completed.
--修改统计信息，行记录增加100倍。
SQL> alter system flush shared_pool;
System altered.
SQL> select * from t1 where id=100;
ID NAME
---------- --------------------------------------------------
100 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
yyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c9bya8ajdfsg3, child number 0
-------------------------------------
select * from t1 where id=100
Plan hash value: 3617692013
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 74 (100)|
|* 1 | TABLE ACCESS FULL| T1 | 100 | 74 (29)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- SQL profile SYS_SQLPROF_013c6516f3f90000 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
25 rows selected.
--可以发现执行计划变成了全表扫描。
5.总结：
通过对比，可以发现SPM以及sql profile都是为了更好的维护执行计划，但是两者控制的方式，设计理念完全不同。其中的细节留给
大家体会。
时间： 2024-07-30 10:47:35