CODEWITHSUNDEEP
oracle-databaseperformance
sorosch
sorosch

Reputation: 3

Materialized View - PCTFREE and PCTUSED

I have some questions about optimizing materialized views (MVs):

In the following tutorial it is recommended to set PCTFREE and PCTUSED to 0 or 99:

Note: If a materialized view is complete refreshed, then set it's PCTFREE to 0 and PCTUSED to 99 for maximum efficiency.

--> https://satya-dba.blogspot.com/2009/07/materialized-views-oracle.html

First question:

Is this recommendation correct? Should the two parameters PCTFREE 0 and PCTUSED 99 be set accordingly?

How is that exact procedure when the MV is updated?

The MV is created ( PCTFREE 0 and PCTUSED 99 are set accordingly):

 CREATE MATERIALIZED VIEW "SYSK85"."CMVF_01_SK85_BESTAND_2019" ("BRNR", "LEAT", "TLRTAL", "LEATSO", "STAND_DATUM", "VOAT", "AQEQ", "ELAT", "ELDT", "VSKN", "VTKZ", "ANZAHL")
 PCTFREE 0 PCTUSED 99
 NOCOMPRESS LOGGING
 NO INMEMORY 
 BUILD IMMEDIATE
 USING INDEX 
 REFRESH FORCE ON DEMAND
 ENABLE QUERY REWRITE
 AS (
 select BRNR, LEAT, TLRTAL, LEATSO, STAND_DATUM, VOAT, AQEQ, ELAT, ELDT, VSKN, VTKZ, count(*) as ANZAHL 
 from CVF_SK85_BESTAND_2019 GROUP BY BRNR, LEAT, TLRTAL, LEATSO, STAND_DATUM, VOAT, AQEQ, ELAT, ELDT, VSKN, VTKZ
);

Second question:

Should the Create of the MV be set the two parameters PCTFREE 0 and PCTUSED 99?

But what if the MV is updated?

EXEC DBMS_MVIEW.REFRESH('CMVF_01_SK85_BESTAND_2019', 'C', atomic_refresh=>FALSE);

Should the two parameters PCTFREE and PCTUSED be reset again explicitly after the update (so that the database can reorganize the data - if necessary)? Or is not that necessary?

Thank you very much

Kind regards

George

Upvotes: 0

Views: 256

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21075

The imporatant point is the limitation to the complete refresh of the MV.

Such MV are refreshed either by truncate and insert or delete + insert, so the recommendation boils down to a trivial statement that you should not reserve space for future updates that would newer happen in complete refresh (PCTFREE = 0).

This will lead to a decrease of the size of the MV.

PCTUSED plays IMO no role in this scenario as there are no subsequent inserts.

Both parameters remain valid until they are explicitely changed with ALTER TABLE.

Summary

If you are unsure about the refresh strategy, I'd never touch those parameters.

If you know that you only make full refressh and you make a lot of full tables scans on the MV - you may see an optimizing effect. While doing normal OLTP index access and nested loops joins on the MV you'll hardly see a difference.

Upvotes: 1

Related Questions