How to use STA(sql tuning advisor),tuningadvisor

來源:互聯網
上載者:User

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.
 

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

Tags Index: