LEITURA DIGITAL

Prévia do material em texto

GERENCIAMENTO DO 
DESEMPENHO DO BANCO DE 
DADOS (TUNING)
W
B
A
04
74
_v
1.
0
2
Odécio José Fernandes de Souza Junior
Londrina 
Editora e Distribuidora Educacional S.A. 
2020
GERENCIAMENTO DO DESEMPENHO DO 
BANCO DE DADOS (TUNING)
1ª edição
3
2020
Editora e Distribuidora Educacional S.A.
Avenida Paris, 675 – Parque Residencial João Piza
CEP: 86041-100 — Londrina — PR
e-mail: editora.educacional@kroton.com.br
Homepage: http://www.kroton.com.br/
Presidente
Rodrigo Galindo
Vice-Presidente de Pós-Graduação e Educação Continuada
Paulo de Tarso Pires de Moraes
Conselho Acadêmico
Carlos Roberto Pagani Junior
Camila Braga de Oliveira Higa
Carolina Yaly
Giani Vendramel de Oliveira
Henrique Salustiano Silva
Juliana Caramigo Gennarini
Mariana Gerardi Mello
Nirse Ruscheinsky Breternitz
Priscila Pereira Silva
Tayra Carolina Nascimento Aleixo
Coordenador
Henrique Salustiano Silva
Revisor
Sergio Eduardo Nunes
Editorial
Alessandra Cristina Fahl
Beatriz Meloni Montefusco
Gilvânia Honório dos Santos
Mariana de Campos Barroso
Paola Andressa Machado Leal
Dados Internacionais de Catalogação na Publicação (CIP)
_________________________________________________________________________________________ 
Souza Junior, Odécio José Fernandes de 
S729g Gerenciamento do desempenho do banco de dados 
 (tuning)/ Odécio José Fernandes de Souza Junior, – 
 Londrina: Editora e Distribuidora Educacional S.A., 2020.
 44 p.
 
 ISBN 978-65-5903-052-1
 1. Gerenciamento. 2. Desempenho. 3. Dados. I. Título. 
 
CDD 004 ____________________________________________________________________________________________
Raquel Torres – CRB 6/2786
© 2020 por Editora e Distribuidora Educacional S.A.
Todos os direitos reservados. Nenhuma parte desta publicação poderá ser 
reproduzida ou transmitida de qualquer modo ou por qualquer outro meio, 
eletrônico ou mecânico, incluindo fotocópia, gravação ou qualquer outro tipo de 
sistema de armazenamento e transmissão de informação, sem prévia autorização, 
por escrito, da Editora e Distribuidora Educacional S.A.
4
SUMÁRIO
A estratégia de ajuste de performance como um método ___________ 05
As consultas ao Banco de Dados ____________________________________ 22
Conhecendo e adequando o desempenho das estruturas 
em memória ________________________________________________________ 41
Memória física: Criação e Tuning ____________________________________ 58
GERENCIAMENTO DO DESEMPENHO DO BANCO DE DADOS (TUNING)
5
A estratégia de ajuste de 
performance como um método
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
Objetivos
• Conhecer um método para ajuste de performance 
de um Sistema Gerenciador de Banco de Dados 
(SGBD). 
• Determinar a significância desse método para uma 
determinada atividade empresarial.
• Entender, nesse método, os papéis e as 
responsabilidades das pessoas envolvidas.
• Relacionar o método estudado com os objetivos de 
negócio da instituição, objetivando a performance 
necessária.
• Diferenciar o que pode ser ajustado na aplicação: as 
instruções ao SGBD e os demais comandos.
• Conhecer, analisar e aprimorar as regras e os 
recursos dos sistemas de banco de dados em 
operação
6
1. Um método
Segundo o Caldas Aulete (AULETE DIGITAL, 2020a), método pode ser 
considerado como um “conjunto de meios ou procedimentos racionais 
para atingir um objetivo (método científico)”. Ao Administrador do Banco 
de Dados (Data Base Administrator – DBA) caberá colocar em operação 
um conjunto de procedimentos cujo objetivo é fazer funcionar o Sistema 
Gerenciador de Bancos de Dados (SGBD), de forma que os interesses da 
instituição sejam servidos da melhor forma possível.
O método foi indicado como “um”, porque não se pode afirmar que 
não existam outros caminhos para obter melhorias de performance 
de um SGBD. É importante entender no caso que há um objetivo a 
ser alcançado, a melhoria da performance (tuning), e que o método 
apresentado e mapeado indica um caminho seguro para alcançá-lo.
Além disso, o método prevê um certo número de pré-requisitos 
e determinados conceitos que deverão ser mantidos sempre em 
perspectiva. Adiante, veremos que, antes mesmo de iniciarmos os 
ajustes do SGBD propriamente dito, será necessário cuidar de outras 
questões, em conjunto com preceitos que deverão ser lembrados.
Assim, como qualquer outra estratégia de administração, o nível de 
qualidade a ser alcançado atenderá ao ciclo conhecido como PDCA (Plan 
– planejamento; Do – execução; Check – verificação; e Action – atuação). 
Ele prevê que, identificada uma possibilidade de melhoria em um 
produto ou processo, ela deva ser planejada e executada; em seguida, 
os resultados devem ser verificados para novamente serem identificadas 
novas possibilidades.
Vamos imaginar que um automóvel esteja sendo produzido e que os 
clientes estão respondendo a questionários de satisfação. Surge, então, 
uma queixa a respeito de ruídos, os quais são investigados, verificando-
se que eles são oriundos da porta esquerda, na qual há uma peça está 
7
mal fixada. Com isso, deve-se planejar como fixá-la melhor e instruir os 
montadores nesse sentido. A partir das mudanças, verifica-se nas novas 
pesquisas de satisfação a efetividade dos ajustes.
O sentido do termo performance que devemos ter em mente se liga à 
“medição do desempenho, auferimento da capacidade de alguma coisa” 
(AULETE DIGITAL, 2020b). Essa capacidade, em termos de um SGBD, 
deverá ser adequada às necessidades da instituição.
Para termos um termo claro e objetivo, a atividade de ajuste de 
performance será referenciada sempre como tuning.
1.1 Quais as pessoas envolvidas?
A troca de informações entre todas as pessoas envolvidas no processo 
de criação do sistema de informação computadorizado é fundamental 
para a obtenção da melhor performance. Assim, o analista de requisitos 
deve comunicar-se com o proprietário do sistema para compreender 
as estruturas dos dados necessários e seus fluxos. Por sua vez, os 
desenvolvedores das aplicações devem comunicar as estratégias 
implementadas e (de uma forma que interessa especialmente para esse 
método) como os comandos em Linguagem Estruturada de Pesquisa 
(Structured Query Language – SQL) foram codificados.
O DBA também deve monitorar e documentar a atividade do sistema 
para identificar e corrigir possíveis problemas de performance. Da 
mesma forma, os administradores dos equipamentos (hardware) e 
componentes lógicos computacionais de serviço (software básico) 
devem documentar e comunicar a configuração do sistema a todos os 
envolvidos no desenvolvimento.
É preciso que toda a equipe envolvida no desenvolvimento de um 
sistema troque informações e que seus integrantes interajam entre si, 
8
para a obtenção de uma boa performance. Essa é uma preocupação 
fundamental que se deve sempre ter em mente. No entanto, 
normalmente, essa não é a realidade. Na prática, é o DBA quem tem 
essas responsabilidades e todas as outras pessoas envolvidas pouco ou 
quase nada se preocupam com tais aspectos.
Os problemas de ajuste de performance ocorrem em dois momentos 
distintos: durante o projeto de um novo sistema de informação, ou 
de sua atualização e melhoria; e, o que ocorre com maior frequência, 
quando uma situação de crise de performance acontece. A experiência 
demonstra que ao DBA caberá organizar as ações e as informações 
relativas ao problema e aquelas a serem tomadas, aqui entendidas 
como método.
1.2 Antes de qualquer outra coisa
O ajuste de desempenho do banco de dados depende de pré-requisitos, 
que são princípios aos quais não se pode deixar de ter atenção. O 
que acontece, na maioria das instituições, é as necessidades de um 
determinado processamento somente serem alcançadas ou verificadas 
após o sistema de informação estar efetivamenteem uso. Além 
disso, o investimento em infraestrutura de um determinado sistema 
frequentemente se faz de forma isolada, nem sempre levando em conta 
questões como sazonalidades, as quais acontecem na contramão do que 
ocorre no dia a dia da instituição, surgindo em momentos específicos de 
uso.
Uma outra característica dos dados, raramente considerada, é 
a utilidade. Há dados, como o histórico de relacionamento com 
empregados, que devem ser mantidos “para sempre”, mas cujo acesso 
é raramente útil; enquanto há outros dados que simplesmente devem 
ser periodicamente eliminados, também para a questão da utilidade 
dos dados. Assim, há dados que devem ser separados daqueles que 
9
precisam ser constantemente acessados. Por um lado, essa é uma 
atividade extra, mas corresponderá a um custo cujo benefício poderá ser 
significativo ao diminuir a carga de trabalho do SGBD, quando do acesso 
a determinadas tabelas.
É possível perceber que não é possível administrar a performance 
do SGBD sem entender como está organizada a infraestrutura 
computacional da instituição e como seus negócios precisam ser 
atendidos a partir dessa infraestrutura.
Independentemente do dispositivo utilizado, seja um smartphone, um 
tablet, um laptop (notebook) ou um desktop, haverá uma memória 
real (RAM) limitada. Do mesmo modo, o computador que atuará 
como Servidor de Banco de Dados terá uma RAM limitada. Cada 
aplicação (APP) em funcionamento nesse dispositivo e cada aplicação 
aberta consomem um espaço na RAM, assim como algum tempo do 
seu conjunto de processadores (seja esse conjunto unitário ou não, 
usualmente referenciado como CPU).
A partir de uma certa quantidade de APPs executados, o dispositivo irá 
se tornando progressivamente mais lento. Desse fato, advém o primeiro 
pré-requisito, ou seja, estabelecer quanto de RAM existe disponível no 
computador que executa o SGBD. É importante lembrar que o Sistema 
Operacional, os APPs de segurança e comunicação, entre outros, 
configuram “necessidades do Sistema”.
A próxima questão se dirige à network, ou seja, ao conjunto dos 
componentes lógicos e físicos que oferecem aos computadores a 
possibilidade de comunicação. O hardware que contém o SGBD 
(DBServer) precisa se comunicar com o hardware servidor de aplicações 
(AppServer), e ambos precisam se comunicar com um ou mais discos 
de armazenamento (Storage) e com os equipamentos e APPs que 
demandam serviços, como a execução de pesadas atividades de 
processamento de dados ou simples consultas (Clients).
10
Não devem existir pontos de estrangulamento dessas comunicações. 
Para isso, é necessário verificar se elas não tomam rotas 
desnecessariamente complexas ou distantes ou e se não há uma 
utilização dessa network além de sua capacidade.
Por fim, antes mesmo de iniciar a atividade de tuning, é preciso 
determinar o objetivo, afinal não se chega a nenhum lugar quando não 
se sabe para onde se deseja ir. Assim, para realizar o tuning de em SGBD, 
deve-se ter em mente constantemente pelo menos três questões que 
apontam para o seu funcionamento:
• As estruturas internas do SGBD são compartilhadas, ou seja, se um 
processor for privilegiado demais, pode causar um desempenho 
ruim eu outros, o que pode criar um gargalo. Em outros termos, se 
a estrutura em memória que abriga os comandos SQL for muito 
privilegiada, a área para os dados pode ficar pequena demais, 
surgindo, assim, um problema novo causado pela atividade de 
tuning. Nas considerações sobre o uso da RAM, a “máquina”, virtual 
ou física, como um todo deverá ser considerada.
• Devemos sempre ter em mente que o funcionamento do SGBD se 
baseia em variáveis específicas, referenciadas como parâmetros. 
Se observarmos o manual de manutenção de um veículo – para 
oferecer uma analogia –, veremos que os pneus devem ter uma 
certa calibragem, sob pena de se desgastarem demasiadamente 
ou causarem instabilidade no veículo. Em certos casos, os pneus 
traseiros merecem uma pressão de ar diferente que os dianteiros, 
mas jamais o lado esquerdo será diferente do direito. Além disso, 
diferentes situações de carga merecem calibragens diferentes.
• Por fim, o diagnóstico de uma situação que exija tuning se fará 
a partir de valores armazenados em visões (views) do dicionário 
de dados (DICT), cujos valores são armazenados a partir da 
inicialização do SGBD. Assim, as medidas a serem obtidas deverão 
ser tomadas após o horário de pico, entre um Startup e um 
Shutdown.
11
Para facilitar, trazemos uma síntese sobre “antes de qualquer outra 
coisa”:
Pré-Requisitos
• RAM–<Necessidades do Sistema>.
• Condições da Rede.
• Objetivo Definido (em números).
Não Esquecer
• Estruturas COMPARTILHADAS.
• Parâmetros EQUILIBRADOS.
• Medidas após horário de pico, entre Startup e Shutdown.
1.3 As etapas da otimização
A atividade de tuning tem como objetivo servir à instituição que 
possui o Banco de Dados, funcionando como um ciclo PDCA, ou seja, 
normalmente ao final da última etapa é possível que novas questões de 
performance se apresentem, sendo necessário voltar para a primeira e 
recomeçar. Aqui devemos observar os pré-requisitos e nos lembrar dos 
itens sobre os quais estudamos até aqui.
1.3.1 Projeto
Em termos ideais, otimizar o projeto significa ser envolvido, enquanto 
DBA, na concepção da infraestrutura a ser utilizada. Assim, será 
necessário opinar sobre o volume de memória, processamento e 
armazenamento a ser utilizado. Nisso implica a configuração do 
Database, ou seja, como as tabelas devem ser organizadas, em sua 
12
estrutura lógica e física, e quais estruturas e estratégias serão utilizadas. 
Aqui, deve-se entender diversas particularidades do negócio, das quais 
trataremos adiante.
1.3.2 Aplicação
Realizar o tuning da aplicação não significa interferir diretamente no 
trabalho do desenvolvedor do software. Ele é o especialista em lógica 
de programação e deve conhecer as necessidades da instituição nesse 
campo. Deveremos, entretanto, interferir na codificação dos comandos 
SQL, examinando o desempenho de consultas que utilizam filtros e 
realizando análises da reação do otimizador a essas consultas, bem 
como os planos de execução. Ao medirmos o impacto do desempenho 
das consultas com índices, por meio do otimizador, auxiliaremos o 
desenvolvedor na criação de um APP conveniente.
1.3.3 Memória
Aqui devemos aplicar o conhecimento sobre como adequar o 
desempenho das estruturas em memória, de modo a tornar os 
dimensionamentos ideais. Tais estruturas têm a seguinte organização, 
dentro de uma superestrutura denominada Área Global Compartilhada 
(Shared Global Area – SGA):
• Shared Pool
• Library Cache (Shared SQL Area).
• Row Cache (Data Dictionary Cache).
• BufferCache.
• ReDo Log Buffer.
• Sort Areas.
13
O SGBD demanda também da RAM do DBServer uma reserva para 
cada processo de usuário (UsrProc), o qual pode ser eventualmente 
compartilhado. Desse modo a “equação” que foi representada como 
“RAM–<Necessidade do Sistema>” resultará em uma memória do 
DBServer que possa suportar tanto a SGA quanto o volume consumido 
pelo UsrProc.
Um benefício dos atuais Sistemas Operacionais é criar memória de 
paginação, também conhecida como memória virtual, de modo a 
impedir que o computador deixe de funcionar, ou pelo menos apresente 
falas, quando o volume da RAM é superado. Ainda que esse recurso seja 
importante, ao representar atividades de entrada e saída de dados de e 
para a RAM (leitura e escrita em disco, I/O), tende a esconder certa perda 
de performance do DBServer.
1.3.4 Entrada e Saída (I/O)
A utilização de estruturas físicas oferecidas pelo SGBD poderá ser 
determinante para a performance. Devemos realizar ajustes nessas 
estruturas físicas usuais, assim como poderemos chegar a utilizar 
estruturas diferenciadas para conjuntos especiais de dados.
Essas estruturas têm finalidades distintas, mas que se integram 
intimamente, sendo classificadas como:
• ReDo Log.
• Tablespaces e DataFiles.
• DataBlocks.
• UnDoou Rollback Segments.
14
1.3.5 Contenção
Os ajustes promovidos até então, a fim de melhorar a utilização de 
índices, adequando estruturas em memória e estruturas físicas, podem 
gerar gargalos, classificados normalmente como contenções. Eles são 
também mensuráveis e se classificam como:
• Lock.
• Em filas – LATCHES.
• ReDo Log Buffer.
1.3.6 Sistema Operacional
Vamos supor que as consultas realizadas contra o SGBD – compostas 
pela linguagem denominada SQL (Queries) – tenham atingido um nível 
pelo menos aceitável de performance; e que as estruturas físicas e 
em memória estejam funcionando adequadamente, após as questões 
de I/O e de contenção terem sido adequadamente solucionadas. 
Ou seja, o ciclo de tuning foi concluído. No entanto, é possível que o 
Sistema Operacional que dá suporte ao SGBD, assim como o próprio 
DBServer, tenha sido sobrecarregado. Esse é um dos sentidos citados 
anteriormente como “estruturas compartilhadas”.
Caso a infraestrutura conte com uma máquina virtualizada, 
provavelmente bastará demandar mais memória ou tempo e 
quantidade maior de CPUs. Se o resultado para a performance for 
muito satisfatório, o aumento de investimento no DBServer pode ser 
justificado.
A partir do que vimos, é possível fazer uma lista das Etapas da 
Otimização:
15
• Projeto (1º).
• Aplicação (2º).
• Memória (3º).
• I/O (4º).
• Contenção (5º).
• Sistema Operacional (6º).
2. Estratégias do negócio da Instituição e do 
tuning
Muitas pessoas acreditam que o processo de ajuste começa quando os 
usuários reclamam do tempo de resposta, mas geralmente já é muito 
tarde para tomarmos a melhor ação. Caso sejam detectados problemas 
com o desenho dos dados, muito dificilmente ele será modificado, 
restando-nos somente as técnicas para o ajuste da memória e do I/O, 
que podem não ser totalmente eficientes para esse tipo de problema.
2.1 Quando ajustar?
O SGBD possui diversos aspectos que devem ser considerados e bem 
conhecidos durante o planejamento dos sistemas e das aplicações. Além 
disso, os desenhistas e projetistas devem saber quais deles oferecem os 
melhores benefícios para serem aplicados durante a fase de projeto.
Ao DBA caberá, durante a criação do SGBD, a atividade de criar 
estruturas de dados e de suporte à utilização desses dados, que 
atendam ao projeto e tendam a privilegiar a performance desse SGBD. 
Considerarmos isso é fundamental para o estabelecimento das devidas 
16
necessidades de backup, disponibilidade para os usuários e tempo de 
vida das aplicações, assim como para a eliminação da contenção e do 
I/O excessivo em disco.
Diversas técnicas podem ser usadas para facilitar essa tarefa, e seu 
domínio e uso são altamente recomendados.
2.2 Particularidades do negócio
Quando o papel do DBA foi analisado, vimos a necessidade de criação 
de estruturas que atendam ao projeto, assim como particularidades do 
sistema informacional da instituição como um todo. Algumas dessas 
necessidades estão ligadas a características que estudaremos adiante.
2.2.1 Alta Disponibilidade ou Resiliência?
Uma das questões que afetam a performance está ligada ao conceito 
de Alta Disponibilidade. Aqui ele será tratado como simplesmente 
Disponibilidade, que deve ser entendida a partir do seguinte cenário: o 
Backup e eventuais Restore e Recovery, do mesmo modo que a velocidade 
dos serviços prestados pelo SGBD em si, compõem a possibilidade ou 
a capacidade de o sistema estar disponível. Podemos usar o conceito 
de resiliência (em termos de um serviço computacional), ou seja, 
disponibilidade significa a capacidade tanto do sistema de responder 
a uma necessidade do seu usuário quanto resistir ou recuperar-se de 
um evento de falha, seja esta física ou lógica. A esse cenário devem ser 
adicionadas as possibilidades de tentativas mal-intencionadas de acesso 
aos dados da instituição.
Muito embora não seja nosso intuito detalhar os tipos de evento 
enumerados a seguir, é importante percebermos que são questões que 
usualmente interferem no volume de memória RAM consumido pelo 
17
ambiente com um todo, alterando também a somatória de I/O que nele 
acontece.
Diferentes utilizações e finalidades dos sistemas informacionais 
demandarão estratégias de Backup, Restore e Recovery. A utilização 
de Clusters e a criação de ambientes em Cloud influenciarão as 
configurações e as estratégias particulares. Algumas estratégias gerais 
da administração dos sistemas informacionais da organização que 
afetarão direta ou indiretamente ações de tuning são analisadas adiante.
2.2.2 SLA & MTTR
Um Acordo de Nível de Serviço (Service Level Agreement – SLA) é um 
protocolo que determina o tempo disponível para corrigir um incidente, 
o qual pode ser um ataque cibernético aos dados da instituição; a 
adulteração maliciosa ou não intencional de dados; ou uma inundação 
do CPD. Em outras palavras, são eventos que façam com que o SGBD 
sofra um Recovery.
O Tempo médio para recuperar (Mean Time To Recover – MTTR) é um 
dado que também deverá ser ajustado. Ele será configurável por meio 
de ajustes nas estruturas ligadas à recuperação do SGBD e à efetivação 
das transações (Commit). Quando o tamanho da área de Re Do Log Buffer, 
a quantidade e a dimensão dos Re Do Log Files On Line e Off Line (Archives) 
são estabelecidos, o MTTR é afetado.
O estado da arte, em termos de armazenamento, é conhecido como 
Cloud. Entretanto, a aquisição, por parte da instituição, desse tipo de 
serviço nem sempre é possível. Mesmo quando tal tipo de Storage 
é utilizado, é necessário estudar as estratégias de Salvaguarda e 
Recuperação disponíveis. Deverão ser estudadas e implementadas 
questões como Backup On Line versus Backup Off Line; Backup User 
Defined; Backup RMAN e Cumulativo; e Espelhamento (BCV).
18
2.2.3 Processamento de transações em tempo real
As aplicações que se caracterizam pelo processamento de transações 
em tempo real, conhecidas como aplicações OLTP (Online Transaction 
Processing ou Processamento de Transações em Tempo Real), são 
sistemas com grande quantidade de consulta, inserção, atualização 
e eliminação de dados, o que causa o armazenamento de grandes 
volumes de dados acessados concorrentemente por centenas e até 
milhares de usuários. Um sistema de reserva de passagens aéreas é 
uma aplicação OLTP típica, assim como um sistema de lançamentos 
bancários.
Para esses sistemas, alguns pontos-chaves são imprescindíveis, 
assim como para todas as aplicações que se encaixam nesse tipo: 
disponibilidade; rapidez nas transações; concorrência e integridade 
dos dados garantidas; e possibilidade de recuperação. Em um sistema 
OLTP com muitos usuários concorrentes, não pode haver quaisquer 
problemas de performance causados pelo número excessivo deles. 
Também é preciso melhorar o uso excessivo de índices e clusters 
pelo fato de essas estruturas causarem uma diminuição no tempo de 
respostas para as inserções e atualizações dos dados.
Os tópicos apresentados a seguir são cruciais para o ajuste de um 
sistema que processa suas transações em tempo real:
• Segmentos de rollback em número e tamanho apropriados.
• Índices, clusters e hash clusters.
• O tamanho dos blocos de dados.
• A alocação dinâmica do espaço para as tabelas e os segmentos de 
rollback.
19
• A monitoração do processamento das transações e a arquitetura 
multithreaded do SGBD.
• O tamanho da estrutura Shared Pool, na SGA.
• O ajuste fino dos comandos SQL.
• As constraints de integridade.
• A arquitetura cliente/servidor.
• O uso de Stored Procedures.
2.2.4 Big Data
As aplicações de suporte à decisão (Decision Support Systems – DSS) 
geralmente buscam informações e as formatam em relatórios 
específicos que auxiliam no direcionamento das empresas. O termo 
Big Data é um fenômeno ligado ao crescimento da Internet, que gera 
enormes quantidades de dados. A origem de dados dos DSS pode se dar 
a partir dessas fontes da Internet, assim como de seus próprios dados. 
Geralmente os DSS consultam grandes quantidadesde dados mantidos 
por transações OLTP.
As decisões tomadas são baseadas nas informações obtidas por essas 
aplicações, para determinar quais as melhores estratégias a serem 
seguidas. Como exemplo de um sistema de suporte à decisão, podemos 
tomar uma ferramenta de marketing que determina o perfil de compra 
de possíveis consumidores por meio de estudos demográficos, o que 
possibilita a determinação de quais itens podem ser mais vendidos em 
cada localização. Esses tipos de sistema caracterizam-se pela velocidade, 
pela confiabilidade e pela disponibilidade dos dados.
Portanto, durante o desenho dessas aplicações, devemos nos certificar 
de que as consultas, que geralmente pesquisam uma grande quantidade 
20
de informações, possuem um bom tempo de resposta. A chave para a 
boa performance desses sistemas de suporte à decisão é o ajuste das 
consultas e o uso apropriado de índices, clusters e hash clusters. A opção 
de consultas em paralelo também pode beneficiá-las.
Os seguintes tópicos são fundamentais para os sistemas de suporte à 
decisão:
• Índices, clusters e hash clusters.
• O tamanho dos blocos de dados.
• A opção de processamento de consultas em paralelo.
• O otimizador do SGBD.
• O uso de hints nas consultas.
• O uso de funções PL/SQL com os comandos SQL.
2.2.5 Inteligência Artificial (IA)
Os sistemas científicos ou estatísticos geralmente executam uma 
grande quantidade de cálculos complexos em uma grande quantidade 
de dados. Utilizados especialmente como origem de treinamento 
de entidades em IA, cujas estratégias de Aprendizagem de Máquina 
(Machine Learning) frequentemente demandam dados representados 
por tipos complicados, como latitude ou pressão atmosférica, ou os 
inúmeros pontos (pixels) de imagens, as aplicações são caracterizadas 
por sofisticados comandos SQL usados para produzir as informações 
necessárias. Elas envolvem complicadas análises estatísticas para 
conseguir os objetivos de treinamento. O ajuste dessas queries é crucial, 
envolvendo:
21
• Uso de funções PL/SQL em comandos SQL.
• Processamento de consultas em paralelo.
• O otimizador do SGBD.
• O uso de hints nas consultas.
Referências Bibliográficas
AULETE DIGITAL. Método. 2020a. Disponível em: http://www.aulete.com.br/metodo. 
Acesso em: 24 jul. 2020.
AULETE DIGITAL. Performance. 2020b. Disponível em: http://www.aulete.com.br/ 
performance. Acesso em: 2 ago. 2020.
DATE, Christopher J. Introdução a sistemas de bancos de dados. 8. ed. Rio de 
Janeiro: Elsevier, 2004.
ELMASRI, Ramez; NAVATHE, Shamkant B. Sistemas de banco de dados. 7. ed. São 
Paulo: Pearson Education, 2019.
ORACLE® DATABASE. Database Concepts. 18c. E84295-04. Oracle Corporation, 
2018. Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/cncpt/database-concepts.pdf. Acesso em: 24 jun. 2020.
ORACLE® DATABASE. Database Performance Tuning Guide. 18c. E83719-04. 
Oracle Corporation, 2019a. Disponível em: https://docs.oracle.com/en/database/
oracle/oracle-database/18/tgdba/database-performance-tuning-guide.pdf. Acesso 
em: 24 jun. 2020.
ORACLE® DATABASE. SQL Tuning Guide. 18c. E84296-04. Oracle Corporation, 
2019b. Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgsql/sql-tuning-guide.pdf. Acesso em: 24 jun. 2020.
SILBERSCHATZ, Abraham; KORTH, Henry F.; SUDARSHAN, S. Sistema de banco de 
dados. 6. ed. Rio de Janeiro: Elsevier, 2012.
http://www.aulete.com.br/metodo
http://www.aulete.com.br/ performance
http://www.aulete.com.br/ performance
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/database-concepts.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/database-concepts.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guid
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guid
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf
22
As consultas ao Banco de Dados
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
Objetivos
• Entender o funcionamento do otimizador e a criação 
dos planos de execução de consultas a um Sistema 
Gerenciador de Banco de Dados (SGBD).
• Administrar as estatísticas do SGBD e compreender 
sua importância. 
• Conhecer os métodos de acesso aos dados. 
• Examinar e influenciar o desempenho de consultas 
que utilizam filtros e índices.
23
1. O Otimizador
Dependerá de nós conhecer o processo otimizador de Linguagem de 
Pesquisa Estruturada (Structured Query Language – SQL) do Sistema 
Gerenciador de Banco de Dados (SGBD) e, a partir dele, executar o tuning 
dessas consultas, juntamente com outros conhecimentos. Para isso, 
começamos com duas observações:
• Existe somente uma parcela, dentro das Aplicações de Computador 
(APPs) ou de softwares aplicativos, que acessa e manipula dados 
por meio da linguagem SQL. Além dos efeitos diretos do SQL, a 
lógica utilizada pelo programador pode afetar a velocidade de seu 
processamento e, consequentemente sua performance, mas essa 
característica não será tratada aqui.
• O otimizador (optimizer), assim como a grande maioria, se não 
todos os artefatos de IA disponíveis no final da segunda década do 
século XXI, são aplicações de princípios estatísticos, ainda que seus 
resultados não evidenciem diretamente os números que estão por 
trás de suas decisões.
Esse artefato, ou porção do SGBD chamada de otimizador, será 
referenciada como optimizer daqui em diante, para evidenciar suas 
características e funções.
É o optimizer que decide o caminho de acesso aos dados mais eficiente, 
tornando mínimo o volume de entrada e saída (Input e Output, I/O, que 
é o trânsito de dados de e para a memória do computador) necessário 
para recuperar as informações. Essa decisão acontece a partir dos 
comandos SQL, os quais se destinam ao relacionamento com o 
SGBD. Particularmente na pesquisa (Select, Query) e nas manipulações 
(Inserção, Alteração, Inclusão, Insert, Update, Delete), há os filtros (cláusula 
Where do SQL) e a indicação das tabelas utilizadas (cláusula From do 
SQL), sendo estes o principal foco desta análise.
24
Por caminho, devemos entender um conjunto de procedimentos, um 
algoritmo. Por exemplo, se estivéssemos em uma biblioteca física diante 
de uma série de gavetas com fichas de cartolina destinadas a registrar 
os livros dessa biblioteca, como procederíamos?
Que tal, antes de continuar a leitura, tentar escrever seu próprio 
conjunto de procedimentos? A ilustração adiante pretende ajudar a 
visualizar a situação a ser resolvida.
Figura 1 – Sistema de Índices de uma biblioteca física
Fonte: Archive/iStock.com
Esse algoritmo seria mais ou menos assim:
• Identificar o conjunto de gavetas que contém fichas organizadas 
pela informação que temos, que pode ser autor, assunto ou título.
• Identificar nesse conjunto a gaveta que contém fichas dentro do 
intervalo alfabético procurado.
• Dentro dessa gaveta, localizar a ficha que nos interessa.
25
• Dessa ficha, anotar o código localizador do livro encontrado.
• Desse código localizador, entender em local da biblioteca e em 
qual prateleira está o livro.
• Nesse local e nessa prateleira, obter o livro.
Escrevendo um algoritmo semelhante para o optimizer, podemos indicar:
• Verificar na cláusula From do SQL qual ou quais tabelas utilizar.
• Deduzir qual ou quais índices dessas tabelas serão necessários a 
partir da cláusula Where do SQL.
• Descobrir qual método de leitura utilizar, o que significa usar ou 
não os índices existentes, e que método de junção (Join) de tabelas 
seguir.
• Obter os blocos desde o índice para a memória e então obter as 
linhas das tabelas.
• Observação: do mesmo modo que tivemos que caminhar 
até a prateleira da biblioteca para obter o livro, o SGBD 
deverá realizar uma certa quantidade de I/O, lendo o meio de 
armazenamento (Storage)e transportando Blocos de Dados 
(Data Block) para a memória, na qual são retidos esses Data 
Blocks, para então poder responder ao SQL.
1.1 Bloco de Dados (Data Block)
Um Bloco de Dados (Data Block) é a unidade mínima de I/O que o SGBD 
executará. É também a menor alocação de espaço em disco que um 
computador endereça.
Esse endereçamento é dado por uma “pseudocoluna” que cada 
tabela contém, denominada ROWID, composta por uma sequência 
26
alfanumérica. Segundo o Database Concepts (2020, p. 12), essa 
sequência endereça cada linha “sem armazenar esse valor, mas 
inferindo-o a partir do arquivo e bloco onde o dado está armazenado 
[...a sequência citada contém] caracteres entre A-Z, a-z, 0-9, +, e /”.
O significado do ROWID é interpretado como AAAPec.AAF.AAAABS.AAA 
para Número do(a): objeto.arquivo.bloco.linha (ORACLE® DATABASE, 
2018, p. 12).
Figura 2 – Volume ocupado no Sistema Operacional
Fonte: elaborada pelo autor.
A ideia de unidade de I/O e de alocação de espaço é fácil de ser 
verificada. Por meio de um redator de texto simples, como o Notepad da 
Microsoft ou o “vi” do Unix (usar os comandos “vi” para criar o arquivo e 
“du” para verificar a utilização do disco):
• Criar um arquivo com um simples “X” dentro.
• Gravar o arquivo e observar no sistema operacional o tamanho; 
deverá ser de 1024 bytes.
27
• Abrir o arquivo e escrever mais dois caracteres “YZ”, por exemplo.
• Novamente fechar o arquivo e verificar seu tamanho, que foi 
mantido em 1024 bytes.
• Repetindo algumas vezes essa operação, será possível notar que 
uma certa quantidade de linhas necessárias para que o arquivo 
passe a ter outra dimensão. No caso da ilustração (Figura 2), 2048 
bytes.
Devemos ter em mente, portanto, que mesmo ao recuperar um simples 
“S” ou “N”, consultando, por exemplo, se um cliente aceita ou não 
receber mensagens promocionais de uma empresa, toda a linha da 
tabela consultada será conduzida, desde o Storage até a memória do 
SGBD, e trafegará na rede até a APP que solicitou tal informação. Ela 
será obtida a partir de uma consulta a uma única tabela ou de muitas 
junções (join) de tabelas. Portanto, por mais simples que seja uma 
consulta, ela levará o SGBD a manipular no mínimo um Data Block.
1.2 Particularidades das Consultas e Manipulações
Todas as operações que envolvem o acesso aos dados de um banco de 
dados são efetuadas por meio de comandos SQL, os quais são formados 
por uma cadeia de caracteres em um formato semelhante a uma 
sentença da língua inglesa.
Do vasto universo da linguagem SQL, homologada como padrão ANSI, 
aqueles que interferem no ajuste, ou nos quais se pode interferir, são 
denominados DML e de controle de transação. Os comandos DML (Data 
Manipulation Language ou Linguagem de Manipulação de Dados) que 
serão alvo de ajuste serão o Select (selecionar), Insert (inserir), Update 
(alterar) e Delete (eliminar).
O Select (selecionar) é também conhecido como DRL (Data Retrieval 
Language, ou linguagem de recuperação de dados). Essa distinção 
28
interessará particularmente ao analisarmos que os procedimentos do 
SGBD divergem para DMLs e DRLs.
Os comandos de controle de transação gerenciam conjuntos de 
alterações nos dados das tabelas, sendo estabelecidos pelos comandos 
Insert, Update e Delete. Eles permitem agrupar essas operações em 
transações lógicas, efetivá-las ou desistir delas. Os comandos COMMIT, 
ROLLBACK e SAVEPOINT fazem parte dessa categoria.
2. As fases de execução de um SQL
Cada comando SQL é processado basicamente em três fases: a 
fase de análise (parse), de execução (execute) e de retorno das 
linhas selecionadas (fetch). Entre os SQLs que receberão ajustes na 
performance, é importante observar que um Select (DRL) contém a fase 
de fetch, mas um Insert, Update, Delete (DML), não.
Entenderemos melhor como essas fases acontecem e que eventos as 
compõem observando-as esquematicamente:
• Parse (compilação dos Comandos SQL):
• Valida o comando SQL verificando sua sintaxe e semântica.
• Calcula o Hash Value da instrução SQL.
• Procura uma cópia existente da instrução SQL na Shared Pool.
• Se existe, adquire um ponteiro para ela.
• Se NÃO existe, cria um cursor (Context Area, Shared SQL Area).
• Efetua pesquisas no dicionário de dados para validar definições 
de tabela e coluna.
29
• Adquire bloqueios de análise em objetos, de forma que as 
definições não se alterem durante a análise da instrução.
• Determina o plano de execução (Execution Plan, Parse Tree) ideal 
para a instrução (aqui atua o optimizer).
• Se adquiriu um ponteiro para a instrução, reutiliza plano pré-
existente.
• Execute:
• Execução do Parse Tree.
• Fetch:
• Retorno das Informações para o Usuário (somente para um 
Select – DRL).
2.1 Comandos idênticos, melhor performance do SQL
Notemos que dois comandos são idênticos quando cada um de seus 
caracteres, espaços, tabulações, comandos de avanço de linha e uso de 
maiúsculas e minúsculas, ou seja, cada byte de sua escrita, é exatamente 
igual. Por normalmente se encontrarem inseridos em uma APP, devem 
utilizar variáreis que tenham o mesmo nome. O SGBD tira proveito dos 
comandos que são idênticos, a fim de diminuir o tempo de análise, 
executando-os, dessa forma, mais rapidamente.
Na fase de análise ou parse, o SGBD verifica a sintaxe do comando e 
pesquisa a área de memória que se encontra na shared pool, chamada 
de shared SQL area, em busca de um comando exatamente igual. Caso 
exista um comando idêntico, verifica o dicionário de dados em busca 
das permissões de acesso, dos privilégios de segurança e do plano de 
execução dos comandos.
30
Se o comando não estiver definido na shared SQL area, ele determina o 
plano de execução com o mais eficiente meio de acesso aos dados (aqui 
atua o optimizer). Esse plano de execução, uma vez armazenado em 
memória, pode ser compartilhado por outras aplicações que emitam o 
mesmo comando (exatamente igual).
Existe um mínimo detalhe, mas de grande utilidade, que deve ser 
observado durante todo o Projeto das APPs. Caso os comandos que 
demandam informações iguais sejam escritos de forma exatamente 
igual ao longo de todas as aplicações que acessam o SGBD, seus planos 
de execução serão compartilhados, diminuindo o tempo total de 
execução de cada um.
O compartilhamento do plano de execução oferece os seguintes 
benefícios:
1. Melhoramento do uso da memória, uma vez que as definições dos 
comandos podem ser compartilhadas entre as diversas aplicações.
2. A memória é dinamicamente ajustada de acordo com o conjunto 
de comandos SQL executados.
3. A fase de parse é resumida e o tempo de execução de um 
comando pode diminuir consideravelmente.
2.2 Como um DML é executado
Para entendermos melhor esta questão, vamos analisar antes as 
estruturas envolvidas nela. Vejamos primeiro o comando exemplificado:
Update tabA
Set colA=1
Where colA=0;
31
• Podem existir várias outras linhas e diversas outras colunas.
• Before Image (BI)=0.
• After Image (AI)=1.
• Before Image (BI) ou Imagem Anterior: é o valor que havia na coluna 
antes da execução do DML.
• After Image (AI) ou Imagem Posterior: é o valor que haverá na 
coluna depois da execução do DML.
• ReDoLogBuffer: é uma parte da memória do SGBD em que cada 
comando será identificado pelo seu Hash Value, juntamente com 
seu BI e AI em forma de relatório (journal).
• LGWR: é um processo do SGBD que transporta os dados do 
ReDoLogBuffer para o ReDoLogFile.
• ReDoLogFile: é um conjunto de arquivos que faz parte do DataBase 
e conterá uma cópia de cada entrada do ReDoLogBuffer.
• DataFile: é um conjunto de arquivos que faz parte do DataBase e 
conterá principalmente as tabelas.
• Checkpoint: é um evento que ocorre a cada intervalo de tempo ou 
de soma de bytes manipulados, forçando uma cópia de todos os 
bytes que foram alterados e estão em memória.
• UnDo: é um espaço em disco que faz parte do DataBase e conterá 
dados de BI e AI, permitindo que um comando seja desfeito 
(Rollback).
Ao entenderas fases de execução de um SQL, é possível notar que, 
entre um DRL (Select) e um DML (Insert, Update, Delete), existe uma 
diferença importante. Um DRL é finalizado pelo fetch, ou seja, a remessa 
32
dos resultados de uma consulta para o solicitante. Já no caso de um 
DML, não existe fetch, mas somente um Código de retorno igual a zero 
(return code 0), quando há sucesso da execução.
Entretanto, para a execução de um DML estar finalizada, o SGBD tem 
que conseguir escrever fisicamente em ReDoLogFiles antes de confirmar 
ao requisitante um COMMIT OK! Além disso, o DML demanda a inclusão 
de uma estimativa de leitura de UnDo no Execution Plan.
Há então uma sequência importante de eventos:
1. Recebido o comando COMMIT, o ReDoLogBuffer é flushed (levado 
embora, como a areia impulsionada por uma mangueira) pelo 
LGWR.
2. O ReDoLogFile deve ser fisicamente escrito.
3. O processamento pode ter que esperar por Checkpoints.
4. Quando o Oracle puder se comprometer (commitment) com os 
dados efetivados (quando as fases 2 e 3 estiverem concluídas), o 
solicitante pode receber a devida notificação de encerramento do 
DML, ou seja, COMMIT OK!
3. Junções de tabelas (joins)
Em função de um conjunto de técnicas conhecido como normalização, 
quase sempre precisaremos obter dados de mais de uma tabela 
para conseguir a informação necessária. É até possível incluir essa 
necessidade programaticamente em uma APP, mas isso significaria 
reinventar a roda e, salvo em casos muito especiais, dificilmente se 
conseguiria ser mais performático que a junção provida pelo optimizer.
O optimizer tem ao seu dispor três métodos nomeados Tipos de Join, 
enumerados adiante. Um desses métodos de combinações (joins) entre 
33
múltiplas tabelas será determinado no momento da definição do plano 
de execução dos comandos SQL.
• Ordenação-Combinação (Sort Merge): ao escolher o Sort-Merge, o 
SGBD combina as linhas ordenadas de cada tabela. Esse método é 
utilizado somente para joins baseados em igualdade de condições 
na cláusula Where, como com a condição EMP.DEPTNO = DEPT.
DEPTNO:
• O SGBD ordena cada conjunto de linhas a serem combinadas, 
ordenadas pelos valores usados na condição de join. 
Naturalmente, se já estiverem ordenadas, essa operação não é 
executada.
• O SGBD combina os dois conjuntos de linhas, sendo cada uma 
delas combinada com cada linha de outro conjunto de valores, 
de acordo com as colunas que foram usadas na condição de 
join.
• Finalmente, o SGBD retorna o resultado com as linhas 
devidamente combinadas.
• Laços Aninhados (Nested Loops): neste método, para cada linha de 
uma tabela, o SGBD localiza todas as linhas da outra tabela que 
satisfaçam a condição de join:
• O optimizer escolhe uma das tabelas como a driving-table (tabela 
principal ou tabela-guia, usada para processar as linhas de um 
comando). As outras tabelas são consideradas como inner-tables 
(usadas internamente, e não como guia para o processamento 
das linhas).
• Para cada linha da tabela-guia, o SGBD localiza todas as linhas 
das outras tabelas que satisfaçam a condição de join.
34
• O SGBD combina também os dados de cada par de linhas 
que satisfaçam a condição de join e retorna as linhas como 
resultado.
• Agrupamento (Cluster): neste método, o join ocorre de forma 
semelhante ao método Nested Loop. Entretanto, as colunas que 
formam as chaves do cluster é que são referenciadas na condição 
de join:
• O otimizador escolhe uma das tabelas como a driving-table, 
enquanto a outra torna-se a inner-table.
• Para cada linha da tabela-guia, o SGBD localiza todas as linhas 
correspondentes da inner-table que satisfaçam a condição de 
join. As linhas correspondentes às linhas retornadas da tabela 
principal estão armazenadas nos mesmos blocos de dados. 
• O SGBD combina os dados em cada par de linhas que satisfaçam 
a condição de join e retorna essas linhas.
4. Métodos de Acesso (caminhos)
Existem 15 caminhos de acesso às informações diferentes em um banco 
de dados, sendo sua escolha uma das principais tarefas do optimizer, 
a fim de acessar mais rapidamente os dados. O conjunto desses 
caminhos de acesso é identificado segundo uma ordem específica, em, 
teoricamente, os primeiros formam o método mais rápido e os últimos o 
mais lento.
1. Acesso a uma única linha por meio de seu ROWID.
2. Acesso a uma única linha por meio de um cluster.
3. Acesso a uma única linha por meio de um hash cluster.
35
4. Acesso a uma única linha por meio de uma chave primária ou 
única.
5. Acesso a um conjunto de linhas por meio de uma combinação de 
linhas de duas ou mais tabelas agrupadas em um cluster.
6. Acesso a um conjunto de linhas por meio de um cluster em cuja 
chave foi aplicada a função hash.
7. Acesso a um conjunto de linhas por meio da chave de um cluster 
indexado.
8. Acesso a um conjunto de linhas por meio de um índice composto.
9. Acesso a um conjunto de linhas por meio de um índice simples, 
isto é, formado por apenas uma única coluna.
10. Acesso a um conjunto de linhas que formam uma faixa de valores 
fechada usando colunas indexadas.
11. Acesso a um conjunto de linhas que formam uma faixa de valores 
em aberto usando colunas indexadas.
12. Acesso a um conjunto de linhas por meio de uma operação SORT 
MERGE.
13. Acesso a uma linha usando as funções MAX() e MIN() em colunas 
indexadas.
14. Acesso a um conjunto de linhas usando na cláusula ORDER BY 
uma referência a uma ou mais colunas indexadas.
15. Acesso a um conjunto de linhas usando full scan, ou seja, a leitura 
e o retorno de todas as linhas de uma tabela.
5. Estatísticas
Para que o optimizer possa escolher o método mais apropriado e 
performático na execução de um SQL, é necessário que ele tenha 
disponíveis dados estatísticos, que contêm dados sobre tempo (quando/ 
duração) e frequência (quantas vezes) de uso de processos, assim como 
distribuição de dados nas tabelas e índices.
36
Essa obtenção de estatísticas acontecerá por meio da execução do 
comando ANALYZE, o qual coleta estatísticas cujos detalhes podem ser 
obtidos com as seguintes palavras-chave, com os efeitos explicados em 
seguida:
• INDEX: identifica o nome do índice a ser analisado.
• TABLE: identifica a tabela a ser analisada. Durante a análise, 
também são coletadas informações estatísticas para os índices 
associados à tabela.
• CLUSTER: identifica o nome do cluster a ser analisado. Durante a 
análise, também são coletadas informações estatísticas das tabelas 
e dos índices associados ao cluster.
• COMPUTE STATISTICS: examina todas as linhas de um objeto. 
Apesar de mais lento, esse modo é mais confiável.
• ESTIMATE STATISTICS: examina uma porção do objeto. Apesar de 
mais rápido, não oferece tanta segurança como a opção COMPUTE 
STATISTICS.
• SAMPLE: especifica a porção, em número de linhas (ROWS) ou em 
percentual (PERCENT), das linhas a serem verificadas.
• DELETE: remove estatísticas de um objeto do dicionário de dados.
• VALIDATE STRUCTURE: valida a estrutura dos objetos analisados. 
Com o uso da opção CASCADE, podemos analisar todos os índices 
de uma tabela e todas as tabelas e todos os índices de um cluster. 
Quando um cluster é analisado, o SGBD automaticamente analisa 
as tabelas armazenadas no objeto, como padrão.
37
6. Visualização do Plano de Execução
A fim de entendermos e eventualmente corrigirmos o resultado da ação 
do optimizer, é necessário visualizar o plano de execução. Para tanto, 
utilizaremos o comando EXPLAIN PLAN, que permite a visualização do 
plano de execução dos comandos INSERT, DELETE, UPDATE e SELECT.
Antes da execução do EXPLAIN PLAN, é necessário criar uma tabela 
específica denominada PLAN_TABLE. Para tanto, devemos executar o 
script padrão “@$ORACLE_HOME/rdbms/admin/catplan.sql” (ORACLE® 
DATABASE, 2019b, p. 143).
Criada a PLAN_TABLE, poderemos executar o comando EXPLAIN PLAN 
FOR <comando SQL a ser inspecionado>, cujo resultado (o plano de 
execução em si) execute seguinte query:
SELECT LPAD(‘ ‘, 2 * (LEVEL–1)) || operation|| ‘ ‘ ||
 DECODE (id, 0, ‘Cost = ‘ || position) “OPERATION”,
 options, object_name
FROM plan_table
WHERE statement_id = ‘identificação’
 START WITH id = 0 
 CONNECT BY PRIOR id = parent_id;
Que resultará (por exemplo) em:
OPERATION OPTIONS OBJECT_NAME
SELECT STATEMENT
 FILTER
 NESTED LOOPS
 TABLE ACCESS FULL EMP
 TABLE ACCESS BY ROWID DEPT
 INDEX UNIQUE SCAN PK_DEPTNO 
 TABLE ACCESS FULL SALGRADE
38
Para entender esse resultado, vejamos uma interpretação. Um filtro 
de linhas será realizado estabelecendo um laço aninhado, por meio do 
acesso à tabela EMP completa e à tabela DEPT, a partir do ROWID, obtido 
da leitura do índice único PK_DEPTNO. A tabela SALGRADE também será 
de forma completa.
7. Escolha do plano de execução
Para escolher um plano de execução, o SGBD utiliza dois métodos de 
otimização: baseado em regras e baseado em custo.
7.1 Método Baseado em Regra
O optimizer determina os caminhos de acesso disponíveis com base 
nas condições da cláusula Where e, então, utiliza um deles, escolhendo 
aquele que ocupa a melhor posição no ranking dos 15 caminhos de 
acesso apresentados anteriormente. A ordem em que as condições 
aparecem na cláusula não afeta a escolha do otimizador. Como o full 
scan ocupa a última posição no ranking, ele sempre escolhe qualquer 
outro caminho de acesso disponível, mesmo que este seja mais rápido.
7.2 Método Baseado em Custo
Neste método, o optimizer utiliza estatísticas sobre as características e a 
distribuição dos dados das tabelas, dos índices e dos clusters envolvidos 
em um comando. O custo de cada caminho de acesso disponível é 
calculado com base no número de leituras lógicas, utilização de CPU 
e tráfego em rede. Apesar de ligeiramente superior ao baseado em 
regra, esse método apresenta problemas no caso de consultas muito 
complexas.
39
O optimizer determina os caminhos de acesso com base nas condições 
da cláusula Where e gera os possíveis planos de execução. Em seguida, 
estima o custo para cada plano usando estatísticas coletadas dos 
índices, das tabelas, das colunas e dos clusters que são acessados pelo 
comando analisado.
Essas estatísticas são coletadas pelo comando ANALIZE e armazenadas 
no dicionário de dados. A ordem em que aparecem as condições na 
cláusula Where não afeta as escolhas do otimizador.
Com o método baseado em custo, o otimizador pode escolher entre a 
utilização de um índice ou de uma estrutura qualquer, ou não; enquanto 
com o método baseado em regra, ele sempre escolhe com base no 
ranking de caminhos disponíveis. Em outras palavras, naquele pode 
escolher um full scan, e não um índice, enquanto neste sempre irá 
escolher o uso de um índice.
8. As dicas (Hints)
Ao observar os caminhos escolhidos pelo optimizer, veremos que em 
algumas situações especiais será necessário especificar outros caminhos 
de acesso para consultas SQL, situações em que ele não foi capaz de 
resolver o problema de forma satisfatória. Tal medida deve ser tomada 
com cautela, inclusive porque depende da reescrita de um comando 
dentro da APP, o que pode levar a constantes manutenções, quando as 
características dos dados se alterarem. Essas adaptações são dinâmicas 
se a conjunção do uso das estatísticas e do optimizer for uma opção.
No caso extremo de influenciar o caminho “manualmente”, tem-se 
à disposição algumas dicas (hints) que podem ser inseridas em um 
comando SQL. Para utilizá-las, deve-se seguir a sintaxe apresentada no 
exemplo:
40
SELECT /*+ FIRST_ROWS */ ename, sal
 FROM emp 
 WHERE deptno = 10;
Referências Bibliográficas
DATE, Christopher J. Introdução a sistemas de bancos de dados. 8. ed. Rio de 
Janeiro: Elsevier, 2004.
ELMASRI, Ramez; NAVATHE, Shamkant B. Sistemas de banco de dados. 7. ed. São 
Paulo: Pearson Education, 2019.
ORACLE® DATABASE. Database Concepts. 18c. E84295-04. Oracle Corporation, 
2018. Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/cncpt/database-concepts.pdf. Acesso em: 24 jun. 2020.
ORACLE® DATABASE. Database Performance Tuning Guide. 18c. E83719-04. 
Oracle Corporation, 2019a. Disponível em: https://docs.oracle.com/en/database/
oracle/oracle-database/18/tgdba/database-performance-tuning-guide.pdf. Acesso 
em: 24 jun. 2020.
ORACLE® DATABASE. SQL Language Reference. 18c. E85457-05. Oracle 
Corporation, 2020. Disponível em: https://docs.oracle.com/en/database/oracle/
oracle-database/18/sqlrf/sql-language-reference.pdf. Acesso em: 5 ago. 2020.
ORACLE® DATABASE. SQL Tuning Guide. 18c. E84296-04. Oracle Corporation, 
2019b. Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgsql/sql-tuning-guide.pdf. Acesso em: 24 jun. 2020.
SILBERSCHATZ, Abraham; KORTH, Henry F.; SUDARSHAN, S. Sistema de banco de 
dados. 6. ed. Rio de Janeiro: Elsevier, 2012.
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/database-concepts.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/database-concepts.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guid
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guid
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/sql-language-reference.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/sql-language-reference.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf
41
Conhecendo e adequando o 
desempenho das estruturas 
em memória
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
Objetivos
• Explorar a organização da memória RAM utilizada 
pelo Sistema Gerenciador de Banco de Dados 
(SGBD).
• Distinguir e medir as alocações compartilhadas 
(Shared, SGA) e privadas (Private, PGA) do SGBD. 
• Examinar e influenciar o desempenho do SGBD em 
relação ao uso da SGA e da PGA.
42
1. O uso da RAM pelo SGBD
Random Access Memory (RAM) caracteriza a Memória de Acesso 
Aleatório de um computador, também considerada como sua memória 
Real. O Sistema Gerenciador de Banco de Dados (SGBD) ocupará espaço 
nessa memória por meio de uma alocação dedicada ao atendimento 
direto dos processos estabelecidos para cada usuário. Tal área, dedicada 
a cada conexão ao SGBD, é designada Área Privativa (Private Global Area 
– PGA).
Vamos analisar aqui a outra área, que é compartilhada por todos os 
processos relativos ao SGBD, ou seja, por dados, comandos e ações 
de todos eles. Por ser a principal alocação em memória, recebe a 
designação de Sistêmica (System). Desse modo, podemos pensar na 
SGA com um S de System ou de Shared, mas será sempre a Área Global 
compartilhada (Global Area).
1.1 SGBD Instance (SID)
É possível instalar e colocar em funcionamento mais de um SGBD em 
um mesmo computador. Para isso, os processos e as alocações de 
memória de cada SGBD serão subordinados ao conceito de instância 
(instance), cujo identificador será constantemente referenciado como 
identificador da instância, ou identificador do sistema (System Identifier 
– SID).
Há um foco no volume da RAM a ser usado pela SGA, sendo 
também preciso observar que um certo volume de RAM é utilizado 
e administrado por cada processo, APP e janela em operação no 
computador. Esses processos, que ocupam espaço da RAM, também 
acabam por determinar quando e como o tempo de processamento é 
consumido, o que são dois dos determinantes da performance de um 
computador. Veremos adiante algumas de suas particularidades.
43
Conexão e Sessão
Conexão conceitua-se como a comunicação entre um User Process e 
um Server Process, enquanto Sessão significa a presença de um usuário 
autorizado, junto ao SGBD.
Processo Usuário (User Process)
Um User Process é estabelecido quando se utiliza qualquer ferramenta 
ou linguagem para acessar, ou seja, conectar-se, o SGBD. Esse 
processo:• É o componente considerado “Cliente”.
• Envia comandos SQL e blocos PL/SQL ao Server Process, 
recebendo dele os respectivos resultados.
• Pode estar localizado na camada Cliente ou na camada Servidor 
(utilização de terminais).
Processo Servidor (Server Process)
O Server Process é estabelecido dentro do servidor, que é o computador 
no qual funciona o SGBD, para permitir a comunicação do User Process 
por default, que ocorre em uma relação de um para um com este. Suas 
tarefas incluem:
• Análise (uso do optimizer) e execução de comandos SQL e blocos 
de linguagens como PL/SQL e Java (pode-se “depositar” processos 
Java para execução interna ao SGBD).
• Leitura de DataBlocks desde o Storage para o DataBase Buffer 
Cache.
• Escrita para o Storage de DataBlocks eventualmente modificados 
no DataBase Buffer Cache.
44
• Devolução dos resultados dos comandos e dos blocos para o User 
Process.
Processos Background
São os processos que dão suporte ao funcionamento do SGBD, permitindo 
o acesso de diversos usuários, as implementações de segurança, a 
recuperação e a distribuição de dados, entre outros. São colocados “por 
trás” da SGA, ou em “segundo plano”, daí o termo background.
O SGBD pode conter muitos processos background ativos, em função da 
configuração estabelecida. Os principais estão enumerados adiante (as 
siglas que possuem uma letra minúscula “n” denotam processos que 
costumam ter múltiplas (n) instâncias ativadas):
• DBWn – DataBase Writer.
• LGWR – Log Writer.
• CKPT – Checkpoint.
• SMON – System Monitor.
• PMON – Process Monitor.
• ARCn – Archiver.
• RECO – Recover.
• LCKn – Lock.
• SNPn – Snapshot Refresh.
• Snnn – Shared Server.
• Dnnn – Dispatcher.
• Pnnn – Parallel Query.
45
PMON, SMON, DBWR, CKPT e LGWR são os cinco processos sem os quais 
o SGBD não pode funcionar. Eles não podem ser omitidos, quando da 
inicialização da SID, e, se qualquer um deles falhar, acontece a falência 
(crash) do sistema.
Descobriremos progressivamente a utilidade e a eventual possibilidade 
de tuning de cada um desses processos. Por enquanto, vejamos:
PMON
O Process Monitor encarrega-se de controlar as conexões com o SGBD e, 
em caso de término anormal destas, liberar os recursos eventualmente 
alocados. Nesse caso, o PMON:
• Provoca a desistência (Rollback) de transações não confirmadas 
(committed).
• Libera reservas de domínio (locks).
• Reinicializa processos Shared Server e Dispatcher eventualmente 
sob Crash.
SMON
O System Monitor encarrega-se de:
• Executar a automatic instance recovery a cada startup executado 
pelo SGBD.
• Recuperar espaços utilizados por segmentos temporários 
(Temporaries) não mais em uso.
• Unir espaços livres nos DataFiles em áreas contíguas (quando um 
Coalesce Tablespace é executado).
46
• Liberar recursos da SGA, como DataBlocks sob leitura ou escrita; 
UnDo Blocks (Rollback Segments); e áreas para algoritmos de 
organização (Temporaries).
1.2 SGA
Retomando o estudo do espaço de RAM a ser ocupado, precisamos 
partir da inicialização da SID. Tal evento é comandado a partir de 
um User Process, que instrui a leitura de um arquivo de parâmetros 
(paRAMeter file) ou de valores default desses parâmetros, que são 
associações de “palavra-chave”=valor.
O documento Database Reference contém o significado e a instrução 
de uso de cada um desses parâmetros. Como há centenas deles, é 
importante saber que existem, mas não vamos nos preocupar com 
eles agora. À medida que formos aprendendo a manipular o SGBD, 
entenderemos quando e como alterar esses parâmetros. O valor default 
de cada um é o ideal, salvo necessidade específica.
Esses parâmetros determinarão como a SGA será organizada. Suas 
quatro principais porções são analisadas a seguir. Existem muitas 
outras, mas não são diretamente visualizadas nem são parametrizáveis.
Shared Pool
É composta por duas subestruturas denominadas Library Cache e Data 
Dictionary Cache. SHARED_POOL_SIZE é o parâmetro que determina o 
tamanho da Shared Pool, em bytes.
Library Cache
É composta de (é um conjunto de) Shared SIQL Areas, possuindo cada 
uma informações utilizadas para a execução de um comando SQL. 
Processos que executam SQLs idênticos compartilham informações da 
mesma área. Essa estrutura compartilhada e relativa a um determinado 
47
comando SQL é também denominada cursor ou Context Area. Além de 
comandos SQL, blocos escritos em PL/SQL também são alocados aí.
Por meio de um algoritmo denominado Hash, o comando é colocado em 
uma Shared SQL Area e obtém um identificador único, chamado de Hash 
do comando, de modo que comandos exatamente iguais possuem Hash 
iguais e podem compartilhar uma mesma área.
Então, o Cursor, com seu Hash, contém:
• O Texto dos comandos SQL ou PL/SQL.
• A forma analisada (parsed form) dos comandos SQL ou PL/SQL.
• O plano de execução (execution plan) para os comandos SQL ou PL/
SQL.
Data Dictionary Cache
Também referenciado como Row Cache, é a estrutura em memória que 
contém partes do Dicionário de Dados (DICT), possuindo, portanto, entre 
outras informações (que são metadados):
• Nomes de tabelas e visões.
• Nomes e DataTypes das colunas dessas tabelas.
• Privilégios dos usuários.
DataBase Buffer Cache
É a parte da SGA que contém cópias dos blocos de dados (DataBlock) 
lidos dos arquivos em disco. Todos os usuários conectados ao sistema 
compartilham globalmente essa estrutura.
48
O estabelecimento do seu tamanho depende do parâmetro DB_BLOCK_
SIZE, o qual reflete o tamanho de um bloco do SGBD. É opcional no 
momento da criação do banco de dados, já que o SGBD assume um 
tamanho default específico para o sistema operacional. Para o HP-UX, 
por exemplo, o valor default é 4 (2048 bytes) e o máximo é 8 (4096 bytes).
O tamanho em bytes da estrutura DataBase Buffer Cache é determinado 
pelo produto de DB_BLOCK_SIZE e DB_BLOCK_BUFFERS, sendo este o 
parâmetro que determina o número de blocos do DataBase Buffer Cache.
Ao acessar os dados, o Server Process realiza um Cache Miss, se há a 
necessidade de ler fisicamente um DataBlock desde o arquivo em que 
está gravado, ou um Cache Hit, caso aconteça a leitura lógica de um 
DataBlock, que possa ser localizado já no DataBase Buffer Cache.
Uma vez que o DataBase Buffer Cache possui um tamanho limitado, 
é improvável que todos os dados dos discos caibam nele, causando, 
portanto, seu total preenchimento de forma frequente. Quando cheio, 
dados que já foram manipulados (designados como Dirty Blocks) 
devem ser rescritos para os discos, de modo a liberar espaço para 
novas leituras. Novos acessos a dados que já foram escritos para disco 
provocam Cache Misses.
Uma estrutura adicional é organizada em forma de duas listas: a Dirty 
List e a Least Recently Used List (LRU). A primeira contém os blocos 
alterados que ainda não foram escritos em disco; enquanto a segunda 
contém blocos do SGBD que foram alterados pelos comandos dos 
usuários, mas ainda não foram gravados em disco, além de blocos livres 
e blocos em uso (que estão servindo para leituras).
Assim, quando um Server Process precisa ler um DataBlock:
1. Pesquisa nas listas LRU e dirty list pelo bloco de dados desejado.
2. Se o bloco foi localizado, realiza sua Leitura Lógica.
49
3. Caso esse bloco de dados não seja localizado, pesquisa a lista LRU 
em busca de um bloco livre.
4. Em seguida, move os blocos alterados encontrados na lista LRU 
para a dirty list, ou seja, movimenta-os para a lista de blocos 
alterados ainda não gravados nos arquivos de dados, de acordo 
com a localização de cada um deles, durante o processo de 
pesquisa de um bloco livre.
5. Se não houver blocos livres o suficiente no DataBase Buffer Cache, 
promove a escrita de Dirty Blocks ou simplesmente “despreza” os 
que foram utilizados só para leitura, conforme os mais “antigos”, 
identificados por meio da LRU. Assim, “cria” espaço suficiente 
(blocos livres) para a leitura necessária.
6. Por fim, efetua uma cópia dos blocos de dados necessários à 
leitura dodisco para os blocos livres (os que foram determinados 
nos passos 3 a 5).
Esse procedimento termina quando o processo servidor localiza um 
bloco livre, ou se um número específico de blocos for pesquisado sem 
encontrar um único bloco livre.
Conforme pode ser observado no passo 5, o SGBD deve gravar os 
blocos alterados da Dirty List para os arquivos em disco, a fim de liberar 
espaço em memória para os novos blocos de dados que precisam ser 
manipulados pelos comandos dos usuários. A obtenção desses blocos, 
desde o disco, representa uma Leitura Física.
ReDo Log Buffer
Este buffer é criado como um registro de eventos (journal), uma linha 
para cada item de transação, e é utilizado de forma circular, ou seja, 
dados são depositados aí e então remetidos para arquivos específicos 
em disco, dos quais não retornam, salvo quando ocorre uma operação 
de recuperação (Recovery) da SID. Tais dados são colhidos das alterações 
processadas contra o DataBase.
50
O ReDo Log Buffer participa das atividades de:
• Registro da imagem anterior e posterior (before image & after 
image) das alterações, seus respectivos números de transação 
e eventual confirmação (commit) ou desistência (rollback). Esses 
registros são conhecidos como entradas de log (ReDo log entries).
• Reconstrução do DataBase durante um Recovery.
LOG_BUFFER é o parâmetro que especifica o número de bytes destinados 
ao ReDo Log Buffer na SGA. O seu tamanho default é quatro vezes o 
tamanho do bloco de dados (DB_BLOCK_SIZE).
Large Pool
Esta implementação do SGBD permite um gerenciamento mais apurado 
da SGA, sendo uma área de memória que pode ser utilizada para 
compensar grandes alocações de Packages, no uso de Multi Threaded 
Server, ou do Recovery Manager. É primariamente utilizada como 
acessória às alocações que “congestionam” a Library Cache.
O parâmetro LARGE_POOL_SIZE determina o tamanho em bytes dessa 
alocação de memória; seu valor default é zero.
2. Tuning da SGA (Ferramentas de Análise)
Shared Pool
Pelo que estudamos a respeito de cursor (Share SQL Area ou Context 
Area), podemos deduzir que essa estrutura consome espaço de memória 
e tempo para ter certas informações incluídas, especialmente desde 
a ação do optimizer. Haverá centenas, milhares, talvez milhões delas 
compartilhando ou tentando compartilhar o espaço. Essa concorrência, 
51
ou melhor, prover o espaço conveniente para essa concorrência, 
constituirá uma tarefa de tuning significativa.
Assim, para tirar proveito das estruturas do SGBD que possibilitam o 
compartilhamento dos comandos SQL (seus identificadores e textos, 
obtidos pela função Hash), para que sejam executados mais rapidamente 
por meio da diminuição do tempo de análise, simplesmente devemos 
escrever códigos genéricos a partir de procedures, functions, packages e 
triggers que possam ser compartilhados entre os diversos programas 
que demandam dados e operações com dados do SGBD (APPs).
Será muito útil desenvolver padrões para os comandos SQL e PL/SQL 
por meio de regras simples, como a utilização de letras maiúsculas e/
ou minúsculas e nomes consistentes para as variáveis utilizadas nos 
comandos. A “aparência” do comando, como quantidades de espaços 
ou tabulações, ou ainda avanços de linhas, fazem coincidir os bytes que 
formam o texto dos comandos; portanto, não são “mera aparência”, mas 
medida de tuning significativa.
Quando a estrutura Shared Pool se torna completamente preenchida, 
os objetos mais antigos são retirados para liberar espaço para novas 
requisições. Como eles dificilmente possuem o mesmo tamanho, isso 
tende a causar uma fragmentação que, eventualmente, impede a carga 
em memória de objetos, resultando no erro ORA-4031: unable to allocate 
x bytes of shared memory. Tal situação pode ser resolvida com o comando 
ALTER SYSTEM FLUSH SHARED POOL.
É possível fixar explicitamente na memória os objetos muito acessados 
e marcá-los de forma que permaneçam na Shared Pool enquanto a SID 
estiver ativa. Para tanto, usa-se o procedimento KEEP da package DBMS_
SHARED_POOL (ORACLE® DATABASE, 2019a, p. 14-22).
52
O gethitratio na v$librarycache determina a porcentagem de chamadas 
de parse que encontram comandos em memória (conforme comando 
adiante). Se essa taxa estiver abaixo de 90%, deverá ser aumentada.
• select namespace, gethitratio from v$librarycache;
A razão entre Reloads e PINS define a quantidade de perdas na Shared 
Pool, o que deve ser mantido abaixo de 1%. Do comando a seguir obtém-
se essa taxa, cujo resultado negativo também aponta para aumento da 
Shared Pool.
• Select sum(pins) Execuções, sum(reloads) “Perdas em Cache”, 
sum(reloads)/sum(pins) “taxa” From v$librarycache Where 
namespace= ‘SQL AREA’;
Outro componente da Shared Pool é a área reservada ao DICT. Logo, 
investigaremos também a taxa entre todos os getmisses e todos os gets 
(comando adiante), os quais devem ser mantidos em menos de 15%. 
Valores acima disto apontam também para um aumento da Shared Pool.
• Select parameter, sum(getmisses)/sum(misses) “taxa” From 
v$rowcache;
Database Buffer Cache
Uma Database Buffer Cache de tamanho considerado eficaz é aquela que 
apresenta uma taxa de 70% a 80%.
Mas taxa de quê? Adiante veremos a resposta. Primeiro, é preciso voltar 
para a explanação sobre essa estrutura e reler a seção toda, analisando 
se o volume de trabalho parece ou não extenso. Além disso, devemos 
anotar o que são leituras físicas e leituras lógicas.
Agora que voltamos e relemos, adiante as leituras físicas serão 
referenciadas como physical reads; leituras lógicas como consistent gets; 
53
e por db block gets a quantidade de blocos necessários ao processo de 
leitura.
Então, a taxa = (1–physical reads / (db block gets + consistent gets))*100.
Porém, isso não responde o principal: o que ela significa? Significa a 
quantidade localizada em memória a cada 100 DataBlocks procurados. 
Para visualizar essa informação, há a consulta que resulta um Hitratio, ou 
seja, a taxa de sucesso que estamos analisando:
SELECT SUM(DECODE(name, ‘consistent gets’,value,0)) Consistent,
SUM(DECODE(name, ‘db block gets’,value,0)) Dbblockgets,
SUM(DECODE(name, ‘physical reads’,value,0)) Physrds,
ROUND(((SUM(DECODE(name, ‘consistent gets’, value, 0))+
SUM(DECODE(name, ‘db block gets’, value, 0)) -
SUM(DECODE(name, ‘physical reads’, value, 0)) )/
(SUM(DECODE(name, ‘consistent gets’,value,0))+
SUM(DECODE(name, ‘db block gets’, value, 0))))
*100,2) Hitratio
FROM V$SYSSTAT;
Resumindo, a taxa desejada revela que entre 70% e 80% dos DataBlocks 
necessários para a satisfação dos comandos (e do optimizer) devem 
ser encontrados em memória. Atenção: não é 70% a 80% do total de 
DataBlocks existentes no SGBD, mas sim entre 70% e 80% dos DataBlocks 
necessários para a satisfação dos comandos (e do optimizer).
A impossibilidade de aproximar-se da taxa ideal pode acontecer em 
função do comportamento excepcional de uso de determinadas tabelas. 
Teremos que procurar por tabelas e índices que se encaixam nos pools 
descritos adiante e então alterá-los, ligando a cada um deles:
• Keep: área utilizada para manter objetos em memória que 
provavelmente serão reutilizados. Manter esses objetos no 
Database Buffer Cache reduzirá operações de I/O.
54
• Recycle: área utilizada para eliminar blocos da memória que 
provavelmente não serão reutilizados.
• Default: essa área sempre existe e é equivalente a um único 
Database Buffer Cache.
Tais áreas são definidas pelo parâmetro BUFFER_POOL_<nome>, que 
indica seu tamanho e a quantidade de latches LRU alocados para o buffer 
pool. O mínimo de buffers que devem ser alocados para cada buffer pool 
é 50 vezes o número de latches LRU. Por exemplo, se uma área possui 10 
latches de LRU, ela deve possuir pelo menos 500 buffers.
Segundo o Database Performance Tuning Guide (2019a, p. 8-15), “Latches 
são mecanismos de serialização simples e de baixo nível para proteger 
estruturas de dados compartilhadas na SGA”.
ReDo Log Buffer
Em máquinas com processadores rápidose discos lentos, a velocidade 
que os processos servidores gravam o ReDo Log Buffer é muito maior que 
a velocidade que os processos LGWR gravam seu conteúdo para os ReDo 
Log Files. Por esse motivo, grandes ReDo Log Buffer tendem a permitir 
que os Server Process não fiquem esperando por entradas de log.
Tal espera (contenção) deve aproximar-se de zero, situação que 
verificaremos com a consulta adiante. Se o resultado for diferente do 
esperado, deve-se aumentar o espaço utilizado.
Select sid, event, seconds_in_wait, state From v$session_wait
Where event=‘log buffer space’;
Mesmo chegando muito perto de zero, pode ocorrer concorrência, ou 
seja, mais de um Server Process requisita registrar dados no ReDo Log 
Buffer ao mesmo tempo, o que causará novas tentativas (retries) daquele 
que não obteve sucesso. O valor do evento redo log buffer allocation 
55
retries deve ser próximo de zero e sua quantidade não deve ser maior 
que 1% do redo entries. Obteremos esses valores da consulta a seguir.
Selectname, value From V$SYSSTAT
Where name in (‘redo buffer allocation retries’, ‘redo entries’);
O aumento do ReDo Log Buffer tende a melhorar esse desempenho.
Checkpoint
A ideia de Checkpoint vai muito mais que a existência de um processo 
background assim designado, o CKPT.
Trazemos esse conceito no final do material em função de toda a 
abrangência desse evento. Ele é um evento que envolve uma das 
características mais pungentes sobre um SGBD, a qual não repousa em 
velocidade nem em capacidade de manipular quantidades de dados 
que são, a partir da “era da internet”, facilmente orçadas em bilhões de 
linhas em centenas ou milhares de tabelas.
Antes de olharmos para o próximo parágrafo, voltemos ao anterior a fim 
de relê-lo. Então, pensemos na resposta à questão: se a característica 
mais importante sobre SGBDs não é sua velocidade nem sua capacidade 
de manipular grandes quantidades de dados, qual seria?
Para ser contemporâneo, podemos chamá-la de resiliência (a 
capacidade de se recuperar de um tombo), mas tecnicamente chama-
se recuperabilidade. Ela é frequentemente “vendida” por meio da 
existência de conjuntos de computadores que assumem a função um 
do outro em caso de crash (cluster), como ocorre a respeito de áreas de 
armazenamento, organizadas em storages, sejam elas em disco ou nos 
modernos chips, gigantescos pen-drives.
Mesmo o cluster mais eficaz não vai recuperar aquela transação que 
havia acabado de ser efetivada no outro computador, e é isso que o 
56
Checkpoint permite. Na era do comércio eletrônico, essa característica é 
fundamental. Construindo esse conhecimento de trás para frente, cada 
Checkpoint assegura que o resultado de cada transação efetivada (após o 
commit) esteja escrito de um modo recuperável e de forma rápida.
A cada Checkpoint, o conteúdo do ReDo Log Buffer será gravado nos 
ReDo Log Files, que serão direcionados para a execução dos Archives. 
Do mesmo modo, os Dirty Blocks que estão no DataBase Buffer Cache 
são copiados para disco, contenham eles alterações efetivadas ou não. 
A dirty list e a LRU são atualizadas com essa informação. Enquanto 
todos esses eventos ocorrem, os demais processos da SGA ficam em 
contenção (ficam parados), esperando a conclusão do Checkpoint.
O Checkpoint ocorrerá a cada volume de dados escrito em ReDo Log 
Buffer, o que é determinado pelo parâmetro LOG_CHECKPOINT_
INTERVAL, ou a cada intervalo de tempo, segundo LOG_CHECKPOINT_
TIMEOUT. Além dessas medidas, é possível optar por criar transações 
(conjuntos de DML) menores ou não gerar recuperabilidade. Mas 
atenção, pois o risco é alto.
Em todo caso, será possível perceber que as opções coincidem 
com atividades de carga de dados, desde fontes potencialmente 
recuperáveis. Esta é a lista das possibilidades:
• Load com Direct Path sem arquivamento.
• Load com Direct Path utilizando arquivamento em modo 
NOLOGGING.
• Load (Insert) com Direct Path em modo NOLOGGING.
• NOLOGGING em comandos SQL.
• SQL*Loader e o modo NOLOGGING.
• A opção NOLOGGING é:
• Aplicável a tabelas, índices e tablespaces.
• Não registra alterações dos dados no log buffer.
• É especificada nos comandos CREATE ou ALTER.
57
Referências Bibliográficas
DATE, Christopher J. Introdução a sistemas de bancos de dados. 8. ed. Rio de 
Janeiro: Elsevier, 2004.
ELMASRI, Ramez; NAVATHE, Shamkant B. Sistemas de banco de dados. 7. ed. São 
Paulo: Pearson Education, 2019.
ORACLE® DATABASE. Database Concepts. 18c. E84295-04. Oracle Corporation, 
2018. Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/cncpt/database-concepts.pdf. Acesso em: 24 jun. 2020.
ORACLE® DATABASE. Database Performance Tuning Guide. 18c. E83719-04. 
Oracle Corporation, 2019a. Disponível em: https://docs.oracle.com/en/database/
oracle/oracle-database/18/tgdba/database-performance-tuning-guide.pdf. Acesso 
em: 24 jun. 2020.
ORACLE® DATABASE. Database Reference. E83773-08. Oracle Corporation, 2020. 
Disponível em: https://docs.oracle.com/en/database/oracle/oracle-database/18/
refrn/toc.htm. Acesso em: 14 ago. 2020.
ORACLE® DATABASE. SQL Language Reference. 18c. E85457-05. Oracle 
Corporation, 2020. Disponível em: https://docs.oracle.com/en/database/oracle/
oracle-database/18/sqlrf/sql-language-reference.pdf. Acesso em: 5 ago. 2020.
ORACLE® DATABASE. SQL Tuning Guide. 18c. E84296-04. Oracle Corporation, 
2019b. Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgsql/sql-tuning-guide.pdf. Acesso em: 24 jun. 2020.
SILBERSCHATZ, Abraham; KORTH, Henry F.; SUDARSHAN, S. Sistema de banco de 
dados. 6. ed. Rio de Janeiro: Elsevier, 2012.
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/database-concepts.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/database-concepts.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guid
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guid
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/toc.htm
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/toc.htm
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/sql-language-reference.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/sql-language-reference.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf
58
Memória física: Criação e Tuning
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
Objetivos
• Analisar a questão da leitura e escrita em um 
computador (I/O).
• Explorar a organização das estruturas que 
influenciam diretamente o I/O em um Sistema 
Gerenciador de Banco de Dados (SGBD).
• Examinar e influenciar o desempenho do SGBD, a 
partir do estabelecimento de um cenário ideal de I/O.
59
1. Leitura e Escrita: I/O
Este Tema se inicia com duas perguntas:
• O que faz mesmo um computador?
• Pensando que um computador é uma “máquina” com potencial 
de obter dados (entradas, leituras, Input), realizar operações com 
esses dados (processamento) e registrar os resultados dessas 
operações (saída, escrita, Output), e pensando ainda o quanto 
nos sentimos irritados quando as respostas não são imediatas, 
qual a importância do I/O em nosso cotidiano e – como estamos 
estudando tuning – qual a importância do I/O para o bom 
desempenho dos sistemas de informação da instituição que 
dependem deles?
Respondendo, depois que a Unidade Central de Processamento (Central 
Processing Unit – CPU) recebe, em memória RAM, todos os dados 
necessários, cumpre muito rapidamente as tarefas de processamento 
demandadas, sejam cálculos, modificações ou comparações. O que 
afirma-se aqui é que, do tempo total de processamento (de qualquer 
tarefa que qualquer computador receba, a qualquer momento), quase 
tudo é consumido por atividades de I/O.
Então, se as consultase a memória RAM do SGBD estiverem 
funcionando da melhor maneira possível, restará obter uma melhoria 
do I/O. Um bom desempenho de I/O está ligado diretamente a uma boa 
organização das estruturas que devem ser gravadas fisicamente.
1.1 Disco e Storage
Estar gravado fisicamente significa ser mantido magneticamente ou 
eletronicamente registrado, armazenado, seja em um disco físico 
unitário, como costuma ocorrer em um LapTop (notebook, ainda que 
60
existam alguns desses aparelhos com SSDs); seja em uma Unidade 
de Estado Sólido (Solid-State Drive – SSD), como um pen drive ou um 
aparelho celular; ou em um conjunto, habitualmente empresarial, 
com capacidades enormes (usualmente com muitos tera ou mesmo 
petabytes), composto por discos físicos ou SSDs ou com ambos os tipos 
misturados, denominado Storage. 
Portanto disco e Storage são utilizados como se fossem sinônimos entre 
si, estando relacionados diretamente com “estar gravado fisicamente”. O 
SGBD Oracle denomina o conjunto de estruturas físicas como Banco de 
Dados, ou DataBase (ORACLE® DATABASE , 2018). 
1.2 Estruturas físicas do SGBD (Segmentos ou Segments)
No nível do Sistema Operacional do Servidor (computador) que abriga 
o SGBD, encontraremos arquivos, organizados em blocos de dados, 
sendo chamados de “estrutura física”. Internamente ao SGBD, portanto 
na “estrutura lógica”, tal organização é entendida desde Tablespaces, 
Segmentos, Extensões e Blocos Oracle (Oracle Block).
Pode-se entender tal conjunto como “hierarquia de armazenamento do 
banco de dados”. Esse último termo é um superconjunto agregado sob 
uma mesma SID.
Segmentos são o termo utilizado por estruturas em Storage, com 
finalidades distintas, como tabelas, índices etc. São registrados 
logicamente em uma estrutura denominada “espaço de tabela” ou 
Tablespace. Cada Tablespace agrega um conjunto, unitário ou múltiplo, 
de arquivos (files) que são efetivamente existentes ao nível do Sistema 
Operacional.
61
1.3 Organização do SGBD
Ao realizar a instalação do SGBD, a ela se destinará um certo volume de 
ocupação em Storage, e teremos então uma primeira oportunidade 
de ganho de performance. Um Oracle Block, também chamado de 
DataBlock, é um conjunto de bytes interpretado pelo SGBD, constituído 
de um conjunto de Blocos do Sistema Operacional. Usualmente, 
podemos solicitar ao administrador que crie o Storage com a mesma 
dimensão de blocos que o DataBlock, a fim de economizar, desse modo, 
uma operação de agrupamento a cada leitura ou escrita executada 
futuramente (e serão milhares e milhares a cada instante).
Para atender a uma consulta, um ou mais segmentos serão utilizados. 
Os dados estão guardados em tabelas (Segmento de Dados ou Data 
Segment) e seus índices em (Index ou Interim Segments). Eventuais 
organizações demandarão o uso de segmentos de “rascunho” dessa 
operação, sendo, portanto, temporários (Temporary Segment). Quando 
se tratar de uma escrita (DML), segmentos que permitem a desistência 
da manipulação, dando consistência à transação, serão escritos (Rollback 
Segment), além daqueles que permitirão a eventual recuperação (ReDo Log).
Assim, a leitura de um dado constantemente exige a leitura dos índices 
relativos às tabelas em que estão depositados. A organização da 
resposta demanda eventualmente o uso de segmentos temporários. 
Quando tudo isso estiver pronto, uma escrita, na forma de uma 
inserção, alteração ou eliminação de linhas (Insert, Update, Delete; DML, 
portanto), pode ser requisitada, provocando o uso de segmentos de 
desistência (Rollback).
Em um intervalo de tempo muito pequeno, dados de diversas tabelas e 
índices, segmentos temporários e de Rollback sofrem leitura e escrita. 
Se todo esse I/O ocorrer em um único arquivo, ou mesmo em arquivos 
depositados em uma área de Storage com um endereçamento igual, 
haverá uma espera maior que a necessária por esses recursos.
62
Para uma segunda oportunidade de ganho de performance, devemos 
distribuir segmentos de natureza diferente em Tablespaces diferentes 
e os arquivos de dados (Datafiles) destas em áreas de Storage distintas 
(Logical Units – LUNs).
2. Tuning do I/O (Ferramentas de Análise)
A criação de cada estrutura adiante, desde as duas oportunidades de 
ganho de performance já esclarecidas, oferecerá oportunidades de 
ganho adicionais.
2.1 Blocos de Dados (DataBlock) e extensões (Extents)
Os blocos de dados formam as estruturas lógicas de menor nível em um 
banco de dados. Sempre corresponderão a um número específico de 
bytes guardados fisicamente em disco.
Cada bloco conterá (ORACLE® DATABASE, 2018, p. 12-10) um cabeçalho 
(header) que identificará o objeto (segmento ou segment) ali contido. Esse 
header terá uma porção fixa e uma variável, com indicação de eventuais 
transações ativas nesse bloco. Antes dos dados em si, o DataBlock 
conterá também um diretório de linhas, um diretório de tabelas, 
uma indicação de “espaço reservado para crescimento das linhas” 
(denominado PCTFREE) e uma outra indicação (PCTUSED), a qual indica 
o limite abaixo do qual o bloco está apto a receber novas inserções. Só 
então aparece o cabeçalho de cada linha e coluna, com os respectivos 
dados.
As extensões, próximo nível de estruturação lógica de um banco, após os 
blocos de dados, são um número específico de blocos contíguos, obtidos 
em uma simples alocação, usados para guardar um tipo específico de 
informação (Data, Index, Rollback, Temporary). Cada tipo de segmento 
63
compreende uma ou mais extensões, e, quando o espaço existente é 
completamente preenchido, o SGBD aloca uma nova extensão para ele. 
A cláusula STORAGE, usada quando da criação dos segmentos, determina 
a forma como o SGBD aloca as extensões.
Seja qual for o tipo, cada segmento em um banco de dados é 
inicialmente criado com pelo menos uma extensão, a qual é chamada 
de extensão inicial (initial extent). Entretanto, os segmentos de rollback 
são uma exceção a essa regra, pois sempre possuem pelo menos duas 
extensões.
Quando um objeto é criado, o seu segmento contém essa extensão 
inicial e ocupa efetivamente esse espaço em disco, ainda que nenhuma 
utilização tenha sido efetuada. Se por um lado essa reserva inicial de 
espaço é benéfica para a performance ao evitar atividades de I/O cada 
vez que se verifica um crescimento de espaço ocupado; por outro, certas 
atividades de backup consumirão tempo e I/O, copiando “o vazio”.
Quando esse objeto necessitar de um espaço maior que o inicial, o 
SGBD aloca uma nova extensão para o segmento, calculada a partir do 
parâmetro PCTINCREASE, que foi informado na criação desse objeto. 
Para propósitos de manutenção, cada segmento em um banco de dados 
contém um bloco de cabeçalho que descreve as suas características e 
a lista de diretórios das extensões do segmento, sendo ele o primeiro 
bloco da primeira extensão.
O SGBD controla a alocação das extensões de um segmento da seguinte 
maneira (ORACLE® DATABASE, 2018, p. 12-22~26):
1. Procura um espaço livre no tablespace que contenha o segmento. 
Esse espaço livre é considerado como o primeiro conjunto de 
blocos de dados livres e contíguos que constituem uma extensão 
incrementada por um valor ou com o mesmo tamanho da 
extensão anterior. 
 
64
Para localizar esse espaço livre, o SGBD pesquisa por um grupo 
de blocos de dados contíguos do mesmo tamanho da nova 
extensão a ser alocada, arredondado para reduzir a fragmentação 
interna. Por exemplo, se 19 blocos de dados forem necessários, 
ele procura exatos 20 blocos de dados contíguos, sempre 
arredondando para múltiplos de 5. 
 
Porém, se nenhum espaço correspondente é encontrado, o SGBD 
procura por um grupo de blocos de dados contíguos que seja 
maior ou igual ao tamanho necessário. Com a localização deste 
com pelo menos 5 blocos a mais do que o tamanho necessário 
para alocar a nova extensão, o conjunto é quebrado em extensões 
separadas, com uma delas do tamanho adequado e esperado. 
 
Com a localização de um grupo de blocos contíguos maior queo 
tamanho necessário, mas menor que o limite de 5 blocos, como 
comentado anteriormente, o SGBD aloca todos os blocos de dados 
contíguos. Se não há a localização por parte do SGBD de um grupo 
de exatos 20 blocos de dados (usados como exemplo), ele procura 
então por um grupo de blocos contíguos maior do que 20. 
 
Se o primeiro conjunto de blocos de dados localizado possui 25 
blocos ou mais, o SGBD para a alocação da nova extensão. Caso 
esse conjunto contenha entre 21 e 24 blocos, ele aloca todos os 
blocos de dados sem terminar a alocação. 
 
Se o maior conjunto de blocos de dados contíguos não é 
localizado, o SGBD une quaisquer blocos de dados livres e 
adjacentes no tablespace (o processo em background SMON 
periodicamente realiza a união dos espaços livres adjacentes, 
processo denominado coalesce). Depois da união dos blocos de 
um tablespace, novamente o SGBD procura por um grupo de 
blocos contíguos. Um erro é retornado se uma extensão não 
65
puder ser alocada nessa segunda vez. 
2. Uma vez que o SGBD tenha localizado o espaço livre necessário 
no tablespace, uma porção desse espaço, que corresponde ao 
tamanho da extensão incrementada, é alocada para ela. Se o 
SGBD encontra um espaço livre maior do que o necessário para a 
extensão, o restante do espaço não é utilizado, a não ser que seja 
menor do que 5 blocos. 
3. Finalmente, o cabeçalho do segmento e o dicionário de dados 
(DICT) são atualizados para mostrar a nova extensão alocada.
Geralmente as extensões de um segmento não são liberadas para o 
tablespace até a desalocação do segmento como um todo, como por 
meio de comandos DROP TABLE e DROP CLUSTER. O fato de o dono 
de uma tabela ou cluster, ou de qualquer usuário com o privilégio de 
DELETE ANY, usar o comando TRUNCATE ou, ainda, o fato de o SGBD 
periodicamente desalocar uma ou mais extensões de um segmento de 
rollback são exceções a essa regra.
Quando uma extensão é liberada, o dicionário de dados (DICT) é 
atualizado para refletir esse evento. Dessa forma, ele mostra que o 
espaço antes utilizado para alocar a extensão pode ser reutilizado para 
alocar novas extensões.
Todas as extensões alocadas para um segmento de índice permanecem 
nessa situação durante toda a sua vida útil. Somente quando um índice 
é eliminado elas podem ser utilizadas para outros fins pelo banco de 
dados.
Periodicamente, o SGBD verifica se o crescimento dos segmentos de 
rollback ultrapassam o tamanho ideal (o termo “ótimo” é originalmente 
utilizado ou optimal) e de modo automático retira uma ou mais 
extensões do segmento. Quando um comando que requer um segmento 
66
temporário finaliza a sua execução, o segmento é automaticamente 
removido, e suas extensões alocadas temporariamente são devolvidas 
para o tablespace que contém o segmento.
Cada segmento é definido por diversos parâmetros de armazenamento 
expressos em termos de extensões. Esses parâmetros aplicam-se a 
todos os tipos de segmentos e controlam como o espaço do banco de 
dados é alocado para cada um.
Fizemos essa longa descrição para evidenciar que um segmento mal 
dimensionado causa, ao movimentar-se, ou seja, crescer ou diminuir, 
uma Alocação Dinâmica, que, analogamente ao fenômeno de paginação 
da RAM do Sistema Operacional, esconde atividade de I/O, “roubando”, 
portanto, a performance do SGBD.
Para evitar essa perda de performance, devemos dimensionar os 
objetos apropriadamente:
a. Determinando o tamanho máximo do objeto.
b. Escolhendo os valores de storage corretos para que a alocação 
dinâmica não seja necessária.
Quando determinarmos o tamanho do segmento, devemos prever um 
percentual de erro para quantidades não previstas. O problema de criar 
grandes extents é que existe uma maior probabilidade de desperdício e 
falta de espaço no tablespace.
Grandes extents, como regra geral, são mais recomendados que os 
pequenos. A partir dos comentários a seguir, veremos as vantagens e 
desvantagens:
• Grandes extents diminuem sua possibilidade de alocação dinâmica, 
uma vez que as linhas das tabelas caberão em um único extent.
67
• Existe um ganho de performance, uma vez que o SGBD necessita 
fazer um único acesso aos extents, ao invés de diversas leituras 
multiblocos.
• A performance de buscas por meio de índices não é afetada se 
possuírem um ou mais extents.
• Um mapa de extents é gerado para verificar se todos eles 
correspondem a um segmento. Quanto menor for esse mapa, 
mais rápida fica a busca de uma informação dentro de uma tabela.
• Em contrapartida, como o extent necessita de alocação contígua, 
pode haver falta de espaço dentro do tablespace, causando 
desperdício de espaço.
2.2 Arquivos de dados (DataFiles e Tablespaces)
Um tablespace é usado para agrupar outras estruturas lógicas 
relacionadas entre si e, dessa forma, organizar o banco de dados. Por 
exemplo, geralmente os tablespaces agrupam todos os objetos de uma 
aplicação para simplificar certas tarefas administrativas.
Um ou mais arquivos de dados são criados explicitamente para cada 
tablespace, ou seja, cada um é formada por um ou mais arquivos 
no sistema operacional. Portanto, os dados de um tablespace são 
armazenados coletivamente nos arquivos de dados que o formam.
Quando um objeto é criado, como uma tabela ou índice, o seu segmento 
é criado também em um tablespace específico. O segmento de um objeto 
aloca espaço em somente um tablespace, e o tamanho combinado 
dos arquivos de dados que o formam ditam a sua capacidade total de 
armazenamento. A combinação da capacidade de armazenamento de 
todos os tablespaces que formam um banco forma sua capacidade total 
de armazenamento.
68
É altamente recomendável manter um padrão para os nomes dos 
arquivos físicos que os formam. Assim, cada nome deveria seguir 
a convenção TN.DBF, em que T representa o nome do tablespace 
(geralmente de até seis caracteres) e a letra N representa um número (1, 
2, 3 etc.).
Por exemplo, um bom nome para os arquivos do tablespace SYSTEM é 
SYSTEM01.DBF e SYSTEM02.DBF. Um tablespace pode estar acessível 
ou não (dizemos comumente que pode estar ON-LINE ou OFF-LINE), 
mas geralmente permanece ON-LINE, disponível para os usuários. 
Entretanto, para algumas tarefas administrativas ou em algumas 
situações especiais, é possível deixá-lo OFF-LINE. Elas são usadas para:
1. Controlar a alocação do espaço em disco para os dados de um 
banco de dados.
2. Assinalar quotas específicas de espaço para os usuários.
3. Controlar a disponibilidade dos dados.
4. Executar operações parciais de cópia e recuperação de um banco 
de dados.
5. Alocar o armazenamento dos dados entre diferentes dispositivos 
de armazenamento para melhorar a performance do banco de 
dados.
Podemos criar tablespaces; adicionar e remover arquivos de dados; 
e configurar e alterar os parâmetros de armazenamento para os 
segmentos criados, além de ser possível removê-los sem maiores 
problemas. Cada SGBD contém um tablespace chamado SYSTEM, 
formado automaticamente na criação do banco, apresentando os dados 
das tabelas do dicionário de dados (DICT).
Um banco de dados pequeno pode ter apenas o tablespace SYSTEM; 
entretanto, é recomendável que pelo menos um outro seja criado para 
armazenar os dados dos usuários separados das informações do DICT. 
Isso permite mais flexibilidade em várias tarefas administrativas e pode 
69
reduzir a contenção (acesso simultâneo aos objetos por processos de 
diversos usuários) entre os objetos do dicionário de dados e os objetos 
dos esquemas dos usuários. Esse tablespace precisa sempre estar 
disponível (ON-LINE) para o banco.
A contenção em disco pode ser minimizada por meio da separação 
dos grupos de segmentos mais acessados em tablespaces diferentes. 
Devemos considerar a possibilidade da separação dos segmentos do 
dicionário de dados de todos os outros segmentos, assim como os de 
rollback e os de dados de seus segmentos de índices correspondentes. 
Um cuidado extra deve ser tomado com os arquivos de redo log, já que 
geralmente são elesque causam os mais sérios problemas de contenção 
em disco.
A separação dos segmentos também precisa seguir alguns critérios 
que, quando aplicados, podem melhorar os procedimentos de cópia 
e recuperação do banco de dados, além de permite-nos uma maior 
clareza na organização e uma garantia extra de segurança para os 
dados. Esses critérios podem ser resumidos em:
• Separar segmentos com diferentes necessidades de backup. Essa 
técnica permite melhor clareza, segurança e funcionalidade para 
os procedimentos de cópia do banco de dados, bem como facilita 
uma possível recuperação.
• Separar segmentos de acordo com a disponibilidade dos dados 
para os usuários. Com a aplicação dessa técnica, é possível 
proibirmos o acesso a determinadas aplicações, somente tornando 
OFF-LINE o tablespace que contém os objetos acessados por elas.
• Separar segmentos de acordo com o tempo de vida das aplicações 
que os utilizam. Caso exista alguma aplicação a ser desativada 
dentro de pouco tempo, o armazenamento dos segmentos 
acessados por ela em um tablespace deve ser considerado. 
70
Quando da necessidade de tirá-la de produção, basta removermos 
o tablespace.
Os arquivos de Controle e ReDo Log devem ser mantidos em cópias 
ON-LINE em discos diferentes (técnica conhecida como espelhamento). 
Além disso, os arquivos de controle nunca podem ser perdidos, e o uso 
de pelo menos dois deles em diferentes dispositivos físicos é altamente 
recomendado.
Também devemos prestar especial atenção nos arquivos de ReDo 
Log, visto que causam um dos mais sérios problemas de contenção 
nos discos. Por esse motivo, devem ser mantidos espelhados em 
diferentes dispositivos físicos, para melhorar não somente a contenção, 
como também para garantir uma maior segurança no caso de erro 
irrecuperável. É imprescindível a monitoração do banco de dados em 
busca de possíveis problemas de contenção em disco.
Por fim, a separação dos arquivos que compõem os tablespaces em 
diferentes discos e o transporte de segmentos muito acessados para 
outros tablespaces armazenados em dispositivos diversos, devem ser 
considerados.
As consultas a seguir fornecerão dados úteis para localizarmos as 
questões elucidadas aqui, a fim de corrigi-las. Vejamos o significado das 
informações:
• PHYRDS – Número de leituras físicas.
• PHYWRTS – Número de gravações físicas.
• PHYBLKRD – Números de blocos físicos lidos.
• PHYBLKWRT – Número de blocos físicos gravados.
• READTIM – Tempo gasto nas leituras.
71
• WRITETIM – Tempo gasto nas gravações.
• Select file_name, bytes From dba_data_files;
• Select file_name, bytes From dba_temp_files;
• Select tablespace_name, contents, extent_management, allocation_
type, plugged_inmanagement From dba_tablespaces;
• Select tablespace_name, file_name, bytes/1024/1024 MBytes_now, 
autoextensible, maxbytes/1024/1024 Mbytes_MAX, user_bytes 
From dba_data_files order by 1,2;
• Select substr(a.file#,1,3) “#”, substr(a.name,1,10) “FileSystem”, 
substr(a.name,11,60) “Namefile”, 
substr(df.tablespace_name,1,23) “TS NAME”, a.bytes/1024 “MB”, 
b.phyrds “RDS”, b.phywrts “WRTS” 
From v$datafile a, v$filestat b, dba_data_files df 
where (a.file#=b.file#) AND (df.file_id=a.file#) 
order by substr(a.name,1,10), substr(df.tablespace_name,1,23);
2.3 Tabelas (Tables)
Por meio da planilha BD_Tuning04~TableSizeEstimationProcedure.xlsx e 
dos slides, aprenderemos a organizar uma tabela e, principalmente, dar 
a ela dimensões apropriadas.
Quando uma linha cresce demasiado, o SGBD terá que dividi-la em mais 
de um bloco, ou transportá-la para outro, ainda que seu ROWID aponte 
para o DataBlock original. Em ambos os casos, um I/O adicional acaba 
acontecendo, e esse fenômeno é conhecido como linhas encadeadas 
(chinned rows).
72
Se isso acontecer com uma frequência muito alta, podemos indicar 
uma estimativa inicial inadequada, especialmente no PCTFREE, o que 
deveremos corrigir. O comando a seguir permitirá essa avaliação:
Select c.table_name, count(*) “Linhas Encadeadas”,
t.num_rows “Linhas da Tabela”,
decode(t.num_rows,0,0,((count(*)/(t.num_rows))*100))
“Percentual Encadeado”
From DBA_TABLES t, CHAINED_ROWS c
where t.table_name = c.table_name
group by c.table_name, t.num_rows 
 order by 4 desc;
Há duas possibilidades de estrutura de tabelas que costumam ser pouco 
exploradas.
• A primeira são tabelas organizadas sob a mesma estrutura que 
um índice balanceado, daí sua designação como Index Organized 
Tables (IOT). Elas têm essa organização restrita por meio de 
seu identificador único, e seu tamanho deve se restringir a um 
DataBlock. Essas restrições são compensadas pelo volume “a 
menor” de I/O estimado, que é de pelo menos 4 I/O, quando usada 
uma estrutura convencional, e somente 3 I/O, quando o acesso é 
feito em uma IOT.
• A segunda, é conhecida como particionamento (partitioning), e é 
uma estratégia útil quando com frequência somente uma porção 
de um grande volume de dados é utilizada por consulta.
Imaginemos um hipotético conjunto de dados a respeito dos 
contribuintes da receita federal. Cada consulta realizada parte de uma 
delegacia regional. Então, se a tabela com a região a que pertence o 
contribuinte estiver particionada por esse dado, somente esse pedaço 
(partição) provocará I/O.
73
2.4 Índices (Indexes)
A estrutura default de um índice é denominada árvore balanceada 
(B*Tree), a qual pode ser entendida como um organograma, de tal forma 
que um DataBlock deve ser lido para entender qual lado deve ser lido 
a seguir. Então, pelo menos uma aba (leaf) conterá um novo nível de 
divisão, apontando para um DataBlock que terá a relação de ROWIDs 
que conterão os dados. Um índice idealmente constituído demandará, 
portanto, três I/O para apontar para os dados.
Quando houver demora excessiva no acesso aos índices, será possível 
optar por criar estruturas diferenciadas, denominadas índices Bitmap, 
e com chave reversa. Esses índices são indicados em colunas que 
possuam uma quantidade limitada de valores e que compareçam 
frequentemente em consultas com cláusulas Where complexas.
Os índices de chave reversa são úteis quando em colunas que 
possuem uma grande quantidade de inserts sequenciais, como chaves 
primárias numéricas ou contadores. Como têm seus valores anotados 
ao contrário, o cliente de número de cadastro “100000234” será mais 
rapidamente localizado, pois no índice aparecerá como “432000001”.
Índices em tabelas voláteis podem causar sérios problemas de 
performance pelo fato de não existir alteração de valores em 
estruturas de índices. Toda vez que um comando UPDATE é executado, 
o SGBD remove a linha indexada e inclui uma nova. Além disso, o 
espaço liberado quando a linha do índice é removida nunca mais é 
reaproveitado.
Esse comportamento gera um fenômeno conhecido como 
fragmentação, que se reflete em excesso de I/O. Recomenda-se a 
reconstrução do índice, quando 20% dele estiver fragmentado. A 
sequência de comandos a seguir auxiliará no diagnóstico e em sua 
correção:
74
• ANALYZE INDEX ... VALIDATE STRUCTURE;
• SELECT (del_lf_rows_len/lf_rows_len)*100 “Index Usage” From 
index_stats;
• ALTER INDEX ... REBUILD;
2.5 Transações (UnDo)
Cada vez que um dado é escrito no SGBD, ele faz parte de uma 
transação. A estrutura que dá suporte às transações é denominada 
segmento de desistência (RollBack Segment – RBS). Para fornecer 
consistência de leitura, os RBS são lidos constantemente.
Assim, comandos de escrita e de leitura devem sempre encontrar 
suas imagens consistentes. Isso depende de número e tamanho de 
RBS apropriados, o que refletirá também na questão da contenção. As 
consultas adiante apoiarão essas averiguações (esperas devem tender a 
zero).
Select sum(waits)*100/sum(gets) Rateio, sum(waits), sum(gets)
From v$rollstat;
Select class, count From v$waitstat where class like ‘%undo%’;
Select value Form v$sysstat where name = ‘consistent gets’; 
Select sum(writes) Form v$rollstat;
Na criação do tablespace que dará suporte aos RBS, será útil informaro 
valor do INITIAL igual ao valor do NEXT e MINEXTENTS para 20, deixando 
o valor do parâmetro OPTIMAL ajustado para zero
2.6 Recuperabilidade (ReDo)
Um fortíssimo motivo pelo qual a instituição paga por um SGBD em 
vez de utilizar seu próprio sistema de arquivamento de dados, são os 
75
mecanismos de recuperabilidade que ele oferece. Esse mecanismo é 
implementado por meio da estrutura de memória ReDo Log Buffer, que 
registra os eventos de alteração dos dados e providencia sua gravação 
em ReDo Log Files (inscritos do DICT por meio de v$logfile e v$log). As 
melhores possibilidades de recuperabilidade existem quando a opção 
de arquivamento estiver acionada (SGBD em archivelog).
Para tanto, um grande volume de I/O é gerado, mas é essencial. O ajuste 
de performance dos ReDo Log Buffer é o caminho pelo o qual todo esse 
mecanismo será aferido.
2.7 Organização (Sort; Temporary)
Do mesmo modo que o uso do SGBD em archivelog é importantíssimo, o 
uso de operações de organização (Sort) são imprescindíveis e acontecem 
em estruturas denominadas temporárias (Temporary) e que existem 
tanto em memória quanto em tablespaces específicos.
Sorts são necessários quando se criam índices, e quando ocorrem 
consultas com as cláusulas e os operadores DISTINCT, ORDER BY 
e GROUP BY, UNION, INTERSECT ou MINUS, o que é efetivamente 
muito comum e raramente pode ser evitado. O optimizer também, 
de forma muito frequente, lança mão de operações de organização, 
particularmente na resolução de uniões (joins).
É recomendável que 95% das operações de Sort ocorram em memória, o 
que deve ser determinado reservando-se espaço suficiente e utilizando 
o parâmetro SORT_AREA_SIZE, o qual é possível verificar com a consulta:
Select name, value From v$sysstat
WHERE name IN (‘sorts (memory)’, ‘sorts (disk)’);
76
Para aqueles 5% restantes, será útil, ao criar o Tablespace Temporário, 
utilizar o INITIAL e o NEXT como valores, múltiplos e inteiros do SORT_
AREA_SIZE e PCTINCREASE zero.
Referências Bibliográficas
DATE, Christopher J. Introdução a sistemas de bancos de dados. 8. ed. Rio de 
Janeiro: Elsevier, 2004.
ELMASRI, Ramez; NAVATHE, Shamkant B. Sistemas de banco de dados. 7. ed. São 
Paulo: Pearson Education, 2019.
ORACLE® DATABASE. Database Concepts. 18c. E84295-04. Oracle Corporation, 
2018. Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/cncpt/database-concepts.pdf. Acesso em: 24 jun. 2020.
ORACLE® DATABASE. Database Performance Tuning Guide. 18c. E83719-04. 
Oracle Corporation, 2019a. Disponível em: https://docs.oracle.com/en/database/
oracle/oracle-database/18/tgdba/database-performance-tuning-guide.pdf. Acesso 
em: 24 jun. 2020.
ORACLE® DATABASE. Database Reference. E83773-08. Oracle Corporation, 2020. 
Disponível em: https://docs.oracle.com/en/database/oracle/oracle-database/18/
refrn/toc.htm. Acesso em: 14 ago. 2020.
ORACLE® DATABASE. SQL Language Reference. 18c. E85457-05. Oracle 
Corporation, 2020. Disponível em: https://docs.oracle.com/en/database/oracle/
oracle-database/18/sqlrf/sql-language-reference.pdf. Acesso em: 5 ago. 2020.
ORACLE® DATABASE. SQL Tuning Guide. 18c. E84296-04. Oracle Corporation, 
2019b. Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgsql/sql-tuning-guide.pdf. Acesso em: 24 jun. 2020.
SILBERSCHATZ, Abraham; KORTH, Henry F.; SUDARSHAN, S. Sistema de banco de 
dados. 6. ed. Rio de Janeiro: Elsevier, 2012.
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/database-concepts.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/database-concepts.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guid
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guid
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/toc.htm
https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/toc.htm
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/sql-language-reference.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/sql-language-reference.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf
https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf
77
BONS ESTUDOS!
	Sumário
	A estratégia de ajuste de performance como um método
	Objetivos
	1. Um método
	2. Estratégias do negócio da Instituição e do tuning
	Referências Bibliográficas
	As consultas ao Banco de Dados 
	Objetivos
	1. O Otimizador
	2. As fases de execução de um SQL 
	3. Junções de tabelas (joins) 
	4. Métodos de Acesso (caminhos) 
	5. Estatísticas 
	6. Visualização do Plano de Execução 
	7. Escolha do plano de execução 
	8. As dicas (Hints)
	Referências Bibliográficas 
	Conhecendo e adequando o desempenho das estruturas em memória
	Objetivos
	1. O uso da RAM pelo SGBD
	2. Tuning da SGA (Ferramentas de Análise)
	Referências Bibliográficas
	Memória física: Criação e Tuning
	Objetivos
	1. Leitura e Escrita: I/O
	2. Tuning do I/O (Ferramentas de Análise)
	Referências Bibliográficas