Database Tuning (MOSC)

MOSC Banner

Java versus OCI - same SQL , different performance

edited Nov 4, 2011 9:03AM in Database Tuning (MOSC) 1 comment
It appears that we have the same SQL that performs well when executed via OCI and performs very badly when executed under JAVA (the database version is 10.2.0.4)

Here is the SQL

select  col1, col2 from table where  col2 between to_number(:1) and to_number(:2) and ( (col3 in ('3', '4') and col4 !=
'Z') or col5 = to_date(:3, 'YYYYMMDD') )  order by col1

According to the explain plan, Oracle runs a full scan on a unique index that exists on the table and includes col1 and then it reads the rest of the data from the table itself. I am not sure how efficient this plan is (I would rather do a full table scan against the table and save on the IOs), but this is the same explain plan that I have seen in production where an old code (OCI - based) is used and no performance issues have been reported.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center