오라클 DB튜닝_소프트파싱,하드파싱

 

요새 회사에서 운영중인 서비스가 이런 저런 문제들로 조용할일이 없는데, 휴가를 다녀오고 보니 DB 성능문제로 시끄러웠다. 
해결은 한 듯 해서 해결한 방법중에 한가지에 대해 정리하려고 한다.
공부 하면 할수록 성능이 개선되는걸 보자니 좀 재미있는것 같기도 하다. 

⭐공부

용어 정리

Shared Pool

• Oracle의 주요 특징 중 하나로 SGA(system global area .. buffer cache,shared pool 등등 이 있음) 내에 shared Pool을 도입함.
• 소프트,하드파싱된 SQL문 캐시를 저장하는 곳인듯
• 쉐어드 풀을 Flush시키는 경우 적재된 딕셔너리 캐시가 삭제되었기 무조건 캐시를 적재할듯

Literal SQL

리터럴 SQL이란 SQL 문장 작성 시 where절의 비교되는 값에 문자/숫자 상수값을 하드코딩해서 작성하는 것을 말함
리터럴 sql문을 사용하면 하드파싱의 빈도를 높이게 되어 캐시 사용률을 높이게 됨.

리터럴 SQL의 예
select * from A WHERE B=1;

Hard Parsing

• 하드파싱의 경우 쿼리 하나하나가 캐싱되므로 상대적으로 오래된 sql문은 age out되어 캐시가 삭제되고, dictionary cache 사용율을 높히게 됨.
• 하드 파싱이란 SQL 문장이 처음 실행될 떄 shared Pool/Libarary Cache에 없으니까 완전히 전부 새로 파싱을 한다는 의미.
• SQL의 실행 4단계 중 최적화(최적의 실행계획 생성), 로우 소스 생성(로우 소스 트리 생성) 과정을 수행한다는 의미임.

---

Bind variable SQL

Bind variable SQL은 where절의 특정 값을 표시하는 자리에 바인드 변수 형태로 표시하는 것을 말한다.

Bind variable SQL의 예
select * from A WHERE B=:1;

Soft Parsing

• 실행하고자 하는 SQL 문장이 이미 shared Pool / Library Cache에 있으므로, 파싱트리,실행계획을 그대로 재사용하므로 최적화,로우소스생성 단계를 생략함.
• 그래서 대부분의 SQL 문장 실행시간은 처음보다 두번째가 빠르다.

Hard parsing 되는 케이스

1. 같은 쿼리라도 쿼리하는 계정이 다를 때
2. 쿼리의 공백이 다를 때
3. 쿼리의 라인이 다를 때
4. 대소문자가 다를 때

---

⭐정리

• 리터럴 SQL문을 찾아서 bind variable을 이용한 방법으로 바꿔야(소프트파싱) 성능 향상에 도움이 된다.

리터럴 SQL문을 찾는 방법

• Shared Pool에 적재된 딕셔너리캐시 실행횟수가 1인 쿼리를 v$sqlarea 뷰에서 찾는다.
• v$sqlarea는 DB 재기동 시 데이터가 없어지니 참고

  select sql_text
  from v$sqlarea
  where executions = 1

mybatis에서의 하드파싱과 소프트 파싱

• XML파일에 작성한 쿼리가 수행될 때 Literal변수($),Bind 변수(#)에 따라 하드파싱/소프트파싱이 일어난다.

bind변수(#)

• 좋은점
  • String 형태로 바인딩되기 때문에 해당 값 자체가 파라미터가 된다.(= sql injection 공격을 막을 수 있다.)
  • 하드파싱을 막아준다.

Literal변수($)의 좋은점

• 좋은점
  • 파라미터를 해당 컬럼 자료형에 맞춰 변경
  • 파라미터가 바로 출력되기 때문에(String 형태로 바뀌지 않기 때문에) 테이블명이나 컬럼명등을 파라미터로 넘길 수 있다.
• 나쁜점
  • 하드파싱
  • sql injection 공격을 막을 수 없다.(보안 취약)

하드파싱은 무조건 나쁠까?

• 데이터가 균형적이면 왠만하면 좋다. 소프트파싱의 경우 동일한 실행계획을 갖게 되는데, A라는 조건일 때 쿼리 결과가 데이터의 90%, B일때는 10%의 결과가 출력된다고 해보자.
• 이 때 B의 쿼리가 먼저 실행되어 이에 맞는 실행계획이 캐싱된다면 A라는 쿼리를 실행하기에는 맞지 않는 실행 계획일 수 있어 성능에 영향을 줄 수 있다.

⭐마무리

• 데이터 구조를 생각해서 SQL문을 만들면 좋을 것 같다. 단 리터럴변수를 사용하는 경우에는 보안취약점에 대한 대책을 세워야 할듯 하다. (사실 스프링을 쓰고 있다면 한번만 귀찮으면 될 것 같은데 ?)
• 정리하다 보니 예전에 팀장님께서 교육해주신 oracle 구조에 대해 어렴풋이 생각이 나긴 하는데 90%는 까먹은것 같다. 배울건 많은데 공부한건 자꾸 까먹으니 큰일이다.

---

⭐추가로 알아볼 점

- 데이터  특정 쿼리 사용량이 굉장히 많아 한 쿼리의 캐시 실행횟수(executions)가 굉장히 높아진다면 어떤 의미가 있고, 범위가 작은 랜던값을 쿼리에 파라미터 값으로 전달해서 강제로 캐시를 나누게 되면 어떤 효과가 있게되는걸까?

-> 바로 알아보자

오라클 DB튜닝_소프트파싱,하드파싱_2

 

⭐참고

 

MyBatis에서 #과 $의 차이 ( Difference between # and $ in MyBatis )