执行sqltuning

--1.执行SQL tuning
exec dbms_sqltune.drop_tuning_task('sql_test');  
DECLARE  
my_task_name VARCHAR2(30);  
my_sqltext   CLOB;  
BEGIN  
my_sqltext := q'[SELECT APP_KEY||CONDITION_ID FROM A_RESULT_CONDITION WHERE CONDITION_GROUP_CD  IN ( '1005','1013','1016','1019')]';  
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(  
sql_text    => my_sqltext,  
user_name   => 'TEST_SZ',   --注意是大小写否则会报错提示用户无效  
scope       => 'COMPREHENSIVE',  
time_limit  => 60,  
task_name   => 'ocpyang_tuning_sql_test',  
description => 'Task to tune a query on a specified table');  
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'ocpyang_tuning_sql_test');  
END;  
/  
--2.执行SQL_tuning的执行计划
exec dbms_sqltune.execute_tuning_task('ocpyang_tuning_sql_test');  
--3.检查优化任务的状态  
  
SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='ocpyang_tuning_sql_test';  


--4.显示SQL_tuning的执行计划
set serveroutput on  
SET LONG 999999  
set serveroutput on size 999999  
SET LINESIZE 100  
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'ocpyang_tuning_sql_test') from DUAL;  


--5.删除SQL_tuning的执行计划
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('ocpyang_tuning_sql_test')






declare  
my_sqlprofile_name VARCHAR2(200);  
begin  
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE  
(  
task_name => 'ocpyang_tuning_sql_test',  
name =>'ocpyang01',  
task_owner => 'TEST_SZ',  
replace => TRUE  
);  
end;  
/  
--4.创建索引
 create index TEST_SZ.IDX$$_03AC0001 on
    TEST_SZ.A_RESULT_CONDITION("CONDITION_GROUP_CD","APP_KEY"||TO_CHAR("CONDITION_ID"));
--5.删除索引
drop index  TEST_SZ.IDX$$_03AC0001


--收集直方图信息
exec dbms_stats.gather_table_stats(OWNNAME=>'TEST_SZ',tabname=>'A_RESULT_CONDITION',estimate_percent => null,method_opt => 'for all columns' ,cascade => true );
optimizer_mode     string ALL_ROWS
alter system set optimizer_mode=ALL_ROWS;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22569416/viewspace-1969327/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22569416/viewspace-1969327/