Baixe o app para aproveitar ainda mais
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! 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 MontefuscoGilvâ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 efetivamente em 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 doSGBD 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. • UnDo ou 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
Compartilhar