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 수

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

MS SQL 서버에서는 Stored Procedure 내에 그냥 SELECT 문을 써서 rowset을 클라이언트로 보낼 수 있어서
Delphi 에서 간단히 TADOStoredProc를 사용해서 파라미터만 넘겨주면 되었는데,
Oracle 에서는 이것이 되지 않아서 인터넷 에서 여러 자료를 검색하고 테스트를 해 보았지만 잘 되지 않았다.

그러다 시행착오 끝에 결국 해결방법을 찾아서 정리를 해 본다.

1. 우선 SP는 패키지를 이용해서 다음과 같은 식으로 정의 한다.

create or replace package pkgname is
     TYPE tRefCur IS REF CURSOR;
     procedure procname (
	Cust IN VARCHAR2,
	rs1 out tRefCur);
end pkgname;

create or replace package Body pkgname is
   procedure procname (
	Cust IN VARCHAR2,
	rs1 out tRefCur)
   Is
   Begin	
     open rs1 for
     SELECT
	 CustNo          
	,CustName          
	,Owner                 
	,EmpId                                     
	,TelNo            
	,BizNo        
	,vacct_no              
     FROM
	CustMst
     WHERE
	CustName like Cust || '%';

   End procname;
end pkgname;

Provider=OraOLEDB.Oracle.1;Password=패스워드;Persist Security Info=True;
User ID=유저;Data Source=데이터소스;Extended Properties="plsqlrset=1"

 

3. 화면은 간단히 다음과 같이 만들었다.

   검색창에 조회조건을 넣고 Find 버튼을 누르면 SP 호출을 통해서 rowset을 가져와서 TDBGrid에 보여 준다.

4. Find 버튼에 대한 Event Procedure 에서 다음과 같이 한다.

procedure TForm1.btnFindClick(Sender: TObject);
begin
  with ADOStoredProc1 do begin
    Close;
    Parameters.Clear;
    Parameters.CreateParameter('Cust', ftString, pdInput, 20, null).Value := txtFind.Text;
    Open;
  end;
end;

또는 미리 TADOStoredProc의 Parameters 속성에서 INPUT 파라미터를 정의하고 다음과 같이 해도 된다.

procedure TForm1.btnFindClick(Sender: TObject);
begin
  with ADOStoredProc1 do begin
    Close;
    Parameters.ParamByName('Cust').Value := txtFind.Text;
    Open;
  end;
end;

이 글이 도움이 되셨으면 좋아요 눌러 주시거나 댓글을 남겨 주시면 감사 하겠습니다.