RE: Oracle Performance

THANK YOU!!!!!! One Index for the 3 fields increase performance (each SQL in 6 
seconds!!)
 
Huascar
 
-----Mensaje original-----
De: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] En 
nombre de Whittle Jerome Contr NCI
Enviado el: Jueves 11 de Marzo de 2004 10:46
Para: oracle-l@xxxxxxxxxxxxx
CC: VChoque
Asunto: RE: Oracle Performance
 
You have 5 indexes of which 3 have the fields in your SQL. You may be better 
off with one index on the COD_IMPORTACION, COD_NIVEL_CALIDAD, and 
NRO_FACTURACION fields. After creating the index, analyze the table before 
running your sql.
You might also just want to try to use the Rule hint. It's usually one of the 
first things I try with slow SQL. If it runs much faster with the Rule hint, 
something is going on to confuse the CBO.
SELECT /*+ RULE */ COUNT(NRO_FACTURACION) .... 
Jerry Whittle 
ASIFICS DBA 
NCI Information Systems Inc. 
jerome.whittle@xxxxxxxxxxxx 
618-622-4145 
-----Original Message----- 
From:   Huascar Espinoza [SMTP:hespinoza@xxxxxxxxxxxxxxx] 
To realize the test, we have the basic following structure (similar on Oracle 
9i and SQL Server 7.0): 
  
*        Table "SC_FACTURACION" have the following indexes: 
*         COD_IMPORTACION
*         NRO_FACTURACION
*         COD_FACTURA_ESTIMADA
*         COD_FACTURA_REFACTURADA
*         COD_MOTIVO_ESTIMACION
*         COD_NIVEL_CALIDAD.
*        Table "GE_HISTORICO_IMPORTACION" has 30 registers. 
*        Table "SC_FACTURACION" has 1,885,618 registers. 
  
On 'SC_FACTURACION' we execute the following SQL sentences: 
  
2.     SELECT COUNT(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION 
= '14' AND COD_NIVEL_CALIDAD = '1'
3.     SELECT MAX(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = 
'14' AND COD_NIVEL_CALIDAD = '1'
4.     SELECT MIN(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = 
'14' AND COD_NIVEL_CALIDAD = '1'
  
The results are: 
  
Sentence 
Performance ORACLE 9i 
Performance MS SQL Server 7.0 
1 
2 minutes 
20 secs. 
2 
2 minutes 
1 sec. 
3 
50 secs. 
1 secs. 
  
The servers are the followins features: 
  
ORACLE 9i Server 
Processor 
Intel Pentium IV - 2.4 GHz 
RAM Memory 
512 MB 
  
S SQL Server 7.0 Server 
Processor 
Intel Celeron 2.4 GHz 
RAM Memory 
380 MB 
  
  
Please let me know, why the performance of Oracle is so low. 
  
Thank you! 
  
Huáscar Espinoza 
  
  
-----Mensaje original-----
De: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] En 
nombre de DENNIS WILLIAMS
  
Huascar 
     Normal? No. In order to help diagnose your situation, please post more 
details. Like your query, the sizes of your tables, etc. Have you analyzed 
the tables in Oracle? 
  
Dennis Williams 
DBA 
Lifetouch, Inc. 
dwilliams@xxxxxxxxxxxxx 
  
-----Original Message----- 
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On 
  
¿Is it normal that a SQL sentence: MAX or MIN, execute over 1.5 minutes on 
Oracle 9i, and in 1 sec. on SQL Server 7.0 (2 millions of registers), using 
equals data structures and servers? ¿Why occur this situation? 
  
Thank you, 
  
Huascar Espinoza 

Other related posts: