Oracle Tuning Advisor (SQL Tuning Advisor with SQL Access Advisor

In the case of performance problems with Oracle databases, it is less effort to use Oracle's own toolkit to give reasonable tuning recommendations.

Tuning Advisor is optimized for the execution plan of the input SQL set
The Accsee Advisor is a recommendation on how much performance is improved if SQL set is introduced into an index or partition at this moment.
SQL Tuning Advisor is a suggestion from the one hand that Access advisor can give analysis suggestions for all operations on a table or multiple tables, such as a SELECT statement that would suggest increasing the index, while reducing the performance of DML, SQL access The Advisor is a comprehensive analysis of all operations including SELECT,DML, etc.

The following two kinds of package introduction and specific practices.

The SQL Tuning Advisor has a minimal granularity adjustment tool.

Add one or more SQL statements as input, analyze all access paths, and then generate recommendations for improved SQL statements, and propose indexes, materialized views, and partitioning schemes to improve the overall performance of your application.
In addition, in the Maintenance window, Oracle will also automate the SQL Tuning Advisor for automatic Workload Repository (AWR) to determine and record high-load statements
SQL Access Advisor
It involves all the SQL statements in the workload and presents indexes, special views, and partitioning schemes to improve the overall performance of the application.

Small difference:

SQL Tuning Advisor adjusts each SQL statement individually
SQL Access Advisor adjusts all SQL statements at the same time

SQL Tuning Advisor:

1. In an anonymous PL/SQL block, define an adjustment task for the SQL statement.

?

123456789101112131415

declare     tune_task_name varchar2(30)     ;    bad_sql_stmt clob;begin    bad_sql_stmt := ‘select distinct id from demo.txcl‘;    tune_task_name := dbms_sqltune.create_tuning_task        ( sql_text => bad_sql_stmt,          user_name => ‘DEMO‘,          scope => ‘COMPREHENSIVE‘,          time_limit => 60,          task_name => ‘xcl_sql_tuing_task‘,          description => ‘See what is wrong with the SELECT‘            ) ;end;/

2. The time limit specified when setting a task is 60 minutes.

?

12345678

begin      dbms_sqltune.set_tuning_task_parameter           (Task_name = ' xcl_sql_tuing_task '            parameter = ' time_limit '            value =           end; /

3. Use the Execute_tuning_task process to start the adjustment task

?

12345

begin    dbms_sqltune.execute_tuning_task      (task_name => ‘xcl_sql_tuing_task‘);end;/

4. Check mission status by connecting Dba_advisor_tasks and V$advisor_progress

?

123	Select Task_name,status,sofar,totalworkfrom dba_advisor_tasks  join v$advisor_progress using(task_id) where task_name = ‘xcl_sql_tuing_task‘;

5. Review the recommendations given by the tuning task

?

1	select dbms_sqltune.report_tuning_task(‘xcl_sql_tuing_task‘) from dual;

SQL Access Advisor:

Use Dbms_advisor.quick_tune to make adjustments to a single SQL statement
It executes much like the SQL Tuning Advisor, but performs a much deeper level of analysis and generates more recommendations.

1. Specify a directory where the output file is stored

?

12	--输出文件目录create directory tune_scripts as ‘/demo/tune_scripts‘;

2. Collect, analyze and give advice

?

12345678910111213141516171819202122232425262728293031323334353637383940414243

declare    v_task_name VARCHAR2(255);    v_sql_stmt  VARCHAR2(4000);    v_tune_sql_filename VARCHAR2(255);    v_cfile_directory VARCHAR2(255);begin    v_sql_stmt := ‘select  * from  demo.txcl t where t.id = :1 ‘;    v_task_name :=‘tune_demo_txcl‘;    v_tune_sql_filename :=  ‘tune_demo_txcl.sql‘;    v_cfile_directory := ‘TUNE_SCRIPTS‘;    dbms_stats.gather_table_stats(        ownname => ‘DEMO‘,        tabname => ‘TXCL‘,        cascade => true);        --dbms_advisor.reset_task(v_task_name);     --分析并得到建议    dbms_advisor.quick_tune(DBMS_ADVISOR.SQLACCESS_ADVISOR,                v_task_name,                v_sql_stmt);    --将建议输出到指定文件中    -- select * from dba_advisor_actions where task_name =‘tune_demo_txcl‘ ;    -- select * user_advisor_sqla_wk_stmts where task_name =‘tune_demo_txcl‘;     dbms_advisor.create_file(        dbms_advisor.get_task_script(v_task_name),        v_cfile_directory,        v_tune_sql_filename        );    --删除任务    dbms_advisor.delete_task(v_task_name);  exception     when others then        dbms_output.put_line(sqlerrm);end;/

Common errors:

Ora-13600:error encountered in Advisor

Qsm-00794:the statement can not is stored due to a violation of the invalid table reference filter

The reason is that Dbms_advisor.quick_tune does not support Advisor functionality for tables of SYS and system two users, and the tables that can be analyzed are not built with these two users.

Oracle Tuning Advisor (SQL Tuning Advisor with SQL Access Advisor