Re: Oracle Performance

From: <jo_holvoet_at_amis.com>
Date: Thu, 11 Mar 2004 17:38:57 +0100
Message-id: <OF2F7C0EF0.3422BCF8-ONC1256E54.005B584B@eu.amis.com>

This will not necessarily return the same result as the original query. Depends on whether nro_facturacion can be null or not.

mvg/regards

Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com> Sent by: oracle-l-bounce_at_freelists.org
03/11/2004 15:51
Please respond to oracle-l

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        Re: Oracle Performance

And is better to do a
SELECT COUNT(*) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1'
----- Original Message -----

From: Huascar Espinoza
To: oracle-l_at_freelists.org
Cc: VChoque
Sent: Thursday, March 11, 2004 9:35 AM
Subject: RE: Oracle Performance

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:

SELECT COUNT(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1'
SELECT MAX(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1'
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de DENNIS WILLIAMS
Enviado el: Jueves 11 de Marzo de 2004 9:16 Para: 'oracle-l_at_freelists.org'
Asunto: RE: Oracle Performance

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_at_lifetouch.com

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Huascar Espinoza
Sent: Thursday, March 11, 2004 7:04 AM
To: oracle-l_at_freelists.org
Subject: Oracle Performance

¿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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Thu Mar 11 2004 - 12:34:08 CST