Узнайте, какой процесс/запрос использует табличное пространство oracle temp

Oracle FAQ определяет временное табличное пространство следующим образом:

Временные табличные пространства используются для управлять пространством для сортировки базы данных операций и для хранения глобальных временные таблицы. Например, если вы объединить две большие таблицы и Oracle не может выполнять сортировку в памяти, пространстве будут распределены во временном табличное пространство для сортировки операции.

Это здорово, но мне нужно больше подробностей о том, что именно использует пространство. Из-за особенностей дизайна приложения большинство запросов выполняют некоторую сортировку, поэтому мне нужно сузить ее до исполняемого файла клиента, целевой таблицы или оператора SQL.

По сути, я ищу подсказки, чтобы рассказать мне более точно, что может быть неправильно с этим (довольно большое приложение). Любой вид подсказки может быть полезен, если он более точен, чем "сортировка".

Ответ 1

Я точно не знаю, какую информацию вам нужно передать, но используя следующий запрос, укажите, какая программа/пользователь/сеансы и т.д. в настоящее время использует ваше временное пространство.

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 и вы должны быть на правильном пути.

Ответ 2

Одно эмпирическое правило состоит в том, что почти любой запрос, который занимает больше секунды, вероятно, использует некоторое пространство TEMP, и это не единственные с участием ORDER BY, но также:

GROUP BYs (SORT GROUPBY до 10.2 и HASH GROUPBY с 10.2 и далее)
ВСТРОЕННЫЕ СОЕДИНЕНИЯ ИЛИ СООТВЕТСТВИЕ СООБЩЕНИЯ
Таблицы глобальных тем (очевидно)
Восстановление индексов

Иногда используемое пространство в temp tablespaces не освобождается Oracle (ошибка/quirk), поэтому вам нужно вручную удалить файл из табличного пространства, вынуть его из файловой системы и создать еще один.

Ответ 3

Спасибо за Майкла Ошеа за его ответ,

но если у вас есть несколько экземпляров 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;

а это script для генерации операторов kill: Пожалуйста, просмотрите, какие сессии вы будете убивать...

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;