Oracle Database 12c: "AUTOMATIC SQL TUNING" (Part I)
Por Joel Perez 
, Karan Dodwal (OCM) & Flávio Soares (OCE),
Postado em Dezembro 2014
Revisado por Marcelo Pivovar - Solution Architect
Olá leitores, bem-vindos mais uma vez a um de nossos artigos. Este artigo será vinculado com a "Parte II" da mesma série. Nesta primeira parte, a "Parte I", vamos introduzir os conceitos básicos para entender o contexto para o “SQL Tuning” do licenciado "Oracle Tuning Pack" e, na segunda parte, vamos desenvolver um "tuning task" com o "sql tuning advisor", utilizando o escopo comprehensive (escopo global).
Então .. vamos começar pelo começo:
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
-------------
27uhu2q2xuu7r
Ok, vamos parar neste ponto o artigo "Parte I" ... com toda essa leitura, você acaba de adquirir os conceitos mínimos necessários para compreender casos reais de trabalho com algumas opções do "Oracle Tuning Pack". No artigo seguinte, o "Part II", iremos desenvolver um caso para que você possa perceber mais detalhes sobre todo este tema.
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.