보미네

 
1. http://blog.daum.net/partsofmemory/5 (참고로, Tistory의 저 아저씨는 SQL Server 기술지원 엔지니어 였었다. )
   위 아티크를 참고로 sqlclient 를 설치한다.
   sqlplus 도 같이 설치한다.

2. ACL 을 확인한다.
   - Network ACL
   - SQL ACL

3. sqlplus without tnsname 으로 connection TEST 를 진행한다.
   sqlplus 니가받은아이디/니가받은패스워드@//대상서버아이피:포트/서비스네임
   예)sqlplus id/pass@//10.0.0.1:1234/a.minsouk.com
   이렇게 해서 안되면 다른 사용중인 클라이언트에서 id pass 가 정확한지 확인한다.

4. tnsname.ora 를 셋팅해서 좀더 편하게 접근하기
   대충 다음과 비슷한 폴더를 찾는다.
   C:\Oracle\xxxxx\product\11.2.0\client_1\network\admin
   여기서 admin 폴더가 없으면 만들어서 tnsname.ora 를 만들어 넣는다.

5. tnsname.ora 의 예제
aliasname =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.1)(PORT = 1234))
)
(CONNECT_DATA =
(SERVICE_NAME = a.minsouk.com)
)
)

6. 나머지는 열심히 구글링한다.
   http://support.microsoft.com/kb/280106/ko

7. 나는 서버 담당자가 service_name 을 이상하게 알려줘서 고생했다. (a.minsouk.com 에서 a 만 알려줌 -_- 뒤는 다 같으니 생략하고 알려줌)

agent 시작계정이 nt service 에 sqlserveragent 뭐 이런식으로 되어 있으면

공급자에 in processes 설정을 해주던지 localsystem 으로 계정을 바꾸어 주어야 한다.

dllHost.exe 의 실행권한이 없어 에러가 나는듯

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx

Hash Aggregate

나의 이전 두 글에서, stream aggregate 연산자를 다루었다. Stream aggregate 는 scalar aggregtates 와 group by 컬럼에 인덱스가 제공되거나 정렬이 필요한 aggregations 에 좋다. (예를들어, order by 절이 있는경우)

다른 또 하나의 aggregation 연산자는 hash aggregate 이다. 이것은 hash join과 비슷하다. 이것은 정렬순서를 유지할 필요가 없고, 메모리가 필요하며, 블럭된다 (즉, 이것은 모든 입력행을 다 처리하기 전까지 어떠한 결과도 만들지 않는다.). Hash aggregate 는 매우 큰 입력셋에서 효과적이다.

Here is pseudo-code for the hash aggregate algorithm:(Hash aggregate 알고리즘을 위한 슈도 코드)

for each input row
  begin
    calculate hash value on group by column(s)
    check for a matching row in the hash table
    if we do not find a match
      insert a new row into the hash table
    else
      update the matching row with the input row
  end
output all rows in the hash table

stream aggregate 가 한번에 한개의 group 값을 계산하는것에 반해 hash aggregate 는 전체 그룹을 동시에 계산한다. 이러한 그룹을 저장하기위해 hash table 을 사용한다. 각각의 새로운 입력행에 대해 hash table 을 체크하고, 입력된 행이 기존 그룹이 있는지 체크한다. 존재하면 해당 그룹을 간단히 업데이트 한다. 존재하지 않으면 새로운 그룹을 만든다. 입력되는 행이 정렬되어 있지 않기 때문에 입력되는 모든 행은 어떠한 그룹에도 속할 수 있다. 그러므로, 입력된 모든 행의 처리가 끝나기 전에 어떠한 결과값도 출력할 수 없다.

Memory and spilling (메모리와 넘침)

hash join 처럼 hash aggregate 는 메모리를 필요로 한다. hash aggregate 를 실행하기 전에, SQL Server 는 cadinality 예측에 기반해 얼마나 많은 메모리가 쿼리 실행에 필요한지 예측하게 된다. hash join 에서 각각의 빌드 입력을 저장한다. 그러므로, 전체 필요한 메모리는 빌드 입력의 행수와 사이즈에 비례한다. join 의 출력 cadinality 와 join 되는 행수는 join 의 메모리 필요량과 상관 없다.

hash aggregate 에서, 각각의 그룹에 하나의 행을 입력한다. 그러므로, 전체 메모리 요구량은 출력 그룹의 수나 행수에 비례한다. 만약 unique 값이 몇개 없고 group 이 몇개 되지 않는다면 메모리를 작게쓰고, 많은 unique 값이 있고, 그룹 컬럼의 개수가 많으면, 많은 메모리를 필요로 한다.

그래서, 메모리가 부족하면 어떤일이 일어날까? hash join 에서 메모리가 부족하면, tempdb 를 사용하게 된다. 부분적으로 aggregated 된 결과를 포함해 하나 이상의 bucket 이나 파티션을 디스크로 넘긴다. 이러한 행에 대해 aggregate 를 다시 시도하지 않지만, 이것들을 다시 hash 해 여러 bucket 이나 파티션으로 나누어야 한다. 모든 입력 그룹에 대해 처리가 끝나면, 메모리 내 그룹의 결과를 출력한다. 그리고, 메모리에서 넘친 부분을 다시 읽어 알고리즘을 반복 실행한다. 메모리에서 넘친 행이 여러 파티션으로 나누어지면, 각각의 파티션 수를 작게해 이러한 알고리즘이 여러번 반복되는 비효율을 줄인다.

주의할 점은, 중복 행이 hash join 에서는 큰 문제이다. 이것은 hash bucket 의 사이즈를 다르게 해 데이터 스큐(skew)를 유발하고, 크기가 고정된 작은 부분으로 나누기 어렵게 한다. 그러나 이러한 중복 행이 hash aggregation 에서는 유리하다. 왜냐하면, 하나의 그룹으로 축약되기 때문이다.

Example (예제)

옵티마이저는 테이블이 많은행과 많은 그룹을 가지면 hash aggregation 을 선호한다. 예를들어 100개의 행과 10개 그룹이 있으면 stream aggregate 를 한다.

create table t (a int, b int, c int)
 

set nocount on
declare @i int
set @i = 0
while @i < 100
  begin
    insert t values (@i % 10, @i, @i * 3)
    set @i = @i + 1
  end

select sum(b) from t group by a
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1010]=COUNT_BIG([t].[b]), [Expr1011]=SUM([t].[b])))
            |--Sort(ORDER BY:([t].[a] ASC))
                 |--Table Scan(OBJECT:([t]))

그러나, 1000개의 행과 100개의 그룹이 있으면 hash aggregate 를 한다.

truncate table t
 

declare @i int
set @i = 100
while @i < 1000
  begin
    insert t values (@i % 100, @i, @i * 3)
    set @i = @i + 1
  end
 

select sum(b) from t group by a
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Hash Match(Aggregate, HASH:([t].[a]), RESIDUAL:([t].[a] = [t].[a]) DEFINE:([Expr1010]=COUNT_BIG([t].[b]), [Expr1011]=SUM([t].[b])))
            |--Table Scan(OBJECT:([t]))

group by 컬럼을 hash 했다. 나머지 비교 연산자(residual predicate)는 hash aggreate 가 hash 값 충돌이 발생 할 때 입력행을 hash table 에서 비교 할 때 사용된다.

hash aggregate 는 정렬이 필요없는 것도 볼 수 있다. 정렬은 hash aggregate 보다 더 많은 메모리를 필요로 한다. 왜냐하면,정렬은 1000개의 행을 정렬해야 하고 hash aggregate 는 100 개의 그룹값만 가지면 되기 때문이다. 하지만, 쿼리에서 order by 를 명시적으로 요구하면, 다시 stream aggregate 를 하는 것을 볼 수 있다.

select sum(b) from t group by a order by a
  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1010]=COUNT_BIG([t].[b]), [Expr1011]=SUM([t].[b])))
            |--Sort(ORDER BY:([t].[a] ASC))
                 |--Table Scan(OBJECT:([t]))

만약, 테이블이 충분히 크고, 그룹의 수가 작으면, 옵티마이저는 hash aggregate 를 수행하고 결과를 다시 정렬하는 것이 비용 효율적이다 라고 판단한다. 예를 들자면, 10000 개의 행을 가지고 100개의 그룹을 가지면, 10000개의 행을 정렬하기보다 옵티마이저는 100개의 그룹을 hash 하고 정렬하게 된다.

truncate table t

set nocount on
declare @i int
set @i = 0
while @i < 10000
  begin
    insert t values (@i % 100, @i, @i * 3)
    set @i = @i + 1
  end

select sum(b) from t group by a order by a
  |--Sort(ORDER BY:([t].[a] ASC))
       |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
            |--Hash Match(Aggregate, HASH:([t].[a]), RESIDUAL:([t].[a] = [t].[a]) DEFINE:([Expr1010]=COUNT_BIG([t].[b]), [Expr1011]=SUM([t].[b])))
                 |--Table Scan(OBJECT:([t]))

Distinct

stream aggregate 와 유사하게 hash aggregate 가 distinct 연산에도 사용될 수 있다.

select distinct a from t
  |--Hash Match(Aggregate, HASH:([t].[a]), RESIDUAL:([t].[a] = [t].[a]))
       |--Table Scan(OBJECT:([t]))

좀 더 흥미있는 것은

select sum(distinct b), sum(distinct c) from t group by a
  |--Hash Match(Inner Join, HASH:([t].[a])=([t].[a]), RESIDUAL:([t].[a] = [t].[a]))
       |--Compute Scalar(DEFINE:([t].[a]=[t].[a]))
       |    |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1018]=(0) THEN NULL ELSE [Expr1019] END))
       |         |--Hash Match(Aggregate, HASH:([t].[a]), RESIDUAL:([t].[a] = [t].[a]) DEFINE:([Expr1018]=COUNT_BIG([t].[b]), [Expr1019]=SUM([t].[b])))
       |              |--Hash Match(Aggregate, HASH:([t].[a], [t].[b]), RESIDUAL:([t].[a] = [t].[a] AND [t].[b] = [t].[b]))
       |                   |--Table Scan(OBJECT:([t]))
       |--Compute Scalar(DEFINE:([t].[a]=[t].[a]))
            |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END))
                 |--Hash Match(Aggregate, HASH:([t].[a]), RESIDUAL:([t].[a] = [t].[a]) DEFINE:([Expr1020]=COUNT_BIG([t].[c]), [Expr1021]=SUM([t].[c])))
                      |--Hash Match(Aggregate, HASH:([t].[a], [t].[c]), RESIDUAL:([t].[a] = [t].[a] AND [t].[c] = [t].[c]))
                           |--Table Scan(OBJECT:([t]))

이 실행계획은 논리적으로 저번 글에서 본 stream aggregate 와 같다. 하지만 정렬과, stream aggregate 와 merge join 한 대신에 hash aggregate 와 hash join 을 사용했다. 두개의 hash aggregate 가 중복을 제거하고 (하나는 distinct b 다른 하나는 distinct c) 두개의 hash aggregate 를 사용해 두개의 sum 값을 구했다. hash join 은 두 결과를 붙여서 마지막 결과를 만들었다.

Hints

"order group" 과 "hash group" 쿼리 힌트를 기술해 stream aggregate 와 hash aggregate 를 강제화 할 수 있다. 이러한 힌트는 전체 쿼리에서 모든 aggregation 연산자에 영향을 준다.

select sum(b) from t group by a option(order group)
select sum(b) from t group by a option(hash group)

Sql profiler

SQL 프로파일러를 사용해 hash join 이나 hash aggregate 메모리 초과를 검출 할 수 있다. ("Error and Warnings" 이벤트 클레서에서) "Hash Warning" 이벤트. 메모리 초과는 I/O 를 유발하고 성능에 좋지않는 영향을 준다. BOL 에서 이 이벤트에 대한 더 많은 정보를 얻을 수 있다.

by Craig Freedman