Oracle speed issues

From: Jack Toering <71045.3122_at_compuserve.com>
Date: Tue, 6 Jul 1999 09:45:44 -0400
Message-ID: <uMrLlZ7x#GA.176_at_nih2naae.prod2.compuserve.com>

I've installed Oracle, SQLAnywhere and MSSQL on a single system. I have not tested Sybase Adaptive Server Enterprise yet, but will. I've loaded 138,000 rows into one table, and 1600 into a related table. All software packages were standard installs to Pentium 550 NT4/SP5 Server, with 256Megs of RAM, and a single Cheetah 18 gig with Adaptec 2940UW2. Not much of a system for this, but all times are relative to each other. Oracle tables were created in the SCOTT database, and the MSSQL tables were created in the Northwind database. Query is the same to all tables. I've used them all with ODBC and native drivers. There is no perceptable difference in speed between native and ODBC.

Pulling in the first 25 rows for a browse from the 138,000 row table where there is an exact match key;

1.  SQLAnywhere = Instant - Too fast to measure
2.  MSSQL = Near Instant - Pegs CPU
3.  Oracle = 1 minute - Uses about 18% of the CPU power on average, disk

fairly busy.

Pulling in the first 25 rows for a browse from the 138,000 row table JOINed by PK 1600/FK 138,000 Ordered by an exact match index in the 1600 row table and exact match index in the 138,000 row table.

1.  SQLAnwhere = 3 1/2 Min - Pegs CPU, disk very busy
2.  MSSQL = 7 Seconds - Pegs CPU, little disk activity.
3.  Oracle = 1 Minute 50 seconds = Uses about 18% of CPU power on average,

disk fairly busy.

All systems worked well in a Multi-user environment and none had any problems with concurrency or lookups.

Dropping indexes on the Oracle or MSSQL tables make no difference in speed. Dropping the indexes in SQLAnywhere makes a huge difference in speed in the first example, and no difference in speed in the JOIN.

I've been using the Sybase product, but in a good 3rd normal form database, SQLAnywhere cannot efficiently ORDER a JOIN when the ORDER BY spans more than one table, hence my reason to investigate another database.

Here is what I won't do. I won't use HINTs, especially when the DBMS has indexes that are an exact match for the query, and I won't throw hardware at it so it can do table scans faster. The DMBS has more information at it's disposal than the programmer, and the information is dynamic. If the DBMS is too stupid to figure out what INDEXes to use when it has exact matches for the query, it must be cave man software. I originally planned on using Oracle. Oracle has a lot of software out there, and runs on several platforms.

I'm hoping that I'm doing something wrong with Oracle. Today if I look at performance, administrative tools, documentation, ease of installation, and ease of administration, MSSQL wins every one.

I'm in no way trying to put Oracle down. I had planned to use it. There was a good deal offered to me at the Oracle FYE, but I had to let it go because of these problems. I don't buy anything based on hearsay or who makes it. Not Oracle, not MS, and not Sybase. I buy what works the best for me. Any ideas as to how to make Oracle faster without using HINTs, is welcome.

Thanks in advance for any responses,
Jack Toering Received on Tue Jul 06 1999 - 15:45:44 CEST