I am troubleshooting a severe storage efficiency issue on a batch logging table in our Oracle 19c database on the below table
Table DDL:
CREATE TABLE APP_BATCH_LOGS
(
BATCH_ID VARCHAR2(100 CHAR) NOT NULL, -- Partition Key
PROCESS_ID VARCHAR2(100 CHAR) NOT NULL,
SEQ_NUM NUMBER(14,0) NOT NULL,
MSG_TIMESTAMP DATE NOT NULL,
MSG_CODE NUMBER(5,0) DEFAULT 20 NOT NULL,
SEVERITY_CODE NUMBER(5,0) DEFAULT 20,
LOG_TEXT_CLOB CLOB,
TENANT_ID VARCHAR2(50 CHAR) NOT NULL,
WORKSPACE_ID VARCHAR2(30 CHAR) NOT NULL,
SERVICE_ID VARCHAR2(30 CHAR) NOT NULL,
EXT_LOGICAL_NAME VARCHAR2(100 CHAR),
EXT_SERVICE_URL CLOB,
)
PARTITION BY LIST (BATCH_ID)
SUBPARTITION BY HASH (PROCESS_ID) SUBPARTITIONS 4
(
PARTITION P_INIT VALUES ('INIT_PARTITION')
)
PARALLEL;
-- 3. Local Unique Index (Recreated via script logic)
CREATE UNIQUE INDEX IDX_APP_BATCH_LOGS_PK
ON APP_BATCH_LOGS (PROCESS_ID, SEQ_NUM, BATCH_ID)
LOCAL;The Issue: We have a massive gap between physical allocation and actual data size:
Total Allocated Size: 13.3 GB (8.9 GB LOB + 4.4 GB Table).
Actual Logical Data Size: ~23 MB (Calculated via SUM(VSIZE) and DBMS_LOB.GETLENGTH).
The Table Structure: The table is partitioned by List (BATCH_ID) and subpartitioned by Hash (PROCESS_ID) with SUBPARTITIONS 4.
Segment Count: We have ~550 partitions
LOB Storage: The the two CLOB columns appears to use legacy BasicFiles storage (confirmed via ORA-10614 when attempting SHRINK SPACE).
The
INITIAL_EXTENT for the table and lob segments is coming to be 8MB and the tablespace where the table is located has ALLOCATION_TYPE as 'SYSTEM'.
The Diagnostics:
Based on the number of partitions and
INITIAL_EXTENT, the allocated memory maps correctly (550 * 24 (1 Table Segment + 2 lob segment) approx = 12.8GB )
Fragmentation:
DBMS_SPACE on the table segments reports 99% wasted space.
Cleanup Failure: I attempted to run
ALTER TABLE ... MOVE SUBPARTITION to reclaim space.
Result: The total size increased to 36 GB.
Attempted Fix: I tried forcing
STORAGE (INITIAL 64K) via MODIFY DEFAULT ATTRIBUTES, but the size remains stuck at 36 GB and even attempting forcing storage on new table still the
INITIAL_EXTENT remains 8MB(probably due to
AUTOALLOCATE)
My Questions:
Given that my tablespace is set to
SYSTEM (AUTOALLOCATE) is there any way to force these partitions/segments down to < 1MB without moving the entire table to a different Tablespace and also making sure that for future partitions that
INITIAL_EXTENT remains < 1MB?
or if the above is not possible what's best solution to resolve the above discrepancy?.
Thanks and Regards,
Danish Sheikh
When you create a partitioned object, we basically run under the assumption of: "Oh, you're partitioning? This is probably going to be a huge table"
This means that we "skip" the smaller space allocations that you typically get with tables (64k and 1M) and immediately start allocating partitions in 8M extents.
Thus a table with 2 clobs and and a local index and 4 subpartitions, is looking at:
- 4 table (sub)partitions
- 8 lob (sub)partitions (4 for each clob)
- 4 index (sub)partitions
so 128M for each "logical" partition as you've already observed.
This is of no concern assuming you intend to fill this table with lots of data.
If the table is not going to be that large, the first question to ask is - do I really need to partition it?
If partitioning is indeed justified, but you need smaller segment sizes, there is a "_partition_large_extents" parameter than you can set with the guidance of Support to use smaller allocations.