How to use STA（sql tuning advisor），tuningadvisor

How to use STA（sql tuning advisor），tuningadvisor
一、手工產生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_text    => my_sqltext,
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 60,
                                                  task_name   => 'test_sql_tuning_task1',
                                                  description => 'Task to tune a query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');
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 => 'b3uaak09jfaxc',
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 60,
                                                  task_name   => 'test_sql_tuning_task1',
                                                  description => 'Task to tune a query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task1');
END;
/


二、查看產生的STAreport：


set long 999999
set LONGCHUNKSIZE 999999
set serveroutput on size 999999
set linesize 200
select dbms_sqltune.report_tuning_task('test_sql_tuning_task1') from dual;


exec dbms_sqltune.drop_tuning_task('test_sql_tuning_task1');


刪除最佳化任務
SQL> execdbms_sqltune.drop_tuning_task(task_name => 'li_sql_1');


三、accept sql profile
接受建議的 SQL 概要檔案,即建立SQL_Profle
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'test_sql_tuning_task1',task_owner =>'SYS', 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='test_sql_tuning_task1';


刪除SQL_Profile
SQL>exec dbms_sqltune.drop_sql_profile(name =>'SYS_SQLPROF_01411bdf99410002');












論文摘要 高分人翻譯

As to huge database of the data amount, the inquiry velocity difference between inferior SQL sentence and high-quality SQL sentence can be in order to reach dozens of very to hundreds of times. In practical application, one inquire system to just realize his function briefly, but the speech as to some inquiry systems needing to respond in real time, it causes performance to be low if because there is not high-quality SQL sentence, it is pointless even can realize its function. SQL inquires the essence optimized is base on the premise that the result is correct. The optimizing device improves the efficiency of finding out according to the index defined, use SELECT sentence optimized, try hard to avoid the emergence that the whole form scans, improve the inquiry velocity, improve working efficiency. This text proceeds from establishment of the index and optimization of SELECT sentence. Among them the establishment of the index is to inquire about the prerequisite optimized, this text has put forward some concrete suggestions on the establishment of the index. And the optimization of bad SQL sentence is the focal point herein. So to the optimization of SQL sentence, it is used for explaining how to optimize and compare with the result after optimizing that this text puts out a large number of examples.