oracle bind 변수 사용한 sql 로그 및 bind 변수 보기 위한 방법

오라클에서 사용한 쿼리를 보는 것은 아래 쿼리를 사용해서 확인 하면되요 

select * from v$sql

해당 쿼리에서 쿼리 LOAD 시간등 여러가지 정보를 사용하면 됩니다. 

하지만 쿼리중 bind 변수 를 사용한 쿼리는 LOAD Time 을 사용해서 프로그램에서 어떠한 쿼리를 사용했는지 추적하기 어렵습니다. 

일단 기본적으로 오라클에서 쿼리가 실행 되면 

가.SQL 파싱(Parsing)

1. SQL을 실행하면 제일먼저 SQL 파서(parser)가 SQL 문장에 문법적 오류가 없는지 검사(Syntax 검사)
2. 문법적 오류가 없다면 의미상 오류가 없는지 검사(Semantic 검사, 오브젝트 존재유무등)
3. 검사를 다 마치면, 사용자가 발생한 SQL과 그 실행계획이 라이브러리캐시(프로시저캐시)에 캐싱되어 있는지 확인
4. 캐싱되어 있다면 소프트파싱, 캐싱되어있지 않다면 하드파싱

파싱종류	설명
소프트파싱 (Soft Parsing)	SQL과 실행계획을 캐시에서 찾아 곧바로 실행단계로 넘어가는 경우
하드파싱(Hard Parsing)	SQL과 실행계획을 캐시에서 찾지 못해 최적화 과정을 거치고 나서 실행단계로 넘어가는 경우

• 라이브러리캐시는 해시 구조로 관리됨
  • SQL마다 해시값에 따라 여러 해시 버킷으로 나뉘며 저장되고, SQL을 찾을때는 SQL 문장을 해시 함수에 적용하여 반환되는 해시값을 이용하셔 해시 버킷을 탐색함.

나.최적화(Optimization)

SQL 최적화를 담당하는 옵티마이저는 사용자가 요청한 SQL을 가장 빠르고 효율적으로 수행할 최적의(처리비용) 처리경로를 선택해 주는 DBMS의 핵심

• 최적화 과정
  -예를들어 5개의 테이블을 조인한다면,순서만 고려해도 5!(=120)개의 실행계획 평가
  -120가지의 실행계획에 포함된 각 단계별 다양한 조인방식 고려
  -테이블을 full scan 할지 인덱스를 사용할지, 어떤 인덱스를 어떤방식으로 스캔할지 고려
  => 이와 같이 무거운 작업이므로 이러한 힘든과정을 거쳐 최적화된 SQL 실행계획을 한번만 쓰고 버린다면 엄청난 비효율이 발생한다.
  => 파싱과정을 거친 SQL과 실행계획이 여러 사용자가 공유해서 재사용 할수 있도록 공유메모리에 캐싱는 이유가 여기에 있다.

2.캐싱된 SQL 공유

가.실행계획 공유 조건

• SQL 수행절차

1. 문법적 오류와 의미상 오류가 없는지 검사
2. 해시 함수로부터 반환받은 해시 값으로 라이브러리 캐시 내 해시버킷 탐색
3. 찾아간 해시버킷에 체인으로 연결된 엔트리를 차례로 스캔하면서 같은 SQL 문장 탐색
4. SQL문장을 찾으면 함께 저장된 실행계획을 가지고 바로 실행
5. 찾아간 해시 버킷에서 SQL 문장을 찾지 못하면 최적화를 수행
6. 최적화를 거친 SQL과 실행계획을 방금 탐색한 해시 버킷 체인에 연결
7. 방금 최적화한 실행계획을 가지고 실행

위와 같이 파싱 >>  최적화 를 하고 나서 bind 변수를 사용 하면 최초 한번 LOAD 할때 빼고는 로그에 남지 않게 됩니다. 

최초 한번 LOAD 가 하드 파싱이라고 부르게 됩니다. 

다음 번 부터는 소프트 파싱이죠. 

하지만 프로그램에서 사용한 쿼리 로그 쉽게 찾기 위해서 

하드 파싱을 유도 해야 합니다. 

alter system flush shared_pool;
alter system flush buffer_cache;


출처: http://artistofdb.tistory.com/entry/sharedpool-비우기 [Go Away! IF You Don't Love.]

위에 쿼리를 사용해 주면 모든 쿼리가 하드 파싱을 하게 되니 v$sql 에 추적 하기쉽게 시간등을 알수 있습니다. 

그리고 사용한 bind 변수를 보려면 

 v$sql_bind_capture  테이블을 사용 하면 됩니다. 

그뒤에 join 은 v$session,v$sql  등등을 사용 하며 됩니다. 

그리고 중요한것은 sesion.sql_address,sesion.sql_hash_value 를 join 조건으로 사용 하면 된다는것입니다, 

select sesion.sid,
       sesion.username,
       sesion.sql_id,
       sesion.sql_child_number,
       sql_bind_capture.name,
       sql_bind_capture.value_string
  from v$sql_bind_capture sql_bind_capture, v$session sesion
 where sesion.sql_hash_value = sql_bind_capture.hash_value
   and sesion.sql_address    = sql_bind_capture.address
   and sesion.username is not null ;

출처: http://smok95.tistory.com/145 [Only YOUng]