DicasSQL
25 pág.

DicasSQL


DisciplinaBancos de Dados para Redes90 materiais676 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
\ufffd
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
\ufffd
	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
\ufffd
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>;
\ufffd
6º) Formate o arquivo trace com o tkprof.
tkprof arq_trace arq_saida explain=usuário/senha sys=no sort=opção
 arq_trace \u2013 nome do arquivo trace
 arq_saida \u2013 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