Estimate Age or Size of ADR Home in 18c

With the release 18c, Oracle introduced the capability to calculate the size of an ADR Home before changing the purging policies. In this short blog post, I will demonstrate this new feature.

Get current Settings

With SHOW CONTROL you get the current settings of the purging policies. For readability I removed the last columns.


ADR Home = /u00/app/oracle/diag/rdbms/l18ceec1/L18CEEC1:
-------------------- -------------------- --------------------
1088192480           720                  8760
1 row fetched

The current setting for SIZEP_POLICY is not visible in this output. To get this information, you have to SELECT it by your own from the ADR_CONTROL_AUX relation.

ADRCI> SELECT sizep_policy FROM adr_control_aux;

ADR Home = /u00/app/oracle/diag/rdbms/l18ceec1/L18CEEC1:
1 row fetched

Estimate Age/Size

You can either estimate the size of the ADR Home based on the short, long or both policies in hours or the opposite way by providing a maximum size of the ADR Home. If the latter is used, the estimation will provide the required settings for the short and long policies. To do this the new command ESTIMATE was introduced – which is not documented yet.

ADRCI> estimate (SHORTP_POLICY = 720)
Short Policy Hours: 720
Long Policy Hours: 8760
Size Policy Bytes: 1429120026

ADRCI> estimate (SHORTP_POLICY = 720, LONGP_POLICY = 1440)
Short Policy Hours: 720
Long Policy Hours: 1440
Size Policy Bytes: 1426168905

ADRCI> estimate (SIZEP_POLICY = 10485760)
Short Policy Hours: 2
Long Policy Hours: 34
Size Policy Bytes: 10485760

If you choose a too small size, the calculation for the short and long policy values will fail.

ADRCI> estimate (SIZEP_POLICY = 1024)
Short Policy Hours: Unknown due to lack of information
Long Policy Hours: Unknown due to lack of information
Size Policy Bytes: 1024

Update Policies

To adjust the policies you use the SET CONTROL command.

ADRCI> set control (SHORTP_POLICY = 2)
ADRCI> set control (LONGP_POLICY = 34)
ADRCI> set control (SIZEP_POLICY = 1024)

Because of the missing documentation, I cannot say, if setting SIZEP_POLICY has an effect – but at least no error is raised, when you set it.


Leave a Reply

Your email address will not be published. Required fields are marked *