70세 코딩

SP는 최초(처음) 실행 하는 시점에 실행계획을 수립하고 이후에는 실행계획을 계속 재사용 하게 됩니다

(이것이 SP를 사용하는 중요한 이유 중 하나 입니다).

이 최초 실행계획 수립시, 인수로 넘겨진 파라미터(매개변수)를 기준으로 통계를 통해 예상 행수를 추측하게 됩니다.

그런데 넘겨진 파라미터가 아닌 로컬 변수를 이용해서 쿼리를 실행하면 옵티마이저가 행수 추측이 실패하게 되어

원하지 않는 실행 계획이 생성 될 수 있습니다.

이로 인하여 SP 성능이 저하 될 수 있습니다.

예를 들어 다음과 같이 SP 가 되어 있을 때,

create proc Proc1 (
@p_date varchar(8)
)
as
SELECT
   *
FROM 
   SDSaleMaster a
WHERE
   a.SaleDate = @p_date

SP를 호출 하면,

EXEC Proc1 @p_date = '20190601'

실행계획에서 행수를 1326으로 예측 합니다.

이는 통계와 일치하는 제대로 된 예상 행 수 이기 때문에, 실행계획이 적합하게 나올 가능성이 높습니다.

그런데 다음과 같이 매개변수가 아닌 로컬 변수를 사용 하게 되어 있을 때,

create proc proc1 (
@p_date varchar(8)
)
as
declare
   @v_date varchar(8)

set @v_date = convert(varchar(8), dateadd(d, -1, @p_date), 112)  -- 하루 전

SELECT
   *
FROM 
   SDSaleMaster a
WHERE
   a.SaleDate = @v_date

SP를 호출 하면,

 EXEC Proc1 @p_date = '20190602'

옵티마이저가 예상 행수를 제대로 추정할 수가 없어서 549로 나오는 데, 이는 해당 테이블의 평균 밀도값을 이용한 것 입니다.

평균밀도

예상 행수 = 밀도 * 테이블 전체 row 수

이 글이 도움이 되셨으면 좋아요 눌러 주시거나 댓글을 남겨 주세요.

윈도우 환경에서 Oracle Client 설치를 해도 ADO를 이용한 접속이 제대로 안되는 경우가 있다.

TNS, 포트 등 기본적인 설정상의 문제가 아니라면 다음을 확인 해보자.

1) OraOLEDB.Oracle 경우

접속 문자열에서 Provider를 'OraOLEDB.Oracle' 로 지정한 경우 시스템에 'Oracle Provider for OLE DB' 공급자가 정상적으로 등록되어 있어야 한다. 

등록여부는 다음과 같이 확인 할 수 있다.

1. 확장자 'UDL'로 임의의 파일을 생성 한다.

2. 해당 파일의 속성 창에서 '공급자' 페이지를 확인 한다.  

만약 위와 같이 'Oracle Provider for OLE DB' 가 나타나지 않으면 Oracle 사이트에서 ODAC를 받아서 설치를 한다. 

(http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html) 

그런데 기존에 Oracle client 설치시 함께 설치를 했는데도 나오지 않는 경우에는 regsvr32 명령으로 OraOLEDB11.dll(11g 기준)을 레지스트리에 다시 등록을 해 본다.

만약 ODAC를 기존 Oracle client와 다른 별도의 경로에 설치 한 경우, network\admin\tnsnames.ora 파일에 TNS를 등록해야 한다.
그리고 32비트, 64비트용이 각각 존재 하므로 만약 64비트 윈도우즈 환경에서 32비트 프로그램이 Oracle 접속이 되지 않는다면
32비트 ODAC의 설치가 필요 하다.

참고로 공급자에 제대로 등록이 되어 있지 않은 상태에서 Oracle 접속을 시도할 경우 경우 다음과 같은 오류가 발생 한다. 

2) MSDAORA.1 경우

접속 문자열에서 Provider를 'MSDAORA.1'로 지정한 경우 시스템에 'Microsoft OLE DB Provider for Oracle' 공급자가 정상적으로 등록되어 있어야 한다. 위와 동일한 방법으로 확인 할 수 있다. 

그런데 MSDAORA는 32비트에서만 제공된다. 64비트에서는 더 이상 지원이 안되기 때문에 사용할 수가 없다.

처음에 이것을 몰라서 엄청 애를 먹었다. 꼭 Provider를 MSDAORA를 사용 해야 한다면 어플리케이션을 32비트로 컴파일 해서 사용하고 그렇지 않으면 OraOLEDB.Oracle 를 대신 사용해야 한다.

참고로 공급자에 제대로 등록이 되어 있지 않은 상태에서 Oracle 접속을 시도할 경우 경우 다음과 같은 오류가 발생 한다. 
어떤 경우에 아래와 같이 각각 다른 형태의 메시지가 나타나는 지는 알 수가 없었다. 

이 글이 도움이 되셨으면 댓글을 남겨 주세요!