DBMS_SQLTUNE详解(2)---sql_profile

来源：互联网发布：广联达预算软件试用版编辑：程序博客网时间：2024/06/06 09:11

DBMS_SQLTUNE详解(2)---sql_profile
---转载http://blog.sina.com.cn/s/blog_61cd89f60102edlp.html

DBMS_SQLTUNE是10g引入的一个新特性，它可以通过自动优化性能较差SQL，并给出合理的优化建议，可以通过OEM进行管理，其中优化建议中的sql_profile文件它是一个存储在数据字典中的信息集合，在SQL执行过程中自动发现并修改较差的估值，进而影响优化器选择最优的执行计划，因此sql_profile文件可以达到不修改应用程序的情况下修改执行计划。
一、执行DBMS_SQLTUNE所需要的权限
     执行dbms_sqltune需要advisor权限
     grant advisor to user;
     grant select_catalog_role to user;  --通过OEM管理必不可少
     grant execute on dbms_sqltune to user;
二、sql_profile文件的作用
     sql_profile不包含单独的执行计划，但是，提供以下信息供优化器选择执行计划
     A.数据库配置、绑定变量、优化统计信息、数据集等信息
     B.追加的统计信息
     值得一提的是，sql_profile并不会以outline方式存储冻结执行计划，当表中数据增长或索引被删除或重建时，在sql_profile不变的情况下执行计划也可以发生变化，信息的存储和与数据的分布或者访问路径有关，但是，经过长时间运行以后，存储的内容就会过时，可以通过先前同样的SQL优化语句进行优化，重新产生sql_profile文件。
三、sql_profile文件相关设置
    sql_profile的控制范围可以通过dba_sql_profiles中的category属性进行控制，如果数据库sqltune_category参数值为DEFAULT，那么此时对所有用户会话session有效，可以通过以下语句查询
    show parameter sqltune_category;
    select category,name from dba_sql_profiles;
    通过修改dba_sql_profile中的category属性来控制sql_profile对那些会话进行控制，举个例子来说，如果将dba_sql_profile中的category属性改为DEV，数据库参数sqltune_category也改为DEV，那么只有这个DEV用户的会话信息受sql_profile的影响，其它用户不受影响，这种设置常用于开发过程中的测试，生产库中不常见
四、sql_profile文件的管理
    sql_profile文件可以通过两种方式进行管理，一种是DBMS_SQLTUNE包进行管理，另一种是通过OEM控制台进行管理

Using Enterprise Manager

On the Performance page, click Top Activity.
The Top Activity page appears.
Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
The SQL Details page appears.
Click the Plan Control tab.
A list of SQL profiles is displayed under SQL Profiles and Outlines.
Select the SQL profile you want to manage.
Do one of the following:
- To enable a SQL profile that is disabled, click Disable/Enable.
- To disable a SQL profile that is enabled, click Disable/Enable.
- To remove a SQL profile, click Delete.
A confirmation page appears.
Click Yes to continue, or No to cancel the action

Using DBMS_SQLTUNE package.

To use the SQL Profiles APIs, you need the CREATE ANY SQL_PROFILE, DROP ANY SQL_PROFILE, and ALTER ANY SQL_PROFILE system privileges.

Accepting a SQL Profile

Use the DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database.

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 is the name of the SQL tuning task.
You can view information about a SQL Profile in the DBA_SQL_PROFILES view.

Altering a SQL Profile

Alter the STATUS, NAME, DESCRIPTION, and CATEGORY attributes of an existing SQL Profile with the ALTER_SQL_PROFILE procedure.

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

In this example, my_sql_profile is the name of the SQL Profile that you want to alter.
The status attribute is changed to disabled which means the SQL Profile will not be used during SQL compilation.

Dropping a SQL Profile

A SQL Profile can be dropped using the DROP_SQL_PROFILE procedure.

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

需要注意的是，sql_profile文件可以使SQL中的hint失效，因此当我们在管理数据库中研究执行计划时，如果发现hint没有启作用，有可能是sql_profile文件影响的结果！

四、查看所有优化任务集

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

五、更多相关信息详见：

NOTE:262687.1 - Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor
NOTE:457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions)
NOTE:1253696.1 - How To Use SQL Profiles for Queries Using Different Literals

0 0