Document Type | Technical Information
Category | Monitoring/Inspection
Document Number | TMOTI027
Overview
This article explains how to query tables targeted for TABLE REORG and example queries to actually perform the REORG operation together.
Method
How to Query REORG Targets
We compare the size each TABLE occupies with the estimated table size, and target tables with a large ratio difference for REORG target query using the DBMS_SPACE package.
(row count * row max length) / (table block count * block size)
1. Query to Check DBMS_SPACE Targets
- Query tables where the difference between the table's extents size and the estimated table size exceeds 100MB and the ratio of estimated table size to extents size is 50% or less.
- The query is performed on tables for which statistics have been collected.
SET LINES 180;
COL owner FOR A10;
COL table_name FOR A30;
SELECT *
FROM (
SELECT a.owner,
b.table_name,
table_type,
last_analyzed,
num_rows,
ROUND(extents_size / 1024 / 1024, 2) AS "EXTENTS_SIZE(MB)",
ROUND(max_data_size / 1024 / 1024, 2) AS "MAX_DATA_SIZE(MB)",
ROUND((extents_size - max_data_size) / 1024 / 1024, 2) AS "DIFF_SIZE(MB)",
ROUND((max_data_size / extents_size * 100), 2) AS "DATA_RATE(%)"
FROM (
SELECT owner,
segment_name,
SUM(bytes) AS extents_size, -- table extents total size
SUM(blocks) AS extents_block_count -- table extents block total count
FROM dba_extents
WHERE owner NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
AND segment_type IN ('TABLE', 'TABLE PARTITION')
GROUP BY owner, segment_name
) a,
(
SELECT a.owner,
a.table_name,
DECODE(partitioned, 'YES', 'PARTITION TABLE', 'TABLE') AS table_type,
last_analyzed,
ROUND(num_rows) AS num_rows,
blocks,
avg_row_len,
max_row_len,
(ROUND(num_rows) * max_row_len) AS max_data_size
FROM dba_tables a,
(
SELECT owner,
table_name,
SUM(data_length) AS max_row_len -- 1 row max size
FROM dba_tbl_columns
WHERE owner NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
GROUP BY owner, table_name
) b
WHERE a.owner = b.owner
AND a.table_name = b.table_name
AND last_analyzed IS NOT NULL
) b
WHERE a.owner = b.owner
AND a.segment_name = b.table_name
) x
WHERE "DIFF_SIZE(MB)" > 100
AND "DATA_RATE(%)" < 50
ORDER BY "DATA_RATE(%)" DESC,
"DIFF_SIZE(MB)" DESC;2. Query for REORG Targets
- Check the data block usage of tables queried in step 1 to determine the need for REORG.
- Enter the tables to check inside the IN condition and run the query.
- If blocks with a FREE ratio of 50% or more exceed 20% of all blocks, select the table as a REORG target.
SET SERVEROUTPUT ON
SET LINESIZE 180
SET PAGESIZE 100
DECLARE
USED_BLOCKS NUMBER;
FRM_BLOCKS NUMBER;
FRM_RATIO NUMBER;
uf NUMBER;
ub NUMBER;
f1 NUMBER;
f1b NUMBER;
f2 NUMBER;
f2b NUMBER;
f3 NUMBER;
f3b NUMBER;
f4 NUMBER;
f4b NUMBER;
fbl NUMBER;
fby NUMBER;
BEGIN
FOR tb_list IN (
SELECT owner, segment_name, partition_name, segment_type
FROM dba_segments
WHERE segment_name IN ('MK_TBH_RF_COV','TEST','ABC') -- tables to query
) LOOP
BEGIN
dbms_space.space_usage(
tb_list.owner,
tb_list.segment_name,
tb_list.segment_type,
uf, ub, f1, f1b, f2, f2b, f3, f3b, f4, f4b, fbl, fby,
tb_list.partition_name
);
USED_BLOCKS := f1 + f2 + f3 + f4 + fbl;
FRM_BLOCKS := f3 + f4;
IF USED_BLOCKS = 0 THEN
dbms_output.put_line('----------------------');
dbms_output.put_line(tb_list.owner || '.' || tb_list.segment_name || '.' || tb_list.partition_name || ' is not used.');
ELSE
FRM_RATIO := ROUND(FRM_BLOCKS / USED_BLOCKS * 100);
IF USED_BLOCKS > 100 THEN
IF FRM_RATIO > 20 THEN
dbms_output.put_line('#######################');
dbms_output.put_line(tb_list.owner || '.' || tb_list.segment_name || '.' || tb_list.partition_name || ' is fragmented ' || FRM_RATIO || '%');
dbms_output.put_line('unformatted blocks : ' || TO_CHAR(uf));
dbms_output.put_line('blocks 0-25% free : ' || TO_CHAR(f1));
dbms_output.put_line('blocks 25-50% free : ' || TO_CHAR(f2));
dbms_output.put_line('blocks 50-75% free : ' || TO_CHAR(f3));
dbms_output.put_line('blocks 75-100% free : ' || TO_CHAR(f4));
dbms_output.put_line('full blocks : ' || TO_CHAR(fbl));
ELSE
dbms_output.put_line('----------------------');
dbms_output.put_line(tb_list.owner || '.' || tb_list.segment_name || '.' || tb_list.partition_name || ' is fragmented [' || FRM_RATIO || '%]');
END IF;
ELSE
dbms_output.put_line('----------------------');
dbms_output.put_line(tb_list.owner || '.' || tb_list.segment_name || '.' || tb_list.partition_name || ' pass [' || USED_BLOCKS || '] Used blocks');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(TO_CHAR(SQLCODE) || ':' || SQLERRM);
END;
END LOOP;
END;
/
Online REORG Method (Table Shrink)
1. Create a temporary table with the same structure as the target table
CREATE TABLE temp_table ( column, ... );
2. Start the Redefinition operation
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE( UNAME => 'owner', ORIG_TABLE => 'target_table', INT_TABLE => 'temp_table', OPTIONS_FLAG => 2); -- 1: Use PK info, 2: Use ROWID info (Default: 1) END; /
3. After redefinition, add PRIMARY KEY and indexes to the temporary table
1) Add PRIMARY KEY
ALTER TABLE temp_table ADD CONSTRAINT pk_constraint_name PRIMARY KEY(column);
2) Add indexes
CREATE INDEX temp_index ON temp_table ( column, ... );
4. Synchronize changes made during the redefinition operation
BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE( UNAME => 'owner', ORIG_TABLE => 'target_table', INT_TABLE => 'temp_table'); END; /
5. Swap the temporary table and the target table
BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE( UNAME => 'owner', ORIG_TABLE => 'target_table', INT_TABLE => 'temp_table'); END; /
6. Rename constraints and index names
1) Rename index names
Rename the index names of the temp_table, then rename the index names of the target_table.
ALTER INDEX temp_index RENAME TO index_name;
2) Rename constraint names
Rename the PRIMARY KEY and other constraint names of the temp_table, then rename the constraint names of the target_table.
ALTER TABLE TEST RENAME CONSTRAINT PK_TEST_BAK TO PK_TEST;
7. Drop the temporary table
DROP TABLE temp_table;