'01.DB튜닝/006.단계별 서버 튜닝' 카테고리의 글 목록

'01.DB튜닝/006.단계별 서버 튜닝'에 해당되는 글 11건

[오라클]2-2단계 Redo 로그 파일

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 15:30

리두로그 파일과 아카이브 파일의 I/O 튜닝

사용자들이 UPDATE, INSERT, DELETE문을 실행하면 모든 변경 데이터는 리두로그 버퍼에 저장된 후 일정시점이 되면 LGWR 백그라운드 프로세스에 의해 리두로그 파일에 저장됩니다.

그리고, 아카이브 모드에서는 리두로그 시스템에 로그 스위치가 발생하면 ARCH 백그라운드 프로세스에 의해 리두로그 파일을 미리 지정된 경로로 복사 합니다.

이 메커니즘이 바로 오라클 데이터베이스의 백업 메커니즘입니다. 하지만, 이 메커니즘이 정상적으로 운영 되었을 경우에는 별 문제가 발생하지 않겠지만, 서로 연속적으로 발생하는 읽기,쓰기 작업이 순차적으로 진행되지 않으면 성능저하 현상이 발생하게 됩니다.

자, 그럼 보다 자세히 성능문제를 알아 봅시다.

리두로그 파일과 성능문제

만약, 여러 개의 리두로그 그룹들이 같은 디스크에 생성되어 있다면, 많은 변경 데이터를 저장할 때 연속적으로 여러 개의 리두로그 파일에 저장할 수 없는 문제가 발생합니다. 이유는 물리적 디스크 장치는 한번에 하나의 I/O 만을 유발시킬 수 있기 때문에 하나의 리두로그 파일에 변경 데이터를 저장한 후 다음 리두로그 파일에 저장하려고 할 때 다른 사용자들의 읽기,쓰기 작업으로 인해 연속적으로 쓰기 작업을 진행하지 못하고 일시적으로 대기해야 하는 문제가 발생하게 됩니다. 이러한 대기상태가 빈번하게 발생하면 데이터베이스 전체적인 성능저하 현상이 나타나게 됩니다.

< 해결방법-1 >
여러 개의 리두로그 파일들을 물리적으로 다른 여러 개의 디스크로 나누어 배치하게 되면 하나의 디스크에서 발생하는 집중현상을 여러 개의 디스크로 분산할 수 있습니다.

< 해결방법-2 >
리두로그 파일의 크기를 보다 크게 늘려 줍니다. 리두로그 파일의 크기가 너무 작으면 많은 변경 데이터를 여러 리두로그 파일에 나누어 저장해야 하기 때문에 불필요한 로그 스위치가 발생하기 때문입니다.

< 해결방법-3 >
리두로그 그룹의 수를 늘려 줍니다. 하나의 리두로그 파일의 크기를 너무 크게 설정해 두면 로그 스위치가 발생할 때 ARCH 프로세스에 의해 아카이브 되는데 소요되는 시간이 너무 길어질 수도 있습니다. 즉, 아카이브가 완료되지 않으면 로그 스위치에 의해 다음 리두로그 파일에 변경 데이터가 저장되지 못하고 일시적으로 대기해야 하는 문제가 발생하게 됩니다.
이런 경우를 최소화 하기 위해서는 리두로그 그룹 수를 적절히 늘려 주어야 합니다.
$HOME/ADMIN/BDUMP/alert_<SID>.ora 파일에 다음과 같은 메시지가 나타나면 LGWR 프로세스에 대기상태가 발생한 것 입니다.

"checkpoint not complete ; unable to allocate file"

아카이브 파일과 성능문제

메모리 영역인 리두로그 버퍼의 정보는 LGWR 프로세스에 의해 리두로그 파일에 저장됩니다. 하나의 리두로그 파일이 모두 쓰여지면 ARCH 프로세스에 의해 오프라인 또는 온라인 저장구조에 저장됩니다. 문제점은, ARCH 프로세스가 다른 저장구조에 저장하는데 소요되는 시간보다 LGWR 프로세스가 메모리 영역으로부터 리두로그 파일에 저장하는데 소요되는 시간이 훨씬 빠르다는 점입니다. 즉, 많은 변경 데이터가 보다 빠르게 리두로그 파일에 저장되려면 아카이브 작업이 빠르게 완료되어야 하는데, 시간이 많이 소요되게 되면 연속적인 쓰기 작업을 하지 못한다는 점입니다.

< 해결방법-1 >
log_archive_max_processes 파라메터의 값을 보다 높게 설정해 줍니다. ARCHIVE 모드로 데이터베이스 환경을 설정하게 되면 기본적으로 하나의 ARCH 프로세스가 활성화 됩니다. 동시에 많은 변경작업이 발생하는 경우 하나의 ARCH 프로세스로 아카이브를 하는 것 보다 여러 개의 ARCH 프로세스를 활성화하게 되면 보다 빠르게 아카이브 작업을 완료할 수 있습니다.

< 해결방법-2 >
아카이브 파일은 결국 리두로그 파일의 백업 정보이므로 리두로그 파일의 크기와 개수를 적절하게 조정하는 것이 아카이브 파일의 개수와 크기를 조절할 수 있는 방법입니다.

< 해결방법-3 >
아카이브 파일들이 저장되는 저장구조를 충분하게 확보하십시오. 아카이브 모드에서 파일들이 저장되는 저장구조에 여유공간이 없으면 아카이브 작업은 더 이상 진행되지 않습니다. 즉, LGWR 프로세스는 리두로그 영역의 데이터를 리두로그 파일에 저장하지 못하게 되고 궁극적으로 데이터베이스 전체적인 대기상태가 발생하게 됩니다.

체크포인트

오라클 데이터베이스의 백그라운드 프로세스 중 CKPT의 역할은 LGWR 프로세스가 리두로그 버퍼의 내용을 리두로그 파일에 저장하는 시점의 정보와 DBWR 프로세스가 데이터 버퍼 캐시영역의 내용을 데이터 파일에 저장하는 시점의 정보를 동기화 시켜주는 역할을 하게 됩니다.
하지만, 이러한 체크포인트가 너무 자주 실행되면 불필요한 읽기,쓰기 작업이 빈번하게 발생되기 때문에 데이터베이스의 전체적인 성능이 저하될 수 있습니다. 반대로, 자주 실행되면 인스턴스 문제로 인한 데이터베이스의 장애가 발생한 경우 빠르게 복구할 수 있는 장점을 가지고 있기도 합니다.

다음은 체크포인트가 발생하는 시점입니다.

- 로그 스위치가 발생할 때
- 데이터베이스가 정상적으로 종료될 때
- ALTER SYSTEM CHECKPOINT 명령어가 실행될 때
- ALTER SYSTEM SWITCH LOGFILE 명령어가 실행될 때
- 테이블스페이스가 ONLINE 상태에서 OFFLINE 상태로 변경될 때

다음은 체트포인트를 유발시키는 파라메터 들입니다.

1) LOG_CHECKPOINT_INTERVAL : 체크포인트가 발생하는 간격을 지정된 블록수로 결정합니다.리두로그 파일에 지정된 변경 데이터가 저장될 때 마다 체크포인트가 발생합니다.

2) LOG_CHECKPOINT_TIMEOUT : 체크포인트가 발생하는 간격을 지정된 시간으로 결정합니다. 이 시간이 지날 때 마다 LGWR과 DBWR 프로세스는 각 파일에 정보를 저장합니다.

3) FAST_START_IO_TARGET : 인스턴스를 복구할 때 복구해야 할 블록 수를 지정할 수 있습니다. 항상 지정된 크기의 정보가 메모리에 남게 되면 체크포인트가 발생합니다.

4) DB_BLOCK_DIRTY_TARGET : DBWR 프로세스는 지정된 블록수가 데이터 버퍼 캐시영역에서 확보되면 데이터 파일에 저장합니다.

5) FAST_START_MTTR_TARGET : 인스턴스를 복구할 때 복구시간을 지정할 수 있습니다. 지정된 복구 대상시간 만큼의 정보가 메모리에 남게 되면 체크포인트가 발생합니다.

결론적으로, 오라클 데이터베이스의 성능 튜닝을 할 때는 체크포인트가 자주 발생하지 않도록 다음과 같이 환경설정을 해야 합니다.

1) 체크포인트의 빈도를 줄인다는 말은 로그 스위치가 발생하는 빈도를 줄인다는 것과 같은 의미를 가지고 있으므로 리두로그 파일의 크기를 보다 크게 설정해야 합니다.

2) 빈번한 변경작업이 발생하는 데이터베이스 환경에서는 LOG_CHECKPOINT_INTERVAL 또는 LOG_CHECKPOINT_TIMEOUT과 같은 파라메터를 통해 체크포인트의 발생빈도를 줄일 수 있습니다.

저작자표시

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]3-3단계 Lock의 경합 (0)	2012.12.19
[오라클]3-2단계 Temp 세그먼트 (0)	2012.12.19
[오라클]3-1단계 Undo 세그먼트 (0)	2012.12.19
[오라클]2-3단계 아카이브 파일 (0)	2012.12.19
[오라클]2-1단계 데이터 파일 (0)	2012.12.19

Posted by redkite

, |

[오라클]3-3단계 Lock의 경합

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:13

락 (LOCK)

DML문을 실행하면 해당 트랜잭션에 의해 발생한 데이터가 다른 사용자에 의해 변경이 발생하지 않도록 LOCK(잠금현상)을 발생시킵니다. 그 이유는 진행중인 트랜잭션의 작업이 완료될 때 까지는 다른 사용자들로부터 보호되어야 하기 때문입니다. 이러한 현상은 COMMIT 또는 ROLLBACK문이 실행되면 자동적으로 해제가 됩니다.

다음 내용은 잠금현상의 주요 특징입니다 .

- 데이터의 검색이 일관되게 해 줍니다.

- DML문장이 실행될 때 ROLLBACK을 위해 오라클 서버가 변경 전 데이터와 변경 후 데이터를 모두 보관합니다.

- 테이블을 직접 변경하고 있는 세션에서 만 변경중인 데이터를 확인할 수 있으며 다른 세션에서는 변경 중인 데이터를 확인할 수 없습니다.

- 어떤 사용자가 변경 중인 행을 다른 사용자가 변경할 수 없습니다. 변경 중인 사용자에 의해 COMMIT 또는 ROLLBACK문이 실행된 후 변경할 수 있습니다.

LOCK의 종류

오라클 사에서 제공하는 락의 종류는 행 -레벨 락과 테이블-레벨 락, 두 가지 종류가 있으며 데이터베이스를 설치하면 기본적으로 행-레벨 락이 기본 모드입니다.

다음은 현재 사용하고 있는 데이터베이스가 어떤 락 -모드인지 확인하는 방법입니다.

SQL >	show parameter always_content
SQL >	show parameter row_locking
	NAME TYPE VALUE

row_locking string always ß 행-레벨 락 모드

ROW_LOCKING = INTENT ß 테이블-레벨 락 모드

1) ROW-LEVEL의 LOCK

행 -레벨 락이란 SELECT~FOR UPDATE, UPDATE, INSERT, DELETE문에 의해 해당 행에만 발생하는 락 종류입니다. 다음 예제를 따라 해 보십시오.

<세션1>

SQL >	SELECT * FROM emp WHERE empno = 7844;
SQL >	UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844;
SQL>

<세션2>

SQL >

UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844;

ß Waiting 발생 : 세션1에서 변경 중인 행이기 때문에 ROW-LEVEL LOCK 발생

<세션1>

SQL >

COMMIT;

<세션2>

SQL> ß 세션1에서 COMMIT이 실행되었기 때문에 LOCK 해제

또는

<세션1>

SQL >

SELECT * FROM emp WHERE empno = 7844 FOR UPDATE;

ß SELECT ~ FOR UPDATE 문장은 SELECT문이지만 LOCK이 발생합니다.

검색되는 ROW들은 앞으로 변경되어질 행으로 미리 LOCK을 설정합니다.

SQL >

<세션2>

SQL >

UPDATE emp SET sal = sal * 1.1 WHERE empno = 7844;

ß Waiting 발생 : 세션1에서 변경 중인 행이기 때문에 ROW-LEVEL LOCK 발생

<세션1>

SQL >

ROLLBACK;

<세션2>

SQL >

ß 세션1에서 COMMIT이 실행되었기 때문에 LOCK이 해제되고 문장이 실행됨

2) TABLE-LEVEL의 LOCK

변경중인 데이터가 특정 행들이 아닌 테이블 전체인 경우에는 테이블 -레벨 락이 발생합니다.

(DML문이 실행되었지만 WHERE 조건절이 없는 경우) 오라클 사에서 제공하는 테이블-레벨 락에는 3가지 종류가 있습니다.

- S LOCK : 테이블 전체 행에 설정되는 락 종류이며 마스터-디테일(Master-Detail) 관계 또는 부모-자식(Paraent-Child) 관계를 가진 2개의 테이블에서 부모 테이블이 변경될 때 자식 테이블에 발생하는 테이블-레벨 락 입니다. 또는 다음과 같은 명령어에 의해 수동으로 발생시킬 수 있습니다.

< 문법 > LOCK TABLE [테이블명] IN SHARE MODE;

- SRX LOCK : S 락과 같은 경우에 발생하며, 차이점은 부모 테이블이 생성될 때 자식 테이블의 FOREIGN-KEY 컬럼에 대한 부모 테이블의 컬럼이 ON DELETE CASCADE 절에 의해 생성되어 있고 FOREIGN-KEY 컬럼에 인덱스가 생성되어 있지 않은 경우입니다.( 다음 페이지에서 자세히 소개됩니다.)

다음과 같은 명령어에 의해 수동으로 발생시킬 수 있습니다 .

< 문법 > LOCK TABLE [테이블명] IN SHARE ROW EXCLUSIVE MODE;

- X LOCK : 가장 강력한 범위의 테이블-레벨 락 입니다. X 락이 발생하면 어떤 종류의 다른 락을 발생시킬 수 없습니다.

< 문법 > LOCK TABLE [테이블명] IN EXCLUSIVE MODE;

<세션1>

SQL >	DELETE emp; ß
SQL >

ß 전체 테이블을 삭제하기 때문에 TABLE-LEVEL LOCK이 발생

SQL >

<세션2>

SQL >

UPDATE emp SET sal = sal * 1.1 WHERE deptno = 20;

ß Waiting 발생 : 세션1에서 삭제 중인 행이기 때문에 TABLE-LEVEL LOCK 발생

<세션1>

SQL >

COMMIT;

<세션2>

0 행이 갱신되었습니다. ß 세션1에서 COMMIT이 실행되었기 때문에 EMP 테이블에는

ROW가 존재하지 않으므로 에러가 발생합니다.

또는

SQL >	LOCK TABLE emp IN EXCLUSIVE MODE; ß TABLE-LEVEL LOCK을 유발시키는 명령어
SQL >	UPDATE emp SET sal = sal * 1.1 WHERE empno = 7954;
SQL >	ß 하나의 행 만 변경하지만 TABLE-LEVEL LOCK 발생

<세션2>

SQL >

UPDATE emp SET sal = sal * 1.1 WHERE empno = 7954;

ß Waiting 발생 : 세션1에서 변경 중인 행이기 때문에 TABLE-LEVEL LOCK 발생

<세션1>

SQL >

COMMIT;

<세션2>

SQL >

ß 세션1에서 COMMIT이 실행되었기 때문에 LOCK 해제

S 락과 SRX 락의 튜닝

위 그림을 보면 , 마스터-디테일(Master-Detail) 관계 또는 부모-자식(Paraent-Child) 관계를 가진 2개의 테이블이 있습니다. 부서정보 테이블에 부서번호 컬럼은 식별키(Primary Key)이고 사원정보 테이블에 부서번호 컬럼은 부서정보 테이블의 부서번호 컬럼을 참조하는

외부키 (Foreign-Key) 입니다.

SQL >	CREATE TABLE DEPT (DEPTNO NUMBER(2) PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) );
SQL >	CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2) REFERENCES DEPT (DEPTNO) );

이와 같은 , 부모-자식 관계를 가진 2개의 테이블에서 부모 테이블의 컬럼이 변경될 때 자식 테이블에 테이블-레벨 락이 발생합니다.

SQL >	DELETE FROM dept WHERE deptno = 10;
SQL >

테이블 -레벨의 락이 발생하는 이유는 2개의 테이블에 저장되어 있는 공통 컬럼은 서로 참조하는 관계를 가지고 있기 때문에 부모 테이블에 저장되어 있지 않은 컬럼 값이 자식 테이블에 존재할 수 없기 때문입니다. 그래서, 부모 테이블에 대해 변경이 발생하면 자식 테이블 전체도 S 테이블 락을 발생하는 것 입니다.

문제점은 자식 테이블의 경우 , 실제적인 DML문이 아닌 부모 테이블로 인해 테이블-레벨 락이 발생했기 때문에 락이 해제될 때 까지 무작정 기다려야 한다는 점입니다.

자식 테이블에 대해 더 이상의 DML문을 수행할 수 없는 문제가 발생하게 됩니다.

이런 문제를 해결하기 위한 유일한 방법은 자식 테이블의 외부키 (Foreign-Key) 컬럼에 인덱스를 생성하는 방법입니다.

SQL >

CREATE INDEX I_emp_deptno ON dept(deptno);

외부키 컬럼에 인덱스를 생성하면 S 락 또는 SRX 락이 발생할 때 자식 테이블에 대해 테이블-레벨 락을 발생시키지 않고 인덱스에 락을 발생시키기 때문에 자식 테이블에 대해 계속적인 DML 작업을 수행할 수 있게 됩니다.

즉 , 이런 문제로 인한 대기상태를 피할 수 있으므로 성능이 개선될 수 있습니다.

락 모니터링

데이터베이스를 사용하다 보면 개발자에 의해 실행되는 수 많은 SQL문들이 반복적으로 락을

설정하고 또 해제하게 됩니다 . 많은 사용자들이 동시에 테이블을 입력, 수정, 삭제, 조회하게 되면 서로 걸고 걸리는 락 관계가 성립되게 됩니다.

그렇다면 , 개발자가 어떤 테이블을 조작하려는데 누군가가 락을 발생시킨 상태라면 더 이상작업을 진행시키지 못하게 되고, 락을 발생시킨 사용자가 COMMIT 또는 ROLLBACK 문을 수행할 때 까지 무작정 대기해야 하는 문제가 발생하게 됩니다.

이런 경우 , 락을 유발시킨 사용자가 누구인지 알 수 있다면 락 해제를 요청할 수 있을 것이고 또한, 어떤 사용자가 어떤 SQL문을 언제 실행하여 어떤 테이블, 어떤 행에 어떤 종류의 락을 유발하고 있는지도 알 수 있다면 보다 효과적으로 락을 관리할 수 있을 것입니다.

위 그림을 보면 , 세션번호가 10번인 사용자가 EMP 테이블의 2번째 행을 UPDATE문으로 변경하고 있습니다. 아직 COMMIT문을 실행하지 않은 상태에서 세션번호가 20번인 사용자가 같은 EMP 테이블의 2번째 행을 UPDATE 하려고 합니다. 이런 경우가 발생하면 2번째 사용자의 UPDATE문은 실행되지 못하고 대기상태에 빠지게 됩니다. 첫 번째 사용자가 트랜잭션을 빠르게 종료 시킨다면 문제가 없겠지만 그렇지 못한 경우에는 무한정 대기상태에 빠지게 됩니다.

이런 경우 , 오라클사에서 제공하는 UTLLOCKT.SQL 스크립트를 실행하면 어떤 세션에서, 어떤 종류의 락을, 어떤 문장으로 실행했고 그 문장으로 인해 대기상태에 빠진 세션은 어떤 정보을 가지고 있는지를 상세히 제공해 줍니다.

SQL >	CONNECT system/manager
SQL >	@$HOME/rdbms/admin/utllockt.sql USERNAME SID OBJECT SQL

SCOTT 10 EMP update emp set sal = sal * 1.1
where empno = 7934
SCOTT 20 EMP update emp set sal = sal * 1.1
where empno = 7934

ß 분석결과를 보면, 10번 세션번호를 가진 사용자에 의해 UPDATE문이 실행되었고 행-레벨의 락으로 인해 20번 세션번호를 가진 사용자의 세션이 대기상태에 있는 것을 확인할 수 있습니다.

락의 원인이 분석되었으면 신속히 락을 해제해야 하는데 락을 해제하는 방법은 2가지가 있습니다. 첫 번째 방법은 락을 유발한 세션에서 COMMIT 또는 ROLLBACK문을 실행하는 방법이고 두 번째 방법은 ALTER SYSTEM KILL SESSION 명령어로 강제로 세션을 종료시키는 방법입니다. 대부분의 경우는 COMMIT, ROLLBACK 문장에 의해 자동적으로 락을 해제하게 되고 데드-락(Dead-Lock)과 같이 더 이상 락을 해제할 수 없는 상황에는 ALTER SYSTEM KILL SESSION 명령어를 사용하게 됩니다.

< 문법 > SELECT sid, serial# FROM v$session;

ALTER SYSTEM KILL SESSION ‘[sid], [serial#]';

자 ~ 다음 예제를 따라 해 보십시오.

실습을 위하여 3개의 세션(session)을 Open하십시오. 2개 세션은 SCOTT 사용자로 나머지 하나는 “/as sysdba”로 접속합니다.

1) 첫 번째 SCOTT Session에서 한 사원의 월급을 변경(Update) 합니다.

먼저 , DEPT, EMP 테이블을 새롭게 생성하고 다음과 같이 제약조건을 설정하십시오

$ sqlplus scott/tiger
SCOTT(1).SQL> @$ORACLE_HOME/sqlplus/demo/demobld
$ sqlplus scott/tiger
SCOTT(1).SQL> alter table dept
add constraint dept_deptno_pk primary key(deptno) ;
SCOTT(1).SQL> alter table emp
add constraint emp_deptno_fk foreign key(deptno)
references dept(deptno);
SCOTT(1).SQL> update emp
set sal = sal * 1.1
where empno = 7934 ; ß

UPDATE문에 의해 LOCK이 발생합니다

2) SYS Sesseion에서 데이터베이스의 Lock 상태를 확인해 봅니다.

	$sqlplus “/as sysdba”
SYS.SQL>	select username, sid from v$session where username = ‘SCOTT';

ß 현재 Lock을 유발하고 있는 사용자명과 Session-ID를 분석하십시오

<질문> 사용자명은 ?

세션 ID 는 ?

SYS.SQL>

SYS.SQL> select sid, type, id1, lmode, request
from v$lock
where sid = <Session ID>;

ß 조금 전 분석된 Session-ID

이번에는 Lock이 발생한 Object가 어떤 것 인지 알아 봅시다.

SYS.SQL>

select object_name
from dba_objects
where object_id in ( ID1 컬럼의 값 );

ß 위 문장 V$LOCK을 실행했을 때 해당 SID의 TYPE 컬럼이 ‘TM' lock이 걸린 행의

ID1 컬럼의 값을 괄호 안에 지정하십시오.

OBJECT_NAME

EMP ß EMP 테이블이 DML문에 의해 Lock 발생하고 있습니다.

다음과 같이 V$LOCKED_OBJECT 자료사전을 통해서도 알 수 있습니다.

SYS.SQL> col ORACLE_USERNAME format a10

SYS.SQL> col OS_USER_NAME format a12

SYS.SQL> select OBJECT_ID, SESSION_ID,

ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE

from v$locked_object

OBJECT_ID SESSION_ID ORACLE_USE OS_USER_NAME LOCKED_MODE

5285 7 SCOTT dba9i101 3

OBJECT_ID : Lock이 유발된 객체번호

SESSION_ID : Lock을 유발한 사용자의 세션번호

ORACLE_USER : Lock을 유발한 사용자명

OS_USER_NAME : Lock을 유발한 사용자의 O/S 계정

LOCKED_MODE : 1 SELECT ~ FOR UPDATE에 의한 Lock

2 INSERT에 의한 Lock

3 UPDATE에 의한 Lock

4 DELETE에 의한 Lock

3) 두번째 SCOTT Session 에서 EMP 테이블을 삭제합니다… 삭제가 될까요 ?

	$ sqlplus scott/tiger
SCOTT(2).SQL>	drop table emp; ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified

ß 이 문장이 실행되기 위해서는 Exclusive Table Lock이 필요한데, 첫번째 SCOTT Session이 EMP 테이블에 대해 Row Exclusive Table Lock 잡고 있기 때문에 DROP 문장은 실행될 수 없습니다.

두번째 SCOTT Session 에서 EMP 테이블의 다른 행을 변경해 봅시다.

SCOTT(2).SQL>

update emp
set sal = sal * 1.1
where empno = 7902 ;

ß SYS Session애서 현재 데이터베이스에 어떤 Lock이 발생했는지 알아봅시다.

SCOTT(2).SQL>

select username, sid
from v$session
where username = ‘SCOTT' ;
USERNAME SID

SCOTT 7
SCOTT 9 새롭게 Lock이 발생한 세션

<질문> 사용자명은 ? 세션 ID 는 ?

SYS.SQL>

select sid, type, id1, lmode, request
from v$lock
where sid = <Session ID>; ß 조금 전 분석된 Session-ID

SYS.SQL>

select OBJECT_ID, SESSION_ID,

ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE

from v$locked_object;

OBJECT_ID SESSION_ID ORACLE_USE OS_USER_NAME LOCKED_MODE

5285 7 SCOTT dba9i101 3
5285 9 SCOTT dba9i101 3 ß 새로운 Lock 정보
두번째 SCOTT session 에서 다음과 같이 UPDATE문을 실행해 보십시오.
SCOTT(2).SQL> rollback;
SCOTT(2).SQL> update emp set sal = sal * 1.1 where empno = 7934 ;
이미 SCOTT(1) Session에서 UPDATE문을 진행하고 있기 때문에 Lock이 발생되어 있기 때문에 변경할 수 없습니다.( Waiting 상태 )

SYS Session 에서 어떤 lock이 발생했는지 알아봅시다.

SYS.SQL>

select sid, type, id1, lmode, request
from v$lock
where request <> 0; ß request <> 0 이면 waiting 중인 Lock정보를 검색합니다.
SID TY ID1 LMODE REQUEST

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

9 TX 131077 0 6

ß 세션ID 9번이 현재 Waiting 상태임을 알 수 있습니다.

또는 , 다음과 같은 방법으로 Waiting 중인 세션을 분석할 수 있습니다.

SYS.SQL> SQL> select XIDUSN, OBJECT_ID, SESSION_ID,

ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE

from v$locked_object

where XIDUSN = 0 ; ß XIDUSN = 0 이면 waiting 중 세션

XIDUSN OBJECT_ID SESSION_ID ORACLE_USE OS_USER_NAME LOCKED_MODE

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

0 5285 9 SCOTT dba9i101 3

이번에는 어떤 사용자의 어떤 세션에서 어떤 Object에 대해 어떤 SQL문을 실행하여 Lock이 발생하였는지를 분석하는 방법입니다.

SYS.SQL> @$ORACLE_HOME/rdbms/admin/utllockt.sql

SYS.SQL> col username format a8

SYS.SQL> col sid format 999

SYS.SQL> col object format a12

SYS.SQL> col sql format a50

SYS.SQL> SELECT b.username username, c.sid sid,

c.object object, a.sql_text SQL

FROM v$sqltext a,v$session b,v$access c

WHERE a.address = b.sql_address and

a.hash_value = b.sql_hash_value and

b.sid = c.sid and c.owner != 'SYS';

USERNAME SID OBJECT SQL

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

SCOTT 9 EMP update emp set sal = sal * 1.1

where empno = 7934

SCOTT 9 EMP update emp set sal = sal * 1.1

where empno = 7934

결과를 보고 lock을 잡고 있는 Session을 강제로 Kill하면 Lock이 풀리게 됨으로 다음 트랜잭션은 실행됩니다.

SQL> select sid, serial#, username from v$session where sid = ???;

SID SERIAL# USERNAME

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

9 81 SCOTT

SQL> alter system kill session ‘9, 81' ;

ß 위 문장에서 분석된 Session-ID와 Seria-No로 Kill하십시오.

SCOTT(2) Session으로 이동해서 Enter-Key를 눌러 보십시오.

이 세션은 강제로 Kill 되었습니다..

SCOTT(2).SQL> update emp

set sal = sal * 1.1

where empno = 7934 ; ß 새로운 SQL문 작성 후 Enter

ERROR at line 1:

ORA-00028: your session has been killed

때로는 , 특정 사용자가 실행한 트랜잭션이 계속해서 Lock을 유발하고 있을 때 DBA는 해당 작업을 분석하여 강제로 세션을 종료시켜야 합니다.

< 잠깐 > 기타 관련사전

1) DBA_WAITERS

이 자료사전은 다른 사용자의 DML문이 실행된 후 락이 발생한 행에 대해 또 다른 사용자가 DML문을 실행하게 되면 대기상태에 빠지게 되는데, 이러한 경우, 대기하는 사용자의 세션 정보를 제공합니다. 이 자료사전을 참조하기 전에 반드시 CATBLOCK.SQL 스크립트를 실행해야 합니다.

< 세션-1 >

SQL> @$HOME/rdbms/admin/catblock.sql

SQL> connect scott/tiger

SQL> update dept set loc = 'aa';

<세션-2>

$ sqlplus scott/tiger

SQL> update dept set loc = 'aa';

< 세션-1 >

SQL> SELECT WAITING_SESSION HOLDING_SESSION LOCK_TYPE, LOCK_ID1, LOCK_ID2

FROM DBA_WAITERS;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE LOCK_ID1 LOCK_ID2

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

10 9 Transaction 131076 229

2) DBA_BLOCKERS : 현재 락을 유발하고 있는 모든 세션정보를 알 수 있습니다.

SQL >

SELECT * FROM DBA_BLOCKERS;
HOLDING_SESSION

저작자표시

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-2단계 Redo 로그 파일 (0)	2012.12.19
[오라클]3-2단계 Temp 세그먼트 (0)	2012.12.19
[오라클]3-1단계 Undo 세그먼트 (0)	2012.12.19
[오라클]2-3단계 아카이브 파일 (0)	2012.12.19
[오라클]2-1단계 데이터 파일 (0)	2012.12.19

Posted by redkite

, |

[오라클]3-2단계 Temp 세그먼트

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:11

소트 영역의 튜닝 분석

대부분의 사용자들이 작성하는 SQL문은 실행 시 분류작업(SORTING)이 발생합니다. 분류작업이 발생하면 별도의 시간과 공간이 추가로 필요하기 때문에 분류작업이 발생하지 않는 경우보다 성능이 저하될 수밖에 없습니다. 또한, 별도의 공간이 분류작업을 하기에 충분하지 못하다면 성능은 더 저하될 수밖에 없는 것입니다. 사용자의 SQL문에서 분류작업이 발생하는 경우는 다음과 같습니다.

- 인덱스를 생성하는 문법을 사용하는 경우(CREATE INDEX ~문)
- 인덱스가 있는 테이블에 병렬로 데이터를 입력하는 경우(INSERT INTO~PARALLEL(DEGREE n)
- ORDER BY, GROUP BY을 사용하는 경우(SELECT ~ ORDER BY ~ GROUP BY ~)
- DISTINCT 키워드를 사용하는 경우(SELECT DISTINCT ~ )
- UNION, INTERSECT, MINUS 연결 연산자를 사용하는 경우(SELECT~ UNION SELECT ~)
- 인덱스가 없는 2개의 테이블을 조인하는 경우(SELECT ~ FROM a, b WHERE ~)
- ANALYZE 명령문을 사용하는 경우(ANALYZE TABLE ~)

이와 같은 SQL문이 실행된다면 내부적으로 분류작업을 통해 데이터를 리턴해 줍니다.

소트 메모리 영역(Sort Memory Area)

자~ 그럼 생각해 보세요. 이러한 분류작업은 어디서 실행될까요 ? "SQL문을 처리하는 과정"과 "DML문을 처리하는 과정"을 통해 데이터베이스의 구조를 알아보았는데 이러한 분류작업은 어떤 구조에서 처리되는 걸까요?

두 가지 경우에 대해서 알아보아야 할 것입니다. 첫 번째, 전용서버 환경에서는 서버 프로세스에 있는 PGA(PROGRAM GLOBAL AREA) 영역의 SORT AREA 영역에서 분류작업이 실행됩니다.
두 번째, 공유서버 환경에서는 SORT AREA 영역이 공유 풀 영역에 존재하기 때문에 모든 분류작업이 SGA 영역에서 실행됩니다.

그럼, 이 영역의 크기는 어떤 값에 의해 결정될까요 ? INIT<DB명>.ORA 파일에 있는 다음 파라메터 값에 의해 결정됩니다. 만약, 대용량 데이터에 대한 분류작업이 발생하는 경우에는 이 파라메터 값을 높게 설정하여 메모리 영역에서 보다 원활한 분류작업이 발생하도록 해야만 좋은 성능을 기대할 수 있습니다.

SORT_AREA_SIZE = [크기]

또한, 사용자의 SQL문에 의해 사용된 SORT_AREA_SIZE 영역이 분류작업 후 PGA 영역에 계속해서 할당되어 있으면 메모리의 낭비가 발생할 수 있기 때문에 다음과 같은 파라메터에 의해 SORT_AREA_SIZE 영역을 축소(SHRINK) 시킬 수 있습니다.

이 파라메터의 값은 SORT_AREA_SIZE 파라메터 값을 초과하여 정의할 수는 없습니다.

SORT_AREA_RETAINED_SIZE = [크기]

소트 디스크 영역(Sort Disk Area)

자~ 한가지 문제에 대해서 더 알아보겠습니다. 시스템 또는 데이터베이스를 위한 메모리 영역은 항상 크기가 제한되어 있습니다. 만약, 분류작업을 해야할 테이블이 100만 건의 행을 가지고 있다면 모든 행을 메모리 영역에 저장한 후 분류작업을 할 수는 없겠죠 ? 그래서, 필요한 영역이 TEMPORARY 테이블스페이스입니다. 메모리 영역인 SORT_AREA_SIZE에서 1차적인 분류작업이 발생하고 작업이 완료되지 못하면 디스크 영역에 생성되어 있는 TEMPORARY 테이블스페이스에 분류된 내용의 일부를 잠시 저장하게 됩니다. 이러한 연속적인 작업을 통해 대용량 데이터에 대한 분류작업을 실행하게 됩니다. 참~~ TEMPORARY 테이블스페이스는 오라클 유니버설 인스톨러에 의해 오라클 데이터베이스를 설치하면 기본적으로 생성되는 논리적 구조입니다.(보다 자세한 내용은 "오라클 9i의 생김새 알아보기"를 참조하십시오.)

예를 들어, 윈도우 시스템에서 [설정] --> [제어판] --> [시스템] --> [고급] --> [성능옵션]에 보면 가상 메모리를 설정하는 기능이 있습니다. 시스템의 메모리 영역에서 완료되지 못한 사용자 작업의 일부를 잠시 저장해 두는 디스크 상의 공간을 의미합니다. 이와 유사한 개념을 가진 데이터베이스의 논리적 구조가 TEMPORARY 테이블스페이스입니다.

TEMPORARY 테이블스페이스는 다음과 같은 문법으로 생성됩니다.

CREATE DATABASE ora90
LOGFILE GROUP 1 ('c:\oracle\oradata\ora90\redo01.log') size 10m,
GROUP 2 ('c:\oracle\oradata\ora90\redo02.log') size 10m,
GROUP 3 ('c:\oracle\oradata\ora90\redo03.log') size 10m
DATAFILE 'c:\oracle\oradata\ora90\system01.dbf' size 100m
UNDO TABLESPACE undo
DATAFILE 'c:\oracle\oradata\ora90\undo01.dbf' size 50m
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE 'c:\oracle\oradata\ora90\temp01.dbf' size 30m
EXTENT MANAGEMENT LOCAL UNIFORM size 1m
CHARACTER SET ko16ksc5601
NATIONAL CHARACTER SET ko16ksc5601
SET TIME_ZONE = 'Korea/Seoul';

이 문법은 오라클 유니버설 인스톨러에 의해 최초 만들어지는 TEMPORARY 테이블스페이스에 관련된 문법입니다.

데이터베이스 설치 후 모든 사용자들은 TEMP 테이블스페이스에서 모든 분류작업을 하게 됩니다. 또한, 이 테이블스페이스는 기본적으로 로컬리매니저 테이블스페이스 형태로 생성됩니다.

CREATE TEMPORARY TABLESPACE [테이블스페이스명]
TEMPFILE '[경로와 파일명] SIZE [크기]
EXTENT MANAGEMENT LOCAL UNIFORM SIZE [크기];

이 문법은 데이터베이스 생성 후 추가적으로 TEMPORARY 테이블스페이스를 생성하는 방법입니다. "사용자 관리" 및 "테이블스페이스 생성 시 주의사항"에서 언급했던 대로 좋은 성능을 위해서는 사용자별로 TEMPORARY 테이블스페이스를 생성하여 할당해야 합니다.

충분한 SORT 공간을 할당하라.

그럼, 분류영역에 대한 튜닝 분석방법을 알아보겠습니다. 데이터베이스가 생성되면 기본적으로 TEMPORARY 테이블스페이스가 생성되며 또한, 사용자는 추가적으로 TEMPORARY 테이블스페이스 생성하게 됩니다. 사용자가 분류하려는 테이블의 데이터가 너무 커서 현재 할당되어 있는 TEMPORARY 테이블스페이스 공간으로는 모든 분류작업을 할 수 없을 때 SQL문의 성능은 저하됩니다. 다음은 자료사전을 통해 분류영역을 분석하는 방법입니다.

SQL >

select disk.value "Disk",
mem.value "Mem",
(disk.value / mem.value) * 100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';

V$SYSSTAT 자료사전을 참조하면 이 영역에 대한 튜닝 여부를 확인할 수 있습니다.
"sorts (memory)"는 서버 프로세스가 PGA의 SORT AREA 영역에서 작업한 블록 수이며 "sorts (disk)"는 TEMPOR ARY 테이블스페이스의 디스크 공간에서 작업한 블록 수를 의미합니다. "sorts (disk)" 값이 "sorts (memory)" 값의 5% 미만일 때 분류작업 시 좋은 성능을 기대할 수 있습니다. 만약, 기준치에 적합하지 않다면 SORT_AREA_SIZE 파라메터 값이 너무 작아 성능이 저하되고 있으므로 파라메터의 값을 높게 설정해 주어야 합니다. 또한, V$SORT_USAGE 자료사전을 조회하여 세션별로 TEMPORARY 테이블스페이스를 얼마나 사용하고 있는지를 분석할 수도 있습니다.

다음 예제를 따라 해 보십시오.

SYSTEM 사용자로 접속하여 현재 시점의 SORTING 영역의 튜닝상태를 분석해 봅시다.

	$ sqlplus scott/tiger
SQL >	select disk.value "Disk", mem.value "Mem", (disk.value / mem.value) * 100 "Ratio" from v$sysstat mem, v$sysstat disk where mem.name = 'sorts (memory)' and disk.name = 'sorts (disk)';

Disk	Mem	Ratio
0	628	0

▲ 대부분의 분류작업은 PGA 영역에서 작업되고 있습니다

	$ sqlplus scott/tiger
SQL >	alter session set sort_area_size=10000000;

▲ PGA 영역을 보다 크게 설정하는 방법입니다.

SQL >

SELECT * FROM big_emp
ORDER BY ename

▲ SORTING이 발생하는 SQL문을 실행하면 SORTING
정보를 메모리와 디스크 공간에 저장하게 될 것입니다.

SQL >

select disk.value "Disk",
mem.value "Mem",
(disk.value / mem.value) * 100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';

Disk	Mem	Ratio
1	660	.151515152

▲ 대부분의 분류작업은 PGA 영역에서 작업되고 있습니다.
디스크 공간에 SORTING 정보가 일부 저장되었군요.

SQL >

EXIT

이번에는, SORT_AREA_SIZE 파라메터를 낮게 설정하고 다시 분류영역의 상태를 분석해 봅시다.

	$ sqlplus scott/tiger
SQL >	alter session set sort_area_size=100;

▲ 이전 상태보다 PGA 영역을 낮게 활성화 합니다.

SQL >

SELECT * FROM account ß SORTING
ORDER BY customer;

▲ SORTING이 발생하는 SQL문을 실행하면 SORTING
정보를 메모리와 디스크 공간에 저장하게 될 것 입니다.

SQL >

select disk.value "Disk",
mem.value "Mem",
(disk.value / mem.value) * 100 "Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';

Disk	Mem	Ratio
3	674	.445103858

▲ 5% 미만이면 성능에 큰 영향을 미치지 않습니다.
하지만 , SORT_AREA_SIZE를 높게 설정하면 보다
좋은 성능을 기대할 수 있습니다.

다음은 디스크 영역에 존재하는 TEMPORARY 테이블스페이스의 현재 사용 현황을 분석하는 방법입니다. 또 다른 윈도우-창에서 데이터베이스에 접속한 다음 사용 중인 TEMPORARY 테이블스페이스의 튜닝상태를 분석하십시오.

SQL >	col TABLESPACE_NAME format a10
SQL >	select tablespace_name, current_users, total_extents, used_extents,extent_hits, max_used_blocks, max_sort_blocks from v$sort_segment;
SQL >	select tablespace_name, current_users, extent_hits, max_used_blocks, max_sort_blocks from v$sort_segment;

TABLESPACE	CURRENT_USERS	MAX_SORT_BLOCKS
TEMP	0	1024

TABLESPACE : TEMPORARY 테이블스페이스 이름
CURRENT_USERS : 분류작업을 실행하고 있는 사용자 수
MAX_SORT_BLOCKS : 분류작업에 의해 사용된 블록 수

다음은 자료사전은 어떤 사용자에 의해 어떤 TEMPORARY 테이블스페이스가 얼마나 사용되고 있는지를 분석하는 방법입니다.

먼저, 새로운 세션을 하나 더 생성하고 기존에 접속된 세션은 그대로 유지하십시오.

	$ sqlplus scott/tiger
SQL >	SELECT * FROM big_emp ORDER BY ename;

▲ 결과가 화면에 계속 출력 됩니다.

또 다른 윈도우-창에서 데이터베이스에 접속한 다음 사용 중인 TEMPORARY 테이블스페이스의 튜닝상태를 분석하십시오.

이 분석결과는 현재 분류작업이 진행 중인 정보 만 나타납니다. 즉, 방금 실행했던 SQL문이 완료되기 전에 자료사전을 참조하십시오.

SQL >	col USERNAME format a10
SQL >	col user format a10
SQL >	select USERNAME, USER, CONTENTS, SEGTYPE, EXTENTS, BLOCKS from v$sort_usage;

USERNAME	USER	CONTENTS	SEGTYPE	EXTENTS	BLOCKS
SCOTT	SCOTT	TEMPORARY	SORT	4	1024

▲ SCOTT 사용자의 분류작업에 의해 4개의 익스텐트를 사용하고 있습니다.

이번에는 TEMPORARY 테이블스페이스를 여러 개 만들어 사용자별로 할당해 줄 수 있도록 재구성해 보겠습니다. 데이터베이스가 설치될 때 생성된 TEMPORARY 테이블스페이스는 데이터베이스를 사용하는 모든 사용자들이 공유하는 공간이므로 경합 현상이 발생하면 WAIT 현상이 발생하게 됩니다. 사용자마다 별도로 TEMPORARY 테이블스페이스를 생성하여 할당한다면 경합현상을 분산시킬 수 있습니다.

SQL >	CONNECT system/manager
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;

SQL >	connect scott/tiger
SQL >	SELECT USERNAME, TEMPORARY_TABLESPACE 2 FROM DBA_USERS 3 WHERE USERNAME = 'SCOTT' OR USERNAME = 'HR';

USERNAME	TEMPORARY_TABLESPACE
HR	TEMP11
SCOTT	TEMP10

효과적인 SORT 영역의 활용

인덱스 생성시 NOSORT 옵션을 사용하라.

인덱스를 생성하면 기본적으로 분류작업이 발생합니다. 즉, 인덱스는 분류작업을 실행한 후 컬럼 값을 기준으로 인덱싱을 하기 때문입니다. 만약, 테이블의 해당 컬럼이 이미 분류가 되어 있다면 인덱스 생성시 분류작업을 할 필요가 없을 것 입니다. 이언 경우에는 다음과 같이 NOSORT 옵션을 사용하면 분류작업을 하지 않습니다. 하지만, 반드시 해당 컬럼이 분류되어 있어야 합니다.

SQL >

CREATE INDEX I_emp_empno ON emp (empno) NOSORT;

UNION 연산자보다 UNION ALL 연산자을 사용하라.

UNION 연산자는 여러 개의 연결된 SQL문에서 중복된 행들은 하나 만 출력되는 집합 연산자입니다. 실행될 때 내부적으로 분류작업이 발생합니다. 만약, 해당 컬럼에 중복된 행이 없거나 또는 중복된 행을 참조해도 무방한 경우에는 되도록 UNION ALL 연산자를 사용하는 것이 불필요한 TEMPORARY 테이블스페이스 공간을 사용하지 않는 방법입니다.

DISTINCT 키워드의 사용을 자제하라.

DISTINCT 키워드는 해당 컬럼을 참조할 때 중복된 값들은 하나 만 출력되는 키워드 입니다. 실행될 떄 내부적으로 분류작업이 발생합니다. 반드시, 사용해야 하는 경우이외에 사용을 자제하는 것이 불필요한 TEMPORARY 테이블스페이스 공간을 사용하지 않는 방법입니다.

ORDER BY절의 사용을 자제하라.

개발자들이 실행하는 대부분의 SQL문에는 ORDER BY 절이 항상 사용된다고 해도 무방할 정도로 자주 사용되는 문법절 입니다. 하지만, 빈번한 분류작업은 불필요한 분류공간을 사용하게 되기 떄문에 성능에 도움이 되지 않습니다. 되도록 적절한 인덱스를 사용한다면 원하는 형태의 결과를 참조할 수 있으므로 인덱스를 사용하십시오.

분류작업을 할 때 모든 컬럼의 사용을 자제하라.

분류작업을 할 때 SELECT절에 불필요한 컬럼들을 정의하면 분류공간이 낭비되므로 참조해야 만 하는 컬럼들 만 정의하십시오.(다음 페이지에서 자세히 소개됩니다.)

SQL >

SELECT ename FROM big_emp ORDER BY ename;

▲ ENAME 컬럼 만 분류작업 합니다.

SQL >

SELECT * FROM big_emp ORDER BY ename;

▲ 전체 컬럼을 분류작업 합니다.

분류영역의 크기 계산

개발자들이 실행하는 SQL문에서 SORTING이 발생하면 SORTING 작업을 하기 위해 임시공간이 필요한데 이 공간을 TEMPORARY 테이블스페이스라고 합니다. 오라클 데이터베이스를 설치하면 최초 하나의 임시공간이 생성되는데, 이 공간에 대한 경합현상을 피하기 위해서는 사용자별로 임시공간을 할당해 주는 것이 가장 좋습니다. 그렇다면, 추가적으로 TEMPORARY 테이블스페이스를 생성할 때 그 크기는 어느 정도가 되어야 할까요 ?
다르게 해석해 보면, 개발자들이 실행하는 SQL문에서 SORTING이 발생할 때 얼마나 많은 SORTING 정보가 생성되는지를 알 수 있다면 그 크기를 쉽게 산정 해 볼 수 있을 것 입니다.

위의 왼쪽그림은 SELECT문에 의해 발생되는 SORTING 정보의 크기를 산정하는 방법입니다.
먼저, 왼쪽그림은 SELECT절에 '*'를 정의한 후 ORDER BY절에 의해 SORTING하는 경우입니다. 만약, 테이블 하나의 컬럼 수가 10개이고, 행 길이는 100 BYTE 이며 3000 행이 저장되어 있다면 이 문장이 실행되면서 필요로 하는 SORTING 영역의 크기는 다음과 같습니다.

SORTING 영역의 크기

= 전체 행수 X (행의 길이 + (2 X 컬럼 수))
= 3000 X (100 + 2 X 10) = 420,000 BYTE

컬럼 수에 2를 곱하는 이유는 하나의 컬럼 당 2 BYTE의 오버헤드가 필요하기 때문 입니다.
계산결과 이 SELECT문은 실행 시 42,000 BYTE의 SORTING 공간을 사용하게 될 것입니다.

이번에는 오른쪽 그림의 경우입니다. 왼쪽그림과 같은 원리, 같은 공식에 의해 SORTING 영역을 계산해 보면 다음과 같습니다.

SORTING 영역의 크기

= 전체 행수 X (행의 길이 + (2 X 컬럼 수))
= 3000 X (8 + 2 X 2) = 36,000 BYTE

이번 경우에는 ENAME 컬럼 만을 참조하며, 36,000 BYTE의 SORTING 공간을 사용하게 될 것입니다.

두 개의 SQL문에서 계산된 SORTING 영역의 크기를 산정해 보면 개발자가 실행하는 SQL문이 어떻게 작성되었느냐에 따라 임시공간의 사용범위가 달라진다는 것을 확인해 보았습니다.
되도록 최적의 SQL문을 작성하는 것이 임시공간을 효과적으로 사용할 수 있을 뿐 아니라 SQL문의 성능을 향상시키는 방법이기도 합니다.

다음 예제를 따라 해 보십시오. (데이터베이스를 재 시작한 다음 실습을 하십시오.)

SQL >

SELECT ename FROM big_emp ORDER BY ename;

TABLESPACE _NAME	CURRENT _USERS	EXTENT _HITS	MAX_USED _BLOCKS	MAX_SORT _BLOCKS
TEMP	1	1	256	256

▲ 특정 컬럼 만 참조하는 경우 분류작업을 위해 최대 256 블록공간이 사용되고 있습니다.

SQL >

SELECT * FROM big_emp ORDER BY ename;

TABLESPACE _NAME	CURRENT _USERS	EXTENT _HITS	MAX_USED _BLOCKS	MAX_SORT _BLOCKS
TEMP	1	1	512	512

▲ 모든 컬럼을 참조하는 경우 분류작업을 위해 최대 512 블록공간이 사용되고 있습니다.

두 개의 SQL문은 거의 같은 결과가 리턴되지만 두 번째 문장은 분류영역을 최소한도로 사용하기 떄문에 더 빠른 성능을 보여줄 것입니다.

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-2단계 Redo 로그 파일 (0)	2012.12.19
[오라클]3-3단계 Lock의 경합 (0)	2012.12.19
[오라클]3-1단계 Undo 세그먼트 (0)	2012.12.19
[오라클]2-3단계 아카이브 파일 (0)	2012.12.19
[오라클]2-1단계 데이터 파일 (0)	2012.12.19

Posted by redkite

, |

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

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:10

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';

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]3-3단계 Lock의 경합 (0)	2012.12.19
[오라클]3-2단계 Temp 세그먼트 (0)	2012.12.19
[오라클]2-3단계 아카이브 파일 (0)	2012.12.19
[오라클]2-1단계 데이터 파일 (0)	2012.12.19
[오라클]1-5단계 래치와 경합 (0)	2012.12.19

Posted by redkite

, |

[오라클]2-3단계 아카이브 파일

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:10

리두로그 파일과 아카이브 파일의 I/O 튜닝

자, 그럼 보다 자세히 성능문제를 알아 봅시다.

리두로그 파일과 성능문제

"checkpoint not complete ; unable to allocate file"

아카이브 파일과 성능문제

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]3-2단계 Temp 세그먼트 (0)	2012.12.19
[오라클]3-1단계 Undo 세그먼트 (0)	2012.12.19
[오라클]2-1단계 데이터 파일 (0)	2012.12.19
[오라클]1-5단계 래치와 경합 (0)	2012.12.19
[오라클]1-4단계 자바풀 영역 (0)	2012.12.19

Posted by redkite

, |

[오라클]2-1단계 데이터 파일

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:09

디스크의 I/O 경합

지금까지 디스크의 물리적 특성으로 인해 성능이 저하되는 현상이 왜 발생하는지 알아보았습니다. 그럼, 오라클 데이터베이스에서 디스크 상에 존재하는 파일들이 언제 읽기/쓰기 작업을 하게 되는지 알아봅시다. 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 상태를 모니터링 해 보십시오.

	$ 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 데이터 파일을 오픈 상태에서 재배치하는 절차입니다.

먼저, 위치 이동을 하고자 하는 데이터 파일의 테이블스페이스를 OFFLINE 하십시오.

SQL >	CONNECT system/manager
SQL >	ALTER TABLESPACE sample OFFLINE;

이동하고자 하는 sample 테이터 파일을 이동하려는 경로로 복사합니다.(SAMPLE 데이터파일은 오라클9i를 설치하면 기본적으로 설치됩니다. 해당 경로에서 다음 작업을 하십시오)

$ cp $HOME/dbs/sample01.dbf $HOME/disk4/sample01.dbf

다음과 같이 SAMPLE 데이터 파일의 경로를 RENAME 하십시오.

SQL >	ALTER TABLESPACE sample RENAME DATAFILE
	'$HOME/dbs/sample01.dbf' TO '$HOME/disk4/sample01.dbf';

RENAME 작업이 완료되었으면 엑세스가 가능하도록 해당 TABLESPACE를 ONLINE해 주십시오

SQL >

ALTER TABLESPACE sample ONLINE;

정상적으로 자료입력이 되는지 확인 한 다음 O/S상의 OLD 파일은 삭제하십시오.

SQL >	! rm /oracle/app/product/910/oradata/disk2/sample01.dbf
SQL >	EXIT

데이터 파일을 재배치 하는 방법은 두 가지가 있습니다. 첫 번째 방법은 데이터베이스를 종료한 후 마운트 상태로 구동하여 RENAME 하는 방법이며, 주로 SYSTEM 데이터 파일을 RENAME할 때 사용됩니다. 이유는, 오라클 데이터베이스의 필수 데이터 파일이기 때문입니다. 즉, 데이터베이스가 구동되려면 반드시 SYSTEM 데이터 파일은 사용가능 상태이어야 하기 때문에 마운트 상태에서 만 RENAME할 수 있습니다. 두 번째 방법은 데이터베이스를 오픈 상태에서 사용하고 있는 동안 특정 데이블스페이스를 RENAME하는 방법입니다. 주로 NON-SYSTEM 데이터 파일을 RENAME할 때 사용합니다.

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]3-1단계 Undo 세그먼트 (0)	2012.12.19
[오라클]2-3단계 아카이브 파일 (0)	2012.12.19
[오라클]1-5단계 래치와 경합 (0)	2012.12.19
[오라클]1-4단계 자바풀 영역 (0)	2012.12.19
[오라클]1-3단계 로그버퍼 영역 (0)	2012.12.19

Posted by redkite

, |

[오라클]1-5단계 래치와 경합

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:08

LRU 알고리즘

먼저, 데이터베이스의 구조에는 데이터베이스 만을 위한 메모리 영역으로 SGA 영역(데이터버퍼 캐시, 로그버퍼, 공유 풀, 라지 풀 영역)이 있으며 또한 2개의 리스트(LRU-LIST, DIRTY-LIST)가 있습니다. LRU-LIST에는 데이터버퍼 캐시영역의 각 블록구조에 대한 사용 상태정보가 저장되어 있고 DIRTY-LIST에는 사용자에 의해 이미 트랜잭션이 완료된 블록에 대한 상태정보가 저장되어 있습니다.

먼저, LRU 알고리즘이 필요한 이유는 공유 풀 영역은 메모리 영역이기 때문에 그 크기가 항상 제한되어 있으며 항상 여유롭게 사용되지 못하는 물리적 한계를 가지고 있기 때문입니다.

자~ 그럼 LRU 알고리즘의 구체적 실행방법을 알아봅시다.

하나의 사용자가 데이터베이스에 접속하면 사용자 프로세스와 서버 프로세스가 할당됩니다. 사용자가 SQL문을 실행하면 서버 프로세스는 구문분석을 끝낸 후 실행(EXECUTE)작업을 하게되는데 먼저, 데이터버퍼 캐시영역을 검색해서 현재 실행된 SQL문에 있는 테이블이 다른 사용자에 의해 이미 읽혀져서 데이터버퍼 캐시영역에 로더되어 있는지 확인해 봅니다.
읽혀진 테이블 데이터가 없다면 데이터 파일로부터 테이블을 읽어서 데이터버퍼 캐시영역에 로더하게 될 것입니다

이때, 읽혀진 데이터를 데이터버퍼 캐시영역에 저장해야 하는데 데이터버퍼 캐시영역은 수많은 블록들로 구성되어 있기 때문에 매번 이 영역에서 빈 블록을 찾아 읽혀진 테이블 데이터를 저장하는 일은 너무 많은 시간이 필요하게 될 것이고 결국 성능을 저하시키게 될 것입니다.

예를 들어, 도서관의 선반에는 수많은 책들이 비치되어 있을 것입니다. 새로운 도서가 반입되어 책을 비치하려고 하는데 도서관 사서는 어떤 선반이 비어있는지를 책이 반입될 때마다 확인한 후 비치한다면 매번 반입작업을 처리하는데 많은 시간이 소요되어 일의 능률이 오르지 않을 것입니다. 이러한 문제를 극복하기 위해서 선반관리 리스트를 작성하며 책의 이동, 배치현황, 선반관리 등 정보를 미리 만들어 놓고 반입이 될 때마다 이 리스트를 통해 비어있는 선반을 쉽게 찾아낸다면 좋은 성능을 기대할 수 있을 것입니다.

데이터베이스에서도 마찬가지로 서버 프로세스가 매번 빈 블록을 데이터버퍼 캐시영역으로부터 직접 찾아낸다면 너무 많은 시간이 소요될 수 있기 때문에 미리 만들어져 있는 LRU-LIST를 통해 빈 블록을 쉽게 찾아냅니다.

이번에는 LRU-LIST를 통해 빈 블록을 찾아내는 방법입니다.
먼저, 데이터버퍼 캐시영역에는 3가지 종류의 버퍼타입이 있습니다

첫 번째는 [FREE BUFFER]이며 비어있는 버퍼공간으로 새로운 데이터를 저장할 수 있는 공간입니다. 최초 사용자에 의해 읽혀진 테이블 데이터가 저장될 수 있는 블록입니다.

두 번째로는 [PINED BUFFER]인데 [FREE BUFFER]이었다가 사용자에 의해 데이터가 저장된 후 트랜잭션이 진행 중인 데이터를 저장하고 있는 버퍼공간입니다.

마지막으로, [DIRTY-BUFFER]는 사용자에 의해 진행 중인 트랜잭션이 완료된 버퍼공간입니다. 또한, LRU-LIST의 가장 오른쪽을 [LRU-END]라고 하며 가장 왼쪽은 [MRU(MOST RECENTLY USED)-END]라고 합니다. [LRU-END] 부분에는 데이터버퍼 캐시영역의 블록들 중에 가장 오래 전에 사용한 블록 정보들이 배치되고 [MUR-END] 부분에는 가장 최근에 사용된 블록 정보들이 배치됩니다.

자~ 서버 프로세스에 의해 읽혀진 테이블 데이터를 데이터버퍼 캐시영역의 빈 블록에 저장하기 위해 서버 프로세스는 LRU-LIST의 LRU-END 부분 블록들 부터 MRU-END 부분으로 검색하면서 [FREE BUFFER] 블록을 찾습니다. 먼저, [D5] 블록을 읽었는데 이 블록은 [PINED BUFFER]이군요. 다른 사용자에 의해 사용되고 있는 버퍼공간이므로 이공간을 사용해서는 안될 것입니다.

다음은 [G9] 블록인데 [FREE-BUFFER] 블록이군요. 서버 프로세스는 데이터버퍼 캐시영역으로 가서 이 블록에 테이블 데이터를 저장하게 됩니다. 이제 [G9] 블록은 [FREE-BUFER]이었다가 [PINED-BUFFER]로 속성이 바뀌게 되었고 가장 최근에 사용한 블록이 되었기 때문에 MRU-END 부분으로 블록정보가 이동하게 됩니다. 즉, [A1] 블록정보 왼쪽에 [G9] 블록정보가 배치됩니다. 하나의 블록으로 읽혀진 데이터가 모두 저장되었다면 작업은 끝나게 될 것입니다. 만약, 하나의 블록으로 데이터를 모두 저장하지 못했다면 [FREE BUFFER]를 찾기 위한 노력은 계속 진행됩니다.

다음은 [S3] 블록인데 [PINED BUFFER]이므로 스킵, 다음은 [F2] 블록인데 [DIRTY BUFFER] 블록이군요. 이 블록은 다른 사용자에 의해 저장된 데이터가 COMMIT 또는 ROLLBACK 문장을 만나 트랜잭션이 완전히 끝난 블록이므로 빨리 테이블에 변경된 정보를 저장해야할 블록입니다. [FREE-BUFFER]를 찾기 위해 검색을 하다가 [DIRTY-BUFFER]를 만나게 되면 이 블록의 정보들은 DIRTY-LIST로 이동되어 별도로 관리됩니다. 그리고, 다시 다음 블록을 검색합니다.

이번에는 [K7] 블록, [FREE-BUFFER]이군요. 서버 프로세스는 읽혀진 데이터를 저장합니다. 이런 방법으로 계속해서 읽혀진 데이터를 저장하기 위해 [FREE-BUFFER]를 찾게되는데 너무 많은 사용자들이 이러한 작업을 하다보면 LRU-LSIT를 아무리 검색해도 [FREE-BUFFER]를 찾지 못하는 경우가 발생할 수도 있습니다. 이런 경우에는 DIRTY LIST에 저장된 블록 정보들을 DBWR 백그라운드 프로세스에 의해 테이블에 저장하고 그 블록들을 [FREE-BUFFER]로 만들어 새로운 데이터를 저장하게 됩니다.

서버 프로세스는 이러한 방법으로 데이터 파일로부터 읽은 데이터를 데이터버퍼 캐시영역에 저장하기 위해 [FREE-BUFFER]를 찾게되는데 이런 메커니즘을 LRU 알고리즘이라고 합니다.

LRU 래치

"LRU 알고리즘"에서 사용자가 실행한 SQL문에 의해 테이블 데이터가 데이터버퍼 캐시 영역에 저장되기 위해서는 LRU LIST로부터 FREE BUFFER를 찾아야하는데, 만약 서버 프로세스가 하나의 FREE BUFFER를 찾았다면 이것을 "래치를 얻었다"라고 합니다. 하나의 서버 프로세스가 얻은 하나의 래치는 약 50개의 FREE BUFFER를 동시에 처리할 수 있습니다.

하나의 서버 프로세스는 지금 이 순간에도 하나의 래치를 얻기 위해 다른 서버 프로세스와 경합을 벌여 FREE BUFFER를 찾게 됩니다. 오라클 데이터베이스를 설치하면 LRU 래치의 기본 값은 1입니다. 사용자 수가 많은 데이터베이스 환경이라면 래치를 얻기 위한 경합(CONTENTION)으로 인해 성능이 저하될 수도 있습니다.

래치의 경합현상은 V$LATCH 자료사전을 통해 알 수 있는데 GETS 컬럼은 LATCH를 얻은 수이며 SLEEPS 컬럼은 LATCH를 얻지 못하고 대기(WAITING)했던 수를 의미합니다.
GETS 컬럼에 대해 SLEEPS 컬럼이 99% 이상의 백분율을 보일 때 경합이 발생하지 않기 때문에 좋은 성능을 기대할 수 있습니다. 만약, 백분율이 좋지 못하다면 다음 파라메터를 통해 래치 수를 더 높게 설정해 주어야 합니다.

DB_BLOCK_LRU_LATCHES = [n]

오라클 데이터베이스를 설치하면 LRU 래치의 기본 값은 1입니다. 이 값은 시스템의 CPU 수와 균형을 맞추어서 설정해야 하는데 최대 값은 (CPU 수 X 2 X 3 또는 데이터버퍼 수 / 50)계산에 의해 산출할 수 있으며 CPU수가 하나인 시스템 환경에서 래치수의 증가는 성능에 별로 도움이 안됩니다.

참 !!! 이 기능은 오라클 9i 버전에서 추가된 동적인 SGA 관리 기법을 사용할 때는 설정해 줄 필요가 없습니다. 래치 수를 오라클 서버가 자동으로 관리해 주기 때문입니다.

리두로그 버퍼 래치

오라클 데이터베이스 구조에서 발생하는 두 번째 래치는 리두로그 버퍼영역에서 발생합니다.
많은 사용자들이 동시에 UPDATE, INSERT, DELETE문을 실행하면 변경 행의 이전 데이터와 이후 데이터가 모두 리두로그 버퍼영역에 저장되어야 합니다. 하지만 하나의 구조로 활성화되어 있는 메모리 공간에 많은 사용자들의 변경 데이터가 저장되려면 서로 리두로그 버퍼 영역을 사용하기 위해 경합을 벌여야 합니다. 결국 경합현상으로 인해 대기상태가 발생하게 되고 성능이 저하되게 됩니다.

서버 프로세스가 변경 데이터를 저장하기 위해 먼저 리두로그 버퍼의 공간을 확보하게 되는데 이것을 리두할당 래치(Redo Allocation Latch)라고 합니다. 리두할당 래치를 입수한 서버 프로세스는 변경 데이터를 리두로그 버퍼에 저장해야 하는데 이것을 리두카피 래치(Redo Copy Latch)라고 합니다.

래치의 유형

오라클 데이터베이스에서 제공하는 래치는 다음과 같이 크게 2가지 유형이 있습니다.

WILLING TO WAIT 타입

이 타입은 서버 프로세스가 래치를 이용할 수 없을 때 잠시 기다렸다가 다시 래치를 요청하고 또 이용할 수 없으면 기다렸다가 다시 래치를 요청하게 됩니다. 일정한 회수를 반복적으로 요청하다 계속 래치를 얻지 못하면 수면상태(SLEEP)에 빠졌다가 일정시간이 지나면 다시 래치를 요청하는 방법으로 운영됩니다. 리두할당 래치가 대표적인 WILLING-TO-WAIT 타입입니다.

SQL>	SELECT b.name, a.misses / a.gets "MISSES/GETS"
	FROM V$LATCH A, V$LATCHNAME B
	WHERE b.name = ('redo allocation')
	And b.latch# = a.latch#;

NAME		MISSES/GETS

redo allocation		.000023226
redo copy		0

<- WILLING-TO-WAIT 타입의 래치 경합이 발생하면 MISSES/GETS의 결과 값이 1% 이상의 값을 보이게 됩니다. MISSES의 경우는 래치를 얻지 못한 경우이며, GETS는 래치를 얻은 경우를 나타냅니다. 이런 경우에는 리두로그 버퍼가 너무 작아 경합이 발생하는 경우이므로 리두로그 버퍼의 크기를 보다 크게 늘려줘야 합니다.

IMMEDIATE 타입

서버 프로세스가 WILLING-TO-WAIT 래치와 같이 요청된 래치를 이용할 수 없을 경우 기다리지 않고 바로 래치를 얻는 타입입니다. 리두카피 래치가 대표적인 IMMEDIATE 타입입니다.

SQL>	SELECT	b.name,
		a.immediate_misses / decode((a.immediate_gets + immediate_misses), 0, 1,
		(a.immediate_gets + a.immediate_misses)) "MISSES/GETS"
	FROM	V$LATCH A, V$LATCHNAME B
	WHERE	b.name = ('redo copy')
		And b.latch# = a.latch#;

NAME			MISSES/GETS
-------------------------------------------			---------------
redo allocation			0
redo copy			.000118391

<- IMMEDIATE 타입의 래치 경합이 발생하면 MISSES/GETS의 결과 값이 1% 이상의 값을 보이게 됩니다. MISSES의 경우는 래치를 얻지 못한 경우이며, GETS는 래치를 얻은 경우를 나타냅니다. 이런 경우에는 리두로그 버퍼가 너무 작아 경합이 발생하는 경우이므로 리두로그 버퍼의 크기를 보다 크게 늘려줘야 합니다.

리두로그 래치의 튜닝

리두할당 래치에서 MISSES/GET의 비율이 1% 이상의 경합이 발생한다면

LOG_SMALL_ENTRY_MAX_SIZE 파라메터의 값을 감소시켜야 합니다. 만약, 변경 데이터의 양이 이 파라메터의 값보다 작으면 리두할당 래치를 얻은 상태에서 변경 데이터를 복사하는 작업까지 완료할 수 있고, 만약, 크다면 리두할당 래치를 할당 받은 후 래치를 해제하게 됩니다.

리두카피 래치에서 MISSES/GET의 비율이 1% 이상의 경합이 발생한다면

LOG_SMALL_ENTRY_MAX_SIZE 파라메터의 값이 작아서 리두할당 래치가 너무 빨리 해제되어 리두카피 래치가 시작되는 문제 때문에 발생한 것입니다.
이런 경우에는 리두카피 래치 수를 증가시켜 경합이 발생하지 않도록 해야 합니다.

리두카피 래치의 기본 수는 해당 시스템의 CPU 수이며 최대 CPU수의 2배까지 정의할 수 있습니다. LOG_SIMULTANEOUS_COPIES 파라메터에 의해 리두카피 래치수가 결정됩니다.

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-3단계 아카이브 파일 (0)	2012.12.19
[오라클]2-1단계 데이터 파일 (0)	2012.12.19
[오라클]1-4단계 자바풀 영역 (0)	2012.12.19
[오라클]1-3단계 로그버퍼 영역 (0)	2012.12.19
[오라클]1-2단계 버퍼캐쉬 영역 (0)	2012.12.19

Posted by redkite

, |

[오라클]1-4단계 자바풀 영역

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:07

1-4 단계 : 자파풀 영역

자바 풀 영역의 튜닝

오라클 8 버전부터 제공되는 Jserver 옵션은 데이터베이스 내에 자바 컴파일러와 자바 실행코드를 내장하고 있어 데이터베이스 내에서 자바 애플리케이션을 작성할 수도 있고 또한 실행할 수도 있습니다. 이와 같은 Jserver 옵션을 설치하여 개발하는 곳은 반드시 SGA 영역 내에 JAVA POOL 영역을 활성화 시켜야 합니다.
자바 풀 영역을 활성화하기 위해서는 다음과 같이 환경설정을 하셔야 합니다.

$ vi init<SID>.ora
--------------------
JAVA_POOL_SIZE = 20M	ß JAVA POOL 영역의 크기를 결정합니다.
SHARED_POOL_SIZE = 50M	ß SHARED POOL 영역의 크기를 결정합니다.

JAVA_POOL_SIZE 파라메터

- 이 파라메터의 기본값은 20MB 입니다.
- JAVA 애플리케이션으로 개발되는 환경에서는 50MB 이상의 크기가 요구됩니다.

SHARED_POOL_SIZE 파라메터

- 하나의 클래스가 실행될 때 마다 8 KB의 공간이 공유 풀 영역에서 요구됩니다.
- JAR 파일을 로더할 때 50 MB의 공간이 요구됩니다.
- 반드시 공유 서버 프로세스 환경이어야 사용가능 합니다

다음은 데이터베이스의 현재 공유 풀 영역의 크기를 분석하는 방법입니다.

SQL>	SELECT * FROM v$sgastat
	WHERE pool = 'java pool';

POOL		NAME	BYTEX
-------------------------------------------------
java pool		free memory	3000234
java pool		memory in use	1974720
<- 현재 사용중인 자바 풀 영역(Memory in Use)의 크기와 사용 가능한 크기(Free Memory)를 알 수 있습니다.

기타 자바 풀 관련 파라메터

JAVA_SOFT_SESSIONSPACE_LIMIT

세션에서 자바 풀 영역을 사용할 수 있는 평균크기를 제한하며 이 값을 초과하면 TRACE 파일에 경고 메시지가 저장됩니다. 기본값은 1M 입니다.

JAVA_MAX_SESSIONSPACE_SIZE

세션에서 사용할 수 있는 자바 풀 영역의 최대크기를 제한하며 이 값을 초과하면 다음과 같이 에러가 발생하고 해당 세션은 종료됩니다. 기본값은 4GB 입니다.

ORA-29554: unhandled Java out of memory condition

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-1단계 데이터 파일 (0)	2012.12.19
[오라클]1-5단계 래치와 경합 (0)	2012.12.19
[오라클]1-3단계 로그버퍼 영역 (0)	2012.12.19
[오라클]1-2단계 버퍼캐쉬 영역 (0)	2012.12.19
[오라클]1-1단계 공유풀 영역 (0)	2012.12.19

Posted by redkite

, |

[오라클]1-3단계 로그버퍼 영역

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:06

1-3 단계 : 로그버퍼영역

로그 버퍼 영역의 튜닝 분석

사용자들이 데이터베이스에 접속 한 후 INSERT, UPDATE, DELETE 작업을 실행하면 트랜잭션에 의해 만들어진 모든 변경정보(UPDATE문을 실행했다면 변경 전 데이터와 변경 후 데이터)가 로그버퍼 영역에 저장됩니다. 그러나, 로그버퍼 영역에는 사용자의 모든 변경된 데이터를 저장해 둘 수 없기 때문에 이 데이터들을 영구히 저장하기 위해 로그기록기(LGWR)에 의해 로그파일에 기록하게 됩니다.

리두로그 버퍼영역은 여려 명의 사용자가 같이 사용하는 공간이기 때문에 너무 많은 서버 프로세스가 동시에 많은 변경된 데이터를 저장하려고 한다면 서버 프로세스간에 경합(CONTENTION)이 발생할 것입니다. 또한, 사용자의 변경된 정보를 저장할 충분한 리두로그 버퍼공간이 없다면 미처 변경된 정보를 저장하지 못하고 대기(WAITING)해야 하는 경우도 발생할 것입니다.

이러한 문제가 발생하면 결국 UPDATE, INSERT, DELETE 시에 좋은 성능을 기대할 수 없습니다. 로그버퍼 영역의 크기는 INIT<DB명>.ORA 파일에 있는 LOG_BUFFER 파라메터에 의해 결정됩니다. 이 파라메터를 설정하지 않으면 오라클 서버는 블록크기(DB_BLOCK_SIZE 파라메터의 크기)의 4배 값을 기본 로그버퍼 영역의 크기로 할당합니다.

로그 버퍼 캐시 영역을 보다 크게 할당한다.

로그버퍼 영역에 대한 튜닝은 여러 사용자가 동시에 변경 작업을 했을 때 변경된 데이터를 저장할 만한 충분한 메모리 공간이 할당되어야 한다는 것입니다. 이 공간이 너무 작으면 서로 변경 데이터를 저장하기 위해 경합(CONTENTION)이 발생하고 결론적으로 어떤 서버 프로세스는 대기(WAITING) 상태가 발생할 것입니다.

다음은 자료사전을 통해 로그버퍼 영역을 분석하는 방법입니다.

V$SESSION_WAIT 자료사전을 참조하면 이 영역에 대한 튜닝 여부를 확인할 수 있습니다

SECOND_IN_WAITS 컬럼의 값이 계속 증가되는 값을 나타내고 STATE 컬럼이 "WAITING"을 나타내면 로그버퍼 영역이 작아서 서로 프로세스간에 경합이 발생하고 있음을 의미합니다.

SQL>	SELECT sid, event, second_in_wait, state
	FROM v$session_wait
	WHERE event = 'log buffer space%';

	SID	EVENT	SECOND_IN_WAIT	STATE
	-----------------------------------------------------
	5	log buffer space	110	WAIT

V$SYSSTAT 자료사전에서 서버 프로세스가 로그정보를 저장했던 로그버퍼의 블록 수(REDO ENTRIES)와 로그버퍼의 경합으로 인해 발생한 대기상태에서 공간이 생겨 다시 로그버퍼 공간을 할당 받았던 블록 수(REDO BUFFER ALLOCATION RETRIES)를 확인할 수 있습니다.

REDO BUFFER ALLOCATION RETRIES의 값은 0 이어야 하고 REDO ENTRIES 값의 1% 미만일 때 로그버퍼 영역에 대한 좋은 성능을 기대할 수 있습니다.

SQL>	SELECT name, value
	FROM v$sysstat
	WHERE name IN ('redo buffer allocation retries', 'redo entries');

	NAME	VALUE
	--------------------------------------------
	redo entries	2015
	redo buffer allocation retries	12

만약, 기준치에 적합하지 않은 결과가 나오면 리두로그 영역이 작아서 발생한 문제임으로 LOG_BUFFER 파라메터 값을 더 높게 설정해야 합니다.

$ cd $HOME/dbs

$ vi init<SID>.ora

LOG_BUFFER = 8192 ß 이전 값 보다 더 크게 설정해야 합니다.

:wq!

인덱스 생성시 NOLOGGING절을 적용한다.

사용자가 테이블을 생성하고 데이터를 입력, 수정, 삭제하면 트랜잭션에 의해 발생한 모든 변경 전, 변경 후 정보가 로그버퍼 영역에 저장되는데 이러한 방법을 로깅모드(LOGGING)라고 합니다. 테이블, 인덱스를 생성할 때 NOLOGGING 키워드를 사용하면 생성 후 발생하는 모든 트랜잭션의 로그정보가 로그버퍼 영역에 저장되지 않습니다. 만약, 해당 테이블의 데이터가 다른 시스템 또는 다른 데이터베이스로부터 이동된 테이블이거나 언제든지 복구가 가능한 테이블이라면 LOGGING 모드로 데이터베이스를 사용하는 것보다 NOLOIGGING 모드로 사용하는 것이 로그버퍼 영역을 적게 사용하기 때문에 로그버퍼 영역에 대한 경합 현상을 최소화시킬 수 있습니다. 인덱스는 테이블을 통해 언제든지 만들 수 있기 때문에 NOLOGGING모드로 만드는 것이 좋은 성능에 도움이 될 수 있습니다.

다음 예제를 따라 해 보세요.
EMP10 테이블을 NOLOGGING 키워드로 생성하고 다시 LOGGING 모드로 바꿔보십시오. 그리고, I_EMP10 인덱스를 NOLOGGING 모드로도 생성해 보십시오.

SQL>	CONNECT scott/tiger
SQL>	CREATE TABLE emp10 (no NUMBER, name VARCAHR2(10))
	NOLOGGING;

SQL>	ALTER TABLE emp10 LOGGING;
SQL>	CREATE INDEX I_emp10 ON emp10 NOLOGGING;

앞에서도 언급되었듯이 테이블을 NOLOGGING 모드로 설정할 때는 데이터의 유형과 복구 전략에 대한 충분한 고려를 한 후 결정해야 하며, 인덱스에 대해선 자주 변경이 발생하는 테이블에 대해 적용한다면 INSERT, UPDATE, DELETE 작업 시 보다 빠르게 변경작업을 수행할 수 있을 것 입니다. 만약, 데이터베이스에 문제가 발생하여 복구작업을 수행해야 한다면 인덱스의 경우에는 재 생성하는 것이 복구이기 때문에 반드시 LOGGING 모드일 필요가 없습니다.

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-1단계 데이터 파일 (0)	2012.12.19
[오라클]1-5단계 래치와 경합 (0)	2012.12.19
[오라클]1-4단계 자바풀 영역 (0)	2012.12.19
[오라클]1-2단계 버퍼캐쉬 영역 (0)	2012.12.19
[오라클]1-1단계 공유풀 영역 (0)	2012.12.19

Posted by redkite

, |

[오라클]1-2단계 버퍼캐쉬 영역

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:06

1-2 단계 : 버퍼캐쉬 영역

데이터 버퍼 캐시 영역의 튜닝 분석

"SQL문의 처리과정"을 통해 알아보았듯이 데이터 파일로부터 읽혀진 데이터가 저장되는 메모리 영역을 데이터버퍼 캐시영역이라고 하며 이 영역의 크기는 INIT<DB명>.ORA 파일에 정의되는 DB_CACHE_SIZE 파라메터에 의해 결정됩니다. 예를 들어, 윈도우 시스템에서 어떤 텍스트 파일의 내용을 편집하기 위해 더블-클릭을 하면 잠시 후 편집기 프로그램이 실행되고 파일의 내용이 화면에 출력됩니다.

이와 같이 실행되는 과정을 살펴보면 선택된 파일은 디스크로부터 읽혀지고 시스템의 메모리 버퍼영역에 로더된 다음 편집기를 통해 사용자의 화면에 출력되는 것 입니다. 만약, DB_CACHE_SIZE 파라메터의 값이 10MB일 때 읽혀지는 테이블의 크기는 20MB이라면 어떻게 될까요 ? 읽혀지는 테이블이 메모리 크기보다 크기 때문에 한번에 테이블 데이터 모두를 메모리에 저장하지 못하고 2번에 나누어서 저장해야 할 것입니다.

그렇다면, DB_CACHE_SZIE가 20MB이라면 어떻게 될까요 ? 당연히 읽혀진 테이블 데이터 20MB를 한번에 데이터버터 캐시영역에 저장하게 될 것입니다. 2번 이상, 여러 번 나누어서 데이터를 처리하는 것보다는 한번에 데이터를 처리하는 것이 좋은 성능을 기대할 수 있는 방법이 될 것입니다. 결론적으로, 데이터버퍼 캐시영역의 크기는 처리하려는 테이블 데이터를 충분히 로더할 수 있을 만큼 설정되어 있는 경우와 그렇지 못한 경우에 의해 성능차이가 발생할 수 있다는 것입니다.

데이터 버퍼 캐시 영역의 튜닝 방법

데이터 버퍼 캐시 영역을 보다 크게 할당한다.

그럼, 데이터버퍼 캐시영역에 대한 성능을 향상시킬 수 있는 첫 번째 방법을 알아 보겠습니다. 사용자가 질의한 데이터를 데이터버퍼 캐시영역에 로더하려면 충분한 메모리 공간이 필요합니다. 공간이 부족하면 한번에 데이터를 로더하지 못하고 여러 번 나누어서 로더해야 하기 때문에 성능이 저하될 수 있습니다.

다음은 자료사전을 통해 데이터 버퍼 캐시영역의 튜닝 상태를 분석하는 방법입니다.
V$SYSSTAT 자료사전을 참조하면 이 영역에 대한 튜닝 여부를 확인할 수 있습니다.

$ sqlplus scott/tiger

SQL> select * from big_dept;

SQL> select * from big_emp;

SQL> connect system/manager

SQL> select 1 - ((phy.value)/(cur.value + con.value)) "CACHE HIT RATIO"

from v$sysstat cur, v$sysstat con, v$sysstat phy

where cur.name = 'db block gets'

and con.name = 'consistent gets'

and phy.name = 'physical reads';

CACHE HIT RATIO

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

.973257182

"PHYSICAL READS"는 사용자가 SQL문을 실행했을 때 데이터 파일로부터 테이블을 읽은 블록 수를 의미하며

"DB BLOCK GETS"는 DML문의 처리과정에서 처럼 데이터버퍼 캐시영역에 있는 변경 후 데이터를 참조할 때 읽은 블록 수를 의미합니다.

"CONSISTENT GETS"은 변경작업 후 ROLLBACK문을 실행했을 때 변경 전 데이터로 복구하기 위해 이전 데이터를 저장해 두는 메모리 영역으로부터 읽은 블록 수입니다.

즉, "PHYSICAL READS"는 물리적 구조에서 읽은 블록 수이며 "DB BLOCK GETS"와 "CONSISTE NT GETS"는 논리적 구조(메모리 영역)에서 읽은 블록 수를 의미합니다. 되도록, 하나의 테이블을 질의할 때 물리적 구조로부터 데이터를 읽는 것보다는 논리적 구조로부터 읽은 블록 수가 더 많아야 성능에 도움이 될 것입니다. 만약, 충분한 데이터 버퍼 공간이 확보되었다면 "DB BLOCK GETS"와 "CONSISTENT GETS"가 더 높은 값이 출력될 것입니다.

그리고, 다음과 같은 공식에 의해 캐시 히트율을 계산할 수 있는데 이것은 사용자가 실행한 SQL문의 테이블이 이미 다른 사용자에 의해 읽혀져서 데이터버퍼 영역에서 발견되었던 블록
수를 백분율로 계산한 것입니다.

히트율 = 1 - (PHYSICAL READS / (DB BLOCK GETS + CONSISTENT GETS))

캐시 히트율이 90% 이상일 때 충분한 데이터버퍼 캐시영역이 설정되어 있고 좋은 성능을 기대할 수 있습니다. 만약, 캐시 히트율이 좋지 못하다면 다음 파라메터를 통해 데이터버퍼 캐시영역의 크기를 크게 설정해 주어야 합니다.

$ cd $HOME/dbs

$ vi init.ora

………………………

DB_CACHE_SIZE = [크기]

:wq!

<잠깐>

오라클 9i 이전 버전에서는 DB_CACHE_SIZE 파라메터가 DB_BLOCK_SIZE와 DB_BLOCK_BUFFERS 파라메터로 나누어져 사용되었습니다. 2개 파라메터 값을 곱한 결과가 DB_CACHE_SIZE의 값과 같습니다.

멀티 버퍼 캐시영역을 할당한다.

일반적으로 오라클 데이터베이스의 SGA 영역에는 데이터 버퍼 캐시영역이 하나의 구조로 생성되는데 이 영역을 데이터의 성격과 크기에 따라 여러 개의 영역으로 나누어 생성할 수 있는 기법을 멀티 버퍼캐시 영역이라고 합니다.

위 그림에서 보시는 것처럼 데이터버퍼 캐시영역을 3가지 영역으로 나누어 생성할 수 있습니다. 첫 번째 영역은 KEEP 데이터버퍼 캐시 영역입니다. 이 영역은 운영되는 데이터베이스 환경에서 빈번하게 사용되는 테이블 데이터를 계속 데이터버퍼 캐시영역에 상주시키기 위한 공간입니다. 이 공간에 저장될 수 있는 테이블은 그 크기가 너무 크면 안되고 사용자에 의해 빈번하게 사용되는 테이블이어야 합니다.

예를 들어, 부서 테이블, 사원 테이블, 코드 테이블 등은 모든 애플리케이션 프로그램에서 항상 참조되며 또한 테이블의 크기가 크지 않기 때문에 가장 적합한 테이블입니다.

두 번째 영역은 RECYCLE 데이터 버퍼 캐시영역입니다, 이 영역에는 자주 사용되지 않고 가끔 사용되는 테이블이면서 테이블의 크기가 아주 큰 테이블을 검색할 때 사용됩니다. 사용자에 의해 검색된 테이블은 이 영역에 데이터를 로더한 후 작업이 완료되면 모두 클리어(CLEAR) 됩니다.
마지막으로, DEFAULT 데이터 버퍼 캐시영역은 하나의 데이터버퍼 캐시영역을 가진 환경에서 사용되던 구조와 동일한 의미를 가집니다. 즉 여러 사용자에 의해 공용으로 사용되는 메모리 공간입니다. 다음은 다중 풀 영역을 설정하는 파라메트들 입니다.

DB_CACHE_SIZE = [크기]
DB_KEEP_CACHE_SIZE = [크기]
DB_KEEP_RECYCLE_SIZE = [크기]

DB_CACHE_SIZE 파라메터는 DEFAULT 데이터버퍼 캐시영역의 크기를 의미합니다.
DB_KEEP_CACHE_SIZE 파라메터는 KEEP 데이터버퍼 캐시영역의 크기를 의미합니다.
DB_KEEP_RECYCLE_SIZE 파라메터는 RECYCLE 데이터버퍼 캐시영역의 크기를 의미합니다.

다음은 다중 풀 영역에 로더될 테이블을 다음과 생성해야 합니다.

< 문법 >

CREATE TABLE [테이블명]

([컬럼-1] [데이터 타입],

…………………………………….

[컬럼-N] [데이터 타입])

STORAGE ( BUFFER_POOL [KEEP | RECYCLE] );

ALTER TABLE [테이블명]

STORAGE ( BUFFER_POOL [KEEP | RECYCLE] );

KEEP 데이터버퍼 캐시영역

자~ 그렇다면 KEEP 데이터버퍼 캐시 영역를 설정하고 싶은데 그 크기는 얼마나 할당해야 할까요 ? 크기를 설정하는 방법은 다음과 같습니다.
먼저, ANALYZE 명령어를 실행하여 캐싱하려는 테이블이 몇 개의 블록으로 구성되어 있는지를 분석해야 합니다. 테이블의 모든 데이터를 KEEP 데이터버퍼 영역에 저장하기 위해서 테이블의 실제 블록 수를 분석하는 것입니다.

$ sqlplus scott/tiger
SQL>	analyze table big_emp compute statistics;
SQL>	analyze table big_dept compute statistics;
SQL>	select table_name, blocks
	from user_tables
	where table_name in ( 'BIG_EMP' , 'BIG_DEPT' );

TABLE_NAME BLOCK -----------------------------------------
BIG_DEPT 5 <- 14 행을 저장하고 있슴
BIG_EMP 370 <- 28955 행을 저장하고 있슴
<-sum(blocks + 1) 을 모두 포함할 만큼의 충분한 크기로 keep buffer pool을 설정해야 합니다.

ANALYZE 명령어를 실행하면 분석결과가 DBA_TABLES, USER_TABLES 자료사전에 저장 됩니다.
만약, KEEP 데이터버퍼 캐시영역에 5개의 테이블을 저장하고 싶다면, 이러한 방법으로 각 테이블의 블록 수를 분석한 다음 전체 블록 수를 집계하여 그 값을 DB_KEEP_CACHE_SIZE 파라메터에 할당해 주면 됩니다.

$ cd $ORACLE_HOME/dbs

$ vi init<DB명>.ora

DB_KEEP_CACHE_SIZE = 4M ß BIG_DEPT, BIG_EMP 가 상주될 메모리 공간

DB_RECYCLE_CACHE_SIZE=4M ß RECYCLE 영역

:wq!

$ sqlplus "/as sysdba"

SQL > startup force ß Shutdown 후 다시 Startup

SQL > show parameter db_keep_cache_size

NAME TYPE VALUE

db_keep_cache_size big integer 8388608

RECYCLE 데이터버퍼 캐시영역

이번에는 RECYCLE 데이터버퍼 캐시 영역를 설정하고 싶은데 그 크기는 얼마나 할당해야 할까요 ? 크기를 설정하는 방법은 다음과 같습니다.
먼저, V$CACHE 자료사전을 참조하기 위해서 $HOME/rdbms/admin 경로에 있는 CATPARR.SQL 스크립트를 실행하십시오.

SQL>	CONNECT SYS/CHANGE_ON_INSTALL AS SYSDBA
SQL>	show parameter db_recycle_cache_size

NAME		TYPE	VALUE
----------------------------------------------------------
db_recycle_cache_size		big integer	8388608

SQL>	START $HOME/rdbms/admin /catparr.sql
SQL>	SELECT owner#, name, count(*) blocks
	FROM v$cache
	WHERE name = 'EMP'
	GROUP BY owner#, name;

OWNER	NAME		BLOCK
5	EMP		200

실행된 결과가 V$CACHE 자료사전에 저장됩니다. 이 결과에서 해당 테이블이 데이터버퍼 캐시영역에서 필요로 하는 블록 수가 어느 정도인지 알 수 있습니다. 이 블록 수의 1/4 값을 DB_RECYCLE_CACHE_SIZE 파라메터에 설정하십시오. RECYCLE 영역에는 가끔 질의되는 테이블 데이터가 저장될 메모리 공간이기 때문에 결과의 전체 블록 수를 설정할 필요가 없습니다.
현재 데이터베이스에 설정된 다중 풀 영역에 대한 정보를 참조할 때는 V$BUFFER_POOL_STA TISTICS와 V$BUFFER_POOL 자료사전을 참조하십시오.

SQL > select name, buffers from v$buffer_pool;
NAME	BUFFERS
--------------	-----------
KEEP	1934	<- KEEP 영역의 크기
RECYCLE	1934	<- RECYCLE 영역의 크기
DEFAULT	967	<- DEFAULT 영역 크기

다중 버퍼 캐시영역이 활성화 되었으면 개발자가 실행할 테이블 중에 KEEP 영역과 RECYCL
영역에 로더될 테이블을 결정합니다.

$ sqlplus scott/tiger
SQL>	alter table big_emp storage ( buffer_pool keep);
SQL>	alter table big_dept storage ( buffer_pool keep);
SQL>	alter table account storage ( buffer_pool recycle);

각 테이블이 어떤 버퍼 캐시영역에 로더 되는지를 확인하는 방법입니다.

SQL>	select table_name, buffer_pool
	from user_tables
	where table_name in ( 'BIG_EMP' , 'BIG_DEPT' );

TABLE_NAME		BUFFER_
----------------		-------
BIG_EMP		KEEP <-KEEP 될 테이블 정보를 의미합니다.

SQL>	SELECT * FROM big_dept;
SQL>	SELECT * FROM big_emp;
	SELECT * FROM account;

다음 문장은 다중 버퍼 캐시영역의 현재 튜닝상태를 제공해 줍니다,

SQL>	select name,
	1-(physical_reads/(db_block_gets+consistent_gets)) "Multiple RATIO"
	from	v$buffer_pool_statistics
	where	db_block_gets + consistent_gets > 0 ;

NAMA		Multiple RATIO
-----------		---------------
KEEP		.839300938 <-100 %에 가까울수록 최적
RECYCLE		.819349315 <-50 %가 최적
DEFAULT		.752205989 <-90 % 이상이 가장 최적

KEEP 영역은 모든 테이블을 캐싱하기 위한 공간이므로 100% 히트율을 유지하는 것이 좋은 성능을 제공하고, RECYCLE 영역은 잠깐 사용되는 공간이므로 50% 히트율로도 충분하며, DEFAULT 영역은 일반적으로 사용되는 공간이므로 90% 히트율을 유지하는 것이 좋습니다.

<잠깐>오라클 8i 버전에서 멀티 버퍼 캐시 영역의 활성화 방법

오라클 9i 이전 버전과 이후 버전에서 멀티 버퍼캐시 영역을 활성화하는 방법의 차이는 문법적인 측면입니다. 즉, 9i 버전에서는 SGA 영역을 동적으로 관리하는 기능이 추가되었기 때문에 이와 관련된 문법이 변경되었습니다

$ vi init<SID>.ora

DB_BLOCK_BUFFERS = 3147

DB_BLOCK_LRU_LATCHES = 6

BUFFER_POOL_KEEP = (BUFFERS:1400, LRU_LATCHES:1)

BUFFER_POOL_RECYCLE = (BUFFERS:200, LRU_LATCHES:3)

<잠깐>오라클 8i 버전에서 멀티 버퍼 캐시 영역의 활성화 방법

ID NAME LO_SETID HI_SETID SET_COUNT BUFFERS LO_BNUM JI_BNUM

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

0 0 0 0 0 0 0

1 KEEP 3 3 1 1400 0 0

2 RECYCLE 4 6 3 200 0 0

3 DEFAULT 1 2 2 1547 1600 0

DB_BLOCK_BUFFERS와 DB_BLOCK_SIZE 파라메터가 오라클 9i에서 DB_CACHE_SIZE 파라메터로 바뀌었고, DB_BLOCK_LRU_LATCHES와 같은 래치 파라메터는 오라클 서버가 자동 관리해 주는 방법으로 변경되어 9i에서는 정의하지 않습니다.

자주 사용되는 테이블을 캐싱한다.

다중 멀티 풀 영역을 이용한 성능향상 기법이 오라클 8 버전부터 추가된 기능이라면 캐싱 테이블 기법은 오라클 이전 버전부터 사용되던 기법입니다. 사용자가 실행한 SQL문에 의해 테이블 전체 스캔 방법으로 테이블이 검색된 후 계속해서 데이터 버퍼 캐시영역에 상주하게 됩니다.

정상적인 데이터버퍼 캐시 메커니즘이라면 다른 서버 프로세스에 의해 데이터버퍼 캐시영역은 재 사용되겠지만 캐싱 테이블로 만들어진 블록들은 다른 서버 프로세스에 의해 재 사용되지 않고 계속해서 상주할 수 있게 되는 기능입니다. 다중 멀티 풀 영역에서 KEEP 데이터버퍼 캐시영역과 유사한 기능을 가지고 있습니다. 이 기법을 통해 빈번하게 사용되는 테이블을 데이터버퍼 캐시영역에 상주시키면 검색 시 성능을 향상시킬 수 있습니다.

다음 문법은 캐시 테이블을 생성하는 방법입니다.

문법	CREATE	TABLE	[테이블명]	([컬럼] [데이터타입])	[CACHE / NOCACHE];
문법	ALTER	TABLE	[테이블명]	[CACHE] /NOCACHE];

테이블을 생성할 때 [CACHE] 키워드를 사용하십시오. 또는, 이미 생성되어 있는 테이블을 [CACHE] 키워드로 변경할 수도 있습니다. 또한, [CACHE]된 테이블을 [NOCACHE]로 변경할 수도 있습니다. 만약, CACHE된 테이블 데이터가 [NOCACHE]로 바뀌면 데이터베이스가 종료되고 다시 시작될 때 적용됩니다.

다음 예제를 따라 해 보세요. DBA_TABLES, USER_TABLES 자료사전을 검색해 보면 어떤 테이블이 캐시 테이블로 생성되어 있는지를 알 수 있습니다.

SQL>	alter table big_dept cache;
SQL>	select table_name, cache
	from user_tables
	where table_name in ('BIG_EMP' , 'BIG_DEPT');

	TABLE_NAME	CACHE
	BIG_DEPT	Y
	BIG_EMP	Y <-데이터 버퍼캐시 영역에 상주되는 테이블을

SQL>	SELECT * FROM big_dept;
SQL>	SELECT * FROM big_emp;

다음은 캐시 테이블로 생성하지는 않았지만 힌트절을 통해 테이블을 캐싱하는 방법입니다.

SQL> SELECT /*+CACHE*/
deptno, dname, loc
FROM big_dept;

SQL> EXIT

Multiple Block 구조

오라클 데이터베이스에서 테이블에 대해 읽기/쓰기 작업을 수행할 때 한번에 읽고 쓸 수 있는 데이터의 크기를 블록(Block)이라고 합니다.

하나의 블록크기는 오라클 데이터베이스가 설치될 때 init<SID>.ora 파일에 정의되는 DB_ BLOCK_SIZE 파라메터에 의해 결정됩니다. 그리고, 이 값은 더 이상 변경할 수 없습니다.

만약, 대용량의 데이터를 저장하고 있는 테이블에 대해 읽기작업을 실행했을 때 DB_BLOCK_SIZE가 4K(4096 Byte)인 경우보다 8K(8192 Byte)인 경우가 한번에 읽을 수 있는 데이터가 크기 때문에 약 2배 이상 빠르게 검색할 수 있을 것 입니다.

오라클 8i 버전까지는 설치할 때 결정된 한 개 블록의 크기를 더 이상 변경하지 못하여 대용량 데이터를 처리해야 하는 경우 근본적인 구조변경을 하지 못하였습니다.
오라클 9i 버전에서는 이러한 문제점을 해결하여 테이블스페이스를 생성할 때 특별한 블록크기를 지정하지 않으면 설치할 때 정의된 DB_BLOCK_SIZE 파라메터 값으로 결정하고, 대용량의 데이터를 저장하고 검색하는 테이블이 존재하는 테이블스페이스를 생성할 때는 별도로 블록크기를 보다 크게 설정할 수 있습니다. 이러한 기능을 다중 블록(Multiple Block)구조라고 합니다.

다음 내용은 블록의 크기가 작은 경우와 큰 경우에 대한 비교 분석된 내용입니다.

먼저, 다중 블록크기를 설정하기 위해서는 데이터 버퍼 캐시영역에 각 블록크기에 대한 전용 데이터 버퍼 캐시영역을 활성화해야 하고 테이블스페이스 생성시 적절한 블록크기를 정의하게 되면 사용가능 합니다.

init<SID>.ora 파일에 관련 파라메터 들을 설정하십시오.

$ vi init<SID>.ora
DB_BLOCK_SIZE = 2048	기본 블록크기
DB_4K_CACHE-SIZE = 8M	4K 블록크기를 설정한 테이블스페이스를 위한 메모리 공간
DB_8K_CACHE-SIZE = 16M	8K 블록크기를 설정한 테이블스페이스를 위한 메모리 공간
DB_16K_CACHE-SIZE = 32M	16K 블록크기를 설정한 테이블스페이스를 위한 메모리 공간
DB_32K_CACHE-SIZE = 64M	32K 블록크기를 설정한 테이블스페이스를 위한 메모리 공간

<- 이 파라메터들에 의해 SGA 영역에는 2K, 4K, 8K, 16K, 32K 블록크기를 가진 데이터가 읽혀질 때 저장될 전용 메모리 공간이 각각 활성화됩니다.

대용량 데이터를 저장해야 할 데이터가 저장될 테이블스페이스를 생성하십시오.

SQL>	CREATE TABLESPACE INSA
	DATAFILE '$HOME/dbs/insa01.dbf' size 10m blocksize 4k;
SQL>	create table emp_test
	(A number)
SQL>	select * from user_tables
	where table_name = 'EMP_TEXT';

DB 캐시 영역의 시뮬레이션

이 기능은 오라클 데이터베이스의 SGA 영역에 활성화 되어 있는 데이터 버퍼 캐시영역에 대한 시물레이션 기능에 대해서 소개하고자 합니다.

오라클 9i 버전에서는 DB_CACHE_SIZE 파라메터에 의해 데이터 버퍼캐시 영역의 크기가 결정되는데 이 공간의 크기가 너무 작게 설정되어 있으면 데이터를 검색할 때 성능이 저하되고 또한 너무 크게 설정되어 있으면 메모리의 낭비가 초래될 수 있는 단점을 가지고 있습니다.
그렇다면 모든 사용자가 실행하는 SQL문을 보다 빠르게 처리할 수 있을 만큼 적절한 메모리 공간이 확보되어 있는지를 확인할 수 있어야 할 것 입니다.

DB_CACHE_ADVICE 파라메터를 ON으로 활성화하게 되면 현재 데이터 버퍼 캐시영역의 각 블록구조의 활용상태를 시뮬레이션 할 수 있습니다.

[OFF]로 설정되면 데이터 버퍼 캐시영역에 대한 시뮬레이션을 하지 않습니다.
[READY]는 향후 시뮬레이션을 위한 통계정보 만 수집하고 실제적인 시뮬레이션은 하지 않습니다. 이 값에 의해 수집된 통계정보는 공유 풀 영역에 약 700 바이트 만큼의 공간을 사용합니다.
[ON]은 지금 즉시 통계정보 수집 후 데이터 버퍼 캐시영역에 대한 시뮬레이션을 시작합니다.

위 그림에서 V$DB_CACHE_ADVICE 자료사전을 분석하여 데이터 버퍼 캐시영역을 분석하고 있습니다. SIZE_FOR_ESTIMATE 컬럼은 데이터 버퍼캐시 영역을 3M 단위로 시뮬레이션하는 것을 나타내는 것이며 BUFFERS_FOR_ESTIMATE 컬럼은 3M 단위의 버퍼크기에 대한 블록 수를 나타내며 ESTD_PHYSICAL_READ_FACTOR와 ESTD_PHYSICAL_READS 컬럼은 각 버퍼크기에 대한 사용된 회수를 보여주고 있습니다.

만약, 특정 메모리 영역에서 주로 작업이 발생하고 어떤 메모리 영역에서는 작업이 거의 일어나지 않는다면 불필요한 메모리 공간이 낭비되고 있음을 의미하는 것이며, 만약, 모든 메모리 영역에서 빈번한 읽기 작업이 발생한다면 오히려 데이터 버퍼 캐시영역의 크기가 너무 작아 성능저하가 발생하고 있음을 나타냅니다

<잠깐> 다음은 오라클 이전 버전까지 제공되던 데이터 버퍼 캐시 영역의
시뮬레이션 방법입니다.

이 기능을 사용하기 위해서는 DB_BLOCK_LRU_EXTENDED_STATISTICS 파라메터를 설정해야 합니다. 위 그림에서 데이터 버퍼캐시 영역의 블록 개수를 200개로 설정했을 때 각 블록구조의 사용된 회수를 V$CURRENT_BUCKET 자료사전을 통해 분석할 수 있습니다. 이 결과를 통해 현재 데이터 버퍼 캐시영역의 크기가 적절한지를 알 수 있습니다.

DB 캐시 영역의 시뮬레이션

사용자가 실행하는 SQL문에 의해 테이블이 디스크로부터 읽혀져서 메모리에 로더된 다음 서버 프로세스에 의해 다시 읽혀져 사용자에게 리턴되는 과정에서 데이터 버퍼 캐시영역의 히트율이 얼마나 되는지를 분석해 봅시다.

사용자의 요청에 의해 읽혀지는 테이블들이 디스크보다는 메모리로부터 읽혀진다면 히트율은 보다 높아질 것이며, 그렇지 않다면 히트율은 낮아질 것 입니다. 결론적으로 히트율이 높아진다면 보다 성능은 향상될 수 있을 것 입니다.

SQL>	SELECT 1 - (phy.value - lob.value - dir.value) / ses.value "Hit Ratio"
	FROM	v$sysstat ses, v$sysstat lob,
		v$sysstat dir, v$sysstat phy
	WHERE	ses.name = 'session logical reads'
		AND dir.name = 'physical reads direct'
		AND lob.name = 'physical reads direct (lob)'
		AND phy.name = 'physical reads';

Hit Ratio
---------
.849176501
<- 히트율이 90% 이상일 때 좋은 성능이 기대될 수 있습니다.

[physical reads]는 디스크로부터 읽은 블록 수이며

[physical reads direct]는 메모리로부터 읽은 블록 수이고,

[physical reads direct (lob)]는 Large Object Binary를 읽은 블록 수이고,

[session logical reads]는 해당 세션에서 논리적으로 읽은 블록 수입니다.

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-1단계 데이터 파일 (0)	2012.12.19
[오라클]1-5단계 래치와 경합 (0)	2012.12.19
[오라클]1-4단계 자바풀 영역 (0)	2012.12.19
[오라클]1-3단계 로그버퍼 영역 (0)	2012.12.19
[오라클]1-1단계 공유풀 영역 (0)	2012.12.19

Posted by redkite

, |

[오라클]1-1단계 공유풀 영역

01.DB튜닝/006.단계별 서버 튜닝 / 2012. 12. 19. 13:05

1-1 단계 : 공유풀 영역

공유 풀 영역을 보다 크게 할당한다

공유 풀 영역을 보다 크게 할당한다.

INIT<SID>.ORA 파일의 SHARED_POOL_SIZE 파라메터에 의해 공유 풀 영역의 크기는 결정됩니다. 하지만 너무 많은 사용자들이 너무 많은 SQL문을 실행하면 구문 분석된 정보가 모두 라이브러리 캐시영역에 저장됩니다. 새로운 사용자가 데이터베이스에 접속하고 새로운 SQL문을 실행했다면 그 문장은 서버 프로세스에 의해 구문 분석되어 라이브러리 캐시영역에 저장될 것입니다.

그런데, 불행하게도 라이브러리 캐시영역은 이미 빈 공간이 모두 사용되어 풀(FULL)된 상태입니다. 이런 경우가 발생하면 오라클 서버 프로세스는 로더를 하지 않고 잠시 대기(WAITING)한 후 로더되어 있는 SQL문 중에 가장 오래된 SQL문을 찾아내어 그 정보를 라이브러리 캐시영역으로부터 제거하게 됩니다. 이런 과정을 통해 확보된 빈 공간에 사용자의 새로운 SQL문 정보를 저장하게 됩니다. 이러한 방법을 AGING 메커니즘이라고 합니다.

하지만, 이러한 공간관리로 인해 생기는 문제점은 라이브러리 캐시영역에서 빈 공간을 확보하기 위해 가장 오래된 SQL문을 제거한 후 동일한 문장이 다른 사용자에 의해 재 실행될 때 구문분석을 다시 한다는 점입니다. 즉, 다시 재 실행될 SQL문의 구문분석 정보가 불필요하게 메모리로부터 제거되기 때문에 재 파싱이 발생하여 SQL문의 성능저하 현상이 초래될 수 있다는 점입니다. 이러한 문제점은 결국 라이브러리 캐시영역의 크기가 너무 작아서 발생한 문제이므로 공유 풀 영역의 크기를 더 크게 할당해 주어야 만 성능을 향상시킬 수 있습니다.

라이브러리 캐시영역의 튜닝분석

지금까지, 라이브러리 캐시영역에 대한 튜닝개념에 대해서 알아보았습니다. 그렇다면, 데이터베이스의 라이브러리 캐시 영역의 크기가 너무 작아 성능이 저하되고 있는지 또는 너무 크게 설정되어 있어 메모리가 낭비되고 있는지를 데이터베이스 관리자가 분석할 수 있어야 만 튜닝 계획을 수립할 수 있을 것입니다. 다음은 자료사전을 통해 라이브러리 캐시영역을 분석하는 방법입니다.

먼저, V$LIBRARYCACHE 자료사전을 통해 히트율(GETHITRATIO)을 분석할 수 있습니다. 히트율이란 V$LIBRARYCACHE 테이블의 GET 컬럼과 GETHITS 컬럼을 백분율로 계산한 값으로 GET은 사용자가 실행한 SQL문이 구문 분석되어 라이브러리 캐시영역에 로더되려고 했던 수이며 GETHITS는 그 중에 로더 되었던 수를 의미합니다. 즉, 라이브러리 캐시영역이 충분한 공간을 할당하고 있어서 사용자의 모든 구문분석 정보를 로더하기에 충분한가를 백분율로 계산해 본 것입니다. 히트율이 90% 이상일 때 좋은 성능을 기대할 수 있습니다.

다음은 히트율을 확인하는 방법입니다.

SQL>	SELECT namespace, gets, gethits, gethitratio
	FROM	v$libraycache
	WHERE	namespace = 'SQL AREA';

NAMESPACE		GETS	GETHITS	GETHITRATIO
SQL AREA		8950	8411	.936779536

GETHITRATIO 컬럼의 값이 90% 이상이면 라이브러리 캐시영역이 개발자들의 SQL 파싱정보를 저장하기에 충분한 메모리 공간을 확보하고 있음을 의미하며, 만약 90% 이하라면 성능이 저하될 수 도 있다는 것을 의미합니다. 물론, 90% 이하라도 사용자들이 성능에 만족한다면 튜닝대상이 안될 수도 있으며 반드시 튜닝을 해야 할 필요가 없다는 것 입니다.

<잠깐>

이러한 히트율의 분석은 기업에서 가장 바쁘게 일을 진행하고 있는 시간대에 분석결과를 기준으로 설명되는 것입니다. 아무도 일을 하고 있지 않는 야간에 결과를 분석한다면 아무런 의미가 없겠죠 ??

조치사항

히트율이 90% 이하이면 다음과 같이 SHARED_POOL_SIZE 파라메터의 값을 높게 설정해야 합니다.

$ cd $HOME/dbs
$ vi init<SID>.ora
…..
SHARED_POOL_SIZE = 32000000 ß 이전 값보다 더 큰 값으로 변경

:wq!

두 번째로는, V$LIBRARY 자료사전을 통해 RELOAD 비율을 분석할 수 있습니다. RELOADS 비율이란 PINS 컬럼에 대한 RLOADS 컬럼의 백분율을 RELOADS 비율이라고 합니다. 이것은 라이브러리 캐시영역의 크기가 너무 작을 때 사용자의 SQL 구문분석 정보가 로더되지 못하고 가장 오래된 SQL문 정보를 라이브러리 캐시영역에서 삭제한 후 그 문장이 다시 실행될 때 RELOADS 컬럼 값이 증가하게 됩니다. 또는 구문 분석된 SQL문에서 사용된 객체가 다른 사용자에 의해 삭제된 상태에서 다시 SQL문이 재실행되면 발생합니다. PINS는 구문 분석되어 라이브러리 캐시영역에 저장될 수 있었던 SQL 정보를 의미합니다.
PINS 컬럼에 대한 RELOADS 컬럼의 백분율이 1% 미만일 때 좋은 성능을 기대할 수 있습니다.

다음은 PINS에 대한 RELOADS의 비율을 확인하는 방법입니다.

SQL>	SELECT sum(pins), sum(reloads), sum(reloads) / sum(pins)
	FROM	v$libraycache
	WHERE	namespace = 'SQL AREA';

SUM(PINS)		SUM(RELOADS)	SUM(RELOADS)/SUM(PINS)
36299		45	0.001239704

PINS에 대한 RELOADS의 비율이 1% 미만일 때 라이브러리 캐시영역의 크기가 SQL 파싱 정보를 저장하기에 충분하다는 것을 의미합니다. 1% 이상이라면 성능이 저하될 수 도 있다는 것을 의미합니다.

마지막으로, V$LIBRARYCACHE 자료사전을 통해 SQL문에서 사용된 객체가 다른 사용자들에 의해 얼마나 자주 삭제되거나 변경되었는지를 분석하는 방법입니다. 주로, ANALYZE 명령어, ALTER, DROP 명령어에 의해 테이블 구조가 변경되는 경우에 발생합니다

SQL>	SELECT namespace, invalidations
	FROM	v$libraycache
	WHERE	namespace = 'SQL AREA';

NAMESPACE		INVALIDATION
SQL AREA		5

INVALIDATION 컬럼의 값이 높게 출력되거나 계속적으로 증가 값을 보인다면 공유 풀 영역이 작아서 성능이 저하되고 있음을 의미합니다. 즉, 불필요한 재파싱, 재로딩 작업이 발생할 가능성이 높아지는 것 입니다. 이러한 결과에 의해 튜닝이 필요하다면 다음과 같은 파라메터 값을 높게 설정하셔야 합니다.

	SHARED_POOL_SIZE = [크기]
문법	SHARED_POOL_RESERVED_SIZE = [크기]
	SHARED_POOL_RESERVED_MIN_ALLOC = [크기]

[SHARED_POOL_SIZE] 파라메트는 공유 풀 영역의 전체크기를 결정하는 파라메터입니다. 만약, V$LIBRARY 자료사전을 통해 분석된 결과값이 튜닝 기준값에 적합하지 않다면 라이브러리 캐시영역의 크기를 크게 할당해 주어야 하는데 오라클 데이터베이스에서는 이 영역만 크게 할당해 주는 파라메터는 없으며 공유 풀 영역의 크기를 크게 할당해주면 오라클 서버가 적절한 크기로 라이브러리 영역의 크기를 할당해 줍니다.

[SHARED_POOL_RESERVED_SIZE] 파라메트는 PL/SQL 블록으로 실행된 SQL문의 구문분석 정보를 저장할 때 사용되는 공유 풀 영역입니다. 일반적인 SQL문과는 달리 PL/SQL 블록들은 문장의 크기가 매우 크기 때문에 구문 분석된 결과 또한 매우 크기 마련입니다. 이러한 결과를 라이브러리 캐시영역에 저장하다 보면 연속된 메모리 공간이 없는 경우(단편화-FRAGMENTATION 현상) 분석결과가 여러 개의 조각으로 쪼개어져 저장됨으로서 단편화 현상을 더 유발시킬 수 있습니다.

결국, 이러한 단편화 현상은 데이터 검색 시 성능을 저하시키기 때문에 미리 아주 큰 PL/SQL 믈록의 구문분석 정보를 저장할 수 있는 충분한 메모리 공간을 확보한다면 이런 문제를 해결할 수 있을 것입니다. 이때 사용하는 파라메터이며 기본값은 SHARED_POOL_SIZE의 10%니며 최대값은 SHARED_POOL _SIZE 값의 1/2 입니다. 만약, 사용자의 개발환경이 PL/SQL 블록으로 개발된 애플리케이션 프로그램이 많다면 이 파라메터 값을 충분하게 설정해 주는 것이 좋습니다.

이 파라메터는 V$SHARED_POOL_RESERVED 자료사전의 REQUEST_FAILURES 컬럼의 값이 0의값이 아니거나 계속 증가값을 보일 때 높은 값을 설정해 주어야 좋은 성능을 기대할 수 있습니다. 반대로, REQUEST_FAILURES 컬럼의 값이 계속 0이면 메모리의 낭비가 초래될 수 있으므로 파라메터의 값을 낮게 설정해 주어야 합니다.

[SHARED_POOL_RECERVED_MIN_ALLOC] 파라메터는 PL/SQL 블록의 크기가 이 파라메터에 지정된 크기 이상되어야 RESERVED 영역에 저장될 수 있습니다.

<잠깐>공유 풀 영역의 단편화(Fragmentation) 현상을 제거하는 방법

시스템을 사용하다 보면 메모리 영역과 디스크 영역에서 많은 읽기/쓰기 작업이 발생합니다. 제한된 공간을 계속적으로 사용하다 보면 공간에 대한 단편화(FRAGMENTATION) 현상이 발생할 수 밖에 없습니다. 문제는 단편화 현상으로 인해 효율적인 공간관리가 되지 않고 성능까지 저하되는 현상이 유발되는 것 입니다. 오라클 데이터베이스의 SGA 영역 또한 단편화 현상으로 인해 메모리 공간이 낭비되거나 SQL문의 성능저하를 유발시킬 수 도 있습니다. 문제를 해결할 수 있는 가장 효과적인 방법은 가끔 데이터베이스를 재 시작하는 방법이지만 실제 기업환경에서는 거의 불가능한 일 입니다. 다음과 같이 ALTER SYSTEM 명령어를 사용하면 데이터베이스의 재 시작없이 공유 풀 영역을 클리어(Clear) 시킬 수 있습니다.

SQL>

ALTER

SYSTEM

SET

FLUSH

SHARED_POOL;

데이터 딕셔너리 캐시 영역의 튜닝 분석

오라클 데이터베이스를 설치하면 기본적으로 생성되는 테이블, 뷰, 패키지, 프로시저, 함수 등을 데이터 딕션어리(DATAT DICTIONARY)라고 합니다. 그리고, 데이터베이스를 시작(STA RTUP)하거나, SQL문을 실행하면 일부 데이터 딕션어리(V$~로 시작되는 뷰) 정보가 오라클 서버에 의해 공유 풀 영역의 데이터 딕션너리 캐시영역에 로드 됩니다.

또한, 개발자 또는 데이터베이스 관리자가 튜닝 및 관리의 목적으로 자료사전을 참조하기도 합니다. 이러한 자료사전이 읽혀질 때마다 공유 풀 영역의 데이터 딕션너리 영역에 관련정보를 로더하게 됩니다. V$~로 시작되는 일부 자료사전은 데이터베이스의 마운트 단계에서 만 질의할 수 있지만 대부분의 자료사전(DBA_, USER_, X$~)들은 데이터베이스를 오픈 단계까지 시작해야 만 질의할 수 있습니다.

그럼, 데이터 딕션너리 영역에 대한 튜닝방법에 대해서 알아보겠습니다. 사용자가 참조하는 자료사전이 질의될 때마다 그 정보를 데이터 딕션너리 영역에서 참조할 수 있다면 자료사전을 보다 빠르게 조회할 수 있을 것이고 그렇지 못하다면 자료사전을 데이터 파일로부터 읽어서 데이터 딕션너리 영역으로 로더해야 할 것입니다. 결론적으로, 디스크로부터 자료사전이 읽혀지는 것보다는 데이터 딕션너리 영역으로부터 읽혀지는 것이 성능에 유리하다는 의미입니다

다음은 자료사전을 통해 라이브러리 영역을 분석하는 방법입니다. V$ROWCACHE 자료사전을 참조하면 이 영역에 대한 튜닝 결과를 확인할 수 있습니다.

SQL>	SELECT sum(gets), sum(getmisses), sum(gets)/sum(getmisses)
	FROM	v$rowcache;

SUM(GETS)		SUM(GETMISSES)	SUM(GETS)/SUM(GETMISSES)
2126		902	2.3569

[GETS] 컬럼은 사용자가 자료사전을 질의했을 때 데이터 딕션너리 영역으로 자료를 요청했던 수를 의미합니다.
[GETMISSES] 컬럼은 자료요청을 했지만 데이터 딕션너리 영역으로부터 자료를 얻지 못했던 수를 의미합니다.

대부분의 자료사전을 빠르게 검색하기 위해서는 [GETS] 컬럼에 대한 [GETMISSES] 컬럼의 백분율이 2% 미만이어야 하고 아주 큰 자료사전 테이블에 대한 검색을 빠르게 하기 위해서는 15% 미만이어야 이 영역에 대한 성능을 기대할 수 있습니다.

만약, 이러한 조건을 만족하지 못한다면 공유 풀 영역의 크기를 더 크게 설정해야 합니다.

라이브러리 영역과 같이 이 영역 만 크게 할당해 주는 파라메터는 없으며 공유 풀 영역의 크기를 크게 할당해주면 오라클 서버가 적절한 크기로 데이터 딕션너리 영역의 크기를 할당해 줍니다.

<조치사항>

$ cd $HOME/dbs
$ vi init<SID>.ora
…..
SHARED_POOL_SIZE = 32000000 ß 이전 값보다 더 큰 값으로 변경

:wq!

동일한 SQL문 작성을 위한 표준화 작업을 한다.

V$LIBRARY 자료사전을 통해 개발자가 실행한 SQL문이 LIBRARY CACHE 공간에서 어떻게 저장되고 관리되는지를 자세히 알아 보았습니다. 그렇다면, 개발자가 실행하는 SQL문의 성능을 극대화 시키고 메모리 공간도 효과적으로 사용할 수 있는 방법은 없을까요 ??

첫 번째 방법이 공유 풀 영역의 크기를 충분히 할당 해 주는 것이라면 두 번째 방법은 모든 SQL문이 동일하게 작성되어 실행되게 함으로서 불필요한 PARSING과 불필요한 RE-LOADING을 피하게 하는 것입니다.

자~ 그렇다면 동일한 SQL문을 실행할 수 있는 가장 최선의 방법은 무엇일까요 ?

프로젝트를 하다 보면 다양한 개성과 창조적인 감성을 가진 인간이 만들어 내는 SQL문을 동일하게 만든다는 것은 말처럼 그리 쉬운 일은 아닐 것 입니다.

가장 최선의 방법은 모든 개발자들이 정해진 규칙에 의해 SQL문을 작성할 수 있도록 표준화를 하는 것 입니다. 일반적으로 정보 시스템을 개발하기 전에 애플리케이션 프로그래밍에 대한 "코딩 표준화", 사용자와의 인터페이스를 효과적으로 하기 위한 "화면 디자인 표준화" 등이 같은 맥락에서 이해할 수 있는 내용입니다.

우리가 이러한 표준화를 하는 궁극적인 목적은 크게 2가지로 요약할 수 있습니다.

첫 번째 목적은 약속된 프로그래밍 규칙을 통해 향후 유지 보수적인 측면을 고려한 것 입니다. 만약, 아무런 규칙없이 프로그래밍을 한다면 너무나도 다양한 유형의 프로그램들이 생성되어 자신이 개발하지 않은 프로그램 소스는 제대로 이해하지 못하는 문제로 인해 유지보수에 어려움을 겪게될 것 입니다.

두 번째 문제는 성능 문제입니다. 다양한 소스코드를 만들게 되면 프로그래밍이 어려워질 뿐 아니라 불필요한 메모리와 디스크 공간의 낭비를 초래할 수 있기 때문입니다.

다음 예제는 시스템 개발초기에 프로젝트 관리자 또는 데이터베이스 분석/설계자에 의해 동일한 SQL문을 만들어 내기 위한 표준화 작업의 일부입니다. 이러한 표준화작업은 사용자의 개발환경에 맞게 적절하게 변형하셔야 하며 새로운 내용의 추가 및 삭제가 추가적으로 필요합니다. 그럼, 지금부터 저를 따라 오세요.

SQL문의 대문자 또는 소문자로 통일하십시오.

사용자의 애플리케이션 프로그램 속에는 수많은 SQL문이 저장되어 있습니다. 시스템 개발은 여러 명이 한 팀이 되어 진행되는데 프로그래머들은 각각 개성과 사고를 가진 인격체이기 때문에 어떤 약속을 해두지 않는다면 어떤 사람은 대문자로, 어떤 사람은 소문자로 SQL문을 작성할 것이고 결국 동일한 SQL문을 만들어 내지 못합니다.

SQL>	SELECT * FROM emp;
SQL>	UPDATE emp SET sal = sal 8 1.1 WHERE deptno = 10;

변수명은 SQL문, 객체명, 변수명과 구분하기 위해 소문자로 작성하십시오.

변수명은 SQL문, 객체명, 컬럼명과 구분하기 위해 소문자로 작성하십시오. SQL문의 빠른 이해와 컬럼 간의 구분을 쉽게 하기 위해 필요합니다.

SQL>	SELECT RESNO, HNAME INTO :ls_ResNo, :ls_HName
	FROM EMPTBL
	WHERE HNAME LIKE '홍길동';

다른 스키마의 테이블을 호출할 때는 SCHEMA명.테이블명 으로 작성하십시오.

다른 스키마의 테이블을 호출할 때는 "Schema명.테이블명"으로 작성하십시오. 만약, 시노늄을 작성하여 사용할 때에는 시노늄 명을 결정하는 네이밍 룰(NAMING RULE)을 잘 작성하여 이름 만 봐도 이해할 수 있도록 해 주십시오.

SQL>	SELECT RESNO, HNAME
	FROM SCOTT.EMP
	WHERE RESNO = 1234;

SQL문의 각 단어의 여백은 한 칸으로 하십시오.

SQL문의 각 단어와 단어의 여백은 1칸으로 작성하십시오. 콤마(comma)는 앞 문자와는 간격을 두지 말고 뒷 문자와는 간격을 1칸으로 작성하십시오. 또한, 컬럼 리스트는 되도록 모두 한 줄에 기술하십시오. (2줄 이상일 경우는 첫줄, 첫 컬럼에 맞추어 작성하십시오.)

SQL>	SELECT RESNO, HNAME INTO :ls_ResNo, :ls_HName
	FROM EMPTBL
	WHERE HNAME LIKE '홍길동';

SQL문 내의 변수명은 변수 선언 기준 안에 따르며 해당 컬럼명을 접두어와 경합하여 사용하십시오.

SQL문 내의 변수 명은 변수선언 기준 안에 따르며 해당 컬럼명을 접두어와 결합하여 사용하십시오. 또한, WHERE절의 비교 처리에는 변수를 사용하십시오.

string gi_resno		// 사원번호(G는 글로벌 변수, L은 로컬변수,
		// i는 정수타입, s는 문자타입)
sql>	SELECT RESNO, HNAME
	FROM EMPTBL
	WHERE RESNO = :ii_resno;

SQL문의 SELECT, FROM, WHERE절은 각 라인의 선두에 사용하십시오.

SQL>	SELECT RESNO, HNAME
	FROM EMPTBL
	WHERE RESNO = :ii_resno;

자주 사용하는 SQL문을 캐싱한다.

라이브러리 캐시영역을 통해 성능을 향상시킬 수 있는 세 번째 방법은 자주 실행되는 SQL문의 구문 분석정보를 라이브러리 영역에 캐싱 해 두었다가 문장이 재실행될 때 마다 재사용하게 하는 방법입니다. 불필요한 구문 분석단계를 피할 수 있기 때문에 SQL문의 성능을 향상을 시킬 수 있습니다.

다음 내용은 라이브러리 캐시영역의 성능과 관련된 파라메터들 입니다.

CURSOR_SPACE_FOR_TIME = FALSE

만약, 2명의 사용자에 의해 동일한 SQL문이 실행된다면 공유 풀 영역에 있는 구문 분석된 SQL TEXT 정보를 공유하게 될 것입니다. 그러나, PRIVATE SQL AREA 라는 영역에는 2명의 사용자가 실행한 구문 분석된 정보가 각각 저장되게 됩니다. 이 파라메터 값을 TRUE로 설정하면 PRIVATE SQL AREA에 공유 풀 영역의 구문 분석정보를 참조하는 커서가 하나라도 있으면 AGING 알고리즘에 의해 공유 풀 영역으로부터 제거되지 않고 계속 상주할 수 있도록 해 줍니다. 이 파라메터의 기본값은 FALSE 입니다. 만약, 개발자들이 실행하는 SQL문에서 같은 문장이 반복적으로 실행되는 경우가 많다면 이 파라메터를 TRUE 설정하는 것이 성능향상에 유리합니다.

SESSION_CACHED_CURSORS = 0

오라클사에서 제공하는 FORMS 개발 툴 또는 C, COBOL, FORTRAN과 같은 3GL 언어로 시스템을 개발하는 경우, 하나의 조회화면에서 또 다른 조회화면으로 전환하는 프로그램을 구현하다 보면 메인 화면에서 다음 화면으로 이동할 때 메인 프로그램에서 실행되었던 SQL문의 구문 분석정보가 라이브러리 캐시영역으로부터 완전히 제거될 수가 있습니다. 서버 프로그램을 조회한 후 다시 메인 프로그램으로 돌아갔을 때 이미 실행했던 SQL문을 다시 구문 분석하게 된다면 불필요한 작업으로 인해 SQL문의 성능은 저하될 수 있을 것 입니다. 이 파라메터는 하나의 세션에서 캐싱할 수 있는 커서의 수를 지정할 수 있습니다. 이 파라메터의 기본값은 0 입니다

CLOSE_CACHED_OPEN_CURSORS = FALSE

개발자가 실행한 SQL문의 트랜잭션이 COMMIT문을 만나는 순간 관련된 구문 분석정보도 라이브러리 캐시영역으로부터 함께 제거하는 파라메터입니다. 이 파라메터의 기본값은 FALSE 입니다

OPEN_CURSORS = 300

하나의 세션에서 동시에 오픈할 수 있는 커서의 수를 지정할 때 사용합니다. 커서를 너무 많이 오픈하게 되면 그 만큼 많은 커서 정보가 라이브러리 캐시영역에 로더되어야 하기 때문에 성능에 도움이 되지 않습니다. 이 파라메터의 기본값은 오라클 데이터베이스 버전에 따라 틀리며 9i인 경우 300 입니다.

다음은 INIT<SID>.ORA 파일에 파라메터들을 등록하는 방법입니다.

$ cd $HOME/dbs

$ vi init<SID>.ora

…..

CURSOR_SPACE_FOR_TIME = TRUE

SESSION_CACHED_CURSORS = 10

CLOSE_CACHED_OPEN_CURSORS = TRUE

OPEN_CURSORS = 500

:wq!

자주 사용하는 PL/SQL/ 블록을 캐싱한다.

프로젝트를 수행하다 보면 아주 복잡한 스토어드 프로시저, 함수, 패키지, 트리거 등을 생성하게 됩니다. 하지만, 이런 PL/SQL 블록들은 너무 커서 실행할 때 마다 라이브러리 캐시영역에 로더 되었다가 다시 제거되는 현상이 반복적으로 발생할 수 밖에 없습니다. 이런 문제가 자주 발생하면 라이브러리 캐시영역에는 단편화 현상이 발생하게 되고 데이터베이스를 사용하는 전체 사용자들의 성능이 저하되게 됩니다.

오라클 사에서는 자주 실행되는 PL/SQL 블록, 시퀀스 등을 라이브러리 영역에 캐싱해 둘 수 있도록 DBMS_SHARED_POOL 패키지를 제공합니다. 다음 예제를 따라 해 보십시오.

먼저, 라이브러리 영역에 캐싱되어 있는 PL/SQL 블록의 정보를 참조하는 자료사전은 V$DB_OBJECT_CACHE 입니다.
v$db_object_cache 자료사전을 조회한 후, 그 중 하나를 공유 풀 영역에 상주 시켜봅시다.

SQL>	> select name, type, kept
	from v$db_object_cache
	where type in ( 'PACKAGE','PROCEDURE', 'TRIGGER', 'PACKAGEBODY');
	NAME	TYPE	KEPT
	-----------------------------
<- 캐싱된 PL/SQL 블록 정보가 없습니다.

일단, 애플리케이션에서 자주 실행되는 스토어드 프로시저를 생성한 후 공유 풀 영역에 로더 시켜 보겠습니다. 라이브러리 캐시영역에 로더시킬 때는 DBMS_SHARED_KEEP 패키지를 사용하고 제거할 때는 DBMS_SHARED_UNKEEP 패키지를 사용합니다.

SQL>	CONNECT scott/tiger
SQL>	CREATE OR REPLACE PROCEDURE check_sawon
	(v_emp_on)		IN	emp.empno%TYPE
	IS
	BEGIN
		DELETE FROM emp WHERE empno = v_emp_no;
	END check_sawon;
	/
SQL>	execute DBMS_SHARED_POOL.KEEP('SCOTT.CHECK_SAWON');
	<- CHECK_SAWON 프로시저를 공유 풀 영역에 상주합니다.

SQL>	select type, name
	from v$db_object_cache
	where type in ('PACKAGE','PROCEDURE','TRIGGER', 'PACKAGE BODY')
		END check_sawon;

NAME			TYPE	KEPT
-----------------------------------------------------
CHECK_SAWON			PROCEDURE	Y
	<-KEPT = Y의 의미는 CHECK_SAWON 프로시저가 캐싱되어 있음을 의미합니다

SQL>	exit

STATS.SQL 스크립트를 실행하면 위와 같이 ANALYZE 명령어에 의해 분석된 테이블의 구조를 확인할 수 있습니다. 앞에서도 언급했듯이 튜닝을 잘 하기 위해서는 데이터베이스의 구조를 잘 알아야 합니다.

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-1단계 데이터 파일 (0)	2012.12.19
[오라클]1-5단계 래치와 경합 (0)	2012.12.19
[오라클]1-4단계 자바풀 영역 (0)	2012.12.19
[오라클]1-3단계 로그버퍼 영역 (0)	2012.12.19
[오라클]1-2단계 버퍼캐쉬 영역 (0)	2012.12.19

Posted by redkite

, |

«이전 1 다음»

All Days 무한도전

카테고리

달력

공지사항

태그목록

최근에 올라온 글

'01.DB튜닝/006.단계별 서버 튜닝'에 해당되는 글 11건

[오라클]2-2단계 Redo 로그 파일

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]3-3단계 Lock의 경합

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]3-2단계 Temp 세그먼트

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

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

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-3단계 아카이브 파일

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]2-1단계 데이터 파일

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]1-5단계 래치와 경합

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]1-4단계 자바풀 영역

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]1-3단계 로그버퍼 영역

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]1-2단계 버퍼캐쉬 영역

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

[오라클]1-1단계 공유풀 영역

'01.DB튜닝 > 006.단계별 서버 튜닝' 카테고리의 다른 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

링크

티스토리툴바