DATOR


튜닝-MSSQL OPENQUERY을 이용한 경우

MSSQL을 튜닝하다가 보면 다른 디비의 데이터를 UPDATE하는 경우 아래의 ODBC을 이용해서 처리 하는 경우가 많이 있다.

그러 인하여 아래의 문제점이 발생된다.

 

문제)

UPDATE GWKDP..INTRAWARE.GEAR_SANG_MASTER            
 SET RESULTFLAG = '9'                      
 WHERE SANG_NO in (SELECT SANG_NO FROM @SANGRESULT)
 

결과

 

                                                                                                                                                            

SQL Server 실행 시간:                                                                                                                                        
   CPU 시간 = 0ms, 경과 시간 = 292ms.                                                                                                                        
'Worktable' 테이블. 스캔 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0.                                                                           
'#2B2C316A' 테이블. 스캔 수 1, 논리적 읽기 수 1, 물리적 읽기 수 0, 미리 읽기 수 0.                                                                           
                                                                                                                                                             
SQL Server 실행 시간:                                                                                                                                        
   CPU 시간 = 2766ms, 경과 시간 = 24771ms.                                                                                                                   
Rows        Executes    StmtText                                                                                                                             
----------- ----------- -------------------------------------------------------------------------------------------------------------------------------------
1           1             |--Remote Update(SOURCE:(GWKDP), OBJECT:("INTRAWARE"."GEAR_SANG_MASTER"), SET:([GWKDP]..[INTRAWARE].[GEAR_SANG_MASTER].[RESULTFLAG]
1           1                  |--Compute Scalar(DEFINE:([Expr1006]='9'))                                                                                    
1           1                       |--Table Spool                                                                                                           
1           1                            |--Nested Loops(Left Semi Join, WHERE:([GWKDP]..[INTRAWARE].[GEAR_SANG_MASTER].[SANG_NO]=@SANGRESULT.[SANG_NO]))    
20028       1                                 |--Remote Scan(SOURCE:(GWKDP), OBJECT:("INTRAWARE"."GEAR_SANG_MASTER"))                                        
20028       20028                             |--Table Spool                                                                                                 
1           1                                      |--Table Scan(OBJECT:(@SANGRESULT))                                                                       

                                                                                                                                                            
(8개 행 적용됨)                                                                                                                                             
                                                                                                                                                            


SQL Server 실행 시간:
   CPU 시간 = 2766ms, 경과 시간 = 24771ms.

 

 

해결방안)

 

 1.1건을 update하든 그 이상을 하든 무조건 테이블을 full scan하는 비효율을 가져오고 있다.
 
개선 SQL)

OPENQUERY 방식으로 처리한다.

 

해결SQL


update openquery(GWKDP, 'select RESULTFLAG,SANG_NO from GEAR_SANG_MASTER where SANG_NO in (200401080861,200401080878,200401080899,200401080908)') 
set  RESULTFLAG='9' 

 

 

SQL Server 구문 분석 및 컴파일 시간:                                                                                                             
 CPU 시간 = 0ms, 경과 시간 = 538ms.                                                                                                              
'Worktable' 테이블. 스캔 수 1, 논리적 읽기 수 6, 물리적 읽기 수 0, 미리 읽기 수 0.                                                               
                                                                                                                                                 

SQL Server 실행 시간:                                                                                                                             
   CPU 시간 = 0ms, 경과 시간 = 121ms.                                                                                                             
Rows        Executes    StmtText                                                                                                                  
----------- ----------- --------------------------------------------------------------------------------------------------------------------------
4           1             |--Remote Update(SOURCE:(GWKDP), OBJECT:(select RESULTFLAG,SANG_NO from GEAR_SANG_MASTER where SANG_NO in (200401080861,
4           1                  |--Compute Scalar(DEFINE:([Expr1004]='9'))                                                                         
4           1                       |--Table Spool                                                                                                
4           1                            |--Remote Scan(SOURCE:(GWKDP), OBJECT:(select RESULTFLAG,SANG_NO from GEAR_SANG_MASTER where SANG_NO in (
                                                                                                                                                  
(5개 행 적용됨)         

                                                                                                                         
                                                                                                                                                 
                                                                                                                                                 
SQL Server 실행 시간:                                                                                                                            
   CPU 시간 = 0ms, 경과 시간 = 121ms.                                                                                                            

 

 

 

 

참고자료)

 

참고URL:http://zzizim.egloos.com/817935

오라클-MS SQL 데이터 호환의 비밀

OPENQUERY의 재발견

지용 | 엔코아컨설팅

 

 

기업의 DBMS가 다양해지면서 이기종 DBMS, 그 중에서도 MS SQL 서버와 오라클 간에 데이터를 주고받는 요구사항이 늘어나고 있다.

이를 지원하는 고가의 상용 소프트웨어도 출시돼 있지만 SQL 서버가 기본적으로 제공하는 OPENQUERY 기능을 이용하면 비용 대비 효과적인 대안을 마련할 수 있다.

OPENQUERY의 기본적인 설정 절차와 성능을 고려한 사용법 등을 살펴보자.


연재가이드
운영체제|윈도우 2000
개발도구|DA# 매니저, 쿼리 애널라이저
기초지식|SQL 서버 어드민, SQL 서버 SQL, 오라클 어드민, 오라클 PL/SQL
응용분야|데이터베이스

굴지의 국내 모 제약회사는 자체적으로 구축한 업무 시스템과 패키지로 도입한 그룹웨어의 데이터베이스관리시스템(DBMS)을 모두 오라클 기반으로 사용해 왔다. 그러나 몇 년 간 같은 방식으로 운영하다보니 시스템이 노후됐고 시장 변화에 따라 요구사항이 바뀌면서 업무 시스템을 다시 구축해야 할 필요성이 대두됐다. 여러 가지를 비교 검토한 끝에 제조업 특성을 잘 반영할 수 있는 특정 기업의 ERP 패키지를 도입하기로 결정했다. 문제는 이 애플리케이션이 마이크로소프트(이하 MS) SQL 서버 환경에서만 운영할 수 있다는 점이다. 오라클 기반으로 다시 개발하기에는 기간과 비용 측면에서 사실상 불가능했고 결국 MS SQL 서버 버전의 ERP를 그대로 사용하기로 했다. ERP 패키지는 MS SQL 서버를 사용하고 그룹웨어는 오라클을 사용하는 이기종 DBMS 환경이 된 것이다.

최근 들어 이와 같은 이기종 DBMS 환경을 운영하는 기업들이 늘어나고 있다. 여기에는 가격과 시장의 흐름, 업무 성격, 데이터 크기 등에 따라 그 종류가 더욱 세분화된 측면도 있지만, 주요 DBMS 업체마다 시장을 주도하려는 욕심에 원천적인 데이터에 대한 상호 투명성을 제공하지 않는 형태로 발전해 왔기 때문이다. 특히 기업 입장에서는 소프트웨어 도입시기에 차이가 있고 솔루션에 따라 주력으로 사용하는 RDBMS가 다르다 보니 기업 규모가 커지고 업무가 복잡해지면서 자연스럽게 2개 이상의 RDBMS로 데이터를 처리하는 환경이 만들어졌다.

이기종 DBMS 환경이 대세

문제는 기업의 IT 시스템이 단순히 여러 개의 DBMS를 운영하는 수준이 아니라, 이기종 DBMS 상에서 운영되는 ERP나 그룹웨어 등의 업무 지원 소프트웨어 간의 데이터 교환이 필요하게 되었고 이를 위한 추가적인 소프트웨어 도입 비용을 야기시킨다는 것이다. 액티브엔터프라이즈(ActiveEnterprise), 팁코소프트웨어(Tibco Software)의 EAI(Enterprise Application Integration) 소프트웨어를 이용하거나 Datastage, Data Integrator 등과 같은 ETL(Extraction, Transformation, Loading) 소프트웨어 등을 이용하기도 하지만 추가 비용을 부담해야 하는 단점이 있다.

050829_oq_01.gif
<화면 1>연결된 서버 설정

그렇다면 비용 부담없이 이기종 DBMS 간에 상호작업할 수 있는 방법은 없을까? 여기서 DB 개발자들과 관리자들이 한 가지 놓치고 있는 사실이 있다. 대부분의 DBMS는 기본적으로 이기종 DBMS 간의 상호작업을 위한 기능을 자체적으로 지원한다는 점이다. OPEN QUERY가 대표적인 사례다. 이를 이용하면 추가로 소프트웨어를 구입하지 않고도 오라클과 MS SQL 서버 간에 데이터를 교환할 수 있다.

그동안 OPENQUERY는 이기종 DBMS 환경에서 유용한 대안으로 언급돼 왔으나 이를 활용할 때 성능상의 고려할 점이나 자세한 활용법 등이 소개되지 못해 그 가치에 비해 널리 사용되지 못했던 것이 사실이다.

앞의 사례에서도 현재 이 제약회사는 결재와 관련된 업무는 오라클로 구축된 그룹웨어를 이용하고, 그 외의 업무는 MS SQL 서버 기반으로 구축된 ERP 패키지로 운영해, 실제로 업무와 결재 진행을 상호 연동하기 위해서는 오라클과 MS SQL 서버 간의 데이터 교환이 필수적이었다.

수많은 방법을 검토한 끝에 이 기업은 다른 솔루션을 도입하지 않고 OPENQUERY를 이용해 처리하기로 결정했다. 그러나 문제가 발생했다. 테스트 당시엔 별 문제가 없었으나 실제 업무가 진행되면서 병목현상이 나타난 것이다. 데이터가 점차 증가함에 따라 결재 데이터 처리용 오라클과 MS SQL 서버 간의 데이터 교환이 시스템 성능을 떨어뜨리는 주범으로 지목됐다. 그렇다면 이제 새로운 솔루션을 구매해야 할까? 이번 글의 출발점은 바로 여기다.

MS SQL 서버에서 오라클에 접속

MS SQL 서버와 오라클 간의 데이터 교환 튜닝을 위해서는 먼저 MS SQL 서버에서 오라클에 접속해야 한다. 이를 위해 오라클 클라이언트를 설치하고 접속하고자 하는 오라클 DB에 대한 정보를 등록한다. 이 과정에서 ODBC Data Source Name과 비슷한 역할을 하는 Oracle Net Service Name을 등록한다. 오라클 클라이언트 프로그램은 오라클 웹사이트(www.oracle.com/technology/software/products/ oracle9i/index.html)에서 다운로드할 수 있다.

설치 과정은 GUI 화면을 통해서 이뤄지므로 특별히 신경써야 할 것은 없으며, Oracle Net Service Name 등록 시 접속하고자 하는 서버의 IP와 오라클의 SID(Service ID), 버전 정보를 입력하면 된다. 이제 linked server를 등록한다. SQL 서버의 Transact-SQL문을 이용하면 SQL 서버는 물론 어떤 OLE DB 데이터 소스에도 접근할 수 있다. 이것은 linked server name(linked server)과 ad-hoc connector name(OPENROWSET/OPENDATASOURCE) 등 두 가지 방법을 사용할 수 있는데, linked server name은 이기종 DBMS에 자주 접속해야 하는 경우 연결 정보를 미리 등록해 linked server를 생성해 놓고 사용하는 방식이며, ad-hoc connector name은 연결 정보를 매번 기술하는 방식이다. 여기서는 linked server name 방식을 사용하기 위해 등록하는 방법을 살펴보자.

linked server name을 등록하는 방법도 두 가지다. 먼저 Enterprise Manager에서 설정하려면 『해당 서버|보안|연결된 서버|마우스 오른쪽 클릭|새 연결된 서버』를 선택해 <화면 1>과 같이 관련 정보를 입력하면 된다. 쿼리 분석기를 이용할 경우에는 sp_addlinkedserver로 linked server를 등록하고 다음과 같이 sp_addlinkedsrvlogin를 이용해 접속시 사용할 로그인을 등록한다.

050829_oq_02.gif
<화면 2> OPENQUERY를 이용한 Select

050829_oq_03.gif
<화면 3> OPENQUERY를 이용한 Insert

050829_oq_04.gif
<화면 4> FULLY QUALIFIED NAME 이용한 Update

sp_addlinkedserver [ @server= ] ‘server’     [ , [ @srvproduct = ] ‘product_name’ ] ,     [ , @provider = ] ‘provider_name’ ],     [ , @datasrc = ] ‘data_source’ ],     [ , @location = ] ‘location’ ],     [ , @provstr = ] ‘provider_string’ ],     [ , @catalog = ] ‘catalog’ ]

예를 들어 EXEC sp_addlinkedserver ‘JYOra817’, ‘Oracle’, ‘MSDAORA’, ‘JY817’라면 JY817이라는 Oracle Net Service Name으로 등록된 오라클 DBMS를 JYOra817이라는 이름의 linked server로 등록한다. 로그인을 생성하는 방법은 다음과 같다. EXEC sp_addlinkedsrvlogin ‘JYOra817’, ‘FALSE’, NULL, ‘scott’, ‘tiger’라면 JYOra817이라는 linked server에 scott/tiger라는 아이디와 패스워드를 이용해 로그인하는 정보를 등록한다.

sp_addlinkedsrvlogin [ @rmtsrvname = ] ‘rmtsrvname’     [ , [ @useself = ] ‘useself’]     [ , [ @로컬login = ] ‘로컬login’ ],     [ , [ @rmtuser = ] ‘rmtuser’ ] [ , [ @rmtpassword = ] ‘rmtpassword’ ]

linked server를 이용한 DML 실행

linked server에 존재하는 Object를 쿼리하기 위해서는 Server.Data base.owner.Object로 구성된 Fully Qualified Name을 사용하거나 OPENQUERY를 이용하는 방법이 있다. 사용 방법도 간단해 쿼리를 작성할 때 해당 Object가 쓰일 자리에 Fully Qualified Name이나 OPENQUERY를 대신 기술하면 된다. 먼저 OPENQUERY를 이용할 경우 다음과 같이 linked server 이름과 해당 object를 select하는 쿼리를 인자로 기술한다.

OPENQUERY ( linked_server , ‘쿼리’)

그럼 Select를 포함한 DML(Data Manipulation Language)문에 OPENQUERY를 사용하는 예를 하나씩 살펴보자.

Select문의 From절에 OPENQUERY 함수를 기술하고 OPEN QUERY 안의 SQL에 원하는 테이블을 Select하는 문장을 기술한다(<화면 2>). Insert문의 경우도 <화면 3>처럼 테이블 명을 기술할 자리에 OPENQUERY를 쓰면 된다(여기서 ‘WHERE 1=0’ 조건을 기술한 이유는 이후 성능 문제를 다루면서 살펴본다). Update문과 Delete문도 테이블 명이 기술될 자리에 OPENQUERY를 쓰면 된다.

Fully Qualified Name

Fully Qualified Name이란 Object를 명시할 때 Server, Database, Owner, Object를 모두 기술하는 방식이다. 앞서 설정한 linked server를 이용해서 쿼리하고자 할 경우에는 OPENQUERY를 이용하지 않고 Fully Qualified Name를 이용한다. 오라클의 경우 단일 Instance에 단일 데이터베이스만 존재하므로 <화면 4>와 같은 방식으로 사용한다.

한 가지 주의해야 할 사항은 오라클의 경우 사용자(owner)와 테이블 등의 정보가 데이터 카탈로그(메타데이터)에 대문자로 저장된다는 점이다. Fully Qualified Name을 사용하면 오라클의 메타데이터 정보 조회 시 SQL 서버에서 쿼리에 공급한 사용자와 테이블 명이 그대로(대소문자 변경 없이) 조회 조건으로 공급되므로 반드시 사용자와 테이블 명을 대문자로 기술해야 한다. 소문자를 사용하면 에러가 발생한다.

한편 기술적으로 보면 Fully Qualified Name은 OPENQUERY보다 사용하기 간편하나, 수행에 필요한 정보를 얻기 위해 메타데이터를 조회하는 양은 OPENQUERY가 적다. 반면 OPENQUERY는 OPENQUERY 안의 Select문을 기술하면서 필요한 컬럼만 나열할 수 있고, Where절도 같이 기술해서 로컬로 가져오는 데이터량을 줄일 수 있다. 따라서 OPENQUERY를 사용하는 것이 Fully Qualified Name를 사용하는 것보다 성능 면에서 유리하고 개선하기도 쉽다.

linked server를 이용해 DML 이외의 작업 수행

먼저 OPENQUERY와 Fully Qualified Name를 이용해 시도해 보자. DML을 수행하려면 linked server를 생성해 OPENQUERY 함수를 사용하거나 Fully Qualified Name을 사용할 수 있다. 그러나 이와 같은 방식으로 오라클의 프로시저를 직접 수행하거나 DDL (Data Definition Language) 작업과 같은 DML 이외의 작업을 수행하면 에러가 발생한다.

예를 들어 OPENQUERY 함수 안의 SQL은 항상 테이블이 선택된 결과만을 리턴해야 하는데, 테이블을 선택한 형태로 결과를 리턴하지 않는 DDL을 수행하면 <화면 5>와 같은 에러가 발생한다. 리모트 쿼리도 마찬가지다. 리모트 서버에 대한 DDL 쿼리를 지원하지 않기 때문에 Fully Qualified Name을 사용하면 에러가 발생한다.

이를 해결하기 위해 기존에는 오라클에서 트리거(trigger)를 생성할 테이블을 만들고 그 테이블에 대해 Insert나 Update 시 원하는 작업을 수행하는 트리거를 생성해 놓고, MS SQL 서버에서 OPEN QUERY를 이용해 해당 테이블에 Insert 또는 Update를 수행하는 방식을 이용했다. 그렇지만 이 방식에는 몇 가지 단점이 있다. 작업용 테이블을 하나 더 생성하기 때문에 관리할 대상이 늘어나고, 작업의 성공 여부와 에러 메시지를 직접 전달받기 어렵다. MS SQL 서버에서는 Insert나 Update 작업의 성공 여부만 알 수 있을 뿐 트리거 안에서 수행된 작업의 성공 여부나 에러 메시지를 받아볼 수 없다. 또한 트리거는 disable되기 쉬우며 사용자가 트리거의 disable 여부를 알아채기 쉽지 않다.

오라클 8i 이후 버전부터는 autonomous transaction을 이용해 이러한 단점을 극복한 새로운 방식을 사용할 수 있다. 함수는 Select절에 기술할 수 있으므로 OPENQUERY 함수 안의 SQL은 항상 테이블이 select된 결과만을 리턴해야 하는 제약이 따른다. autonomous transactions이란 다른 transaction 안에서 수행되는 독립적인 트랜잭션으로 상위 트랜잭션의 rollback이나 commit 여부와 관계없이 자신의 트랜잭션을 유지한다. 이것을 이용하면 함수 안에서 DDL이나 Stored Procedure를 호출할 수 있다. 여기서는 MS SQL 서버에서 OPENQUERY로 오라클에 작업용 테이블을 생성하는 예를 통해 함수와 autonomous transaction을 어떻게 사용해야 할지 살펴보자.

먼저 오라클에서 처리해야 하는 작업이다. <화면 6>은 특정 테이블(JY_TABKKK)의 Create/Drop 여부를 파라미터(delYn)로 받아서 성공한 경우 ‘Y’를, 실패한 경우 에러 메시지를 리턴하는 함수이다. 두 가지 점을 유의해야 하는데, pragma autonomous_transactions은 autonomous transactions을 사용하겠다는 키워드이므로 반드시 함수를 생성할 때 사용해야 한다. 그렇지 않으면 에러가 발생한다. 또 한 가지 주의할 점은 PL/SQL 안에서 DDL을 사용하려면 Dynamic SQL을 이용해야 한다는 점이다. execute immediate는 Dynamic SQL을 사용하기 위한 방법 중 하나이다.

함수를 생성했으면 이제 권한을 부여할 차례다. autonomous transactions으로 테이블을 생성할 때는 설사 로그인한 유저 소유의 테이블을 생성할지라도 ‘create any table 권한이 없으면 권한이 부족하다’는 에러 메시지를 만나게 된다. 따라서 <화면 7>과 같이 create any table 권한을 부여해야 한다. 이제 생성한 함수를 select DDL_TAB(‘C’)와 같이 실행해보자. ‘Y’가 리턴되면 정상적으로 수행된 것을 의미한다. 참고로 dual은 오라클을 설치할 때 메타데이터와 함께 기본적으로 생성되는 테이블로, 한 개의 행만 들어가 있어 상수 연산이나 함수를 실행할 때 사용한다.

다음은 SQL 서버에서 작업해보자. 먼저 linked server를 생성한다. <화면 8>은 JY817이라는 Oracle Net Service Name으로 등록된 오라클 DBMS를 JYOra817이라는 이름의 linked server에 등록하는 과정이다. <화면 9>는 앞서 작성한 JYOra817 linked server에 접속할 때 scott/tiger라는 아이디와 패스워드를 이용하는 로그인이다. 이제 OPENQUERY를 이용해 오라클에서 생성한 DDL_TAB 함수를 호출하는 SQL문을 실행하면 정상적으로 수행되는 것을 확인할 수 있다.

050829_oq_05.gif
<화면 5> OPENQUERY를 이용한 DDL 수행 에러

050829_oq_06.gif
<화면 6> DDL 수행용 함수 생성하기

050829_oq_07.gif
<화면 7> 오라클의 권한 부여

050829_oq_08.gif
<화면 8> MS SQL 서버 linked server 생성

050829_oq_09.gif
<화면 9> MS SQL 서버 로그인 생성

050829_oq_10.gif
<화면 10> 필요한 컬럼만 나열

050829_oq_11.gif
<화면 11> OPENQUERY 밖에 where절을 기술한 경우 SQL 서버 쪽 트레이스

OPENQUERY 성능을 높이기 위한 노하우

지금까지 OPENQUERY 사용법에 대해 살펴봤다. 기본적인 방법을 배웠으니 지금부터는 어떻게 하면 더 효율적으로 이용할 수 있을지 알아보자.

Select절에는 반드시 필요한 컬럼만 나열한다

모든 쿼리에 다 해당되겠지만 OPENQUERY는 리모트에 존재하는 데이터를 로컬로 가져와 다시 클라이언트에게 결과를 돌려주기 때문에 반드시 필요한 컬럼만 나열해 네트워크 통신의 부하를 줄여야 한다. 이를 위해서는 무엇보다 OPENQUERY내 쿼리의 Select절에 필요한 컬럼만 나열하는 것이 중요하다.

Select문을 포함한 DML문을 수행할 경우 일단 OPENQUERY 내의 쿼리 결과를 로컬로 가져온 후 해당 작업을 진행한다. 따라서 Select의 경우는 OPENQUERY 내의 쿼리만 수행되고 Select를 제외한 DML문은 Select절에 기술된 컬럼과 rowid를 선택해 로컬로 가져오는 쿼리와, 이 때 얻은 rowid를 이용해 해당 DML 작업을 수행하는 두 단계로 나눠서 작업이 이뤄진다. 즉 DML문을 수행하면 무조건 OPENQUERY안의 쿼리 결과를 가져오기 위한 Select 문장이 수행된다. 따라서 OPENQUERY 내의 쿼리에서 Select절에는 항상 필요한 컬럼만 나열해야 한다. 예를 들어 <화면 10>에서 최종적으로 보고자 하는 컬럼은 empno, ename, sal뿐이므로 OPENQUERY 내의 SQL에도 empno, ename, sal 세 컬럼만 나열해서 오라클이 해당 컬럼들만 선택하도록 해야 한다.

단순 Delete문일 경우 OPENQUERY안의 Select절에 rowid만 기술한다. 단순 Delete의 경우는 작업을 위해 rowid에 대한 정보 외에 다른 컬럼에 대한 정보를 알 필요가 없으므로 OPENQUERY 안의 Select절에 rowid만을 기술해 주면 된다.

리모트 테이블 조건은 OPENQUERY 내의 SQL에 기술하라

리모트 테이블에 대한 조건을 OPENQUERY 함수 밖의 Where절에 기술하면 <화면 11>과 같이 MS SQL 서버는 해당 조건을 OPENQUERY 함수 안에 사용된 쿼리를 만족하는 전체 데이터를 로컬로 가져온 후 걸러내는 필터로 사용된다.

이 때 오라클에서 트레이스(trace)를 떠보면 OPENQUERY 밖에서 기술한 Where절은 제공되지 않고 OPENQUERY 안에서 기술된 Select * From SCOTT.EMP만 수행된 것을 알 수 있다.

따라서 이처럼 네트워크를 통해 불필요한 데이터를 가져온 뒤 로컬에서 걸러내는 비효율인 과정을 없애려면 리모트(오라클)에서 필요한 데이터만 가져오도록 OPENQUERY 함수 안의 SQL에 조건을 기술하면 된다. 이렇게 하면 필터 작업이 없어지고 리모트 스캔 작업 결과 건수와 행이 1건으로 표시돼 필요한 데이터만을 가져온다. 오라클의 트레이스 결과를 봐도 해당 where 조건이 포함된 SQL이 수행됐고 MS SQL 서버에 나타난 결과 건수인 한 행만 결과로 리턴된 것을 확인할 수 있다.

No Row Returned가 발생할 수 있는 조건을 기술하라

OPENQUERY를 이용해서 Insert문을 수행하고 오라클에서 트레이스를 떠보면 메타데이터 조회 쿼리 외에 다음 두 개의 문장이 실행된다.

Select scott.emp.*, scott.emp.rowid  From scott.emp Insert INTO “SCOTT”.“EMP”     (“EMPNO”, “ENAME”, “JOB”, “MGR”, “HIREDATE”, “SAL”, “COMM”, “DEPTNO”)  VALUES (:V00001, :V00002, :V00003, :V00004, :V00005, :V00006, :V00007, :V00008

050829_oq_12.gif
<화면 12> OPENQUERY 이용한 Delete 문 오라클 쪽 트레이스

Insert 작업 시 해당 테이블의 데이터 존재 여부나 데이터 건수는 알 필요는 없다. 단지 테이블의 존재 여부와 사용될 컬럼의 존재 여부만 중요하다. 그렇지만 Insert문도 다른 DML문과 마찬가지로 OPENQUERY 내의 쿼리 결과를 로컬로 가져와 해당 DML을 수행하는 절차를 거친다. 따라서 이처럼 OPENQUERY 내의 쿼리를 작성하면 해당 테이블의 모든 컬럼에 대해 전체 데이터를 쿼리해서 로컬로 가져오는 매우 비효율적인 과정을 거치게 된다. 데이터량이 증가할수록 부하는 더 커지게 된다. 이를 방지하려면 결과 건이 절대로 발생하지 않는 조건(예:where 1=0)을 추가하면 된다. 참고로 Fully Qualified Name을 사용한 경우에도 전체 데이터 Select후 Insert하는 동일한 방식으로 수행된다. 그렇지만 OPENQUERY를 이용한 경우처럼 전체 데이터 Select를 방지할 수 있는 방법이 없으므로 주의해야 한다.

대량 데이터를 변경/삭제할 경우 리모트에서 수행하라

Delete문을 수행하고 MS SQL 서버에서 실행계획을 확인하면 리모트 스캔(Remote Scan)을 통해 OPENQUERY 내의 쿼리 수행결과 전체를 가져와서 Table Spool 즉 TEMPDB에 저장하고 Remote Delete를 수행한 것을 알 수 있다. 이 때 오라클의 트레이스를 보면 Insert문과 마찬가지로 메타데이터 조회 쿼리 외에 다음 두 개의 쿼리가 수행된 것을 확인할 수 있다.

Select rowid  From scott.emp Delete From “SCOTT”.“EMP” WHERE rowid = :V00001

이 때 오라클의 트레이스에 나타난 Delete 문장 수행 정보를 살펴보면 <화면 12>와 같다. 주목할 것은 Parse과 Execute의 횟수이다. 현재 EMP 테이블에는 14건이 존재하는데 Execute는 14번 발생했다. 전체 건수만큼 루프를 돌면서 rowid를 이용해서 한 건씩 지웠다는 의미이다. 이렇게 수행하면 DBMS 콜이 과도하게 발생해 성능에 문제가 발생할 가능성이 크다.

050829_oq_13.gif
<화면 13> 오라클 로컬에서 직접 수행한 Delete 문 트레이스

반면 <화면 13>은 리모트 쪽인 오라클에서 Delete문을 직접 수행하고 트레이스를 떠본 결과이다. Execute의 횟수가 1번인 것에 주목할 필요가 있다. 또한 수행 시간을 비교해도 둘의 성능 차이는 극명하게 드러난다. 처리할 데이터 건수가 많아질수록 두 방식의 성능 차이는 더 벌어질 것이다. 따라서 리모트에 존재하는 테이블에 대한 조건과 값 만으로 수행할 수 있는 작업이라면 리모트(오라클)에서 직접 수행하고, MS SQL서버에서 OPENQUERY를 이용해서 작업해야 한다면 앞서 소개된 함수와 autonomous transaction을 이용한 방식으로 수행해 리모트(오라클)에서 작업하는 것이 효율적이다.

지금까지 OPENQUERY를 이용하여 리모트(오라클)에 존재하는 테이블 데이터를 불러오는 방법과 DDL과 Procedure를 수행하기 위한 방법, 성능상 주의할 점에 대해 살펴봤다. RDBMS의 데이터 처리시 성능을 높이기 위한 대명제는 ‘읽어야 할 것만 읽고, 어쩔 수 없이 읽은 후에 골라내야 한다면 버리는 것이 적어야 한다’는 것이다. 이런 시각으로 접근한다면 이번 강좌에서 살펴본 사항들을 응용하는 것도 어렵지 않을 것이다.

참고자료

① SQL 서버 온라인 설명서
② Oracle concept manual
③ Oracle Performance Tuning manual
④ DBZINE.COM www.dbazine.com/sharma2.shtml

 

Tag

Leave Comments