Борьба с цепочками записей

ПиЛ
Дата: 06.03.2007 09:30:48
Борьба с цепочками и мигрирующими записями.

Присутствие в таблице цепочек записей, переходящих из блока в блок,
или мигрирующих записей (записи, перенесенные в другой блок с сохранением
старого ROWID) замедляет выполнение запросов. Такие ситуации возникают,
если в блоке не хватает места для размещения записи после корректировки.
Документация ORACLE предлагает 10-шаговый сценарий для преобразования таких записей с созданием временной таблицы, куда выбираются эти записи. Затем они удаляются из основной таблицы и вновь заносятся в нее из временной таблицы, после чего временная таблица удаляется.
Но удаление (даже временное) и вставка строк – вопрос скользкий, могут
сработать связанные с таблицей триггеры.
Я выполняю эту операцию в три шага с полной реорганизацией таблиц.

Среда : Oracle 9.2.0.8, Linux SuSE Ent 9, SHELL = tcsh

Все дальнейшее возможно только, если Вы собираете для оптимизации запросов статистику по таблицам (предложением ANALYZE или пакетом DBMS_STAT),

Шаг1. Обнаружение наличия цепочек.
Выполняется на LINUX-сервере из командной строки.

Скриптом chain_cnt определяю имена таблиц, затронутые этим недугом.

Скрипт chain_cnt:

       sqlplus –s "/as SYSDBA" << konec
         set pagesize 80
         set feed off
         spool chain_cnt 
         select owner, table_name, chain_cnt from all_tables
           where nvl(chain_cnt,0) <> 0
           order by chain_cnt desc
           ;
        konec

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

   OWNER                             TABLE_NAME                         CHAIN_CNT        
     ------------------------------ ------------------------------ ----------        
     SHEMA_1                        TABLE_1                               447        
     SHEMA_2                        TABLE_3                               127        

и т.п.


Шаг 2. Выполняется на PC( Win-Xp, DBArt).
.
Артизаном (мне так удобней) изменяю для таких таблиц параметры
PCTFREE (увеличиваю) и PCTUSED (уменьшаю) – т.е. расширяю пространство
в блоке таблицы для будущих корректировок записей.


Шаг 3. На LINUX-сервере из командной строки
запускаю скрипт reorg_tables.

Скрипт формирует операторы для реорганизации таблиц, в которых
обнаружены цепочки и помещает их в файл reorg, который затем выполняется.
При выполнении скрипта reorg реорганизуются таблицы, перестраиваются
индексы этих таблиц и выполняется ANALYZE для таблиц.

Скрипт reorg_tables:

    sqlplus -s “/as SYSDBA” << konec

   set serveroutput on size 1000000 format wrapped
   set verify    off
   set heading off
   set pages 1000
   set linesize 150
   set feed off

   spool reorg

   declare
     OWN1 varchar2(10);
     TN1  varchar2(20);

   cursor c1 is
     select TABLE_NAME TN, OWNER OWN,
       'alter table '||OWNER||'.'||replace(TABLE_NAME,'\$','\\$')||' move;' stroka_1
          from ALL_TABLES 
          where nvl(chain_cnt,0) <> 0 
      ;
   cursor c2 is   
    select INDEX_NAME IN1,
      ' alter index '||TABLE_OWNER||'.'||replace(index_name,'\$','\\$') ||
                                           ' rebuild;' stroka_2
          from ALL_INDEXES 
          where TABLE_OWNER=OWN1 and TABLE_NAME=TN1
    ;
   begin
     dbms_output.put_line(''); 
     dbms_output.put_line('# ТЕКСТ СГЕНЕРИРОВАННОГО СКРИПТА'); 
     dbms_output.put_line(''); 
     dbms_output.put_line('sqlplus -s “/as SYSDBA” << konec');
     dbms_output.put_line('spool reorg');
     dbms_output.put_line('pro'); 
     dbms_output.put_line('pro В Ы П О Л Н Н И Е'); 

     for r1 in c1 loop
       dbms_output.put_line('pro'); 
       dbms_output.put_line('pro ТАБЛИЦА ' || r1.TN); 
       dbms_output.put_line(r1.stroka_1);
       OWN1 := r1.OWN;
       TN1  := r1.TN;
       for r2 in c2 loop
         dbms_output.put_line('pro   ИНДЕКС ' || r2.IN1);
         dbms_output.put_line(r2.stroka_2);
       end loop;
       dbms_output.put_line('  analyze table             
                '||OWN1||'.'||replace(TN1,'\$','\\$') || 
         ' estimate statistics sample 20 percent;');
     end loop;  
     dbms_output.put_line('quit;');
     dbms_output.put_line('konec');
   end;
  /
  quit;
  konec

  mv reorg.lst reorg
  chmod 766 reorg
    reorg
Сформированный файл reorg для приведенного выше случая цепочек выглядит так:

# ТЕКСТ СГЕНЕРИРОВАННОГО СКРИПТА

sqlplus -s “/as SYSDBA” << konec
spool reorg
pro
pro В Ы П О Л Н Е Н И Е
pro
pro ТАБЛИЦА TABLE_1
alter table SHEMA_1.TABLE_1 move;
pro   ИНДЕКС I1_ТABLE_1
 alter index SHEMA_1.I1_TABLE_1 rebuild;
pro   ИНДЕКС I2_TABLE_1
 alter index SHEMA_1.I2_TABLE_1 rebuild;
  analyze table SHEMA_1.TABLE_1 estimate statistics sample 20 percent;
pro
pro ТАБЛИЦА TABLE_3
alter table SHEMA_2.TABLE_3 move;
pro   ИНДЕКС I1_ТABLE_3
 alter index SHEMA_2.I1_TABLE_3 rebuild;
  analyze table SHEMA_2.TABLE_3 estimate statistics sample 20 percent;
quit;
konec
При выполнении этого скрипта вывод выглядит таким образом:

В Ы П О Л Н Е Н И Е

ТАБЛИЦА TABLE_1
Table altered.
ИНДЕКС I1_TABLE_1
Index altered.
ИНДЕКС I2_TABLE_1
Index altered.
Table analyzed.

ТАБЛИЦА TABLE_3
Table altered.
ИНДЕКС I1_TABLE_3
Index altered.
Table analyzed.
Через некоторое время (день-два) после выполнения описанного цикла работ
надо повторить шаг1 для проверки – не появились ли вновь цепочки.
Изменяя параметры и реорганизуя таблицы, в несколько этапов можно добиться полного
устранения цепочек. (Конечно, если длина записи меньше длины блока).
Скрипты можно запускать и от имени SYSTEM,
немного переделав. Можно добавить условия обработки
только схем пользователей, чтобы исключить системные таблицы.
===================================================
Эксплуатирую на двух серверах в трех базах. Пока все нормально.
Приму любые замечания на эту тему.