Este artigo fornece algumas maneiras de formatação dos planos de execução, apresentando
o plano em um formato mais legível.
Esses scripts permitem que DBAs e desenvolvedores vejam os planos de execução de modo mais claro. Os scripts ficam localizados em “$ORACLE_HOME/rdbms/admin/” o primeiro tem o nome de “utlxplan.sql”, este script é utilizado para criar a tabela PLAN_TABLE. O segundo script é o “utlxpls.sql”, este script apresenta o plano de execução formatado.
A tabela PLAN_TABLE é populada com os dados do plano de execução toda vez que a
instrução EXPLAIN PLAN. Uma vez que a PLAN_TABLE é preenchida, as consultas dos planos podem ser geradas acessando os dados armazenados.
IMPORTANTE: Ao usar estes scripts, lembre-se que o uso
repetido da cláusula EXPLAIN
PLAN não exclui
automaticamente os planos de execução antigos, o ideal é remover estes
planos antes de executar novos a fim de evitar resultados
confusos.
A exclusão pode ser realizada manualmente, ou ainda pode-se utilizar a coluna STATEMENT_ID para indicar um nome para o plano de execução a ser consultado.
Criando a tabela PLAN_TABLE
Executar o script “utlxplan.sql” para criar a tabela conforme as instruções abaixo.
SQL> @?/rdbms/admin/utlxplan
Table created.
SQL> desc plan_table
Name
Null? Type
-----------------------------------------
-------- --------------------------
STATEMENT_ID
VARCHAR2(30)
PLAN_ID
NUMBER
TIMESTAMP DATE
REMARKS
VARCHAR2(4000)
OPERATION
VARCHAR2(30)
OPTIONS
VARCHAR2(255)
OBJECT_NODE
VARCHAR2(128)
OBJECT_OWNER
VARCHAR2(30)
OBJECT_NAME
VARCHAR2(30)
OBJECT_ALIAS
VARCHAR2(65)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE
VARCHAR2(30)
OPTIMIZER
VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID
NUMBER(38)
PARENT_ID
NUMBER(38)
DEPTH
NUMBER(38)
POSITION
NUMBER(38)
COST
NUMBER(38)
CARDINALITY
NUMBER(38)
BYTES
NUMBER(38)
OTHER_TAG
VARCHAR2(255)
PARTITION_START
VARCHAR2(255)
PARTITION_STOP
VARCHAR2(255)
PARTITION_ID
NUMBER(38)
OTHER
LONG
DISTRIBUTION
VARCHAR2(30)
CPU_COST
NUMBER(38)
IO_COST
NUMBER(38)
TEMP_SPACE
NUMBER(38)
ACCESS_PREDICATES
VARCHAR2(4000)
FILTER_PREDICATES
VARCHAR2(4000)
PROJECTION
VARCHAR2(4000)
TIME
NUMBER(38)
QBLOCK_NAME
VARCHAR2(30)
OTHER_XML CLOB
Para executar um plano de execução deve-se utilizar o comando abaixo:
SQL> EXPLAIN PLAN FOR [CÓDIGO_SQL];
Segue um exemplo.
SQL> EXPLAIN PLAN FOR SELECT DISTINCT * from emp;
Explained.
Para visualizar o resultado do plano de excução deve-se utilizar o script “utlxpls.sql” conforme
as instruções abaixo.
SQL> @?/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3709190377
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 14 | 1218 |
4 (25)| 00:00:01 |
| 1 | HASH UNIQUE |
| 14 | 1218 |
4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14
| 1218 | 3
(0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for
this statement (level=2)
13 rows selected.
Importante observar que o script “utlxpls.sql”, formata o plano, exibindo as informações
de modo colunar e também coloca observações sobre a consulta, esse item é
interessante para grande consultas que utilizam muitos filtros, através desta
observação é possível verificar a possibilidade de melhoria no código.
AUTOTRACE
Existe também outro mecanismo para a execução dos planos de execução, o AUTOTRACE. Desde a versão do Oracle 7.3.4 o SQL*Plus contém um utilitário chamado autotrace que permite que os planos de consulta a ser exibido em tempo de execução.
Existe também outro mecanismo para a execução dos planos de execução, o AUTOTRACE. Desde a versão do Oracle 7.3.4 o SQL*Plus contém um utilitário chamado autotrace que permite que os planos de consulta a ser exibido em tempo de execução.
Para usar esse utilitário, o
“schema” em uso deverá possuir a tabela PLAN_TABLE.
E para habilitar o plano de execução sem executar o comando EXPLAIN PLAN, usar.
E para habilitar o plano de execução sem executar o comando EXPLAIN PLAN, usar.
SQL> set autotrace traceonly explain
Depois basta simplesmente executar a consulta SQL para que seja apresentada
diretamente no prompt do SQL*Plus, conforme demonstrado abaixo:
SQL> SELECT
DISTINCT * from emp;
Execution Plan
----------------------------------------------------------
Plan hash value:
3709190377
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 14 | 1218 |
4 (25)| 00:00:01 |
| 1 |
HASH UNIQUE | |
14 | 1218 | 4
(25)| 00:00:01 |
| 2 |
TABLE ACCESS FULL| EMP | 14 |
1218 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
(level=2)
Veja que a consulta
não foi executada, somente o plano de execução que foi apresentado.
Referências: Oracle® Database SQL Language Reference 11g Release 2 (11.2)
Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
MSc. Rubens Oliveira
DBA Oracle Consultor
