Oracle very slow than sybase when there is multiple joins among tables

[Artemesia Lakener]

I have tested a SQL query on Sybase and Oracle. The query involves join of 3 to 4 tables. Same data set in Sybase and Oracle. Same JDBC/Java code. Test shows the query from Oracle is significantly slower than Sybase. What can be the reason ? Just reindex ? anything else to your experience ?

Thanks.

[Michael Duffy]

Originally posted by Artemesia Lakener:
I have tested a SQL query on Sybase and Oracle. The query involves join of 3 to 4 tables. Same data set in Sybase and Oracle. Same JDBC/Java code. Test shows the query from Oracle is significantly slower than Sybase. What can be the reason ? Just reindex ? anything else to your experience ?

Thanks.

EXPLAIN PLAN on both and see what the difference is.

Indexes, primary keys, order of JOIN - all will affect performance.

[Michael Duffy]

Difference JDBC drivers, too.

Sure you know where the time is being spent? Have you profiled it to be sure?

[Artemesia Lakener]

Originally posted by Michael Duffy:


EXPLAIN PLAN on both and see what the difference is.

Indexes, primary keys, order of JOIN - all will affect performance.

can you help further on "order of JOIN" ? if I have join among 2 big tables and 1 small Table, what order will be best ?

select * from BigTable_1, BigTable_2, SmallTable_1
select * from SmallTable_1, BigTable_1, BigTable_2
select * from BigTable_1, SmallTable_1, BigTable_2

[Artemesia Lakener]

Actually I think theoretically we should be able to break any JOIN among tables into a series of chained SELECT statements like --

SELECT * from Table_1 where col_1=.. and col_2=.. and col_3 IN (SELECT col_3 from Table_2 where col1=.. and col2 IN (SELECT col2 from Table_3 where col1=.. and col3=..)

Things like that, right ?

[Avi Abrami]

Artemesia,
Excuse me, but I couldn't find any details in your post of the Oracle database version you are using. For your information, since Oracle 9i, the rule-based optimizer (RBO) has been replaced by the cost-based optimizer (CBO). In order for the CBO to perform efficiently, you need to perform a task known as "gathering statistics".

There are entire books that are completely devoted to tuning Oracle databases. In fact, there are entire books devoted to tuning SQL statements alone! So your question is very broad, and therefore difficult to provide a satisfactory answer for, in a forum (like this one).

Allow me to suggest that you start with the Oracle documentation, in particular Oracle Database Performance Tuning Guide. You can access it via the following Web site:

http://www.oracle.com/technology

By the way, the things Michael mentioned -- EXPLAIN PLAN, etc -- are described in the "Performance Tuning Guide", I referred you to.

Good Luck,
Avi.
[ February 21, 2006: Message edited by: Avi Abrami ]

[Reid M. Pinchback]

It can also matter which version of Oracle you are using. Oracle 8 and back used dictionary-managed tablespaces, and improper extent sizing can crush query performance for non-trivial table sizes. Storage management options increased in later versions, and sometimes the specifics you hit depend on the version-specific defaults or how your DBA chose to set up the database.