TEMP TABLESPACE 사용량 조회

TEMP TABLESPACE의 사용량을 조회할 수 있는 딕셔너리뷰가 여러개 있길래, 정확하게 사용하고자 정리하는 차원에서 글을 작성합니다.

 

DBA_TEMP_FREE_SPACE

DBA_TEMP_FREE_SPACE displays temporary space usage information at tablespace level.

해당 뷰는 TABLESPACE 레벨에서 TEMP 사용량을 보여줍니다. 결론부터 이야기하면 이뷰를 통해서 TEMP 사용량을 구할 수 있습니다.

TABLESPACE_NAME                TABLESPACE_SIZE                ALLOCATED_SPACE                FREE_SPACE           SHARED        INST_ID        
------------------------------ ------------------------------ ------------------------------ -------------------- ------------- -------------- 
TEMP                                                  2.0e+12                        2.0e+12              2.0e+12 SHARED                      
TEMP_MIG                                              1.2e+13                        1.2e+13              8.7e+12 SHARED                      

2 rows selected.

 

출처 : https://docs.oracle.com/database/121/REFRN/GUID-61C81A2C-C8D6-4B79-ABA3-296489CBB17A.htm#REFRN23627

V$TEMP_SPACE_HEADER

This view displays aggregate information per file per LOCALLY MANAGED temporary tablespace regarding how much space is currently being used and how much is free as identified in the space header.

해당 뷰는 TEMP의 header에 식별된대로 현재 사용중인 공간과 사용 가능한 공간의 정보를 LOCALLY MANAGED TEMP 테이블스페이스 파일 별로 보여줍니다. (요즘 Oracle 버전은 Manual 하게 Dictionary managed 방식으로 설정하지 않는 한 기본적으로 LOCALLY MANAGED 방식으로 생성되기 때문에 TEMP의 HEADER 정보를 보여준다고 생각하시면 될 것 같습니다.)

 

TABLESPACE_NAME                FILE_ID        BYTES_USED           BLOCKS_USED            BYTES_FREE           BLOCKS_FREE            RELATIVE_FNO             CON_ID       
------------------------------ -------------- -------------------- ---------------------- -------------------- ---------------------- ------------------------ ------------ 
TEMP                                        1              3.2e+10                3921664             85983232                  10496                        1            0
TEMP                                        2              3.2e+10                3932160                    0                      0                        6            0
TEMP                                        3              3.2e+10                3932160                    0                      0                        2            0
...생략

출처 : https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2161.htm#REFRN30282

 

V$TEMP_EXTENT_POOL

This view displays the state of temporary space cached and used for a given instance. Note that loading of the temporary space cache is lazy, and that instances can be dormant. Use GV$TEMP_EXTENT_POOL for information about all instances.

해당 뷰는 지정된 인스턴스에 대해 캐시되고 사용된 TEMP 공간의 상태를 표시합니다. TEMP 공간 캐시의 로딩은 느리고, 해당 인스턴스는 휴면 상태일 수 있습니다. 모든 인스턴스에 대한 자세한 내용은 GV$TEMP_EXTENT_POOL을 사용하십시오. (익스텐트를 기준으로 TEMP 사용량을 확인하고 싶을때 사용하면 좋을 듯 합니다.)

 

TABLESPACE_NAME                FILE_ID        EXTENTS_CACHED               EXTENTS_USED             BLOCKS_CACHED              BLOCKS_USED            BYTES_CACHED             BYTES_USED           RELATIVE_FNO             CON_ID       
------------------------------ -------------- ---------------------------- ------------------------ -------------------------- ---------------------- ------------------------ -------------------- ------------------------ ------------ 
TEMP                                        1                            0                        0                          0                      0                        0                    0                        1            0
TEMP                                        2                        15357                        0                    3931392                      0                  3.2e+10                    0                        6            0
TEMP                                        3                         3316                        0                     848896                      0                  7.0e+09                    0                        2            0
...생략

출처 : https://docs.oracle.com/cd/B14117_01/server.101/b10755/dynviews_2138.htm

 

 

테스트를 통해 각 뷰들이 TEMP 영역 사용 시 어떻게 값들이 변화되는지 확인해보도록 하겠습니다.

해당 테스트는 아래의 출처의 댓글을 참조하여 재구성하였습니다.

출처 : https://community.oracle.com/mosc/discussion/3633775/v-temp-space-header-and-dba-temp-free-space-providing-different-data

 

순서

1. 새로운 TEMP 테이블스페이스 생성 후 DEFAULT TABLESPACE 설정

2. 로우수가 많은 테스트 테이블 생성(인덱스 생성 시 TEMP 테이블스페이스 보다 더 크게 생성되도록 하여 에러를 유도하기 위함)

3. 생성 전 , 생성 중, 생성 실패 후 3단계로 각 뷰들의 값들의 변화를 살펴봄

 

1. 새로운 TEMP 테이블스페이스 생성 후 DEFAULT TABLESPACE 설정

SQL> CREATE TEMPORARY TABLESPACE TEMP_YS TEMPFILE SIZE 1024M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_YS;

--확인

SQL> select username,temporary_tablespace from dba_users where username='TEST';

USERNAME    TEMPORARY_TABLESPACE                     
----------- ---------------------------------------- 
TEST        TEMP_YS                                 

1 rows selected.

 

2.테스트 테이블 생성

 

SQL> CREATE TABLE EMP_TEMP_TEST
as SELECT * FROM EMP;

SQL> INSERT INTO EMP_TEMP_TEST
select * from EMP_TEMP_TEST; -- n번 수행

SQL> commit;

--확인
SQL> select bytes/1024/1024 as MB from dba_segments where segment_name='EMP_TEMP_TEST';

MB        
--------- 
     2714

1 rows selected.

 

3.생성 전, 생성 중, 생성 후 각 뷰 비교

세션을 하나 더 열어 인덱스 생성

SQL> create index test.IX_EMP_TEMP_TEST2 on test.EMP_TEMP_TEST(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO);
...일정 시간이 지난 후

--에러 발생
create index test.IX_EMP_TEMP_TEST2 on test.EMP_TEMP_TEST(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
                                            *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_YS

 

3-1)DBA_TEMP_FREE_SPACE 뷰 확인

FREE_SPACE가  1071644672 -> 214958080 -> 1073741824 (단위 bytes)로 생성이 실패하자 다시 FREE SPACE를 반환하여 커진 모습을 확인할 수 있습니다. 

SQL> select * from dba_temp_Free_space
where tablespace_name='TEMP_YS'

--생성 전
TABLESPACE_NAME                TABLESPACE_SIZE                ALLOCATED_SPACE                FREE_SPACE           SHARED        INST_ID        
------------------------------ ------------------------------ ------------------------------ -------------------- ------------- -------------- 
TEMP_YS                                              1073741824                      932184064              1071644672 SHARED           
--생성 중
TABLESPACE_NAME                TABLESPACE_SIZE                ALLOCATED_SPACE                FREE_SPACE           SHARED        INST_ID        
------------------------------ ------------------------------ ------------------------------ -------------------- ------------- -------------- 
TEMP_YS                                              1073741824                      932184064            214958080 SHARED                      


--생성 실패 후
TABLESPACE_NAME                TABLESPACE_SIZE                ALLOCATED_SPACE                FREE_SPACE           SHARED        INST_ID        
------------------------------ ------------------------------ ------------------------------ -------------------- ------------- -------------- 
TEMP_YS                                            1073741824                    1073741824          1070596096 SHARED

 3-2)V$TEMP_HEADER_SPACE 뷰 확인

해당 뷰는 TEMP 영역의 BLOCKS_FREE 영역이 생성 실패후 반환되지 않고 0으로 표시됩니다. 이유는 해당 뷰는 이 TEMP 영역의 HWM(High Water Mark)를 보여준다고 보면 되기 때문입니다. 비록 HWM은 제일 고수위에 설정되어 BLOCKS_FREE 영역이 0으로 보이지만 다른 뷰를 확인해보면 TEMP 영역은 반환된 것을 확인 할 수 있습니다.

SQL> select * from v$temp_space_header
where tablespace_name='TEMP_YS';


--생성 전
TABLESPACE_NAME                FILE_ID        BYTES_USED           BLOCKS_USED            BYTES_FREE           BLOCKS_FREE            RELATIVE_FNO             CON_ID       
------------------------------ -------------- -------------------- ---------------------- -------------------- ---------------------- ------------------------ ------------ 
TEMP_YS                                     2            932184064                 113792            141557760                  17280                        1            0


--생성 중
TABLESPACE_NAME                FILE_ID        BYTES_USED           BLOCKS_USED            BYTES_FREE           BLOCKS_FREE            RELATIVE_FNO             CON_ID       
------------------------------ -------------- -------------------- ---------------------- -------------------- ---------------------- ------------------------ ------------ 
TEMP_YS                                     2            932184064                 113792            141557760                  17280                        1            0


--생성 실패 후
TABLESPACE_NAME                FILE_ID        BYTES_USED           BLOCKS_USED            BYTES_FREE           BLOCKS_FREE            RELATIVE_FNO             CON_ID       
------------------------------ -------------- -------------------- ---------------------- -------------------- ---------------------- ------------------------ ------------ 
TEMP_YS                                     2              1.1e+09                 131072                    0                      0                        1            0

3-3)V$TEMP_EXTENT_POOL 뷰 조회

해당 뷰도 생성 중일때 EXTENTS_USED 가 818까지 올라갔지만, 실패 후 다시 반환하여 사용되는 EXTENTS_USED는 2로 줄어들어 공간이 반환된 것을 확인할 수 있습니다.

SQL> select * from v$temp_extent_pool
where tablespace_name='TEMP_YS';


--생성 전
TABLESPACE_NAME                FILE_ID        EXTENTS_CACHED               EXTENTS_USED             BLOCKS_CACHED              BLOCKS_USED            BYTES_CACHED             BYTES_USED           RELATIVE_FNO             CON_ID       
------------------------------ -------------- ---------------------------- ------------------------ -------------------------- ---------------------- ------------------------ -------------------- ------------------------ ------------ 
TEMP_YS                                     2                          886                        2                     113408                    256                929038336              2097152                        1            0


--생성 중

TABLESPACE_NAME                FILE_ID        EXTENTS_CACHED               EXTENTS_USED             BLOCKS_CACHED              BLOCKS_USED            BYTES_CACHED             BYTES_USED           RELATIVE_FNO             CON_ID       
------------------------------ -------------- ---------------------------- ------------------------ -------------------------- ---------------------- ------------------------ -------------------- ------------------------ ------------ 
TEMP_YS                                     2                          886                      818                     113408                 104704                929038336            857735168                        1            0

--생성 실패 후
TABLESPACE_NAME                FILE_ID        EXTENTS_CACHED               EXTENTS_USED             BLOCKS_CACHED              BLOCKS_USED            BYTES_CACHED             BYTES_USED           RELATIVE_FNO             CON_ID       
------------------------------ -------------- ---------------------------- ------------------------ -------------------------- ---------------------- ------------------------ -------------------- ------------------------ ------------ 
TEMP_YS                                     2                         1023                        2                     130944                    256                  1.1e+09              2097152                        1            0

 

결론은

DBA_TEMP_FREE_SPACE 뷰를 사용하여실시간으로 사용중인 TEMP 영역을 확인할 수 있으며, EXTENT 단위로 확인하려면 V$TEMP_EXTENT_POOL 뷰를 확인하면 됩니다. V$TEMP_HEADER_SPACE는 헤더 정보를 확인하는 뷰이므로 HWM를 확인하기엔 적합하지만 실시간으로 증감을 확인하기엔 적절하지 않습니다.

 

추가+) TEMP영역을 어떤 세션이 사용하는지 확인하는 쿼리

 

출처 : https:https://positivemh.tistory.com/260//positivemh.tistory.com/260

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

 

DBA_TEMP_FREE_SPACE를 활용한 실시간 TEMP영역 사용량 확인 쿼리

 

SELECT TABLESPACE_NAME
      ,ROUND(ALLOCATED_SPACE/1024/1042/1024,2) AS ALLOC_GB
      ,ROUND((ALLOCATED_SPACE-FREE_SPACE)/1024/1024/1024,2) AS USED_GB
      ,ROUND(FREE_SPACE/1024/1024/1024,2) AS FREE_GB
      ,ROUND((ALLOCATED_SPACE-FREE_SPACE)/ALLOCATED_SPACE*100,2) AS "USED(%)" 
FROM DBA_TEMP_FREE_SPACE;