Section4. Shared Pool 개요와 SQL 공유 방안

[출처: 오라클 성능 분석과 인스턴스 튜닝 핵심 가이드]

Shared Pool 구조

Shared Pool	Library Cache
	Shared SQL Area		SQL 또는 PL/SQL 코드를 모두 보관하는 장소.  사용 된 SQL 및 실행계획이 파싱 되어 보관 됨.
	Data Dictionary Cache	테이블/인덱스 등의 Object 정의, 사용자명, Role, 권한 등의 정보를 보관하는 장소. 주로 사용자가 해당 Obejct에 접근 가능 권한이 있는지 확인하는데 사용.
	Result Cache	사용자 SQL의 결과 값을 지속적으로 보관. 동일한 SQL이 요청될 경우 Buffer 나 Disk I/O Access 작업을 수행하지 않고 Result Cache에 있는 값을 그대로 반환.
	Reserved Pool	매우 큰 PL/SQL 패키지와 같은 대용량 SQL 관련 오브젝트들을 저장하기 위한 별도 공간.

 

SQL Bind 변수 사용의 필요성

• Literal SQL
  • Ex> Select * from Customer Where cust_id = '100000'
           Select * from Customer Where cust_id = '200000' 
           Select * from Customer Where cust_id = '300000' 
  • Optimizer는 SQL 의 문자 하나만 차이가 생겨도 서로 다른 SQL로 인식
  • 3개 SQL은 모두 서로 다른 SQL로 인식되며 첫 번째 호출 된 SQL에서 생성 된 실행계획은 다른 SQL과 서로 공유 할 수 없으므로 모두 Hard Parsing 수행
  • 위와 같은 Literal SQL 형식으로 Application 이 작성되고 많은 동시 접속 사용자에 의해 사용된다면 Hard Parsing으로 인한 시스템 부하가 급증 가능성 존재
• Bind 변수 SQL
  • Ex> Select * from Customer Where cust_id = '100000'
           Select * from Customer Where cust_id = '200000' 
           Select * from Customer Where cust_id = '300000' 
                        = Bind 변수
  • SQL에 Bind 변수를 사용하면 Optimizer에서 실행 계획 생성이 완료된 후에 변수값을 후에 호출하여 SQL을 수행하곘다는 거임.
  • 따라서 위 3개 SQL은 모두 같은 SQL로 인식하고 첫번째 호출 된 SQL만 Hard Parsing 되고 이후에 호출되는 모든 SQL은 모두 Soft Parsing 됨.
  • OLTP에서 사용되는 거의 모든 SQL들은 반드시 Bind변수 형식으로 작성 되어야 함.
  • JDBC를 사용하는 경우 PreparedStatement 객체로 구현 가능.

SQL 공유 강화를 위한 초기화 파라미터 설정

• 이미 Bind Variable을 사용하지 않은 수많은 SQL들을 Bind 변수를 사용하도록 SQL을 수정하는 것은 많은 노력과 테스트가 필요함.
• ALTER SYSTEM SET CURSOR_SHARING = 'FORCE';
  • 비슷한 SQL들을 Library Cache에 공유하기 위해서 Optimizer가 동일한 SQL로 인지할 수 있는 기능이 필요.
  • FORCE를 사용하면 아래 3개 SQL은 Bind 변수를 사용 한 것과 유사하게 SQL을 공유하게 되고, 동일한 실행계획을 가짐,
  • Ex> Select * from Customer Where cust_id = '100000'
           Select * from Customer Where cust_id = '200000' 
           Select * from Customer Where cust_id = '300000'
  • Oracle 9iR2 이전 버전에서는 버그가 발생 할 수 있으므로 이후부터 적용 필요,
• ALTER SYSTEM SET CURSOR_SHARING = 'EXACT';
  • SQL이 완벽하게 동일해야 SQL 공유