Oracle固定执行计划---SQL概要
| 技术 | 是否修改SQL | 版本要求 | 适合场景 |
|---|---|---|---|
| 提示 | 是 | 所有版本 | |
| 存储概要 | 否 | 8i及以上版本 | |
| SQL概要 | 否 | 10g及以上(企业版) | SELECT statements UPDATE statements INSERT statements (only with a SELECT clause) DELETE statements CREATE TABLE statements (only with the AS SELECT clause) MERGE statements (the update or insert operations) |
| SQL计划基线 | 否 | 11g及以上(企业版) |
It is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. This can be done by running Automatic SQL Tuning again on the same statement to regenerate the SQL Profile.
select category,name from dba_sql_profiles;By default, all profiles are created in the DEFAULT category. This means that all user sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.
By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. For example, by setting the category of a SQL Profile to DEV, only those users sessions where the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a SQL Profile in a restricted environment before making it available to other user sessions.
Accepting a 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 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
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
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
end;
/
A SQL Profile can be dropped using the DROP_SQL_PROFILE procedure.一、创建SQL Profile任务
---当前普通用户创建SQL Profile
set serveroutput on
declare
v_task_name varchar2(30);
begin
v_task_name:=dbms_sqltune.create_tuning_task(sql_text=>'select /*+ no_index(test idx_test_id) */ * from test where id=5', task_name=>'sql_profile_task_1');
dbms_sqltune.execute_tuning_task(v_task_name);
dbms_output.put_line(v_task_name);
end;
/
---SYS用户创建SQL Profile
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select /*+ no_index(test idx_test_id) */ * from test where id=5';
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_1',
description => 'Task to tune a query on a specified table');
end;
/
---按SQL_ID创建任务
set serveroutput on
declare
v_task_name varchar2(30);
begin
v_task_name:=dbms_sqltune.create_tuning_task(sql_id =>'8hv8m192vj9nc', time_limit=>7200, task_name=>'sql_profile_task_1');
dbms_sqltune.execute_tuning_task(v_task_name);
dbms_output.put_line(v_task_name);
end;
/
user_name: 用户名需要大写
二、执行Profile任务
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_sql_tuning_task_1');
end;
/三、查看调优报告
set long 10000
set longchunksize 1000
set linesize 100
set heading off
select dbms_sqltune.report_tuning_task('my_sql_tuning_task_1') from dual;
set heading on四、接受Profile
declare
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := dbms_sqltune.accept_sql_profile(
task_name => 'sql_profile_task_1',
name => 'my_sql_profile');
end;
/
or
execute dbms_sqltune.accept_sql_profile(task_name =>'sql_profile_task_1', task_owner => 'SCOTT', replace => TRUE);五、查看Profile
col created for a20
col sql_text for a20
col category for a20
set linesize 200 pagesize 200
select name,category,created,status,sql_text from dba_sql_profiles;
select task_name,status,execution_start,execution_end from user_advisor_log;
SELECT TASK_ID,TASK_NAME,ADVISOR_NAME,CREATED,LAST_MODIFIED,LAST_EXECUTION,STATUS FROM DBA_ADVISOR_TASKS WHERE TASK_NAME = 'my_sql_tuning_task_1';
select distinct TASK_NAME,SQL_ID,EXECUTION_NAME,PLAN_HASH_VALUE,TIMESTAMP from dba_advisor_sqlplans where sql_id in('4zbqykx89yc8v') order by sql_id;六、禁用/启用/删除Profile
---禁用profile
begin
dbms_sqltune.alter_sql_profile(
name => 'SYS_SQLPROF_0177d85381f70000',
attribute_name => 'status',
value => 'disabled');
end;
/
PL/SQL procedure successfully completed.
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;
---启用profile
begin
dbms_sqltune.alter_sql_profile(
name => 'SYS_SQLPROF_0177d85381f70000',
attribute_name => 'status',
value => 'enabled');
end;
/
SQL> select name,category,signature,type,status,force_matching from dba_sql_profiles;
---删除profile
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => '**my_sql_profile**');
end;
/七、删除Profile任务
---删除task
exec dbms_sqltune.drop_tuning_task('my_sql_tuning_task_1');
八、彩蛋
---步骤一、二可以合并写在一起:
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select /*+ no_index(test idx_test_id) */ * from test where id=5';
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_1',
description => 'Task to tune a query on a specified table');
dbms_sqltune.execute_tuning_task(my_task_name);
dbms_output.put_line(my_task_name);
end;
/