什么是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
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;
修改SQL Profile,可以修改STATUS、NAME、DESCRIPTION和CATEGORY属性
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'my_sql_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
删除SQL Profile
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
end;
/
对我们来说,重点在于创建SQL Profile时的my_sql_tuning_task上,它通过函数create_tuning_task来创建,执行这个函数需要传递下面的参数之一:SQL语句文本、存储在共享池中的SQL语句引用(sql_id)、存储在自动工作量资料库中的SQL语句引用(sql_id)、SQL调优集名称。
declare
tuning_task varchar2(30);
begin
tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'bfb9vn0gh3z0t');
dbms_output.put_line(tuning_task);
end;
下面引用MOS提供的一个示例来演示一下这个过程
示例
SQL> create table test (n number );
Table created.
SQL> declare
begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> create index test_idx on test(n);
Index created.
SQL> exec dbms_stats.gather_table_stats('','TEST');
PL/SQL procedure successfully completed.
set autotrace on
select /*+ no_index(test test_idx) */ * from test where n=1
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
SESSION2--SYS
创建并执行tuning task,并运行report tuning task,采用建议的SQL Profile
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task_2',
description => 'Task to tune a query on a specified table');
end;
/
PL/SQL procedure successfully completed.
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/
PL/SQL procedure successfully completed.
set long 1000
set longchunksize 1000
set linesize 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 10/26/2011 15:07:04
Completed at : 10/26/2011 15:07:08
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
----------------------------------------------------------------------------------
----------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : d4wgpc5g0s0vu
SQL Text : select /*+ no_index(test test_idx) */ * from test where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.95%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);
DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_2',
name => 'my_sql_profile');
end;
/
PL/SQL procedure successfully completed.
SESSION1--SCOTT
重新执行查询,即使使用了no_index提示,索引也会被使用
注意,在执行计划中,我们会看到SQL profile "my_sql_profile" used for this statement
SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
Execution Plan
-------------------------------------------------------------------------
Plan hash value: 1416057887
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
---------------
Note
-----
- SQL profile "my_sql_profile" used for this statement
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