Aula 8 - Tuning no Oracle

Prévia do material em texto

/
Administração de Banco de Dados III
Aula 8: Tuning no Oracle
Apresentação
Esta oitava aula da disciplina Administração de Bancos de Dados III trata da sintonia �na em bancos de dados Oracle,
incluindo as técnicas e ferramentas para análise de planos de execução, atualização de estatísticas e identi�cação das
estruturas de acesso que possuem potencial de ganho de desempenho para as consultas SQL.
Ao �nal desta aula você será capaz de utilizar as ferramentas do Oracle para melhorar o desempenho das consultas
executadas no banco de dados, utilizando índices, visões materializadas e particionamento. Será capaz de compreender
os operadores dos planos de execução e, a partir de suas observações, aplicar as melhores práticas de tuning das cargas
de trabalho submetidas às instâncias Oracle.
Objetivo
Examinar os planos de execução gerados pelo otimizador do Oracle e como os operadores do plano trabalham de
forma integrada para resolver as consultas;
Usar índices e visões materializadas para aumentar o desempenho de consultas submetidas ao banco de dados
Oracle;
Aplicar técnicas de particionamento para distribuir dados em partições e, com isso, melhorar o desempenho da
carga de trabalho submetida ao Oracle.
Introdução
Garantir que as consultas submetidas sejam executadas no menor tempo possível, de acordo com os recursos disponíveis é
um grande desa�o para os administradores de banco de dados. A monitoração dos processos, sessões, transações e
bloqueios frequentemente sinalizam problemas de desempenho que podem ser resolvidos por técnicas de tuning. 
/
Para apoiar os DBAs, existem três estruturas de acesso largamente
utilizadas que podem ser aplicadas aos diferentes cenários encontrados.
Índices, visões materializadas e particionamento horizontal são as técnicas
mais utilizadas no endereçamento desses problemas.
Antes de experimentar as técnicas, porém, é preciso compreender o conceito de seletividade. É ele quem vai nortear a análise
para o entendimento de qual técnica é a mais indicada. Em muitos cenários, uma combinação das técnicas será o mais
indicado.
Vamos trabalhar esse conceito e as técnicas disponíveis durante a aula. Ao observar tempos e custos associados aos planos
de execução das consultas, podemos confrontar as técnicas com os resultados e concluir qual delas é a mais indicada para os
casos observados.
Planos de execução
Um plano de execução apresenta o caminho de�nido pelo otimizador do Oracle para resolver uma determinada consulta.
Quando exibimos um plano de consulta, visualizamos os operadores que compõem essa solução. As decisões tomadas pelo
otimizador são baseadas nos recursos e nas informações disponíveis no banco de dados. 
Nesta seção vamos realizar experimentos para ilustrar as decisões mais frequentes do otimizador. Considerando que a tabela
Usuarios contém três linhas, executamos um loop para inserir linhas na tabela e criar uma massa de dados que possa ser
utilizada nesses experimentos.
 Loop de inserção de dados. Fonte: O autor.
Ao utilizar o comando EXPLAIN PLAN ou o botão correspondente ao plano de explicação no SQL Developer (ao lado dos dois
botões de execução), você pode acessar o plano construído pelo otimizador para a consulta. Vamos começar por um SELECT
na tabela Usuarios e um �ltro na coluna nome.
/
 Plano de execução. Fonte: Fonte: O autor.
Saiba mais
A primeira informação relevante para o administrador é o operador TABLE ACCESS e os dados a ele associados. O operador
recorre à uma varredura completa (FULL) da tabela Usuarios para buscar as linhas que satisfazem à condição imposta. Podemos
ver também que a cardinalidade estimada no plano é de quatro linhas.
Por que o plano apresenta quatro linhas, se temos na verdade uma linha que satisfaz à condição?
Por causa das estatísticas. Parte das informações utilizadas pelo otimizador para decidir quais operadores utilizar estão
depositadas nas estatísticas do banco de dados. Regularmente, essas estatísticas são atualizadas, mas isso não aconteceu
logo após a execução do loop que inseriu linhas na tabela Usuarios e Usuarios_Historico. Quando rodamos a query, o
otimizador utilizou a informação disponível naquele momento, que era a de que a tabela Usuarios continha quatro linhas que
satisfaziam à condição da query.
Estatísticas
Garantir que as estatísticas do banco de dados estejam atualizadas é a principal tarefa de um DBA, no que concerne às
atividades de tuning do banco de dados. No exemplo que vimos anteriormente as estatísticas da tabela Usuarios estão
desatualizadas. Para atualizá-las, utilizamos procedimentos disponíveis no pacote DBMS_STATS, como mostrado na imagem a
seguir.
 Atualização de estatísticas. Fonte: O autor.
/
No comando executado especi�camos o proprietário, o nome do objeto e a opção cascade. Ao incluir cascade=true, queremos
que o Oracle atualize, além das estatísticas da tabela, as estatísticas dos índices da tabela.
Observe que o pacote possui procedimentos distintos para, conforme mostrado na imagem, atualizar as estatísticas de todo o
banco de dados, todos os objetos do esquema, um objeto especí�co (o que foi executado) e para remover do banco de dados
as estatísticas associadas a uma tabela.
Atenção
É muito importante de�nir uma estratégia para atualização das estatísticas. Em muitos cenários, principalmente em bancos de
dados muito grandes (very large databases), não é viável executar uma atualização completa em todos os objetos de todos os
esquemas, porque a janela de manutenção pode não acomodar a execução da atualização.
A solução da Oracle para endereçar esse problema é utilizar um parâmetro que de�ne qual o percentual de alterações que
devem ser realizadas em uma tabela para disparar a atualização das estatísticas. Esse parâmetro é denominado stale.
Supondo que o parâmetro esteja con�gurado para 10%, as estatísticas da tabela serão atualizadas somente quando mais de
10% do total de linhas da tabela for alterado. 
Vamos ver o que acontece com o plano de execução depois que as estatísticas são atualizadas. Observe, na imagem a seguir,
que a informação de cardinalidade agora está correta.
 Cardinalidade atualizada. Fonte: O autor.
Enquanto o administrador garante que as estatísticas são atualizadas regularmente, de acordo com as restrições existentes no
ambiente, o próximo passo é disponibilizar as estruturas de acesso mais adequadas. 
Observe que, no plano de execução mostrado anteriormente, o operador escolhido foi um TABLE ACCESS FULL. O otimizador
fez isso porque não havia outra opção.
Qual estratégia podemos considerar nesses casos, em que queremos selecionar um pequeno subconjunto de linhas da
tabela?
Precisamos percorrer à tabela inteira para selecionar essas linhas?
Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online
Í
/
Índices
Índices são estruturas de acesso que apoiam as escolhas do otimizador na construção dos planos de execução das consultas.
Quando o otimizador veri�ca, nas estatísticas, que o �ltro da consulta aponta para um percentual pequeno das linhas da tabela,
ele procura por um índice que possa ser utilizado no plano de execução. Dizemos, nesse caso, que o predicado da consulta tem
alta seletividade, isto é, ele faz com que a consulta retorne um pequeno subconjunto do conjunto total de linhas.
 Criação do índice. Fonte: O autor.
Após o CREATE INDEX na coluna nome da tabela Usuarios, o plano de execução da consulta mostra o uso de um operador
INDEX com RANGE SCAN, indicando que uma faixa do índice será percorrida. Para cada linha do índice que satisfaz a condição,
o rowid é utilizado para localizar a linha correspondente na tabela principal.
 Plano de execução após create index. Fonte: O autor.
Comentário
Um parâmetro de referência importante para o administrador é o custo da operação. Se você voltar ao plano que utilizou um
TABLE ACCESS FULL, verá que o custo associado é 104. Nesse último plano, com INDEX RANGE SCAN, temos um custo muito
menor.
No próximo exemplo vamos ver como se comporta ojoin entre as tabelas Usuarios e Usuarios_Historico. O plano a seguir é
resultado do SELECT com �ltro na coluna nome.
/
 Plano de execução de um Join. Fonte: O autor.
Observe que o join é implementado por um operador NESTED LOOPS. É ele quem faz a junção das linhas das tabelas Usuarios
e Usuarios_Historico. Para a tabela Usuarios, é utilizado o índice na coluna nome e, para a tabela Usuarios_Historico, é realizado
um TABLE ACCESS FULL.  
Vamos trabalhar um pouco mais a tabela Usuarios_Historico. Para de�nir uma abordagem de sintonia �na para seus bancos de
dados, é importante que você se aproxime dos desenvolvedores para entender os tipos de uso mais frequentes das tabelas do
banco de dados. No caso da tabela Usuarios_Historico, vamos considerar que as consultas mais frequentes utilizarão a coluna
data e faixas de datas como �ltro nas consultas. 
Sabendo que esse tipo de consulta é a mais frequente, vamos preparar a base dados para receber consultas com �ltros na
coluna data. A construção adiante passeia pelas linhas da tabela e altera as datas utilizando o próprio contador para gerar
novas datas.
 Datas. Fonte: O autor.
/
Temos agora uma faixa de datas que vai de 27/12/2019 a
29/09/2020. Vamos ver como se comporta uma consulta
que faz join nas duas tabelas, com �ltro na coluna nome e
na coluna data.
 Plano de execução de um Join e filtro. Fonte: O autor.
Observe que foi utilizado um TABLE ACCESS FULL tanto para Usuarios_Historico quanto para Usuarios, e um HASH JOIN para
unir as linhas com base nas suas chaves primárias. Para Usuarios_Historico, de fato não há um índice que possa ser utilizado.
Vamos criá-lo.
 Índice em Usuarios_historico. Fonte: O autor.
Foi criado o índice na coluna data da tabela Usuarios_Historico e o otimizador selecionou o índice para compor o plano de
execução da consulta. Note que o custo dessa parte da consulta diminuiu em mais de 60% com a utilização do índice.
Vamos praticar?
Utilize o script de apoio da aula para reproduzir os experimentos que �zemos. Pode haver alguma diferença nas estimativas e
nos custos, mas você vai poder con�rmar as observações que �zemos sobre as estatísticas e sobre os recursos. Quando
chegar nesse último SELECT, responda à pergunta a seguir:
Por que o otimizador não selecionou o índice idx_CRM_Usuario_nome nesse último SELECT?
Dica
Seletividade do predicado.
/
Visões materializadas
Uma técnica tradicional na sintonia �na em bancos de dados relacionais é a construção de visões materializadas. Com a
persistência dos dados, o otimizador pode utilizar diretamente a visão para buscar o resultado da consulta. 
Na imagem a seguir podemos ver a criação da visão materializada e o plano de execução gerado para o SELECT na visão. Foi
utilizado um operador do tipo MAT_VIEW ACCESS FULL, bem semelhante ao TABLE ACCESS FULL, para varredura em todas as
linhas da visão materializada.
 Criação de visão materializada. Fonte: O autor.
Comentário
Note que o custo nesse plano é bem menor do que o custo do plano gerado com uso de índices. Naturalmente, esse custo tende
a ser menor na medida em que estamos utilizando uma única estrutura para satisfazer à consulta. A contrapartida é a
manutenção da visão materializada.
No Oracle, o refresh da visão pode ser executado automaticamente ou sob demanda a partir de um job agendado, ou
manualmente. A opção ON COMMIT dispara a atualização da visão toda vez que um commit que envolva uma de suas tabelas
for executado. Nesse cenário, a visão será atualizada como parte do commit, garantindo que será atualizada automaticamente.
A contrapartida é o aumento do tempo de execução do commit.
No exemplo anterior construímos a visão persistindo o valor das colunas nome e data. Uma abordagem alternativa é criar a
visão persistindo somente uma das colunas, como a coluna nome.
 Criação de visão materializada. Fonte: O autor.
/
Veja que, ao fazer isso, o SELECT na visão materializada precisa incluir o �ltro da coluna data. O plano de execução re�ete essa
construção incluindo um �ltro para o operador MAT_VIEW ACCESS FULL.
Particionamento
Uma técnica especialmente importante para administradores e desenvolvedores de bancos de dados é o particionamento. O
Oracle implementa os principais tipos de particionamento, a saber, RANGE, LIST e HASH, além de permitir a composição dos
tipos para construção de subparticionamentos. Vamos tomar o nosso exemplo para mostrar o quão e�ciente pode ser essa
técnica.
Como vimos após a distribuição de datas, temos uma faixa que vai de 27/12/2019 a 29/09/2020. A ideia é construir partições
da tabela para abrigar subconjuntos de dados por faixa de datas, por exemplo. Na imagem a seguir você pode ver o ALTER
TABLE na tabela Usuarios_Historico e a especi�cação das partições.
Saiba mais
Para o particionamento, é de�nido o tipo RANGE na coluna data, indicando que subconjuntos de valores dessa coluna vão
determinar as partições da tabela.
Para cada partição, é designado o valor máximo da coluna data. Sendo assim, todas as linhas da tabela cujo valor na coluna
data for menor do que ‘01/01/2020’ serão depositadas na partição P202001. Todas as linhas entre 01/01/2020 e 01/02/2020
serão depositadas na partição P202002. Todas as linhas entre 01/02/2020 e 01/03/2020 serão depositadas na partição
P202003, e assim por adiante.  
Com esse arranjo, podemos distribuir as linhas da tabela pelas partições, isolando os subconjuntos de dados em estruturas
próprias.
 Particionamento. Fonte: O autor.
/
A cláusula UPDATE INDEXES pode ser utilizada para, ao criar as partições, atualizar os índices discriminados segundo sua
concepção. Um índice LOCAL é aquele que acompanha o particionamento, isto é, suas linhas apontam para linhas dentro da
mesma partição, diferentemente do índice GLOBAL, cujas linhas podem apontar para diferentes partições. 
Após a execução da modi�cação imposta à tabela Usuarios_Historico, vamos examinar o plano da execução da consulta
anterior.
 SELECT usando Particionamento. Fonte: O autor.
Comentário
Podemos ver claramente o uso do operador PARTITION RANGE para percorrer somente as partições da tabela que contêm os
dados necessários. Essa abordagem derruba os custos e os tempos de execução das consultas, porque varre somente os
subconjuntos de dados de interesse da consulta.
Na consulta anterior a varredura da tabela Usuarios continua sendo feita por um operador TABLE ACCESS FULL. Por quê? Olhe
o predicado da consulta. Ele tem seletividade alta o su�ciente para que o otimizador selecione o índice correspondente? Veja a
próxima consulta.
 SELECT usando Particionamento. Fonte: O autor.
Veja que, nesse exemplo, o índice na coluna nome da tabela Usuarios foi utilizado porque o predicado tem seletividade muito
alta, 1/99.999, diferentemente do predicado anterior que possui seletividade muito mais baixa.
/
Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online
Operadores de join
Durante os experimentos que �zemos até aqui com índices e particionamento, observamos as escolhas dos operadores
NESTED LOOPS e HASH JOIN. Por que o otimizador escolheu esses operadores? Além desses dois, temos outros operadores de
join no Oracle, como o MERGE JOIN. Quais informações o otimizador leva em consideração para escolher um ou outro?
Vamos observar os dois últimos planos de execução mostrados anteriormente.
No primeiro, quando o otimizador
encontrou um predicado de seletividade
baixa e utilizou o operador TABLE ACCESS
FULL na tabela Usuarios, o join entre a
tabela Usuarios e Usuarios_Historico foi
operado por um HASH JOIN.

No segundo, quando o otimizador
encontrou um predicado de seletividade
alta e utilizou o operador RANGE SCAN no
índice da tabela Usuarios, o join entre a
tabela Usuarios e Usuarios_Historico foi
operado por um NESTED LOOPS.
Quando o otimizador do Oracle precisa fazer uma varredura completa em uma tabela (ou visão materializada), sua estratégia é
construir uma tabela hash em memória com o valor hash da chave. Com essa tabela compara osvalores hash da chave da
segunda tabela para encontrar as correspondências corretas.  
Por outro lado, quando o otimizador do Oracle utiliza um índice para a varredura da tabela, sua estratégia criar um loop
aninhado para encontrar as correspondências entre os valores chave.
O Hash Join tende a ser muito rápido por conta do uso dos valores hash em memória. O Nested Loops também pode ser muito
rápido por conta do uso de índices. Algumas consultas executam mais rápido com Hash Join e outras com Nested Loops. A
escolha de um ou outro depende fundamentalmente das escolhas que o otimizador faz para a varredura dos dados.
Dica
Naturalmente, você pode testar o uso desses operadores e medir os tempos com o uso de cada um deles. Muito frequentemente
o otimizador acerta na escolha, mas você sempre pode interferir e escolher o seu próprio método. Veja em
https://docs.oracle.com/cd/B13789_01/server.101/b10752/hintsref.htm como utilizar as optimizer hints.
Conclusão
É sempre importante pensar na seletividade. Quando ela é alta o su�ciente, o otimizador escolhe um índice, se houver. Garanta
que existam somente os índices que podem ser utilizados na maior parte das consultas. Para os predicados de baixa
seletividade existe o particionamento, que isola a consulta nas partições de interesse.
javascript:void(0);
/
As técnicas disponíveis no Oracle endereçam os tipos mais frequentes de cenários encontrados, inclusive em VLDB (very large
databases). Em bancos de dados transacionais, com volume pequeno de dados, é mais frequente a utilização de índices. O
particionamento começa a ser útil com volumes maiores de dados e seletividades menores, por isso são frequentemente
encontrados em Data Warehouses.
Já as visões materializadas são muito poderosas na medida em que podem concentrar os dados de vários joins em uma
persistência que deve sempre ser orientada a um propósito muito bem especi�cado, porque o seu custo de manutenção pode
ser bem alto.
Atenção! Aqui existe uma videoaula, acesso pelo conteúdo online
Atividade
1. Assinale a alternativa incorreta sobre planos de execução no Oracle.
a) Planos de execução mostram como o otimizador decidiu resolver uma determinada consulta.
b) Estatísticas atualizadas do banco de dados são fundamentais para o otimizador.
c) O otimizador verifica quais estruturas de acesso, como índices e visões materializadas, estão disponíveis.
d) Quanto maior o custo apresentado no plano de execução, maior o tempo que pode levar a consulta para executar.
e) O custo apresentado é diretamente proporcional ao tempo de execução da consulta.
2. Quando predicados de uma consulta tem seletividade alta, é provável que o otimizador tente escolher um(a) ____ apropriado
para resolver a consulta. Quando a seletividade é baixa, é mais provável que o plano de execução contenha um operador do tipo
____. 
a) índice – INDEX RANGE SCAN.
b) visão materializada – TABLE ACCESS FULL.
c) partição – INDEX RANGE SCAN.
d) índice-TABLE ACCESS FULL.
e) partição – INDEX RANGE SCAN.
3. Em um banco de dados transacional (não analítico), uma determinada tabela contém registros de interações com clientes, com
colunas de data da interação e descrição. Há muitas interações na mesma data e a tabela contém centenas de gigabytes de
informação. 
Uma consulta frequente na aplicação é a que recupera todas as interações em um período que pode ser diário, semanal,
quinzenal ou mensal. Qual a melhor estratégia no Oracle para maximizar o desempenho dessas consultas? 
a) Construir um índice na coluna data e particioná-lo em um tipo range.
b) Construir visões materializadas para todos os períodos possíveis.
c) Particionar a tabela por data, em um tipo list, com índices locais na coluna data.
d) Particionar a tabela por data, em uma composição do tipo hash, com índices globais na coluna data.
e) Particionar a tabela por data, em uma composição do tipo range, com índices locais na coluna data.
Referências
DBMS_STATS. Oracle Help Center. Disponível em: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm.
Acesso em: 30 set. 2020.
INDEXES and Index-Organized Tables. Oracle Help Center. Disponível em:
https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721. Acesso em: 30 set. 2020.
MATERIALIZED Views in Oracle. Oracle-Base. Disponível em: https://oracle-base.com/articles/misc/materialized-views. Acesso
em: 30 set. 2020.
javascript:void(0);
javascript:void(0);
javascript:void(0);
/
NotasMODIFICANDO Materialized Views. Oracle. Disponível em: https://www.oracle.com/br/technical-resources/articles/database-
performance/modifying-materialized-views.html. Acesso em: 28 set. 2020.
ORACLE Partitioning. Oracle. Disponível em: https://www.oracle.com/database/technologies/partitioning.html. Acesso em: 25
set. 2020.
PARTITIONED Tables And Indexes. Oracle-Base. Disponível em: https://oracle-base.com/articles/8i/partitioned-tables-and-
indexes. Acesso em: 28 set. 2020.
PUGA, S.; FRANÇA, E.; GOYA, M., 2013. Banco de Dados: implementação em SQL, PL/SQL e Oracle 11g. São Paulo: Pearson.
332p.
RAMAKHRISHNAN, R.; GEHRKE, J., 2002. Database Management Systems. 3rd. edition. New York: McGraw-Hill. 1098p.
RANGE Partitioning. Docs Oracle. Disponível em: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/partitioning-range.html.
Acesso em: 30 set. 2020.
TYPES of Indexes for Materialized Views. Oracle Help Center. Disponível em:
https://docs.oracle.com/cd/E29633_01/CDMOG/GUID-8D4FCC4B-39FF-464C-844E-7AD7E87190D3.htm. Acesso em: 28 set.
2020.
USING Explain Plan. Docs Oracle. Disponível em: https://docs.oracle.com/cd/B10501_01/server.920/a96533/ex_plan.htm.
Acesso em: 30 set. 2020.
DBMS_STATS. Oracle Help Center. Disponível em: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_stats.htm.
Acesso em: 30 set. 2020.
INDEXES and Index-Organized Tables. Oracle Help Center. Disponível em:
https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT721. Acesso em: 30 set. 2020.
MATERIALIZED Views in Oracle. Oracle-Base. Disponível em: https://oracle-base.com/articles/misc/materialized-views. Acesso
em: 30 set. 2020.
MODIFICANDO Materialized Views. Oracle. Disponível em: https://www.oracle.com/br/technical-resources/articles/database-
performance/modifying-materialized-views.html. Acesso em: 28 set. 2020.
ORACLE Partitioning. Oracle. Disponível em: https://www.oracle.com/database/technologies/partitioning.html. Acesso em: 25
set. 2020.
PARTITIONED Tables And Indexes. Oracle-Base. Disponível em: https://oracle-base.com/articles/8i/partitioned-tables-and-
indexes. Acesso em: 28 set. 2020.
PUGA, S.; FRANÇA, E.; GOYA, M., 2013. Banco de Dados: implementação em SQL, PL/SQL e Oracle 11g. São Paulo: Pearson.
332p.
Próxima aula
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
/
Espelhamento;
Replicação;
Clusters RAC.
Explore mais
Índices, Visões materializadas e Particionamento são as três principais técnicas de tuning em bancos de dados Oracle. Explore
a trilha Oracle Partitioning disponível em Ask Tom no endereço a seguir: https://asktom.oracle.com/partitioning-for-
developers.htm
Não pare no meio! Siga em frente e veja as subpartições. Essa técnica oferece um grande potencial de aumento de
desempenho para seus bancos de dados.
javascript:void(0);