HowtouseSTA（sqltuningadvisor）

一、手工生成Sql tuning advisor 1、SQL text format: DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := SELECT * FROM DBA_SEGMENTS WHERE OWNER=CLIC AND SEGMENT_TYPE=TABLE; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

一、手工生成Sql tuning advisor

1、SQL text format:
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := &＃39;SELECT * FROM DBA_SEGMENTS WHERE OWNER=&＃39;&＃39;CLIC&＃39;&＃39; AND SEGMENT_TYPE=&＃39;&＃39;TABLE&＃39;&＃39;&＃39;;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
scope => &＃39;COMPREHENSIVE&＃39;,
time_limit => 60,
task_name => &＃39;test_sql_tuning_task1&＃39;,
description => &＃39;Task to tune a query&＃39;);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => &＃39;test_sql_tuning_task1&＃39;);
END;
/

2、SQL id format：
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => &＃39;b3uaak09jfaxc&＃39;,
scope => &＃39;COMPREHENSIVE&＃39;,
time_limit => 60,
task_name => &＃39;test_sql_tuning_task1&＃39;,
description => &＃39;Task to tune a query&＃39;);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => &＃39;test_sql_tuning_task1&＃39;);
END;
/

二、查看生成的STAreport：
set long 999999
set LONGCHUNKSIZE 999999
set serveroutput on size 999999
set linesize 200
select dbms_sqltune.report_tuning_task(&＃39;test_sql_tuning_task1&＃39;) from dual;
exec dbms_sqltune.drop_tuning_task(&＃39;test_sql_tuning_task1&＃39;);

删除优化任务
SQL> execdbms_sqltune.drop_tuning_task(task_name => &＃39;li_sql_1&＃39;);

三、accept sql profile

接受建议的 SQL 概要文件,即创建SQL_Profle
SQL> execute dbms_sqltune.accept_sql_profile(task_name => &＃39;test_sql_tuning_task1&＃39;,task_owner =>&＃39;SYS&＃39;, replace => TRUE);

查看创建起来的SQL_Profile信息
SQL>select a.name,a.task_id,a.createdfrom dba_sql_profiles a,dba_advisor_log bwhere a.task_id=b.task_idand b.task_name=&＃39;test_sql_tuning_task1&＃39;;

删除SQL_Profile
SQL>exec dbms_sqltune.drop_sql_profile(name =>&＃39;SYS_SQLPROF_01411bdf99410002&＃39;);