The Oracle Tuning Advisor (SQL Tuning Advisor and SQL Access Advisor)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Performance problems appear in the Oracle database, using the Oracle itself toolkit, give reasonable tuning advice is more labor-saving approach.

The following two kinds of package is introduced and the specific practices.

SQL Tuning Advisor the smallest granularity adjustment tool.

The one or more SQL statements as input, analysis of all access paths, and then generate the improved SQL statement suggestion, and put forward the indexes, materialized views and partition scheme to improve the overall performance of the application.
In addition, during a maintenance window, Oracle will according to the Automatic Workload Repository (AWR) high load automatic running SQL Tuning Advisor statement to determine and record
SQL Access Advisor
It involves all the SQL statements in the workload, and put forward the index, specialized view and partition scheme to improve overall application performance.

Small differences:

SQL Tuning Advisor were adjusted to each SQL statement
SQL Access Advisor at the same time adjustment of all SQL statements

SQL Tuning Advisor :

1 in the anonymous PL/SQL blocks, SQL statement to define the task of adjusting.

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;
/
value 2 setup time specified task; is 60 minutes.
begin
	dbms_sqltune.set_tuning_task_parameter
		(task_name => 'xcl_sql_tuing_task',
		 parameter => 'TIME_LIMIT',
		 value => 30
		 );
end;
/
3 using EXECUTE_TUNING_TASK process adjustment
begin
	dbms_sqltune.execute_tuning_task 
	 (task_name => 'xcl_sql_tuing_task');
end;
/
4 by the connection of DBA_ADVISOR_TASKS and V$ADVISOR_PROGRESS to check the task state
select task_name,status,sofar,totalwork
from dba_advisor_tasks  
join v$advisor_progress using(task_id) 
where task_name = 'xcl_sql_tuing_task';
5 see the task of adjusting the advice given
select dbms_sqltune.report_tuning_task('xcl_sql_tuing_task') from dual;

SQL Access Advisor:


Use dbms_advisor.quick_tune to carry on the adjustment to the single SQL statement
Its execution is like SQL Tuning Advisor, but the level of analysis performed is much deeper, generating recommendations more.

1 specify a stored output directory

The output file directory
create directory tune_scripts as '/demo/tune_scripts';

2 collection, analysis and recommendations

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); 

	Analysis and suggestions
	dbms_advisor.quick_tune(DBMS_ADVISOR.SQLACCESS_ADVISOR,
				v_task_name,
				v_sql_stmt);

	-- will suggest output to the specified file
	-- 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
		);


	- delete tasks
	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 be stored due to a violation of the invalid table reference filter

The reason for the SYS and SYSTEM two users table advisory function does not support dbms_advisor.quick_tune, can analyze table don't use this two user built.


Official documents: http://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm#PFGRF94881


MAIL:

BLOG

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Beck at December 02, 2013 - 1:46 PM