TUNNING com SQL_ID
Para executarmos um tunning em um SQL através de linha de comando, podemos seguir o passo a passo abaixo.
1) Criar a atividade para o tunning do SQL
Com SQL ID:
SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(50);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '0p20wcvkp0pjw');
DBMS_OUTPUT.put_line('id: ' || stmt_task );
end;
/
Com SQL TEXT
Nesta opção, ao invés de utilizarmos o SQL ID , utilizaremos o texto do SQL que queremos fazer o tunning
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => '<comando sql>');
Obs.: Se o comando SQL for muito grande, vale a pena obter o SQL ID para fazer o tunning
Nas duas formas de criação da atividade de tunning (TASK) informadas acima, será gerado um TASK_ID.
Devemos guardar esse TASK_ID, pois será necessário para a continuidade da análise.
2) Executar o tunning
Aqui, iremos executar o tunning no SQL desejado, conforme configurado para a atividade desejada
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_ID');
end;
/
3) Monitorar o processo de tunning
Podemos monitorar o processo de tunning na atividade desejada, com o comando abaixo:
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = '<TASK_ID>';
Quando o TASK_NAME tiver o STATUS=COMPLETED significa que o processo de tunning na atividade configurada foi finalizado.
4) Verificar resultado
Com a confirmação de que o tunning para a atividade foi finalizado, podemos executar o comando abaixo para verificarmos as recomendações.
set lines 200
set long 9999999
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_ID') AS recommendations FROM dual;
Obs.: O parâmetro SET LONG deve ser configurado antes de executar o SELECT, pois sem essa configuração o SELECT poderá não exibir o resultado e acharmos que o mesmo está vazio, quando na verdade ele precisa da configuração do SET LONG, pois o tamanho da saída do tunning é grande.
5) Examinar os resultados
Com as informações do relatório que foram exibidas no passo anterior (passo 4), podemos implementar ou não as recomendações.
Uma das recomendações pode ser a aplicação de um profile. Caso queira aplicar o profile, podemos utilizar o comando abaixo:
begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => '<TASK_ID>', task_owner => 'SYSTEM', replace => TRUE);
end;
/
onde:
TASK_ID é a identificação da atividade onde foi feito o tunning
task_owner é o usuário que fará a aplicação do profile e para isso precisa ser o usuário SYSTEM, ou um usuário que possua as permissões necessárias.
replace => TRUE informa que um sql profile existe será substituído por este que estamos aplicando no momento.
Obs.: No resultado do SQL TUNNING que é gerado no passo 4 desse procedimento, quando existe a possibilidade de aplicarmos um profile, o comando já vem exibido nas recomendações.
6) Verificar os sql profiles
Podemos verificar os profiles no banco de dados através do comando abaixo:
select * from dba_sql_profiles;
7) Desabilitar um sql profile
Se desejarmos desabilitar um sql profile, podemos utilizar o comando abaixo:
begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/
Referências:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CHDGBCDB
http://www.runningoracle.com/product_info.php?products_id=435