PLANILHA AF2 - FULL TABLE SCAN

Prévia do material em texto

WBA0474_v1.0
GERENCIAMENTO DO DESEMPENHO 
DO BANCO DE DADOS (TUNING) 
APRENDIZAGEM EM FOCO
2
APRESENTAÇÃO DA DISCIPLINA
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
Olá! Esta é a mais apaixonante disciplina que se relaciona à 
administração de sistemas gerenciadores de bancos de dados 
(SGBD). É apaixonante porque vai exigir que você lide com todos 
os aspectos da informática, desde sistemas operacionais a 
aplicações, desde comunicações entre máquinas (network, redes) 
até a comunicação com as pessoas envolvidas.
Você verá que ajustar a performance (fazer o tuning) de um 
SGBD se iniciará por entender as pessoas e as necessidades 
da instituição que esse SGBD serve. Então, analisar as diversas 
estruturas lógicas e físicas que estão em operação, saber medir 
seu desempenho e conhecer o caminho para a correção de 
eventuais problemas é o principal objetivo.
Dentre as estruturas lógicas, você encontrará os comandos 
que as linguagens e aplicações remeterão ao SGBD, solicitando 
informações ou alterando dados. Estão também aí os processos 
particulares do SGBD que o manterão em funcionamento, 
estabelecendo uma comunicação deste com o sistema 
operacional da máquina que abriga o SGBD. Por fim, as estruturas 
em memória que foram estabelecidas e serão usadas por esses 
processos. Nas áreas de armazenamento da instituição, você 
encontrará as estruturas físicas que serão lidas e escritas pelo 
SGBD.
Esse caminho de aprendizagem foi organizado em quatro temas:
• O primeiro tema será completado com você entendendo 
a estratégia de ajuste de performance como um método, 
3
analisando e aperfeiçoando as regras de negócio, 
adequando o uso das aplicações, o projeto dos dados e os 
recursos do banco de dados.
• Ao término do segundo tema, você terá examinado o 
desempenho de consultas que utilizam filtros. Terá também 
completado uma análise do otimizador e os planos de 
execução, o que permitirá medir o impacto do desempenho 
das consultas com índices, por meio desse otimizador.
• Completar o terceiro tema significará conhecer e saber 
adequar o desempenho das estruturas em memória.
• Ao saber utilizar estruturas físicas diferenciadas para 
conjuntos especiais de dados e conhecer cada uma das 
estruturas físicas, e como organizá-las, você terá cumprido o 
quarto tema.
Vamos aprender a fazer tuning! Bons estudos.
INTRODUÇÃO
Olá, aluno (a)! A Aprendizagem em Foco visa destacar, de maneira 
direta e assertiva, os principais conceitos inerentes à temática 
abordada na disciplina. Além disso, também pretende provocar 
reflexões que estimulem a aplicação da teoria na prática 
profissional. Vem conosco!
TEMA 1
A estratégia de ajuste de 
performance como um método
______________________________________________________________
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
5
DIRETO AO PONTO
Ajustar a performance, realizar o tuning de um SGBD significa 
muito mais do que apagar um incêndio quando alguém declara “tá 
lento”.
Você terá que adotar uma estratégia de ajuste de tuning e segui-
la de forma a não perder o rumo. Desse modo, apresenta-se 
um método que se fundamenta na experiência, assim como nos 
vastos documentos do fornecedor do SGBD escolhido como 
referência.
Tal referência, o SGBD utilizado para ilustrar as atividades de 
ajuste de performance é o Oracle®. Desde a sua versão 18, o 
termo autonomous aparece em sua designação, indicando uma 
grande capacidade de autoadequação de suas estruturas. Tal 
autonomia demanda, entretanto, um projeto adequado e um 
certo tempo de utilização, a partir da qual dados estatísticos 
podem ser recolhidos por seus algoritmos baseados em 
inteligência artificial (IA).
A partir dessas informações, você pode deduzir que, quanto 
melhor for o projeto inicial do SGBD, mais “autônomo” ele poderá 
ser. Ainda, é fácil concluir que a referida coleta de estatísticas 
demanda um determinado período de utilização, o qual pode ser 
longo demais. Além disso, há questões “fora da curva”, ou seja, 
momentos em que a informação estatística não dará conta de 
resolver um desempenho não aceitável.
Nos cases que você explorará na seção Teoria em Prática, esses e 
outros aspectos serão evidenciados.
Constantemente, será oportuno entender quais são as 
necessidades da instituição que detém o SGBD e como seus 
6
clientes, internos e externos, devem ser servidos. O conjunto de 
dados de um cartório de notas não tem exatamente as mesmas 
características que um site de vendas on-line.
Curiosamente, demonstra a experiência, dentre os diversos 
profissionais envolvidos no dia a dia do suporte às questões 
informacionais de uma instituição, é o DBA que concentrará o 
encaminhamento das situações que se podem configurar como 
problemas de performance.
Desse modo, você tem em mãos um roteiro de atividades que 
denominamos método tuning. Observe adiante o que se pode 
caracterizar como objetivos de ajuste. 
O método de tuning deverá garantir que:
1. Os comandos SQL acessem o menor número possível de 
blocos de dados.
2. Os blocos de dados a serem acessados devem já estar 
disponíveis na memória.
3. Os usuários precisam compartilhar o mesmo código ao 
demandarem dados iguais.
4. Quando um código armazenado (Stored Procedures) for 
requisitado, ele já deve estar presente na memória.
5. Quando o I/O físico for realmente necessário, ele deve ser 
feito da maneira mais rápida possível.
6. Os usuários não podem permanecer esperando pelos 
recursos do banco que estejam sendo utilizados por outros 
usuários.
7. As atividades de salvaguarda dos dados (backups) e a 
eventual recuperação (restore e recovery) do SGBD devem ser 
feitos o mais rapidamente possível, assim como quaisquer 
outras tarefas administrativas, como uma carga de dados, 
por exemplo.
7
8. Os processos não podem ficar muito tempo em estado de 
espera pelos recursos do SGBD.
Você deve observar que esse método deve atender a certos pré-
requisitos e que há fatores importantes que você nunca deve 
esquecer.
Figura 1 – Atenção essencial
Fonte: elaborada pelo autor.
Além disso, tenha sempre em mente que o método atende 
ao “ciclo PDCA” e que você deverá percorrer um determinado 
conjunto de etapas.
8
Figura 2 – Etapas da otimização
Fonte: elaborada pelo autor.
Referências bibliográficas
ORACLE. Database Performance Tuning Guide. 18c. 2019. 
Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgdba/database-performance-tuning-guide.pdf. 
Acesso em: 24 jun. 2020. 
PARA SABER MAIS
Você já deve ter percebido que a atividade de ajuste de 
desempenho de um SGBD – performance tuning, ou simplesmente 
tuning – é um exercício empírico, prático, que aponta para diversos 
componentes.
Os equipamentos em que são executados os softwares e que são 
habitualmente segregados por função, são designados hardware. 
Servidores (servers), clientes (clients), rede (network) são os 
hardwares mais comuns. 
9
Clients ou terminais, e mais modernamente smartphones, são 
aqueles de onde partem as solicitações de processamento. Pode 
ser meramente o saldo bancário de um cliente da instituição ou 
a demanda para iniciar o processamento da folha de pagamento, 
contemplando milhares de funcionários. Será necessário avaliar 
se tal solicitação envia uma demanda de forma que a carga de 
trabalho seja executada nos servers ou se solicita dados para 
um processamento local. Neste último caso, as condições desse 
equipamento (o client) precisarão ser analisadas também. Essa 
análise alcançará a performance de comunicação (vide network 
adiante), do armazenamento próprio (storage) e de sua memória 
volátil (RAM).
Servers são computadores habitualmente dedicados a uma 
função, a um conjunto de aplicações (softwares, APPs) específico. 
Você encontrará pelo menos três funções distintas, o servidor 
de banco de dados (DBServer), o de aplicações (AppServer), o de 
rede (NetwServer; vide network adiante), lembrando que cada 
um deles conterá conexões dedicadasou não a dispositivos de 
armazenamento e seu próprio sistema operacional, pelo menos. 
Esses dispositivos de armazenamento podem fazer parte de um 
outro server, conhecido como storage.
Tais computadores podem existir fisicamente separados, podem 
ser placas montadas em uma estante (RAC), ou partições lógicas 
(virtualizações) de um computador maior. A comunicação entre 
eles, então, será feita por um meio físico (um cabo de rede 
metálico ou ótico), ou lógico, denominado canal (channel).
Network tem duas dimensões. Aquela que permite à instituição a 
comunicação com o mundo, a qual você denominará internet, e 
aquela na qual transitam internamente os dados, a intranet. Entre 
elas existirá um equipamento que pretende controlar o acesso à 
intranet, com funções de firewall e de servidor de autenticação. 
10
Lorem ipsum dolor sit amet
Autoria: Nome do autor da disciplina
Leitura crítica: Nome do autor da disciplina
Esta dimensão interna, a intranet, conterá conexões físicas ou sem 
fio (wireless) entre os elementos de hardware.
A partir do sistema operacional, há uma longa lista de programas 
de computador (softwares) que podem simplesmente providenciar 
a visualização de um ponteiro de mouse, a otimização de um 
caminho de rede, a identificação de um storage, ou ser um SGBD.
Referências bibliográficas
ORACLE. Database Concepts. 18c. 2018. Disponível em: https://
docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/
database-concepts.pdf. Acesso em: 24 jun. 2020.
TEORIA EM PRÁTICA
Considere um sistema de gestão de pessoas. Diariamente, 
centenas de funcionários registram suas entradas e saídas; além 
disso, alguns registros diários são feitos pelo pessoal de RH, como 
férias e licenças, ou contratações e demissões.
No dia 25 do mês, um relatório de ausências ou atrasos deve 
ser emitido para os gestores de pessoal, de modo que eventuais 
abonos ou correções de outro tipo possam ser realizados. Elas 
devem ser registradas no sistema durante o dia 26.
No dia 27, será iniciado o processamento da folha de pagamento. 
Cada hora trabalhada de cada funcionário deverá ser computada 
e ainda deverão ser considerados fatores como férias, 13º, 
dependentes, convênio médico, pensões, etc. Ainda, acessar 
tabelas legais, como INSS, FGTS e IRPF. Deve-se, então, gerar um 
11
crédito em conta corrente, imprimir o comprovante de pagamento 
em si e registrar tudo isso na contabilidade da empresa.
Pondere sobre essa sobrecarga de trabalho (entre os dias 25 e 
30, data inadiável para a conclusão dessa tarefa). Está em jogo 
a capacidade de concluir a tarefa em um prazo determinado. 
Lembre-se de que as atividades diárias não podem deixar de 
acontecer.
Outra situação para você pensar a respeito alcança uma 
característica dos dados, que é raramente considerada e 
que eu chamarei de utilidade. Há dados, como o histórico de 
relacionamento com empregados, que devem ser mantidos 
“para sempre”, mas cujo acesso é raramente demandado. Há 
outros dados que simplesmente devem ser periodicamente 
eliminados. Que ideias você consegue elencar sobre isso? O que 
fazer com a “utilidade dos dados”? Será proveitoso separar dados 
a serem guardados “para sempre”? Qual a utilidade em eliminar 
periodicamente certos dados?
Para conhecer a resolução comentada proposta pelo 
professor, acesse a videoaula deste Teoria em Prática no 
ambiente de aprendizagem.
LEITURA FUNDAMENTAL
Indicação 1
Ainda que haja neste artigo uma referência mais explícita ao 
tuning de queries, ao expressar detalhes convergentes com o 
método apresentado, ajudará você a entender detalhes deste. 
Indicações de leitura
12
Para realizar a leitura, acesse a Biblioteca Virtual da Kroton e 
busque pelo título da obra.
VICARI, S. R. E.; IOCHPE, C. Estudo e elaboração de um conjunto 
de regras para sintonia de performance em aplicações de 
banco de dados. In: CONGRESO ARGENTINO DE CIENCIAS DE LA 
COMPUTACIÓN, 3., 1997, La Plata. Anais [...]. La Plata: Universidad 
Nacional de La Plata, 1997.
Desta dissertação, por ora, observe o capítulo 2, denominado 
“Referencial teórico”. As questões abordadas, explicando a 
virtualização, fornecerão para você uma ferramenta adicional para 
o tuning.
Para realizar a leitura, acesse a Biblioteca Virtual da Kroton e 
busque pelo título da obra.
NEIVA, A. S. Estudo comparativo de desempenho em ambiente 
tradicional e virtualizado aplicado a banco de dados em 
plataforma X86. 2010. Dissertação (Mestrado em Engenharia 
Elétrica) – Faculdade de Tecnologia, Universidade de Brasília, 
Brasília, 2010.
QUIZ
Prezado aluno, as questões do Quiz têm como propósito a 
verificação de leitura dos itens Direto ao Ponto, Para Saber 
Mais, Teoria em Prática e Leitura Fundamental, presentes 
neste Aprendizagem em Foco.
Para as avaliações virtuais e presenciais, as questões serão 
elaboradas a partir de todos os itens do Aprendizagem em 
Foco e dos slides usados para a gravação das videoaulas, 
13
além de questões de interpretação com embasamento no 
cabeçalho da questão.
1. O comportamento humano frente a um computador é talvez 
o fator mais crítico e mais imprevisível. Desde o “temor em 
estragar alguma coisa” até a “ousadia de acreditar saber o que 
está fazendo”, passando pelo desagradável “sabe com quem 
está falando?”, norteiam esses comportamentos. 
 
Compare a observação anterior com a questão da 
sazonalidade que exploramos na Teoria em Prática. O humor 
do usuário, as pressões que ele está sofrendo no momento, 
necessidades imediatas da instituição, etc., interferirão no 
tuning? 
a. É possível, uma vez que poderão nortear o aspecto “objetivo 
em números” do método estudado.
b. Demandas de pessoas em altos cargos devem ser sempre 
atendidas, mesmo que injustificáveis.
c. As sazonalidades às que se refere a questão e as demandas 
eventuais de um usuário não têm qualquer ligação.
d. As pessoas devem ser envolvidas no projeto (fase 1 do 
método), depois disso, o DBA trabalhará sozinho.
e. Se o comportamento humano é imprevisível, jamais o 
sistema informacional da instituição se importará com ele. 
14
2. Analise as seguintes afirmações: 
I. Coletar estatísticas é uma atividade que deve ser efetuada 
levando em conta que elas demandam, como amostra, um 
determinado período de utilização do SGBD.
II. Nem sempre a informação estatística não dará conta de 
resolver um desempenho não aceitável.
III. Existem questões “fora da curva”, ou seja, momentos 
em que uma situação em que a sazonalidade distorce as 
estatísticas, como o momento do fechamento de uma folha 
de pagamento.
Escolha agora o item que reflete precisamente essa 
análise:
a. Não há relação de causa e efeito entre os textos indicados.
b. Ao se repetirem frequentemente, as sazonalidades serão, 
afinal, consideradas.
c. Ainda que as sazonalidades interfiram nas estatísticas, elas 
constituirão exatamente “pontos fora da curva”.
d. Ao DBA, jamais caberá avaliar quando uma estatística 
coletada é apropriada em termos de prazo ou qualidade.
e. Recolher estatísticas e observar seus efeitos por um 
algoritmo ou uma pessoa jamais é útil. 
GABARITO
Questão 1 - Resposta A
Resolução: Levando-se em conta que “crises de 
performance” ocorrerão tanto em função de necessidades 
periódicas (ou sazonais) quanto pela tentativa de responder 
15
a um evento isolado (como a presença de um fiscal tributário 
que – dependendo das informações obtidas – poderá aplicar 
uma pesada multa à instituição), um certo “objetivo” pode ser 
criado. 
Questão 2 - Resposta C
Resolução: São os comportamentos sazonais exatamente 
aqueles que tendem a distorcer as estatísticas, podendo levar 
o SGBD a tomadas de decisão que apresentem performance 
não aceitável. 
TEMA 2
As consultas ao banco de dados
______________________________________________________________
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
17
DIRETO AO PONTO
O ajuste da performance dos comandos SQL (tuning) deveria 
acontecer de forma automática, a partir deum processo 
denominado otimizador (optimizer) do SGBD.
O termo “deveria” incomodou você? É esperado que sim. E por 
que isso ocorre? Porque o optimizer é um algoritmo que se baseia 
em dados estatísticos e, portanto, sujeito a incertezas.
E então entra você, o DBA, que deverá corrigir o resultado dessas 
incertezas. De que modo?
Observando que o SGBD, para resolver consultas, moverá 
um certo conjunto de data blocks desde o storage até a RAM, 
eventualmente lendo dados desse data block para saber quais 
outros devem ainda ser lidos. Tenha sempre em mente que cada 
data block contém dados exclusivamente de uma tabela, exceção 
válida para tabelas em cluster.
Essas leituras são provocadas por pelo menos duas situações, 
seja pela necessidade de acesso a um índice, seja pela imposição 
de obter colunas de mais de uma tabela para compor uma 
informação (junção de tabelas, joins).
Um índice do SGBD é formado por uma árvore balanceada que 
organiza os dados em uma pirâmide hierárquica, cuja leitura parte 
de um bloco que explica a organização do índice (denominado 
raiz ou root) e vai avançando por blocos que vão subdividindo as 
entradas sucessivamente, até que, em um último nível, haja um 
data block que indica todos os ROWIDs daquela entrada.
Se você imaginar uma árvore iniciada por um bloco que indica 
qual dos ramos deve ser lido (denominado root), e uma leitura de 
18
ramo, e mais uma do bloco final, somou três ações de I/O para 
localizar uma linha e mais uma para trazê-la para a memória 
(três no índice e uma na tabela, quatro no total). 
Para entender os demais dados a serem calculados, saiba que o 
sistema operacional em que reside o SGBD tem um parâmetro 
denominado Maxphys, o qual limita a quantidade de blocos que 
podem ser transportados a cada operação de I/O. Em nosso 
exemplo, será 524.288 (64 * 8.192).
O SGBD, por sua vez, tem um parâmetro denominado “db_file_
multiblock_read_count”, que determina quantos blocos serão lidos 
de uma única vez. Para nosso exemplo, será 64.
A partir desse número, imagine que sua tabela inteira esteja 
contida em blocos de 8k (8.192) e contenha 14.218.713 de linhas, 
sendo 200 por bloco e, portanto, 71.094 blocos em sua tabela 
(estes dados teriam sido coletados pelo analyze). Sua consulta SQL 
vai obter somente 279 linhas. Finalmente, é possível calcular que 
um Full Table Scan realizará 1.111 operações de I/O, enquanto 
uma leitura By Index (4 I/O per row), 1.116.
A planilha denominada Planilha AF2 – Full Table Scan, 
disponibilizada no AVA, apresenta estes dados e cálculos, 
permitindo variar os valores de modo que você possa se 
familiarizar com esses cálculos.
Você precisa observar, neste ponto, que tal cálculo contém uma 
situação extremada, que foi projetada para mostrar que, em casos 
especiais, pode acontecer uma performance melhor daquela que 
o optimizer projeta.
Outro fato aponta a necessidade constante de joins. Tabelas que 
podem não existir sozinhas (como empregados e departamentos) 
19
podem ser armazenadas em blocos compartilhados, formando 
clusters, o que tende a facilitar a criação do join. Para as demais 
tabelas, o optimizer escolherá entre o sort-merge e o nested-loops 
para solucionar o join.
Nesses casos, você utilizará dicas (hints) que influenciarão 
a decisão sobre o caminho a tomar. Deverá tomar cuidado, 
observando se o SGBD acata ou não sua hint. Há diversas 
situações em que isso acontece. Portanto, codificada a hint, você 
precisará repetir o processo e reexaminar o plano de execução.
Observando o plano de execução, pode também notar que talvez 
um índice possa ser criado.
Outro aspecto a observar é que entre DRL (SELECT) e DML (INSERT, 
UPDATE, DELETE) há diferenças de processamento.
O INSERT costuma ser desprezado por não conter WHERE, que é a 
cláusula principal (mas não a única) observada pelo optimizer.
Os demais costumam possuir WHERE e, logo, demandarão um 
plano para sua execução. No caso do DRL, o fetch é responsável 
por remeter as informações de volta ao solicitante, o que não 
ocorre no DML, o qual, entretanto, deve realizar uma estimativa de 
UnDo, e que esperarão por um commit, savepoint ou rollback.
Referências bibliográficas
ORACLE. Database Concepts. 18c. 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 Performance Tuning Guide. 18c. 2019. 
Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
20
database/18/tgdba/database-performance-tuning-guide.pdf. 
Acesso em: 24 jun. 2020. 
PARA SABER MAIS
Alguns comandos são transformados em comandos equivalentes 
para otimizar a performance de algumas consultas. Por exemplo, 
o optimizer pode transformar algumas delas, contendo operadores 
OR, em consultas equivalentes por meio do uso do operador 
UNION ALL para aumentar a eficiência de execução do comando. 
Isso ocorrerá somente se cada uma das condições utilizadas 
no comando possuírem um caminho de acesso disponível (por 
exemplo, um índice). Caso qualquer condição requeira um full 
scan, o comando não é transformado pelo otimizador. Suponha, 
como exemplo, que existam índices para as colunas EMPNO e 
DEPTNO da tabela EMP. O optimizer pode transformar a primeira 
consulta a seguir apresentada em um segundo comando que 
pode ser executado mais rapidamente: 
1.SELECT * FROM emp WHERE deptno = 10 OR empno > 
7844; 
2.SELECT * FROM emp WHERE deptno = 10
 UNION ALL
 SELECT * FROM emp WHERE empno > 7844 AND 
deptno != 10;
Quanto ao acesso a uma visão, o optimizer pode combinar o 
comando que a define com aquele que a acessa. Use, como 
exemplo, uma visão que apresenta os funcionários que têm 
21
comissão diferente de zero. Consideremos que um comando 
SELECT seja executado para pesquisar essa visão. 
CREATE VIEW emp_comm AS
SELECT * FROM emp WHERE comm > 0;
SELECT ename FROM emp_comm WHERE deptno = 20;
O optimizer pode combinar esse comando com a definição 
da visão para acessar a mesma tabela: 
SELECT ename FROM emp WHERE comm > 0 AND deptno = 
20;
O optimizer pode transformar consultas complexas em joins se 
essa combinação retornar o mesmo conjunto de linhas. Por 
exemplo, consideremos que o comando a seguir seleciona todas 
as linhas da tabela DEPT que possuem linhas correspondentes na 
tabela EMP.
SELECT * FROM dept 
WHERE deptno IN (SELECT DISTINCT deptno FROM emp);
O optimizer transforma esse comando complexo na seguinte 
combinação de tabelas:
SELECT dept.* FROM dept, emp WHERE dept.deptno = emp.
deptno;
Se for utilizada uma consulta complexa que se relaciona com uma 
subconsulta que possua funções de grupo, o otimizador não as 
transforma, como neste exemplo:
SELECT * FROM dept
22
WHERE dept.total_salary > (SELECT AVG(sal) FROM emp);
A respeito das dicas (hints), você deve ter percebido que o texto 
indicador da hint a ser utilizada em um comando aparece entre os 
sinais /*+ e */, usados como comentários. Portanto, as hints são 
vistas como comentários que podem ou não ser aplicados em um 
comando.
O SGDB ignora as hints se os comentários não seguirem os 
comandos DELETE, UPDATE ou SELECT, se contêm erros de 
sintaxe, se ocorrem conflitos entre as diversas dicas usadas em 
um comando ou se as estruturas usadas não existirem ou não 
puderem ser utilizadas.
Você pode verificar, no Quadro 1 a seguir, as hints mais comuns e 
o efeito esperado:
Quadro 1 – Principais dicas e seus propósitos
Hint Propósito
RULE Especifica o método baseado em regra.
FIRST_ROWS Especifica o método baseado em custo 
com ênfase na recuperação da primeira 
linha de um comando.
ALL_ROWS Especifica o método baseado em custo 
com ênfase na recuperação de todas as 
linhas retornadas por um comando.
FULL (tabela) Usa o full scan em uma tabela. 
ROWID (tabela) Usa o ROWID para localizar uma linha 
de uma tabela se for especificado no 
comando. 
CLUSTER (tabela) Usa um cluster para localizar linhas da 
tabela especificada no comando.
HASH (tabela) Usa um hashcluster para localizar linhas 
de uma tabela especificada no comando. 
23
INDEX (tabela índice) Usa um índice para a tabela se ele estiver 
disponível. Podemos especificar mais de 
um índice e o SGDB se utiliza daquele 
que resulta em um menor custo para o 
comando. 
INDEX_ASC (tabela índice) O mesmo que INDEX. 
INDEX_DESC (tabela índice) Para pesquisas que somente usam uma 
única tabela, o índice é utilizado na 
ordem descendente. Para pesquisas que 
usam mais de uma tabela, o otimizador 
transforma essa dica no hint INDEX. 
AND_EQUALS (tabela 
índice)
Usa um plano de execução que combina 
as linhas retornadas de diversos índices. 
Devemos especificar de dois a cinco 
índices para este hint. 
USE_NL (tabela) Utiliza-se de um NESTED LOOP para 
combinar as linhas entre a tabela-guia 
(especificada na dica) e as outras tabelas 
que compõem um joia. 
USE_MERGE (tabela) Combina a tabela especificada com outra 
usando o método SORT MERGE.
Fonte: elaborado pelo autor.
Referências bibliográficas
ORACLE. SQL Tuning Guide. 18c. 2019. Disponível em: https://
docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-
tuning-guide.pdf. Acesso em: 24 jun. 2020.
24
TEORIA EM PRÁTICA
Já que toda a teoria deste módulo é uma preparação para a 
atividade prática de ajuste de performance de um SQL, vamos 
observar um comando e entender o que ele está executando 
(observação: literais prefixados por dois pontos, como :b1 adiante, 
são a “leitura” que o SGBD faz de variáveis de aplicação, que não 
interferirão na execução do SQL).
O Comando:
SELECT COLUNA_UM,COLUNA_DOIS
FROM TABELA_TRES T, TABELA_DOIS G, TABELA_UM H
WHERE T.COLUNA_TRES = G.COLUNA_TRES
AND G.COLUNA_QUATRO = H.COLUNA_QUATRO
AND G.COLUNA_CINCO = H.COLUNA_CINCO
AND G.COLUNA_SEIS = H.COLUNA_SEIS
AND G.COLUNA_SEIS = :b1
AND ((:b2 IN ( 32,532 )
AND G.COLUNA_QUATRO = 32
AND G.COLUNA_CINCO = :b3 )
OR (:b2 IN ( 80,580 )
AND G.COLUNA_QUATRO = 80
25
AND G.COLUNA_CINCO = :b3 )
OR ((:b2 NOT IN ( 32,532,80,580 )
AND :b2 = H.COLUNA_QUATRO )))
O Execution Plan:
 ROWS OPERATION 
307276 NESTED LOOPS
1117625 HASH JOIN
230168825 INDEX FAST FULL SCAN (object id 10182)
298904339 TABLE ACCESS FULL TABELA_UM
 307276 TABLE ACCESS BY INDEX ROWID TABELA_TRES
 614552 INDEX UNIQUE SCAN (object id 10187)
A interpretação do Execution Plan (a primeira coluna é a 
quantidade de linhas manipuladas):
307276 Linhas manipuladas em Junção laços aninhados. 
1117625 Linhas manipuladas em Junção organização parcial de 
dados.
230168825 Linhas manipuladas em Índice lido integralmente (estava 
analisado e é único).
298904339 Linhas manipuladas em Acesso integral à tabela (estava 
analisada).
26
307276 Linhas manipuladas em Acesso via índice à tabela (estava 
analisada).
14552 Linhas manipuladas em Leitura de uma linha do índice (estava 
analisado e é único)
Seu desafio é considerar a possibilidade desse plano ser 
melhorado. Como você faria essa melhoria? Utilizando qual ou 
quais instrumentos? O que você sugere? 
Para conhecer a resolução comentada proposta pelo 
professor, acesse a videoaula deste Teoria em Prática no 
ambiente de aprendizagem.
LEITURA FUNDAMENTAL
Indicação 1
Na seção 3 deste artigo, há uma referência explícita a um 
procedimento de tuning de queries que apresenta aspectos 
complementares àqueles que tratamos até então. 
Para realizar a leitura, acesse a Biblioteca Virtual da Kroton e 
busque pelo título da obra.
VICARI, S. R. E.; IOCHPE, C. Estudo e elaboração de um conjunto 
de regras para sintonia de performance em aplicações de 
banco de dados. In: CONGRESO ARGENTINO DE CIENCIAS DE LA 
COMPUTACIÓN, 3., 1997, La Plata. Anais [...]. La Plata: Universidad 
Nacional de La Plata, 1997.Indicação 2
Indicações de leitura
27
Esta dissertação aponta alternativas de verificação de performance 
de SQL.
Para realizar a leitura, acesse a plataforma Minha Biblioteca, na 
Biblioteca Virtual da Kroton, e busque pelo título da obra.
QUEIROZ, L. T. Um benchmark para avaliação de técnicas 
de busca no contexto de análise de mutantes SQL. 2013. 
Dissertação (Mestrado em Computação) – Universidade de Goiás, 
Goiânia, 2013. 
QUIZ
Prezado aluno, as questões do Quiz têm como propósito a 
verificação de leitura dos itens Direto ao Ponto, Para Saber 
Mais, Teoria em Prática e Leitura Fundamental, presentes 
neste Aprendizagem em Foco.
Para as avaliações virtuais e presenciais, as questões serão 
elaboradas a partir de todos os itens do Aprendizagem em 
Foco e dos slides usados para a gravação das videoaulas, 
além de questões de interpretação com embasamento no 
cabeçalho da questão.
1. A leitura das estatísticas coletadas e a observação das 
estratégias possíveis para join são tarefas que o optimizer 
realiza com o intuito de: 
a. Escrever um plano de acesso perfeito, que não pode sofrer 
alterações nem ser observado.
28
b. Escrever um plano de acesso tão bom quanto possível, 
o qual se pode observar e alterar, mas o volume de I/O 
possível é irrelevante.
c. Escrever um plano de acesso tão bom quanto possível, o 
qual se pode observar e alterar, procurando determinar o 
mínimo de volume de I/O possível.
d. Escrever um plano de acesso cuja união (join) das tabelas 
siga sempre e necessariamente uma estratégia que jamais 
requeira organização (sort).
e. Escrever um plano de acesso perfeito, cuja decisão se baseia 
exclusivamente em observar um dado conjunto de 15 
regras. 
2. Da possibilidade de cálculo apresentada, evidenciando 
as possibilidades de tomada de decisão entre um full-
table-scan e uma leitura via índice, pode-se depreender as 
seguintes afirmativas: 
I. Existem parâmetros do sistema operacional que devem 
ser levados em conta.
II. Existem parâmetros do SGBD que interferem nos 
números.
III. Das estatísticas, podem-se inferir quantas linhas há por 
bloco.
IV. Das estatísticas, podem-se inferir quantos blocos há na 
tabela.
V. Utiliza-se como critério que, para ler uma linha via índice, 
4 ações de I/O são necessárias. 
 
Aponte adiante as corretas: 
29
a. Somente I e III estão corretas.
b. Somente III, IV e V estão corretas.
c. Somente III e V estão corretas.
d. Somente I e II estão corretas.
e. Todas as alternativas estão corretas. 
GABARITO
Questão 1 - Resposta C
Resolução: Porque o optimizer é um algoritmo que se baseia 
em dados estatísticos e, portanto, sujeitos a incertezas, o 
SGBD permite que seja observado e alterado por meio de 
hints. Quanto menor o volume de I/O, melhor performance 
pode-se esperar do comando. 
Questão 2 - Resposta E
Resolução: Ao exercitar os cálculos sugeridos, através da 
planilha fornecida, se pode observar que todas as assertivas 
são verdadeiras, além do que, para entender os dados a 
serem calculados, observe-se que o sistema operacional 
em que reside o SGBD tem um parâmetro denominado 
“Maxphys”, o qual limita a quantidade de blocos que podem 
ser transportados a cada operação de I/O. O valor assumido 
no exemplo, será 524.288 ( 64 * 8192).
“O SGBD, por sua vez, tem um parâmetro denominado ‘db_
file_multiblock_read_count’, que determina quantos blocos 
serão lidos de uma única vez. Para nosso exemplo, será 64”, 
pode-se observar a assertiva sobre “quatro ações de I/O para 
localizar uma linha e trazê-la para a memória (três no índice 
e uma na tabela)” e após, “sendo 200 por bloco e, portanto, 
30
71.094 blocos em sua tabela (estes dados teriam sido 
coletados pelo analyze”). 
TEMA 3
Conhecendo e adequando o 
desempenho das estruturas em 
memória
______________________________________________________________
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
32
DIRETO AO PONTO
O ajuste da memória do SGBD depende diretamente da memória 
real de acesso aleatório (RAM) da máquina que o abriga. Não pode 
haver paginação (swap), importante recurso conhecido como 
memória virtual, que evita que o computador deixe de funcionar, 
caso sua capacidade de RAM seja excedida, mas esconde que um 
certo volumede I/O possa estar ocorrendo.
Então, antes de iniciar o ajuste da performance (tuning) da 
memória do SGBD, você deverá assegurar que a somatória de 
RAM demandada pelo sistema operacional e por quaisquer 
outros processos (por exemplo, aqueles que promovem backup, 
segurança, como um firewall, etc.) em funcionamento nesse 
computador seja conhecido. A partir daí, assegurar que o volume 
de RAM reservado ao SGBD não ultrapasse o volume restante 
da RAM. É recomendada uma margem de segurança de dez por 
cento. 
O parâmetro do SGBD denominado MEMORY_MAX_TARGET deve 
ser usado para garantir que o SGBD não ultrapasse o espaço de 
RAM reservado para ele (ORACLE, 2019, p. 11-14).
Assim, siga a fórmula: RAM >= { Somatória Sistema Operacional & 
outros } + { MEMORY_MAX_TARGET } + { 10% da RAM }.
Ao explorar o parâmetro MEMORY_MAX_TARGET, você observará 
que ele realiza o equilíbrio entre SGA_MAX_SIZE e a alocação 
máxima para a somatória da RAM destinada a atender aos 
processos de usuário, denominada Program Global Area (PGA).
Estabelecidos estes parâmetros, juntamente com o valor corrente 
total, dado pelo parâmetro MEMORY_TARGET, eles não precisam 
33
mais ser alterados. Permitirão uma alocação dinâmica por parte 
do SGBD.
Mas você deve ter percebido que precisa ser adequada a área 
de memória consumida pela PGA. Dessa área, uma pequena 
porção é reservada para valores de variáveis que estejam sendo 
manipuladas pelos programas ou comandos que eventualmente 
estejam sendo executados no interior do SGBD. Sempre que esses 
comandos ou programas demandarem a utilização de atividades 
de organização, o restante dessa área será consumido.
Se seu sistema é destinado a suporte à decisão, existirão poucos 
usuários que provavelmente manipularão grandes quantidades 
de dados. Então, processos usuários individuais, com PGAs 
espaçosas, deverão ser satisfatórias, o que caracteriza processos 
servidores dedicados (dedicated server). 
Caso contrário, se o seu sistema atende a grandes quantidades de 
usuários, como um site de vendas, ele conterá pequenas e rápidas 
transações e você utilizará processos capazes de atender a filas de 
demandas (queues), coordenadas por despachantes (dispatchers), 
servindo, portanto, a diversos usuários ao mesmo tempo, o que é 
o significado de multithreaded server (MTS).
O MTS é um recurso útil, por reduzir a quantidade de 
processadores ativos no servidor (esse limite é bem grande, mas 
existe), e por realizar uma utilização de memória compartilhada 
pelos diversos usuários, mas pode causar um uso maior (overhead) 
de CPU.
A configuração do MTS depende de um software adicional 
do SGBD, denominado Listener. Tal acessório tem, em seus 
arquivos de configuração (listener.ora, tnsnames.ora e sqlnet.
ora), parâmetros que devem ser ajustados para possibilitar 
34
o reconhecimento dessa opção. O SGBD deve ter também os 
seguintes parâmetros modificados:
• MTS_DISPATCHERS, que define a quantidade inicial de 
dispatchers.
• MTS_MAX_DISPATCHERS, que define a quantidade máxima 
de dispatchers.
• MTS_SERVERS, que define a quantidade inicial de servidores 
compartilhados.
• MTS_MAX_SERVERS, que define a quantidade máxima de 
servidores compartilhados.
• MTS_SERVICE, que define o nome da instância a utilizar o 
MTS.
35
Figura 1 – Lições
Fonte: elaborada pelo autor.
Referências bibliográficas
ORACLE. Database Concepts. 18c. Oracle® Database. 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 Performance Tuning Guide. 18c. 2019. 
Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgdba/database-performance-tuning-guide.pdf. 
Acesso em: 24 jun. 2020. 
36
PARA SABER MAIS
“Ferramentas de análise” é uma locução sob a qual são 
apresentadas características do SGBD e indicações sobre como 
medir o desempenho dessas características e, eventualmente, 
promover ajustes em sua performance, o que é a finalidade última 
do tuning.
Essas ferramentas serão, principalmente, consultas realizadas em 
tabelas e visões do dicionário de dados do SGBD (DICT), que são 
metadados do SGBD, ou seja, contêm informações a respeito do 
SGBD. 
Em relação ao MTS, o DICT contém informações sobre:
• v$circuit: circuitos virtuais, que são as conexões de usuários 
ao SGBD, usando MTS.
• v$dispatcher: os processos despachantes.
• v$dispatcher_rate: estatísticas dos processos despachantes.
• v$queue: mensagens do MTS.
• v$mts: ajuste atuais do MTS.
• v$session: cada usuário conectado ao banco de dados.
• v$shared_server: os processos de servidores 
compartilhados.
Das estatísticas principais armazenadas na visão V$DISPATCHER, 
IDLE representa o tempo que os processos despachantes 
permaneceram parados, em centésimos de segundos, mesma 
37
escala demonstrada por BUSY, que contém o tempo que os 
processos despachantes ficaram ocupados. 
Ciente disto, você utilizará a seguinte consulta para determinar a 
ocupação média dos despachantes para cada tipo de protocolo 
(aqui, TCP/IP). Caso os dispatchers permaneçam ocupados mais de 
50% do tempo, deve-se considerar o aumento da quantidade deles 
para evitar contenção (que é o tempo que um processo aguarda 
para ser atendido).
SELECT network “Protocol”, SUM(busy) / ( SUM(busy) + 
SUM(idle) ) “Total Busy Rate“
FROM v$dispatcher GROUP BY network;
A v$queue registra como WAIT o tempo total de espera, em 
centésimos de segundos, enquanto TOTALQ mostra o número 
total de respostas que entraram na fila.
Você poderá determinar o tempo médio que uma resposta espera 
em cada fila (queue). Caso o tempo médio de espera para certo 
protocolo aumente durante a execução das aplicações, você deve 
considerar o aumento do número de dispatchers.
SELECT network “Protocol”, DECODE(SUM(totalq), 0, ‘No 
Responses’, SUM(wait)/SUM(totalq) || ‘ hundredths of 
seconds’) “Average Wait Time per Response“ FROM v$queue 
q, v$dispatcher d WHERE q.type = ‘DISPATCHER‘ AND 
q.paddr = d.paddr GROUP BY network;
Outra possibilidade de contenção a ser investigada é verificável 
pelo comando a seguir. Se o número máximo permitido de 
processos servidores compartilhados for alcançado e o tempo 
médio de espera continuar a aumentar durante certo período, 
38
deve-se considerar o aumento do número máximo desses 
processos.
SELECT DECODE(totalq, 0, ‘No Requests’, wait/totalq || ‘ 
hundredths of seconds’) “Average Wait Time Per Requests“ 
FROM v$queue WHERE type = ‘COMMON’;
Aqui, uma consulta que retorna à quantidade de processos 
servidores em execução no momento:
SELECT COUNT(*) “Shared Server Processes“ FROM 
v$shared_server WHERE status != ‘QUIT’;
Por fim, mas não menos importante, você poderá obter da 
V$SESSTAT informações sobre a SESSION MEMORY (o total de 
memória em bytes destinados a cada sessão) e MAX SESSION 
MEMORY (a quantidade máxima de memória em bytes que foi 
alocada para cada sessão), de modo a determinar a eventual 
necessidade de acréscimo de área para a PGA:
SELECT SUM(value) || ‘ bytes’ “Total memory for all sessions“ 
FROM v$sesstat, v$statname WHERE name = ‘session 
memory‘ AND v$sesstat.statistic# = v$statname.statistic#;
Metadados são estruturas que apontam para outras estruturas, 
revelando-lhes a organização.
No SGBD, os metadados compõem dois grupos distintos de 
estruturas, sob o apelido de dicionário de dados (data dictionary 
ou DICT).
O grupo que abriga os objetos fixos do SGBD são apresentados 
em três abrangências, de prefixo USER ao se referir aos objetos 
do usuário que está logado durante a consulta; ALL tem o mesmo 
conteúdo, com uma coluna adicional onde consta o proprietário 
39
do objeto, alcançando, portanto, objetos que não são de USER, 
mas que este tem o direito de utilizar; DBA contém todas as 
informações apresentadas em ALL, acrescidas de detalhes que são 
exclusivos do DBA. A consulta a seguir vai auxiliar você a localizar 
essas estruturas:
Select * From DICT where upper(table_name)like upper(‘% xxx %’)
Onde xxx serão as três primeiras letras do tipo de objeto do 
qual se quer informações.
Por exemplo, se desejar saber informações sobre tabelas, inicie 
sua pesquisa substituindo xxx por TAB.
O outro grupo contém informações estatísticas e, portanto, 
voláteis. São dados que vão sendo colhidos a partir da inicialização 
da SID e se perdem quando esta é encerrada. Têm como prefixo 
“V$”. O documento Database Reference contém centenas de 
páginas que explicam a finalidade de cada uma. Você utilizou e 
utilizará algumas delas com o intuito de verificar o estado atual do 
desempenho das estruturas cuja performance está investigando
Referências bibliográficas
ORACLE. Database Reference. 18c. 2020. Disponível em: https://
docs.oracle.com/en/database/oracle/oracle-database/18/refrn/toc.
htm. Acesso em: 14 ago. 2020.
ORACLE. Database Performance Tuning Guide. 18c. 2019. 
Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgdba/database-performance-tuning-guide.pdf. 
Acesso em: 24 jun. 2020.
40
TEORIA EM PRÁTICA
Cada vez que você inicia uma tarefa de ajuste de performance, 
você enfrenta uma situação diferente. Seja um outro APP com 
problemas, seja um momento diferente do mês, seja por causa do 
cargo do reclamante.
Observe, entretanto, que você resolverá os problemas conforme 
uma sequência de ações, resolvendo aspectos, problemas que 
você tem estudado, conforme um método de trabalho específico.
Não por acaso, há uma sequência que seguimos, citando primeiro 
a shared pool, depois o Database Buffer Cache e então o redo log 
buffer.
Os comandos são armazenados na shared pool e lá o optimizer 
atua, determinando o que deve ser lido e, no caso de um DML, 
projetando inclusive a leitura de undo blocks (segmentos de 
rollback) para o DataBase Buffer Cache.
Realizar as leituras, então, significa investigar o DataBase Buffer 
Cache procurando por data blocks eventualmente existentes 
(logical reads), calculando o espaço disponível, para forçar eventual 
escrita em storage, se for necessário obter espaço para novas 
leituras físicas.
Ainda no caso de um DML, anotar a before-image e o after-image 
tanto nos data blocks do DataBase Buffer Cache quanto no journal 
do redo log buffer.
Que bom que você reconheceu cada uma dessas ações e estudou 
como cada uma dessas estruturas de memória podem ser 
ajustadas.
41
Imaginando que estas ações se assemelham a um tráfego de 
veículos, os quais representariam os bytes que trafegam de e para 
cada estrutura, analise se todas as questões de performance da 
alocação de memória do SGBD já estão resolvidas pela melhoria 
da performance de cada uma delas. Concordando ou não, 
explique o porquê de sua análise e, se optou por entender que 
ações adicionais devem ser tomadas, explique-as.
Para conhecer a resolução comentada proposta pelo 
professor, acesse a videoaula deste Teoria em Prática no 
ambiente de aprendizagem.
LEITURA FUNDAMENTAL
Indicação 1
Na seção “Monitoramento de performance”, o autor explora 
questões relativas a diversos SGBDs. 
Para realizar a leitura, acesse a Biblioteca Virtual da Kroton e 
busque pelo título da obra.
FLORENTINO, P. T. Gerenciamento e desenvolvimento em banco 
de dados. São Paulo: Editora e Distribuidora Educacional, 2018.
Indicação 2
Esta dissertação aponta para aspectos importantes de 
performance, em função de uma dada infraestrutura, 
especialmente em seu capítulo 2. 
Indicações de leitura
42
Para realizar a leitura, acesse a Biblioteca Virtual da Kroton e 
busque pelo título da obra.
NEIVA, A. S. Estudo comparativo de desempenho em ambiente 
tradicional e virtualizado aplicado a banco de dados em 
plataforma X86. 2010. Dissertação (Mestrado em Engenharia 
Elétrica) – Faculdade de Tecnologia, Universidade de Brasília, 
Brasília, 2010. 
QUIZ
Prezado aluno, as questões do Quiz têm como propósito a 
verificação de leitura dos itens Direto ao Ponto, Para Saber 
Mais, Teoria em Prática e Leitura Fundamental, presentes 
neste Aprendizagem em Foco.
Para as avaliações virtuais e presenciais, as questões serão 
elaboradas a partir de todos os itens do Aprendizagem em 
Foco e dos slides usados para a gravação das videoaulas, 
além de questões de interpretação com embasamento no 
cabeçalho da questão.
1. No SGBD, os metadados compõem dois grupos distintos 
de estruturas, sob o apelido de dicionário de dados (data 
dictionary ou DICT). Examine as proposições adiante:
I. O grupo que abriga os objetos fixos do SGBD são 
apresentados em três abrangências.
II. As de prefixo USER se referem aos objetos do usuário que está 
logado durante a consulta.
43
III. As de prefixo ALL têm o mesmo conteúdo de USER, com uma 
coluna adicional.
IV. A coluna adicional daquelas com prefixo ALL não tem utilidade.
V. As de prefixo DBA contêm todas as informações apresentadas 
em ALL, acrescidas de detalhes que são exclusivos do DBA.
Indique a seguir a opção que corresponde às proposições 
corretas. 
a. Somente as proposições I e II estão corretas.
b. Somente as proposições I, II, III e V estão corretas.
c. Somente as proposições III e IV estão corretas.
d. Somente as proposições III e V estão corretas.
e. Somente as proposições I, II, III e IV estão corretas. 
2. A fórmula RAM >= { Somatória Sistema Operacional & 
outros } + { MEMORY_MAX_TARGET } + { 10% da RAM } 
deve ser interpretada como?
Indicar a alternativa correta, lembrando que RAM é 
a memória real do computador em que o SGBD está 
funcionando.
a. A memória real do computador em que opera o SGBD não 
precisa ser considerada.
b. A somatória do espaço ocupado pelo próprio sistema 
operacional e programa acessórios, com o valor reservado 
para o SGBD, que é dado por MEMORY_MAX_TARGET, e 
ainda com uma margem de segurança de dez por cento, 
deverá ser menor ou igual à RAM. 
c. A somatória do espaço ocupado pelo próprio sistema 
operacional e programa acessórios, com o valor reservado 
para o SGBD, que é dado por MEMORY_MAX_TARGET, e 
44
ainda com uma margem de segurança de dez por cento, 
deverá ser maior ou igual à RAM.
d. O resultado dessa fórmula garantirá que haja paginação em 
disco de partes da RAM.
e. A RAM deverá ser aumentada de tal forma que a somatória 
indicada caiba nela. 
GABARITO
Questão 1 - Resposta B
Resolução: A proposição I está correta, uma vez que o DICT 
aponta para três categorias distintas, que são explicadas por 
outras proposições.
Assim, II está correta, por explicar que a categoria USER 
se refere aos objetos que pertencem ao usuário que está 
conectado e executando as consultas.
Do mesmo modo, III está correta, indicando que a categoria 
ALL contém uma coluna adicional, que aponta para o 
proprietário do objeto referenciado.
Do mesmo modo, a proposição V, que aponta para a 
similaridade com ALL, mas que contém estruturas que são de 
uso exclusivo do DBA. 
Questão 2 - Resposta B
Resolução: Antes de iniciar o ajuste da performance 
(tuning) da memória do SGBD, você deverá assegurar que a 
somatória de RAM demandada pelo sistema operacional e 
por quaisquer outros processos (por exemplo, aqueles que 
promovem backup, segurança, como um firewall, etc.) em 
funcionamento nesse computador seja conhecido. A partir 
45
daí, assegurar que o volume de RAM reservado ao SGBD não 
ultrapasse o volume restante da RAM. É recomendada uma 
margem de segurança de dez por cento. O parâmetro do 
SGBD denominado MEMORY_MAX_TARGET deve ser usado 
para garantir que o SGBD não ultrapasse o espaço de RAM 
reservado para ele.
TEMA 4
Memória física: criação e tuning
______________________________________________________________
Autoria: Odécio Souza
Leitura crítica: Sergio Eduardo Nunes
47
DIRETO AO PONTO
Diz a sabedoria popular: “Não coloque todos os seus ovos numa 
mesma cesta”, que se dirige ao risco de todos quebrarem no 
caso de uma queda. Para você, preocupado com o tuning, o risco 
é ligeiramente diferente, voltado mais ao problema de perda de 
tempo.
Perder tempo, em um processoque acessa um SGBD, significa um 
fenômeno revelado pela espera (wait), ou seja, contenção.
Você dificilmente encontrará uma única e grande solução para a 
contenção, mas – ao ajustar cada estrutura –, ao conquistar cada 
milissegundo isolado, acabará por somar um ganho que, por fim, 
será significativo. 
Ao construir – ou reconstruir – a estrutura física do SGBD, comece 
analisando os destinos dos segmentos, colocando aqueles com 
características diferentes em tablespaces diferentes:
Figura 1 – Análise dos destinos dos segmentos
Fonte: elaborada pelo autor.
48
Por consequência, distribuir os arquivos por meio de diferentes 
dispositivos:
• Espalhar datafiles pela maior quantidade de LUNs possíveis.
• Separar datafiles e redo log files.
• Separar redo log files de archive log files.
Antes da criação efetiva dos tablespaces e seus datafiles, 
entretanto, você deverá calcular que tamanho de bloco de dados 
é conveniente. Observe que cada data block contém um múltiplo 
inteiro de blocos endereçados pelo sistema operacional. 
A planilha fornecida “Planilha AF4 – Table Size Estimation 
Procedure”, disponibilizada no AVA, ajudará você a determinar o 
dimensionamento de cada tabela e, assim, de cada tablespace.
Adicionalmente, observe que questões como o I/O desnecessário, 
causado pela fragmentação das linhas entre os blocos de dados, 
os latches desnecessários e o desperdício de espaço vão surgindo 
ou sendo resolvidas pela dimensão desse data block.
Desse modo, observe que o uso de data blocks pequenos 
apresentam vantagens, pois reduzem a probabilidade de 
contenção, uma vez que menos linhas cabem no mesmo bloco; 
eles são bons para pequenas linhas, em acesso randômico, 
característica marcante de sistemas transacionais, como um site 
de vendas (OLTP). O inverso também é verdade, portanto, há 
desvantagens, já que poucas linhas cabem em um mesmo bloco, 
com grande probabilidade de overhead (uso maior de CPU), com 
maior acesso a linhas de índices.
Em contrapartida data blocks grandes tendem a causar menos 
overhead, porém consomem maior espaço para armazenamento. 
49
São convenientes para grandes linhas, em acesso sequencial, ou 
utilizando índices, características mais específicas de sistemas 
de suporte à decisão (Big Data). Há também aspectos negativos 
a considerar, já que um data block grande de índice não trabalha 
bem quando a aplicação tem uma característica OLTP, tendendo a 
desperdício de espaço no DataBase Buffer Cache.
Você deverá analisar também a possibilidade dos fenômenos 
denominados migração e encadeamento, ambos apontados 
estatisticamente como chainned rows.
Em algumas circunstâncias, uma ou mais linhas de uma tabela 
podem não caber em um único bloco de dados e necessitar 
de dois ou mais blocos. Quando isso ocorre, as linhas são 
armazenadas em uma série sucessiva de blocos encadeados. 
O encadeamento de linhas por meio de diversos blocos ocorre, 
geralmente, quando elas são muito grandes, como aquelas que 
possuem colunas dos tipos LONG ou LONG RAW. 
Uma tabela pode conter uma única coluna do tipo LONG e, como 
este tipo de dado pode armazenar até 2 gigabytes de informação, 
as linhas talvez ocupem um ou mais blocos de dados. Nada pode 
ser feito para melhorar essa forma de encadeamento que ocorre 
em linhas muito grandes, a não ser a criação de uma tabela 
separada para conter somente a coluna do tipo LONG. Uma linha 
atualizada para um tamanho maior em um bloco sem espaço livre 
para a atualização sofre migração para outro bloco. 
O pedaço original da linha migrada é preservado para apontar 
para o novo bloco que contém a linha. O ROWID de uma linha 
migrada não se altera. Quando uma linha sofre migração ou 
encadeamento, a performance do I/O associado a ela é afetado, 
pois o SGDB precisa buscar mais um data block para processar a 
operação.
50
Referências bibliográficas
ORACLE. Database Concepts. 18c. 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 Performance Tuning Guide. 18c. 2019. 
Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgdba/database-performance-tuning-guide.pdf. 
Acesso em: 24 jun. 2020. 
PARA SABER MAIS
A esta altura, você deve estar pulando na cadeira cada vez que 
lê as palavras estimativa, estatística, projeção. Não se assuste, 
pois sua capacidade de pilotar uma bola de cristal não está sendo 
colocada em jogo aqui.
Algumas das ferramentas que você está estudando permitem sim 
projeções e estimativas sérias, como a quantidade de bytes que 
você espera depositar em uma linha de uma tabela. 
Nessa mesma ferramenta, entretanto, você deverá estimar 
quais colunas serão conhecidas na criação da linha e quais 
serão adicionadas mais tarde. Quais colunas jamais terão bytes 
adicionados a elas e quais poderão ser “alargadas”.
Então saiba que essa aparente armadilha não tem solução. Se 
por um lado você não vai criar estruturas a esmo, por outro, você, 
assim como toda a instituição que detém um SGBD, deve saber 
que a performance dependerá de como os dados serão utilizados 
pelos usuários. 
51
Aqui não se está apontando para questões de lógica de aplicação, 
como sequência de telas ou permissões de acesso, mas de como 
os usuários se comportarão frente ao APP. 
Se isso lhe parece estranho, pense como prever o que os bilhões 
(sim, bilhões) de usuários de certa rede social muito famosa 
no Brasil farão: se percorrerão suas páginas, distribuindo likes 
e comentários, ou se somente abrirão fotos e vídeos, ou se 
comporão longos stories, procurando depois pelos stories dos 
outros usuários...
Fará parte de suas atribuições, portanto, fazer entender para si 
mesmo e para todas as pessoas que esperarão uma performance 
ideal no primeiro dia que a localização desse tipo de problema 
depende, por exemplo, de coleta de estatísticas.
Quando os comandos SQL forem a causa, pode ser possível 
modificar seu texto on flight, como pode ser possível criar 
ou eliminar um índice imediatamente. Recriar um tablespace 
temporary ou de rollback pode acontecer no final do expediente, 
mas readequar a blocagem do SGBD pode significar mais que um 
final de semana de indisponibilidade.
Depois de tudo isso explicado e eventualmente realizado, ou 
talvez antes, como estudioso do assunto, você terá que esclarecer, 
conforme aprendeu no seu estudo do Database Performance 
Tuning Guide (ORACLE, 2019, p. 3-1):
A melhoria de desempenho, por sua natureza, é iterativa. Por esse 
motivo, remover o primeiro gargalo pode não levar ao desempenho 
melhoria imediata, porque outro gargalo pode ser revelado. Além 
disso, em alguns casos, se os pontos de serialização mudarem 
para um mecanismo de compartilhamento mais ineficiente, o 
desempenho poderá ser prejudicado. Com experiência e seguindo 
52
um método rigoroso de limitação de gargalo, os aplicativos podem 
ser depurados e tornados escaláveis.
Trocando em miúdos, não um, mas diversos ciclos de tuning serão 
necessários. Alguns simples, como ajustar uma cláusula WHERE 
de um SELECT, outros um pouco mais complicados, como criar um 
índice. Em outros momentos, graves e complicados, como destruir 
tudo, rever a organização e tamanho dos blocos de dados no nível 
do sistema operacional e criar tudo novamente.
Referências bibliográficas
ORACLE. Database Reference. 18c. 2020. Disponível em: https://
docs.oracle.com/en/database/oracle/oracle-database/18/refrn/toc.
htm. Acesso em: 14 ago. 2020.
ORACLE. Database Performance Tuning Guide. 18c. 2019. 
Disponível em: https://docs.oracle.com/en/database/oracle/oracle-
database/18/tgdba/database-performance-tuning-guide.pdf. 
Acesso em: 24 jun. 2020.
TEORIA EM PRÁTICA
Este é um desafio “casado”, em duas etapas. A primeira será, a 
partir da sua solução, um subsídio importante para a segunda. 
Primeiro, você vai considerar como será a ocupação de espaço de 
uma linha, na sua entrada, e, a partir de então, como ela tende 
acrescer. Em decorrência, na segunda etapa, um instrumento 
de cálculo será utilizado para determinar o espaço inicial a 
ser reservado para cada tabela e o percentual de crescimento 
esperado.
53
Mãos à obra: imagine que você está projetando o registro das 
informações de uma pessoa física. Todas as pessoas têm os 
mesmos dados? 
Um menor de idade não tem ainda título de eleitor, nem 
habilitação para conduzir veículos, nem cônjuge. Se for 
menino, ainda não tem carteira da reserva militar; se for 
menina, nunca terá. Meninas serão 75% da sua amostra, e 
meninos, portanto, 25%, mas você não tem como estimar sua 
idade (gênero, no Brasil, restringe-se legalmente a masculino ou 
feminino, homem ou mulher; como não estamos querendo ser 
preconceituosos, dissemos meninos ou meninas). Digamos que 
você estime que metade da sua amostra tende a permanecer 
solteira a vida toda e jamais obter habilitação para conduzir 
veículos.
Você notou os termos em negrito? Sugerem alguma característica 
a ser levada em conta ao criar uma tabela? Lembre-se de que se 
um dado puder não ser informado, ele deverá ser nulo (null).
Vai aí uma dica importante: se uma ou mais colunas à direita da 
última que contiver valor estiverem vazias (null), antes do header 
da próxima coluna, isso constituirá um espaço não ocupado.
Observe então estas duas estruturas sugeridas para a tabela 
PESSOA e escolha qual delas tende a consumir menos bytes 
manipulados, cada vez que essa linha estivesse envolvida em 
um comando. Você não tem o direito de acreditar que essa 
estruturação é indiferente.
54
Figura 2 – Estruturas sugeridas para a tabela PESSOA
 
Fonte: elaborada pelo autor.
A partir da organização da tabela PESSOA escolhida, que deve ter 
estimado uma estimativa de PCTFREE (o quanto você acredita que 
uma linha deve ter espaço para crescer, depois de uma inserção), 
calcule a área que essa tabela precisará, imaginando que você 
armazenará inicialmente 500 mil linhas, e terá novas 50 mil linhas 
a cada mês.
Para tanto, utilize as informações e fórmulas da planilha fornecida: 
BDTuningAF_Tema4_TableSizeEstimationProcedure.xlsx (“tab” 
EstimaTabela).
Para conhecer a resolução comentada proposta pelo 
professor, acesse a videoaula deste Teoria em Prática no 
ambiente de aprendizagem.
LEITURA FUNDAMENTAL
Indicação 1
Especialmente na seção 3.3, o autor explora a estruturação de um 
SGBD. Ainda que seu foco seja a criação desse SGBD em nuvem, 
Indicações de leitura
55
os preceitos lá estabelecidos dialogam muito bem com aqueles 
esclarecidos nesta unidade.
Para realizar a leitura, acesse a Biblioteca Virtual da Kroton e 
busque pelo título da obra.
FLORENTINO, P. T. Gerenciamento e desenvolvimento em 
banco de dados. São Paulo: Editora e Distribuidora Educacional, 
2018.
Indicação 2
Ainda que analisando a particularidade de dados em multimídia, 
mas justamente por esse acréscimo, esta leitura é importante. Sua 
análise de índices será particularmente útil.
Para realizar a leitura, acesse a plataforma Senac, na Biblioteca 
Virtual da Kroton, e busque pelo título da obra.
COLOSSI, N. G. Avaliação de desempenho de estruturas de 
acesso a dados hiperdimensionais. Dissertação (Mestrado em 
Engenharia Elétrica) – Instituto de Computação, Universidade 
Estadual de Campinas, Campinas, 2000. 
QUIZ
Prezado aluno, as questões do Quiz têm como propósito a 
verificação de leitura dos itens Direto ao Ponto, Para Saber 
Mais, Teoria em Prática e Leitura Fundamental, presentes 
neste Aprendizagem em Foco.
56
Para as avaliações virtuais e presenciais, as questões serão 
elaboradas a partir de todos os itens do Aprendizagem em 
Foco e dos slides usados para a gravação das videoaulas, 
além de questões de interpretação com embasamento no 
cabeçalho da questão.
1. A partir da organização que o SGBD estabelece, existe uma 
relação de dependência entre as estruturas existentes na 
memória física. 
 
Observe as proposições adiante:
I. O tablespace é formado de datafiles.
II. O bloco do SGBD (data block) é composto de um ou mais 
blocos do sistema operacional.
III. Os tablespaces contêm segmentos.
IV. Os segmentos são formados por extents.
Determine qual alternativa corresponde às proposições 
corretas: 
a. Somente as proposições I e II estão corretas.
b. Somente as proposições I, II e IV estão corretas.
c. Somente a proposição III está correta.
d. Somente as proposições III e IV estão corretas.
e. Todas as proposições estão corretas. 
2. O encadeamento ocorre quando uma linha nova 
extrapola o espaço disponível ou quando ela é aumentada 
para além desse espaço. Sendo assim, classifique as 
afirmativas a seguir em verdadeiras (V) ou falsas (F).
57
( ) Encadeamento é o evento quando o SGBD aumenta 
automaticamente o tamanho do data block.
( ) Uma linha nova corresponde a um INSERT.
( ) Uma linha é aumentada por um UPDATE.
( ) Encadeamento é o evento quando o SGBD aumenta 
automaticamente o tamanho do extent.
( ) O ROWID é mantido apontando para o data block 
original.
Selecione a alternativa que corresponde à sequência 
correta.
a. F – F – V – F – V.
b. V – V – F – V – F.
c. V – F – V – V – V.
d. F – V – V – F – V.
e. V – F – V – F – V. 
GABARITO
Questão 1 - Resposta E
Resolução: A leitura do diagrama que expressa a hierarquia 
de armazenamento de banco de dados é lida da seguinte 
maneira: o tablespace é formado por arquivos de dados 
e contém um ou mais segmentos que, por sua vez, são 
compostos de extensões. Estas são compostas de blocos 
Oracle (data blocks), que são compostos de um ou mais 
blocos do sistema operacional. 
58
Questão 2 - Resposta D
Resolução: A migração ocorre quando, sob um UPDATE, 
a linha tem acréscimo de bytes, mas o data block em que 
ela reside não contém mais espaço; então, o SGBD deverá 
reescrever essa linha em outro data block. Quando um 
INSERT não encontra espaço suficiente para a linha inteira no 
data block que deveria conter originalmente a linha, escreve 
essa linha em outro. Em ambos os casos (INSERT ou UPDATE), 
se a linha é muito maior que o espaço disponível nos data 
blocks, o SGBD utilizará diversos blocos. Ambos os eventos 
são conhecidos como linhas encadeadas (chainned rows). Em 
ambos os casos, o ROWID aponta para o data block original.
Logo, é falso que o encadeamento é o evento quando o SGBD 
aumenta automaticamente o tamanho do data block, já que 
seu tamanho é fixo.
Uma linha nova corresponde a um INSERT, considerando-se 
que a isto se destina o comando INSERT.
Uma linha é aumentada por um UPDATE, considerando-se 
que o comando pode incluir novas colunas ou fazer variar o 
tamanho das existentes.
É falso que o encadeamento é o evento quando o 
SGBD aumenta automaticamente o tamanho do extent. 
Encadeamento significa uma linha que não pode ser 
convenientemente acomodada em um data block.
O ROWID é mantido apontando para o data block original, 
sendo justamente a questão mais grave, já que exige uma 
ação extra de I/O para leitura dessa linha. 
BONS ESTUDOS!
	Apresentação da disciplina
	Introdução
	TEMA 1
	Direto ao ponto
	Para saber mais
	Teoria em prática
	Leitura fundamental
	Quiz
	Gabarito
	TEMA 2
	Direto ao ponto
	TEMA 3
	Direto ao ponto
	TEMA 4
	Direto ao ponto
	Botão TEMA 5: 
	TEMA 2: 
	Botão 158: 
	Botão TEMA4: 
	Inicio 2: 
	Botão TEMA 6: 
	TEMA 3: 
	Botão 159: 
	Botão TEMA5: 
	Inicio 3: 
	Botão TEMA 7: 
	TEMA 4: 
	Botão 160: 
	Botão TEMA6: 
	Inicio 4: 
	Botão TEMA 8: 
	TEMA 5: 
	Botão 161: 
	Botão TEMA7: 
	Inicio 5: