Раздуваем таблицы и пожираем tablespaces

[cawaleb]

Но есть в большинстве этих статей общая черта. Когда данные спасены (или уничтожены, как получится), предлагается победить поврежденный free block захватом всего свободного места в tablespace.

На самом деле уже не важно, что будет с Tablespace, когда данные спасены. Только время простоя.
А по сути статьи, я все таки предпочитаю иметь копию и возможность восстановить повреждённый блок с помощью Rman.
Не критичные неточности, которые увидел при беглом прочтении: в команде alter table allocate extent можно указывать размер экстента, возможно будет быстрее, а так же DDL не требует commit.

[Vantela]

Спасибо! Попробую.
Я пробовал играться с параметрами создания таблицы, но оракл их воспринимает как рекомендации и все равно делает как хочет.

[shane54]

Я пробовал играться с параметрами создания таблицы, но оракл их воспринимает как рекомендации и все равно делает как хочет.

Обратите внимание на параметры, с которыми создано табличное пространство — ASSM (Automatic Segment Space Management) или MSSM (Manual Segment Space Management).

[Vantela]

Эх. Если бы все в этой жизни зависело от нас.:)
TS у меня ASSM по умолчанию. Их так создает SAP.

[Vantela]

Коммиты убрал — действительно лишние! Еще раз спасибо.
Как то стал писать execute совместно с commit — так и повелось.

[shane54]

Неплохо для «не-DBA». Потому что во всех мануалах предлагается создавать «big table» методом CTAS до тех пор, пока нужные блоки не затрутся (читай — не переформатируются). И эти советы основаны на идее, что Оракл переформатирует блок при первом обращении. Ваш же способ быстрее в N-раз, т.к. нет нужды читать исходную таблицу и писать в результирующую. Специально посмотрел в документации:

If you allocate an extent to a specific instance, the blocks are immediately allocated to the free list.

[Vantela]

Да!
CTAS я тоже пробовал. На том же оборудовании, той же базе — таблички надувались в течении почти часа для почти 100гигов.

[xtender]

А теперь попробуйте с initial сразу нужного размера( не забудьте отключить deffered segment creaton)

[xtender]

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

[Vantela]

Не успел ответить, вы сами исправились. :)

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

И вот как сверху советовали alter table allocate extent (size… ); я тоже попробовал.
Проблема та же. Муторно подбирать размер который оно проглотит. А писать прогу которая сначало будет вычислять максимальный размер на который можно расшириться, а потом постепенно понижать — глупо как то…
Сложность и размер скрипта сильно возрастет, а что мы выйграем? 45 секунд из 150?

[xtender]

Ну вообще-то я попроще имел ввиду — через dba_free_space

[Vantela]

Я вот о чем:

ALTER SYSTEM set DEFERRED_SEGMENT_CREATION=false;

Затем:

SELECT round(sum(bytes)/1048576,2) from DBA_FREE_SPACE where TABLESPACE_NAME = 'PSAPSR3702';

Результат: 10668,75

И после этого:

create table SAPSR3.TESTTABLE (id number(10), USER_NAME varchar2(10), CREATE_DATE date) tablespace PSAPSR3702
storage    (
            initial          10570M
            NEXT             10M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            );

Не проходит. И, разумеется, большие размеры, которые ближе к максимальному, тоже не катят.
Жалуется на ORA-01659(Failed to find sufficient contiguous space to allocate MINEXTENTS for the segment being created.)

Вот размер 10500M создать можно.
А сидеть и подбирать этот размер на который оно таки может создаться — мне видится глупым.

Или я не так все таки понял?

[xtender]

Неправильно, не suM(bytes) надо, а max(bytes). Вообще эта вьюха дает размеры свободных кусков, поэтому в принципе их можно просто перебрать по уменьшению

[Vantela]

Хм. Спасибо! Попробую так.

[Vantela]

Не получается.:( А очень жаль.
Т.е. написал прогу которая по max(bytes) создает с таким начальным екстентом табличку — и для 100G свободных шикарно отработало за несколько секунд.
Но получилось не универсально.

Вот для другого TS со свободным размером чуть меньше гига вручную повторил:

alter system set DEFERRED_SEGMENT_CREATION=false;

system SET altered.

SELECT max(bytes) from DBA_FREE_SPACE where TABLESPACE_NAME = 'PSAPSR3USR';

868089856

create table SAPSR3.TESTTABLE (id number(10), USER_NAME varchar2(10), CREATE_DATE date) tablespace PSAPSR3USR
storage ( initial 868089856);

И уппс: ORA-01659: не могу выделить MINEXTENTS выше 19 в разделе PSAPSR3USR

Пока думаю с чем такое может быть связано. Свободный кусок есть… но не совсем свободный.

[xtender]

Думаю, нужно уменьшить блока на 4 и снова попробовать. Посмотри тут: jonathanlewis.wordpress.com/2013/02/25/free-space-2/

[Vantela]

SELECT max(bytes)/1024 from DBA_FREE_SPACE where TABLESPACE_NAME = 'PSAPSR3USR';

847744

И максимальное количество килобайт с которым таблица создалась: 844800
Пришлось отступить на 368 блоков…

[xtender]

А в dba_recyclebin ничего не лежит случайно из этого ts?

[Vantela]

Не, корзина вообще выключена.

[xtender]

Ну тогда весьма и весьма занятно… После создания таблицы остался кусок свободный с этими 368 блоками в dba_free_space? Попробуй сдампить. И на всяк пожарный и по dba_extents посмотреть было бы нужно по всему этому диапазону. Для удобства и более быстрого общения можно мне по мылу это прислать

[Vantela]

Все хитрее:) Остаются два куска.

После создания таблички 844800К делаем:

SELECT bytes/1024 from DBA_FREE_SPACE where TABLESPACE_NAME = 'PSAPSR3USR';

896
2048

И вот с этими размерами уже можно создать две таблицы и они реально покроют всю TS.
Покопаться в dba_extents пока не получилось. Обращение долгое, а мне пора бежать… Завтра продолжу.

Что и как дампнуть не понял.:(

Да, ладно зачем уходить в приват. Я столько полезностей вычитал из подобных обсуждений:)

[xtender]

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

[Vantela]

Размер блока 8K
Т.е. вначале было 105968 блоков, но сразу все сожрать нельзя.
А после выделения 105600 остается 112 и 256 блоков. И их уже можно до полного заполнения.

Ну и alter table SAPSR3.TESTTABLE allocate extent(size ...); разумеется тоже не проходит. Жаль, очень бы быстро отрабатывало.