[튜닝] Performance Moniter and Tuning 관련 기술 모음
Oracle Performance Moniter and Tuning 관련 기술 모음
Performance And Tuning
=========================================================================================
* Tuning Goal
– 최소 block만 access하도록
– 원하는 data(자주 사용되는)는 memory에 오래 보존되도록
– application code를 공유하도록 (parsed code는 반복적으로 parsing되지 않도록)
– Read/Write는 가능한한 빠르게(RBS관련)
– user가 resource사용을 위해 기다리는 시간을 최소화(lock,rbs 관련)
=========================================================================================
* Tuning Steps
a. Tune the design
b. Tune the application (sql 문)
c. Tune memory (SGA tuning – shared pool, db buffer cache, redolog buffer 순)
d. Tune I/O
e. Tune contention
f. Tune the operation system
=========================================================================================
3. Oracle Alert and Trace Files
=========================================================================================
* Alert & Trace files
a. Alert log file : db 상황, Oracle Instance가 수행시 발생하는 error등 (DB당 하나생성됨)
init.ora 에 보면 다음과 같이 지정된 경로에 생성된다.
background_dump_dest = ….
b. Background Process trace file : LGWR,DBWR,SMON,PMON,CKPT….
(문제시마다 생성되므로 여러개 생성됨)
init.ora 에 보면 다음과 같이 지정된 경로에 생성된다.
background_dump_dest = ….
c. User trace file : user request에 의해서 생성 server의 processing중 resource consumptio등
(여러개 생성됨)
c.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로 확인할 수 있다.
SQL> select sid, serial#,username,status from v$session;
SID SERIAL# USERNAME STATUS
———- ———- —————————— ——–
1 1 ACTIVE
2 1 ACTIVE
3 1 ACTIVE
4 1 ACTIVE
5 1 ACTIVE
6 1 ACTIVE
7 101 ACTIVE
8 101 ACTIVE
9 101 ACTIVE
10 101 ACTIVE
11 1 SYS ACTIVE
– alter session set sql_trace=true; => connect 된 user에 대해 enable시킴
execute dbms_session.set_sql_trace(true); 도 마찬가지
– init.ora 의 sql_trace=true로 놓고 올리기 => 전체 session에 대해 (선호하지 않음)
c.2 Controlling User Trace Files
– init.ora file의 user_dump_dest 경로에 file생성됨
– init.ora file의 max_dump_file_size 에 설정된대로 trace file의 size 제한 가능
=========================================================================================
* alert file과 trace file의 생성위치 확인
SQL> show parameter dump
NAME TYPE VALUE
———————————– ——- ——————————
background_dump_dest string D:\\Oracle\\admin\\IBM\\bdump
max_dump_file_size string 10240
user_dump_dest string D:\\Oracle\\admin\\IBM\\udump
=========================================================================================
4. Utilities and Dynamic Performance Views
=========================================================================================
* V$xxx 는 dynamic troubleshooting and dictionary views
DBA_xxx 는 dictionary views
* UTLBSTAT.SQL 과 UTLESTAT.SQL script는 필요한 기간동안 사용된 db의 사용을 볼 수 있는 script
tuning초기에 data수집을 위해 사용하는것이 보통이다.
=========================================================================================
* UTLBSTAT.SQL, UTLESTAT.SQL 을 사용하여 data 수집 sample
a. 준비
$ sqlplus internal
다음과 같이 timed_statistics parameter를 확인후
SQL> show parameter timed_statistics
NAME TYPE VALUE
———————————— ——- ——————————
timed_statistics boolean FALSE
false로 되어있으므로
SQL> alter system set timed_statistics=true;
SQL> show parameter timed_statistics
NAME TYPE VALUE
———————————— ——- ——————————
timed_statistics boolean TRUE
변경되었다.
b. UTLBSTAT.sql 을 돌리고 필요한 data 수집을 위한 시간 경과후 UTLESTAT.sql을 돌려서 종료한다.
– recommendation : 필요한 실운영시 돌린다.
중간에 db restart는 절대 금물
timed_statistics=true로 하는게 좋다.
tablespace는 default가 아니게 별도로 설정하는게 좋다.
c. 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을
가지고 있다.
=========================================================================================
* System 전반적인 상태
a. General System_Wide Statistics : v$statname, v$sysstat ==> system 상태 보는 view
ex)
SQL> select name,class,value from v$sysstat; => instance startup되고 난 후 누적치로 나옴
NAME CLASS VALUE
————————————————– ———- ———-
logons cumulative 1 830
logons current 1 12
opened cursors cumulative 1 4015
opened cursors current 1 2
user commits 1 0
user rollbacks 1 0
user calls 1 1920
recursive calls 1 69008
recursive cpu usage 1 0
…..
위에서 class값은
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 목적에 참조
b. SGA Global Statistics : v$sgastat => instance start되고 난후 SGA의 usage 상세정보
c. Waiting Events Statistics : v$event_name, v$system_event
=> resource에 대한 waiting check
d. General Session-Related Statistics : v$mystat, v$session, v$sesstat 으로 확인
ex)
SQL> select username,name,value
2 from v$statname n, v$session s, v$sesstat t
3 where s.sid=t.sid
4 and n.statistics#=t.statistics$
5 and s.type=’USER’
6 and s.username is not null
7 and n.name=’session pga memory’
8 and t.value > 30000; ==> 3000 byte이상 사용하는것
USERNAME NAME VALUE
——————– —————————————- ———-
SYS session pga memory 94424
SYS session pga memory 197624
e. Session Waiting Events Statistics : v$session_event, v$session_wait
ex)
SQL> select sid,event
2 from v$session_wait
3 where wait_time=0; => wait_time=0 이면 waiting중임을 의미
SID EVENT
———- —————————————————————-
1 pmon timer
2 rdbms ipc message
3 rdbms ipc message
6 rdbms ipc message
8 rdbms ipc message
10 rdbms ipc message
9 rdbms ipc message
7 rdbms ipc message
4 rdbms ipc message
5 smon timer
11 SQL*Net message from client
=========================================================================================
* utlbstat, utlestat을 실행하여 얻은 report.txt에서 얻을 수 있는 정보
a. Library cache statistics
(p-code가 들어가는 공간:SQL,PL/SQL 공간)
b. System statistics
(여러가지 정보가 제공됨)
c. Wait events statistics
(Wait 의 Total Time을 보고 병목부분을 찾을 수 있다.)
d. Latch statistics
(Latch란? SGA의 internal structure보호하기위해 단시간동안 잡았다 놓는 lock)
e. Rollback contention statistics
(rollback header 정보를 얻기위해 경합이 있으면 performace저하)
f. Buffer Busy Wait statistics
(Buffer Busy Wait 가 높으면 data block, segment header, undo header중 어느 block에
병목이 걸리는지 알 수 있다.)
g. Dictionary cache statistics
(dictonary cache의 object에 access할때 miss가 높으면 I/O 가 높아져 performance가 낮아진다.
cache된 각 항목의 gets/misses를 알 수 있다.)
h. I/O statistics per data file and tablespace
(각 datafile, tablespace의 physical read/write, physical block read/write, 소요시간을
알아 여러 disk에서 file I/O 분산을 보여준다.)
i. 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 .05
DBWR transaction table writes 7
7 .61 0
DBWR checkpoints 는 DB Writer에게 보내진checkpoint의 증가수를 보여준다.
checkpoint동안 data I/O의 증가는 system performance를 떨어뜨릴 수 있다.
이때 checkpoint수를 줄이기 위해
init.ora file의
log_checkpoint_interval, log_checkpoint_timeout을 늘려줘서 checkpoint 발생을 좀 적게
해줄수 있다.
<주의> 하지만 이 경우 DB의 recovery time 이 좀 길어질 수 있다.
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 .12
physical reads 265
265 23.04 .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한 수
이 정보들을 가지고 hit ratio 측정하여 database buffer cache가 충분한지 sizing할 수 있다.
=========================================================================================
* v$session_wait view : active session이 waiting하고 있는 resource나 event의 list
어떤 event들이 waiting을 하고 있는지 monitoring하기 좋다.
SQL> desc v$session_wait
이름 널? 유형
——————- ——– ———————
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64) -> waiting하고 있는 resource나 event
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
위에서 p1text,p2text,p3text로 나오는 값들은 v$event_name에 정의되어 있는
additional parameter들이며 p1,p2,p3 는 그것들의 값이고
p1raw,p2raw,p3raw 는 value의 hexadecimal값들이다.
중요한 값인 wait_time column은 timed_statistics parameter가 true로 되어 있을때만 추출이 가능
false이면 저 위에서 적어둔것 처럼 변경.
* wait_time 의 값에 따른 설명
>0 : session의 마지막 wait time
=0 : session이 현재 waiting
=-1 : wait time이 1/100초 미만
=-2 : system에서 time정보를 제공하지 않음
이외에도 system,session에서의 wait 횟수를 보려면 v$system_event, v$session_event를 본다.
=========================================================================================
5. Tuning the Shared Pool
=========================================================================================
* SGA(Shared Global Area)의 구성
– Database buffer cache : data block이 들어가는 영역(I/O 줄이는게 관건 : db_block_buffer 조정)
– redo log buffer
– 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정보를 가지고 있는 영역
– Large Pool
=========================================================================================
* Library Cache Tuning Goal
– Library Cache는 SQL이나 pl/SQL block의 parsed code가 저장되는 영역으로
LRU Algorithm으로 되어있음
– tugnig 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;
POOL NAME BYTES
———– ————————– ———-
shared pool free memory 21708792
shared pool dictionary cache 235084
shared pool library cache 835264
shared pool sql area 343636
shared pool sessions 147108
….
위의 v$sgastat에서 주의해야 할 점은 초반이 아닌 운영이 한참 되고 있는 상태에서 free memory가
많다는것은 얼핏 memory가 적게 쓰이니까 좋아보이지만 실제 LRU Algorithm에 의해 거의 memory는
사용되는게 원칙이므로 실제는 fragmentation으로 인해 못쓰는 공간이 많다는 의미가 된다.
=========================================================================================
* Cursor가 share 되고 있는가?
아래의 gethitration(gethits/gets)는 90%이상이 되어야 한다.
이하이면 대,소문자별 select가 따로있나, invalidation이 많나 check해본다.
SQL> select namespace,gethitratio
2 from v$librarycache;
SQL AREA .986897718
TABLE/PROCEDURE .782696994
BODY .5
TRIGGER 0
INDEX 0
CLUSTER .962121212
OBJECT 1
PIPE 1
아래에서 대략의 sql에 대한 execution이나 load등을 확인 할 수 있다.
SQL> select sql_text, users_executing, executions, loads
2 from v$sqlarea;
sql을 자세하게 보려면 v$sqltext를 조회해 본다.
SQL> select * from v$sqltext
2 where sql_text like
3 ‘select * from scott.s_dept where id =%’;
=========================================================================================
* 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 410 .954 1087 .964 0
1
위에서 reloads/pins 가 1%보다 크면 init.ora에서 shared_pool_size 증가시킨다.
* invalidation은? schema object가 변경되어 shared SQL area가 invalidation 됨을 의미
(reparse된다.)
=========================================================================================
* memory의 설정 –> shared_pool_size 결정 방법
Application이 얼마나 많은 memory를 사용하나? (Global Space Allocation) 를 산정하자.
–> 아래에 기술된 내용을 자세히 보면
Shared_pool_size는 아래의 (a)+(b)+(c)+(30%정도의 free space추가) 로 잡는다.
먼저 init.ora에서 shared_pool_size 을 충분히 크게 setting해두고
application을 돌려보고 다음을 조회해 보면
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.ora에
shared_pool_reserved_size를 설정해 주는데 일반적으로 shared_pool_size의 10%정도를 초기값으로
설정하고 필요시 늘려준다. 대신 shared_pool_size 의 50%를 넘을 수 없고 넘으면 startup시
다음과 같은 error가 난다.
SQL> startup
ORA-01078: 시스템 매개변수 처리 오류입니다
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 사용하여 측정
a. shared_pool_reserved_size가 작으면 : – shared_pool_reserved_size, shared_pool_size 늘림
– reserved list에서 할당된 memory의 수를 줄임
=========================================================================================
* 어떻게 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 되지 않는다.
=========================================================================================
* Data Dicitonary Cache Tuning : v$rowcache
Library cache와 함께 Shared pool 의 part인 Data Dicitonary Cache에서의 Tuning 역시
miss율을 줄이는 것이다.
주의해야 할 점은 db startup 직후 이를 측정해 보면 당연히 miss율이 높다. Data Dicitonary Cache
를 check하기 위해서는 어느시간 사용한 후에 miss율을 측정해본다.
SQL> select parameter, gets, getmisses, getmisses/gets
2 from v$rowcache
3 where gets !=0;
PARAMETER GETS GETMISSES GETMISSES/GETS
——————————– ———- ———- ————–
dc_free_extents 144 12 .083333333
dc_segments 29 22 .75862069
dc_tablespaces 7 1 .142857143
dc_users 2118 1 .000472144
dc_rollback_segments 275 8 .029090909
dc_objects 500 221 .442
dc_object_ids 189 22 .116402116
dc_sequences 1 1 1
dc_usernames 31 3 .096774194
위 값들이 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
dc_synonyms 3 2 0 0 0
4 2
=========================================================================================
* MTS(Multithreaded Server)의 경우 Shared Pool Size
– Server configuration 비교
a. Dedicated Server – User Process:Server Process = 1:1
이때 Server Process는 Shared Pool 밖의 PGA 영역에 생성됨
(PGA = UGA + Stack Space )
UGA = Sort Area + Cursor State + User Session Info
b. MTS – User Process:Server Process = 1:1
MTS의 경우 UGA가 Shared Pool 한으로 들어옴
==> MTS의 경우 동시에 몇개의 process를 운용하는가에 따라 Shared Pool Size를 늘려줘야함
=========================================================================================
* 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
=========================================================================================
* The Large Pool : LRU Algorithm 적용받지 않음 -> 즉 공간 없으면 error
– SGA내에 Shared Pool과는 별도의 memory공간을 만듦
large_pool_size parameter setting한다
Advantages : – MTS server구성을 위한 session memory로 사용
– 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
SQL> select * from v$sgastat;
POOL NAME BYTES
———– ————————– ———-
large pool free memory 614400
* large pool의 configuration : minimum – 600 KB
maximum – 2GB 이상(OS에 따라)
=========================================================================================
6. Tuning the Buffer Cache
=========================================================================================
* Goal : server find data in memory
역시 buffer cache의 hit ratio가 관건
경우에 따라 multiple buffer pools 생성, sizing
LRU latch contention, free list contention 등도 고려하여 tuning
DB_BLOCK_BUFFERS 증가
Cache tables
=========================================================================================
* 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?
이해를 쉽게 하기 위해 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의 임계치에 이르게 되면 DBWn process가 disk에 writer하고
dirty buffer는 free buffer로 바뀐다.
d. Free buffers, Dirty blocks, Pinned buffers ? 위에서 언급된 내용처럼
Free buffers : memory와 disk의 image가 같음
Dirty blocks : memory와 disk의 image가 다름
Pinned buffers : 사용중인 block
=========================================================================================
* Server Process가 db buffer cache로 data 읽어오는 step
a. hash function 이용하여 해당 block이 memory내에 존재하는지 찾는다.
(있으면 MRU end로 이동하고 끝)
없으면 다음 step 으로 진행
b. server process가 LRU list로부터 free block을 찾아 data file로 부터 읽어온다.
그리고 block은 LRU list의 MRU end로 이동
LRU list를 찾는동안 dirty blocks을 dirty list에 등록한다. 이때 dirty list의 임계치를
초과하면 DBWn process가 data buffer cache로부터 dirty block을 flush한다. 또 free block을
찾다가 search 임계치까지 free block을 못찾아도 DBWn 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-SecondTime-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에 악영향)
– Clean Shutdown : shutdown Normal, Immeidate, Transactional 의
경우(Abort만 빼고)
=========================================================================================
* Cache Hit Ratio : 이것 역시 db startup 이후 어느정도 안정되게 사용한 후 계산해야 맞다.
SQL> select 1-(phy.value / (cur.value+con.value)) ” Cache Hit Ratio”
2 from v$sysstat cur, v$sysstat con, v$sysstat phy
3 where cur.name = ‘db block gets’
4 and con.name = ‘consistent gets’
5 and phy.name = ‘physical reads’;
Cache Hit Ratio
—————-
.95751146 > 90%
위에서 db block gets + consistent gets = logical gets 이므로
1-(physical gets / logical gets) 가 cache hit ratio이다.
* 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을 한다.)
DB_BLOCK_BUFFERS를 늘리고 속도를 test해 보아 효과가 없을 경우에는 반복적으로 할 필요없이
답이 아니다.
=========================================================================================
* 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 이상정도면 적당.(일반적으로 sigle buffer cache일때와 같다.)
multiple buffer cache로 사용해도 특별히 설정 안하고 쓴다.
(자동으로 잡힘)
* 설정 방법
init.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 이다.
defaul의 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로도 알 수 있다.
ex)
analyze table codes estimate statistics;
SQL> select table_name, blocks
2 from dba_tables
3 where owner = ‘HR’ and table_name = ‘codes’;
해보면 해당 table 중 header를 뺀 사용중인 blocks 가 나온다.
* Recycle buffer pool 설정?
recycle pool을 diable시켜두고 찾는다.
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
———- —————————— ———-
0 IDL_UB1$ 502
0 ATTRCOL$ 208
0 CLU$ 208
0 COL$ 208
0 ICOL$ 208
0 IND$ 208
0 ICOLDEP$ 208
0 LOB$ 208
— 전체 object에 대해 block의 합이 recycle buffer 에서 사용될것이고
이값의 1/4 이 recycle pool size로 잡으면 된다.
=========================================================================================
* 현재 session중에서 i/o 에 관한 통계정보
SQL> select io.block_gets, io.consistent_gets, io.physical_reads
2 from v$sess_io io, v$session s
3 where s.audsid = USERENV(‘SESSIONID’)
4 and io.sid = s.sid;
BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS
———- ————— ————–
0 0 0
0 0 14
0 0 7
0 0 35
8396 23230 6474
265 24 1
8 2 0
0 0 0
0 0 0
0 0 0
0 0 0
11 rows selected.
=========================================================================================
* 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_SETID HI_SETID 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)
또, Sv$system_event, v$session_wait로도 wait정보 찾아볼 수 있다.
SQL> select event,total_waits
2 from v$system_event
3 where event in (‘free buffer waits’,’buffer busy waits’);
=========================================================================================
* Caching Tables
OLTP 성 업무에서 가능한 한 Full Table Scan은 삼가해야 한다.
Full Table Scan은 LRU end로 가서 빨리 memory에서 내려가는데 Cache를 사용하면 MRU end로 가서
memory에 오래 남게 할 수 있다.
사용방법 :
a. Create a table using th CACHE clause
ex) create table cache
b. Alter a table using CACHE clause
ex) alter table
cache
c. Code the CACHE hint clause into a query
ex) select /*+ cache */ * from codes;
select –+ cache * from codes;
주의 : 너무 자주 사용하지 말고 너무 큰 table은 cache사용하지 말라.
=========================================================================================
* 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 재생성해라)
=========================================================================================
7. Tuning the Redo Log Buffer
=========================================================================================
* 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 = ‘log buffer space%’;
SID EVENT SECOND_IN_WAIT STATE
—— ————————– ————— ——-
5 log buffer space 110 WAITING
아래는 내가 쓰려는 공간이 아직 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
———————————– ——
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
— redo log group의 size와 수를 확인해라…(중요)
— alert.log file을 보면 spining시 “CHECKPOINT NOT COMPLETE” 가 자주 발생(2~3분마다)
— 다음과 같이 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%’;
— 다음 parameter 확인
SQL> show parameter log_checkpoint
NAME TYPE VALUE
———————————— ——- ——————————
log_checkpoint_interval integer 10000
log_checkpoint_timeout integer 1800
log_checkpoints_to_alert boolean FALSE
– archiver가 redo log를 archived redo logfile에 쓰지 않아 LGWR가 기다리는경우
— archive device가 full이 아닌지 확인
— redo log group 을 추가
— 다음과 같이 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’;
— ARCn process가 바쁠때 LGWR가 새로운 ARCn process를 띄우도록 다음 parameter 설정
log_archive_max_processes = n -> n개 만큼 까지 ARCn process를 띄우면서 작업
— data block, redo log block이 깨졌는지 안깨졌는지 확인하는 mechanism인
db_block_checksum parameter가 true로 되어있는지 확인
=========================================================================================
* Redo Operation을 줄이기 :
Redo operation이 줄면 redo entry가 그만큼 적어지고 redo log buffer space가 덜 사용된다.
a. archiving 없이 direct path loading
– SQL loader로 db가 noarchivelog mode일때
b. NOLOGGING mode사용해 direct path loading
– table에 nologging mode로 setting하거나 control file에 unrecoverable 절을 사용할때
c. NOLOGGING mode에서 direct load INSERT
– tables, tablespaces, indexes에 적용
– redo log buffer에 recording이 안됨
– insert /*+ Append */ into emp.temp select * from emp;
이렇게 하면 free list에서 찾아서 insert하는게 아니라 High Water Mark이후에 왕창
가져다 insert하는것임
d. 특정 SQL 문에서 NOLOGGING을 사용
– create table …. as select ….
– create index…
– alter index … rebuild
에만 사용가능
=========================================================================================
8. Database Configuration and I/O Issues
=========================================================================================
* objectives : – system,rbs,temp,data,index tablespace들의 사용을 분석
– locally managed TS 사용(8i new feature: 이전까지는 Dictionary Managed TS)
– Detect I/O Problem
– minimize I/O contention & 적당한 device분산
– 적당한 striping
– checkpoing 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
=========================================================================================
* Tablespace 사용 : 가급적 많이 분리하자(data dictionary object,rollback segments,temporary segment,
tables, indexes,very large objects)
– system tablespace는 data dictionary object전용으로
– 공간관리 문제를 피하기 위해 locally managed tablespace를 생성
– table, index등은 각각 다른 tablespace로 분리
– rbs도 다른 tablespace로(rbs는 수시로 I/O 일으키므로 fragmentation의 경향 높다.)
– 한개이상의 temporary tablespace사용
– very large object도 따로 TS생성 사용
=========================================================================================
* file들을 여러 device로 나누자.
– data file(DBWn 작업), redo log files(LGWR 작업) 은 둘다 바쁜작업이므로 분리
– stripe table data
– reduce disk I/O(중요한 factor)
– raw devices사용 고려 (Unix)
=========================================================================================
* 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
allocate extent (datafile ‘file명’ size 10M);
– parallel query 사용
=========================================================================================
* Full Table Scans
a. full table scan이 필요한지 조사 : index찾고 db찾고 하는 cost와 한번에 full table scan으로
db read하는 cost비교하여 cost가 작은쪽으로
->작은 크기 table은 Full Table Scan하는게 이익
– 한번에 10개의 block을 일어온다치되 extent가 8개의 block이면 한번에 두 extent에서
10block을 일어오는게 아니라 두번에 나눠서 읽어온다.
NAME VALUE
—————————— ——-
table scans (short tables) 49 -> 5block이하 scan
table scans (long tables) 42 -> 5block이상 scan : 이값이 높으면 index생성,변경 고려
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0
b. db_file_multiblock_read_count : 한번에 읽어올 block수
실제 한번 I/O당 size는 DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT
c. v$session_longops view로 long-runing full table scans를 monitoring
(v$session_longops 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.
SQL> select phyrds,phywrts,d.name
2 from v$datafile d, v$filestat f
3 where d.file#=f.file#
4 order by d.name;
PHYRDS PHYWRTS NAME
———- ———- ————————————————–
4 2 D:\\ORACLE\\ORADATA\\IBM\\DR01.DBF
4 2 D:\\ORACLE\\ORADATA\\IBM\\INDX01.DBF
18 16 D:\\ORACLE\\ORADATA\\IBM\\RBS01.DBF
1061 5 D:\\ORACLE\\ORADATA\\IBM\\SYSTEM01.DBF –> 가장 physical read가 많음
4 2 D:\\ORACLE\\ORADATA\\IBM\\TEMP01.DBF
4 2 D:\\ORACLE\\ORADATA\\IBM\\TOOLS01.DBF
4 2 D:\\ORACLE\\ORADATA\\IBM\\USERS01.DBF
b. 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가 부족하다는것을 나타냄
=========================================================================================
* Online Redo Log File Configuration : 최소 2group member
각 group당 member수,size는 동일하게 맞추는것이 좋다.
redo log file은 raw device에 생성할 수 있다.
group1 group2 group3
nember1-1 member2-1 member3-1 disk1
——————————————-
member1-2 member2-2 member3-2 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에 저장
– 어떤 log flie/member가 사용중인지 v$logfile,v$log로 monitor
=========================================================================================
* Archive Log File Configuration
– 하나의 ARCn process가 read하는것으로부터 LGWR 가 다른 dik에 쓰도록
– shrare the archiving work : ARCn process들이 바빠서 workload를 감당 못하면 LGWR process가
새 ARCn process를 생성한다.
manual하게 하는방법은 다음과 같다.
alter system archive log all
to
다음을 돌려봐서 archive들이 바쁘면 생성
SQL> select * from v$archive_processes;
PROCESS STATUS LOG_SEQUENCE STAT
———- ———- ———— —-
0 ACTIVE 0 IDLE
1 STOPPED 0 IDLE
….
– 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 tuning : checkpoint는 DBWn이 I/O를 수행하도록 동작시키고,
data file header와 control file에 동기화 위해 update
checkpoint가 빈번하면 DBWn이 자주 작동하므로 i/o가 잦아
run-time perforamce 감소
반면에 instance recovery time이 줄어드는 장점도 있다.
log switch 는 alert.log file에서 찾아볼 수 있다.
– 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
=========================================================================================
9. Using Oracle Blocks Efficiently
=========================================================================================
* objective
– 적정한 DB_BLOCK_SIZE의 설정
– block 내의 space usage를 최적화
– row migration과 chaining 에 대하여
=========================================================================================
* Database Storage의 Hierachy
Tablespace > Segments > Extents > Blocks
먼저 block은 I/O의 최소 단위이고 여러개의 OS Block으로 이루어질 수 있으므로
OS상의 Block의 배수로 설정하는게 좋겠죠? (같다면 더 좋을 수도….)
여러개의 Extents 는 Segment내에 구성이 되고 Segment의 space가 완전히 사용되면
Oracle Server가 새로운 extent를 segment에 할당하게 됩니다.
=========================================================================================
* 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;
OWNER TABLE_NMAE BLOCKS EMPTY_BLOCKS
—— ———– ———– ————-
HR EMP 1450 50
….
위에서 나온값을
SQL> alter table hr.emp allocate extent;
한다.
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도 줄어듭니다.(여기서 가장 중요한 이유겠죠.)
=========================================================================================
* Extent Size ?? Large Extent..
– extent를 크게 설정하면 사용되지 않는 space가 생길 여지가 있어 공간낭비가 될 수도 있지만
I/O를 줄일 수 있으므로 extent도 가급적 좀 크게 잡으시는게 좋을듯 싶네요.
다만 extent는 init.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, 뭐 이런식으로 만들면 딱 떨어지네요.
=========================================================================================
* Database Block Size : db생성시 정해지므로 변경이 어려우니 초기에 계산 잘해서 설정!!
a. block을 읽어오는 횟수를 줄이기 위해서
– large block size로 설정
– pctfree를 좀 작게 해서 block을 좀 가득가득 사용하는법(select 성 업무의 경우)
– row migration이 적게 일어나도록
(update가 잦으면 pcfree를 여유있게 설정해야 row migration이 적다.)
b. default는 2나 4KB dlrh 64KB까지 허용 (OS block size의 배수로 설정)
OS I/O size는 DB_BLOCK_SIZE와 같거나 커야함
=========================================================================================
* PCTFREE, PCTUSED
– PCTFREE : default 10, update가 없다면 아예 0으로 잡자.
100*upd/(upd+ins)
# upd : update에 의해 add 되는 평균값 (bytes)
ins : 평균 insert값
– PCTUSED : default 40, delete되어 pctused밑으로 내려가는 경우 free list에 재등록된다.
100-PCTFREE-100*rows*(ins+upd)/blocksize
# free list maintenance가 일어나기 전에 delete 되는 row 수
=========================================================================================
* 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> analyze table scott.emp compute statistics;
Table analyzed.
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에서 이 수치가 높으면
block설정 필요하죠.
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;
=========================================================================================
* Migrated & Chained Rows 보기 & 없애기
a. Migrated & Chained Rows 보기
SQL> analyze table scott.dept list chained rows; –> 수행 안되면 utlchain.sql수행해서
chained_row table생성먼저 해야함
Table analyzed.
SQL> select owner_name,table_name,head_rowid
2 from chained_rows
3 where table_name=’DEPT’;
이렇게 뽑아보면 나온다.
b. 위와 같이 뽑은 Migrated & Chained Rows들을 임시 table에 저장해두고 해당 row들을 삭제한다음
다시 insert한다.
SQL> analyze table
list chained rows;
SQL> create table table_temp from
as
select temp.* from
temp, chained_rows cr
where temp.rowid = cr.head_rowid
and cr.table_name = upper(‘
‘);
SQL> delete from
where rowid in(select head_rowid from chained_rows);
SQL> insert into
select * from table_temp;
하면 된다.
여기서 소수 row들만 Migrated & Chained Rows 이면 위와 같이 하면 되고
많은 row들이 Migrated & Chained Rows 되어 있으면 export/import table하거나
8i에서 지원하는 alter table emp move data01; 한다.
(tablespace이동 : 다른 TS이거나 원래 TS여도 무관)
* 고찰(순전히 제 생각입니다. 테스트 후 재고의 여지가 있음)
이전에 block 를 설정하는 방법은 intert성 업무인 경우엔 data chaing이 적게 일어나도로
pctfree를 작게 잡아서(update가 전혀 없다면 0으로 잡는게 좋음)block을 좀 빡빡하게
사용하도록하구요
완전히 insert만 일어난다면
pctused도 그냥 크게 잡으면 되겠네요.(free list에 재등록 되지 않으니까.. 그래도 혹시 모르니까
너무 크게 잡는건 좀 위험할듯 싶기도 하네요. ==> 만약의 update,delete를 고려하면)
너무 크게 잡고 만약 한 row라도 delete된다면 free list에 재등록되어
다음 row insert할때 chaining이 일어날것 같은데요.
(pctfree – pctused > insert되는 row size 이렇게 되어야 chaining이 없을것 같네요)
(참고로 update성인 경우에는 pctfree를 좀 여유있게 잡구요 pctused도 좀 작게 하는게 공간낭비는
있지만 performance에는 좋을듯..)
=========================================================================================
* High-Water Mark
– High-Water Mark 이후 한번도 안쓴 block 해제
alter table 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
SQL> analyze table scott.emp compute statistics;
Table analyzed.
SQL> select num_rows, blocks,empty_blocks as empty,
2 avg_space,chain_cnt,avg_row_len
3 from dba_tables
4 where owner = ‘SCOTT’
5 and table_name = ‘EMP’;
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
———- ———- ———- ———- ———- ———–
14 1 6 7507 0 40
NUM_ROWS : table내 총 row수
BLOCKS : high-water mark 밑의 block수
EMPTY : high-water mark 위의 block수(아직 사용되지 않은)
AVG_SPACE : high-water mark 위의 평균 free block수
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) 필요하다.
* Index Rebuild
SQL> alter index dept_idx rebuild;
Index altered.
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
———–
0 ==> rebuild하고 나서 0이 되었음을 알 수 있다.
– rebuild의 중요 option : 대상이 많으면 parallel, 빨리 rebuild 하게 하려면 nologging 을
쓸 수 있다.
– rebuild의 용도 : reorg
tablespace,storage절 수정
reverse index <–> normal index 로 switching
– rebuild중 query는 가능하고, 8i이상에서는 DML도 가능하다.
=========================================================================================
10. Optimizing Sort Operations
=========================================================================================
* objective : – sorting은 memory에서 일어나도록(I/O줄이자)
– temporary space를 적절히 조절
=========================================================================================
* Sort가 필요한 operaton
a. index creation : server process가 B-tree index를 생성하기 전에 index된 값들을 sorting
b. order by, group by, distinct, union,intersect,minus
c. sort-merge join : index가 없는 두 table의 column에서 query하면 각가 full table scan하여
따로따로 sort하고 merge한다.
==> 가급적 sort가 안 일어나게 다음을 권고 :
– index생성시 nosort 사용
SQL> create index s_emp_dept_id_fk on s_emp(dept_id) nosort;
– union 대신 union all 사용(이건 sort없이 그냥 append)
– table join시 index사용(sort-merge join 안일어나도록)
# SORT 발생 예
SQL> select name,value from v$sysstat where name=’sorts (rows)’;
NAME VALUE
—————————————————————- ———-
sorts (rows) 13141
SQL> select s_dept.deptno from scott.dept s_dept,scott.emp s_emp
2 where s_emp.deptno =s_dept.deptno;
SQL> select name,value from v$sysstat where name=’sorts (rows)’;
NAME VALUE
———————– ———-
sorts (rows) 13145 -> sort된 row수가 증가.
d. analyze 수행
SQL> select name,value from v$sysstat where name=’sorts (rows)’;
NAME VALUE
————————- ———-
sorts (rows) 13445
SQL>
SQL> execute sys.dbms_utility.analyze_schema(‘SCOTT’,’COMPUTE’);
PL/SQL procedure successfully completed.
SQL> select name,value from v$sysstat where name=’sorts (rows)’;
NAME VALUE
——————– ———-
sorts (rows) 13625 -> sort된 row수가 증가.
=========================================================================================
* Sort Process
Sort 할 size가 sort_area_size parameter보다 작을 경우 memory에서 sorting을 하고(이상적)
클경우는 data는 작게 쪼개져(sort runs라 부름) 각 piece별로 sorting됨.
sorting되는 부분만 memory에 (sort_area_size만큼)만 올라와 있고 나머지는
temporary tablespace에 보관된다. 나중에 merge하는이 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 : init.ora나 alter session, alter system deffered로 변경 가능
default값은 os에 의존적이고 OLTP성 업무에 적합하다.
(DSS app,batch jobs,large operation에서는 변경필요)
–> 너무 커도 O/S의 memory를 많이 사용하니까 O/S의 paging/swapping이
일어나 performance에 지대한 악영향을 줄 수 있다.
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는 반환하여 다른놈들이 쓸 수 있도록.
=========================================================================================
* 분석 tool :
v$sysstat, report.txt, v$sort_segment(sorting사용한 정보),
v$sort_usage(temp Tablespace사용한 경우만)
SQL> select * from v$sysstat where name like ‘%sort%’;
STATISTIC# NAME CLASS VALUE
———- —————- ———- ———-
188 sorts (memory) 64 18900 -> 완전히 memory에서 수행된 sort
189 sorts (disk) 64 0 -> temporary segment 에 I/O하는 sort
190 sorts (rows) 64 13625 -> monitoring 되는 동안 sort된 총 rows
Statistic Total
——————————————— ————
Per Transaction Per Logon Per Second
————— ———— ————
sorts (disk) 2
2 .17 .02
sorts (memory) 94 -> 거의 memory에서 sorting
94 8.17 .81
sorts (rows) 5109
5109 444.26 44.04
=========================================================================================
* 분석방법
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 수정
SQL> alter session set sort_area_size=1048756; ==> memory sorting할 수 있도록
=========================================================================================
* temporary space를 사용하기 : database닫힐때 drop 된다.
SQL> create tablespace datafile …. temporary
SQL> alter tablespace …. temporary
==> sorting시작순간 extent할당하고 sorting끝나면 해제하지 않고 다음에 들어온 sorting이
재사용한다.(부족하면 늘리고) ==> fragmentation문제 없다.
ex) 새로운 temporary TS생성하여 scott이 sort를 일으켰을때 disk sort가 일어나면,
그걸 사용하도록 변경
SQL> create tablespace temp2 datafile ‘$ORACLE_HOME/DATA/temp2.dbf’ size 2m temporary;
SQL> alter user scott temporary tablespace temp2;
scott으로 접속해서 temporary TS의 사용을 살펴보자
SQL> select tablespace_name,current_users,total_extents,used_extent,
2 extent_hits,max_used_blocksmax_sort_blocks
3 from v$sort_segment;
* temporary TS의 설정
– initial, next의 값은 sort_area_size의 배수로 한다.
– pctincrease는 0으로 크기가 균등하게 한다.
SQL> select s.username,u.”USER”, u.tablespace, u.contents,u.extents,u.blocks
2 from v$session s, v$sort_usage u
3 where s.saddr=u.session_addr
4 and u.contents=’TEMPORARY’;
s.username : 누가 이 view를 query했는지.
# temporary TS는 여러disk에 striping되어야함.
초당 50 I/O를 하는 두개의 disk에 striped되었으면 100 I/O가 되니까..
=========================================================================================
11. Tuning Rollback Segments
=========================================================================================
* objective : – rbs performance check를 위해 dynamic performance view 사용
– rbs 재구성, monitoring
– rbs 수와 size 설정
– transaction에 적절한 rbs할당
=========================================================================================
* Rollback Segment Usage
– Transaction Rollback
– Transaction Recovery
– Read Consistency
rbs는 db startup시마다 offline으로 바뀜. 다만 init.ora 에
rollback_segments=…. , ….. , ….
위와같이 설정해주면 startup시 online으로..
* 사용 원칙 : 가능하면 rbs는 늘리지 말고 있는것 가지고 쓰자.
각 rbs는 첫번째 extent의 첫 block에 transaction정보를 가지고 있는 transaction table이
있는데 rbs사용시 여길 먼저 읽고 update한다. 여러개의 transaction이 순차적, 순환적으로 쓰임.
# OLTP성 업무에서처럼 transaction이 많을때 rbs 1개만을 사용하면 여러 transaction이
하나의 segment header를 update하려 하므로 contention이 있을 수 있다.==>rbs 갯수를 늘리자.
# batch성 업무에서는 size가 큰 하나의 rbs를 할당해서 작업하도록 하자.
# rbs의 사용중 4개의 extent 가졌는데 1 -> 2 -> 3 -> 4 -> 다 쓰고 다시 1번 segment를
쓰려고 할때 1번에서 사용중인 transaction(commit안된)이 있는경우 2번이 비어있다고 할지라고
2번으로 건너뛰어 사용하는것이 아니라 새로운 extent를 추가 확장하게 된다.
=========================================================================================
* Rollback Segment의 tuning
a. transacton이 rbs를 access하기 위해 기다려서는 안된다.
(transaction이 돌고있는동안 rbs extend는 없어야)
– 다음을 적절히 살피자.
segment당 extent의 수, extent의 적절한 size, rbs의 적정수, rollback을 덜하는 더 나은
utility의 사용
b. user와 utilitiy들이 rollbak을 덜하도록
c. 하나의 transaction이 rollback sapce를 전부써서는 안됨 : rbs size,large transaction 의
split 을 고려
d. 사용자들이 read-consistent image 를 볼 수 있도록 : rbs 의 수 , size 고려
e. 다음 view들에서 각 정보를 얻을 수 있다.
– v$rollname : online rbs의 수와 이름
– v$rollstat : 각 online rbs의 activity statistics
– v$system_event : rbs header의 contention
– v$waitstat : system, nonsystem rbs의 data block,header block에 waiting된 누적 통계
– report.txt(utlbstat/utlestat)
– v$sysstat : the number of consistent and data block gets
– v$transaction : rbs를 사용하는 현 transaction
=========================================================================================
* RBS Header contention 분석
a.
SQL> select sum(waits)*100 / sum(gets) “Ratio”, sum(waits) “Waits”, sum(gets) “Gets”
2 from v$rollstat;
Ratio Waits Gets
———- ———- ———-
0 0 33452 ==> Ratio < 1% 이어야 하고 아니면 rbs를 더 생성한다.
b.
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
c. 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
401408 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는 rowid만 보존되므로 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;
USN WRITES
———- ———-
0 2996
1 561900
2 1307484
3 502128
4 608072
5 619988
6 477130
7 837952
– update에 따라
SQL> update scott.emp set deptno=10;
14 rows updated.
다음과 같이 변경되었다.
SQL> select usn, writes from v$rollstat;
USN WRITES
———- ———-
0 2996
1 562034–> 조금 사용했죠? 이transaction을 위해 사용한 rollback data의 양
2 1308840 (이값 – 이전값)
3 502128
4 608072
5 619988
6 477130
7 837952
* – 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
=========================================================================================
12. Monitoring and Detecting Lock Contention
=========================================================================================
* objective : – Lock contention 을 적게
=========================================================================================
* 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 금지
=> Manual Table-level lock 방법(수동으로 share lock거는 방법) :
LOCK TABLE IN MODE;
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 생각하면 된다.)
=========================================================================================
* Lock Contention을 일으키는 가능성
– 개발자의 불필요한 높은 level의 locking coding
– 개발자의 불필요한 long transaction coding
– user가 commit을 하지 않아 (빨리 lock을 해제하자)
– application들이 higher-level locks
=========================================================================================
* Lock Monitoring : v$lock, v$locked_object
SQL> update scott.emp set deptno=10;
14 rows updated.
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
4 ;
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 과 같다.)다음과 같이 object명 확인 가능
SQL> select object_name from dba_objects where object_id=24651;
OBJECT_NAM
———-
EMP
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)
=========================================================================================
* lock을 잡고있는 session을 강제로 죽이기
SQL> select sid,serial#,username
2 from v$session
3 where type=’USER’;
SID SERIAL# USERNAME
———- ———- ——————————
9 24448
10 35709 SYS
12 32362
16 35332 EJSADMIN –> 이 session을 죽여보자
17 16144
19 26262
24 9546 EJSADMIN
31 14724 EJSADMIN
32 389 EJSADMIN
34 2446 EJSADMIN
36 3564 EJSADMIN
v$session view에서 다음을 보고 어떤 row가 contention을 일으키는지 알 수 있다.
ROW_WAIT_OBJ#
ROW_WAIT_FILE#
ROW_WAIT_BLOCK#
ROW_WAIT_ROW#
=========================================================================================
* Dead lock
ORA-00060: deadlock detected while waiting for resource 메세지…
USER_DUMP_DEST directory에 trace file(SID_ora_PID.trc)에 보면 나온다.
재현해보자.
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가 떨어지고
user_dump_dest의 file에 다음과 같은 message가 발견된다.
….
DEADLOCK DETECTED
Current SQL statement for this session:
update scott.emp set sal= sal*1.1 where empno=7934
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-00060033-000000b9 20 10 X 18 16 X
TX-00070019-000000b4 18 16 X 20 10 X
session 10: DID 0001-0014-00000002 session 16: DID 0001-0012-00000002
session 16: DID 0001-0012-00000002 session 10: DID 0001-0014-00000002
Rows waited on:
Session 16: obj – rowid = 0000604B – AAAGBLAABAAAISaAAM
Session 10: obj – rowid = 0000604B – AAAGBLAABAAAISaAAN
….
=========================================================================================
13. SQL Issues and Tuning Considerations for Different Applications
=========================================================================================
* 분석 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)이 수행되어 plan_table이 생성된
이후에 가능
a.2 explain plan [set statement_id=’..’]
into table명 for ;
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
실제 DML문도 작업이 일어나기 때문에 주의해야 함( 일반적으로 DML문은 explain plan을 사용하자)
b.1 init.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명 : _ora_????.trc)
– 우선 다음 4가지중 하나로 sql_trace=true로 전환
init.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(,,true);
=>특정 user session
– 필요한 문장 수행
select ………..;
select ………….;
.
.
– 다음 방법중의 하나로 sql_trace=false
alter session set SQL_TRACE=false;
execute DBMS_SESSION.SET_SQL_TRACE(false);
execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(,,false);
대충 user_dump_dest에 ora_51122_ibm1.trc 과 같은 형식으로 file이 생성됨
b.3 tkprof로 formatting 하여 읽을수 있도록 한다.
다음은 일반적 방법
$ tkprof explain=/ sys=no
note : explain – 실행계획 정보도 보기 위해 넣는 option
sys=no – system call에 해당하는것은 안본다.
b.4 생성된 outfile을 분석해보라…(자세한 내용은 SQL Tuning책을 보고 공부를 해야겠죠?)
내용은 대충 다음과 같다.
count = number of execution call
cpu = cpu seconds used —> 실제 중요…
elapsed = total elapsed time —> 이건 사실 network에 dependent하다.
disk = number of physical reads of buffers from disk
query = number of logical reads for consistent read
current = number of logical reads in current mode (usually for update)
rows = number of rows processed by the fetch or execute call(rows speed)
–> 중요
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
sql 수행하면 마지막에 다음과 같이 정보가 나온다.(간편.. 편리)
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=448)
1 0 TABLE ACCESS (FULL) OF ‘EMP’ (Cost=1 Card=14 Bytes=448)
Statistics
———————————————————-
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
참고 : set auto trace [on|off|traceonly] [explain|statistics]
traceonly : 수행결과는 보여주지 않고 trace만 보여줌
on explain : on일때 사용되며 statistics는 보여주지 않음
(수행결과와 execution plan만 보여줌)
traceonly statistics : statistics만 보여줌
=========================================================================================
* Optimizer mode setting
a. optiomer mode
a.1 rule-based : 자체에 query의 ranking이 있어 access path를 결정.
(syntax- & data dictionary-driven)
a.2 cost_based : least-cost path를 선택.(statistics-driven)
b. setting 방법
b.1 Instance level : optimizer_mode parameter를 setting하여
optimizer_mode={choose|rule|first_rows|all_rows} ==> 이중 하나로 setting
– choose : default값으로 통계정보가 있으면 cost-based, 없으면 rule-based로 사용
– rule : 통계정보 있더라도 rule-based 사용
– first_rows : data 100만건을 가져온다치면 화면내 처음 몇건을 보여주고 나머지는
background로 fetch ==> OLTP성 업무에 적합(응답속도 더 빨리 보이므로)
– all_rows : data 100만건을 가져온다치면 모든 row를 가져와서 보여줌
==> batch job에 적합(전체성능은 first_rows보다 이게 조금 더 좋다.)
b.2 Session level : alter session command로
SQL> alter session set optimizer_mode={choose|rule|first_rows|all_rows}
b.3 Statement level : hint 사용하여…. rule|first_rows|all_rows 는 사용가능,
choose는 여기서는 사용 불가
예)
SQL> select /*+ FIRST_ROWS */
2 *
3 from scott.emp;
==> 위에서 여러가지level에 적용되어 있다고 한다면
Statement level > Session level > Instance level 순 우선순위
c. rule-based optimizer mode를 위한 statistics
c.1 analyze commnad를 써서 통계정보 수집,삭제
analyze {index|table|cluster}
{compute|delete|estimate} statistics
[for … [size n]] [sample n {rows|percent}
– compute는 전체분석, delete는 통계정보 삭제, estimate는 smapling하여 분석
– size절 : default 75, max 254
– for절에 쓸수 있는것들.
— for table, for columns, for all columns, for all indexed columns,
for all [local] indexes
* table statistics : row수, 사용중이거나 빈 blocks, 평균 사용가능한
free space, chained&migrate row 수
평균 row 길이, 마지막 analyze날짜와 sample size, data dictionary view
(dba_tables)
ex)
SQL> analyze table emp estimate statistics for table;
테이블이 분석되었습니다.
SQL> select num_rows,empty_blocks,avg_space,avg_row_len,sample_size
2 from dba_tables
3 where table_name=’EMP’ and owner=’SCOTT’;
NUM_ROWS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE
———- ———— ———- ———– ———–
14 6 7507 40 1064
* index statistics : index level(height:2~3정도가 적당),
leaf block & distict keys 수,
key 당 leaf block 평균수, key당 data block 평균수,
index entries수,
clustering factor, data dictionary view(dba_indexes)
ex)
SQL> analyze index PK_DEPT compute statistics;
인덱스가 분석되었습니다.
SQL> select blevel,leaf_blocks,distinct_keys,clustering_factor
2 from dba_indexes
3 where index_name=’PK_DEPT’ and owner=’SCOTT’;
BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
———- ———– ————- —————–
0 1 4 1
여기서 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’;
=========================================================================================
* 통계정보를 타 DB로 copy 하기 : tuning을 용이하게 하기위해 test DB로 copy 해두고 한다.
통계정보는 data dictionary에 들어있어 sys 소유이므로 export로
옮길수 없다. 통계 table을 임시 table로 copy해 두고 export하여
다시 export/import(new db로)하고 new db의 data dictionary로
집어 넣는다.
ex) scott user의 모든 object에 대해 통계정보를 생성하여 옮기기
a. scott user로 connect하여 통계정보가 있는지 확인
SQL> connect scott/tiger
SQL> select blocks from dba_tables where table_name=’EMP’;
BLOCKS
———-
없으면 위와 같이 나오고 있다면 다음과 같이 삭제하여 초기화
SQL> execute dbms_stats.delete_schema_stats(‘SCOTT’);
b. 이제 scott userdml 모든 object에 대한 통계정보 생성, 확인
SQL> execute dbms_stats.gather_schema_stats(‘SCOTT’);
SQL> select blocks from dba_tables where table_name=’EMP’;
BLOCKS
———-
1 –> 생성된것 확인.
c. export전에 통계정보를 저장할 table생성
SQL> execute dbms_stats.create_stat_table(‘SCOTT’,’MY_STATS’);
d. scott user의 통계정보를 export한다.
SQL> execute dbms_stats.export_schema_stats(‘SCOTT’,’MY_STATS’);
e. exp util로 export
$ exp scott/tiger tables=EMP file=test.exp ==> 여기까지 일단 export다 받았다.
e. imp util로 다른 db로 import
$ imp scott/tiger tables=EMP full=n file=test.exp
참고 : 만약 test용으로 다른 db가 아닌 같은 db로 import해보고 제대로 되는지 확인하고 싶으면
다음과 같이 scott의 통계정보를 삭제해두고 같은방법으로 해보아도 좋다.
execute dbms_stats.delete_schema_stats(‘SCOTT’);
f. scott user의 통계정보를 import후 확인한다.
SQL> execute dbms_stats.import_schema_stats(‘SCOTT’,’MY_STATS’);
SQL> select blocks from dba_tables where table_name=’EMP’;
BLOCKS
———-
1 –> 제대로 import 되었다.
=========================================================================================
* 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
2 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;
=========================================================================================
* Index – B* index — normal
— reverse
– Bitmap index
a. B-Tree Indexes : 사용할수록 사용효율이 떨어지므로 주기적으로 rebuild 해 주는것이 필요
재생성
SQL> alger index rebuild
2 [parallel n] online ==> online은 DML가능하면서 index rebuild
3 [compute statistics] ==> index rebuild하면서 통계정보 생성
4 [nologging];
b. 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로 저장
– cardinality가 낮은 column에 사용( 성별 처럼… 구분이 거의 없는 column에 적합)
– 조건절이 많을때 적합( and, or등을 사용한 조건절에서 bit 연산을 하므로 performance good)
– read only에 최적(update시에는 bitmap segment전체에 lock이 걸리므로 그다지 좋지 않다)
=> OLTP성에 좋지않다. DSS system에 좋다.
– 매우 큰 table에 좋다
– storage space 적게 쓴다.
생성방법
SQL> create bitmap index emp_deptno_idx on emp(deptno)
2 storage(initial 200k next 200k pctincrease 0 maxextents 50)
3 tablespace indx01;
c. B-Tree Index와 Bitmap index의 비교
B-Tree Index Bitmap index
========================================== ==========================================
high-cardinality column에 적합 low-caldinality 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위주 작업
d. Reverse Key indexes –> 값을 뒤집어서 저장
예를들어 다음과 같이 reverse로 index만들어 저장하면
100 –> 001
101 –> 101
102 –> 201
105 –> 501
104 –> 401
leaf의 각각 다른 위치에 분산되므로 OPS 같은 환경에서의 I/O load를 분산시킬 수 있다.
– 생성방법
SQL> create unique index i1_t1 on t1(c1)
2 reverse pctfree 30
3 storage(initial 200k next 200k pctincrease 0 maxextents 50)
4 tablespace indx01;
– 재생성
SQL> alter index i1_t1 rebuild reverse;
e. Index-Organized Tables : table과 B-tree index를 위한 segment를 딸로 구성하는게 아니라
하나의 B-tree구조가 PK base로 구성하는것.반드시 PK가 있어야 함.
– 장점 : storage가 덜 필요. exact matches and range search하는 query에 유리.
– 생성방법 : 자세한 option은 manual 참조
SQL> create table sales
2 (office_cd number(3),
3 qtr_end date,
4 revenue number(10,2),
5 review varchar2(1000),
6 constraint sales_pk primary key(office_cd,qtr_end))
7 organization index tablespace indx
8 pctthreshold 20 –> row길이가 block의 20%넘으면 primary key는 원래
9 including revenue 그대로 두고 별도의 overflow segment에 따로 data
10 overflow tablespace user_data; 저장하게 한다.
f. 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보다 느리다.
g. Materialized Views : view 정의와 view 실행결과row를 저장(view생성시 query실행되고 결과는
table에 저장됨)
– index나 partition을 Mateialized view에 사용가능하고 query시 Mateialized view를 사용하여
performance향상
– 생성방법
SQL> create MATERIALIZED VIEW sales_summary
2 tablespace sales_ts
3 parallel (degree 4)
4 build immediate refresh fase –> build immediate는 create 되는순간 바로 만들어지게
5 enable query rewrite
6 as
7 select s.zip,p.product_type,sum(a.amount)
8 from sales s, product p
9 where s.product_id=p.product_id
10 group by s.zip,p.product_type;
h. 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;
=========================================================================================
* 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. hibrid system : OLTP 와 DSS를 병행하는 system
– shared_pool_size, large_pool_size, db_block_buffers, sort_area_size 고려
– online rbs : 낮시간에는 작은 size로 수 많이 밤시간에는 큰size로 수 적게
=========================================================================================
14. Managing a Mixed Workload
=========================================================================================
* 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됨
-user건 DBA건 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에 속한 모든 sessioon
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 parllelism 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 Managemet 단계
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.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
=========================================================================================
16. Multithreaded Server Tuning Issues
일반적으로 Dedicated Server방식을 많이 쓰는것으로 알고는 있지만
동시 접속자가 많은경우에는 MTS를 사용하는것이 유리합니다.
각 system에 맞게 dedicated server 와 MTS를 선택하시는것이 맞습니다.
다만 일반적으로 500 User 기준으로 이상이면 MTS를 사용하시는게 좋다고 보시면 됩니다.
=========================================================================================
* configuring the MTS : MTS를 사용하기 위해서는 반드시 Net8i가 설치되어 있어야함.
** MTS(MultiThreaded Server) 구성 **
1. Server쪽 tnsname.ora 에 리스너에 대한 tns_service name이 등록되어 있어야 한다.
은 listener.ora file에서 setting 된대로 사용할 listener를 등록해주면 된다.
tnsnames.ora
—————————————————————————————-
.= #=> 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 =
NAMES.DIRECTORY_PATH = (TNSNAMES) #===> default 가 TNSNAMES 이므로 사실 잡지 않아도 된다.
—————————————————————————————-
2. 리스너가 기동되어 있는지를 확인
$ lsnrctl stat
기동되어있다면….
———————————————————————————
LSNRCTL for 32-bit Windows: Version 8.1.6.0.0 – Production on 27-JAN-2000 15:19:
09
(c) Copyright 1998, 1999, Oracle Corporation. All rights reserved.
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))에 연결되었습니다
리스너의 상태————————
별칭 listener
버전 TNSLSNR for 32-bit Windows: Version 8.1.6.0.0 – Production
시작 날짜 27-JAN-2000 14:27:21
업타임 0 일 0 시간. 51 분. 47 초
트레이스 수준 off
보안 OFF
SNMP OFF리스너 매개변수 파일 D:\\Oracle\\Ora81\\network\\admi
n\\listener.ora
리스너 로그 파일 D:\\Oracle\\Ora81\\network\\log\\listener.log
서비스 요약…
PLSExtProc has 1 서비스 핸들러
kys has 1 서비스 핸들러
kys has 6 서비스 핸들러
명령이 성공적으로 수행되었습니다
———————————————————————————
기동되어 있지 않다면…
———————————————————————————
LSNRCTL for 32-bit Windows: Version 8.1.6.0.0 – Production on 27-JAN-2000 15:21:
07
(c) Copyright 1998, 1999, Oracle Corporation. All rights reserved.
(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))에 연결되었습니다
TNS-01103: 그 주소의 지정된 프로토콜 요소가 틀리게 지정되었습니다
TNS-12541: TNS:리스너가 아닙니다
TNS-12560: TNS:프로토콜 어댑터 오류
TNS-00511: 리스너가 아닙니다
32-bit Windows Error: 2: No such file or directory
———————————————————————————
기동되어 있지 않으면 다음과 같이 기동시키면 되지요…..
$ lsnrctl start
3. init.ora file 편집
MTS구성을 위해 다음과 같이 추가 편집
#####################################
# Multithreaded Server Environment
local_listener = .
mts_service=
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
or
$ sqlplus internal
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> startup
SVRMGR> exit
startup시 error가 떨어지면 설정을 다시 확인해서 정확하게 수정하자.
제대로 기동되면 shared server process와 dispatcher process가 정확히 떴는지 확인해보세요..
os level에서 ps -ef|grep |sort …. unix경우 뭐 이렇게 확인해보면 되겠죠??
그리고 서버에 접속하여 다음의 data dictionry 를 통해서 monitoring 해본다.
v$circuit : MTS를 이용한 접속여부를 알려준다.
v$shared_server : 공유된 서버가 시작된 것을 확인할 수 있다.
v$dispatcher : 얼마나 많은 dispatcher가 시작되었는지 알 수 있다.
$ sqlplus system/manager@
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개 떠있는것을 볼 수 있죠??
또
SQL> select * from v$dispatcher;
column이 많아서 여기 attatch하지는 않겠습니다. 직접들 확인해 보시기 바랍니다.
다음과 같이 하나의 session을 더 열자.
$ sqlplus scott/tiger@
$ 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로 구성은 하였으나 배치업무나,데이터베이스의 startup,stop작업,
“internal”로 접속을 할 경우에는 MTS Server로 접속을 할 수 없지요….
그런경우 TNS 서비스명에 서버의 형태를 지정하여 전용 서버(Dedicated Server)로 접속을 하자구요.
우선 전용서버로 접속을 할 수 있도록 TNS서비스명을 추가하고 접속해봅시다..
tnsnames.ora
——————————–
.=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = xxxx))
)
(CONNECT_DATA = (SID=xxxx)
(Server=Dedicated) #==============> 중요한 부분은 바로 여기입니다.!!!!
)
)
.
.
.
——————————–
그다음에
$ ps -ef|grep 로 확인해보면
……………………….oracle (LOCAL=NO) ====> 이런 process가 있을겁니다.
그럼 ok…
=========================================================================================
* Monitoring Dispatchers
a.
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=’,’;
=========================================================================================
* Monitoring Shared Servers
– dynamic 하게 mts_max_servers 수를 변경할 수 있다.
SQL> alter system set mts_servers=;
– shared server의 수
SQL> select count(*) from v$shared_server where status !=’QUIT’;
COUNT(*)
———-
3
– 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
* Shared Servers and Memory Usage
![[관리] sqlplus 로 오라클 연결하기](http://nblog.syszone.co.kr/wp-content/themes/hueman/assets/front/img/thumb-medium-empty.png)
3 Responses
2autonomy
1duplicity
… [Trackback]
[…] Find More here on that Topic: nblog.syszone.co.kr/archives/1057 […]