Re: Oracle running really slow.

Just a few comments:
In the future please post the *exact* explain plan results, instead of summarizing it.
From your summary it looks like Oracle is *not* using a cost-based approach, but a rule-based as that processes tables from right to left, whereas cost based processes them from left to right. Also it looks like the expression in your where clause needs to be swapped, you should always use
FK column = PK column, not the other way around. You should try to :
analyze ... compute statistics for all indexed columns size 10, which will give the optimizer a better picture of the distribution of the data. You should try to use the /*+ordered */ hint and or the /*+ first_rows */ hint.
Then, you must you have determined your SGA settings with a wet thumb in the air, so without any measurements.
All three of them are outrageous and they really suck, especially log_buffer which should not be greater than 1M, and definitely not 128M like you have. Usually this kind of setting will force the O/S to fault very heavily and you are just relocating the problem.

Hth,

Sybrand Bakker, Oracle DBA

<decardin_at_my-deja.com> wrote in message news:8ta4lt$3r7$1_at_nnrp1.deja.com...
> 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 - 18:03:02 CDT