| 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
My guess is that SQL Server is by default using 3 indexes to resolve the
query
COD_IMPORTACION, NRO_FACTURACION and COD_NIVEL_CALIDAD whereas Oracle is only using one (or performing a full-table scan).
Have you gernerated statistics on the tables? Can you post the explain plan for the Oracle query?.
Several tuning stragegies can be used..
1) create a composite index on the 3 columns (COD_IMPORTACION,
COD_NIVEL_CALIDAD, NRO_FRACTURACION).
2) Try the index_combine hint to try to force oracle to use the 3
indexes.
3) Try FIRST_ROWS hint in a inline view
SELECT /*+NO_MERGE(x) */ COUNT(NRO_FACTURACION)
FROM (SELECT /*+ FIRST_ROWS */ nro_facturacion from sc_facturacion
WHERE ...) x
HTH
Kevin.
-----Original Message-----
From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
Sent: Thursday, March 11, 2004 8:57 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: Oracle Performance
You should be able to cache all the data in these two tables in the buffer
cache.
Increase your buffer cache settings to 200M-300M and set the degree of
parallelism to 1 for the two tables.
Send us the init.ora file.
Waleed
-----Original Message-----
From: Huascar Espinoza [mailto:hespinoza_at_superele.gov.bo]
Sent: Thursday, March 11, 2004 8:35 AM
To: oracle-l_at_freelists.org
Cc: VChoque
Subject: RE: Oracle Performance
To realize the test, we have the basic following structure (similar on Oracle 9i and SQL Server 7.0):
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
-- 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 - 09:14:28 CST---------------------------------------------------------------- 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 -----------------------------------------------------------------
 |
 |