Automatic SQL Tuning foi um novo recurso introduzido no 10G e depois no 11g algumas mudanças foram feitas que serão depois mostradas aqui nesse “White Paper”. Com a versão 10G, o Automatic SQL Tuning, passou a executar o SQL Tuning Advisor em instruções SQL de alta carga, assim com a versão Oracle 10g, o Oracle introduziu o SQL Tuning Advisor e com isso o Tuning Experts usado para realizar o tuning das instruções SQL manualmente executando o SQL Tuning Advisor nos SQL de alta carga identificados pelo ADDM. O ADDM automaticamente identifica as instruções com maior carga através do sistema de estatísticas de desempenho dentro da retenção do AWR no “tablespace” SYSAUX. O problema é que os usuários devem procurar manualmente nos relatórios de ADDM afim de encontrar as instruções com maiores cargas para executar então oSQL Tuning Advisor. Nas versões 11G e 12C leva isso um passo à frente, estas versões executa automaticamente todas as noites o SQL Tuning Advisor em instruções SQL de maior cargas com base no AWR TOP SQL e visualiza os relatórios de recomendações ou então aceita as recomendações automaticamente caso o SQL Profileesteja habilitado através do parâmetro accept_sql_profiles da package dbms_sqltune do tuning advisor. Vamos discutir mais a abaixo o que é um SQL Profile e qual o seu uso.
A tarefa automática de sql tuning executada a noite, tem um processo de tuning que identifica as instruções SQL de maiores cargas e executa o SQL Tuning Advisor sobre elas e todas as recomendações são relatados, exceto a aceitação de SQL Profile automática que tem que ser habilitado, caso contrário, tudo é simplesmente recomendado e não implementado. Nesse artigo, usamos aqui duas principais “features” do Oracle que são o SQL Tuning Advisor e SQL Profiles. Vamos entender o que elas são, o SQL Access Advisor será discutido em um próximo artigo, então vamos continuar com o SQL Tuning Advisor e SQL Profile por enquanto.
SQL Tuning Advisor SQL Tuning Advisor é um advisor disponível no tuning pack e leva a entrada das instruções SQL e realiza análises sobre elas. Ele faz 4 tipos de análises sobre as instruções e em seguida, da as suas recomendações para o DBA implementar caso o percentual de melhoria for positivo, com base em seus requisitos e os acordos de nível de serviço (SLA). Esse advisor funciona também a noite, durante a janela de manutenção( “maintenance Windows”). Quando essa tarefa é executada, ele limita o tempo de execução de cada instrução para 1200 segundos (20 minutos). As 4 análises feitas pelo SQL Tuning Advisor são as seguintes :-
Statistics Analysis (Análise de estatísticas):= Para conferir se as tabelas envolvidas na instrução contém estatísticas obsoletas ou se elas simplesmente não existem. Caso as estatísticas não estão estejam presentes, ou se eles já estão obsoletos, o Oracle atualiza as informações para GATHER_STATS_JOB.
“Access path analysis” (Análise do caminho de acesso):= Como podemos criar índices e otimizar as instruções. Mas lembre-se, apenas a criação de índices é recomendado e não o seu impacto, é por isso que algumas vezes, um outro advisor chamado SQL Access Advisor é recomendado para ser executado com o SQL Tuning Advisor. O SQL Access advisor é um especialista em recomendações de indexes como quetipo de índiceque deve ser criado ou se podemos alterar um tipo existente de índice, como por exemplo mudar um index b tree para um bitmap index se a cardinalidade da coluna é baixa. Essa é a razão pela qual SQL Tuning Advisor recomenda nos a executar SQL Access Advisor, porque ele é um especialista em “indexes”, “materialized views” e “partitioning”.
Sql Structure analysis (Análise da estrutura “) := Verifica como escrevemos as nossas queries, para que possa recomendar alteração no código, como por exemplo usar “union all” em vez de union pois unionall evita sorting, ou até mesmo pode nos recomendar a usar o operador EXISTS em vez de IN.
SQL Profiling := SQL Profiles foi introduzido no 10g e continua disponível nas versões 11g e 12c onde tiveram diversas melhorias. Do 11g a diante, o SQL PLAN MANAGEMENT assume o controle de todo o uso dos planos e também cuida dos sql profiles. SQL Profiles teve um grande impacto no funcionamento do banco de dados Oracle em que você nunca tem que codificar nenhuma instruçãoSELECT para que você chutar que caminho seguir. O hard coding, foi realizada em versões anteriores através de hints de esquemas armazenados. Quando o optimizer apresenta um plano ruim devida a falta de estatísticas apuradas, é quando o SQL Profile contribui com informações para o optimizer na configuração de banco de dados, como bindvariables, estatísticas, conjunto de dados (data set), etc. Resumindo, SQL profilesão um guia para o optimizer a fazer um plano melhor. Lembre-se que você não pode criá-los, você é guiado para aceitar o SQL Profile gerado pelo Oracle com a ajuda do SQL Tuning Advisor, que será então utilizado pelo banco de dados Oracle automaticamente quando a instrução é analisado caso umSQL Profile está disponível. Uma coisa muito importante a saber é que o SQL Profiles não tem capacidade para reproduzir um plano de execução enquanto que o “SQL Plan Baselines” tem essa capacidade. Assim, o SQL Profiles vai apenas corrigir a estimativa de custo que são recomendadas uma vez que você tenha executado o SQL Tuning Advisor na instrução em que você deseja realizar o tuning, essa é uma abordagem reativa em contraste com a abordagem “SQL Plan baseline”. Iremos discutir mais a fundo sobre os SQL Profiles em outro artigo, por enquanto, vamos focar ao SQL Tuning Advisor. Se você tem a licença do “tuning Pack”, você tem então o direito de utilizar o “SQL Tuning Advisor”, que pode recomendar um SQL Profile para os seguintes tipos de instruções:
# Instruções DML (SELECT, INSERT com a cláusula SELECT, UPDATE e DELETE)
# Instruções CREATE TABLE (somente com a cláusula AS SELECT)
# Instruções MERGE (operações de “insert” ou “update”)
SQL Tuning Advisor pode ser executado no escopo limitado ou em um escopo global. No escopo limitado que se faz todas as análises, exceto para “SQL profiling”, em um escopo global se faz toda a análise incluindo SQL Profiles, por isso, caso tenha tempo suficiente para a análise, devemos escolherão escopo global. Quando o SQL Tuning Advisor é executado na tarefa automática na janela de manutenção, o seu foco principal é para as instruções SQL de alta carga, baseado em 4 períodos diferentes: – Na semana passada, em qualquer dia da semana passada, qualquer hora do semana passada, ou em um único tempo de resposta. Ele verifica as instruções de alta carga com base tanto no tempo de CPU como no de I/O. Ambos os tempos de CPU e I/O devem que ser melhores, mesmo que apenas um deles apresente melhor resultado, o Oracle ignora o plano para o SQL Profile. Ao verificar se deve ou não criar um “sql profile” para a instrução, ele e verifica sea instrução pode ser melhorada 3 vezes mais do que a execução original, por isso se a instrução ficar três vezes melhor do que a original, olhando juntos para os benefícios de CPU e I/O, ele recomenda a aceitação do sql profile. Vamos dar um exemplo := se uma instrução é executada melhor quando utilizada paralelismo, acaba levando menos tempo de I/O, mas o consumo de CPU aumenta, neste caso, será rejeitado porque o tempo de CPU é pior e do que o plano original. A tarefa automática tem os seguintes parâmetros que podem ser configurados:
SQL>BEGIN
2 DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
3'SYS_AUTO_SQL_TUNING_TASK',
4'LOCAL_TIME_LIMIT',
51200
6);
7
8DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
9'SYS_AUTO_SQL_TUNING_TASK',
10'ACCEPT_SQL_PROFILES',
11'true'
12);
13
14DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
15'SYS_AUTO_SQL_TUNING_TASK',
16'MAX_SQL_PROFILES_PER_EXEC',
1750
18 );
19
20DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
21'SYS_AUTO_SQL_TUNING_TASK',
22'MAX_AUTO_SQL_PROFILES',
2310002
24);
25
26 END;
27 /O LOCAL_TIME_LIMIT significa o tempo máximo dedicado para uma única instrução (1,200 segundos) 20 minutos é o padrão. O ACCEPT_SQL_PROFILES significa aceitar o “sql profile” automaticamente ou não, por padrão é FALSE. O MAX_SQL_PROFILES_PER_EXEC significa limite de aceitação máxima do sql profile para uma única tarefa. 20 é o valor padrão. O MAX_AUTO_SQL_PROFILES significa quantos profiles podem ser aceitos em geral no banco de dados oracle em qualquer ponto no tempo. 10.000 é o valor padrão. Para usar as APIs, o usuário precisa de pelo menos o privilégio ADVISOR. Para visualizar o relatório do “Automatic SQL Tuning Report” para as tarefas noturnas, você precisa do privilégio ADVISOR e também o privilégio SELECT sobre a view DBA_ADVISOR ## Executando o SQL Tuning Advisor para uma única instrução SQL:
SQL> selectsql_idfromv$sql
2 where sql_textlike 'select * from t1%';
SQL_ID
-------------
27uhu2q2xuu7rVamos criar uma tuning task com o SQL Tuning Advisor com escopo abrangente (comprehensive) que significa que o Oracle também irá analisar se a instrução pode se beneficiar do SQL Profile. Se tivesse sido utilizado o escopo normal ele teria analisado apenas as estatísticas, caminho de acesso e estrutura do SQL e não o SQL Profile.
Vamos ver o plano de execução de uma instrução SELECT com um “full table scan”.
SQL>select * from T1;
ExecutionPlan
--------------------------------------------------------------------------
Planhashvalue: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1159K| 107M| 4471 (1)| 00:00:54 |
| 1 | TABLE ACCESS FULL | T1 | 1159K| 107M| 4471 (1)| 00:00:54 |
--------------------------------------------------------------------------
SQL> set serveroutputon
SQL> declare
2 a varchar2(200);
3 begin
4 a := dbms_sqltune.create_tuning_Task(
5 sql_id=>'27uhu2q2xuu7r',
6 scope=>'comprehensive',
7 task_name=>'task3',
8 time_limit=>60);
9
10 dbms_output.put_line('taskname := '||a);
11 end;
12 /
taskname := task3
PL/SQL procedure successfullycompleted.Para ver exatamente o que foi feito em background habilitamos o “tracing”:
SQL> ALTER SESSION SET EVENTS '10046 trace namecontextforever, level 8';
Sessionaltered.
SQL> EXEC dbms_sqltune.execute_tuning_task('task3');
PL/SQL procedure successfullycompleted.
SQL> SELECT SOFAR, TOTALWORK
2 FROM V$ADVISOR_PROGRESS WHERE USER_NAME = 'HR'
3 AND TASK_NAME = 'TASK3';
SQL> SET linesize 180
SQL> SET longchunksize 180
SQL> SET pagesize 900
SQL> SET long1000000
SQL> SELECT dbms_sqltune.report_tuning_task('task3') FROM dual;
## Aceitando o SQL Profile
SQL>EXEC dbms_sqltune.accept_sql_profile(:task_name);## Executando o SQL Tuning Advisor com o SQL Tuning Sets
## Os privilégios de Advisor e Administrador do SQL Tuning são privilégioscríticos para dar ao usuário a permissão da execução do abaixo:CopyCopied to ClipboardError: Could not Copy
SQL> set serveroutputon
SQL> declare
2 a varchar2(200);
3 begin
4 a := dbms_sqltune.create_tuning_task(
5 sqlset_name=>'STS1',
6 scope=>'comprehensive',
7 task_name=>'task6',
8 time_limit=>60);
8 dbms_output.put_line('taskname := '||a);
9 end;
10 /
taskname := task6
PL/SQL procedure successfullycompleted.Afim de ver exatamente o que foi executado em background, nos podemos habilitar o tracing:
SQL>ALTER SESSION SET EVENTS '10046 trace namecontextforever, level 8';
Sessionaltered.
SQL>EXEC dbms_sqltune.execute_tuning_task('task6');
PL/SQL procedure successfullycompleted.
SQL>select OPNAME,ADVISOR_NAME,SOFAR,TOTALWORK from V$ADVISOR_PROGRESS ;
OPNAME ADVISOR_NAME SOFAR TOTALWORK
---------- -------------------- ---------- ----------
Advisor SQL Tuning Advisor 16 16
1 rows selected.
SQL> SET linesize 180
SQL> SET longchunksize 180
SQL> SET pagesize 900
SQL> SET long 1000000
SQL> SELECT dbms_sqltune.report_tuning_task('task6') FROM dual;## Vejamos algumas recomendações feitas pelo Oracle para algumas sessões
RESULTADOS DA SESSÕES (3 resultados)
—————————————————————————–
1- StatisticsFinding
—————————————————————————-
Estatísticas do otimizador para a tabela “SYS”.”CLU$” e seus indexes estão em status stale.
Recomendação
—————————–
– Considere a coleta de estatísticas para essa tabela.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'CLU$', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');Análise Racional
——————————
O otimizador requer que as estatísticas estejam atualizada para a tabela, afim de selecionar um melhor plano de execução.
2- SQL Profile Descoberta (veja a sessão explainplans abaixa)
—————————————————————-
Um plano de execução potencialmente melhor foi encontrada para esta instrução.
Recomendação (benefício estimado: 19%)
—————————————————————-
– Considere aceitar o recomendado SQL Profile
execute dbms_sqltune.accept_sql_profile(
task_name => 'task6',
object_id => 10,
task_owner => 'SYS',
replace => TRUE);Resultados da Validação
——————————————————————
O SSL Profile foi testado executando o seu plano, como também o plano original e medindo suas respectivas estatísticas de execução. Um plano pode ter sido apenas executada parcialmente, caso o outro fosse executado em menos tempo para concluir.
Plano Original Com SQL Profile % Melhora
------------- --------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .0462 .049297 -6.7 %
CPU Time (s): .044793 .043793 2.23 %
User I/O Time (s): .003361 .001033 69.26 %
Buffer Gets: 19189 15542 19 %
PhysicalReadRequests: 229 229 0 %
Physical Write Requests: 0 0
PhysicalRead Bytes: 1875968 1875968 0
Physical Write Bytes: 0 0
RowsProcessed: 3699 3699
Fetches: 3699 3699
Executions: 1 1
Notas
------------ Estatísticas para o plano original foi em média mais de 10 execuções.
- Estatísticas para o plano do SQL profile foi em média mais de 10 execuções.
- Descoberta de um Plano Alternativo
Alguns planos de execução alternativos para esta instrução foram encontrados através de pesquisaem tempo real do sistema e os dados históricos de desempenho.
A tabela a seguir lista esses planos classificados por seu tempo médio decorrido.Consulte a seção “PLANOS ALTERNATIVOS” para obter informações detalhadas sobre cadaplano.
id plan hash visto pela última vezdecorrido (s) originnote
-- --------------- ---------------------- --------------- -----------
1 3607810482 2014-08-31/01:23:10 0.450 STS
InformaçõesPorque nenhum histórico de execução para o plano original foi encontrado, o SQL Tuning Advisor não pôde determinar se algum desses planos de execução sãosuperior a esse. No entanto, se você sabe que um plano alternativo é melhorque o plano original, você pode criar SQL planbaseline para isso. Eleinstruirá o otimizador do Oracle para buscá-lo sobre quaisquer outras condições no futuro.
execute dbms_sqltune.create_sql_plan_baseline(
task_name => 'task6',
object_id => 10,
owner_name => 'SYS',
plan_hash_value =>xxxxxxxx);INFORMAÇÕES ADICIONAIS
– O otimizador não pode realizar o merge na view na linha ID 1do plano de execução.
O otimizador não pode realizar merge de view que contém um conjunto de operador.
## Com as recomendações acima, fica claro que as estatísticas da tabela CLU$estão obsoletas e também um SQL Profile foi recomendado para aceitar um melhor plano a ser aplicado no SQL PLAN Baseline. O que é o SQL Profile é explicado nessewhitepaper, enquanto que o SQL PLAN Baseline é discutido em outro whitepaper de minha autoria que pode ser encontrado no meu blog.
Para visualizar o Relatório do Automatic SQL Tuning Reportpara as tarefasnoturnas, você precisa do privilégio ADVISOR e também do privilégios SELECT sobre a view DBA_ADVISOR.
Vamos executar a função REPORT_AUTO_TUNING_TASK no pacote DBMS_SQLTUNE, o seguinte código irá exibir um relatório com todas as instruções SQL que foram analisadas em execução recentemente. Todas as recomendações serão mostrados, incluindo várias seções detalhadas, como por exemplo Seção de Informações Gerais que exibe informações gerais como o tempo do execução da tarefa e também a seção da análise de estatísticas e assim por diante ..
SQL> variablemy_rept CLOB;
SQL> BEGIN
2 :my_rept :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
3 begin_exec => NULL,
4 end_exec => NULL,
5 type => 'TEXT',
6 level => 'TYPICAL',
7 section => 'ALL',
8 object_id => NULL,
9 result_limit => NULL);
10 END;
11 /
print :my_reptPara habilitar o automatedtask, execute o seguinte bloco PL/SQL:
SQL> BEGIN
2 DBMS_AUTO_TASK_ADMIN.ENABLE (
3 client_name => 'sql tuning advisor'
4 , operation => NULL
5 , window_name => NULL
6 );
7 END;
8 /Para desabilitar o automatedtask, execute o seguinte bloco PL/SQL:
SQL> BEGIN
2 DBMS_AUTO_TASK_ADMIN.DISABLE (
3 client_name => 'sql tuning advisor'
4 , operation => NULL
5 , window_name => NULL
6 );
7 END;
8 /Consulta para confirmar no dicionário de dados a mudança: Por exemplo, consulte a view DBA_AUTOTASK_CLIENTE, da seguinte forma:
SQL>COL CLIENT_NAME FORMAT a20
SQL>SELECT CLIENT_NAME, STATUS
2 FROM DBA_AUTOTASK_CLIENT
3 WHERE CLIENT_NAME = 'sql tuning advisor';
CLIENT_NAME STATUS
-------------------- --------
sql tuning advisor ENABLED