ORACLE概要檔案-- sql profile(一）

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不會受影響。

ORACLE概要檔案-- sql profile(一）

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List