Query Tuning

[bruno_vieira_ora]

Environment information:
* Oracle version: 9i
* Operating system: W2K

Dear, good morning!

Please, what is your suggestion for the plan in question?

Select all

SELECT STATEMENT Optimizer=CHOOSE (Cost=3186 Card=1 Bytes=275) 
  SORT (ORDER BY) (Cost=3186 Card=1 Bytes=275) 
    NESTED LOOPS (Cost=3183 Card=1 Bytes=275) 
      NESTED LOOPS (Cost=3182 Card=1 Bytes=207) 
        NESTED LOOPS (Cost=3181 Card=1 Bytes=183) 
          HASH JOIN (Cost=107 Card=1537 Bytes=196736) 
            NESTED LOOPS (Cost=90 Card=1092 Bytes=28392) 
              TABLE ACCESS (BY INDEX ROWID) OF ESTOQUE (Cost=90 Card=1092 Bytes=24024) 
                INDEX (RANGE SCAN) OF IN04_ESTOQUE (NON-UNIQUE) (Cost=2 Card=1092) 
              INDEX (UNIQUE SCAN) OF PK_UNIDMEDIDA (UNIQUE) 
            TABLE ACCESS (BY INDEX ROWID) OF LOTEMATERIAL (Cost=14 Card=29649 Bytes=3024198) 
              INDEX (RANGE SCAN) OF IN07_LOTEMATERIAL (NON-UNIQUE) (Cost=2 Card=1) 
          TABLE ACCESS (BY INDEX ROWID) OF SALDOESTQLOTEMAT (Cost=2 Card=1 Bytes=55) 
            INDEX (RANGE SCAN) OF IN02_SALDOESTQLOTEMAT (NON-UNIQUE) (Cost=1 Card=1) 
        TABLE ACCESS (BY INDEX ROWID) OF LOCALARMAZ (Cost=1 Card=1 Bytes=24) 
          INDEX (UNIQUE SCAN) OF PK_LOCALARMAZ (UNIQUE) 
      TABLE ACCESS (BY INDEX ROWID) OF MATERIAL (Cost=1 Card=1 Bytes=68) 
        INDEX (UNIQUE SCAN) OF PK_MATERIAL (UNIQUE) 
 
 
SELECT SALDOESTQLOTEMAT.CDALMOX, 
      SALDOESTQLOTEMAT.ANOMESREF, 
      MATERIAL.CdMaterial "Cód.", 
      MATERIAL.nomaterial "Material", 
      MATERIAL.CdReferCli "Ref", 
      LOCALARMAZ.nolocalreduz "Local", 
      LOTEMATERIAL.DtInic " Inicio Validade", 
      LOTEMATERIAL.CdLoteForn "Lote Fornecedor", 
      LOTEMATERIAL.NRLOTEMAT || '-' || DECODE(LOTEMATERIAL.CDLOTEESTQ, 
      NULL, 
      LOTEMATERIAL.CDRASTREABILIDADE, 
      LOTEMATERIAL.CDLOTEESTQ) "Lote", 
      (SALDOESTQLOTEMAT.QTDEINICIAL + SALDOESTQLOTEMAT.QTDEENTRADAS - SALDOESTQLOTEMAT.QTDESAIDAS -  SALDOESTQLOTEMAT.QTDECOMPROM - SALDOESTQLOTEMAT.QTDEBLOQUEADA) "Qtde. disponível", 
      (SALDOESTQLOTEMAT.QTDEINICIAL + SALDOESTQLOTEMAT.QTDEENTRADAS - SALDOESTQLOTEMAT.QTDESAIDAS) "Qtde. total", 
      LOTEMATERIAL.DTFIM "Vencimento"  
  FROM SALDOESTQLOTEMAT, 
      MATERIAL, 
      LOCALARMAZ, 
      LOTEMATERIAL, 
      ESTOQUE, 
      UNIDMEDIDA 
 WHERE LOTEMATERIAL.CDALMOX = 26  
   AND LOTEMATERIAL.CDMATERIAL = SALDOESTQLOTEMAT.CDMATERIAL  
   AND LOTEMATERIAL.CDALMOX = SALDOESTQLOTEMAT.CDALMOX  
   AND LOTEMATERIAL.NRLOTEMAT = SALDOESTQLOTEMAT.NRLOTEMAT  
   AND LOTEMATERIAL.CDPESSOAEMP = SALDOESTQLOTEMAT.CDPESSOAEMP  
   AND SALDOESTQLOTEMAT.CDMATERIAL = MATERIAL.CDMATERIAL  
   AND SALDOESTQLOTEMAT.CDALMOX = LOCALARMAZ.CDALMOX  
   AND SALDOESTQLOTEMAT.CDLOCAL = LOCALARMAZ.CDLOCAL  
   AND SALDOESTQLOTEMAT.FLGSALDOATUAL ='S'  
   AND UNIDMEDIDA.CDUNIDMED = ESTOQUE.CDUNIDMED  
   AND LOTEMATERIAL.CDALMOX = ESTOQUE.CDALMOX  
   AND LOTEMATERIAL.CDMATERIAL = ESTOQUE.CDMATERIAL  
   AND (SALDOESTQLOTEMAT.QtdeInicial + SALDOESTQLOTEMAT.QtdeEntradas - SALDOESTQLOTEMAT.QtdeSaidas - SALDOESTQLOTEMAT.QtdeComprom - SALDOESTQLOTEMAT.QtdeBloqueada) > 0 
 ORDER BY SALDOESTQLOTEMAT.CDALMOX, SALDOESTQLOTEMAT.CDMATERIAL 
 
Tabela / Rows 
 
ESTOQUE	87320 
LOCALARMAZ	34759 
LOTEMATERIAL	2045809 
MATERIAL	62057 
SALDOESTQLOTEMAT	21298013 
UNIDMEDIDA	45 

[ruevers]

Friend,


apparently nothing too bad no ... How long does it take to run?
Two things I would do ... Just to start ...

put this after Joins ...

Select all

LOTEMATERIAL.CDALMOX = 26 

]] and start join by the table that has more data ...

Select all

SALDOESTQLOTEMAT 21298013 

That is, Join, always greater for the smallest table.

Verified if the statements they are using are the best ...
If the table has no other. And from index hints and several others
possible.

[bruno_vieira_ora]

Anderson, \ [99]
Thank you very much for the analysis!

is taking 20 minutes rsrs ..

As for indexes, I tried some compounds in the largest tables but nothing ..

I will follow your suggestions

Hugs

[rogenaro]

Apparently does not have much to be improved even, since there are no clauses to limit their results except for the conditions:

Select all

 
WHERE LOTEMATERIAL.CDALMOX = 26 
... 
AND SALDOESTQLOTEMAT.FLGSALDOATUAL ='S' 
... 
AND (SALDOESTQLOTEMAT.QtdeInicial + SALDOESTQLOTEMAT.QtdeEntradas - SALDOESTQLOTEMAT.QtdeSaidas - SALDOESTQLOTEMAT.QtdeComprom - SALDOESTQLOTEMAT.QtdeBloqueada) > 0 

Since we do not know their modeling and their data, VU Do some questions before:

1) Are there how many records in lotematerials with cdalmox = 26?
2) Are there how many records in sqldoestqlotemat with flgsaldoatual = 's'?
3) There are how many records in SaldoStQlotemat where

Select all

(SALDOESTQLOTEMAT.QtdeInicial + SALDOESTQLOTEMAT.QtdeEntradas - SALDOESTQLOTEMAT.QtdeSaidas - SALDOESTQLOTEMAT.QtdeComprom - SALDOESTQLOTEMAT.QtdeBloqueada) > 0

4) What existing indexes on each of the tables involved, and their respective fields?
5) What is the average records that each of the tables-daughter holds by registration on your motherboard (according to the join conditions specified in your query)?

shooting in the dark if the answer from some of the first three questions is a very small value in relation to the size of the table in question (something between 0% and 5% of the total records, perhaps a little more), and is not a value that should suffer many variations over time (if, for example, on a day 1% of the Lotematerial records has cdalmox = 26, but a week after 90% of the records may meet In this situation, it is best to skip the following suggestion), I would consider using HINTS to force Join to start with the table that will limit the results more, forcing the subsequent joins to use Nested Loops, removing the Hash Join that appeared in his plan (of course , this considering that a relatively small number of records are returned at each step of the new execution plan).

Anyway, apparently the villain of this story is the Hash Join between SaldoStqlotemat and Join ((stock -> unidmedida) -> Lotematerial). What fields form the IN02_SALDEESTQLOTEMAT index? Are they useful in limiting the results returned from the SaldoStQlotemat table? Otherwise, it may be interesting to force a full access to this table, since it is at the tip of a join hash (or I err at the time of idling the execution plan ...: p)

[bruno_vieira_ora]

Dear Rafael,

Thank you for your analysis and follows some infos:

Select all

LOTEMATERIAL com CDALMOX = 26?   [color=red] [color=#444444]694492[/color][/color]SQLDOESTQLOTEMAT com FLGSALDOATUAL = 'S'?

1426796


] 1525671

Query returns 10 lines ...

indicates





Thanks for the help of all!

[ruevers]

20 minutes? damn it. : "

[rogenaro]

Poxa, difficult this ..: p

Despite filtering well, the number of records returned by the conditions is very large still ...

There are how many records which satisfy the condition

Select all

SQLDOESTQLOTEMAT com FLGSALDOATUAL = 'S'

and the condition

Select all

SALDOESTQLOTEMAT.QtdeInicial + SALDOESTQLOTEMAT.QtdeEntradas - SALDOESTQLOTEMAT.QtdeSaidas - SALDOESTQLOTEMAT.QtdeComprom - SALDOESTQLOTEMAT.QtdeBloqueada) > 0

assuming that are many few records, it may be interesting to start join with a full scan in this table, And then leave for the other tables. In this case, the HINT below may help a little:

Select all

 
SELECT /*+ ordered 
           full(SALDOESTQLOTEMAT)  
           index(LOTEMATERIAL PK_LOTEMATERIAL) 
           index(ESTOQUE PK_ESTOQUE) 
           index(MATERIAL PK_MATERIAL) 
           index(LOCALARMAZ IN03_LOCALARMAZ) 
           index(UNIDMEDIDA PK_UNIDMEDIDA) 
           use_nl(SALDOESTQLOTEMAT LOTEMATERIAL ESTOQUE MATERIAL LOCALARMAZ UNIDMEDIDA) 
        */ 
... 
FROM   SALDOESTQLOTEMAT 
     , LOTEMATERIAL 
     , ESTOQUE 
     , MATERIAL 
     , LOCALARMAZ 
     , UNIDMEDIDA  
... 

Now, if the number of existing records remain high, the Plan initially generated is not the worst. The only tip I can give would be to find at what point the data is so restricted.

As there are 6 tables involved, and most of them returns hundreds of thousands of records, and the end result is only 10 records, it is possible that:
- Some table It has a very small amount of records with cdalmox = 26 - as this field is part of the PK of several tables, maybe this can be a starting point for the joins;
- Some related table generally does not have corresponding child records. In this case, it would be interesting to accomplish this Join first, restricting the number of records to then continue Join with the other tables.

[bruno_vieira_ora]

Expensive,

I would like to thank the commitment to help ..

Anderson,

Last Analyzed is in 18 / 05, but I had to rotate again ...

Rafael,

are 62206 which contemplate the two conditions.

tables with cdalmox = 26

Select all

	  SALDOESTQLOTEMAT 9181096 
	  LOTEMATERIAL 694492 
	  ESTOQUE 14185 
	  LOCALARMAZ 2669

I did not understand very well its last observation ..

I'm going to work on your hint and see if there comes out something ..

The plan with your HINT:

Select all

SELECT STATEMENT Optimizer=CHOOSE (Cost=26554 Card=1 Bytes=275) 
  SORT (ORDER BY) (Cost=26554 Card=1 Bytes=275) 
    NESTED LOOPS (Cost=26551 Card=1 Bytes=275) 
      NESTED LOOPS (Cost=26551 Card=1 Bytes=271) 
        NESTED LOOPS (Cost=26434 Card=1 Bytes=247) 
          NESTED LOOPS (Cost=26433 Card=1 Bytes=179) 
            NESTED LOOPS (Cost=26432 Card=1 Bytes=157) 
              TABLE ACCESS (FULL) OF SALDOESTQLOTEMAT (Cost=10998 Card=7717 Bytes=424435) 
              TABLE ACCESS (BY INDEX ROWID) OF LOTEMATERIAL (Cost=2 Card=1 Bytes=102) 
                INDEX (UNIQUE SCAN) OF PK_LOTEMATERIAL (UNIQUE) (Cost=1 Card=1) 
            TABLE ACCESS (BY INDEX ROWID) OF ESTOQUE (Cost=1 Card=1 Bytes=22) 
              INDEX (UNIQUE SCAN) OF PK_ESTOQUE (UNIQUE) 
          TABLE ACCESS (BY INDEX ROWID) OF MATERIAL (Cost=1 Card=1 Bytes=68) 
            INDEX (UNIQUE SCAN) OF PK_MATERIAL (UNIQUE) 
        INDEX (FULL SCAN) OF IN03_LOCALARMAZ (NON-UNIQUE) (Cost=117 Card=1 Bytes=24) 
      INDEX (UNIQUE SCAN) OF PK_UNIDMEDIDA (UNIQUE)

Hugs

[bruno_vieira_ora]

Expensive,

I identified that the exception

Select all

 AND (SALDOESTQLOTEMAT.QtdeInicial + SALDOESTQLOTEMAT.QtdeEntradas - SALDOESTQLOTEMAT.QtdeSaidas - SALDOESTQLOTEMAT.QtdeComprom - SALDOESTQLOTEMAT.QtdeBloqueada) > 0

was making a full tablescan

I created a 15-minute



reduced to 50 seconds!

Thank you for the help of all,

[Renan Orati]

Could you show us how you did to create this index ??

Thanks! = D