SQL Profile介绍

转自：

http://blog.csdn.net/wildwave/article/details/6964053

什么是SQL Profile

 

       SQL Profile在性能优化中占有一个重要的位置。

       MOS里这么描述SQL Profile：

       SQL Profile是10g中的新特性，作为自动SQL调整过程的一部分，由Oracle企业管理器来管理。除了OEM，SQL Profile可以通过DBMS_SQLTUNE包来进行管理。

       查询优化器有时候会因为缺乏足够的信息，而对一条SQL语句做出错误的估计，生成糟糕的执行计划。而自动SQL调整通过SQL概要分析来解决这个问题，自动调整优化器会生成这条SQL语句的一个概要，称作SQL Profile。它由针对这条语句的一些辅助统计信息组成，通过采样和局部执行技术来确认，必要的话，会调整执行计划中的估计值。在SQL概要分析中，自动调整优化器还可以通过一条SQL语句的执行历史信息来设置合适的优化器参数，比如将OPTIMIZER_MODE参数由ALL_ROWS改为FIRST_ROWS。

       换句话说，SQL概要是一个对象，它包含了可以帮助查询优化器为一个特定的SQL语句找到高效执行计划的信息。这些信息包括执行环境、对象统计和对查询优化器所做评估的修正信息。它的最大优点之一就是在不修改SQL语句和会话执行环境的情况下影响查询优化器的决定。(《Oracle性能诊断艺术》)

       SQL Profile中包含的并非单个执行计划的信息，必须注意的是，SQL Profile不会固定一个SQL语句的执行计划。当表的数据增长或者索引创建、删除，使用同一个SQL Profile的执行计划可能会改变，而储存在SQL Profile中的信息会继续起作用。然而，经过一段很长的时间之后，它的信息有可能会过时，需要重新生成。

       SQL Profile的作用范围由CATEGORY属性来控制，这个属性决定了哪些用户会话可以应用这个概要。你可以从DBA_SQL_PROFILES中的CATEGORY字段来查看这个属性。默认情况下，所有概要文件都创建为DEFAULT范畴，这意味着所有SQLTUNE_CATEGORY初始化参数为DEFAULT的用户会话都可以使用这个概要。你可以修改这个属性，比如将其改为DEV，则SQLTUNE_GATEGORY参数为DEV的用户会话才能使用它，利用这个功能，你可以在一个受限制的环境中来测试一个SQL Profile。

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

 

Oracle执行SQL语句的步骤如下：

1. 用户传送要执行的SQL语句给SQL引擎

2. SQL引擎要求查询优化器提供执行计划

3. 查询优化取得系统统计信息、SQL语句引用对象的对象统计信息、SQL概要和构成执行环境的初始化参数

4. 查询优化器分析SQL语句并产生执行计划

5. 将执行计划传递给SQL引擎

6. SQL引擎执行SQL语句

SQL Profile的管理

       SQL Profile可以由OEM来管理，也可以通过DBMS_SQLTUNE包来手动使用。

使用OEM时步骤如下：

1. 在Performance页面，点击Top Activity。出现了Top Activity页面

2. 在Top SQL下面，点击正在使用SQL Profile的SQL表达式的SQL ID链接，会出现一个SQL Details页面

3. 点击Plan Control选项卡，在SQL Profiles and Outlines下面会显示一个SQL profile的列表

4. 选择你想要管理的SQL Profile，可以做如下操作：启用或禁用、移除

5. 会出现一个确认的页面，点击Yes继续，No取消

如果使用DBMS_SQLTUNE包，你需要CREATE ANY SQL_PROFILE、DROP ANY SQL_PROFILE还有ALTER ANY SQL_PROFILE的系统权限。

使用DBMS_SQLTUNE.ACCEPT_SQL_PROFILE过程来接受并创建SQL Tuning Advisor建议的SQL Profile

[sql]  view plain copy

1. DECLARE  
2. my_sqlprofile_name VARCHAR2(30);  
3. BEGIN  
4. my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (   
5. task_name => 'my_sql_tuning_task',  
6. name => 'my_sql_profile');  
7. END;  

my_sql_tuning_task是SQL调整目标的名称。

这个过程的传入参数中有一个可选参数force_match，默认为FALSE。当设置为FALSE时，不区分空白和大小写，为TRUE时，空白、大小写和字面量都不区分。通过企业管理器来接受SQL概要时，这个参数在ORACLE11g中才可以设置。

修改SQL Profile，可以修改STATUS、NAME、DESCRIPTION和CATEGORY属性

[sql]  view plain copy

1. BEGIN  
2. DBMS_SQLTUNE.ALTER_SQL_PROFILE(  
3. name => 'my_sql_profile',   
4. attribute_name => 'STATUS',   
5. value => 'DISABLED');  
6. END;  
7. /  

删除SQL Profile

[sql]  view plain copy

1. begin  
2. DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');  
3. end;  
4. /  

对我们来说，重点在于创建SQL Profile时的my_sql_tuning_task上，它通过函数create_tuning_task来创建，执行这个函数需要传递下面的参数之一：SQL语句文本、存储在共享池中的SQL语句引用(sql_id)、存储在自动工作量资料库中的SQL语句引用(sql_id)、SQL调优集名称。

比如利用sql_id来创建tuning_task，我们可以这么运行

[sql]  view plain copy

1. declare  
2. tuning_task varchar2(30);  
3. begin  
4.   tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'bfb9vn0gh3z0t');  
5.   dbms_output.put_line(tuning_task);  
6. end;  

记下这个tuning_task，用于后面的过程来使用

什么是SQL调优集(tuning set)？简单来讲，SQL调优集是存储一系列SQL语句及其相关信息的对象集合，这些信息包括执行环境、运行统计和可选的执行计划。

下面引用MOS提供的一个示例来演示一下这个过程

示例

SESSION1--SCOTT

创建表，填充数据，然后创建索引和采集统计信息。使用no_index提示来执行查询，使用全表扫描

[sql]  view plain copy

1. SQL> create table test (n number );  
2. Table created.  
3.   
4. SQL> declare  
5.           begin  
6.            for i in 1 .. 10000 loop  
7.                insert into test values(i);  
8.                commit;  
9.            end loop;  
10.           end;  
11. /  
12. PL/SQL procedure successfully completed.  
13.   
14. SQL> create index test_idx on test(n);  
15. Index created.  
16.   
17. SQL> exec dbms_stats.gather_table_stats('','TEST');  
18. PL/SQL procedure successfully completed.  
19.   
20. set autotrace on  
21. select /*+ no_index(test test_idx) */ * from test where n=1  
22.   
23.   
24. --------------------------------------------------------------------------  
25. | Id  | Operation         | Name | Rows   | Bytes | Cost (%CPU)| Time    |   
26. --------------------------------------------------------------------------  
27. |   0 | SELECT STATEMENT  |      |     1 |     4 |     5   (0)| 00:00:01 |  
28. |*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     5   (0)| 00:00:01 |   
29. --------------------------------------------------------------------------   

SESSION2--SYS

创建并执行tuning task，并运行report tuning task，采用建议的SQL Profile

[sql]  view plain copy

1. declare  
2.   my_task_name VARCHAR2(30);  
3.   my_sqltext CLOB;  
4.   begin  
5.      my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';  
6.      my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(  
7.      sql_text => my_sqltext,  
8.      user_name => 'SCOTT',  
9.      scope => 'COMPREHENSIVE',  
10.      time_limit => 60,  
11.      task_name => 'my_sql_tuning_task_2',  
12.      description => 'Task to tune a query on a specified table');  
13. end;  
14. /  
15. PL/SQL procedure successfully completed.  
16.   
17. begin  
18. DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');  
19. end;  
20. /  
21. PL/SQL procedure successfully completed.  
22.   
23. set long 1000  
24. set longchunksize 1000  
25. set linesize 100  
26. SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;  

[sql]  view plain copy

1. DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')   
2. -------------------------------------------------------------------------------------  
3. GENERAL INFORMATION SECTION  
4. -------------------------------------------------------------------------------  
5. Tuning Task Name   : my_sql_tuning_task_2  
6. Tuning Task Owner  : SYS    
7. Workload Type      : Single SQL Statement   
8. Scope              : COMPREHENSIVE   
9. Time Limit(seconds): 60   
10. Completion Status  : COMPLETED   
11. Started at         : 10/26/2011 15:07:04  
12. Completed at       : 10/26/2011 15:07:08  
13.   
14. DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')   
15. ----------------------------------------------------------------------------------  
16. ----------------------------------------------------------------------------------   
17. Schema Name: SCOTT  
18. SQL ID     : d4wgpc5g0s0vu  
19. SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1  
20. -------------------------------------------------------------------------------   
21. FINDINGS SECTION (1 finding)  
22. -------------------------------------------------------------------------------  
23. 1- SQL Profile Finding (see explain plans section below)  
24. --------------------------------------------------------  
25. DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')  
26. -------------------------------------------------------------------------------------    
27. A potentially better execution plan was found for this statement.  
28. Recommendation (estimated benefit: 90.95%)  
29. ------------------------------------------  
30. - Consider accepting the recommended SQL profile.  
31. execute dbms_sqltune.accept_sql_profile(task_name =>  
32.              'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);  

[sql]  view plain copy

1. DECLARE  
2. my_sqlprofile_name VARCHAR2(30);  
3. begin  
4. my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (  
5. task_name => 'my_sql_tuning_task_2',  
6. name => 'my_sql_profile');  
7. end;  
8. /  
9.   
10. PL/SQL procedure successfully completed.  

SESSION1--SCOTT

重新执行查询，即使使用了no_index提示，索引也会被使用

注意，在执行计划中，我们会看到SQL profile "my_sql_profile" used for this statement

[sql]  view plain copy

1. SQL> set autotrace on  
2. SQL> select /*+ no_index(test test_idx) */ * from test where n=1;  
3.   
4.   
5. Execution Plan        
6. -------------------------------------------------------------------------  
7. Plan hash value: 1416057887                                                                    
8. -----------------------------------------------------------------------------   
9. | Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |   
10. -----------------------------------------------------------------------------   
11. |   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |   
12. |*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |   
13. -----------------------------------------------------------------------------                                                                                 
14. Predicate Information (identified by operation id):  
15. ---------------------------------------------------    
16.    1 - access("N"=1)  
17. ---------------          
18. Note   
19. -----   
20.    - SQL profile "my_sql_profile" used for this statement   

 

由这个例子我们可以发现，在必要情况下，SQL Profile可以让hint失效

 

 

如何为每个tuning set 产生一个报告

SELECT 'SELECT d.id , d.owner , d.description , d.created , d.last_modified , d.statement_count, ss.* FROM TABLE(DBMS_SQLTUNE.select_sqlset ('''||name||''')) ss, dba_sqlset d WHERE d.name='''||name||''';'
FROM dba_sqlset d
ORDER BY d.last_modified DESC