Oracle: Entendendo o SQL Explain Plan

Referência https://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm

Coletando o Explain

EXPLAIN PLAN FOR
SELECT e.employee_id, j.job_title, e.salary, d.department_name
   FROM employees e, jobs j, departments d
   WHERE  e.employee_id < 103
      AND e.job_id = j.job_id 
      AND e.department_id = d.department_id;

Gerando o output com o comando

select * from table(dbms_xplan.display)

Output

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  3 - filter("E"."EMPLOYEE_ID"<103)
  5 - access("E"."JOB_ID"="J"."JOB_ID")
  7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Termos:

Índices e acesso à tabela

TABLE ACCESS FULL Lê a tabela inteira — todas as linhas e colunas — conforme armazenada no disco. É uma das operações mais caras.

INDEX UNIQUE SCAN O INDEX UNIQUE SCAN executa apenas uma B-tree traversal . O BD usa essa operação se uma restrição exclusiva garantir que os critérios de pesquisa não correspondam a mais de uma entrada.

INDEX RANGE SCAN O INDEX RANGE SCAN realiza a uma B-tree traversal e segue a "leaf node chain" para encontrar todas as entradas correspondentes.

INDEX FULL SCAN Lê todas as linhas do índice. Dependendo de várias estatísticas do sistema, o BD pode executar essa operação se precisar de todas as linhas na ordem do índice - por exemplo, devido a uma cláusula correspondente de ordem por. Em vez disso, o otimizador também pode usar um INDEX FAST FULL SCAN e executar uma operação de classificação adicional.

INDEX FAST FULL SCAN Lê todas as linhas do índice, conforme armazenado no disco. Essa operação geralmente é executada em vez de uma verificação completa da tabela se todas as colunas necessárias estiverem disponíveis no índice. Semelhante ao TABLE ACCESS FULL, o INDEX FAST FULL SCAN pode se beneficiar de operações de leitura de vários blocos.

TABLE ACCESS BY INDEX ROWID Recupera uma linha da tabela usando o ROWID recuperado da pesquisa de índice anterior.

Associations

operações de join, processam apenas duas tabelas por vez.

NESTED LOOPS JOIN Une duas tabelas buscando o resultado de uma tabela e consultando a outra tabela para cada linha da primeira.

HASH JOIN A junção de hash carrega os registros candidatos de um lado da junção em uma tabela de hash que é então sondada para cada linha do outro lado da junção.

MERGE JOIN A junção de join combina duas listas classificadas como um zíper. Ambos os lados da junção devem ser pré-classificados.

Classificando e Agrupamento

SORT ORDER BY Classifica o resultado de acordo com a cláusula order by. Utiliza muita memória para gerar o resultado intermediário.

SORT ORDER BY STOPKEY Classifica um subconjunto do resultado de acordo com a cláusula order by. Usado para consultas top-N se a execução em pipeline não for possível.

SORT GROUP BY Classifica o conjunto de resultados no grupo por colunas e agrega o resultado classificado em uma segunda etapa. Utiliza muita memória para gerar o resultado intermediário (não em pipeline).

SORT GROUP BY NOSORT Agrega um conjunto pré-classificado de acordo com a cláusula group by. Esta operação não armazena em buffer o resultado intermediário: ela é executada em pipeline.

HASH GROUP BY Agrupa o resultado usando uma tabela de hash. Utiliza muita memória para gerar o resultados intermediário (não em pipeline). A saída não é ordenada de forma significativa.

COUNT STOPKEY Aborta as operações subjacentes quando o número desejado de linhas foi buscado.

WINDOW NOSORT STOPKEY Usa uma função de janela (cláusula over) para abortar a execução quando o número desejado de linhas foi buscado.

Ver também

• Mais Artigos sobre Oracle