Temporary Tablespace Group은 잘 알려지지 않는 오라클 기능 중 하나로, Oracle 10g에 추가된 기능이다.


일반적으로 temporary tablespace는 유저에게 지정하여, 특정 유저는 단 하나의 temporary tablespace만을 사용할 수 있었으나, Temporary Tablespace Group은 유저에게 임시 테이블스페이스 그룹을 지정하여 동시에 여러개의 임시 테이블스페이스를 사용하여 정렬 등의 작업 효율을 향상시키는 기능이다.



특징

  • 하나 이상의 temporary tablespace 구성
  • temporary tablespace로만 구성됨.
  • 명시적으로 생성하지 않고, 첫번째 temporary tablespace 할당되면 암시적으로 그룹이 생성되고,
  • 마지막 temporary table space 그룹에서 제거되면 삭제됨.


장점

  • 데이터베이스 레벨에서 여러 개의 default temporary tablespace 지정할 있다.

  • 사용자는 동시에 여러 세션에서 여러 개의 temporary tablespace 사용할 있다.

  • 단일 SQL 조작으로 여러 개의 temporary tablespace 사용하여 정렬할 있다.


->  하나의 임시 파일에 비해 여러 파일에 I/O 분산시킴으로써 정렬, hash join 등의 작업에 상당한 성능 향상을 기대할 수 있음



추가사항

  • temporary tablespace group에 추가 있는  temporary tablespace 의 개수 제한은 없다.
  • 하나의  temporary tablespace 는 하나의  temporary tablespace group에만 속할 있다.

  • temporary tablespace group 모든 정렬 문제를 개선한다.


예제



  • 기존에 생성된 임시 테이블스페이스를 그룹에 할당(그룹 생성)

ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;


  • 새로운 임시 테이블스페이스를 생성하여 그룹에 추가

CREATE TEMPORARY TABLESPACE temp2

  TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M

  TABLESPACE GROUP temp_ts_group;


  • 할당된 임시 테이블스페이스그룹을 조회할 수 있는 dba_tablespace_groups 딕셔너리 

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

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

TEMP_TS_GROUP                  TEMP

TEMP_TS_GROUP                  TEMP2

2 rows selected.



그룹이 생성되면 테이블스페이스와 동일하게 그룹 자체를 유저나 디폴트 임시 테이블스페이스로 지정이 가능하다. 


  • 사용자에게 임시 테이블스페이스그룹을 할당

ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;


  • 디폴트 임시 테이블스페이스로 그룹을 할당

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;


  • 그룹에서 테이블스페이스 제거

ALTER TABLESPACE temp2 TABLESPACE GROUP '';


SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

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

TEMP_TS_GROUP                  TEMP

1 row selected.




★★★ 참고자료


아래 사이트는 temporary tablespace group과 하나의 임시 테이블스페이스 파일을 사용했을때의 성능 비교 자료를 제공.

https://dbakevlar.com/2011/08/warp-speed-with-temp-tablespace-groups/


요약한다면... 

temporary tablespace group은 하나의 temporary tablespace 만을 사용할때 보다 reads : 66% 성능향상, writes - 29% 성능향상이 됨.


※ temporary tablespace group을 구성할때 각각의 temporary tablespace 파일 크기를 동일하게, 위치는 다르게  위치시키는게 I/O 분산 효과가 더 좋다.


이전 글 Real-Time SQL Monitoring - v$sql_plan_mointor 조회하기 ( http://overoid.tistory.com/40?category=357137 ) 에서 사용된 SQL의 경우. LONG SQL 수행 후 문제가 있어 중지하고, 재 수행했다면... 이전에 수행했던 실행계획이 겹쳐서 나오는 단점이 있다.

대신 쿼리가 심플하고 Version 2에 비해서 조회 성능이 더 우수하다. 


Version 1의 중복 이슈를 해결한 Ver2 - v$sql_plan_mointor 조회 쿼리.


WITH SQL_PLAN_INF AS (

    SELECT /*+ materialize ordered */

           SQL_ID, PLAN_LINE_ID, STATUS, PROCESS_NAME, PLAN_DEPTH, PLAN_OPERATION, PLAN_OPTIONS, PLAN_OBJECT_OWNER, PLAN_OBJECT_NAME, OUTPUT_ROWS, LAST_CHANGE_TIME, FIRST_CHANGE_TIME, SQL_EXEC_START

      FROM V$SQL_PLAN_MONITOR A

     WHERE SQL_ID IN (SELECT SQL_ID

                        FROM (SELECT SQL_ID, ROW_NUMBER() OVER(ORDER BY SQL_EXEC_START DESC) AS RN

                               FROM V$SESSION

                               WHERE MODULE = :module_name)

                       WHERE RN = 1

                      )

       AND STATUS = 'EXECUTING'

),

SQL_PLAN_INFO AS (

    SELECT /*+ materialize */* FROM (

        SELECT A.*, RANK() OVER(PARTITION BY SQL_ID ORDER BY SQL_EXEC_START DESC) AS RN

          FROM SQL_PLAN_INF A


      ) X

     WHERE RN = 1

)

SELECT  LPAD(' ', a.plan_DEPTH * 4 ,' ')||A.plan_OPERATION||' '||a.PLAN_OPTIONS, PLAN_OBJECT_OWNER,PLAN_OBJECT_NAME,  B.OUTPUT_ROWS,B.LAST_CHANGE_TIME, B.FIRST_CHANGE_TIME,

        ROUND(((B.LAST_CHANGE_TIME - B.FIRST_CHANGE_TIME)*24*60*60),2)  AS ELAPSED

  FROM SQL_PLAN_INFO A

     ,( SELECT PLAN_LINE_ID,SQL_ID,SUM(OUTPUT_ROWS) OUTPUT_ROWS,MAX(LAST_CHANGE_TIME) LAST_CHANGE_TIME, MIN(FIRST_CHANGE_TIME) AS FIRST_CHANGE_TIME

          FROM SQL_PLAN_INFO

         WHERE 1=1

          AND STATUS = 'EXECUTING'

          AND PROCESS_NAME <> 'ora'

        GROUP BY PLAN_LINE_ID,SQL_ID

       ) B

 WHERE A.SQL_ID = B.SQL_ID

   AND A.PLAN_LINE_ID = B.PLAN_LINE_ID

   AND A.PROCESS_NAME = 'ora'

ORDER BY A.PLAN_LINE_ID



사용법 및 설명은 이전글(http://overoid.tistory.com/40?category=357137 )을 참고하자.



오래동안 수행되는 SQL이 얼마나 진행되었는지 알 수 있을까?

예상실행계획으로 확인된 실행계획 그대로 수행되고 있는지를 쿼리 수행중에 확인할 수 있을까?


수억에서 수십, 수백억 레코드를 조인하여 처리하는데.. 언제 끝날지 모른다면 정말로 답답하다. 

수분에서 수십, 수백분이 소요되는  SQL 대량 작업의 경우 잘못된 실행계획으로 쿼리가 실행되고 있다는 것을 실행 초반에 알 수 있다면, 

SQL을 중지하고 수정하여 재실행함으로써 소중한 시간을 아끼고 일찍 퇴근할 수도 있을것이다.  



다행히 오라클에서는 11g부터 Real-Time SQL Monitoring 기능을 제공한다.

물론 쿼리가 수행완료된 이후 dbms_sqltune.report_sql_monitor 로 레포팅을 뽑아 결과를 확인할 수 도 있지만, 

실행중에도 v$sql_mointor, v$sql_plan_mointor 에 값이 변경되므로, 이 뷰를 잘 조회하면 얼마나 실행되었는지? 어떤 실행계획으로 실행되고 있는지 쉽게 파악할 수 있다.



SELECT LPAD(' ', a.plan_DEPTH * 4 ,' ')||A.plan_OPERATION||' '||a.PLAN_OPTIONS plan_text, 

       PLAN_OBJECT_OWNER,PLAN_OBJECT_NAME,  

       B.OUTPUT_ROWS,B.LAST_CHANGE_TIME,  B.FIRST_CHANGE_TIME,

       ROUND(((B.LAST_CHANGE_TIME - B.FIRST_CHANGE_TIME)*24*60*60),2)  AS ELAPSED

FROM V$SQL_PLAN_MONITOR A,

    (

    SELECT PLAN_LINE_ID,SQL_ID,SUM(OUTPUT_ROWS) OUTPUT_ROWS,

           MAX(LAST_CHANGE_TIME) LAST_CHANGE_TIME, 

           MIN(FIRST_CHANGE_TIME) AS FIRST_CHANGE_TIME

     FROM V$SQL_PLAN_MONITOR a

     WHERE 1=1

       AND STATUS = 'EXECUTING'

       AND PROCESS_NAME <> 'ora'

    GROUP BY PLAN_LINE_ID,SQL_ID

    ) B

WHERE A.SQL_ID = B.SQL_ID

  AND A.PLAN_LINE_ID = B.PLAN_LINE_ID

  AND A.PROCESS_NAME = 'ora'

  AND A.SQL_ID IN (SELECT SQL_ID  FROM V$SESSION

                    WHERE MODULE = :module_name

                      AND STATUS = 'ACTIVE'

                   )

ORDER BY  A.PLAN_LINE_ID

;



:module_name에 원하는 모듈을 입력하거나, 해당 부분을 AND A.SQL_ID = :sql_id로 수정하여 sql_id를 직접 입력하면 된다.


sql_id는 어떻게 찾느냐고? 

v$sql을 조회해보자.

일반적으로는 SQL 작성시 SQL을 유일하게 구별할 수 있는 ID를 주석으로 기술한다. 

그 텍스트로 select sql_id from v$sql where sql_text like '%찾는문자열%'로 조회하면 sql_id를 알 수 있다.




아래 그림은 위 SQL 실행결과의 일부분이다. 

이쿼리를 2~3초에 한번씩 실행하면 실행계획을 확인할 수 있으며, 실행계획 순서에 따라서 OUTPUT_ROWS에 처리되고 있는 레코드 값이 변경되는 것을 확인할 수 있다. 그 부분이 현재 수행되고 있는 부분이다.

또한 보여주는 실행계획이 현재 수행중인 실행계획이므로, 쿼리 수행전 확인한 예상실행계획과 동일하게 실행되고 있는지 확인하면 된다.




Real-Time SQL Monitoring(v$sql_mointor, v$sql_plan_mointor)은 Oracle 11g에서 추가된 기능으로 아래 조건에서 모니터링 된다.


  • 실행시간이 5초 ( '_sqlmon_threshold' 히든 파라미터로 조정)이상 느린 쿼리의 실행이력
  • 병렬 쿼리의 실행 이력
  • monitor 힌트를 사용한 쿼리에 대한 추적



위 조건을 만족하는데도 모니터링 되지 않는 다면????,  v$sql_monitor에 나타나지 않는다면????



만일, 모니터링 하는 SQL 문장의 실행계획 라인수가 300 라인이 넘는 복잡한 쿼리는 모니터링에서 누락된다.


오라클 히든 파라미터 중 '_sqlmon_max_planlines'=300 으로 초기 설정되어 있다.

이 값을 늘리면 문제 해결!!!



SQL> alter system set "_sqlmon_max_planlines"=800 scope=both;




[참고]


1. 모니터링된 SQL은  dbms_sqltune.report_sql_monitor로 레포팅

    

select dbms_sqltune.report_sql_monitor(sql_id=>'bpfu171khfbg6') from dual



2. 오라클 히든 파라미터 조회 (Oracle Hidden Parameter View)


select a.ksppinm name, b.ksppstvl value,b.ksppstdf deflt,

decode (a.ksppity, 1, 'boolean',

                2, 'string',

                3, 'number',

                4, 'file',

                a.ksppity) type,

a.ksppdesc description

from sys.x$ksppi a, sys.x$ksppcv b

where a.indx = b.indx and a.ksppinm like '\_%' escape '\'

order by name


+ Recent posts