Oracle 11gR2 APM database is growing very large

Document ID : KB000020013
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The APMDB database can become very large even when it contains very little data. The problem is seen with Oracle version 11.2.0.2 and above, and is especially noticeable on Introscope only deployments.

Solution:

Beginning with Oracle version 11.2.0.2, the default first extent size has increased from 64K to 8MB per partition, thus an empty or small partition created as part of daily aggregation will use significantly more space. This is very noticeable with Introscope-only deployments where CEM data is not being generated.

You can use the following query to determine the current first extent size:


 	SELECT segment_name,       
 	  segment_type, 
 	  partition_name,
 	  bytes/1024/1024 AS MB
 	FROM user_segments where segment_name like 'TS_ST%';

The article below has further details:

http://oradbastuff.blogspot.in/2011/04/initial-extent-size-of-partition.html

Executing one of the following queries before creating the APMDB Schema will revert Oracle 11gR2 to the prior default first extent size:


 	ALTER SESSION SET "_partition_large_extents"=false; 
 	ALTER SYSTEM SET "_partition_large_extents" =FALSE SCOPE=BOTH; 

The Alter System query changes the entire instance permanently, whereas Alter Session affects only the current connection, thus the Alter Session statement would need to be added to the scripts that APM submits to create the database schema.

Both of these statements only affect new objects created after they have been executed. Existing schemas are unaffected.