공부흔적

http://docs.oracle.com/cd/B19306_01/server.102/b14220/memory.htm

Oracle Alert and Trace Files 

a. Alert log file : db 상황, Oracle Instance가 수행시 발생하는 error등을 기록함 

                       파라미터 파일의 " background_dump_dest = " 로 설정함 

                       nomount 단계에서 기록을 시작함. 

파라미터 파일의 내용

b. Background Process trace file : LGWR,DBWR,SMON,PMON,CKPT.... 

                                                 (문제시마다 생성되므로 여러개 생성됨) 

                                                  파라미터 파일의 " background_dump_dest = "로 설정함 

                                                  파일이름을 보면 어떤 프로세스인지 알수 있음. 

a(xxxx.log), b(xxxx.trc)의 경로는 같다.

c. User trace file : user request에 의해서 생성됨. 

                          (여러개 생성됨) 

                          파라미터 파일의 " user_dump_dest= " 로 설정함 

4 인덱스의 종류

1) B-Tree 인덱스

인덱스가 생성될 경우 leaf block -> branch block -> root block의 순서

leaf block : 실제 데이터를 저장하고 있는 데이터 블록들의 주소

branch block : leaf blocks 에 대한 정보

root block : branch block 에 대한 정보

데이터를 찾는 순서 

root block -> branch block -> leaf block 정보를 찾음 

-> 해당데이터의 rowid 를 찾은 후 데이터가 들어있는 블록을 메모리로 복사 

특징  

B-Tree 에서 B는 Binary이란 의미와 함께 Balance의 의미도 있음

Root block를 기준으로 왼쪽과 오른쪽에 들어있는 데이터의 Balance가 맞을 때, 성능이 가장 좋음

일반적으로 가장 많이 사용됨

아래는 다양한 B-Tree의 인덱스들...

(1) UNIQUE INDEX

인덱스를 만드는 key 값에 중복되는 데이터가 없다는 뜻

해당 테이블 컬럼에 중복된 값이 없고, 앞으로도도 중복된 값이 들어올 수 없다는 의미

일반 인덱스와 비교시에도 속도가 빠르기 때문에 둘 중에서는 유니크 인덱스 권함

키 컬럼의 데이터들이 중복없는 유일성을 보장하는 인덱스라서 속도는 빠름

주의 - 나중에 중복되는 데이터가 들어올 수 없는 단점이 있기 때문에 잘 생각해서 사용해야 함

생성문법

CREATE UNIQUE INDEX 인덱스명

ON 테이블이름(컬럼명1 ASC|DESC, 컬럼명2,.....);

생성예제

CREATE UNIQUE INDEX IDX_DEPT2_DNAME

ON dept2(dname);

(2) NON-UNIQUE INDEX

중복되는 데이터가 들어가야 하는 컬럼일 경우에는 UNIQUE INDEX 를 생성할 수 없기 때문에

NON-UNIQUE INDEX 를 생성함

생성문법

CREATE INDEX 인덱스명

ON 테이블명(컬럼명1 ASC|DESC, 컬럼명2, ....);

생성예제

dept2 테이블의 area 컬럼에 NON UNIQUE INDEX 를 생성

CRAETE INDEX idx_dept2_area

ON dept2(area);

(3) FUNCTION BASED INDEX(FBI-함수기반 인덱스)

인덱스는 WHERE 절에 오는 조건에 컬럼이나 조인컬럼 등에 만들어야 함 *

(단 몇 가지 특별한 경우에 SELECT에 있는 컬럼에 생성하기도 함)

WHERE pay=1000이란 조건일 경우 pay 컬럼에 인덱스를 만들어야 함

pay 컬럼에 인덱스를 생성 후 위와 같이 사용하면 인덱스를 이용함 

WHERE pay + 1000 = 2000 의 조건 검색은 인덱스 이용 못 함 - 연산자를 사용하는 경우 

WHERE ename != 'FORD'; 인덱스 이용 못 함- NOT을 사용하는 경우

=> INDEX Suppressing Error : 인덱스는 잘 생성하고, SQL 를 잘못 작성해서 인덱스를 사용할 수 없는 경우

인덱스를 사용하려면 WHERE 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안 됨

WHERE pay+1000 = 2000 을 인덱스를 사용하고 싶으면?

(pay+1000) 이 형태로 사용해 줌

이런 형태의 인덱스를 함수기반 인덱스라고 함

pay+1000 이라는 컬럼이 없지만 함수처럼 연산을 해서 인덱스를 만들어준다는 의미

생성예제

CREATE INDEX idx_prof_pay_fbi

ON professor(pay+1000);

단점

pay+1000 이라는 컬럼이 생성됨, 오라클이 인덱스를 만들 때, 이 연산을 이용해 인덱스를 만들어 주기 때문

하지만! 임시적인 해결책임, pay+1000 의 조건이 변하면 인덱스도 다시 만들어줘야하는 단점이 있고

FBI는 기존 인덱스를 활용할 수 없음 

(4) DESCENDING INDEX( 내림차순 인덱스 )

여러 인덱스를 생성할 때 큰 값이 먼저오게, 내림차순 순으로 인덱스를 생성함

1000

999

998 

...

이런식으로 인덱스 생성

사용 

큰 값을 많이 조회하는 SQL에 사용하는 것이 좋음

예) 

계좌조회시 최근 날짜부터 나오게 하는 경우(최근 날짜가 큰 날짜)

매출 테이블의 상위 매장이나 사원을 보는 경우(매출이 큰 값을 먼저 조회하는 경우)

문제) 하나의 메뉴에 오름차순과 내림차순을 한꺼번에 조회할 경우는?

A 최근 날짜 부터 계좌조회  

B 예전 날짜 부터 계좌조회

인덱스를 오름차순, 내림차순으로 두번이나 만들면 (인덱스가 많으면) DML 성능에 악영향

해결 : 인덱스를 위나 아래에서 읽게 하는 힌트라는 방법을 제공

         힌트들을 이용해 튜닝에서는 정렬하지 않고, 정렬효과를 내고, 최소값과 최대값을 구하기도 함

(5) COMPOSITE INDEX ( 결합인덱스 )

인덱스를 생성할 때 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것

사용

SQL문장에서 WHERE 절의 조건 컬럼이 2개 이상이 AND로 연결되어 함께 사용되는 경우에 사용

아주 많이 사용되나 , 인덱스를 잘못 생성하게 되면 굉장히 나쁜 영향을 미침

예) emp 테이블에 100명의 인원 중 남자(M)은 50명, 여자(F) 50명

50명의 남자 중 이름이 'SMITH'인 사람이 단, 2명 있다고 가정

일반쿼리문

SELECT ename, sal

FROM emp

WHERE ename='SMITH'

AND sex='M';

결합인덱스 생성 구문 예

CREATE INDEX idx_emp_comp

ON emp(ename, sex); 이 부분이 위와 같음  WHERE ename='SMITH'

  AND sex='M';

해당 데이터를 찾을때 두 개의 컬럼을 동시에 만족하는 블록을 검색하겠다는 의미

같은 테이블에 같은 SQL이지만 결합 인덱스를 어떻게 생성하는가에 따라서 속도나 검사횟수가 완전히 달라질 수도 있음

ON emp(ename, sex) 100건의 성별 중 2건의 이름 찾은 후 성별검사 - 2건 출력

On emp(sex, ename)  100건의 성별 중 50건을 찾은 후 다시 이름을 검색해서 - 2건 출력

=> 첫번째 조건에서 최대한 많은 데이터를 걸러서 두번째 검사를 쉽게 만들어야 함

결합인덱스는 경우의 수도 많고, 고려해야할 사항이 많으므로, 아주 신중하게 고려하고 사용해야 함 

1. 인덱스란 ?

* 인덱스  

어떤 데이터가 어디에 있다라는 주소록의 개념

잘 활용하면 아주 빠르게 데이터를 찾을 수 있음

어떤 데이터의 주소는 어디다. 라는 식으로 

모든 데이터들의 주소록을 만들어 관리하는 기법

=> 

인덱스가 있으면 모든 블록을 다 읽지 않고, 원하는 데이터가 있는 블록 주소를 찾아내

그 블록만 메모리로 복사해 와서 작업이 빨리 끝남

어떤 데이터가 어디에 저장되어 있는지 알고 있기 때문에 데이터 조회,변경, 호출해야 할 때 아주 빠르게 작업 가능

BUT 인덱스 생성이나 관리를 잘못할 경우는 성능이 떨어지는 역효과가 발생하기도 함

* 쿼리 실행시 데이터 검색 과정

많이 사용되는 데이터는 데이터베이스 버퍼캐시에 캐싱해두고 

많이 사용하지 않는 데이터는 디스크에 저장했다가 

필요할 때 메모리로 복사해 와서 조회나 변경 작업등을 함

* Table full scan

사용자가 어떤 데이터를 찾을 때 

메모리에서 해당 데이터를 못찾을 경우 

파일에 가서 데이터를 찾아 메모리로 복사해와야 하는데 

하드디스크에 데이터가 너무 많을 경우(예 1억개)에는

해당 데이터가 어떤 블록이 들어 있는지 모른다면

모든 블록을 읽어서 확인함

2. 인덱스의 생성 원리

해당 테이블의 내용들을 모두 다 읽어 메모리로 가져옴

어떤 내용이 있는지 내용을 파악해서 목록만듬

인덱스를 만드는 동안 데이터가 변경되면 문제가 되므로

해당 데이터들이 변경되지 못하도록 조치한 후 PGA의 Sort area에서 정렬을 함

PGA 메모리가 부족하면 임시테이블스페이스(Temporary Tablespace)를 사용해서 정렬하게 됨

메모리에서 정렬과정이 끝난 데이터들은 인덱스를 저장하는 파일의 블록에 순서대로 기록함

인덱스 생성

전체 테이블 스캔 -> 정렬(sort) -> Block 기록

인덱스는 데이터가 정렬되어서 들어감 

3. 인덱스 구조와 작동원리(B-Tree  인덱스 기준)

인덱스는 key 칼럼와 rowid 칼럼으로 이루어짐

key 칼럼 : 인덱스를 생성하라고 지정한 컬럼 값

rowid 칼럼 : 데이터가 저장되어 있는 주소 

실제 데이터 - Data file

인덱스 정보 - Index file 에 저장되어 있음

1 가장 먼저 메모리의 데이터베이스버퍼캐쉬에서 해당 데이터가 있는지 확인

2 인덱스 파일 찾는 순서는 트리구조의 오른쪽 상단그림 

root 9 - branch 7 - leaf 1 순으로 읽음 

3 인덱스에 있는 블록 데이터를 가지고 옴

경우에 따라 인덱스를 사용하지 않는 것이 성능에 더 좋을 수도 있음

여러건의 데이터를 조회할 경우 인덱스를 사용할 때는 한 번에 하나의 블록만 읽을 수 있음                 

single block I/O : 1번에 1개의 블록만 읽어서 메모리로 가져옴( db_block_size )

multi block I/O : 인덱스를 쓰지 않았을 경우에 한 번에 여러 블록을 가져오는 것 ( DB_FIEL_MUTLIBLOCK_COUNT ( MBRC ))

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

BIN$Bnc5lHWi9ALgUAB/AQActw==$0 TABLE

BIN$CmN1Dij4JNzgUAB/AQAKKg==$0 TABLE

BIN$CmN1Dij5JNzgUAB/AQAKKg==$0 TABLE

BIN$CmNu0NooARXgUAB/AQAKEw==$0 TABLE

BIN$CmNu0NopARXgUAB/AQAKEw==$0 TABLE

BONUS                          TABLE

CAL                            TABLE

DAECHUL                        TABLE

DEPARTMENT                     TABLE

DEPT                           TABLE

DEPT2                          TABLE

TNAME                          TABTYPE  CLUSTERID

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

EMP                            TABLE

EMP2                           TABLE

EXAM_01                        TABLE

FRUIT                          TABLE

GIFT                           TABLE

GOGAK                          TABLE

HAKJUM                         TABLE

JUMSU                          TABLE

MEMBER                         TABLE

PANMAE                         TABLE

PLAN_TABLE                     TABLE

TNAME                          TABTYPE  CLUSTERID

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

PRODUCT                        TABLE

PROFESSOR                      TABLE

PT_01                          TABLE

PT_02                          TABLE

P_01                           TABLE

P_02                           TABLE

P_GRADE                        TABLE

P_TOTAL                        TABLE

REG_TEST                       TABLE

REG_TEST2                      TABLE

SALGRADE                       TABLE

TNAME                          TABTYPE  CLUSTERID

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

STUDENT                        TABLE

TEST_ENABLE                    TABLE

TEST_NOVALIDATE                TABLE

TEST_VALIDATE                  TABLE

TT100                          TABLE

TT200                          TABLE

TT300                          TABLE

40 rows selected.

위와 같은 숫자 번호대로 sql의 실행계획을 읽는다. 

레벨이 같은 단계는 위에서부터 읽고, 오른쪽에서 시작해서 가장 왼쪽으로 들어간 것을 먼저 읽는다. 

예1)

 A 4

B 3

C 1

D 2

예2)

A 5 

B 2

C 1

D 4

E 3

SQL 실행 계획 예 

EXPLAIN PLAN 명령 등을 수행한 경우의 실행 계획의 표현 

ID OPERATION                      OPTIONS               OBJECT_NAME

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

0   SELECT STATEMENT

1       FILTER

2           NESTED LOOPS

3                TABLE ACCESS    FULL                     EMP

4                TABLE ACCESS    BY ROWID                 DEPT

5                     INDEX UNIQUE SCAN    PK_DEPTNO

6                TABLE ACCESS    FULL                     JOBS

3-5-4-6-2-1-0

mts 관련 정보 링크

76 101

https://community.oracle.com/thread/428692?start=0&tstart=0

http://mclee.tistory.com/129

1 사용자가 특정 데이터의 변경을 요청하는 쿼리를 수행

2 유저프로세스로 부터 해당 sql을 받은 서버프로세스는 원하는 block가 있는지 없는지 확인

   원하는 파일이 없을 경우 해당 블록을 데이터 파일에서 찾아 복사 후 메모리로 가지고 옴

3 해당 블록의 해당 row부분을 다른 사용자가 바꿀 수 없도록 블록에 lock를 설정 - page fix

4 PGA 영역에서 Server process가 redo change vector 를 생성 후 redo log buffer 에서 필요한 용량을 계산함 

redo change vector : 변경된 데이터를 복구 할 목적으로 redo log에 기록할 변경된 데이터에 대한 모든 정보의 세트

undo 관련 내용도 들어있음 ( rollback 데이터를 복구할 경우

commit 후 checkpoint 발생 전에 DB 강제종료가 일어나서 

DB가 강제 종료 되었을 때도 rollback 해야할 때 이전 데이터가 필요하기 때문) 

changer vector 들은  redo record 라는 형태의 row  단위로 redo log buffer 에 복사 됨

5 Change vector 를 redo log buffer에 복사하기 위해 필요한 redo copy latch를 획득해야 하며, 

   change vector 를 redo buffer 에 복사하는 작업의 전 과정동안 redo copy를 보유해야 함. 

latch : 일종의 번호표 개념, 한정된 메모리 자원을 여러 사용하자 사용할 때 순서를 정리해 주는 역할

    여러개의 Server process가 동시에 데이터를 변경하면, 

    Redo copy latch를 획득하는 과정에서 경합( latch : redo copy 이벤트 대기 ) 이 발생됨

    경합이 발생하면 성능이 저하됨

6 Redo copy latch 를 확보한 Server process는 Redo log buffer에 내용을 기록하기 위해 Redo allocation latch를 확보 해야 함

oracle 8i : Redo allocation latch 1개, 많은 경합이 생겨 성능이 저하됨

oracle 9i : Shared Redo Strand 기능 - Redo log buffer를 여러 개의 공간으로 나누어 각 공간 별로 Redo allocation latch를 할당

               여러 서버 프로세스가 동시에 작업이 가능해 성능이 높아짐 

   Redo log buffer 를 몇 개로 나누는 가가 성능에 영향을 미침 

   Strands : 여러 개로 분할된 영역을 의미

   기본값 LOG_PARALLELISM = 1

oracle 10g : LOG_PARALLELISM 가 히든파라미터 _LOG_PARALLELISM 로 바뀜 

oracle 이 동적으로 관리하도록 _LOG_PARALLELISM_DYNAMIC 파라미터가 추가됨

이 값을 TRUE로 설정하면 Oracle 이 해당 Strand 개수를 자동으로 제어함, 권장값은 ( CPU개수 / 8)

Private Redo Strand 기능 - 9i 의 Shared Redo Strand 보다 확장된 개념이 도입됨

        각 Server process 는 Shared pool 에 자신 만의 독립적인 Private Strand 공간을 만들어  

        이 곳에 Change vector 을 생성 후 필요할 경우 LGWR 이 Redo log file 에 바로 기록함

        latch를 거치지 않기 때문에 성능이 향상됨, 그래서 'zero copy redo' 라고도 함 

 7 Redo log buffer에 기록 된 내용( Change 

최소 단위에서 큰 단위

OS block -> Oracle block -> Extent -> Segment -> Tablespace 

Oracle Block의 개요

Shared Pool / Library Cache 와 OWI

Shared Pool과 Heap

Shared Pool은 SGA 영역 중 Variable 영역^[각주:1]에 속함

- Library Cache

- Row Cache(=Data Dictionary Cache)

Shared Pool이 어떤 메모리 영역으로 나누어지는 확인 -> V$SGASTAT 뷰 조회

Shared Pool의 구성요소

Permanent Area : process, session, segmented array(enqueue, transaction,...), 프로세스 목록, 세션 목록, Enqueue 목록 등의 자원들은 Shared Pool 영구 영역(Permanent Area)에 할당되며 인스턴스와 그 수명을 같이 함

Library Cache : SQL문을 수행하는데 필요한 모든 객체(SQL,테이블, 뷰, 프로시저 등)에 대한 정보를 관리함

Row Cache(Dictionary Cache) : 오라클이 사용하는 딕셔너리 정보를 관여함

Reserved Area : 예약영역, 동적인 메모리 할당을 위해 오라클 여분의 예약 영역을 할당해줌

Shared Pool은 힙(heap)라고 불리는 메모리 관리기법을 이용해 관리

- 힙은 순서대로 저장하지 않고 빈자리가 있으면 막 넣기 때문에 별도의 리스트(Heap Manager)가 필요함

shared pool heap 구조도

Shared pool heap 으로 부터 메모리를 할당받고자 하는 모든 프로세스는 반드시 shared pool latch 를 획득 해야함

하드 파싱이 발생하는 경우

- 프로세스는 새로운 SQL문을 저장할 공간을 Shared pool에서 할당 받아야 하고, 반드시 Shared pool latch 를 획득 해야함

- Shared pool latch는 기본적으로 전체 인스턴스에 하나만 존재하며, 필요한 메모리(청크)를 할당받는 전체 과정동안 보유해야 함

- 동시에 많은 프로세스가 Shared pool 메모리를 사용하는 경우 latch를 획득하는 과정에서 경합이 발생하게 됨

   이 때, latch:shared pool 이벤트를 대기함