[Mybatis] Oracle Bind Mismatch 해결하기

오라클 쿼리 수행 순서

1. Shared pool로 로드: SQL 코드가 분석을 위해 메모리로 로드
2. 구문 분석: SQL 키워드 중 잘못입력된 키워드가 있는지 체크
3. 쿼리 변환: 복잡한 SQL을 동일하고, 단순한 형식으로 변환
4. 최적화(실행 계획 생성): 낮은 비용으로 데이터를 탐색할 수 있도록 의사결정 트리를 추축
5. 실행 파일 생성: SQL 쿼리를 서비스하는 기본 호출과 실행 파일을 생성
6. 행 페치(fetch): 행을 검색하여 데이터 전달

Soft parsing & Hard parsing

오라클에서 쿼리를 수행할때 무조건 위와 같은 쿼리 수행 순서를 따르는 것이 아니다.
오라클이 동일한 쿼리를 수행한다고 판단하면 1번의 단계를 건너 뛴 뒤 작업을 진행하게 되는데, 이를 Soft parsing이라 하고, 1~7의 단계를 모두 수행하는 것을 Hard parsing 이라고 한다.

이와 같은 이유로 Hard parsing 보다 Soft parsing이 쿼리 수행이 훨씬 빠르다.

Oracle Bind Mismatch 와 Hard parsing

Bind Mismatch는 Bind Type 이나 Bind Value의 크기 변화로 인해 Bind 변수가 공유되지 못하는 현상을 뜻한다.

예를 들어

PreparedStatement stmt = con.prepareStatement("SELECT * FROM bind_test WHERE name =?")
stmt.setString(1, 'a');
//... 생략

PreparedStatement stmt = con.prepareStatement("SELECT * FROM bind_test WHERE name =?")
stmt.setString(1, 'aaaaaaa.....(생략)');

//... 생략

로 데이터를 처리할때, 두 쿼리는 Bind 된 변수의 길이가 달라 서로 다른 쿼리로 인식하고 두 쿼리 모두 하드 파싱이 일어나게 된다.

오라클은 Varchar형의 Bind 변수의 경우, 그 길이를 다음과 같은 구간의 값으로 반올림 한다.

32 byte
128 byte
2000 byte
4000 byte

즉 varchar 타입의 Bind 변수가 1~32byte 일 경우에는 소프트 파싱이 발생하고 이 외에 바인드 된 값이 커지면 하드 파싱이 일어난다.

Hard parsing 해결 방법

alter session set cursor_sharing=force로 설정하여 where 조건에 바인드된 변수의 길이가 다르더라도 소프트 파싱을 하도록 유도할수 있다.

추가 옵션에 대한 상세한 내용은 여기에서 확인 가능하다.

Mybatis에서 cursor_sharing 설정 방법

Mybatis의 DefaultSqlSessionFactory와 SqlSessionFactoryBuilder 상속받아 구현하면 된다.

import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.TransactionIsolationLevel;
import org.apache.ibatis.session.defaults.DefaultSqlSessionFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class CustomSqlSessionFactory extends DefaultSqlSessionFactory {
    public CustomSqlSessionFactory(Configuration configuration) {
        super(configuration);
    }

    @Override
    public SqlSession openSession()
    {
        SqlSession session = super.openSession();
        alterSession(session);
        return session;
    }

    protected void alterSession(SqlSession session) {
        try {
            //addBatch를 통해 cursor_sharing을 설정한다.
            Statement statement = session.getConnection().createStatement();
            statement.addBatch("alter session set cursor_sharing=force");
            statement.executeBatch();
            statement.close();
            statement.close();
        }
        catch (SQLException e) {
            log.error("Alter session failed!", e);
        }
    }

    @Override
    public SqlSession openSession(boolean autoCommit)
    {
        SqlSession session = super.openSession(autoCommit);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(Connection connection)
    {
        SqlSession session = super.openSession(connection);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(ExecutorType execType)
    {
        SqlSession session = super.openSession(execType);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(ExecutorType execType, boolean autoCommit)
    {
        SqlSession session = super.openSession(execType, autoCommit);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(ExecutorType execType, Connection connection)
    {
        SqlSession session = super.openSession(execType, connection);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(ExecutorType execType, TransactionIsolationLevel level)
    {
        SqlSession session = super.openSession(execType, level);
        alterSession(session);
        return session;
    }

    @Override
    public SqlSession openSession(TransactionIsolationLevel level)
    {
        SqlSession session = super.openSession(level);
        alterSession(session);
        return session;
    }
}

DefaultSqlSessionFactory

import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class CustomSqlSessionFactoryBuilder extends SqlSessionFactoryBuilder {
    @Override
    public SqlSessionFactory build(Configuration config) {
        return new CustomSqlSessionFactory(config);
    }
}

위와 같이 설정한 뒤 Spring에서 SqlSesisonFactory 생성시 setSqlSessionFactoryBuilder 메소드를 통해 SqlSessionFactoryBuilder를 지정해 주면 된다.

    @Bean
    public CustomSqlSessionFactoryBuilder customSqlSessionFactoryBuilder() {
        return new CustomSqlSessionFactoryBuilder();
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource());
        bean.setTypeAliasesPackage("..."); //생략
        bean.setMapperLocations("..."); //생략
        bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        //setSqlSessionFactoryBuilder 메소드를 통해 위에서 생성한 SqlSessionFactoryBuilder를 지정하면 된다.
        bean.setSqlSessionFactoryBuilder(customSqlSessionFactoryBuilder());
        return bean.getObject();
    }

 

참고자료

1. Oracle hard-parse vs. soft parse
2. Oracle Metric hard parse (bind mismatch) elapsed time