Fetch jpa sql execution plan with @QueryHint and spring data repository

Question

I am using spring-data for DB interaction. I want to see the jpa sql execution plan for a query written in repository. How can i do it.

https://vladmihalcea.com/execution-plan-oracle-hibernate-query-hints/ tells about using GATHER_PLAN_STATISTICS and COMMENT query hints. I added COMMENT hint but don't know how to add other one.

public interface StudentRepository extends JpaRepository<Student, Long>{
   @QueryHints({
       @QueryHint(name=org.hibernate.annotation.queryHints.COMMENT, 
        value="SQL_PLAN_STUDENT")
   })
   List<Student>findByStudentIDIn(List<Long> ids);
}

score 1 · Answer 1 · answered Jul 23 '19 at 20:14

The @QueryHints annotation accepts an array constructor like list of @QueryHint items.

So you can add multiple QueryHints by addings them to a comma separated list. For example:

   @QueryHints({
            @QueryHint(name=org.hibernate.annotations.QueryHints.COMMENT, value="SQL_PLAN_STUDENT"),
            @QueryHint(name="GATHER_PLAN_STATISTICS" , value="GATHER_PLAN_STATISTICS")
    })

Unfortunately I don't have access to a running instance of a oracle dbms, so I can't check the result of the given hint.

Fetch jpa sql execution plan with @QueryHint and spring data repository

1 Answers1