Summary: I have materialized views in oracle 11g that seem to hog disk space, unlike normal tables that mark rows as deleted and stats eventually show them as free space (allocated to the table, allowing reuse). Tablespace usage only grows for materialized views unlike stats for origin tables. Tested in Oracle 12c with same results. How to ensure MV reuse space from deleted rows?
What have I done? I have these partitioned materialized views set up in a separate schema, separate tablespace from the origin tables (i know they could have partitions created dynamically, call it technical debt).
CREATE MATERIALIZED VIEW replication_schema.origin_table PARTITION BY RANGE(tbl_timestamp) ( PARTITION tbl_before_2016 VALUES LESS THAN (TO_TIMESTAMP('2016-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')), PARTITION tbl_2016_01 VALUES LESS THAN (TO_TIMESTAMP('2016-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS')), PARTITION tbl_2016_02 VALUES LESS THAN (TO_TIMESTAMP('2016-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')), ... PARTITION tbl_after_2025 VALUES LESS THAN (MAXVALUE) ) REFRESH FORCE ON DEMAND START WITH SYSDATE NEXT sysdate+1/1440 AS SELECT * FROM origin_schema.table;
And they have some indexes on them as well, some global and some are local.
CREATE INDEX tbl_account_index ON replication_schema.origin_table (tbl_account DESC) LOCAL; CREATE INDEX tbl_column1_index ON replication_schema.origin_table (tbl_column1 DESC) LOCAL; CREATE INDEX tbl_column2_index ON replication_schema.origin_table (tbl_column2 DESC) LOCAL; CREATE INDEX tbl_column3_index ON replication_schema.origin_table (tbl_column3 DESC); CREATE INDEX tbl_column4_index ON replication_schema.origin_table (tbl_column4 DESC);
Most of the time they get new rows (about 4M/mo) but users have set up a process to delete old rows from the origin table every two weeks. They can delete up to 500K/1M rows from each replicated table, every time.
There are seven materialized views in this schema. Each one extract data from one origin table.
What we see is that, contrary to what happens with the origin table, the space reported as free in
dba_ tables does not change over time and tablespace usage only grows from these materialized views.
If I wait a while after deleting rows and run this query:
select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name and df.totalspace <>0 ;
It shows an increase in the
Free MB column (space in dba_data_files minus allocation declared in dba_segment) for origin tablespace but the used MB for replication never decrease, only increase on new rows (over three years now)
Tablespace Used MB Free MB Total MB Pct. Free SYSTEM 491 9 500 2 SYSAUX 1628 162 1790 9 UNDOTBS1 0 9645 9645 100 ORIGIN_DATA 2705 1391 4096 34 ORIGIN_REP_DATA **1975** 2121 4096 52
That tablespace only holds these materialized views. There's no other object there being used.
I tried the advisor to see what can I do:
variable id number; begin declare name varchar2(100); descr varchar2(500); obj_id number; begin name:='REPCHECK'; descr:='Replication advisory'; dbms_advisor.create_task ( advisor_name => 'Segment Advisor', task_id => :id, task_name => name, task_desc => descr); dbms_advisor.create_object ( task_name => name, object_type => 'TABLE', attr1 => 'REPLICATION_SCHEMA', attr2 => 'ORIGIN_TABLE', attr3 => NULL, attr4 => NULL, attr5 => NULL, object_id => obj_id); dbms_advisor.set_task_parameter( task_name => name, parameter => 'recommend_all', value => 'TRUE'); dbms_advisor.execute_task(name); end; end; /
And it says
Perform re-org on the origin_table object, estimated savings is xxx bytes
If I try querying recommendations through procedure:
select tablespace_name, allocated_space, used_space reclaimable_space from table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'))
ORIGIN_REP_DATA 100663296 38419844
But I only get errors when trying to run SHRINK SPACE or COMPRESS options
ORA-10635: Invalid segment or tablespace type 10635. 00000 - "Invalid segment or tablespace type" *Cause: Cannot shrink the segment because it is not in auto segment space managed tablespace or it is not a data, index or lob segment. *Action: Check the tablespace and segment type and reissue the statement
Long story short: What can I do to avoiding disk space wasting in this materialized views? How to perform maintenance on them? Shall I drop them and recreate them? Datafiles usage in tablespace is growing about 10GB per month and I'm running out of time (and space). Thanks.