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.

ADRCI> SHOW CONTROL

ADR Home = /u00/app/oracle/diag/rdbms/l18ceec1/L18CEEC1:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY
-------------------- -------------------- --------------------
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:
*************************************************************************
SIZEP_POLICY
--------------------
1426168905
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)
Estimate
Short Policy Hours: 720
Long Policy Hours: 8760
Size Policy Bytes: 1429120026

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

ADRCI> estimate (SIZEP_POLICY = 10485760)
Estimate
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)
Estimate
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.

References

Leave a Reply

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