Сжатие и измерение объёма базы данных Оракл

THE HOLY BIBLE - King James Version - БИБЛИЯ в Синодальном переводе
"Нас Атакуют!" Изобличи козни лукавого, запрети диаволу

Сжатие и измерение объёма базы данных Оракл

Размеры баз данных постоянно растут. Как следствие, в последние годы сжатие (компрессия) данных нашло широкое применение. Часто возникает необходимость оценить реальный размер данных, хранимых в базе, а также степень их сжатия после компрессирования. В то же время, измерения объёма информации, основанные на использовании представлений DBA_SEGMENTS (и подобных) не позволяют получить точные результаты.

Предлагаемая читателю заметка рассказывает об использовании пакета DBMS_SPACE, выдающего точную информацию о реальном объёме данных в сегментах до и после сжатия.

Прежде чем мы продолжим, я хотел бы привести строки из Евангелия:



.......................... == Книга Екклезиаста == .............................
=== Глава 12, Стих 13 ===
13 Выслушаем сущность всего: бойся Бога и заповеди Его соблюдай, потому что
 в этом все для человека;
14 ибо всякое дело Бог приведет на суд, и все тайное, хорошо ли оно, или
 худо.

Лично для вас благая весть - Единородный Сын Божий Иисус Христос любит вас, Он взошёл на крест за ваши грехи, был распят и на третий день воскрес, сел одесную Бога и открыл нам дорогу в Царствие Небесное.

Но сможем ли мы пройти по этой дороге? Ведь каждый из нас грешен, даже если мы и не знаем о Божьем законе и Его слове. Даже если кто-то специально отгораживается от имени Иисуса мирскими заботами, властью, деньгами и нарочно не даёт себе возможности открыть Библию и обратиться к слову Божию - но сказано "всякое дело Бог приведет на суд". И будет суд этот страшен, и не сможешь "договориться", "решить вопрос", "спустить на тормозах" - всё то, что узнал и приобрёл в жизни своей - станет в один момент бесполезным.
И одна будет у нас зашита - Иисус Христос, одно оправдание - соблюдение заповедей Божиих, и одно упование - на милость Господню, которую надо заслужить покаянием, верой и делами добрыми в угоду Отцу нашему небесному.

Покайтесь, примите Иисуса как вашего Спасителя, ибо наступают последние времена и время близко - стоит Судья у ворот.

Пожалуйста, в своих каждодневных трудах, какими бы занятыми вы себе ни казались - находите время для Бога, Его заповедей и Библии.

На главной странице этого сайта вы найдете программу для чтения Библии в командной строке - буду очень рад если программа окажется полезной. Пожалуйста, читайте Библию, на экране или в печатном виде - вы будете искренне удивлены как много там сказано лично про вас и ваши обстоятельства.


Вернёмся к нашим техническим деталям.

Мы рассмотрим простой пример - изначально маленькая таблица с большим размером экстента. Такой подход применяется в хранилищах данных, где используются большие размеры блока и экстента, для облегчения администрирования и более эффективного и рационального хранения данных. Наша таблица будет иметь экстент размером 16 Мб.

Создание тестовой таблицы

Создадим таблицу в соответствии с заданными выше требованиями:

........... == Первое соборное послание святого апостола Петра == ..............
=== Глава 3, Стих 21 ===
21 Так  и  нас  ныне  подобное  сему  образу  крещение,  не  плотской  нечистоты
омытие,  но  обещание  Богу  доброй  совести,  спасает  воскресением   Иисуса
Христа,
22 Который, восшед  на  небо,  пребывает  одесную  Бога  и  Которому  покорились
Ангелы и Власти и Силы.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

16:22:02 SQL>

1  create table t1 storage (initial 16M NEXT 16M)
2* as select * from dba_segments
16:22:42 SQL> /

Table created.

Elapsed: 00:00:01.10
16:22:44 SQL>

16:28:26 SQL> select count(*)
16:28:33   2  from t1;

COUNT(*)
----------
2673

1 row selected.

Elapsed: 00:00:00.01
16:28:36 SQL>

1  select SEGMENT_NAME, SEGMENT_TYPE, EXTENT_ID, BYTES, BLOCKS
2* from user_extents
16:29:35 SQL> /

SEGMENT_NA SEGMENT_TYPE        EXTENT_ID      BYTES BLOCKS
---------- ------------------ ---------- ---------- ----------
T1    TABLE         0    8388608   1024
T1    TABLE         1    8388608   1024

2 rows selected.

Elapsed: 00:00:00.03
16:29:36 SQL>

Таблица имеет всего две с половиной тысячи строк, занимает два экстента данных по 8Мб (или 1024 блока данных) каждый. Очевидно, что наша база использует блок данных с размером 8Кб. Наверняка в единственном сегменте нашей таблицы имеется огромное количество пустого места - проверим это. Сначала соберём статистику.

......................... == Книга пророка Михея == ............................
=== Глава 7, Стих 2 ===
2 Не стало милосердых на земле, нет правдивых между  людьми;  все  строят  ковы,
чтобы проливать кровь; каждый ставит брату своему сеть.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

16:41:06 SQL>

1  select SEGMENT_NAME, SEGMENT_TYPE, BYTES, BLOCKS, EXTENTS
2* from user_segments
16:41:46 SQL> /

SEGMENT_NA SEGMENT_TYPE     BYTES     BLOCKS    EXTENTS
---------- ------------------ ---------- ---------- ----------
T1    TABLE  16777216       2048      2

1 row selected.

Elapsed: 00:00:00.08
16:41:46 SQL>

16:41:46 SQL> exec dbms_stats.gather_table_stats('TEST', 'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.35
16:43:50 SQL>

16:44:19 SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN, BLOCKS*8192 BYTES
16:45:14   2  from user_tables;

TABLE_NAME    NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN BYTES
------------------------------ ---------- ---------- ------------ ----------- ----------
T1         2673   68  0   122 557056

1 row selected.

Elapsed: 00:00:00.21
16:45:18 SQL>

Уже сразу заметны расхождения. Таблица содержит в себе всего лишь 2673 строки, по 122 байта каждая - это 326106 байт, почему же мы видим 68 использованных блоков (то есть 557056 байт)? Не говоря уже о том, что на самом деле для таблицы T1 Ораклом выделено два экстента - целых 16 Мб (правда, по нашему требованию).

Какому же результату нам доверять? Запрос к DBA_SEGMENTS показывает размер таблицы T1 равным 16 Мб, представление DBA_TABLES утверждает, что таблица содержится в 68 блоках данных размером 544 Кб, а статистика говорит о 318 Кб (2673 строки по 122 байта):

........................... == Притчи Соломона == ..............................
=== Глава 13, Стих 6 ===
5 Праведник ненавидит ложное слово, а  нечестивый  срамит  и  бесчестит  _себя._
6 Правда   хранит   непорочного   в   пути,   а   нечестие   губит     грешника.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

16:57:01 SQL>

------ DBA_SEGMENTS --------------
16:57:11 SQL> select sum(bytes) from dba_segments
17:01:13   2  where owner = 'TEST' and segment_name = 'T1';

SUM(BYTES)
----------
16777216

1 row selected.

Elapsed: 00:00:00.18
17:01:24 SQL>

------ DBA_TABLES ---------------
1  select blocks*8192/1024 kbytes
2  from dba_tables
3  where owner = 'TEST'
4* and table_name = 'T1'
17:05:38 SQL> /

KBYTES
----------
544

1 row selected.

Elapsed: 00:00:00.04
17:05:39 SQL>

----- STATISTICS ---------------
1  select floor(NUM_ROWS*AVG_ROW_LEN/1024) kbytes
2  from dba_tables
3  where owner = 'TEST'
4* and table_name = 'T1'
17:08:29 SQL> /

KBYTES
----------
318

1 row selected.

Elapsed: 00:00:00.04
17:08:30 SQL>

Ответ прост - все значения верны, но эти представления показывают результаты разных вычислений для разных целей. DBA_SEGMENTS даёт нам возможность оценить сколько дискового пространства понадобится для всей таблицы / схемы / базы с учётом выбранных параметров хранения (storage). DBA_TABLES даёт нам число занятых блоков с данными таблицы T1, а статистика - правильное число записей (и приблизительный размер собственно данных в таблице). Эти цифры используются оптимизатором запросов Оракл и могут быть полезны администраторам для быстрой оценки размера дисковой памяти и тенденций её изменения.

Но ни один из этих результатов не подходит для точного вычисления объёма собственно данных пользователя в таблице T1.

Измерение действительного обьёма данных, до сжатия

Учитывая необходимость вычисления как можно более точного коэффициента сжатия данных, мы используем пакет DBMS_SPACE:

.......... == Первое соборное послание святого апостола Иоанна == ..............
=== Глава 4, Стих 3 ===
2 Духа  Божия   (и  духа  заблуждения)  узнавайте  так:   всякий   дух,  который
исповедует Иисуса Христа, пришедшего во плоти, есть от Бога;
3 а всякий дух, который не  исповедует Иисуса Христа,  пришедшего  во  плоти, не
есть от Бога, но это дух антихриста, о  котором вы слышали,  что  он  придет и
теперь есть уже в мире.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

17:25:18 SQL> get /tmp/t
1  declare
2 unformatted_blocks  NUMBER;
3 unformatted_bytes  NUMBER;
4 fs1_blocks   NUMBER;
5 fs1_bytes   NUMBER;
6 fs2_blocks   NUMBER;
7 fs2_bytes   NUMBER;
8 fs3_blocks   NUMBER;
9 fs3_bytes   NUMBER;
10 fs4_blocks   NUMBER;
11 fs4_bytes   NUMBER;
12 full_blocks   NUMBER;
13 full_bytes   NUMBER;
14  begin
15  dbms_space.space_usage (
16  segment_owner=> 'TEST',
17  segment_name=>'T1',
18  segment_type=>'TABLE',
19  unformatted_blocks=>unformatted_blocks,
20  unformatted_bytes=>unformatted_bytes,
21  fs1_blocks=>fs1_blocks,
22  fs1_bytes=>fs1_bytes,
23  fs2_blocks=>fs2_blocks,
24  fs2_bytes=>fs2_bytes,
25  fs3_blocks=>fs3_blocks,
26  fs3_bytes=>fs3_bytes,
27  fs4_blocks=>fs4_blocks,
28  fs4_bytes=>fs4_bytes,
29  full_blocks=>full_blocks,
30  full_bytes=>full_bytes,
31  partition_name=>NULL
32  );
33  dbms_output.put_line ('Всего в таблице T1 '||
34  full_bytes||' bytes.');
35* end;
17:25:20  36
17:25:24  37  /

Всего в таблице T1 409600 bytes.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
17:25:25 SQL>

Вот это новость - таблица T1 содержит ровно 400 Кб пользовательских данных - не 16 Мб, не 544 Кб и не 318 Кб. Естественно, использование любого метода, не основанного на DBMS_SPACE, выдало бы нам значительно заниженные или завышенные значения степени сжатия нашей таблицы.

Пакет DBMS_SPACE выдаёт точные данные, поскольку он в состоянии учитывать текущую позицию High Water Mark для таблицы T1. В нашем примере таблица T1 не содержит частично заполненных блоков, таким образом значения fs1_bytes ... fs4_bytes равны нулю и не учитываются. В реальной ситуации все эти значения надо суммировать.

Сжатие данных

Мы будем использовать одну из платных опций Oracle Enterprise Edition - OLTP Compression. Такое сжатие подходит для динамически изменяемых объектов. Бесплатная BASIC Compression эффективно работает только с данными "для чтения". Естественно, я предполагаю что вопросы лицензирования Оракл решены и у вас имеются все необходимые лицензии.

Прежде чем сжимать таблицу T1, убедимся что она имеет значение PCT_FREE равное 10% (по умолчанию) - именно это значение будет использовано для OLTP сжатия.

.......... == Первое соборное послание святого апостола Иоанна == ..............
=== Глава 5, Стих 5 ===
5 Кто побеждает мир,  как  не  тот,  кто  верует,  что  Иисус  есть  Сын  Божий?
6 Сей есть Иисус Христос, пришедший водою и кровию и  Духом,  не  водою  только,
но водою и кровию,  и  Дух  свидетельствует  о  _Нем,_  потому  что  Дух  есть
истина.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

17:45:06 SQL>

1* select table_name, PCT_FREE from user_tables
17:45:46 SQL> /

TABLE_NAME     PCT_FREE
------------ ----------
T1       10

1 row selected.

Elapsed: 00:00:00.04
17:45:47 SQL>

1  select segment_name, EXTENT_ID, BLOCK_ID
2  from dba_extents
3* where owner = 'TEST'
17:49:45 SQL> /

SEGMENT_NAME  EXTENT_ID   BLOCK_ID
------------ ---------- ----------
T1        0        128
T1        1       1152

2 rows selected.

Elapsed: 00:00:00.46
17:49:46 SQL>

17:49:46 SQL> alter table t1 move compress for oltp;

Table altered.

Elapsed: 00:00:00.88
17:50:12 SQL>

1  select segment_name, EXTENT_ID, BLOCK_ID
2  from dba_extents
3* where owner = 'TEST'
17:50:35 SQL> /

SEGMENT_NAME  EXTENT_ID   BLOCK_ID
------------ ---------- ----------
T1        0       2176
T1        1       3200

2 rows selected.

Elapsed: 00:00:00.10
17:50:36 SQL>

17:55:05 SQL> select table_name, PCT_FREE from user_tables
17:57:49   2  ;

TABLE_NAME     PCT_FREE
------------ ----------
T1       10

1 row selected.

Elapsed: 00:00:00.14
17:57:51 SQL>

Как видно, таблица была перемещена в два новых экстента, начинающихся с других блоков. Насколько же уменьшились в объёме данные пользователя в нашей таблице?

Измерение действительного обьёма данных, после сжатия

Используем тот же самый скрипт с пакетом DBMS_SPACE:

..................... == Книга Премудрости Соломона == .........................
=== Глава 4, Стих 7 ===
7 А   праведник,   если    и    рановременно    умрет,    будет    в      покое,
8 ибо  не  в  долговечности  честная  старость  и  не  числом  лет   измеряется:
9 мудрость есть седина для  людей,  и  беспорочная  жизнь  -  возраст  старости.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

17:55:02 SQL> @/tmp/t

Всего в таблице T1 106496 bytes.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:55:05 SQL>

Уменьшение обьёма данных пользователя значительно - 3.84 раза. Но это именно данные. Что же произошло с самой таблицей и какой коэффициент сжатия мы бы получили без использования пакета DBMS_SPACE? Мы уже знаем, что таблица по-прежнему расположена в двух экстентах. Проверим что покажут запросы к DBA_SEGMENTS и статистика.

........................ == Книга пророка Софонии == ...........................
=== Глава 2, Стих 2 ===
1 Исследуйте    себя    внимательно,    исследуйте,    народ       необузданный,
2 доколе не пришло определение - день пролетит как мякина  -  доколе  не  пришел
на вас пламенный гнев Господень,  доколе  не  наступил  для  вас  день  ярости
Господней.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

18:02:36 SQL>

------ DBA_SEGMENTS --------------
1  select sum(bytes) from dba_segments
2* where owner = 'TEST' and segment_name = 'T1'
18:03:06 SQL> /

SUM(BYTES)
----------
16777216  -- То же самое значение!

1 row selected.

Elapsed: 00:00:00.01
18:03:06 SQL>

18:03:06 SQL> exec dbms_stats.gather_table_stats('TEST', 'T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
18:04:24 SQL>

------ DBA_TABLES ---------------
1  select blocks, empty_blocks, blocks*8192/1024 kbytes
2  from dba_tables
3  where owner = 'TEST'
4* and table_name = 'T1'
18:06:05 SQL> /

BLOCKS EMPTY_BLOCKS     KBYTES
---------- ------------ ----------
31       0        248 -- коэффициент сжатия 1.28

1 row selected.

Elapsed: 00:00:00.04
18:06:06 SQL>

----- STATISTICS ---------------
1  select floor(NUM_ROWS*AVG_ROW_LEN/1024) kbytes
2  from dba_tables
3  where owner = 'TEST'
4* and table_name = 'T1'
18:08:55 SQL> /

KBYTES
----------
318   -- Значение не изменилось.

1 row selected.

Elapsed: 00:00:00.01
18:08:56 SQL>

18:08:56 SQL> select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN,
BLOCKS*8192 BYTES
18:09:29   2  from user_tables;

TABLE_NAME     NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  BYTES
------------ ---------- ---------- ------------ ----------- ----------
T1     2673  31       0  122 253952

1 row selected.

Elapsed: 00:00:00.04
18:09:33 SQL>

Что же мы видим? Факт уменьшения данных зафиксирован только в одном месте - колонка BLOCKS представления DBA_TABLES уменьшила свое значение с 68 до 31. Но даже в этом случае мы получили в два раза заниженное значение степени сжатия данных. Это обьясняется тем, что представление DBA_TABLES не отслеживает значения High Water Mark для нашего сегмента T1. Таким образом, для оценки степени сжатия данных единственно верное средство - пакет DBMS_SPACE.

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

Используемый нами скрипт работает для одной таблицы. Что же делать, если нам необходимо определить степень сжатия для всей схемы целиком, включающей сотни таблиц, секций и подсекций (partitions и subpartitions)?

Приведенный ниже скрипт позволит вам произвести такие вычисления. Обратите внимание - пакет DBMS_SPACE надо использовать только в периоды минимальной нагрузки на вашу базу данных! Подразумевается что сегменты используют Automatic Segment Space Management.

Скрипт: Измерение действительного обьёма данных для всех таблиц схемы

Использовать скрипт просто - предположим, я хочу оценить реальный размер данных в схеме SOE, используемой программой Swingbench:

== Второе послание к Фессалоникийцам (Солунянам) святого апостола Павла == ....
=== Глава 3, Стих 3 ===
3 Но  верен  Господь,  Который   утвердит   вас   и   сохранит   от    лукавого.

[home]$ . oraenv
ORACLE_SID = [EE11] ?
The Oracle base remains unchanged with value /home/ora11/orabase

[home]$ ./space.read-and-think.org "soe/soe" -- может быть "sys/passwd as sysdba"
...
... Partition SOE.ORDER_ITEMS : SYS_P97 - 6086656 bytes.
... Partition SOE.ORDER_ITEMS : SYS_P98 - 6103040 bytes.
... Partition SOE.ORDER_ITEMS : SYS_P99 - 6103040 bytes.
ORDER_ITEMS     64 Par,.....................390029312 bytes.
PRODUCT_DESCRIPTIONS     Total:........................245760 bytes.
PRODUCT_INFORMATION     Total:........................204800 bytes.
WAREHOUSES      Total:.........................32768 bytes.
======= SCHEMA SOE TOTAL: 1100513280 bytes.

Проверим для сравнения, какой результат администратор базы данных получит "обычным" способом:

............ == Послание к Колоссянам святого апостола Павла == ................
=== Глава 3, Стих 11 ===
8 А теперь вы отложите все: гнев, ярость,  злобу,  злоречие,  сквернословие  уст
ваших;
9 не  говорите  лжи  друг  другу,  совлекшись  ветхого  человека  с  делами  его
10 и облекшись в нового, который обновляется в  познании  по  образу  Создавшего
его,
11 где нет ни Еллина, ни Иудея, ни обрезания, ни  необрезания,  варвара,  Скифа,
раба, свободного, но все и во всем Христос.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

11:48:04 SQL>

1  select segment_type, sum(bytes) bytes
2  from user_segments
3  where segment_type like 'TABLE%'
4* group by segment_type
11:49:03 SQL> /

SEGMENT_TYPE  BYTES
------------------ ----------
TABLE PARTITION    1610612736
TABLE       92930048
----------
sum     1703542784

2 rows selected.

Elapsed: 00:00:00.08
11:49:04 SQL>
12:25:45 SQL> show user
USER is "SOE"
12:25:47 SQL>

Разница составила 35 процентов и продемонстрировала нам, что широко используемые сегодня способы измерения размера базы значительно завышают реальный объём данных. Это вполне приемлемо при планировании закупок дополнительных дисков, но не подходит для анализа и реорганизации данных в больших хранилищах, где каждый гигабайт имеет реальную достаточно высокую цену.

Посмотрим глазами Enterprise Manager'а

Пакет DBMS_SPACE имеет недокументированную процедуру "OBJECT_SPACE_USAGE", позволяющую выполнить те же действия и используемую Enterprise Manager'ом. Создадим таблицу заново и проверим, сколько байт найдёт в ней эта функция:

.................. == От Марка святое благовествование == ......................
=== Глава 7, Стих 21 ===
21 Ибо   извнутрь,   из   сердца   человеческого,    исходят    злые    помыслы,
прелюбодеяния, любодеяния, убийства,
22 кражи,  лихоимство,  злоба,   коварство,   непотребство,   завистливое   око,
богохульство, гордость, безумство, --
23 все    это    зло    извнутрь    исходит     и     оскверняет       человека.

[home]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: test/test

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

18:33:39 SQL> get /tmp/t1
1  declare
2  used_bytes NUMBER;
3  alolocated_bytes NUMBER;
4  percent_chains NUMBER;
5   BEGIN
6   dbms_space.object_space_usage(
7   OBJECT_OWNER => 'TEST',
8   OBJECT_NAME => 'T1',
9   OBJECT_TYPE =>'TABLE',
10   SAMPLE_CONTROL => 100,
11   SPACE_USED => used_bytes,
12   SPACE_ALLOCATED => alolocated_bytes,
13   CHAIN_PCENT => percent_chains);
14   dbms_output.put_line('Used space: '||used_bytes||
15   ', allocated: '||alolocated_bytes);
16*  end;
18:33:46 SQL> /
Used space: 402543, allocated: 16777216

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

18:37:28 SQL> @/tmp/t
Total 409600 bytes.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
18:37:38 SQL>

В этом случае наша таблица выглядит ещё меньшей, что можно попытаться обьяснить "таинственным происхождением" и недокументированностью процедуры "OBJECT_SPACE_USAGE". Кроме того, эта процедура может серьёзно мешать работе базы данных - значительно больше, чем просто "DBMS_SPACE.SPACE_USAGE".

И тем не менее, недокументированная функция может нам очень помочь. Что случится если мы удалим строки из таблицы и проверим её размер вновь?

18:45:32 SQL> select count(*)
18:45:39   2  from t1;

COUNT(*)
----------
2674

1 row selected.

Elapsed: 00:00:00.00

1* delete from t1 where owner = 'SYS'
18:46:34 SQL> /

2208 rows deleted.

Elapsed: 00:00:00.07
18:46:34 SQL> commit;

Commit complete.

Elapsed: 00:00:00.03

18:46:37 SQL> select count(*)  from t1;

COUNT(*)
----------
466

1 row selected.

Elapsed: 00:00:00.00
18:47:28 SQL>

18:47:44 SQL> @/tmp/t
Total 409600 bytes.  -- ничего не изменилось.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
18:47:49 SQL> @/tmp/t1
Used space: 117166, allocated: 16777216  -- размер таблицы уменьшился!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
18:47:54 SQL>

18:50:13 SQL> alter table t1 move;

Table altered.

Elapsed: 00:00:00.03

18:50:38 SQL>

18:51:24 SQL> @/tmp/t
Total 73728 bytes.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
18:51:28 SQL> @/tmp/t1
Used space: 73180, allocated: 16777216

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
18:51:36 SQL>

Из этого примера явно видно, что недокументированная процедура, используемая EM, сразу же обнаруживает удаление строк, а "SPACE_USAGE" - только после перемещения (или "shrink space") таблицы. Исходя из практических соображений, ниже я привожу версию скрипта, использующую процедуру "OBJECT_SPACE_USAGE".

Скрипт: Измерение действительного обьёма данных для всех таблиц схемы, используя "OBJECT_SPACE_USAGE"

Спасибо что зашли,

Будьте благословенны!
Денис