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

이전글과 연결되는 글 입니당.

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

회사에서 문제가 되는 쿼리 개선을 위해 2가지 처리를 했는데
한가지는 앞서 포스팅한 리터럴 변수를 바인딩 변수로 변경했다.
이를 통해 쉐어드풀 내에 하드파싱되던 부분이 소프트 파싱되도록 변경됐다.

두번째로 적용한 내용은 쿼리 내에 주석을 넣고, 그 안에 바인딩 변수로 랜덤 상수를 넣는것이다. (범위는 1~5정도)

이렇게 되면 오라클은 1,2,3,4,5가 들어간 쿼리를 각각 다른 SQL문이라고 판단하여 총 5개의 캐시를 적재한다.

이것이 어떤 효과가 있는지 알아보자.

---

⭐공부

• (참고)실행계획 생성하는건 CPU를 많이 잡아먹는다

Cursor Sharing

• 커서라는건 쉐어드풀내 (라이브러리) 캐시에 저장된 구문분석 된 SQL문을 얘기함.
  • 부모커서(SQL문 전체 문장과 정적 메타데이터)
  • 자식커서(다이나믹 변수)
• 커서 쉐어링 기법은 상수를 사용한 SQL문을 자동으로 바인드 변수를 사용하게끔 치환해서 커서가 공유되도록 해주는 기능임.
• SQL문 실행 시 실행계획을 생성하며 CPU를 많이 잡아먹는 부분을 피하기 위해 도입된 기능.

1. 쿼리를 실행하면 shared pool 안에 공백,대소문자등이 모두 일치하는 SQL문이 있는지 검사한다. (있으면 4번, 없으면 2번 수행)
2. Shared Pool에서 유사한 SQL문을 찾는다. 없다면 Hard Parsing(파싱트리 및 실행계획 만들고 4번으로 이동, 이미 있다면 3번 수행)
3. 유사한 SQL문을 찾았다면, 유사하지 않은 부분의 실행계획이 이미 캐시된 실행계획과 공유가 될 수 있는지 확인하고 공유 불가능하면 하드파싱 수행 및 4번 진행.
4. SQL Area아 SQL문 및 파싱트리,실행계획을 공유시킴.
5. 실행계획대로 SQL문을 실행

카디날리티 피드백 (11g버전)

• 앞서 얘기한 하드파싱이 무조건 나쁜가를 염두하고 생각하면 이해하는데 도움이 될 것 같다.(소프트 파싱으로 저장된 실행 계획이 모든 동일한 캐시를 타는 쿼리에 동일한 성능을 가지지 않는다는 부분)
• 쿼리가 수행되고 결과 집합이 캐싱된 실행결과(예상)과 다르다면 카디날리티 피드백은 부모커서를 마크할 수 있다.
• 마크가 되면 다음번 파싱 때 하드파싱을 수행하게 된다.

🌠뮤텍스(Mutex)

• 다수의 프로세스가 동일한 리소스를 공유할 때 동시 사용을 피하기 위해 사용되는 알고리즘

직렬화

• i-1,i,i+1,i+2의 노드가 연결되어 있을 때 i,i+1 노드를 삭제한다고 칠 때 프로세스
  • i 삭제 > i-1과 i+1 연결
  • i+1 삭제 > i-1과 i+2연결
  • 결론 : 한번에 한 노드만 삭제할 수 있다(동시삭제 불가능)
• 🌠부모와 자식커서는 각각의 뮤텍스를 갖는다. 하드파싱이 일어난다면 직렬화의 특징에 의해 뮤택스 삭제 및 연결이 일어날건데, 쿼리가 굉장히 자주 일어난다면 그만큼 DB에 부하를 줄것임.
• 세션이 맺어지고, SQL문을 실행하기 위해서는 세션 ID가 SGA 내의 메모리 구조를 할당받아야 함
  • 메모리 구조(memory structure)를 할당받으려면 컨트롤 구조(control structure)를 할당받아야 함
  • 🌠컨트롤 구조를 할당할 때 뮤택스가 사용됨
  • 🌠컨트롤 구조를 할당할 때 대기가 오래 발생하는 성능이 현저히 저하됨

⭐⭐뮤텍스 경합

• 여러개의 프로세스가 동일한 자원을 요청할 때 경합이 발생한다.
  • 여러개의 세션이 (쉐어드풀에 있는)라이브러리 캐시 내에 커서(자원)을 요청한다면 한 세션은 자원을 잡고있고, 나머지는 대기(WAIT = SLEEP)한다.
  • 대기하는 세션들은 뮤텍스의 상태를 계속해서 체크(질문)하는데 이를 🌠스피닝(spinning)이라고 한다.
    • 스피닝은 while문을 생각하면 되는데, 사용 가능한 상태인지 계속 체크하고, 이 무의미한 반복 체크 작업은 CPU를 많이 쓴다.

---

⭐정리

해결법1 (정말 급할때만 !)

• 단기 해결법으로는 SGA영역을 flush해서 오라클의 메모리영역을 RESET하는 방법임

해결법2

• 쿼리 사용량이 높고, 이 쿼리가 하나의 SQL로 라이브러리 캐시에 적재되어 있다면 이 커서를 사용하려는 세션이 많아지며 경합이 발생한다.
• 그렇기 때문에 사용량이 높은 SQL문을 여러가지 쿼리로 캐시에 적재하면 위 부분이 분산되는 효과를 받을 수 있다. 아래와 같이 구현하면 된다. 
• SELECT * FROM A WHERE B=:p1 -- 라는 쿼리가 있다면 .. -- 1) p2라는 바인드변수를 주석 안에 설정해준다. -- 2) p2 파라미터에는 RANDOM 상수값을 전달해 준다. 범위는 캐시를 적재하고 싶은 만큼 -- 3) 1,2,3,4,5를 상수로 전달해준다면 5개의 캐시가 적재되기때문에 부하가 5곳으로 분산되는 효과를 볼 수 있다. SELECT * /* :p2 */FROM A WHERE B=:p1

---

⭐마무리

• 웹개발을 하면서 인프라영역에 예상치 못한 문제들을 마주할 때가 있는데, 이부분이 많이 약해서 배워나가고 있는 단계인데 참 생각할게 많은 것 같다. 다만 동료들이 있어 아주 든든하다 😁

---

⭐참고

• cursor_sharing 파라메터를 변경으로 바인드변수 처럼 경합 줄이기
• 자식커서로 인한 높은 파싱율 - 뮤텍스 대기를 초래하는 자식 커서 문제 해결.\
• [오라클] Library Cache 튜닝 방법