Louis.Kim 의 제 3막

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 만 확인 가능하기에 이 점 유의!!

-- owner 와 table_name 을 입력해야 함

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) );

한가지 더 테스트로 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);

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

1. Database 로 진행

- Tablespace 및 Datafile 확인

- DB shutdown 후 Mount 로 startup 진행

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  2079480 bytes

Variable Size             176162056 bytes

Database Buffers          427819008 bytes

Redo Buffers                6307840 bytes

Database mounted.

- 해당 Datafile 를 이동 후 rename 진행

SQL> alter database rename file 'E:\MASDBDEV\HG1.dbf' to 'E:\HG1.dbf';

- Database open 진행

SQL> alter database open;

Database altered.

- 확인

SQL> select file_name, tablespace_name from dba_data_files order by 2;

FILE_NAME                                TABLESPACE_NAME

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

E:\MASDBDEV\HG2.DBF                      HG

E:\HG1.DBF                               HG

E:\MASDBDEV\IMSI01.DBF                   IMSI

E:\MASDBDEV\SYSAUX01.DBF                 SYSAUX

E:\MASDBDEV\SYSTEM01.DBF                 SYSTEM

E:\MASDBDEV\TEST01.DBF                   TEST

E:\MASDBDEV\UNDOTBS01.DBF                UNDOTBS1

E:\MASDBDEV\USERS01.DBF                  USERS

2. Tablespace 로 진행

- Tablespace 및 Datafile 확인

- 옮기고자 하는 Datafile 의 Tablespace 를 offline 진행

SQL> alter tablespace HG offline;

- Offline 이 후 해당 Datafile 를 이동 후 rename 진행

SQL> alter tablespace HG rename datafile 'E:\HG1.dbf' to 'E:\MASDBDEV\HG1.dbf';

- Rename 까지 완료 되면 다시 online 진행

SQL> alter tablespace HG online;

- 옮겨 졌는지 확인 후 Test로 Talbe 를 Select 해 보자.

ERROR at line 1:

ORA-00376: file 7 cannot be read at this time

ORA-01110: data file 7: 'E:\HG1.DBF'

또한, SYSTEM, Rollback segment혹은 Temporary segment를 포함하는 테이블스페이스는 안되므로 이때는 Database로 진행

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

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

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

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

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

expdp system/oracle directory=dp dumpfile=user_151120.dmp logfile=user_log.log tables=user.table1, user.tabl2....

요청 받은 table 들은 truncate 로 삭제 진행

impdp system/oracle directory=dp dumpfile=user_151120.dmp logfile=user_log_imp.log content=data_only

ORA-31693: Table data object "유저명"."테이블명" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

ORA-39786: Number of columns does not match between export and import database

The errors occur when the ACCESS_METHOD is EXTERNAL_TABLE. This method is chosen if at least one of these conditions is met:

There is an active trigger

The table is partitioned

FGAC (Fine Grained Accesses Control) in insert mode

Referential integrity constraint exists

Unique index exists

Enabled constraint is present on pre-existing table

If any of these conditions apply, then you need to do import using DIRECT_PATH access method to solve the problem.

Follow either of the below workarounds to solve the issue:

o  Either drop the constraints/unique indexes on table before impdp is started and add access_method=direct_path to command line

- OR -

o  Rename the table before impdp, do the import and transfer the data per INSERT/SELECT to renamed table. Finally drop the imported table and rename table to old name. 

In 10g, DataPump chooses external tables for the access method to import data into a table with an unique index. On the other hand, in 11g the DataPump chooses the direct path access method even in this case. 

This issue specified in the Bug 4383811 Which has been closed as is not feasible to fix in 10g.

The ORA-39786 error is introduced (in 10.2.0.3.0) to avoid an ORA-600 error when importing data into the pre-existing table using the external tables access method.

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 테이블스페이스의 사용량을 모니터링할 수 있다.

OPatch failed with error code = 74

Distributed Transaction Coordinator

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);

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.

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

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

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;

SELECT * FROM DBA_TEMP_FILES;

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'.

참고로 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;