Scribd
Upload
10 views3 pages

SQL Tuning with DBMS_SQLTUNE Steps

This document outlines a step-by-step process for tuning poorly performing SQL queries using Oracle's SQL Tuning Advisor. It includes identifying the slow query, creating and executing a tuning task, generating reports, accepting recommended SQL profiles, and fixing preferred SQL plans. The steps are designed to improve query performance effectively.

Uploaded by

caipei.ccp.chen478
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views3 pages

SQL Tuning with DBMS_SQLTUNE Steps

This document outlines a step-by-step process for tuning poorly performing SQL queries using Oracle's SQL Tuning Advisor. It includes identifying the slow query, creating and executing a tuning task, generating reports, accepting recommended SQL profiles, and fixing preferred SQL plans. The steps are designed to improve query performance effectively.

Uploaded by

caipei.ccp.chen478
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

⚡ Quick Steps to Implement:

📌 1. Identify a Poorly Performing SQL Query​


Use AWR or SQL Monitor to find the slow query and note down the sql_id (e.g.,
dhmg0ggdtvcgn).

📌 2. Create a SQL Tuning Task


DECLARE
sql_tune_task_id VARCHAR2(100);
BEGIN
sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'dhmg0ggdtvcgn',
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => 300,
task_name => 'dhmg0ggdtvcgn_sql_tune_task1',
description => 'Tuning task for statement dhmg0ggdtvcgn'
);
DBMS_OUTPUT.PUT_LINE('SQL Tune Task ID: ' || sql_tune_task_id);
END;
/

📌 3. Execute the Tuning Task and Accept the Recommended SQL Profile
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name =>
'dhmg0ggdtvcgn_sql_tune_task1');

📌 4. Generate a Report for the SQL Tuning Task


SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('dhmg0ggdtvcgn_sql_tune_task1')
FROM dual;

Review the output and identify the recommended execution plan.

📌 5. Accept the SQL Profile Recommended by SQL Tuning Advisor


EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => 'dhmg0ggdtvcgn_sql_tune_task1',
task_owner => 'SYS',
replace => TRUE
);

📌 6. Find the SQL Handle


SELECT * FROM (
SELECT t.SQL_HANDLE, [Link], t.SQL_TEXT, s.SQL_ID
FROM [Link]$TEXT t, DBA_HIST_SQLSTAT s
WHERE s.SQL_ID = 'dhmg0ggdtvcgn'
AND [Link] = s.FORCE_MATCHING_SIGNATURE
) WHERE ROWNUM = 1;

📌 7. Generate SQL Plan Baselines​


If the SQL Handle value is SQL_a31ce7054bbc4aa9, run:

SELECT dbms_sqltune_util0.sqltext_to_sqlid(sql_text||chr(0)) sql_id,


plan_name, enabled, accepted, fixed, creator, origin, created, last_modified
FROM dba_sql_plan_baselines b
WHERE SQL_HANDLE LIKE 'SQL_a31ce7054bbc4aa9'
ORDER BY sql_id DESC;

📌 8. Fix the Preferred SQL Plan​


If sql_handle = 'SQL_a31ce7054bbc4aa9' and plan_name =
'SQL_PLAN_a67770p5vskp9dbefc0aa', execute:

DECLARE
plans_loaded PLS_INTEGER;
BEGIN
plans_loaded := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => 'SQL_a31ce7054bbc4aa9',
plan_name => 'SQL_PLAN_a67770p5vskp9dbefc0aa',
attribute_name => 'FIXED',
attribute_value => 'YES'
);
DBMS_OUTPUT.PUT_LINE('Plans modified: ' || plans_loaded);
END;
/
📌 9. Verify SQL Plan Baselines
SELECT dbms_sqltune_util0.sqltext_to_sqlid(sql_text||chr(0)) sql_id,
plan_name, enabled, accepted, fixed, creator, origin, created, last_modified
FROM dba_sql_plan_baselines b
WHERE SQL_HANDLE LIKE '%SQL_a31ce7054bbc4aa9%'
ORDER BY sql_id DESC;

You might also like