[20130123]spm與sql profile的主要區別在那裡.txt
[20130123]spm與sql profile的主要區別在那裡.txt
SPM是11G的新特性，而10g下sql profile一定程度代替store ountlined來穩定執行計劃，oracle為什麼11G下還要推出SPM？
兩者區別主要在那裡呢？
我舉一個例子來說明:
1.建立測試環境:
--我沒有建立直方圖！
--t1,t2資訊一樣，id數值是從0-99均勻分佈，而id=100僅僅1個,理論講上面使用索引效果更加。但是由於索引的聚集因子很大，幾乎接
--近記錄大小。這樣oracle認為資料很離散，執行計劃並不會使用索引 [注意：我並沒有建立直方圖。]
--可以發現sql profile實際上使用的提示如上。SCALE_ROWS=0.0100989901,表T1的總記錄10001條，id不同的鍵值是0-100,有101個。
--如果執行如下：
--不加提示看看：
--可以明顯看出提示控制了記錄返回的行數。
3.再使用SPM看看：
--執行計劃是全表掃描，並沒有選擇索引。SPM更多的是穩定計劃。它並不知道有很好的執行計劃。
--抽取提示看看:
--可以發現SPM記錄的提示是執行計劃，而sql profile記錄的提示記錄的返回資訊。並沒有記錄執行計劃。
4.這樣按照上面的提示,如果改變T1表的相關統計資訊，執行計劃一樣會變化：
5.總結：
透過對比，可以發現SPM以及sql profile都是為了更好的維護執行計劃，但是兩者控制的方式，設計理念完全不同。其中的細節留給
大家體會。
SPM是11G的新特性，而10g下sql profile一定程度代替store ountlined來穩定執行計劃，oracle為什麼11G下還要推出SPM？
兩者區別主要在那裡呢？
我舉一個例子來說明:
1.建立測試環境:
select * from v$version where rownum<=1;
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<=10000;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<=10000;
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
----------------------------------------------------------------------------------------------------
<
![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都是為了更好的維護執行計劃，但是兩者控制的方式，設計理念完全不同。其中的細節留給
大家體會。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-753102/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20130109]SPM與sql profile.txtSQL
- [20170302]fuzzy儲存在那裡.txt
- 公有云和私有云的主要區別在哪裡
- HTTP代理與SOCKS代理的主要區別HTTP
- Java中Array與ArrayList的主要區別Java
- MPS與MRP的主要區別
- [20220324]toad與sql profile使用問題.txtSQL
- [20151218]未提交事務儲存在那裡.txt
- SQL Plan Management(SPM)SQL
- 【SQL】UNION ALL 與UNION 的區別SQL
- .bash_profile和.bashrc等等的區別
- 自動/手動型別sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)型別SQL
- Docsify 的 GitHub 連結在那裡設定的Github
- 動態IP與靜態IP的主要區別
- SOCKS代理與HTTP代理主要區別分析HTTP
- MPLS與專線的區別在哪裡？——VecloudCloud
- SQL中Having與Where的區別SQL
- SQL join中on與where區別SQL
- SQL truncate 、delete與drop區別SQLdelete
- Audit裡審計SQL語句與審計系統許可權的區別SQL
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- sql profile的使用SQL
- sql profileSQL
- PL/SQL中command window與SQL window的區別SQL
- 單件流與批次流的區別在哪裡？
- 關於rman裡面的from 與until的區別
- [20131122]跟蹤sql profile的操作.txtSQL
- Sql server char,nchar,varchar與Nvarchar的區別SQLServer
- .bash_profile和.bashrc的什麼區別(轉)
- 新媒體運營與文案的區別在哪裡?
- MySQL中普通sql與預編譯sql 區別MySql編譯
- sql server agent與sql server有什麼區別 ?SQLServer
- sql profile使用SQL
- HTTPS 和 HTTP 的主要區別HTTP
- PL/SQL DEVELOPER工具與toad工具的區別SQLDeveloper
- .NET Framework 4和.NET Framework 4 Client Profile的區別Frameworkclient
- 資料探勘與資料分析的主要區別是什麼
- EJB1.1與EJB2.0的主要區別都那些阿？？？