Utilizando o pacote dbms_sqltune para otimização de queries

O pacote DBMS_SQLTUNE identifica e ajuda a resolver problemas de desempenho do banco de dados

Abaixo Passo a Passo para tunning de uma query

1) Pegar o SQL_ID da query que pretende otimizar

SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT coluna1, coluna2 FROM tabela%';

2) Criar a Task

SQL_ID --> SQL_ID (captura no select acima)
SCOPE --> DBMS_SQLTUNE.SCOPE_COMPREHENSIVE
TIME_LIMIT --> Tempo de execução da query

DECLARE
VAL VARCHAR2(4000);
BEGIN
VAL := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_ID => 'gxtg1pzkt5fm5', SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, TIME_LIMIT => 60, TASK_NAME => 'Tuning 1', DESCRIPTION => ''Tuning de SQL 1');
END;
/

Caso não tenha o SQL_ID

set serveroutput on
DECLARE
l_sql CLOB;
l_sql_tune_task_id VARCHAR2(200);
BEGIN
l_sql := 'select * from teste whete coluna_teste > sysdate -30';
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
user_name => 'SCOTT',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'Tuning 1',
description => 'Tuning de SQL 1');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

3) Executar a task para obter as informações
Obs.: nesse passo que vai demorar, dependendo do tempo da query

EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK('Tuning 1');

4) Recomendações

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('Tuning 1') RECOMMENTATION FROM DUAL;

5) Excluir o Task

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('Tuning 1');

6) Verificar outros advisor

select task_id,task_name,status from user_advisor_log;

7) Interromper a task

begin
DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'Tuning 1');
end;
/

8) Verifica task interrompida

begin
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'Tuning 1');
end;
/

9)Cria o script com o conjunto de advisor

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('Tuning 1') RECOMMENTATION FROM DUAL;