HELP: Oracle running really slow.

Howdy,

I've got two tables that I am joining with the following query:

Select Count(*)
From jwl_rep, jwl_alignment
Where jwl_rep.rep_id = jwl_alignment.rep_id;

-jwl_rep has 550 records, jwl_alignment 1.3 million. -there is a unique index
on jwl_rep.rep_id. -there is a nonunique index on jwl_alignment.rep_id. -both tables have been ANALYZED (with COMPUTE STATISTICS). -COST based optimizer is being used. -our SGA is huge (256 meg for buffers, 128 meg shared pool, 128 meg log buffer) -we are the only user of this Oracle instance

This query takes 25 - 30 seconds to execute in Oracle. The exact same data schema and query in MSSQLServer7 executes in 5-10 seconds. The difference gets worse with more complicated joins (joining in another table takes over 10 minutes in oracle and only 45 seconds in SQLServer).

The execution plan is different between MS and Oracle. Oracle does a full TABLE scan of the big table (jwl_alignment) and a unique INDEX scan of the small table (jwl_rep) to do the Join. SQLServer does a full INDEX scan of the small table (jwl_rep) and an INDEX seek of the big table (jwl_alignment). Either way is defendable because the index in jwl_alignment is so non-unique (550 unique values spread across 1.5 million records). No amount of hinting will make Oracle do it any other way.

Our Oracle instance is on a massive (terabytes of hd space and 4 gig of memory 4 processors) unix server and the SQLServer database is located on my own pc (800mhz, 256 meg ram). The Oracle/UNIX combination should blow away the SQLServer setup but is routinely badly beaten.

If anyone has any suggestions on why our Oracle database might be sucking so badly it would be greatly appreciated.

Sincerely,
--Dan

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 26 2000 - 15:37:18 CDT