About 20 seconds just to call an stored procedure
Hello,
I have the following calling to a PL/SQL stored procedure in orm.xml:
<named-native-query name="WEP_EXAMENES_UXXI_SEAM" result-class="es.ual.webacademica.entity.Examenes">
<query>{call UAL$UTL_WEBB.P_HOR_EXAMENES(?,:I_PLAN,:I_FINI,:I_FFIN)}</query>
<hint name="org.hibernate.callable" value="true" />
<hint name="org.hibernate.readOnly" value="true" />
</named-native-query>
When I call it in my bean BeanExamenes, the calling lasts about about 20 secs, while if I execute the procedure itself (with TOAD for example) it lasts about 2 seconds.
The calling in BeanExamenes is like that:
@Scope(ScopeType.CONVERSATION)
@Name("beanExamenes")
public class BeanExamenes {
private List<Examenes> examenes;
@In EntityManager entityManager;
...
...
public List<Examenes> getExamenes() {
if(examenes == null){
examenes = new ArrayList<Examenes>();
SimpleDateFormat formatoDeFecha = new SimpleDateFormat("dd/MM/yyyy");
Query query = getEntityManager().createNamedQuery("WEP_EXAMENES_UXXI_SEAM");
query.setParameter("I_PLAN",plan);
query.setParameter("I_FINI",formatoDeFecha.format(f_inicio));
query.setParameter("I_FFIN",formatoDeFecha.format(f_fin));
examenes = new ArrayList<Examenes>(query.getResultList());
log.info("Hola, estoy en el getExamenes");
}
...
}
And the debug info I get in the console:
09:05:26,702 INFO [STDOUT] Hibernate:
select
taluplaest0_.CODALF as CODALF8_0_,
taluplaest0_.ANY_ANYACAETG as ANY2_8_0_,
taluplaest0_.ANY_ANYACAFIN as ANY3_8_0_,
taluplaest0_.ANY_ANYACAINI as ANY4_8_0_,
taluplaest0_.CODCICLO as CODCICLO8_0_,
taluplaest0_.CODOFIPLA as CODOFIPLA8_0_,
taluplaest0_.CONVENIO as CONVENIO8_0_,
taluplaest0_.CREEQU as CREEQU8_0_,
taluplaest0_.CREMINPRJ as CREMINPRJ8_0_,
taluplaest0_.DATPUB as DATPUB8_0_,
taluplaest0_.DCTMAT as DCTMAT8_0_,
taluplaest0_.DECRETO as DECRETO8_0_,
taluplaest0_.DURACION as DURACION8_0_,
taluplaest0_.EST_CODALF as EST14_8_0_,
taluplaest0_.ESTMODU as ESTMODU8_0_,
taluplaest0_.FLGAUTLC as FLGAUTLC8_0_,
taluplaest0_.FLGAUTMAT as FLGAUTMAT8_0_,
taluplaest0_.FLGCNJ as FLGCNJ8_0_,
taluplaest0_.FLGESTUNI as FLGESTUNI8_0_,
taluplaest0_.FLGMATQNTPEN as FLGMATQ20_8_0_,
taluplaest0_.FLGNOR as FLGNOR8_0_,
taluplaest0_.FLGPAR as FLGPAR8_0_,
taluplaest0_.FLGPLANTILLA as FLGPLAN23_8_0_,
taluplaest0_.FLGQNTPENAPL as FLGQNTP24_8_0_,
taluplaest0_.FLGREB as FLGREB8_0_,
taluplaest0_.FLGREN as FLGREN8_0_,
taluplaest0_.FLGSET as FLGSET8_0_,
taluplaest0_.FLGSML as FLGSML8_0_,
taluplaest0_.FLGSOLOPRO as FLGSOLOPRO8_0_,
taluplaest0_.FLGTAN as FLGTAN8_0_,
taluplaest0_.FLGTEC as FLGTEC8_0_,
taluplaest0_.FLGVALACA as FLGVALACA8_0_,
taluplaest0_.FLGVEX as FLGVEX8_0_,
taluplaest0_.GRA_CODNUM as GRA34_8_0_,
taluplaest0_.GRA_FLGOFI as GRA35_8_0_,
taluplaest0_.ISCED as ISCED8_0_,
taluplaest0_.MOD_CODALF as MOD37_8_0_,
taluplaest0_.NOLID1 as NOLID38_8_0_,
taluplaest0_.NOLID2 as NOLID39_8_0_,
taluplaest0_.NOLID3 as NOLID40_8_0_,
taluplaest0_.NOMID1 as NOMID41_8_0_,
taluplaest0_.NOMID2 as NOMID42_8_0_,
taluplaest0_.NOMID3 as NOMID43_8_0_,
taluplaest0_.NUMANYS as NUMANYS8_0_,
taluplaest0_.NUMHOR as NUMHOR8_0_,
taluplaest0_.NUMREF as NUMREF8_0_,
taluplaest0_.NUMSEGEXP as NUMSEGEXP8_0_,
taluplaest0_.PERMAT as PERMAT8_0_,
taluplaest0_.STE_CODALF as STE49_8_0_,
taluplaest0_.TCURS_CODALF as TCURS50_8_0_,
taluplaest0_.TES_CODALF as TES51_8_0_,
taluplaest0_.TIPFIN as TIPFIN8_0_,
taluplaest0_.TIPPLA as TIPPLA8_0_
from
UXXIAC.TALU_PLAESTUDI taluplaest0_
where
taluplaest0_.CODALF=?
09:05:26,966 INFO [STDOUT] Hibernate:
{call UAL$UTL_WEBB.P_HOR_EXAMENES(?,?,?,?)}
09:05:45,008 INFO [BeanExamenes] Hola, estoy en el getExamenes
Any ideas about the big difference cost in time beetwen calling the pl/sql directly and through hibernate?
I'm using
Oracle JDBC driver, version: 10.2.0.5.0.
Hibernate 3.5
Jboss seam: 2.2.2.Final
Thanks in advance!
Pablo