SQL Profile介绍

本文详细介绍了SQLProfile的概念及其在Oracle数据库性能优化中的应用。SQLProfile能够帮助查询优化器为特定SQL语句生成更高效的执行计划,通过自动SQL调整过程生成并管理SQLProfile,从而改善SQL语句的执行效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

什么是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;
my_sql_tuning_task是SQL调整目标的名称。
这个过程的传入参数中有一个可选参数force_match,默认为FALSE。当设置为FALSE时,不区分空白和大小写,为TRUE时,空白、大小写和字面量都不区分。通过企业管理器来接受SQL概要时,这个参数在ORACLE11g中才可以设置。

修改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调优集名称。
比如利用sql_id来创建tuning_task,我们可以这么运行
declare tuning_task varchar2(30); begin tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'bfb9vn0gh3z0t'); dbms_output.put_line(tuning_task); end;
记下这个tuning_task,用于后面的过程来使用

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


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



示例


SESSION1--SCOTT

创建表,填充数据,然后创建索引和采集统计信息。使用no_index提示来执行查询,使用全表扫描
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

由这个例子我们可以发现,在必要情况下,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



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值