Follow

Recommended sizing for BEAM Data Manager tablespaces

The BEAM 2.2 Installation Guide, provides a SQL script with a starting point to create the BEAM Oracle table spaces - with the initial space sizes as highlighted at the end of this article.

Although we try to our best to use metrics for the table space sizes based on our internal FIN and HCM data, we do not have the transactional data volume as per our customers databases.

The most table space and I/O expensive area of BEAM Data Manager process, is the Job results logging, e.g., every single row of data difference found is logged at the BCN_CMP_LOG_xxx tables.

Given the dynamic nature of Data Manager, it is difficult to predict a formula for the table space sizing, because the LOG tables row volume per Job Instance will dynamically vary depending of the data being compared and the volume of the differences found. The same Job/Method/Databases may provide different data results volume from time to time, depending of Method Parameters usage.

The good rule of thumb is to keep BEAM Job instance numbers and their log results at low numbers, by using the Dashboard Maintenance features that allows to delete – purge – the Jobs from the BEAM Log tables (like PS Process Scheduler purge), which have no relevance anymore for the users.  In a job result, the results tend to become obsolete very fast by its nature.

The highest number we achieved internally  was  34 million rows in the largest log table, and this chew up almost 6.5 GB of table space.

The suggestion of a guideline document for BEAM  table space sizing is noted.

We also would like to collect metrics about table space from customers.  Please feel free to add the table sizes that worked for you as a comment.

  

The main BEAM Log tables in order of data volume:

 PS_BCN_CMP_LOG    - This is the biggest table in size and number of rows in the entire system, and  contain one row for every single row difference found in all the Jobs processed.  If the Dashboard is not maintained occasionally (purged) this will grow.   This data shows at Job “Results” pages.

 PS_BCN_CMP_LOG_SQL  - Logs every single dynamic Beam SQL executed by a Job instance –  Data is show on the Job Results “Beam SQL” page.   

 PS_BCN_CMP_LOG_HDR  - Header – One row for each  Beam Job instance. 

 ORACLE BEAM TABLESPACE (Script provided on Installation folders)

 CREATE TABLESPACE BCN_LOG DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_log01.dbf' SIZE 500M

 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

 SEGMENT SPACE MANAGEMENT AUTO

 /

 ALTER TABLESPACE "BCN_LOG" ADD DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_log02.dbf'SIZE 500M

 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

 /

ALTER DATABASE DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_log01.dbf'

 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

 /

 ALTER DATABASE DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_log02.dbf'

 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

 /

 CREATE TABLESPACE BCN_TBL DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_tbl01.dbf' SIZE 100M

 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

 SEGMENT SPACE MANAGEMENT AUTO

 /

 ALTER DATABASE DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_tbl01.dbf'

 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

 /

 CREATE TABLESPACE BCN_INDEX DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_index01.dbf'SIZE 350M

 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

 SEGMENT SPACE MANAGEMENT AUTO

 /

 ALTER TABLESPACE "BCN_INDEX" ADD DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_index02.dbf' SIZE 350M

 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

 /

 ALTER DATABASE DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_index01.dbf'

 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

 /

 ALTER DATABASE DATAFILE 'D:\Oracle\Databases\BMAPQA1\bcn_index02.dbf'

 AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk