보미네

요즘 어떤분 때문에 인문학에 좀 관심이 있다.

근대 인문학은 르네상스에서 기인했다고 알려져 있다. 흔히 ‘르네상스’ 하면 무슨 호텔 이름처럼 풍요롭고 화려한 이미지만을 떠올리지만, 사실 그 시대의 인문학자들이야말로 중세적 신의 질서, 억압적 권력, 무지의 관성에 맞서 목숨을 내놓고 싸웠던 지적 전사들이었다. 대중의 입맛에 맞도록 쉽게 요약해주거나, 자본가들 상대로 호텔에서 강의를 하거나, 대통령에게 괴테를 인용해 찬사나 보내는 따위의 행위는 ‘인간의 무늬’(人文)를 탐구하는 데 따르는 지난하고 복합적이며 때로는 답이 보이지도 않는 인문학적 작업과는 관계가 없으며, 그저 소비사회의 천박한 요청에 부응해 신속하게 상품화된 지식일 뿐이다. 외려 진정한 인문학은 이 시대의 가장 불편한 문제를 제기하는 일, 가장 인기 없는 학문을 묵묵히 계속하는 일, 가장 주변부의 사람들과 연대하는 일 속에 있다. 이 시대는 ‘인간’의 가치가 헐값으로 떨어진 총체적 야만의 시대이기에 그렇다. 이런 암울하고 절박한 시대를 쉽고 실용적이고 희망찬 말들로 포장하여 팔아치우는 오늘의 ‘인문학’, 그것이야말로 실은 가장 먼저 처리되어야 할 쓰레기다.

문강형준 문화평론가

REMARK 2 START

SQL 2005 부터는 allocation contention 발생을 줄이기 위해 저장프로시저 내 임시테이블이 캐시된다. 이런 캐시 영향도가 전체 프로시저의 실행계획에 영향을 끼칠 수 있다. 이 경우 Ramesh Meyyappan 이 여러가지 솔루션을 제안했다. (이사람도 지금은 컨설턴트라서 좋은 솔루션은 절대 안가르쳐 준다. workshop 수업 들어야 알려줄것 같다. ) 좀 더 효율적인 솔루션이 없을까? 가 문제이다.

Ramesh Meyyappan 이 제안한 방법은 3가지 정도가 된다. (별로 영양가가 없으므로, 자세히 하지 않겠다. )

1. option recompile 로는 임시테이블 통계가 바뀌지 않기 때문에 문제가 해결되지 않는다.
2. 템프 테이블에 명명된 제약조건 생성(동시에 실행하면, 이름충돌 문제있음)
3. 임시테이블에 인덱스를 만드는 방법

CREATE DATABASE EPLAN 
GO

USE EPlan
go

set nocount on
set statistics time off
set statistics io off

drop table tab7
go

create table tab7 (
	c1 int primary key clustered
,	c2 int
,	c3 char(200)
)
go

create index test on tab7(c2, c1, c3)
go

/*
-- 주의) 실행계획 표시 해제할 것
*/
begin tran
	declare @i int
	set @i = 1
	while @i <= 50000
	begin
	insert into tab7 values (@i, 1, 'a') -- *) c2는 모두 1
	set @i = @i + 1
	end
commit tran
go
insert into tab7 values (50001, 1, 'a')
go

checkpoint
go


/*
1) 원본
*/
DROP PROC test_slow
go

CREATE PROC test_slow 
	@i int
AS
BEGIN
	DECLARE @j int
	
	CREATE TABLE #temp1 (c1 int primary key)

	INSERT INTO #temp1 (c1) SELECT @i
	
	SELECT @j = t7.c1 
	FROM tab7 t7 
	INNER JOIN #temp1 t 
		ON (t7.c2 = t.c1)		
        
END
go
	

위 쿼리는 인자를 1로 주면 SCAN, 인자를 2로 줘도 SCAN 이다. 이것을 1이 인자로 들어오면 SCAN 2가 인자로 들어오면 SEEK 가 되도록 고쳐야 하고, 전체 프로시저를 리컴파일(플랜이 캐시되지 않는다.) 하지는 않아야 한다. 아래 스크립트를 실행하면, 캐시하여 둘 다 SCAN 한다.

SCAN

exec test_slow 1

exec test_slow 2

DROP PROC test_slow
go

CREATE PROC test_slow 
	@i int
AS
BEGIN
	DECLARE @j int
	
	CREATE TABLE #temp1 (c1 int primary key)

	INSERT INTO #temp1 (c1) SELECT @i
	 
	update statistics #temp1

	SELECT @j = t7.c1 
	FROM tab7 t7 
	INNER JOIN #temp1 t 
		ON (t7.c2 = t.c1)
	option (recompile)

END
go
	

위 방법은 전체 프로시저를 recompile 하지 않았고 필요한 부분만 정확하게 재컴파일 할 수 있는 방법이다. update statistics 와 option (recompile) 은 같이 쓰여져야 한다.

이 글은 두 사람보다 훨씬 심도깊은 테스트와 의견을 제시한 사람이 있다. 다음 두개의 글을 읽어보길 바란다.

http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx

http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx

위 두 글 뿐만 아니라 PAUL 의 글은 매우 수준이 높고 SQL을 이해하는데 도움이 많이 된다. 이글을 소개한 강사는 솔루션을 계속 찾고있다고 한다. 인터넷에 위 글을 참고하면 좋을듯 하다.

REMARK 2 END

REMARK 1 START

USE TEMPDB
GO

IF OBJECT_ID('TEST') IS NOT NULL
       DROP TABLE TEST
GO

CREATE TABLE TEST(
   ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,  T INT NOT NULL
,  DUMMY CHAR(1000) NOT NULL DEFAULT('A')
)
GO

SET NOCOUNT ON
GO


DECLARE @I INT = 1
WHILE @I < 100000 -- 이 숫자만 효과를 극대화 하기 위해 크게 만들었음
BEGIN
       INSERT INTO TEST(T) VALUES((@I%3) *2 )
       SELECT @I = @I+1
END
GO

CREATE INDEX T ON TEST(T)
GO

아래 쿼리는 SEEK 할까? SCAN 할까?

 
SELECT TOP(1) * FROM TEST WHERE T = 4 
-- 위 쿼리가 왜 SEEK 를 하지 않고 SCAN 하는가? 가 질문이다.

테이블 'TEST'. 검색 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

그런데, 위와같은 경우는 SCAN 이 실제로 유리하다. 왜냐하면, HISTOGRAM 에서 4가 존재할 확율을 구하게 되는데 4는 통계를 보면 다음과 같다.

전체 행수가 99999 에서 33333 개가 있다고 나와 있다. 그러니 첫번째 PAGE 에서 4를 만날 확율이 높을수 있다. 그러니 SQL 은 SEEK 하지 않고 SCAN 하게 된다. 행수가 제한되는것은 HISTOGRAM 에 있는 행의 수와 TOP 의 두 조건이 모두 동작한다.  자 이번에는 SEEK 하도록 해보겠다.

쿼리의 인자를 다음과 같이 바꾸어 보자

 
SELECT TOP(1) * FROM TEST WHERE T = 5

잘 SEEK 한것을 볼 수 있다. 왜냐하면, 전체 행수에서 5가 있을 확률은 없기 때문이다. 이 경우 SCAN 으로 찾을 확율은 전체 테이블을 모두 다 읽어야 찾을 수 있기 때문에 SEEK 해서 찾는다. 자 이번에는 통계를 보고 판단해도 비 합리적으로 동작하도록 해보겠다. 통계는 행의 순서정보를 압축을 통해 볼 수 없게되므로, 순서정보를 다르게 넣으면 SQL 통계기반 판단에서 더 비 효율적인 선택을 할 수 있는 기회가 커지게 된다. 만약 다음과 같은 데이터를 준비 한다면 통계를 이용한 옵티마이징의 문제를 좀 더 쉽게 모니터링 할 수 있다.

HISTOGRAM 은 같으나 문제가 될 수 있는 데이터 생성

 

SET STATISTICS IO OFF
SET NOCOUNT ON
GO
 
USE TEMPDB
GO
IF OBJECT_ID('TEST') IS NOT NULL
       DROP TABLE TEST
GO
CREATE TABLE TEST(
   ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,  T INT NOT NULL
,  DUMMY CHAR(1000) NOT NULL DEFAULT('A')
)
GO
INSERT INTO TEST (T) VALUES (0);
GO 33333
INSERT INTO TEST (T) VALUES (2);
GO 33333
INSERT INTO TEST (T) VALUES (4);
GO 33333
 
CREATE INDEX T ON TEST(T)
GO
SET STATISTICS IO ON
GO
-- 아까와 동일 쿼리 실행
SELECT TOP(1) *  FROM TEST WHERE T = 4

테이블 'TEST'. 검색 수 1, 논리적 읽기 수 9578, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

위 데이터도 역시 통계 데이터는 같다 99999 행 중33333 개가 있지만 024024024... 로 들어 있을지 000...222...444... 식으로 데이터가 들어 있는지 통계에서 알 수 없다. 이때는 앞에 있는것 처럼 잘 혼합되어 있다고 생각하는것 같다. 그러나, 데이터를 임으로 2번째 경우와 같이 넣게되면 SQL SERVER의 옵티마이저 기준에서는 효율적이다. 라고 선택한 SCAN 이 더 비효율적인 경우가 된다. 이때는 아래와 같이 힌트를 명시하는것이 좋다.

인덱스 힌트를 사용한 쿼리

 

SELECT TOP(1) * FROM TEST WITH(INDEX(T)) WHERE T = 4

테이블 'TEST'. 검색 수 1, 논리적 읽기 수 7, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

이렇게 설명하는게 더 좋지 않았을까 싶다. INDEX SEEK 가 좋다 SCAN 이 좋다. 가 아니라 통계의 한계와 데이터의 이해가 선행되어야 하지 않을까 싶다. 위 설명이 아마 강사가 설명중 "난 SEEK 하는게 더 빠른 경우를 본적 있어요 SEEK 하도록 해주세요" 라고 설문에 답한 시나리오의 재현이 아닐까 생각한다. 사실 이런 부분이 비즈니스에서 발견되면 일반적인 통계기반 옵티마이저 보다 데이터를 잘 알고 있는 인간이 힌트를 통해 성능을 고정시켜야 한다. 힌트가 필요한가? 필요하지 않은가? 만약 플랜이 캐시라도 된다면...

그런데, 강사가 이 부분을 HISTOGRAM 설명없이 SCAN SEEK 를 설명하고 있는데, 상당히 혼란스러웠다. 이걸 어떻게 HISTOGRAM 설명없이 설명 할 수 있는지 아직도 이해가 가지 않고 혹시 위 현상을 HISTOGRAM 없이 설명할 수 있다거나, 오류내용이 있다면 언제든지 누구든지 알려주길 바란다. 강의자는 실행계획상 SCAN 과 SEEK 의 비용을 비교해 보고 SCAN 이 싸기 때문에 비용기반 옵티마이저가 SCAN 한다라고 설명하고 있다. 여기서 왜 비용이 쌀까? 에 주목해야 한다. 통계에 중복도가 상당히 높기 때문에 SCAN으로 읽을때 첫 번째 페이지나 두 번째 페이지에서 원하는 4를 만날 확율이 매우 높다. SEEK 의 경우도 B-TREE 의 두 번째 페이지 + LOOKUP 의 두 번째 페이지 정도에서 데이터를 찾을 수 있으나, SEEK 연산자의 기본비용과 LOOKUP 의 기본기비용을 더하면 단 한단계에서 처리하는 SCAN 보다 비용이 비싸게 된다.

이것이 5를 넣어 비용을 비교하고 이것이 통계의 영향인지 TOP 의 영향인지를 설명한 의도이다. SEEK 와 SCAN 의 기본 이터레이터 비용 계산은 UnPlugged Level 400 병렬처리 세션을 보기 바란다. (발표 : 김민석)

REMARK 1 END