DicasSQL

Prévia do material em texto

Dicas para otimização de consultas
�
Índice
3Introdução	�
41. Método de Acesso	�
41.1 Full Table Scan	�
41.2 Table Access by Rowid	�
51.3 Index Scan	�
82. Tipos de Join	�
82.1 Nested Loops	�
92.2 Sort-Merge	�
102.3 Hash Join	�
112.5 Star Join	�
122.6 Star transformation	�
143. Hints	�
143.1 Tipo de Otimizador	�
143.2 Subquery / View	�
153.3 Acesso	�
163.4 Join	�
173.5 Parallel Query (PQ)	�
173.7 Variados	�
184. Dicas	�
194.1 Evitar função em coluna indexada	�
204.2 Join com colunas do mesmo tipo	�
224.3 Coletar as estatísticas com regularidade	�
244.4 Sql Trace	�
�
Introdução
Este documento tem o objetivo de auxiliar os desenvolvedores e dbas na otimização de consultas.
Inicialmente revisaremos alguns pontos importantes: os métodos de acesso, os algoritmos usados para realização do join e os hints usados para modificar o plano de execução das consultas.
Em seguida veremos algumas dicas para se aproveitar o máximo da eficiência deste banco de dados.
 �
1. Método de Acesso
Representa o tipo de acesso para obter os dados de uma determinada tabela. O Oracle possui os seguintes métodos: 
1.1 Full Table Scan
Realiza a leitura direta da tabela para determinar os registros que satisfazem a cláusula Where
Permite o uso do multiblock I/O (leitura de vários blocos seqüenciais com um simples I/O)
Permite a paralelizar ção
Nem sempre representa um método de acesso ruim
Representação no explain plan:
OPERATION OPTIONS OBJECT_NAME 
------------------------------ -------------------- ---------------
 0 SELECT STATEMENT .
 1 SORT AGGREGATE .
 2 TABLE ACCESS FULL TESTE.TAB_LOC
1.2 Table Access by Rowid
Realiza o acesso a tabela pelo Rowid
É a maneira mais rápida de acessar um registro
Representação no explain plan:
OPERATION OPTIONS OBJECT_NAME 
------------------------------ -------------------- -------------------
 0 SELECT STATEMENT .
 1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA
�
1.3 Index Scan
Realiza o acesso aos dados através de um índice
A maioria é do tipo b*-tree (padrão)
Estrutura interna da B*-tree
Possui os seguintes tipos:
Unique Scan: é usado quando a tabela possui uma chave única ou primária. Retorna apenas 1 rowid.	
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- -------------------
 0 SELECT STATEMENT .
 1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 2 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK
Range Scan: é usado para obter um determinado intervalo de dados. Retorna 0 ou mais rowids.
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- --------------------
 0 SELECT STATEMENT .
 1 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA
Full Scan: é usado quando a query usa pelo menos uma coluna do índice. Também é usado quando a consulta não possui predicado e se deseja evitar a operação de sort.
Fast Full Scan: é um Full Table Scan alternativo usado quando o índice possui todas colunas necessárias para a query. Para ler todos dados do índice, é mais rápido que o Index Scan por possibilitar o Multiblock I/O e a paralelização. 
 Obs: não pode ser usado para evitar a operação de sort (os dados não são retornados de forma ordenada). 
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- -------------------
 0 SELECT STATEMENT .
 1 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA
Index Join: é um hash join com alguns índices da tabela. Juntos obtêm todas colunas referenciadas na query.
OPERATION OPTIONS OBJECT_NAME 
------------------------- ----------------- -----------------------
 0 SELECT STATEMENT .
 1 VIEW TESTE.index$_join$_001
 2 HASH JOIN .
 3 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA
 4 INDEX FAST FULL SCAN TESTE.TAB_LOC_PK
Bitmap: utiliza um bitmap como chave para cada valor distinto da coluna e uma função de mapeamento que converte cada bit para um rowid. Se a coluna indexada tiver baixa cardinalidade, são rápidos e ocupam pouco espaço.
 Obs: uma coluna é considerada de baixa cardinalidade se o número de valores distintos for menor ou igual a 1% do total de registros.
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- --------------------
 0 SELECT STATEMENT .
 1 TABLE ACCESS BY INDEX ROWID TESTE.VENDAS
 2 BITMAP CONVERSION TO ROWIDS .
 3 BITMAP INDEX SINGLE VALUE TESTE.VENDAS_TIPO_BM
�
Exemplo: tabela Customer com 6 registros
 
 
 
 Índice Bitmap sobre a coluna Region 
 
Consulta de clientes casados e da região central e oeste.
			SELECT COUNT(*) FROM customer
WHERE STATUS = 'married' 
 AND REGION IN ('central','west');
		
	 
�
2. Tipos de Join
O join é uma operação que permite combinar o resultado de duas ou mais tabelas baseando se nos valores das colunas em comum. O Oracle utiliza os seguintes algoritmos 
2.1 Nested Loops
O otimizador escolhe uma tabela para ser a outer table (driving table) e outra para ser a inner table
Para cada registro da outer table, o Oracle acha todos registros da inner table que satisfazem o join condition
O Oracle combina os dados de cada par de registros que satisfazem o join condition e então retorna os registros resultantes
select *
 from tab_loc l,
 tab_loc_peq p
 where l.codigo = p.codigo;
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- ------------------
 0 SELECT STATEMENT .
 1 NESTED LOOPS .
 2 TABLE ACCESS FULL TESTE.TAB_LOC_PEQ
 3 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 4 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK
 
2.2 Sort-Merge
Os registros de cada tabela são ordenados pelas colunas do join condition
É feito um merge das duas ordenações e os registros que satisfizerem o join condition são retornados
select /*+ use_merge(l p) */ *
 from tab_loc l,
 tab_loc_peq p
 where l.codigo = p.codigo;
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- ----------------------
 0 SELECT STATEMENT .
 1 MERGE JOIN .
 2 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_PEQ
 3 INDEX FULL SCAN TESTE.TAB_LOC_PEQ_PK
 4 SORT JOIN .
 5 TABLE ACCESS FULL TESTE.TAB_LOC
 
 
Obs: a etapa 2 não precisou realizar o sort porque o range scan já retornou os dados ordenados.
�
2.3 Hash Join
O Oracle escolhe uma tabela menor para construir a tabela hash e o maior para verificar a tabela hash
Geralmente o Hash Join é mais rápido que o Sort-Merge
select /*+ use_hash(l) */ *
 from tab_loc l,
 tab_loc_peq p
 where l.codigo = p.codigo;
OPERATION OPTIONS OBJECT_NAME 
------------------------- -------------------- --------------------
 0 SELECT STATEMENT.
 1 HASH JOIN .
 2 TABLE ACCESS FULL TESTE.TAB_LOC_PEQ
 3 TABLE ACCESS FULL TESTE.TAB_LOC
 
�
2.5 Star Join
Join realizado entre uma tabela fato e algumas dimensões (tabelas lookup com informações dos atributos da tabela fato)
Geralmente é usado quando o schema tem tabelas fato grandes e dimensões bem pequenas
Cada tabela lookup possui um join com a tabela fato (PK_Lookup = FK_Fato_Lookup), mas as tabelas lookups não têm join entre si.
Os joins são realizados nas seguintes etapas: 
produto cartesiano entre as dimensões 
join com a tabela fato usando um índice concatenado por nested loops
Exemplo de um star join
 
 
Não confunfir o start join com o star transformation
 �
2.6 Star transformation
Transformação de uma consulta realizada entre uma tabela fato e algumas dimensões 
É realizado apenas se houver redução no custo
Não há necessidade de criar índices concatenados para atender a combinação de tabelas referenciadas, pois combina índices bitmap da tabela fato
Pré-requisitos
- Deve existir um índice bitmap para cada FK
- STAR_TRANSFORMATION_ENABLED = TRUE
- CBO deve estar sendo usado
A consulta é realizada da em 2 fases:
O Oracle transforma a consulta e utiliza os índices bitmap das colunas FKs da tabela fato para obter o result set (conjunto exato dos registros necessários para avaliar a consulta). Em seguida os registros da tabela fato são obtidos.
O join entre a tabela fato e as dimensões é realizado. Geralmente o hash join é o mais eficiente.
Exemplo:
Comando original
Tabela fato: sales
Dimensões : store, time e product
SELECT store.sales_district,
 time.fiscal_period,
 SUM(sales.dollar_sales) revenue,
 SUM(dollar_sales) - SUM(dollar_cost) income
FROM sales, store, time, product
WHERE sales.store_key = store.store_key 
 AND sales.time_key = time.time_key 
 AND sales.product_key = product.product_key 
 AND time.fiscal_period IN ('3Q95', '4Q95', '1Q96') 
 AND product.department = 'Grocery' 
 AND store.sales_district IN ('San Francisco', 'Los Angeles')
GROUP BY store.sales_district, time.fiscal_period;
Transformação 
1ª parte: obtenção do result set
SELECT ... 
FROM sales
WHERE store_key IN (SELECT store_key FROM store 
 WHERE sales_district IN ('WEST','SOUTHWEST')) 
 AND time_key IN (SELECT time_key FROM time 
 WHERE quarter IN ('3Q96', '4Q96', '1Q97')) 
 AND product_key IN (SELECT product_key FROM product 
 WHERE department = 'GROCERY');
2ª parte: join entre o result set e as dimensões
�
Plano de execução
SELECT STATEMENT
 HASH JOIN
 HASH JOIN
 HASH JOIN
 TABLE ACCESS SALES BY INDEX ROWID
 BITMAP CONVERSION TO ROWIDS
 BITMAP AND
 BITMAP MERGE
 BITMAP KEY ITERATION
 TABLE ACCESS STORE FULL
 BITMAP INDEX SALES_STORE_KEY RANGE SCAN
 BITMAP MERGE
 BITMAP KEY ITERATION
 TABLE ACCESS TIME FULL
 BITMAP INDEX SALES_TIME_KEY RANGE SCAN
 BITMAP MERGE
 BITMAP KEY ITERATION
 TABLE ACCESS PRODUCTS FULL
 BITMAP INDEX SALES_PRODUCT_KEY RANGE SCAN
 TABLE ACCESS TIME FULL
 TABLE ACCESS PRODUCTS FULL
 TABLE ACCESS STORE FULL
�
3. Hints
Hint é um comentário padronizado que se inclui no meio do comando para modificar o plano de execução. Geralmente ele é utilizado nos casos em que o Oracle não consegue definir uma boa estratégia.
Sintaxe: 
 select /*+ Hint... */ 
 colunas ...
 from tabelas ...
Abaixo segue a listagem dos hints para consultas:
3.1 Tipo de Otimizador
	
All_rows – força o cost-based optimizer(CBO) a otimizar o comando para retornar todos registros com o menor tempo possível (maior throughput)
First_rows – força o CBO a otimizar o comando para obter o melhor tempo de resposta
Rule – força a utilização do rule-based optimizer(RBO) 
Choose – deixa para o Oracle optar por CBO ou RBO. Se pelo menos uma das tabelas possuir estatísticas coletadas então CBO é optado, senão é considerado o RBO.
3.2 Subquery / View
	
Push_Subq – executa as subqueries antes. Normalmente são executadas por último.
	Exemplo
 select /*+ push_subq */ 
 count(*)
 from tabela_teste
 where codigo in (select codigo 
 from tabela_teste2
 where tipo = ‘T’);
 
Merge(v) – força o merge da view com a query principal
	Exemplo
 select /*+ merge(v) */
 count(*)
 from view_teste v,
 tabela_teste t
 where v.codigo = t.codigo;
�
No_Merge(v) – previne o merge da view com a query principal
	Exemplo
 select /*+ no_merge(v) */ 
 count(*)
 from view_teste v,
 tabela_teste t
 where v.codigo = t.codigo;
 
Merge_Aj – transforma o NOT IN subquery em sort-merge anti-join
 
Hash_Aj – transforma o NOT IN subquery em hash anti-join
Merge_Sj - transforma o EXISTS em sort-merge semi-join mantendo a semântica. O default do EXISTS é usar o algoritmo nested loops. 
 Obs: nos casos em que a tabela que está sendo verificada (tabela dentro do EXISTS) não possuir índice nas colunas do join, haverá um ganho de performance se o sort-merge semi-join for usado.
Hash_Sj – transforma o EXISTS em hash semi-join mantendo a semântica. O default do EXISTS é usar o algoritmo nested loops.
 Obs: nos casos em que a tabela que está sendo verificada (tabela dentro do EXISTS) não possuir índice nas colunas do join, haverá um ganho de performance se o hash semi-join for usado.
Push_Join_Pred(v) – força a view usar o join predicate da query principal
3.3 Acesso
Full(tab) – força o full table scan para tabela ‘tab’
Cache(tab) – força que blocos retornados da consulta sejam colocados na lista LRU do buffer cache quando o full table scan for executado, se o tamanho da tabela for menor ou igual ao valor do parâmetro CACHE_SIZE_THRESHOLD.
Nocache(tab) – força que os blocos retornados da consulta não sejam colocados na cache, mesmo que a tabela tenha o parâmetro cache habilitado.
Rowid(tab) – força o acesso a tabela ‘tab’ pelo rowid
Index(tab index) – força o acesso a tabela ‘tab’ pelo índice ‘index’
Index_Asc(tab index) – induz o Oracle a acessar o índice ‘index’ da tabela ‘tab’ com um range scan
Index_Desc(tab index) – induz o Oracle a acessar o índice ‘index’ da tabela ‘tab’ na ordem decrescente
Ndex_Ffs(tab index) – opta pelo fast full scan ao invés do full table scan
Index_Combine(tab i1.. i5) – realiza a combinação booleana de índices bitmap com melhor custo.
Index_join(tab i1.. i5) – induz a utilização index join
And_Equal(tab i1.. i5) – realiza o merge de 2 a 5 índices com apenas 1 coluna.
Use_Concat – transforma a combinação de ORs/IN na cláusula WHERE em uma query composta com operador de conjunto UNION ALL. 
No_Expand – não permite a concatenação 
Driving_Site(tab) – a query é executada no site que a tabela ‘tab’ se encontra
	
3.4 Join
Use_Nl(tab) – induz o Oracle a optar a tabela ‘tab’ como sendo a inner table.
Use_Merge(tab) – induz o Oracle a realizar o join da tabela ‘tab’ com o método sort-merge.
Use_Hash(tab) – induz o Oracle a realizar o join da tabela ‘tab’ com o método hash join.
Star – induz o Oracle a realizar o start query
Star_Transformation – induz o Oracle a optar pelo melhor plano com Star Transformation. A transformação só é realizada se for benéfico.
Ordered – induz o Oracle a executar o join na ordem em que as tabelas se encontram na cláusula FROM (da esquerda para direita ede cima para baixo).
Leading(tab) - induz o Oracle iniciar o join com a tabela ‘tab’.
3.5 Parallel Query (PQ)
Parallel (tab,degree[,inst]) – induz o Oracle a paralelizar a consulta a tabela ‘tab’ pelo grau de paralelismo ‘degree’ com ‘inst’ instâncias.
Parallel_Index(table,[index,[degree[,inst]]]) - induz o oracle a paralelizar o acesso de índice particionado.
Pq_Distribute(tab,out,in)- indica como distribuir os registros da tabela ‘tab’ em uma PQ entre produtores e consumidores. Os valors para ‘out’ (outer table) e ‘in’ (inner table) podem ser: hash, none, broadcast ou partition.
Noparallel(tab) – evita a paralelização do acesso a tabela ‘tab’
Noparallel_Index(tab) – evita o parallel index scan
3.7 Variados
Rewrite(v1[,v2]) – permite que uma query compatível com a consulta de uma materialized view (v1,v2,...) seja reescrita de forma que aproveite os dados pré processados da materialized view.
	Obs: versão posterior a 8.1
Norewrite – não permite que a query seja reescrita
�
4. Dicas
	Abaixo seguem as tabelas que usaremos nos exemplos.
 tab_loc: tabela local
índices : tab_loc_pk (codigo)
 tab_loc_ind (data )
registros: 500.000 
SQL> desc tab_loc
 Name Null? Type
 ------------------------ -------- --------------
 CODIGO NOT NULL VARCHAR2(10)
 DESCRICAO NOT NULL VARCHAR2(50)
 DATA NOT NULL DATE
 VALOR NOT NULL NUMBER
 tab_loc_peq: tabela local pequena
índices : tab_loc_peq_pk (codigo)
registros: 50 
SQL> desc tab_loc_peq
 Name Null? Type
 ------------------------ -------- --------------
 CODIGO NOT NULL VARCHAR2(10)
 DESCRICAO NOT NULL VARCHAR2(50)
 DATA NOT NULL DATE
 VALOR NOT NULL NUMBER
 tab_loc_gra: tabela local grande
índices : tab_loc_gra_pk (codigo)
registros: 3.070.307
SQL> desc tab_loc_peq
 Name Null? Type
 ------------------------ -------- --------------
 CODIGO NOT NULL NUMBER(10)
 DESCRICAO NOT NULL VARCHAR2(50)
 DATA NOT NULL DATE
 VALOR NOT NULL NUMBER
 tab_rem@link_bci: tabela remota
índices : tab_rem_pk (codigo)
registros: 500.000
SQL> desc tab_rem@link_bci
 Name Null? Type
 ------------------------ -------- ------------
 CODIGO NOT NULL VARCHAR2(10)
 DESCRICAO NOT NULL VARCHAR2(50)
 DATA NOT NULL DATE
 VALOR NOT NULL NUMBER
�
4.1 Evitar função em coluna indexada
Exemplo: consultar todos registros de um determinado dia
Consulta ineficiente
 select count(*)
 from tab_loc
 where trunc(data) = to_date(‘10/10/2003’,‘dd/mm/yyyy’);
Resultado
 Tempo: 2,5s
 OPERATION OPTIONS OBJECT_NAME 
 ------------------------------ -------------------- ------------------
 0 SELECT STATEMENT .
 1 SORT AGGREGATE .
 2 INDEX FAST FULL SCAN TESTE.TAB_LOC_DATA
 Statistics
 ---------------------------------------------------
 0 recursive calls
 6 db block gets
 1196 consistent gets
 1196 physical reads
 0 redo size
 205 bytes sent via SQL*Net to client
 261 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 1 sorts (memory)
 0 sorts (disk)
 1 rows processed
Consulta eficiente
 select count(*)	
 from tab_loc
 where data >= to_date(‘10/10/2003’,‘dd/mm/yyyy’)
 and data < to_date(‘10/10/2003’,‘dd/mm/yyyy’) + 1;
Resultado
 Tempo: 0,1s
 OPERATION OPTIONS OBJECT_NAME 
 ------------------------------ -------------------- ------------------
 0 SELECT STATEMENT .
 1 SORT AGGREGATE .
 2 INDEX RANGE SCAN TESTE.TAB_LOC_DATA
 Statistics
 ---------------------------------------------------
 0 recursive calls
 0 db block gets
 3 consistent gets
 0 physical reads
 0 redo size
 205 bytes sent via SQL*Net to client
 310 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 1 sorts (memory)
 0 sorts (disk)
 1 rows processed
�
4.2 Join com colunas do mesmo tipo
Se isto for inevitável, converta (aplique uma função to_char, to_number, to_date,...) no lado do join que não prejudique o plano de execução. A conversão implícita é custosa, além de ter o mesmo efeito que a utilização de funções em campos indexados.
Consulta ineficiente
 select count(*)
 from tab_loc l,
 tab_loc_gra g
 where l.codigo = g.codigo /* l.codigo é VARCHAR2 e g.codigo é NUMBER */
 and g.data = to_date('10/10/2003','dd/mm/yyyy');
Resultado
 Tempo: 1s
 OPERATION OPTIONS OBJECT_NAME 
 ------------------------------ -------------------- ----------------------
 0 SELECT STATEMENT .
 1 HASH JOIN .
 2 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_GRA
 3 INDEX RANGE SCAN TESTE.TAB_LOC_GRA_DATA
 4 TABLE ACCESS FULL TESTE.TAB_LOC
 Statistics
 ---------------------------------------------------
 0 recursive calls
 4 db block gets
 1061 consistent gets
 0 physical reads
 0 redo size
 207 bytes sent via SQL*Net to client
 378 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
Inclusão da conversão explícita de tipo
 
 select count(*)
 from tab_loc_gra g,
 tab_loc l
 where l.codigo = to_char(g.codigo,'fm99999999') /* ambos VARCHAR2 */
 and g.data = to_date('10/10/2003','dd/mm/yyyy');
Resultado
 Tempo: 0,4s
Obs: Apenas incluindo a conversão de tipo, o tempo foi reduzido pela metade. Note que o plano de execução não foi alterado.
�
 OPERATION OPTIONS OBJECT_NAME 
 ------------------------------ -------------------- -----------------------
 0 SELECT STATEMENT .
 1 SORT AGGREGATE .
 2 HASH JOIN .
 3 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_GRA
 4 INDEX RANGE SCAN TESTE.TAB_LOC_GRA_DATA
 5 INDEX FAST FULL SCAN TESTE.TAB_LOC_PK
 Statistics
 -----------------------------------------------------
 0 recursive calls
 4 db block gets
 1061 consistent gets
 0 physical reads
 0 redo size
 206 bytes sent via SQL*Net to client
 360 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
Consulta incluindo a conversão de tipo e Hints
 select /*+ ordered use_nl(l) */ count(*)
 from tab_loc_gra g,
 tab_loc l
 where l.codigo = to_char(g.codigo,'fm9999999') /* ambos c/ tipo VARCHAR2 */
 and g.data = to_date('10/10/2003','dd/mm/yyyy');
Resultado
 Tempo: 0,01s
 OPERATION OPTIONSOBJECT_NAME 
 ---------------------------- -------------------- -------------------- 
 0 SELECT STATEMENT .
 1 SORT AGGREGATE .
 2 NESTED LOOPS .
 3 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC_GRA
 4 INDEX RANGE SCAN TESTE.TAB_LOC_GRA_DATA
 5 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK
 Statistics
 ---------------------------------------------------
 0 recursive calls
 0 db block gets
 39 consistent gets
 0 physical reads
 0 redo size
 206 bytes sent via SQL*Net to client
 407 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
�
4.3 Coletar as estatísticas com regularidade
Utilize o comando Analyze ou a package Dbms_stats
	Consulta sem anlyze
 analyze table tab_loc delete statistics
 analyze table tab_loc_peq delete statistics
 select l.*
 from tab_loc l,
 tab_loc_peq d,
 tab_rem@link_bci r
 where l.codigo = r.codigo
 and l.codigo = d.codigo
Resultado
 Tempo: 18s
 OPERATION OPTIONS OBJECT_NAME 
 ------------------------------ -------------------- -----------------------
 0 SELECT STATEMENT .
 1 NESTED LOOPS .
 2 NESTED LOOPS .
 3 REMOTE . 
 4 TABLE ACCESS BY INDEX ROWID TESTE.TAB_LOC
 5 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK
 6 INDEX UNIQUE SCAN TESTE.TAB_LOC_PEQ_PK
 ID OTHER
 -- ------------------------------------------------------------------------
 3 SELECT "CODIGO" FROM "TAB_REM" "R"
 Statistics
 -----------------------------------------------------
 7 recursive calls
 6 db block gets
 1999953 consistent gets
 0 physical reads
 172 redo size
 191 bytes sent via SQL*Net to client
 332 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
�
	Consulta com analyze
 analyze table tab_loc compute statistics;
 analyze table tab_loc_peq compute statistics;
 select l.*
 from tab_loc l,
 tab_loc_peq d,
 tab_rem@link_bci r
 where l.codigo = r.codigo
 and l.codigo = d.codigo;
Resultado
 Tempo: 0.1s
 OPERATION OPTIONS OBJECT_NAME 
 ------------------------------ -------------------- ---------------------
 0 SELECT STATEMENT .
 1 SORT AGGREGATE .
 2 NESTED LOOPS .
 3 NESTED LOOPS .
 4 INDEX FULL SCAN TESTE.TAB_LOC_PEQ_PK
 5 INDEX UNIQUE SCAN TESTE.TAB_LOC_PK
 6 REMOTE . 
 ID OTHER
 -- ----------------------------------------------------------------------
 6 SELECT "CODIGO" FROM "TAB_REM" "R" WHERE :1="CODIGO"
 Statistics
 ----------------------------------------------------
 0 recursive calls
 0 db block gets
 151 consistent gets
 0 physical reads
 0 redo size
 207 bytes sent via SQL*Net to client
 332 bytes received via SQL*Net from client
 3 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
�
4.4 Sql Trace 
	
Utilize o sql trace para identificar comandos ineficientes de uma determinada sessão. 
1º) Ative o timed_statistics
alter system set timed_statistics=true;
2º) Ative o trace
Se for a própria sessão execute:
 	alter session set sql_trace=true;
Se for outra sessão execute:
	exec sys.dbms_system.set_sql_trace_in_session(<sid>,< serial#>,true);
3º) Depois de um certo tempo desative o trace
Se for a própria sessão execute:
 	alter session set sql_trace=false;
Se for outra sessão execute:
	exec sys.dbms_system.set_sql_trace_in_session(<sid>,< serial#>,false);
4º) Desative o timed_statistics
alter system set timed_statistics=false;
5º) Localize o trace gerado (sempre no servidor)
Os traces serão gerados no diretório definido para o parâmetro USER_DUMP_DEST. Para identificá-lo execute:
select value 
 from v$parameter 
 where name = 'user_dump_dest';
O nome do arquivo pode ser obtido com o sguinte comando:
select 'ora_'||spid||'_'||lower(i.instance_name)||'.trc' arquivo
 from v$process p,
 v$session s,
 v$instance i
where p.addr = s.paddr
 and s.sid = <sid>;
�
6º) Formate o arquivo trace com o tkprof.
tkprof arq_trace arq_saida explain=usuário/senha sys=no sort=opção
 arq_trace – nome do arquivo trace
 arq_saida – nome do arquivo de saída (formatado)
 opção - pode ser qualquer um dos valores abaixo:
 prscnt number of times parse was called
 prscpu cpu time parsing
 prsela elapsed time parsing
 prsdsk number of disk reads during parse
 prsqry number of buffers for consistent read during parse
 prscu number of buffers for current read during parse
 prsmis number of misses in library cache during parse
 execnt number of execute was called
 execpu cpu time spent executing
 exeela elapsed time executing
 exedsk number of disk reads during execute
 exeqry number of buffers for consistent read during execute
 execu number of buffers for current read during execute
 exerow number of rows processed during execute
 exemis number of library cache misses during execute
 fchcnt number of times fetch was called
 fchcpu cpu time spent fetching
 fchela elapsed time fetching
 fchdsk number of disk reads during fetch
 fchqry number of buffers for consistent read during fetch
 fchcu number of buffers for current read during fetch
 fchrow number of rows fetched
 userid userid of user that parsed the cursor
		
Exemplo: formatar o trace de forma que os comandos estejam ordenados pelo tempo gasto no fetch.
 tkprof ora_33_mig.trc saida.txt explain=usr/usr sys=no sort=fchela