Monday, May 16, 2016

How to run SQL Tune for a poorly performing SQL query

Disclaimer

It's always best to work on the underlying query rather than apply a profile to a query. A profile would affect the same query across all schemas and may reduce performance for another customer.
A profile when applied should be considered a short term solution to an immediate customer problem.
A CR must be open requesting changes to the query which required a profile, after the profile is applied, even if the problem seems to be resolved.

Obtain the querie's sql_id

select sql_id, sql_text
from v$sql
where sql_text like 'select * from sometable%';

Run a tuning task

Replace <sql_id> with the sql_id obtained from the previous query.
A tuning task ID will be obtained from this step.

set serveroutput on
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/

Execute the tuning task 

Replace <tuning id="" task=""> with the task id obtained from the previous step.

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tuning id="" task=""');
end;
/
This step takes some time depending on the query's complexity, amount of objects to analyze.
It's possible to verify that the task is running with the following query, in another session.
Replace <task id=""> with the task id used in the previous step:

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = ';

Read the recommandations

set pages 0
set linesize 1000
SELECT DBMS_SQLTUNE.report_tuning_task('TASK_107340') AS recommendations FROM dual;
The tuning task may provide a new execution plan. Compare the current and previous plan.  It will also inform of the improvement gain from changing to the new profile. If the benefits are great and can alleviate a current problem for a customer, apply the profile

 Applying a profile

begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE);
end;
/
That command will be provided inside the recommandation.

No comments :

Post a Comment