Utilizando o plano de execução: Melhore a performance das suas consultas com o SQL Execution Plan da Oracle !

Utilizando o plano de execução: Melhore a performance das suas consultas com o SQL Execution Plan da Oracle !

Olá, pessoal!

Muitos desenvolvedores principalmente quando estão iniciando a profissão se preoculpam somente em realizar consultas que funcionem retornando os dados desejados, porem devem se preocupar também em garantir que estas consultas tenham o melhor custo possível facilitando a vida do DBA e garantindo mais qualidade nas suas aplicações.Neste artigo vamos utilizar o plano de execução (SQL Execution Plan) da Oracle para verificar a performance das consultas.

Quando um SQL e executado no DB Oracle, o (CBO) Cost based optimizer ou seja otimizador baseado em custo usa as estatísticas do banco de dados para criar um plano de execução que vai ajudar o desenvolvedor a retornar os dados da melhor maneira possível.

O plano de execução é armazenado em uma tabela chamada plan table, mas dependendo dos critérios de instalação do seu banco esta pode não ter sido criada. Caso isto não tenha acontecido vamos cria-la. Abaixo você verá como criar a tabela no usuário SYS e deixa-la disponível para todos os usuários.O script de criação desta tabela ficam na pasta onde foi instalado seu BD, ou seja no seu oracle home.

\ORACLE_HOME \product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql

Para executar o script acesse o banco pelo usuário SYS

SQL>CONN sys/password AS SYSDBA

Após conectado executa-se o script

SQL> @$ORACLE_HOME\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql;

Para liberar deixar disponível para todos os usuários de os grant’s e crie um sinônimo.

SQL> Grant all on sys.plan_table to public;

Grant succeeded.

SQL> Create public synonym plan_table for sys.plan_table;

Agora a tabela esta pronta para ser utilizada!

O Plano de execução ou Explain Plan quando executado grava as informações de performance na tabela plan table sem executar a query. Ela apenas coleta os dados estatísticos e mostra um plano de execução evitando que consultas muito pesadas ou mal projetadas efetem a performance do banco sobrecarregando a memória. Para executar o plano de execuçãoutilizamos o comando explain plan for select * from <tabela> where <condição>. Porem para que você saiba se o plano de execução que será mostrado e o seu, deve-se colocar o comando set statement id = ‘ID’ .O ID escolhido equivale a coluna statement_id da tabela Plan_table que tem o formato Varchar2(30).

Com isso o comando para gerar o plano de execução ficaria assim:

SQL>explain plan SET STATEMENT_ID='ID' for Select * From <TABELA>

Depois para visualizar o plano utilizamos a procedure display da package DBMS_XPLAN.

SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','ID','ALL'));

Exemplo:

SQL> select plan_table_output from table(dbms_xplan.display('plan_table','TESTE','serial'));

PLAN_TABLE_OUTPUT

Plan hash value: 3956160932

| Id  | Operation                  | Name | Rows  | Bytes | Cost |

|   0 | SELECT STATEMENT  |           |    14    |   518 |      3 |

|   1 |  TABLE ACCESS FULL | EMP    |    14    |   518 |      3 |

Podemos então identificar que caso façamos essa consulta no nosso banco iremos acessar a tabela completa (TABLE ACCESS FULL), serão retornadas 14 linhas (Rows) com um uso de 518 bytes e um custo (Cost) igual a 3.