1. PGA_AGGREGATE_TARGET

1.1 통계 (V$SYSSTAT, V$SESSSTAT)

“workarea memory allocated : work area에 dedicate로 할당된 전체 PGA 메모리(단위 KB)

“workarea executions – optimal” : optimal size를 가진 work area의 합

(sort가 디스크에서 발생할 필요가 없다면 optimal size에 정의 된다.

“workarea executions – onepass” : one pass size를 사용하는 work area의 합

one pass란 disk sort를 피할 수 없는 경우 발생한다.

“workarea executions – multipass”: one pass 이상 발생하는 경우를 말한다. 이 경우 튜닝을 해야 하다.

따라서 workarea는 optimal, onepass, multipass 이다.

1.2 정의

oracle 8i 까지 sort_area_size, hash_area_size, bitmap_merge_size, create_bitmap_area_size

설정은 DBA 역할 이다. 그러나 9i 부터 system은 자동으로 영역에 대한 메모리 할당을 수행한다.

그러므로 질의에 따라 사용되는 최대 메모리를 조절하여 LOW OR HIGH 메모리 사용을 상쇄시킨다.

또한 메모리에 대한 과도한 할당을 피한다. 기본적으로 PGA 영역의 사용을 조절한다.

이 영역은 Tunable and untunable 로 나누어지고, Tunable 영역은 DSS 환경에서 많이 사용된다.

1.3 수행

a. WORKAREA_SIZE_POLICY

b. PGA_AGGREGATE_TARGET

A.WORKAREA_SIZE_POLICY

==========================

MANUAL: Disable (default)

AUTO : able

Auto로 설정하면 다음과 같은 프로세스가 일어난다.

1) 튜닝이 일어난다. 그래서 PGA 전체 크기는 PGA_AGGREGATE_TARGET를 넘지 않는다.(따라서 untunable 부분이 부족할 경우 PGA_AGGREGATE_TARGET를 넘는 OVER ALLOCATE가 발생한다). 오라클은 PGA의 Tunable 부분을 튜닝 할 것이다. 그래서 전체 PGA 가 PGA_AGGREGATE_TARGET를 넘지 못하도록 한다. 그러나 Untunable 영역이 PGA_AGGREATE_TARGET를 넘는다면 PGA는 PGA_AGGREGATE_TARGET를 넘을 수 있다.

오라클은 이 모드에서 다음과 같이 설정 하려고 한다 .

TUNABLE MEMORY SIZE + UNTUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET

2) 프로세스에 의해서 할당된 tunable 메모리는 통제되기 때문에 프로세스는 결코 메모리 영역을 넘어서 수행되지 않는다.

B.PGA_AGGREGATE_TARGET

========================

WORKAREA_SIZE_POLICY가 AUTO일 경우 질의에 의해서 사용될 수 있는 최대 메모리의 양을 설정한다.

값은 Bytes, (k), (M) , (G) 로 설정하며 default 는 0 이다.

alter system set PGA_AGGREGATE_TARGET =10m

PGA_AGGREGATE_TARGET은 전체 PGA 사용량과 각 Workarea 의 크기로 제한된다.

예를 들어 단일 SQL에 할당 될 수 있는 최대 메모리는 (5% PGA_AGGREGATE_TARGET),

serial SQL은 100M, DOP를 사용하는 세션은 30% PGA_AGGREGATE_TARGET/DOP

또한 이 파라메터는 CBO에서 효과적이다. 그것은 질의문에서 사용하는 Bitmap ,hash join,

sort operation 에 대하여 실시간으로 이용하여야 할 최대/최소 메모리를 계산 한다.

이 부분에서 init.ora에 *_area_size를 명시하면 plan 에 영향을 미칠 뿐 실제 할당되어

사용되지 않는다. 다만 workarea_size_policy=manual 후 alter session으로 변경하여 사용

하면 alter session에서 명시한 값으로 PGA에 할당 된다.

1. V$PROCESS (PGA_USED_MEM, PGA_ALLOC_MEM, PGA_MAX_MEM)

2. V$PGASTAT

C.F) Automatic tuning은 실제 필요 이상의 메모리를 할당 하는 경우가 있다.

Monitoring Private Memory using V$SYSSTAT

=========================================

다음 질의는 Optimal size를 가진 work area의 % 이다.

select
trunc (
       (sum(case when name like 'workarea executions - optimal'
                                       then value else 0 end) *100) /
               (
                  sum(case when name like 'workarea executions - optimal'
                                             then value else 0 end)         +
                  sum(case when name like 'workarea executions - one pass'
                                             then value else 0 end)         +
                  sum(case when name like 'workarea executions - multipass'
                                             then value else 0 end)
               )
        ) optimal_percent
from v$sysstat
where name like 'workarea executions - %'
/

New views

=========

a. V$SQL_WORKAREA

b. V$SQL_WORKAREA_ACTIVE

select *
from (select hash_value,workarea_address, operation_type,

policy, estimated_optimal_size
from v$sql_workarea
order by estimated_optimal_size DESC)
where ROWNUM <=10;

Finding top ten work areas requiring the most cache memory:

select operation_type, total_executions * 100 / optimal_executions "%cache"
From v$sql_workarea
Where policy='AUTO'
And optimal_executions > 0
Order By operation_type;

2. Finding the percentage of work areas using maximum memory:

select c.sql_text, w.operation_type, top_ten.wasize
From (Select *
    From (Select workarea_address, actual_mem_used wasize
            from v$sql_workarea_active
            Order by actual_mem_used)
      Where ROWNUM <=10) top_ten,
      v$sql_workarea w,
      v$sql c
Where    w.workarea_address=top_ten.workarea_address
        And c.address=w.address
        And c.child_number = w.child_number
        And c.hash_value=w.hash_value;

3. Finding the top ten biggest work areas currently allocated in the system:

select total_used,
        under*100/(total_used+1) percent_under_use,
        over*100/(total_used+1)   percent_over_used
From
        ( Select
                sum(case when expected_size > actual_mem_used
                                       then actual_mem_used else 0 end) under,
                sum(case when expected_size<> actual_mem_used
                                       then actual_mem_used else 0 end) over,
                sum(actual_mem_used) total_used
        From v$sql_workarea_active
        Where policy='AUTO') usage;

4. Finding the percentage of memory that is over and under allocated: (튜닝 포인트)

Process Global area : 일명 program global area(PGA)로 알려져 있는 이 영역은 서버 프로세스의 process private memory에 존재 하며, global 변수 와 데이터 구조, 서버 프로세스에 대한 제어 정보를 가지고 있으며, 그런 정보의 예로써, 커서가 실행 될 때마다 그 커서를 실행한 PGA 메모리 영역 안에 그 커서에 대해 새로운 실행 영역이 생성된다.

Work area의 크기는 제어되고, 튜닝 된다. 일반적으로 work area가 클수록 메모리 사용이 높은 operations은 특히 성능이 좋아진다. 이상적으로 work area 의 크기는 입력 데이터와 연관된 SQL 에 의해서 할당된 보조 메모리 구조에 Accommodate 할 수 있도록 커야 한다.

이것이 optimal size라고 알려져 있다. (예 메모리 소트)

HOW TO TUNE PGA_AGGREGATE_TARGET (중요하다)

1.1.1 First

- For OLTP systems
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 20%

- For DSS systems
PGA_AGGREGATE_TARGET = (<Total Physical Memory > * 80%) * 50%

1.1.2 사용 가능한 PGA 통계를 이용

PGA_AGGREGATE_TARGET 의 under size 와 over size를 보아라. (위에 있다.)

SELECT * FROM V$PGASTAT

NAME                                                  VALUE
-----------------------------------------------------------------
aggregate PGA target parameter                     524288000 bytes
aggregate PGA auto target                         463435776 bytes
global memory bound                                    25600 bytes
total PGA inuse                                      9353216 bytes
total PGA allocated                                 73516032 bytes
maximum PGA allocated                              698371072 bytes
total PGA used for auto workareas                          0 bytes
maximum PGA used for auto workareas                560744448 bytes
total PGA used for manual workareas                        0 bytes
maximum PGA used for manual workareas                      0 bytes
over allocation count                                      0 bytes
total bytes processed                              4.0072E+10 bytes
total extra bytes read/written                     3.1517E+10 bytes
cache hit percentage                              55.97 percent

(a) aggregate PGA auto target: automatic mode에서 work area를 위해 사용할 수 있는 최대 메모리 영역 , 메모리의 이 부분은 PGA 메모리의 tunable 부분이 된다. 예를 들어서 집중적인 SQL Operator ( hash join, group by, sorts, bitmap merge and bitmap index create)

를 위해서 할당된다. 또 다른 부분인 untunable 영역은 예를 들어 : 각 세션과 각 open/active 커서에 대한 context 정보 ,pl/sql or java memory) 따라서 이 값은 PGA_AGGREGATE_TARGET 보다 작다. 하지만 커질 수 있다.

(b) total PGA used for auto workarea : 시스템에 의해서 사용된 실질적인 tunable PGA 메모리 정보를 준다. “maximum PGA used for auto workareas” 는 인스턴스 시작 이후 이전 통계에서 사용되었던 최대 값을 보여 준다.

Oracle 920 only

(d) over allocation count : PGA_AGGREGATE_TARGET 의 값이 너무 작아서 untunable PGA 메모리 공간 + work area를 실행하기 위한 최소 메모리에 부합하지 않는 다면 PGA over allocating은 발생 한다.이것이 일어나면, PGA_AGGREGATE_TARGET는 의미가 없어진다. 그리고 extra 메모리 할당이 필요하게 된다. Over allocation count는 인스턴스가 시작된 이후 이런 상태가 몇 번 발생 했는지 보여준다. 이상적은 값은 0 이다.

(e) cache hit percentage : 이런 공식은 PGA 메모리 컴포넌트의 성능을 반영하여 계산 된다.

인스턴스 시작이후 계산되며, 이 값이 100%라는 것은 모든 work area가 PGA 메모리의 optimal 량을 사용한 것이다. 그러니 이것은 사실상 단순한 OLTP를 제외하고 발생 하지 않는다.

예를 들어 1MB 3회 sort + 100M 1회 sort==è 103M 4회

여기서 1mb가 one pass 하면 hit는 99.3 ::::::100m가 one pass 하면 hit는 50.7% 이다.

total bytes processed * 100
PGA Cache Hit Ratio = --------------------------------------------
(total bytes processed + total extra bytes read/written)

■ V$SQL_WORKAREA_HISTOGRAM(oracle 920 only)

이 뷰는 optimal memory size, one pass size, multi pass 메모리 크기를 가지고 실행된 work area의 수를 보여준다. 이 뷰는 다시 work area의 최적 메모리 요구에 의해서 정의된 bucket로 세분화 된다. 각 bucket는 LOW_OPTIMAL_SIZE 와 HIGH_OPTIMAL_SIZE로 최적의 메모리 요구 범위를 나타낸다.

SELECT LOW_OPTIMAL_SIZE/1024 low_kb,(HIGH_OPTIMAL_SIZE+1)/1024 high_kb,
optimal_executions, onepass_executions, multipasses_executions
FROM v$sql_workarea_histogram
WHERE total_executions != 0;

Example :

LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
------ ------- ------------------ ------------------ ----------------------
8      16      156255             0                   0
16     32      150                0                   0
32     64      89                 0                   0
64     128    13                 0                   0
128    256     60                 0                   0
256    512     8                  0                   0
512    1024    657                0                   0
1024   2048    551                16                  0
2048   4096    538                26                  0
4096   8192    243                28                  0
8192   16384   137                35                  0
16384 32768   45                 107                 0
32768 65536   0                  153                 0
65536 131072 0                  73                  0
131072 262144 0                  44                  0
262144 524288 0                  22                  0

1024 KB TO 2048 KB는 optimal 하게 사용된 메모리는 551 bucket이며, 16번의 one pass,0 번의

multi pass가 발생했다. 이것은 또한 1MB 이하의 모든 work area가 optimal mode로 실행될 수 있

다는 것이다. 또한 이 뷰는 optimal memory size, one pass size, multi pass 메모리 크기를 가지

고 실행된 work

area 의 %를 보여준다.

Example :

SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
       onepass_count, round(onepass_count*100/total, 2) onepass_perc,
       multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
       (SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
               sum(OPTIMAL_EXECUTIONS) optimal_count,
               sum(ONEPASS_EXECUTIONS) onepass_count,
               sum(MULTIPASSES_EXECUTIONS) multipass_count
        FROM   v$sql_workarea_histogram
        WHERE low_optimal_size > 64*1024);   ---- for 64 K optimal size

■ V$SQL_WORKAREA_ACTIVE(oracle 920 only)

이 뷰는 현재 ACTIVER 하거나 실행하는 모든 work area를 보여준다. 그러나 (64k) 이하는 이

뷰에 포함 되지 않는다. 따라서 active 한 work area가 temporary segment에 spill이 일어나는

여부를 판단 할 수 있다.

Example :

SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
       operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
       trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
       NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;

SID OPERATION         ESIZE     MEM       MAX MEM    PASS TSIZE
--- ----------------- --------- --------- --------- ----- -------
8   GROUP BY (SORT)   315       280       904         0
8   HASH-JOIN         2995      2377      2430        1   20000
9   GROUP BY (SORT)   34300     22688     22688       0
11 HASH-JOIN         18044     54482     54482       0
12 HASH-JOIN         18044     11406     21406       1   120000

위의 결과에서 session 12(column SID)은 hash join을 실행하고 있으며, one pass(PASS column)가 발생하였으며, 현재 11406 KB 메모리를 사용하고(MEM column) 그리고 과거에 21406 KB 까지 최대 사용하였다.(MAX MEM) 또한 120000 KB 크기로 temporary segment 를 사용한다.

마지막으로 ESIZE는 이 hash join을 실행하기 위한 최대 메모리를 나타낸다. 이 최대 메모리는

workload에 따라서 동적 변경 된다.

연관된 SQL 실행이 종료 했을 때 work area는 해제 되고 v$sql_workare_active 에 제거 된다.

1.1.3 PGA_AGGREGATE_TARGET를 튜닝하기 위한 새로운 두개의 view.

- V$PGA_TARGET_ADVICE

- V$PGA_TARGET_ADVICE_HISTOGRAM

위의 두 뷰를 조사하여 최적을 PGA_AGGREGATE_TARGET를 구할 수 있다.

ADVICE 기능을 활성화 시키기 위해서 다음 파라메터를 수정한다.

- PGA_AGGREGATE_TARGET

- STATISTICS_LEVEL : 이것을 TYPICAL(디폴트) 또는 ALL로 세팅한다.

(alter session, system) 이 값이 BASIC 이면 기능은 비활성화 된다.

뷰에 값은 PGA_AGGREGATE_TARGET 값이 ALTER 되거나 instance start up 되면 새롭게 reset.

V$PGA_TARGET_ADVICE는 cache hit percentage 와 v$pgastat의 over allocation count에 얼마나 영향을 미칠 것인지 추측할 수 있도록 한다.

SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM   v$pga_target_advice;

TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
63         23             367
125        24             30
250        30             3
375        39             0
500        58             0
600        59             0
700        59             0
800        60             0
900        60             0
1000       61             0
1500       67             0
2000       76             0
3000       83             0
4000       85             0

위 결과에서 모든 OVER ALLOCATION을 피하기 위해서는 PGA_AGGREGATE_TARGET을 375 로 한다

( ESTD_OVERALLOC_COUNT = 0)

Over allocation을 제거한 후 cache hit percentage를 최대로 하여야 한다.

COMMON ISSUES

==============

1- PGA_AGGREGATE_TARGET 와 WORKAREA_SIZE_POLICY를 설정시 *_area_size는 무시된다.

그리고 오라클은 자동으로 그런 값을 계산 한다.

2- 오라클 8i 와 그 이전에서 PGA는 고정 값이었다. 따라서 한번 PGA에 할당된 값은 해당 프로세스 죽거나 EXIT를 하지 않을 경우 OS에 반환 하지 않는다. 그러나 OS와 메모리 과부하는 그 프로세스에 속해 있는 사용되지 않은 공간을 SWAP 영역에 page out을 할지 결정한다. ( 즉 과부하시 반환한다.)

오라클 9i 부터 프로세스로부터 사용되지 않는 PGA 공간을 해제 할 수 있다.그래서 다른 프로세스는 이 영역을 사용할 수 있다.

3- Automatic PGA 메모리 관리는 오라클 프로세스가 사용 할 수 있는 리소스를 제한 하는데 도움이 되며 더욱 효과적으로 사용할 수 있도록 한다.

4- OS 제한에 걸리지 않는다면, ora-4030 에러 발생을 줄일 수 있다.

ORA-04030out of process memory when trying to allocate num bytes (str)

Cause : Operating system process private memory has been exhausted.

Action: See the database administrator or operating system administrator to increase process memory quota. There may be a bug in the application that causes excessive allocations of process memory space.

5- 컬럼 ESTD_OVERALLOCATION_ACCOUNT in V$PGA_TARGET_ADVICE 의 값이 0 이 아니라면

PGA_AGGREGATE_TARGET 값이 매우 작다는 것이다. 따라서 PGA_AGGREGATE_TARGET를 over allocation zone으로 설정하는 것은 메모리 관리에 있어서 over-allocation이 발생하고 실제로 사용자가 제한한 값보다 더 사용할 수 있다는 것이다. 따라서 over-allocation zone으로 설정하는 것은 의미가 없다.

6- 오라클 8i 보다 9i의 SQL LOADER가 더 느리다는 보고가 있는데, PGA_AGGREGATE_TARGET는 이 문제를 해결 했다.

7- PGA_AGGREGATE_TARGET in 9.0.1 on HP-UX(11.0) 은 OS panic를 만든다. 이것은 bug: 2122307 이다.

2. 초보 오라클

1.2 SQL TIP

2.1.1 Deptno 순으로 시퀀스 부여

1. Emp table의 deptno 컬럼에 인덱스를 추가한다.

Create index I_emp on emp(deptno);

2. table에 시퀀스 컬럼을 추가한다.

Alter table emp add (seq1 number);

3. update emp

set seq1= rownum

where deptno > ‘0’

2.1.2 각 부서별 시퀀스 부여

update emp a

set a.seq1= (select a.seq1- min(b.seq1) + 1

from emp b

where b.deptno= a.deptno);

2.1.3 salary를 1위부터 5위까지 구하기

select * from emp a

where 5 > (select count(*) from emp b

where b.salary > a.salary)

order by sal desc;

2.2 SPACE 가 포함된 CHAR 컬럼을 VARCHAR2로 변환

1) export/import는 space를 제거하지 못함,

2) create table as select 또한 space 제거 못함

3) 유일한 방법은 rtrim함수를 사용하는 것이다.

1. datatype이 varchar2를 가지는 dummy 테이블 생성

2. insert into dummy select rtrim(char_col) from original_table;

2.3 한글 DATA 조회시 ???? 로 출력되는 경우

create database 시 character set 으로 저장되기 때문에 다른 character set으로 조회시

??? 발생한다. Database에 대한 re-create 없이 character set을 변경 할 수 없다.

현재 저장된 character set 확인 방법

select * from DBA_DATABASE_PARAMETERS;

또한 system 환경 변수도 database character set과 같아야 한다.

Unix : .profile 확인

Window: oracle.ini 확인

2.4 LONG COLUMN을 가진 테이블이 COPY

long column을 가진 테이블을 copy를 해야 하는 경우 long_column은 sub-query를 사용 할 수 없다. 즉 create table as select, insert into select 시 ora-00997 에러 발생.

따라서 long column 이 32k 이하일 경우 pl/sql을 이용하고 그 이상이면 3GL을 사용한다.

<table>

create table longtab(no number(2), text long);

declare

string varchar2(32000);

no number(2);

begin

select no, longcolumn into no, string from longtable;

insert into longtab values(no, string);

end;

2.5 날짜 변환 예제

select /* 오늘날짜 시분 초 포함 */ to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')

select /* 오늘날짜 00시 00분 00초 */ to_char(trunc(sysdate),'yyyy/mm/dd hh24:mi:ss')

select /* 오늘날짜 00시 00분 00초 */ to_char(trunc(sysdate,'dd'),'yyyy/mm/dd hh24:mi:ss')

select /* 이번 달 1일 00시 00분 00초 */ to_char(trunc(sysdate,'mon'),'yyyy/mm/dd hh24:mi:ss')

select /* 올해 1월 1일 00시 00분 00초 */ to_char(trunc(sysdate,'year'),'yyyy/mm/dd hh24:mi:ss')

select /* 올해 1월 1일 00시 00분 00초 */to_char(to_date('2002','yyyy'),'yyyy/mm/dd hh24:mi:ss')

select /* 2월 1일 00시 00분 00초 */ to_char(to_date('200202','yyyymm'),'yyyy/mm/dd hh24:mi:ss')

select /* 2월 2일 0시 0분 0초 */

to_char(to_date('20020202','yyyymmdd'),'yyyy/mm/dd hh24:mi:ss')

select /* 2월 2일 0시 0분 1초 */

to_char(to_date('20020202','yyyymmdd')+1/68400,'yyyy/mm/dd hh24:mi:ss')

select /* 2월 2일 00시 00분 00초 -> 한달뒤*/

to_char(add_months(to_date('20020202','yyyymmdd'),1),'yyyy/mm/dd hh24:mi:ss')

2.6 그룹별로 하나의 로우만 가져오기

///////////////////////////////////////////////////////

//그룹별로 하나의 row만 가져오기..

///////////////////////////////////////////////////////

test1 table 의 내용(emp+dat+code+seq가 기본키 이다)

emp dat code seq amt aa

1 3/12 a 1 100 1

1 3/12 b 1 100 2

2 3/12 a 1 200 0

2 3/12 c 1 300 2

2 3/12 c 2 300 1

위 데이타에서 emp+dat로 그룹한 데이터 중에서 amt이 가장 큰 row를 select 해라...

결과 내용

emp dat code seq amt aa

1 3/12 b 1 100 2

2 3/12 c 2 300 1

(1)

select t1.emp, t1.dat, t1.code, t1.seq, t1.amt, t1.aa

from test1 t1

where to_char(t1.amt,'0000000000')||t1.code||t1.seq =

(select max(to_char(t2.amt,'0000000000')||t2.code||t2.seq)

from test1 t2

where t1.emp = t2.emp and t1.dat = t2.dat)

order by emp,dat;

(2)또는 다음과 같이 한다. (1)보다 훨씬 속도가 빠르다.

select t1.emp, t1.dat, t1.code, t1.seq, t1.amt, t1.aa

from test1 t1,

( select emp,dat,max(to_char(amt,'0000000000')||code||seq) as diff

from test1

group by emp,dat) v1

where t1.emp = v1.emp

and t1.dat = v1.dat

and to_char(t1.amt,'0000000000')||t1.code||t1.seq = v1.diff

order by t1.emp,t1.dat;

(3)또는 다음과 같이 한다.

select t2.emp, t2.dat, t2.code, t2.seq,t2.amt , t2.aa

from test1 t2,

( select v2.emp, v2.dat, v2.maxcode,max(t1.seq) maxseq

from test1 t1,

( select v1.emp, v1.dat, max(t1.code) maxcode

from test1 t1,

(select emp,dat,max(amt) as maxamt

from test1

group by emp,dat) v1

where t1.emp = v1.emp

and t1.dat = v1.dat

and t1.amt = v1.maxamt

group by v1.emp,v1.dat ) v2

where t1.emp = v2.emp

and t1.dat = v2.dat

and t1.code = v2.maxcode

group by v2.emp,v2.dat,v2.maxcode

) v3

where t2.emp = v3.emp

and t2.dat = v3.dat

and t2.code = v3.maxcode

and t2.seq = v3.maxseq;

2.7 TOP N QUERY

2.7.1 중복 로우 제거

ID NUM

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

1 1

2 2

3 3

4 4

5 1

6 1

자 위에서 보다시피, 테이블의 ID컬럼은 primary key가, NUM컬럼은 데이터가 들어있는 형태이다. 그런데, 이 테이블에는 NUM컬럼의 데이터가 중복되어 들어가 있다. 즉, ID=1일 때 NUM=1이며, ID=5일 때와 ID=6일 때도 NUM값은 1이다. 이제 NUM필드에 중복된 값을 모두 삭제해버리도록 해보자. 즉, NUM값이 1인 row를 어느것이든 한 개만 남기도록 하는 것이다.

select * from temp t1, temp t2

where t1.rowid > t2.rowid

and t1.num = t2.num

ID NUM ID NUM

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

5 1 1 1

6 1 1 1

6 1 5 1

이 쿼리는 중복된 row를 찾아준다. 따라서 이 쿼리를 사용하면 간단하게 중복된 데이터를 삭제할 수 있을 것이다.

그러나 위의 쿼리는 중복된 데이터를 중복되게 찾아 준다. 즉, 중복된 값을 가지고 있는 row가 5,6,6으로 6이 2회 나타난다.

select * from temp t1

where t1.rowid > (select min(rowid)

from temp where t1.num = num)

ID NUM

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

5 1

6 1

2.7.2 SAL 큰 순서로 10개 로우를 구해라.

IN ORACLE 8I

SELECT SAL FROM

(SELECT SAL FROM EMP

ORDER BY SAL DESC) E

WHERE ROWNUM <= 10;

IN ORACLE 7, 8 ( 서브 쿼리에서 ORDER BY 사용을 못한다.)

SELECT EMPNO, ENAME, ABS(SAL) FROM

( SELECT E.EMPNO, -E.SAL SAL, MAX(E.ENAME) ENAME FROM EMP E

GROUP BY -E.SAL, E.EMPNO

)

WHERE ROWNUM <= 10

group by를 사용해 e.sal으로 묶어준다. 그러나 이렇게만 묶어버리면 sal이 중복되는 row들이 사라져버린다. 따라서 여기서는 EMPNO(EMP테이블의 primary key이다)까지 묶어서 group by를 해주었다. group by 는 desc 구문이 존재하지 않으므로 sal이 큰 순으로 나오려면 –E.SAL 형태로 group by를 해주어야 한다. 그리고 group by에 나타나지 않은 컬럼들인 E.ENAME과 은 단순히 MAX()함수를 취해주었다. 이것은 항상 그룹이 단일 row단위로 들어오므로 언제나 단순히 E.ENAME을 돌려준다.

2.7.3 SAL 큰 순서로 10등까지 구해라.

SELECT EMPNO, ENAME, SAL FROM EMP E1

WHERE ( SELECT COUNT(1) FROM EMP E2

WHERE E1.SAL < E2.SAL ) <= 10

ORDER BY SAL DESC

2.7.4 부서별 N등까지를 구하라

우리가 하고자 하는 것은 각 부서별로 2등까지(두번째 까지가 아니다)의 사람을

구하는 것이다.

SELECT * FROM

(SELECT DNAME, EMPNO, ENAME, SAL,

RANK() OVER ( PARTITION BY E.DEPTNO ORDER BY SAL DESC ) RNK

FROM DEPT D, EMP E

WHERE D.DEPTNO = E.DEPTNO )

WHERE RNK <= 2

바로 이 부분이 핵심인데 그 중에서도 핵심은

RANK() OVER ( PARTITION BY E.DEPTNO ORDER BY SAL )의 의미를 파악하면 이 쿼리를 완전히 아는 것이다. 먼저 RANK()함수는 순위를 구하는 함수이며 PARTITION 은 부분을 나누는 구문이다. 그리고 ORDER BY는 각 PARTITION(부분)내에서의 정렬을 위한 컬럼을 명시하는데 쓰인다.

즉 위의 코드는 E.DEPTNO 를 기준으로 파티션들을 분할하고 그들 각각을 SAL에 의해서 역순으로 정렬한 순위를 얻어오라는 것이다.

2.8 HINT

/*+ ALL_ROWS */ : 전체적인 자원소비 차원에서 CBO 선택

/*+ CHOOSE */ : 통계정보 여부에 따라 CBO OR RBO 선택

/*+ FIRST_ROWS */ : 가장 빠른 응답시간 차원에서 CBO 선택

/*+ RULE */ : RBO 선택

접근 방법

/*+ USE_CONCAT */ : UNION ALL 형식으로 푼다.

/*+ STAR */ : 큰 테이블과 작은 테이블간 nested-loop 형식으로 join 한다.

Join operation

/*+ driving_site(table) */ : 원격 테이블이 선행 테이블이 되도록 한다.

Parallel execution

/*+ APPEND */ , /*+ NOAPPEND */ : 오직 INSERT에서 가능하다. 데이터를 단순히 테이블에 append

할 것인지 아닌지 선택한다 : append일 경우 free space가 존재

하지만 사용하지 않고 새로운 공간을 사용한다.

/*+ PARALLLE(table, instance) */ : 세션에서 parallel dml이 가능해야 한다.

(alter session set parallel DML enable;)

/*+ parallel_index */ : 파티션 이거나 아니거나 index fast full 스캔을 가능하게 한다.

[/*+ index_ffs (table index) */ ]

/*+ cache */ :full scan 시에 밀려나는 것을 막기 위해서 lru 알고리즘을 따르도록 한다.

/*+ merge(table) */ :서브쿼리와 complex 뷰를 원래 테이블과 merge 한다.

/*+ push_subq */ : 플랜을 만들 때 서브쿼리를 가장 먼저 수행하도록 한다.

2.9 INIT PARAMETER (8개)

■ DB_BLOCK_BUFFERS

■ SHARED_POOL_SIZE

Dictionary cache 는 데이터 베이스 기동 직후에는 캐쉬영역에 데이터가 저장되지 않기 때문에 hit 율이 낮다

Library cache : 공유 SQL 영역과 PL/SQL 영역으로 나누어진다.

SELECT name, bytes/1024/1024 “size (MB)” from v$sgastat

Where name=’free memory’;

■ SORT_AREA_SIZE

Dedicate : sort area는 PGA에 할당

PGA: session-information (user-privilege),

cursor-status(of that session)

stack-space(session-variable)

MTS : sort area는 UGA에 할당

UGS는 SGA내 Shared pool 안에 존재 한다.

첫번째 sort 시 메모리 할당의 점차 증가되면서 sort_area_size 가 될 때 까지 증가한다. SORT 가 끝난 후 다음 SORT를 위해서 sort_area_retained_size 만큼 줄어 든다.

디폴트로 이 두 값은 같다. 즉 sort를 위한 메모리가 그대로 다음 sort를 위해서 남는 다는 의미이며, dedicate 에서 process가 사라질 때 비로소 o/s에 반환 하며,

MTS 에서는 shared pool에 존재 한다.

사용자 생성시 Temporary tablespace는 타입이 PERMANENT 일 수 있다.

또한 temporary segment는 permanent, temporary type에 모두 저장 가능하다

그러나 temporary seg를 permanent에 저장시 다음과 같은 단점이 있다.

1) 모든 temporary seg는 트랜잭션 레벨에서 permanent에 저장될 수 있다.

2) Temporary seg의 반복적인 할당 해제는 tbs에 대한 fragment를 유발한다.

3) Smon은 구문이 완성 되었을 때 temporary seg를 해제해야 한다.

Locally tbs의 uniform 이 72k 이고 block size 8k라면 9개의 block으로 extent는

구성 되며 각 bit는 하나의 extent(72k)를 나타낸다.

8i 에서부터 locally 에 temporary seg를 생성 하기 위해서는 반드시 uniform size를

사용해야 한다.

Dictionary tbs의 경우 permanent type을 temporary type으로 변경 할 수 있다.

그러나 해당 tbs에 permanent object가 없어야 한다.

locally로 관리 되는 tablespace는 다양한 크기의 extent를 가질 수 있다. 이것은 tablespace 생성시 uniform 또는 autoallocate(system 관리) 로 하여 가능하다.

즉 uniform은 동일한 크기.

Locally tablespace는 dictionary 비해서 recursive call이 적으며, free extent에 대한 coalesce 가 불필요 하다. Tbs 생성시 지정할 수 있으며, alter로 변경 불가능 하다.

■ DBWR_IO_SLAVES

이 파라미터는 오라클 8 이전의 DB_WRITERS를 대체한다. 오라클 8에서는 DB_WRITER_PROCESS가 DB_WRITERS를 대체 하지만 DBWR_IO_SLAVES 파라미터와 함께 사용할 경우 문제가 발생할 수 있다.

즉 DB_WRITER_PROCESS 와 DBWR_IO_SLAVES는 동시에 둘을 사용할 수 없다.

■ ROLLBACK_SEGMENTS

개수와 관련한 모니터 방법

Select a.name, b.extents, b.rssize , b.xacts, b.waits, b.gets, optsize, status

From v$rollname a , v$rollstat b

Where a.usn=b.usn;

Xacts 가 계속해서 1 이상인 경우 rollback segment 개수를 늘려주는 것이

contention을 발생할 가능성을 줄여주며, wait개수가 0 보다 크고 특별한 사항에서만

나타나는 것이 아니라면 rollback segment 개수를 늘려준다.

■ SORT_AREA_RETAINED_SIZE

소트 종료 후 메모리 상에 유지되는 소트 공간으로 세션이 exit 하여야 사라진다.

■ DB_BLOCK_LRU_EXTENED_STATISTICS

Alter system 으로 변경된 buffer cache 값으로 시뮬레이션이 된다.

그러나 여러 문제점으로 인하여 사용하지 않도록 권고한다.

■ SHARED_POOL_RESERVE_SIZE

Large object를 위한 공간을 미리 확보하는 것으로 shared_pool_size에 5%로 지정한다. (단위 bytes)

2.10 TABLE 변경과 VIEW(WITH CHECK OPTION) 관계

1) with check option을 가지고 테이블 생성

create view v_test as select * from emp where empno=1111 with check option;

insert into v_test(empno) values(1233); =============è오류 발생

2) 테이블 rename

create view v_test as select * from emp;

rename emp to emp_back;

select * from v_test; =========è 에러 발생

3) 테이블 삭제

create view v_test as select * from emp;

drop table emp;

select * from v_test; ========è오류 발생

2.11 TOW PHASE COMMIT

1. Two-Phase Commit 이란?

(1) Two-Phase Commit은 Application Programmer가 별도의 Program없이

Global Database의 Integrity를 보장하는 기법이다.

(2) Two-Phase Commit 은 분산 환경 Database(DDB)를 사용하는 경우에

사용된다. 이는 Oracle V7.0이후 Version에서 제공된다.

2. Two-Phase Commit의 구성

- Two-Phase Commit은 Prepare Phase와 Commit Phase로 나누어 진다.

(1) Prepare Phase

Global Coordinator(분산 Transaction을 일으킨 Node)가 분산 Transaction에

참석한 Node들에 대해 Commit이나 Rollback을 수행해도 되는가를

확인하는 단계이다.

(2) Commit Phase

분산 Transaction에 참여한 Node가 Global Coordinator에게 분산

Commit해도 좋다는 Response(즉 Prepare 되었음)를 받고 Transaction을

Commit한다. 어떠한 Node라도 Prepare가 안되었다는 Response가 있으면

Transaction을 Rollback한다.

3. Two-Phase Commit의 수행

(1) Prepare Phase에 대한 Response는 Prepared, Read-Only, Abort가 있다.

A. Prepared는 Data가 정상적으로 수정되었으며 Prepare되었음을 의미한다.

B. Read-Only 는 해당Node에 Data의 수정이 없었다는 의미이다.

C. Abort는 Prepare되지 않았음을 의미한다.

(2) 모든 Node에서 Prepared가 되면 Commit Phase가 수행된다.

그러나 한 Node라도 Abort되면 Commit Phase에서 전 Node에 Rollback을

수행한다.

select sql_text

from v$sqltext a, v$session b

where a.hash_value = b.sql_hash_value

and logon_time <=

to_date(to_char(sysdate-1/144, 'yyyymmdd hh24:mi:ss') ,'yyyymmdd hh24:mi:ss')

and b.status= 'ACTIVE'

order by a.address, a.piece
2.12 CONNECT 이 안끊어지는 SQL 찾기

2.13 EXPORT/IMPORT 시 TABLE , INDEX 분리

import 시 indexes=n 으로 하면 인덱스는 생성되지 않는다.

Import 시 indexfile=sample.sql 이라고 하면 sample.sql 안에 인덱스 스크립트가 생성 되어 있고, 테이블 생성 script은 REM으로 막혀 있게 된다. 따라서 결과적으로 테이블 script도 없을 수 있다.

단 import 시 indexes=n 으로 하면 constraint 로 인해 생긴 index는 그대로 따라가게 되기 때문에 rebuild를 이용하여 분리해야 한다.

Alter index index_name rebuild tablespace tablespace_name;

2.14 EXCEPTION 테이블을 사용하여 중복된 데이터를 찾는 방법

2.14.1 절차

1. alter table dept add constraint un_deptno unique(deptno) disable;

disable을 하지 않으면 에러 난다고 가정(즉 unique 하지 못한 경우)

2. alter table dept enable constraint un_deptno exceptions into exception;

만약 unique 하다면 에러가 없고 있다면 exception 테이블에 저장된다.

ORA-02299: cannot enable(owner.constraint) –duplicate keys found

exception 테이블 생성 스크립($ORACLE_HOME/rdbms/admin/utlexcpt.sql)

3. SQL> SELECT * FROM exceptions;

4. ROW_ID OWNER TABLE_NAME CONSTRAINT

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

000024BB.0005.0005 KHLEE DEPT UN_DEPTNO

000024BB.0002.0005 KHLEE DEPT UN_DEPTNO

5. 자세한 sql을 이용하여 제약 조건에 위배되는 row들을 확인할 수 있다.

SQL> SELECT deptno, dname, row_id

FROM dept, exceptions

WHERE exceptions.constraint = 'UN_DEPTNO'

AND dept.rowid = exceptions.row_id;

DEPTNO DNAME ROW_ID

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

30 XX 000024BB.0005.0005

30 SALES 000024BB.0002.0005

6. 해당 data를 확인한 후 제약조건에 위배되는 자료를 삭제한다.

SQL> DELETE FROM dept

WHERE deptno = 30

AND dname = 'XX';

7. 해당 constraint를 enable한다.

SQL> alter table dept enable constraint un_deptno;

3. DBA checklists

3.1 일일 체크리스트

1) 인스턴스 상태 체크

OEM ‘probe’ event 활용가능

2) alert.log 확인

3) dbsnmp running 확인

- ps –ef|grep dbsnmp (일부 싸이트는 특정 목적을 위해서 이것을 disable 한다)

4) backup 상태 확인

5) archive 성공 여부 확인

6) 리소스 확인

A.free space in tablespace

- free mb check in tablespace free.sql 실행

- free percentage check in tablespace space.sql

B. rollback segment 확인

- optional : 각 RBS는 ONLINE 이어야 한다 (특정 목적에 의해서 OFFLINE 가능)

- 상태 확인은 V$ROLLSTAT를 통해서 한다.

- STORAGE 확인은 DBA_ROLLBACK_SEGS를 통해서 한다. 여기서 STATUS가 실제로

PENDING OFFLINE, FULL 이더라도 OFFLINE, ONLINE으로 보여진다.

C. 성장율을 확인

- 일별 성장율 확인 analyze5pct.sql

- 현재 extent 확인 nr_extents.sql

- 테이블 크기 정보 질의

- 인덱스 크기 정보 질의

- 성장 trend 질의

D.space에 기반한 오브젝트 질의

- object의 next extent가 그 테이블 스페이스의 가장 큰 extent 보다 크다면 에러.

- 이러한 오브젝트 선별한 후 해당 테이블 스페이스를

- alter tablespace <tablespace> coalesce;

spacebound.sql을 실행해서 모든 값이 0 이라면 양호한 상태이다.

E. cpu, memory, network 또는 disk 리소스 경합을 조사한다.

7) standby database의 archive log를 copy하고 적용해라

3.2 야간 절차

1) 데이터 크기 측정

- 스키마 분석과 데이터 측정

- 측정 위한 테이블 생성 mk_volfact.sql

- 크기 정보 추출 analyze_comp.sql

- 통계 수집 pop_vol.sql

3.3 주간 체크리스트

1) rule을 어긴 object 찾기

- 해당 object가 속한 테이블 스페이스의 default next extent를 따르는 것이 좋다.

Nextext.sql

- 기존의 extent를 검사 existext.sql

- 모든 테이블은 primary key를 가져야 한다. No_pk.sql, disable pk dispk.sql

- 모든 primary key는 unique 해야 한다. Nonupk.sql

- 모든 인덱스는 인덱스 테이블 스페이스에 존재해야 한다. Mkrebuil_idx.sql

- ops 환경에서 각 스키마는 동일한 datatype을 가져야 한다. Datatype.sql

- ops 환경에서 동일한 스키마는 스키마 타입도 같아야 한다. Obj_coord.sql

3.4 월간 체크리스트

1) 데이터 증가량 검사

2) tuning point 검사 (hit ratio, latch contention)

3) lock i/o contention

4) fragmentation (row chain)

A. Daily Procedures

Free.sql

-- free.sql

-- To verify free space in tablespaces

-- Minimum amount of free space

-- document your thresholds:

-- <tablespace_name> = <amount> m

SELECT tablespace_name, sum ( blocks ) as free_blk ,

trunc(sum(bytes) /(1024*1024) ) as free_m,

max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks

FROM dba_free_space

GROUP BY tablespace_name

Space.sql

-- space.sql

-- To check free, pct_free, and allocated space within a tablespace

-- 11/24/98

SELECT tablespace_name, largest_free_chunk

, nr_free_chunks, sum_alloc_blocks, sum_free_blocks

, to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%'

AS pct_free

FROM ( SELECT tablespace_name

, sum(blocks) AS sum_alloc_blocks

FROM dba_data_files

GROUP BY tablespace_name),

( SELECT tablespace_name AS fs_ts_name

, max(blocks) AS largest_free_chunk

, count(blocks) AS nr_free_chunks

, sum(blocks) AS sum_free_blocks

FROM dba_free_space

GROUP BY tablespace_name )

WHERE tablespace_name = fs_ts_name

analyze5pct.sql

-- analyze5pct.sql

-- To analyze tables and indexes quickly, using a 5% sample size

-- (do not use this script if you are performing the overnight

-- collection of volumetric data)

-- 11/30/98

BEGIN

dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ;

END ;

nr_extents.sql

-- nr_extents.sql

-- To find out any object reaching <threshold>

-- extents, and manually upgrade it to allow unlimited

-- max_extents (thus only objects we *expect* to be big

-- are allowed to become big)

-- 11/30/98

SELECT e.owner, e.segment_type , e.segment_name ,

count(*) as nr_extents , s.max_extents,

to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90')

as MB

FROM dba_extents e , dba_segments s

WHERE e.segment_name = s.segment_name

GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents

HAVING count(*) > &THRESHOLD

OR ( ( s.max_extents - count(*) ) < &&THRESHOLD )

ORDER BY count(*) desc

spacebound.sql

-- spacebound.sql

-- To identify space-bound objects.

-- If all is well, no rows are returned.

-- If any space-bound objects are found, look at value of NEXT extent

-- size to figure out what happened.

-- Then use coalesce (alter tablespace <foo> coalesce;).

-- Lastly, add another datafile to the tablespace if needed.

-- 11/30/98

SELECT a.table_name, a.next_extent, a.tablespace_name

FROM all_tables a,

( SELECT tablespace_name, max(bytes) as big_chunk

FROM dba_free_space

GROUP BY tablespace_name ) f

WHERE f.tablespace_name = a.tablespace_name

AND a.next_extent > f.big_chunk

B. Nightly Procedures

mk_volfact.sql

-- mk_volfact.sql

-- (only run this once to set it up; do not run it nightly!)

-- -- Table UTL_VOL_FACTS

CREATE TABLE utl_vol_facts

(

table_name VARCHAR2(30),

num_rows NUMBER,

meas_dt DATE

)

TABLESPACE platab

STORAGE (

INITIAL 128k

NEXT 128k

PCTINCREASE 0

MINEXTENTS 1

MAXEXTENTS unlimited

)

-- Public Synonym

CREATE PUBLIC SYNONYM utl_vol_facts FOR &OWNER..utl_vol_facts

-- Grants for UTL_VOL_FACTS

GRANT SELECT ON utl_vol_facts TO public

analyze_comp.sql

-- analyze_comp.sql

BEGIN

sys.dbms_utility.analyze_schema ( '&OWNER','COMPUTE');

END ;

pop_vol.sql

-- pop_vol.sql

insert into utl_vol_facts

select table_name

, NVL ( num_rows, 0) as num_rows

, trunc ( last_analyzed ) as meas_dt

from all_tables -- or just user_tables

where owner in ('&OWNER') -- or a comma-separated list of owners

commit

C. Weekly Procedures

nextext.sql

-- nextext.sql

-- To find tables that don't match the tablespace default

-- for NEXT extent.

-- The implicit rule here is that every table in a

-- given tablespace should

-- use the exact same value for NEXT, which should

-- also be the tablespace's default value for NEXT.

-- This tells us what the setting for NEXT is for these objects today.

-- 11/30/98

SELECT segment_name, segment_type, ds.next_extent as Actual_Next

, dt.tablespace_name, dt.next_extent as Default_Next

FROM dba_tablespaces dt, dba_segments ds

WHERE dt.tablespace_name = ds.tablespace_name

AND dt.next_extent !=ds.next_extent

AND ds.owner = UPPER ( '&OWNER' )

ORDER BY tablespace_name, segment_type, segment_name

existext.sql

-- existext.sql

-- To check existing extents

-- This tells us how many of each object's extents differ in size from

-- the tablespace's default size.

-- If this report shows a lot of different sized extents, your free

-- space is likely to become fragmented. If so, this tablespace is a

-- candidate for reorganizing.

-- 12/15/98

SELECT segment_name, segment_type

, count(*) as nr_exts

, sum (DECODE(dx.bytes,dt.next_extent,0,1)) as nr_illsized_exts

, dt.tablespace_name, dt.next_extent as dflt_ext_size

FROM dba_tablespaces dt, dba_extents dx

WHERE dt.tablespace_name = dx.tablespace_name

AND dx.owner = '&OWNER'

GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent

No_pk.sql

-- no_pk.sql

-- To find tables without PK constraint

-- 11/2/98

SELECT table_name

FROM all_tables

WHERE owner = '&OWNER'

MINUS

SELECT table_name

FROM all_constraints

WHERE owner = '&&OWNER'

AND constraint_type = 'P'

disPK.sql

-- disPK.sql

-- To find out which primary keys are disabled

-- 11/30/98

SELECT owner, constraint_name, table_name, status

FROM all_constraints

WHERE owner = '&OWNER' AND status = 'DISABLED’ AND constraint_type = 'P'

nonuPK.sql

-- nonuPK.sql

-- To find tables with nonunique PK indexes. Requires that PK names

-- follow a naming convention. An alternative query follows that

-- does not have this requirement, but runs more slowly.

-- 11/2/98

SELECT index_name, table_name, uniqueness

FROM all_indexes

WHERE index_name like '&PKNAME%'

AND owner = '&OWNER' AND uniqueness = 'NONUNIQUE'

SELECT c.constraint_name, i.tablespace_name, i.uniqueness

FROM all_constraints c , all_indexes i

WHERE c.owner = UPPER ( '&OWNER' ) AND i.uniqueness = 'NONUNIQUE'

AND c.constraint_type = 'P' AND i.index_name = c.constraint_name

mkrebuild_idx.sql

-- mkrebuild_idx.sql

-- Rebuild indexes to have correct storage parameters

-- 11/2/98

SELECT 'alter index ' || index_name || ' rebuild '

, 'tablespace INDEXES storage '

|| ' ( initial 256 K next 256 K pctincrease 0 ) ; '

FROM all_indexes

WHERE ( tablespace_name != 'INDEXES'

OR next_extent != ( 256 * 1024 ) )

AND owner = '&OWNER'

datatype.sql

-- datatype.sql

-- To check datatype consistency between two environments

-- 11/30/98

SELECT

table_name,

column_name,

data_type,

data_length,

data_precision,

data_scale,

nullable

FROM all_tab_columns -- first environment

WHERE owner = '&OWNER'

MINUS

SELECT

table_name,

column_name,

data_type,

data_length,

data_precision,

data_scale,

nullable

FROM all_tab_columns@&my_db_link -- second environment

WHERE owner = '&OWNER2'

order by table_name, column_name

obj_coord.sql

-- obj_coord.sql

-- To find out any difference in objects between two instances

-- 12/08/98

SELECT object_name, object_type

FROM user_objects

MINUS

SELECT object_name, object_type

FROM user_objects@&my_db_link

3.5 안전한 데이터 베이스 운영

win98 용 “Oracle Net Service”를 설치한 Win98 시스템의 어플리케이션은 UNIX

시스템상에서 운영되는 오라클 데이터베이스에 운영체제 구애 없이 접근 할 수 있다.

① 서버의 리스너는 특정 포트로 들어오는 접속요구를 감시한다

(디폴트 포트는 1521/TCP)

② 클라이언트는 접속 하고자 하는 데이터베이스의 리스너에게 접속요구를 송신한다.

③ 원하는 데이터베이스는 네트워크 서비스명으로 지정한다.

④ 리스너는 수신한 접속 요구가 올바른지를 분석한다. 해당 접속요구가 올바르면,

일련의 접속 관련정보를 클라이언트에게 송신한다.

⑤ 클라이언트는 해당 접속 관련 정보를 이용하여 오라클 데이터베이스와

직접 통신한다.

■ connect : 접속 및 조회를 할 수 있는 권한

■ resource : 자원을 사용 할 수 있는 권한

■ Role 은 DBA 만 생성 할 수 있다.

■디폴트 사용자 아이디를 잠그고(lock) 기간만료(expire) 시켜야 한다.

■ DBCA를 이용하여 데이터베이스를 생성하면 다수의 디폴트 사용자 생성된다.

■ DBCA는 디폴트 사용자 아이디를 잠그고 기간만료를 수행한다 .

그러나 예외 유저가 있다. 따라서 암호를 변경 시켜야 한다.

Sys, system, scott, dbsnmp, outln, 그리고 3개의 jserv 사용자

■ 계정을 잠그고 기간 만료 시기는 명령어

SQL> alter user test account lock password expire;

■데이터 딕셔너리 보호하기

O7_dictionary_accessibility=false

라고 parameter를 정의하면 /as sysdba 만 딕셔너리를 수정할 수 있다.

즉 true 일 경우 drop any table 권한이 있다면 딕셔너리 삭제할 수 있기 때문이다.

딕셔너리를 조회해야만 하는 사용자는 select any dictionary 권한만 부여 하여 view

처럼 이용하게 할 수 있다.

■ Public 권한 그룹에서 불필요한 권한은 삭제한다.

즉 pl/sql 은 실행 권한이 있다면 그 pl/sql 내 권한이 부여된 것이기 때문에

UTL_SMTP, UTL_HTTP, UTL_TCP, UTL_FILE, DBMS_RANDOM 패키지

사용권한을 PUBLIC에서 제거한다.

■원격 인증 기능을 비활성화 한다.

(9I 기능으로 클라이언트 자체 OS가 인정한 사용자를 신뢰하는 기능)

REMOTE_OS_AUTHENT = FALSE (비활성)

즉 OS_AUTHENT_PREFIX 의 값을 가진 remote 클라이언트에 대한 접속을 허락하는 기능

OS와 연결한 사용자 관리를 행하는 기능이 있습니다. 이것은 V6에서도 같습니다.

이 기능을 고려한 경우, 다음과 같은 3가지 타입의 사용자가 존재한다고 말할 수 있습니다.

(1) OS의 사용자와는 관련 없는 사용자

이것이 원래 일반적인 ORACLE 데이타베이스의 사용자입니다. 여기까지의 내용은 모두 이 타입

의 사용자를 사용하여 쓰여져 있습니다. 다음과 같은 친숙한 커맨드에 의해 작성됩니다.

SQL> CREATE USER username IDENTIFIED BY password;

(2) OS의 사용자와 연결한 패스워드를 가지고 있지 않은 사용자

이 타입의 사용자를 작성하기에는 다음의 커맨드를 실행합니다.

SQL> CREATE USER OPS$unername IDENTIFIED BY EXTERNALLY;

사용자명의 처음에 OPS$라고 접두사를 붙일 경우, OS상에 존재하는 사용자에 대하여 사용자명 패스워드 입력 없이 데이터 베이스의 접속을 허가 합니다. 예를 들면 UNIX의 사용자 guest에 대하여 사용자를 작성했다고 하면 guest에 login 한 경우 > sqlplus상에 입력하는 것으로 데이타베이스에의 전송이 가능합니다. 단지 guest이외의 UNIX의 사용자로는 ORACLE 사용자 OPS$GUEST에 접속할 수는 없습니다.

(3) OS의 사용자와 연결한 패스워드가 있는 사용자

이 타입의 사용자를 작성하려면 아래와 같은 커맨드를 실행합니다.

SQL> CREATE USER OPS$username IDENTIFIED BY password;

예를 들면 이 커맨드에 의해 UNIX사용자 guest에 대해 사용자를 작성했다고 하면 guest에 록 인한 경우 (2)의 경우와 같이 데이타 베이스에의 접속이 가능합니다. (2)와 다른점은 UNIX의 guest이외의 사용자로도 > sqlplus ops$username/password처럼 사용자명과 패스워드를 지정하면 접속할 수 있습니다.

이제 여기서 사용된 접두사 'OPS$'는 초기 파라미터의 OS_AUTHENT_PREFIX에 의해 임의의 문자열로 변경할 수 있습니다. OS_AUTHENT_PREFIX=''''라고 지정하면 NULL에도 설정이 가능하므로, 그 경우 OS와 완전히 일치한 사용자의 관리가 가능합니다.

■ 원격으로 리스너 조작을 금지 한다. (listener.ora)

ADMIN_RESTRICTIONS_listener_name= ON

■ 접속 가능한 ip 대역 설정 (protocol.ora)

tcp.validnode_checking = YES

tcp.excluded_nodes = {list of ip address}

tcp.invited_nodes = {list of ip address}

■ File checklist

▶ Control file

BACKUP

- datafile, redo log file에 추가나 삭제등 DB 변경 사항이 있는 경우 백업

- hot backup시 end backup 할 때 마다 백업

▶ ONLINE REDO LOG FILE

BACKUP

- hot backup 시 end backup 수행 후 반드시 log switch 수행한 후 그 log file을

백업 받는다.

- hot backup시 archive log 만 백업하고 online redo log는 받지 않는다.

Configuration

- ops 일 경우 instance 복구를 위해 모든 member를 동시에 access 가능 해야 한다.

▶ ARCHIVE LOG FILE

BACKUP

- ops 일 경우 모든 thread에서 생성되는 archive를 전부 백업해야 한다.

Configuration

- startup 이후에 archive log 를 시작하기 위해서

(startup 전 log_archive_start=true)

- svrmgr> alter system archive log start;

- ops의 경우 thread를 알 수 있게 하기 위해서

log_archive_format=”LOG%s_%t.ARC” 에서 %t를 한다.

■TABLESPACE checklist

▶ DATA TABLESPACE

BACKUP

- os file 손실은 tablespace 손실을 의미한다. (fet$, uet$에 해당 파일 기억함)

4. SERVER PROCESS 실행단계

4.1 SQL문의 실행순서 = SERVER PROCESS의 실행단계 GO TOP

1) parsing

- syntax check

- semantic check (테이블, 칼럼 존재 여부등 확인) - data dictionary를 이용

- parse tree -> execution plan

A) 이상 세개 작업의 결과가 shared sql area에 저장된다.

B) 이 단계의 부하를 줄이기 위해 stored procedure를 이용하고,

C) sql command naming rule을 정한다.

2) execute

- file을 읽어 database buffer cache에 가져다 놓는 것 - DML은 여기까지만 수행

è cache hit = logical read : database buffer cache에서 읽는 것

è cache miss = physical read

- block단위로 입출력 되므로 선택된 행만 리턴되는 것은 아니다.

3) fetch

- select문일 경우만 수행

4.2 ACCESSING AND UPDATING DATA

4.2.1 개요

- SQL문은 Server Process에 의해 처리된다.

- Identical SQL문은 Shared pool의 shared SQL area를 사용한다.

- database buffer cache는 rollback block에 수정된 data의 전상

before image)을 보관하고 data block에 후상(after image)를 보관한다.

- DBWR(database writer)은 모든 변경된 buffer들을 disk에 기록한다.

4.2.2 Database Buffer Cache

◆ user가 data를 요청하면 Server process는 database buffer cache에 있는 data인지

확인한다.

◆ Data Blocks

> data가 없다면 Server process는 datafile의 적절한 block을 읽어 database buffer cache에 옮긴다. (Oracle data block 한 개의 크기는 DB_BLOCK_SIZE 에 정의)

> 만일 user가 요청한 data를 수정한다면, cache의 data block에서 발생된다. 변경된 data를 후상(after image)라고 하는 이유는 수정된 이후의 data image 이기 때문이다.

◆ Rollback Blocks

> Oracle은 rollback block에 처음의 data를 저장한다. 이것을 전상(before image)이라고 하는 것은 user가 수정하기 전의 data를 보존하고 있기 때문이다. 만일 user가

commit하기 전에 작업을 취소한다면, rollback block의 정보를 이용해 transaction을

rollback 시킬 수 있다.

◆ Redo Log Buffer

> transaction이 진행되는 동안 Server process는 전상과 후상을 redo log

buffer에 기록한다. 이 정보는 recovery에 이용된다.

◆ Least Recently Used (LRU) List (제일 옛날에 사용한 버퍼 목록^^)

> database buffer cache는 dirty list, LRU list 두 개로 조직되어 있다. (관리된다.)

dirty list에는 disk에 기록되지 않은, 수정된 buffer들이 기록되어 있다.

◆ LRU list의 내용

[1]

Buffer Type Description

Free Buffers 수정되지 않은 Buffer로 사용 가능한 Buffer

Pinned Buffers 현재 access 되고 있는 Buffer

Dirty Buffers disk에 기록되어야 할 수정된 Buffer

> process가 buffer를 찾거나 접근할 때, 그 buffer를 LRU list의 끝에 있는 MRU

(Most Recently Used)로 옮긴다. dirty buffer들은 LRU list의 LRU 쪽 끝으로 모여진다.

[1]

MRU end LRU end

4.2.3 User Requests

◆ 모든 SQL문은 user process로부터 직접 요청을 접수하는 server process 에 의해

처리된다. server process는 parse, execute, fetch라고 하는 세가지 주요 단계를

거친다.

◆ The Server Process

> server process가 disk에서 database buffer cache로 data를 읽을 필요가 있으면

1) LRU list를 검색해

2) free buffer를 찾고

3) dirty buffer들을 dirty list로 옮긴다.

> server process는 free buffer를 찾았을 때나, 지정된 개수의 buffer를 검색해도

free buffer를 찾지 못했을 때 검색을 종료한다.

◆ Configuration of the PGA

> PGA (Program Global Area)는 단일 사용자나 server process를 위한 data와 제어정보를 가진 메모리의 한 부분이다. PGA는 user process가 Oracle database에 접속하고,

session이 만들어질 때 Oracle Server에 의해 할당된다.

◆ PGA 의 특성

> Stack Space는 session variable이나 array를 보관하기 위해 할당된 메모리이다.

> user session data는 user의 session을 위한 추가 메모리이다.

> PGA는 쓰기 가능이고 공유되지 않는다.

[1]

Server Process

[1]

[1] [1]PGA

Stack Space User Session Data

4.2.4 Processing SQL Statements SQL문의 실행순서 = Server Process의 실행단계

◆ Parsing은 SQL문 처리의 일단계다.

parse가 호출되는 동안 오라클은 parse, execute, fetch를 수행한다.

> syntax와 semantic validity를 check한다.

> object resolution, security privilege, 가장 효과적인 search path

(= parse tree, execution plan)를 위해 Data dictionary를 질의한다.

> 해당 SQL문을 위한 private SQL area를 할당한다. (cursor space in pga)

◆ parse tree는 Shared SQL Area에 저장된다. (library cache area)

Multiple server processe들은 동일한 parse tree를 공유할 수 있다.

- Oracle Server는 cursor라 불리는 Memory의 영역을 사용해서 각 statement의 상태정보를 기록한다.

◆ Execute는

> parse tree를 data buffer들에 적용한다. (applies)

> physical, logical read와 write를 수행한다.

> constraint checking 수행하고, 필요하다면 자료를 변경한다.

◆ Fetch는

> Select 문을 위해 rows of data를 Retrieve한다.

◆ Connecting to the Instance

> Cursor Space 는 Server process PGA 나 SGA 의 private area에 있다.

> The user connects to the instance, which spawns a server process(dedicated)

and the connection is established.

4.2.5 Shared SQL Areas

◆ Application 이 identical SQL 문을 보내면, Oracle은 자동으로 database에 통지한다.

If two identical statements are issued, the SQL area used to process the first instance of the statement is shared.

그러므로, identical SQL 문을 위해 다중 SQL area를 가지는 대신, 오직 하나의 공유된

SQL area 가 unique statement를 위해 존재한다.

◆ Identical SQL Statements (동일 SQL)

> 대소문자와 space 등을 포함한 모든 문자열이 동일할 때

> 참조하는 objects 가 동일할 때

> 변수의 이름과 type 이 동일할 때, SQL 문을 identical 하다고 말한다.

4.2.6 The SELECT Operation

◆ A SELECT operation requires that the block of data used for the search criteria is found in the database buffer cache.

◆ SELECT Operation

> 메모리에 block이 있으면 logical read를 수행하고, 없으면 physical read를 수행한다.

◆ The Oracle Server will perform multiblock reads into memory when possible, that is, when doing full table scans.

4.2.7 The UPDATE Operation update emp set ename='A' where empno = 7849;

수행절차

◆ 모든 UPDATE 연산은 read consistency, recovery, rollback 을 지원하기 위해

rollback segment 를 획득한다 (acquire).

◆ 1. Acquires data blocks into the database buffer cache.

2. Acquires rollback blocks into the database buffer cache.

(database buffer cache 에 data block 과 rollback block 을 확보한다.)

3. 변경 하려는 행에 대해 배타적 행 lock 을 설정한다.

4. 전,후상을 구분하기 위해 redo log buffer에 기록을 저장한다.

5. rollback data를 rollback segment block buffer에 저장한다.

6. data block buffer에 변경을 적용한다.

◆ What is selected if user 1 re-executes the select statements ?

> user 1 : select salary from emp where empno = 7376;

user 2 : update emp set salary = salary * 1.1 where empno = 7376; (940 -> 1034)

> 이러한 상황에서 user 1 이 다시 select 문을 수행하면 이전 값인 940을 본다는 내용

> If user 1 re-executes a select operation, a read consistent image of the

changed row is made. If a block is dirty, the Server process constructs a read

consistent image through the use of rollback segments.

> Block R20 with row value 940 represents the value user 1 would see during a

subsequent query. (940 을 가진 R20 block 이 user 1 이 볼 자료다. ??)

> Data from all ongoing transactions is included together in the redo log buffer.

> The two values, 940 and 1034, make up an atomic record in the redo log buffer.

The log record includes a location address for the column including the file, the

block, the row, and the column.

◆ UPDATE 연산의 특성

> rollback segment 는 rollback data를 저장하기 위해 사용되는 object다.

> Oracle Server 는 모든 reader 에게 read-consistent image of data를 제공한다.

◆ A SELECT operation can also be referred to as a READ operation.

Readers do not block writers and writers do not block readers.

The Oracle Server achieves this through the use of rollback segments.

4.2.8 Database Writer (DBWR) Process LGWR, DBWR의 동작

◆ DBWR 은 database buffer cache를 관리해서, server process가 언제나 free buffer를 찾을 수 있도록 한다.

- DBWR Process

> 모든 수정된 버터들을 데이타파일에 기록한다.

> 메모리에서 가장 최근에 사용된 block을 유지하기 위해 LRU 알고리즘을 이용한다.

> I/O optimization 을 위해 기록을 연기한다. (defers writes)

◆ DBWR이 dirty buffer를 디스크에 기록하는 시점

- dirty list가 threshold(입구,출발점,경계) length에 도달했을 때

- LRU에서 지정된 개수의 버퍼를 찾았으나 free buffer를 만나지 못했을 때

- server process가 time-out (3초)

- DBWR checkpoint 가 발생했을 때

5. Performance And Tuning

5.1 TUNING GOAL

- 최소 block만 access하도록

- 원하는 data(자주 사용되는)는 memory에 오래 보존되도록

- application code를 공유하도록 (parsed code는 반복적으로 parsing되지 않도록)

- Read/Write는 가능한 한 빠르게(RBS관련)

- user가 resource사용을 위해 기다리는 시간을 최소화(lock,rbs 관련)

5.2 SESSION TRACING

1. enble/diable하기

- set_sql_trace_in_session procedure를 돌려서 특정 session에 enble시킴

잘 안되거나 필요하다면 prvtutil.plb script를 다시 돌리도록 하자.

SQL> EXECUTE dbms_system.set_sql_trace_in_session(11,1,TRUE);

위에서 11은 SID, 1은 SERIAL# 로 다음 script로 확인할 수 있다.

- alter session set sql_trace=true; => connect 된 user에 대해 enable시킴

execute dbms_session.set_sql_trace(true); 도 마찬가지

- init<SID>.ora 의 sql_trace=true로 놓고 올리기 => 전체 session에 대해(20 ~ 30 %

5.3 UTILITIES AND DYNAMIC PERFORMANCE VIEWS

◆ V$xxx 는 dynamic troubleshooting and dictionary views

DBA_xxx 는 dictionary views

5.4 REPORT.TXT의 결과를 확인한다.

◆ V$ Views & X$ tables : V$FIXED_TABLE, v$fixed_view_definition 에서 정보를 볼 수 있다.

- V$ views : X$ tables 에 기초한다. nomount,mount 상태에서 볼 수 있다.

V$ view들은 sys user에 속해있음

- X$ tables : 일반적으로 direct로 query되지 않음

Dynamic and constantly chaning

timed_statistics=true 로 되어있으면 X$ table들이 timing information을 가진다.

5.5 SYSTEM 전반적인 상태

5.5.1 General System_Wide Statistics

: v$statname, v$sysstat ==> system 상태 보는 view

SQL> select name,class,value from v$sysstat;

=> instance startup되고 난 후 누적치

CLASS	DESC
1	general instance activity에 참조
2	redo log buffer activity에 참조
4	locking 참조
8	database buffer cache에 참조
16	OS activity에 참조
32	paralleliztion에 참조
64	table access에 참조
128	debugging 목적에 참조

5.5.2 SGA Global Statistics

: v$sgastat => instance start되고 난후 SGA의 usage 상세정보

5.5.3 Waiting Events Statistics

: v$event_name, v$system_event => resource에 대한 waiting check

5.5.4 General Session-Related Statistics

: v$mystat, v$session, v$sesstat 으로 확인

5.5.4 Session Waiting Events Statistics

: v$session_event, v$session_wait

SQL> select sid,event

from v$session_wait

where wait_time = 0; => wait_time=0 이면 waiting중임을 의미

5.6 UTLB/ESTAT을 실행하여 얻은 REPORT.TXT에서 얻을 수 있는 정보

Library cache statistics	p-code가 들어가는 공간:SQL,PL/SQL 공간
System statistics	여러 가지 정보가 제공됨
Wait events statistics	Wait 의 Total Time을 보고 병목부분을 찾을 수 있다.
Latch statistics	SGA의 internal structure 보호하기위해 단시간동안 잡았다 놓는 lock
Rollback contention statistics	rollback header 정보를 얻기 위해 경합이 있으면 performance저하
Buffer Busy Wait statistics	Buffer Busy Wait 가 높으면 data block, segment header, undo header중 어느 block에 병목이 걸리는지 알 수 있다.
Dictionary cache statistics	dictionary cache의 object에 access 할 때 miss가 높으면 I/O 가 높아져 performance가 낮아진다. cache된 각 항목의 gets/misses를 알 수 있다.
I/O statistics per data file and tablespace	각 datafile, tablespace의 physical read/write, physical block read/write, 소요시간을 알아 여러 disk에서 file I/O 분산을 보여준다.
Period of measurement	utlbstat,utlestat 의 수행된 시간을 알 수 있다.

◆ report.txt에서 얻는 정보의 예

ex1) DB Writer checkpoints:

Statistic	Total	Per Transaction	Per Logon	Per Second
DBWR checkpoint buffers written	137	137	11.91	0.05
DBWR transaction table writes	7	7	0.61	0

DBWR checkpoints 는 DB Writer에게 보내진checkpoint의 증가량을 보여준다.

checkpoint동안 data I/O의 증가는 system performance를 떨어뜨릴 수 있다.

이때 checkpoint수를 줄이기 위해 log_checkpoint_interval, log_checkpoint_timeout을

늘려줘서 checkpoint 발생을 좀 적게 해줄 수 있다.

ex2) consistent gets, db block gets, physical reads:

Statistic	Total	Per Transaction	Per Logon	Per Second
consistent gets	7998	7998	695.48	3.17
db block gets	309	309	26.87	0.12
physical reads	265	265	23.04	0.1

consistent gets : query시 buffer에 access한 block 수

(단 select... for update... 는 빠짐)

db block gets : insert, update, select for update시 buffer에 access한 block 수

physical reads : physical I/O를 일으키는 block들에 request한 수

5.7 V$SESSION_WAIT VIEW

: active session이 waiting하고 있는 resource나 event의 list

SQL> desc v$session_wait

EVENT -> waiting하고 있는 resource나 event

WAIT_TIME -> timed_statistics 설정시 추출이 가능

◆ wait_time 의 값에 따른 설명

>0 : session의 마지막 wait time

=0 : session이 현재 waiting

=-1 : wait time이 1/100초 미만

=-2 : system에서 time정보를 제공하지 않음

SECONDS_IN_WAIT

위에서 p1text,p2text,p3text로 나오는 값들은 v$event_name에 정의되어 있는 additional parameter들이며 p1,p2,p3 는 그것들의 값이고 p1raw,p2raw,p3raw 는 value의 hexadecimal값들이다.

이외에도 system, session에서의 wait 횟수를 보려면 v$system_event, v$session_event

5.8 TUNING THE SHARED POOL

5.8.1 SGA(Shared Global Area)의 구성

1) Database buffer cache : data block이 들어가는 영역

(I/O 줄이는게 관건 : db_block_buffer 조정)

2) redo log buffer

3) Shared Pool : system tuning중 가장 priority가 높은 부분

(shared_pool_size 조정)

-- Library cache : 현재 실행되고 있는 SQL정보를 저장

(stored procedure와 trigger code가 포함)

-- Data dictionary cache : 환경 정보를 저장

(참조 무결성,테이블 선언, 인덱싱정보,v$... 정보들)

-- UGA(User Global Area) : MTS connection정보를 가지고 있는 영역

4) Large Pool

5.8.2 Library Cache Tuning Goal

1) Library Cache는 SQL이나 pl/SQL block의 parsed code가 저장되는 영역으로 LRU Algorithm으로 되어있음

2) tuning goal

1. reduce misses(자주 사용되는 sql은 memory에 올라가 있어야..)

-- 대소문자가 달라도 다른 sql로 인식

-- bind 변수 사용(constant보다는) : Dynamic SQL

2. fragmentation 은 피하도록

-- shared pool area에 충분히 큰 연속된 예약공간을 둬 필요시 사용

-- 자주 사용되는 object는 아예 memory에 고정시키자

-- large anonymous block 대신에 small PL/SQL을 사용

-- MTS환경에서는 shared server process에 의한 session memory사용을

측정하자

◆ Library Cache Tuning을 위한 분석 view들

v$sgastat : 모든 SGA구조의 size들

v$librarycache : library cache의 management 정보들

v$sqlarea : 모든 shared cursor에 대한 full statistics와 sql문의 첫 1000자

v$sqltext : full SQL text

v$db_object_cache: package를 포함한 cached된 object들

SQL> select * from v$sgastat;

위의 v$sgastat에서 주의해야 할 점은 초반이 아닌 운영이 한참 되고 있는 상태에서 free memory가 많다는 것은 얼핏 memory가 적게 쓰이니까 좋아보이지만 실제 LRU Algorithm에 의해 거의 memory는 사용 되는 것이 원칙이므로 실제는 fragmentation으로 인해 못쓰는 공간이 많다는 의미가 된다.

◆ Cursor가 share 되고 있는가?

아래의 gethitration(gethits/gets)는 90%이상이 되어야 한다.

이하이면 대,소문자별 select가 따로있나, invalidation이 많나 check해본다.

SQL> select namespace,gethitratio from v$librarycache;

아래에서 대략의 sql에 대한 execution이나 load등을 확인 할 수 있다.

SQL> select sql_text, users_executing, executions, loads from v$sqlarea;

sql을 자세하게 보려면 v$sqltext를 조회해 본다.

◆ Library Cache의 Reloads : reloads/pins < 1% 이하여야 좋다.

è 1% 이상 이면 LRU list에서 aging out 된 것,

è invalidation check =>shared_pool_size를 증가시킨다.

a. v$librarycache에서 확인

SQL> select sum(pins) "Executions", sum(reloads) "Cache Misses",

2 sum(reloads/pins)

3 from v$librarycache

4 where pins != 0;

Executions	Cache Misses	SUM(RELOADS/PINS)
8573	7	.000919601 -> 1%이하이므로 상태가 양호

b. utlbstat/utlestat후 report.txt에서 확인

LIBRARY	GETS	GETHITRATIO	PINS	PINHITRATIO	RELOADS	INVALIDATIONS
SQL AREA	401	0.95	1087	0.96	0	1

◆ invalidation은? schema object가 변경되어 shared SQL area가 invalidation 됨을

의미 (reparse (hard) 된다.)

◆ memory의 설정 --> shared_pool_size 결정 방법

Application이 얼마나 많은 memory를 사용하나? (Global Space Allocation) 를 산정하자.

--> 아래에 기술된 내용을 자세히 보면

Shared_pool_size는 아래의 (a)+(b)+(c)+(30%정도의 free space추가) 로 잡는다.

a. shared object에 대한 필요한 공간

SQL> select sum(sharable_mem)

2 from v$db_object_cache

3 where owner is not null;

SUM(SHARABLE_MEM)

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

3354299 ---> (a)

참고) 좀 자세히 object type별로 보려면

SQL> select type,sum(sharable_mem)

2 from v$db_object_cache

3 where type='PACKAGE' or type='PACKAGE BODY' or

4 type='FUNCTION' or type='PROCEDURE'

5 group by type;

TYPE SUM(SHARABLE_MEM)

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

PACKAGE 301274

PACKAGE BODY 13437

b. 자주 사용되는 memory공간 계산

그리고 자주 사용되는(일반적으로 5회이상) application의 memory를 조회해 보면

SQL> select sum(sharable_mem)

2 from v$sqlarea where executions > 5;

SUM(SHARABLE_MEM)

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

190765 ---> (b)

c. open된 cursor의 수에 따른 memory할당

또 user당 open cursor당 shared pool은 250bytes 정도 할당 하는 것을 보통으로 하며

peak time시의 전체 memory는 (open된 cursor개수 * 250 bytes)로 생각하여 산정한다.

SQL> select sum(250 * users_opening)

2 from v$sqlarea;

SUM(250*USERS_OPENING)

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

250 ---> (c) : 현재는 open된 cursor하 하나뿐이다.

◆ Large Memory Requirements : shared_pool_reserved_size를 사용하기

shared pool 내에 fragmentation이 나지 않은 일정 공간 사용

pl/sql compilation이나 trigger compilation과 같은 large allocation에 사용

init<SID>.ora에

shared_pool_reserved_size를 설정해 주는데 일반적으로 shared_pool_size의 10%정도를 초기값으로 설정하고 필요시 늘려준다. 대신 shared_pool_size 의 50%를 넘을 수 없고 넘으면 startup시 다음과 같은 error가 난다.

SQL> startup

ORA-01078: 시스템 매개변수 처리 오류입니다

최소 : shared_pool_min_alloc

최대 : shared_pool_size 의 50%

v$shared_pool_reserved view는 shared pool 내에 reserved pool을 tuning하는데 도움이 된다. shared_pool_reserved_size 가 setting되어 있을 경우에만 column들이 유효하다.

중요한 column들을 보면 free_space, avg_free_size, max_free_size, request_misses 등이 있다.

◆ reserved space를 tuning하기

request_misses=0 으로 하는 것이 목적이다.

위에서 언급한 v$shared_pool_reserved view 뿐 아니라

$ORACLE_HOME/rdbms/admin/dbmspool.sql을 돌리고 나서 dbms_shared_pool package내에 , aborted_request_threshold procedure 사용하여 측정

free_space > shared_pool_reserved_size 보다 큰 것은 9i(dynamic SGA) 도입 후 버그

◆ 어떻게 large Object를 keep 하게 하나?

kept 되어 있지 않은 procedure를 아래와 같이 찾아

SQL> select * from v$db_object_cache

2 where sharable_mem > 10000

3 and (type='PACKAGE' or type='PACKAGE BODY' or

4 type='FUNCTION' or type='PROCEDURE')

5 and kept='NO';

SQL> EXECUTE dbms_shared_pool.keep('package_name');

SQL> select * from v$db_object_cache

2 where sharable_mem > 10000

3 and (type='PACKAGE' or type='PACKAGE BODY' or

4 type='FUNCTION' or type='PROCEDURE')

5 and kept='YES';

항상 db startup 후 keep 해야하는 경우는 8i부터는 startup,shutdown,connect 실행에 따른 trigger를 생성할 수 있으므로

SQL> create or replace trigger user120_startup

2 after startup on database

3 begin

4 dbms_shared_pool.keep('package_name');

5 end;

* 주의 : alter system flush shared_pool 의 명령으로 kept object는 flush 되지 않는다.

5.8.3 Data Dictionary Cache Tuning : v$rowcache

Library cache와 함께 Shared pool 의 part인 Data Dictionary Cache에서의 Tuning 역시

miss율을 줄이는 것이다.

주의해야 할 점은 db startup 직후 이를 측정해 보면 당연히 miss율이 높다. Data Dictionary Cache를 check하기 위해서는 어느 시간 사용한 후에 miss율을 측정해본다.

SQL> select parameter, gets, getmisses, getmisses/gets

2 from v$rowcache

3 where gets !=0;

위 값들이 15% 이상이면 shared_pool_size를 늘리는 것을 고려해봐라.

여기서는 바로 startup 후 test한 것이라 상당히 높은 편이다.

또 utlbstat/utlestat 의 report.txt에서 다음을 참고해도 된다.

NAME	GET_REQS	GET_MISS	SCAN_REQS	SCAN_MISS	MOD_REQS	COUNT	CUR_USAGE
dc_objects	62	20	0	0	0	342	338

5.8.4 MTS(Multithreaded Server)의 경우 Shared Pool Size

- Server configuration 비교

a. Dedicated Server - User Process:Server Process = 1:1

이때 Server Process는 Shared Pool 밖의 PGA 영역에 생성됨

PGA

UGA

SORT_AREA+CURSOR_STAT+USER SESSION I INFO

STACK SPACE

b. MTS - User Process:Server Process = 1:1

MTS의 경우 UGA가 Shared Pool 한으로 들어옴

◆ UGA(User Global Area) 크기 설정

a. UGA space used by your test connection :

SQL> select sum(value)||' bytes' "Total session memory"

2 from v$mystat, v$statname

3 where name = 'session uga memory'

4 and v$mystat.statistic# = v$statname.statistic#;

Total session memory

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

108920 bytes

b. UGA space used by all MTS users :

SQL> select sum(value)||' bytes' "Total session memory"

2 from v$sesstat, v$statname

3 where name = 'session uga memory'

4 and v$sesstat.statistic# = v$statname.statistic#;

Total session memory

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

494832 bytes

c. Maximum UGA space used by all MTS users :

SQL> select sum(value) ||' bytes' "Total max memory"

2 from v$sesstat,v$statname

3 where name = 'session uga memory max'

4 and v$sesstat.statistic# = v$statname.statistic#;

Total max memory

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

527040 bytes

5.8.5 The Large Pool

1) LRU Algorithm 적용 받지 않음 -> 즉 공간 없으면 error

2) SGA내에 Shared Pool과는 별도의 memory공간을 만듦 LARGE_POOL_SIZE parameter setting한다

Advantages

- MTS server구성을 위한 session memory와 SORT_AREA 로 사용

- shared SQL cache의 shrink에 의한 performance overhead를 방지하는데 유용

- I/O Server Process

- Oracle의 backup & restore operation(RMAN)

- Parallel query

ex) SQL> select /*+ (parallel 4) */ empno from scott.emp;

PARALLEL_AUTOMATIC_TUNING parameter가 true로 setting하면 됨.

--> v$sgastat의 large_pool_size parameter를 setting

◆ large pool의 configuration : minimum - 600 KB

maximum - 2GB 이상(OS에 따라)

5.8.6 shared pool 튜닝

이 문서는 오라클 7/8 에서 shared pool을 튜닝하기 위한 키 포인트를 제공한다.

다음 문제를 가진 시스템이라면 여기에 서술된 내용이 특히 중요하다.

► latch 경합이 library cache latch 에서 발생

► Shared pool latch 에 대한 경합

► 높은 cpu 파싱 파임

► V$library cache의 높은 reloads 수

► 잦은 ora-4031 에러

5.8.6.1 What is the shared pool ?

SGA내 Shared pool에 Sql 구문, 패키지, 오브젝트 정보와 많은 정보를 유지한다. 메모리 공유 영역은 정교화된 cache와 heap 관리자를 겸하여 관리된다. 이것은 3가지 문제를 가지고 있다.

1. 할당되는 메모리 단위는 일정하지 않다. pool로부터 할당되는 메모리는 몇 바이트에서 수 킬로바이트가 될 수 있다.

2. shared pool의 목적이 정보 공유의 최적화 이므로 사용자가 이 영역을 가지고 작업을 마치면 모든 메모리가 freed 되는 것은 아니다. 즉 공유를 위해 보관된다.

오라클은 정보들이 모든 사람에게 사용될지 안될지는 모른다. 그러나. 메모리내 정보는 다른 세션에 유용할 수 있다.

3. Page out을 위한 disk 공간은 없다. 그래서 전통적인 cache와 다르며 file backing 저장을 한다.오직 재구성 가능한 정보만이 cache로 밀려 날 수 있다.즉 재생성/구성 할 수 없는 정보는 계속 유지 되어야 한다. 그리고 다음에 필요시 다시 재 생성 되어야만 하다.

Shared pool에 대한 관리와 이해의 지식은 매우 복잡하다. Shared pool과 이것에 관련한 latch에 성능 효과에 대해서 설명한다.

Literal SQL

Literal SQL은 바인드 변수 보다 술어 부분에 문자를 사용하는 것으로 간주된다. 다양한 실행 구문을 다르게 만든 원인이 된다.
Eg 1:

          SELECT * FROM emp WHERE ename='CLARK';

          SELECT * FROM emp WHERE ename=:bind1;

두 구문은 다른 구문으로서 인식 된다.

Eg 2:

          SELECT sysdate FROM dual;

          위 구문은 바인드 변수를 사용하지 않는다. 그러나 공유 될 수 있다면 Literal SQL로서 간주

          되지 않는다.

Eg 3:

  SELECT version  FROM app_version WHERE version>2.0;

이 구문이 어플리케이션을 통해서 version을 체크하기 위해서 사용된다면 문자 값 2.0은 항상 동일하다 그래서 이 구문은 공유 될 수 있다 (즉 상수가 hard cord 되어 있다면 공유된다)

Hard Parse

공유 풀에 없는 새로운 sql 구문이 생성 된다면 전체 파싱을 해야 한다. 즉 오라클은 구문을 위한 메모리 할당을 해야 하고,구문에 대한 syntax와 semantic 등등 체크 해야 한다. 이것을 hard parse로 불리며 매우 높은 CPU 사용과 수 많은 latch 획득을 위해서 리소스를 사용한다.

Soft Parse

세션이 sql 구문을 실행했을 때 shared pool에 이미 있다면 존재하는 버전의 구문을 사용할 것이면 이것을 soft parse라고 한다.

Identical Statements ?

두 sql 구문의 의미가 같고 식별 문자가 다르다면 오라클 관점으로 다른 sql 이다. 하나의 세션에서 SCOTT에 의해 실행된 문장 이다.

SELECT ENAME from EMP;

SELECT ename from emp;

비록 두 문장이 거의 동일하지만 대문자 E 와 소문자 e가 다르기 때문에 다르다.

Sharable SQL

두 세션이 동일한 sql을 실행 한다고 해서 구문이 공유 되는 것이 아니다.

User SCOTT has a table called EMP and issues:

SELECT ENAME from EMP;

User FRED has his own table called EMP and also issues:

SELECT ENAME from EMP;

비록 text가 동일하지만 emp 테이블은 소유자가 다른 object 이다. 그러므로 다른 버전의 구문이다. 두개의 SQL STRING이 정말로 동일한 구문인지 결정하는 많은 요소가 있다.. (그래야 공유된다.)

► 모든 오브젝트 이름은 반드시 정확히 동일하게 풀려야 한다.

► 구문을 실행 하는 세션의 옵티마이져 모드는 동일해야 한다.

► 모든 바인드 변수의 타입과 길이는 비슷해야 한다 (우리는 이것에 대해서 논의 하지 않겠다. 그러나 다른 타입과 길이의 바인드 변수는 다른 버전으로 분류 될 수 있는 원인이 된다)

► 구문에 적용되는 NLS 환경이 동일 해야 한다.

Versions of a statement

위에서 언급한 것처럼 두 구문은 문장적으로 일치하지만 공유 될 수 없다 그러한 동일 구문을 versions라고 부른다. 오라클이 많은 버전을 가진 문장 만나면 각 버전을 현재 파싱 되고 있는 문장에 정말로 일치하는 차례차례 체크 해야 한다. 그러므로 버전이 많다면 다음과 같이 튜닝 한다.

► 클라이언트에 의해서 서술된 최대 바인드 길이의 표준화

► 오브젝트 권한을 사용하는 수많은 스키마로부터 동일한 sql 사용을 피한다.

예) Select XX from MYTABLE: 각 유저는 자신의 MYTABLE를 사용

► Setting _SQLEXEC_PROGRESSION_COST to '0' in Oracle 8.1

Library Cache and Shared Pool latches

Shared pool latch는 shared pool내 메모리의 할당과 해제를 수행 할 때 위험한 operations을 방어한다.

Library cache latch는 (오라클7 library cache pin latch) 는 library cache 자체에 대한 operation을 방어 한다.

모든 Latch는 경합의 원이 된다. 발생하는 수많은 latch가 shared pool내 전체 활동량에 의해서 직접적으로 영향을 받는다. 예) parse operations

latch gets을 최소화 하는 모든 것은 shared pool 의 전체 활동량의 성능과 가요성을 확대시킨다.

5.8.6.2 Literal SQL versus Shared SQL

여기 literal sql과 sharable sql의 장점을 간략하게 설명하였다.

Literal SQL

CBO 는 전체 통계정보가 있을 때 그리고 구문의 술어 부분에 문자를 사용할 때 최고의 역할을 한다.

SELECT distinct cust_ref FROM orders WHERE total_cost < 10000.0;

versus

SELECT distinct cust_ref FROM orders WHERE total_cost < :bindA;

위의 첫번째 는 CBO 에서 OREDRES의 FULL SCAN을 하는 것과 INDEX SCAN (ON TOTAL_COST)하는 것 중 어떤 것이 이 최선일 수 있다는 것을 결정하기 위해 모은 Histogram statistics를 사용할 수 있다.

두번째 구문에서 CBO는 실행 계획 결정시 바인드 변수에 값이 없기 때문에 :bindA 값으로 얼마의 ROWS가 있는 지 알 수 없다 따라서 full scan / index scan을 구별 하지 못한다.

:bindA는 0.0 ~ 9999999999999999999. 일 수 있다.

최고의 실행 계획을 달성하기 위해서 CBO를 사용하려고 한다면 두 실행 중에 literal 문장이 유리하다. 따라서 DSS 시스템에서는 반복적으로 사용되는 문장이 없기 때문에 문장 공유의 기회는 작다. 따라서 CPU의 대부분이 파싱을 하는데 소요되며, 실제 구문 실행시 적은 양의 CPU를 사용한다. 그래서 파싱 시간을 줄이기 위해서 가능한 많은 정보를 옵티마이져에게 주는 것이 중요하다.

5.8.6.3 Sharable SQL

어플리케이션이 literal SQL을 사용한다면 가용성과 산출물에 대한 심각한 제한을 줄 수 있다. 새로운 구문에 대한 파싱의 비용은 CPU에 대한 요구와 수많은 Library cache 와 shared pool latch의 획득과 제거로 인해 매우 높다.

단순한 SQL 이라도 20 ~ 30 번의 Latch를 요구한다.

최상의 접근법은 Adhoc 또는 자주 사용되지 않는 SQL이 없다면 모든SQL을 공유하는 것이다. 좋은 실행계획을 세울 수 있도록 가능한 많은 정보를 CBO에 제공하는 것이 중요하다.

5.8.6.4 Reducing the load on the Shared Pool

Parse Once / Execute Many

OLTP 타입의 어플리케이션에서 최고의 접근법은 오직 한번만 파싱하고 OPENED CURSOR 을 잡고, 요구 할 때마다 이것을 실행하는 것이다. 이것은 각 구문에 대한 오직 최초 파싱(soft or hard)만 한다.

반드시 드물게 실행되는 구문이 있을 것이며 그것을 위해 열려진 CURSOR를 관리하는 것 낭비이다.

참고: 한 세션은 오직 OPEN_CURSORS에서 정의된 수 만큼 CURSOR를 이용할 수 있으며 이 파라메터를 이용하여 동시에 열수 있는 CURSOR 수를 늘릴 수 있다.

PRECOMPILER에서 HOLD_CURSOR 파라메터는 OPEN CURSOR를 잡고 있을 것인지 아닌지 제어한다. 그러나 OCI developers는 이런 CURSOR를 제어 할 수 없다.

Eliminating Literal SQL

어플리케이션에 literal SQL이 존재 한다면 모든 LITERAL SQL을 제거하는 것은 쉽지 않다. 그러나 문제를 야기 한다면 반드시 제거 해야 한다.

V$SQLAREA 뷰를 조사하여 LITERAL 구문 중 바인드 변수 사용으로 전환 가능한 후보들을 볼 수 있다.

SELECT substr(sql_text,1,40) "SQL",

count(*) ,

sum(executions) "TotExecs"

FROM v$sqlarea

WHERE executions < 5

GROUP BY substr(sql_text,1,40)

HAVING count(*) > 30

ORDER BY 2

;

참고: 만약 latch 경합이 Library cache latch에 있다면 위 구문 또한 더 많은 문제를 야기 할 수도 있다.

값이 40,5, 30은 sql을 찾기 위한 예이다. Shared pool에 적어도 30 개의 다른 구문이 있고 실행 시간이 매우 짧고 처음부터 40 개 문자가 다른 sql을 찾는다. 이 질의문은 일반적인 literal 구문을 찾는데 사용된다.

참고: 종종 바인드 변수를 사용하기 위해 literal sql로 전환하는 것은 개발자의 저항이 있을 수 있다. 가장 자주 사용되는 구문에 대한 변환은 성능을 대폭 개선하며, shared pool내 문제를 제거 할 수 있다.

CURSOR_SHARING parameter

이 파라메터는 8.1.6. 에서 처음 나온 파라메터이다. 8.1.6.에서 조심해서 사용해야 한다. 이 파라메터를 force로 조정한다면 literal 문자는 시스템에 의해서 가능한 바인드 변수로 변한다.

여러 개의 비슷한 구문에서 literal 만 다른 경우에 CURSOR를 공유 하도록 한다.

이 파라메터는 세션에서 조절이 가능하다.

ALTER SESSION SET cursor_sharing = FORCE;

또는 init.ora에서 세팅 할 수 있다.

참고: 이 파라메터가 시스템으로 하여금 literal 값을 바인드 변수로 변경하기 때문에

CBO 모드에서 실제 값에 의한 최고의 실행 계획 수립은 더 이상 사용할 수 없다..

SESSION_CACHED_CURSORS parameter

이 파라메터는 인스턴스 레벨과 세션 레벨에서 다음 명령어로 값(숫자)을 줄 수 있다..

ALTER SESSION SET session_cached_cursors = NNN;

Nnn 세션에서 얼마나 많은 cached cursor을 가질 수 있는지 결정한다.

구문이 언제 parsing 되던지 간에 오라클은 먼저 개인 세션 캐쉬에 의해 포인트 되는 구문을 찾는다. 만약 공유 버전의 구문이 존재하면 그것을 사용할 수 있다. 이것은 자주 파싱 되는 구문에 짧은 액세스를 제공하여 CPU를 덜 사용하고 soft 또는 hare 파싱 보다 아주 적은 latch를 사용하다.

동일 구문을 session cache에 위치 시키기 위해서 동일 cursor 안에서 3번 파싱 되어야 한다. 모든 session cache cursor가 사용 중이면 최근에 사용된 엔트리는 무시된다. 따라서 새로운 session cursor cache를 연다.

즉 parsing 시 shared cache 보다 session cache를 먼저 찾기 때문에 파라메터에서 값을 많이 할당 할수록 빠르게 parsing 찾을 수 있다.

당신이 이 파라메터를 이미 사용하고 있다면 처음 50으로 설정하도록 권장한다.

Bstat/estat의 통계값 중에 session cursor cache hits의 값은 cached cursor의 값이 어떤가 보여준다.

Cached cursor의 크기는 필요에 의해서 증가 시키거나 줄일 수 있다. 파라메터는 특히 오라클 FORMS (자주 open / close) 어플리케이션에서 유용하다.

CURSOR_SPACE_FOR_TIME parameter

이 파라메터는 남아 있는 Cursor의 일부분이 다른 구문을 실행 사이에 pinned 될 것인지 아닌지 제어한다. PINNED의 의미는 Cursor가 닫힐 것인지 아닌지 구문하는 것이다.

이것은 공유 구문이 자주 사용 되지 안거나, 특별히 Cursor의 pinning/unpinning 이 있는 경우 사용한다. 많은 latch wait는 kglnc: child 와 kglupc :child 때문이다.

이것은 pinning/unpinning curosr이다.

shared pool이 충분히 부하를 감당할 만큼 큰 것을 확인해야 한다. 그렇지 않다면 성능은 더욱 나빠지거나, 결국 ora-4031 가 발생한다.

Shared_pool이 매우 작다면 ora_4031은 자주 발생한다.

전체적으로 어플리케이션에서 Cursor가 낭비되고 있다면 낭비되는 Cursor는 일정 한 기간 동안 operation후 성능에 많은 메모리 낭비하여 성능을 악화 시킨다.

<Bug:770924> (Fixed 8061 and 8160) ORA-600 [17302] may occur

<Bug:897615> (Fixed 8061 and 8160) Garbage Explain Plan over DBLINK

<Bug:1279398> (Fixed 8162 and 8170) ORA-600 [17182] from ALTER SESSION SET NLS...

CLOSE_CACHED_OPEN_CURSORS parameter

오라클 8i에서 없어진 이 파라메터는 pl/sql Cursor가 트랜잭션이 commit 되었을 때 닫을 것인지 아닌지를 제어하는 파라메터이다. 기본값이 false 이며 pl/sql cursor가 commit 이후 계속 열려 있도록 유지 한다. 이것은 hard parse를 줄일 수 있다. True로 설정하면 SQL이 사용되지 않을 때 shared pool로 부터 flushed 될 기회를 증가 시킨다.

SHARED_POOL_RESERVED_SIZE parameter

SHARED_POOL_RESERVED_SIZE는 지속적으로 설명 되었기 때문에 간단하게 설명한다. 이 파라메터는 7.1.5에 소개 되었고, SHARED_POOL의 일부분을 큰 메모리 할당을 위해서 보존하겠다는 의미 이다.

실제적인 관점에서 SHARED POOL 이 매우 크지 않거나 SHARED_POOL_RESERVED_MIN_ALLOC 이 기본 값보다 작게 설정 되어 있다면 SHARED_POOL_RESERVED_SIZE는 SHARED_POOL_SIZE의 10%가 되어야 한다.

SHARED_RESERVED_SIZE가 10% 보다 매우 크다면 중요한 메모리 낭비일 것이다 오직 수 MB 면 충분하다.

SHARED_POOL_RESERVED_MIN_ALLOC가 낮다면 많은 space request는 SHARED POOL RESERVED 영역을 사용하려고 하기 때문에 10%는 매우 작은 값이 된다. 즉 SHARED_POOL_RESERVED_MIN_ALLOC을 작게 하려면 SHARED_POOL_RESERVED_SIZE를 늘려야 한다.

Reserved의 사용량은 v$shared_pool_reserved 의 free_space 컬럼을 사용하여 조회한다.

SHARED_POOL_RESERVED_MIN_ALLOC parameter

Orace8i에서 히든이다. 이 파라메터는 일반적으로 디폴트 값으로 남겨야 한다. 비록 어떤 경우에는 4100 또는 4200 의 값이 (8i 디폴트 4400) shared pool에 대한 경합을 약간 줄일 수 있다.

SHARED_POOL_SIZE parameter

이 파라메터는 shared pool 크기를 제어한다. Shared pool의 크기는 성능에 많은 영향을 준다. 너무 작다면 공유 될 수 있는 sql 이 적고, literal SQL이 많이 사용되고 SHARED POOL 이 너무 크다면 너무 많은 작은 메모리 CHUNK가 INTERNAL MEMORY FREELIST를 구성하게 되며 차레차레 SHARED POOL LATCH를 오랫동안 잡도록 하므로 성능에 영향을 미친다. 이것은 작은 SHARED POOL이 큰 것 보다 성능에 유리함을 보여준다 즉 메모리가 너무 CURSOR CHUNK가 많은 것 보다 SHARED POOL이 작으면 그만큼 CHUNK가 작다는 의미이다. 이런 문제는 8.0.6에서 급격히 줄었고 8.1.6에서 BUG 986149를 유발한다.

공유 풀 자체는 결코 너무 크게 생성되면 안된다. PAGING 과 SWAPPING성능에 가장 악 영향을 미친다.

_SQLEXEC_PROGRESSION_COST parameter (8.1.5 onwards)

이 히든 파라메터는 8.1.5에서 소개 되었다 디폴트 값을 가진 이 파라메터는 SQL 공유성 문제를 유발한다. 이 값을 0으로 세팅 하는 것은 SHARED POOL안에 다중 버전 구문을 만드는 것을 피할 수 있다.

Eg: Add the following to the init.ora file

# _SQLEXEC_PROGRESSION_COST is set to ZERO to avoid SQL sharing issues

# See Note:62143.1 for details

_sqlexec_progression_cost=0

참고 : 0으로 했을 때 다른 문제는 v$sesson_longops 뷰가 long running query값을 가지지 못한다. <Note:68955.1>

Precompiler HOLD_CURSOR and RELEASE_CURSOR Options

Precompiler를 사용할 때 shared pool의 작동은 release_cursor과 hold_cursor에 의해서 수정 될 수 있다. 이러한 파라메터는 library cache내 Cursor의 상태를 결정하고 세션은 실행이 끝난 후 이 Cursor를 Cash한다. <Note:73922.1>

DBMS_SHARED_POOL.KEEP

이 프로시져는 (dbmspool.sql in rdbms/admin) shared pool내 object를 keep 하기 위해서 사용된다.

Dbms_shared_pool.keep는 package, procedure, function , trigger (7.3 +) , sequence(7.3.3.1 +) 을 keep 하도록 한다. <Note:61760.1>

일반적으로 자주 사용되는 package는 표시하는 것이 바람직하다. 오브젝트는 인스턴스 startup 후 짧은 시간 안에 keep 되어야 한다. 데이터 베이스가 shutdown 된 이후에는 자동으로 이것을 할 수 없기 때문이다.

오라클 7.2 이전에는 DBMS_SHARED_POOL.KEEP 은 정확히 SHARED POOL에 KEEP된 모든 OBJECT를 LOAD 할 수 없다. 따라서 각 PROCEDURE내 KEEP 되어야 하는 DUMMY PROCEDURE가 포함하는 것이 현명하다. DUMMY PROCEDURE는 DBMS_SHARED_POOL.KEEP를 호출한 후 호출될 수 있기 때문에 오브젝트가 전체적으로 로드 될 수 있도록 한다. 이것은 7.2에서의 문제이다.

Flushing the SHARED POOL

많은 literal sql을 사용하는 시스템에서 shared pool은 조각나기 쉽고 동시성은 감소 할 수 있다.

Shared pool flushing는 종종 잠시동안 성능을 복원 할 것이다.그것은 작은 메모리 chunk가 coalesce 되기 때문이다. Flush 이후에 flushing이 공유 sql을 제거 하였기 때문에 성능은 임시적 사용 불능이 되기 쉽다. 그러나 shared pool fragmentation을 개선하기 위한 것이 아무것도 없다. 다음 명령어는 flush를 실행한다.

ALTER SYSTEM FLUSH SHARED_POOL;

보고와는 반대로 dbms_shared_pool.keep 명령으로 keep된 object는 위 명령어로 flush 되지 안을 것이다. 세션에 의해서 정확히 PINNED 된 모든 아이템(object or SQL) 은 또한 SHARED POOL에 남는다.

Shared pool Flushing은 모든 cached 된 sequence를 잠정적으로 시퀀스 범위의 차이를 남기고 모든 모두 flush 한다. 따라서 sequence 차이가 발생 할 수 있다. 그래서 그러한 차이를 막기 위해서 Dbms_shared_poo.keep(‘sequence_name’, ‘Q’)를 사용한다.

Using V$ Views (V$SQL and V$SQLAREA)

참고 : v$ view중 일부는 질의 응답을 위한 데이터를 얻기 위해서 연관된 latch를 얻어야 한다.

이것은 library cache와 SQL area에 대한 view에 대해서 주목할 만하다. 그런 view를 질의한 sql에 대해서 선택적이 되는 것은 일반적으로 타당하다. 특히 v$sqlarea 의 사용은 library cache latch에 많은 로드를 일으킨다.

참고 : v$sql은 종종 v$sqlarea를 대신해서 사용되고, latch 획득에 덜 영향을 준다. 이것은 v$sql은 group를 하지 않은 반면 v$sqlarea가 shared pool내 구문을 group by 했기 때문이다.

MTS and XA

오라클 8/8i XA 세션은 그 세션의 UGA를 shared pool에 넣지 않는다.

오라클 8의 large pool은 MTS를 위해서 사용 되기 때문에 shared pool 부하를 을 줄이는데 사용될 수 있다. 그러나 large pool내 많은 메모리 할당은 여전히 shared pool latch를 사용한다.

<Note:62140.1>

MTS 보다 dedicate 의 사용은 UGA가 shared pool 보다 오히려 개별 프로세스에 할당 되도록 한다.

개별 메모리 할당은 shared pool latch를 사용하지 않는다.

5.8.6.5 Useful SQL for looking at Shared Pool problems

Finding literal SQL

SELECT substr(sql_text,1,40) "SQL",

count(*) ,

sum(executions) "TotExecs"

FROM v$sqlarea

WHERE executions < 5

GROUP BY substr(sql_text,1,40)

HAVING count(*) > 30

ORDER BY 2

;

Finding the Library Cache hit ratio

SELECT SUM(PINS) "EXECUTIONS",

SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"

FROM V$LIBRARYCACHE;

Misses 율이 1% 이상이라면 library cache misses를 줄이도록 시도한다.

Checking hash chain lengths:

SELECT hash_value, count(*)

FROM v$sqlarea

GROUP BY hash_value

HAVING count(*) > 5

;

이 질의는 일반적으로 row를 리턴하지 않는다. 모든 Hash_values가 높은 counts를 가지고 있다면 당신은 bug의 효과를 볼 수 있거나, 비 정상적인 형태의 literal SQL을 볼 수 있다. 좀 더 조사하는 것이 타당하고 동일한 HASH_VALUE를 가지는 모든 statements를 리스트한다.

예) SELECT sql_text FROM v$sqlarea WHERE hash_value= <XXX>;

그리고 V$SQLTEXT로부터 동일한 전체 구문을 볼 수 있다면, 많은 Literal이 동일한 hash_value 에 mapping하는 것이 가능하다.

예) 오라클 7.3에서 literal 값이 구문에서 두 번 사용되고 두 구문이 정확히 32 개의 charecters를 가지고 있다면 동일한 hash_value를 가진다.

Checking for high version counts:

SELECT address, hash_value, version_count ,

users_opening ,users_executing, sql_text

FROM v$sqlarea

WHERE version_count > 10

버전은 일치하는 statement에 대한 기호이다. 그러나 이하 오브젝트와 바인드 등등은 sharable sql에서 서술된 것과 다르다. 높은 버전 count는 다양한 8i release 에서 발생 할 수 있다. 프로세스 모니터링의 문제 때문이다. 이것은 SQLEXEC_PROCESSING_COST를 0으로 세팅 하므로 쓸모없게 만들 수 있다.

Finding statement/s which use lots of shared pool memory:

SELECT substr(sql_text,1,40) "Stmt", count(*),

sum(sharable_mem) "Mem",

sum(users_opening) "Open",

sum(executions) "Exec"

FROM v$sql

GROUP BY substr(sql_text,1,40)

HAVING sum(sharable_mem) > <MEMSIZE> ;

MEMSIZE는 SHARED POOL size의 대략 10% 이다. 이것은 비슷한 LITERAL 구문이 있는지 없는지 보여줄 수 있다.. 또한 다중 version statement는 공유 메모리내 많은 양을 차지함을 설명한다.

· Allocations causing shared pool memory to be 'aged' out

· SELECT *

· FROM x$ksmlru

· WHERE ksmlrnum>0

· ;

이 select는 10개 이상의 로우를 리턴하지 않으며, k$ksmlru의 항목을 지운다. 그래서 output을 spool 하는 것이 안전하다. X$ksmlru 테이블은 마지막 질의 이후 공유 메모리에 할당된 내용을 보여 준다. 이것은 때때로 연속적으로 공간 요구를 하는 세션 또는 구문을 식별하는 데 유용하다. 만약 시스템이 잘 돌고, sql 공유도 잘 되고 있는데 가끔 느리다면 해당 sql을 돌려 원인을 식별 하는 데 유용하다. <Note:43600.1>

Issues in various Oracle Releases

Release 별 shared pool의 성능에 영향을 미치는 중요한 요소가 있다.

Increasing the CPU processing power of each CPU can help reduce shared pool contention problems in all Oracle releases by decreasing the amount of time each latch is held. A faster CPU is generally better than a second CPU.

각 CPU에 대한 CPU PROCESSING 능력의 증가는 shared pool의 경합을 줄이는데 도움이 된다. 더 빠른 CPU가 두개 보다 더욱 좋다.

5.9 TUNING THE BUFFER CACHE

5.9.1 Buffer Cache 특징 ?

a. buffer cache의 size? DB_BLOCK_SIZE * DB_BLOCK_BUFFERS

b. 한번에 read하는 size? DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE

(클수록 i/o 횟수가 줆)

c. LRU list, Dirty list? (모든 buffer는 둘중 하나의 list에 포함 되어야 한다.)

이해를 쉽게 하기 위해 update문장을 예를 들어 실행이 되면

LRU list에서 free buffer를 찾는다. update될 buffer는 pinned buffer로 다시 dirty buffer로 바뀌고 MRU(Most Recently Used) end로 이동하고 Dirty list에도 등록이 된다.

redo log buffer에 수정된 내용 기록하고 db buffer cache에도 기록한다.

dirty list나, LRU list의 임계치에 이르게 되면 DBWRn process가 disk에 writer하고

dirty buffer는 free buffer로 바뀐다.

d. Free buffers, Dirty blocks, Pinned buffers? 위에서 언급된 내용처럼

Free buffers : memory와 disk의 image가 같음 (select 된 블록)

Dirty blocks : memory와 disk의 image가 다름

Pinned buffers : 사용중인 block

5.9.2 Server Process가 db buffer cache로 data 읽어오는 step (매우 중요)

a. hash function 이용하여 해당 block이 memory내에 존재하는지 찾는다.

이때 hash_bucket을 이용하며, hash_bucket에 있는 hash_table을 잡기위한 buffer cache chain latch에 대한 경합이 있을 수 있다.

è 있으면 해당 블록을 MRU end로 이동 시키고 끝

è 없으면 다음 step으로 진행

b. 찾는 블록이 없기 때문에 disk에서 읽어 메모리에 올리기 위해서 빈 메모리 블록을 찾는다. 이때 server process가 LRU list로부터 free block을 찾아 data file 로부터 읽어온다. 이렇게 읽어온 블록은 LRU list의 MRU end로 이동, LRU list를 찾는 동안 발견된 dirty blocks을 dirty list에 등록한다. 이때 dirty list의 임계치를 초과하면 DBWRn process가 data buffer cache로부터 dirty block을 flush한다. 또 free block을 찾다가 search 임계치까지 free block을 못찾아도 DBWRn process가 data buffer cache로부터 dirty block을 flush한다.

c. block이 consistent하지 않으면 current block(자신에게만 유효한 블록) 과 rbs segment로부터 이전 block으로 돌아간다

◆ DBWn 이 작동하는 case

- Dirty List Exceeds its Size Threshold

- Search Threshold Exceeded : LRU list를 임계치까지 scan하고도 free buffer

못 찾을 경우.

- Three-Second Time-Out : 3초마다

- LGWR Signals a Checkpoint : LGWR가 checkpoint 발생 할 경우

- Alter Tablespace Offline Temporary (or) Alter Tablespace Begin Backup

- Drop Object

- Pinged Block : OPS(Oracle Parallel Server)환경에서 1번 node에 request한 data를 2번 node에도 동일하게 들어오면 동기화 때문에 어쩔 수 없이 file에 쓰게 되는데 이를 ping이라 함.(performance에 악영향)

- shutdown Normal, immediate, Transactional 의 경우 (Abort만 빼고)

◆ Data나 application Design에 따라, data를 access하는 방법에 따라 많이 차이가 나는데 예를 들면 full table scan 같은 경우에는 dirty buffer가 MRU end쪽으로 가는게 아니라 Memory에서 빨리 내리려는 목적으로 LRU end 쪽으로 간다.

(특히 OLTP성 업무와는 달리 DW성 업무는 장기간 full table scan을 해야 하므로 hit ration보다는 I/O에 point를 두고 tuning을 한다.)

결론적으로 모든 변경 블록이 한번에 DIRTY BUFFER에 기록되는 것이 아니라. Free buffer를 찾을 때 발견된 DIRTY BUFFER만 기록된다.

그리고 DBWR이 그런 블록을 내려 쓴다. 이렇게 내려써진 블록은 메모리와 disk의 이미지가 똑같기 때문에 MRU END로 이동한다.(FREE BUFFER가 된다)

SELECT에 의한 BUFFER는 LRU END로 밀려서 없어지게 되는 것이다 즉 free buffer는 lru end로부터 검색되어지므로 select에 의해서 사용된 블록은 free 상태이므로 덮어써진다. DML(INSERT, UPDATE)에 의한 변경 블록이 DIRTY LIST에 등록 된다.

즉 select 는 dirty 개념이 없고, update ,insert, delete에서 개념이 사용된다.

Data Blocks

만일 사용자의 요청으로 데이터가 변하면 새로운 데이터 블록인 after image 가 되어 블록에 반영이 되고 이전의 데이터는 before image 가 되어 아래와 같이 기록된다.

Block Status

•Clean : 디스크에서 읽어져 캐시내에 존재하는 블록

•Dirty : 데이터에 변경이 생긴 블록

•Pinned : 현재 사용중인 블록

•Free : 빈 영역

◆ Using multiple buffer pool : 같은 LRU list이나 중요성이 비슷한 무리끼리 level을 두어 경쟁하도록 함. 총 3개의 buffer pool이 있다.

a. recycle pool : 자주 사용되지 않는 object에 사용.

50% 내외의 hit ratio(transaction 중에만 있으면 된다.)

b. keep pool : 거의 100%의 hit ratio.

I/O를 줄이기 위해 object를 memory에 keep

c. default pool : 90% hit ratio 이상 정도면 적당.

(일반적으로 single buffer cache일 때와 같다.)

multiple buffer cache로 사용해도 특별히 설정 안하고 쓴다. (자동으로 잡힘)

◆ 설정 방법

init<SID>.ora

db_block_buffers = 20000

db_block_lru_latches = 6

buffer_pool_keep = (buffers:14000,lru_latches:1)

buffer_pool_recycle = (buffers:2000 ,lru_latches:3)

==> 위와 같이 설정하면 default는?

위에서 전체 buffer는 20000이고 keep이 14000, recycle이 2000이므로 나머지 default는

20000-(14000+2000) = 4000 이다.

default의 latch도 마찬가지로 6-(1+3) = 2 이다.

◆ multiple buffer pool 사용하기

create index cust_idx ... storage (buffer_pool keep ...);

alter table customer … storage (buffer_pool recycle);

alter index cust_name_idx rebuild storage (buffer_pool keep);

◆ sizing buffer pool

먼저 analyze하여 사용되는 block의 수를 알 수 있다.

analyze ... compute statistics; --> object전체를 analyze

analyze ... estimate statistics; --> 1024개의 block만 sampling하여 analyze header

block scan하여 high water mark 읽으므로 얼마나 사용되었는지는 estimate

로도 알 수 있다.

◆ Recycle buffer pool 설정?

recycle pool을 disable시켜두고 찾는다.

peak running time시에 다음 query가 얼마나 많은 block을 쓰는지 알아낸다.

$ORACLE_HOME/rdbms/admin/catparr.sql을 돌리고

SQL> @catparr

SQL> select owner#, name, count(*) blocks

2 from v$cache

3 group by owner#, name

4 order by 3 desc;

OWNER#	NAME	BLOCKS
O	IDL_UB1$	502
O	ATTRCOL$	208
0	CLU$	208
0	COL$	208
0	ICOL$	208
0	IND$	208
0	ICOLDEP$	208
0	LOG$	208

-- 전체 object에 대해 block의 합이 recycle buffer에서 사용될 것이고

이 값의 1/4 이 recycle pool size로 잡으면 된다.

◆ buffer pool hit ratio

SQL> @catperf

SQL> select name,

2 1-(physical_reads/(db_block_gets + consistent_gets)) "HIT_RATIO"

3 from sys.v$buffer_pool_statistics

4 where db_block_gets + consistent_gets > 0;

NAME HIT_RATIO

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

KEEP .981... -> 거의 100% 에 육박해야 함

RECYCLE .503... -> 50%정도면 적정

DEFAULT .793... -> 90%정도까지 맞추자.

◆ buffer pool의 dictionary view 정보

SQL> select * from v$buffer_pool

2 where id <>0;

ID	NAME	LO_SET_ID	HI_SET_ID	SET_COUNT	BUFFERS	LO_BNUM	HI_BNUM
1	KEEP	3	3	1	1000	0	0
2	RECYCLE	4	6	3	500	0	0
3	DEFAULT	1	2	2	548	0	0

- where id <>0 을 넣는 이유? id 는 0~3까지 있는데 0은 필요 없는 정보(일종의 bug)

- LO_SETID,HI_SETID 가 3,3 면 DB_BLOCK_LRU_LATCHES 3~3까지(1개) 할당됨을 의미

◆기타 buffer pool 관련 정보들 : v$sysstat, v$system_event

SQL> select name,value from v$sysstat

2 where name = 'free buffer inspected';

NAME VALUE

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

free buffer inspected 10 --> free buffer찾기 위해 skip한 buffer수

(dirty, pinned buffer)

◆ Caching Tables

OLTP 성 업무에서 가능한 한 Full Table Scan은 삼가 해야 한다.

Full Table Scan은 LRU end로 가서 빨리 memory에서 내려가는데 Cache를 사용하면 MRU end로 가서 memory에 오래 남게 할 수 있다.

사용방법 :

a. Create a table using the CACHE clause

ex) create table <table명> cache

b. Alter a table using CACHE clause

ex) alter table <table명> cache

c. Code the CACHE hint clause into a query

ex) select /*+ cache */ * from codes;

select --+ cache * from codes;

◆ LRU latch 경합

SQL> select name, sleeps/gets "LRU Miss%"

2 from v$latch

3 where name = 'cache buffers lru chain';

NAME LRU Miss%

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

cache buffers lru chain 0 --> < 1%가 목표

위에서 뽑은 수치 LRU Miss% 가 1% 를 넘게 되면 DB_BLOCK_LRU_LATCHES 수를 늘려준다.

latch의 최대 수는

- Number of CPUs * 2 * 3 ==> buffer pool당 2개가 max, buffer pool종류가 3가지

(keep, recycle, default)

- Number of buffers / 50

위 두 값 중 작은 값을 max로 잡아야 한다.

◆ Free List 경합

SQL> select s.segment_name, s.segment_type, s.freelists, w.wait_time,

2 w.seconds_in_wait, w.state

3 from dba_segments s, v$session_wait w

4 where w.event = 'buffer busy waits'

5 and w.p1 = s.header_file

6 and w.p2 = s.header_block;

위의 것을 돌려보아서 나오는 것들(wait이 걸리는 것들)은 object를 새로 생성해준다.

free list는 alter....등의 dynamic한 처리는 되지 않는다. (object 재생성 해라)

5.9.3 buffer cache tuning

5.9.3.1 개요

7.1 과 8.1에서 DBWR과 buffer cache 튜닝에 관하여 설명하고 있다.

시스템이 다음과 같은 상황이라면 튜닝이 필요한 시기이다.

- cache buffers lru chain 또는 cache buffer chain 에 대한 경합이 발생

- average write queue의 크기가 큰 경우

- write complete waits에 대한 대기가 발생하는 경우

- free buffer waits 또는 buffer busy wait이 발생하는 경우

5.9.3.2 buffer cache란

오라클은 sga 영역중 buffer cache에 database block copy를 보관한다.

cache는 동시에 하나의 블럭에 대한 여러 copy를 가질 수 있으면 디스크와 내용이 다른

dirty block을 가진다 = update 경우 데이타베이스 writer(DBWR)은 dirty blcok을 disk

로 내려쓰는 역할을 한다.

buffer cache내의 모든 블럭은 lru알고리즘의 영향을 받는다. 즉 프로세스가 free

buffer를 필요로 할때 lru list의 끝에서 부터 scan을 시작한다. 그리고 non-dirty

buffer를 찾아서 사용한다.

cache buffers lru chain latch는 lru list에 대하여 (serial)로 작업을 한다.

(오라클 8i에서 초기 버전과 다른 알고리즘을 사용하여 lru list를 관리한다)

■ buffer cache activity

buffer cache hit ratio는 얼마나 많이 요구된 블럭이 메모리에서 재사용가능 한가를

나타낸다. v$sysstat view에서 hit ratio을 알수 있다. oracle 버전에 따라 내용이 다

를 수 있다.

오라클 버전별 계산 방법은

for Oracle7/8 is:

( physical reads )

hit ratio = 1 - -----------------------------------

( consistent gets + db block gets )

for Oracle9i

1 - ( physical reads - (physical reads direct + physical reads direct (lob)) )

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

( db block gets + consistent gets - (physical reads direct + physical reads direct (lob)) )

오라클 9i에서는 Dynamic Buffer Cache Advisory feature 기능을 이용하여 buffer cache

의 크기를 동적으로 변경 가능 하다.

주의할 것은 너무 과도한 buffer cach의 크기는 cpu와 메모리에 대한 과부하를 유발한다.

그러므로 장시간 모니터를 통하여 알맞은 크기를 산정해야 한다.

5.9.3.3 buffer cache 튜닝

buffer cache의 증가가 튜닝에 도움이 되지 못하는 경우 증가 시켜서는 안된다. 즉 full

scan과 같은 경우 buffer cache의 크기를 증가 시켜도 도움이 되지 못한다.

buffer cache를 증가 시키기 전에 반드시 latch 또는 wait event가 발생하지 않도록 app

를 수정해야 한다.

튜닝 전에 buffer cache 성능에 영향을 미치는 중요한 2가지 요소를 알아보자.

1) 얼마나 자주 cache에 블럭이 위치하는가? cache에 위치되는 각각의 block은 다른

블럭을 lru에서 move down한다.

2) DBWR가 얼마나 빠르게 dirty buffer를 cache로 부터 제거 하는가? 즉 dirty buffer

에 대한 wait는 없는가?

위에서 buffer cache에 영향을 미치는 두 가지가 어떤 영역에 영향을 미치는가?

- execution plan

buffer의 재사용 비율에 가장 영향을 미치는 부분이다.

예를 들어 구문이 10000 블럭을 access 하는 경우 , 구문이 실행 되었을 경우 그 구문

자체로 느린 것 뿐만 아니라 다른 사용자의 사용에 의해서 느려진다.

즉: select * from employee where empid=1023 and gender='MALE';

employee가 큰 테이블이고 구문이 자주 사용(Gender 컬럼에 인덱스)되는 블럭을 cache내

존재하게 된다.

(lru list에서 move up을 시키기 때문이다) 이것은 다른 block을 move down시킨다.

- sorting 과 sort 파라메터

sort가 buffer cache와 DBWR에 미치는 영향에 대해서 알아보자

sort는 sort_area_size 영역을 이용한다. 즉 sort가 많은 메모리를 사용하고 그것이

sort_area_retained_size 크기에 적합하다면 disk에 block을 써야할 필요는 없다.

즉 area_size 보다 큰 sort 는 disk의 temporary segment에 write되어야 한다.

■ sort block을 디스크로 보내는 두 가지 방법이 있다.

- buffer cache를 통해서 (DBWR)

- sort direct write

첫번째 DBWR이 작업을 하는 것이 일반적으로 디폴트이며, 7.1에서는 오직 이 옵션만

가능하였다.

sort block은 buffer cache에 위치하기 때문에 다른 블럭을 age out 시킨다.

sort block이 lru end에 위치하면 DBWR은 디스크로 flush 한다.

결국 개별적으로 사용되고 있는 sort block도 다른 sort에 의해서 age out 된다.

그러므로 DBWR에 대한 경합이 유발된다.

7.2부터 sort_direct_writes가 사용되어 buffer cache에 대한 sort block의 사용의 피할

수 있었다. 따라서 일반적으로 sort_direct_writes는 true로 사용하는 것이 바람직하다.

단. sort_direct_writes는 true로 할 경우 세션에 대한 추가적인 메모리가 할당 된다.

load가 작은 환경이라면 sort_direct_writes는 개별적인 작업이 약간 더 오래 걸린다.

즉 block이 buffer cache에 위치하고 있는 경우 메모리의 확장과 같은 내부적인

operation이 발생하여(sort block gets 처럼)

디스크(temporary segment) 보다는 오히려 buffer cache에서 완료 될 수 있기 때문이다.

오라클 8i 이후에 sort_direct_writes는 제거 되었고, sort_area_size에 맞지 않는 경우

항상 direct write를 사용한다.

- cached table과 full table scan

create / alter table에 의해서 cache 옵션이 사용된 경우 오라클은 내부적인 mark를

한다. cache_size_threshold의 값 안에서 를 테이블에 공간이 제공된다. 이 파라메터는

테이블의 full scan에 의한 블럭을 lru list의 MRU end로 위치시킨다 (일반 full scan은

lru list의 lru end에 위치해서 빠르게 age out 된다.

테이블의 대한 부적당한 caching은 buffer cache의 문제를 야기할 수 있다.

이 파라메터 또한 8i에서 제거 되었다. 즉 multiple buffer를 이용하여 이 파라메터

없이 cache될 할 수 있다.

- data clustering

한 블럭에 존재할 수 있는 row의 수와 연관된 개념이다.

io와 관련되어서 얼마나 잘 data가 cluster 되었는냐가 중요한 문제이다.

예: 하나의 테이블에서 자주 rows를 fetch하며 인덱스를 통하여 두가지 값으로 구성되어

있는 한 컬럼을 access 한다.

만약 각 인덱스 블럭이 100rows 로 구성되어 있다면 극단적으로 두가지 결론이 있다.

A) 테이블 로우가 각각 다른 물리적인 블럭에 위치

(인덱스를 통하여 100개 블럭 access)

B) 테이블의 모든 로우가 인접한 블럭에 모두 위치

(소량의 블럭만 access)

pre_sorting 또는 데이타의 구조 또는 다른 컬럼을 where 추가하는 경우

즉 다양한 상황에 따라서 논의 될 수 있는 문제이다.

- parallel query

몇몇 query는 오라클 7/8/9에서 parallel query를 이용하여 더 빠르게 수행 될 수

있다. 그와 같은 query는 parallel이 buffer cache를 피하여 direct read를 수행 할

수 있기 때문에 buffer cache에 이득이 된다.

5.9.3.4 DBWR의 성능 향상

DBWR 성능은 다양한 플랫폼과 다양한 버전에 따라 다르므로 일반적인 개념을 설명한다.

다음 서술은 DBWR가 cache로부터 블럭을 제거하는 비율에 영향을 미친다.

- 물리적 디스크 구성 (stripe size, speed, layout)

- raw device AS file system

- 데이타의 분포도

- async i/o 사용여부

- 오라클 데이터베이스가 async I/O를 사용할 수 있는 경우에는 dbwr를 하나로 지정

하거나(V7) dbwr_io_slave를 여러 개로 지정(V8, V8i)하고 만약 async I/O를 사용

하지 못할 경우에는 multiple dbwr를 사용한다.

oracle 7 : db_writers (only 한 개)

oracle 8/8i: dbwr_io_slave (multi 가능)

참고

만일 system에서 Asynchronous I/O의 사용이 불가능할 경우 multiple DBWRs를 사용하시기 바랍니다. Oracle에서는 disk당 최소한 한개 이상의 DBWR를 사용하기를 권장합니다. 만일 asynchronous I/O가 사용되는 경우에는 한개의 DBWR를 사용하십시요. I/O가 parallel로 되는 경우에는multiple DBWRs를 사용할 이유가 없습니다.

■ DBWR_IO_SLAVES

이 파라미터는 오라클 8 이전의 DB_WRITERS를 대체한다.

오라클 8에서는 DB_WRITER_PROCESS가 DB_WRITERS를 대체 하지만 DBWR_IO_SLAVES 파라미

터와 함께 사용할 경우 문제가 발생할 수 있다.

DBWR_IO_SLAVES 는 slave writer process가 OS에서 지원할 경우 asynch I/O를 수행 하

도록 허용한다. 즉 DB_WRITERS와 DBWR_IO_SLAVES는 동시에 둘을 사용할 수 없다.

- _db_block_write_batch를 크게 한다. ( 8I에서 제거)

- 오라클 8이사에서 multiple buffer pool 사용

참고: 위에서 언급된 내용은 플랫폼에서 최적의 상태로 되어 있는 DBWR에 영향을 미친다. 올바르게 asyn io를 설정하기 위해서 o/s page size 의 배수로 db_block_size를 설정한다.

5.9.3.5 buffer cache size과 config

- oracle 9i에서 db_cache_size는 default pool을 위해서 사용되는 파라메터이다.

즉 primary block size를 위한 공간이다.

DB_nk_cache_size를 이용하여 오라클 9 부터 다른 block size를 가진 tablespace를

구성할 수 있다.

DB_nk_cache_size는 nk buffers를 위한 공간이다. nk의 값은 DB_block_size 보다

커야 한다.

즉 만약 primary block가 8K 이면 4k를 위한 cache 는 만들수 없다.

- db_block_lru_latchs는 오라클 7.3 이상에서 buffer cache 다중 lru chain를 허용

한다. 이것은 디폴트로 적당한 값으로 설정되어있다. 그러나 init.ora에 명시적으로

선언가능하다.

oracle 7에서 db_block_lru_latches는 2 * cpu 개수 여야 한다.

oracle 8에서 db_block_lru_latches는 2 * cpu 개수 multiple pool 개수이다.

- oracle 8에서 db_block_lru_statistics는 true로 설정한다.

- oracle 9에서는 수동으로 심지어 _db_block_lru_statistics로도 설정 할 수 없다.

현재 이 값은 각각의 buffer cache에 대해서 (default , keep , recycel, nk cache)

cpu_count의 1/2 로 hard code되어 있다.

5.9.3.6 관련된 wait와 latch

■ latch : cache buffer chain latch

이 latch는 sga에서 data block를 찾기 위해서 요구되어진다. buffer cache는 block의 chain으로 구성되어 있기 때문에 각 chain은 cache buffer chain latch의 child latch에 의해서 보호 받는다.

이 latch에 대한 경합은 single block에 대한 매우 잦은 access 가 원인이다.

이 경우 application을 수정해야 한다. 즉 parameter 로 수정되지 않는다.

오라클 8i의 경우 수많은 hash bucket이 존재하면 각각의 latch로 구성되어 있다.

그래서 각 latch 에만 많은 buffer가 존재한다.

■ cache buffers lru chain latch

프로세스가 buffer cache에서 lru 알고리즘에 따라서 buffers를 옮길 필요가 있을

경우 이 래치를 요구하게 된다. 즉 free buffer를 찾거나, dirty buffer 제거 할 때

이 래치에 대한 경합은 multiple buffer pool을 사용하여 피하거나 lru latch의 수를

증가 시켜서(db_block_lru_latchs: 많은 시스템에서 디폴트로 충분히 설정되어 있다.)

피할 수 있다. sql tuning은 또한 요구되는 data block을 줄이기 때문에 효과적일 수

있다.

■ buffer busy wait

이 event는 다중 세션에서 동일한 블럭을 읽으려고 하는 경우 발생하는 일반적은 wait

event이다. 또는 다중 세션에서 동일 블럭에 대하여 변경이 완료되기를 기다리는 경우도

발생한다. buffer busy wait는 블럭의 type에 따라 조치 방법이 다르다.

블럭의 type은 v$waitstat 와 x$kcbfwait를 조회하여 알 수 있다.

data blocks:

- 블럭당 row의 수를 줄인다. (pctused, pctfree, db_block_size)

- right_hand_indexs(많은 프로세스에서 동일한 지점으로 insert하는 경우 )

reverse key 인덱스를 고려한다.

segment header:

- freelists 증가

- 너무 작은 extent size는 테이블이 규칙적으로 성장하는 경우 header에 대한

경합이 된다. 따라서 extent size를 증가시킨다.

undo header:

- rbs segment를 추가하여 rbs당 트랜잭션의 수를 줄인다.

- transaction_per_rollback_segment의 수를 줄인다.

undo blocks:

- rbs 크기를 증가 시킨다.

free buffer wait

- 일반적으로 DBWR이 충분히 빠르게 buffer를 비우지 못할 때 발생한다.

- DBWR을 증가 시킨다.

5.10 TUNING THE REDO LOG BUFFER

insert, update, delete와 같은 DML(Data Manipulation Language)나 create, alter, drop과 같은 DDL(Data Definition Language) 수행시 db recovery시 사용하기 위해

oracle server process는 redo entry 들을 user's memory space에서 redo log buffer로 copy한다. 또 LGWR process는 redo log buffer를 active online redo log file에 write 한다.

실제 contention이 별로 없는 부분이고 redo log buffer는 LGWR가 작동할 때 clear되는데 LGWR가 작동하는 경우는

a. commit시

b. Redo log buffer가 2/3이상 차면

c. redo log 양이 1M 이상 되면

d. timeout (3초)시

e. DBWR가 작동할 때

◆ Tuning Redo Log Buffer : - log_buffer parameter를 변경하며 아래의

allocation retries <1% 이내로

- log_buffer의 size는 os block size의 배수이어야 함

다음을 보면 SECOND_IN_WAIT에 110 이나 쌓여있으면 log writer가 빨리 일을 처리하지 못하고 있다.

다음과 같은 waiting event가 없도록 하는 것이 objective

SQL> select sid,event,seconds_in_wait,state

2 from v$session_wait

3 where event like 'log buffer space%';

SID	EVENT	SEOCOND_IN_WAIT	STATE
5	log buffer space	110	wating

아래는 내가 쓰려는 공간이 아직 disk에 쓰이지 않아 기다리는 통계정보

SQL> select name,value

2 from v$sysstat

3 where name in ('redo buffer allocation retries', 'redo log space requests',

4 'redo entries');

NAME	VALUE	Desc
redo entries	78	redo log buffer를 얼만큼 사용했나
redo buffer allocation retries	0	내가 쓰려는 공간이 아직 disk에 쓰이지 않아 기다리는 통계정보
redo log space requests	0	0 이 아니면 혹시 archive log file에 full 있나 확인

◆ LGWR가 freeing buffer를 하는데 느려지는 현상 monitor & tuning

- redo log file에 경합이 있는지(log files 의 i/o 여러 disk에서 동시에 발생하도록)

- v$system_event에 log file switch completion에 wait가 있는지.

있다면 redo log file size를 늘리자

SQL> select event,total_waits,time_waited,average_wait

2 from v$system_event

3 where event like 'log file switch completion%';

- DBWn가 끝나지 않아 Redo log file의 spining을 위해 LGWR가 waiting

n redo log group의 size와 수를 확인해라.

n 중요: DBWR가 느려지면 로그 버퍼 freeing도 느려진다.

n alert.log file을 보면 spining시 "CHECKPOINT NOT COMPLETE" 가 자주 발생 (2~3분마다)

n 다음과 같이 v$system_event 를 확인해도됨

SQL> select event,total_waits,time_Waited, average_wait

2 from v$system_event

3 where event like 'log file switch (check%';

- archiver가 redo log를 archived redo logfile에 쓰지 않아 LGWR가 기다리는 경우

n archive device가 full이 아닌지 확인

n redo log group 을 추가 (로그 파일에 쓰기에 대한 여유 확보)

n 다음과 같이 v$system_event 를 확인

SQL> select event,total_waits,time_Waited, average_wait

2 from v$system_event

3 where event like 'log file switch (arch';

n ARCn process가 바쁠 때 LGWR가 새로운 ARCn process를 띄우도록 다음 parameter 설정 log_archive_max_processes = n -> n개 만큼 까지 ARCn process를 띄우면서 작업

n data block, redo log block이 깨졌는지 안 깨졌는지 확인하는 mechanism인

db_block_checksum parameter가 true로 되어있는지 확인

5.10.1 what i the redo log buffer

redo log buffer는 데이타베이스의 변경정보를 가지는 SGA내에서 circular buffer 이다.

이 buffer는 redo entries는 저장한다.

redo entry는 재구성 또는 redo을 위해서 반듯이 필요한 정보로 구성되며 필요시 복구하기 위해서 사용된다.

redo entry는 사용자 메모리로부터 SGA내 redo buffer에 copy된다.

redo entry는 log buffer의 sequential 공간을 사용한다.

lgwr은 redo buffer를 active online인 log file에 Write한다.

log_buffer 파라메터는 bytes 단위로 buffer의 크기를 결정한다.

일반적으로 값이 클 수록 log file i/o를 줄인다. 특히 트랜잭션이 long query이거나 많은 경우 디폴트 값은 o/s block의 4배이다.

5.10.2 redolog latches

데이타 블럭에 변경이 발생할 경우 필요로 한다.

■ 다음 단계를 통하여 redo log buffer에 redo record를 생성한다.

1) 더 높은 scn을 생성하는 프로세스는 없다.

2) redo record를 쓰기 위한 공간을 찾는다. free 공간이 없다면 lgwr은 디스크

로 buffer를 비우거나 log switch를 한다.

3) redo log buffer에서 필요로 하는 공간을 할당한다.

4) log buffer에 record를 기록하고 recover를 목적으로 하는 적당한 링크를

구성한다.

위와 같은 목적을 위해서 latch를 사용한다.

즉 더 높은 scn을 생성하는 프로세스가 없어야 한다는 것은 buffer에 동시 access를

할 수 없다는 것이다.

■ redo copy latch

이 래치는 위에서 언급한 1) ~ 4) 까지 수행되는 동안 필요로 한다.

log_simultaneout_copies는 redo copy의 수를 결정한다. free space를 확보하기 위한

log switch가 발생하고 한번의 log switch가 더 일어날 경우 오직 redo copy는 추가로

발생된다. 즉 init.ora에 명시한 값이 모두 기동 되는 것이 아니라 위 상황이 되어야

추가로 기동한다는 것이다.

■ redo allocation latch

이 래치는 buffer에 공간을 할당하기 위해서 요구되는 latch이다.

오라클 9.2 이전에는 redo allocation latch는 하나였으며 그래서 entry 는 serial 하게

redo buffer를 사용하였다. 9.2에서 redo allocation latch는 log_parallelism 파라메

터에 의해서 결정된다.

redo allocation latch는 각 트랜잭션 entry에 대해서 log buffer cache에서 메모리를

할당 받는다. 트랜잭션이 작다면 또는 오직 cpu가 하나인 경우 redo allocation latch는

log buffer cache에 해당 트랜잭션 데이타를 copy 한다.

free space를 얻기 위해서 log swtich가 필요한 경우 이 latch는 redo copy latch 처럼

release된다.

■ redo writing latch

이 래치는 하나인데 multi process가 lgwr 프로세스로 하여금 동시에 log switch 를 요

구하지 못하도록 한다. free space를 필요로 하는 프로세스는 lgwr로 하여금 write를 실

행할 것인지, log switch를 실행할 것인지,그냥 대기할 것인지를 결정하기 전에 이

latch를 획득해야 한다.

5.10.3 redo log latch 관련 init parameter

오라클 7과 오라클 8에서 redo log buffer에 대한 latch 할당을 조절하기 위한 두개

파라메터가 있다.

■ log_simultaneous_copies : 시스템이 한 개 이상의 cpu를 가지고 있는 경우 redo

copy의 수를 조절한다.

■ log_small_entry_max_size: log_simultnaoues_copies가 0 이 아닌 값으로 설정되어

있고, 트랜잭션 entry의 크기가 log_small_entry_max_size

보다 작은 경우 트랜잭션 entry는 redo allocation latch에

의해서 수행된다. 즉 이 값 보다 큰 것은 redo copy latch에

의해서 수행된다.

오라클 8i/9.0에서 redo entry의 크기에 상관없이 redo copy latch는 항상 요구된다.

그래서 size 체크는 발생하지 않는다.

log_simultaneous_copies는 없어졌으며 redo copy latch의 수는 cpu의 두배가

디폴트이다. log_small_entry_max_size 또한 없어진 파라메터이다.

오라클 9.2에서 multi redo allocation latch가 가능하게 되었다. (log_parallelism)

log buffer는 log_parallelism에 의해서 나누어져 각각이 init.ora의 log_buffer 크기를

가진다. 각 영역에 할당된 작업은 특정한 redo allocation latch에 의해서 보호된다. redo copy latch의 수는 여전히 cpu 두배 이다.

5.10.4 redo log 관련 성능문제 검사 및 해결

redo log buffer에 대한 경합은 dml/ddl이 실행전에 반듯이 redo entry를 redo buffer에

기록해야 하기 때문에 성능에 영향을 미친다.

경합은 latch 경합 또는 과도한 log buffe내 free 공간에 대한 요구로 나타난다.

참고: 일반적인 log buffer에 대한 경합은 언급된 latch가 지속적으로 top wait 가

아니라면 문제가 되지 않는다. 경험상 redo 에 대한 경합은 i/o가 원인인 경우

가 대부분이다.

■latch contention

miss ratio와 immedaite miss ratio를 나타낸다.

SELECT substr(ln.name, 1, 20), gets, misses, immediate_gets, immediate_misses

FROM v$latch l, v$latchname ln

WHERE ln.name in ('redo allocation', 'redo copy')

and ln.latch# = l.latch#;

miss와 get의 비율이 1% 이상이라면 또는

immediate_miss와 immediate_gets + immediate_misses)의 비율이 1% 이상이라면

latch 경합이 존재한다고 볼 수 있다.

참고 : 오라클은 redo copy latch보다는 먼저 redo allocation latch 튜닝 할 것을

권고한다.

☞ 오라클 7/8

redo allocation latch에 대한 경합은 log_small_entry_max_size를 줄여서 redo copy 를

쓰도록 한다. 권고값은 redo size의 평균값이며 (v$sysstat에서 구할 수 있다. redo

size/redo entries) redo copy latch에 대한 경합은 log_simultaneous_copies 값을 증가

시킨다. 권고값은 cpu 두배

☞ 오라클 8i/9.0

redo allocation latch에 대한 경합은 nologging을 사용하여 전체적 redo entry양을

줄인다. log_buffer 를 증가시켜서 latch 경합을 줄인다.

_log_io_size 파라메터를 이용하여 줄일 수도 있다.

redo copy latch경합이 있다면 _log_simultaneous_copies를 증기 시킨다.

디폴트 cpu 2배

☞ 오라클 9.2

redo allocation latch에 대한 경합이 있다면 log_parrallelism을 증가 시킨다.

redo latch에 대한 경합이 있다면 _log_simultaneous_copies를 증가 시킨다.

디폴트 cpu 2배

■ free space 요구 경합

redo log space requests 는 사용자 프로세스가 몇 번 redo log file에 대한 wait를

했는지 보여준다. 즉 buffer에 대한 경합이 아니다.

이 통계값은 v$sysstat에서 알 수 있다. 디폴트로 이 테이블은 오직 sys에 의해서 이용가능하며 select any table 권한이 있는 경우(system 유저)도 가능 하다.

SELECT name, value

FROM v$sysstat

WHERE name = 'redo log space requests';

이 값이 0에 가까워야 한다. 이 값이 지속적으로 증가 한다면 buffer에 대한 space를 대

기해야 한다. 즉 log file에 써야 하는데 쓰지 못하니까 결국은 buffer에서도 write가

일어나지 않는다. 이것은 checkpoint 또는 log switch에 의해서 발생 할 수 있다.

checkpoint와 archive를 튜닝 한다.

5.11 DATABASE CONFIGURATION AND I/O ISSUES

- locally managed TS 사용(8i new feature: 이전까지는 Dictionary Managed TS)

- Detect I/O Problem

- minimize I/O contention & 적당한 device분산

- 적당한 striping

- checkpointing tuning

- DBWn process I/O tuning

참고 : 여태까지는 dictionary managed tablespace(TS에 관련된 모든 정보들이 system TS에 있었으나) 8.0부터는 locally managed tablespace(bitmap으로 해당 TS의 file header에 정보로 관리)

--> Ts의 extent를 균등하게 (uniform size keyword로) 관리

◆ 각 필수 server process 들이 일으키는 i/o file (이 i/o를 minimize하자)

a. CKPT - data file, control file에 write

b. DBWn - Data file에 write

c. LGWR - log file에 write

d. ARCn(DB mode 가 archive일 때) - log, control file에서 read, archive control file에 write

e. server - data file에서 read

◆ Oracle File Striping

a. OS Striping

- 적정한 stripe size결정하여 os striping software 나 RAID

- 적정한 striping size는 DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT 의 배수로 정함

b. Manual Striping

- create table, alter table command로 object생성시 minextents를 1보다 크게 생성 하고, 각 extents는 striped data file보다 좀 작게

ex) alter table <tablename> allocate extent (datafile 'file명' size 10M);

- parallel query 사용

◆ full scan

a. DB_FILE_MULTI_BLOCK_READ_COUNT 조절

b. v$session_longops view로 long-runing full table scans를 monitoring

(이 view에서 안보이면 dbms_application_info package의 set_session_longops procedure 를 사용한다.

SQL> select sid, serial#, opname,

2 to_char(start_time,'HH24:MI:SS') as START_TIME,

3 (sofar/totalwork)*100 as percent_complete

4 from v$session_longops;

◆ I/O bottle neck을 찾기

A. report.txt에서 찾기

SQL> Rem I/O should be spread evenly accross drives. A big difference between

SQL> Rem phys_reads and phys_blks_rd implies table scans are going on.

SQL> select table_space, file_name,

2 phys_reads reads, phys_blks_rd blks_read, phys_rd_time read_time,

3 phys_writes writes, phys_blks_wr blks_wrt, phys_wrt_tim write_time,

4 megabytes_size megabytes,

5 round(decode(phys_blks_rd,0,0,phys_rd_time/phys_blks_rd),2) avg_rt,

6 round(decode(phys_reads,0,0,phys_blks_rd/phys_reads),2) "blocks/rd"

7 from stats$files order by table_space, file_name;

TABLE_SPACE	FILE_NAME	READS	BLKS_READ
scott_data	/disk2/scott_dat.dbf	61098	416752
scott_index	/disk2/scott_ind.dbf	0	0

여기서 주의해야 할 점은 data에서는 I/O 가 크나 index에서는 없는 것은 index가 잘 사용되지 않거나 필요한 index가 부족 하다는 것을 나타냄

group1	Group2	Group3
nember1-1	Nember2-1	Nember3-1	Disk1
nember1-1	Nember2-1	Nember3-1	Disk2

- contention을 최소화 하기위해 log file size를 적절히

- spinning(member1-1을 다쓰면 member2-1로 넘어가는것)시 waiting 없도록 group수는 충분히 만든다. group이 두개밖에 없고 spinning시 log file이 DBWn에 의해 data file로 update되지 않았을 경우 waiting하므로..

- 각 group과 member는 분리하여 fast devices에 저장

◆ Archive Log File Configuration

- 하나의 ARCn process가 read 하는 것으로 부터 LGWR 가 다른 disk에 쓰도록

- shrare the archiving work : ARCn process들이 바빠서 workload를 감당 못하면 LGWR process가 새 ARCn process를 생성한다. manual하게 하는 방법은 다음과 같다.

alter system archive log all to <log_archive_dest>

- archive speed 개선

log_archive_max_processes -> 8i new feature.

log_archive_dest_n -> n은 5개 까지 줄 수 있다.

log_archive_duplex_dest -> secondary archive destination

log_archive_min_succeed_dest ->

1: log_duplex 중 하나만 끝나면 archiving 끝난 것으로 보겠다.

2: 필수로 두개 다 끝나면 archiving 끝난 것으로 보겠다.

- checkpoint 수를 줄이기 위해 online redo log file을 size를 적절히

- LGWR가 다음 log group으로 overwrite하기 전에 여유시간을 늘려주기 위해 redo log group을 적절히 추가

- 다음 parameter들을 조정하여 checkpoint발생을 제어 가능

fast_start_io_target : 1000 block으로 지정되어있고 현재 instance recovery해야

하는 상황이라면 1000개 이상 복구하지 않겠다는 의미

log_checkpoint_interval : log file의 size가 이 이상 되면 checkpoint 발생

log_checkpoint_time : time경과마다 checkpoint 발생

db_block_max_dirty_target

◆ Multiple I/O Slaves : I/O 만 전담하게 하는 꼬붕 process를 생성해서 사용가능

- nonblocking asynchronous I/O requests를 제공

- DBWn,LGWR,ARCn, backup process들에 적용 가능

- asynchronous I/O 가 사용가능하면 일반적으로 추천하지 않음

이 기능 사용시엔

disk_asynch_io = false

tape_asynch_io = false로 해두는 것이 좋다.(bug가 존재)

- naming convention : ora_iNnn_SID

dbwr_io_slaves=4 로 되어있으면 DBW0 process는 다음 4개의 slave process를 spawn한다.

ora_i101_SID

ora_i102_SID

ora_i103_SID

ora_i104_SID

slave process는 첫번째 호출 때 생성되고 workload많아 idle한 slave process가 없을 때마다 하나식 spawn

◆ Multiple DBWn processes : DBWn의 꼬붕인 i/o slave를 띄우느니 DBWR을 더 많이

띄우겠다는 의미 (둘다 띄우는건 안된다.)

- db_writer_processes에 개수 지정

하지만 db_block_lru_latches의 개수에 한정된다.

즉 db_block_lru_latches=4 이고 db_writer_processes=10 이라도 DBWn 은 4개만

뜬다.

◆ Tuning DBWn I/O

- db_block_max_dirty_target parameter로 DBWn 이 dirty buffers에 더 자주 쓰도록

- DBWn은 Dirty block 개수가 low limit 이하로 떨어지면 천천히 작업

- DBWn은 Dirty block 개수가 high limit 이상으로 올라가면 빨리 작업

low limit : min(max(DB_BLOCK_MAX_DIRTY_TARGET,100), buffers)

high limit : min((low limit*12)/10, buffers)

- default value : (2*32)-1

◆ Using Oracle Blocks Efficiently

Tablespace > Segments > Extents > Blocks

먼저 block은 I/O의 최소 단위이고 여러 개의 OS Block으로 이루어질 수 있으므로

OS상의 Block의 배수로 설정 하는 것이 좋겠죠? (같다면 더 좋을 수도....)

◆ Allocating Extent

Dynamic extention은 Oracle Server가 space가 부족할 때 자동으로 extent를 할당하여

segment를 늘려 주는 것 인데 Dynamic하게 extention을 하게 되면 free space를 찾기 위해 recursive SQL statement를 수행하게 되므로 performance를 떨어뜨리는 원인이 됩니다. Dynamic extention을 하지 않게 하기위해 locally managed tablespaces 를 생성하셔야 합니다.

그리고 segment size도 적정하게 해야 하구요.(충분히 크게 잡고 해보세요)

◆ Dynamic allocation을 피하기 위해 다음과 같은 방법이 있습니다.

a. 먼저 free block이 10%가 안되는 segments를 뽑아보고 dynamic allocation이 일어날 object를 미리 allocate 한다.

SQL> select owner,table_name,blocks,empty_blocks

2 from dba_tables

3 where empty_blocks / (blocks+empty_blocks) <.1;

b. locally managed tablespace를 생성한다.

command는 다음과 같습니다.

create tablespace user_data_1

datafile ' ...../...../aa.dbf'

size 100M

extent management local

uniform size 2M;

간단히 설명하면 2M의 extent들로 tablespace내 segment를 100M/2> = 50 개 구성해서 사용한다는 의미

- 이렇게 사용하는 장점은 Dydnamic extention에 의해 불규칙적으로 사용시 발생하는 split을 주기적으로 reorg 할 필요가 없어지고,

- 연속적인 block 사용을 위한 coalescing도 필요 없고(size가 균등하므로)

- recursive call도 줄어듭니다.(여기서 가장 중요한 이유겠죠.)

5.11.1 optimize extent size

◆ Extent Size ?? Large Extent..

다만 extent는 init<SID>.ora file의 parameter중

(db_file_multiblock_read_count값 * 5) 의 배수로 잡아야 Performance에 좋다.

==> 이유는 oracle server가 five-block boundaries에 extent를 allocate하기 때문.

즉 db_file_multiblock_read_count = 32 라면 한번 I/O 에 32 block씩 읽어오는데 32 * 5 = 160 의 배수로 extent의 size가 정해져야 합니다. (I/O 가 extent마다 딱 맞게 끊어지도록..) 게다가 db_block_size의 배수로도 끊어지는 것이 당연히 맞겠죠.

그럼

db_block_size = 8192

db_file_multiblock_read_count = 32 라면

8192 * 32 * 5 = 1310720 => 1310720/1024=1280K => 1280K/1024=1.25M 의 배수로extent size를 지정 이 경우 5M,10M,15M, 뭐 이런 식으로 만들면 딱 떨어지네요.

다음의 두 I/O를 고찰하라.

1. Direct block I/O - 인덱스의 특정블록을 읽거나 인덱스를 통한 테이블블록을 읽는 것

2. Tablescan I/O - 테이블의 모든 블록을 읽는 것

direct block read의 경우

오라클은 블록 어드레스를 사용하여 원하는 블록을 요청한다. Extents의 수는 이 I/O 수행시간에 영향을 주지 않는다.

Tablescan인 경우

오라클은 각 I/O마다 X 블록을 요청한다.

여기서 X는 DB_MULTIBLOCK_READ_COUNT에 의해 설정된 값이다. 예를 들어 이 값이 16으로 설정되어 있다고 가정하자. 그리고 160 블록으로 구성된 하나의 extent를 갖고 있는 테이블을 scan한다. 그러면 10번의 I/O 호출이 발생된다. 테이블이 80블록으로 구성된 두개의 extent로 되어있다고 가정하자. 이 또한 (16 * 5) 10번의 I/O 호출이 발생한다. I/O 호출은 extent에서 다른 extent로 교차할 수 없다.

즉 다른 extent로 I/O를 할 때는 다른 I/O 호출이 생긴다. Multiple extent에서 extent 크기기가 db_multiblock_read_count의 배수가 아니면 extra I/O가 생긴다. 예를 들어, 두개의 extent를 가진 테이블이 있는데 첫번째 extent는 100block이 두번째 extent는 60block이 있다고 하자. 첫번째 extent에는 16블록마다 각 6번의 call (16 *6 =96 블록)이 있고 7번째 call은 4block만 해당한다. 이 extra call은 full call보다 이롭지 않다. 두번째 extent에는 3개의 full call(16 * 3 = 48 블록) 과 12block에 대한 4번째 call이 있다. (이것은 11개의 CALL 이 발생한다)

extent의 수를 줄여 성능 개선이 된 것은 사실상 reorg를 하기 위해서 exp/imp를 했기

때문이다.

테이블이 drop되고 다시 생성될 때 성능개선이 되는 두개의 주된 일이 진행된다.

1. 각 블록은 row들로 가득 채워진다.

2. high-water mark는 가장 낮은 지점으로 설정된다.

Extent 크기를 DB_MULTIBLOCK_READ_COUNT와 상관지어 설정하면(배수로) extra multi-block call을 피할 수 있다. 그렇지 않으면 extent당 최대 하나의 extra multi-block I/O가 생긴다.

Number of Extents	Extent Size	Full Calls	Extra Calls	Impact of 1 Extra Call per Extent
Low (5)	Small (100 blocks)	30	5	중요하지 않음
Low (5)	Large (5000 blocks)	1,560	5	중요하지 않음
High (500)	Small (100 blocks)	3,000	500	중요함 - total I/O call에서 extra call이 차지하는 비중이 큼
High (500)	Large (5000 blocks)	15,600	500	중요하지 않음

위에서처럼 크기가 작고 많은 extent인 경우에 이 extra call은 전체 I/O에서 무시할 수 없다. 이 같은 상황은 INITIAL, NEXT의 크기를 적당히 설정함으로써 피할 수 있다.

그리고 이러한 extra call은 direct block I/O가 아니라 tablescan에서만 발생한다.

I/O 가 하나일 경우 single extent는 disk head movement(seek time)을 줄이는 것이 사실이지만 다중 I/O 환경에서는 의미가 퇴색해져 가고 있다.

또한 index의 경우 direct I/O를 하기 때문에 extra I/O의 문제는 없다. 또 복수개의 extent가 동시적인 index I/O 요청을 수행하는데 유용할 수가 있다. (index_ffs)

Index Depth and Entry Deletions

인덱스는 자라고 변하기 때문에 다음과 같은 두 경우가 발생한다.

● 인덱스 키가 알파벳처럼 제한된 범위를 갖는 데이터 값에 근거하고, 범위 내에서 많은 삽입이 일어난다면 특정 데이터 값이나 문자 내에서 밀도를 조절하기 위해 인덱스 높이는 증가한다. 이것은 인덱스의 깊이 또는 레벨이라 불린다. 인덱스가 더 깊어질수록 root block에서 leaf block까지의 I/O 수가 증가한다. 안 좋게 들리겠지만 그렇지 않다. 왜냐하면 busy system에서 branch block은 일반적으로 모두 캐쉬 된다. 그래서 I/O가 physical이 아니라 logical이다. 그러므로 상대적으로 비용이 적게 든다. 그러나 매우 사용이 많은 인덱스인 경우 이 깊이를 줄이기 위해 인덱스를 rebuilding하는 것으로부터 약간의 성능이 증가될 수 있다. 이것은 시스템 전체 환경에서 조사되어져야 한다.

● 테이블 블록처럼 인덱스 블록도 deletion시에 hole이 생긴다. 인덱스 블록이 비어지면 자동적으로 다시 사용할 수 있다. 시간이 지나 인덱스는 부분적으로 공간이 빈 블록들을 가질 수 있다. 그래서 몇몇 블록들은 더 적은 인덱스 entry를 가지게 되어 있어 그렇지 않은 블록과 비교할 때 읽기에 있어서 불리하다. 하지만 이 문제는 테이블에서 언급된 것만큼 그렇게 중요하지 않다. 인덱스 엔트리는 전체 로우에 비해 작다. 그래서 삭제로 인한 빈 공간은 블록에 비해 작다. 게다가 많은 시스템에서 로우를 삭제하지 않는다.

◆ Database Block Size

a. block을 읽어오는 횟수를 줄이기 위해서

- large block size로 설정

- PCTFREE 를 좀 작게 해서 block을 좀 가득가득 사용하는 법

(select 성 업무의 경우)

- row migration이 적게 일어나도록

(update가 잦으면 PCTFREE 를 여유 있게 설정해야 row migration이 적다.)

b. default는 2KB 나 4KB 이고 64KB까지 허용 (OS block size의 배수로 설정)

OS I/O size는 DB_BLOCK_SIZE 와 같거나 커야 함

◆ PCTFREE, PCTUSED

- PCTFREE : default 10, update가 없다면 아예 0으로 잡자. (그러나 10 이하는 10으로

잡히고 딕셔너리에는 storage에 값으로 보인다.

- PCTUSED : default 40, delete되어 PCTUSED 밑으로 내려가는 경우 free list에

재등록된다.

◆ Migration, Chaining

- migration : update시 block space 부족하면 다른 block으로 row가 migration한다.

(이전 block에는 흔적을 남기는 이유는 index에서 rowid를 사용하므로 남기는 것이다)

- chaining : insert시 block이 2k이고 data(row)가 4k 이면 아예 쪼개져서 들어간다.

a. 확인방법 1 : analyze table, dba_tables에서 확인

SQL> select num_rows,chain_cnt from dba_tables

2 where table_name='EMP';

NUM_ROWS CHAIN_CNT -> chain_cnt = chaining건수 + migration건수

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

14 0 -> test db라 없네... 하지만 production에서 이 수치가 높으면

b. 확인방법 2 : report.txt에서 볼 수 있다.

아래 내용이 수행된 부분에서 Statistic 이 table fetch continued row 를 보면 된다.

SQL> select n1.name "Statistic",

2 n1.change "Total",

3 round(n1.change/trans.change,2) "Per Transaction",

4 round(n1.change/((start_users + end_users)/2),2) "Per Logon",

5 round(n1.change/(to_number(to_char(end_time, 'J'))*60*60*24 -

6 to_number(to_char(start_time, 'J'))*60*60*24 +

7 to_number(to_char(end_time, 'SSSSS')) -

8 to_number(to_char(start_time, 'SSSSS')))

9 , 2) "Per Second"

10 from

11 stats$stats n1,

12 stats$stats trans,

13 stats$dates

14 where

15 trans.name='user commits'

16 and n1.change != 0

17 order by n1.name;

◆ High-Water Mark

- High-Water Mark 이후 한번도 안쓴 block 해제

alter table <table_name> deallocate unused ...

- full table scan시 high water mark까지만 read하게 된다.그러므로 high water mark이내에 delete된 space들이 많으면 그만큼 performance에 영향이 있다.

- high water mark 위에 빈 block들이 많으면 performance에는 영향이 없으나 그만큼 공간 낭비

- truncate로는 reset이 된다. (delete로는 안됨)

◆ Table statistics (DBA_TABLES)

NUM_ROWS	table내 총 row수
BLOCKS	high-water mark 밑의 block수
EMPTY	high-water mark 위의 block수(아직 사용되지 않은)
AVG_SPACE	블록당 남아 있는 평균 크기 (BYTES)
CHAIN_CNT	table의 chained or migrated row 수
AVG_ROW_LEN	평균 row 길이(row overhead 포함)

◆ Index Reorganization : index는 주기적으로 재 생성해줄 필요가 있다.

index를 analyze하여 usage를 측정해보자

SQL> analyze index dept_idx validate structure;

Index analyzed.

SQL> select (del_lf_rows_len/lf_rows_len)*100 as index_usage

2 from index_stats;

INDEX_USAGE

-----------

16.4948454 ==> delete된 leaf row length로 방금 만든 index라 값이 정확치 않으나

10~20% 이상이 되면 index 재생성(rebuild) 필요하다.

- rebuild의 용도 : reorg ,tablespace,storage절 수정

reverse index <--> normal index로 switching

- rebuild중 query는 가능하고, 8i이상에서는 DML도 가능하다.

5.12 OPTIMIZING SORT OPERATIONS

5.12.1 Sort Process

Sort 할 size가 sort_area_size parameter보다 작을 경우 memory에서 sorting을 하고(이상적) 클 경우는 data는 작게 쪼개져(sort runs라 부름) 각 piece별로 sorting됨.

Sorting 되는 부분만 memory에 (sort_area_size만큼)만 올라와 있고 나머지는

temporary tablespace에 보관된다. 나중에 merge시에도 sort_area_size가 충분치

않으면 runs의 subset들이 여러 번에 나누어 merge.

SORT_MULTIBLOCK_READ_COUNT는 merge 진행 중 더 큰 부분을 disk에서 읽어 memory에 sort하도록 하는 parameter 초당 I/O가 너무 많고 CPU 가 idle한 상태면 이 값을 올려본다.

- 참고 : MTS의 경우 server process의 일부 memory공간인 sort area를 shared pool로 옮겨 사용한다.

a. sort_area_size

b. sort_area_retained_size : sorting 끝나면 fetch하기 위해 memory에 sorted rows를

가지고 있는데 sorting 할 때 보다는 적겠죠. 그래서 이 size만큼 줄어들 수 있다.

- dedicated server경우 : sort_area_retained_size 까지 줄여주면 release된 memory가 계속 process의 private영역에 남게 되어 (O/S에 반환되지 않음) 다른 놈들이 사용하지 못하므로 의미 없다. ==> 이때는 sort_area_size = sort_area_retained_size 로 하는 것이 일반적

- MTS의 경우 : sort_area_size > sort_area_retained_size 로 하여 사용하지 않는 memory는 반환하여 다른 놈들이 쓸 수 있도록.

◆ 분석방법

a. v$sysstat을 조회하여 sort(disk), sort(memory) 조사하여 disk sort ration 구한다.

SQL> select disk.value Disk, mem.value Mem, (disk.value/mem.value)*100 Ratio

2 from v$sysstat mem, v$sysstat disk

3 where mem.name = 'sorts (memory)'

4 and disk.name = 'sorts (disk)';

DISK MEM RATIO

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

0 18932 0 --> ratio가 5% 를 넘을 경우 sort_area_size를 늘린다.

b. sort_area_size 수정

◆ temporary space를 사용하기 : database 닫힐 때 drop 된다.

==> sorting 시작순간 extent할당하고 sorting 끝나면 해제하지 않고 다음에 들어온 sorting이 재사용한다.(부족하면 늘리고) ==> fragmentation문제 없다.

5.13 TUNING ROLLBACK SEGMENTS

◆ rbs는 db startup시마다 offline으로 바뀜 다만 init<SID>.ora 에

rollback_segments=.... , ..... , ....

위와 같이 설정해주면 startup시 online으로.. (단 public는 무조건 online)

◆ 각 rbs는 첫번째 extent의 첫 block에 transaction정보를 가지고 있는 transaction table이 있는데 rbs사용시 여길 먼저 읽고 update한다. 여러개의 transaction이 순차적, 순환적으로 쓰임.

◆ rbs의 사용 중 4개의 extent 가졌는데 1 -> 2 -> 3 -> 4 -> 다 쓰고 다시 1번 extent를 쓰려고 할 때 1번에서 사용중인 transaction(commit안된)이 있는 경우 2번이 비어있다고 할지라고 2번으로 건너뛰어 사용하는 것이 아니라 새로운 extent를 추가 확장하게 된다.

5.13.1 Rollback Segment의 tuning

a. transacton이 rbs를 access하기 위해 기다려서는 안된다.

(transaction이 돌고있는 동안 rbs extend는 없어야 한다.)

b. user와 utilitiy들이 rollbak을 덜하도록

d. 하나의 transaction이 rollback space를 전부써서는 안됨

e. 사용자들이 read-consistent image를 볼 수 있도록 : rbs 의 수 , size 고려

(snapshot too old 방지)

f. 다음 view들에서 각 정보를 얻을 수 있다.

v$rollname	online rbs의 수와 이름
v$rollstat	각 online rbs의 activity statistics
v$waitstat	system, nonsystem rbs의 data block,header block에 waiting된 누적 통계
v$sysstat	the number of consistent and data block gets
v$transaction	rbs를 사용하는 현 transaction

5.13.2 RBS Header contention 분석

SQL> select sum(waits)*100 / sum(gets) "Ratio",

sum(waits) "Waits",

sum(gets) "Gets"

from v$rollstat;

Ratio Waits Gets

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

0 0 33452 ==> Ratio < 1% 이어야 하고 아니면 rbs를 더 생성한다.

SQL> select class,count from v$waitstat

2 where class like '%undo%';

CLASS COUNT

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

save undo block 0

save undo header 0

system undo header 0

system undo block 0

undo header 0 --> 수가 많으면 역시 rbs header block contention

undo block 5

◆ report.txt(utlbstat/utlestat)

역시 waits/gets >5% 이면 header contention

SQL> select * from stats$roll;

UNDO_SEGMENT	TRANS_TBL_GETS	TRANS_TBL_WAITS	UNDO_BYTES_WRITTEN	SEGMENT_SIZE_BYTES	XACTS	SHRINKS	WRAPS
0	2	0	0	41408	0	0	0
1	3	0	1528	4186112	1	0	0
2	2	0	0	4186112	0	0	0

◆ RBS Contention 분석

SQL> select class,count from v$waitstat

2 where class like '%undo%';

CLASS COUNT

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

save undo block 0

save undo header 0

system undo header 0 전체의 합을 A 라 하고(waits)

system undo block 0

undo header 0

undo block 5

SQL> select value from v$sysstat

2 where name='consistent gets';

VALUE

----------

508598 --> 이 gets B 라 하면(gets)

A/B < 1% 이어야 적정

◆ RBS 의 수는?

a. OLTP성 : 4개 transaction당 하나의 RBS를 사용하도록

b. Batch : space 자체의 부족이 많을 수 있으므로 각 concurrent job당 하나의 큰 RBS를 구성하자

ex) SQL> set transaction use rollback segment large_rbs;

ð 이전 tranx이 끝난 상태(commit,rollback된 상태)에서 실행해야 error 안난다.

SQL> execute dbms_transaction.use_rollback_segment('large_rbs');

◆ RBS의 Size는? ==> 얼마로 하는가가 매우 중요하다.

a. storage parameter

- 충분히 크게 initial을 잡자.

- next는 initial과 동일하게, pctincrease는 0으로...(모든 extent가 동일한 size로)

- 모든 rbs는 동일한 size로.

- minextents=20 으로..

b. tablesapce size

- rbs tablespace는 충분한 free space를 갖고 있도록..

- optimal setting은 나중에 rbs를 shrink하게 할 수 있다.

◆ Transaction Rollback Data의 sizing

- delete는 expensive (rollback불필요하면 truncate 쓰자)

- insert는 rowed 만 보존되므로 rbs 최소 사용

- update는 변경되는 columns 수에 따라 사용량 정해짐

- index걸려있으면 더 많은 RBS 사용

(update하면 RBS 에 old data, old index, new index value 기록됨)

#** LOB data type에 변경위한 rbs space 사용하지 말자.

특정 transaction이 rbs block을 얼마나 사용했나 하는 정보는 다음을 통해서 본다.

SQL> select s.username, t.used_ublk, t.start_time

2 from v$transaction t, v$session s

3 where t.addr = s.taddr;

◆ rollback data 사용량을 볼 수 있는 방법...

- 먼저 rollstat을 보고

SQL> select usn, writes from v$rollstat;

◆ rbs를 덜쓰기 위해 commit을 규칙적으로 하고

개발자들도 가급적 long transaction을 하면 위험 -> 불가피한 경우

set transaction으로...

import 시 commit=y , buffer_size keyword setting등을 하자.

export 시 consistent=n 로 setting -> set transaction read only 와 같은 효과

SQL*Loader : ROWS keyword로 COMMIT interval을 setting

5.13.3 소개

extent라고 하는 연속된 multi-block으로 구성되어있다.

circular 형태로 extent는 사용된다. 즉 현재 extent가 full이 되면 다음 extent로 이동한다. 트랜잭션은 RBS에 현재의 위치에 대한 record를 남기며, record size에 의해서 pointer를 이동 시킨다. (record를 기록하는 위치는 RBS head이다) 여기서 tail이라는 용어는 가장 오래된 active transaction record의 시작 위치를 말한다.

일반적인 RBS 구성을 하기 위해서 세그먼트의 수와 각 세그먼트의 크기는 balance 되어야 한다. RBS에 대한 경합을 줄이기 위해서 충분한 RBS 개수가 되어야 한다.

개별 트랜잭션이 각각의 트랜잭션을 처리할 만큼 충분히 커야 한다.

오라클은 전체적인 RBS 사용을 고르게 하기 위해서 RBS를 round-robin 방식으로

할당한다.

5.13.4 size and number of rollback segments

1) 하나의 트랜잭션은 오직 하나의 RBS를 사용한다

2) 다중 트랜잭션은 동일한 extent를 사용할 수 있다 (그러나 블럭은 안된다)

3) RBS head는 결코 tail에 의해서 현재 사용중인 RBS로 이동할 수 없다.

4) ring에 존재하는 extents는 결코 head 의 순서를 어기고 skip 되지 않는다.

5) head가 next extent를 사용할 수 없다면 , 새로운 extent를 할당하여 ring에

입력한다.

이러한 원리로부터 트랜잭션의 시간은 트랜잭션의 크기만큼 중요한 것이다.

가령 하나의 트랜잭션이 오직 1 byte를 수정하지만 오랫동안 기다린다면 이것은

RBS로 하여금 사용되었던 RBS가 재사용이 필요한 경우 (active라서 head가 tail을 재사용하려고 해도 안되기 때문에) 확장된다.

■SIZE

RBS가 충분한 크기인지 아닌지 결정하기 위해서 고려되어야 할 두 가지가 있다.

1) head로 하여금 너무 빠르게 wrap around 하거나 tail을 잡도록 해서는 안된다.

이것은 RBS가 확장되는 결과를 초래한다.

2) 만약 데이터를 엑세스 하거나 자주 변경하는 long query가 있다면 RBS가 wrap

around(snapshot) 되지 않기를 바랄 것이다.

RBS가 필요로 하는 크기는 데이타베이스의 활성 activity에 영향을 받는다.

우선 고려되어야 하는 것은 데이타베이스의 일반적인 상황에서 RBS의 활동이다.

즉 특별한 경우는 특별하게 처리되어야 한다.

RBS의 수는 RBS 경합을 유발하지 않아야 하는 개수이다.

즉 v$waitstat를 사용하여 모니터한다. wait는 명확한 경합을 나타낸다.

이 view는 instance 기동 후 몇 번의 wait가 있는지 보여준다.

SELECT CLASS, COUNT FROM V$WAITSTAT WHERE CLASS = '%undo%';

0 이 아닌 값이라면 RBS header에 대한 경합을 나타낸다.

크기와 개수를 구하기 위해서 우선 작은 RBS를 가지고 시작하여 application으로

하여금 extend를 시키는 것이다.

■ TEST 단계

1) RBS tablespaces 생성

2) RBS 개수 선정

3) 동일한 extent 크기를 가진 RBS 생성

(full size 될 경우 segments는 20 ~ 30 개 사이가 되도록 크기 선정)

4) minextents는 2

5) system RBS는 online / 트랜잭션 수행

6) RBS 경합을 조사

7) RBS 확장을 조사

test 하는 동안 RBS의 최대 크기는 config 할 때 사용되어야 하는 크기이다.

이 크기는 minimum coverage size 라고 한다. 만약 경합이 보인다면 세그먼트의 수

를 조절하여 다시 테스트한다.

또한 가장 큰 크기가 10개의 extent를 넘지 않는다면 또는 30 개 이상이라면 extent size를 늘이거나 줄이거나 한다. 그렇지 않다면 공간 낭비가 발생한다.

RBS extent를 size 하는데 있어서 동일한 size를 가지는 것은 강력히 권장되는

사항이다. 사실상 아래 서술된 모든 전략은 모든 RBS가 동일한 크기의 extent를 가진다

는 전제 하에 설명 된 것이다.각 RBS에 대한 extent의 최소 개수는 최고의 성능을

위해서 20개 정도로 한다.

■왜 최소 extent가 20개 인가?

RBS는 동적으로 공간에 대한 요구가 있을 경우 할당이 일어나고 더 이상 필요 없는 경우 해제된다 (단 optimal 설정시) RBS 구성이 더 적은 수의 extent를 가질수록 규칙적인 공간의 할당과 해제는 덜 발생한다. 예를 들어서 200Mb의 RBS가 오직 100MB 2개로 구성된 경우 , 이 RBS에 추가 공간이 요청되면 100MB 를 더 할당해야 한다. 이 경우 바로 RBS는 50% 증가하며 실제로 필요 이상이 할당된다.

반대로 10MB 20개로 구성되어 있다면 추가 공간이 필요시 10MB가 할당된다. RBS가 20개 또는 그 이상의 extent로 구성되어 있는 경우 수 많은 extents에서의 모든 single change는 5% 이상 변경될 뿐이다. 결국 할당과 해제에 더욱 유연하다.

이와 같이 제안된 20개 이상으로 extent의 개수를 증가시키는 것은 할당과 해제에 더욱 유연할 수 있다. 그러나 테스트에서 20개 이상으로 설정시 성능이 빠르게 감소하는 것을 보여준다.

추가적으로 할당과 해제는 cost-free operation이 아니다. 즉 resource를 많이

사용한다. 데이타베이스는 extent operation시 성능 감소를 유발한다.

각각의 extent의 비용은 minor이다. 그러나 전체적인 cost는 증가한다.

■ SYSTEM RBS 사용 시기

create database를 사용하여 database를 만드는 경우 , 오직 단일 RBS가 생성된다.

이것이 SYSTEM RBS이며 SYSTEM TBS에 생성된다.

SYSTEM RBS는 다른 RBS와 다른 한가지가 있다. 그것은 SYSTEM RBS는 오직 SYSTEM

TBS내의 object에서 발생하는 트랜잭션을 위해서 사용된다.

이것은 SYSTEM RBS의 중요한 목적이 DDL 트랜잭션에 대한 rollback을 처리하기

때문이다.

즉 data dictionary table에 대한 트랜잭션을 다루기 위해서 사용된다.

물론 SYSTEM TBS에 존재하는 일반 object에 대한 transaction 정보는 SYSTEM RBS에서 다루어진다.

SYSTEM TBS에 존재하는 다른 RBS는 이와 같은 제약 사항을 가지지 않는다.

오라클 7.1.5 이전에는 데이타베이스 생성하는 동안 적어도 하나의 RBS가 system에

생성되어야 했다. 그렇지 않다면 추가적인 tablespace는 생성될 수 없었다.

(create tablespace는 SYSTEM TBS의 dictionary table에 대한 트랜잭션이기 때문)이런 경우 'ora-1552 cannot use the system rollback segment for non-system tablespace x' 에러가 유발된다.

이 경우 SYSTEM TBS 이외 TBS에서 발생하는 모든 rollback에 대해서 에러를 유발하게 된다. 하나의 NON SYSTEM RBS라도 online 이면 에러는 발생하지 않는다. 새로운 데이타베이스 생성시 첫번째 operation은 RBS TBS 를 생성하고 NON SYSTEM RBS를 생성하는 것이며, 추가로 생성된 SYSTEM RBS는 offline 되거나 drop된다.

online으로 남겨두면 SYSTEM TBS 는 단편화가 발생한다.

■read consistency and ora-1555 errors

오라클에서 일반적으로 발생하는 에러로 메뉴얼상 RBS가 너무 작아서 발생한다고

하지만 잘못된 내용이다. 즉 RBS를 증가 시켜도 도움이 되지 못한다.

트랜잭션 시작시 오라클은 time 정보(정확히 scn)를 추적한다. 구문을 수행하기

위한 row 정보를 수집하는 동안 오라클은 각 row에 대해서 현재 트랜잭션 시작

이후에 변화된 row가 있는지 없는지 확인한다.

변화된 row가 있다면 트랜잭션 시작시에 존재한 값을 RBS에서 찾는다. commit되지

않은 경우 정보는 항상 RBS에 존재한다. 그리고 snapshot 문제도 발생하지 않는다. 그러나 트랜잭션 시작 후 변경이 commit된 경우 그리고 RBS 공간이 다른 트랜잭션 정보로 채워진 경우 또는 optimal로 인한 shrink 되었다면 오라클이 row에 대한 RBS 정보를 찾을 수 없기 때문에 읽기 일관성이 깨진다.

RBS 크기가 어떠하던지 commit된 정보가 overwrite될 수 있는 가능성이 있다.

RBS 크기나 개수를 증가 시키는 경우 트랜잭션은 덜 덮어써지게 될 수 있다.

하지만 해결책은 아니다.

참고: snapshot too old는 주로 select에서 발생한다. select는 RBS 정보를 생성

하지 않는다. RBS 정보는 create table as select에서 발생하는데 select 보다는 create에서 발생한다.

ora-1555를 줄이기 위한 최고의 방법은 long query를 튜닝 하는 것이다. 또는 long query를 다른 트랜잭션이 없는 시간에 수행하는 것이다.

access 되고 있는 테이블에서 update가 발생하는 동안 snapshot 에러는 가능 한다. 가능하다면 트랜잭션을 짧게 나누어서 실행하는 것이 도움이 될 수 있다. 그러나 둘 다(수행시간을 변경하거나 단위를 쪼개기) 불가능 하다면 RBS 구성을 통해서 해결해야 한다.

- 모든 RBS를 online으로 한다. online인 RBS가 많을수록 많은 트랜잭션이 더욱

넓게분포된다. 따라서 RBS를 덮어 쓸 가능성을 줄인다.

- 모든 RBS를 동일한 크기의 online 상태로 한다.

5.13.5 setting optimal on rollback segments

RBS storage 절에서 optimal 이라는 파라메터가 있다. 이것은 'optimal size'를

bytes로 나타낸다. 이 값이 설정된 경우 오라클은 세그먼트를 이 크기로 유지하려고 한다. RDBMS는 정의된 optimal 크기와 같거나 큰 RBS를 가지려 한다.

추가적인 공간이 optimal size 보다 크다면 RBS는 optimal 보다 크게 확장된다. 그러나 언젠가 이 크기를 shrink하기 위해서 공간을 해제한다.

optimal 크기로 shrink를 수행하는 것은 즉각적인 것이 아니다.

트랜잭션이 정해진 값(optimal size) 이상으로 RBS를 확장 했을 때 RBS는 optimal size로 줄어들지 않는다. 만약 optimal size를 바로바로 줄이려고 한다면 성능에 지대한 악영향이 있을 것이고 읽기 일관성 문제를 유발할 것이다.

Extent 해제는 세그먼트에서 확장을 유발했던 트랜잭션 이후에 다른 트랜잭션이 수행되는 동안 일어난다. 즉 다른 트랜잭션이 수행되는 동안 extent가 다음 extent로 head를 옮길 때 세그먼트는 check 된다.

세그먼트 크기가 optimal 이상이라면 rdbms는 extent를 해제할지 여부를 결정한다.

extent는 연속된 두개의 extents에서 더 이상 active 트랜잭션이 없는 경우에 해제

한다.

왜 연속적인 두개의 extent인가. 오직 next extent만 사용가능 여부에 대한 check가 일어난다면 오라클은 이것을 해제할 것이다 그러면 오라클은 현재 트랜잭션이 수행되는 동안 계속해서 next extent가 사용 중이라고 알고 extent에 대한 재할당을 하기 때문이다.

필요하다면 RDBMS는 optimal size를 만족 할 때 까지 다중 extent를 해제할 것이다.

optimal의 낮은 설정이 성능에 영향을 미친다는 것이다.(정확히 이것은 항상 성능을 저하시킨다. 이 값을 작게 한다면 인지할 만큼 성능이 줄어든다.)

최고의 해결책은 모든 단일 트랜잭션에 알맞은 크기로 설정하는 것이다. 그러나

실제로 이것은 불가능 하다.즉 가장 큰 트랜잭션이 2GB이고 동시에 10개의 RBS를 사용하는 경우는 알 수 없다.

optimal 크기 이하로 shrink 될 경우 보장할 수 없는 두 가지가 있다.

- shrink는 오직 트랜잭션이 또 다른 extent로 이동하는 경우 발생하고 extent는 해제 요구를 받는다. RBS가 shrink의 후보가 된다면 그러나 어떠한 트랜잭션도

이것에 할당되지 않는다면 이것은 shrink 되지 않는다. 명백히 결과는 shrink을 위한 online 상태여야 한다.

5.13.6 Bringing Rollback Segments Online and Offline

RBS가 언제 생성되던지 offline 이 디폴트 이다. 그래서 online이 요구된다.

RBS를 offline 시킬 경우 active transaction 이 없다면 즉시 offline 이 된다.

하지만 active transaction 이 있다면 해당 active transaction 이 commit 또는

rollback 할 때까지 대기 한다. 그러나 새로운 트랜잭션은 해당 RBS를 사용할 수

없다. shutdown 없이 다시 이용하려고 한다면, 명시적으로 online 시켜야 한다.

비록 rollback_segments에 명시적으로 선언 하지 않았어도 startup시 online

될 수 있다. 이것은 다음 두 파라메터에 의해서 가능하다.

transactions 과 transactions_per_rollback_segment

startup시 오라클은 transactions_per_rollback_segemnt를 가지고 transactions를 나누서 반올림한다.

결과가 rollback_segments에 의해서 online 시킬 RBS 개수보다 적다면 아무것도 일어나지 않는다. 그러나 결과가 많고 더 많은 RBS가 존재 하지만 online 이 아니라면 해당 그 결과에 도달할 때 까지 online 되거나 전부 online을 시킨다. 두 파라메터가 설정되어 있지 않는 경우 디폴트 값이 사용된다.

즉 rollback_segment에 상관없이

transactions/(transactions_per_rollback_segment) 만큼은 무조건 띄운다.

5.13.7 public vs private rollback segment

ops가 아니라면 둘의 구분은 없다. 즉 ops에서 public는 모든 instance에서 사용

가능한 것이고, private는 특정 instance에서 사용 가능한 것이다.

(rollback_segment에서 지정한 RBS만 사용)

5.13.8 config rollback segments

1. 평균 트랜잭션 비율

- 트랜잭션의 비율이 일정하다면 TBS 구성은 간단하다. 결정된 최소 유효범위

크기(minimum coverage size)로 RBS의 개수를 계산하여 RBS TBS를 생성한다.

- 모든 extent를 같은 크기.

- 안전하게 하기 위해서 추가 공간을 추가한다.

- optimal은 최소 유효범위 크기 보다 작아선 안된다.

2. 큰 트랜잭션들이 자주 수행

데이타베이스에서 큰 트랜잭션이 자주 수행되는 경우 다루기가 까다롭다.

자주라는 시간은 모든 세그먼트에서 optimal이 수행되는 기간보다 큰 트랜잭션이 수행되는 시간이 짧은 경우를 말할 수 있다.

큰 트랜잭션이란 RBS정보를 다루기 충분한 크기를 가지지 못한다는 의미이다.

우리가 반복적인 큰 트랜잭션을 감당하기 위해서 RBS shrinking에 의존할 수 없다.

optimal은 이런 환경에는 적합하지 않다.

이 경우 기본적으로 두 가지 옵션을 사용한다. 하나는 RBS를 줄인다. 그래서 가장

큰 트랜잭션을 처리하기에 충분하도록 한다. 이 옵션은 RBS에 대한 경합을 유발 하

고 성능을 저하한다. 성능 평가를 해서 적용한다. 두번째는 하나 또는 다수의 큰

RBS를 생성한다. 그러나 adhoc 질의라면 큰 RBS를 적용하는 것이 어렵다.

3. 큰 트랜잭션 가끔 수행

이 경우 optimal 을 적용한다. optimal은 최소 유효범위 크기

(minimum coverage size) 와 동일하게 한다.

5.14 MONITORING AND DETECTING LOCK CONTENTION

5.14.1 Lock Type

DML Lock	Row-level Lock (TX)
	Table-level Lock (TM)	Row Share (RS): select for update
		Row Exclusive (RX): insert, delete, update
		Share (S): no index foreign key(index만들면 없어짐) parent table에 DML중이면 chile에 lock
		Share Row Exclusive (SRX) no index foreign key on delete cascade
		Exclusive (X) 최고 level lock 모든 DML, 모든 manual lock 금지
DDL Lock	exclusive Lock	drop table, alter table,
	shared Lock	create procedure, audit(해당 table 변경사항을 trace하는)
	breakable parse locks	shared SQL area를 invalidate시킴 shared pool(library cache)의 SQL area에 문장정보 들어있으나 analyze 등에 의해 invalidation되면 정보를 더 상 못쓰게 되었다고 하는 (pointer 생각하면 된다.)

=> Manual Table-level lock 방법(수동으로 share lock거는 방법) :

LOCK TABLE <table_name> IN <table_level_lock> MODE;

◆ Lock Monitoring : v$lock, v$locked_object

SQL> select owner,object_id,object_name,object_type, v$lock.type

2 from dba_objects,v$lock

3 where object_id=v$lock.id1

OWNER OBJECT_ID OBJECT_NAM OBJECT_TYP TY

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

SYS 6 C_TS# CLUSTER MR

SYS 5 CLU$ TABLE MR

SYS 4 TAB$ TABLE MR

SYS 3 I_OBJ# INDEX MR

SYS 2 C_OBJ# CLUSTER MR

SYS 7 I_TS# INDEX MR

SCOTT 24651 EMP TABLE TM --> table locks

그럼 v$locked_object를 보자.(이건 분석이 어렵다.)

SQL> select xidusn,object_id,session_id,locked_mode

2 from v$locked_object;

XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE

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

6 24651 10 3

XIDUSN	rbs number
OBJECT_ID	수정된 object의 id (위 v$lock 과 같다.)
SESSION_ID	object를 lock 하고있는 session의 id
LOCKED_MODE	0 \| NONE 1 \| NULL 2 \| RS(ROW Sahre) 3 \| RX(ROW Exclusive) 4 \| S(Share) 5 \| SRX(Share Row Exclusive) 6 \| X(Exclusive)

5.14.2 Dead lock

ORA-00060: deadlock detected while waiting for resource 메세지...

SQL> update scott.emp set sal= sal*1.1 where empno=7902; ==> 1 session

SQL> update scott.emp set sal= sal*1.1 where empno=7934; ==> 2 session

SQL> update scott.emp set sal= sal*1.1 where empno=7934; ==> 1 session

SQL> update scott.emp set sal= sal*1.1 where empno=7902; ==> 2 session

이렇게 하면 1 session에서

update scott.emp set sal= sal*1.1 where empno=7934

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource 와 같은 error가 떨어진다.

5.15 SQL ISSUES 와 TUNING CONSIDERATIONS FOR DIFFERENT APPLICATIONS

5.15.1 분석 tool

a. EXPLAIN PLAN : 실제문장 수행 안함

b. SQL Trace and TKPROF : 실행계획 포함 안함,상세정보 알 수 있다. SQL Trace로 받은 정보를 TKPROF로 읽을 수 있도록 formatting

c. SQL*Plus AUTOTRACE : 실행계획 보겠다. resouce 사용량 보겠다.

d. Oracle SQL Analyze : Oracle Enterprise Manager Tuning Pack에 포함됨

b, c번은 실제 문장 수행하고 난 후 알려줌

◆ 분석 tool 사용하여 sql문 분석하기.....

a. explain plan 명령

a.1 plan_table($ORACLE_HOME/rdbms/admin/utlx.sql)이 수행된 후 사용 가능

a.2 explain plan [set statement_id='..']

into table명 for <SQL문>;

a.3 이후 plan_table을 조회해 보면 알 수 있다.

SQL> @utlxplan

SQL> explain plan set statement_id='test' into table plan_table for

2 select * from scott.emp;

SQL> select * from plan_table;

b. sql*plus Autotrace & tkprof

b.1 init<SID>.ora 편집

- max_dump_file_size=n : K,M로 기술되면 bytes로 인식, 아니면 os block수.

(default는 10000 blocks) tracefile size 초과되면

"*** Trace file full ***" message나온다.

- user_dump_dest = ..... 에 정의된 trace file 생성 destination directory가

이미 만들어져 있어야 함

- timed_statistics=true => session level로 alter session으로 가능

b.2 다음 작업방법에 의해 trace file 생성 (file명 : <SID>_ora_????.trc)

- 우선 다음 4가지 중 하나로 sql_trace=true로 전환

init<SID>.ora file의 SQL_TRACE=TRUE 로 setting => 전체에 대한 trace생성

alter session set SQL_TRACE=true;

execute DBMS_SESSION.SET_SQL_TRACE(true);

execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<session_id>,<serial_id>,true);

=>특정 user session

c. SQL*Plus AUTOTRACE

c.1 $ORACLE_HOME/rdbms/admin/plustrace.sql로 권한을 만들고

(실제 dba 권한에 포함되어있다.)

c.2 grant plustrace to user명; --권한 부여

c.3 plan_table 생성

($ORACLE_HOME/rdbms/admin/utlxplan.sql이 수행되어 plan_table이

생성된 이후에 가능) --> user session에서 돌려서 생성해야..

c.4 set auto trace on

참고 : set auto trace [on|off|traceonly] [explain|statistics]

traceonly : 수행결과는 보여주지 않고 trace만 보여줌

on explain : on일 때 사용되며 statistics는 보여주지 않음

(수행결과와 execution plan만 보여줌)

traceonly statistics : statistics만 보여줌

5.15.2 Optimizer mode setting

a. cost-based optimizer mode를 위한 statistics

a.1 analyze command를 써서 통계정보 수집,삭제

analyze {index|table|cluster} <object_name>

{compute|delete|estimate} statistics

[for ... [size n]] [sample n {rows|percent}

- compute는 전체분석, delete는 통계정보 삭제, estimate는 sampling하여 분석

- size절 : default 75, max 254

- for절에 쓸 수 있는 것들.

n for table,

n for columns

n for all columns

n for all indexed columns

n for all [local] indexes

● table statistics : row수, 사용 중 이거나 빈 blocks, 평균 사용 가능한 free space, chained&migrate row 수 ,평균 row 길이, 마지막 analyze날짜와 sample size, data dictionary view

● index statistics : index level(height:2~3정도가 적당),

leaf block & distinct keys 수, key 당 leaf block 평균수,

key당 data block 평균수, index entries수, clustering factor,

data dictionary view(dba_indexes)

여기서 clustering_factor는 군집정도, index를 통해서 해당 table 모두 select할 때 거쳐야 하는 block수, 이게 row의 수와 거의 비슷하면 table data가 여러 block에 산재 되어 있다는 의미이므로 rerog 필요

● column statistics : distinct values의 수, 마지막 analyze날짜와 sample size,

data dictionary view(user_tab_col_statistics)

ex)

SQL> select column_name,num_distinct,low_value,high_value,num_nulls,

2 num_buckets

3 from user_tab_col_statistics

4 where table_name='DEPT' and column_name='DEPT_NO';

5.15.3 Stored Outline

- 원하는 SQL access path를 사용하도록 지정가능(DB 변경등에도)

- hint로 구성된 stored outline을 사용하여 구현

- 이 경우 SQL text는 꼭 일치해야 한다.

- 다만 Oracle Version변경, object 상태정보 변경, init parameter변경, DB reorg, Schema 변경일 때는 stored outline도 변경된다.

◆ outline 생성방법(2가지)

a. 첫번째 방법

a.1 outline을 저장할 category생성

SQL> alter session

2 set create_stored_outlines = test; ==> category 명이 test 로 지정되었다.

# 이름이 아니고 true로 할 경우 oracle의 default category명으로 지정됨

a.2 문장 수행

SQL> select * from scott.emp;

SQL> select * from scott.dept;

a.3 outline 생성을 중지

SQL> alter session set create_stored_outlines=false;

a.4 작업(upgrade,RBO->CBO전환,....)

b. 또 한가지 방법(직접생성)

SQL> create or replace outline co_cl_join

2 for category test on

3 select * from scott.emp;

Outline created.

◆ outline 사용방법 : 생성방법에 상관없이 아래 방법으로 사용

outline의 category 사용해서 지정

SQL> alter session set use_stored_outlines=test;

5.16 INDEX

■ B* index - normal

- reverse

특징 : 사용 할수록 사용효율이 떨어지므로 주기적으로 rebuild 해 주는 것이 필요

<< 재생성 >>

SQL> alger index <index명> rebuild

2 [parallel n] online ==> online은 DML가능하면서 index rebuild

3 [compute statistics] ==> index rebuild하면서 통계정보 생성

4 [nologging];

■ Bitmap index

아래와 같이 사번 당 color data가 있다면 오른쪽과 같이 1,0 flag로 index생성하여 세로로 segment로 저장

사번 color B Y R G

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

1 B 1 0 0 0

2 Y 0 1 0 0

3 R 0 0 1 0

4 G 0 0 0 1

5 B 1 0 0 0

6 G 0 0 0 1

7 B 1 0 0 0

이렇게 segment로 저장 (세로로 저장하는 이유는 예를 들어 위에서 4개의 값이 존재하고 각각이 index에 들어 있다면 index에서 B 값을 찾을 경우 첫번째 인덱스를 세로로 읽으면서 1인 값을 가지고 로우를 찾아 가면 된다. 즉 Y인 값을 찾는 다면 두번째 인덱스를 찾으면 된다.

ü cardinality가 낮은 column에 사용( 성별처럼... 구분이 거의 없는 column에 적합)

ü 조건절이 많을 때 적합

ü ( and, or등을 사용한 조건절에서 bit 연산을 하므로 performance good)

ü read only에 최적(update시에는 bitmap segment전체에 lock이 걸리므로 그다지 좋지 않다) => OLTP성에 좋지않다. DSS system에 좋다.

ü 매우 큰 table에 좋다

ü storage space 적게 쓴다.

● B-Tree Index와 Bitmap index의 비교

B-Tree Index	Bitmap index
high-cardinality column에 적합	low-cardinality column에 적합
updates on keys relatively inexpensive	updates to keys very expensive
or조건절에 적합치 않음	or조건절에 적합
row-level locking	Bitmap segment-level locking
많은 공간 필요	적은 공간 필요
OLTP성 업무에 유용	DSS성 업무에 적합(DW) =>update별로 없고 Query위주 작업

■ Reverse Key indexes --> 값을 뒤집어서 저장

예를 들어 다음과 같이 reverse로 index만들어 저장하면

100 --> 001

101 --> 101

102 --> 201

105 --> 501

104 --> 401

leaf의 각각 다른 위치에 분산되므로 OPS 같은 환경에서의 I/O load를 분산시킬 수 있다.

<<< 재생성 >>>

SQL> alter index i1_t1 rebuild reverse;

■ Index-Organized Tables

▶ 특징: table과 B-tree index를 위한 segment를 따로 구성 하는게 아니라 하나의

B-tree구조가 PK base로 구성하는 것.반드시 PK가 있어야 함.

▶ 장점 : storage가 덜 필요. exact matches and range search하는 query에 유리.

■ Clusters (Object 개념의 cluster)

▶ 특징: 자주 공유되고 함께 쓰이는 것들을 같은 data block을 공유하여 하나이상

의 table의 group. index cluster, hash cluster가 있다.

▶장점 : 해당 clustered table join Query시 Disk I/O가 줄어든다.

각 cluster key는 한번만 저장되므로 storage절약

▶단점 : full table scan시 일반적으로 nonclustered tables보다 느리다.

■ Materialized Views

▶ 특징: view 정의와 view 실행결과 row를 저장

(view생성시 query실행되고 결과는 table에 저장됨)

▶ 장점: index나 partition을 Materialized view에 사용가능하고 query시

Materialized view를 사용하여 performance향상

▶ Query Rewrites

base table대신 Materialized View를 사용하기 위해, query는 반드시 rewritten 되어야 함. user는 Query rewirtes 에 대해 모르고 특별한 권한이 필요 없다.

MV가 query rewrite를 위해 enable/disable 될 수 있다.

● Query Rewirte enabling

-- initialization parameters : optimizer_mode - cost-based로 되어있어야

query_rewrite_enabled --- false로 되어있어도 나중에 alter 가능

query_rewrite_integrity --- enforced : 똑같아야 Q.R

--- trusted : rely flag 있는 MV만 Q.R

--- stale_tolerated : MV의 syntax와 약간의 차이

인정하고 Q.R

-- Dynamic and session-level parameters : query_rewrite_enabled

query_rewrite_integrity

-- New hints : rewrite, norewrite

ex) SQL> select /*+ norewrite */ s.zip,p.product_type,sum(s.amount)

2 from sales s, product p

3 where s.product_id = p.product_id

4 group by s.zip,p.product_type;

5.17 OLTP VS DSS(DW)

a. OLTP : insert, updatae가 많고 큰 size,지속적을 data volumn이 증가. 동시에

여러 user access

- tuning goal : availability, speed, concurrency, recoverability

- index : 너무 많지 않게(bitmap보다는 B-tree index선호)

sequence columns에 reverse key

주기적 rebuild

- clusters : index clusters for growing tables

hash clusters for stable tables

- short transactions, rbs는 경합방지하기 위해 작은 size 여러개로..

- large minextents 필요

- database constraint는 application code level -> trigger -> constraint로

갈수록 성능 좋다.

- SQL sharing을 위해 bind 변수를 사용

b. DSS(DW) : 대량 data, 잦은 full table scan

- tuning goal : fast response time, accuracy, availability

- parallel Query중요

- storage allocation : db_block_size, db_file_multiblock_read_count 크게

extent size는 이런 parameter 값들의 배수로.

analyze를 주기적으로 실행

- index : 가능하면 bitmap index사용

pk로 하는 range 검색시 indez-organized table 사용

균일하게 분산되지 않은 indexed column에 histogram 생성

- clustering : hash cluster 고려

- parse time은 그다지 중요하지 않다.

- execution plan이 optimize 중요 :parallel query 사용

적절한 hints 사용

logic을 query문장 안으로 넣도록 노력

c. hybrid system : OLTP 와 DSS를 병행하는 system

- shared_pool_size, large_pool_size, db_block_buffers, sort_area_size 고려

- online rbs : 낮 시간에는 작은 size로 수 많이 밤시간에는 큰size로 수 적게

5.18 MANAGING A MIXED WORKLOAD (resource manager)

◆ Resource Manager : 8i new feature로 system의 load에 관계없이 특정 user나 group 에 processing resource를 부여 가능 CPU time을 각 다른 user에 분배하여 resource분산 가능

(낮 시간에는 DSS보다 OLTP에 higher priority부여)

parallel query server의 수를 제한할 수 있다.

dynamic하게 변경 가능

a. Resource manager 개념 : resource manager를 사용하기 위해 다음 세가지는 DBA가

정의해 준다.

- Resource consumer groups : 비슷한 resource usage가 필요한 user끼리 grouping

- Resource plan directives : resource plan에 consumer group이나 subplan을

assign consumer group이나 subplan에 resource를 할당

- Resource plans : resource plan directives를 포함.

resource plan directives는 resource consumer group에 할당되는 resource들을 명시 , 여러 개의 resource plan을 가질 수 있지만 한 instance에서는 하나만 active 가능 ,resource plan에 subplan들을 정의할 수 있고 plan을 activate하면 sub도 activated 됨

◆ 다음 세부적인 내용에 대해서 알아보자.

b. Resource Consumer Group : - user는 여러 resource group의 member가 될 수 있다.

- default group은 user login할 때 assign됨

- 한 session에 대해 한번에 하나의 group만 activate됨

- session내에서 consumer group이 될 수 있다.

- group은 database생성될 때 함께 생성됨(다음 group들)

-- sys_group : high priority in the plan system_plan

sys,system user 에게 할당

-- low_group : low priority in the plan system_plan

-- default_group : 다른 group에 속하지 않은 session에 대한default group

-- other_group : current resource plan에 해당 안되는 consumer group에

속한 모든 session

c. Resource Plan Directives :

- Manage parallelism: Absolute method

Allocate Parallel Query servers for an operation limit degree of

parallelism

- Managed CPU usage: Emphasis method Allocate based on percentages at

different levels delay work that exceeds CPU limits

EX) Database Resource Management

plan level consumer group cpu parallelism degree

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

1 sys_group 100% 20

day 2 OLTP 100% 0

3 DSS 100% 20

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

1 sys_gropu 100% 20

night 2 OLTP 25% 0

2 DSS 75% 20

3 OLTP 100% 0

- level은 1~8까지 둘 수 있다. 같은 level에서의 cpu의 합은 100%를 초과할 수 없다.

- 상위 level에서 사용하고 남은 양 중에서 다음 level에서 cpu %만큼 쓸 수 있다.

위에서 낮에 sys_group 이 100%쓰고 남은 CPU의 100%를 OLTP에서 사용 가능하다는

의미 상대적으로 낮에 sys_group이 사용하는 cpu가 적으면 OLTP에서 그만큼 많이

사용가능

- parllelism degree는 가능한 parallel process의 max수

◆ Database Resource Management 단계

a. resource manager system 권한을 admin에게 assign

SQL> execute dbms_resource_manager_privs.grant_system_privilege

(grantee_name => 'SCOTT', privilege_name => 'ADMINISTER_RESOURCE_MANAGER', admin_option => false);

여기서 admin_option => true로 주면 권한 받은 scott user가 다른 user에게 권한을 줄 수 있음

b. DBMS_RESOURCE_MANAGER package로 resource object들 생성

b.1 pending area 생성 : new Database Resource Manager plans, consumer groups,

resource plan directives commit전에 변경 사항들을 저장한 공간으로 사용. 나중에 이 area를 가지고 handling

SQL> execute dbms_resource_manager.create_pending_area();

b.2 resource consumer group 생성

SQL> execute dbms_resource_manager.create_consumer_group

(consumer_group => 'OLTP', comment => 'Online users');

b.3 resource plan 생성

SQL> execute dbms_resource_manager.create_plan

(plan => 'NIGHT', comment => 'DSS/Batch priority,...');

b.4 resource plan directives 생성

SQL> execute dbms_resource_manager.create_plan_directive

(plan => 'NIGHT', group_or_subplan => 'SYS_GROUP',

comment => '.....', cpu_p1 => 100, parallel_degree_limit_p1 => 20);

b.5 validate the pending area

SQL> execute dbms_resource_manager.validate_pending_area();

==> 지금까지 setting이 맞는지 check

b.6 commit the pending area

SQL> execute dbms_resource_manager.submit_pending_area();

c. user를 group에 DBMS_RESOURCE_MANAGER_PRIVS package로 assign

assgin user to groups

SQL> execute dbms_resource_manager_privs.grant_switch_consumer_group

(grantee_name => 'MORIA', consumer_group => 'OLTP', grant_option => FALSE);

set the initial consumer group for users

SQL> execute dbms_resource_manager.set_initial_consumer_group

(user => 'MORIA', consumer_group => 'OLTP');

d. instance에 의해 사용가능 하도록 plan 명시

d.1 init<SID>.ora에 setting

resource_manager_plan=day

d.2 dynamic 하게 shutdown/restart 없이 resource plan 변경

SQL> alter system set resource_manager_plan=night;

◆ session내에서 consumer group 변경

- user 나 application이 current consumer group을 변경

SQL> execute dbms_session.switch_current_consumer_group

(new_consumer_group=>'DSS',old_consumer_group=>v_old_group, initial_group_on_error=>false);

- dba에 의해 한 session 변경

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

SID Serial#

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

7 13 --> 이 값을 가지고..

SQL> execute dbms_session.switch_consumer_group_for_sess

(session_id=>7,session_serial=>13,comsumer_group='OLTP');

- DBA에 의해 한 user의 모든 session이 변경

SQL> dbms_resource_manager.switch_consumer_group_for_user

(user=>'MORIA',consumer_group=>'OLTP');

◆ Database Resource Manager정보

a. resource plan

SQL> select plan,num_plan_directives,status,mandatory

2 from dba_rsrc_plans;

PLAN NUM_PLAN_DIRECTIVES STATUS MAN

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

SYSTEM_PLAN 3 ACTIVE NO

NIGHT 1 PENDING NO --> pending은 '작업 중'

b. resource plan directives

SQL> select plan,group_or_subplan,cpu_p1,cpu_p2,cpu_p3

parallel_degree_limit_p1,status

2 from dba_rsrc_plan_directives;

c. resource consumer groups and privileges

SQL> select * from dba_rsrc_consumer_group_privs;

GRANTEE GRANTED_GROUP GRA INI

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

PUBLIC DEFAULT_CONSUMER_GROUP YES YES

PUBLIC LOW_GROUP NO NO

SYSTEM SYS_GROUP NO YES

SQL> select consumer_group,status,mandatory

2 from dba_rsrc_consumer_groups;

CONSUMER_GROUP STATUS MAN

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

OTHER_GROUPS ACTIVE YES

DEFAULT_CONSUMER_GROUP ACTIVE YES

SYS_GROUP ACTIVE NO

LOW_GROUP ACTIVE NO

OTHER_GROUPS PENDING YES

DEFAULT_CONSUMER_GROUP PENDING YES

SYS_GROUP PENDING NO

LOW_GROUP PENDING NO

OLTP PENDING NO

5.19 MULTITHREADED SERVER TUNING ISSUES

◆ configuring the MTS : MTS를 사용하기 위해서는 반드시 Net8i가 설치되어 있어야 함.

◆ MTS(MultiThreaded Server) 구성 **

1. Server쪽 tnsname.ora 에 리스너에 대한 tns_service name이 등록되어 있어야 한다.

<LISTENER명> 은 listener.ora file에서 setting 된 대로 사용할 listener를 등록

해주면 된다.

tnsnames.ora

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

<LISTENER명>.<domain명>= #=> domain명은 sqlnet.ora file의 NAMES.DEFAULT_DOMAIN = .에

(DESCRIPTION = 설정된대로

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = xxxx))

)

(CONNECT_DATA = (SID=xxxx)

)

sqlnet.ora

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

NAMES.DEFAULT_DOMAIN = <domain명>

NAMES.DIRECTORY_PATH = (TNSNAMES) #===> default 가 TNSNAMES 이므로 사실 잡지 않아도 된다.

2. 리스너가 기동되어 있는지를 확인

$ lsnrctl state <listner명>

기동 되어 있지 않으면 다음과 같이 기동 시키면 되지요.....

$ lsnrctl start <LISTENER명>

3. init<sid>.ora file 편집

MTS구성을 위해 다음과 같이 추가 편집

#####################################

# Multithreaded Server Environment

local_listener = <listener명>.<domain명>

mts_service=<SID>

mts_dispatchers='(PRO=TCP)(DIS=5)' ==> protoco, dispatcher 수

mts_servers=3 ==> 초기 server수

mts_max_dispatchers=10 ==> 최대 dispatche process수

mts_max_servers=6 ==> 최대 server process수

4.설정이 다 된 것이므로 지금부터는 제대로 설정이 되었는지 확인을 해보도록 하자.

$ svrmgrl

$ sqlplus internal

SVRMGR> connect internal

SVRMGR> shutdown immediate

SVRMGR> startup

SVRMGR> exit

v$circuit : MTS를 이용한 접속여부를 알려준다.

v$shared_server : 공유된 서버가 시작된 것을 확인할 수 있다.

v$dispatcher : 얼마나 많은 dispatcher가 시작되었는지 알 수 있다.

$ sqlplus system/manager@<LISTENER명>

SQL> select * from v$shared_server;

NAME PADDR STATUS MESSAGES BYTES BREAKS CIRCUIT IDLE BUSY REQUESTS

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

S000 02A47BD4 WAIT(COMMON) 0 0 0 00 56453 0 0

S001 02A47EF4 WAIT(COMMON) 0 0 0 00 56424 0 0

S002 02A48214 WAIT(COMMON) 0 0 0 00 56394 0 0

위와 같이 shared_server process가 3개 떠있는 것을 볼 수 있죠??

다음과 같이 하나의 session을 더 열자.

$ sqlplus scott/tiger@<LISTENER명>

SQL> select * from emp;

이전 system/manager 에서 다음을 확인해보자.

SQL> select * from v$circuit;

CIRCUIT DISPATCH SERVER WAITER SADDR STATUS QUEUE

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

MESSAGE0 MESSAGE1 MESSAGE2 MESSAGE3 MESSAGES BYTES BREAKS

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

PRESENTATION

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

02DD1830 02A491B4 00 00 02A5A2D8 NORMAL NONE

0 0 0 0 80 8519 0

TTC

위와 같이 하나 더 session을 열어서 v$circuit을 확인해보니 MTS를 이용해서 접속한 것이 보이네요..

◆ MTS 환경에서 DEDICATE 로 접속 하기

tnsnames.ora

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

<LISTENER명>.<domain명>=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = xxxx))

)

(CONNECT_DATA = (SID=xxxx)

(Server=Dedicated) #==============> 중요한 부분은 바로 여기입니다.!!!!

)

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

그 다음에

$ ps -ef|grep <sid> 로 확인해보면

............................oracle<SID> (LOCAL=NO) ====> 이런 process

◆ Monitoring Dispatchers

SQL> select network "Protocol", sum(busy)/(sum(busy)+sum(idle))

2 "total busy rate"

3 from v$dispatcher

4 group by network;

Protocol total busy rate

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

(ADDRESS=(PROTOCOL=tcp)(HOST=swsvrctr)(PORT=55157))

...

(ADDRESS=(PROTOCOL=tcp)(HOST=swsvrctr)(PORT=55161)) .00002682

(ADDRESS=(PROTOCOL=tcp)(HOST=swsvrctr)(PORT=55162)) .000026826

==> total busy rate >0.5 이면 busy하다 dispatcher의 수를 더 늘린다.

b. wait time을 비교해보면

SQL> select decode(sum(totalq),0,'No Responses',sum(wait)/sum(totalq))

"Average wait time"

2 from v$queue q, v$dispatcher d

3 where q.type = 'DISPATCHER'

4 and q.paddr = d.paddr;

를 돌려보면서 꾸준히 wait time이 증가하면 문제 있는 것==>dispatcher의 수를 더

늘린다.

SQL> alter system set mts_dispatchers='<protocol>,<number>';

◆ Monitoring Shared Servers

- dynamic 하게 mts_max_servers 수를 변경할 수 있다.

SQL> alter system set mts_servers=<number>;

- shared server의 수

SQL> select count(*) from v$shared_server where status !='QUIT';

COUNT(*)

--------

- Average Wait Time Per Requests

SQL> select decode(totalq,0,'No Requests',wait/totalq||' hundredths of secons')

2 "Average Wait Time Per Requests"

3 from v$queue

4 where type='COMMON';

◆ Monitoring Process Usage :

v$circuit --> server address,dispatcher address,user session address

6. IO slave

Oracle7까지는 DB_WRITERS 패러미터를 이용하여 다중 DB Writer로 운영하도록 하였고

Oracle8 이상에서는 DB_WRITER_PROCESSES로 설정하며 최대 10개(DBW0 ~ DBW9)까지

지정할 수 있으나 불필요한 DB Writer의 다중 설정은 그 관리부담의 증가가 수반되므로

신중하게 고려해야 한다.

이러한 다중 DB Writer의 설정은 SMP(Multi-CPU) 시스템에서 매우 유용하지만,

하드웨어에서 제공하는 Asynchronous I/O 기능과 동시에 사용하지 못하며 Oracle8 이상부터 지원되는 Multiple I/O Slaves와의 동시 사용도 할 수 없다.

그러나 Nonblocking Asynchronous I/O 처리를 가능하게 하는 Multiple I/O Slaves는 I/O

성능 개선에 효과적인 기능을 제공함은 물론 하드웨어 플랫폼에서 제공하는

Async. I/O와의 병용이 가능하다.

■ 적용 대상

Multiple I/O Slaves의 적용은 DBWR, LGWR, ARCH, BACKUP 프로세스들에 가능하며,

이 중에서 DBWR은 인스턴스가 시작할 때 지정된 수만큼 기동되고 LGWR, ARCH, BACKUP 등의 프로세스는 기본적으로 하나로 운영되다가 부하의 정도에 따라 필요한 시점에 자동적으로 추가되는 방식으로 관리된다.

Naming Rule은 ora_iNnn_<SID> 형식이다. 예를 들어 dbwr_io_slaves = 3으로 설정한다면

기동되는 프로세스는 ora_i101_SID, ora_i102_SID, ora_i103_SID으로 확인이 가능하다.

■ Initialization Parameters

DBWR, LGWR, ARCH, BACKUP 프로세스들의 I/O Slaves에 대한 전개 여부는 다음의

패러미터에 의해 필요시 다중으로 정의할 수 있다.

DBWR_IO_SLAVES

LGWR_IO_SLAVES

ARCH_IO_SLAVES

BACKUP_DISK_IO_SLAVES

BACKUP_TAPE_IO_SLAVES

특히 BACKUP_DISK_IO_SLAVES는 RMAN(Recovery Manager)에 의해 백업, 복구시 사용되는

I/O Slave의 수를 지정하는 것으로, RMAN 채널별로 개별적인 지정이 가능하다.

■ Asynchronous I/O

표준 Unix 특성으로 일반화되어 있는 AIO 기능은 하나의 프로세스가 동시에 다수의

디스크를 액세스하게 함으로써 디스크 I/O의 전체적인 생산성을 높이는 것이다.

하드웨어 플랫폼에서 제공하는 AIO의 채택 여부는 다음의 패러미터에 의해 정의된다.

DISK_ASYNCH_IO = TRUE or FALSE

TAPE_ASYNCH_IO = TRUE or FALSE

이러한 AIO 기능은 하드웨어 플랫폼에서 기능을 제공하지 못하거나 기본적으로 기능은

제공하지만, 버그 또는 이용시 효율적이지 못한 상황에서는 AIO 기능의 채택은 신중할

필요가 있다.

■ LGWR & ARCH I/O Slaves

LGWR_IO_SLAVES는 LGWR에 의해 사용되는 I/O Slave의 수이며 ARCH_IO_SLAVES는 ARCH에

의해 사용되는 I/O Slave의 수이다.

기본값은 0이고 I/O Slave는 사용하지 않는다. 다중으로 사용하고자 할 때에는 I/O시의

병목 현상 발생 가능성을 면밀하게 분석한 후 필요한 수를 설정한다.

특히 AIO를 지원하지 않거나 사용하기 어려운 상황에서의 채택시 유의해야 한다.

7. 자원 경합을 해결하는 기능들

한정된 자원을 놓고 벌어지는 치열한 경쟁을 해결하기 위해 Oracle8/8i는 다양한 기능들을 제공하는데, 이를 Redo Log와 LRU Latch, Freelist 측면에서 살펴보자.

7.1 Redo Log 경합

LGWR(Log Writer)가 리두 로그 버퍼에서 디스크상의 리두 로그 파일로 리두 엔트리를

저장할 때 또 다른 사용자 프로세스들은 메모리상에 새로운 다수의 엔트리들을 생성하는데, 일반적으로 LGWR는 새로운 엔트리를 위한 버퍼상의 가용한 영역을 확보시키기에 충분한 속도로 엔트리들을 기록한다.

그러나 리두 로그 버퍼의 가용한 버퍼 영역이 부족하면 사용자 프로세스들은 대기 상태로 빠지며 이는 성능에 악영향을 끼칠 수 있다.

이 때 Performance View V$SYSSTAT를 통해 사용자 프로세스의 대기여부를 확인하고

대응하는 조치를 취할 수 있다.

SQL> SELECT name, value

2 FROM v$sysstat

3 WHERE NAME = 'redo buffer allocation retries' ;

Redo buffer allocation retries는 사용자 프로세스가 가용한 버퍼 영역을 확보하기

위해 대기한 수를 나타내는데, 0에 가까운 수치를 반드시 유지하도록 해야 하며

이러한 대기 수치가 클 경우 LOG_BUFFER 패러미터의 값을 증가시킨다.

■ Note

Multiple Archive Processes는 권장하지 않는다 하나의 독립적인 ARCH 프로세스가

LGWR 프로세스와의 연계 기능을 충분히 수행할 수 있으므로 다중 프로세스를 기동 시킬

경우에는 매우 신중히 고려해야 한다.

■ Redo Log Latch

사용자 프로세스가 변경된 정보를 저장하기 위해 리두 로그 버퍼를 액세스할 때에는

다음 두 가지 형태의 래치 획득이 필수이다.

▶ Redo Allocation Latch

Redo Allocation Latch는 리두 로그 버퍼 안에 리두 엔트리를 기록하기 위한 영역의 할당을 관리하는 래치로서 Oracle 사용자 프로세스가 버퍼내에 가용 영역을 할당 받기 위해 반드시 획득해야 할 리두 로그 래치이다.

이 Redo Allocation Latch는 데이타베이스에 단 하나만이 존재하므로 특정 시점에 한 명의 사용자 프로세스만이 버퍼내에 가용 영역을 할당 받을 수 있는 순차적인 방식의 관리가 불가피하다.

리두 엔트리를 위한 버퍼 영역을 할당 받은 후 정해진 임계치 보다 작은 리두 엔트리일

경우에 Redo Allocation Latch를 이용해 복사한다. 즉 redo copy latch를 필요로 하지 않는다.

▶ Redo Copy Latches

사실 사용자 프로세스가 생성한 로그 엔트리를 로그 버퍼에 복사하기 위해서는 먼저 Redo Copy Latch부터 획득해야 한다. 그런 후에 Redo Allocation Latch를 획득해 복사할 가용 영역을 확보하고 Redo Allocation Latch는 즉시 해제 시킨다.

다음에 Copy Latches를 이용해 로그 엔트리를 로그 버퍼에 복사한 뒤 Copy Latches를

해제 시키는 순서를 거친다.

이러한 Redo Allocation Latch와 Redo Copy Latches의 사용 여부는 패러미터

LOG_SIMULTANEOUS_COPIES의 설정 값에 따라 달라진다.

LOG_SIMULTANEOUS_COPIES = 0 인 경우

사용자 프로세스는 Redo Allocation Latch를 이용하여 로그 엔트리를 위한 버퍼 영역을

할당하고 복사 기능도 수행한다.

LOG_SIMULTANEOUS_COPIES > 0 인 경우

사용자 프로세스는 Redo Allocation Latch를 이용하여 로그 엔트리를 위한 버퍼 영역만을 할당하고 지정된 수만큼의 Redo Copy Latches가 복사 기능을 수행한다. 이 경우에도 로그 엔트리가 임계치 보다 작은 리두 엔트리일 경우에 Redo Allocation Latch를 이용해 복사한다.

- 리두 엔트리 임계치 = log_small_entry_max_size (Oracle8i까지)

= _log_small_entry_max_size (Oracle8i)

결론적으로 SMP 시스템에서는 리두 로그 엔트리 처리시 Redo Allocation Latch를 이용하여 로그 엔트리를 위한 버퍼 영역을 할당하는 역할만 수행하게 하고 복사 기능은 Redo Copy Latches에 분담시켜 자원 경합의 가능을 줄여야 성능 개선을 기대할 수 있다.

■ Redo Log Latch 가이드라인

SQL> select ln.name, misses, gets,

2 immediate_misses imdt_misses, immediate_gets imdt_gets, sleeps

3 form v$latch l, v$latchname ln

4 where ln.name in ('redo allocation', 'redo copy')

5 and ln.latch# = l.latch#

6 order by ln.name desc ;

NAME GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES

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

redo allocation 252867 83 0 0

redo copy 0 0 22830 0

다음 문장의 결과 Misses나 Immediate_Misses의 비율이 항상 1%보다 작도록 유지한다.

그 비율이 1% 보다 클 경우 즉, Redo Log Latch에 경합이 발생하면 LOG_SMALL_ENTRY_MAX_SIZE 의 크기를 줄여서 Copy Latch의 활용성을 높임(즉 copy latch를 더 많이 쓰도록)과 동시에 LOG_SIMULTANEOUS_COPIES의 수를 증가 (일반적으로 CPU*2)시켜 다중화함으로써 성능을 개선시킨다.

7.2 LRU Latch 경합

데이타베이스 블록 버퍼는 'System Sets'으로 불리는 LRU Pair(LRU/LRUW)에 의해 관리된다. 다시 말해 모든 버퍼는 System Sets에 할당되어 있고 'round-robin' 방식으로 관리하며 LRU 래치를 획득해야 액세스가 가능하다.

LRU : Least Recently Used List

LRUW : LRU Write List = Dirty List

즉, LRU(Least Recently Used) 래치는 버퍼 캐시를 구성하는 모든 버퍼들의 운용을 관리하는 것으로, SMP에서 유용하며 일반적으로 CPU 수만큼의 값으로 설정한다.

LRU 래치의 수는 DB_BLOCK_LRU_LATCHES 패러미터로 설정이 가능하며 다음의 내용을 고려한다.

- 설정할 수 있는 범위 : 1~2*CPU

- 래치 하나당 최소 50버퍼 이상 할당

- 1CPU 시스템일 경우 다수의 래치 불필요

- 업무 부하가 큰 인스턴스의 운영시에 반드시 다수의 래치로 운영

실제로 SMP상에서 LRU 래치의 경합은 성능에 지대한 영향을 미치는 요소가 될 수 있으므로 V$LATCH, V$SYSTEM_EVENT, v$SESSION_EVENT 등의 뷰를 통해 경합의 여부와 정도를 분석한다.

7.3 Freelists & Freelist Groups 경합

테이블의 헤더에는 그 테이블에 확보된 Extent 영역 중 사용자 요구시 할당이 가능한 Free Block에 대한 리스트가 기록 및 관리되는데, 그 리스트가 바로 'Freelist'이며 기본적으로 테이블에는 하나가 설정된다. 또한 그러한 Freelist는 다수로 설정할 수 있고그것을 'Freelist Group'이라 부르며 이러한 Freelist Group도 관리자의 정의에 의해 다수로 설정할 수 있다.

Free Block을 필요로 하는 사용자의 작업요구(Insert 또는 길이가 늘어나는 Update 요구)가 동시에 발생되면 Oracle은 순차적으로 Free Block을 할당해줄 수밖에 없으므로 Freelist에 대한 경합이 유발되어 처리 성능에 영향을 미치는 것이 사실이다. 더구나 여러 인스턴스를 통해 다수의 사용자가 동시에 대상 테이블에 대해 위의 작업을 요구하면 상당한 성능 저하의 요인이 되므로 동시 사용자 수만큼의 Freelist와 Instance 수만큼의 Freelist Group은 반드시 정의해야 한다.

이와는 달리 Extent 2와 5는 인스턴스 X에 Extent 3과 4는 인스턴스 Y로 매핑되도록 정의되어 있고 Freelist Group이 다수로 정의되어 있으므로 각각의 그룹별로 서로 다른 Extent에 대한 Free Block List를 가지게 되어 인스턴스별로 물리적, 논리적인 분리가 보장될 수 있다.

결론적으로 보다 효과적인 운영을 위해 Extent를 특정 인스턴스에 귀속시킬 수 있으며 이러한 경우 하나의 테이블 데이타라도 인스턴스별 처리 대상 데이타는 물리적으로 완전히 독립적인 운영이 가능하므로 입/출력의 분산 및 인스턴스간의 경합이 최소화되어 성능 향상을 기대할 수 있다.

8. Close internal block

8.1 개요

하나의 테이블 세그먼트는 하나의 블록 헤더와 하나 이상의 extents로 구성되어 있으며 extent는 연속된 블럭의 집합이다.

각 세그먼트의 첫번째 블럭은 세그먼트 헤더라고 한다. 따라서 하나의 세그먼트는 최소한 2개의 블럭으로 구성될 수 있다.

즉 세그먼트 헤더와 initial extent : create table x(c number) storage(initial 1)

db_block_size의 정확한 값을 알려면 x$kvis를 질의 한다.

8.2 block layout

● 모든 블럭은 header area 구성 요소

- block type

- block format

- relative database address(DBA)

- SCN

- SCN seq number

- checksum (check value)

- tail

- block format 은 oracle 7 인지 oracle 8 형식인지를 나타낸다.

- SEQ number 는 동일한 SCN 내에서 블럭의 순서를 나타낸다.

- check value는 DB_CHECK_SUM으로 조절하며 block level에서 무결성을 체크(init.ora)

- tail은 블럭의 시작과 끝이 일관성이 있다는 체크하기 위해서 사용된다.

이것은 (SCN + block type, SCN SEQ number)로 구성된다.

● The following list shows some important block types:

● 데이터 블럭 dump와 visualize

in unix : dd bs=2k if=/ora8/data/user01.dbf skip=200 count=5 | od -x > pg

in vms : dump /BLOCKS = (START:<os block #> , END: <os block #>) /OUT = blockdump.out users01.dbf

in NT : HEDIT를 사용한다. format된 dump는 svrmgrl 을 통해서 얻을 수 있다.

alter system temp datafiel 7 block 201;

● 세그먼트 헤더는 항상 모든 오브젝트의 첫번째 블럭에 위치한다.

● 세그먼트 헤더는 6개의 중요 영역을 가지고 있다.

- extent control header

- map header

- extent map

- free lists의 3가지 type

● extent control header

- 해당 세그먼트에 할당된 extent수와 블럭수,

- extent map에 마지막 블럭의 DBA(만약 unlimited extents 사용시:0 )

- extent map 블럭수

- HWM extent size

- HWM extent number

- HWM block number를 포함

HWM 이하 블럭은 freelists에서 관리된다. HWM 이상은 할당은 되었지만 아직 사용되지 않은 공간이다. HWM 이상은 sql*loader/direct load/parallel dml/freelist에서 HWM이하를 다 사용한 경우 사용된다.

- map header 은 단순히 extent map 정보를 포함하는 next block의 DBA의 정보를

포함한다. 즉 extent map이 여러 개 경우

- extent map은 각 extent의 시작 데이타 블럭의 DBA와 블럭수를 포함한다.

extent map 크기는 사용되는 블럭과 직접적으로 관련한다.

oracle7.3 이후에 소개된 unlimited extents는 extent map의 확장을 야기하여 추가적인 블럭이 필요하게 되었다.

8.3 Finding free space

● free lists에는 3가지 타입이 존재한다.

- master free lists(MFL) : HWM를 끌어 올리는 새롭게 할당된 블럭과 commit에 의해서

free 된 블럭을 포함한다.

- Process free lists(PFL) : 사용자가 정의한다 (storage parameter freelists N)

그리고 MFL에 대한 경합을 줄이기 위해서 사용된다.

이 LIST는 여러 블럭으로 요구를 분산한다.

- transaction free lists(TFL): 후에 동일한 트랜잭션에서 사용 할 수 있는지 없는지

보기 위한 개별적인 트랜잭션에 의해 free된 블럭을

가진 list 즉 commit 후 이 공간은 MFL에 등록된다.

이 MFL은 flag(freelist 가 사용되고 있는지 아닌지

(1 or 0) 를 포함한다. 세그먼트 헤더는 또한 free list

chain의 첫번째 블럭의 DBA와 마지막 블럭의 DBA를 포함

한다. free list chain(FLC)에 블럭은 해당 블럭이 FLC

에 링크 되어 있는지를 나타내는 flag를 포함하며 chain

에서 next block의 DBA를 포함한다. 이 정보는 cache

layer와 data block header에 저장된다.

HWM 이하 block이 PCTFREE를 넘어선 블럭은 MFL에서 관리 된다.

(그림 3) 은 오라클이 새로운 로우를 입력하기 위한 어떻게 free list를 찾는지

보여준다.

첫번째 uncommit 블럭을 TFL에서 찾는다. 찾으면 이것을 사용하고 못찾으면 PFL을 찾는다. 마찬가지로 찾으면 사용하고 못찾으면 MFL을 찾는다.

여기서 free 공간을 찾으면 해당 블럭을 PFL 로 이동시킨 후 사용한다.

commit된 TFL을 찾는 오라클의 경우 찾으면 이 공간을 사용하면 이것을 MFL로 이동 시키고 전체 찾기 프로세스를 다시 시작한다. 여기서 천체 찾기가 실패할 경우 오라클은 HWM를 이동 시킬 수 있는지 체크하여 가능하다면 새로운 블럭을 MFL에 할당한 후 다시 새로운 찾기를 수행한다. 그러나 HWM을 이동 시킬 수 없다면 새로운 extent를 할당한다.

8.4 dynamic extent allocation

하나의 extent는 연속된 블럭의 집합으로 extent size는 bytes단위로 정의를 하는데, storage 에서 minimum extent가 정의된 경우 이것의 배수로 round up을 하고 정의가 안된 경우 five block의 배수로 된다 오라클은 정확한 extent size를 구하기 위해서 dictionary cache를 먼저 찾는다. 만약 찾지 못하면 해당 extent size와 정확하게 일치하거나 이것보다 것을 fet$에서 찾는다. 정확히 일치하는 것이 없다면 오라클은 두번째 찾기를 반복하여 larger area를 찾는다. 찾으면 larger area로 부터 요구된 extent size를 사용한다. 여기서도 없다면 테이블 스페이스를 coalesce를 한 후. 찾기를 반복한다. 이러한 찾기 프로세스에서 찾지 못하면 tablespace를 확장한다.

인접한 사용되지 않은 extents는 더 큰 extent로 coalsece를 한다. free space는 3가지 방법으로 coalesce를 한다.

1. 요구시(serarch 시 필요시)

2. 수동 (alter tablespace .....coalesce)

3. SMON은 pctincrease 가 0 보다 큰 경우 space transaction하는 동안 수행한다.

8.5 Data block.

● data block layout

데이타 블럭은 header, table directory, row directory , row data 와 약간의 free space를 포함한다. 블럭 헤더는 다른 블럭과 마찬가지의 고정적인 정보를 가진다.

▶ cache layer

- interested transaction list(ITL)의 개수

- 데이타/인덱스 flag

- free list와 free list에서 다음 블럭(free list chain)정보를 포함한다.

ITL은 트랜잭션이 끝날 때 까지 row locked 표시하기 위해서 사용된다. ITL은 트랜잭션

식별자를 포함한다. ITL은 디폴트로 1이며 initrans/maxtrans를 가지고 조절할 수 있다.

▶ 트랜잭션 layer

- 정확한 ITL 개수

- undo address 정보

- 상태 flag

- 트랜잭션에 의해서 영향을 받은 로우의 개수

- free space credits을 포함한다.

free space credits 는 commit를 할 때 까지 update 또는 delete에 의해서 free된

모든 free space를 잡는다.

- 테이블 디렉토리는 데이타 블럭내 로우를 가진 테이블에 대한 track를 한다.

- TFL은 분리된 구조로 블럭을 저장되어 있으며 external parameter로 조절할 수 없다. 세그먼트당 최소 16개이다. 내부적으로 오라클에 의해서 생성된다. 각 TFL은 오직 하나의 transaction에 연계된다. 따라서 TFL 최대값에 도달하면 다른 트랜잭션은 TFL이 free 될 때까지 wait하여야 한다. v$wait와 v$transacion을 모니터 하여 알 수 있다.

- row directory는 데이타 블럭에 저장된 row에 대한 정보를 포함한다.

▶ row data

row data format를 나타낸다. 블럭내 이용할 수 있는 공간이 row data와 block header 사이에 있다고 말하고 있다. 로우가 insert or update 될 때, 로우 데이타는 블럭 끝에서 부터 쌓인다. 블럭 헤더는 ITL의 수와 추가된 row 등 기타 등등의 이유로 위에서 아래로 증가한다.

free space는 pctused와 pctfree의 결합으로 관리된다. pctfree 는 블록이 더 이상 insert 하지 못하도록 결정하는 것이며, pctused는 블럭에 다시 insert를 할 수 있는지 결정한다.

TIP

pctused + pctfree는 75 이상 이어야 한다.

참고

pctfree는 10% 보다 작더라고 항상 적어도 10%가 디폴트이다.

● row layout

row data는 블럭내에서 아래에서 위쪽으로 성장한다. row format의 일부분이 row flag, lock type(ITL entry),로우의 컬럼 개수, 클러스터 key 지시자를 포함한다.

컬럼의 길이(1byte 만약 254보다 길이가 작다면 그렇지 않다면 3byte)와 실제 컬럼

데이타를 포함 그러나 null value는 저장되지 않는다. dump를 읽을때 next row은 다른

row flag의 출현으로 지시되어 진다.

다음 테이블은 row flag 중 일부 값을 보여준다.

저작자표시 비영리 변경금지

'분류없는 게시글' 카테고리의 다른 글

오라클 테이블 사이즈 계산방법(수정 및 확인중) (0)	2011.07.05
Oracle Wait Event 모니터링 (0)	2011.06.22
DBMS의 심장 트랜젝션과 동시성 제어 (0)	2011.06.22
Partitioned Table (0)	2011.06.22
Index Organized Table (0)	2011.06.13

기본 SQL과 오라클 기본 작동방식

1.1 통계 (V$SYSSTAT, V$SESSSTAT)

1.2 정의

1.3 수행

1.1.3 PGA_AGGREGATE_TARGET를 튜닝하기 위한 새로운 두개의 view.

2. 초보 오라클

1.2 SQL TIP

2.1.1 Deptno 순으로 시퀀스 부여

2.1.2 각 부서별 시퀀스 부여

2.1.3 salary를 1위부터 5위까지 구하기

2.2 SPACE 가 포함된 CHAR 컬럼을 VARCHAR2로 변환

2.3 한글 DATA 조회시 ???? 로 출력되는 경우

2.4 LONG COLUMN을 가진 테이블이 COPY

2.5 날짜 변환 예제

2.6 그룹별로 하나의 로우만 가져오기

2.7.1 중복 로우 제거

2.7.2 SAL 큰 순서로 10개 로우를 구해라.

2.7.3 SAL 큰 순서로 10등까지 구해라.

2.7.4 부서별 N등까지를 구하라

2.9 INIT PARAMETER (8개)

2.10 TABLE 변경과 VIEW(WITH CHECK OPTION) 관계

select sql_text from v$sqltext a, v$session b where a.hash_value = b.sql_hash_value and logon_time <= to_date(to_char(sysdate-1/144, 'yyyymmdd hh24:mi:ss') ,'yyyymmdd hh24:mi:ss') and b.status= 'ACTIVE' order by a.address, a.piece2.12 CONNECT 이 안끊어지는 SQL 찾기

2.14.1 절차

4. SERVER PROCESS 실행단계

4.2.1 개요

4.2.4 Processing SQL Statements SQL문의 실행순서 = Server Process의 실행단계

4.2.8 Database Writer (DBWR) Process LGWR, DBWR의 동작

5.8.1 SGA(Shared Global Area)의 구성

5.8.4 MTS(Multithreaded Server)의 경우 Shared Pool Size

5.8.6 shared pool 튜닝

5.9.1 Buffer Cache 특징 ?

5.9.2 Server Process가 db buffer cache로 data 읽어오는 step (매우 중요)

5.9.3.1 개요

5.9.3.2 buffer cache란

5.9.3.3 buffer cache 튜닝

5.9.3.4 DBWR의 성능 향상

5.9.3.5 buffer cache size과 config

5.9.3.6 관련된 wait와 latch

5.10.3 redo log latch 관련 init parameter

5.10.4 redo log 관련 성능문제 검사 및 해결

5.13.1 Rollback Segment의 tuning

5.13.2 RBS Header contention 분석

5.13.3 소개

5.15 SQL ISSUES 와 TUNING CONSIDERATIONS FOR DIFFERENT APPLICATIONS

5.15.1 분석 tool

7. 자원 경합을 해결하는 기능들

'분류없는 게시글' 카테고리의 다른 글

'분류없는 게시글' Related Articles

티스토리툴바

select sql_text

from v$sqltext a, v$session b

where a.hash_value = b.sql_hash_value

and logon_time <=

to_date(to_char(sysdate-1/144, 'yyyymmdd hh24:mi:ss') ,'yyyymmdd hh24:mi:ss')

and b.status= 'ACTIVE'

order by a.address, a.piece
2.12 CONNECT 이 안끊어지는 SQL 찾기