JPA EclipseLink Oracle11 - Takes loong time

JPA EclipseLink Oracle11 - Takes loong time

I am working on a Java EE 7 EAR application made up of Maven modules. It
makes database calls to an Oracle 11 database on another server on the
LAN. I'm deploying to Glassfish 4.0.1-b03 running on Ubuntu 12.04. I've
just now pulled down the latest OJDBC6.jar from Oracle. As per
http://docs.oracle.com/cd/E18930_01/html/821-2418/gbxjh.html I put the
ojdbc6.jar in domain1/lib/ext.
Using SQLDeveloper and querying the database with what is pretty-much the
same query takes 10 miliseconds. The same query through the JPA/JPQL takes
0.5 to 3 minutes depending on the configuration and I'm trying to get this
down to something more reasonable.
The query returns 40 rows so pagination won't help.
I use Wireshark to monitor the data being returned and the time taken is
all on the database. The problem must lie in the query being sent.
I turned on logging with the following and I'm showing what I believe is
the query EclipseLink is sending to the DB:
<property name="eclipselink.logging.level" value="FINEST" />
<property name="eclipselink.logging.level.sql" value="FINEST" />
Config 1
persistence.xml contains:
<property name="eclipselink.target-database" value="Oracle"/>
Query in log:
SELECT ALERT_P, CNT, COLOUR, DFROMUIDCNT, FROMUIDCNT, IS_PRIORITYWORD,
IS_STOPWORD, N, NIJ, NRTCNT, PJ, RTCCNT, RTCNT, TIME_STAMP, STEM, TWEETS
FROM ALL_ALERTS WHERE (TIME_STAMP BETWEEN ? AND ?) ORDER BY TIME_STAMP,
STEM
Time taken 25 seconds
Config 2
Change persistence.xml to contain:
<property name="eclipselink.target-database" value="Oracle11"/>
There is an exception:
org.glassfish.deployment.common.DeploymentException:
java.lang.NoSuchFieldError: HINT
at
org.eclipse.persistence.internal.jpa.EntityManagerSetupImpl.deploy(EntityManagerSetupImpl.java:765)
at
org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.getAbstractSession(EntityManagerFactoryDelegate.java:204)
at
org.eclipse.persistence.internal.jpa.EntityManagerFactoryDelegate.createEntityManagerImpl(EntityManagerFactoryDelegate.java:304)
at
org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManagerImpl(EntityManagerFactoryImpl.java:336)
at
org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl.createEntityManager(EntityManagerFactoryImpl.java:302)
....
According to http://en.wikibooks.org/wiki/Java_Persistence/Oracle one
should use Oracle11. I don't know why I'm getting this exception. Any
thoughts on this problem?
Config 3
Remove eclipselink.target-database from persistence.xml.
Query is:
SELECT ALERT_P, CNT, COLOUR, DFROMUIDCNT, FROMUIDCNT, IS_PRIORITYWORD,
IS_STOPWORD, N, NIJ, NRTCNT, PJ, RTCCNT, RTCNT, TIME_STAMP, STEM, TWEETS
FROM ALL_ALERTS WHERE (TIME_STAMP BETWEEN ? AND ?) ORDER BY TIME_STAMP,
STEM
Oh, its the same as above so obviously this is not what is being sent to
Oracle.
Time taken: 2.5 minutes.
So the EclipseLink property is helping but perhaps the value of "Oracle11"
would work better if I can get that going.
Config 4
Same as Config 3 excepting I removed the ORDER BY TIMESTAMP, STEM.
Time taken: 1.5 minutes.
Config 5
Same as Config 1 excepting I removed the ORDER BY TIMESTAMP, STEM.
Time taken: 25 seconds.
Config 6
I restored the ORDER BY and moved the ojdbc6.jar from lib/ext to lib/.
Time taken: 26 seconds.
Config 7
Same as Config 6 but changed the eclipselink.target-database to Oracle11
and this time the error is:
org.glassfish.deployment.common.DeploymentException:
java.lang.NoClassDefFoundError: oracle/sql/TIMESTAMP
Verifying the wiki page as correct that the jar needs to be in ext to pick
up the Oracle-specific extensions.
It seems to me the problem is that the query being sent to Oracle isn't
optimized for it.
Can anyone tell me:
What that exception when using "Oracle11" is (when the jar is in ext/)?
Is there any other way the query can be optimized for Oracle?
Next I'm going to try a @NamedNativeQuery to see if I can use the same
query used in. SQLDeveloper. Though the query isn't complex so I can't see
this will improve on what JPA can do. That query is:
select * from all_alerts t1 WHERE t1.time_stamp between
to_date('2013-01-22 16:00:00','YYYY-MM-DD HH24:MI:SS') and
to_date('2013-01-22 16:05:00','YYYY-MM-DD HH24:MI:SS') ORDER BY
t1.time_stamp, t1.stem;
Actually what I'll try first is to make sure the dates I'm sending in my
JPQL queries are of the same format.
Any comments or feedback appreciated.