2

I have a Java application with JPA and eclipse link as backend . I had like to find explain plan of a particular query that gets executed, while the application is running . I know i can check it through sql developer , but i would like to find the explain plan of the query when it is invoked by the application . Is there a way to do it?

I tried adding explain plan for select 1 from dual but it kept throwing exception.

Sam
  • 1,298
  • 6
  • 30
  • 65

1 Answers1

1

You do not have many options. For some unknown reason both hibernate and enclipselink do not support something like that see setting-context-in-a-jpa

So you have to use pure Oracle solution

  • Create LOGON TRIGGER ON SCHEMA and enable session tracing in it. Run the app and analyze the trace file then
  • Check content of the v$SQL_PLAN view and check the real exec plan for the query.

If you insist on doing it from Eclipse link then you must start transaction somehow, to avoid returning the session into the pool. Query v$mystat and v$session to get SID, SERIAL# (to get unique session identifier). Then execute your query. And get SQL_ID and PREV_SQL_ID from v$session. Check v$sql_plan and v$sql_text to get queries sql text and exec plan.

Community
  • 1
  • 1
ibre5041
  • 4,903
  • 1
  • 20
  • 35