通過(guò)案例學(xué)調(diào)優(yōu)之--SQL Profile

通過(guò)案例學(xué)調(diào)優(yōu)之--SQL Profile

一、什么是SQL Profile（概要）

        SQL Profile在性能優(yōu)化中占有一個(gè)重要的位置。

       MOS里這么描述SQL Profile：

       SQL Profile是10g中的新特性，作為自動(dòng)SQL調(diào)整過(guò)程的一部分，由Oracle企業(yè)管理器來(lái)管理。除了OEM，SQL Profile可以通過(guò)DBMS_SQLTUNE包來(lái)進(jìn)行管理。

       查詢(xún)優(yōu)化器有時(shí)候會(huì)因?yàn)槿狈ψ銐虻男畔?，而?duì)一條SQL語(yǔ)句做出錯(cuò)誤的估計(jì)，生成糟糕的執(zhí)行計(jì)劃。而自動(dòng)SQL調(diào)整通過(guò)SQL概要分析來(lái)解決這個(gè)問(wèn)題，自動(dòng)調(diào)整優(yōu)化器會(huì)生成這條SQL語(yǔ)句的一個(gè)概要，稱(chēng)作SQL Profile。它由針對(duì)這條語(yǔ)句的一些輔助統(tǒng)計(jì)信息組成，通過(guò)采樣和局部執(zhí)行技術(shù)來(lái)確認(rèn)，必要的話(huà)，會(huì)調(diào)整執(zhí)行計(jì)劃中的估計(jì)值。在SQL概要分析中，自動(dòng)調(diào)整優(yōu)化器還可以通過(guò)一條SQL語(yǔ)句的執(zhí)行歷史信息來(lái)設(shè)置合適的優(yōu)化器參數(shù)，比如將OPTIMIZER_MODE參數(shù)由ALL_ROWS改為FIRST_ROWS。

       換句話(huà)說(shuō)，SQL概要是一個(gè)對(duì)象，它包含了可以幫助查詢(xún)優(yōu)化器為一個(gè)特定的SQL語(yǔ)句找到高效執(zhí)行計(jì)劃的信息。這些信息包括執(zhí)行環(huán)境、對(duì)象統(tǒng)計(jì)和對(duì)查詢(xún)優(yōu)化器所做評(píng)估的修正信息。它的最大優(yōu)點(diǎn)之一就是在不修改SQL語(yǔ)句和會(huì)話(huà)執(zhí)行環(huán)境的情況下影響查詢(xún)優(yōu)化器的決定。(《Oracle性能診斷藝術(shù)》)

       SQL Profile中包含的并非單個(gè)執(zhí)行計(jì)劃的信息，必須注意的是，SQL Profile不會(huì)固定一個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃。當(dāng)表的數(shù)據(jù)增長(zhǎng)或者索引創(chuàng)建、刪除，使用同一個(gè)SQL Profile的執(zhí)行計(jì)劃可能會(huì)改變，而儲(chǔ)存在SQL Profile中的信息會(huì)繼續(xù)起作用。然而，經(jīng)過(guò)一段很長(zhǎng)的時(shí)間之后，它的信息有可能會(huì)過(guò)時(shí)，需要重新生成。

       SQL Profile的作用范圍由CATEGORY屬性來(lái)控制，這個(gè)屬性決定了哪些用戶(hù)會(huì)話(huà)可以應(yīng)用這個(gè)概要。你可以從DBA_SQL_PROFILES中的CATEGORY字段來(lái)查看這個(gè)屬性。默認(rèn)情況下，所有概要文件都創(chuàng)建為DEFAULT范疇，這意味著所有SQLTUNE_CATEGORY初始化參數(shù)為DEFAULT的用戶(hù)會(huì)話(huà)都可以使用這個(gè)概要。你可以修改這個(gè)屬性，比如將其改為SCO，則SQLTUNE_GATEGORY參數(shù)為SCO的用戶(hù)會(huì)話(huà)才能使用它，利用這個(gè)功能，你可以在一個(gè)受限制的環(huán)境中來(lái)測(cè)試一個(gè)SQL Profile。

16:42:03 SYS@ prod >desc dba_sql_profiles
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 NAME                                                              NOT NULL VARCHAR2(30)
 CATEGORY                                                          NOT NULL VARCHAR2(30)
 SIGNATURE                                                         NOT NULL NUMBER
 SQL_TEXT                                                          NOT NULL CLOB
 CREATED                                                           NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                                                              TIMESTAMP(6)
 DESCRIPTION                                                                VARCHAR2(500)
 TYPE                                                                       VARCHAR2(7)
 STATUS                                                                     VARCHAR2(8)
 FORCE_MATCHING                                                             VARCHAR2(3)
 TASK_ID                                                                    NUMBER
 TASK_EXEC_NAME                                                             VARCHAR2(30)
 TASK_OBJ_ID                                                                NUMBER
 TASK_FND_ID                                                                NUMBER
 TASK_REC_ID                                                                NUMBER
 
16:50:43 SYS@ prod >select name, CATEGORY,sql_text,status from dba_sql_profiles;
no rows selected

       SQL Profile可以作用在如下表達(dá)式中：SELECT; UPDATE; INSERT(在包含SELECT子句的情況下); DELETE; CREATE TABLE(包含SELECT子句的情況下); MERGE(UPDATE或INSERT操作)。

二、SQL Profile的管理

Oracle執(zhí)行SQL語(yǔ)句的步驟如下：

1. 用戶(hù)傳送要執(zhí)行的SQL語(yǔ)句給SQL引擎

2. SQL引擎要求查詢(xún)優(yōu)化器提供執(zhí)行計(jì)劃

3. 查詢(xún)優(yōu)化取得系統(tǒng)統(tǒng)計(jì)信息、SQL語(yǔ)句引用對(duì)象的對(duì)象統(tǒng)計(jì)信息、SQL概要和構(gòu)成執(zhí)行環(huán)境的初始化參數(shù)

4. 查詢(xún)優(yōu)化器分析SQL語(yǔ)句并產(chǎn)生執(zhí)行計(jì)劃

5. 將執(zhí)行計(jì)劃傳遞給SQL引擎

6. SQL引擎執(zhí)行SQL語(yǔ)句

 SQL Profile可以由OEM來(lái)管理，也可以通過(guò)DBMS_SQLTUNE包來(lái)手動(dòng)使用。

（1）、使用OEM時(shí)步驟如下：

1. 在Performance頁(yè)面，點(diǎn)擊Top Activity。出現(xiàn)了Top Activity頁(yè)面

2. 在Top SQL下面，點(diǎn)擊正在使用SQL Profile的SQL表達(dá)式的SQL ID鏈接，會(huì)出現(xiàn)一個(gè)SQL Details頁(yè)面

3. 點(diǎn)擊Plan Control選項(xiàng)卡，在SQL Profiles and Outlines下面會(huì)顯示一個(gè)SQL profile的列表

4. 選擇你想要管理的SQL Profile，可以做如下操作：?jiǎn)⒂没蚪?、移?/span>

5. 會(huì)出現(xiàn)一個(gè)確認(rèn)的頁(yè)面，點(diǎn)擊Yes繼續(xù)，No取消

（2）、使用DBMS_SQLTUNE包

如果使用DBMS_SQLTUNE包，你需要CREATE ANY SQL_PROFILE、DROP ANY SQL_PROFILE還有ALTER ANY SQL_PROFILE的系統(tǒng)權(quán)限。

1）、創(chuàng)建sql profile

使用DBMS_SQLTUNE.ACCEPT_SQL_PROFILE過(guò)程來(lái)接受并創(chuàng)建SQL Tuning Advisor建議的SQL Profile

DECLARE 
my_sqlprofile_name VARCHAR2(30); 
BEGIN 
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (  
task_name => 'my_sql_tuning_task', 
name => 'my_sql_profile'); 
END;

這個(gè)過(guò)程的傳入?yún)?shù)中有一個(gè)可選參數(shù)force_match，默認(rèn)為FALSE。當(dāng)設(shè)置為FALSE時(shí)，不區(qū)分空白和大小寫(xiě)，為T(mén)RUE時(shí)，空白、大小寫(xiě)和字面量都不區(qū)分。通過(guò)企業(yè)管理器來(lái)接受SQL概要時(shí)，這個(gè)參數(shù)在ORACLE11g中才可以設(shè)置。

2）、修改SQL Profile

可以修改STATUS、NAME、DESCRIPTION和CATEGORY屬性

BEGIN 
DBMS_SQLTUNE.ALTER_SQL_PROFILE( 
name => 'my_sql_profile',  
attribute_name => 'STATUS',  
value => 'DISABLED'); 
END; 
/

3）、刪除SQL Profile

begin 
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile'); 
end; 
/

對(duì)我們來(lái)說(shuō)，重點(diǎn)在于創(chuàng)建SQL Profile時(shí)的my_sql_tuning_task上，它通過(guò)函數(shù)create_tuning_task來(lái)創(chuàng)建，執(zhí)行這個(gè)函數(shù)需要傳遞下面的參數(shù)之一：SQL語(yǔ)句文本、存儲(chǔ)在共享池中的SQL語(yǔ)句引用(sql_id)、存儲(chǔ)在自動(dòng)工作量資料庫(kù)中的SQL語(yǔ)句引用(sql_id)、SQL調(diào)優(yōu)集名稱(chēng)。

比如利用sql_id來(lái)創(chuàng)建tuning_task，我們可以這么運(yùn)行
 declare 
tuning_task varchar2(30); 
begin 
  tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'bfb9vn0gh4z0t'); 
  dbms_output.put_line(tuning_task); 
end;

 什么是SQL調(diào)優(yōu)集(tuning set)？簡(jiǎn)單來(lái)講，SQL調(diào)優(yōu)集是存儲(chǔ)一系列SQL語(yǔ)句及其相關(guān)信息的對(duì)象集合，這些信息包括執(zhí)行環(huán)境、運(yùn)行統(tǒng)計(jì)和可選的執(zhí)行計(jì)劃。

下面引用MOS提供的一個(gè)示例來(lái)演示一下這個(gè)過(guò)程

案例分析：

1、scott用戶(hù)執(zhí)行sql
17:19:56 SCOTT@ prod >create table test (n number);
Table created.

17:20:16 SCOTT@ prod >begin
17:20:24   2  for i in 1..100000 loop
17:20:36   3  insert into test values (i);
17:20:47   4  commit;
17:20:49   5  end loop;
17:20:52   6  end;
17:20:53   7  /
PL/SQL procedure successfully completed.

17:22:02 SCOTT@ prod >create index test_ind on test(n);
Index created.

17:22:55 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'TEST' ,cascade=>true);
PL/SQL procedure successfully completed.

17:23:15 SCOTT@ prod >set autotrace on
17:23:43 SCOTT@ prod >select * from test where n=100;
         N
----------
       100
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3357096749
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     5 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N"=100)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
對(duì)此sql建立sql profile：
17:24:02 SCOTT@ prod >select /*+ no_index(test,test_ind) */ * from test where n=100;
         N
----------
       100
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |    69   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     5 |    69   (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N"=100)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        191  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
2、通過(guò)sys用戶(hù)建立sql profile（使用dbms_sqltune包）
17:28:26 SYS@ prod >declare
17:29:34   2
17:29:34   3    my_task_name VARCHAR2(30);
17:29:34   4
17:29:34   5    my_sqltext CLOB;
17:29:34   6
17:29:34   7    begin
17:29:34   8
17:29:34   9       my_sqltext := 'select /*+ no_index(test test_ind) */ * from test where n=100';
17:29:34  10
17:29:34  11       my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
17:29:34  12
17:29:34  13       sql_text => my_sqltext,
17:29:34  14
17:29:34  15       user_name => 'SCOTT',
17:29:34  16
17:29:34  17       scope => 'COMPREHENSIVE',
17:29:34  18
17:29:34  19       time_limit => 60,
17:29:34  20
17:29:34  21       task_name => 'my_tun1',
17:29:34  22
17:29:34  23       description => 'Task to tune a query on a specified table');
17:29:34  24
17:29:34  25  end;
17:29:35  26  /
PL/SQL procedure successfully completed.

建立調(diào)優(yōu)任務(wù)：
17:29:37 SYS@ prod >begin
17:30:39   2
17:30:39   3  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_tun1');
17:30:39   4
17:30:39   5  end;
17:30:39   6
17:30:39   7  /
PL/SQL procedure successfully completed.

查看調(diào)優(yōu)task：
17:32:47 SYS@ prod >set long 1000
17:33:17 SYS@ prod >set longchunksize 1000
17:33:24 SYS@ prod >set linesize 100
17:33:32 SYS@ prod >SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_tun1') from dual
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUN1')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_tun1
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 11/07/2014 17:30:41
Completed at       : 11/07/2014 17:30:49
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : b1wdr0b0qzsbg
SQL Text   : select /*+ no_index(test test_ind) */ * from test where n=100
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUN1')
----------------------------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
  Recommendation (estimated benefit: 99.41%)
  
17:34:58 SYS@ prod >execute dbms_sqltune.accept_sql_profile(task_name =>'my_tun1',task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.

建立sql profile：
17:39:22 SYS@ prod >DECLARE
17:41:13   2
17:41:13   3  my_sqlprofile_name VARCHAR2(30);
17:41:13   4
17:41:13   5  begin
17:41:13   6
17:41:13   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
17:41:13   8
17:41:13   9  task_name => 'my_tun1',
17:41:13  10
17:41:13  11  name => 'my_sqlprofile',force_match=>false);
17:41:13  12
17:41:13  13  end;
17:41:15  14  /
DECLARE
*
ERROR at line 1:
ORA-13830: SQL profile with category DEFAULT already exists for this SQL statement
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16259
ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31
ORA-06512: at "SYS.DBMS_SQLTUNE", line 7133
ORA-06512: at line 7

17:44:28 SYS@ prod >DECLARE
17:46:00   2
17:46:00   3  my_sqlprofile_name VARCHAR2(30);
17:46:00   4
17:46:00   5  begin
17:46:00   6
17:46:00   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
17:46:00   8
17:46:00   9  task_name => 'my_tun1',
17:46:00  10
17:46:00  11  name => 'my_sqlprofile',force_match=>false,CATEGORY=>'scott');
17:46:00  12
17:46:00  13  end;
17:46:01  14  /
PL/SQL procedure successfully completed.

17:53:49 SYS@ prod >select name, CATEGORY,sql_text,status from dba_sql_profiles;
NAME                           CATEGORY
------------------------------ ------------------------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
STATUS
--------
SYS_SQLPROF_0149899c759a0000   DEFAULT
select /*+ no_index(test test_ind) */ * from test where n=100
ENABLED
my_sqlprofile                  SCOTT
select /*+ no_index(test test_ind) */ * from test where n=100
ENABLED

刪除存在的sql profile：
17:53:51 SYS@ prod >exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'SYS_SQLPROF_0149899c759a0000');
PL/SQL procedure successfully completed.

17:55:20 SYS@ prod >exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'my_sqlprofile');
PL/SQL procedure successfully completed.

重新建立sql profile：
17:55:35 SYS@ prod >DECLARE
17:56:13   2
17:56:13   3  my_sqlprofile_name VARCHAR2(30);
17:56:13   4
17:56:13   5  begin
17:56:13   6
17:56:13   7  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
17:56:13   8
17:56:13   9  task_name => 'my_tun1',
17:56:13  10
17:56:13  11  name => 'my_sqlprofile');
17:56:13  12
17:56:13  13  end;
17:56:16  14  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04

查看sql profile：
18:01:48 SYS@ prod >col name for a20
18:01:55 SYS@ prod >r
  1* select name,CATEGORY,SQL_TEXT,TASK_EXEC_NAME ,STATUS from dba_sql_profiles
NAME                 CATEGORY   SQL_TEXT
-------------------- ---------- --------------------------------------------------
TASK_EXEC_NAME                 STATUS
------------------------------ --------
my_sqlprofile        DEFAULT    select /*+ no_index(test test_ind) */ * from test
                                where n=100
EXEC_427                       ENABLED

3、以scott用戶(hù)的身份進(jìn)行驗(yàn)證
18:01:55 SYS@ prod >conn scott/tiger
Connected.
18:02:43 SCOTT@ prod >set autotrace on
18:02:46 SCOTT@ prod > select /*+ no_index(test test_ind) */ * from test where n=100;
         N
----------
       100
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 3357096749
-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     5 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IND |     1 |     5 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("N"=100)
Note
-----
   - SQL profile "my_sqlprofile" used for this statement
Statistics
----------------------------------------------------------
        790  recursive calls
          0  db block gets
        168  consistent gets
          6  physical reads
        116  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看出即使使用了‘no_index'的hint，sql執(zhí)行計(jì)劃仍使用index 訪(fǎng)問(wèn)。  使用了  SQL profile "my_sql_profile" used for this statement  

由這個(gè)例子我們可以發(fā)現(xiàn)，在必要情況下，SQL Profile可以讓hint失效！