Ola,

O plano da execucao das consultas podera sera executado atraves de ferramentas de terceiros ou atraves do proprio oracle. Primeiramente precisamos ter a tabela que contera os planos gerados pelo oracle ou pelas ferramentas. Para isso vamos executar o script $ORACLE_HOME/rdbms/admin/utlxplan.sql:

cd $ORACLE_HOME/rdbms/admin
sqlplus “/ as sysdba”

SQL> create user query identified by query;

Usußrio criado.

SQL> grant resource,connect to query;

ConcessÒo bem-sucedida.

SQL> grant dba to query;

ConcessÒo bem-sucedida.

SQL> create table TBLDADOS as select * from dba_tables;

Tabela criada.

SQL> create table TBLJOIN as select * from dba_tables;

Tabela criada.

SQL>

SQL>@utlxplan.sql;

Agora execute o comando:

SQL> set autotrace on

SQL> explain plan for select * from TBLDADOS, TBLJOIN
where TBLDADOS.TABLE_NAME = TBLJOIN.TABLE_NAME
ORDER BY 1,2,3;

Após a query ser executada, aparecerá no final do SQL*Plus o plano de execução pronto, sem que seja necessário mais nenhum passo.

Para remover é so editar o seguinte comando:

SQL> set autotrace off

Ou ver o resultado que foi inserido na table PLAN_TABLE com a consulta:

SQL>
select
substr (lpad(‘ ‘, level-1) || operation || ‘ (‘ ||
options || ‘)’,1,30 ) “Operation”,
object_name
“Object”
from plan_table
start with id = 0
connect by prior id=parent_id;

ou como na saida dos melhores programas de terceiros:

SQL> select * from table (dbms_xplan.display);

O custo de CPU somente aparece quando as estatísticas de sistema estão habilitadas. “Dynamic sampling used for this statement” significa que esta tabela não esta com estatísticas. O banco também esta configurado para gerar um sample das estatísticas se não existir na tabela. Para ativa-lo coloque na inicializacao este parametro para TRUE mas tome cuidado pode reduzir o performance se for em um ambiente em producao, portanto use e depois volte para false.

TIMED_STATISTICS = TRUE — Parametro de inicializacao do banco

Ou na sessao:

alter session
set timed_statistics=true
alter session
set max_dump_file_size=unlimited
*** DUMP FILE SIZE IS LIMITED TO 1048576 BYTES ***

Resolva o problema criando um indice para as clausulas envolvidas na junção.

SQL> create index TBLJOINIDX1 on TBLJOIN(TABLE_NAME);

SQL> create index TBLDADOSIDX1 on TBLDADOS(TABLE_NAME);

Refaça o plano para verificar as devidas

SQL> explain plan for select * from TBLDADOS, TBLJOIN
where TBLDADOS.TABLE_NAME = TBLJOIN.TABLE_NAME
ORDER BY 1,2,3;

Após a query ser executada, aparecerá no final do SQL*Plus o plano de execução pronto, sem que seja necessário mais nenhum passo.

Para remover é so editar o seguinte comando:

SQL> set autotrace off

Ou ver o resultado que foi inserido na table PLAN_TABLE com a consulta:

SQL>
select
substr (lpad(‘ ‘, level-1) || operation || ‘ (‘ ||
options || ‘)’,1,30 ) “Operation”,
object_name
“Object”
from plan_table
start with id = 0
connect by prior id=parent_id;

Voce deve apagar o conteudo da tabela PLAN_TABLE depois de analisar o resultado, caso utilize a sintaxe sql para consulta-la ao inves da dbms_xplan.

SQL> DELETE FROM plan_table;

Se tiver usando um programa para visualizar a saida o melhor é colocar em uma fonte fixa, como fixedsys ou Misc Fixed.

Create table do PLAN_TABLE que vem com o 9i R2:

create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000));

Para o 10G release 2 ficou mais facil:

A new feature of Oracle 10g Release 2 is that

set autotrace traceonly explainis using DBMS_XPLAN now.

Quote from http://www.oracle.com/technology/oramag/oracle/05-sep/o55asktom.html

SQL> set autotrace traceonly explain

SQL> select *
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and emp.job = ‘CLERK’;

Execution Plan
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –

Plan hash value: 877088642

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
| 0 | SELECT STATEMENT | | 4 | 468 | 7 (15) | 00:00:01 |
|* 1 | HASH JOIN | | 4 | 468 | 7 (15) | 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 4 | 348 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (0) | 00:00:01 |
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
Predicate Information (identified by operation id):
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
1 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)
2 – filter(“EMP”.”JOB”=’CLERK’)
Note
– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
– dynamic sampling used for this statement