How To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary (Doc ID 422826.1)

 In this Document
Goal
Solution
References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.4 to 10.2.0.5.0 [Release 9.2 to 10.2]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.0.1.4 to 9.2.0.8



GOAL

The Purpose of this Note Is To explain the behavior of PCTVERSION and RETENTION  in LOB Segments.

The Following Example shows the issue.

Create a LOB specifying PCTVERSION = 5 and create another one specifying RETENTION.
When query DBA_LOBS, would not expect to see default PCTVERSION = 10 when RETENTION was specified.

SQL> CREATE TABLE lobpctversion
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) pctversion 5);

Table created.

SQL> CREATE TABLE lobretention
(LOBLOC blob,id NUMBER)
LOB ( lobLoc ) STORE AS
(TABLESPACE users STORAGE (INITIAL 5k NEXT 5k PCTINCREASE 0) retention);

Table created.

SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name in ('LOBPCTVERSION', 'LOBRETENTION');

TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
----------------- --------------------------- ---------- ---------
LOBPCTVERSION SYS_LOB0000096861C00001$$ 5 10800
LOBRETENTION SYS_LOB0000096864C00001$$ 10 10800




> />see that both PCTVERSION and RETENTION columns have values

As You can see, would expect table named LOBRETENTION to clearly show 0 for PCTVERSION since RETENTION was specified. Likewise, LOBPCTVERSION should show only PCTVERSION. So when query DBA_LOBS down the road would have no indication which is truly active.

Also if You Change the Storage Parameters the Dictionary does not change.

> />

SQL> alter table lobretention modify lob(lobLoc) (pctversion 5);

Table altered.


SQL> select table_name, segment_name, pctversion, retention
from dba_lobs where table_name = 'LOBRETENTION';

TABLE_NAME SEGMENT_NAME PCTVERSION RETENTION
----------------- --------------------------- ---------- ---------
LOBRETENTION SYS_LOB0000096864C00001$$ 5 10800



see the change in value under PCTVERSION, but both PCTVERSION and RETENTION columns are populated.

SOLUTION



This Issue Identified as Bug 3844939 and its fixed in 10.2 and higher

There is Work Around to Identify Which Truly Active Value PCTVERSION OR RETENTION , by Running The Following Query :

select bitand(flags,32) from sys.lob$ where OBJ#= (select OBJECT_ID from dba_objects where
OWNER=<schema> and OBJECT_NAME='<table_name>');



if the output is 0 then its PCTVERSION , if its 32 then its RETENTION

Or

create or replace view DBA_LOB_RETENTION
(OWNER, TABLE_NAME, COLUMN_NAME, PCTVERSION, RETENTION)
as
select u.name, o.name,
decode(bitand(c.property, 1), 1, ac.name, c.name),
decode(bitand(l.flags, 32), 0, l.pctversion$, to_number(NULL)),
decode(bitand(l.flags, 32), 32, l.retention, to_number(NULL))
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac, sys.lob$ l,
sys.user$ u
where o.owner# = u.user#
and o.obj# = c.obj#
and c.obj# = l.obj#
and c.intcol# = l.intcol#
and c.obj# = ac.obj#(+)
and c.intcol# = ac.intcol#(+)
and bitand(c.property,32768) != 32768 /* not unused column */
;

select * from DBA_LOB_RETENTION where TABLE_NAME='<table_name>' ;


The RETENTION parameter is designed for use with Undo features of the database, such as Flashback Versions Query. When a LOB column has the RETENTION property set, old versions of the LOB data are retained for the amount of time specified by the UNDO_RETENTION parameter.

You Cannot Specify Both PCTVERSION and RETENTION for LOBs Creation.
Also You Cannot Specify RETENTION if the Database is Running in Manual Undo Mode.

PCTVERSION :

Specify the maximum percentage of overall LOB storage space to be used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until 10% of the overall LOB storage space is used.

RETENTION :

If the database is in automatic undo mode, then you can specify RETENTION instead of PCTVERSION to instruct Oracle to retain old versions of this LOB. This clause overrides any prior setting of PCTVERSION.  NOTE:  RETENTION will remain constant in DBA_LOBS even after subsequent changes to UNDO_RETENTION.  However, LOB undo should take into affect changes to the parameter UNDO_RETENTION over time. 

REFERENCES

BUG:4231990 - NOT POSSIBLE TO IDENTIFY VERSION RETENTION STRATEGY OF A LOB SEGMENT
NOTE:415845.1 - Lob Segment Size Increases in Dictionary Managed Tablespace: Freelist Blocks Are Not Used
BUG:3844939 - DBA_LOBS COLUMNS RETENTION AND PCTVERSION CONTAIN WRONG VALUES

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more