Re: Oracle Performance
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Mar 2004 11:43:03 -0400
RE: Oracle PerformanceHonestly I don't agree with this, I don't think there is
any need to use rule, in Oracle 9i, CBO works
perfectly with this kind of situation, the problem must be find and fixed.
----- Original Message -----
From: Whittle Jerome Contr NCI
To: oracle-l@xxxxxxxxxxxxx
Cc: VChoque
Sent: Thursday, March 11, 2004 10:46 AM
Subject: 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:
a.. COD_IMPORTACION
b.. NRO_FACTURACION
c.. COD_FACTURA_ESTIMADA
d.. COD_FACTURA_REFACTURADA
e.. COD_MOTIVO_ESTIMACION
f.. 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@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:
