воскресенье, 21 марта 2010 г.

Фрагментация таблиц в Oracle

В статье рассказано о фрагментации таблиц. Представлен пример и предложено 4 варианта решения проблемы.


Содержание
  1. Введение
  2. Как найти фрагментацию таблицы?
  3. Как сбросить HWM/убрать фрагментацию?
  4. Вариант 1. "alter table ... move + rebuild indexes"
  5. Вариант 2. "Create table as select"
  6. Вариант 3. "export/truncate/import"
  7. Вариант 4. "dbms_redefinition"

Введение
Когда строки не сохраняются рядом, или если строки разбиты больше чем в один блок, снижается производительность, потому что эти строки требуют дополнительного доступа к блокам. Следует понимать, что фрагментация таблиц отлична от файловой фрагментации. Когда выполняется серия операций DML над таблицей, таблица фрагментируется, потому что DML не освобождает свободное пространство до HWM.

HWM - это индикатор использования блоков (USED BLOCKS) в базе данных. Блоки идущие до линии HWM - используемые блоки и содержат данные. Эти данные могут быть удалены. Oracle знает какие блоки до HWM не содержат данных, он читает блоки выше HWM, когда выполняет полное сканирование таблицы.

DDL предложение всегда сбрасывает HWM.

Как найти фрагментацию таблицы?
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
Connected as hr
 
select count(1) from all_objects;
 
  COUNT(1)
----------
     53805

 
create table big1 as select * from all_objects;
 
Table created

 
insert into big1 select * from big1;
 
53805 rows inserted

 
insert into big1 select * from big1;
 
107610 rows inserted

 
insert into big1 select * from big1;
 
215220 rows inserted

 
insert into big1 select * from big1;
 
430440 rows inserted

 
insert into big1 select * from big1;
 
860880 rows inserted

 
select count(1) from big1;
 
  COUNT(1)
----------
   1721760

 
delete from big1 where rownum <= 700000;
 
700000 rows deleted

 
select count(1) from big1;
 
  COUNT(1)
----------
   1021760

 
commit;
 
Commit complete

 
UPDATE BIG1 SET OBJECT_ID = 0 WHERE ROWNUM <=350000;
 
350000 rows updated

 
commit;
 
Commit complete

 
exec dbms_stats.gather_table_stats('HR', 'BIG1');
 
PL/SQL procedure successfully completed

Размер таблицы (с фрагментацией):
SELECT TABLE_NAME, ROUND((BLOCKS * 8)/1024, 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';
 
TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                  207

Реальные данные:
SELECT TABLE_NAME, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024), 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   98

Итого 207 - 98 = 109 Mb используется без толку.

Как сбросить HWM/убрать фрагментацию?


Для этого потребуется реорганизовать фрагментированную таблицу. Есть четыре опции для реорганизации фрагментированных таблиц:
  1. alter table ... move + rebuild indexes
  2. export / truncate / import
  3. create table as select (CTAS)
  4. dbms_redefinition

Вариант 1. "alter table ... move + rebuild indexes"

Перемещаем таблицу:
ALTER TABLE big1 MOVE;

Table altered

Если у таблицы были индексы - нужно их перестроить:
SELECT STATUS, INDEX_NAME FROM USER_INDEXES
 WHERE TABLE_NAME = 'BIG1';

  STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE BIGIDX


ALTER INDEX BIGIDX REBUILD;

Index altered.


SELECT STATUS, INDEX_NAME FROM USER_INDEXES
 WHERE TABLE_NAME = 'BIG1';

  STATUS INDEX_NAME
-------- ------------------------------
VALID    BIGIDX
Собираем статистику по таблице:
exec dbms_stats.gather_table_stats('HR', 'BIG1');
 
PL/SQL procedure successfully completed

И смотрим результат.
Размер таблицы (с фрагментацией):
SELECT TABLE_NAME, ROUND((BLOCKS * 8)/1024, 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';
 
TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                  120

Реальные данные:
SELECT TABLE_NAME, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024), 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   98

Итого, 207 - 120 = 87 Mb удается освободить.

Возвращаем таблицу к исходной фрагментации (например, удалив и повторив действия по созданию и наполнению) и смотрим второй способ.

Вариант 2. "Create table as select"

Если в таблице присутствуют индексы, то после дефрагментации этим вариантом нужно будет их создать заново. Получить DDL скрипт по созданию нужных индексов можно способом, описанным здесь.
CREATE TABLE BIG2 AS SELECT * FROM BIG1;
 
Table created
 

-- удаляем таблицу не помещаю в "корзину"
DROP TABLE BIG1 PURGE;
 
Table dropped
 

RENAME BIG2 TO BIG1
/
 
Table renamed
 

exec DBMS_STATS.GATHER_TABLE_STATS('HR','BIG1')
/
 
PL/SQL procedure successfully completed

Размер таблицы (с фрагментацией):
SELECT TABLE_NAME, ROUND((BLOCKS * 8)/1024, 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';
 
TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                  120

Реальные данные:
SELECT TABLE_NAME, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024), 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   98

Вариант 3. "export/truncate/import"

-- Размер таблицы (с фрагментацией)
SELECT TABLE_NAME, ROUND((BLOCKS * 8)/1024, 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   86

-- Реальные данные
SELECT TABLE_NAME, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024), 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1'

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   43


SELECT STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'BIG1';

STATUS
--------
VALID


EXIT



Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\>exp scott/tiger@Orcl file=c:\big1.dmp tables=big1
Export: Release 10.1.0.5.0 - Production on Sat Jul 28 16:30:44 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table BIG1 468904 rows exported
Export terminated successfully without warnings.

C:\>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:12 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

TRUNCATE TABLE BIG1;

TABLE truncated.


EXIT

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\>imp scott/tiger@Orcl file=c:\big1.dmp ignore=y
Import: Release 10.1.0.5.0 - Production on Sat Jul 28 16:31:54 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT objects into SCOTT
. . importing table "BIG1" 468904 rows imported
Import terminated successfully without warnings.
C:\>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.1.0.5.0 - Production on Sat Jul 28 16:32:21 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

-- Размер таблицы (с фрагментацией)
SELECT TABLE_NAME, ROUND((BLOCKS * 8)/1024, 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   86

-- Реальные данные
SELECT TABLE_NAME, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024), 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1'

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   43

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','BIG1');

PL/SQL procedure successfully completed.

-- Размер таблицы (с фрагментацией)
SELECT TABLE_NAME, ROUND((BLOCKS * 8)/1024, 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1';

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   52

-- Реальные данные
SELECT TABLE_NAME, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024), 0) "SIZE, Mb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'BIG1'

TABLE_NAME                       SIZE, Mb
------------------------------ ----------
BIG1                                   43


SELECT STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'BIG1';

STATUS
--------
VALID


SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','BIG1', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.


Вариант 4. "dbms_redefinition"

Создадим тестовую таблицу с наполнением.
CREATE TABLE TABLE1(
       NO NUMBER,
       NAME VARCHAR2(20) DEFAULT 'NONE',
       DDATE DATE DEFAULT SYSDATE);

Table created


ALTER TABLE TABLE1 ADD CONSTRAINT PK_NO PRIMARY KEY(NO);
 
Table altered


BEGIN
  FOR X IN 1 .. 100000 LOOP
    INSERT INTO TABLE1 (NO, NAME, DDATE) VALUES (X, DEFAULT, DEFAULT);
  END LOOP;
END
/

PL/SQL procedure successfully completed


CREATE OR REPLACE TRIGGER TRI_TABLE1
  AFTER INSERT ON TABLE1
BEGIN
  NULL;
END;

Trigger created


SELECT COUNT(*) FROM TABLE1;
 
  COUNT(*)
----------
    100000


DELETE TABLE1 WHERE ROWNUM <= 50000;
 
50000 rows deleted


commit;

Commit complete


EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','TABLE1');
 
PL/SQL procedure successfully completed


-- Размер таблицы с фрагментацией
SELECT TABLE_NAME, ROUND((BLOCKS * 8), 0) "SIZE, Kb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'TABLE1';

TABLE_NAME                       SIZE, Kb
------------------------------ ----------
TABLE1                               2960


-- Реальные данные
SELECT TABLE_NAME, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 ), 0) "SIZE, Kb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'TABLE1'
 
TABLE_NAME                       SIZE, Kb
------------------------------ ----------
TABLE1                                830

Требуется роль "DBA" или право "EXECUTE" на dbms_redefinition pkg
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
Connected as SYS
 
SQL> grant execute on dbms_redefinition to hr;
 
Grant succeeded

Проверим что таблица кандидат на переопределение.
begin
  SYS.DBMS_REDEFINITION.CAN_REDEF_TABLE('HR',
                                        'TABLE1',
                                        SYS.DBMS_REDEFINITION.CONS_USE_PK);
end;

PL/SQL procedure successfully completed

После проверки таблицы на возможность переопределения, вручную создается пустая временная таблица (в той же схеме и точно такая же как переопределяемая)
CREATE TABLE TABLE2 AS SELECT * FROM TABLE1 WHERE 0 = 1;
 
Table created

Для выполнения этого и следующего скрипта мне почему-то не хватило привелегии EXECUTE на dbms_redefinition и пришлось подключиться как DBA.
EXEC SYS.DBMS_REDEFINITION.START_REDEF_TABLE('HR', 'TABLE1', 'TABLE2');

PL/SQL procedure successfully completed

Cинхронизируем две таблицы, исходную и временную.
EXEC SYS.DBMS_REDEFINITION.SYNC_INTERIM_TABLE('HR', 'TABLE1', 'TABLE2');
 
PL/SQL procedure successfully completed

Создаем PRIMARY KEY на временную таблицу(TABLE2).
ALTER TABLE TABLE2 ADD CONSTRAINT PK_NO1 PRIMARY KEY (NO);
 
Table altered


CREATE TRIGGER TRI_TABLE2
  AFTER INSERT ON TABLE2
BEGIN
  NULL;
END;

Отключаем внешний ключ на оригинальной таблице, если существует, прежде чем завершить процесс.
EXEC SYS.DBMS_REDEFINITION.FINISH_REDEF_TABLE(&#39;HR&#39;, &#39;TABLE1&#39;, &#39;TABLE2&#39;);
 
PL/SQL procedure successfully completed


EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','TABLE1');
 
PL/SQL procedure successfully completed


-- Размер таблицы с фрагментацией
SELECT TABLE_NAME, ROUND((BLOCKS * 8), 0) "SIZE, Kb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'TABLE1';
 
TABLE_NAME                       SIZE, Kb
------------------------------ ----------
TABLE1                               1376


-- Реальные данные
SELECT TABLE_NAME, ROUND((NUM_ROWS * AVG_ROW_LEN / 1024 ), 0) "SIZE, Kb"
  FROM USER_TABLES
 WHERE TABLE_NAME = 'TABLE1'

TABLE_NAME                       SIZE, Kb
------------------------------ ----------
TABLE1                                830


SELECT STATUS, CONSTRAINT_NAME
  FROM USER_CONSTRAINTS
 WHERE TABLE_NAME = 'TABLE1';

STATUS   CONSTRAINT_NAME
-------- ------------------------------
ENABLED  PK_NO


SELECT STATUS, TRIGGER_NAME 
  FROM USER_TRIGGERS 
 WHERE TABLE_NAME = 'TABLE1';

STATUS   TRIGGER_NAME
-------- ------------------------------
ENABLED  TRI_TABLE1


DROP TABLE TABLE2 PURGE
 
ORA-12083: следует использовать DROP MATERIALIZED VIEW для удаления "HR"."TABLE2"

DROP materialized view TABLE2;
 
Materialized view dropped

Источники (русский, английский).
Автор: maart на 9:53 AM
Метки: , , , , ,

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)