DicasSQL
25 pág.

DicasSQL

Disciplina:Bancos de Dados para Redes81 materiais667 seguidores
Pré-visualização4 páginas
OBJECT_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