-- CASE1
select * from emp where empno = 123;
-- CASE2
select * from emp where empno = :empno;
래치
오라클이 사용하는 공유 데이터 구조에 '직렬화 접근(serialize access)'을 하기 위해 사용되는 가벼운 락의 일종
공유 풀(shared pool)은 SGA(System Global Area)에 존재하는 매우 큰 공유 데이터 구조 이며 컴파일한 SQL을 저장하는 공간, 수정시 한개의 프로세스만 허용 해야 함, 이때 래치 사용
하드파싱이 많아 질 수록 공유 풀, 라이브러리 캐시를 위해 사용되는 래치를 획득하기 위해 기다리게 되고, 큐에 대기하는 것이 더 길어져 대기 시간이 더 길어진다
-- 준비
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.
SQL Injection
상수를 입력받아 원래의 쿼리에 결합시켜서 컴파일하고, 그 쿼리를 실행 할 빌미를 제공하는 보안의 구멍
고객으로부터 SQL 코드 조각을 받아서 컴파일하고 실행 하게 되며, 고객이 SQL 문을 수정해서 의도하지 않은 방향으로 애플리케이션을 조작하게 할 위험이 생김
-- 준비 (상수, 바인드 변수 아님)
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.
동시성 제어(currency control)
데이터베이스 자체를 구별하고, 데이터베이스를 파일 시스템과 구별하고, 데이터베이스 간에 서로 구별하는 분야
-- 준비
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;
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 -- 결과없음
플래시백 데이터 아카이브
여러 달 또는 수년 전의 데이터를 조회할 수 있는 플래시백 데이터 아카이브는 아카이브 속에 들어 있는 자료의 이전 이미지 복사본을 사용함 (읽기 일관성과 멀티버저닝 안씀)
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 /
-- 준비
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;
| 시간 | 쿼리 | 계좌 변경 트랜잭션 |
|---|---|---|
| T1 | 로우 1 읽는다: 잔액(500), 합계(500) | |
| T2 | 로우 1 업데이트: 로우 1에 배타적 락 설정, 잔액(100) | |
| T3 | 로우 2 읽는다: 잔액(250), 합계(750) | |
| T4 | 로우 3 읽는다: 잔액(400), 합계(1150) | |
| T5 | 로우 4 업데이트: 로우 4에 배타적 락 설정, 잔액(500) | |
| T6 | 로우 4 읽는다: 잔액(100), 합계(1250) | |
| T7 | 트랜잭션 커밋 | |
| T8 | 결과출력: 합계(1250) |
-- 결과 예측
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;
-- 오라클 에서는 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
-- 해결책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) );
| 데이터베이스 | 기능 |
|---|---|
| 오라클 | 시퀀스, SYS_GUID() |
| 인포믹스 | SERIAL |
| Sybase, SQL 서버 | IDENTITY |
-- 준비
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;
/
-- 쿼리 안에 쿼리를 실행하는 인라인 뷰
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
/
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;
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"
만병통치약
ALTER SYSTEM SET FAST = TRUE;
이런거 없음, 있다면 기본적으로 제공 될것임
| 항목 | DBA(SA) | 개발자 |
|---|---|---|
| 설치, 환경 설정 | O | X |
| 운영체제 | 튜닝+ | 능숙한 사용 |
| 백업/복구 | O | ROLLBACK, REDO 이해 |
| 인스턴스 | 시스템 파라미터 설정+ | 세션에 관련된 설정 |
| 공간 | 전체관점 | 담당 애플리케이션 관점 |
| 실행원리 | O | 데이터베이스 안에서 프로그램 실행 |