Louis.Kim 의 제 3막

[Oracle] Merge 구문 1

2015. 11. 12. 14:48

~~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

[Oracle] ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

2015. 10. 30. 15:50

업체에서 점검 중에 아래와 같은 에러 메시지를 Alert Log 에서 학인함

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Detail

This error is fairly self explanatory - we cannot get enough space for a temporary segment.

The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

NOTE:

A "temp segment" is not necessarily a SORT segment in a temporary tablespace. It is also used for temporary situations while creating or dropping objects like tables and indexes in permanent tablespaces.

eg: When you perform a CREATE INDEX a TEMP segment is created to hold what will be the final permanent index data. This TEMP segment is converted to a real INDEX segment in the dictionary at the end of the CREATE INDEX operation. It remains a temp segment for the duration of the CREATE INDEX operation and so failures to extend it report ORA-1652 rather than an INDEX related space error.

그렇다면 Temp 를 사용하는 경우다..

내가 가장 많이 알고 있는게 Sort 할 경우인데, 그 외에도 아래와 같다.

(Create , Drop 할때도 생길 수 있음)

A SORT	Used for a SELECT or for DML/DDL
CREATE INDEX	The index create performs a SORT in the users default TEMP tablespace and ALSO uses a TEMP segment to build the final index in the INDEX tablespace. Once the index build is complete the segment type is changed.
CREATE PK CONSTRAINT
ENABLE CONSTRAINT
CREATE TABLE	New tables start out as TEMPORARY segments. Eg: If MINEXTENTS is > 1 or you issue CREATE table as SELECT.
Accessing a GLOBAL TEMPORARY TABLE	When you access a global temporary table a TEMP segment is instantiated to hold the temporary data.

Sort Segment 확인

-- Used_block 과 Total_block 이 같으면 temp 가 부족한 것을 확인 가능

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

아래의 명령어로 현재 temp 를 누가 사용하는지 확인 가능

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

TEMP 확이

SELECT * FROM DBA_TEMP_FILES;

Trace 확인 했을 때에는 GATHER_TABLE_STATS 를 확인 하였다...

흠...테이블 단위 통계 정보 수집이면...이때도 Temp 를 쓰는가 보다..

When gathering GLOBAL statistics a large amount of TEMP space is needed in order to collect the necessary information.

해결방법

Gather the statististics at the partition level rather than the table level and derive the table level statistics from the partitions.

To do this gather using granularity => 'PARTITION'.

exec dbms_stats.gather_table_stats(ownname => '<schema name>', tabname => '<table name>', granularity => 'PARTITION');

참고로 10g 이라서 아래도 참조할 부분이다.

This new value exists in 10.2.0.5 and for 10.2.0.4 after Patch 6526370 applied (see more in Note 6526370.8 Bug 6526370 - New GRANULARITY option for DBMS_STATS statistics on the table).

해결방법

1. tempfiles을 추가, 현재 파일 사이즈를 증가하거나 혹은 auto extend를 활성화

2. 쿼리/구문을 튜닝하여 sort 작업들이 디스크가 아니라 메모리에서 수행(가장중요)

1. Temp 를 하나 더 추가

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORADATA/TEMP02.DBF' SIZE 1000M AUTOEXTEND ON;

2.기본 Temp resize

ALTER DATABASE TEMPFILE ''/ORADATA/TEMP01.DBF'' RESIZE 2000M;

참조oracle 문서

How Do You Find Who And What SQL Is Using Temp Segments (문서 ID 317441.1)

ORA-1652 에러에 대한 문제 해결 방안 (문서 ID 1908925.1)

RAC 환경은, Note 280578.1

참조 사이트

http://jun3.tistory.com/26

저작자표시 비영리 (새창열림)

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

[Oracle] IMPDP 시 ORA-29913 (0)	2015.11.20
[Oracle] Windows Opatch 시 74 Error (0)	2015.11.18
[Oracle] Hotbackup도중 abort 진행 (0)	2015.10.27
[ORACLE] [펌] ragmain check 데몬의 비정상적인 증가 (0)	2015.10.16
PRCN-2065 : Port(s) 1521 are not available on the nodes given (0)	2015.09.16

[Oracle] ORACLE 11g grid 리소스 설명 [출처] ORACLE 11g grid 리소스 설명

2015. 10. 30. 09:26

아래 내용도 기본적으로 알아야 하는 내용들.....

CLUSTERWARE PROCESSES in 11g RAC R2 Environment

By user782636 on Dec 14, 2011

i).Cluster Ready Services (CRS)

$ ps -ef | grep crs | grep -v grep

root 25863 1 1 Oct27 ? 11:37:32 /opt/oracle/grid/product/11.2.0/bin/crsd.bin reboot

crsd.bin => The above process is responsible for start, stop, monitor and failover of resource. It maintains OCR and also restarts the resources when the failure occurs.

This is applicable for RAC systems. For Oracle Restart and ASM ohasd is used.

ii).Cluster Synchronization Service (CSS)

$ ps -ef | grep -v grep | grep css

root 19541 1 0 Oct27 ? 00:05:55 /opt/oracle/grid/product/11.2.0/bin/cssdmonitor

root 19558 1 0 Oct27 ? 00:05:45 /opt/oracle/grid/product/11.2.0/bin/cssdagent

oragrid 19576 1 6 Oct27 ? 2-19:13:56 /opt/oracle/grid/product/11.2.0/bin/ocssd.bin

cssdmonitor => Monitors node hangs(via oprocd functionality) and monitors OCCSD process hangs (via oclsomon functionality) and monitors vendor clusterware(via vmon functionality).This is the multi threaded process that runs with elavated priority.

Startup sequence: INIT --> init.ohasd --> ohasd --> ohasd.bin --> cssdmonitor

cssdagent => Spawned by OHASD process.Previously(10g) oprocd, responsible for I/O fencing.Killing this process would cause node reboot.Stops,start checks the status of occsd.bin daemon

Startup sequence: INIT --> init.ohasd --> ohasd --> ohasd.bin --> cssdagent

occsd.bin => Manages cluster node membership runs as oragrid user.Failure of this process results in node restart.

Startup sequence: INIT --> init.ohasd --> ohasd --> ohasd.bin --> cssdagent --> ocssd --> ocssd.bin

iii) Event Management (EVM)

$ ps -ef | grep evm | grep -v grep

oragrid 24623 1 0 Oct27 ? 00:30:25 /opt/oracle/grid/product/11.2.0/bin/evmd.bin

oragrid 25934 24623 0 Oct27 ? 00:00:00 /opt/oracle/grid/product/11.2.0/bin/evmlogger.bin -o /opt/oracle/grid/product/11.2.0/evm/log/evmlogger.info -l /opt/oracle/grid/product/11.2.0/evm/log/evmlogger.log

evmd.bin => Distributes and communicates some cluster events to all of the cluster members so that they are aware of the cluster changes.

evmlogger.bin => Started by EVMD.bin reads the configuration files and determines what events to subscribe to from EVMD and it runs user defined actions for those events.

iv).Oracle Root Agent

$ ps -ef | grep -v grep | grep orarootagent

root 19395 1 0 Oct17 ? 12:06:57 /opt/oracle/grid/product/11.2.0/bin/orarootagent.bin

root 25853 1 1 Oct17 ? 16:30:45 /opt/oracle/grid/product/11.2.0/bin/orarootagent.bin

orarootagent.bin => A specialized oraagent process that helps crsd manages resources owned by root, such as the network, and the Grid virtual IP address.

The above 2 process are actually threads which looks like processes. This is a Linux specific

v).Cluster Time Synchronization Service (CTSS)

$ ps -ef | grep ctss | grep -v grep

root 24600 1 0 Oct27 ? 00:38:10 /opt/oracle/grid/product/11.2.0/bin/octssd.bin reboot

octssd.bin => Provides Time Management in a cluster for Oracle Clusterware

vi).Oracle Agent

$ ps -ef | grep -v grep | grep oraagent

oragrid 5337 1 0 Nov14 ? 00:35:47 /opt/oracle/grid/product/11.2.0/bin/oraagent.bin

oracle 8886 1 1 10:25 ? 00:00:05 /opt/oracle/grid/product/11.2.0/bin/oraagent.bin

oragrid 19481 1 0 Oct27 ? 01:45:19 /opt/oracle/grid/product/11.2.0/bin/oraagent.bin

oraagent.bin => Extends clusterware to support Oracle-specific requirements and complex resources. This process runs server callout scripts when FAN events occur. This process was known as RACG in Oracle Clusterware 11g Release 1 (11.1).

ORACLE HIGH AVAILABILITY SERVICES STACK

i) Cluster Logger Service

$ ps -ef | grep -v grep | grep ologgerd

root 24856 1 0 Oct27 ? 01:43:48 /opt/oracle/grid/product/11.2.0/bin/ologgerd -m mg5hfmr02a -r -d /opt/oracle/grid/product/11.2.0/crf/db/mg5hfmr01a

ologgerd => Receives information from all the nodes in the cluster and persists in a CHM repository-based database. This service runs on only two nodes in a cluster

ii).System Monitor Service (osysmond)

$ ps -ef | grep -v grep | grep osysmond

root 19528 1 0 Oct27 ? 09:42:16 /opt/oracle/grid/product/11.2.0/bin/osysmond

osysmond => The monitoring and operating system metric collection service that sends the data to the cluster logger service. This service runs on every node in a cluster

iii). Grid Plug and Play (GPNPD):

$ ps -ef | grep gpn

oragrid 19502 1 0 Oct27 ? 00:21:13 /opt/oracle/grid/product/11.2.0/bin/gpnpd.bin

gpnpd.bin => Provides access to the Grid Plug and Play profile, and coordinates updates to the profile among the nodes of the cluster to ensure that all of the nodes have the most recent profile.

iv).Grid Interprocess Communication (GIPC):

$ ps -ef | grep -v grep | grep gipc

oragrid 19516 1 0 Oct27 ? 01:51:41 /opt/oracle/grid/product/11.2.0/bin/gipcd.bin

gipcd.bin => A support daemon that enables Redundant Interconnect Usage.

v). Multicast Domain Name Service (mDNS):

$ ps -ef | grep -v grep | grep dns

oragrid 19493 1 0 Oct27 ? 00:01:18 /opt/oracle/grid/product/11.2.0/bin/mdnsd.bin

mdnsd.bin => Used by Grid Plug and Play to locate profiles in the cluster, as well as by GNS to perform name resolution. The mDNS process is a background process on Linux and UNIX and on Windows.

vi).Oracle Grid Naming Service (GNS)

$ ps -ef | grep -v grep | grep gns

gnsd.bin => Handles requests sent by external DNS servers, performing name resolution for names defined by the cluster.