| |
지금까지 디스크의 물리적 특성으로 인해 성능이 저하되는 현상이 왜 발생하는지 알아보았습니다. 그럼, 오라클 데이터베이스에서 디스크 상에 존재하는 파일들이 언제 읽기/쓰기 작업을 하게 되는지 알아봅시다. 3장에서 "SELECT문의 처리과정"과 "DML문의 처리과정" 그리고 "COMMIT"문의 처리과정을 통해 데이터베이스의 각 구조가 데이터 파일과 어떤 상관관계를 가지고 있는지 알아보았습니다. 즉, 사용자가 실행하는 모든 SELECT, UPDATE, INSERT, DELETE문이 실행될 때마다 디스크에 존재하는 파일로부터 I/O가 발생하며 이러한 I/O의 발생을 최소화 시켜주고 분산시켜줌으로서 데이터베이스의 성능을 향상시킬 수 있는 것입니다.
위 그림은 오라클 데이터베이스의 백그라운드 프로세스와 DISK I/O 와의 상관관계를 그림으로 표현한 것입니다. 한 예로, 개발자가 SELECT문을 실행하면 서버 프로세스는 데이터 파일(READ)로부터 테이블을 읽은 다음 데이터버퍼 캐시영역에 로더하게 됩니다. 또한, COMMIT문을 실행하면 DBWR 프로세스는 데이터버퍼 캐시의 내용을 데이터 파일(WRITE)에 저장하게 됩니다. 이와 같이, 모든 SQL문이 실행될 때 마다 관련 파일로부터 디스크 I/O가 발생하게 됩니다. 그리고, 수십 명, 수백 명의 사용자들이 동시에 SQL문을 수행한다면 디스크 I/O 문제로 인해 경합이 발생하며 성능이 저하되게 됩니다. |
| |
|
| |
 디스크 I/O 튜닝
|
| |
디스크 I/O에 대한 튜닝 방법에 대해서 알아보겠습니다. 데이터베이스가 생성되면 기본적인 데이터 파일이 생성되며 사용자는 추가적인 데이터 파일을 생성하게 되는데 물리적 구조의 특성을 충분히 고려하지 않는다면 디스크의 경합 및 동시성으로 인한 문제로 데이터베이스의 성능이 저하될 수도 있습니다.
다음은 V$FILESTAT 자료사전을 통해 디스크 I/O 상태를 분석하는 방법입니다. |
| |
SQL > | SELECT tablespace_name, name, phyrds, phtwrts | | | FROM v$datafile df, v$filestat fs | | | WHERE df.file# = fs.file#; |
|
| |
[PHYRDS] 컬럼은 디스크로부터 읽기 작업을 한 횟수를 의미하며 [PHYWRTS] 컬럼은 디스크에 쓰기 작업을 한 횟수입니다. 만약, 어떤 데이터 파일에 대해 읽기/쓰기 작업이 과다하게 발생한다면 해당 데이터 파일에 저장되어 있는 테이블 검색시 성능이 저하되게 될 것 입니다.
위 그림을 보면 DISK-1에 SYSTEM.DBF 파일과 DATA1.DBF 파일이 저장되어 있고, DISK-2에는 DATA2.DBF 파일과 INDEX1.DBF, UNDO.DBF 파일이 저장되어 있으며 DISK-3에는 TEMP.DBF 파일이 저장되어 있습니다. V$FILESTAT 자료사전의 분석결과를 참조해 보면 SYSTEM 테이블스페이스의 SYSTEM.DBF와 UNDO 테이블스페이스의 UNDO.DBF 파일이 저장되어 있는 DISK-1과 DISK-2에 매우 많은 읽기/쓰기 작업이 진행되고 있음을 확인할 수 있습니다. |
| |
Tablespace_Name | Name | PHY_READS | PHY_WRTS | UNDO | /disk2/rbs.dbf | 1200 | 5259 | TEMP | /disk3/temp.dbf | 650 | 634 | DATA1 | /disk1/data1.db | 202 | 94 | DATA2 | /disk2/data2.db | 62 | 50 | INDEX1 | /disk2/index1.dbf | 42 | 3 | SYSTEM | /disk1/system.dbf | 740 | 44 |
|
| |
첫 번째 문제점은 SYSTEM 테이블스페이스는 데이터 딕션너리 테이블 정보가 저장되어 있는 공간이기 때문에 계속적인 I/O가 증가할 수 밖에 없는 구조적 특징을 가지고 있습니다. 그런데, 사용자의 테이블이 저장되어 있는 DATA1.DBF 파일이 같은 디스크-1에 저장되어 있기 때문에 검색시 성능이 저하될 수 밖에 없는 상황입니다.
두 번째 문제점은 UNDO 테이블스페이스는 사용자들이 변경작업(UPDATE, INSERT, DELETE)을 실행할 때 ROLLBACK을 위해 변경 전 데이터를 잠시 저장하는 공간입니다. 데이터베이스를 사용하는 한 지속적인 변경작업이 진행될 수 밖에 없기 때문에 디스크 I/O가 증가할 수 밖에 없는 구조적 특징을 가지고 있습니다. 그런데, 사용자의 테이블이 저장되어 있는 DATA2.DBF 파일과 INDEX1.DBF 파일이 같은 디스크-2에 저장되어 있기 때문에 검색시 성능이 저하될 수 밖에 없는 상황입니다.
자, 이번에는 SCOTT 사용자로 접속하여 관련 테이블에 대한 SELECT문을 수행한 후, 파일 I/O가 어떻게 변경되었는지 다시 분석해 봅시다. |
| |
$ sqlplus scott/tiger | SQL > | select * from big_dept; | SQL > | select * from big_emp; | SQL > | col name format a52 | SQL > | select f.phyrds, f.phywrts, d.name
from v$datafile d, v$filestat f
where d.file# = f.file#; |
|
| |
Tablespace_Name | Name | PHY_READS | PHY_WRTS | UNDO | /disk2/rbs.dbf | 1210 | 5259 | TEMP | /disk3/temp.dbf | 650 | 634 | DATA1 | /disk1/data1.db | 202 | 94 | DATA2 | /disk2/data2.db | 62 | 50 | INDEX1 | /disk2/index1.dbf | 42 | 3 | SYSTEM | /disk1/system.dbf | 755 | 44 |
|
| |
어떤 테이블스페이스의 물리적 읽기/쓰기회수가 증가되었는지 확인해 보십시오. |
| |
|
| |
 데이터 파일의 설계
|
| |
디스크의 I/O 문제로 인한 경합현상과 동시성 문제로 인해 성능이 저하되는 문제를 해결하기 위해서는 데이터베이스 설치단계에서 충분한 고려를 하여 물리적 설계를 하는 것이 문제를 최소화할 수 있는 방법입니다. |
| |
| | |
| |
| |
| 데이터 테이블스페이스와 SYSTEM 테이블스페이스는 분리하라. |
| |
| | SYSTEM 테이블스페이스는 자료사전 테이블, 뷰 등이 있는 저장공간입니다. 사용자가 생성한 테이블들이 새로운 익스텍트를 할당하거나 또는 삭제할 때 , 객체가 생성되거나 삭제될 때 등 모든 변화에 대한 상태정보를 자료사전 테이블에 저장합니다. 그런데, 사용자가 생성한 테이터 파일이 SYSTEM 테이블스페이스의 데이터 파일과 같은 디스크에 존재한다면 디스크 경합현상으로 인해 데이터베이스의 전체 성능이 저하될 수 있습니다. |
| |
| |
| |
| 데이터 테이블스페이스와 인덱스 테이블스페이스는 분리하라. |
| |
| | "디스크의 동시성"을 통해 알아보았듯이 인덱스를 가진 테이블에 대한 검색 시 발생하는 문제를 해결하기 위해 테이블을 가진 데이터 파일과 인덱스를 가진 데이터 파일을 물리적으로 다른 디스크에 생성해야 합니다. |
| |
| |
| |
| 각 데이터 테이블스페이스는 I/O 경합을 줄이기 위해 분리하라. |
| |
| | 가능하다면 각각의 데이터 파일들을 물리적으로 다른 디스크에 저장하는 것이 I/O를 분산시킬 수 있는 가장 최적의 방법입니다. 자주 사용되는 데이터 파일들은 반드시 같은 디스크에 생성하지 마십시오. |
| |
| |
| |
| 데이터 테이블스페이스와 UNDO 테이블스페이스는 분리하라. |
| |
| | 데이터베이스에 접속하는 사용자들은 무조건 하나의 언두 세그멘트를 할당 받습니다. 언두 세그멘트는 많은 사용자들이 공유하는 저장공간이기 때문에 데이터가 저장되어 있는 디스크와 같이 사용하게 되면 디스크 경합현상이 발생합니다. 때문에 테이블에 대한 데이터 파일과 물리적으로 다른 디스크에 생성해야 경합을 줄 일수 있습니다. |
| |
| |
| 데이터 테이블스페이스와 TEMP 테이블스페이스는 분리하라. |
| |
| | 말그대로 데이터 테이블스페이스와 TEMP 테이블스페이스는 분리하십시오. |
| |
| |
| 대용량의 분류작업을 위해 TEMP 테이블스페이스를 각 사용자별로 할당하라. |
| |
| | 언두 세그멘트와 마찬가지로 하나의 TEMPORARY 테이블스페이스는 많은 사용자들이 동시에 사용하는 공유영역이기 때문에 디스크 경합현상이 발생합니다. 때문에 테이블에 대한 데이터 파일과 물리적으로 다른 디스크에 생성해야 경합을 줄 일수 있습니다. 또한, 여러 개의 TEMPORARY 테이블스페이스 생성하여 데이터베이스 사용자별로 할당하는 것이 DISK-I/O 문제로 인한 경합현상을 피할 수 있습니다. |
| | |
| | SQL > | create tablespace temp10
datafile '$HOME/dbs/temp10.dbf' size 2m TEMPORARY; | SQL > | create tablespace temp11
datafile '$HOME/dbs/temp11.dbf' size 2m TEMPORARY; | | | ◀ SCOTT 사용자에게는 TEMP10을, HR 사용자에게는 TEMP11을 사용하게 하십시오. | SQL > | alter user scott temporary tablespace temp10; | SQL > | alter user hr temporary tablespace temp11; |
|
| |
| |
| 테이블스페이스 생성시 전체 경로를 지정하여 기본경로에 생성되는 것을 피하라. |
| |
| | 테이블스페이스를 생성할 때 데이터 파일의 절대경로를 지정하지 않으면 오라클 서버가 지정하는 기본경로에(SYSTEM 테이블스페이스) 데이터 파일을 생성해 줍니다. 절대경로를 지정하지 않고 테이블스페이스를 생성했다면 모든 데이터 파일들은 같은 디스크, 같은 절대경로에 생성되기 때문에 디스크 경합현상이 발생할 것입니다. |
| | |
| | 다음 문장은 개발자가 실수로 SYSTEM 테이블스페이스에 테이블을 생성한 경우 분석하는 방법입니다. |
| | |
| | SQL > | SET LINESIZE 500 | SQL > | col owner format a10 | SQL > | col segment_name format a25 | SQL > | col tablespace_name format a15 | | | | SQL > | select owner, segment_name, segment_type, tablespace_name
from dba_segments
where tablespace_name = 'SYSTEM'and owner = 'SCOTT; |
|
| | |
| | OWNER | SEGMENT_NAME | SEGMENT_TYPE | TABLESPACE_NAME | SCOTT | BIG_DEPT | TABLE | SYSTEM | SCOTT | ACCOUNT | TABLE | SYSTEM | SCOTT | EMP | TABLE | SYSTEM | SCOTT | DEPT | TABLE | SYSTEM | SCOTT | SALGRADE | TABLE | SYSTEM | SCOTT | ACCOUNT1 | TABLE | SYSTEM |
|
| |
| |
| |
| LOCALLY 테이블스페이스를 사용하라. |
| |
| | 오라클 8i버전부터 추가된 로컬리매니저 테이블스페이스를 사용하면 SYSTEM 테이블스페이스에 저장되는 자료사전 정보를 각 테이블이 생성되는 로컬리 테이블스페이스에 저장함으로서 디스크 경합현상을 최소화 시킬 수 있습니다. |
| | |
|
| |
 물리적 설계의 튜닝
|
| |
디스크 I/O 와 경합문제로 인해 성능이 저하되면 먼저, 어떤 디스크에 있는, 어떤 데이터 파일에 I/O가 얼마나 발생하는지 분석해야 합니다. 이러한 문제는 데이터베이스의 설치와 추가적인 데이터 파일의 생성시 정확한 이해와 분석 없이 데이터베이스를 구축하게 되면 발생하는 문제점들 입니다. 사실 이런 문제가 발생하는 보다 근본적인 원인은 데이터베이스의 물리적 설계 단계에서 성능에 대한 충분한 고려를 하지 못한 경우입니다. (3장 "시스템 개발절차와 성능", "물리적 설계"를 참조하십시오.)
이러한 경우에는 두 가지 방법으로 튜닝할 수 있는데, 첫 번째 방법을 사전튜닝이라고 합니다. 즉, 물리적 분석/설계 단계에서 디스크 I/O와 경합을 최소화 시킬 수 있는 방법으로 설계하는 것 입니다. 데이터베이스가 구축되기 전 상태에서 성능을 고려하는 방법을 사전튜닝이라고 합니다. (3장 "시스템 개발절차와 성능", "물리적 설계"를 참조하십시오.)
가장 이상적인 형태의 튜닝방법 입니다.
두 번째 방법은 사후 튜닝입니다. 데이터베이스 분석/설계 단계에서 충분한 고려를 하지 못하였거나 잘못된 분석으로 인해 데이터베이스 구축 후 성능이 저하되는 문제가 발생하여 튜닝하는 방법을 사후 튜닝이라고 합니다. 여기서는 사후튜닝 방법을 자세히 소개하도록 하겠습니다.
위 그림을 참조해 보면, DISK-1에 SYSTEM.DBF 파일과 DATA1.DBF 파일이 저장되어 있고, DISK-2에는 DATA2.DBF 파일과 INDEX1.DBF, UNDO.DBF 파일이 저장되어 있으며 DISK-3에는 TEMP.DBF 파일이 저장되어 있습니다. V$FILESTAT 자료사전의 분석결과를 참조해 보면 SYSTEM 테이블스페이스의 SYSTEM.DBF와 UNDO 테이블스페이스의 UNDO.DBF 파일이 저장되어 있는 DISK-1과 DISK-2에 매우 많은 읽기/쓰기 작업이 진행되고 있음을 확인할 수 있습니다.
그리고, 시스템에는 DISK-4가 사용 가능한 상태로 남아 있습니다.
먼저, DISK-2에 있는 UNDO.DBF 파일은 디스크 I/O가 많이 발생하는 파일 중에 하나이기 때문에 별도의 디스크에 배치하는 것이 데이터베이스 전체 성능향상을 위해 좋습니다.
그리고, DISK-1에 있는 SYSTEM.DBF 파일도 가장 많은 디스크-I/O를 유발시키기 때문에 DATA1.DBF 파일을 DISK-4로 이동시켜 전체 4개의 디스크에 평균적으로 I/O가 발생할 수 있도록 이동시키는 것이 좋습니다. |
| |
| | |
| |
| |
| 먼저, 데이터베이스를 종료(SHUTDOWN) 합니다. |
| |
| | SQL > | SHUTDOWN TRANSACTIONAL | SQL > | EXIT |
|
| |
| |
| 운영체계 상에서 UNDO 테이블스페이스의 데이터 파일을 DISK-4에 복사하고, DATA1.DBF 파일도 DISK4로 복사 하십시오. |
| |
| | | | $ cp $HOME/disk2/undo.dbf $HOME/disk4/undo.dbf | | | $ cp $HOME/disk1/data1.dbf $HOME/disk4/data1.dbf |
|
| |
| |
| |
| 데이터베이스를 MOUNT 단계로 시작합니다. |
| |
| | | | $ sqlplus "/as sysdba" | SQL > | STARTUP MOUNT |
|
| |
| |
| |
| UNDO 데이터 파일은 DISK-2에서 DISK-4로, DATA1.DBF 파일은 DISK-1에서 DISK-4로 RENAME 합니다. |
| |
| | SQL > | ALTER DATABASE RENAME '$HOME/disk2/undo.dbf' TO '$HOME/disk4/undo.dbf' | SQL > | ALTER DATABASE RENAME '$HOME/disk1/data1.dbf' TO '$HOME/disk4/data1.dbf' |
|
| |
| |
| 데이터베이스를 사용 가능한 상태로 오픈한 후 이전의 데이터 파일은 삭제합니다. |
| |
| | SQL > | ALTER DATABASE OPEN; | SQL > | EXIT |
|
| |
| |
| 데이터 파일의 튜닝이 완료되고 나면 다시 V$FILESTAT 자료사전을 통해 전체 디스크에서 발생하는 DISK-I/O 상태를 모니터링 해 보십시오. |
| |
| | 언두 세그멘트와 마찬가지로 하나의 TEMPORARY 테이블스페이스는 많은 사용자들이 동시에 사용하는 공유영역이기 때문에 디스크 경합현상이 발생합니다. 때문에 테이블에 대한 데이터 파일과 물리적으로 다른 디스크에 생성해야 경합을 줄 일수 있습니다. 또한, 여러 개의 TEMPORARY 테이블스페이스 생성하여 데이터베이스 사용자별로 할당하는 것이 DISK-I/O 문제로 인한 경합현상을 피할 수 있습니다. |
| | | | $ sqlplus scott/tiger | SQL > | select * from big_dept; | SQL > | select * from big_emp; | SQL > | col name format a52 | SQL > | select f.phyrds, f.phywrts, d.name
from v$datafile d, v$filestat f
where d.file# = f.file#; |
|
| |
Tablespace_Name | Name | PHY_READS | PHY_WRTS | UNDO | /disk4/rbs.dbf | 1210 | 5259 | TEMP | /disk3/temp.dbf | 1650 | 4634 | DATA1 | /disk4/data1.db | 1202 | 3594 | DATA2 | /disk2/data2.db | 1262 | 230 | INDEX1 | /disk2/index1.dbf | 1042 | 453 | SYSTEM | /disk1/system.dbf | 1555 | 1244 |
|
| |
각 디스크에서 발생하는 물리적 읽기/쓰기 회수가 균등하게 발생하고 있는지 분석해 보십시오. 이 경우는 SYSTEM 테이블스페이스 또는 NON-SYSTEM 테이블스페이스의 데이터 파일에 모두 적용할 수 있는 절차입니다. 만약, 재 배치하려는 파일이 NON-SYSTEM 데이터 파일이라면 반드시 데이터베이스를 종료한 후 작업할 필요 없이 오픈 상태에서도 RENAME이 가능합니다. |
| |
다음 예제를 따라 해 보십시오.
이번에는 NON-SYSTEM 데이터 파일을 오픈 상태에서 재배치하는 절차입니다. |
