1. 오라클 아키텍처의 이해
2. 싱글 커넥션을 사용하라
3. 바인드 변수를 사용하라
4. 바인드 변수 사용 여부 차이점 데모 (성능)
5. 바인드 변수 사용 여부 차이점 데모 (보안 / SQL Injection)
6. 동시성 제어의 이해
7. 플래시백
8. 읽기 일관성과 non-blocking 읽기
9. 읽기 일관성 사례 (재귀적 문장)
10. 데이터베이스 독립성 (비의존성)
11. 표준의 영향
12. 여러분이 할 수 있다는 것을 확신하라
13. 레이어 프로그래밍
14. 특징과 함수들
15. 새로운 기능 바로 알기
16. 문제를 단순하게 해결하기
17. 개방성
18. 애플리케이션 실행 속도를 빠르게 하는 방법
19. DBA와 개발자와의 관계
20. 정리

오라클 아키텍처의 이해

• SQL 서버와 같은 이기종의 데이터베이스로부터 오라클로 '이식(migration)' 된 애플리케이션 특징
• 대부분 'SQL 서버 코드를 컴파일하고 오라클에서 실행할 수 있도록 만들기 위한 최소한의 변경 대상이 무엇인가' 관점을 가짐
• 반대 방향 이식도 마찬가지 이며, 이런 유형의 애플리케이션에서 많은 문제 발생
• 최소한의 작업으로 데이터를 옮기고, 클라이언트/데이터베이스 레이어에서 최소한의 수정으로 SQL 서버에서와 같은 방식의 아키텍처를 사용하려고 함
  1. SQL 서버에서 사용하는 DB 연결 아키텍처를 그대로 오라클에서 사용함
  2. 개발자들은 리터럴(LITERAL) SQL을 사용함
• 시스템은 사용자의 부하를 견디지 못하고 심각한 성능 문제 발생

싱글 커넥션을 사용하라

• 일반적으로 SQL 서버에서는 동시에 실행되는 각각의 쿼리에 대해 하나의 커텍션을 독립적으로 연다 (5개 쿼리 수행 ==> 5개 커넥션 생성)
• 오라클에서는 몇개의 쿼리를 수행 하던지 하나의 커넥션만 유지 한다. (오라클에서는 다중 커넥션을 절대로 만들지 말아야 한다)
  • 간단한 웹 페이지 하나에 5, 10, 15 혹은 그 이상의 커넥션을 맺는 상황 (동시 사용 가능한 사용자 수를 1/5, 1/10, 1/15 로 줄이는 효과)

해결책

1. 한 페이지 생성하는데 1개의 커넥션만 사용 (동시 접속자 수 제한 문제 해소)
2. 대용량의 메모리 모델 사용 가능한 OS 사용 (윈도우 데이터센터 버전 등)
3. 다중 프로세스가 사용되는 윈도우즈 운영체제의 경우 가용한 모든 메모리를 사용하도록 작업

바인드 변수를 사용하라

• '확장성이 전혀 없는 오라클 애플리케이션 만들기' 책의 주제는 '바인드 변수를 사용하지 마라' ㅋㅋ

-- CASE1
select * from emp where empno = 123;
-- CASE2
select * from emp where empno = :empno;

• 바인드 변수 : 성능, 확장성, 보안 과 관련됨
  • 쿼리에 상수를 대입하여 사용하면, 데이터베이스는 모든 쿼리를 새로운 쿼리로 인식하여 각각 파싱/문법/보안 체크 및 옵티마이징을 하게 됨 (매번 컴파일 하게됨, 하드파싱)
  • 쿼리에 변수를 대입하여 사용하면, 한번 컴파일 된 후 SHARED POOL(라이브러리 캐시)에 저장되며, 동일한 쿼리가 실행될 때 재사용 됨 (소프트파싱)

• 하드파싱은 소프트파싱에 비해서 자원 소비가 증가됨, 차이는 라이브러리 캐시의 '래치 메커니즘(latching mechanism)' 때문에 발생
  • 쿼리를 하드파싱 할 때, 데이터베이스는 '래치(latches)' 라고 부르는 로우 레벨의 '직렬화 장치(serialization device)' 를 좀더 길게 붙잡고 있게 된다.
    • 래치 : 두 개의 세션이 동시에 동일한 데이터를 수정하려 할 때, 수정 중인 데이터를 읽을 때 데이터를 보호하는 역할
  • 더 길게, 더 자주 래치를 붙잡을 수록 래치를 가져오는 큐는 더 긴시간을 소비하게 되고 부족한 자원을 독점하기 시작하여 데이터베이스의 모든 기능들이 매우 느리게 동작하게 됨
  • 잘못 동작하는 작은 애플리케이션 하나만으로도 전체 애플리케이션의 성능에 큰 영향을 미칠 수 있음 (썩은 사과 하나가 바구니 전체의 사과를 망친다)
• 바인드 변수를 통해 소프트파싱(재사용) 하는 것이 효율적이며 데이터베이스 측면에서 추천됨 (더 적은 자원 사용, 래치를 더 적은 시간 점유 => 성능 향상, 확장성 증가)

바인드 변수 사용 여부 차이점 데모 (성능)

-- 준비
create table t ( x int );

create or replace procedure proc1
as
begin
	for i in 1 .. 10000
	loop
		execute immediate
		'insert into t values ( :x )' using i;
	end loop;
end;
/

create or replace procedure proc2
as
begin
	for i in 1 .. 10000
	loop
		execute immediate
		'insert into t values ( '||i||')';
	end loop;
end;
/

-- 수행
set serveroutput on

exec runstats_pkg.rs_start
exec proc1
exec runstats_pkg.rs_middle
exec proc2
exec runstats_pkg.rs_stop(10000)

-- 결과
Run1 ran in 26 cpu hsecs
Run2 ran in 255 cpu hsecs
run 1 ran in 10.2% of the time

Name                                  Run1        Run2        Diff
STAT...parse count (hard)                3      10,026      10,023
STAT...parse count (total)              20      10,056      10,036
STAT...consistent gets from ca          87      10,600      10,513
...
STAT...physical read total byt      90,112   1,490,944   1,400,832
STAT...cell physical IO interc      90,112   1,490,944   1,400,832
STAT...physical read bytes          90,112   1,490,944   1,400,832

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
76,865     959,781     882,916      8.01%

PL/SQL procedure successfully completed.

• 바인드 변수를 사용하지 않은 것이 바인드 변수를 사용한 것에 비해 10,000개의 로우를 입력하는데 훨씬 긴 시간이 필요하고, 훨씬 더 많은 자원을 차지함 (래치 20배 / 12.5배)
• 바인드 변수 없이 SQL 문을 실행 하는것 : 메서드를 호출하기 전에 서브루틴을 컴파일 하는것 (매번)
• 바인드 변수를 사용하면 성능이 좋아지고, 동시 접속자 수가 증가 된다.

바인드 변수 사용 여부 차이점 데모 (보안 / SQL Injection)

-- 준비 (상수, 바인드 변수 아님)
create or replace procedure inj( p_date in date )
as
	l_rec   all_users%rowtype;
	c       sys_refcursor;
	l_query long;
begin
	l_query := '
	select *
	  from all_users
	 where created = ''' ||p_date ||'''';

	dbms_output.put_line( l_query );
	open c for l_query;

	for i in 1 .. 5
	loop
		fetch c into l_rec;
		exit when c%notfound;
		dbms_output.put_line( l_rec.username || '.....' );
	end loop;
	close c;
end;
/

-- 실행 (입력 값 타입이 DATE 이므로 안전하다?)
exec inj( sysdate )

-- 준비
create table user_pw
( uname varchar2(30) primary key,
  pw    varchar2(30)
);
insert into user_pw
( uname, pw )
values ( 'TKYTE', 'TOP SECRET' );
commit;

-- 권한부여
grant execute on inj to scott;

-- 나쁜실행1
connect scott/tiger

alter session set
nls_date_format = '"''union select tname,0,null from tab--"';

SQL> select sysdate from dual;

SYSDATE
-------------------------------------
'union select tname,0,null from tab--

SQL>  exec ops$tkyte.inj( sysdate );

        select *
          from all_users
         where created = ''union select tname,0,null from
tab--'
USER_PW.....

PL/SQL procedure successfully completed.

-- 권한없음
SQL> select * from ops$tkyte.user_pw;
select * from ops$tkyte.user_pw
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

-- 나쁜실행2
SQL> alter session set
nls_date_format = '"''union select tname||cname,0,null from col--"';  2

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------------------------------
'union select tname||cname,0,null from col--

SQL> exec ops$tkyte.inj( sysdate );

        select *
          from all_users
         where created = ''union select
tname||cname,0,null from col--'
USER_PWPW.....
USER_PWUNAME.....

PL/SQL procedure successfully completed.

-- 나쁜실행3
SQL> alter session set
  2  nls_date_format = '"''union select uname,0,null from user_pw--"';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-----------------------------------------
'union select uname,0,null from user_pw--

SQL> exec ops$tkyte.inj( sysdate );

        select *
          from all_users
         where created = ''union select uname,0,null from
user_pw--'
TKYTE.....

PL/SQL procedure successfully completed.

-- 나쁜실행4
SQL> alter session set
nls_date_format = '"''union select pw,0,null from user_pw--"';
  2
Session altered.

SQL> select sysdate from dual
  2  /

SYSDATE
--------------------------------------
'union select pw,0,null from user_pw--

SQL>  exec ops$tkyte.inj( sysdate );

        select *
          from all_users
         where created = ''union select pw,0,null from
user_pw--'
TOP SECRET.....

PL/SQL procedure successfully completed.

-- 준비 (바인드 변수)
create or replace procedure NOT_inj( p_date in date )
as
	l_rec   all_users%rowtype;
	c       sys_refcursor;
	l_query long;
begin
	l_query := '
	select *
	  from all_users
	 where created = :x';

	dbms_output.put_line( l_query );
	open c for l_query USING P_DATE;

	for i in 1 .. 5
	loop
		fetch c into l_rec;
		exit when c%notfound;
		dbms_output.put_line( l_rec.username || '.....' );
	end loop;
	close c;
end;
/

-- 실행 (안전하다)
exec NOT_inj( sysdate )

-- 나쁜실행
connect scott/tiger

SQL> alter session set
  2  nls_date_format = '"''union select tname,0,null from tab--"';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------------------------
'union select tname,0,null from tab--

SQL>  exec ops$tkyte.NOT_inj( sysdate );

        select *
          from all_users
         where created = :x

PL/SQL procedure successfully completed.

• 바인드 변수를 사용하면 SQL Injection 으로 부터 안전하다.

동시성 제어의 이해

• 데이터베이스의 동시성 제어 구현을 이해하지 못하면 겪게될 일들
  • 데이터 무결성 오류
  • 사용자가 늘어가면서 점점 느려짐
  • 많은 수의 사용자들을 대응하지 못함 (자원에 접속하는 큐가 더 길어질수록 대기 시간은 더욱 더 길어짐)
• 동시성 문제는 멀티 쓰레드 프로그램을 디버깅 하는 것과 비슷하게 찾아내기 가장 어려운 문제

락킹 구현하기

• 데이터베이스는 특정 시간에 한 개의 트랜잭션이 하나의 데이터를 수정하는 것을 보장하기 위해 락을 사용
• 락은 동시성을 보장하는 메커니즘
  • 같은 로우에 대해 동시 업데이트를 방지 함 으로서 다중 사용자 접근 가능
  • 락을 과도하거나 부적절하게 사용하면, 동시에 수행 가능한 트랜잭션이 제한 됨
• 확장성 있는 정확한 애플리케이션 개발을 위해 동작 원리 이해 필요

• 각각의 데이터베이스는 락을 서로 다른 방법으로 적용 (페이지 레벨, 로우 레벨, 에스컬레이션 여부, 락 읽기 사용 여부 등)
  • 데이터베이스별 미묘한 차이점 이해 필요

오라클 락 정책

• 데이터를 수정할 때 로우 레벨 락 사용 (에스컬레이션 없음)
• 단순 읽기시 락 없음
• 데이터 쓰기 작업은 데이터 읽기 작업을 막지 않음 (다른 제품과 근본적 차이)
• 데이터 쓰기 작업은 이미 락이 걸린 로우에 대해 다른 쓰기를 할때만 차단

Lost updates 방지하기

• 오라클의 non-blocking 접근법의 부작용 : 한 번에 한 로우의 데이터에 한 명 이상의 사용자가 접근하지 않도록 락 이외에 추가적으로 해주어야 할 일이 있음

자원 스케쥴링 프로그램 동시성 사례

• 주어진 시간 동안 한 명 이상의 사람에게 자원을 할당하지 않음
• 다른 사람이 예약한 시간대를 검사하는 코드 포함 (원하는 시간대에 중복된 데이터가 없으면 새로운 데이터 입력)

-- 준비
create table resources
( resource_name varchar2(25) primary key,
  other_data    varchar2(25)
);
create table schedules
( resource_name varchar2(25) references resources,
  start_time    date,
  end_time      date
);

insert into resources
( resource_name, other_data )
values
( 'conference room', 'xxx' );
commit;

-- SCHEDULES 예약 데이터 입력 후 검사 (1이면 예약 가능, 1이상 이면 예약 불가)

-- 세션1
insert into schedules
( resource_name, start_time, end_time )
values
( 'conference room',
   to_date( '01-jan-2010 9am', 'dd-mon-yyyy hham' ),
   to_date( '01-jan-2010 10am', 'dd-mon-yyyy hham' )
);

variable resource_name varchar2(25)
variable new_start_time varchar2(30)
variable new_end_time varchar2(30)

set autoprint on
alter session set nls_date_format = 'dd-mon-yyyy hh:miam';
begin
    :resource_name := 'conference room';
    :new_start_time := to_date( '01-jan-2010 9:30am', 'dd-mon-yyyy hh:miam' );
    :new_end_time := to_date( '01-jan-2010 10:00am', 'dd-mon-yyyy hh:miam' );
end;
/

select count(*)
  from schedules
 where resource_name = :resource_name
   and (start_time < :new_end_time)
   AND (end_time > :new_start_time)
/

-- 세션2
insert into schedules
( resource_name, start_time, end_time )
values
( :resource_name,
   to_date( :new_start_time ),
   to_date( :new_end_time )
);

select count(*)
  from schedules
 where resource_name = :resource_name
   and (start_time < :new_end_time)
   AND (end_time > :new_start_time)
/

-- 커밋이 일어나기 전에 다른 세션에서 변경된 상황이 보이지 않고, 같은 데이터를 수정하지 않았기 때문에 서로 블로킹도 없음
-- 비지니스 룰과 안맞는 데이터 발생

-- 해결책 : COUNT(*) 전 직렬화 처리 (FOR UPDATE)
-- 자원(방)을 스케쥴링하기 전에 그것에 락을 걸어서 다른 사용자가 동시에 변경하지 못하도록 보장
-- FOR UPDATE 는 변경과 마찬가지로 다른 사람이 데이터를 읽는것을 방해하지 않는다
select * from resources where resource_name = :resource_name FOR UPDATE;

• 이러한 락킹 이슈들은 이기종 데이터베이스 간에 애플리케이션을 이식 할 때 많은 영향을 미침
  • 쓰기가 읽기에 락을 걸거나, 읽기가 쓰기에 락을 거는 데이터베이스 사용시 무결성 문제는 편리하나 동시성이 부족하다 (오라클 외의 대부분 데이터베이스 작동 방식)
• 예를들어 하이버네이트 애플리케이션의 트랜잭션 관리는 단순히 커밋/롤백을 지원 하는 것이며, 생성된 코드가 일관성 있는 트랜잭션을 한다는 의미는 아님
  • 따라서 동시성 제어에 대한 지식은 필수

멀티버저닝

• 오라클의 동시성 제어와 밀접한 관계가 있음
• 오라클은 다중 버전, 읽기 일관성 모델을 기본으로 제공

오라클 데이터베이스의 동시성 관련 핵심 개념

• 읽기 일관성 쿼리 - 각 시점별로 일관된 결과를 추출 (추출 중 다른 사람이 수정하거나 락을 걸어도)
• NON-BLOCKING 쿼리 - 읽기 처리는 쓰기 처리에 의해 절대 블로킹 되지 않음

멀티버저닝 사례

drop table t purge;

create table t
as
select *
  from all_users
/
set autoprint off
variable x refcursor;

begin
    open :x for select * from t;
end;
/

declare
    pragma autonomous_transaction;
    -- you could do this in another
    -- sqlplus session as well, the
    -- effect would be identical
begin
    delete from t;
    commit;
end;
/

print x -- 결과나옴
select * from t -- 결과없음

플래시백

• 오라클은 항상 쿼리 결과가 일관되도록 특별한 하나의 시점을 결정
  • 쿼리가 오픈된 시점 - READ COMMITTED 기본 동작
  • 쿼리가 속해 있는 트랜잭션이 시작되는 시점 - READ ONLY 와 SERIALIZABLE 의 기본 동작
• 9i 부터 나온 플래시백 쿼리 기능은 'AS OF' 절을 통해서 과거 특정 시점 지정 가능

• 데모 (SCN / System Change/Commit Number)

connect / as sysdba
grant execute on dbms_flashback to scott;

connect scott/tiger
alter table emp enable row movement;
variable scn number
exec :scn := dbms_flashback.get_system_change_number;
print scn

       SCN
----------
9.4261E+10

select count(*) from emp;

  COUNT(*)
----------
        14

delete from emp;

select count(*) from emp;

  COUNT(*)
----------
         0

-- 특정 시점 조회 가능
select count(*),
       :scn then_scn,
       dbms_flashback.get_system_change_number now_scn
  from emp as of scn :scn;

  COUNT(*)   THEN_SCN    NOW_SCN
---------- ---------- ----------
        14 9.4261E+10 9.4261E+10

commit;

-- 같은 쿼리로 서로 다른 두 시점 데이터 조회 가능
select cnt_now, cnt_then,
       :scn then_scn,
       dbms_flashback.get_system_change_number now_scn
  from (select count(*) cnt_now from emp),
       (select count(*) cnt_then from emp as of scn :scn)
/

   CNT_NOW   CNT_THEN   THEN_SCN    NOW_SCN
---------- ---------- ---------- ----------
         0         14 9.4261E+10 9.4261E+10


-- 테이블 상태를 특정 시점으로 원복
SQL> flashback table emp to scn :scn;

Flashback complete.

select cnt_now, cnt_then,
       :scn then_scn,
       dbms_flashback.get_system_change_number now_scn
  from (select count(*) cnt_now from emp),
       (select count(*) cnt_then from emp as of scn :scn)
/

   CNT_NOW   CNT_THEN   THEN_SCN    NOW_SCN
---------- ---------- ---------- ----------
        14         14 9.4261E+10 9.4261E+10

connect /

읽기 일관성과 non-blocking 읽기

• 사례분석 (한 개의 데이터베이스 블록 마다 한 개의 로우를 저장 가정)

-- 준비
create table accounts
( account_number number primary key,
  account_balance number
);

insert into accounts values (123, 500); -- 로우1
insert into accounts values (234, 250); -- 로우2
insert into accounts values (345, 400); -- 로우3
insert into accounts values (456, 100); -- 로우4

-- 은행 잔고 일마감 리포트 : 1250
select sum(account_balance) from accounts;

로우 2, 3 을 읽는 동안, 계좌 123 에서 456 으로 400 을 이체 가정

• 로우 4에서 500 을 읽고, 1650 을 응답 한다? ==> 오류
• 다른 많은 데이터베이스에서 이 쿼리에 대해 일관적이고 정확한 결과를 얻으려면, 합계를 계산하는 기간 동안 전체 테이블 혹은 읽고 있는 여러 로우에 대해 락을 걸어야 함 (공유 읽기 락)
  • 락을 통해 읽고 있는 동안 다른 사람이 값을 변경하는 것을 방지
  • 테이블 락 : 쿼리가 시작되는 시점에 데이터베이스에 있는 값을 응답
  • 데이터 락 : 쿼리가 종료되는 시점에 실제 데이터베이스에 있는 값을 응답
  • 많은 로우를 가진 테이블의 경우 시간이 소요될 수 있으며, 데드락 발생 가능성 있음
• 오라클은 읽기 일관성을 통해 이런 오류가 없음
  • 멀티버저닝 사용
  • 'shared read' 락 없음, 동시성 작업을 방해하는 것 중 제거될 수 있는 것은 모두 제거됨
  • 데이터 수정시 두 개의 다른 위치에 엔트리 생성 (undo, redo)
  • 블록들을 읽을 때, 쿼리가 시작하는 시점의 블록을 복구하기 위해 undo 사용
• 오라클 처리 순서 (T6 에서 UNDO 사용 하여 과거 값 추출)

• 읽기 일관성 데이터는 항상 SQL 문 레벨에서 수행됨 (SQL이 시작한 시점의 올바른 데이터를 보여줌)

읽기 일관성 사례 (재귀적 문장)

-- 결과 예측
for x in (select * from t)
loop
  insert into t values (x.username, x.user_id, x.created);
end loop;

-- SELECT * FROM T 결과는 쿼리가 시작될 때 결정되며, INSERT INTO T 에 의해 생성된 데이터를 볼 수 없음
-- 따라서 무한 루프 없으며 이는 아래 도 마찬가지 임 (몇몇 데이터베이스는 재귀적 문장은 허용하지 않음)
INSERT INTO T SELECT * FROM T;

• 오라클의 능력을 극대화하고 정확한 코드를 구현하기 위해서, 다른 데이터베이스에 어떻게 되어 있는지가 아니라 오라클에 존재하는 이러한 이슈에 대해 이해 필요

데이터베이스 독립성 (비의존성)

• 매우 확장성이 높은 데이터베이스 독립적인 애플리케이션을 구축하는 것은 매우 어려움
  • 각각의 데이터베이스가 어떻게 작동하는지 세세한 부분까지 자세하고 정확히 알고 있지 않으면 불가능
  • 알고 있다고 해도 데이터베이스 독립성은 어렵다는 것을 알것임 (논쟁거리)
• A 와 B 는 다르다
  • 데이터베이스 A 에서 완벽하게 동작하던 프로그램이 데이터베이스 B에서 이상하게 동작 한다면, 단지 다르게 작동하는 것이다.
• 다르게 동작하는 사례

-- 오라클 에서는 l_some_variable 이 NULL 이면 어떤 데이터도 안나옴, Sybase 나 SQL 에서는 그렇지 않음
declare
  l_some_variable varchar2(25);
begin
  if ( some_condition )
  then
    l_some_variable := f( ... );
  end if;

  for x in ( select * from t where x = l_some_variable )
  loop
  ...

-- NULL 비교 차이
SQL> select * from dual where null = null;

no rows selected

SQL> select * from dual where null <> null;

no rows selected

SQL> select * from dual where null is null;

D
-
X

• 모든 데이터베이스는 ANSI를 따르지만 여전히 다르게 동작 한다 (ANSI 표준 따른 다는것은 ANSI 표준 100% 지원이 아님)
  • 모호성, 이전 기능과의 호환성 (SQL 최신 버전은 NULL 비교시 ANSI 방법 지원 하지만 기본은 아님)

-- 해결책1
select *
  from t
 where ( x = l_some_variable OR (x is null and l_some_variable is NULL ))

-- 하지만, x 컬럼 인덱스가 있다면 오라클과 SQL 서버는 여전히 다르게 동작 (오라클 B*Tree 인덱스는 NULL을 인덱스에 포함하지 않음)
-- 해결책2 : X에 실제상황에서 발생하지 않는 어떤 값을 할당 (FBI)
select * from t where nvl(x, -1) = nvl(l_some_variable, -1)
create index t_idx on t( nvl(x, -1) );

• 데이터베이스들은 서로 다르다, 근본적 뿐 아니라 사소한 차이점에 대해서도 대비 필요
• 사소한 차이점(NULL 처리)이 근본적인 차이점(동시성 제어 메커니즘)만큼 큰 영향을 미칠 수 있음
• 데이터베이스가 어떻게 동작하고, 이러한 문제들을 해결하기 위해 어떤 기능들을 적용하고 있는지 알아야 함

표준의 영향

SQL99 : 데이터베이스를 위한 ANSI/ISO 표준

• 과거 SQL89 ANSI/ISO, SQL92 ANSI/ISO 있었고 현재 SQL2003, SQL2008 로 갱신 됨
• 데이터베이스가 어떻게 동작할 것인지 말해주는 언어(SQL)와 동작(트랜잭션, 고립화 레벨)을 정의
• Core, Enhanced 두가지 적합성 정의 (인증 받은 데이터베이스 없음)

SQL92 Level

1. Entry : 대부분 벤더들이 따르고 있음, SQL89를 약간 개선, 어떤 벤더도 이보다 더 높은 레벨에 대해 인증 받은 바 없음 (NIST 는 더 이상 인증을 하지 않음)
2. Transitional : 기능 집합 측면에서 Entry 와 Intermediate 중간
3. Intermediate : 괄호를 포함하는 기능 추가 (동적SQL, CASCADE DELETE, DATE/TIME 데이터타입, 도메인, 가변 길이 문자열, CASE 문, 데이터타입 간 CAST 함수 등)
4. Full : 괄호를 포함하는 기능 추가 (커넥션 관리, BIT 문자열 데이터타입, 유예 가능 무결성 제약, FROM 절에 DERIVED 테이블, CHECK 절에 서브쿼리, 임시 테이블 등)
   • SQL92 관련 책은 SQL92 전체 기능을 구현한 이론적인 데이터베이스 모습을 설명
   • SQL92 책의 모든 내용을, SQL92 데이터베이스에 단순 적용 하는것은 불가능 (SQL92 표준의 entry-level 수준 이상은 어려움)

벤더에 특화된 기능을 사용하는 것을 두려워 하지 말자

• 특화된 기능을 사용하는 데 이미 많은 비용을 지불하고 있다.
• 모든 데이터베이스는 그들 자신만의 기술을 제공, 사용중인 데이터베이스의 최고의 기능을 사용 하자
• 데이터베이스 변경시 컴포넌트들을 다시 적용하고, 이런 변경점들과 상관없이 동작하는 좋은 프로그램 기술 사용하자

여러분이 할 수 있다는 것을 확신하라

• 오라클의 기능은 윈도우즈/유닉스 두개 플랫폼에서 사용 가능하지만, 매우 다른 방식으로 구현됨
• 여러 데이터베이스에서 기능해야만 하는 애플리케이션도 마찬가지임
  • UNIQUE KEY

• 각 데이터베이스별로 서로 다른 기능에 대해 대처할 수 있는 방법
  1. 유일 키를 생성하는 모든 데이터베이스에 독립적인 메서드를 개발

-- 준비
create table id_table
( id_name  varchar2(30) primary key,
  id_value number );
insert into id_table values ( 'MY_KEY', 0 );
commit;

-- 새로운 키 얻기
update id_table
   set id_value = id_value+1
 where id_name = 'MY_KEY';

select id_value
  from id_table
 where id_name = 'MY_KEY';

-- 한 번에 한 사용자만 트랜잭션 처리 (키 생성 작업의 직렬화)
-- 오라클(SERIALIZABLE)에서는 이 작업을 동시에 수행하려 하는 첫 번째 사용자는 "ORA-08177: can't serialize access for this transaction" 오류 발생

-- 세션1
set transaction isolation level serializable;

update id_table
   set id_value = id_value+1
 where id_name = 'MY_KEY';

select id_value
  from id_table
 where id_name = 'MY_KEY';

-- 세션2
set transaction isolation level serializable;

update id_table
   set id_value = id_value+1
 where id_name = 'MY_KEY';

-- 세션1
commit;

-- 세션2 (세션1의 commit 여부와 관계 없이, 다른 세션에서 수정한 자료를 수정하려고 할 때 발생)
ORA-08177: can't serialize access for this transaction

-- 데이터베이스 독립적인 로직은 실제 전혀 독립적이지 않다, 고립 레벨에 따라 한 개의 데이터베이스에서 조차 잘 동작하지 않는다 (상황에 따라 블로킹 되거나, 에러가 발생)

• 각각의 데이터베이스에서 키를 생성할 때 서로 다른 수행 방법을 수용하고 다른 기술을 사용

-- 오라클 최적화
create table t
( pk number primary key,
  other_data varchar2(20)
)
/
create sequence t_seq;

create trigger t before insert on t
for each row
begin
	:new.pk := t_seq.nextval;
end;
/

레이어 프로그래밍

• 좋은 이식성을 위해 데이터베이스 액세스를 계층으로 작성
  • 하나의 데이터베이스 환경 이라면 SQL을 직접 코딩 하는것이 좋을 수 있음
  • 이식성과 더 나은 성능을 위해서 결과집합을 반환하는 저장 프로시저 사용 (데이터베이스 별로 조금씩 다름)
  • 결과집합을 반환하는 저장 프로시저 안씀 (모든 데이터베이스에서 동작하는 SQL 사용)
  • 다른 데이터베이스마다 다른 코드를 구현 하고 저장 프로시저 사용
  • 모든 데이터베이스에서 완벽하게 동작하는 SQL을 찾는 대신 해당 데이터베이스에 최적화된 SQL 작성
• 적용할 데이터베이스에 특화된 프로그램 코드를 개발하는 방식의 이슈
  • 오라클, SQL 서버, DB2 세 가지만 한정해도, 데이터베이스들 간에 미묘한 차이를 충분히 이해할 정도로 유능한 개발자를 찾기가 사실상 불가능
• 다중 플랫폼 코드를 개발하는 개발자가 사용하는 기술
  • 오라클 개발시 이 기술 사용하며, 각 플랫폼에 특화되어 구현된 OSD(Operating System Dependent) 코드 존재
  • 이런 추상화 레이어를 사용하여 데이터베이스 자체 코드를 수정하지 않고 성능과 통합을 위해 많은 운영체제 본래 특징 사용
• 하나의 데이터베이스에서 애플리케이션을 집어서 다른 데이터베이스에 떨어뜨리는 것(무작정 이식하는 것)은 표준에 100% 맞추어 개발했더라도 많은 어려운 작업과 노력이 든다

특징과 함수들

• 반드시 데이터베이스 독립적으로 만들려고 할 필요가 없다 = 데이터베이스가 제공하는 것이 무엇인지 이해하고 그것을 완전히 사용해야 한다
• 오라클에서 만 페이지가 넘는 자료를 제공하고 있으며, 모든 특징과 기능을 다룬다는 것은 정말 힘든 일 이지만 이 책은 각 릴리즈에서 제공하는 기능에 대해 최소한의 지식 제공
• 데이터 복제를 구현을 원하는 사람
  • INSERT INTO A_TABLE SELECT * FROM A_TABLE 을 동시에 실행 (처음 100개 로 시작 하나, 결과는 커밋 시점에 따라 300 OR 400 개)
  • 데이터 복제는 구현은 쉬운 일이 아니고 매우 어렵다, 오라클은 10년 이상 데이터 복제 기능을 제공 하고 있다, 그걸 쓰자. (빠르고, 쉽고, 견고함)

새로운 기능 바로 알기

• 데이터베이스에서 사용할 수 있는 기능에 대해 완전히 이해하지 못하면, 오히려 독이 된다
• 사례1 : 인라인 뷰, 분석 함수, 스칼라 서브쿼리 등의 SQL 문의 기능을 모르는 개발자
  • 한 개의 부모 테이블과, 두 개의 자식 테이블에 있는 데이터 분석 필요
  • 미들 티어에 그들 자신이 만든 보잘 것 없는 데이터베이스를 만듦 (nested join 과 비슷한 기능을 수행하는 데이터베이스 재개발)
  • 대부분의 개발 시간이 이 리포트 '엔진' 개발에 소모됨
  • 해결책 제시

-- 쿼리 안에 쿼리를 실행하는 인라인 뷰
select p.id, c1_summ1, c2_sum2
  from p,
  (select id, sum(q1) c1_sum1 from c1 group by id) c1,
  (select id, sum(q2) c2_sum2 from c2 group by id) c2
 where p.id = c1.id
   and p.id = c2.id
/

-- 각 로우마다 서로 다른 쿼리를 실행하는 스칼라 서브쿼리
select p.id,
       (select sum(q1) from c1 where c1.id = p.id) c1_sum1,
       (select sum(q2) from c2 where c2.id = p.id) c2_sum2,
  from p
 where p.name = '1234'
/

-- WITH 절을 활용한 서브쿼리 팩토링
with c1_vw as
     (select id, sum(q1) c1_sum1 from c1 group by id),
     c2_vw as
     (select id, sum(q2) c2_sum2 from c2 group by id),
     c1_c2 as
     (select c1.id, c1.c1_sum1, c2.c2_sum2
        from c1_vw c1, c2_vw c2
       where c1.id = c2.id)
select p.id, c1_sum1, c2_sum2
  from p, c1_c2
 where p.id = c1_c2.id
/

최종 목표는 미들 티어를 걷어내는 것

• 사례2 : 파이프(데이터베이스 IPC 메커니즘)의 메시지를 읽는 오라클 데이터베이스 안에 데몬 프로세스를 개발하여 구동
• 데몬 프로세스들은 파이프 메시지 안에 있는 SQL을 실행하고 작업 결과를 커밋 한다. (감사 기능과 오류 로깅 기능 수행)
  • 다른 프로세스에 메시지를 보내는 방법으로 부모 트랜잭션을 롤백하더라도 분리된 트랜잭션이 작업을 수행하고 커밋할 수 있게 한다. (8i 이전)
• 해결책 : autonomous transaction
• 데이터베이스가 이미 제공하는 기능(해법)에 대해 직접 개발 하느라 시간을 소모하는 이런 종류의 예들은 많이 있다.

문제를 단순하게 해결하기

• 문제 해결 : 쉬운길 / 어려운길
  • 사람들은 대개 일부러 어려운 길을 택하는데, 몰라서 그럴 수 있음 (데이터베이스가 '그것' 을 할 수 있다고 기대하지도 않는다.)
  • 필자는 데이터베이스가 전부 할 수 있다고 기대하고, 절대로 불가능 함이 확인되면 어려운 길을 택함
• 예) 사용자가 데이터베이스세션을 최대 하나만 가지도록 강제하기
  • 어려운길 : V$SESSION 모니터링 후 세션을 죽이는 배치 잡 돌림, 사용자가 로그인 하면 로우를 입력하고 로그아웃 하면 삭제, 등등...
  • 쉬운길 : PROFILE 사용
    
    

set echo on
connect /
create profile one_session limit sessions_per_user 1;
alter user scott profile one_session;
alter system set resource_limit=true;
connect scott/tiger
host sqlplus scott/tiger


connect /
alter user scott profile default;
drop profile one_session;

• '완벽한 아키텍처', '완벽한 언어', '완전한 처리 방법' 은 존재하지 않으며 그때그때 다름
• 언제든 복잡한 문제를 해결할 수 있는 가장 간단한 아키텍처 선택

개방성

일부러 힘든 방법으로 일하는 사람들 : 개방성, 독립성

• 저장 프로세스나, 시퀀스 같이 간단한 기능이라도 폐쇄적이고 해당 데이터베이스에서만 작동하는 기능을 피함 (종속 방지)
• 하지만 아무런 변경 없이 이기종 데이터베이스로 이식하는 것은 매우 드문 일임 (NULL = NULL)

PL/SQL 로 비지니스 로직을 구현 했기에 데이터베이스 종속적이 되어 버렸다?

• 시스템에 종속될 수밖에 없는 언어를 사용했음 : JAVA
• 시스템에 종속될 수밖에 없는 컴포넌트 기술을 선택했음 : J2EE
• 한 개의 플랫폼에서만 사용 가능한 웹 서버를 선택했음 : IIS
  • 각각의 기술은 특정한 프로그램만 사용할 수 있는 것임, 운영 체제 관점에서 다양한 선택을 제공할 수 있는 유일한 기술은 데이터베이스 뿐

애플리케이션 로직과 더 중요한 보안을 데이터베이스 외부에 둠

• 결과는 데이터베이스를 막아버린 것이며, 그것을 '비개방' 적으로 만들었음 (특정 메서드를 이용하거나 보안을 모두 건너 뛰어야 함)
• FGAC(fine-grained access control) 기능 제공 (데이터베이스 안에서 접근 제어)
  • 사용예1) 정상적인 업무 시간 외의 시간에 특정 권한을 가진 사용자에 의해 실행된 쿼리는 응답하지 않는다
  • 사용예2) 모든 데이터는 보완 장치가 있는 터미널에 응답될 수 있고, 원격 클라이언트 터미널에는 민감하지 않는 정보만 응답

누가 더 개방적?

• 비주얼 베이직 코드와 ActiveX 컨트롤을 호출해야만 데이터에 접근 가능
• 다양한(ODBC, JDBC, OCI 등) API를 사용하는 등 데이터베이스에 질의를 던질 수 있는 모든 것이 접근 가능

• 어떤 데이터베이스를 사용하든 간에 그 제품으로부터 나올 수 있는 기능 마지막 하나까지 짜내면서 완벽하게 이용해야 함
• 데이터베이스 독립성 < 데이터베이스 기능 활용하여 5배 빠르게 수행

애플리케이션 실행 속도를 빠르게 하는 방법

• '빠른 것 = 옳은 것'
• 80% 이상의 성능 향상은 애플리케이션 설계와 구현 레벨에서 실현됨
• CURSOR_SHAREING=FORCE (데이터베이스 레벨에서 개선)

connect /
set echo on

select /* TAG */ substr( username, 1, 1 )
  from all_users au1
 where rownum = 1;

SU
--
S

alter session set cursor_sharing=force;

select /* TAG */ substr( username, 1, 1 )
  from all_users au2
 where rownum = 1;

SUBSTR(USERNAME,1,1)
------------------------------------------------------------
S

select sql_text from v$sql where sql_text like 'select /* TAG */ %';

SQL_TEXT
--------------------------------------------------------------------------------
select /* TAG */ substr( username, 1, 1 )   from all_users au1  where rownum = 1
select /* TAG */ substr( username, :"SYS_B_0", :"SYS_B_1" )   from all_users au2
  where rownum = :"SYS_B_2"

• 하드파싱을 극적으로 줄여줌 (래치가 줄어듬),
• 하지만 부작용 있음 : 두번째 실행한 쿼리에서 결과 컬럼이 커짐
  • 쿼리에서 정보(상수)를 제거 함으로서 길이가 1인 SUBSTR 임을 알 수 없게 됨, 1개 레코드 추출 함을 알 수 없게 됨 (실행 계획에 영향)
• 애플리케이션에서 바인드 변수를 사용하는 것이 정답

• 데이터베이스 레벨에서 몇가지 옵션이 있지만 많지 않다, 성능 개선도 많지 않다.
• 애플리케이션을 고친다면 큰 성능 향상을 얻을 수 있다.

DBA와 개발자와의 관계

• 성공적인 정보 시스템은 DBA 와 애플리케이션 개발자 간에 협력 관계에 근거 함

• DBA는 개발자를 방문하고(애플리케이션 튜닝), 개발자는 DBA를 찾는다(데이터베이스 튜닝). (같은 퍼즐의 다른 조각에서 같이 일하는 것과 같음)

정리

• 오라클 아키텍처에 대해 이해 (특정 기능 활용시 관련 지식의 충분한 이해)
• 락과 동시성 이해, 각각의 데이터베이스 구현이 다른 것에 대한 이해
• 블랙박스처럼 다루지 않기
• 기본적으로 제공하는 기능 사용하기
• 문제를 간단한 방법으로 해결하기
• 사용할 수 있는 데이터베이스 기능 사용하기
• 데이터는 영원히 존재한다, 데이터가 중요하다.
  • 애플리케이션에 비정형 쿼리 불가능, 낡은 애플리케이션에 새로운 애플리케이션 구축 불가능