SQL Tuning (com fazer)

[mson77 0]

Olás,

Estou com uma sql que está me consumindo quase 21seg. Segue resultado do trace+explain:

 

 

TKPROF: Release 10.2.0.1.0 - Production on Sáb Ago 18 02:27:46 2007Copyright (c) 1982, 2005, Oracle.  All rights reserved.Trace file: cm3b_ora_1004.trcSort options: default********************************************************************************count	= number of times OCI procedure was executedcpu	  = cpu time in seconds executing elapsed  = elapsed time in seconds executingdisk	 = number of physical reads of buffers from diskquery	= number of buffers gotten for consistent readcurrent  = number of buffers gotten in current mode (usually for update)rows	 = number of rows processed by the fetch or execute call********************************************************************************alter session set sql_trace=truecall	 count	   cpu	elapsed	   disk	  query	current		rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse		0	  0.00	   0.00		  0		  0		  0		   0Execute	  1	  0.00	   0.00		  0		  0		  0		   0Fetch		0	  0.00	   0.00		  0		  0		  0		   0------- ------  -------- ---------- ---------- ---------- ----------  ----------total		1	  0.00	   0.00		  0		  0		  0		   0Misses in library cache during parse: 0Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 39  (CM)********************************************************************************select condition from cdef$ where rowid=:1call	 count	   cpu	elapsed	   disk	  query	current		rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse	   32	  0.00	   0.00		  0		  0		  0		   0Execute	 32	  0.00	   0.00		  0		  0		  0		   0Fetch	   32	  0.00	   0.00		  0		 64		  0		  32------- ------  -------- ---------- ---------- ---------- ----------  ----------total	   96	  0.00	   0.00		  0		 64		  0		  32Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: CHOOSEParsing user id: SYS   (recursive depth: 1)Rows	 Row Source Operation-------  ---------------------------------------------------	  1  TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=27 us)********************************************************************************SELECT m.codartigo,	m.codalmoxarifado,	MAX(idmov) AS	   idmov	   FROM moviment m,	almox a,		  (SELECT m.codartigo,	m.codalmoxarifado,	MAX(m.datamov) AS		 datamov		 FROM moviment m,	almox a		 WHERE(m.idpessoa = 3)		 AND(m.codalmoxarifado IN(9))		 AND(m.codalmoxarifado = a.codalmoxarifado)		 AND(a.codcusteio = 3)		 AND(m.datamov < to_date('01/08/2007',	'DD/MM/YYYY'))		 AND(m.datamov >=		  (SELECT to_date(nvl(s.ultimadata,	p.dataimplanta),	'DD/MM/YYYY')		   FROM paralmox p,			  (SELECT MAX(ultimadata) AS			 ultimadata			 FROM ultdatarepresa			 WHERE ultimadata < to_date('01/08/2007',	'DD/MM/YYYY')			 AND idpessoa = 3) s			 WHERE p.idpessoa = 3))		  GROUP BY m.codartigo,			 m.codalmoxarifado)		md		 WHERE(md.datamov = m.datamov)		 AND(md.codartigo = m.codartigo)		 AND(md.codalmoxarifado = m.codalmoxarifado)		 AND(m.codalmoxarifado = a.codalmoxarifado)		 AND(a.codcusteio = 3)		 AND(m.datamov < to_date('01/08/2007',	'DD/MM/YYYY'))		 AND(m.datamov >=		  (SELECT to_date(nvl(s.ultimadata,	p.dataimplanta),	'DD/MM/YYYY')		   FROM paralmox p,			  (SELECT MAX(ultimadata) AS			 ultimadata			 FROM ultdatarepresa			 WHERE ultimadata < to_date('01/08/2007',	'DD/MM/YYYY')			 AND idpessoa = 3) s			 WHERE p.idpessoa = 3)		  )		GROUP BY m.codartigo,		   m.codalmoxarifadocall	 count	   cpu	elapsed	   disk	  query	current		rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse		1	  0.06	   0.05		  0		  0		  0		   0Execute	  1	  0.00	   0.00		  0		 10		  0		   0Fetch	  159	 20.64	  21.14		735	   8910		  0		2370------- ------  -------- ---------- ---------- ---------- ----------  ----------total	  161	 20.70	  21.19		735	   8920		  0		2370Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 39  (CM)Rows	 Row Source Operation-------  ---------------------------------------------------   2370  HASH GROUP BY (cr=8920 pr=735 pw=735 time=21143294 us)   3367   VIEW  (cr=8920 pr=735 pw=735 time=20897991 us)   3367	FILTER  (cr=8920 pr=735 pw=735 time=20887885 us)  80134	 HASH GROUP BY (cr=8920 pr=735 pw=735 time=24185932 us)12936656	  FILTER  (cr=8920 pr=0 pw=0 time=77840993 us)12936656	   HASH JOIN  (cr=8910 pr=0 pw=0 time=26094090 us)  80134		TABLE ACCESS BY INDEX ROWID MOVIMENT (cr=4453 pr=0 pw=0 time=320651 us)  99457		 INDEX RANGE SCAN XIE1MOVIMENT (cr=265 pr=0 pw=0 time=201276 us)(object id 43797)	  1		  NESTED LOOPS  (cr=5 pr=0 pw=0 time=146 us)	  1		   TABLE ACCESS BY INDEX ROWID PARALMOX (cr=2 pr=0 pw=0 time=40 us)	  1			INDEX UNIQUE SCAN XPKPARALMOX (cr=1 pr=0 pw=0 time=20 us)(object id 44082)	  1		   VIEW  (cr=3 pr=0 pw=0 time=91 us)	  1			SORT AGGREGATE (cr=3 pr=0 pw=0 time=80 us)	  5			 TABLE ACCESS FULL ULTDATAREPRESA (cr=3 pr=0 pw=0 time=58 us)  80134		NESTED LOOPS  (cr=4457 pr=0 pw=0 time=721414 us)	  1		 NESTED LOOPS  (cr=4 pr=0 pw=0 time=97 us)	  1		  TABLE ACCESS BY INDEX ROWID ALMOX (cr=2 pr=0 pw=0 time=54 us)	  1		   INDEX UNIQUE SCAN IDXALMOXA (cr=1 pr=0 pw=0 time=26 us)(object id 41109)	  1		  TABLE ACCESS BY INDEX ROWID ALMOX (cr=2 pr=0 pw=0 time=27 us)	  1		   INDEX UNIQUE SCAN IDXALMOXA (cr=1 pr=0 pw=0 time=12 us)(object id 41109)  80134		 TABLE ACCESS BY INDEX ROWID MOVIMENT (cr=4453 pr=0 pw=0 time=480927 us)  99457		  INDEX RANGE SCAN XIE1MOVIMENT (cr=265 pr=0 pw=0 time=201373 us)(object id 43797)	  1		   NESTED LOOPS  (cr=5 pr=0 pw=0 time=93 us)	  1			TABLE ACCESS BY INDEX ROWID PARALMOX (cr=2 pr=0 pw=0 time=24 us)	  1			 INDEX UNIQUE SCAN XPKPARALMOX (cr=1 pr=0 pw=0 time=11 us)(object id 44082)	  1			VIEW  (cr=3 pr=0 pw=0 time=55 us)	  1			 SORT AGGREGATE (cr=3 pr=0 pw=0 time=45 us)	  5			  TABLE ACCESS FULL ULTDATAREPRESA (cr=3 pr=0 pw=0 time=34 us)	  1	   NESTED LOOPS  (cr=5 pr=0 pw=0 time=146 us)	  1		   TABLE ACCESS BY INDEX ROWID PARALMOX (cr=2 pr=0 pw=0 time=40 us)	  1			INDEX UNIQUE SCAN XPKPARALMOX (cr=1 pr=0 pw=0 time=20 us)(object id 44082)	  1		   VIEW  (cr=3 pr=0 pw=0 time=91 us)	  1			SORT AGGREGATE (cr=3 pr=0 pw=0 time=80 us)	  5			 TABLE ACCESS FULL ULTDATAREPRESA (cr=3 pr=0 pw=0 time=58 us)	  1	   NESTED LOOPS  (cr=5 pr=0 pw=0 time=93 us)	  1			TABLE ACCESS BY INDEX ROWID PARALMOX (cr=2 pr=0 pw=0 time=24 us)	  1			 INDEX UNIQUE SCAN XPKPARALMOX (cr=1 pr=0 pw=0 time=11 us)(object id 44082)	  1			VIEW  (cr=3 pr=0 pw=0 time=55 us)	  1			 SORT AGGREGATE (cr=3 pr=0 pw=0 time=45 us)	  5			  TABLE ACCESS FULL ULTDATAREPRESA (cr=3 pr=0 pw=0 time=34 us)Rows	 Execution Plan-------  ---------------------------------------------------	  0  SELECT STATEMENT   MODE: ALL_ROWS   2370   HASH (GROUP BY)   3367	VIEW   3367	 FILTER  80134	  HASH (GROUP BY)12936656	   FILTER12936656		HASH JOIN  80134		 TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 					'MOVIMENT' (TABLE)  99457		  INDEX   MODE: ANALYZED (RANGE SCAN) OF 					 'XIE1MOVIMENT' (INDEX)	  1		   NESTED LOOPS	  1			TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 					   OF 'PARALMOX' (TABLE)	  1			 INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 						'XPKPARALMOX' (INDEX (UNIQUE))	  1			VIEW	  1			 SORT (AGGREGATE)	  5			  TABLE ACCESS   MODE: ANALYZED (FULL) OF 						 'ULTDATAREPRESA' (TABLE)  80134		 NESTED LOOPS	  1		  NESTED LOOPS	  1		   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 					  OF 'ALMOX' (TABLE)	  1			INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 					   'IDXALMOXA' (INDEX (UNIQUE))	  1		   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) 					  OF 'ALMOX' (TABLE)	  1			INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 					   'IDXALMOXA' (INDEX (UNIQUE))  80134		  TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 					 'MOVIMENT' (TABLE)  99457		   INDEX   MODE: ANALYZED (RANGE SCAN) OF 					  'XIE1MOVIMENT' (INDEX)	  1			NESTED LOOPS	  1			 TABLE ACCESS   MODE: ANALYZED (BY INDEX 						ROWID) OF 'PARALMOX' (TABLE)	  1			  INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 						 'XPKPARALMOX' (INDEX (UNIQUE))	  1			 VIEW	  1			  SORT (AGGREGATE)	  5			   TABLE ACCESS   MODE: ANALYZED (FULL) OF 						  'ULTDATAREPRESA' (TABLE)	  1		NESTED LOOPS	  1		 TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 					'PARALMOX' (TABLE)	  1		  INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 					 'XPKPARALMOX' (INDEX (UNIQUE))	  1		 VIEW	  1		  SORT (AGGREGATE)	  5		   TABLE ACCESS   MODE: ANALYZED (FULL) OF 					  'ULTDATAREPRESA' (TABLE)	  1		NESTED LOOPS	  1		 TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 					'PARALMOX' (TABLE)	  1		  INDEX   MODE: ANALYZED (UNIQUE SCAN) OF 					 'XPKPARALMOX' (INDEX (UNIQUE))	  1		 VIEW	  1		  SORT (AGGREGATE)	  5		   TABLE ACCESS   MODE: ANALYZED (FULL) OF 					  'ULTDATAREPRESA' (TABLE)********************************************************************************alter session set sql_trace=falsecall	 count	   cpu	elapsed	   disk	  query	current		rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse		1	  0.00	   0.00		  0		  0		  0		   0Execute	  1	  0.00	   0.00		  0		  0		  0		   0Fetch		0	  0.00	   0.00		  0		  0		  0		   0------- ------  -------- ---------- ---------- ---------- ----------  ----------total		2	  0.00	   0.00		  0		  0		  0		   0Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 39  (CM)********************************************************************************OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTScall	 count	   cpu	elapsed	   disk	  query	current		rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse		2	  0.06	   0.06		  0		  0		  0		   0Execute	  3	  0.00	   0.00		  0		 10		  0		   0Fetch	  159	 20.64	  21.14		735	   8910		  0		2370------- ------  -------- ---------- ---------- ---------- ----------  ----------total	  164	 20.70	  21.20		735	   8920		  0		2370Misses in library cache during parse: 2Misses in library cache during execute: 1OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall	 count	   cpu	elapsed	   disk	  query	current		rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse	   32	  0.00	   0.00		  0		  0		  0		   0Execute	 32	  0.00	   0.00		  0		  0		  0		   0Fetch	   32	  0.00	   0.00		  0		 64		  0		  32------- ------  -------- ---------- ---------- ---------- ----------  ----------total	   96	  0.00	   0.00		  0		 64		  0		  32Misses in library cache during parse: 1Misses in library cache during execute: 1	3  user  SQL statements in session.   32  internal SQL statements in session.   35  SQL statements in session.	1  statement EXPLAINed in this session.********************************************************************************Trace file: cm3b_ora_1004.trcTrace file compatibility: 10.01.00Sort options: default	   1  session in tracefile.	   3  user  SQL statements in trace file.	  32  internal SQL statements in trace file.	  35  SQL statements in trace file.	   4  unique SQL statements in trace file.	   1  SQL statements EXPLAINed using schema:		   CM.prof$plan_table			 Default table was used.			 Table was created.			 Table was dropped.	 539  lines in trace file.	  73  elapsed seconds in trace file.

Qqr ajuda é muito bem-vinda.

Obrigado.

 

 

mson77

[MonTiLLa 1]

Opa amigo,

 

Tenta refazer essa SQL com alguns comandos de Left Join, Rigth Join, Minus, Union, having... e retirar algumas dessas subconsultas...

 

http://forum.imasters.com.br/public/style_emoticons/default/assobiando.gif

[alphamek 2]

Mson,Antes de mexer na instrução SQL, veja algumas "tuning" que pode fazer a nível de objeto e instância:1 - Veja a data das estatísticas das tabelas e índices, pois está usando CBO na instância.2 - Veja se os índices que está utilizando é adequado.3 - Veja os parâmetros de DB_BUFFER_CACHE, SHARED_POOL e SORT_AREA estão adequeados.4- Veja se as tabelas estão para PARALLEL, MONITORING, LOGGING e seus parâmetros de STORAGE como NEXT EXTENT e limites de extents.5 - Veja se as tablespaces estão fragmentadas.6 - Faça alguns rebuilds nos índices.7 - Utilize alguns hints para melhorar a consulta.E depois nos informe sobre os resultados!Abraços,

[mson77 0]

Olá Rodrigo,

 

1) Como faço p ver a data das estatistica e indices?

Eu fiz o seguinte:

dbms_stats.delete_schema_stats; ==> cascade=true

dbms_stats.gather_schema_stats; ==> cascade=true

 

2) Cheguei a criar um indice... mas qdo fui observar sua utilização via:

alter index <nome_indice> monitoring usage;

Vi que a indice que eu criei... oracle nao usou.

Criei o indice e depois rebuild. Precisa mais alguma ação?

 

3) Estou usando SGA com parametros automaticos do oracle;

 

4) Como posso saber se as tabelas estao em PARALLEL, MONITORING ou LOGGING?

O gerenciamento das extents está LOCAL.

 

5) Como faço p levantar a fragmentação das tablespaces?

 

6) Fiz rebuild dos indices;

 

7) Como se faz HINTS para melhorar a consulta?

 

 

mson77

[alphamek 2]

Mson,

 

Segue as respostas:

 

1) Como faço p ver a data das estatistica e indices?

R: Basta fazer o SELECT abaixo para verificar as estatísticas:

 

SELECT owner, table_name, to_char(last_analyzed,'DD-MM-RRRR'), num_rows
from dba_tables where table_name = 'NOME_DA_TABELA';

 

2) Cheguei a criar um indice... mas qdo fui observar sua utilização via:

alter index <nome_indice> monitoring usage;

Vi que a indice que eu criei... oracle nao usou.

Criei o indice e depois rebuild. Precisa mais alguma ação?

R: A opção MONITORING não é o mais adequado para essa verificação, a melhor maneira de verificar se o índice que tu acabou de criar está sendo utilizado, é verificar pelo plano de execução da sua instrução se ele está sendo utilizado, em algum momento do plano, ele deverá ser utilizado, e caso não esteja, veja se a chave (colunas) que está utilizando é o mais adequado e se a quantidade de registros que está pedindo é necessário para um índice, pois, ele pode estar fazendo FULL SCAN.

 

3) Estou usando SGA com parametros automaticos do oracle;

 

R: Quando se diz parâmetros automáticos, está com um valor superior que 0 para o parâmetro SQL_TARGET, caso seja uma versão 10G.

 

4) Como posso saber se as tabelas estao em PARALLEL, MONITORING ou LOGGING?

R: Apenas consultando o dicionário de dados, veja:

 

SELECT owner, table_name, degree, monitoring, logging FROM dba_tables WHERE table_name = 'NOME_DA_TABELA';

 

O gerenciamento das extents está LOCAL.

 

R: O gerenciamento das tablespaces são locais, porém, tem que saber se elas estão como AUTOALLOCATE (Padrão de 64KB de next extent) ou UNIFORM. E outra, para esses valores de INITIAL EXTENT serem válidos, deverá ter certeza que os segmentos (tabelas e índices) não foram criados com os parâmetros de STORAGE, onde se específica o valor dos extents a nível de segmento.

 

5) Como faço p levantar a fragmentação das tablespaces?

 

R: Aqui no Fórum Oravle existe um Sub-Fórum, chamado Laboratório de Scripts que tem os scripts para verificar a fragmentação das tablespaces e objetos.

 

6) Fiz rebuild dos indices;

 

R: O inárirebuld nos índices só é necessário quando o índices está muito fragmentado, ou seja, já recebeu muitos INSERTS/UPDATES/DELETES na tabela ou caso, esse índice está a muito tempo (6 meses ou mais) sem reconstruir sua árvore binária (caso seja b*tree) ou mapa de bits (para índices Bitmaps).

 

7) Como se faz HINTS para melhorar a consulta?

 

R: Os hitns são "dicas" que pode ser colocar nas instruções de DML para melhorar seu plano de execução ou performance, existe diversos sites ou até mesmo os guias da Oracle que fornecem todos os tipos de Hints existentes.

 

Abraços, http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif