Oracle Database 12c: "AUTOMATIC SQL TUNING" (Part II)
Revisado por Marcelo Pivovar - Solution Architect
Oi leitores, bem-vindos mais uma vez a mais um de nossos artigos. Este artigo é precedido por Oracle Database 12c: "AUTOMATIC SQL TUNING" (Part I), necessário para assegurar o conhecimento básico para entender essa parte.
Então .. Vamos começar se você leu a primeira parte ..
Vamos 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:
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ções
Porque 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_rept
Para 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
Então .. temos a conclusão deste artigo. Esperamos que ele tenha sido útil para seguir com o crescimento do seu conhecimento sobre as tecnologias Oracle.
Nos vemos no próximo artigo ..
Abraços!
Joel é um DBA Especialista (Oracle ACE Director, OCM Cloud Admin. & OCM11g ). Com mais de 14 anos de experiência do mundo Oracle Technology, especializado em arquitetura e implementação de soluções como: Cloud, Alta disponibilidade, Disaster/Recovery, Upgrades, replicação e todos as áreas relacionadas com bancos de dados Oracle. Consultor internacional com deveres, conferências e atividades em mais de 50 países e inúmeros clientes em todo o mundo. Palestrante regular nos eventos Oracle em todo o mundo como: OTN LAD, OTN MENA, OTN APAC e muito mais. Joel sempre foi conhecido por ser pioneiro em tecnologia Oracle desde os primeiros dias de sua carreira sendo o primeiro latino-americano premiado como "OTN Expert" no ano de 2003 pela Oracle Corporation, um dos primeiros "ACE Oracle" no Oracle ACE Program no ano de 2004, um dos primeiros OCP Database Cloud Administrator em todo o mundo no ano de 2013 e como um das maiores realizações profissionais em sua carreira, recentemente ele foi homenageado como o primeiro "OCM Database Cloud Administrator" do mundo.
Karan Dodwal (OCM) é um Oracle arquiteto com especialização em Oracle High Availability. Ele é um DBA Oracle Certified Master (OCM) com vários anos de experiência em banco de dados Oracle e no desenvolvimento Oracle. Ele trabalha como consultor Oracle e já realizou diversos serviços e treinamentos sobre os produtos da Oracle na Ásia Pacífico, Ásia do Sul e na Grande China. Ele é um speaker do All India Oracle Users Group (North India Chapter) e apresenta sessões no Oracle Technology. Ele tem várias configuração feitas do Oracle High Availability em todas as plataformas para missões críticas do Oracle Database. Ele é um expert em todas as soluções de High Availability da Oracle como RAC, Exadata, Data Guard e outros. Ele freqüentemente publica artigos em diversos sites e no seu bloghttp://karandba.blogspot.in e participa ativamente de eventos do grupo de usuários Oracle AIOUG AllIndia Oracle UsersGroup (North IndiaChapter) e ajuda diversos usuário no OTN Fórum da Oracle.
Flávio Soares é um Oracle DBA Sênior, Exadata DMA, Troubleshooter e Consultor Oracle, certificado em OCP/OCE RAC. Especialista em Exadata, alta disponibilidade e replicação de dados com soluções Oracle. Flávio disponibiliza frequentes informações para a comunidade Oracle através do seu blog.
Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.