'Oracle/DBA' 카테고리의 글 목록

오랜만에 DB Guide net 에 접속했다.

그 중 Index 사용 여부에 대한 가이드 글을 읽고 느낀점에 대해서 정리해 보았다.

먼저 읽은 글의 본문은 아래와 같다.

이병국의 개발자를 위한 DB 이야기: 튜닝(31회)

개발자를 위한 DB 튜닝 실전(1편)

http://www.dbguide.net/knowledge.db?cmd=view&boardUid=192095&boardConfigUid=19&boardStep=&categoryUid=196

고작 1편보고 왜 작성하느냐...가 아니라 그동안 잠시 잊고 있었던 부분을 다시 짚는 부분과 함께,

예시 쿼리들을 직접 손으로 작성하면서 이렇게 변경해도 되는 구나 ....하기 위해서 작성하는 부분이다.

* 인덱스를 사용하는 것은 언제나 옳다???

- 답은 No 이다. 누구나 알겠지만 Full scan이 성능상 더 효과적인 경우가 있기 때문이다.

아래의 쿼리에 대해서 보자

SELECT SUM(CASE WHEN 활동구분 = ‘방문’ THEN 1 ELSE 0 END) AS 방문횟수

SUM(CASE WHEN 활동구분 = ‘우편’ THEN 1 ELSE 0 END) AS 우편횟수

SUM(CASE WHEN 활동구분 = ‘전화’ THEN 1 ELSE 0 END) AS 전화횟수

SUM(CASE WHEN 활동구분 = ‘SMS’ THEN 1 ELSE 0 END) AS SMS건수

FROM 영업활동 -- 3000만 건 이상의 대용량 테이블(10년 활동 보관)

WHERE 활동일자 BETWEEN ? AND ? -- 조회 구간 최대 1년(인덱스 있음)

AND 활동구분 IN (‘방문’, ’우편’, ’전화’, ‘SMS’)

- 테이블 전체 건수의 1/10에 해당하는 300만 건을 추출

위의 쿼리를 인덱스를 사용하면 엄청난 시간이 소요될 수 있다. 하지만 /*+ FULL(영업활동) */ 힌트를 사용하여 Full Scan을 하면 더 적게 소요될 수 있다.

이 말은 즉 데이터를 scan 시 Block 단위로 읽게(I/O) 된다. Index 를 이용하게 되면 Random access 를 하게 된다. 하지만 full scan을 하게 되면 Seqential read 하게 된다. 물론 다 그런거는 아니겠지만 대부분 Random access 하게 되면 Single block I/O 가 되며, Seqential read 하게 되면 Multi block I/O를 하게 되어 훨씬 I/O양을 줄일 수 있는 효과를 가져올 수 있다.

물론 이 내용들은 머릿속에서 엇박자로 알고 있는 부분들이라 다시 정리하게 되는 것 같다. (이해는 가지만 또 설명하게 되면 어버버 되는 상황??ㅠ)

자세한 내용은 아래에서 읽으면서 정리하는 것을 추천한다.

- I／O 효율화 원리

http://wiki.gurubee.net/pages/viewpage.action?pageId=29065463

해당 내용을 이해한다면 왜 Index를 사용안하고 Full table scan 이 더 효율적인지 이해할 수 있을 것이다.

두번째 내용은 Group by 사용과 성능 이슈 (이 부분은 기존 dbguide.net 내용을 펌~~~)

업무를 진행하다 보면 group by를 많이 사용하게 된다...내 의지와 상관없이...통계 때문일 것이다.

SELECT A.부서코드

, B.부서명

, SUM(A.판매수량) AS 판매수량

, SUM(A.판매금액) AS 판매금액

FROM 판매실적 A -- 1천만 건 이상의 대용량 테이블(10년치 판매 실적)

, 부서 B -- 수백 건 미만의 부서코드

WHERE A.부서코드 = B.부서코드

AND A.판매일자 BETWEEN ? AND ? -- 조회 구간 최대 1주일(인덱스 있음)

GROUP BY A.부서코드, B.부서명

ORDER BY A.부서코드, B.부서명

여기서는 데이터 구간이 총 데이터의 1/100 , 10만건 이하이기에 인덱스를 활용해야 한다고 한다.

또한, 부서명은 order by 절에 영향을 주지 않으며, 부서 테이블은 오로지 부서명을 조회하는 용도로만 사용한다.

부서 테이블은 부서명 조회 용도이며 부서테이블과 관련한 조건절이 없다.

SELECT A.부서코드

, (SELECT 부서명 FROM 부서 WHERE 부서코드 = A.부서코드) AS 부서명

, SUM(A.판매수량) AS 판매수량

, SUM(A.판매금액) AS 판매금액

FROM 판매실적 A -- 1천만 건 이상의 대용량 테이블(10년치 판매 실적)

WHERE A.판매일자 BETWEEN ? AND ? -- 조회 구간 최대 1주일(인덱스 있음)

GROUP BY A.부서코드

ORDER BY A.부서코드

위와 같이 변경하면 부서 테이블에 대한 접근 빈도수를 최대한 줄일 수 있음.

Group by 절의 수행 이전에 부서 테이블을 접근하였으나

개선된 쿼리는 group by 절의 수행 이후에 부서 테이블을 접근하므로 접근 빈도수가 대폭 줄어든 효과

Order by 절의 선행컬럼에 부서명이 있거나, 조건절에 부서 테이블의 컬럼이 있다면 Outer Join 방식으로 변경 불가능

SELECT A.부서코드

, B.부서명

, SUM(A.판매수량) AS 판매수량

, SUM(A.판매금액) AS 판매금액

FROM 판매실적 A -- 1천만 건 이상의 대용량 테이블(10년치 판매 실적)

, 부서 B -- 수백 건 미만의 부서코드

WHERE A.부서코드 = B.부서코드

AND A.판매일자 BETWEEN ? AND ? -- 조회 구간 최대 1주일(인덱스 있음)

AND B.사용여부 = ‘Y’ -- 현재 시점에 사용하는 부서코드

GROUP BY B.부서명, A.부서코드

ORDER BY B.부서명, A.부서코드

아래와 같이 인라인 뷰 방식의 쿼리로 개선 가능

SELECT A.부서코드

, B.부서명

, A.판매수량

, A.판매금액

FROM

(

SELECT 부서코드

, SUM(판매수량) AS 판매수량

, SUM(판매금액) AS 판매금액

FROM 판매실적 -- 1000만 건 이상의 대용량 테이블(10년치 판매 실적)

WHERE 판매일자 BETWEEN ? AND ? -- 조회 구간 최대 1주일(인덱스 있음)

GROUP BY 부서코드

) A, 부서 B -- 수백 건 미만의 부서코드

WHERE A.부서코드 = B.부서코드

AND B.사용여부 = ‘Y’ -- 현재 시점에 사용하는 부서코드

ORDER BY B.부서명, A.부서코드

부서 테이블에 대한 접근 빈도수를 최대한 줄일 수 있었으며, Group by 절의 수행 이후에 부서 테이블을 접근하는 부분이라 접근 빈도수도 줄어드는 효과

하지만, 최종 집계된 부서의 개수가 부서코드 테이블의 전체 개수의 1/100 이상이라면 아래의 쿼리처럼 힌트절 조정을 통하여 성능 개선을 추가적으로 가능

첫번째 쿼리와 같이 Full 로 변경함으로써 성능 효과 개선 가능

SELECT /*+ FULL(B) */

A.부서코드

, B.부서명

, A.판매수량

, A.판매금액

FROM

(

SELECT 부서코드

, SUM(판매수량) AS 판매수량

, SUM(판매금액) AS 판매금액

FROM 판매실적 -- 1000만 건 이상의 대용량 테이블(10년치 판매 실적)

WHERE 판매일자 BETWEEN ? AND ? -- 조회 구간 최대 1주일(인덱스 있음)

GROUP BY 부서코드

) A, 부서 B -- 수백 건 미만의 부서코드

WHERE A.부서코드 = B.부서코드

AND B.사용여부 = ‘Y’ -- 현재 시점에 사용하는 부서코드

ORDER BY B.부서명, A.부서코드

만약 조건절의 판매일자 조회 구간이 한 달 이상이라면 이번에도 인덱스를 사용치 않는 방법으로 아래와 같이 힌트를 이용해 변경 가능

SELECT /*+ FULL(A) FULL(B) */

A.부서코드

, B.부서명

, A.판매수량

, A.판매금액

FROM

(

SELECT 부서코드

, SUM(판매수량) AS 판매수량

, SUM(판매금액) AS 판매금액

FROM 판매실적 -- 1000만 건 이상의 대용량 테이블(10년치 판매 실적)

WHERE 판매일자 BETWEEN ? AND ? -- 조회 구간 최대 1주일(인덱스 있음)

GROUP BY 부서코드

) A, 부서 B -- 수백 건 미만의 부서코드

WHERE A.부서코드 = B.부서코드

AND B.사용여부 = ‘Y’ -- 현재 시점에 사용하는 부서코드

ORDER BY B.부서명, A.부서코드

조회 구간이 한달 이상이라면 대규모의 집계 처리를 의미하므로 두 테이블 모두 인덱스를 타는 것보다는 타지 않는 것이 더 선능에 좋을 것.

Group by 절의 어떠한 형식이나 어떠한 조건에 따라서 성능 개선의 방법은 다양하게 다를 수 있다.

2번째 내용들은 이해를 하기보다는 정리 수준이라 쿼리 개발 때 참고해서 설계하는 것이 좋을 듯 싶다.

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Process / Session 모니터링 (0)	2016.01.17
[펌][ORACLE] ORA-에러 (0)	2015.12.22
[Oracle] Index monitoring (0)	2015.12.14
[Oracle] Partition Local Index 테스트 (0)	2015.12.02
[ORACLE] ASM 에 Datafile 추가 (1)	2015.11.25

몇일 전 프로세스 이슈인 줄 알고 찾아보면서,

하루에 한번씩 프로세스 현황을 각 DB 마다 수집 하여

통계가 필요할 것 같아 스크립트를 작성해 보았다.

대충은 아니지만 후다닥 만든 부분 이라 앞으로 테스트로 수집하면서

필요하거나 부족한 부분을 보충하면서 완성해야 될 것 같다.

대략 흐름은 아래와 같다.

1. 모니터링을 관장할 DB에서 각 수집을 해야할 DB들의 Link를 생성

2. DB Link를 통해 매일 오전 7시에 각 DB 의 session과 Process 를 수집

(Scheduler 를 이용)

3. 수집 대상은 session / process / instance / host 등이 된다.

(RAC 장비들이 다수이기 때문에 gv$를 이용하여 진행)

스크립트는 아래와 같다

--DB Link생성

CREATE PUBLIC DATABASE LINK DBA_LINK

CONNECT TO SYSTEM

IDENTIFIED BY 비밀번호

USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DBIP)(PORT = 포트)))(CONNECT_DATA = (SERVICE_NAME = DB서비스명)))';

--정보를 저장할 Table 생성

create table chk_session

(

l_date date default sysdate not null,

l_instance nvarchar2(20),

l_hostname nvarchar2(20),

ss_max number default 0,

ss_current number default 0,

ss_limit number default 0,

ss_active number default 0,

ss_inactive number default 0,

ps_max number default 0,

ps_current number default 0,

ps_limit number default 0

);

--procedure 생성

create or replace procedure SYS.proc_chk_session

is

begin

insert into chk_session

select

sysdate as rs_date,

b.instance_name AS RS_INSTANCE,

b.host_name AS RS_HOSTNAME,

MAX(case when resource_name='sessions' then MAX_UTILIZATION end) as SESSION_MAX,

MAX(case when resource_name='sessions' then CURRENT_UTILIZATION end) as SESSION_CURRENT,

MAX(case when resource_name='sessions' then LIMIT_VALUE end) as SESSION_LIMIT,

MAX((SELECT COUNT(*) FROM GV$SESSION@DBA_LINK C WHERE C.STATUS='ACTIVE' and b.inst_id=c.inst_id)) AS SESSION_CURRENT_ACTIVE,

MAX((SELECT COUNT(*) FROM GV$SESSION@DBA_LINK C WHERE C.STATUS='INACTIVE' and b.inst_id=c.inst_id)) AS SESSION_CURRENT_INACTIVE,

MAX(case when resource_name='processes' then MAX_UTILIZATION end) as PROCESS_MAX,

MAX(case when resource_name='processes' then CURRENT_UTILIZATION end) as PROCESS_CURRENT,

MAX(case when resource_name='processes' then LIMIT_VALUE end) as PROCESS_LIMIT

from gv$resource_limit@DBA_LINK a, gv$instance@DBA_LINK b

where a.resource_name in ('processes', 'sessions')

and a.inst_id = b.inst_id

GROUP BY sysdate, b.instance_name, b.host_name;

end;

/

--scheduler 생성

begin

SYS.dbms_scheduler.create_job

(job_name => 'check_session_db'

,start_date => TO_TIMESTAMP_TZ('2016/01/16 07:00:00','yyyy/mm/dd hh24:mi:ss')

,job_class => 'DEFAULT_JOB_CLASS'

,JOB_TYPE => 'PLSQL_BLOCK'

,repeat_interval => 'FREQ=DAILY; INTERVAL=1'

,job_action => 'BEGIN PROC_CHK_SESSION; END;'

);

end;

/

--scheduler 활성화 및 테스트 실행

exec sys.dbms_scheduler.enable('check_session_db');

exec sys.dbms_scheduler.run_job('check_session_db');

정말 허접해 보이기는 하지만 나는 이것도 내 경험이라 생각해 본다.

수정 보완점은 아래와 같다.

1. system 계정으로 진행했기 때문에 보안상 문제가 될 소지가 있다. 그래서 이 부분을 각 권한을 따로 부여하여 진행

2. 성능 부분을 체크 안했기 때문에 플랜을 확인하여 최소한의 리소스를 사용

이렇게 하나하나 쿼리를 작성하는 것도 좋을 듯 하다.

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[튜닝가이드] 인덱스 사용 여부 관련 생각 + [펌] (0)	2016.10.06
[펌][ORACLE] ORA-에러 (0)	2015.12.22
[Oracle] Index monitoring (0)	2015.12.14
[Oracle] Partition Local Index 테스트 (0)	2015.12.02
[ORACLE] ASM 에 Datafile 추가 (1)	2015.11.25

▶ 발생원인

▶ 확인/처리

모든 에러메세지는 SM홈피 '참고 -> Oracle_Error'를 에러코드 제목/원인/해결방법 참고

# CTRL-F 를 통해서 ORA-XXX 을 해서 찾을 것

- 담당자 : DBA

(1) ORA-00604: error occurred at recursive SQL level 2

- 원인 : worksheet를 사용할 경우 발생할 수 있음. server process가 대용량 데이터를

처리할시 child cursor과 경합을 벌일 경우 , 다른 프로세스가 같은 child

cursor를 요구할 경우 발생할 수 있음. SQL 수행 중 에러가 발생한 것으로 큰 문제는

없음

- 조치방법 : SQL 문장 실행중 발생한 것이며 서비스 문제 없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(2) ORA-01013: user requested cancel of current operation (현 조작의 취소가 요구되었

습니다)

- 원인 : DB에서 작업중이던 사용자가 수행하던 SQL문을 ctrl+C 등으로 강제 종료시켰을

경우 발생할 수 있음

- 조치방법 : 유저가 조작을 취소했을 경우 발생하므로 서비스 문제없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 20회이상 발생시 담당자에게 연락

@@

(3) ORA-1146 signalled during: ALTER TABLESPACE "##" BEGIN BACKUP

- 원인 : Online DB Backup이 시작되었을 경우 발생할 수 있으며, DB의 Tablespace가 이미

Backup mode로 되어 있을 경우 Begin backup을 재시도하려할 때 발생할 수 있음.

9i new feature인 temporary tablespace일 경우 백업의 대상이 아니지만 백업을

시도하려고 했을 경우 발생할 수 있음

- 조치방법 : online DB backup이 일어났을 경우 발생하며 서비스 문제 없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(4) ORA-1595 signalled during: alter rollback segment RBS01 shrink to ###

- 원인 : Rollback segment를 shrink 하려고 시도하였으나, 사용중이거나 하는 이유로 인해

shrink에 실패했을 경우 발생되는 에러코드

- 조치방법 : rollback segment를 조작하는 명령어 실행중 명령이 실행할 수 없을 경우 발생하는

에러코드, 데이터 손실 및 서비스 문제없음,

(주간)(야간) 연락 불필요

(5) ORA-02050: transaction 34.66.192307 rolled back, some remote DBs may be in-doubt

- 원인 : 현재 crm_db1서버에서만 이와 같은 에러가 발생하고 있으며, 근본적인 원인을 파악하는 중

2pc와 관련된 application 에러로 판단됨

- 조치방법 : 에러코드에 대한 분석/조치중이며, 서비스에 영향 없으며 연락 불필요

@@

(6) ORA-3217 signalled during: alter tablespace TEMP2 coalesce

- 원인 : DB에서 Tablespace에 대해 coalesce를 수행하는 script가 cron에 의해 자동

실행되고 있으나,대상 tablespace가 현재 다른 session에 의해 사용중이거나 Temporary

tablespace 형식의 TEMP는 coalesce될 수 없으므로 이와 같은 에러메세지가 발생될 수 있음.

- 조치방법 : temporary tablespace에 대한 조작명령어가 실행되지 못했을 경우 발생하는 에러코드,

(주간)(야간) 연락 불필요

@@

(7) ORA-07445: exception encountered: core dump [kcbzps()+688] [SIGSEGV]

[Address not mapped to object] [0] [] []

- 원인 : OS로부터 fatal signal을 받았을 때, 오라클 서버 프로세스가 일으키는 에러코드

- 조치방법 : 여러 조건에서 발생할 수 있음, 발생후 즉각적으로 dba에 알릴 필요는

없으나 서비스 체크는 필요함, crmdb, crmmk db에서 발생하고 있음,

infomail DB에서도 자주 발생하고 있으나 infomail DB의 문제일 경우

바로 서비스 체크를 할 것(db instance가 terminate될 수 있음)

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(8) ORACLE lock이 발견되었습니다. 확인 후 조치 바랍니다.

- 원인 : SQL 수행 중 다른 세션이 사용하고 있는 자원을 Update/delete 등을 하려고

시도하는 경우 발생될 수 있음. 한번에 수백건이 나오기도 하지만,

횟수는 중요하지 않으며 얼마나 오래동안 지속되는지에 따라서 판단해야 함.

- 조치방법 : lock으로 인해서 다른 작업이 멈추어 있는 경우이며 일반적으로

lock이 생기고 나서 곧 풀리지만, 오래지속될 경우 deadlock 이 되어 모든

DML이 rollback 될 수도 있음. 한번에 수백건의 lock이 발생되더라도

10분 이상 지속해서 발생하지 않으면 연락불필요. 10분 이상 지속시에는

deadlock으로 될 수 있으므로 바로 DBA에게 연락 요망

(주간) 발생시 즉시 담당자에게 연락 (야간) 10분 이상 계속 발생시 담당자에게 연락

@@

(9) ORA-00600: internal error code, arguments: [17182]

- 원인 : Oracle heap memory가 corruption되었을 경우 발생함

- 조치방법 : 현재 crm_db1에서 자주 발생하고 있음, memory corruption 으로 인해서

그 내부 문제점 원인을 찾기 어려움, 한국 오라클 기술지원 필요

crmdb, crmmk 에서 자주 발생하고 있음,

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

@@

(10) ORA-00600: internal error code, arguments: [729], [10992], [space leak],

- 원인 : User Global Area 에서 공간부족으로 발생하며,(memory housekeeping problem)

데이터 손실 및 서비스 문제는 없음.

- 조치방법 : 데이터 손실 및 서비스 문제는 없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(11) ORA-00600: 내부 오류 코드, 인수 : [4400], [48], [], [], [], [], [], []

- 원인 : 현재 crm_db1,2에서 발생하고 있으며, 사용자 트랜잭션이 logoff될 경우에 발생할 수 있음

ORA-02050 분산 환경에러로 인해서 발생하는 에러코드로 보고도어 있음, 데이터 손실

및 서비스 영향없음

- 조치방법 : 서비스 영향없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(12) ORA-03113: end-of-file on communication channel(통신 채널에 EOF 가 있습니다)

- 원인 : 현재 crm_db1,2에서 발생하고 있으며, 접속했던 사용자가 불분명하게 DB에 접속이 끊길경우

발생할 수 있음. Java Function Call 문제로 발생할 수 있음. 데이터 손실 및 서비스 영향 없음

- 조치방법 : 서비스 영향없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(13) ORA-1237 signalled during: alter database datafile 'xxx

- 원인 : DBA의 명령어 실행으로 발생하며, 데이터파일을 resize 하는중 발생할 수 있음

- 조치방법 : DBA의 명령어 실행으로 발생하며, 명령어 실행시 에러로 DBA에게 메세지가 명령어창에

확인 되므로 연락 불필요.

(14) ORA-000060: Deadlock detected. More info in file XXX

- 원인 : lock이 오래 지속될 경우, DML문에 의해서 deadlock이 발생할 경우 발생함.

오라클은 deadlock이 발생하면 모든 트랜잭션을 rollback 시킴

- 조치방법 : 오라클은 deadlock이 발생하면 모든 트랜잭션을 rollback 시키므로

배치작업에 문제가 생길 수 있음. 배치작업이 재대로 작동하는지 확인요망

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락.

(15) ORA-00028: your session has been killed

- 원인 : DBA에 의해서 oracle 명령에 의해서 유저가 kill 되었을 경우 나타나는 메세지.

또는 crmdb1에서 발생하는 ORA-02050 transaction XXX rolled back 에 의해서

발생할 수 있음.

(주간) 발생시 즉시 담당자에게 연락 (야간) 20회 이상 발생시 담당자에게 연락

(16) ORA-00600: internal error code, arguments: [1157]

- 원인 : oracle 메모리 에러(보고된 사항 없음)

- 조치방법 : 데이터 손실 및 서비스 이상은 없음.

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(17) ORA-00600: internal error code, arguments: [12235]

- 원인 : oracle의 defunt process 가 생겼을 경우 발생하는 메세지

heavily loaded system 에서 server process의 기동이 느릴 경우 발생할 수 있음

버그로 보고되어 있음 Bug# 3616023

- 조치방법 : defunt process가 발생했을 경우 나타날 수 있음,

데이터 손실 및 서비스 이상은 없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(18) ORA-00600: internal error code, arguments: [12333]

- 원인 : Oracle Version과는 관계없음. Oracle Server와 Client상의 network 이상으로

recognize하지 않은 패킷을 클라이언트에서 받았을 겨우 발생할 수 있음,

유저 세션이 비정상적으로 종료되었을 경우 발생할 수 있음

- 조치방법 : application client와 Oracle Server process간에 패킷오류가 있거나

비정상적으로 세션이 끊어졌을 경우 발생할 수 있으며, 데이터 손실 및 서비스에는 이상없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(19) ORA-00600: internal error code, arguments: [2141]

- 원인 : 데이터파일을 이동/추가시키려 하나 permission이 없을 경우 발생할 수 있음.

이미 mount/open된 instance에 또다시 같은 instance를 띄울 경우 발생할 수 있음

- 조치방법 : DBA의 명령실행에 의해서 발생할 수 있음, 데이터 손실 및 서비스 이상없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(20) ORA-00600: internal error code, arguments: [4411]

- 원인 : 오랫동안 수행되는 배치작업에서 savepoint 문제로 일어날 수 있음

oracle version 8.1.7.2, 9.0.1.2 에서 발생할 수 있음. 버그로 보고되어 있음

8.1.7.3 ,9.0.1.3 ,9.2.0.1 version에서 패치되어있음

- 조치방법 : batch작업이 재대로 돌아가고 있는지 확인 필요

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(21) ORA-00600: 내부 오류 코드, 인수 : [12403]

- 원인 : bitmap index를 사용할 경우 또는 row를 fetch 도중 발생할 수 있음.

oracle version 7.3 이상 8.1.6 이하에서 bitmap index를 사용할 경우 버그로 보고되어 있음

8.1.7 version에서 패치되었음

- 조치방법 : 8.1.6 이하 version의 oracle 에서 bitmap index를 사용했을 경우 발생하므로

개발팀에게 연락요망, 데이터 손실 및 서비스 이상없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(22) ORA-00600: 내부 오류 코드, 인수 : [15711], [4], [0x63AA35610],

- 원인 : parallel로 create 명령실행시 발생할 수 있음, 7.3.3 version에서 패치되었음

- 조치방법 : oracle version 확인 , noparallel로 명령 실행

(23) ORA-00600: 내부 오류 코드, 인수 : [18209], [3809926384], [3897286416],

- 원인 : distributed transactions 환경과 관련 있으며, 8.1.7.0 , 9.0 version

에서 버그로 보고되어 있음 bug 1131214.

- 조치방법 : 8.1.7.4 또는 9.2로 patch 할 것, 서비스 이상없음,

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(24) ORA-00600: 내부 오류 코드, 인수 : [4412], [3889941488], [0],

- 원인 : package 생성 명령시 DB link를 사용할 경우 발생할 수 있음, 9.2.0.4 이하

version에서 버그로 보고되어 있음

- 조치방법 : 9.2.0.5로 패치, 서비스 이상없음,

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(25) ORA-00600: 내부 오류 코드, 인수 : [4886], [], [], [], [], [], [], []

- 원인 : procedure 생성 명령시 DB link를 사용할 경우 컴파일 하는동안

remote server를 잃어버려 db link가 비정상적인 상태가 되어 발생할 수 있음

, 9.2.0.4 이하 version에서 버그로 보고되어 있음

- 조치방법 : 서비스 이상없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(26) ORA-01013: 현 조작의 취소가 요구되었습니다

- 원인 : 사용자가 CTRL-C 또는 다른 방법으로 연산을 중지했을 경우 발생할 수 있음

- 조치방법 : 사용자에 의한 연산중지로 인해 발행하며 데이터 손실 및 서비스 영향 없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(27) ORA-01017: invalid username/password; logon denied

- 원인 : 사용자가 password를 틀려서 login하지 못할 경우 발생

- 조치방법 : 정확한 username password 작성

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(28) ORA-01110: data file XXX

- 원인 : 다른 메세지와 함께 나오는 에러코드

- 조치방법 : 함께 생긴 다른 에러코드를 볼것

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(29) ORA-01114: 208 파일에 블록을 기록하는데 IO 오류입니다

- 원인 : db blck corruption 이 일어났을 경우 발생할 수 있음

- 조치방법 : critical 한 상태일 수 있음. DBA 즉시 Call

(30) ORA-01204: file number is 132 rather than 126 - wrong file

- 원인 : 파일 헤더의 파일 번호가 정확하지 않을 경우 발생, 정확한 데이터파일을

restore해야 함, 복구 수행이 필요

- 조치방법 : critical한 상태일 수 있음. DBA 즉시 call 복구 수행

(31) ORA-01259: unable to delete datafile XXX

- 원인 : 'DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES'

tablespace drop중 파일을 삭제할 수 없을 경우 발생하는 에러코드.

또는 file을 옮기는 중에 발생할 수 있음

- 조치방법 : O/S 에서 file delete 가 보호되어 있을 경우 또는 삭제가 불가능

한 경우 이므로 DBA가 수동으로 삭제하여야 함

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(32) ORA-04031: unable to allocate 4128 bytes of shared memory

- 원인 : shared pool 에 연속적인 메모리 공간을 필요료 하는 package,

function , procedure를 할당할 수 없을 경우 발생

- 조치방법 : dbms_shared_pool.keep 을 쓰거나 shared pool 를 flush,

서비스에 critical 한 상황일 수 있음. DBA 즉시 call

(33) ORA-01595: error freeing extent (2) of rollback segment (5))

- 원인 : Oracle9i의 새로운 기능인 Undo tablespace의 경우에 해당함.

undo tablespace는 rollback segment를 자동으로 관리하며, 시스템 startup초기에

8개의 rollback segment가 있지만 부족할 경우 자동으로 늘어남.

만약 배치작업으로 늘어났다가 필요가 없을 경우 smon이 rollback segment를 자동으로

shrink하고 없애지만 자동으로 shrink할 경우라도 rollback segment를 사용중인

경우 등으로 shrink가 안될 경우 ORA-01595 메세지를 일으킴.

안전하게 무시해도 되는 사항임. oracle version 10에서는 fix되었음.

- 조치방법 : 조치방법 없음, 연락할 필요 없음

(34) ORA-1122 signalled during: alter tablespace XXX offline...

- 원인 : 아래 3개의 에러코드와 함게 발생할 수 있음. dbv를 실행중 발생가능 있음,

tablespace를 offline 하는중 발생할 수 있음

.ORA-1110 database file %s failed verification check

.ORA-1122 data file %s: '%s'

.ORA-1207 file is more recent than control file - old control file

control file 정보와 datafile의 정보가 일치하지 않을 경우 발생

- 조치방법 : 즉시 DBA Call

(35) ORA-1142 signalled during: ALTER TABLESPACE "RBS" END BACKUP...

- 원인 : online backup을 시도하려고 하였으나 backup을 수행할 file을 찾을 수 없을

경우 발생할 수 있음. end backup 을 하려고 했으나 backup mode가 아니라서

end backup을 할 수 없을 경우 발생할 수 있음

-조치방법 : backup mode로 되어 있는 tablespace가 있는지, 백업이 재대로 되었는지

체크 요망

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락,

(36) ORA-1516 signalled during: alter database datafile '/db03/orarnd/oradata/rnd3...

- 원인 : DBA가 명령어 실행중 발생 가능.

데이터파일 추가 resize 또는 경로이동, offline, 이름변경시 발생할 수 있음

- 조치방법 : 올바른 명령어 실행 , crm_db2의 경우 crm_db1의 sync작업을 하며

crm_db2의 datafile resize를 할 경우 발생할 수 있음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(37) 감시 대상 프로세스의 DOWN

감시대상 프로세스(/XXXX/tnslsnr LISTENER -inherit)가 DOWN되었습니다.

감시대상 프로세스(ora_arc0_XXX)가 DOWN되었습니다. 프로세스를 확인하여 주시기 바랍니다

감시대상 프로세스(ora_ckpt_XXX)가 DOWN되었습니다. 프로세스를 확인하여 주시기 바랍니다

감시대상 프로세스(ora_dbwr_XXX)가 DOWN되었습니다. 프로세스를 확인하여 주시기 바랍니다

감시대상 프로세스(ora_lgwr_XXX)가 DOWN되었습니다. 프로세스를 확인하여 주시기 바랍니다

감시대상 프로세스(ora_pmon_XXX)가 DOWN되었습니다. 프로세스를 확인하여 주시기 바랍니다

감시대상 프로세스(ora_reco_XXX)가 DOWN되었습니다. 프로세스를 확인하여 주시기 바랍니다

감시대상 프로세스(ora_smon_XXX)가 DOWN되었습니다. 프로세스를 확인하여 주시기 바랍니다

감시대상 프로세스(ora_snp0_XXX)가 DOWN되었습니다. 프로세스를 확인하여 주시기 바랍니다

오라클[XXX] 체크중 장애가 발생하였습니다:ORA-01034: ORACLE not available

오라클[XXX] 체크중 장애가 발생하였습니다:ORA-12541: TNS:no listener

- 원인 : 작업중에 DB는 shutdown 했으면 발생할 수 있음,

작업중이 아니면 DB서비스가 down된 것이므로 critical한 인시던트임.

아래와 같은 메세지가 나오면

- 조치방법 : 즉시 DBA를 call할 것 (주/야간)

(37) ORA-1537 signalled during: alter tablespace XXX

- 원인 : DBA의 명령어 실행중 이미 있는 파일에 데이터파일을 추가하려고 할 경우 발생할 수 있음

- 조치방법 : 다른 파일 명으로 데이터파일을 추가

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(38) ORA-12203: TNS:unable to connect to destination

- 원인 : Oracle network 전송 문제로 인해서 접속이 불가능 할 경우, 또는 네트워크 설정이

잘못되었을 경우 발생할 수 있음

- 조치방법 : network 설정 올바른 설정

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(39) ORA-1644 signalled during: alter tablespace TS_DPACM015 read only...

- 원인 : 이미 read only tablespace를 또 다시 read only로 할 경우 발생하는 에러코드

- 조치방법 : 서비스 이상없음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(40) 파일시스템(/oracle )의 가용공간이 위험수준에 도달하였습니다(사용율:100%, 가용:5045KBytes)

- 원인 : DB file system 이 full 이 되었을 경우

- 조치방법 : DBA 즉시 call

(41) ORA-1650: unable to extend rollback segment XXX by 19200 in tablespace RBS

- 원인 : rollback segment에 큰 용량의 initial , next extent size 를 가지는 segment를

생성 또는 데이터를 insert하려 할 경우 발생할 수 있음

- 조치방법 : rollback segment를 일반 permanent tablespace에 만드는 경우에는

rollback segment를 shrink 시키고 coalesce를 시킨다.

undo tablespace를 사용하는 경우에는 이러한 메세지가 발생할 경우,

undo tablespace의 size를 늘려주어야 한다.

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(42) ORA-1652: unable to extend temp segment by 512 in tablespace

- 원인 : drop table, create index, create table 추가시, 삭제 및 생성 할tablespace에

일시적으로 temporary segment를 만들려 하지만 size가 작을 경우 발생하는 메세지

- 조치방법 : segment를 생성및 삭제하려는 tablespace에 대해서 size를 늘려줄 것

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(43) ORA-1653: unable to extend table USPODS.TB_PPCM019_BK by 64 in

- 원인 : segment의 initial, next 또는 uniform size 값보다 큰 데이터가 들어갈 경우

발생할 수 있음

- 조치방법 : 해당 tablespace size를 늘려줌

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(44) ORA-01662 tablespace 'TEMP' is non-empty and cannot be made temporary

ORA-01662 signalled during: alter tablespace temp temporary...

- 원인 : temporary tablespace에 남아 있는 sort작업이 있지만 , 이 temporary tablespace를

변경하거나 drop 하려 할 경우 발생할 수 있음

- 조치방법: 새로운 temporary tablespace를 만들어주고, default tempoaray tablespace를

새로운 temporary tablespace에 지정. v$tempseg_usage 를 조회해서 기존 temporary

tablespace 에서 sort 작업이 없어진 것을 확인하고 drop 할 것

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(45) ORA-1688: unable to extend table XXX

- 원인 : tablespace에 데이터를 넣으려 하나 extent 가 부족할 경우 발생

- 조치방법 : 새로운 데이터파일 추가 또는 데이터파일 resize를 하여 문제 해결

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(46) EXTENT error

ORACLE[XXX] Segment next extent 발생시 에러를 발생할 수 있는 Segment가 발견되었습니다

Oracle xxx DB Ext.Fail:XXXX - call DBA

- 원인 : 데이터를 입력하려고 할 경우, extent를 할당 받은 상태에서 데이터를 입력해야 하며

부족할 경우 새로운 extent를 할당받아야 데이터를 추가할 수 있음. 데이터파일의 공간 부족으로

인해서 extent를 할당 받을 수 없을 경우 발생할 수 있음

- 조치방법 : 데이터파일의 크기를 중가시킬 것

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(47) ORA-01555 caused by SQL statement below (SCN: 0x04e5.a634f3be):

ORA-01555 snapshot too old

- 원인 : rollback segment에 기록된 사항을 다른 writer가 사용하거나 변경했을 경우

이를 다시 조회하려 할 경우 비일관성으로 인해 발생하는 에러메세지.

배치작업의 일부가 rollback될 소지가 있음.

- 조치방법 : Oracle9i 에서 undo_management=auto로 되어있을 경우 undo_retention값을

즐려주어야 함. 또는 rollback segment tablespace의 크기를 증가 시킴

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(46) ORA-27063: skgfospo: 읽기/쓰기 바이트의 수가 틀립니다

- 원인 : OS 의 file size가 제한되어 있을 경우 데이터파일을 resize할 경우 발생할 수

있음. crm_db2의 경우 발생가능

일반적으로 limit된 값이 지정되어 있을 경우 발생할 수 있음.

OS 의 NFS를 사용할 경우 mount가 재대로 안되어 있을 경우 발생 가능.

- 조치방법 : DBA 명령시 resize 명령 및 OS file 제한을 참고.

autoextend가 있는 file에 대해서 에러를 일으킬 수 있으니 autoextend off

할 것

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(47) ORA-30021 signalled during: alter tablespace undotbs coalesce...

- 원인 : oracle9i 새로운 기능인 undo tablespace 를 사용할 경우 coalesce를 하게 되면

에러 발생

- 조치방법 : undo tablespace 를 사용시 coalesce하지 말것

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(48) ORA-3297 signalled during: alter database datafile

- 원인 : 데이터파일을 resize하서 줄이려 하지만 줄이지 못할 경우 발생

- 조치방법 : DBA의 명령어 실행중 발생할 수 있음

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(49) ORA-959 signalled during:

- 원인 : tablespace를 변경하거나 참고하는 작업을 할 경우, 실제 테이블 스페이스가 없을 경우

발생할 수 있음

- 조치방법 : dba_tablespaces를 확인후 정확한 tablespace 를 지정할 것

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회 이상 발생시 담당자에게 연락

(50) ORA-02068: following severe error from XXX

- 원인 : DB LINK를 통해 다른 서버에 접속시 접속 불능일 경우 확인

- 조치방법 : db link 와 username, password 확인 후 처리 , network설정 확인

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(51) ORA-1632: max # extents 121 reached in index XXX

- 원인 : 인덱스 생성시 설정했던 storage 절의 maxextents값에 현재의 extents값이

도달했을 경우 생길 수 있는 에러코드

- 조치방법 : maxextents 값을 늘려줄 것.

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(52) 오라클[XXX] SEGMENT(XXX)에 enqueue가 발생하였습니다.

- 원인 : oracle enqueue는 oracle lock과 동일한 의미임. 공유되어 있는 row, table 등에

대해서 DML문을 실행하여 exclusive mode가 되어 있는 segment를 다른 세션에서

DML 문을 하려 할 경우 발생할 수 있음.

- 조치방법 : enqueue도 일종의 lock 이므로 지속적으로 일어날 경우 deadlock으로

될 수 있음, 한번에 수백건의 enqueue이 발생되더라도 10분 이상 지속해서 발생하지

않으면 연락불필요. 10분 이상 지속시에는 deadlock으로 될 수 있으므로

바로 DBA에게 연락 요망

(주간) 발생시 즉시 담당자에게 연락 (야간) 10분 이상 계속 발생시 담당자에게 연락

(53) Oracle[XXX] objects that will have trouble throwing extents.

- 원인 : extent fail이 난 것임

- 조치방법 : 바로 DBA를 call 할 것, oracle background_dump_dest 위치에서

alert log의 extent fail에 찾아보고 처리할 것.

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(54) ORA-00600: internal error code, arguments: [547]

- 원인 : alter system flush shared_pool 명령을 했을 경우 발생할 수 있음

정확한 원인에 대한 보고는 없음

- 조치방법 :

(주간) 발생시 즉시 담당자에게 연락 (야간) 5회이상 발생시 즉시 담당자에게 연락

(55) OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME NEXT_EXTENT

- 원인 : extent fail이 난 것임

- 조치사항 : extent check script를 수행하거나, oracle background_dump_dest에서

alert log를 확인해서 extent fail난 segment를 찾아서 조치할 것

(주간) 발생시 즉시 담당자에게 연락 (야간) 발생시 즉시 담당자에게 연락

(56) 이외의 에러코드

- 무조건 DBA call

[출처] oracle error |작성자 작은새

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[튜닝가이드] 인덱스 사용 여부 관련 생각 + [펌] (0)	2016.10.06
[Oracle] Process / Session 모니터링 (0)	2016.01.17
[Oracle] Index monitoring (0)	2015.12.14
[Oracle] Partition Local Index 테스트 (0)	2015.12.02
[ORACLE] ASM 에 Datafile 추가 (1)	2015.11.25

DB 점검 도중, 약 60만건의 테이블에(담당자가 메인테이블이라고..) index가 20개 이상이 걸려 있는 것을 확인했다.

그리고 대부분 index들이 결합인덱스보다는 단일인덱스 인 것을 확인하고,

담당자에게 모두 필요한 index라고 물어보니

필요에 의해서 해당 테이블과 연관된 담당자들에 의해서 생성한 거라고,

자기도 잘 모른다고 하더군...

흠...내가 소속 DBA가 아닌 파견도..아닌 협력업체...DBA이다 보니..

이러지도 저러지도 못하는 상황..

보고는 하였지만..그래도 나는 DBA의 지부심이 있기에!!!내가 주체가 되어...제안....

"우리 INDEX 모니터링 걸어서 불필요한 거는 제거하고,

필요한 거는 새로 걸자고 제안....

index monitoring 관련 쿼리를 확인해 봤다.

1. index monitoring 여부 확인

-- 해당 user 라면 아래 쿼리를 이용하여 확인이 가능

SELECT table_name,
       index_name,
       monitoring
FROM   v$object_usage
WHERE table_name = UPPER('&1')
AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

--나는 DBA다!!!!

col owner for a10
col index_name for a20
col table_name for a20

SELECT U.NAME OWNER,
IO.NAME INDEX_NAME,
T.NAME TABLE_NAME,
DECODE(BITAND(I.FLAGS, 65536), 0, 'NO', 'YES') MORNITORING,
DECODE(BITAND(OU.FLAGS, 1), 0, 'NO', 'YES') USED,
OU.START_MONITORING,
OU.END_MONITORING
FROM SYS.USER$ U,
SYS.OBJ$ IO,
SYS.OBJ$ T,
SYS.IND$ I,
SYS.OBJECT_USAGE OU
WHERE I.OBJ# = OU.OBJ#
AND IO.OBJ# = OU.OBJ#
AND T.OBJ# = I.BO#
AND U.USER# = IO.OWNER#;

-- v$object_usage는 자신의 index 만 확인 가능하기에 이 점 유의!!

2. index에 모니터링이 걸려 있지 않으면 아래 쿼리를 이용하여 명령문을 만들 수 있다.

-- owner 와 table_name 을 입력해야 함

SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" MONITORING USAGE;'

FROM dba_indexes i

WHERE owner = UPPER('&1')

AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

3. 다시 체크한 결과 (위의 index monitoring 으로 조회)

4. index에 모니터링이 걸려 있는 것에 대해 해제 하는 명령어

SELECT 'ALTER INDEX "' || i.owner || '"."' || i.index_name || '" NOMONITORING USAGE;'

FROM dba_indexes i

WHERE owner = UPPER('&1')

AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'));

이렇게 하여 일단 체크를 진행하기 시작했다.

여기서 반은 걷어 냈으면 하는 바램이...

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Process / Session 모니터링 (0)	2016.01.17
[펌][ORACLE] ORA-에러 (0)	2015.12.22
[Oracle] Partition Local Index 테스트 (0)	2015.12.02
[ORACLE] ASM 에 Datafile 추가 (1)	2015.11.25
[Oracle] OWI Latch & Lock 정리 (0)	2015.11.24

아흐..

지난 주 스터디 하면서 자신있게 Partition Table 과 관련하여 발표 도중

Partition Local index 에서 맨붕이 와버렸다..

그래도 내가 알려주는거에 대한 책임감(??변명..) 때문에 다음에 다시 발표하겠다 한 후

이제서야 테스트 하면서 결과에 대해서 공유해 본다.

1. 파티션 테이블 생성

SQL> create table test_a

(col1 number,

col2 number,

col3 varchar2(20))

Partition by range (col1, col2)

(Partition part_test_a_1 values less than (10, 100),

Partition part_test_a_2 values less than (20, 200) ,

Partition part_test_a_3 values less than (30, 300) ,

Partition part_test_a_4 values less than (40, 400) );

2. Local Index 생성

(여기서 맨붕이 왔다. 내가 테스트한 부분은 아래와 같이 생성하였으며 이렇게 생성해야만 하는걸로 알고 있었다..)

SQL> create Index ix_test_a on test_a(col1, col2) Local

(Partition in_test_a_1 ,

Partition in_test_a_2 ,

Partition in_test_a_3 ,

Partition in_test_a_4 );

조회해 보면 아래와 같다

하지만 책에는 아래와 같이 나와 있다.

SQL> create index ix_test_a on test_a(col1, col2) local;

확인해 보면 partition_name 만 틀릴 뿐 나머지는 동일하다.

partition name 은 어떻게 되는걸까?

Table를 다시 생성해 본다

SQL> create table test_c

(col1 number,

col2 number,

col3 varchar2(20))

Partition by range (col1, col2)

(Partition test_c_1 values less than (10, 100),

Partition test_c_2 values less than (20, 200) ,

Partition test_c_3 values less than (30, 300) ,

Partition test_c_4 values less than (40, 400) );

SQL> create index ix_test_c on test_c(col1, col2) local;

Index Partition_Name은 명시해 줄 경우 명시된 partition 이름으로 가며,

명시안하는 경우 Partition Table의 partition name을 따라가는 것을 확인할 수 있다.

왜 이렇게 쉬운 부분에서 멘붕이 오냐고 질책할 수 있지만...내 잘못입니다...ㅠㅠ

테스트 꼭 해보고 책도 한번씩 쿼리 다 작성해서 결과확인 후 진행 하도록 하겠습니다.

죄송합니다.ㅠ

한가지 더 테스트로 Local index 를 명시하되, 파티션 table과 동일한 갯수로 생성을 안하면 어떻게 되는걸까,

SQL> create Index ix_test_a on test_a(col1, col2) Local

(Partition in_test_a_1 ,

Partition in_test_a_2 ,

Partition in_test_a_3);

우리가 책에서 배웠듯이 에러가 발생한다.

아래는 내가 어떤 자료를 보고 정리한 내용이다.

구분

적용 기준 및 고려 사항

비 파티션

- 파티션 키 칼럼이 조건절에 누락되면 여러 인덱스 파티션을 액세스해야 하므로 비효율적. 특히, OLTP 환경에서 성능에 미치는 영향이 크므로 비파티셔닝 전략이 유용할 수 있음

- NL Join 에서 파티션 키에 대한 넓은 범위검색 조건을 가진 Inner 테이블 액세스 용도로 인덱스 파티션이 사용된다면 효율적 -> 비파티션 인덱스 사용을 고려

- 파티션 인덱스를 이용하면 sort order by 대체 효과 상실. Sort 연산을 대체함으로써 부분범위 처리를 활용하고자 할 땐 비파티셔닝 전략이 유용

- 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므로 적용 시 주의

Global Prefixed

- 인덱스 경합 분산에 효과적

- 여러 Local 인덱스 파티션을 액세스하는 것이 비효율적일 때 대안으로 활용 가능

- 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므로 적용 시 주의

Local Prefixed

- 관리적 특면에서 유용 : 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때

- 이력성 데이터를 주로 관리하는 DB 환경에 효과적

- 파티션 키 칼럼이 “=” 조건으로 사용될 때 유용

- 파티셔닝 칼럼에 대한 검색 조건이 없으면, 인덱스 선두 칼럼이 조건절에 누락된 것이므로 정상적인 사용이 불가 (Index Full Scan 으로는 선택 가능)

- 파티션 키 칼럼(=인덱스 선두 칼럼)이 Like, Between, 부등호 같은 범위검색 조건일 때 불리

Local Non Prefixed

- 관리적 측면에서 유용 : 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때

- 이력성 데이터를 주로 관리하는 DB 환경에 효과적

- 파티션 키 칼럼이 조건절에 사용될 때 유용

- 파티셔닝 칼럼에 대한 검색 조건이 없으면 인덱스 파티션 전체를 액세스하는 비효율이 발생할 수 있으므로 주의

- 파티션 키 칼럼이 범위검색 조건으로 자주 사용된다면 Local Prefixed 보다 Local NonPrefixed가 유리. 그렇더라도 좁은 범위검색이어야 함

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[펌][ORACLE] ORA-에러 (0)	2015.12.22
[Oracle] Index monitoring (0)	2015.12.14
[ORACLE] ASM 에 Datafile 추가 (1)	2015.11.25
[Oracle] OWI Latch & Lock 정리 (0)	2015.11.24
[펌][Oracle] 대용량 자료전환 시 튜닝 포인트 - Hash join (0)	2015.11.19

사실 내가 관리하는 사이트 들은 모두 File system을 사용한다.

(개발/관리 업무만 하던 이전 직장에 있다가 DB로 넘어온 후 File system / Raw device / Cluster가 뭔지 정말 생소한 단어들에 엄청 맘 고생하면서 찾아보고...물어보기도 창피하고 직급은 있는데...정말 그때 생각하면 아직도 가슴이 찡하다..이게 도대체 뭐라고 ㅋㅋㅋ)

하지만 동료들 사이트 보면 간간히 ASM 이 포함되어 있어서 이레저레 한두번 보기만 해봤다.

물론 테스트 디비랑은 다 구축해 놨지만 정작 구축만 하고 활용은 안했다는..

각설하고 이번에 ASM 에 datafile 추가하는데 file system 처럼 하면 되는데,

갑자기 왜 file 명이 이래요..? 이리 물어보니.. 어버버 거려서 이참에

한번 정리해 본다.

ASM 에 Tablespace 추가

sql) CREATE TABLESPACE TEST DATAFILE SIZE 10M;

이렇게 하면 자동으로 datafile 명시 안해줘도 disk group 아래에 생성이 된다.

cf) File system 에서는 아래와 같이 명시적으로 추가해 줘야한다

sql) CREATE TABLESPACE TEST DATAFILE '/DATA/ORCL/test_data01.dbf' SIZE 10m;

혹시라도 아래와 같은 에러가 발생할 수 있다.

ERROR at line 1:

ORA-01119: error in creating database file '+DATA'

ORA-17502: ksfdcre:4 Failed to create file +DATA

ORA-15001: diskgroup "DATA" does not exist or is not mounted

그러면 아래와 같이 경로를 확인해 보자.

SQL> show parameter db_create_file_dest

Disk Group 위치도 확인해 보자

SQL> select group_number, name, state from v$asm_diskgroup;

SQL> select b.name group_name, a.name disk_name, a.header_status , a.state, a.free_mb from v$asm_disk a, v$asm_diskgroup b

where a.group_number = b.group_number;

이리 저리 봐도 DB_CREATE_FILE_DEST 가 잘못 설정되어 있는 것을 확인할 수 있다.

변경하자.

변경 후에 DB는 재기동 할 필요가 없다.

SQL> alter system set db_create_file_dest='+DATA1';

이후 다시 Tablespace 생성 하면 문제 없이 생성 되는 것을 확인할 수 있다.

이번에는 Add datafile 진행을 해 보자.

SQL> alter tablespace TEST add datafile '+DATA1/orcl/datafile/test2.dbf' size 10m;

이렇게 추가는 가능하지만, Data guard 등에서 문제가 생길 수 있다고 하기에..

(이 부분은 찾아서 올리도록 하겠습니다.........[정중히ㅠ])

가급적 자동으로 생성 되도록 유도하자.(ASM 에서 A가 Automatic 이니깐!! Storage Management )

SQL> ALTER TABLESPACE TEST ADD DATAFILE SIZE 10m;

아래를 보면 명시한 파일명을 명시한 경우와 명시하지 않은 경우가 있다.

가급적 우리는 명시하지 않도록 하고 자동으로 관리하도록 하자.

추가적으로 확인해야 할 부분들도 많고 하지만..

차근차근 진행을 한 후 공유 하도록 해야겠다.

참고 사이트 : http://dinggur.tistory.com/201

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Index monitoring (0)	2015.12.14
[Oracle] Partition Local Index 테스트 (0)	2015.12.02
[Oracle] OWI Latch & Lock 정리 (0)	2015.11.24
[펌][Oracle] 대용량 자료전환 시 튜닝 포인트 - Hash join (0)	2015.11.19
[Oracle] Merge 구문 1 (0)	2015.11.12

틈틈히 OWI 책을 보면서 공부하는데..

가장 기본이 되는 Latch 와 Lock 관련하여 정리하여 올려본다.

아는 내용도 있지만 그래도 다시 한번 익히는 좋은기회겸..

또 몰랐던 내용도 알 수 있는 거라 공부하면서 Word 로 정리 해봤다.

흠....이쁘게 정리할 수도 있지만...그냥 편하게...ㅎㅎ

복사해서 필요한것만 find 하도록...

래치와 락 (latch & lock) - OWI

By. Louis Kim

래치와 락의 존재 이유는 동시 작업으로 오라클의 자원을 보호하는 것

분류	래치(Latch)	락(Lock)
목적	하나의 목적을 수행함 : 메모리 구조에 대한 배타적인 접근을 위함 ( 오라클 9i 부터cache buffers chains latch 들은 읽기 전용시에 공유가 가능함	두 가지 목적을 수행함: 락 모드가 호환가능하면 다수의 프로세스가 동일한 리소스를 공유하는 것을 허용하며, 락 모드가 호환 가능하지 않으면 리소스에 대한 배타적인 접근만 허용함
사용범위	SGA 내부의 데이터 구조에만 적용. 메모리 오브젝트를 임시적으로 보호함. 단일 오퍼레이션으로 메모리 구조에 대한 접근 제어. 트랜잭션 단위가 아님	테이블, 데이터 블록 및 state object와 같은 오브젝트를 보호함. 데이터베이스의 데이터 또는 베타데이터 접근 제어. 트랜잭션 단위
획득방식	두 가지 모드로 요청이 가능 : willing-to-wait 또는 no-wait	6가지 모드로 요청이 가능 : null, row share, row exclusive, share, share row exclusive 또는 exclusive
범위	SGA내부에 정보가 존재하며, 로컬 인스턴스에서만 볼 수 있음 – 래치는 인스턴스 레벨로 작동	데이터베이스 내부에 정보가 존재하며, 모든 인스턴스에서 볼 수 있음 – 락은 데이터베이스 레벨에서 작동
복잡도	단순한 명령어를 사용하여 구현됨. 일반적으로 test-and-set, compare-and-swap 또는 단순한 CPU명령어. 구현이 쉬움	문맥 교환(context switch)를 포함한 일련의 명령어들을 사용하여 구현됨, 구현이 복잡함
지속기간	아주 짧은 순간만 지속됨 (microsecond 단위)	일정 시간 동안 지속됨 (트랜잭션 동안)
큐	프로세스가 래치 획득을 실패한 후 슬립(sleep) 상태로 들어갈 때, 해당 요청은 큐(queueu)로 관리되지 않으며, 요청한 순서대로 서비스되지 않음(latch wait list를 이용하여 큐 방식으로 사용되는 래치들은 예외임)	프로세스가 락 획득을 실패한 후, 해당 요청은 큐(queue)로 관리되며, 요청한 순서대로 서비스됨(no-wait 모드는 예외임)
데드락	래치는 데드락(deadlock)이 발생되지 않도록 구현됨	락은 큐(queue)방식을 사용하며, 데드락이 발생될 가능성이 높다. 데드락이 발생 될 때마다 트레이스 파일이 생김

Latch (래치)

- 가벼운 락(lightweight lock) 으로 분류 (아주 빠른 속도로 작동하게끔 구현된 경량화된 락)

- Shared pool 영역에 존재하는 일종의 메모리 구조체

- 매우 간단하고 작은 메모리영역을 사용하며, 래치를 획득(get)하고 해제(release)하는 작업들 또한 하드웨어 맞게 극도로 최적화 되어 있음

- 래치를 획득하고 해제하는 과정은 원자성(atomicity)이 보장

- 래치 획득의 순서를 보장하지 않음. 순서가 보장되어야 하는 작업은 락에서 진행

- 래치는 SGA 리소스를 보호하기 위함

- SQL문을 실행하고자 하는 프로세스는 SQL를 shared pool 의 library cache 영역에 올려야하는데, 이 때 heap 메모리 영역을 할당받기 위해 반드시 shared pool 래치를 획득 필요

- 하나의 프로세스만이 힙으로부터 청크(chunk)를 할당받을 수 있음

- select문의 경우 특정 데이터블록을 읽기 위해 데이터블록의 DBA와 Class에 해당하는 Hash chain에 접근하고자 하는 모든 프로세스는 반드시 Hash chain을 관장하는 cache buffers chains Latch를 획득해야 함

- DML를 실행하기 위해서는 PGA영역에 Redo 데이터를 생성 / Redo data를 Redo buffer로 복하기 위해 redo copy latch 가 필요

- Latch 획득하기 위해서 특정 대기 이벤트가 필요 (latch free 대기 이벤트 / p1=address , p2=latch# 래치 이름 획득)

- Child latch를 사용하는 메모리 영역들도 있음

- Buffer cache 의 경우 각각의 hash chain을 독립적으로 관리할 수 있는 구조로 되어 있기에 복수개의 cache buffer chains latch 를 사용

Parent Latch(부모래치) : 여러 개의 자식 latch를 거느릴 수 있음

Solitary Latch (독립래치) : 전체 인스턴스에 단 하나만 존재하는 래치

Child Latch (자식 래치) : 부모 래치에 속한 래치

부모 래치와 독립 래치는 V$LATCH_PARENT 뷰를 이용하여 관찰 가능

자식 래치는 V$LATCH_CHILDREN 부를 통해 관찰 가능

- In Memory Undo 의 경우 10g 이후부터 생긴 것이며 KTI-UNDO 영역을 보호하기 위한 in memory undo latch 가 있음

래치 동작 메커니즘

- 여러 프로세스가 동시에 래치를 획득하는 과정에서 데드락 현상 발생(서로 가지고 있는 래치를 요구하는 경우)

- 데드락 발생을 피하기 위해서 자신 보다 낮은 래치의 경우 no-wait 또는 자신이 획득한 래치 레벨보다 높은 레벨을 요청

래치 모드

- 래치는 기본적으로 Exclusive 모드 사용(한 순간에 하나의 프로세스만이 래치를 보유)

- 특정 래치에 대해서는 Shared mode 를 사용( Cache buffers chains Latch)

래치 획득

- Willing-to wait 모드 래치 획득

n 래치 획득하기 위해서 기본적으로 willing-to-wait 모드를 사용(래치를 획득할 때까지 대기를 의미) – 실패 시 적절한 회수만큼 루프를 돌며 획득 시도 (_SPIN_COUNT 기본 2000) – 이후에도 실패 시 래치가 풀릴때가지 sleep

n Sleep 을 바로 하지 않고 spin 하는 이유

1. 아주 빠른 시간안에 다른 프로세스가 래치를 해지할 것이라고 기대

2. 슬립 상태로 빠지게 되면 OS차원에서 Context switching이 발생(이것이 약간의 CPU를 소모하기 때문에 비용적인 측면에서 sleep 보다는 spin이 나음)
cf) spin 과정에서 Active 상태로 CPU를 계속 점유하기 위해서 일정 시간 소량의CPU를 소모(Active wait) -> 래치 경합에 의해서 많은 수의 프로세스가 동시에 스핀을 수행할 경우 높은 CPU 사용율을 보일 수 있음(래치 경합을 줄이기 위해 CPU를 늘이는 것은 더욱더 상황을 악화 위험)

n Sleep 에서 깨어나는 방법

1. 약속된 시간(타임아웃)

2. Latch wait list에서 자신의 순서가 되어 다른 프로세스가 깨워주는 방법 (latch wait posting)

u 주로 sleep 상태에서 프로세스는 latch:xxx / latch free 이벤트를 대기

u 매번 슬립 시간이 두배로 증가함으로써 빠른 시간내에 래치 획득을 재시도하는 과정에서 지나친 스핀으로 인해 CPU를 과다하게 사용하는 것을 방지

No-wait 모드의 래치 획득

- 대부분의 latch 획득은 willing-to-wait 모드를 사용하지만 특수한 경우 no-wait 모드 사용

- 가장 최근에 획득한 래치보다 더 낮거나 같은 레벨의 래치를 획득하고자 할 때

- 데드락을 피하기 위함

- No-wait 모드의 래치 획득에 실패할 경우 획득하고자 하는 래치보다 높은 레벨의 래치는 모두 해제하고 올바른 순서로 다시 획득 시도

Ex) 1,2,4 레벨의 레치를 획득 후 3레벨 래치 획득 시도 시 no-wait 시도 / 실패 시 1,2 레벨을 남기고 4 레벨 래치 포기 후 3레벨 순서로 획득

래치 cleanup

- 래치를 획득한 후 공유 메모리 영역을 변경하고자 하는 프로세스는 변경 전에 래치복구 영역에(latch recovery area) 자신의 목적에 대한 정보를 기록
이 후, 갑작스럽게 프로세스가 래치를 보유한 채로 종료되면 PMON에서 cleanup function을 호출하여 래치 복구영역에 기록된 정보를 이용하여 복구

래치 관련 Dynamic Views (V$LATCH)

- v$latch_parent : 독립 래치에 대한 통계값을 제공

- v$latch_children : 모든 개별 자식 래치들에 대한 통계값을 제공

컬럼	설명
GETS	Willing-to-wait 모드에서 슬립하기 전의 래치 “요청” 회수
MISSES	Willing-to-wait 모드에서의 슬립하기 전의 래치 획득 실패 회수
SPIN_GETS	Willing-to-wait 모드에서 슬립하기 전의 스핀단계에서의 래치 획득 성공 회수
SLEEPS	Willing-to-wait 모드에서의 슬립 회수
IMMEDIATE_GETS	No-wait 모드에서 래치 획득 성공 회수
IMMEDIATE_MISSES	No-wait 모드에서 래치 획득 실패 회수
SLEEP1 ~ SLEEP4	1~3 회의 슬립회수와 4회 이상의 슬립 회수. 오라클 10gR2부터는 사용되지 않으며 V$EVENT_HISTOGRAM 뷰로 대체
WAITERS_WOKEN	Latch wait posting 을 사용할 경우 세션이 깨어난 회수. 오라클 10gR2 부터는 사용되지 않는다. Latch wait list 래치가 없어진 것과 같은 맥락으로 latch wait posting 알고리즘이 완전히 바뀌었음을 의미
WAIT_TIME	래치를 획득하기 위해 대기한 시간(microsecond) 단위

- GETS 와 MISSES 비율을 이용하여 경합이 있는지를 판단

- Willing-to-wait 모드일 경우 MISSES / GETS 값, No-wait 모드일 경우에는 IMMEDIATE_MISSES / (IMMEDIATE_GETS + IMMEDIATE_MISSES) 값이 1% 이상이면 래치 경합이 발생한 것으로 판단

- Wait_time 을 이용해 대기시간이 CPU 시간에 비해 어느 정도 높은 값을 보일 경우 래치 경합이 발생한 것으로 판단

일반적인 래치 관련 대기이벤트들

- Latch: cache buffers chains : Buffer cache에서 특정 블록을 탐색하고자 하는 프로세스는 cache buffers chain Latch 를 반드시 필요 – 이 과정에서 경합이 발생 시 발생 이벤트

- Latch: cache buffers lru chain : Buffer cache에서 free buffer 와 dirty buffer를 탐색하고자 할 때 경합 발생시 발생되는 이벤트

- Latch: shared pool : Shared pool Heep 영역에 chunk를 할당 받고자 할 때 경합 발생 시

- Latch: library cache : Library cache 영역을 탐색하고 할 때 경합 발생 시 이벤트

- Latch: redo copy : DML에 의한 변동사항을 Redo Buffer에 기록하고자 할 때 작업의 전 과정 동안 redo copy latch 획득 필요 이 때 경합 시 발생

Latch Free 대기 이벤트의 경우 P2의 값이 latch# 이므로 이 값을 V$LATCHNAME.LATCH# 과 조인하면 래치이름을 얻을 수 있음

락(Lock)

락의 분류

1. 락에 대한 정확한 분류가 존재하지 않음

2. Enqueue Lock과 일반 Lock과 정확한 구분이 존재하지 않음

- DML Lock

n Row Locks (TX)

n Table Lock (TM)

- DDL Lock

- Latches and Internal Locks

n Latches

n Intercal locks

u Dictionary Cache locks

u File and Log Management Locks

u Tablespace and Rollback Segment locks

Steve Adams 의 Lock 분류

- Row cache Enqueues

- Library cache locks and Pins

- DML Locks

- Buffer Locks

- Sort Locks

Enqueue Lock : Enqueue 구조로 관리되는 Lock (10g 부터는 Wait Event로 등록 (v$event_name)

Row cache lock : shared pool 의 Row Cache 영역을 보호하기 위해 사용 ( Row cache 메모리 영역 자체에 보유 프로세스 목록 (Owner List) 와 대기프로세스 목록(Waiter List) 를 관리) -> Row cache Lock, Library cache Lock, Library cache pin, Buffer Lock 등이 있음

- Enqueue Lock

n User Type lock : TX, TM, UL

n System Type Lock : CF, US, CI, TC, JS, …

- 일반 Lock

n Row cache Lock

n Library cache Lock, Library cache pin

n Buffer lock

Lock 이 보호하는 Resource

- Latch 가 SGA 보호 / Lock은 Database 전체를 보호 (Table, Transaction, undo segment, tablespace, Job 등

- TM Lock 은 Table resource 보호 (ID1=object id)

- TX Lock 은 Transaction Resource 보호 (ID1=USN << 16|SLOT, ID2=Sequence)

- US Lock 은 Undo Segment Resource 보호 (ID1=USN)

- HW Lock (ID1=Tablespace#, iD2=DBA of Undo Segment Header)

- Enqueue Lock은 V$LOCK의 TYPE, ID1, ID2 값이 resource 구분자 역할

V$SESSION_WAIT

- 대기현상이 잘생하는지 관찰 가능

- Buffer lock 경합의 경우 Buffer busy waits 나 Read by other session 이벤트에 대한 대기현상 발생

- Row cache lock 경합은 row cache lock Event로 확인 가능

- Library cache lock 과 library cache pin 에서의 경합은 library cache lock Evnet, library cache pin Event 로 관찰 가능

Lock 동작 매커니즘

1. Lock 모드

Mode	설명
0	None
1	Null(N)
2	Sub-Shared(SS) 또는 Row-Shared(RS)
3	Sub-Exclusive(SX) 또는 Row-Exclusive(RX)
4	Shared(S)
5	Shared-Sub-Exclusive(SSX) 또는 Shared-ROW-Exclusive(SRX)
6	Exclusive(X)

- Table 변경의 경우 Exclusive Mode로 획득 해야 함

- DML 의 경우 TM Lock을 Sub Exclusive 모드로 획득

- Sub-Exclusive의 경우 상호 호환성이 있기 때문에, 동시에 여러프로세스가 하나의 테이블에 대해 DML 작업을 수행하는 것이 허용

- Lock Mode 호환성

	N	SS	SX	S	SSX	X
N	O	O	O	O	O	O
SS	O	O	O	O	O	X
SX	O	O	O	X	X	X
S	O	O	X	O	X	X
SSX	O	O	X	X	X	X
X	O		X	X	X	X

Ex1) Procedure 실행하는 process 는 Library cache pin을 Shared mode 로 획득 해야 함.

Ex2) Procedure compile 의 경우, library cache pin을 Exclusive mode로 획득해야 함(호환성이 없기에 library cache pin에 의한 경합이 발생)

2. Lock 획득

- Enqueue Lock 획득에 실패한 프로세스는 Enqueue resource의 대기목록(Waiter list)에 등록

(3초 마다(timeout) 스스로 깨어나서 데드락 발생 여부 확인 후 sleep 또는 락을 보유한 프로세스가 락의 사용이 끝난 후 대기 모드 프로세스를 깨워 줌)

- enq: XX – YYYYY 이벤트를 대기

TX Lock 경합

n enq: TX – row lock contention

n enq: TX – allocate ITL entry

n enq: TX – index contention

n enq: TX – contention

이벤트가 별도로 정의

- Row cache lock과 같은 일반 lock 도 Enqueue Lock 과 기본적으로 같은 방식으로 동작

- Buffer lock은 타임아웃이 1초

- 역속적으로 buffer lock 획득하기 위해 대기하는 경우 3초 타임아웃

- Row cache lock 은 60초까지 대기

- Library cache lock 과 library cache pin 은 3초를 대기

- PMON 프로세스는 타임아웃이 1초

- 데드락의 경우 원천적으로 Latch는 level 매커니즘에 의해 방지하지만 lock 의 경우 데드락의 가능성이 항상 존재하며,
데드락 판정이 나면 데드락을 발견한 프로세스의 락 요청은 취소되며, 전체 Transaction 이 아닌 현재 SQL만 rollback 된다

Lock 관련 Dynamic Views

- V$LOCK 에서 Enqueue Lock 관련 정보를 확인 가능

SID	Lock 을 보유중이거나 요청 중인 Session ID LMODE > 0 이면 Lock을 보유중인 Session 이며, Request > 0 이면 Lock 요청중인 session
Type	Enqueue Lock Resoure Type (ex – TX, TM, UL, US, CI, TC, …etc)
ID1	Resource ID 1
ID2	Resource ID 2 <TYPE-ID1-ID2>가 Lock이 보호하는 Resource 구분자
LMODE	Lock 을 보유하고 있는 경우의 Mode (1~6 -> Lock 모드 참조)
REQUEST	Lock 을 요청중인 경우의 Mode (1~6 -> Lock 모드 참조)
CTIME	현재의 lock mode가 허용된 시간(second) Lock을 보유하거나 요청한 이후부터의 시간
Block	현재의 Lock이 다른 Lock을 블로킹하고 있는지의 여부. 1: 다른 Lock을 블로킹 중 0 : 다른 Lock을 블로킹 안하고 있음

V$LOCK_OBJECT

- 현재 시스템의 모든 Transaction에 의해 획득 중인 TM Lock에 대한 정보를 제공

- OBJECT_ID를 이용하여 object를 직접 조회할 수 있음

- Lock 이 걸린 테이블을 조회할 때 유용

V$ENQUEUE_LOCK

- V$LOCK과 동일하지만, 실제 Enqueue 현상-Blocking 이 발생한 경우 Waiting session 에 대한 정보만 조회

- 어떠한 session 이 lock을 대기하고 있는지 정보만 확인 가능

Library cache lock

- DBA_DDL_LOCKS, DBA_KGLLOCK, X$KGLLK를 통해 관찰이 가능

Library cache pin

- DBA_KGLLOCK, X$KGLPN 통해서 관찰이 가능

- 122793.1 메타링크 참조

1. System Trace 를 이용한 분석

$sqlplus '/ as sysdba'

oradebug setmypid

oradebug unlimit

oradebug dump systemstate 266

행 걸린 SID 를 이용하여 PID 검색

select pid from v$process where addr=

(select paddr from v$session where sid= sid번호;

TRACE 파일에서 아래 PID로 검색하여 wait event 확인('waiting for' 를 검색)

PROCESS 위에서 확인한 PID

2. X$KGLLK 테이블을 조사

select sid,saddr from v$session where event= 'library cache lock';

select kgllkhdl Handle,kgllkreq Request, kglnaobj Object

from x$kgllk where kgllkses = '위에서 찾은 saddr정보'

and kgllkreq > 0

--직접 만든 join 문

select a.kgllkhdl Handle,a.kgllkreq Request, a.kglnaobj Object

from x$kgllk a, v$session b

where b.event='library cache lock'

and b.saddr=a.kgllkses

and a.kgllkreq > 0;

또는

select sid,saddr from v$session where event= 'library cache lock';

select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object

from x$kgllk lock_a

where kgllkmod > 0

and exists (select lock_b.kgllkhdl from x$kgllk lock_b

where kgllkses = '위에서 찾은 saddr' /* blocked session */

and lock_a.kgllkhdl = lock_b.kgllkhdl

and kgllkreq > 0);

메타링크 원문

공통 상황들

테이블이 진행중인 변경(ALTER TABLE)에 의해 사용중이라면 이 테이블에 DML작업은 행이 걸리게 됩니다. 테이블의 크기나 변경이 어떤 작업(예를 들어 (수천 건의 레코드를 가지고 있는 테이블에 ALTER TABLE x MODIFY (col1 CHAR(200))) )이냐에 따라 아주 오래 락이 걸리기도 합니다.

이런 경우, v$lock은 이 테이블 오브젝트에 DML 인큐 락(ID1이 테이블의 OBJECT_ID로 LMODE=6, TYPE=TM)을 가지고 'ALTER TABLE' 을 수행하고 있는 세션을 보여줄 것입니다. 이 기다리고 있는 세션은 v$lock에서 보이지 않는데 동시 접속 세션들이 많은 환경에서는 V$LOCK 정보를 이용하여 작업을 방해하고 있는 문제의 세션을 확인하기에 부족할 것입니다.

어떤 사용자가 패키지에서 정의하고 있는 프로시져나 펑션을 수행중이라면 이 패키지 컴파일은 라이브러리 캐시 락 과 라이브러리 캐시 핀을 기다리게 될 것이다.

방법 1: Systemstate 분석

Systemstate 이벤트는 모든 오라클 프로세스들의 상세한 정보를 담고 있는 트레이스 파일을 생성해 줍니다. 이 정보에는 특정 프로세스에 의해 사용중이거나 요청중인 모든 자원들을 포함하고 있습니다.

어떤 작업이 행 상태에 있는 동안 새로운 세션을 열고 다음의 명령을 발행합니다:
Oracle 9.2.0.1 또는 그 이후 :

$sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266

더 오래 전 버젼은 상위 버젼에서도 수행 가능한 다음의 구문을 이용할 수 있습니다. 이벤트 level 266은 9.2.0.6 이전에서는 사용할 수 없습니다.

alter session set max_dump_file_size=unlimited;
alter session set events 'immediate trace name systemstate level 10'

오라클은 USER_DUMP_DEST 디렉토리에 systemstate 트레이스 파일을 생성합니다.

행 상태에 있는 세션의 PID를 얻기 위해 :

select pid from v$process where addr=
(select paddr from v$session where sid= <sid_of_hanging_session> );

이 Systemstate 덤프는 각각의 프로세스별로 구분되는 정보를 담고 있습니다.
트레이스 화일을 열고 "PROCESS <위에서 확인한 PID>"를 찾습니다.
이 프로세스 부분에서 'waitinf for' 를 검색하여 지연 이벤트가 무엇인지 확인합니다.

PROCESS 20:
----------------------------------------
SO: 0x7d2bd820, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=20, calls cur/top: 0x7d3d62d0/0x7d3d85dc, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 109 0 4
last post received-location: kslpsr
last process to post me: 7d2b8d94 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 7d2b8d94 1 6
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: 0x7d2ed5dc
O/S info: user: oracle, term: pts/7, ospid: 19759
OSD pid info: Unix process pid: 19759, image: goblin.forgotten.realms (TNS V1-V3)

<cut>

(session) sid: 141 trans: (nil), creator: 0x7d2bd820, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-0014-00000668, short-term DID: 0000-0000-00000000
txn branch: (nil)
oct: 6, prv: 0, sql: 0x62d01c34, psql: 0x7c20f24c, user: 542/SCOTT
service name: SYS$USERS
O/S info: user: oracle, term: pts/7, ospid: 19758, machine: goblin.forgotten.realms
program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
waiting for 'library cache lock' blocking sess=0x(nil) seq=36 wait_time=0 seconds since wait started=11
handle address=62d064dc, lock address=79f88a68, 100*mode+namespace=c9

U락이 잡힌 오브젝트의 정보를 찾기 위해 핸들 주소를 사용합니다:

SO: 0x79f88a68, type: 53, owner: 0x7d3d62d0, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=79f88a68 handle=62d064dc request=S
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x79f88ab4[0x79e71e60,0x79e71e60] htb=0x79e71e60 ssga=0x79e716fc
user=7d3a13b8 session=7d3a13b8 count=0 flags=[0000] savepoint=0xce
LIBRARY OBJECT HANDLE: handle=62d064dc mtx=0x62d06590(0) cdp=0
name=SCOTT.EMPLOYEES

라이브러리 오브젝트 락이 공유 모드(request=S)에서 요청되었으며 그 이름이 SCOTT.EMPLOYEE임을 알 수 있습니다.

같은 트레이스 파일에서 이 'handle address' 주소로 검색하여 자원에 락을 잡고 있는 프로세스가 무엇인지 찾습니다.

PROCESS 18:
----------------------------------------
SO: 0x7d2bcca8, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=18, calls cur/top: 0x79f3ab84/0x7d3d5fc8, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 109 0 4
last post received-location: kslpsr
last process to post me: 7d2b8d94 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: 7d2b8d94 1 6

<cut>

SO: 0x75fe8f7c, type: 53, owner: 0x7b751914, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=75fe8f7c handle=62d064dc mode=X
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x75fe8fc8[0x79f81790,0x79fc3ef8] htb=0x79f81790 ssga=0x79f8102c
user=7d3988d0 session=7d3988d0 count=1 flags=[0000] savepoint=0x146e
LIBRARY OBJECT HANDLE: handle=62d064dc mtx=0x62d06590(0) cdp=0
name=SCOTT.EMPLOYEES

위 결과에서 우리는 Process 18(pid)가 우리가 사용하고자 하는 오브젝트에 배타 락(mode=X)을 걸고 있는 것을 볼 수 있습니다. V$PROCESS 와 V$SESSION을 이용하여 이 프로세스의 sid, 사용자, 터미널, 프로그램, 등을 조회해 볼 수 있습니다.

이 세션이 실행했던 실제 문장도 이 트레이스 화일에서 확인 할 수 있습니다(문장들과 다른 라이브러리 오브젝트들은 "name=" 다음에 위치합니다).

방법 2: X$KGLLK 테이블에서 조사

X$KGLLK 테이블(SYS/INTERNAL로 조회 가능)은 모든 세션들이 잡고 있거나 요청중인 모든 라이브러리 케시 오브젝트들 담고 있으며 비록 의미를 알기 어려운 열이름들이 사용되지만 V$LOCK 뷰보다 더욱 완벽한 정보를 제공합니다.

여러분은 V$SESSION에 있는 세션 주소(SADDR) 을 찾아 보고 아래의 조회를 해 봄으로써 대기중인 세션에 의해 요청된 락들을 조사할 수 있습니다:

select sid,saddr from v$session where event= 'library cache lock';

SID SADDR
---------- --------
16 572ed244

select kgllkhdl Handle,kgllkreq Request, kglnaobj Object
from x$kgllk where kgllkses = '572ed244'
and kgllkreq > 0;

HANDLE REQUEST OBJECT
-------- ---------- ------------------------------------------------------------
62d064dc 2 EMPLOYEES

이 결과는 라이브러리 캐시 락을 요청하고 있는 세션(KGLLKREQ > 0) 과 함께 락을 잡고자 하는 오브젝트의 이름중 첫 80자리를 KGLNAOBJ로 보여주고 있습니다. KGLLKHDL 의 값은 앞에서 봤던 방법 1의 Systemstate 분석절차에서의 오브젝트에 대한 '핸들 주소' 에 해당됩니다.

만일 이 KGLLKHDL 이 X$KGLLK 의 다른 세션의 핸들과 일치한다면 블로킹 세션의 주소를 알수가 있게 됩니다. 이 락을 작고 있는 세션은 락을 잡고 있기 때문에 KGLLKMOD > 0 상태를 가지고 있을 것입니다.

select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
from x$kgllk lock_a
where kgllkmod > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses = '572ed244' /* blocked session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);

SADDR HANDLE MOD
-------- -------- ----------
OBJECT
------------------------------------------------------------
572eac94 62d064dc 3
EMPLOYEES

조금 더 살펴보면, v$session 에서 KGLLKSES 과 일치하는 SADDR 를 찾아 블러킹 세션에 대한 추가적인 정보를 확인할 수 있습니다:

select sid,username,terminal,program from v$session where saddr = '572eac94'

SID USERNAME TERMINAL
---------- ------------------------------ ------------------------------
PROGRAM
------------------------------------------------
12 SCOTT pts/20
sqlplus@goblin.forgotten.realms (TNS V1-V3)

같은 방법으로, 모든 대기 세션들을 찾을 수 있습니다:

select sid,username,terminal,program from v$session
where saddr in
(select kgllkses from x$kgllk lock_a
where kgllkreq > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
             where kgllkses = '572eac94' /* blocking session */
             and lock_a.kgllkhdl = lock_b.kgllkhdl
             and kgllkreq = 0)
);

SID    USERNAME                       TERMINAL
---------- ------------------------------ ------------------------------
PROGRAM
------------------------------------------------
13         SCOTT                           pts/22
sqlplus@goblin.forgotten.realms (TNS V1-V3)

16         SCOTT pts/7
sqlplus@goblin.forgotten.realms (TNS V1-V3)

일반적인 Lock 관련 대기 이벤트

TX Lock 에 의한 대기 현상

Row cache lock	Row cache lock에 의한 경합
Buffer busy waits, read by other session	Buffer lock에 의하 경합
Library cache lock	Library cache lock에 의한 경합
Library cache pin	Library cache pin 에 의한 경합
DFS Lock handle	Global lock인 경우에 관찰

V$session_wait 나, v$session 을 이용하여 lock 에 대한 경합을 확인 가능

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Partition Local Index 테스트 (0)	2015.12.02
[ORACLE] ASM 에 Datafile 추가 (1)	2015.11.25
[펌][Oracle] 대용량 자료전환 시 튜닝 포인트 - Hash join (0)	2015.11.19
[Oracle] Merge 구문 1 (0)	2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리 (0)	2015.09.03

아래와 같이 튜닝을 하려고 포인트를 Merge join 에 맞춰서 group by 시 해당 부분을 튜닝하려다가

찾게 된 자료를 공유해 본다

내 자료는 아래와 같다.

서브 쿼리로 쪼개서 그 중에 하나이다.

공유 자료다.

1억건 정도의 데이터를 가진 2~3개의 테이블을 조인하여 자료를 이행하고자 하면 심각한 수행속도의 저하에 부딪히게 된다.

1. Join Method 의 선택

대용량의 데이터라면 당연히 Hash Join 을 이용해야 한다.

해쉬조인은 해슁함수 기법을 활용하여 조인을 수행하는 방식으로 Nested Loop 조인이 가진 랜덤 액세스의 단점과 Sort Merge 조인이 가진 정렬의 부담을 해결할 수 있는 대안이다.

랜덤의 부담은 최악의 경우 하나의 로우를 액세스하기 위해 한 블록씩 액세스를 해야 하고, 그 블록이 아직 디스크에 위치하고 있을 수도 있다는 점이다. 정렬의 부담은 정렬처리 그 자체의 부담이라기 보다는 메모리 내에 지정한 정렬영역보다 정렬할 크기가 지나치게 큰 경우에 발생하는 기하급수적인 부담을 말한다.

해쉬조인의 가장 큰 장점은 조인의 연결고리의 인덱스 존재유무에 영향을 받지 않는다는 점이다.

따라서 인라인뷰를 이용한 중간집합을 만들어 가면서 집합의 연산을 통해 결과 집합을 생성해 가다보면 중간에 생성되는 집합들은 일종의 임시 테이블이라고 할 수 있으므로 당연히 인덱스를 가질 수 없다. 이러한 처리에서 해쉬조인의 가치는 크게 부각될 수 밖에 없다.

2. Hash 조인의 튜닝 포인트

1) Build Table 과 Probe Table 의 선택

해쉬 조인을 위해 먼저 액세스하여 필요한 준비를 해두는 처리를 빌드입력(Build Input) 이라 하며, 나중에 액세스하면서 조인을 수행하는 처리를 검증 혹은 검색입력(Probe Input)이라고 한다.

결론부터 언급하면 적은 처리 대상 집합을 가지는 Table 이 Build Table 로 선정되어야 한다.

처리대상 집합이 적다는 것은 테이블 사이즈가 작다는 것을 의미하지는 않는다. 테이블이 아무리 크더라도 인덱스 스캔을 통해 테이블의 3~5% 미만을 액세스한다면 이 경우는 처리 대상 집합이 적은 것이다.

빌드입력이 해쉬영역에 모두 위치할 수 있는 경우는 인_메모리(In_memory) 해쉬조인을 수행하게 되고, 그렇지 못한 경우에는 유예 해쉬조인을 수행하게 된다.

유예 해쉬조인이 수행되는 경우 해쉬 테이블은 Temp 테이블스페이스에 생성된다. Temp 테이블스페이스에 있는 해쉬 테이블이 필요시 해당 부분에 대해서 Block 단위로 액세스하게 된다.

참고) 해쉬조인으로 유도하기 위해서는 USE_HASH(B A) 힌트를 사용한다. 여기서 테이블 B 는 빌드 테이블이며 테이블 A 는 검증테이블을 의미한다.

2) Temp Tablespace 를 이용한 성능 향상

일반적으로 해쉬영역의 크기를 크게 할수록 Temp 테이블스페이스를 적게 사용하지만 무한정 해쉬영역을 확장할 수 없기 때문에 초대용량 데이터를 이행할 때는 해쉬 테이블이 Temp 테이블스페이스에 생성된다.

PGA 의 해쉬 테이블과 Temp 테이블스페이스는 Build 테이블의 처리 대상 집합의 크기에 따라 계속적인 통신을 하게 된다. 아래는 Temp 테이블스페이스의 사용을 확인하는 SQL이다.

SQL> SELECT USERNAME, TABLESPACE, CONTENTS, SEGTYPE, EXTENTS

FROM V$TEMPSEG_USAGE;

SEGTYPE : 해쉬조인을 수행할 경우 HASH 라고 표시된다.

SORT 라고 표현되면 SQL에서 Order by, Group by 등에 의해 Sort 를 수행하거나

인덱스를 생성하는 부분이다.

EXTENTS : 할당된 익스텐트의 갯수이다.

SQL> SELECT A.USERNAME

,A.TABLESPACE

,A.CONTENTS

,A.SEGTYPE

,A.EXTENTS AS USED_EXTENTS

,TO_CHAR(ROUND(A.EXTENTS / B.TOTAL_EXTENTS * 100,2),'999.00') || '%'

AS USED_PCT

,B.FREE_EXTENTS AS FREE_EXTENTS

,TO_CHAR(ROUND(B.FREE_EXTENTS / B.TOTAL_EXTENTS * 100,2),'999.00') || '%'

AS FREE_PCT

,B.TOTAL_EXTENTS

FROM (SELECT USERNAME, TABLESPACE, CONTENTS, SEGTYPE, EXTENTS

FROM V$TEMPSEG_USAGE) A

,(SELECT TABLESPACE_NAME, EXTENT_SIZE, TOTAL_EXTENTS

,TOTAL_BLOCKS, USED_EXTENTS, FREE_EXTENTS

FROM V$SORT_SEGMENT) B

WHERE A.TABLESPACE = B.TABLESPACE_NAME

;

3) Parallel Processing 을 이용한 성능 향상

해쉬조인 만을 이용하는 것보다는 Parallel Processing 을 이용한 해쉬 조인은 대용량 데이터를 처리하기 위한 최적의 솔루션을 제공하고 있다.

/*+ USE_HASH(B A) PARALLEL(A 8) PARALLEL(B 8) */

4) Parameter 조정을 통한 성능 향상

오라클 9i 이상

SQL> alter system set pga_aggregate_target = 10737418240;

SQL> alter session set workarea_size_policy = auto;

SQL> alter session set hash_multiblock_io_count = 256;

오라클 9i 부터는 자동실행 메모리 기능을 제공하므로 PGA 공간의 크기를 각 세션별로 지정하지 않고 전체 세션이 사용하는 총 크기를 지정하기 때문에 한 세션이 PGA 크기를 전부 독차지 할 수도 있다.

그러나 자동실행 메모리 기능을 실제 사용해보면 오라클 9i 는 제대로 동작하지 않을 때가 많다. 즉, pga_aggregate_target 을 크게 잡아주어도 해쉬영역으로 메모리를 사용하지 못하고 Temp 테이블스페이스를 사용한다. 이는 자동실행 메모리 기능이 9i 에서 새로 추가되었기 때문에 기능이 완벽하지 못하다고 사료된다. 오라클 10g에서는 제대로 동작한다.

db_file_multiblock_read_count : Table Full Scan 시 해당 파라미터를 증가시킴으로써 한 번 디스크 IO에 더 많은 블록을 액세스할 수 있으므로 디스크 IO 횟수를 줄일 수 있다.

이 파라미터는 세션 레벨에서 변경 가능하다.

오라클 9i 이전

SQL> alter session set workarea_size_policy = manual;

SQL> alter session set sort_area_retained_size = 20480000;

SQL> alter session set sort_area_size = 20480000;

SQL> alter session set hash_area_size = 20480000;

SQL> alter session set hash_multiblock_io_count = 256;

SQL> alter session set db_file_multiblock_read_count = 256;

Hash Area Size

해쉬영역(Hash Area)이란 해쉬 조인을 수행하기 위해 메모리(PGA) 내에 만들어진 영역을 말한다. 이 해쉬영역이 부족하면 디스크(Temp Tablespace)를 사용하게 되어 수행속도에 지대한 영향을 미치게 된다.

해쉬영역은 세션별로 지정할 수 있으며 최대 PGA의 크기만큼 설정할 수 있다. 아래는 해쉬영역의 크기를 1G 로 설정한다.

SQL> alter session set hash_area_size = 1073741824;

3. Hash 조인으로 유도

1) 힌트(Hint) 사용

/*+ ORDERED USE_HASH(A B C) PARALLEL(A 8) PARALLEL(B 8) */

USE_HASH 힌트를 사용하여도 옵타마이져가 원하는 실행계획을 만들어 내지 않으면 ORDERED 힌트를 사용하여 실행계획을 고정시킬 필요가 있다. ORDERED 힌트는 쿼리 내 FROM 절의 순서대로 조인을 수행한다.

4. Hash 조인의 모니터링

1) V$PROCESS

SELECT ADDR, PID, PGA_USED_MEM

FROM V$PROCESS

;

해쉬 조인이 사용하는 메모리 용량을 모니터링 할 수 있다.

2) V$SESSION_LONGOPS

SELECT SID, START_TIME, TIME_REMAINING, ELAPSED_SECONDS, MESSAGE

FROM V$SESSION_LONGOPS

WHERE SID = XXX

;

V$SESSION_LONGOPS 는 6초 이상 수행되는 OPERATION 의 진행상황을 모니터링 할 수 있는 동적성능 뷰이다.

3) V$TEMPSEG_USAGE

SELECT USERNAME, TABLESPACE, CONTENTS, SEGTYPE, EXTENTS

FROM V$TEMPSEG_USAGE

;

해쉬조인이 사용하는 Temp 테이블스페이스의 사용량을 모니터링할 수 있다.

원본 출처 : http://blog.daum.net/servant2342/5417565

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[ORACLE] ASM 에 Datafile 추가 (1)	2015.11.25
[Oracle] OWI Latch & Lock 정리 (0)	2015.11.24
[Oracle] Merge 구문 1 (0)	2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리 (0)	2015.09.03
특정 컬럼만 Masking (0)	2015.09.03

~~DB에 접속이 안되는 현상이 있다고 연락을 받아 확인하다가,~~

~~Merge 구문이 inactive 상태에서 다른 Session 을 block 하고 있는 상태를 확인하게 되었다.~~

~~Merge 구문이 실행 된 후 완료 되고 Inactive 상태로 빠지는데,~~

~~이 때 다른 Merge 구문을 block을 함으로써 lock 상태가 되었다. 그러면서 자원을 획득하지 못한~~

~~Blocked 는 inactive 상태가 되고..이렇게 트리 형태로 증가되는 부분을 확인 하였다.~~

~~이것이 왜 그런지는 조금 더 찾아 봐야겠지만 내가 본 현상과 예측은 이와 같다.~~

그러면 찾아보기 전에 Merge 구문에 대해서 정리부터 해본다.

너무나도 쉽다고 생각할 수 있지만 어차피 언젠가는 merge 구문 사용이 필요할 때를 대비해서 정리해 본다.

쉽게 말해서 있으면 update / 없으면 insert 를 하는 구문이다.

간단한 테스트로 진행 (타 블로그 참조 하였음)

2개의 테이블 생성

create table emp_t (no number(3), name varchar2(10), deptno number(3));

create table emps_s ( no number(3), name varchar2(10), deptno number(3));

테이터 생성

SQL> insert into emps_s values(100, 'AA', 600);

SQL> insert into emps_s values(200, 'B', 20);

SQL> insert into emps_s values(300, 'C', 30);

SQL> insert into emps_s values(210, 'D', 40);

SQL> insert into emps_s values(400,'F',50);

SQL> insert into emp_t values(100,'A',10);

SQL> insert into emp_t values(200,'B',20);

SQL> insert into emp_t values(300,'C',30);

확인

merge 구문실행

SQL> merge into emp_t t using emps_s s on (t.no=s.no)

when matched then

update set t.name = s.name, t.deptno=s.deptno

when not matched then

insert values(s.no, s.name, s.deptno);

5 rows merged.

두 테이블의 데이터가 동일한 것을 확인 가능

commit 은 필수!!!

단, 조건절 내의 동일한 값이 있을 경우 ORA-30926 가 발생

SQL> merge into emp_t t using emps_s s on (t.no=s.no)

when matched then

update set t.name = s.name, t.deptno=s.deptno

where deptno=777 <-원하는 조건을 추가하여 가능

when not matched then

insert values(s.no, s.name, s.deptno);

플랜을 한번 확인해 보니 아래와 같이 확인이 가능하다

Hash Join 으로 풀리고, 테이블에 대해서 index 가 없으니 당연히 full scan ..

이 부분도 고려하여 index 설계를 잘 하면, merge 구문도 성능 향상을 볼 수 있을 듯 싶다.

아래는 테스트 하다가, index 타는 merge 구문을 캡쳐하여 추가한 것이다.

Index 를 이용하여 merge 구문이 실행 된 것을 확인할 수 있다.

참조 블로그

http://majesty76.tistory.com/37

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[Oracle] OWI Latch & Lock 정리 (0)	2015.11.24
[펌][Oracle] 대용량 자료전환 시 튜닝 포인트 - Hash join (0)	2015.11.19
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리 (0)	2015.09.03
특정 컬럼만 Masking (0)	2015.09.03
[펌] DBMS_XPLAN (0)	2015.08.28

정의

SYS_CONTEXT 함수는 세션정보를 얻어오는 함수

Namespace와 관계되는 Parameter 값을 반환

오라클에서 사용하는 Namespace는 USERENV이다.

형식

SYS_CONTEXT ('Namespace', 'Parameter' [, Length])

예제

- INSTANCE NAME/ DB NAME을 확인하고 싶은데 권한이 없는 일반 유저가 확인하고 싶을때 가능
- USERENV : 현재 세션의 환경정보를 반환는 네임스페이스 입니다.

SELECT SYS_CONTEXT ('USERENV', 'SESSIONID'),   --  사용자 감사세션
       SYS_CONTEXT ('USERENV', 'OS_USER'),     --  사용자 운영체제 시스템 ID 
       SYS_CONTEXT ('USERENV', 'HOST'),        --  사용자 PC이름

       SYS_CONTEXT ('USERENV', 'DB_NAME'),     --  DB 이름
       SYS_CONTEXT ('USERENV', 'INSTANCE_NAME'),     --  INSTANCE 이름
       SYS_CONTEXT ('USERENV', 'SID'),         --  SID (session number)
       SYS_CONTEXT ('USERENV', 'TERMINAL'),    --  사용자 운영체제 시스템 ID
       SYS_CONTEXT ('USERENV', 'IP_ADDRESS')   --  사용자 IP주소
  FROM DUAL ; 

파라미터	정의
Action	모듈내 위치로 DBMS_Application_Info 패키지로 설정됨
Audited_Cursorid	감사를 트리거하는 SQL의 커서 ID를 반환
Authenticated_Identity	authentication 하에 사용중인 ID를 반환
Authentication_Data	로그인 사용자가 권한받아 사용중인 데이터
Authentication_Method	권한 방법을 반환
BG_Job_id	현재 세션의 Job ID
Client_Identifier	DBMS_Session을 통한 응용에 의해 설정된 ID
Client_Info	사용자 세션정보를 64 Byte 까지 반환
Current_Bind	FGA에 대한 Bind Variables
Current_Schema	현재 스키마에서 사용중인 Default 스키마 명
Current_Schemaid	현재 스키마에서 사용중인 Default 스키마 ID
Current_SQL	트리거된 첫 4 KByte
Current_SQL_n	n 은 1~7의 4 KByte
Current_SQL_Length	현재 SQL문의 길이
DB_Domain	DB_Domain 으로 지정한 DB의 도메인
DB_Name	DB_Name 으로 지정한 DB의 이름
DB_Unique_Name	DB_Unique_Name 으로 지정한 DB의 이름
Entryid	현재 Audit Entry 번호
Entrprise_identity	사용자의 Enterprise-wide identity
FG_Job_id	현재 세션의 Job ID
Global_Context_Memory	Global Access Context에 의해서 사용중인 SGA의 번호
Global_Uid	Global User ID
Host	Host Machin의 이름
Identification_Type	스키마가 Db에서 생성된 방법
Instance	현재 인스턴스 ID
Instance_Name	클라이언트가 연결된 Machine의 IP
IP_Address	연결된 IP 주소
isDBA	DBA 권한의 사용자인지 질의 [ True \| False ]
Lang	언어 명
Language	User Session에서 사용중인 언어
Module	DBMS_Application_info 패키지로 설정한 모듈 이름
Network_Protocol	네트워크 프로토콜
NLS_Calendar	현재 세션의 달력
NLS_Currency	현재 세션의 화폐
NLS_Date_Format	현재 세션의 날짜 포멧
NLS_Date_Language	현재 세션의 날짜 표현 언어
NLS_Sort	Binary 또는 언어적 Sort
NLS_Territory	현재 세션의 territory
OS_User	OS 유저 ID
Policy_invoker	Row Level Security Policy 함수의 invoker
Proxy_Enterprise_identity	Proxy 사용자가 Enterprise User인 경우, 오라클 인터넷 디렉토리 DN
Proxy_Global_Uid	Enterprise User Security Proxy 사용자에 대한 오라클 인터넷 디렉토리의 Global 사용자
Proxy_User	DB 사용자 명
Proxy_Userid	DB 사용자 ID
Server_Host	인스턴스가 동작중인 Machine의 호스트명
Service_Name	주어진 세션이 접속된 서비스 명
Session_User	엔터프라이즈 사용자인 경우 스키마 이름, 그외에는 DB 사용자 이름
Session_Userid	DB 사용자 이름의 ID
Sessionid	감사 세션 ID
SID	세션 번호
Statementid	감사문 ID
Terminal	운영체제 시스템 ID

[출처]

http://yysvip.tistory.com/250

http://develop.sunshiny.co.kr/681

http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions079.htm

저작자표시 비영리

'Oracle > DBA' 카테고리의 다른 글

[펌][Oracle] 대용량 자료전환 시 튜닝 포인트 - Hash join (0)	2015.11.19
[Oracle] Merge 구문 1 (0)	2015.11.12
특정 컬럼만 Masking (0)	2015.09.03
[펌] DBMS_XPLAN (0)	2015.08.28
[펌]Index Access and Filter (0)	2015.08.28

Louis.Kim 의 제 3막

Oracle/DBA

[튜닝가이드] 인덱스 사용 여부 관련 생각 + [펌]

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Process / Session 모니터링

'Oracle > DBA' 카테고리의 다른 글

[펌][ORACLE] ORA-에러

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Index monitoring

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Partition Local Index 테스트

'Oracle > DBA' 카테고리의 다른 글

[ORACLE] ASM 에 Datafile 추가

'Oracle > DBA' 카테고리의 다른 글

[Oracle] OWI Latch & Lock 정리

'Oracle > DBA' 카테고리의 다른 글

[펌][Oracle] 대용량 자료전환 시 튜닝 포인트 - Hash join

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Merge 구문 1

'Oracle > DBA' 카테고리의 다른 글

[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리

'Oracle > DBA' 카테고리의 다른 글

+ Recent posts

티스토리툴바