sql_profile 固定SQL执行计划

drop table t1;
drop table t2;
create table t1 as select object_id,object_name,owner from dba_objects where owner like 'C##%' and rownum<=50000; 
create table t2 as select * from dba_objects; 
create index t2_idx on t2(object_id);

select count(1) from t1;
select count(1) from t2;

begin dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1');end;
begin dbms_stats.gather_table_stats(user,'t2',cascade=>true,method_opt=>'for all columns size 1');end;

select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1' and t1.object_id=t2.object_id;

select /*+ use_nl(t1 t2) index(t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id=t2.object_id;

select sql_id,sql_text from v$sql where sql_text like '%t1.object_name%' -- bwjr380j31jay


DECLARE
my_task_name VARCHAR2(30);
sql_id VARCHAR2(30);
BEGIN
sql_id := '&sqlid';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => sql_id,
scope => 'comprehensive',
time_limit => 60,
task_name => 'my_sql_tuning_' ||
sql_id,
description => 'Tuning Task');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('my_sql_tuning_' || sql_id);
END;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_&sqlid') FROM DUAL;
--BEGIN dbms_sqltune.drop_tuning_task('my_sql_tuning_btdjzt659wtmk'); END;
BEGIN dbms_sqltune.drop_tuning_task('my_sql_tuning_&sqlid'); END;

--调优任务推荐内容
begin
dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_bwjr380j31jay', task_owner => 'C##xxx',
replace => TRUE);
end; 


--查看当前用户创建的调优任务
select * from USER_ADVISOR_TASKS;
begin
DBMS_SQLTUNE.drop_tuning_task(task_name => 'wxw_sql_tuning_task');
end;

--查询已存在的SQL_PROFILE
select * from DBA_SQL_PROFILES;
begin
dbms_sqltune.drop_sql_profile(name=>'SYS_SQLPROF_0178392b025c0000');
end;