• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 162
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Difference JDBC drivers, too.

Sure you know where the time is being spent? Have you profiled it to be sure?
 
Artemesia Lakener
Ranch Hand
Posts: 162
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 162
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ?
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic