SQL Tuning Advisory with direct SQL Query in Oracle

SQL Tuning Advisory with direct SQL Query in Oracle

We have Query which we want to run the SQL Advisory directly on Query.

Following are the steps to run query directly in SQL Advisory

1. Create a SQL tuning task defining SQL which need to be tuned
You need to enter the query and username of objects present.

DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
my_sqltext := 'SELECT name,job_title FROM EMP';
my_sqltext := my_sqltext || ' where emp_no=100';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task1');
END;
/

2. Execute the SQL tuning task

exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task1');

3. Fetch the Advisory report of SQL tuning

SET LONG 100000
SET PAGESIZE 200
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNING_TASK1') AS recommendations FROM dual;

4. Drop the task name if everything done or not needed more

exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'SQL_TUNING_TASK1');

5. Check if any tuning task already present

SELECT task_name, status FROM dba_advisor_log WHERe task_name = 'SQL_TUNING_TASK';

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.