EXPLAIN PLAN

Depois de um bom tempo sem postar nada aqui no blog, estou voltando a ativa com um bom post. Tive algumas mudanças que estavam me impossibilitando de contribuir mais aqui no blog, como a mudança de emprego. Mas agora voltarei ao blog como antes.

Vamos discutir nesse artigo, algo muito utilizado tanto por desenvolvedores como por DBAs. Vamos verificar o plano de execução de uma query. Qual foi o caminho que foi tomado ao se executar uma instrução. Se foi feita uma busca inteira na tabela, se foi utilizado um index, o custo de se processar uma instrução e etc. Ou seja, todos os passos feitos em uma query, além do custo envolvido, processamento e etc. Iremos aprender os conceitos mais básicos de como verificar e analisar o plano de execução. No futuro, posso fazer um post com informações mais avançadas sobre isso, visto que esse assunto é bem grande.

Para que a nossa query armazene os passos do plano de execução, devemos utilizar o seguinte comando antes da nossa query:

EXPLAIN PLAN FOR 
  SELECT ...

E para verificar qual foi o plano de execução.

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'st1','TYPICAL'));

Primeiro, vamos analisar os parâmetros da função DISPLAY do package DBMS_XPLAN. O primeiro parâmetro é o nome da tabela em que esta armazenado o plano de execução para ser exibido. A tabela PLAN_TABLE é a tabela padrão e poderá ser omitida utilizando o valor null por exemplo. O segundo parâmetro é o statement_id. Podemos sempre utilizar null que será exibido o último plano de execução em que o explain plan não utilizou uma statement_id. Podemos criar vários planos de execução com diferentes statement_id, assim dessa forma podemos comparar qual foi o melhor. Para utilizar um statement_id no plano de execução devemos executar o explain plan da seguinte forma:

EXPLAIN PLAN
  SET STATEMENT = 'NOME' FOR
    SELECT ...

Desa forma, podemos salvar vários planos de execução com statement_id diferentes para análises.

O terceiro e último parâmetro é o formato, em que temos 4 opções. A primeira seria BASIC, em que retorna o mínimo de informação do plano, como o id, operation e nome das colunas do objeto. A segunda opção seria TYPICAL, que é o padrão, em que retorna tudo que já é exibido no BASIC mais algumas informações muito importantes, como o custo da query, cardinalidade, estimativas e etc. A terceira opção seria a SERIAL em que é a mesma coisa retorna a mesma coisa que TYPICAL, menos a informação parallel. E por último temos a opção ALL, que retorna todas as informação da opção TYPICAL e mais algumas informações adicionais, como hints, lista de colunas e etc.

Vamos realizar o nosso primeiro teste, bem simples, um SELECT na tabela dual.

EXPLAIN PLAN FOR 
        SELECT * FROM DUAL;
        
SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(null, NULL,'TYPICAL'));

Resultado:

Plan hash value: 272002086

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
————————————————————————–

A instrução foi o SELECT, e o método de acesso foi um TABLE ACESS FULL, não teve uma cláusula WHERE em coluna nenhuma. Isso quer dizer que todas as linhas da tabela será acessada. Ainda bem que a tabela DUAL só tem uma. O custo foi bem baixo, 2 apenas. O otimizador do Oracle é baseado em custo. O Oracle pode ter vários planos de execução para a mesma query, e para saber qual é a melhor o Oracle irá escolher a que tem o menor custo. O custo representa uma estimativa de recursos que serão utilizados para processar a tal query. Desde uso de memória, processamento, disco e etc.

Mas um TABLE ACCESS FULL nunca é bom. Alguns dos principais tipos de acessos são os seguintes:

FULL TABLE SCAN – O já conhecido, em que toda a tabela será acessada, todas as linhas, excluindo aquelas que não cumprem as regras da cláusula WHERE. Esse tipo de acesso é quando toda a tabela ou uma boa parte dela será acessada. Tipicamente quando não existe índices na tabela ou eles não podem ser utilizados.

TABLE ACCESS BY ROWID – O ROWID indica fisicamente onde esta aquela linha. O Oracle obtém esses ROWIDs através de uma boa cláusula WHERE com utilização de um ou mais índices da tabela. Acessando assim linha por linha baseado nos ROWIDs.

Index Unique Scan – Com esse tipo de acesso apenas uma linha será retornada, utilizando um índice do tipo único. Geralmente quando utilizamos uma consulta com uma Primary Key, sempre com um operador de igualdade.

Index Range Scan – O Oracle consegue todas as ROWIDs desejadas utilizando um index. Esse tipo de acesso é utilizado quando utilizamos uma coluna que tem um index não unique.

Index Skip Scan – Esse tipo de acesso é quando utilizamos todas as colunas de um index menos a primeira. Isso pode ser feito quando existir poucos valores distintos na primeira coluna e muitos valores distintos no restante das colunas.

Index Full Scan – Esse tipo de acesso processa todas as ROWIDs dex um índice. Claro que ele não acessa o index inteiro, mas apenas uma parte necessária para satisfazer a instrução e o custo é mais baixo do que acessar a tabela inteira.

Index Fast Full Scan – Retorna todas as ROWIDs utilizando multi blocos ao contrário do FULL SCAN.

Esses são apenas alguns dos métodos de acessos existentes, para ter mais informações sobre os restantes entre outras informações a respeito do EXPLAIN PLAN acessem a documentação oficial no seguinte link: http://docs.oracle.com/database/121/TGSQL/tgsql_interp.htm

Vamos executar algumas querys com JOINs para ver os difernetes métodos de acessos. Vejama seguir:

SQL> EXPLAIN PLAN FOR
  2  SELECT e.first_name||' '||e.last_name,
  3         d.department_name
  4  FROM hr.employees e
  5  JOIN hr.departments d ON e.department_id = d.department_id;

Resultado:

SQL> SELECT *
  2  FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1850382749

——————————————————————————–
——————

| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |

————————————————————————————————–

PLAN_TABLE_OUTPUT
——————————————————————————–
| 0 | SELECT STATEMENT | | 106 | 7314 | 2 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 106 | 7314 | 2 (0)| 00:00:01 |

| 2 | NESTED LOOPS | | 107 | 7314 | 2 (0)| 00:00:01 |

| 3 | VIEW | index$_join$_001 | 107 | 4173 | 2 (0)| 00:00:01 |

|* 4 | HASH JOIN | | | | | |

| 5 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 4173 | 1 (0)| 00:00:01 |

| 6 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 107 | 4173 | 1 (0)| 00:00:01 |

|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 30 | 0 (0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT
——————————————————————————–

4 – access(ROWID=ROWID)
7 – access(“E”.”DEPARTMENT_ID”=”D”.”DEPARTMENT_ID”)

Note
—–
– dynamic statistics used: dynamic sampling (level=2)

25 rows selected.

Foi feito um JOIN nas tabelas employees e departments, utilizando a FK da tabela departments e a PK da tabela employees, que contém um índice único. Dessa maneira foi utilizado o método INDEX UNIQE SCAN para o JOIN. Não teve uma cláusula WHERE, então seriam retornadas todas as linhas da tabela employees.

Vamos repara em algumas colunas muito importantes também, além do método de acesso. A coluna custo é uma das mais importantes a se observar. Devemos sempre estar atento a essa coluna para que sempre o custo fique o mínimo o possível, assim o banco irá gerar o resultado em menos tempo utilizando menos recursos. O custo nesse caso foi 2, bem pequeno mesmo, visto que estamos utilizando tabelas bem pequenas do schema de exemplo do Oracle(HR). Mas esse custo poderá passar da casa de milhões, ou até mesmo bilhões, se não tivermos cuidados em ambientes de desenvolvimento ou produção, e isso sempre que possível deverá ser evitado, olhando cuidadosamente o plano de execução e verificando se algum método de acesso poderá ser alterado para melhorar a query.

Outra coluna importante seria a coluna cardinalidade, em que exibe apenas uma “estimativa” da quantidade de linhas acessadas em cada operação. O otimizador do Oracle cria essa estimativa baseado em um conjunto de fórmulas complexas e estatísticas. Lembrando que esse número nem sempre será correto.

Bem, isso aqui foi apenas uma introdução ao Explain Plan. No futuro irei fazer um post mais avançado nesse mesmo tema.

Anúncios

Formado em Ciências da Computação pela UFPB com experiência em Servidores Windows Server e Linux e banco de dados Oracle desde 2008 juntamente com os seus serviços. Desenvolvimento de Sistemas em Java SE com banco de dados Oracle e MySQL. Certificado Oracle Certified SQL Expert, mantendo o blog https://oraclepress.wordpress.com/ reconhecido pela OTN, articulista na OTN, portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine.

Marcado com: ,
Publicado em SQL

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Esse Blog é reconhecido pela
Certificações
Sou articulista
Sou articulista

Clique para seguir este blog e receber notificações via email de novos posts.

Tércio Costa

Tércio Costa

Formado em Ciências da Computação pela UFPB com experiência em Servidores Windows Server e Linux e banco de dados Oracle desde 2008 juntamente com os seus serviços. Desenvolvimento de Sistemas em Java SE com banco de dados Oracle e MySQL. Certificado Oracle Certified SQL Expert, mantendo o blog https://oraclepress.wordpress.com/ reconhecido pela OTN, articulista na OTN, portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine.

Links Pessoais

Serviços verificados

Visualizar Perfil Completo →

Total de Visualizações da Página
  • 47,032 Visualizações
%d blogueiros gostam disto: