Como interpretar um plano de acesso no Oracle: Guia completo para otimizar consultas SQL

Olá, caro leitor !

Se você está interessado em aprender como interpretar um plano de acesso no Oracle, a versão mais recente e estável do banco de dados relacional da Oracle, você veio ao lugar certo. Neste artigo, vamos mostrar como você pode usar o plano de acesso, que é uma ferramenta que revela os segredos do otimizador de consultas, que é o responsável por decidir a melhor forma de executar as suas consultas SQL. Vamos apresentar alguns conceitos teóricos e também alguns exemplos práticos, usando o SQL Developer, que é uma ferramenta gráfica e gratuita da Oracle, que permite desenvolver e executar códigos SQL e PL/SQL, entre outras funcionalidades. Vamos usar o Oracle Database 19c como base, mas os conceitos e os códigos apresentados neste artigo também podem ser aplicados a versões anteriores do Oracle Database que suportam o plano de acesso.

O que é o plano de acesso?

O plano de acesso é uma representação do plano de execução de uma consulta SQL, que é a estratégia que o otimizador de consultas do Oracle Database usa para acessar os dados das tabelas envolvidas na consulta. O plano de acesso mostra as operações, os custos, as estimativas, os índices, as estatísticas, entre outras informações que podem ajudar a analisar e a melhorar o desempenho da consulta. O plano de acesso é gerado pelo otimizador de consultas, que é um componente interno do Oracle Database que avalia as possíveis formas de executar uma consulta SQL, e escolhe a mais eficiente, baseado em vários fatores, como as condições da consulta, a estrutura das tabelas, os índices disponíveis, as estatísticas dos dados, os parâmetros do banco de dados, as dicas de otimização, entre outros. O otimizador de consultas é um dos principais responsáveis pelo desempenho das consultas SQL, e por isso é importante entender como ele funciona e como ele pode ser influenciado. Para saber mais sobre o otimizador de consultas, veja .

Como gerar o plano de acesso?

Para gerar o plano de acesso de uma consulta SQL, existem várias formas, como usar comandos SQL, usar ferramentas gráficas, usar pacotes PL/SQL, entre outras. Neste artigo, vamos usar o recurso de explicação de plano do SQL Developer, que é uma forma simples e prática de gerar e visualizar o plano de acesso de uma consulta SQL. Para usar o recurso de explicação de plano do SQL Developer, basta clicar com o botão direito na consulta SQL, e escolher a opção Explain Plan. O resultado da explicação de plano é exibido em uma janela separada, que pode ser visualizada em diferentes formatos, como texto, gráfico ou tabela. Para saber mais sobre o recurso de explicação de plano do SQL Developer, veja .

Como interpretar o plano de acesso?

Para interpretar o plano de acesso de uma consulta SQL, é preciso entender os principais elementos que compõem o plano de acesso, como as operações, os custos, as estimativas, os índices, as estatísticas, entre outros. Vamos ver o que cada um desses elementos significa e como eles podem afetar o desempenho da consulta.

Operações

As operações são as ações que o otimizador de consultas realiza para acessar os dados das tabelas envolvidas na consulta. As operações podem ser de vários tipos, como acesso a tabela, acesso a índice, junção, ordenação, agrupamento, projeção, filtro, entre outros. Cada operação tem um nome, um identificador, um nível, um tipo e um conjunto de opções. O nome da operação indica o tipo de ação que é realizada, como TABLE ACCESS, INDEX RANGE SCAN, NESTED LOOPS, SORT, GROUP BY, etc. O identificador da operação é um número que identifica a operação de forma única no plano de acesso. O nível da operação indica a profundidade da operação na árvore do plano de acesso, sendo que o nível 0 corresponde à operação raiz, que é a última operação a ser executada, e que retorna o resultado final da consulta. O tipo da operação indica o tipo de objeto que é acessado pela operação, como TABLE, INDEX, VIEW, etc. O conjunto de opções da operação indica as características específicas da operação, como FULL, RANGE SCAN, UNIQUE, HASH, MERGE, etc.

As operações são organizadas em uma árvore hierárquica, que representa a ordem de execução das operações. A operação raiz é a primeira a ser exibida no plano de acesso, e as operações filhas são exibidas abaixo da operação pai, com um recuo à direita. A ordem de execução das operações é de baixo para cima, e da esquerda para a direita, ou seja, as operações mais à esquerda e mais abaixo são as primeiras a serem executadas, e as operações mais à direita e mais acima são as últimas a serem executadas. As operações são executadas em blocos, que são unidades de trabalho que o otimizador de consultas usa para processar os dados. Cada bloco é composto por uma ou mais operações, que são executadas em paralelo ou em série, dependendo do tipo de operação e do grau de paralelismo. O resultado de cada bloco é passado para o bloco pai, até chegar ao bloco raiz, que retorna o resultado final da consulta.

Para interpretar as operações do plano de acesso, é preciso entender o que cada operação faz, quais são as suas vantagens e desvantagens, e como elas se relacionam com as outras operações. Algumas operações são mais eficientes do que outras, dependendo do tipo e da distribuição dos dados, da estrutura das tabelas, dos índices disponíveis, das condições da consulta, entre outros fatores. Por exemplo, uma operação de acesso a índice é geralmente mais rápida do que uma operação de acesso a tabela, pois ela evita a leitura de toda a tabela, e usa uma estrutura ordenada e compacta para localizar os dados. Porém, uma operação de acesso a índice pode ser mais lenta do que uma operação de acesso a tabela, se o índice não estiver bem projetado, se a coluna indexada tiver uma baixa seletividade, se a consulta retornar muitas linhas, entre outros fatores. Por isso, é importante analisar as operações do plano de acesso, e verificar se elas estão de acordo com as expectativas, e se elas podem ser melhoradas, usando técnicas como a criação, a remoção ou a alteração de índices, a coleta de estatísticas, o uso de dicas de otimização, entre outras.

Vamos ver um exemplo de como interpretar as operações do plano de acesso. Suponha que temos uma tabela chamada clientes, com as seguintes colunas: id, nome, email, telefone e cidade. Suponha também que temos um índice na coluna cidade, chamado idx_cidade. Suponha ainda que queremos executar uma consulta SQL que retorna os dados dos clientes que moram em São Paulo. A consulta SQL é a seguinte:

-- Retorna os dados dos clientes que moram em São Paulo

SELECT * FROM clientes WHERE cidade = 'São Paulo';

Para gerar o plano de acesso da consulta SQL, vamos usar o recurso de explicação de plano do SQL Developer, clicando com o botão direito na consulta SQL, e escolhendo a opção Explain Plan. O resultado da explicação de plano é exibido em uma janela separada, no formato de texto. O plano de acesso da consulta SQL é o seguinte:

Plan hash value: 123456789

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    50 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CLIENTES  |     1 |    50 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_CIDADE|     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CIDADE"='São Paulo')

Para interpretar o plano de acesso da consulta SQL, vamos analisar as operações que o compõem, começando pela operação raiz, que é a operação 0, do tipo SELECT STATEMENT. Essa operação indica que a consulta SQL é uma instrução de seleção, que retorna os dados dos clientes que moram em São Paulo. Essa operação tem um custo estimado de 2 unidades, que é uma medida relativa do tempo e dos recursos necessários para executar a operação. Essa operação tem um tempo estimado de 1 segundo, que é uma medida aproximada do tempo real de execução da operação. Essa operação tem uma estimativa de 1 linha e 50 bytes, que é o número e o tamanho dos dados que são retornados pela operação.

A operação 0 tem uma operação filha, que é a operação 1, do tipo TABLE ACCESS BY INDEX ROWID, com o nome CLIENTES. Essa operação indica que a consulta SQL acessa a tabela CLIENTES, usando o identificador de linha (ROWID) que é obtido pelo índice. Essa operação tem um custo estimado de 2 unidades, que é o mesmo custo da operação pai, pois ela é a única operação filha. Essa operação tem um tempo estimado de 1 segundo, que é o mesmo tempo da operação pai, pois ela é a única operação filha. Essa operação tem uma estimativa de 1 linha e 50 bytes, que é o mesmo número e tamanho dos dados que são retornados pela operação pai, pois ela é a única operação filha.

A operação 1 tem uma operação filha, que é a operação 2, do tipo INDEX RANGE SCAN, com o nome IDX_CIDADE. Essa operação indica que a consulta SQL usa o índice IDX_CIDADE, que é um índice na coluna CIDADE da tabela CLIENTES, para localizar as linhas que satisfazem a condição da consulta, que é CIDADE = ‘São Paulo’. Essa operação tem um custo estimado de 1 unidade, que é a metade do custo da operação pai, pois ela é a primeira operação a ser executada. Essa operação tem um tempo estimado de 1 segundo, que é a metade do tempo da operação pai, pois ela é a primeira operação a ser executada. Essa operação tem uma estimativa de 1 linha, que é o número de linhas que são encontradas pelo índice, e que são passadas para a operação pai. Essa operação não tem uma estimativa de bytes, pois ela não retorna os dados da tabela, mas apenas os identificadores de linha.

A operação 2 tem uma informação adicional, que é a informação de predicado, que mostra a condição que é usada pela operação para filtrar os dados. A informação de predicado indica que a operação 2 usa um predicado de acesso, que é um predicado que usa o índice para acessar as linhas da tabela. O predicado de acesso é CIDADE = ‘São Paulo’, que é a mesma condição da consulta SQL.

Para interpretar o plano de acesso da consulta SQL, podemos concluir que a consulta SQL usa o índice IDX_CIDADE para localizar as linhas da tabela CLIENTES que têm a coluna CIDADE igual a ‘São Paulo’, e depois acessa a tabela CLIENTES usando o identificador de linha para retornar os dados dessas linhas. O plano de acesso mostra que a consulta SQL tem um baixo custo, um baixo tempo e uma baixa estimativa de linhas e bytes, o que indica que a consulta SQL tem um bom desempenho. O plano de acesso também mostra que a consulta SQL usa o índice IDX_CIDADE de forma eficiente, pois ele tem uma alta seletividade, ou seja, ele retorna poucas linhas em relação ao total de linhas da tabela. O plano de acesso também mostra que a consulta SQL não usa outras operações que poderiam afetar o desempenho, como ordenação, junção, agrupamento, etc.

Como melhorar o desempenho das consultas SQL usando o plano de acesso?

Para melhorar o desempenho das consultas SQL usando o plano de acesso, é preciso analisar o plano de acesso, e verificar se ele está de acordo com as expectativas, e se ele pode ser melhorado, usando técnicas como a criação, a remoção ou a alteração de índices, a coleta de estatísticas, o uso de dicas de otimização, entre outras. Algumas dicas para melhorar o desempenho das consultas SQL usando o plano de acesso são:

Criar índices nas colunas que são usadas nas condições, nas ordenações, nas junções e nas agregações das consultas SQL, pois eles podem acelerar essas operações, evitando a leitura de toda a tabela, e usando uma estrutura ordenada e compacta para localizar os dados.
Remover ou alterar índices que não são usados ou que são ineficientes nas consultas SQL, pois eles podem afetar o desempenho, ocupando espaço, consumindo recursos e exigindo manutenção.
Coletar estatísticas dos dados das tabelas e dos índices envolvidos nas consultas SQL, pois elas são usadas pelo otimizador de consultas para estimar o custo, o tempo, o número e o tamanho dos dados que são retornados pelas operações. As estatísticas devem ser coletadas periodicamente, ou sempre que houver uma alteração significativa nos dados, para que o otimizador de consultas tenha uma visão atualizada e precisa dos dados.
Usar dicas de otimização nas consultas SQL, que são instruções que influenciam o comportamento do otimizador de consultas, forçando ou sugerindo o uso de determinadas operações, índices, parâmetros, etc. As dicas de otimização devem ser usadas com cuidado, pois elas podem afetar o desempenho de forma positiva ou negativa, dependendo do contexto e da situação. As dicas de otimização devem ser usadas como uma solução temporária ou alternativa, e não como uma regra geral.

Agora, suponha que temos uma tabela chamada pedidos, com as seguintes colunas: id, data, valor e cliente_id. Suponha também que temos uma tabela chamada clientes, com as seguintes colunas: id, nome, email, telefone e cidade. Suponha ainda que queremos executar uma consulta SQL que retorna os dados dos pedidos e dos clientes que moram em São Paulo, ordenados pelo valor do pedido. A consulta SQL é a seguinte:

-- Retorna os dados dos pedidos e dos clientes que moram em São Paulo, ordenados pelo valor do pedido

SELECT p.id, p.data, p.valor, c.nome, c.email, c.telefone
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
WHERE c.cidade = 'São Paulo'
ORDER BY p.valor;

O plano de acesso da consulta SQL é o seguinte:

Plan hash value: 987654321

------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   100 |       |    10  (10)| 00:00:01 |
|   1 |  SORT ORDER BY     |          |     1 |   100 |  2048 |    10  (10)| 00:00:01 |
|*  2 |   HASH JOIN        |          |     1 |   100 |       |     9  (12)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| CLIENTES |     1 |    50 |       |     4  (25)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| PEDIDOS  |   100 |  1300 |       |     4  (25)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."CLIENTE_ID"="C"."ID")
   3 - filter("C"."CIDADE"='São Paulo')

Essa operação indica que a consulta SQL é uma instrução de seleção, que retorna os dados dos pedidos e dos clientes que moram em São Paulo, ordenados pelo valor do pedido. Essa operação tem um custo estimado de 10 unidades, que é uma medida relativa do tempo e dos recursos necessários para executar a operação. Essa operação tem um tempo estimado de 1 segundo, que é uma medida aproximada do tempo real de execução da operação. Essa operação tem uma estimativa de 1 linha e 100 bytes, que é o número e o tamanho dos dados que são retornados pela operação.

A operação 0 tem uma operação filha, que é a operação 1, do tipo SORT ORDER BY. Essa operação indica que a consulta SQL ordena os dados dos pedidos e dos clientes que moram em São Paulo, pelo valor do pedido. Essa operação tem um custo estimado de 10 unidades, que é o mesmo custo da operação pai, pois ela é a única operação filha. Essa operação tem um tempo estimado de 1 segundo, que é o mesmo tempo da operação pai, pois ela é a única operação filha. Essa operação tem uma estimativa de 1 linha e 100 bytes, que é o mesmo número e tamanho dos dados que são retornados pela operação pai, pois ela é a única operação filha. Essa operação tem um espaço temporário de 2048 bytes, que é o espaço que é usado para armazenar os dados que são ordenados pela operação.

A operação 1 tem uma operação filha, que é a operação 2, do tipo HASH JOIN. Essa operação indica que a consulta SQL junta os dados das tabelas PEDIDOS e CLIENTES, usando um algoritmo de junção por hash, que é um método que usa uma função de hash para particionar e comparar os valores das colunas de junção. Essa operação tem um custo estimado de 9 unidades, que é a maior parte do custo da operação pai, pois ela é a primeira operação a ser executada. Essa operação tem um tempo estimado de 1 segundo, que é a maior parte do tempo da operação pai, pois ela é a primeira operação a ser executada. Essa operação tem uma estimativa de 1 linha e 100 bytes, que é o número e o tamanho dos dados que são retornados pela operação, e que são passados para a operação pai.

A operação 2 tem duas operações filhas, que são as operações 3 e 4, do tipo TABLE ACCESS FULL, com os nomes CLIENTES e PEDIDOS, respectivamente. Essas operações indicam que a consulta SQL acessa as tabelas CLIENTES e PEDIDOS, usando uma leitura completa, que é um método que lê todas as linhas e colunas das tabelas, sem usar índices. Essas operações têm um custo estimado de 4 unidades cada, que é a metade do custo da operação pai, pois elas são executadas em paralelo. Essas operações têm um tempo estimado de 1 segundo cada, que é a metade do tempo da operação pai, pois elas são executadas em paralelo. A operação 3 tem uma estimativa de 1 linha e 50 bytes, que é o número e o tamanho dos dados que são retornados pela operação, e que são usados pela operação pai para fazer a junção. A operação 4 tem uma estimativa de 100 linhas e 1300 bytes, que é o número e o tamanho dos dados que são retornados pela operação, e que são usados pela operação pai para fazer a junção.

A operação 3 tem uma informação adicional, que é a informação de predicado, que mostra a condição que é usada pela operação para filtrar os dados. A informação de predicado indica que a operação 3 usa um predicado de filtro, que é um predicado que é aplicado após a leitura da tabela, e que reduz o número de linhas que são retornadas pela operação. O predicado de filtro é CIDADE = ‘São Paulo’, que é a mesma condição da consulta SQL.

A operação 2 também tem uma informação adicional, que é a informação de predicado, que mostra a condição que é usada pela operação para fazer a junção. A informação de predicado indica que a operação 2 usa um predicado de acesso, que é um predicado que é usado para comparar os valores das colunas de junção, e que determina quais linhas das tabelas são combinadas pela operação. O predicado de acesso é P.CLIENTE_ID = C.ID, que é a condição de junção da consulta SQL.

Para interpretar o plano de acesso da consulta SQL, podemos concluir que a consulta SQL lê todas as linhas e colunas das tabelas PEDIDOS e CLIENTES, filtra as linhas da tabela CLIENTES que têm a coluna CIDADE igual a ‘São Paulo’, junta as linhas das tabelas PEDIDOS e CLIENTES que têm a coluna CLIENTE_ID igual a ID, ordena os dados pelo valor do pedido, e retorna os dados dos pedidos e dos clientes que moram em São Paulo. O plano de acesso mostra que a consulta SQL tem um alto custo, um alto tempo e uma alta estimativa de linhas e bytes, o que indica que a consulta SQL tem um baixo desempenho. O plano de acesso também mostra que a consulta SQL não usa índices para acessar as tabelas PEDIDOS e CLIENTES, o que implica em uma leitura completa das tabelas, que é um método lento e ineficiente. O plano de acesso também mostra que a consulta SQL usa uma operação de ordenação, que consome espaço temporário e recursos adicionais. O plano de acesso também mostra que a consulta SQL usa uma operação de junção por hash, que é um método que pode ser rápido e eficiente, mas que depende da distribuição dos dados e do tamanho das tabelas.

Para melhorar o desempenho da consulta SQL, podemos usar algumas técnicas, como:

Criar índices nas colunas que são usadas nas condições, nas ordenações e nas junções da consulta SQL, como CIDADE, CLIENTE_ID e VALOR, pois eles podem acelerar essas operações, evitando a leitura completa das tabelas, e usando uma estrutura ordenada e compacta para localizar os dados.
Coletar estatísticas dos dados das tabelas e dos índices envolvidos na consulta SQL, pois elas são usadas pelo otimizador de consultas para estimar o custo, o tempo, o número e o tamanho dos dados que são retornados pelas operações. As estatísticas devem ser coletadas periodicamente, ou sempre que houver uma alteração significativa nos dados, para que o otimizador de consultas tenha uma visão atualizada e precisa dos dados.
Usar dicas de otimização na consulta SQL, que são instruções que influenciam o comportamento do otimizador de consultas, forçando ou sugerindo o uso de determinadas operações, índices, parâmetros, etc. As dicas de otimização devem ser usadas com cuidado, pois elas podem afetar o desempenho de forma positiva ou negativa, dependendo do contexto e da situação. As dicas de otimização devem ser usadas como uma solução temporária ou alternativa, e não como uma regra geral.

Criemos os seguintes índices nas tabelas PEDIDOS e CLIENTES:

-- Cria um índice na coluna CIDADE da tabela CLIENTES

CREATE INDEX idx_cidade ON clientes (cidade);

-- Cria um índice na coluna CLIENTE_ID da tabela PEDIDOS

CREATE INDEX idx_cliente_id ON pedidos (cliente_id);

-- Cria um índice na coluna VALOR da tabela PEDIDOS

CREATE INDEX idx_valor ON pedidos (valor);

Vamos coletar as estatísticas dos dados das tabelas e dos índices, usando o seguinte comando:

-- Coleta as estatísticas dos dados das tabelas e dos índices

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(USER);

Vamos usar a seguinte dica de otimização na consulta SQL, para forçar o uso do índice idx_valor na ordenação:

-- Retorna os dados dos pedidos e dos clientes que moram em São Paulo, ordenados pelo valor do pedido, usando a dica de otimização INDEX_ASC

SELECT /*+ INDEX_ASC(p idx_valor) */ p.id, p.data, p.valor, c.nome, c.email, c.telefone 
FROM pedidos p 
JOIN clientes c 
ON p.cliente_id = c.id 
WHERE c.cidade = ‘São Paulo’ 
ORDER BY p.valor;

O resultado da explicação de plano é exibido em uma janela separada, no formato de texto. O plano de acesso da consulta SQL é o seguinte:

Plan hash value: 123456789

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |   100 |     4   (0)| 00:00:01 |
|   1 |  SORT ORDER BY               |              |     1 |   100 |     4   (0)| 00:00:01 |
|*  2 |   HASH JOIN                  |              |     1 |   100 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CLIENTES    |     1 |    50 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN         | IDX_CIDADE   |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| PEDIDOS     |     1 |    50 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN         | IDX_CLIENTE_ID|    1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("P"."CLIENTE_ID"="C"."ID")
   4 - access("C"."CIDADE"='São Paulo')
   6 - access("P"."CLIENTE_ID"="C"."ID")

Essa operação indica que a consulta SQL é uma instrução de seleção, que retorna os dados dos pedidos e dos clientes que moram em São Paulo, ordenados pelo valor do pedido. Essa operação tem um custo estimado de 4 unidades, que é uma medida relativa do tempo e dos recursos necessários para executar a operação. Essa operação tem um tempo estimado de 1 segundo, que é uma medida aproximada do tempo real de execução da operação. Essa operação tem uma estimativa de 1 linha e 100 bytes, que é o número e o tamanho dos dados que são retornados pela operação.

A operação 0 tem uma operação filha, que é a operação 1, do tipo SORT ORDER BY. Essa operação indica que a consulta SQL ordena os dados dos pedidos e dos clientes que moram em São Paulo, pelo valor do pedido. Essa operação tem um custo estimado de 4 unidades, que é o mesmo custo da operação pai, pois ela é a única operação filha. Essa operação tem um tempo estimado de 1 segundo, que é o mesmo tempo da operação pai, pois ela é a única operação filha. Essa operação tem uma estimativa de 1 linha e 100 bytes, que é o mesmo número e tamanho dos dados que são retornados pela operação pai, pois ela é a única operação filha. Essa operação não tem um espaço temporário, pois ela usa o índice idx_valor para ordenar os dados, sem precisar armazená-los em um espaço adicional.

A operação 1 tem uma operação filha, que é a operação 2, do tipo HASH JOIN. Essa operação indica que a consulta SQL junta os dados das tabelas PEDIDOS e CLIENTES, usando um algoritmo de junção por hash, que é um método que usa uma função de hash para particionar e comparar os valores das colunas de junção. Essa operação tem um custo estimado de 3 unidades, que é a maior parte do custo da operação pai, pois ela é a primeira operação a ser executada. Essa operação tem um tempo estimado de 1 segundo, que é a maior parte do tempo da operação pai, pois ela é a primeira operação a ser executada. Essa operação tem uma estimativa de 1 linha e 100 bytes, que é o número e o tamanho dos dados que são retornados pela operação, e que são passados para a operação pai.

A operação 2 tem duas operações filhas, que são as operações 3 e 5, do tipo TABLE ACCESS BY INDEX ROWID, com os nomes CLIENTES e PEDIDOS, respectivamente. Essas operações indicam que a consulta SQL acessa as tabelas CLIENTES e PEDIDOS, usando o identificador de linha (ROWID) que é obtido pelo índice. Essas operações têm um custo estimado de 1 unidade cada, que é a metade do custo da operação pai, pois elas são executadas em paralelo. Essas operações têm um tempo estimado de 1 segundo cada, que é a metade do tempo da operação pai, pois elas são executadas em paralelo. A operação 3 tem uma estimativa de 1 linha e 50 bytes, que é o número e o tamanho dos dados que são retornados pela operação, e que são usados pela operação pai para fazer a junção. A operação 5 tem uma estimativa de 1 linha e 50 bytes, que é o número e o tamanho dos dados que são retornados pela operação, e que são usados pela operação pai para fazer a junção.

As operações 3 e 5 têm cada uma uma operação filha, que são as operações 4 e 6, do tipo INDEX RANGE SCAN, com os nomes IDX_CIDADE e IDX_CLIENTE_ID, respectivamente. Essas operações indicam que a consulta SQL usa os índices IDX_CIDADE e IDX_CLIENTE_ID, que são índices nas colunas CIDADE da tabela CLIENTES e CLIENTE_ID da tabela PEDIDOS, para localizar as linhas que satisfazem as condições da consulta, que são CIDADE = ‘São Paulo’ e CLIENTE_ID = ID. Essas operações têm um custo estimado de 1 unidade cada, que é a metade do custo da operação pai, pois elas são as primeiras operações a serem executadas. Essas operações têm um tempo estimado de 1 segundo cada, que é a metade do tempo da operação pai, pois elas são as primeiras operações a serem executadas. Essas operações têm uma estimativa de 1 linha cada, que é o número de linhas que são encontradas pelos índices, e que são passadas para as operações pai.

As operações 4 e 6 têm cada uma uma informação adicional, que é a informação de predicado, que mostra a condição que é usada pela operação para filtrar os dados. A informação de predicado indica que as operações 4 e 6 usam predicados de acesso, que são predicados que usam os índices para acessar as linhas das tabelas. Os predicados de acesso são CIDADE = ‘São Paulo’ e CLIENTE_ID = ID, que são as mesmas condições da consulta SQL.

Para interpretar o plano de acesso da consulta SQL, podemos concluir que a consulta SQL usa os índices IDX_CIDADE e IDX_CLIENTE_ID para localizar as linhas das tabelas CLIENTES e PEDIDOS que têm a coluna CIDADE igual a ‘São Paulo’ e a coluna CLIENTE_ID igual a ID, respectivamente, e depois acessa as tabelas CLIENTES e PEDIDOS usando o identificador de linha para retornar os dados dessas linhas. Em seguida, a consulta SQL junta os dados das tabelas CLIENTES e PEDIDOS usando um algoritmo de junção por hash, e ordena os dados pelo valor do pedido, usando o índice idx_valor. Por fim, a consulta SQL retorna os dados dos pedidos e dos clientes que moram em São Paulo. O plano de acesso mostra que a consulta SQL tem um baixo custo, um baixo tempo e uma baixa estimativa de linhas e bytes, o que indica que a consulta SQL tem um bom desempenho. O plano de acesso também mostra que a consulta SQL usa os índices IDX_CIDADE, IDX_CLIENTE_ID e IDX_VALOR de forma eficiente, pois eles têm uma alta seletividade, ou seja, eles retornam poucas linhas em relação ao total de linhas das tabelas. O plano de acesso também mostra que a consulta SQL não usa operações que poderiam afetar o desempenho, como leitura completa, espaço temporário e recursos adicionais. O plano de acesso também mostra que a consulta SQL usa uma operação de junção por hash, que é um método que pode ser rápido e eficiente, mas que depende da distribuição dos dados e do tamanho das tabelas.

Espero que este artigo tenha sido útil para você aprender como interpretar um plano de acesso no Oracle, e como usar essa ferramenta para melhorar o desempenho das suas consultas SQL. Lembre-se que o plano de acesso é uma forma de entender o que o otimizador de consultas faz, e não o que ele deveria fazer. Por isso, é importante analisar o plano de acesso com cuidado, e verificar se ele está de acordo com as expectativas, e se ele pode ser melhorado, usando técnicas como a criação, a remoção ou a alteração de índices, a coleta de estatísticas, o uso de dicas de otimização, entre outras. Se você quiser saber mais sobre o plano de acesso, o otimizador de consultas, e outras questões relacionadas ao desempenho das consultas SQL no Oracle, eu recomendo as referências abaixo.

Obrigado pela sua atenção, e até a próxima ! Valeuuuu !

Referências

Oracle Database SQL Tuning Guide, disponível em https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/index.html
Oracle Database Performance Tuning Guide, disponível em https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdbp/index.html
Oracle Database Concepts, disponível em https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/index.html
Oracle Database SQL Language Reference, disponível em https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/index.html
Oracle Database PL/SQL Language Reference, disponível em https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/index.html
SQL Developer User’s Guide, disponível em https://docs.oracle.com/en/database/oracle/sql-developer/19.4/index.html

Como interpretar um plano de acesso no Oracle: Guia completo para otimizar consultas SQL