[오라클]3-1단계 Undo 세그먼트

UNDO 세그멘트   한 명의 사용자가 SQL*PLUS 툴 또는 애플리케이션을 통해 데이터베이스에 접속하면 오라클 서버는 UNDO 테이블스페이스에 미리 생성되어 있는 UNDO 세그멘트 중 가장 적게 사용중인 세그멘트를 사용자에게 할당해 줍니다. 언두 세그멘트를 할당 받은 사용자는 UPDATE, INSERT, DELETE 작업을 통해 만들어진 언두(ROLLBACL문의 실행을 위해 백업해두는 정보) 정보를 언두 세그멘트에 트랜잭션이 종료될 때까지 저장하게 됩니다. 하나의 언두 세그멘트는 여러 명의 사용자에 의해 공유되는 공간이며 데이터베이스 접속 시 무조건 하나의 언두 세그멘트를 할당 받습니다. 또한 , 오라클 이전버전에서 사용되던 롤백 테이블스페이스와 롤백 세그멘트는 데이터베이스 관리자에 의해 수동으로 공간관리가 되었지만 오라클 9i 버전의 언두 테이블스페이스와 언두 세그멘트는 오라클 서버가 자동으로 관리해 주기 때문에 훨씬 사용이 간편하고 쉬워졌습니다. 하지만, 하나의 언두 세그멘트는 한 명의 사용자가 독점적으로 사용하는 것이 아니라 여러 명의 사용자가 동시에 사용하는 공유 공간이기 때문에 항상 경합 현상이 발생할 수 있으며 결론적으로 이러한 문제들로 인해 성능이 저하되는 현상이 초래되기도 합니다.         언두 세그멘트의 경합현상   하나의 언두 세그멘트는 하나의 트랜잭션에 의해서만 사용되는 것이 아니라 여러 명의 사용자들이 실행한 여러 개의 트랜잭션이 저장되는 공유공간 입니다 . 동시에 많은 사용자들이 INSERT, UPDATE, DELETE문을 실행하게 되면 제한된 개수의 롤백 세그멘트를 나누어서 사용해야 하기 때문에 경합현상이 발생하게 됩니다. 즉 , 경합현상은 대기상태를 의미함으로 먼저 실행된 DML문이 처리될 때 까지 나중에 요구된 DML문은 일시적인 대기(WAIT)를 하게 됩니다. 다음 문장은 현재 활성화되어 있는 언두 세그멘트의 수와 현재 상태를 분석하는 방법입니다 .   SQL > SELECT usn, extents, writes, xacts, waits, hwmsize, status FROM v$rollstat;   USN EXTENTS WRITES XACTS WAITS STATUS 0 7 5808 0 0 ONLINE 1 3 1149154 0 0 ONLINE 2 3 1566806 0 0 ONLINE 3 3 1284076 0 0 ONLINE 4 3 1174812 0 0 ONLINE 5 3 1803600 0 0 ONLINE 6 3 1076738       7 3 1573834       8 4 1504118         [USN] 컬럼은 언두 세그멘트 번호입니다. 0 번은 시스템 언두 세그멘트이고 1 번부터 8 번까지는 일반 사용자들의 트랜잭션 데이터가 저장되는 언두 세그멘트입니다. [EXTENTS] 컬럼은 현재 각 언두 세그멘트가 활성화되어 있는 익스텐트 수입니다. [WRITES] 컬럼은 언두 세그멘트에 저장된 언두 데이터의 크기입니다. [XACTS] 컬럼은 하나의 언두 세그멘트에 현재 몇 개의 트랜잭션 데이터가 저장되어 있는지를 보여줍니다. 또한, [WAITS] 컬럼은 해당 언두 세그멘트에 발생한 대기상태의 회수를 나타냅니다 다음 문장은 언두 세그멘트에 경합현상이 발생하는지를 분석하는 방법입니다 .   SQL > SELECT sum(waits) * 100 / sum(gets) "비율", Sum(waits) "Waits", sum(gets) "Gets" FROM V$ROLLSTAT;   비율 Waits Gets 0 0 44879   GETS 컬럼은 언두 세그멘트를 사용했던 블록 수이며 WAITS 컬럼은 언두 세그멘트를 사용하려고 했을 때 경합현상이 발생하여 대기했던 블록수를 의미합니다. WAITS / GETS 의 계산에 의해 얼마나 많은 대기상태가 발생하였는지를 백분율로 알아낼 수 있습니다. 이 비율의 값이 5% 이상이면 언두 세그멘트에 경합이 발생하고 있으므로 보다 충분한 언두 세그멘트를 생성해 주어야 경합현상을 피할 수 있습니다.       언두 세그멘트의 관리   오라클 9i 이전버전 까지는 모든 언두 세그멘트를 데이터베이스 관리자가 직접 생성하고 관리하였지만 9i 버전부터는 추가적으로 오라클 서버가 직접 생성하고 관리해주는 AUM(Auto matic Undo Management) 기능이 제공되고 있습니다. 다음은 수동관리와 자동관리 방법의 장단점에 대해 좀 더 자세히 알아 봅시다 . 1) 수동관리 - 모든 언두 세그멘트를 데이터베이스 관리자가 직접 생성하고 관리하기 때문에 관리가 어렵고, 문제가 발생하는 경우 직접 유지보수를 해야 합니다 - 세션별로 발생하는 데이터의 유형에 따라 적적한 크기의 언두 세그맨트를 할당해 줄 수 있기 때문에 각 기업에 맞는 최적의 언두 세그멘트 환경을 구현할 수 있습니다. 2) 자동관리 - 데이터베이스에서 기본적으로 사용하게 될 언두 세그멘트 공간은 오라클 서버에 의해 데이터베이스가 설치될 때 생성됩니다. 또한, 사용자 수에 따라 적절한 개수의 언두 세그멘트가 생성되기 때문에 경합현상을 최소화할 수 있습니다. - 모든 언두 세그멘트의 관리가 서버에 의해 자동관리 되기 때문에 데이터의 양과 유형에 맞는 최적의 언두 세그멘트를 세션별로 할당해 주지 못하는 단점을 가지고 있습니다. 먼저 , 자동관리에 의한 언두 세그멘트를 적용해 보시고 지속적인 경합현상이 계속 발생하면 데이터의 양과 유형에 맞는 수동관리 기법을 적용하는 것이 튜닝의 순서입니다.       Snapshot Too Old 에러의 원인과 해결   오라클 데이터베이스를 사용하다 보면 개발자들이 자주 보게 되는 에러유형 중에 Snapshot Too Old 에러는 언두 세그멘트와 관련된 경우입니다. 이런 경우의 에러가 발생하는 근본적인 원인은 충분한 크기의 언두 세그멘트가 확보되지 못했기 때문입니다 . 자~ 위 그림을 통해 이 에러가 발생하는 원인을 알아 봅시다. 위 그림을 분석해 보면 , 현재 EMP 테이블에 10,000개의 행이 저장되어 있고, 해당 세션에 RBS01 이라는 언두 세그멘트가 할당되어 있습니다. 1) A 사용자는 EMP 테이블에 대해 UPDATE문을 실행하여 10,000 행을 변경하려고 합니다. 이 문장의 실행에 의해 변경 전 데이터가 RBS01 언두 세그멘트에 저장되었습니다. 2) B 사용자는 A 사용자가 현재 변경하고 있는 EMP 테이블로부터 모든 행을 검색하는 SELECT문을 실행하고 있습니다. 이런 경우, 오라클 서버는 EMP 테이블이 A 사용자에 의해 변경되고 있는 중이기 때문에 A 사용자가 사용하고 있는 RBS01 언두 세그멘트로 부터 해당 행들을 조회하게 됩니다. 3) 이때, A 사용자는 1) 단계에서 처리했던 UPDATE문에 대해 COMMIT문을 실행하게 됩니다. 트랜잭션의 종료에 의해 RBS01 언두 세그멘트의 모든 행들은 더 이상 필요 없는 변경 전 데이터들 이지만, 즉시 제거되지는 않습니다. 그 이유는 RBS01 언두 세그멘트의 내용을 삭제하는 오퍼레이션을 실행하게 되면 불필요한 오퍼레이션으로 인해 SQL문의 성능이 저하될 수 있기 때문입니다. A 사용자의 COMMIT문에 관계없이 B 사용자는 계속해서 RBS01에 저장되어 있는 변경 전 데이터를 읽게 됩니다. 4) A 사용자는 새로운 UPDATE문을 실행하게 됩니다. 5) 새로운 UPDATE문에 의해 발생한 변경 전 데이터들은 RBS01 언두 세그멘트의 빈 공간에 저장되게 됩니다. 하지만, 곧 RBS01의 빈공간은 모두 채워지게 되고 더 이상 여유공간을 발견하지 못하면서 6) 이전에 저장되었던 언두 데이터의 공간에 새로운 변경 데이터를 저장하게 됩니다. 즉, 이전 데이터 위에 재작성 하게 됩니다. 그리고, 이때까지도 B 사용자는 계속 이전 정보를 읽게 됩니다. 7) B 사용자는 A 사용자의 작업에 관계없이 계속 RBS01 언두 세그멘트에서 해당 테이블 정보를 참조하게 되는데, 문제는 6) 단계에서 새로운 트랜잭션이 발생하면서 재 작성된 부분의 데이터를 읽으려고 할 때 입니다. 검색하려는 행이 다른 트랜잭션에 의해 삭제되었기 때문에 읽을 행이 더 이상 존재하지 않는다는 것 입니다. 이런 경우 , 발생하는 것이 SnapShot Too Old 에러 입니다. 즉, 언두 세그멘트의 크기가 너무 작아 여러 개의 트랜잭션이 연속적으로 실행되는 경우 이전 변경정보가 새로운 변경정보에 의해 삭제되는 경우가 발생하는 것 입니다. 보다 충분한 언두 세그멘트를 확보하기 위해서는 충분한 크기의 언두 테이블스페이스를 생성해야 합니다 이러한 언두 세그멘트가 부족한 현상은 궁극적으로 SQL문의 성능을 저하시키게 되고 데이터베이스 전체의 성능을 저하시킬 수도 있는 원인을 제공하기 때문에 반드시 올바른 튜닝으로 원인을 해결해야 합니다.           언두 세그멘트의 할당   동시에 많은 사용자들이 데이터베이스를 사용하다 보면 , 어떤 사용자는 소량의 데이터를 입력, 수정, 삭제할 수 도 있고 또는 대용량의 데이터를 입력, 수정, 삭제할 수 도 있습니다. 하지만, 이러한 사용자들이 동시에 데이터베이스를 사용하다 보면 하나의 언두 세그멘트를 같이 사용하는 경우가 빈번하게 발생합니다. 이런 경우, 언두 세그멘트의 대부분의 공간은 대용량 데이터를 처리하는 사용자가 사용하게 되는데, 이런 현상이 발생하게 되면 소량의 데이터를 처리하는 사용자는 언두 세그멘트를 할당 받지 못해 계속적으로 대기하는 문제가 발생하게 됩니다. 소량의 데이터를 처리하는 사용자의 성능뿐 만 아니라, 대용량 데이터를 처리하는 사용자의 작업도 성능이 저하되게 됩니다. 이런 경우 , 서로 다른 양과 다른 유형의 데이터를 처리하는 사용자에게 각각 언두 세그멘트를 따로 설정해 줄 수 있다면, 서로의 성능에 영향을 미치지 않는 범위에서 변경작업을 진행할 수 있을 것 입니다.       특정 언두 세그멘트의 할당   대용량의 데이터를 변경하는 사용자에게 별도로 하나의 언두 세그멘트를 할당해 줄 수 있다면 OLTP 성 업무를 진행하는 일반 사용자의 성능은 보다 향상될 것이며 또한 대용량 데이터를 처리하는 사용자의 성능도 보다 향상시킬 수 있을 것 입니다. 다음 절차는 특정 사용자의 세션에서 전용으로 사용할 언두 세그멘트를 생성하고 할당하는 방법입니다 .           먼저 , ALTER ROLLBACK 명령어를 실행할 수 있는 시스템 권한을 특정 사용자에게 부여하십시오.     SQL > CONNECT /AS SYSDBA SQL > GRANT alter rollback segment TO scott;       특정 사용자에게 할당할 아주 큰 언두 세그멘트를 생성하십시오 .     SQL > CREATE ROLLBACK SEGMENT BIG_ROLL STORAGE (INITIAL 5M NEXT 5M);       만약 , 개발된 애플리케이션 프로그램 중에 월마감 작업과 같이 대용량 데이터를 처리해야 하는 경우가 있다면 해당 프로그램 내에 다음 문장을 실행한 후 SQL문이 실행될 수 있도록 변경하십시오.     SQL > ALTER ROLLBACK SEGMENT BIG_ROLL ONLINE; SQL > SET TRANSACTION USE ROLLBACK SEGMENT RBS01; 먼저 , BIG_ROLL 언두 세그멘트를 온라인 시킨 후 해당 세션에서 전용으로 사용하기 위해서 SET TRANSACTION 명령문을 실행하십시로.       전용 언두 세그멘트가 활성화되었으면 처리하려는 SQL문을 실행하십시오.     SQL > UPDATE big_emp SET loc = 'seoul'; SQL > COMMIT;     변경 작업이 완료되고 나면 사용되었던 언두 세그멘트를 오프라인 시키십시오. 만약 , 작업 완료 후 오프라인 하지 않으면 다른 사용자들에 의해 사용될 수도 있기 때문에 다음에 재사용하려고 했을 때 사용하지 못할 수도 있습니다.     SQL > ALTER ROLLBACK SEGMENT BIG_ROLL OFFLINE;       Blocking Session   누구나 한번쯤 다음과 같은 경험을 해 보았을 것 입니다 . 운영체계 상에서 프로그램을 실행하다가 CTRL+C 와 같은 인터럽트(Interrupt)를 실행하게 되면 애플리케이션이 강제로 종료되는 경우입니다. 문제는 애플리케이션 만 종료되고 프로세스는 운영체계 상에 계속 남아있는 경우가 발생할 수 있는데, 이런 경우 불필요한 프로세스들로 인해 시스템의 메모리가 낭비되게 됩니다. 즉, 메모리의 낭비는 시스템 전체의 성능저하를 초래할 수 있게 됩니다. 오라클 데이터베이스를 사용하다 보면 SQL*PLUS 툴 또는 애플리케이션 프로그램을 통해 데이터베이스에 접속하여 일련의 작업을 진행하게 됩니다. 이런 경우, UPDATE, INSERT, DELETE문에 의해 트랜잭션이 발생하게 되고 언두 세그멘트에는 변경 전 데이터가 저장되게 됩니다. 문제는 트랜잭션이 정상적으로 종료되지 않고 인터럽트와 같은 비정상적인 취소로 인해 종료되는 경우입니다. 이런 경우, 언두 세그멘트에 언두 데이터가 남아 있게 되는데, 이러한 비정상적인 언두 데이터들로 인해 실제 저장되어야 할 언두 데이터가 저장되지 못해 경합현상이 발생하게 됩니다. 이러한 경우가 빈번하게 발생하면 언두 세그멘트는 부족하게 되고 데이터베이스의 전체 성능은 저하되게 됩니다. 이러한 비정상적인 세션을 블록킹 세션(BLOCKING SESSION)이라고 합니다. 다음은 데이터베이스 내에 존재하는 비정상적인 블록킹 세션을 분석하는 방법입니다 .   SQL > Select s.sid, s.serial#, t.start_time, t.xidusn, s.username From v$session s, v$transaction t, v$rollstat r Where s.saddr = t.ses_addr AND t.xidsun=r.usn AND ((r.curext = t.start_uext-1) OR ((r.curext=r.extents-1) AND t.start_uext=0));   SID SERIAL# START_TIME XIDUSM USERNAME 9 27 2003/2/12 13:25:23 2 SCOTT   이 문장의 실행에 의해 분석된 결과를 참조해 보면 , 2003년 2월 12일 SCOTT 사용자로 접속한 9번 세션이 2번째 언두 세그멘트를 사용하면서 블록킹 세션으로 남아 있는 것을 알 수 있습니다. 분석된 블록킹 세션의 접속상태를 완전히 제거하기 위해서는 ALTER SYSTEM KILL SESSION 명령어를 사용해야 합니다.   SQL > ALTER SYSTEM KILL SESSION '9,27';