Oracle temp 테이블스페이스를 사용하는 프로세스/쿼리 확인

Oracle temp 테이블스페이스를 사용하는 프로세스/쿼리 확인

Oracle FAQ는 다음과 같이 임시 테이블 공간을 정의합니다.

임시 테이블스페이스는 데이터베이스 정렬 작업을 위한 공간을 관리하고 전역 임시 테이블을 저장하는 데 사용됩니다.예를 들어 두 개의 큰 테이블을 조인할 때 Oracle이 메모리에서 정렬을 수행할 수 없는 경우 정렬 작업을 수행하기 위한 임시 테이블 공간에 공간이 할당됩니다.

잘됐네요, 하지만 정확히 무엇이 공간을 사용하고 있는지 더 자세히 알고 싶어요.응용프로그램 설계의 문제로 인해 대부분의 쿼리는 어떤 종류의 정렬을 수행하므로 클라이언트 실행 파일, 대상 테이블 또는 SQL 문으로 범위를 좁힐 필요가 있습니다.

기본적으로, 저는 이것이 무엇이 잘못되었는지 더 정확하게 알려줄 단서를 찾고 있습니다(비교적 큰 응용프로그램)."정렬"보다 더 정확하다면 어떤 종류의 단서라도 유용할 수 있습니다.

이미 어떤 정보를 전달해야 하는지는 잘 모르겠지만 다음 쿼리를 사용하면 현재 어떤 프로그램/사용자/세션 등이 임시 공간을 사용하고 있는지 알 수 있습니다.

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

손상을 초래하는 세션을 확인한 후에는 실행 중인 SQL을 확인하십시오. 그러면 올바른 경로를 선택해야 합니다.

마이클 오쉬아의 답변에 감사드립니다.

하지만 Oracle RAC 인스턴스가 여러 개 있는 경우에는 이 기능이 필요합니다.

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.inst_ID
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP2'
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

그리고 이 스크립트는 킬 문을 생성합니다: 어떤 세션을 죽일 것인지 검토하십시오...

SELECT  b.TABLESPACE, a.username , a.osuser , a.program , a.status ,
       'ALTER SYSTEM KILL SESSION '''||a.SID||','||a.SERIAL#||',@'||a.inst_ID||''' IMMEDIATE;'
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP'
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

일반적으로 1초 이상 걸리는 거의 모든 쿼리는 일부 TEMP 공간을 사용할 수 있으며, 이들은 ORDER BY뿐만 아니라 다음과 같은 작업도 포함합니다.

1. GROUP BY(10.2 이전의 GROUP BY 정렬, 10.2 이후의 해시 GROUP BY 정렬)
2. 해시 조인 또는 병합 조인
3. 글로벌 온도 테이블(분명)
4. 인덱스 재구성

임시 테이블스페이스의 사용된 공간이 Oracle(bug/quirk)에 의해 해제되지 않는 경우가 있으므로 테이블스페이스에서 파일을 수동으로 삭제하고 파일 시스템에서 삭제한 후 다른 파일을 만들어야 합니다.

언급URL : https://stackoverflow.com/questions/174727/discover-what-process-query-is-using-oracle-temp-tablespace