SQL TUNING ADVISOR SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. SQL Tuning Advisor is a mechanism for resolving problems related to suboptimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance of high-load SQL statements, and prevent regressions by only executing optimal plans. Find SQL Id of the Statement You Just Ran Quickly retrieve the SQL ID of your last executed statement. While connected to the database, you might want to know the sql id of the query you just ran (in your own session, not some other session). Let’s run a sample query Now you might want to know the SQL ID of the above command. You must query PREV_SQL_ID column from V$SESSION You can check if the sql id returned above is correct by checking the SQL TEXT associated with the sql id STEP 2: Run the SQL Advisor Manually ******************************THE END***********************************************

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

set serveroutput off
Select * from hr.employees;

select prev_sql_id from v$session where sid=sys_context('userenv','sid');

select sql_id, sql_text from v$sqltext
where sql_id in ('fxdbrc4jhqn5r');

DECLARE
    l_sql_tune_task_id VARCHAR2(100);
BEGIN
    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
        sql_id => '7uk907hrgrj6j',
        scope => DBMS_SQLTUNE.scope_comprehensive,
        time_limit => 500,
        task_name => '7uk907hrgrj6j_tuning_task11',
        description => 'Tuning task1 for statement 7uk907hrgrj6j'
    );
    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Execute the SQL Tuning Task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '7uk907hrgrj6j_tuning_task11');

-- Retrieve and Display the Tuning Report
SET LONG 65536
SET LONGCHUNKSIZE 65536
SET LINESIZE 100
SELECT DBMS_SQLTUNE.report_tuning_task('7uk907hrgrj6j_tuning_task11') FROM dual;