• Базы данных
  • Oracle
  • Сбор и воспроизведение нагрузки базы данных Oracle 11g Database Replay

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

Written on . Posted in Oracle

ОГЛАВЛЕНИЕ

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

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

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

Как найти фрагментацию таблицы?

SQL> SELECT COUNT(*) FROM BIG1;
1000000 rows selected.

SQL> DELETE FROM BIG1 WHERE ROWNUM <= 300000;
300000 rows deleted.

SQL> COMMIT;
Commit complete.

SQL> UPDATE BIG1 SET OBJECT_ID = 0 WHERE ROWNUM <=350000;
342226 rows updated.

SQL> COMMIT;
Commit complete.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','BIG1');
PL/SQL procedure successfully completed.

Размер таблицы (с фрагментацией)

SQL> SELECT TABLE_NAME, ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 72952KB

Реальные данные:

SQL> SELECT TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME                     SIZE
------------------------------ ------------------------------------------
BIG1                           30604.2KB 
Итог = 72952 - 30604 = 42348 Kb используется без толку.

Разница между двумя значениями почти 60% и Pctfree 10% (по-умолчанию), в итоге, 50% пространства не используется и простаивает, потому что не содержит данных.

Как сбросить 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"

SQL> ALTER TABLE BIG1 MOVE;
TABLE altered.

SQL> SELECT STATUS, INDEX_NAME FROM USER_INDEXES
2 WHERE TABLE_NAME = 'BIG1';
STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE BIGIDX

SQL> ALTER INDEX BIGIDX REBUILD;
Index altered.

SQL> SELECT STATUS, INDEX_NAME FROM USER_INDEXES
2 WHERE TABLE_NAME = 'BIG1';
STATUS INDEX_NAME
-------- ------------------------------
VALID BIGIDX

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','BIG1');
PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 38224KB

SQL> SELECT TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 30727.37KB 


Опция: 2 "Create table as select"

SQL> CREATE TABLE BIG2 AS SELECT * FROM BIG1;
TABLE created.

SQL> DROP TABLE BIG1 PURGE;
TABLE dropped.

SQL> RENAME BIG2 TO BIG1;
TABLE renamed.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','BIG1');
PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME,ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE 
------------------------------ ------------------------------------------
BIG1 85536KB

SQL> SELECT TABLE_NAME,ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 68986.97KB

SQL> SELECT STATUS FROM USER_INDEXES
2 WHERE TABLE_NAME = 'BIG1';
no rows selected

SQL> --Важно, потребуется заново создать все индексы. 


Опция: 3 "export/truncate/import"

SQL> SELECT TABLE_NAME, ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 85536KB

SQL> SELECT TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 42535.54KB

SQL> SELECT STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'BIG1';
STATUS
--------
VALID
SQL> 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
SQL> TRUNCATE TABLE BIG1;
TABLE truncated.

SQL> 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's 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
SQL> SELECT TABLE_NAME, ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 85536KB

SQL> SELECT TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 42535.54KB

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','BIG1');
PL/SQL procedure successfully completed.

SQL> SELECT TABLE_NAME, ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 51840KB

SQL> SELECT TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'BIG1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
BIG1 42542.27KB

SQL> 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"

SQL> CREATE TABLE TABLE1 (
2 NO NUMBER,
3 NAME VARCHAR2(20) DEFAULT 'NONE',
4 DDATE DATE DEFAULT SYSDATE);
TABLE created.

SQL> ALTER TABLE TABLE1 ADD CONSTRAINT PK_NO PRIMARY KEY(NO);
TABLE altered.

SQL> BEGIN
2 FOR X IN 1..100000 LOOP
3 INSERT INTO TABLE1 ( NO , NAME, DDATE)
4 VALUES ( X , DEFAULT, DEFAULT);
5 END LOOP;
6 END;
PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE TRIGGER TRI_TABLE1
2 AFTER INSERT ON TABLE1
3 BEGIN
4 NULL;
5 END;
Trigger created.

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

SQL> DELETE TABLE1 WHERE ROWNUM <= 50000;
50000 rows deleted.

SQL> COMMIT;
Commit complete.

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

SQL> SELECT TABLE_NAME, ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'TABLE1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
TABLE1 2960KB

SQL> SELECT TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'TABLE1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
TABLE1 822.69KB

SQL> --Требуется роль "DBA" или "SELECT" на dbms_redefinition pkg
SQL> --Проверьте что таблица кандидат на переопределение.
SQL>
SQL> EXEC SYS.DBMS_REDEFINITION.CAN_REDEF_TABLE
2    ('SCOTT',-> 'TABLE1',-> 
3    SYS.DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.

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

SQL> EXEC SYS.DBMS_REDEFINITION.START_REDEF_TABLE
2    ( 'SCOTT',-> 'TABLE1',-> 'TABLE2');
PL/SQL procedure successfully completed.

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

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

SQL> CREATE TRIGGER TRI_TABLE2
2 AFTER INSERT ON TABLE2
3 BEGIN
4 NULL;
5 END;
Trigger created.

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

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

SQL> SELECT TABLE_NAME, ROUND((BLOCKS*8),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'TABLE1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
TABLE1 1376KB

SQL> SELECT TABLE_NAME, ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2)//'KB' "SIZE"
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'TABLE1';
TABLE_NAME SIZE
------------------------------ ------------------------------------------
TABLE1 841.4KB

SQL> SELECT STATUS,CONSTRAINT_NAME
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'TABLE1';
STATUS CONSTRAINT_NAME
-------- ------------------------------
ENABLED PK_NO1

SQL> SELECT STATUS ,TRIGGER_NAME
2 FROM USER_TRIGGERS
3 WHERE TABLE_NAME = 'TABLE1';
STATUS TRIGGER_NAME
-------- ------------------------------
ENABLED TRI_TABLE2

SQL> DROP TABLE TABLE2 PURGE;
TABLE dropped. 
Какой из способов подходит вам, смотрите по ситуации.
форум oracle