1. 데이터베이스 설계 튜닝
데이터베이스 설계 단계에서 성능을 고려하여 설계
2. 데이터베이스 환경
성능을 고려하여 메모리나 블록 크기 등을 지정
3. SQL 문장 튜닝
성능을 고려하여 SQL 문장을 작성
튜닝을 수행하는 이유는 물론 데이터베이스의 활용 성능을 최상/최적으로 만들기 위해서다.
(1) 업무적인 환경과 시스템적 환경에 적합한 데이터베이스 파라미터를설정한다.
(2) 데이터베이스에 접근하는 SQL 문장은 가능한 한 디스크 블록에 최소로 접근하도록 한다.
(3) 디스크 브록에서 한 번 읽은 데이터는 가능하면 메모리 영역에 보관한다.
(4) 모든 사용자의 SQL 문장은 공유 가능하도록 명명 표준을 준수하여 작성한다.
(5) 잠금 발생이 최소가 되도록 한다.
[오라클 패치
Oracle Version Upgrade 의 이유 - Optimizer Upgrade, New Feature, Maintenance
Oracle Patch - DB Bug 수정
Oracle Patch 종류
특정 문제를 완벽하게 해결한 패치셋(patchset)이나
차기 릴리즈를 발표하기 전에 임시 패치(interim patch, 혹은'one-off'patch) 가 있다.
[SYSAUX Tablespace 정보]
이 tablespace는 다음과 같은 모든 Oracle option들과 기능들에 관한
데이타베이스 metadata를 관리하기 위해 새로이 추가되었다.
Option or Feature Schema Tablespace in 8, 8i or 9i
\
Oracle Label Security LBACSYS SYSTEM
Recovery Catalog RMAN TOOLS
EM Repository SYSMAN OEM_REPOSITORY
Stored Outlines OUTLN SYSTEM
Streams SYS (STREAM$_xxx) SYSTEM
LogMiner SYSTEM SYSTEM
Logical Standby SYSTEM SYSTEM
Statspack PERFSTAT User Specified
Job Scheduler SYS SYSTEM
Auditing SYS (AUD$) \
SYS (FGA$) - SYSTEM
SYS (FGA_LOG$) /
[파티션테이블 정보]
FROM 엔코아
문 : 박범철
답 : 한준희
은행에서 운영 dba로 일하고 있는데, 운영중인 테이블에 대한 파티션 적용을 고려하던 중
의문나는 사항들이 있어 문의드립니다.
경험자 분들의 답변 부탁드리겠습니다.
아래 문의사항 외에 고려할 사항이 있다면 그 부분도 언급부탁드립니다.
정성어린 답변에 미리 감사드립니다. \^\^
파티션을 적용할 것이냐 말것이냐의 결정 기준은 파티션의 특징을 정확하게 이해하고
있어야 기준을 이해하실 수 있을 겁니다.
파티션이라는 것은 테이블을 수평분할한 것으로 어떠한 생성 규칙에 맞는 데이터들만
하나의 세그먼트에 몰아두겠다는 것이고, 파티션을 사용한 PROGRAM에서 그렇지 않은
프로그램과 비교해 볼 때 효과를 얻을 수 있는 경우는 바로 읽고 처리해야 할 데이터가
상당히 많을 때 해당 조건을 만족하는 파티션만 읽고 마칠 수 있는 경우입니다.
전체 테이블을 모두 다 읽고 그 중에 조회조건에 맞는 것만 선택하며
다른 것은 버리는 경우보다 I/O 측면에서 유리한 것입니다.
즉 내가 주로 처리하는 데이터 량이 일정량 이상이 되어 인덱스만 가지고는 효과를 보기
어렵다거나, 보관주기가 지난 데이터를 주기적으로 삭제해야 하는데 삭제에 대한 부담을
많이 갖고 있을 때 이를 효율적으로 처리하기 위한 방법으로 파티션이라는 것을 도입하여 사용하고 있습니다.
2. 대용량테이블에 대해서 반드시 파티션을 적용해야만 하는가?
일반테이블로 유지할 경우 발생할 수 있는 문제점은?
대용량 테이블이라 하더라도 주로 ACCESS하는 데이터가 소량이고 OLTP성 처리가
대부분이라면 궂이 파티션을 적용하지 않아도 되겠지요. 데이터 관리 측면을 제외한 성능상에서의 의견입니다.
3 ACCESS 패턴이 PREFIX INDEX 만으로 제한되지 않아서 NONPREFIX INDEX 가 상당수 존재하는 경우
(업무상 파티션키 조건이 항상 들어올 수 없는 경우, 고객번호로 검색 등)에는
성능상 마이너스 효과가 발생할 것으로 보이는데 이 경우에도 대용량테이블이라면 파티션 적용을 해야하는가?
4. 위의 경우는 테이블과 인덱스의 파티션키가 동일한 경우를 가정하였는데,
인덱스의 파티션 키를 테이블의 파티션 키가 아닌 해당 인덱스의 컬럼중 일부로 하여
PREFIX INDEX로 구성하는 경우에는 성능이 보장되며, 이런 구성을 많이 사용하는지? 관리상 어떤 어려움이 발생할 수 있는지?
인덱스에는 PREFIX와 NON-PREFIX 두개가 있습니다. PREFIX형태의 조회조건이 들어오지
않오 AD-HOC QUERY가 빈번하게 발생한다면 GLOBAL INDEX 혹인 LOCAL NON-PREFIX인덱스를 사용하시면 됩니다.
예를 들면 조회조건에 고객번호가 = 로 들어오는데 파티션은 고객번호를 선두로 하지 않은
일자 컬럼을 기준으로 만들었다고 하겠습니다.
이 경우 고객번호로 LOCAL NON-PREFIX INDEX를 만든 다음 PARTITION PRUNING 조건인
일자가 들어오지 않고 고객번호만 들어왔다면 전체 PARTITION에 있는 LOCAL INDEX에
대 놓고 고객 번호검색을 하게 될 것입니다.
인덱스를 통해 ACCESS함에 모든 파티션에 있는 LOCAL INDEX를 ACCESS하게 됩니다만
TABLE FULL SCAN에 비해 성능상의 이익이 분명 존재합니다.
3~4번 문제 공히 고려되는 내용이라 생각하시면 되겠네요.
[오라클 성능 분석 방법론 정보]
RATIO ANALYSIS - 성능 분석 방법론
Ratio 라는 것이 0 에서 1사이의 범위를 가진 수치로 표현하여 상대적인 수치를
절대적인 수치로 변경하였기 때문에 언제, 어느 데이터베이스에 적용하더라도 같은
단위로 비교가 가능하다는 의미이다.
오라클의 공유 풀 영역은 라이브러리 캐시와 딕셔너리 캐시로 구분되며,
라이브러리 캐시에는 SQL 문장과 PL/SQL 문장이 저장되고, 딕셔너리 캐시에는
데이터베이스에 관련된 각종 정보들이 딕셔너리로 저장된다.
sys@ARTDOM>select gets, gethitratio, pins, pinhitratio, reloads, invalidations
2 from v$librarycache
3 where namespace ='SQL AREA';
GETS GETHITRATIO PINS PINHITRATIO RELOADS INVALIDATIONS
---------- ----------- ---------- ----------- ---------- -------------
340751 .990635391 1383994 .995631484 756 1197
sys@ARTDOM>select (sum(reloads)/sum(pins))*100 "MISS RATE"
2 from v$librarycache ;
MISS RATE
----------
.083522836
1. init<SID>.ora 파일에서 shared_pool_size 를 크게 한다.
2. 명명표준을 준수하여 동일한 문장에 대해 재파싱 작업이 일어나지 않게 한다.
3. 상수를 사용하기보다는 바인딩 변수를 사용한다.
sys@ARTDOM> select to_char(trunc(sum(getmisses)/sum(gets)*100,5),0999.99)||'%(LESS THAN 15%)' "MISS RATE"
from v$rowcache ;
MISS RATE
-----------------------
4.05%(LESS THAN 15%)
VLDB: WAIT_RATIO 가 1% 이상이 나오면 리두로그가 발생할 때 경합이 발생한 경우다.
리두로그 버퍼는 모든 SQL 문장이 실행될 때 이용하는 메모리 영역이므로 작업하는 동안은
이 메모리 공간을 이용해야 한다. 데이터에 대해 수정이 발생하면 잠금을 설정하듯이
메모리 영역에서 다른 프로세스가 이용하지 못하도록 독점하는 방법으로 래치(LATCH)를
할당받아 작업한다. 래치를 할당받지 못한 SQL 문장들은 래치를 할당받을 때까지 기다려야
한다. 그러므로 대기율을 분석하여 오랜 시간동안 대기하고 있거나 잦은 대기 상태가 되는
경우에는 리두 로그 버퍼의 크기를 조정해야 한다.
sys@ARTDOM>select a.name, (b.misses/b.gets)*100 WAIT_RATIO
from v$latchname a, v$latch b
where a.name in ('redo allocation') and
a.latch# = b.latch# ;
NAME WAIT_RATIO
-------------------------------------------------- ----------
redo allocation .034880924
sys@ARTDOM>select name, value from v$sysstat where name ='redo log space requests';
NAME VALUE
-------------------------------------------------- ----------
redo log space requests 57
특정 디스크에 있는 데이터 파일에 I/O가 집중되면 이는 성능 저하의 요인이 되므로
I/O 현황을 분석하여 집중된 I/O를 분산시켜야 한다.
sys@ARTDOM> select name, phyrds, phywrts, phyblkrd, phyblkwrt
from v$filestat, v$datafile
where v$filestat.file# = v$datafile.file# ;
NAME PHYRDS PHYWRTS PHYBLKRD PHYBLKWRT
-------------------------------------------------- ---------- ---------- ---------- ----------
/u02b/ORACLE/ARTDOM/ARTDOM/system01.dbf 5381 18424 7194 19874
/u02b/ORACLE/ARTDOM/ARTDOM/undotbs01.dbf 47 11516 47 26253
/u02b/ORACLE/ARTDOM/ARTDOM/sysaux01.dbf 3212 19088 9979 26131
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users01.dbf 50 36 179 180
/u02b/ORACLE/ARTDOM/ARTDOM/example01.dbf 35 26 43 26
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users02.dbf 59 43 183 187
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users03.dbf 42 32 158 156
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users04.dbf 42 32 158 156
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users05.dbf 38 38 153 162
9 rows selected.
[v$filestat 정보]
PHYRDS NUMBER Number of physical reads done
PHYWRTS NUMBER Number of times DBWR is required to write
PHYBLKRD NUMBER Number of physical blocks read
PHYBLKWRT NUMBER Number of blocks written to disk, which may be the
same as PHYWRTS if all writes are single blocks
일반적으로 데이터베이스의 디스크 I/O 를 줄이기 위해서는 다음과 같은 설계
방법을 권한다.
로우 마이그레이션(Row Migration)
: 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지
못하고, 다른 블록의 빈 공간을 찾아 데이터를 저장하는 방식
보통 PCTFREE 가 낮게 설정된 경우 발생
로우 체이닝(Row Chaining)
: 로우 길이가 너무 길어서 테이블 블록 하나에 데이터가 모두 저장되지 않고, 두 개
이상의 블록에 걸쳐 하나의 로우가 저정되어 있는 형태
보통 DB_BLOCK_SIZE 가 너무 작게 지정 혹은 LOB 타입 사용시
sys@ARTDOM>analyze table scott.emp compute statistics ;
Table analyzed.
sys@ARTDOM>select num_rows, chain_cnt from dba_tables where table_name ='EMP';
NUM_ROWS CHAIN_CNT
---------- ----------
14 0
사전 실행[VLDB:아래 SQL 을 실행 하여 CHAINED_ROWS 테이블을 사전 생성 해야한다. ]
sys@ARTDOM>!ls /app/oracle/product/10.1.0/rdbms/admin/utlchain.sql
/app/oracle/product/10.1.0/rdbms/admin/utlchain.sql
sys@ARTDOM>analyze table scott.emp list chained rows into CHAINED_ROWS ;
Table analyzed.
sys@ARTDOM>select count(*) from CHAINED_ROWS where table_name =upper('EMP');
COUNT(*)
----------
0
SQL 문장을 작성할 때 꼭 알고 있어야 할 사항
[dual table 정보]
– Online Help
Selecting from the DUAL Table
DUAL is a table automatically created by Oracle along with the data dictionary.
DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users.
It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value 'X'.
Selecting from the DUAL table is useful for computing a constant expression
with the SELECT statement.
Because DUAL has only one row, the constant is returned only once.
Alternatively, you can select a constant, pseudocolumn, or expression from any table,
but the value will be returned as many times as there are rows in the table
-- 메타링크 공지:363988.1
Originally, this table was introduced by Oracle for its internal processing, for example, RMAN
uses this table to fetch the current date for a backup. Contention on this table can occur, if
application uses this table frequently and concurrently, with common waits like Buffer Busy Wait,
Latch Free (Cache Buffer Chain / Cache Buffer LRU chain). The behaviour of this table is changed in
Oracle database version 10g and hence, this note is only applicable for database versions 9i and below.
SELECT MAX(주문번호) + 1
FROM :V_주문일련번호
FROM 주문 ;
INSERT INTO 주문(주문일련번호, COL2, COL3...)
VALUES ( V_주문일련번호, 'XXX','XXX'...);
INSERT INTO 주문(주문일련번호, COL2, COL3... )
SELECT DECODE(MAX(주문일련번호), NULL, 0, MAX(주문일련번호))+1 주문일련번호, :COL2값...
FROM 주문
INSERT INTO 주문(주문일련번호, COL2, COL3... )
SELECT DECODE(MAX(주문일련번호), NULL, 0, MAX(주문일련번호))+1 주문일련번호, :COL2값...
FROM 주문
WHERE 지점코드 ='XX'
AND 주문일자 ='2002-01-01
-- WHERE 절을 통해서 충분하게 값을 걸러주어 MAX 부하를 줄인다.
CREATE SEQUECNE 주문_SEQ
INCREMENT BY 1 ;
INSERT INTO 주문( 주문일련번호, COL2, COL3 ... )
VALUES ( 주문_SEQ.NEXTVAL, 'XXX','XXX'...);
세 가지 SQL은 트랜잭션 하나로 처리 되어야 정확한 데이터 관리가 가능하다.
1) 신규 일련번호 채번 ( FOR UPDATE ~ )
SELECT 최종일련번호 + 1
INTO :V_주문일련번호
FROM 주문
WHERE 지점코드 ='01'
AND 주문구분 ='인터넷';
2) 본 테이블에 데이터 입력
INSERT INTO 주문(주문일련번호, COL2, COL3... )
VALUES (V_주문일련번호, 'XXX','XXX'...);
3) 채번 테이블 수정
UPDATE 주문채번테이블
SET 최종일련번호 = :V_주문일련번호
WHERE 지점코드 = '01'
AND 주문구분 = '인터넷'
INSERT INTO 주문(주문일련번호, COL2, COL3... )
SELECT /*+ INDEX_DESC(B i_01) */ <-- 인덱스 i_01 일때 인덱스를 역정렬함
DECODE(MAX(주문일련번호), NULL, 1, 주문일련번호+1), 'XXXX','XXX'...
FROM 주문 A,
( SELECT 0 SER FROM DUAL ) B
WHERE A.주문일련번호 = B.SER(+) <-- 데이타가 하나도 없을 때 채번하기 위함
AND ROWNUM = 1 ;
주로 인덱스에서 문제가 발생한 경우다.
1) 인덱스에 대해 ANALYZE 를 수행
sys@ARTDOM>analyze index scott.BIG_INDEX estimate statistics ;
Index analyzed.
sys@ARTDOM>alter session set db_file_multiblock_read_count=256;
Session altered.
sys@ARTDOM>alter session set sort_area_retained_size=100000000;
Session altered.
sys@ARTDOM>alter session set sort_area_size=100000000;
Session altered.
sys@ARTDOM>analyze index scott.BIG_INDEX estimate statistics ;
Index analyzed.
2)인덱스 레벨을 검사 VLDB: 적정수준 100만 ROW - 3 LEVEL
sys@ARTDOM>select blevel, leaf_blocks from dba_indexes where index_name ='BIG_INDEX';
BLEVEL LEAF_BLOCKS
---------- -----------
1 230
3) 인덱스를 대체한다. VLDB: 재생성한다
sys@ARTDOM>alter index scott.big_index rebuild ;
Index altered.