标签: dbms_sqltunesql_profile王显伟sql优化执行计划 | 分类: ORACLE新特性实践 |
一、执行DBMS_SQLTUNE所需要的权限
二、sql_profile文件的作用
三、sql_profile文件相关设置
四、sql_profile文件的管理
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.
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
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.
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: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