How to use STA (SQL tuning advisor), tuningadvisor

How to use STA (SQL tuning advisor), tuningadvisor
1. manually generate 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 => 'computing ',
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 => 'computing ',
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;
/


Ii. view the generated STAreport:


Sets long 999999
Set long chunksize 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 ');


Delete optimization task
SQL> execdbms_sqltune.drop_tuning_task (task_name => 'li _ SQL _1 ');


Iii. accept SQL profile
Accept the recommended SQL profile, that is, create SQL _Profle
SQL> execute dbms_sqltune.accept_ SQL _profile (task_name => 'test _ SQL _tuning_task1 ', task_owner => 'sys', replace => TRUE );
 
View the created SQL _Profile Information
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 ';


Delete SQL _Profile
SQL> exec dbms_sqltune.drop_ SQL _profile (name => 'sys _ SQLPROF_01411bdf99410002 ');












High score Translation

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.