| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Performance
Jo has raised a significant point that will help answer this question...
There is an index on the NRO_FACTURACION field of the table however it is not clear from the supplied data if this field is defined as NULL or NOT NULL. Oracle should get MAX(...) and MIN(...) from the index very quickly so your performance indicates this is not occuring - possibly a lack of statistics. As for the COUNT(...) query - it cannot use the index unless the column is defined as NOT NULL.
If you are using SQL*Plus type "set autotrace on" before running the query and look for the "Execution Plan" - does it indicate TABLE ACCESS (FULL) or INDEX scans for each of the three queries? Post your results if you aren't sure - we will slowly understand this problem and things like HINTS and HISTOGRAMS are very unlikely to apply to your problem. I'd prefer to solve the underlying problem so you don't have the same thing happen on the very next query as well.
In terms of COUNT(*) being better than COUNT(NRO_FACTURACION) - this is not true in your example, but could be true if the field is NULLABLE and an index exists on a different NOT NULLABLE column since Oracle could count that field instead.
jo_holvoet_at_amis.co
m To: oracle-l_at_freelists.org
Sent by: cc:
oracle-l-bounce_at_fr Subject: Re: Oracle Performance
eelists.org
12/03/2004 03:38
Please respond to
oracle-l
This will not necessarily return the same result as the original query. Depends on whether nro_facturacion can be null or not.
mvg/regards
Jo
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:
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
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
 |
 |