DA 조직의 구성에 대하여

필자의 후배들이 몇몇 업체의 DBA를 담당하는데 차세대 프로젝트에서 DA 조직의 구성방법에 대하여 가끔 필자에게 질문이 들어오곤 한다.
이때 필자는 반드시 아래의 구조를 가지게끔 답변을 한다.

1.CIO 는 반드시 DA팀의 수장이어야 한다.
2.전사적인 관점에서 DA 조직의 팀은 크게 7으로 나뉘어야 한다.
-표준화팀: 단어사전, 용어(속성)사전, ERD 표준, SQL 표준 , 프로그램 언어의 표준등 각종 표준정의및 관리
-모델링팀: 업무파악, 개념모델및 논리/물리모델 정의 및 관리
-DBA팀 : DBMS 종류별로 오브젝트의 생성및/관리, 오브젝트별 권한관리, 오프젝트별 용량관리, 백업/복구관리, DBMS 버그페치
-튜닝팀 : 쿼리및 오브젝트, 인스턴스의 최적화
-SYS ADMIN 팀:운영체제 관리및 최적화, 디스크 최적화(스트라이핑)및 관리, 네트워크 관리, OS 버그페치등
-데이터 보안및 품질팀: 기업의 데이터 품질이나 보안(DBMS내의 각종권한 정책 및 데이터 암호화등)에 대하여 관리
-전환팀 : 기존 NDB/HDB/RDB 의 데이터를 Cleansing및 매핑 하여 TO-BE 시스템으로 이관
3.DA의 각팀에는 반드시 권위있는 갑의 직원이 상주해야 한다.
4.DA의 각팀의 구성원은 반드시 해당하는 분야의 전문가 이어야 한다.
5.DA의 각팀은 반드시 긴밀히 협조해야 한다.

1번을 명시한 이유는 권력이 없는 수장이 관리 하는 DA 조직은 항상 불안하고 불필요한 논쟁이 끊이지 않을것이다.
예를 들면 표준화팀에서 표준을 배포해도 개발팀에서 이런저런 이유로 따르지 않을것이고 시간이 지나서 프로그램의 품질및 성능은 보장받지 못할것이며 그럴경우 관리자들은 누구의 책임인지 추궁하게 될것이다.

2번의 팀구성은 항상 7개의 팀으로 구성될 필요는 없다.
예를 들면 차세대 시스템의 분석단계에서는 표준화팀과 모델링팀은 필수적이고 나머지팀은 선택적일수 있다.
또한 개발단계에서는 튜닝팀과 데이터보안/품질팀이 필수적이며 이 필수적이며 세팅이 끝난 SYSTEM 팀은 선택적일수 있다.
OPEN 전단계에서는 전환팀이 필수적이다.
이처럼 단계별로 DA 조직을 늘였다 줄였다 할수 있으나 반드시 단계별로 필수적인 팀을 구성하여 운영하여야 한다.
또한 SYS ADMIN 팀은 사실상 DA 팀으로 볼수 없으나 운영하지 않을경우 DA 팀이 다치는 경우를 많이 보아왔다.
예전에는 요구가 거의 없다시피 했으나 최근에 특히 필수적으로 구성되는 팀이 데이터의 보안및 품질 팀이다.

3번을 명시한이유는 차세대프로젝트의 전형적인 문제점 때문이다.
다시말하면 갑의 요구와 컨설팅의 결과를 분석/설계/개발 하는것이 을의 할일 이지만 그것을 하기위해서는 반드시 갑의 도움이 있어야 한다.
예를 들면 모델러가 AS-IS 모델을 분석해야 할때 갑의 담당자가 없다면? 아마 팀마다 돌아다니면서 ERD 를 받아야 할것이다. 필자도 ERD 및 각종 분석에 필요한 문서를 수거하는데 3주나 걸린 뼈아픈 경험이 있다. 이것 때문에 WBS 상에 분석이 2주가 밀려 버렸다.
ERD와 각종문서를 를 받아야 할 당위성같은 문서를 작성해야 했으며 각종 문서별로 보안등급이 있어 부서마다 처음보는 업무요청서를 작성해야 했으며 부서중에서 1/3 정도는 보안관계상 문서를 주기 어렵다고 기각당하기 까지 했다.
반드시 권위있는 갑측 직원이 팀원으로 구성되어야 한다. 그리고 갑측직원은 반드시 전문가일 필요는 없다.

4번을 명시한 이유는 말하지 않아도 알것이다.
모델러를 잘못 선택한 순간 데이터의 구조가 엉망이 되고 튜너가 실력이 없으면 성능이 떨어지고 결국 DBMS 가 멎을 것이다. 또한 전환팀이 부실하면 데이터 이관작업이 몇년이 걸릴수도 있고 보안팀이 부실하면 OPEN 후에 해킹을 당할수도 있다. 똑똑한 DBA는 장애에 대한 계획을 세우고 장애시 모든데이터를 살린다.똑똑한 몇명이 전체 기업을 살릴수도 똑똑하지 못한 몇명이 전체 기업을 죽일수도 있다.

5번을 명시한 이유는 DA 팀의 각각의 팀원들은 전문가 들이고 또한 자존심 또한 매우 많은 편이다.
불필요한 논쟁을 하다보면 서로가 다치게 된다.
여러분도 알고있는 전형적인 논쟁을 예를들면 물리모델링 할때 모델러와 DBA 간의 싸움은 끝이없다.
최소한 같은 DA 조직내에 있는 팀원모두는 모를지라도 각팀의 PL 들은 서로를 잘알고 친해져야 할필요가 있다.
생각해보라 SQL 표준을 표준화팀에서 만들었고 그것을 어기면서 SQL 을 튜닝하는 튜닝팀이란...

1번부터 5번까지 반복적인 답변을 너무도 많이 하여 이제 는 외울(?) 지경이다
다행인것은 최근의 대형금융권 혹은 대형통신사의 차세대 프로젝트는 거의 필자의 구성법과 비슷하게 가고 있다는 것이다.
꼭 대형 업체(금융권이나 통신사)들의 차세대 시스템 구축이 아니더라도 여러분들이 IT 관련 일을 하고 있다면 나름대로 DA 팀을 구성 해보기 바란다.
어느자리에 누굴 쓸것인가? 혹은 내가 어느자리로 갈것인가?
DA 조직이란것이 21세기에 들어온이상 꿈같은 이야기만은 아닐것이다.

Jonathan Lewis 의 퀴즈 접근법

Jonathan Lewis 의 Cost-Based Oracle Fundamental 이라는 책을보면
퀴즈에 대한 Jonathan의 재미있는 접근법과 사고방식을 엿볼수 있다.
물론 SQL을 이용한 퀴즈문제 이다.

퀴즈는 두 수학자의 대화로 부터 시작하며 수학자2의 세딸아이의 나이를 맞추는 것이다.
Jonathan은 본퀴즈에 대한 자신의 접근법이 성능과는 무관하다는 것을 먼저 이야기 하였다.
퀴즈의 제약조건은 다른 언어(3GL 또는 4GL)의 도움없이 순수한 select 쿼리 하나로만 이루어져야 한다.
수학자1이 나이를 맞추어야 하며 수학자1이 힌트를 요구할때마다 수학자2가 적절한
힌트를 주는 방식으로 대화가 진행된다.

수학자1 : Do you have any children ?
수학자2 : Yes, three.
수학자1 : How old are they ?
수학자2 : Multiply their ages together and you get 36 --> 세딸의 나이를 곱하면 36임을 알수있다.
수학자1 : That's not enough information to work out the answer -->위의정보로는 부족하므로 힌트를 더 요구한다.
수학자2 :Add their ages together and the answer is the same as the number of people in this room --> 세딸의 나이를 더하면 이방의 사람수와 같음을 알수 있다.
수학자1 :That's still not enough information to work out the answer --> 그것만으론 부족해서 힌트를 더 요구한다.
수학자2 :The oldest daughter has a pet hamster with a wooden leg. -->가장 나이가 많은 딸이 햄스터를 키운다.
수학자1 :Thank you. I've got the answer. --> 수학자1이 드디어 정답을 맞추었다.

위의 대화를 종합하여 필자는 다음과 같은 결론을 내렸다.

1.세딸의 나이의 곱이 36 이다. 따라서 1*1*36 일수도 있고 1* 2* 18 일수 도 있고 나머지조합도 여럿일수 있다.
2.세딸의 나이의 합은 그방의 사람의 수와 일치하나 세딸의 나이의곱 = 36 and 세딸의 나이의 합이 같은 조합 >= 2
위의 조건으로도 조합이 여러개 생겨서 정답을 구할수 없으므로 수학자 1이 힌트를 더요구하고 있다.
3.가장 나이가 많은 딸이 한명임을 알수있다. 즉 막내와 둘째는 나이가 같을수 있지만 장녀는 둘보다 나이가 많다는 것이다.

select 문 한방으로 퀴즈를 풀수 있어야 한다.
이제부터 Jonathan의 방식대로 한줄 한줄씩 풀어보자.

먼저1~36 까지의 나이 LIST 를 만든다.
with age_list as ( select rownum age from all_objects where rownum <= 36),

1)위에서만든 LIST 로 곱이 36인 막내, 둘째, 첫째아이의 나이조합을 만들고
나이의 합과 곱도 만든다.
product_check as (
select age1.age as youngest,
age2.age as middle,
age3.age as oldest,
age1.age + age2.age + age3.age as summed,
age1.age * age2.age * age3.age as product
from age_list age1,
age_list age2,
age_list age3
where age2.age >= age1.age
and age3.age >= age2.age
and age1.age * age2.age * age3.age = 36 ),


2)세딸의 나이를 더하면 이방의 사람수와 같으나 그조합이 여러개임
summed_check as
(
select youngest, middle, oldest, summed, product
from ( select youngest, middle, oldest, summed, product,
count(*) over (partition by summed) ct
from product_check )
where ct > 1 --> 나이의 합의 조합이 2개 이상 나와야 한다.
)

3)가장 나이가 많은 딸이 한명임을 이용한 조건
select *
from summed_check
where oldest > middle;

With 문의 특징과 분석함수를 이용하여 단계적(절차적)으로 퀴즈를 풀고 있다.
유용하고 수학적인 접근법이다.

여러분이라면 더멋지게 퀴즈를 풀수 있지 않을까?

서브쿼리 팩토링(with 서브쿼리) 의 한가지 주의사항은 인라인뷰와는 다르게 no_merge 힌트등을 쓸필요가 없고 (꼭써야 한다면 서브쿼리에 Merterialize 힌트를 사용한다) 서브쿼리에 inline힌트를 써서 옵티마이져 자신이 서브쿼리를 최적화 한다는데 묘미가 있다고 Jonathan은 밝히고 있다.
아래 압축파일중에 9장 with_subq_01~02 부분을 보면된다. 꼭 테스트 해보기 바란다.
관련 SQL:http://www.apress.com/book/downloadfile/2450

히스토그램의 가상(virtual) 컬럼이용

개념 :
FBI (Function Based Index) 를 사용하고 히스토그램을 생성하면 오라클은 유져의 의지와는 상관없이 히스토그램에 가상컬럼을 사용한다.
가상컬럼은 11g 에서는 아예 컬럼값으로 인정하고 파티션및 인덱스도 생성할수 있게 되었다.
예를 들면 upper(컬럼1) 로 해서 컬럼을 생성할수 있는것이다.
하지만 11g 에서도 가상컬럼을 인덱스로 만들면 FBI 로 생성된다.
FBI 사용시 히스토그램에서 가상(virtual) 컬럼이용의 개념과 주의사항을 살펴본다.
아래예제는 오라클 10gR2 에서 테스트 하였다.

--테이블 생성
create table HIDDEN_COL_TEST (A varchar2(20) , B varchar2(100));

-- 데이터 생성
--컬럼 A 에는 분포도가 'a' 가 50%, 'A' 가 50% 로 생성한다.
insert into HIDDEN_COL_TEST
select 'a' , rpad('b',100) from all_objects
union all
select 'A' , rpad('b',100) from all_objects;

commit;

--통계정보생성
EXEC dbms_stats.gather_table_stats(user,'HIDDEN_COL_TEST',cascade=>true);

--인덱스 생성
create index HIDDEN_COL_IDX on HIDDEN_COL_TEST ( upper(A) ) ;

--인덱스 통계생성
EXEC dbms_stats.gather_index_stats(user,'HIDDEN_COL_IDX');

이제 준비가 다되었다.
explain plan for select * from HIDDEN_COL_TEST where upper(A) = :v_bind;
-->변수에 'a' 사용함.

위의쿼리의 경우 전체건이 조회된다.
Plan은 당연히 FTS( Full Table Scan) 으로 풀려야함에도 불구하고 옵티마이져는 우리의 기대를 져버린다.

select * from table(dbms_xplan.display);

---------------------------------------------------------------
Id Operation Name Rows
---------------------------------------------------------------
0 SELECT STATEMENT 792
1 TABLE ACCESS BY INDEX ROWID HIDDEN_COL_TEST 792
* 2 INDEX RANGE SCAN HIDDEN_COL_IDX 320
---------------------------------------------------------------

전체건수의 100% 에 해당하는 데이터를 오라클은 인덱스를 사용하였다.왜그럴까?
아래는 dynamic_sampling 을 최고수준으로 주었지만 별소용이 없었다.

explain plan for
select /*+ dynamic_sampling(HIDDEN_COL_TEST 10) */ *
from HIDDEN_COL_TEST where upper(A) = :v_bind;

select * from table(dbms_xplan.display);

---------------------------------------------------------------
Id Operation Name Rows
---------------------------------------------------------------
0 SELECT STATEMENT 792
1 TABLE ACCESS BY INDEX ROWID HIDDEN_COL_TEST 792
* 2 INDEX RANGE SCAN HIDDEN_COL_IDX 320
---------------------------------------------------------------

오라클이 비정상적으로 인덱스를 사용한 이유는 히스토그램에 가상컬럼을 생성시켜 주지 않았기 때문이다.

히스토그램을 조회해보자.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE table_name = 'HIDDEN_COL_TEST';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------- ------------------- ------------------------ ------------------------
HIDDEN_COL_TEST A 0 3.37499295804764E35
HIDDEN_COL_TEST B 0 5.09496674487288E35
HIDDEN_COL_TEST A 1 5.03652795277878E35
HIDDEN_COL_TEST B 1 5.09496674487288E35

아직 가상컬럼이 나타나지 않았다.

--테이블 통계정보를 다시생성한다.
EXEC dbms_stats.gather_table_stats(user,'HIDDEN_COL_TEST',cascade=>true);

히스토그램을 다시 조회해보자.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE table_name = 'HIDDEN_COL_TEST';

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------- ------------------- ------------------------ ------------------------
HIDDEN_COL_TEST A 0 3.37499295804764E35
HIDDEN_COL_TEST B 0 5.09496674487288E35
HIDDEN_COL_TEST A 1 5.03652795277878E35
HIDDEN_COL_TEST B 1 5.09496674487288E35
HIDDEN_COL_TEST SYS_NC00003$ 5585 3.37499295804764E35

히스토그램에 가상컬럼이 생성됬다.
이제 실행계획을 다시 생성시켜보자.
explain plan for select * from HIDDEN_COL_TEST where upper(A) = :v_bind;

select * from table(dbms_xplan.display);

-----------------------------------------------------
Id Operation Name Rows
-----------------------------------------------------
0 SELECT STATEMENT 79061
* 1 TABLE ACCESS FULL HIDDEN_COL_TEST 79061
-----------------------------------------------------

예상대로 실행계획이 정상으로 돌아왔다.

결론 :
FBI 생성시 반드시 테이블 통계정보를 다시생성해야 한다는걸 알수 있다.
그렇지않으면 가상컬럼이 히스토그램에 생성되지 않을 뿐만아니라 성능도 저하될수 있다는걸 반드시 기억하여야 한다.

참조 URL:
http://www.oracledba.co.uk/tips/collect_stats_subtle.htm

묵시적인 형변환을 피하라

SQL 에서 묵시적인 형변환(Implicit Datatype Conversion) 을 피하라.
위와같은 말을 언젠가 들어보았을것이다.
WHERE 절의 묵시적 형변환에 의한 Full table scan 은 잘알려져 있다 .
이문제는 Oracle 10g 부터 SQL 이 PL/SQL내에서 사용된다면 dbms_warning 패키지나 plsql_warnings 기능을 사용하면 식별될수 있다.
아래 예제를 보자.

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; --> warning 가능 활성화

SQL> CREATE TABLE t ( a VARCHAR2(10) ); --> varchar2 타입으로 컬럼생성
Table created.

SQL> CREATE OR REPLACE PROCEDURE p
2 IS
3 BEGIN
4 INSERT INTO t VALUES ( 10 ); --> number 타입으로 insert
5 END p;
6 /

SP2-0804: Procedure created with compilation warnings

SQL> SHO ERR
Errors for PROCEDURE P:

4/26 PLW-07202: bind type would result in conversion away from column type
-->4번째 라인에서 묵시적인 형변환이 일어나고 있음을 알려준다.

그런데 이런 묵시적인 형변환에의한 성능저하가 SQL에만 해당되는 이야기인가?
묵시적인 형변환에 의한 PL/SQL 자체의 성능도 한번쯤 의문을 가져볼 필요가 있다.

아래 예제는 데이터 타입선정을 잘못하면 프로그램이 얼마나 망가질수 있는지를 잘나타내고 있다.

create or replace procedure imp_type_conv_test is
x date;
y varchar2(12) := '01-MAR-03'
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 묵시적인 형변환
end loop; dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/

결과 : 826hsec

create or replace procedure exp_type_conv_test is
x date;
y x%type := to_date('01-MAR-03'); --> date 형으로 선언하고 미리 conversion 함
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 형변환이 일어나지 않음
end loop;
dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/

결과 : 38hsec --> 무려 2200% 가까이 성능이 향상됬다.

결론 :
Implicit Datatype Conversion 은 SQL 의 성능 뿐만아니라 PL/SQL 자체의 성능도 저하시킴을 알수 있다.
명시적으로 TO_DATE, TO_NUMBER 등의 함수를 사용함으로써 좀더 직관적이고
성능면에서도 유리한 프로그램을 개발할수 있다.

참조서적 : Mastering Oracle PL/SQL(Connor McDonald )

PL/SQL 에서 NUMBER 타입의 성능 테스트

10g 버젼까지는 소수점없는 숫자타입 연산에서 제일빠른 숫자타입은 pls_integer 였다.
이제 11g 에서 기존 pls_integer 의 sub type 인 simple_integer 이 나왔으므로 성능관점에서
어떤것이 가장빠른것인지 test를 해볼 필요가 있다.
PL/SQL 의 성능관점에서 11g 에 새로운 simple_integer 타입에 대하여 간단한 테스트를 진행하고자 한다.
진행하기전에 먼저 simple_integer 의 특징에 대해 간단히 설명한다.

simple_integer 의 특징
1)simple_integer 는 기존 pls_integer 의 sub type 이다.
2)simple_integer 의 사용범위는 2,147,483,648 ~ 2,147,483,647 이다.
3)null 값을 가지지 못한다. --> default 로 0 을 주어야 한다.
4)성능면에서 최적이다. 특히 native 로 컴파일 할때는 pls_integer 보다 4~5 배 빠르다.

아래는 number 와 pls_interger,simple_integer 를 비교실험하는 간단한 프로시져이다.
로직은 단순히 1씩 증가하는 연산이다.
성능테스트

CREATE OR REPLACE PROCEDURE plsql_number_test(v_loop in number) as
l_start NUMBER;
l_number NUMBER := 0;
l_number_incr NUMBER := 1;
l_pls_integer PLS_INTEGER := 0;
l_pls_integer_incr PLS_INTEGER := 1;
l_simple_integer SIMPLE_INTEGER := 0;
l_simple_integer_incr SIMPLE_INTEGER := 1;

BEGIN

-- number 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_number := l_number + l_number_incr;
END LOOP;

DBMS_OUTPUT.put_line('NUMBER : ' (DBMS_UTILITY.get_time - l_start) ' hsecs');

-- pls_integer 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_pls_integer := l_pls_integer + l_pls_integer_incr;
END LOOP;
DBMS_OUTPUT.put_line('PLS_INTEGER: ' (DBMS_UTILITY.get_time - l_start) ' hsecs');

--simple_integer 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_simple_integer := l_simple_integer + l_simple_integer_incr;
END LOOP;
DBMS_OUTPUT.put_line('SIMPLE_INTEGER: ' (DBMS_UTILITY.get_time - l_start) ' hsecs');

END plsql_number_test;
/

실행1
SQL> plsql_number_test(100000000 );

결과1
NUMBER : 1217 hsecs
PLS_INTEGER: 546 hsecs
SIMPLE_INTEGER: 512hsecs --> 가장빠르나 PLS_INTEGER 와는 거의 차이가 나지않는다.

NATIVE 모드로 컴파일 한다.
ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;
ALTER PROCEDURE plsql_number_test COMPILE;

실행2
SQL> plsql_number_test(100000000 );

결과2
NUMBER : 386 hsecs
PLS_INTEGER: 129 hsecs
SIMPLE_INTEGER: 32 hsecs -->PLS_INTEGER 보다 무려 4배정도가 빠르다.

결론 :
native 로 컴파일 했을때 모든 숫자형식이 다빨라졌지만 특히 SIMPLE_INTEGER의 성능향상이 눈에 뛴다.
2,147,483,648 ~ 2,147,483,647 범위내에서 소수점이 없는 숫자연산을 할경우에는
SIMPLE_INTEGER 를 써야 한다.
물론 10g 이하버젼 이라면 number 형보다 2~3 배 이상 빠른 PLS_INTEGER 를 사용해야 할것이다.
아쉬운점은 PL/SQL 뿐만 아니라 테이블의 데이터 타입으로도 사용할수 있으면 하는것이다.
12버젼에서 컬럼타입으로 사용할수 있게 되기를 기대해보면서 이글을 마친다.

참조 URL :
http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#simple_integer