매개 변수화된 sql 쿼리 느리고 활성화된 오라클 세션의 최대 절전 모드

매개 변수화된 sql 쿼리 느리고 활성화된 오라클 세션의 최대 절전 모드

오라클 데이터베이스에 대한 최대 절전 모드 쿼리로 며칠 동안 어려움을 겪고 있습니다.이와 같은 것은 기록을 그리드에 공급하는 데 사용됩니다.

SELECT 
    fields
FROM 
    tables and JoinedTables
WHERE
        Field1  >= :value1
    AND Field2 = :value2
    AND Field3 = :value3
Order By MaintTable.Id Desc

Spring Java + Hibernate 4.2 방법으로 이 방법을 사용합니다.

SQLQuery query = (SQLQuery) session.createSQLQuery(querySql)
                                .addEntity(CertificateViewEnt.class)
                                .setParameter("value1", firstCertificateRecordDate)
                                .setParameter("value2", certType.toUpperCase())
                                .setParameter("value3", deleted? 1:0);      

필터링된 모든 필드가 올바르게 색인화되고 기본 테이블에 함수 색인이 작성됩니다.성능 향상을 위한 ID Descendent.

처음에는 세션/연결 풀이 올바르게 관리되지 않는 것으로 생각하여 상태 비저장 세션으로 변경하고 이 session.close()를 추가했습니다.

query.setCacheable(false)
              .setTimeout(30)
              .setReadOnly(true);
...
...
//Pagination
query.setMaxResults(rows);
query.setFirstResult(HelperMethod(page, rows));

result = (List<CertificateViewEnt>) query.list(); 
session.close();
return result;

해결이 안 됐어요.쿼리가 몇 번 정상적으로 실행되지만 알 수 없는 이유로 인해 이전에 이미 실행된 값을 사용하면 Oracle에서 세션이 중단되고 세션이 열립니다(status=ACTIVE) 및 시간 초과 시 실패합니다.모든 SQL 클라이언트에서 Oracle과 동일한 쿼리를 실행하고 가능한 모든 조합의 매개 변수를 사용하여 수십 번 실행하는 동일한 쿼리는 한 번에 10개의 레코드에 대해 약 400ms의 최고 성능으로 실행됩니다.

여기저기 기사를 읽고 링크1 [Hibernate + Java에서는 성능이 느리지만 동일한 네이티브 Oracle 쿼리와 TOAD를 사용하면 빠름 링크2: [쿼리에 오라클 10g이 걸려 있음

Hibernate에서 QueryPlan을 제대로 사용하지 못할 것으로 예상하고 bound parameter를 사용하여 모든 필터를 제거하기로 결정했고 해결되지 않았지만 조금 더 나아졌습니다.잠시 후 1, 2, 3, 4 페이지와 같은 다른 페이지로 이동할 때 중단되었습니다.

그 후 하이버네이트의 방법으로 생성된 SQL이 의심스러웠습니다.

query.setMaxResults(rows)
query.setFirstResult(SomeHelperMethod(page, rows));

로그에서 오라클에 바인딩 매개변수로 전달되는 것을 보았기 때문입니다.

       ...
       Order By Certificado.Id Desc ) row_
       where rownum <= ?)
  where rownum_ > ?

나도 추적 기록에서 이것을 봤습니다.

2015-09-15 14:09:53 TRACE QueryPlanCache:200 - Located native-sql query plan in cache (SELECT /*+ INDEX(

그리고 이것:

2015-09-15 14:09:53 TRACE BasicBinder:84 - binding parameter [2] as [VARCHAR] - E
2015-09-15 14:09:53 DEBUG Loader:2031 - bindNamedParameters() 0 -> deleted [3]
2015-09-15 14:09:53 TRACE BasicBinder:84 - binding parameter [3] as [INTEGER] - 0
2015-09-15 14:09:53 TRACE Loader:1931 - Bound [7] parameters total
/*
SLOW here !!!  Around 3 secs when query runs in ~0,300 secs via SQL client.
And ACTIVE sessions are left running in Oracle.
*/
2015-09-15 14:09:56 TRACE JdbcCoordinatorImpl:397 - Registering result set [org.apache.commons.dbcp.DelegatingResultSet@f0c620]
2015-09-15 14:09:56 TRACE Loader:943 - Processing result set

마지막으로 모든 Hibernate bind param을 포기하고 사용자 지정 계산 페이지화를 구현하고 페이지 행을 검색하기 위해 모든 SQL을 작성했으며 DB 세션을 올바르게 실행하고 관리하고 있습니다.

그래서 제 질문은 다음과 같습니다.최대 절전 모드는 쿼리가 데이터베이스에 대해 실행될 때 실행되지 않도록 하는 장면에서 무엇을 합니까?바인딩 매개 변수 쿼리에 알려진 문제가 있습니까?

바인딩 매개 변수가 있을 때 모든 SQL 코드를 작성하고 이 SQL을 하드 파싱하는 것을 별로 좋아하지 않습니다.

환경에 대한 몇 가지 참고 사항:Tomcat과 Oracle은 동일한 호스트에 있습니다.그래서 네트워크 연결이 문제가 아닙니다.

동면 버전 4.2.15 Final

테이블에는 dev 데이터베이스에 약 300k rec가 있으며(생산 시 1,5M) Primary Key Desc(Sequence generated)별로 정렬된 10, 20, 50rec의 페이지가 한 번에 표시됩니다.

Hibernate 전문가들이 이 문제에 대해 도움을 주어서 대형 데이터베이스 프로젝트에서 Hibernate 쿼리를 여전히 신뢰할 수 있기를 바랍니다.미리 감사드립니다.

이것이 문제인지는 모르겠지만 Oracle은 쿼리를 구문 분석할 때 bind 변수 값을 확인한 다음 쿼리 계획을 저장하므로 새로운 바인딩 변수 집합으로 쿼리를 실행할 때마다 쿼리를 계속 구문 분석할 필요가 없습니다.하지만 가끔 쿼리가 다시 구문 분석됩니다.구문 분석 중에 비정상적인 바인딩 변수 값이 전달되면 잘못된 계획이 저장되어 사용됩니다.그것은 일종의 구속 변수의 저주입니다.이를 통해 구문 분석을 줄일 수 있지만 쿼리를 다시 구문 분석할 때 일반적인 바인딩 변수 값에 대해 계획을 뒤집을 수 있습니다.힌트가 도움이 됩니다.SQL Profiles를 사용하여 계획을 변경하려는 바인딩 변수가 있는 쿼리의 계획을 잠급니다.최적화 통계가 수집되는 시기와 방법을 사용자 정의하여 바인딩 변수에 전달되는 값에 관계없이 양호한 계획이 생성되도록 할 수도 있습니다.

어쨌든, 그것은 제가 항상 보는 것이고 당신의 문제일 수도 있고 아닐 수도 있습니다.

바비

언급URL : https://stackoverflow.com/questions/32596761/hibernate-parameterized-sql-query-slow-and-active-oracle-sessions