Oracle DB: Временное табличное пространство

Материал из sysadm
Перейти к навигации Перейти к поиску

Oracle Database / Oracle DB: Обзор

Обзор

Особенности временного табличного пространства:

  • не логируется, т.е. изменения не пишутся в redo-лог
  • не нуждается в восстановлении (recovery) и может быть пересоздано
  • содержит данные, которым не нужна целостность чтения (read-consistency)
  • выделение или расширение сегмента не требует записи о выделении экстентов в словарь данных, также не требуется запись о выделении в файловые битовые карты.
  • когда данные данные перечисленных ниже операций не помещаются целиком в PGA, Oracle разбивает эти данные на части и начинает их сохранять на диск во временное табличное пространство
    • данные сортировки (sort)
    • данные хеширования (hash join)
    • данные глобальной временной таблицы
  • нельзя размещать обычные сегменты такие как таблицы или индексы (кроме глобальной временной таблицы)
  • сегмент сортировки является единственным во временном табличном пространстве, принадлежит пользователю SYS и используется всеми сессиями для размещения данных сортировки

Если для параметра WORKAREA_SIZE_POLICY установлено значение AUTO, то параметр PGA_AGGREGATE_TARGET указывает, какой объем памяти может совместно использоваться всеми сеансами для таких действий, как сортировка и хеширование. Oracle автоматически оценивает и решает, какой объем этой памяти должен быть разрешен любому отдельному сеансу.

Если для параметра WORKAREA_SIZE_POLICY установлено значение MANUAL, то параметры экземпляра, такие как SORT_AREA_SIZE, HASH_AREA_SIZE и BITMAP_MERGE_AREA_SIZE, определяют объем памяти, который каждый сеанс может использовать для этих операций.

Каждому пользователю базы данных назначается временное табличное пространство, или группа временных табличных пространств (Oracle 10g+). Атрибут TEMPORARY_TABLESPACE в представлении dba_users отображает какое временное табличное пространство или их группа назначено пользователю. Все операции пользователя используют назначенное ему временное табличное пространство или их группу.

Один SQL оператор может создать несколько сортировок в сегменте сортировки. В одной сессии может одновременно работать несколько таких SQL операторов. Когда используемые сортировкой блоки более не нужны, они освобождаются и могут быть использованы другими сортировками.

Операция сортировки завершится сбоем, если не получится выделить достаточно свободного места:

  1. недостаточно неиспользуемых блоков в сегменте сортировки
  2. не удается расширить сегмент сортировки новым экстентом

Как определить причину переполнения временного табличного пространства

  1. Когда не удается расширить временный сегмент в алерт-лог пишется сообщение ORA-1652: unable to extend temp segment
  2. Необходимо обратить внимание в каком табличном пространстве это произошло, т.к. некоторые типы временных сегментов выделяться в постоянных табличных пространствах (см. Oracle DB: Временные сегменты в постоянных табличных пространствах)
  3. Сообщение ORA-1652 не содержит информацию об источнике вызвавшем переполнение, поэтому для получения полной информации необходимо предварительно включить трассировку данных сообщений
    • это можно сделать в текущей сессии
      • включение ALTER SESSION SET EVENTS '1652 trace name errorstack';
      • выключение ALTER SESSION SET EVENTS '1652 trace name context off';
    • либо на уровне базы данных
      • включение ALTER SYSTEM SET EVENTS '1652 trace name errorstack';
      • выключение ALTER SYSTEM SET EVENTS '1652 trace name context off';
      • если требуется чтобы данная трассировка не выключалась при перезагрузке инстанции, то необходимо добавить в команду SCOPE=SPFILE
  4. Необходимо учитывать, что ошибка ORA-1652 говорит лишь о том что данному запросу не хватило места, но это не является основанием считать, что именно он занял все место.

Примечание: поддержка Oracle не рекомендует включать трассировку для событий, если нет прямой рекомендации поддержки Oracle. Включение трассировки некоторых событий может создать серьезные проблемы. Относительно события ORA-1652 прямой рекомендации нет, но есть мнение что это относительно безопасно.

Для точного определения источника переполнения можно использовать запросы ниже. Их необходимо запускать в момент переполнения или использовать в автоматических заданиях мониторинга использования временного табличного пространства. 

-- информация об использовании временного табличного пространства
SELECT * FROM DBA_TEMP_FREE_SPACE;

-- информация о сегменте сортировки
SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
     (SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
      FROM     v$tablespace B, v$tempfile C
      WHERE    B.ts#= C.ts#
      GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

-- размеры сортировок по сессиям
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
       S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
       COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
      AND S.paddr = P.addr
      AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

-- размеры сортировок по операторам
SELECT S.sid || ',' || S.serial# sid_serial, S.username,
       T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
       T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
      AND T.sqladdr = Q.address (+)
      AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

Глобальные временные таблицы

Когда постоянная таблица обновляется, она создает довольно много накладных расходов, генерирует undo и redo. Часто требуется таблица, содержащая временные результаты операции, которые не нужно сохранять между сеансами или перезапусками базы данных. Глобальные временные таблицы очень хороши для этой роли, но во временном табличном пространстве должно быть достаточно свободного места.

Примечание: глобальная временная таблица изначально не использует PGA, даже небольшие объемы сразу пишутся во временное табличное пространство (проверено на Oracle 12.1.0.2)

Пример создания временной глобальной таблицы 

create global temporary table work_temp (master_id number) on commit delete rows tablespace temp;

Переменные PL/SQL могут содержать наборы записей, похожие на временную таблицу, но они могут храниться только в PGA, поэтому для больших объемов они не подходят.

Подробнее см. здесь Oracle DB: Временные таблицы

Управление временными табличными пространствами

Администратор базы данных может расширять или усекать размер временных табличных пространств, а также назначать пользователям разные временные табличные пространства.

Пример создания временного табличного пространства 

create temporary tablespace MYTEMP datafile '/path/to/mytemp01.dbf' size 1G autoextend on maxsize unlimited;
alter tablespace MYTEMP datafile '/path/to/mytemp02.dbf' size 1G autoextend on maxsize unlimited;

Если включен OMF (Oracle Management File) то путь к файлу можно не указывать, путь и имя файла будут назначены автоматически.

Если одна учетная запись или группа учетных записей часто захватывают все временное табличное пространство, то имеет смысл выделить для них отдельное временное табличное пространство, чтобы они не создавали проблемы остальным пользователям.

Пример изменения временного табличного пространства для пользователя 

alter user myuser temporary tablespace newtemp;

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

Примеры 

--  проверка текущего размера и свободного места
select * from DBA_TEMP_FREE_SPACE;

-- максимальное усечение файлов данных
alter tablespace temp shrink space;

-- усечение до заданного размера
alter tablespace temp shrink space keep 1G;

-- удаление лишнего файла данных временного табличного пространства
alter tablespace tempfile ‘/path/to/temp03.dbf’ drop including datafiles;

Ссылки по теме

Источник — https://sysadm.mooo.com/index.php?title=Oracle_DB:_Временное_табличное_пространство&oldid=2900