[관리][튜닝] 오라클 인스턴스 튜닝 ( instence tunning )
### 오라클 인스턴스 튜닝 ###
인스턴스 튜닝의 최적의 결과를 얻기 위해 데이터 베이스 버퍼캐쉬,
로그버퍼와 같은 메모리 영역을 적당한 크기로 할당하는 작업을 의
미 합니다.
먼저 시스템 공유영역의 크기를 확인해 보자
SVRMGR> show sga
—————————————————————
Total System Global Area (시스템 전체 공유영역) 71998864 bytes
Fixed Size (오라클 서버 사용 영역 ) 64912 bytes
Variable Size (공유 풀 영역) 54984704 bytes
Database Buffers (데이타 버퍼 캐쉬 영역 ) 16777216 bytes
Redo Buffers (로그 버퍼 캐쉬 영역 ) 172032 bytes
1. 데이타 버퍼 캐쉬 영역
데이터 버퍼 캐쉬영역은 실행한 SQL문에 의해 데이터 파일의 데이터를
읽어서 저장하는 메모리 영역으로 저장비율의 70% 정도로 설정해야 최
적의 상태입니다. 70% 보다 낮다면 메모리의 낭비를 초래 하는 결과고
70% 보다 높다면 실행속도가 늦어진다.
데이타 버퍼 캐쉬 영역 점검
SVRMGR> select name,value
2> from v$sysstat
3> where name in (‘consistent gets’,’db block gets’,’physical reads’);
NAME VALUE
—————————————————————- ———-
db block gets 468
consistent gets 29309
physical reads 2460
3 rows selected.
select 문 결과에 의한 데이터 베이스 버퍼 캐쉬 영역의 사용비율
hit ratio = 1 – (physical reads / (db block gets + consistent get))
만약 결과가 70%보다 낮다면 할당된 블록수를 줄여야한다. 70% 보다 높다면 블럭수를 늘려준다.
[oracle@zzang911 oracle]$ cd $ORACLE_HOME/dbs
[oracle@zzang911 dbs]$ vi initORCL.ora
————————————————————————-
..
..
db_block_buffers = 8192 실제크기는 8192 X 2048 = 16777216 byte
..
2. 공유 풀 영역
공유풀영역은 사용자가 실행한 SQL문이 저장되는 영역(Library-Cache 영역)과 데이터 딕션어리 캐쉬 영역 이 있습니다. 이는 데이터베이스가 메모리에서 작 업하는거와 디스크에서 작업하는 비율로 정해진다.
(물런 메모리에서 작업하는 것이 데이터베이스 속도에 좋겠죠.)
## 라이브러리 캐쉬 영역 튜닝
SVRMGR> select sum(pins) pins, sum(reloads) reloads
2> from v$librarycache;
PINS RELOADS
———- ———-
1069 5
## 계산법
ratio = ( reloads / pins) * 100
만약 ratio 값이 1 보다 크다면 공유 풀 영역의 크기를 늘려주어야 한다.
## 데이터 딕션어리 캐쉬영역 튜닝
SVRMGR> select sum(gets) gets, sum(getmisses) getmisses
2> from v$rowcache;
GETS GETMISSES
———- ———-
833 70
## 계산법
ratio = (getmisses /gets) * 100
비율이 10% 보다 더 큰값이면 shared_pool_size 값을 늘려줘야한다.
3. 라지 풀 영역
MTS(multi-thread server) 환경을 설정하여 데이터 베이스에 접속하는 경우 UGA(user global area,접속된 사용자들의 정보를 저장하는 영역) 영역의 정보가 저장되는 영역입니다. 또한 데이터 베이스 백그라운드 프로세스(DBWR,LGWR,ARCH)들의 입출력 작업 시 사용되는 영역입니다.
또한 복구 매니저(recovery manager)를 사용할때 백업고 복구 작업을 원할히 하기 위해 사용하는 영역 입니다.
large_pool_size는 정의하지 않으면 기본값은 0 이고 설정치는 300k 부터 2G 까지 설정가능함..
변경은 init<DB명>.ora 에서 변경함..
..
large_pool_size=300
..
4. 로그 버퍼 영역
insert,update,delete 에 관련된 SQL 문을 실행했을때 적용된 데이터 값들은 모두 리두로그파일에 백업이 되어진다. 급작스런 시스템 다운 시 리두로그 파일에 백업된 데이터로 복구작업을 하기 위해서 이다.
SVRMGR> select name,value
2> from v$sysstat
3> where name = ‘redo log space requests’;
NAME VALUE
—————————————————————- ———-
redo log space requests 0
이때 value 값이 0 에 가까울수록 로그버퍼는 충분히 설정 되었다고 보면 된다.
변경은 init<DB명>.ora 파일에서 한다.
..
log_buffer = 163840
..
5. 롤백 세그먼트의 경합
insert,update,delete 문이 실행되었을때 항상 롤백 세그먼트에 변경전의 데이터를 저장합니다.
SVRMGR> select r.name, s.gets, s.waits
2> from v$rollstat s, v$rollname r
3> where s.usn = r.usn;
NAME GETS WAITS
—————————— ———- ———-
SYSTEM 151 0
R05 21 0
R01 21 0
R02 21 0
R03 21 0
R04 21 0
R06 21 0
R07 21 0
R08 21 0
R09 21 0
R10 21 0
## 계산법
ratio = ( waits / gets ) * 100
결과가 2 보다 큰 값이면 롤백 세그먼트에 경합이 발생하고 있다고 판단하면 됩니다.
롤백 세그먼트의 수는 ” 동시사용자/4 ” 로 정하면 된다.
주의점 : 인스턴스 튜닝시 init<DB명>.ora 파일에서 파라메타를 수 없이 변경할것입니다. 변경전의 값과 변경후의 값. 그리고 누가 변 경했는지 언제 변경했는지에 대해 항상 별도의 체크를 해두어야 한 다..그리고 ora 파일을 문서화하는 것이 파라메타 값에 대한 효율성 분석에 큰 도움이 될것이다.
### 오라클 데이터 베이스 튜닝 ###
1. 디스크 경합
물리적인 경합장치(DISK,MEMORY)에서 일어나는 경합이 데이터 베이스 튜닝에서 가장 일반적인 형태라고 볼수 있다.
SVRMGR> select d.name, f.phyrds, f.phywrts
2> from v$datafile d, v$filestat f
3> where d.file# = f.file#;
NAME PHYRDS PHYWRTS
——————————————————————————– ———- ———-
/home/oracle/oradata/auction/system01.dbf 1253 9
/home/oracle/oradata/auction/oemrep01.dbf 4 2
/home/oracle/oradata/auction/rbs01.dbf 14 2
/home/oracle/oradata/auction/temp01.dbf 4 2
/home/oracle/oradata/auction/users01.dbf 4 2
/home/oracle/oradata/auction/indx01.dbf 4 2
/home/oracle/oradata/auction/drsys01.dbf 4 2
/home/oracle/oradata/auction/data01.dbf 4 2
만약 system01.dbf 와 users01.dbf 파일이 같은 디스크에 있고 읽 기 쓰기 작업이 이 두데이타파일에 집중적으로 일어날경우 디스크 경합이 생기게 된다.
이때 하나의 파일을 다른 디스크로 옭겨 주는 것이 경합을 피하는
길이다.
SVRMGR> select file_name, tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
——————————————————————————– ——————————
/home/oracle/oradata/auction/system01.dbf SYSTEM
/home/oracle/oradata/auction/oemrep01.dbf OEM_REPOSITORY
/home/oracle/oradata/auction/rbs01.dbf RBS
/home/oracle/oradata/auction/temp01.dbf TEMP
/home/oracle/oradata/auction/users01.dbf USERS
/home/oracle/oradata/auction/indx01.dbf INDX
/home/oracle/oradata/auction/drsys01.dbf DRSYS
/home/oracle/oradata/auction/data01.dbf DATA01
SVRMGR> alter tablespace data01 offline;
Statement processed.
SVRMGR> alter tablespace data01
2> rename datafile ‘/home/oracle/oradata/auction/data01.dbf’ to
3> ‘/home/oracle/oradata/alang/data01.dbf’;
Statement processed.
SVRMGR> alter tablespace data01 online;
Statement processed.
정상적으로 자료입력이 완료되면 이전 데이타 파일은 삭제.
![[관리] listener.ora 설정 예제](http://nblog.syszone.co.kr/wp-content/themes/hueman/assets/front/img/thumb-medium-empty.png)
