태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

샘플링 vs. 이벤트

오라클 2009.10.06 14:48
아래와 같은 질문이 있습니다.
특정 세션이 실행한 모든 SQL의 이력을 추적할 수 있는 방법은 없는가?
대답은 간단합니다.
SQL Trace(10046 Event)를 수행하면 된다
하지만 이 간단한 질문과 대답 속에 성능 데이터 수집에 대한 심오한 철학이 있습니다.

직접 성능 데이터를 수집하고 분석해 보고자 하는 욕심을 내 본 분이라면 자기도 모르게 반드시 다음 두 가지 방법을 가지고 고민하게 됩니다. 이것들은 오라클과 무관하게 데이터를 수집하는 데 있어서 어디나 적용되는 보편적인 두 가지 기법입니다.

  • 이벤트(Event) 방식: 10046 이벤트가 대표적인 방법입니다. 즉 특정 이벤트가 발생할 때 그것을 받아서 성능 데이터를 수집하는 방식입니다.
  • 샘플링(Sampling) 방식: Maxgauge나 ASH가 대표적인 방법입니다. 초당 몇 번씩, 또는 분당 몇 번씩 원하는 데이터를 직접 캡쳐하는 방식입니다.
이벤트 방식은 오라클이 내부적으로 미리 정의해 놓은 이벤트들에 대해서만 가능합니다. 가령 10046(SQL 실행), 10053(Optimization 실행), 10032(Sort 실행) 등 오라클이 정해놓은 범위안에서만 사용 가능합니다. 이벤트 방식의 장점은 샘플링 방식과 달리 수집의 오류가 없다는 것입니다. 즉 원하는 모든 데이터를 수집할 수 있습니다.

샘플링 방식은 우리가 원하는 어떤 데이터든 우리가 원하는 주기로 수집할 수 있다는 장점이 있습니다. 오라클에서라면 Dynamic Performance View들이 주 대상이 됩니다. 단, 수집 주기에 따라 수집의 오류가 매우 클 수 있다는 단점이 있습니다.

아래의 질문을 이벤트 방식이 아닌 샘플링 방식으로 구현할 수 있을까요?

특정 세션이 실행한 모든 SQL의 이력을 추적할 수 있는 방법은 없는가?
1. 다음과 같이 Session A(SID=159)에서 총 10,000개의 SQL 문장을 연속적으로 수행합니다.
declare
	v_cursor	sys_refcursor;
	v_value		number;
begin
	for r in 1 .. 10000 loop
		open v_cursor for 'select /*+ case_' || r || ' */ 1 from dual';
		fetch v_cursor into v_value;
		close v_cursor;
	end loop;
end;
/
2. Session B에서 총 4,000,000번의 샘플링을 CPU를 100% 활용해서 수행하면서 X$KSUSE(V$SESSION의 베이스 테이블) 테이블로부터 SQL을 추적합니다.(힌트를 이용해 샘플링이 가능한 실행 계획을 제어하는데 유의하세요)
select * from 
(
	select
		sql_id,
		(select substr(t.sql_text,1,25) from v$sqlarea t 
					where t.sql_id = a.sql_id) as sql_text,
		hitcnt
	from (
		select /*+ no_merge */
			a.sql_id,
			count(*) as hitcnt
		from (
			select /*+ ordered use_nl(x s) */
					s.ksusepsi as sql_id
			from
				(select /*+ no_merge */ level as r from dual connect by level <= 4000000) x,
				sys.xm$ksuse s
			where s.indx = 154
		) a
		group by a.sql_id
	) a
) where sql_text is not null
;
3. 아래에 그 결과가 있습니다.
...
7hbnjb7uhrs2n select /*+ case_8766 */ 1                                 144
6150v08vhwyzs select /*+ case_8767 */ 1                                 169
7pxf03wwjqzf3 select /*+ case_8773 */ 1                                 349
ggq5ruaj6b9zf select /*+ case_8781 */ 1                                 343
abg6fj0kntu55 select /*+ case_8782 */ 1                                 304
6akym930shhgn select /*+ case_8786 */ 1                                 163
044rsut971xtb select /*+ case_8792 */ 1                                1802
8mjjurfg59y40 select /*+ case_8798 */ 1                                 142
4q3vgvnv2chn9 select /*+ case_8822 */ 1                                 172
34ddvz7nc4mrv select /*+ case_8836 */ 1                                1385
58zhgmbwa3r6z select /*+ case_8860 */ 1                                1410
g3xjqmh2fmp18 select /*+ case_8919 */ 1                                 185
7fjm8yu32sfyv select /*+ case_8929 */ 1                                 233
6wkawk6brmx41 select /*+ case_8283 */ 1                                 197
4zp1k8mr17t70 select /*+ case_8288 */ 1                                1040
5pha5zp0catjt select /*+ case_8289 */ 1                                 303
dswcqaxvy7pr9 select /*+ case_8307 */ 1                                 227
39fjqykryfvn8 select /*+ case_8315 */ 1                                 184
3xk1sjqdd49td select /*+ case_8321 */ 1                                 123
...
이론적으로는 하나의 SQL이 4,000,000/10,000 = 400 번 정도 샘플링이 이루어지는 것이 이상적입니다. 하지만 실제 샘플링 회수는 대단히 편차가 크고 오류를 가지고 있습니다.

이런 오차는 샘플링 방식에서는 어쩔 수 없는 것입니다. 가령 ASH(Active Session History)는 1초에 한번씩 Active Session을 수집합니다. 1초라는 단위는 Session을 수집하는데는 충분하지만, SQL 이력을 수집하기에는 너무나 큰 값입니다. ASH의 샘플링 정확성은 _ash_sampling_interval, _ash_size, _ash_sample_all 등의 파라미터로 조정 가능하지만 본질적인 오류는 피할 수 없습니다.

저희 회사에서 제공하는 Maxgauge라는 툴은 보통 1초에 10번~100번 정도 Active Session들의 SQL 문장을 샘플링 기법으로 수집합니다. 이 정도면 대단히 놀라운 수치같지만, 실제 시스템에서 운영해보면 생각보다 매우 큰 오류가 생깁니다. 위의 예에서 CPU를 100% 점유하면서 샘플링을 해도 오류가 크다는 사실을 감안하면 충분히 예상 가능한 일이긴 하지만요.

자신만의 방법으로 성능 데이터를 수집하고 분석하고자 하는 욕심을 가진 분들이라면 샘플링 vs. 이벤트라는 이 명제에 항상 유의하시기 바랍니다.

저작자 표시
신고

'오라클' 카테고리의 다른 글

왜 V$ 뷰보다 X$ 테이블을 더 좋아할 수 밖에 없는가?  (1) 2009.10.08
오라클을 잘 한다는 것  (5) 2009.10.07
샘플링 vs. 이벤트  (0) 2009.10.06
오라클 가계부 쓰고 있습니까?  (0) 2009.10.05
Extended Describe  (6) 2009.10.01
Trackback 0 : Comment 0

Write a comment

티스토리 툴바