Step-by-Step Guide to Creating and Executing a SQL Tuning Task
SQL tuning is crucial for optimizing query performance in Oracle databases. This step-by-step guide explains how to create and execute a SQL tuning task, along with a detailed overview of task parameters for various scenarios.
1. Find the SQL_ID:
If you’re not using a SQL Tuning Set (which is a set of pre-captured SQL IDs) or manually supplying the query, you will need the SQL ID of the query you want to analyze. Here are some ways to find the SQL_ID of a query:
- From the V$SQL view (if the query is still in the cache):
SQL> SELECT * FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT * FROM %';- From a particular session (for example, a user named ‘TEST’):
SELECT SQL_ID FROM V$SESSION WHERE USERNAME='TEST';- From AWR Reports: The SQL_ID information for top resource/time-consuming queries is also available in AWR reports.
2. Create the SQL Tuning Task:
You can create a tuning task from different sources such as the cursor cache, AWR, tuning set, or by manually supplying the SQL query. Below are the examples with explanations for the task parameters:
From the Cursor Cache
DECLARE
v_sql_tune_task_id VARCHAR2(100);
BEGIN
v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '7d95f5850jkjr', -- SQL_ID of the query to be tuned
scope => DBMS_SQLTUNE.scope_comprehensive, -- Scope of the tuning (comprehensive tuning)
time_limit => 1000, -- Time limit in seconds for the task
task_name => 'test_tuning_task', -- Name of the tuning task
description => 'Tuning task for the SQL statement with the ID:7d95f5850jkjr from the cursor cache' -- Task description
);
DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
END;
/- Explanation of Parameters:
- sql_id: The unique identifier of the SQL query to be tuned.
- scope: Defines the level of tuning (comprehensive or basic). The scope_comprehensive option performs a full analysis, including various aspects like physical and logical I/O.
- time_limit: Specifies how long the tuning task should run (in seconds).
- task_name: The name for the SQL tuning task.
- description: A description of the task.
From the AWR
DECLARE
v_sql_tune_task_id VARCHAR2(100);
BEGIN
v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 617, -- AWR snapshot ID for the start of the period
end_snap => 620, -- AWR snapshot ID for the end of the period
sql_id => '7d95f5850jkjr', -- SQL_ID of the query to be tuned
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1000,
task_name => 'test_tuning_task',
description => 'Tuning task for the SQL statement with the ID:7d95f5850jkjr from the AWR');
DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
END;
/- Explanation of Parameters:
- begin_snap and end_snap: Snapshot IDs from AWR that define the period during which the SQL performance is analyzed. These snapshots provide historical data for the tuning task.
- sql_id: The SQL identifier for the query.
- scope, time_limit, task_name, description: Same as above.
From the Tuning Set
DECLARE
v_sql_tune_task_id VARCHAR2(100);
BEGIN
v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'sql_tuning_set_0800_1200', -- Name of the SQL Tuning Set to be used
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1000,
task_name => 'test_tuning_task',
description => 'Tuning task for a particular SQL tuning set.');
DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
END;
/- Explanation of Parameters:
- sqlset_name: The name of the pre-defined SQL Tuning Set that contains the SQL queries to be tuned.
- scope, time_limit, task_name, description: Same as above.
By Manually Supplying the Query
DECLARE
v_sql_text VARCHAR2(1000);
v_sql_tune_task_id VARCHAR2(100);
BEGIN
v_sql_text := 'SELECT U.USERID, U.USERNAME ' ||
'FROM USERS U, REGION R ' ||
'WHERE U.REGID=R.REGID AND U.USERID=:uid AND R.REGCLASS=:cid';
v_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => v_sql_text, -- SQL query text for tuning
bind_list => sql_binds(anydata.ConvertNumber(100)), -- Bind variables for the query
user_name => 'HR', -- The user under which the query is executed
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1000,
task_name => 'test_tuning_task',
description => 'Tuning task for a problematic query...');
DBMS_OUTPUT.put_line('v_sql_tune_task_id: ' || v_sql_tune_task_id);
END;
/- Explanation of Parameters:
- sql_text: The SQL query you want to tune (instead of using sql_id).
- bind_list: A list of bind variables for the query (if applicable).
- user_name: The user who runs the query.
- scope, time_limit, task_name, description: Same as above.
3. Execute the Tuning Task:
Once the tuning task has been created, you can execute it as follows:
SQL> EXECUTE DBMS_SQLTUNE.execute_tuning_task(task_name => 'test_tuning_task');4. Cancel the Tuning Task (if needed):
If the execution is taking too long or causing performance issues, or if you need to cancel the task for any other reason, use:
SQL> EXECUTE DBMS_SQLTUNE.cancel_tuning_task(task_name => 'test_tuning_task');5. Pause/Resume the Tuning Task:
You can also pause or resume the tuning task during execution:
- Pause the task:
SQL> EXECUTE DBMS_SQLTUNE.interrupt_tuning_task(task_name => 'test_tuning_task');- Resume the task:
SQL> EXECUTE DBMS_SQLTUNE.resume_tuning_task(task_name => 'test_tuning_task');6. Monitor the Status of the Tuning Task:
To check the status of the tuning task, use the following query:
SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE UPPER(TASK_NAME)='TEST_TUNING_TASK';
TASK_NAME STATUS
------------------------------ -----------
test_tuning_task COMPLETED7. Get the Resulting Report:
Once the task is completed, you can get the tuning report using:
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_tuning_task') FROM DUAL;This will generate a comprehensive tuning report that helps you analyze and improve the performance of the SQL query.
By following these steps, you can efficiently create, execute, and manage SQL tuning tasks in Oracle, ensuring that your queries are optimized for the best performance.
