RE: Oracle Performance

From: Whittle Jerome Contr NCI <Jerome.Whittle_at_scott.af.mil>
Date: Thu, 11 Mar 2004 08:46:27 -0600
Message-ID: <DFEC3C69DE8C524DA705BC09A220740B029CAF4E@AMCW2VN801.amc.ds.af.mil>

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_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Huascar Espinoza [SMTP:hespinoza_at_superele.gov.bo]
>
> 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:
>
> 1. SELECT COUNT(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1'
> 2. SELECT MAX(NRO_FACTURACION) FROM SC_FACTURACION WHERE COD_IMPORTACION = '14' AND COD_NIVEL_CALIDAD = '1'
> 3. 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
>
> 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
>
> ¿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
-----------------------------------------------------------------

Received on Thu Mar 11 2004 - 14:01:16 CST