데이터이야기
DB 노하우, 데이터직무, 다양한 인터뷰를 만나보세요.
PL/SQL을 사용하는 이유 이번 시간에는 PL/SQL에 대한 이야기를 해볼까 합니다. 우리가 알고 있듯이 SQL은 집합적 사고에 기반하고 있으며 PL/SQL은 C나 JAVA처럼 절차적 언어입니다. 이는 PL/SQL로 작성할 수 있는 것들은 C나 JAVA로도 얼마든지 작성할 수 있다는 뜻이 됩니다. 그렇다면 굳이 PL/SQL을 써야 할 이유가 어디에 있을까요 PL/SQL이 갖는 장점을 좀 더 자세히 살펴보도록 하겠습니다. 그렇다면 언제 C나 JAVA를 쓰고 언제 PLSQL을 쓰는 것이 좋을까요 PLSQL은 많은 장점을 가지고 있지만 현장에서 잘못 사용되어 오히려 문제를 일으키는 경우를 자주 목격합니다. 다음에서 잘못 사용하는 사례를 짚어보도록 하겠습니다.
BEGIN
DECLARE 따라서 개발자는 먼저 SQL만으로 작성할 수 있는지 꼼꼼히 확인한 후에 PLSQL 사용을 검토해보는 것이 좋습니다.
create or replace function get_deptname (부서코드 in number) 실제 SQL은 다음과 같이 작성됩니다. 국내 SI 개발 현장을 둘러보면 PLSQL 사용을 꺼리거나 (정책적으로 아예 금지하는 곳도 있음) 오남용하는 경우를 자고 생각합니다. PLSQL을 사용하면 훨씬 유지보수도 쉽고 성능이 뛰어난 상황에서도 C나 JAVA의 사용을 강요하는 것은 비합리적이라고 여겨집니다. PL/SQL을 사용하는 이유
PL/SQL이 C나 JAVA에 비해 유리한 점은 애플리케이션 서버 측에서 수행되는 것이 아니라 데이터베이스 엔진 쪽에서 수행된다는 데 있습니다. 데이터와 가장 가까운 곳에 위치하고 있으므로 애플리케이션 서버로 데이터를 주고 받는 데 따르는 네트워크 트래픽이 최소화된다는 장점이 있습니다.
즉, 데이터를 처리하는데 있어 PL/SQL이 C나 JAVA에 비해 성능상 많은 장점이 있습니다. 가끔 배치 프로그램을 Pro*C나 JAVA로 짜는 경우를 많이 보는데, 이 경우 WAS 쪽으로 대량의 데이터를 fetch해온 후 루프를 돌면서 데이터 가공처리(Update나 Delete, 또는 다른 테이블에 Insert)하는 처리를 하게 됩니다. 당연히 WAS 쪽으로 대량의 데이터를 fetch해올 경우 네트워크를 통해 막대한 데이터가 흐르게 되고 WAS에서 Out of memory(메모리 고갈) 상황이 벌어지는 경우도 비일비재합니다.
물론 PL/SQL이 많은 장점을 가지고 있긴 하지만 새로운 언어를 새로 배워야 한다는 점에서 부담을 느끼는 개발자가 많은 것도 사실입니다. 하지만 PL/SQL은 C나 JAVA에 비해 상당히 쉬운 문법 구조를 가지고 있기 때문에 익히기 쉽습니다.
PL/SQL의 장점
1. SQL과 긴밀히 통합되어 있다.
PLSQL 내에서 모든 SQL 구문, 커서 제어, 트랜잭션 제어 구문, SQL 내장 함수, 연산자, pseudo 칼럼을 자유롭게 사용할 수 있습니다. 또한 C나 JAVA와 달리 PLSQL의 데이터 타입은 SQL 데이터 타입과 바로 호환됩니다. 즉, PLSQL내에서 사용된 SQL 구문이 VARCHAR2 타입의 칼럼값을 반환할 경우 이를 바로 PLSQL의 VARCHAR2 타입 변수에 저장할 수 있습니다. 따라서 PLSQL이 JAVA의 JDBC나 C의 PRO*C을 사용하는 것보다 훨씬 쉽고 단순합니다.
2. 성능이 뛰어나다
(1) PLSQL은 JAVA(JDBC)와 달리 별도의 API를 사용하지 않아도 대개의 경우 알아서 BIND 변수로 처리해주므로 hard parsing 오버헤드에 대해 걱정하지 않아도 됩니다.
(2) 일괄 작업을 수행하는 SQL들을 하나의 PL/SQL에 작성해두고 필요할 때마다 데이터베이스에서 반복적으로 수행할 수 있습니다. 네트워크를 통한 라운드 트립이 최소화되므로 성능상 유리합니다. 예를 들어, 증권 거래와 같이 수 ms 정도의 짧은 수행 시간을 보장해야 하는 경우 대부분의 시간이 클라이언트와 서버간에 메시지와 데이터를 주고 받는데 사용됩니다. 만약 이를 PLSQL로 작성하여 서버에서 한번에 처리하고 마지막에 그 결과만 클라이언트로 반환할 수 있다면 상당한 시간을 단축할 수 있게 됩니다.
(3) SQL을 최적화하는 옵티마이저가 존재하듯이 PLSQL도 사용자가 작성한 코드를 재배치하여 최적화하는 PL/SQL 옵티마이저가 존재합니다. JAVA와 달리 PLSQL은 PLSQL 옵티마이저가 데이터베이스에서 최적으로 수행될 수 있도록 자동으로 코드를 최적화시켜 줍니다.
3. 생산성 및 관리의 용이성
일반적으로 PLSQL로 작성한 코드가 C나 JAVA로 작성한 코드 보다 간결합니다. 또한 PLSQL은 서버에 저장되므로 어디서나 쉽게 호출하거나 개발툴로 수정할 수 있습니다. 서버에 저장되므로 JAVA처럼 클라이언트의 모든 코드를 수정할 필요 없이 서버의 PLSQL을 수정하면 즉각 모든 변경 사항이 PLSQL을 호출하는 애플리케이션으로 전파됩니다.
4. 이식성
JAVA와 마찬가지로 PLSQL도 중간 형태의 코드로 변환되어 데이터베이스 저장되므로 데이터베이스가 설치된 플랫폼이나 운영체제에 무관하게 이식할 수 있습니다. 이는 JAVA와 마찬가지로 플랫폼 독립성을 가지므로 이식에 따른 코드 수정이 불필요하다는 의미가 됩니다.
언제 PLSQL을 사용해야 하는가
1. 사용자의 입력을 받아서 입력 값을 처리하는 프리젠테이션 로직은 C나 JAVA로 작성하고, 실제 비즈니스 로직은 PLSQL이나 SQL로 처리하는 것이 좋습니다.
2. PLSQL은 객체지향 방식을 지원합니다. 물론 JAVA처럼 완전한 객체지향 언어는 아닙니다. PLSQL에서 제공하는 패키지를 사용하여 공통 비즈니스 로직을 작성해둔다면 코드 재활용과 유지보수 측면에서 상당히 유리합니다.
PLSQL 사용시 주의해야 할 점
첫째, SQL로도 얼마든지 처리 가능함에도 불구하고 PLSQL로 처리하는 경우 다음과 같이 단순한 SQL 구문을 PLSQL로 처리하는 사례가 자주 목격 됩니다.
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),1500, 0, 30);
END;
/
개발자에게 SQL로 처리하지 않고 위와 같이 PLSQL로 작성한 이유를 물어보면 PLSQL을 사용하는 것이 성능에 좋다고 책에 적혀있기 때문에 사용했다는 답변을 하더군요. 물론 개발자가 책의 내용을 오해한 것입니다. SQL로 처리 가능하다면 SQL 처리하는 것이 가장 효율적입니다. 데이터베이스에는 SQL을 처리하는 SQL 엔진과 PLSQL을 처리하는 PLSQL 엔진이 따로 존재합니다.
위와 같이 작성할 경우 SQL 엔진과 PLSQL 엔진 사이를 전환하면서 처리하게 되며 이를 컨텍스트 스위칭(Context switching)이라고 하며 자주 발생하면 성능에 좋지 않습니다. 또한 위처럼 anonymous PLSQL로 작성할 경우 이전에 컴파일 된 바이트코드가 라이브러리 캐시에 존재하지 않을 경우 매번 syntax check를 하고 바이트 코드로 컴파일하는 과정을 거치야 하므로 이렇게 작성하면 안됩니다. 더욱이 위의 예제는 PLSQL내에 삽입된 SQL구문에서 BIND 변수를 하지 않고 리터럴(literal)로 처리했으므로 라이브러리 캐시에 저장된 코드를 재사용하지 못하고 매번 컴파일 과정을 거쳐야 합니다. 이는 마치 JAVA에서 소스 코드를 매번 수행할 때마다 컴파일하는 것과 마찬가지로 비효율적입니다. 결국 개발자가 모든 단순 PLSQL을 SQL로 변환한 다음에서야 데이터베이스 성능이 정상화될 수 있었습니다.
다음은 좀 더 복잡한 경우를 살펴보겠습니다. SQL 구사 능력이 다소 떨어지는 개발자일 경우 SQL로도 얼마든지 작성할 수 있음에도 불구하고 PLSQL을 사용하여 중첩 루프로 작성하는 경우를 많이 목격합니다. 앞서 살펴봤듯이 관계형 데이터베이스는 집합적 사고에 의해 SQL 단위로 짰을 때 가장 효율적입니다. 예를 들어 아래와 같은 PLSQL 로직은 SQL의 MERGE 구문으로 재작성할 수 있습니다.
CURSOR c1 AS SELECT ... FROM source_t;
BEGIN
FOR c1_rec IN c1
LOOP
UPDATE target_t SET .. WHERE n1=c1_rec.n1 AND n2=c1_rec.n2;
EXCEPTION
WHEN no_data_found THEN
INSERT INTO target_t VALUES(…) ;
END LOOP;
COMMIT;
END;
/ MERGE INTO target_t t
USING (SELECT n1, n2... FROM source_t s WHERE ...) S
ON (s.n1 = t.n1 and s.n2 = t.n2)
WHEN MATCHED THEN UPDATE SET t.status = 'X'
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);
둘째, 코드 재사용을 위해 PLSQL을 남용하는 경우 개발자들은 한번 작성한 코드를 재사용하거나 너무 긴 SQL을 단순화하기 위해 PLSQL을 사용하는 경우가 있습니다. 예를 들어, 다음과 같이 조인 구문을 단순화하기 위해 사용자 정의 함수로 대체한 사례를 자주 볼 수 있습니다.
select 사원번호, 사원이름, 직급, 부서명 from 사원, 부서 where 사원.부서코드 = 부서.부서코드 위의 조인문을 단순화하기 위해 ‘부서명’을 얻는 부분을 함수로 작성하게 됩니다.
return varchar2
is v_dname varchar2(30);
BEGIN
SELECT 부서명 into v_dname from 부서 WHERE 부서코드 = 입력받은 부서코드;
RETURN v_dname
END;
/
select 사원번호, 사원이름, 직급, get_deptname(부서코드) from 사원 단 두 개의 테이블 조인을 단순화하려고 PLSQL 함수를 사용하는 사례가 있을까 하고 생각하겠지만 실제로 현장에서 자주 목격되는 사례입니다. 물론 두 개 테이블 간의 조인이 아니라 모델링 상의 문제 등으로 인해 불필요하게 다수의 테이블을 매번 조인해야 한다면 개발자로써는 PLSQL을 써서 반복 타이핑 작업을 회피하고 싶은 강한 유혹을 느끼게 될 겁니다. 이에 대한 저의 조언은 SQL에서 코드 재사용이 필요한 경우에는 PLSQL을 쓰지 말고 VIEW를 사용하라는 것입니다.
노파심에서 말씀 드리지만 위의 내용은 SQL내에서 절대로 PLSQL을 사용하지 말라는 이야기는 아닙니다. 사용에 따른 결과를 충분히 주의해서 사용해야 한다는 의미이고 조인 SQL을 단순화하려는 목적으로는 사용하지 말라는 것입니다.
참고: SQL에서 PL/SQL을 사용했을 때의 또 다른 문제점으로는 데이터 일관성이 깨지는 문제가 있을 수 있다는 겁니다. 오라클은 쿼리의 수행 시작 시점을 기준으로 데이터를 보여줍니다. 하지만 SQL 내에서 PLSQL을 사용하고 해당 PLSQL내에서 또 다른 테이블을 조회하는 경우 ‘쿼리의 수행이 시작된 시점(SCN)’이 SQL과 PLSQL 간에 서로 다르게 적용됩니다. 즉, SQL이 장시간 수행되면서 PLSQL 내에서 참조하는 테이블에 변경이 일어날 경우 수행시점 기준으로 일관성이 맞지 않는 데이터가 조회될 수 있습니다. 실제로 장기 수행되는 SQL 내에서 다른 테이블의 데이터를 조회하는 PLSQL을 사용하여 집계(SUM, COUNT 등)하는 업무에서 문제가 된 사례가 있었습니다.
셋째, PL/SQL의 루프 내에서 DB LINK를 사용하는 경우
현장에서 분석을 위해 PLSQL 코드를 열어보고 가장 놀라게 되는 점은 너무 무분별하고 유지보수가 불가능할 정도로 PLSQL과 DB LINK가 사용된다는 점입니다. 뒤에서도 이야기하겠지만 무분별한 트리거(trigger) 사용과 PL/SQL내에서 또 다른 PLSQL을 호출하고 해당 PLSQL을 따라가보면 또 다른 PLSQL을 계속해서 호출하는 식으로 작성되어 있는 사례가 빈번하다는 점입니다. 저는 이러한 코드를 ‘양파 스타일 코드’라고 부릅니다. 과연 이러한 코드의 유지보수가 가능한 것인지 심지어 작성자조차도 시간이 흐르면 파악이 되지 않을 정도의 복잡도에 PLSQL 내에서 다른 데이터베이스로의 무분별한 DB LINK 사용으로 거미줄처럼 실타래가 얽혀서 성능 분석조차 불가능한 경우가 많습니다. 솔직히 이 정도로 얽혀있으면 향후 업무 변경에 따른 코드 유지보수는 물론이고 성능을 최적화하는 것은 불가능합니다. 처음부터 새로 작성하는 수 밖에 없고 이는 전면 재개발을 의미합니다. 처음부터 데이터베이스를 잘 아는 사람이 아키텍처를 잡고 불필요한 PLSQL 오남용을 방지하고 DB LINK 사용에 대해서도 교통정리를 해줬어야 합니다. 그러나 대부분의 프로젝트는 이미 사태()가 벌어지고 난 다음에 성능 분석가가 투입되고 개발자와 데이터베이스 전문가는 조직이 분리되어 있어 개발자가 개발 과정에서 모델링 그리고 SQL이나 PLSQL에 대해 적절한 조언을 받을 수 없는 게 현실입니다.
이야기가 많이 딴 길로 샜는데 다시 루프 내에서의 DB LINK 사용 문제로 돌아가겠습니다. DB LINK는 물리적으로 떨어진 데이터베이스를 네트워크를 통해 데이터를 가져오거나 변경하는 수단입니다. 우리나라의 경우 국토 면적이 좁고 인터넷 인프라가 발달해서 큰 문제가 없을 수도 있지만, 바로 이웃인 중국의 경우만 봐도, 지역별로 인터넷 공급자가 다르거나 서비스 품질이 떨어지거나 너무 멀리 위치해서 네트워크 지연이 상당한 경우가 많습니다. DB LINK를 사용하는데 1ms가 걸리더라도 루프가 10만번만 돌아도 100초가 걸립니다. 대부분 DB LINK 사용에 따른 SQL*NET 네트워크 지연에 시간이 소요됩니다. 따라서 루프 내에서 DB LINK 사용은 성능상 매우 불리합니다. 이를 해소하기 위한 방법 중 하나는 PLSQL 코드를 SQL로 작성하는 것입니다. 데이터베이스의 옵티마이저는 불필요한 네트워크 트래픽을 최소화하는 방향으로 실행 계획을 수립하기 때문에 SQL이 PLSQL보다 성능상 유리합니다. 그러나 PLSQL이 너무 복잡해서 SQL로 작성하는 것이 불가능하다면 DB LINK를 Materialized View로 대체하는 방안을 고려해볼 수 있습니다. 일반적인 View가 단순히 데이터 딕셔너리에 저장된 SQL에 불과하고 실체가 없는데 반해 Materialized View는 일반 테이블과 마찬가지로 물리적인 공간을 차지하며 필요하면 인덱스도 추가하여 성능을 향상시킬 수 있습니다. Materialized View는 로컬 테이블과 마찬가지이므로 DB LINK와 같은 네트워크 지연이 없습니다.
넷째, 무분별한 트리거 사용
입력되는 데이터에 대해 아주 복잡한 비즈니스 규칙을 check해야 할 경우나 모델링상 반정규화된 칼럼 데이터를 갱신하는 용도로 트리거를 사용할 수 있습니다. 적절히 사용한다면 아주 훌륭한 수단이지만 트리거의 단점도 만만치 않습니다. 트리거는 특정 이벤트(테이블에 데이터가 삽입된다거나 변경될 경우)에 반응하는 자동화된 방식이라 트리거가 복잡하게 얽혀있을 경우 트리거에 의해 변경되는 데이터를 추적하기가 쉽지 않습니다. 또한 DBA가 테이블에 대해 작업을 할 경우에도 문제를 일으켜 장애의 원인이 되기도 합니다. 따라서 일반적으로 DBA들은 트리거를 좋아하지 않습니다. 만약 트리거를 사용해야 한다면 적절한 가이드라인을 만들고 지나치게 사용하지 않도록 주의해야 합니다.
마지막으로…
특히 수 ms 정도의 미세 튜닝이 필요한 경우 SQL 레벨에서는 더 이상 튜닝이 불가능할 때가 많은데, 이럴 때 PLSQL을 쓰면 네트워크에서의 불필요한 지연을 줄여서 상당한 개선 효과를 볼 수 있습니다. 또한 PLSQL을 쓰지 말아야 할 상황에서 무분별하게 오남용하는 경우도 문제입니다.
무턱대고 금지하지 말고 정책적인 가이드라인을 제시해야 체계적으로 사용할 수 있는 체계를 갖추는 것이 올바른 방법이라고 생각합니다. 개발자들도 단순히 문법책에 나온 예제나 타인의 소스 코드를 무비판적으로 수용하여 PL/SQL을 작성하지 말고 PLSQL이 제공하는 다양한 기능과 최적화 기법을 연구하는 자세가 필요하다고 여겨집니다. 감사합니다.