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