서버 튜닝
server tuning은 최적의 성능을 얻기 위해 DB 버퍼 캐시, 로그 버퍼와 같은 메모리 영역과 기타 프로세스 영역의 크기를 적당하게 할당하여 성능을 향상시키는 방법을 말한다.
서버튜닝을 통하여 성능을 향상시킬 수 있는 내용은 다음과 같다.
• DB 메모리 영역의 튜닝을 통한 성능 향상
• 물리적 디스크로부터 입출력 수행에 따른 성능 저하 개선
• 백그라운드 프로세스의 활성하를 통한 DB 성능 개선
1)공유 풀 영역튜닝
2)데이터버퍼 캐시 영역튜닝
3)리두로그 버퍼 튜닝
공유 풀 영역튜닝
공유 풀(shared pool)은 사용자가 실행한 SQL 문의 구문 분석 정보가 저장되는 영역으로 라이브러리 캐시 영역과 데이터 사전 캐시로 구성되어 있다.
라이브러리 캐시는 오라클 DB가 메모리에서 데이터를 얼마나 자주 변경했는지, 어떤 SQL 문이 자주 실행되는지 등의 실행 결과와, 사용자가 실행한 SQL 문의 텍스트 정보가 저장되어 있다.
데이터 사전 캐시 영역은 테이블/뷰의 이름, 테이블을 구성하는 컬럼의 이름과 타입, 이들에 대한 사용자 권한 등과 같은 정보를 저장하는 영역이다.
라이브러리 캐시 튜닝
라이브러리 캐시 튜닝의 목적은 동일한 SQL이나 PL/SQL은 라이브러리 캐시에 한번만 저장될 수 있도록 하자는 것이다. 즉, 동일한 SQL이나 PL/SQL의 텍스트, 파싱(parsing) 결과, 실행계획 등이 각각 메모리에 저장되도록 하는 것이 아니라 하나의 SQL이나 PL/SQL의 정보들만 메모리에 저장되도록 하고 나머지는 이미 메모리에 저장된 정보를 이용할 수 있도록 하자는 것이다.
이를 위해서는 표준화된 코딩 규칙에 따라 SQL과 PL/SQL을 개발함으로써 모든 사용자가 공유할 수 있도록 하고, 이미 라이브러리 캐시에 저장되어 있는 정보가 제거 되지 않도록 하면 된다.
동일한 SQL 문이란 다음과 같은 조건을 만족하는 두 개의 SQL 문을 의미한다.
• 대문자/소문자 동일 • 띄어쓰기 규칙 동일 • 공백(space)의 개수 동일 • SQL 문에 사용된 변수의 데이터 타입과 변수 이름이 동일공유 푸 영역을 검색할 때는 V$SQLAREA, V$SQLTEXT 데이터 사전을 사용한다.
검색 결과에서 PARSE_CALLS 항목을 유의해볼 필요가 있다.
동일한 SQL 문에 대해서는 구문 분석이 이루어 지지 않음을 알 수 있다.
SQL> conn system/manager
connected
SQL> select ename,sal from scott.emp where sal >=3000;
ENAME SAL
---------- ----------
SCOTT 3000
KING 5000
FORD 3000
SQL> select ename,sal from scott.emp where sal >=3000;
ENAME SAL
---------- ----------
SCOTT 3000
KING 5000
FORD 3000
SQL> select sql_text,version_count,loads,invalidations,parse_calls,sorts
2 from v$sqlarea
3 where sql_text not like '%$%'
4 and command_type in (2,3,6,7)
5 and upper(sql_text) like '%SCOTT.EMP%'
6 order by sql_text;
SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS
------------- ---------- ------------- ----------- ----------
select ename,sal from scott.emp where sal>=3000
1 1 0 2 0
SQL> select sql_text,version_count,loads,invalidations,parse_calls,sorts
2 from v$sqlarea
3 where sql_text not like '%$%'
4 and command_type in (2,3,6,7)
5 and upper(sql_text) like '%SCOTT.EMP%'
6 order by sql_text;
SQL_TEXT
--------------------------------------------------------------------------------
VERSION_COUNT LOADS INVALIDATIONS PARSE_CALLS SORTS
------------- ---------- ------------- ----------- ----------
select ename,sal from scott.emp where sal>=3000
1 1 0 3 0
SQL>
사용하고 있는 DB의 라이브러리 캐시 영역에 대한 분석은 라이브러리 캐시에 대한 튜닝의 계힉을 세우기 위해 아주 중요하다. 공유 풀 영역에 대한 분석을 위한 데이터 사전 뷰는 다음과 같다.| • V$sgastat | 모든 SQL 구조의 크기에 대한 정보를 제공 |
| • V$librarycache | 라이브러리 캐시에 있는 항목의 유형에 대한 통계정보들을 제공 |
| • V$sql | |
| • V$sqlarea | 현재 어떤 SQL 문들이 사용자들에게 공유되고 있는지에 대한 정보 |
| • V$sqltext | 현재 어떤 SQL 문들이 사용자들에게 공유되고 있는지에 대한 정보 |
| • V$db_object_cache | 패키지를 포함한 캐시된 객체들 |
V$librarycache 뷰를 사용한 분석
| 히트율 분석 | v$librarycache 데이터 사전의 GETS 컬럼과 GETTHIS 컬럼에 대한 백분율 GETS는 사용자가 실행한 SQL 구문이 분석되어 라이브러리 캐시 영역에 로더 되려고 했던 횟수 GETTHIS는 그 중에서 실제로 load된 수를 의미함 |
| reload비율 | v$librarycache 데이터 사전으로부터 라이브러리 캐시의 reload 비율을 분석한다. reload비율은 PINS 컬럼에 대한 RELOADS 컬럼에 대한 비율임 PINS 컬럼은 라이브러리 캐시의 정보가 사용된 횟수를 의미하고, RELOADS 컬럼은 라이브러리 캐시의 정보가 메모리로부터 제거디어 다시 로딩된 횟수를 의미한다. 만약 0.01이하라면 SHARED_POOL_SIZE 값을 늘려야 한다. |
| 무효화 | invalidation(무효화)는 v$librarycache 데이터 사전으로부터 SQL문에서 참조된 객체가 다른 사용자에의해 삭제된 상태가 얼마나 발생했는지를 나타내는 것으로 INVALIDATION 컬럼의 값이 계속 증가하는 값이면 공유 풀 영역이 작아 성능이 저하됨을 나타낸다. |
SQL> select * from v$sgastat;
POOL NAME BYTES
------------ -------------------------- ----------
fixed_sga 778796
buffer_cache 184549376
log_buffer 524288
shared pool subheap 52652
shared pool KQR L SO 213068
shared pool KQR M PO 3021240
shared pool KQR M SO 483516
shared pool KQR S PO 669044
shared pool KQR S SO 12628
shared pool sql area 13678560
shared pool KGLS heap 2622804
POOL NAME BYTES
------------ -------------------------- ----------
shared pool joxs heap 4220
shared pool row cache 3707272
shared pool PX subheap 131068
shared pool parameters 27604
shared pool repository 145944
shared pool ASH buffers 4194304
shared pool free memory 10909496
shared pool PL/SQL DIANA 1225720
shared pool PL/SQL MPCODE 2761632
shared pool library cache 11884516
shared pool miscellaneous 23639736
POOL NAME BYTES
------------ -------------------------- ----------
shared pool pl/sql source 18244
shared pool PLS non-lib hp 10404
shared pool XDB Schema Cac 3797208
shared pool alert threshol 4220
shared pool joxlod exec hp 337180
shared pool joxlod pcod hp 56004
shared pool partitioning d 347548
shared pool table definiti 1988
shared pool trigger defini 2668
shared pool trigger inform 2008
shared pool trigger source 5832
POOL NAME BYTES
------------ -------------------------- ----------
shared pool type object de 251344
shared pool event statistics per sess 3860360
shared pool fixed allocation callback 352
large pool PX msg pool 902160
large pool free memory 3292144
java pool joxs heap 354816
java pool free memory 2578496
java pool joxlod exec hp 5455296
41 rows selected.
SQL> select namespace,gethitratio from v$librarycache;
NAMESPACE GETHITRATIO
--------------- -----------
SQL AREA .980316179
TABLE/PROCEDURE .817467385
BODY .997121957
TRIGGER .984754522
INDEX .112746633
CLUSTER .987701768
OBJECT 1
PIPE 1
JAVA SOURCE 1
JAVA RESOURCE 1
JAVA DATA 0
11 rows selected.
SQL> select sum(pins) PINS, sum(reloads) RELOADS, sum(reloads/pins) RATIO
2 from v$librarycache where pins != 0;
PINS RELOADS RATIO
---------- ---------- ----------
3000107 7964 .014909064
SQL>
SQL> select namespace,gethitratio, invalidations
2 from v$librarycache;
NAMESPACE GETHITRATIO INVALIDATIONS
--------------- ----------- -------------
SQL AREA .980312259 2065
TABLE/PROCEDURE .817544718 0
BODY .997125699 0
TRIGGER .984762397 0
INDEX .112746633 0
CLUSTER .987701768 0
OBJECT 1 0
PIPE 1 0
JAVA SOURCE 1 0
JAVA RESOURCE 1 0
JAVA DATA 0 0
11 rows selected.
SQL>
공유 풀 크기의 변경
위에서 설명한 것과 같은 다양한 튜닝 도구들에 의해 공유 풀 영역의 튜닝이 필요하다면 공유 풀의 크기를 변경해야 한다.
공유 풀 크기의 변경은 다음 사항을 고려하여 정해야 한다.
1) 저장된 객체(테이블, 뷰,프로시저등)들을 위해 필요한 공간얼마나 되는지 산정한다.
2) 자주 사용되는 (4회이상) 응용 프로그램의 메모리를 조회해 본다.
3) 한 사용자의 open 커서당 공유 풀은 250바이트 정도 할당하는 것이 좋으며, 최고조일 때의 전체 메모리는 (오픈커서수 x 25바이트)로 산정한다.
이제 매개변수 shared_pool_size는 (1+2+3+30% 정도의 여유공간)로 잡는다. 공유 풀 영역의 크기를 설정하기 위해 다음 매개변수의 값을 설정한다.
SHARED_POOL_SIZE=[크기]; SHARED_POOL_REVERSED_SIZE=[크기];
【예제】
SQL> select sum(sharable_mem) from v$db_object_cache;
SUM(SHARABLE_MEM)
-----------------
35909092 ☜ 가용 설정 공간
SQL> select sum(sharable_mem) from v$db_object_cache
2 where owner is not null;
SUM(SHARABLE_MEM)
-----------------
16046514 ☜ 1) 공유객체를 위한 공간
SQL> select sum(sharable_mem) from v$sqlarea
2 where executions > 4;
SUM(SHARABLE_MEM)
-----------------
9785693 ☜ 2) 자주 사용되는 으용 프로그램 공간
SQL> select sum(250*users_opening) from v$sqlarea;
SUM(250*USERS_OPENING)
----------------------
10500 ☜ 3) 오픈된 커서의 수에 따른 할당된 메모리 크기
SQL>
이 예제에서 가용 메모리=35909092로 되어 있다.
계산하여 보면 { 1)+2)+3)}*30%추가=25842707 * 1.3= 335955191임
그러므로 가용메모리 설정 값 35909092이 계산 값 335955191보다 크므로 여유가 있음
데이터 사전 캐시 튜닝
라이브러리 캐시와 함께 공유 풀의 데이터 사전 캐시에서의 튜닝의 목적은 히트율을 높이는 것이다. 데이터 사전 캐시를 모니터링하기 위해 V$ROWCACHE 뷰를 사용한다.
GETS 컬럼은 사용자가 자료사전을 쿼리했을 때 data dictionary영역으로 자료를 요청했던 횟수
GETMISSES는 자료 요청을 했지만 data dictionary로부터 자료를 얻지 못했던 횟수
대부분 자료사전을 빠르게 검색하기 위해서 GETS에 대한 GETMISSES의 백분율이 2% 미만이어야하고, 아주 큰 자료사전이라면 15%미만이어야 좋은 성능을 기대할 수 있음
이 값들이 15% 이상이면 매개변수 shared_pool_size 크기를 늘리는 것을 고려해 보아야 한다. DB 캐시 영역의 크기는 shared_pool_size 매개변수를 사용하여 변경할 수 있다.
또 utlbstat, utlestat 유틸리티의 분석결과인 report.txt나 statspack 패키지의 분석 결과인 report.txt에서 다음 사항을 참고해도 좋다.
| name | GET_REQS | GET_MISS | SCAN_REQS | COUNT | CUR_USAGE |
| dc_objects | |||||
| dc_synonyms |
【예제】 SQL> select parameter, gets, getmisses, getmisses/gets 2 from v$rowcache 3 where gets != 0; PARAMETER GETS GETMISSES GETMISSES/GETS -------------------------------- ---------- ---------- -------------- dc_segments 157452 5536 .035159922 dc_tablespaces 127769 8 .000062613 dc_tablespace_quotas 5896 7 .001187246 dc_files 72 4 .055555556 dc_users 661602 80 .000120919 dc_rollback_segments 46198 21 .000454565 dc_objects 118673 9587 .080785014 dc_global_oids 461691 175 .000379041 dc_constraints 1899 634 .333859926 dc_object_ids 599331 3945 .006582339 dc_sequences 181 28 .154696133 dc_usernames 29648 48 .001618996 dc_histogram_defs 168054 62822 .37382032 dc_table_scns 133 133 1 dc_profiles 5472 1 .000182749 global database name 4 2 .5 outstanding_alerts 5597 1100 .196533857 dc_awr_control 6079 1 .000164501 dc_histogram_data 31078 6155 .198050068 dc_histogram_data 13713 488 .03558667 dc_users 63178 60 .000949698 21 rows selected. SQL>
