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

Question

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

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

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

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

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

Answer 2

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

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

и этот скрипт для генерации операторов 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;

Answer 3

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

1. GROUP BYs (сортировка GROUPBY до 10.2 и HASH GROUPBY с 10.2 и далее)
2. HASH JOINS или MERGE СОЕДИНЕНИЯ
3. Глобальные временные таблицы (очевидно)
4. Индекс перестраивается

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