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
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
Comentários