Primeiras aulas do curso Desempenho do Oracle Database: otimização de consultas

Desempenho do Oracle Database: otimização de consultas

Estatísticas - Introdução

Oi, gente. Tudo bem? Meu nome é Victorino Vila e este é o curso de Oracle Performance Parte 2. Atenção, temos um curso Oracle Performance Parte 1. Apesar do Parte 1 e Parte 2 tratarem de assuntos diferentes do ambiente Oracle, tudo é performance. Performance então é o tempo em que um usuário espera para que o banco de dados lhe dê uma resposta.

O que acontece é que no banco de dados podemos separar em duas partes. Ou é o ambiente, e depois que o ambiente está otimizado, ainda há a forma com que Oracle vai resolver a consulta que o usuário está enviando para o banco de dados. O curso Oracle Performance Parte 1 pega mais em ênfase no ambiente. Já esse treinamento que você está fazendo aqui, ênfase na lógica.

A pergunta é: posso fazer o curso Parte 2 sem fazer o curso Parte 1? A resposta é sim, pode, mas eu não aconselho. Principalmente porque nesse treinamento, nós vamos fazer depois um trabalho prático, e algumas coisas nesse trabalho prático vão cobrir alguns assuntos que vão ser vistos no Oracle Parte 1. Então eu aconselho a vocês fortemente que façam o Parte 1 para depois fazer o Parte 2.

Na verdade esses dois, o Parte 1 e o Parte 2, eles eram para ser um curso único, mas aqui na Alura, não gostamos de ter cursos muitos extensos. Então quando um assunto que tem um começo, meio e fim, ele acaba ficando muito longo, nós dividimos ele em dois ou três, ou quantas vezes forem necessárias, tentando criar um início, meio e fim, meio que intermediário, mas existe uma lógica global entre esses dois treinamentos.

Então, novamente, eu acho legal você fazer o Oracle Parte 1 para depois de fazer esse. Mas ok. O que nós vamos ver nesse treinamento aqui, no Parte 2? Vamos voltar a falar de coleta de estatísticas, pois aqui é feita, é falada também no curso Parte 1. Só que as coletas de estatísticas aqui vão estar muito mais ligadas à resolução dos comandos de consulta.

São estatísticas que envolvem os objetos Oracle que fazem parte das consultas. Isso porque o Oracle, ele consulta as estatísticas para decidir o que vai fazer na hora de resolver uma consulta SQL. E vamos falar sobre os Advisors, que são aconselhadores, digamos assim, do ambiente. Vamos falar sobre índices, que é uma estrutura que ajuda na performance, nas visões materializadas, uma coisa exclusiva da Oracle que também ajuda na performance.

E, como eu já falei, vamos fazer um problema prático, onde vamos resolver a performance de duas consultas e de três comandos de inclusão na base de dados, e tentando aplicar coisas não somente do Parte 1, mas que vão ser vistas nesse curso Parte 2. Então é isso aí gente, espero que vocês gostem do curso. Vamos seguindo em frente, um abraço, até a próxima.

Estatísticas - Gerenciando estatísticas de consultas SQL

Vamos continuar estudando então? Até agora, o treinamento introduziu alguns aspectos no ajuste da instância, principalmente na parte de gerenciamento de memória. Embora o ajuste da instância como um todo seja muito importante, vale a pena enfatizar que é preciso que o ajuste esteja orientado para o problema de negócio. Isso significa que também precisamos trabalhar no ajuste dos comandos SQL para que eles possam atender aos tempos de resposta esperados.

Em outras palavras, por mais que ajustemos o ambiente em temos de memória e ter a instância sendo executada de maneira ótima, compatível com as necessidades do ambiente e compatíveis com o hardware que eu estou trabalhando, ainda assim precisamos analisar os comandos SQL. Porque qualquer instrução SQL, ela pode ser executada de maneiras diferentes.

Por exemplo, eu posso fazer um join, juntar tabelas, em diferentes ordens. Eu posso haver uma escolha entre usar ou não o índice para varrer uma tabela ou alguns métodos de execução podem ser mais extensivos no uso de entrada e saída de disco em relação a outros processos de execução de SQL, que vão utilizar mais CPU.

Qualquer SQL que eu for executar no Oracle vai respeitar uma regra que está contida no que nós chamamos de planos de execução. E eu posso resolver uma SQL utilizando diversos planos de execução diferentes. Em outras palavras, dentro de um SQL específico, eu posso ter vários planos de execuções que vão chegar ao mesmo resultado.

A escolha de qual plano de execução o SQL vai usar para resolver a sentença vai ser primordial para o desempenho. Ou seja, em outras palavras novamente, quando eu executo um SQL, ele vai ter que escolher um plano de execução e dependendo do plano de execução que ele, que o Oracle vai escolher, eu posso ter um resultado bom ou ruim na hora de resolver essa SQL.

Então, qualquer instrução SQL vai precisar de um plano de execução para ser resolvida e podem existir diversos planos de execução para executar a mesma SQL. O Oracle possui um otimizador, que ele vai escolher sempre o melhor plano de execução possível. Ou seja, nesse caso aqui, o Oracle sozinho vai, dentre todos os planos de execução existentes para resolver essa SQL, ele vai escolher o melhor deles.

Esses planos de execuções, eles são desenvolvidos dinamicamente pelo otimizador. O otimizador de SQL, que é interno do Oracle, como é que ele desenvolve esses planos de execução e como é que escolhe qual é o melhor plano de execução a ser escolhido? Ele se baseia também em uma série de estatísticas. Estatística essas que também são coletadas automaticamente pelo Oracle.

Lembra? Nós falamos já de estatísticas e métricas quando vimos a parte de otimização da instância. Também são coletadas estatísticas e, no caso aqui, elas são baseadas nas tabelas e não mais no ambiente. E baseadas nessas estatísticas, ele vai conseguir escolher qual é o melhor plano de execução a ser seguido.

Existem muitos tipos de estatísticas que o Oracle coleta a respeito do ambiente, mas as principais estatísticas são as estatísticas que fornecem detalhes das tabelas que estão envolvidas nas instruções SQL. Ou seja, quando eu tenho um comando de SQL que vai consultar dados de várias tabelas, são dados específicos da tabela física que ele vai usar como decisão para escolher o melhor plano de execução. Bem, a análise de uma tabela baseia-se nas estatísticas que vão ser úteis para o otimizador.

As estatísticas, elas podem ser visualizadas através desta tabela DBA_TABLES, e elas vão dar para mim informações basicamente sobre o número de linhas da tabela; o número de blocos que foram usados ou ainda não utilizados e alocados para aquela tabela; a quantidade de espaço em disco livre nos blocos que estão sendo usados; o comprimento médio de cada linha e chamamos de um número de encadeamento de linhas que cortam dois ou mais blocos.

É basicamente essa última afirmação, esse número de encadeamento de linhas que cortam dois ou mais blocos, é uma medição interna da forma com que os bytes que estão relacionados com aquela tabela, que têm informações daquela tabela, estão gravados em discos. Então são essas as estatísticas que o otimizador vai ler a respeito das tabelas que estão sendo usadas para a resolução do SQL, a fim de decidir qual é o melhor plano de execução que vai ser, digamos assim, executado.

Além das estatísticas sobre a tabela. também o otimizador vai ler algumas estatísticas que são coletadas sobre as colunas da tabela. A tabela, que eu consigo ver as estatísticas das colunas das tabelas, é a DBA_TAB_COLUMNS. E eu consigo ver, claro, o número de valores distintos em cada coluna da tabela; os valores mais altos e mais baixos, contidos na tabela; o comprimento médio do tamanho da coluna e o número de valores nulos dentro daquela coluna.

Todos esses quatro, digamos essas quatro métricas ou estatísticas, elas são importantes para decidir qual é o melhor plano de execução que o SQL vai seguir. Ou seja, claro, baseado nesses valores relacionados com tabelas e com colunas é que o otimizador vai calcular uma série de indicadores para decidir qual é o melhor plano de execução que vai ser seguido.

Mas também algumas estatísticas sobre índices são analisados pelo otimizador. Quando uma tabela é analisada, os seus índices, que estão envolvidos naquela tabela, também são analisados de forma implícita. É possível reunir estatísticas dos índices, que são mostradas nesta a tabela DBA_INDEX.

E essas estatísticas incluem, por exemplo, a profundidade da árvore de índice - nós depois, mais à frente, eu vou explicar como é que funciona um índice, vamos ver que um índice, ele tem uma forma, através de árvore binária, de poder construir a sua organização. Então a profundidade dessa árvore binária é um indicador importante para a decisão do uso ou não de um índice em um plano de execução.

Também é importante o número de valores-chaves distintos, ou seja, da primary key, quantos valores diferentes eu tenho dentro da tabela. E o fator de agrupamento, ou seja, o quão perto a ordem natural das linhas segue a ordem natural das primary keys. Quando eu falo ordem natural das linhas, é a ordem com que as linhas foram gravadas na tabela, exemplo: se eu tenho uma tabela cuja chave primária é pelo nome do cliente, se eu dou INSERT na tabela - a tabela está vazia, ela tem como chave primária o nome do cliente.

Se eu dei INSERT na tabela respeitando a ordem alfabética, mesmo que eu tenha um índice, essa tabela vai ser bem mais rápida do que se eu der INSERT com a letra Z, depois com a letra R, depois com a letra Y. Ou seja, a ordem natural com que os registros vão ser gravados na tabela vai ser completamente diferente da ordem dos índices. Então é esse tipo de relação que o otimizador testa quando está calculando o índice.

Deixa eu ver se tem mais alguma coisa para falar aqui para vocês. Essas estatísticas, elas são coletadas e são armazenadas no dicionário de dados. E vai fornecer, claro, informações necessárias para que o otimizador tome decisões vitais sobre a melhor forma de executar as instruções SQL. Se eu tiver estatísticas ausentes ou incorretas, pode ser que o desempenho do meu SQL diminua, porque ele vai coletar respostas sobre estatísticas que não estão atualizadas, e ele pode tomar a decisão e escolher o plano de execução errado.

Normalmente essas estatísticas, elas são imperfeitas. Primeiro porque são estatísticas, por si só isso já leva em consideração que estatísticas pegam um espaço na mostra da realidade. Isso significa que as estatísticas, elas podem estar obsoletas ou desatualizadas. Bem, um outro fator é que as estatísticas dos objetos também são imperfeitas porque, como regra geral, elas são coletadas através de um processo de amostragem.

Se o objeto tiver muitos gigabytes, por exemplo uma tabela enorme, uma análise completa das estatísticas dessa tabela vai consumir muito tempo e recurso. Analisar dessa tabela gigante somente uma mostra fornece números representativos daquela tabela a um custo muito menor. Claro, se eu tivesse que analisar a tabela com um todo.

Às vezes, ou na maioria das vezes, a coleta dessas estatísticas, mesmo que seja através de amostras, são adequadas. Vou dar um exemplo: imagina uma tabela que tenha um bilhão de linhas, tabela enorme. Podemos ter estatísticas totalmente corretas a respeito daquela tabela de um bilhão de linhas e durante um determinado momento, eu adicione alguns milhares de linhas.

Ao fazer isso eu não vou prejudicar a eficiência dos planos de execução, porque a probabilidade dessas milhares de linhas que vão ser incluídas dentro dessa tabela que tem bilhões de linhas, a probabilidade de ela respeitar, digamos assim, um comportamento estatístico daquilo que já está gravado no banco é alto. Só vai dar problema se por algum motivo essas milhares de linhas tenham um comportamento completamente diferenciado daquilo que está gravado no banco.

Então, dependendo da situação, uma quantidade de inserts, updates ou deletes na tabela pode ou não afetar as estatísticas da nossa tabela. Bem, em outras palavras, em uma tabela grande a probabilidade de novas linhas respeitarem um padrão é bem maior, logo não é necessário analisarmos a tabela toda, toda hora, senão gastaria muito recurso para obter as minhas estatísticas para que o otimizador decida qual é o melhor plano de execução a ser escolhido quando um comando SQL for executado.

Estatísticas - Diretivas do plano SQL

Vamos continuar? Olha só, é comum então haver várias maneiras de executar qualquer instrução SQL. Vamos considerar um exemplo bem trivial: eu tenho uma tabela chamada "emp", de empregados, onde cada linha da tabela possui uma coluna chamada S-A-L, "sal", que possui o salário do funcionário e, essa coluna "sal", ela está indexada. Eu tenho uma consulta que vai recuperar o nome dos funcionários cujo salário seja maior do que um determinado número.

Para responder a essa pergunta, eu tenho dois tipos de plano de execução: ou eu percorro a tabela inteira - é esse plano de execução que está aqui à esquerda, e, para cada linha que eu vou olhando na tabela inteira, eu vou vendo se o salário do funcionário é maior ou não do número que eu coloquei na condição. Ou eu posso usar o índice, porque essa coluna "sal" tem um índice.

Pelo índice, eu consigo capturar mais facilmente, identificar mais facilmente as linha respeitando uma determinada condição. Então são dois planos de execuções diferenciados, que eu posso usar caso eu queira resolver essa SQL, ou seja, me listar o nome dos funcionários respeitando uma condição específica para o salário.

Bem, a primeira consulta, ou seja, eu quero saber o nome dos funcionários cujo salário é maior do que 1 milhão, fica óbvio que ela é muito mais rápida se eu executar o método do índice. Vamos supor, por exemplo, que são pouquíssimos funcionários que possuem salário maior do que um milhão. Então tem sentido eu varrer a tabela inteira para retornar três, quatro, cinco linhas? Pesquisando no índice para encontrar os funcionários mais relevantes, eu vou gastar um tempo muito menor para resolver esse problema.

Agora, a segunda consulta, no entanto, ela vai ser mais rápida se usarmos a varredura completa na tabela, sem o índice. Primeiro, sabemos que salário sempre é maior que zero, ninguém ganha salário negativo. Então, óbvio que essa consulta aqui debaixo, ela vai sempre retornar a tabela toda e para retornar a tabela toda eu não vou precisar usar o índice.

O índice, ele é eficiente se eu quero limitar a minha saída, mas se a saída for a tabela como um todo, a varredura da tabela é muito mais eficiente. Então eu mostrei aqui para vocês dois exemplos de SQL onde, dependendo do que eu tenho aqui na condição de filtro, um plano é melhor do que o outro.

Agora, vamos supor que eu faça o meu select usando lá a condição salário maior do que um milhão, e escolheríamos, óbvio, o plano usando o índice. Mas vamos supor que eu só tenha um funcionário na tabela e esse funcionário é o cara que ganha mais de um milhão. Então, apesar dessa condição aqui, nós termos concordado que usar a varredura da tabela por índice é melhor, se a tabela tiver poucas linhas, já não vale a pena usar o índice, vale a pena varrermos a tabela.

Ou seja, a estatística do objeto que informa ao Oracle quantas linhas eu tenho na tabela, quantos blocos de segmento a tabela está ocupando, os valores máximos e mínimos das colunas, a profundidade dos índices e muito mais, vai fazer com que ele decida, independente do tipo de condição que está aqui, se ele vai usar ou não o índice. O plano de execução interno do Oracle, ele é esperto o suficiente para escolher qual é a melhor condição.

Então eu mostrei à vocês que a estatística sobre as características da tabela é importante para o Oracle decidir. Nesse caso aqui: tem índice? Tem. A tabela é muito grande? É. Então eu uso índice. Tem índice? Tem. A tabela é muito grande? Não, é pequena. Então eu varro a tabela toda. Então a estatística sobre o número de linhas que eu tenho na tabela é fundamental para o otimizador escolher o plano de execução que ele vai fazer no caso deste SQL que eu esteja executando.

Então essa decisão de escolher o plano de execução vai estar baseada em uma estimativa de cardinalidade, ou seja, em uma estimativa de tamanho da tabela, que o otimizador vai ter baseado na estatística. Então o otimizador vai fazer palpites sobre quantas linhas e blocos vão ser acessados pelos vários métodos de execução para poder fazer a escolha.

A precisão desse palpite é crítico para o desempenho da consulta e vai depender muito da frequência com que as estatísticas são coletadas e no tamanho da amostra. Em alguns casos, essas estimativas podem trazer valores errados. Por quê? Normalmente porque estatística são antigas ou são baseadas em uma amostra inadequada.

Para resolver essa anomalia, a Oracle possui dois recursos que são usados pelo otimizador para corrigir esses problemas. São as Diretivas de Plano SQL e planos de execução adaptáveis. Bem, durante a execução da instrução, o otimizador vai monitorar quantas linhas realmente vão ser retornadas para cada etapa, e ele vai comparar com a estimativa estimada que ele usou para decidir o plano de execução que ele escolheu.

Se esses números forem muito divergentes, entre o número de linhas que o otimizador achou que a tabela tinha, baseado na estatística, e por isso ele escolheu um plano de execução e o número real que ele está retornando quando aquele plano de execução está sendo executado - quando esses números são muito diferentes, o Oracle vai criar o que nós chamamos de uma Diretiva do Plano SQL.

Essa diretiva vai ser usada não naquela consulta que está sendo executada naquele momento. Naquele momento, aquela consulta foi perdida, vai demorar porque a decisão foi errada. Mas ele vai gravar essa diretiva para que em uma outra instrução SQL que envolva essa tabela, ele além de se basear na estatística, o otimizador vai ver que existe uma diretiva e ele vai reunir informações adicionais sobre o objeto, por meio de um mecanismo conhecido na Oracle amostragem dinâmica.

Um exemplo simples da nossa a tabela "emp": vamos supor que ao consultar a estatística, o otimizador vai supor que a tabela só tem uma linha e vai escolher o método de varrer a tabela toda como o melhor método para resolver aquela minha consulta SQL. Mas, na hora de rodar, vai retornar milhões de linhas.

Então eu vou gerar uma diretiva futura dizendo: olha, toda vez que você executar de novo um SQL envolvendo a tabela "emp", você checa antes quantas linhas realmente a tabela tem, não se baseia muito na estatística. Essas diretivas, elas vão ficar salvas na tablespace SYSAUX e vão estar associadas à tabela na qual a diretiva se refere. Portanto vão ser benéficas para futuras instruções SQL que envolverem essa mesma tabela "emp". Então é assim que funciona a diretiva do plano SQL.

Sobre o curso Desempenho do Oracle Database: otimização de consultas

O curso Desempenho do Oracle Database: otimização de consultas possui 254 minutos de vídeos, em um total de 57 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

Aprenda SQL e Banco de Dados acessando integralmente esse e outros cursos, comece hoje!

Plus

  • Acesso a TODOS os cursos da plataforma

    Mais de 1200 cursos completamente atualizados, com novos lançamentos todas as semanas, em Programação, Front-end, UX & Design, Data Science, Mobile, DevOps e Inovação & Gestão.

  • Alura Challenges

    Desafios temáticos para você turbinar seu portfólio. Você aprende na prática, com exercícios e projetos que simulam o dia a dia profissional.

  • Alura Cases

    Webséries exclusivas com discussões avançadas sobre arquitetura de sistemas com profissionais de grandes corporações e startups.

  • Certificado

    Emitimos certificados para atestar que você finalizou nossos cursos e formações.

  • Alura Língua (incluindo curso Inglês para Devs)

    Estude a língua inglesa com um curso 100% focado em tecnologia e expanda seus horizontes profissionais.

12X
R$85
à vista R$1.020
Matricule-se

Pro

  • Acesso a TODOS os cursos da plataforma

    Mais de 1200 cursos completamente atualizados, com novos lançamentos todas as semanas, em Programação, Front-end, UX & Design, Data Science, Mobile, DevOps e Inovação & Gestão.

  • Alura Challenges

    Desafios temáticos para você turbinar seu portfólio. Você aprende na prática, com exercícios e projetos que simulam o dia a dia profissional.

  • Alura Cases

    Webséries exclusivas com discussões avançadas sobre arquitetura de sistemas com profissionais de grandes corporações e startups.

  • Certificado

    Emitimos certificados para atestar que você finalizou nossos cursos e formações.

  • Alura Língua (incluindo curso Inglês para Devs)

    Estude a língua inglesa com um curso 100% focado em tecnologia e expanda seus horizontes profissionais.

12X
R$120
à vista R$1.440
Matricule-se
Conheça os Planos para Empresas

Acesso completo
durante 1 ano

Estude 24h/dia
onde e quando quiser

Novos cursos
todas as semanas