All Days 무한도전

• Primary Key 컬럼 순서와 데이터베이스 성능

프린트 이메일 전송

데이터 처리를 할 때 Primary Key(PK)의 역할은 거의 절대적이라 할 수 있다. 웬만한 트랜잭션 처리는 모두 PK를 이용해 처리하기 때문이다. 만일 우리가 설계하는 데이터 모델에서 PK를 이용하는 부분에 비효율적인 면이 있다면 어떻게 될까? 그것이 주는 영향은 단순히 SQL 몇 개를 튜닝하는 정도가 아니라, 해당 테이블을 이용하는 전 트랜잭션에 영향을 주는 치명적인 문제가 될 수 있다.

이춘식 csklee11@chol.com｜현재 LG CNS DB관리팀장으로 국내외 데이터베이스를 총괄해 운영하는 리더를 맡고 있다. '데이터베이스 설계와 구축'이라는 전문서로 수많은 프로젝트 설계방법을 제시했으며, 진단을 통한 실질적인 문제점과 개선점을 제시했다. 데이터베이스 영역과 함께 기술사/감리사로서 강의, 집필, 평가위원, 자격 검정위원 등의 활동을 수행하고 있다. 저서로 데이터베이스 설계와 구축, 아는 만큼 보이는 데이터베이스 등 총 9권이 있다.

IT 프로젝트를 진행하면서 테이블을 추가하거나 변경할 때, PK 컬럼 구성과 컬럼 순서를 중요하게 생각하지 않은 채 있는 그대로 테이블을 생성하는 경우가 많이 있다.

실제로 많은 프로젝트를 진단해보면, 설계단계 끝 부분을 트랜잭션이 들어오는 특성을 분석해 적절하게 PK 순서를 조정해야 되지만 그렇지 않고 데이터 모델링이 된 그대로 DDL(Data Definition Language)을 생성해 테이블을 만들어버리는 경우가 비일비재하다. 그렇게 한참을 개발 한 후, 충분한 테스트 데이터를 생성해 실행하면 성능이 좋지 않아 그때 새로운 인덱스를 추가해 결국 오버헤드를 유발하는 경우가 발생한다.

테이블 생성 이전에 SQL Where 절 조건을 분석하라
여러 개의 컬럼으로 구성된 PK 구성 테이블을 있는 그대로 생성할 경우 어떤 일이 발생할까? 먼저 의도하지 않은 인덱스 구성인 'Primary Key Unique Index'가 생성될 것이다. 그에 따라 조회 SQL 실행 시 성능이 저하되거나 설계로 인해 생성된 인덱스 외의 추가 인덱스를 생성하게 돼 결국 인덱스 과다로 입력, 수정, 삭제 등 불필요한 내부 작업이 증가 할 것이다. 결국 불필요한 작업으로 또 다른 성능저하의 원인이 된다.

따라서 테이블 생성 이전에 설계단계에서 엔티티 타입의 PK 컬럼 순서를 SQL Where 절을 분석해 조정하는 작업이 위 문제를 해결하는데 가장 중요한 열쇠가 된다.

데이터베이스는 분석 - 설계 - 구축 - 테스트 - 이행의 IT 프로젝트 진행 라이프 사이클 중 설계단계 이후에 완성된 데이터 모델을 토대로 물리적인 테이블을 정해진 DBMS에 맞게 생성한다. 보통 상용화된 데이터모델링 툴(ERStudio, ERWin, DA# 등)을 이용해 데이터 모델을 만들고 DDL을 생성하기 때문에, 설계단계 말 즉 데이터베이스 생성단계에서 PK 컬럼의 순서를 고려하지 않고 생성하는 경우가 많이 있다.

<그림 1> PK 순서, 반드시 조정해라

PK 순서를 잘못 지정해 성능이 저하된 경우(단순한 오류)
설계단계에서 데이터모델링을 종료하고 스키마 정보를 생성하기 전, 데이터 모델의 PK 순서를 조정하지 않고 테이블을 생성하면 인덱스를 이용하지 못해 테이블을 Full Scan하는 현상이 발생될 수 있다.

학사관리 업무에서 데이터 모델에 따른 PK 구성을 예로 들어보겠다. '입시마스터'라는 테이블의 PK는 수험번호+년도+학기로 구성돼 있고, '전형과목 실적' 테이블은 입시마스터 테이블에서 상속받은 수험번호+년도+학기에 전형과목 코드로 PK가 구성돼 있는 복합 식별자 구조의 테이블이다.

입시마스터에는 200만 건의 데이터가 있으며 학사는 4학기로 구성돼 있다. 데이터의 보관은 5년이다. 한 학기당 평균 1만 건의 데이터가 있다고 가정해보겠다. PK가 수험번호+년도+학기로 구성돼 있으므로 대부분의 자동 DDL 생성 툴은 세 개의 컬럼을 순서 그대로 조합한 인덱스를 생성한다. 이때 입시 마스터의 인덱스를 '입시마스터_I01'이라 하자.

이 테이블 구조에서 <그림 2>와 같은 SQL구문이 실행되면, 입시마스터 테이블에 있는 인덱스 입시마스터_I01을 이용할 수 있을까?

<그림 2> PK 순서에 따라 생성된 인덱스와 SQL - 개선 전

입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE 절에 들어오지 않으므로, FULL TABLE SCAN이 발생해 200만 건의 데이터를 모두 읽어 당연히 성능이 저하됐다. 다수의 IT 프로젝트 현장 DB 담당자가 PK컬럼 순서를 조정하지 않아, 인덱스를 이용해야 하는 SQL 문장이 결국 인덱스를 이용하지 못해 성능저하가 발생하는 것이다.

이런 테이블 구성을 효율적으로 개선하기 위해서 인덱스의 정렬구조를 고려하고, 해당 테이블에 들어오는 조회조건을 사전에 분석시켰다. 그래서 설계된 화면정의서의 조회조건을 분석하고, 설계된 SQL 문장의 Where절에 '='이나 'Between', '<', '>' 등을 분석해 PK 컬럼 순서를 범위가 들어오는 조건부터 앞쪽으로 위치시켰다. 즉, 입시마스터 엔티티 타입의 PK 순서를 SQL Where 절에서 조회하는 조건으로 조정했다.

<그림 3> PK 순서에 따라 생성된 인덱스와 SQL - 개선 후

입시마스터 테이블의 데이터를 조회할 때, 년도와 학기에 대한 내용이 빈번하게 들어오므로 이를 이용해 PK순서를 변경, 인덱스를 이용 가능하게 만들었다.

생성된 인덱스가 정상적으로 이용돼 평균 2만 건의 데이터를 처리할 수 있게 돼 성능이 한층 개선됐다. 단순하게 PK 순서만 조정했을 뿐인데 SQL 문장 실행에서 Full Table Scan을 했는지 아니면 Index Scan을 했는지 결정된 것이다. 좀 더 복잡한 다른 예를 보자.

PK 순서를 잘못 지정해 성능이 저하된 경우(복잡한 오류)
A씨는 은행권의 프로젝트를 진행하면서 현금 출급기의 PK 컬럼에 대해 유일성과 최소성 확보를 데이터모델링에선 고려했으나 PK 컬럼 순서에는 신경쓰지 않고 바로 테이블을 생성했다.

이렇게 생성된 현금출급기 실적 테이블의 PK는 거래일자+사무소코드+출급기 번호+명세표번호로 구성돼 있는데, 대부분의 SQL 문장에서는 조회를 할 때 사무소 코드가 '='로 들어오고 거래일자는 'BETWEEN' 조회를 하고 있다. 과연 이러한 유형의 트랜잭션 성능이 좋은 상태로 나타나는 것일까?

<그림 4> PK 순서에 따라 생성된 인덱스와 SQL - 개선 전

A씨가 PK 컬럼을 조정하지 않고 해당 테이블에 업무적인 규칙에 의해 작성된 SQL 구문 이다. 자 그렇다면 인덱스를 이용해볼까? 과연 인덱스를 사용하는 것이 최선의 성능을 가져오는 것일까?

실행계획을 분석하면 인덱스가 정상적으로 이용됐기 때문에 SQL 문장은 잘 튜닝된 것으로 착각할 수 있다. 문제는 인덱스를 이용은 하는데 넓은 Range 조회로 인해 SQL 실행성능이 심각하게 저하된다는 점이다.

<그림 5>는 인덱스를 '거래일자+사무소 코드' 순서로 구성 할 때와 '사무소 코드+거래일자'로 구성할 때 각각 데이터를 처리하는 범위가 어떻게 달라지는지 보여주고 있다.

<그림 5> 인덱스 구성에 따른 데이터 처리범위 변경

거래일자+사무소코드로 구성된 왼쪽은 BETWEEN 비교를 한 거래일자 '20120701'이 인덱스의 앞에 위치하기 때문에 범위가 넓어졌고, 사무소 코드+거래일자로 구성된 오른쪽은 인덱스의 경우 = 비교를 한 사무소 코드 '000368'가 인덱스 앞에 위치해 범위가 좁아졌다. 따라서 인덱스 순서를 고려해 데이터 모델의 PK 순서를 거래일자+사무소 코드+출급기 번호+명세표 번호에서 사무소 코드+거래일자+출급기 번호+명세표 번호로 수정하면 성능을 개선시킬 수 있다.

<그림 5> 구조를 토대로 <그림 6>과 같이 테이블의 PK 구성을 다시 조정해 PK 인덱스 조회 시 범위를 줄여 성능향상을 유도했다.

<그림 6> PK 순서에 따라 생성된 인덱스와 SQL - 개선 후

물론 테이블의 PK 구조를 그대로 둔 상태에서 인덱스만 하나 더 만들어도 성능은 개선될 수 있다. 하지만 이미 만들어진 PK 인덱스가 전혀 사용되지 않는다면 입력, 수정, 삭제 시 불필요한 인덱스로 인한 성능저하를 피할 수 없게 된다. 결국 최적화된 인덱스 생성을 위해는 PK 순서변경을 통한 인덱스 생성이 바람직하다. 테이블을 구축하기 이전에 PK 구성단계에서부터 최적의 성능을 나타낼 수 있도록 PK 순서를 조정하는 작업을 반드시 수행해야 한다.

PK 컬럼 순서구성에 대한 결론
지금까지 강조해 온 PK 컬럼 순서를 통한 성능저하 예방의 핵심은 프로젝트 설계단계의 마지막인 데이터모델링 수행 시 다수의 컬럼을 사용하는 PK 컬럼 순서에 대해 트랜잭션의 패턴을 분석하고, 이를 검토해 조정해야 한다는 것이다.

PK 순서가 잘못돼 SQL 문장의 성능이 저하되는 경우는 크게 두 가지다. 첫 번째는 단순한 오류 사례와 같이 인덱스를 이용하지 못하고 FULL TABLE SCAN 하면서 성능이 저하되는 경우이다. 두 번째는 복잡한 오류 사례와 같이 인덱스는 이용하는데 그 범위가 넓어져 성능이 저하되는 경우다.

보통 첫 번째는 SQL의 실행계획을 보고 쉽게 튜닝을 하는데, 두 번째의 경우는 UNIQUE 인덱스를 이용해야 하지만 성능이 저하되는 이유로 인해 쉽게 튜닝을 못 하는 경우가 빈번하다. 따라서 인덱스의 정렬(SORT) 구조를 이해한 상태에서 트랜잭션의 특성에 따른 PK 구성을 해 인덱스 범위를 최소화 하는 방향을 데이터 모델에 반영해야 한다.

데이터 모델의 PK 순서가 아무 의미 없는 것 같지만 실전 프로젝트에서 의미 없는 PK 순서를 설계해 성능이 저하되는 경우가 아주 빈번하다. 성능저하 현상의 대부분이 PK가 여러 개의 속성으로 구성된 복합 식별자일 때 PK 순서를 고려하지 않고 데이터모델링을 한 경우에서 온다. 이건 단순하지만 자주 범하는 오류다.

이렇게 발생한 설계상의 오류를 해결하기 위해 SQL 구문을 수정하거나 인덱스를 생성하는 방법을 사용하려고 한다. 하지만 이것은 구불구불한 길을 만들어 놓고 그 길을 빨리 가기 위해 또 다른 지름길을 만들거나 구불구불한 길을 빨리 갈 수 있는 차를 임시로 개발하는 것과 같다. 결코 근본적인 해결법이 아니라는 말이다.

특히 물리적인 데이터모델링 단계에서는 스스로 생성된 PK 순서 이외에 다른 엔티티 타입으로부터 상속받아 발생되는 PK 순서까지 항상 주의해 표시해야 한다. PK는 해당 테이블의 데이터에 가장 빈번하게 접근해 사용되는 유일한 인덱스(Unique Index)를 모두 자동으로 생성한다.

PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 그 순서를 지정하는 것이다. 즉 인덱스는 그 특성 상 여러 개의 속성이 인덱스 하나로 구성돼 있을 때 앞쪽에 위치한 속성값이 비교자로 있어야 좋은 효율을 나타낸다. 앞쪽에 위치한 속성값이 가급적 '=' 이나 최소한 범위인 'BETWEEN', '< >'에 들어와야 이용할 수 있다. 다시 말하면 그 특성 상 어떤 값이 들어오는지를 예상하고, 데이터베이스에서 발생하는 트랜잭션의 성격을 제대로 이해하지 못하면 원활한 PK 순서를 지정할 수 없게 된다는 것이다. 즉, 데이터모델링에 참여한 사람이 정확한 프로세스의 특징을 이해하지 못한다면 PK 순서를 정확하게 지정할 수 없다는 의미이다.

- 'PLAN_TABLE' is old version

SQL> drop table plan_table;

@/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql

테이블에 CLOB 타입이 포함된 dmp 파일을 import 하고자 할때.

다음과 같은 오류가 발생할수 있다.

IMP-00017: following statement failed with ORACLE error 959:
...
...
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TS_ICMS_DATA' does not exist

import의 경우 컬럼에 CLOB 데이터타입이 포함되어 있을경우는 export받은 테이블의 테이블스페이스 정보와 import 하려고 하는 테이블스페이스가 같아야 한다.

따라서 다음과 같이 테이블 스페이스를 추가한다.

# tablespace 생성
CREATE TABLESPACE TS_ICMS_DATA

DATAFILE '/export/home/oracle/data/ICMSDATA1.dbf' SIZE 1000M;

# 사용자에게 테이블스페이스 지정
CREATE USER test_user IDENTIFIED BY test_pwd DEFAULT TABLESPACE ts_icms_data TEMPORARY TABLESPACE tmp;

# 권한부여
GRANT CONNECT, RESOURCE TO test_user;

select tablespace_name, bytes, max_bytes
from user_ts_quotas;

Solution : SQL> Alter user AVS quota unlimited on USR_D;
SQL> alter user AVS quota <size> on USR_D;

0003. LOCK object 확인

select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
lo.PROCESS, lo.LOCKED_MODE
from dba_objects do, v$locked_object lo
where do.OBJECT_ID = lo.OBJECT_ID
and do.OWNER = 'IBFUSR'
and do.OBJECT_NAME = 'BT_TAB_SYNC_INFO';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID SESSION_ID ORACLE_USERNAME
---------- ---------- ------------------------------
OS_USER_NAME PROCESS LOCKED_MODE
------------------------------ ------------ -----------
IBFUSR
BT_TAB_SYNC_INFO
125142 66 IBFUSR
안민혁 2952:852 3

select SID, SERIAL# from v$session where sid = 66;

SID SERIAL#
---------- ----------
66 48801

alter system kill session '66,48801';
ALTER SYSTEM KILL SESSION '66,48801' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION '66,48801' IMMEDIATE;

Situation 1

To check objects currently being used, e.g. by a DML statement, before executing a DDL command, you can query v$locked_object as in the example below.


Session A

SQL> connect miles/oracle;
Connected.

SQL> drop table test;

Table dropped.

SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.

Session B

SQL> connect / as sysdba
Connected.

SQL> truncate table miles.test;
truncate table miles.test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
--> this is expected because miles.test is locked in Session A


SQL>
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
lo.PROCESS, lo.LOCKED_MODE
from dba_objects do, v$locked_object lo
where do.OBJECT_ID = lo.OBJECT_ID
and do.OWNER = 'MILES'
and do.OBJECT_NAME = 'TEST';


OWNER OBJECT_NAME OBJECT_ID SESSION_ID ORACLE_USERNAME
-------- ------------ ---------- ---------- ------------------------------
OS_USER_NAME PROCESS LOCKED_MODE
-------------------- -------------- -----------
MILES TEST 63754 1079 MILES
oracle 23013 3

From above, you can see miles.test is locked by Oracle user 'MILES' with OS user name 'oracle'.
Session id is 1079, OS process id is 23013.


You can kill the session to release the lock.

SQL> select SID, SERIAL# from v$session where sid = 1079;

SID SERIAL#
---------- ----------
1079 663


SQL> alter system kill session '1079,663';

System altered.

Then the DDL can be executed successfully:

SQL> truncate table miles.test;

Table truncated.

Situation 2

If a DDL statement on an object, e.g. create or replace procedure, is hanging because another user is executing the procedure, you can find the blocking session as in the example below.

Using 3 sessions:

1. Session A: connect as sysdba
2. Session B: connect as user rob/rob
3. Session C: connect as user tony/tony

Session A

SQL>
connect / as sysdba
create user rob identified by rob;
grant connect, resource to rob;
create user tony identified by tony;
grant connect, resource to tony;
grant execute on dbms_lock to rob;
grant execute on dbms_lock to tony;

Session B

SQL>
connect rob/rob

create or replace procedure rob_test_p1 is
begin
null;
end;
/

grant execute on rob_test_p1 to tony;

Session C

SQL>
connect tony/tony
begin
rob.rob_test_p1;
dbms_lock.sleep(120);
end;
/

Session A

SQL>
connect / as sysdba

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
ob.kglnaobj obj_name, ob.KGLNAOWN obj_owner,
ses.KSUUDNAM cur_user
from x$kglpn pn, x$kglob ob, x$ksuse ses
where ob.KGLNAOBJ='ROB_TEST_P1'
and (ob.KGLHDPMD <> 0
or
(ob.KGLHDPMD = 0 and ob.KGLHDLMD not in (0,1))
)
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr;

SID SERIAL# OBJ_NAME OBJ_OWNER CUR_USER
---------- ---------- -------------------- --------------- ------------------
1094 5909 ROB_TEST_P1 ROB TONY


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=5909;

SQL_TEXT
--------------------------------------------------------------------------------
begin rob.rob_test_p1; dbms_lock.sleep(120); end;

If the DDL is already hanging due to a lock, you can follow below test case to find the lock details.

Session C

SQL> connect tony/tony

SQL>
begin
rob.rob_test_p1;
dbms_lock.sleep(120);
end;
/

Session B

SQL> connect rob/rob

SQL> alter procedure rob_test_p1 compile;
--> This is hanging

Session A

col obj_name format a30
col pin_cnt format 999
col pin_mode format 999
col pin_req format 999
col state format a30
col event format a30
col wait_time format 999999999
col seconds_in_wait format 999999999

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
ob.kglnaobj obj_name,
pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req,
w.state, w.event, w.wait_time, w.seconds_in_Wait
from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc;


SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ
---------- ---------- ------------------------------ ------- -------- -------
STATE EVENT WAIT_TIME
------------------------------ ------------------------------ ----------
SECONDS_IN_WAIT
---------------
1082 6765 ROB_TEST_P1 3 2 0
WAITING PL/SQL lock timer 0
39

1074 6060 ROB_TEST_P1 0 0 3
WAITING library cache pin 0
27


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=6765;

SQL_TEXT
--------------------------------------------------------------------------------
begin rob_test_p1; dbms_lock.sleep(120); end;


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=6060;

SQL_TEXT
--------------------------------------------------------------------------------
alter procedure rob_test_p1 compile

SQL> conn /as sysdba
ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

---------------------------------------------------------------------------

1. init<sid>.ora 에 <parameter:AUDIT_FILE_DEST> 존재 확인

2. $ORACLE_HOME/rdbms/audit 스페이스 확보 full 이면 데이터 임시 폴더로 MOVE

3. $ORACLE_HOME/bin/oracle 의 permission 이 6755 인지 확인
아니면 chmod 6755 oracle

0001. Undo Segment 삭제 방법

ORA-01552 ORA-00376 ORA-01110 ORA-01548 - UNDO tablespace

Recently our Helpdesk people configured backup on Payroll server. while running the backup, backup applications locking the writing file.for that issue alert log showing errors like below:

Mon Jul 06 05:07:36 2009
KCF: write/open error block=0x6f6 online=1
file=2 D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 2: D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
Mon Jul 06 05:07:36 2009
Errors in file d:\oracle9\product\payroll\admin\payroll9\bdump\payroll9_smon_2700.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'

Immediately we stopped the backup & restarted the database .
Still the Application is not working. Application show error message

"ORA-01552: cannot use system rollback segment for non-system tablespace"

then I checked for the corrupted rollback segments

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- -----------------
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1

then I recreated another tablespace as UNDOTBS2

CREATE UNDO TABLESPACE "UNDOTBS2"
DATAFILE 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS02.DBF' SIZE 200M REUSE
AUTOEXTEND ON NEXT 51200K MAXSIZE 800M;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

Now I try to drop the old UNDO tablespace but it's giving error:

ora-01548 active rollback segment found

Now the issue is resolved but in Alert log we are getting errors like:

Wed Jul 15 11:50:52 2009
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
……..
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery

& for some transactions from applications we are getting some more errors:

ORA-00376: file 2 cannot be read at this tme ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'

Solution:
1. create pfile from spfile;

2. Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
'_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

3. comment undo management-Auto

#undo_management=AUTO

4. create spfile from pfile

5. Start the database again:

SQL> STARTUP RESTRICT

6. Drop bad rollback segments

SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
......
SQL> drop rollback segment "_SYSSMU10$";
Rollback segment dropped.

7. Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

No rows returned

8. DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

9. shutdown immedaite

10. remove added lines & comments from pfile and recreate the spfile

11.startup

Now the issue is resolved.

SELECT tablespace_name,status FROM dba_rollback_segs;

select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

Response 폴더의 clientadmin.rsp 파일을 연다.


#------------------------------------------------------------------------------
#Name : ORACLE_HOME
#Datatype : String
#Description: Complete path of the Oracle Home.
#Example : ORACLE_HOME = "/product/10.2.0/client"
#------------------------------------------------------------------------------
ORACLE_HOME="c:\oracle\product\10.2.0\client"

#------------------------------------------------------------------------------
#Name : ORACLE_HOME_NAME
#Datatype : String
#Description: Oracle Home Name. Used in creating folders and services.
#Example : ORACLE_HOME_NAME = "OraClient10ghome1"
#------------------------------------------------------------------------------
ORACLE_HOME_NAME="OraClient10ghome1"

위와 같이 수정한다

그런다음

실행창에서 cmd.exe 를 입력하여 커멘드 창을 연 후 실행 폴더로 이동한다음

setup.exe -silent -responsefile D:\Download\Oracle\Response\clientadmin.rsp

아래와 같이 입력하고 실행 하면 콘솔창에서 oracle이 설치된다.

PATCH
/data/oracle/Disk1/stage/products.xml

커널 파라미터 설정

# cd /proc/sys/kernel
# echo 2147483648 > shmmax

# vi /etc/sysctl.conf

fs.file-max = 65536
kernel.shmmax = 2147483648
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range= 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

# sysctl -p

참고 : kernel_parameter.rar

-----------------

CentOS 5.2를 지원하지 않으므로 레드햇 4로 수정.

# vi /etc/redhat-release

Red Hat Enterprise Linux AS release 4 (Taroon)

-----------------

매뉴얼이 제공하는 값과는 다르다.
다른 머신에 설치된 값을 참고했다.

# vi /etc/security/limits.conf

oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

참고 : Shell_Limits.rar

-----------------

# SELINUX=disabled

-----------------

적당히 최신 버전을 설치한다.

# rpm -Uvh setarch-2*
# rpm -Uvh make-3*
# rpm -Uvh glibc-2*
# rpm -Uvh libaio-0*

# rpm -Uvh compat-libstdc++-33-3*
# rpm -Uvh compat-gcc-34-3*
# rpm -Uvh compat-gcc-34-c++-3*
# rpm -Uvh gcc-4*
# rpm -Uvh libXp-1.0.0-8.1.el5.x86_64.rpm
# rpm -Uvh libXp-1.0.0-8.1.el5.i386.rpm

# rpm -Uvh openmotif-2*
# rpm -Uvh compat-db-4*

-------------------

그룹과 유저 생성

# groupadd oinstall
# groupadd dba
# groupadd oper

# useradd -g oinstall -G dba oracle
# passwd oracle

-------------------

설치 디렉토리 생성.
# mkdir -p /u01/app/oracle/product/10.2.0/db_1
# chown -R oracle.oinstall /u01

-------------------

베이스 디렉토리, 홈 디렉토리, 라이브러리 경로 설정

# su - oracle
$ vi .bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

-------------------

오라클 설치 파일의 압축을 푼다.

$ gzip -d 10201_database_linux_x86_64.cpio.gz
$ cpio -idmvc < 10201_database_linux_x86_64.cpio

-------------------

다음은 오라클 인스톨러를 사일런트 모드로 실행하기 위한 작업이다.

oraInst.loc 파일을 수통 생성한다.

# su -
# cd /etc
# vi oraInst.loc

inventory_loc=/u01/app/oracle/oraInventory
inst_group=

# chown oracle:oinstall oraInst.loc
# chmod 664 oraInst.loc

---------------------

리스폰스 파일 수정, 엔터프라이즈의 다음 항목만 수정했다.

# vi /home/oracle/database/response/enterprise.rsp

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_HOME_NAME=orcl

---------------------

# su - oracle

./runInstaller -silent -responseFile /home/oracle/database/response/enterprise.rsp

글자가 마구 깨져나왔다.

설치 디렉토리를 삭제하고 재성성, 재권한설정을 한다.

$ echo $LANG
$ ko_KR.UTF-8

$ LANG=en_US.UTF-8

재실행, 설치가 완료되었다면 root.sh를 실행한다.

$ su -
# /$ORACLE_HOME/root.sh

---------------------

사일런트 모드로 인스톨을 했다면 NetCA 와 DBCA를 따로 셋팅해 주어야 한다.
$ vi /home/oracle/database/response/netca.rsp

수정 한 후 다음과 같이 실행한다.

$ $ORACLE_HOME/bin/netca /silent /responsefile /local_dir/netca.rsp

DBCA도 비슷하다.

$ vi /local_dir/dbca.rsp

$ $ORACLE_HOME/bin/dbca {-progressOnly | -silent} [-cloneTemplate] [-datafileDestination /datafilepath] -responseFile /local_dir/dbca.rsp

---------------------

마지막으로 listener.ora, tnsname.ora 를 셋팅하고 리스너를 켜봅시다.
$ cd $ORACLE_HOME/network/admin/

예제: listener.ora tnsnames.ora

$ lsnrctl start

리스너 상태 확인
$lsnrctl status

문자셋을 확인하자.->따로 포스팅 되어있다.

### 신규 롤백 테이블 생성 ###
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/data1/oradata/COSS/undotbs02.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

### 신규 테이블로 변경 ###
ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

### UNDO 테이블 확인 ###
SELECT tablespace_name,status FROM dba_rollback_segs;

### 온/오프라인 확인 ###
select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

### 테이블 스페이스 드랍 ###
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

### 이전 테이블로 변경하기 위한 테이블스페이스 생성 ###
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/data1/oradata/COSS/undotbs01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

### 이전 테이블로 변경 ###
ALTER SYSTEM SET undo_tablespace=UNDOTBS1 SCOPE=BOTH;

### 온/오프라인 확인 ###
select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

### 신규 테이블 스페이스 드랍 ###
DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;

0002. DBMS_STATS pack 이용

dbms_stats.gather_schema_stats 프로시저를 이용해서 통계 정보를 수집하는 경우 수행 시간이 너무 많이 걸린다는 불만이 종종 있다. 이런 현상은 대부분 다음과 같은 경우에 발생한다.

- 특정 테이블들의 크기가 매우 크다.
- 일부 큰 테이블들은 Partitioning이 되어 있다.

즉 큰 테이블들이 있고 더구나 Partitioning까지 되어 있으니(Global/Partiton Statistics모두 수집해야 하므로) 그만큼 통계 정보 수집에 시간이 걸리기 마련이다. 하지만 Oracle 10g에서는 estimate_percent 파라미터와 method_opt 파라미터의 값이 Oracle에 의해 적절하게 판단되어 통계 정보 생성 시간을 최적화한다. 그렇다면 왜 필요 이상 많은 시간이 걸리는 경우가 발생하는가?

이것은 이 두 파라미터에 다음과 같은 결함이 있기 때문이다.

- estimate_percent 파라미터의 값이 AUTO이다. 즉, Oracle이 테이블의 크기에 따라 적절한 샘플 크기를 선택한다는 의미이다. 대부분의 경우 Oracle은 테이블의 크기에 따라 적절한 값을 찾지만, 간혹 아주 큰 테이블에 대해 100%의 값을 사용하는 경우가 있다.
- method_opt 파라미터의 기본값이 FOR ALL COLUMNS SIZE AUTO이다. 즉 모든 컬럼에 대해 통계정보를 수집하면 히스토그램의 생성 여부는 Oracle이 판단한다. 대부분의 경우 Oracle은 적절한 히스토그램 버킷 크기를 할당하지만, 간혹 불필요하게(가령 Unique Key에 대해) 히스토그램을 생성하는 경우가 있다.

위와 같은 상황이 발생하면 예상보다 지나치게 많은 시간이 걸리게 되는 셈이다.

이런 상황을 해소할 수 있는 방법이 있을까? 가령 다음과 같이 하면...?

dbms_stats.gather_schema_stats(estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');

물론 위의 방법으로 수행 시간 문제는 해결하겠지만, 좋은 방법은 될 수 없다. 만일 크기가 작은 테이블들이 있다면 샘플 크기가 작으면 왜곡된 통계 정보가 수집될 수 있다. 만일 특정 테이블에 대해서는 모든 컬럼(ALL COLUMNS)에 대해 통계 정보를 수집해야 한다면? 또는 특정 테이블의 특정 컬럼에 대해서는 히스토그램 크기를 주고 싶다면?

그렇다고 테이블 레벨로 일일이 dbms_stats.gather_table_stats를 호출하는 것은 너무나 지겨운 일이다.

그래서 보통 다음과 같은 트릭을 종종 사용한다.(편의상 정확한 문법은 생략)

-- 특정 테이블 통계 정보 변경을 막음
dbms_stats.lock_table_stats(user, 'TABLE1');

exec dbms_stats.gather_schema_stats(ownname=>'ORANGE', ESTIMATE_PERCENT=> 10, BLOCK_SAMPLE=>TRUE, DEGREE=> 8, CASCADE=>TRUE);

-- 스키마 레벨로 통계 정보 수집
dbms_stats.gather_schema_stats(user);

-- 통계 정보 활성화 후 이 테이블만 다시 통계 정보 수집
dbms_stats.unlock_table_stats(user, 'TABLE1');
dbms_stats.gather_table_stats(user,'TABLE', estimate_percent=>10, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

begin

dbms_stats.gather_table_stats(ownname => 'PS_MOM', tabname => 'ETL_JOB_LOG_TBL', estimate_percent=>10)

end;

/

begin

dbms_stats.gather_index_stats(ownname => 'PS_MOM', indname => 'IDX_ETL_LOG_ID');

end;

/
위와 같이 하면 Schema 레벨로 대부분의 테이블에 대해 통계 정보를 수집한 후 특정 문제가 되는 테이블에 대해서만 별도로 통계 정보를 수집할 수 있다.

지저분한 트릭같지만 매우 유용한 방법이다.

Oracle 11g에서는 "Preference"라는 새로운 개념을 통해 위와 같은 불편을 일시에 해소하고 있다. Oracle 11g에서라면 다음과 같이 훨씬 직관적이고 깔끔한 방법을 쓸 수 있다.

-- 아래와 같이 한번만 설정
dbms_stats.set_table_prefs(user,'TABLE1', 'ESTIMATE_PERCENT', '10');
dbms_stats.set_table_prefs(user,'TABLE1','METHOD_OPT', 'FOR ALL INDEXED COLUMNS SIZE AUTO');

-- Schema 레벨에서 통계 정보 수집
dbms_stats.gather_schema_stats(user);

즉, 특정 테이블별로 Preference(선호도)를 저장할 수 있어서 10g에서와 같은 부자연스러운 트릭은 불필요한 것이다. 11g에서 가장 마음에 드는 New Feature중 하나이다.

오라클을 통해 작업할 시

insert시점에서 정상적으로 인덱싱이 되지 않는 경우가 발생한다.

특히 결합인덱스를 많이 사용하고 있는 경우 발생될 확률이 높다.

이런경우 오라클의 Analyzed를 통해서 해결이 가능하고

어느정도의 실행속도를 향상 시킬 수있다.

(실제 오라클사에서도 3개월에 한번씩은 Analyze를 실행하라 권고하고 있다.)

[Analyzed 확인 방법]

select table_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_tables

select index_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_indexes

ex) select table_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_tables;

TABLE_NAME NUM_ROWS TO_CHAR(
------------------------------ ---------- --------
ABS_TYPE 38 20040101

ANNIVERS 183 20040101
APPRFLDRHISTORY 570 20040101
APPRFOLDER 16885 20040101
APPRFOLDER_ERR 3670 20040101
APPRFORM 359 20040101
.
.
.
USR_INFO_ADMIN 0 20040101
VAR_DEPT_INFO 0 20040101
VIEW_TYPE 0 20040101
WASTEBOX 0 20040101
ZIP_CODE 44195 20040101

252 rows selected.

※ 참고 : desc user_tables 에서 보통 num_rows 로도 확인 가능

[특정 Table만 Analyze 하는 방법]

analyze table document compute statistics

ex) DOCUMENT Table 만 Analyze

analyze index xpkdocbox compute statistics

ex) XPKDOCBOX Index 만 Analyze

[전체 Table Analyze 하는 간단한 방법]

1. vi analyze_all.sql
select 'analyze table || table_name || estimate statistics;' from user_tables

2. @analyze_all.sql

3. set heading off
set echo off
set feedback off
set pagesize 300 (line 이 300 미만일 경우)
spool analyze_table.sql
/
spool off

4. vi analyze_table.sql
필요없는 Line 제거 및 정리

5. @analyze_table.sql

[전체 Index Analyze 하는 간단한 방법]

1. vi analyze_all.sql
select 'analyze '||object_type||' ps_mom.'||object_name||' compute statistics; '

from dba_objects

where owner = 'PS_MOM'

and object_type in('TABLE','INDEX')

/

2. @analyze_all.sql

3. set heading off
set echo off
set feedback off
set pagesize 300 (line 이 300 미만일 경우)
spool analyze_index.sql
/
spool off

4. vi analyze_index.sql
필요없는 Line 제거 및 정리

5. @analyze_index.sql

0017. INDEX 생성 가이드라인

☞ 12-5 b-tree index

* 특성
1. 컬럼값이 같으면 rowid 순으로 정렬
2. null에는 인덱스가 생성되지 않는다.

* 인덱스에 대한 DML 작업 효과

Index 에는 pctused파라미터가 없다.

이말은 인덱스를 삭제하고 남은 공간을 다시 활용하지 않는 다는 뜻이다.
이런 이유 때문에 인덱스는 주기적으로 정리 되어야 하며 그렇게 하지 않으면 인덱스의 사이즈는 점점 증가 한다.

☞ 12-7 bitmap index

index를 경유하면 모든 데이터를 읽지않고 원하는 범위의 데이터만 읽기 때문에 빠른 성능을 보장받을 수 있다. 하지만 그범위가 아주 넓은 경우에는 오히려 더 나쁜 성능을 나타내게 된다. 이처럼 B*tree inex의 단점은 cardinality가 낮은 속성에 대하여 취약하는 것이다.
(색깔의 종류가 적을 수록 좋다)

☞ 12-9 비트리 인데스와 비트맵 인덱스 비교

비트리 인덱스는 카디널리티가 높은 것에 유리하고 비트맵은 낮은것에 유리 함. 그리고 비트맵 인덱스는 비트맵 세그먼트 수준의 잠금을 사용하기 때문에 비트맵 인덱스의 키열을 갱신하면 더 많은 비용이 들지만 B트리 인덱스에서는 테이블의 각행에 해당하는 항목을 잠금으로 update 할 때 유리 . 비트맵은 부울 연산 식을 사용해서 부울 수식에 유리.
OLTP(게시판이나 수시로 바끼는 것)환경에서는 B트리 인덱스가 유리. 대형 정적 테이블에서 복합 질의 사용하는 데이터 웨어하우스 환경에서는 비트맵이 유리.

☞ 12-10 일반 B 트리 인덱스 생성

create index hr.employees_last_name_indx
on hr.employees(last_name)
pctfree 30
storage (initial 200k next 200k
pctincrease 0 maxextents 50)
tablespace indx;

last_name 열을 사용하여 employees 테이블에 인덱스를 생성



주의) 밑에 tablespace indx;를 지정하지 않으면 동시에 사용자가 접속하면 index를 사용하면 lock이 걸리기 때문에 지정해 줘야함

☞ 12-13 인덱스 생성시 가이드라인

create index emp_hiredate on emp(hiredate) pctfree 30 initrans 10;

1. 인덱스를 사용하면 쿼리 속도는 빨라지나 DML 작업속도는 느려진다.

2. 별도의 테이블 스페이스에 인덱스 세그먼트를 둔다.

3. 대용량 테이블에 인덱스 생성시 nologging 옵션을 쓰는게 좋다.

이유) 큰 인덱스의 경우 리두로그파일 생성을 방지하면 성능을 상당히 향상시킬수 있으므로

4. 인덱스 생성시 initrans(각 블록에서 미리 할당하는 트랜잭션 항목수) 값을 테이블 보다 많이 줘야 한다.

이유) 인덱스 항목은 자신이 인덱스하는 행보다 작기 때문에 인덱스 블록은 블록마다 많은 항목을 포함 해당 테이블보다 인덱스에 대한 initrans가 높아야 한다.

☞ 12-15 비트맵 인덱스 생성

create bitmap index orders_region_id_idx
on orders(region_id)
pctfree 30
storage(initial 200k next 200k
pctincrease 0 maxextents 50)
tablespace indx;

☞ 12-18 인덱스 저장 영역 매개변수 변경

alter index employees_last_name_idx
storage(next 400k
maxextents 100);

☞ 12-20 인덱스 공간 할당 및 할당 해제

alter index orders_region_id_idx
allocate extent (size 200k
datafile '/disk6/indx01.dbf');

alter index orders_id_idx
deallocate unused;

테이블에 대량의 삽입 작업 전에 인덱스에 확장영역을 추가 할수 있으며 확장 영역을 추가하면 인덱스가 동적으로 할당 되어 성능 저하를 방지 할수 있다.


☞ 12-21 인덱스 재구축

alter index orders_region_id_idx rebuild
teablespace indx02;

* 기존 인덱스를 데이터 소스로 사용하여 인덱스 구축
* 기존 인덱스를 사용하면 다시 구축할 필요가 없으므로 성능 향상
* 새 인덱스를 구축할 충분한 공간 필요
* 새 인덱스를 구축하는 동안 quary에서 기존 인덱스를 사용 가능

@ 재구축이 필요한 상황

1. 인덱스가 있는 테이블 스페이스를 이동할때
2. 인덱스 테이블을 alter table ........ move tablespace 명령을 사용하여 이동

SQL> select table_name, tablespace_name from user_tables
2 where table_name='EMP';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP USERS

SQL> create index emp_ename
2 on emp(ename);

인덱스가 생성되었습니다.

SQL> alter table emp
2 move tablespace tools;

테이블이 변경되었습니다.

SQL> select ename
2 from emp
3 where ename='SCOTT';

select ename
*
1행에 오류:
ORA-01502: 인덱스 'SCOTT.EMP_ENAME'또는 인덱스 분할영역은 사용할 수 없은
상태입니다

SQL> alter index emp_ename rebuild;

인덱스가 변경되었습니다.

SQL> select ename
2 from emp
3 where ename='SCOTT';

ENAME
----------
SCOTT

SQL> @p.sql

file toto.sql(이)가 기록되었습니다

테이블이 잘렸습니다.

1* select ename
1* explain plan set statement_id='MyTest' for select ename

해석되었습니다.

Query Plan
---------------------------------------------------------------
SELECT STATEMENT Cost =
INDEX RANGE SCAN EMP_ENAME NON-UNIQUE

실습2)

1* alter index emp_ename rebuild reverse
SQL> /

인덱스가 변경되었습니다.

SQL> select index_name, index_type
2 from user_indexes;

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
C_ID_DATE NORMAL
C_ID_PK NORMAL
DEPT200_DEPTNO_PK NORMAL
EMP21_EMPNO_PK NORMAL
EMP22_EMPNO_PK NORMAL
EMP_BIT_JOB BITMAP
EMP_ENAME NORMAL/REV
EMP_HIREDATEON NORMAL
SYS_C003006 NORMAL
SYS_IL0000030314C00001$$ LOB

10 개의 행이 선택되었습니다.

bitmap 도 될까 ?

☞ 12-23 index 온라인으로 재구축

alter index ordes_id_idx rebuild online;

9i 에서 인덱스 재 구축 작업 동안 동시에 DML 작업 수행할수있는것

SQL> update test1
2 set sal=0
3 where id=9993;

2 행이 갱신되었습니다.

다른세션에서 인덱스를 생성하려하거나 재구축 또는 삭제하려하는게 안된다.

SQL> alter index test1_sal rebuild reverse;
alter index test1_sal rebuild reverse
*
1행에 오류:
ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다

SQL> /

인덱스가 변경되었습니다.

SQL> /
alter index test1_sal rebuild reverse
*
1행에 오류:
ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다

SQL> drop index test1_sal;
drop index test1_sal
*
1행에 오류:
ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다

SQL> /

인덱스가 삭제되었습니다.

SQL> create index test1_sal
2 on test1(sal);
on test1(sal)
*
2행에 오류:
ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다

다음 실습은 sh 유져에서 작업할것이다. sh 계정을 활성화시키시요 ( 유쌤 ^^)

SQL> create index sales_amount_sold
2 on sales(amount_sold);

인덱스가 생성되었습니다.

다른세션에서

SQL> update sales
2 set amount_sold=0
3 where prod_id=250;

475 행이 갱신되었습니다.

책에 나온 내용과 테스트가 틀리다. 책이 틀린것 같다. ( 또 오라클이 오버했네요. 안되는거 된다고 하고...)

그런데 재구축은 책이 맞습니다.

먼저 online 을 붙이지 않고 실습하고

alter index slaes_amount_sold rebuild ;

다른세션에서

SQL> update sales
2 set amount_sold=0
3 where prod_id=250;

475 행이 갱신되었습니다.

online 붙이고 실습하라

alter index slaes_amount_sold rebuild online;

SQL> update sales
2 set amount_sold=0
3 where prod_id=250;

475 행이 갱신되었습니다.

☞ 12-23 index 유효성 검사

SQL> analyze index sales_amount_sold validate structure;

SQL> select blocks, pct_used, distinct_keys
2 , lf_rows, del_lf_rows
3 from index_stats;

BLOCKS PCT_USED DISTINCT_KEYS LF_ROWS DEL_LF_ROWS
---------- ---------- ------------- ---------- -----------
2816 90 6755 1016271 0

del_lf_rows 와 lf_rows 의 비율이 30% 를 초과하는 경우에 인덱스를 재구성한다

coalesce 작업을 수행하기 전 첫번때 두개의 최하위 블록은 50%가 채워진 상태이고, 인덱스가 단편화 되었으므로 병합하여 하나로 합친다.

☞ 12-25 인덱스 및 유효성 검사

ANALYZE INDEX orders_region_id_idx
VALIDATE STRUCTURE;

인덱스를 분석하여 다음을 수행
* 모든 인덱스 블록에 대해 손상된 블록을 확인. 이명령을 수행해도 인덱스 항목이 테이블의 데이터에 대응되는지 여부는 확인되니 않음

* index_stats 뷰를 인덱스 정보로 채움

SELECT (del_lf_rows_len/ lf_lows_len) * 100 as balancing from index_stats;

15% 만 rebuilding 함

☞ 12-29 사용 되지 않은 익덱스 식별

*인덱스 사용에 대한 모니터를 시작하는 방법

ALTER INDEX hr.dept_id_idx
MONITORING USAGE

* 인덱스 사용에 대한 모니터를 중지하는 방법

ALTER INDEX hr.dept_id_idx
NOMONITORING USAGE

==>인덱스 사용에 대한 통계를 수집하여 V$OBJECT_USAGE에 표시

SELECT index_name, used from V$OBJECT_USAGE;

0016. Oracle 8i Nosegment Index vs. Oracle 11g Invisible Index

Oracle 11g New Feature에 새로운 이야기들을 많이 들었을 것이다.
이중 유난히 나의 관심을 끄는 한 가지가 Invisiblie Index이다. Invisilble Index란 말 그대로 "보이지" 않는 인덱스를 의미한다. 더 정확하게 말하면 인덱스 세그먼트로서 물리적으로 존재는 하지만 Optimizer에게는 보이지 않는 인덱스를 의미한다.

테이블에 새로운 인덱스를 추가하면 Optimizer의 비용(Cost) 계산에 영향을 주어 기존 쿼리의 실행 계획에 영향을 주게 된다. 물론 이것은 바람직한 것이고, 인덱스의 존재 이유이기도 하다.

하지만, 간혹 인덱스를 추가했을 때 이 인덱스로 인해 성능 개선이 이루어질 것인지만을 테스트해고 싶은 경우가 있다. 이런 경우에는 어떻게 해야할까?

잘 알려지지 않은 사실이지만, Oracle 8i에서 Virtual Index 기능이 추가되었다. Virtual Index란 말 그대로 가상의 인덱스로 실제로는 인덱스가 존재하지 않지만, 인덱스가 존재하는 것처럼 데이터를 조작하는 것을 의미한다. 이런 의미에서 흔히 Fake Index(가짜 인덱스)라고 부르기도 한다. 아래 테스트 스크립트를 보자.

select * from v$version;
--------------------------------------------------------
BANNER
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.0.0 - Development
NLSRTL Version 3.4.1.0.0 - Production

-- Table 생성
create table t_noseg(id int, id2 int);

-- Clustering Factor를 달리 하기 위해 id, id2의 순서를 다르게 삽입한다.
insert into t_noseg
select rownum, decode(mod(rownum,2),1,50000-rownum+1,rownum) from all_objects t1, all_objects t2
where rownum <= 50000

commit;

-- Virtual Index를 생성한다. (NOSEGMENT 속성 부여)
-- id 컬럼에 대한 인덱스는 Clustering Factor가 매우 우수하다.
-- 하지만 id2 컬럼에 대한 인덱스는 Clustering Factor가 매우 불량하다.
create index t_noseg_idx on t_noseg(id) nosegment;
create index t_noseg_idx2 on t_noseg(id2) nosegment;

-- 통계 정보를 생성한다.
exec dbms_stats.gather_table_stats(user,'T_NOSEG', cascade=>true);

-- Virtual Index는 실제로는 사용되지 않으며 실행 계획을 테스트하는 목적으로만 사용된다.
-- _use_nosegment_indexes 값을 true로 변경하면 실행 계획에 반영할 수 있다.
alter session set "_use_nosegment_indexes" = true;

-- 다음과 같이 id 컬럼에 대해 가상의 인덱스를 선택하는 실행 계획이 수립됨을 확인할 수 있다.
-- Cost가 4이고, Index는 Analyze가 되지 않았다는 사실에 주목하자.
select * from t_noseg
where id between 1 and 100;

SELECT STATEMENT CHOOSE-Cost : 4
TABLE ACCESS BY INDEX ROWID MAXGAUGE.T_NOSEG(1) Analyzed : 20080113
INDEX RANGE SCAN MAXGAUGE.T_NOSEG_IDX(NU) (ID) NOT ANALYZED

-- id2 컬럼에 대해서도 역시 인덱스를 선택하는 실행 계획이 수립된다.
-- 하지만, Clustering Factor에 의한 비용 증가는 계산되지 않는다.
select * from t_noseg
where id2 between 1 and 100;

SELECT STATEMENT CHOOSE-Cost : 4
TABLE ACCESS BY INDEX ROWID MAXGAUGE.T_NOSEG(1) Analyzed : 20080113
INDEX RANGE SCAN MAXGAUGE.T_NOSEG_IDX2(NU) (ID2) NOT ANALYZED

-- 세그먼트가 존재하는가?
select count(*) from dba_segments where segment_name = 'T_NOSEG_IDX';
----------------------------------------------------------------
0

select count(*) from dba_objects where object_name = 'T_NOSEG_IDX';
----------------------------------------------------------------
1

위의 테스트 결과를 보면 다음과 같은 사실을 추론할 수 있다.
- Virtual Index는 실제로는 세그먼트가 생성되지 않으며, 딕셔너리에만 존재하는 인덱스이다.
- 따라서 실행 계획을 추측해보는데는 도움이 되지만, 실제로 실행에 반영할 수는 없다.
- 실제 세그먼트가 존재하지 않기 때문에 통계 정보를 수집할 수 없다. 따라서 Clustering Factor와 같은 중요한 요소는 계산되지 않는다. 이는 비용 계산에 오류를 초래할 수 있다.

Virtual Index는 오라클 내부적으로만 사용될 목적으로 만들어졌으며 대중적으로 공개가 되어 있지 않다.

Oracle 11g는 Virtual Index를 계승 발전시켜서 Invisible Index라는 전혀 새로운 개념을 소개하고 있다. Invisible Index는 Virtual Index와는 달리 실제로 세그먼트 형태로 존재하는 인덱스이다. 다만 Optimizer에게 보이지 않을 뿐이다. 아래 테스트 스크립트를 보자.

-- Table 생성
create table t_invisible(id int, id2 int);

-- 역시 Clustering Factor를 달리 하기 위해 id, id2의 순서를 다르게 삽입한다.
insert into t_invisible
select rownum, decode(mod(rownum,2),1,50000-rownum+1,rownum) from all_objects t1, all_objects t2
where rownum <= 50000

commit;

-- Invisible Index를 생성한다.
create index t_invisible_idx on t_invisible(id) invisible;
create index t_invisible_idx2 on t_invisible(id2) invisible;

-- Invisible Index를 사용하게끔 변경한다. 이렇게 해야만 통계 정보가 수집된다.
alter session set optimizer_use_invisible_indexes=true;

-- 통계 정보 수집
exec dbms_stats.gather_table_stats(user,'T_INVISIBLE', cascade=>true);

-- 다음과 같이 id 컬럼에 대해 가상의 인덱스를 선택하는 실행 계획이 수립됨을 확인할 수 있다.
-- Cost가 3이고, Index가 Analyze되었음에 유의하자.
select * from t_invisible
where id between 1 and 100;

SELECT STATEMENT ALL_ROWS-Cost : 3
TABLE ACCESS BY INDEX ROWID UKJA.T_INVISIBLE(1)
INDEX RANGE SCAN UKJA.T_INVISIBLE_IDX (ID)

-- id2 컬럼에 대해서는 Full Table Scan을 사용하다.
-- id2 컬럼은 Clustering Factor가 매우 불량하기 때문에 이것이 비용에 반영된 결과이다.
select * from t_invisible
where id2 between 1 and 100;

SELECT STATEMENT ALL_ROWS-Cost : 21
TABLE ACCESS FULL UKJA.T_INVISIBLE(1)

-- 실제 id2 컬럼에 대해 인덱스를 사용하면 비용이 어떻게 계산되지는 확인해보면 아래와 같다.
-- 비용이 102로 Full Table Scan보다 오히려 비싸게 계산되는 것을 확인할 수 있다.
select /*+ index(t_invisible t_invisible_idx2) */* from t_invisible
where id2 between 1 and 100;

SELECT STATEMENT ALL_ROWS-Cost : 102
TABLE ACCESS BY INDEX ROWID UKJA.T_INVISIBLE(1)
INDEX RANGE SCAN UKJA.T_INVISIBLE_IDX2 (ID2)

-- 세그먼트가 존재하는가?
select count(*) from dba_segments where segment_name = 'T_INVISIBLE_IDX';
-----------------------------------------------------------------
1

Invisible Index는 Virtual Index와는 달리 실제로 존재하는 인덱스이고 따라서 통계 정보가 수집된다. 따라서 보다 정확한 테스트를 수행할 수 있다. 테스트후 반영여부가 결정되면 다음과 같이 손쉽게 Visible Index로 전환할 수 있다.

alter index t_invisible_idx visible;

위의 간단한 테스트 결과를 보면 Invisible Index가 기존의 Virtual Index에 비해서 훨씬 유용한 기능을 제공하는지 잘 알 수 있다. 아무쪼록 많이 활용되었으면 한다.

(참고) Invisible Index가 존재하는 테이블/인덱스에 대해 통계 정보를 수집하려고 하면 ORA-904: Invalid Column 에러가 발생한다. 이것은 11g R1의 버그이다. 패치가 나오기 전까지는 optimizer_use_invisible_indexes 값을 true로 변경한 후 통계 정보를 수집해야 한다.

0015. 데이터베이스 아키텍쳐

1. 아키텍처 개관

가. oracle 아키텍처

Database : 데이터 집합(Datafile, Redo Log File, Control File 등)

Instance : SGA 공유 메모리 영역과 이를 액세스하는 프로세스 집합

기본적으로 하나의 인스턴스가 하나의 데이터베이스만 액세스하지만, RAC(Real Application Cluster) 환경에서는 여러 인스턴스가 하나의 데이터베이스를 액세스할 수 있다. 하나의 인스턴스가 여러 데이터베이스를 액세스할 수는 없다.

나. SQL Server 아키텍처

데이터베이스 하나를 만들 때마다 주(Primary 또는 Main) 데이터 파일과 트랜잭션 로그 파일이 하나씩 생기는데, 전자는 확장자가 mdf이고 후자는 ldf이다. 저장할 데이터가 많으면 보조(Non-Primary) 데이터 파일을 추가할 수 있으며, 확장자는 ndf이다.

2. 프로세스

프로세스는 서버 프로세스(Server Processes)와 백그라운드 프로세스(Background Processes) 집합으로 나뉨. 서버 프로세스는 전면에 나서 사용자가 던지는 각종 명령을 처리하고, 백그라운드 프로세스는 뒤에서 묵묵히 주어진 역할을 수행한다.(SQL Server는 쓰레드(Thread) 기반 아키텍처)

가. 서버 프로세스(Server Processes)

서버 프로세스는 사용자 프로세스와 통신하면서 사용자의 각종 명령을 처리하며, SQL Server에선 Worker 쓰레드가 같은 역할을 담당한다.

SQL을 파싱하고 필요하면 최적화를 수행하며, 커서를 열어 SQL을 실행하면서 블록을 읽고, 읽은 데이터를 정렬해서 클라이언트가 요청한 결과집합을 만들어 네트워크를 통해 전송하는 일련의 작업을 모두 서버 프로세스가 처리.

스스로 처리하도록 구현되지 않은 기능, 이를테면 데이터 파일로부터 DB 버퍼 캐시로 블록을 적재하거나 Dirty 블록을 캐시에서 밀어냄으로써 Free 블록을 확보하는 일, 그리고 Redo 로그 버퍼를 비우는 일 등은 OS, I/O 서브시스템, 백그라운드 프로세스가 대신 처리하도록 시스템 Call을 통해 요청. Oracle에는 전용/공유 서버 방식 두 가지가 있다.

1) 전용 서버(Dedicated Server) 방식

서버 프로세스가 단 하나의 사용자 프로세스를 위해 전용(Dedicated) 서비스를 제공.

SQL을 수행할 때마다 연결 요청을 반복하면 서버 프로세스의 생성과 해제도 반복하게 되므로 DBMS에 매우 큰 부담을 주고 성능을 크게 떨어뜨림(OLTP성 애플리케이션에선 Connection Pooling 기법을 필수적으로 사용해야 한다.)

2) 공유 서버(Shared Server) 방식

하나의 서버 프로세스를 여러 사용자 세션이 공유하는 방식으로서 여러 개의 서버 프로세스를 띄어 놓고 이를 공유해서 사용.

사용자 프로세스는 서버 프로세스와 직접 통신하지 않고 Dispatcher 프로세스를 거친다.

사용자 명령이 Dispatcher에게 전달되면 Dispatcher는 이를 SGA에 있는 요청 큐(Request Queue)에 등록.

이후 가장 먼저 가용해진 서버 프로세스가 요청 큐에 있는 사용자 명령을 꺼내서 처리하고

그 결과를 응답 큐(Response Queue)에 등록한다.

응답 큐를 모니터링하던 Dispatcher가 응답 결과를 발견하면 사용자 프로세스에게 전송해 준다.

나. 백그라운드 프로세스(Background Processes)

3. 파일 구조

가. 데이터 파일

1) 블록(=페이지)

대부분 DBMS에서 I/O는 블록 단위.(Oracle은 '블록(Block)', SQL Server는 '페이지(Page)')

Oracle은 2KB, 4KB, 8KB, 16KB, 32KB, 64KB의 다양한 블록 크기를 사용가능

SQL Server에선 8KB 단일 크기를 사용한다.

*블록 단위로 I/O 한다는 것은, 하나의 레코드에서 하나의 칼럼만을 읽으려 할 때도 레코드가 속한 블록 전체를 읽게 됨을 뜻함

SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스하는 블록 개수.

2) 익스텐트(Extent)

테이블 스페이스로부터 공간을 할당하는 단위. 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블 스페이스(물리적으로는 데이터 파일)로부터 추가적인 공간을 할당받는데, 이때 정해진 익스텐트 크기의 연속된 블록을 할당받는다.

익스텐트 내 블록은 논리적으로 인접하지만, 익스텐트끼리 서로 인접하지는 않는다.

(Oracle은 다양한 크기의 익스텐트를 사용하지만, SQL Server에선 8개 페이지의 익스텐트만을 사용.)

Oracle은 한 익스텐트에 속한 모든 블록을 단일 오브젝트가 사용하지만, SQL Server에서는 2개 이상 오브젝트가 나누어 사용할 수도 있다.

SQL Server는 다음 2가지 타입의 익스텐트를 사용.

• 균일(Uniform) 익스텐트 : 64KB 이상의 공간을 필요로 하는 테이블이나 인덱스를 위해 사용되며, 8개 페이지 단위로 할당된 익스텐트를 단일 오브젝트가 모두 사용한다.
• 혼합(Mixed) 익스텐트 : 한 익스텐트에 할당된 8개 페이지를 여러 오브젝트가 나누어 사용하는 형태다. 모든 테이블이 처음에는 혼합 익스텐트로 시작하지만 64KB를 넘으면서 2번째부터 균일 익스텐트를 사용하게 된다.

3) 세그먼트

세그먼트는 테이블, 인덱스, Undo처럼 저장공간을 필요로 하는 데이터베이스 오브젝트다. (한개 이상의 익스텐트 사용)

((ex 테이블,인덱스 )

다른 오브젝트는 세그먼트와 1:1 대응 관계를 갖지만 파티션은 1:M 관계를 갖음.

파티션 테이블(또는 인덱스)을 만들면, 내부적으로 여러 개의 세그먼트가 만들어진다.

한 세그먼트는 자신이 속한 테이블 스페이스 내 여러 데이터 파일에 걸쳐 저장될 수 있음.

세그먼트에 할당된 익스텐트가 여러 데이터 파일에 흩어져 저장되는 것이며, 그래야 디스크 경합을 줄이고 I/O 분산 효과를 얻을 수 있다.

4) 테이블 스페이스

테이블 스페이스는 세그먼트를 담는 콘테이너로서, 여러 데이터 파일로 구성된다.

사용자는 세그먼트를 위한 테이블 스페이스를 지정할 뿐, 실제 값을 저장할 데이터 파일을 선택하고 익스텐트를 할당하는 것은 DBMS의 몫. 각 세그먼트는 정확히 한 테이블 스페이스에만 속하지만, 한 테이블 스페이스에는 여러 세그먼트가 존재할 수 있다.

특정 세그먼트에 할당된 모든 익스텐트는 해당 세그먼트와 관련된 테이블 스페이스 내에서만 찾아진다. (한 세그먼트가 여러 테이블 스페이스에 걸쳐 저장될 수는 없음.)

한 세그먼트가 여러 데이터 파일에 걸쳐 저장될 수는 있다. 한 테이블 스페이스가 여러 데이터 파일로 구성되기 때문.

나. 임시 데이터 파일

임시(Temporary) 데이터 파일은 특별한 용도로 사용된다. 대량의 정렬이나 해시 작업을 수행하다가 메모리 공간이 부족해지면 중간 결과집합을 저장하는 용도다. ( Redo 정보를 생성하지 않기 때문에 나중에 파일에 문제가 생겼을 때 복구되지 않는다. 따라서 백업할 필요도 없다.)

Oracle에선 임시 테이블 스페이스를 여러 개 생성해 두고, 사용자마다 별도의 임시 테이블 스페이스를 지정해 줄 수도 있다.

create temporary tablespace big_temp tempfile '/usr/local/oracle/oradata/ora10g/big_temp.dbf' size 2000m;

alter user scott temporary tablespace big_temp;

SQL Server는 단 하나의 tempdb 데이터베이스를 사용한다. tempdb는 전역 리소스로서 시스템에 연결된 모든 사용자의 임시 데이터를 여기에 저장한다.

다. 로그 파일

DB 버퍼 캐시에 가해지는 모든 변경사항을 기록하는 파일

Oracle : 'Redo 로그'

SQL Server는 '트랜잭션 로그'.

변경된 메모리 버퍼 블록을 디스크 상의 데이터 블록에 기록하는 작업은 Random I/O 방식으로 이루어지기 때문에 느리다.

반면 로그 기록은 Append 방식으로 이루어지기 때문에 상대적으로 매우 빠르다.

DBMS는 버퍼 블록에 대한 변경사항을 건건이 데이터 파일에 기록하기보다 우선 로그 파일에 Append 방식으로 빠르게 기록하는 방식을 사용한다. 그러고 나서 버퍼 블록과 데이터 파일 간 동기화는 적절한 수단(DBWR, Checkpoint)을 이용해 나중에 배치(Batch) 방식으로 일괄 처리한다.

'Fast Commit' : 사용자의 갱신내용이 메모리상의 버퍼 블록에만 기록된 채 아직 디스크에 기록되지 않았더라도 Redo 로그를 믿고

빠르게 커밋을 완료

• Online Redo 로그
  캐시에 저장된 변경사항이 아직 데이터 파일에 기록되지 않은 상태에서 정전 등으로 인스턴스가 비정상 종료되면, 그때까지의 작업내용을 모두 잃게 된다. 이러한 트랜잭션 데이터의 유실에 대비하기 위해 Oracle은 Online Redo 로그를 사용한다.
• '캐시 복구' " 마지막 체크포인트 이후부터 사고 발생 직전까지 수행되었던 트랜잭션들을 Redo 로그를 이용해 재현
• Online Redo 로그는 최소 두 개 이상의 파일로 구성된다. 현재 사용 중인 파일이 꽉 차면 다음 파일로 로그 스위칭(log switching)이 발생하며, 계속 로그를 써 나가다가 모든 파일이 꽉 차면 다시 첫 번째 파일부터 재사용하는 라운드 로빈(round-robin) 방식을 사용
• 트랜잭션 로그
  트랜잭션 로그는 Oracle의 Online Redo 로그와 대응되는 SQL Server의 로그 파일이다. 주 데이터 파일마다, 즉 데이터베이스마다 트랜잭션 로그 파일이 하나씩 생기며, 확장자는 ldf이다. 트랜잭션 로그 파일은 내부적으로 '가상 로그 파일'이라 불리는 더 작은 단위의 세그먼트로 나뉘며, 이 가상 로그 파일의 개수가 너무 많아지지 않도록(즉, 조각화가 발생하지 않도록) 옵션을 지정하는 게 좋다. 예를 들어, 로그 파일을 애초에 넉넉한 크기로 만들어 자동 증가가 발생하지 않도록 하거나, 어쩔 수 없이 자동 증가한다면 증가하는 단위를 크게 지정하는 것이다.
• Archived(=Offline) Redo 로그
  Archived Redo 로그는 Oracle에서 Online Redo 로그가 재사용되기 전에 다른 위치로 백업해 둔 파일을 말한다. 디스크가 깨지는 등 물리적인 저장 매체에 문제가 생겼을 때 데이터베이스(또는 미디어) 복구를 위해 사용된다. 참고로, SQL Server에는 Archived Redo 로그에 대응되는 개념이 없다.

4. 메모리 구조

메모리 구조는 시스템 공유 메모리 영역과 프로세스 전용 메모리 영역으로 구분된다.

• 시스템 공유 메모리 영역
  시스템 공유 메모리는 말 그대로 여러 프로세스(또는 쓰레드)가 동시에 액세스할 수 있는 메모리 영역.
• Oracle에선 'System Global Area(SGA)', SQL Server에선 'Memory Pool'이라고 부른다
• DB 버퍼 캐시, 공유 풀, 로그 버퍼가 있다.
• 공유 메모리 영역은 그 외에 Large 풀(Large Pool), 자바 풀(Java Pool)
• 시스템 구조와 제어 구조를 캐싱하는 영역도 포함. 시스템 공유 메모리 영역은 여러 프로세스에 공유되기 때문에 내부적으로 래치(Latch), 버퍼 Lock, 라이브러리 캐시 Lock/Pin 같은 액세스 직렬화 메커니즘이 사용
• 프로세스 전용 메모리 영역
  서버 프로세스가 자신만의 전용 메모리 영역 'Process Global Area(PGA)'라고 부르며, 데이터를 정렬하고 세션과 커서에 관한 상태 정보를 저장하는 용도로 사용.
• 쓰레드(Thread) 기반 아키텍처를 사용하는 SQL Server는 프로세스 전용 메모리 영역을 갖지 않는다. 쓰레드는 전용 메모리 영역을 가질 수 없고, 부모 프로세스의 메모리 영역을 사용하기 때문이다.

가. DB 버퍼 캐시(DB Buffer Cache)

DB 버퍼 캐시는 데이터 파일로부터 읽어 들인 데이터 블록을 담는 캐시 영역이다.

인스턴스에 접속한 모든 사용자 프로세스는 서버 프로세스를 통해 DB 버퍼 캐시의 버퍼 블록을 동시에(내부적으로는 버퍼 Lock을 통해 직렬화) 액세스할 수 있음.

일부 Direct Path Read 메커니즘이 작동하는 경우를 제외하면, 모든 블록 읽기는 버퍼 캐시를 통해 이루어진다. 즉, 읽고자 하는 블록을 먼저 버퍼 캐시에서 찾아보고 없을 때 디스크에서 읽는다. 디스크에서 읽을 때도 먼저 버퍼 캐시에 적재한 후에 읽는다.

데이터 변경도 버퍼 캐시에 적재된 블록을 통해 이루어지며, 변경된 블록(Dirty 버퍼 블록)을 주기적으로 데이터 파일에 기록하는 작업은 DBWR 프로세스의 몫이다. 디스크 I/O는 물리적으로 액세스 암(Arm)이 움직이면서 헤드를 통해 이루어지는 반면, 메모리 I/O는 전기적 신호에 불과하기 때문에 디스크 I/O에 비교할 수 없을 정도로 빠르다. 디스크에서 읽은 데이터 블록을 메모리 상에 보관해 두는 기능이 모든 데이터베이스 시스템에 필수적인 이유다.

1) 버퍼 블록의 상태

모든 버퍼 블록은 아래 세 가지 중 하나의 상태에 놓인다.

• Free 버퍼 : 인스턴스 기동 후 아직 데이터가 읽히지 않아 비어 있는 상태(Clean 버퍼)이거나, 데이터가 담겼지만 데이터 파일과 서로 동기화돼 있는 상태여서 언제든지 덮어 써도 무방한 버퍼 블록을 말한다. 데이터 파일로부터 새로운 데이터 블록을 로딩하려면 먼저 Free 버퍼를 확보해야 한다. Free 상태인 버퍼에 변경이 발생하면 그 순간 Dirty 버퍼로 상태가 바뀐다.
• Dirty 버퍼 : 버퍼에 캐시된 이후 변경이 발생했지만, 아직 디스크에 기록되지 않아 데이터 파일 블록과 동기화가 필요한 버퍼 블록을 말한다. 이 버퍼 블록들이 다른 데이터 블록을 위해 재사용되려면 디스크에 먼저 기록되어야 하며, 디스크에 기록되는 순간 Free 버퍼로 상태가 바뀐다.
• Pinned 버퍼 : 읽기 또는 쓰기 작업이 현재 진행 중인 버퍼 블록을 말한다.

2) LRU 알고리즘

버퍼 캐시는 유한한 자원이므로 모든 데이터를 캐싱해 둘 수 없다. 따라서 모든 DBMS는 사용빈도가 높은 데이터 블록 위주로 버퍼 캐시가 구성되도록 LRU(least recently used) 알고리즘을 사용한다. 모든 버퍼 블록 헤더를 LRU 체인에 연결해 사용빈도 순으로 위치를 옮겨가다가, Free 버퍼가 필요해질 때면 액세스 빈도가 낮은 쪽(LRU end) 데이터 블록부터 밀어내는 방식이다.

나. 공유 풀(Shared Pool)

공유 풀은 딕셔너리 캐시와 라이브러리 캐시로 구성되며, 버퍼 캐시처럼 LRU 알고리즘을 사용한다. SQL Server에서 같은 역할을 하는 메모리 영역을 '프로시저 캐시(Procedure Cache)'라고 부른다.

• 딕셔너리 캐시
  데이터베이스 딕셔너리(Dictionary)는 테이블, 인덱스 같은 오브젝트는 물론 테이블 스페이스, 데이터 파일, 세그먼트, 익스텐트, 사용자, 제약에 관한 메타 정보를 저장하는 곳.
• 라이브러리 캐시
  라이브러리 캐시(Library Cache)는 사용자가 수행한 SQL문과 실행계획, 저장 프로시저를 저장해 두는 캐시영역.

실행계획(execution plan) : 사용자가 SQL 명령어를 통해 결과집합을 요청하면 이를 최적으로(→가장 적은 리소스를 사용하면서 가장 빠르게) 수행하기 위한 처리 루틴을 생성

하드 파싱(Hard Parsing) :쿼리 구문을 분석해서 문법 오류 및 실행 권한 등을 체크하고, 최적화(Optimization) 과정을 거쳐 실행계

획을 만들고, SQL 실행엔진이 이해할 수 있는 형태로 포맷팅하는 전 과정.

(특히 최적화 과정은 하드 파싱을 무겁게 만드는 가장 결정적 요인)

다. 로그 버퍼(Log Buffer)

로그 엔트리도 파일에 곧바로 기록하는 것이 아니라 먼저 로그 버퍼에 기록한다.

(건건이 디스크에 기록하기보다 일정량을 모았다가 기록하면 훨씬 빠르기 때문)

서버 프로세스가 데이터 블록 버퍼에 변경을 가하기 전에 Redo 로그 버퍼에 먼저 기록해 두면 주기적으로 LGWR 프로세스가 Redo 로그 파일에 기록한다.

(Oracle의 Redo 로그, Redo 로그 버퍼와 대비되는 개념이 SQL Server의 트랜잭션 로그, 로그 캐시)

변경이 가해진 Dirty 버퍼를 데이터 파일에 기록하기 전에 항상 로그 버퍼를 먼저 로그 파일에 기록해야만 하는데, 그 이유는 인스턴스 장애가 발생할 때면 로그 파일에 기록된 내용을 재현해 캐시 블록을 복구하고, 최종적으로 커밋되지 않은 트랜잭션은 롤백해야 한다.

이때, 로그 파일에는 없는 변경내역이 이미 데이터 파일에 기록돼 있으면 사용자가 최종 커밋하지 않은 트랜잭션이 커밋되는 결과를 초래하기 때문.

정리 : 버퍼 캐시 블록을 갱신하기 전에 변경사항을 먼저 로그 버퍼에 기록해야 하며, Dirty 버퍼를 디스크에 기록하기 전에 해당 로그 엔트리를 먼저 로그 파일에 기록해야 하는데, 이를 'Write Ahead Logging'이라고 한다.

로그 파일에 기록했음이 보장돼야 안심하고 커밋을 완료할 수 있다.

라. PGA(Process Global Area)

PGA는 다른 프로세스와 공유되지 않는 독립적인 메모리 공간으로서, 래치 메커니즘이 필요 없어 똑같은 개수의 블록을 읽더라도 SGA 버퍼 캐시에서 읽는 것보다 훨씬 빠르다.

• User Global Area(UGA)
• 각 세션을 위한 독립적인 메모리 공간이 필요해지는데, 이를 'UGA(User Global Area)'라고함
• 전용서버 : 프로세스와 세션이 1:1관계(PGA에 할당)
• 공유서버 : 프로세스와 세션이 1:M관계(SGA 라지풀설정지 라지풀 또는 Shared pool에 할당)
• Call Global Area(CGA)
  PGA에 할당되는 메모리 공간으로는 CGA도 있다. Oracle은 하나의 데이터베이스 Call을 넘어서 다음 Call까지 계속 참조되어야 하는 정보는 UGA에 담고, Call이 진행되는 동안에만 필요한 데이터는 CGA에 담는다.
• CGA는 Parse Call, Execute Call, Fetch Call마다 매번 할당받는다. Call이 진행되는 동안 Recursive Call이 발생하면 그 안에서도 Parse, Execute, Fetch 단계별로 CGA가 추가로 할당된다. CGA에 할당된 공간은 하나의 Call이 끝나자마자 해제돼 PGA로 반환된다.
• Sort Area
  데이터 정렬을 위해 사용되는 영역.
• 소트 오퍼레이션이 진행되는 동안 공간이 부족해질 때마다 청크(Chunk) 단위로 조금씩 할당.
• 세션마다 사용할 수 있는 최대 크기를 예전에는 sort_area_size 파라미터로 설정하였으나, 9i부터는 새로 생긴 workarea_size_policy 파라미터를 auto(기본 값)로 설정하면 Oracle이 내부적으로 결정.

PGA 내에서 Sort Area가 할당되는 위치는 SQL문 종류와 소트 수행 단계에 따라 다르다.

DML 문장은 하나의 Execute Call 내에서 모든 데이터 처리를 완료하므로 Sort Area가 CGA에 할당된다.

SELECT 문장의 경우, 수행 중간 단계에 필요한 Sort Area는 CGA에 할당되고, 최종 결과집합을 출력하기 직전 단계에 필요한 Sort Area는 UGA에 할당된다.

쓰레드(Thread) 기반 아키텍처를 사용하는 SQL Server는 프로세스 전용 메모리 영역을 갖지 않는다. 대신, 데이터 정렬은 Memory Pool 안에 있는 버퍼 캐시에서 수행하며, 세션 관련 정보는 Memory Pool 안에 있는 Connection Context 영역에 저장한다.

5. 대기 이벤트

DBMS 내부에서 활동하는 수많은 프로세스 간에는 상호작용이 필요하며, 그 과정에서 다른 프로세스가 일을 마칠 때까지 기다려야만 하는 상황이 자주 발생.

그때마다 해당 프로세스는 자신이 일을 계속 진행할 수 있는 조건이 충족될 때까지 수면(sleep) 상태로 대기하는데, 그 기간에 정해진 간격으로(1초, 3초 등) 각 대기 유형별 상태와 시간 정보가 공유 메모리 영역에 저장.

대개 누적치만 저장되지만, 사용자가 원하면(10046 이벤트 트레이스를 활성화하면) 로그처럼 파일로 기록해 주기도 한다.

이러한 대기 정보를 Oracle에서는 '대기 이벤트(Wait Event)'라고 부르며, SQL Server에서는 '대기 유형(Wait Type)'이라고 부른다.

세션 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 문제를 해결하는 방법과 과정을 다루는 이 방법론은, 데이터베이스 서버의 응답 시간을 서비스 시간과 대기 시간의 합으로 정의하고 있다.

Response Time = Service Time + Wait Time
= CPU Time + Queue Time

서비스 시간(Service Time) : 프로세스가 정상적으로 동작하며 일을 수행한 시간(CPU Time)

대기 시간(Wait Time) : 프로세스가 잠시 수행을 멈추고 대기한 시간. ('Queue Time')

Response Time Analysis 방법론은 Response Time을 위와 같이 정의하고, CPU Time과 Wait Time을 각각 break down 하면서 서버의 일량과 대기 시간을 분석해 나간다.

CPU Time은 파싱 작업에 소비한 시간인지 아니면 쿼리 본연의 오퍼레이션 수행을 위해 소비한 시간인지를 분석.

Wait Time은 각각 발생한 대기 이벤트들을 분석해 가장 시간을 많이 소비한 이벤트 중심으로 해결방안을 모색.

Oracle 10g 기준으로 대기 이벤트 개수는 890여 개

가. 라이브러리 캐시 부하

아래는 라이브러리 캐시에서 SQL 커서를 찾고 최적화하는 과정에 경합이 발생했음을 나타나는 대기 이벤트다.

• latch: shared pool
• latch: library cache

라이브러리 캐시와 관련해 자주 발생하는 대기 이벤트로는 아래 2가지가 있는데, 이들은 수행 중인 SQL이 참조하는 오브젝트에 다른 사용자가 DDL 문장을 수행할 때 나타난다.

• library cache lock
• library cache pin

나. 데이터베이스 Call과 네트워크 부하

아래 이벤트에 의해 소모된 시간은 애플리케이션과 네트워크 구간에서 소모된 시간으로 이해하면 된다.

• SQL*Net message from client
• SQL*Net message to client
• SQL*Net more data to client
• SQL*Net more data from client

SQL*Net message from client 이벤트는 사실 데이터베이스 경합과는 관련이 없다.

클라이언트로부터 다음 명령이 올 때까지 Idle 상태로 기다릴 때 발생하기 때문이다

나머지 세 개의 대기 이벤트는 실제 네트워크 부하가 원인일 수 있다. SQL*Net message to client와 SQL*Net more data to client 이벤트는 클라이언트에게 메시지를 보냈는데 메시지를 잘 받았다는 신호가 정해진 시간보다 늦게 도착하는 경우에 나타나며, 클라이언트가 너무 바쁜 경우일 수도 있다. SQL*Net more data from client 이벤트는 클라이언트로부터 더 받을 데이터가 있는데 지연이 발생하는 경우다.

다. 디스크 I/O 부하

아래는 모두 디스크 I/O가 발생할 때마다 나타나는 대기 이벤트이다.

• db file sequential read (single Block I/O 수행시)
• db file scattered read (Multiblock I/O 수행시)
• direct path read
• direct path write
• direct path write temp
• direct path read temp
• db file parallel read

Single Block I/O : 한번의 I/O Call에 하나의 데이터 블록만 읽는 것을 말한다. 인덱스 블록을 읽을 때, 그리고 인덱스를 거쳐 테이블

블록을 액세스할 때 이 방식을 사용.

Multiblock I/O : I/O Call이 필요한 시점에 인접한 블록들을 같이 읽어 메모리에 적재하는 것.

Table Full Scan 또는 Index Fast Full Scan 시 나타난다.

라. 버퍼 캐시 경합

아래는 버퍼 캐시에서 블록을 읽는 과정에 경합이 발생했음을 나타나는 대기 이벤트이다.

• latch: cache buffers chains
• latch: cache buffers lru chain
• buffer busy waits
• dfree buffer waits

버퍼 캐시에서 블록을 읽더라도 이들 대기 이벤트가 심하게 발생하는 순간 동시성은 현저히 저하되는데, 이들 대기 이벤트를 해소하는 방안도 디스크 I/O 부하 해소 방안과 다르지 않다. 따라서 이들 경합의 해소 원리도 4절과 더불어 4장, 5장에서 함께 다루게 된다.

마. Lock 관련 대기 이벤트

아래 'enq'로 시작되는 대기 이벤트는 Lock과 관련된 것으로서, 그 발생 원인과 해소 방안을 2장에서 일부 소개한다.

• enq: TM - contention
• enq: TX - row lock contention
• enq: TX - index contention
• enq: TX - allocate ITL entry
• enq: TX contention
• latch free

latch free는 특정 자원에 대한 래치를 여러 차례(2,000번 가량) 요청했지만 해당 자원이 계속 사용 중이어서 잠시 대기 상태로 빠질 때마다 발생하는 대기 이벤트다.

Lock : 사용자 데이터를 보호하

래치 : SGA에 공유돼 있는 갖가지 자료구조를 보호할 목적으로 사용하는 가벼운 Lock.

래치도 일종의 Lock이지만, 큐잉(Queueing) 메커니즘을 사용하지 않는다. 따라서 특정 자원에 액세스하려는 프로세스는 래치 획득에 성공할 때까지 시도를 반복할 뿐, 우선권을 부여 받지는 못한다. 이는 가장 먼저 래치를 요구했던 프로세스가 가장 늦게 래치를 얻을 수도 있음을 뜻한다.

지금까지 소개한 것 외에 자주 발생하는 대기 이벤트로는 아래와 같은 것들이 있다.

• log file sync
• checkpoint completed
• log file switch completion
• log buffer space

한개의 Oracle Database 내에 취급가능한 데이터 파일의 최대갯수는 64000개 라는군요.

그리고 한개의 tablespace (small tablespace) 가 가질수 있는 파일의 갯수는 1022개 라고 하네요.

(11gR2 기준)

SQL> -- 현재 오라클에게 셋팅되어 있는 테이블스페이스 타입

col property_value for a32

select PROPERTY_NAME, PROPERTY_VALUE

from DATABASE_PROPERTIES

where PROPERTY_NAME = 'DEFAULT_TBS_TYPE';

SQL> -- 디폴트 테이블스페이스를 변경 [BIGFILE]

alter database set default BIGFILE tablespace;

SQL> -- 디폴트 테이블스페이스를 변경 [SMALLFILE]

alter database set default SMALLFILE tablespace;

JDBC Version JDK Driver File Name

------------ ---- ----------------

11.2.0 1.6.x ojdbc6.jar

1.5.x ojdbc5.jar

11.1.0 1.6.x ojdbc6.jar

1.5.x ojdbc5.jar

10.2.0 1.5.x ojdbc14.jar

1.4.x ojdbc14.jar

1.3.x classes12.jar

1.2.x classes12.jar

10.1.0 1.4.x ojdbc14.jar

1.3.x classes12.jar

1.2.x classes12.jar

9.2.0 1.4.x ojdbc14.jar

1.3.x classes12.zip

1.2.x classes12.zip

1.1.x classes111.zip