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 preoculpar tambem 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ção utilizamos o comando explain plan for select * from <tabela> where <condição>. </condição> </tabela> 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></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.