Re: Oracle speed issues

Have you generated and stored table statistics (used by optimizer). You can generate statistics with the ANALYZE command and should be done as a normal part of maintenance.

AJ
Jack Toering <71045.3122_at_compuserve.com> wrote in message news: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 - 17:19:47 CEST