Hello ,

We're currently facing a performance issue in our FileNet Content Platform Engine environment. The DOCVERSION table in our Object Store database has grown to around 45 GB, which is now causing noticeable slowness in query performance - particularly when users retrieve document versions or when workflows query document metadata.

We are exploring possible options to improve performance and wanted to ask the community and IBM experts:

• Is database partitioning a supported approach for the DOCVERSION table in FileNet CPE?

• If partitioning is not supported, what are the recommended best practices or IBM-approved solutions for handling large DOCVERSION tables?

Environment Details

• Product: FileNet Content Platform Engine 5.5.7

• Database: Oracle 19c
  
  Any insights, experiences, or official guidance on managing large DOCVERSION tables would be greatly appreciated.

Dear,

We had faced similar issue earlier and opened the PMR and support team suggested create below index and then performance improved.

"CREATE INDEX "xxxOS "."UI_UC7B7_OWNERDOCU_IBM" ON " xxxOS "."DOCVERSION" ("UC7B7_OWNERDOCUMENT" ASC,"RECOVERY_ITEM_ID" ASC,OBJECT_ID ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS "

 "CREATE INDEX "xxxOS "."IDX_COMPOSITE_PERFORMANCE" ON " xxxOS "."DOCVERSION" ("HOME_ID" ASC,"RECOVERY_ITEM_ID" ASC,OBJECT_ID ASC,"UBE06_SEARCHTYPE" ASC,"VERSION_STATUS" ASC) COMPRESS NO INCLUDE NULL KEYS ALLOW REVERSE SCANS"

Once created runstats and reorg the db.

Thank you,

Asif

Our docversion table is 800GB and recently exceeded 1 billion rows. 

Indexing and query design is key, size of table is not the only determining factor to performance. 
But I have to mention we are running on Exadata which has a lot of pulling power. 

If not already done, use the ADDM reports to check if there are any suggestions to improve performance. 

Dear Mahmoud,

Are you currently storing the objects into your database as well? Looking at the size 45 Gb) makes me wonder if you did. If so you can use advance storage areas - ASA (preferably S3 object storage) for the objects. That will decrease the database for the metadata significantly and boost performance.

Database partitioning is legit and will help from the database perspective bt in our diuscussion with IBM they said the support options in case of problems will be hard. At first there was none but with a little leverage is changed to hard as IBM doesn't have en environment to simulate any issue that you may encouter in your production environment.

It is possible and IBM is currently looking into supporting partioning. Best is to check with your IBM representative if they wanna do that for your case.

But again I would need to know more about your implkementaion to provide more insight into the options.

FileNet is not the issue as we ingest 500 objects/second - 40 million a day growing towards 100 billion objects. But we choose to use multiple object stores maxed on 4 Tb mainly because of backup&restore window requirements.

So first: objects in database or on ASA? Second, IBM is looking into support for partioning, check with IBM and last, why just one object store?

I hope that this helps you. Feel free to reach out.

------------------------------
Ronald Heerema
Architect
Dutch Tax Office
Apeldoorn
+31613009685
------------------------------

This is information I received from the FileNet database specialist several years ago...

We don't officially support Oracle partitioning but have a few customers that have implemented it on their own. There are many different ways in which Oracle partitioning can be configured. We (IBM) do not document how to configure or work with Oracle partitioning, nor will we be able to help customers configure or troubleshoot Oracle partitioning related issues. We are tolerant of P8 Content Engine running in an Oracle partitioning environment, as long as the customer, along with Oracle support, takes full responsibility for any Oracle partitioning related issues.

Generally we have not found that partitioning is necessary for good performance.  Partitioning helps manageability:  Backup, reorg, attach/detach, dbms_stats, etc.  Ours is predominantly an OLTP application: Queries return a small number of rows, single row updates/deletes, joins are on a GUID column (not typically partitioned on), and queries cannot use partition pruning in all cases.  Parallel options can hurt performance (https://www.ibm.com/support/pages/node/151727). 

One large customer did try partitioning and encountered good performance for queries that made use of the partitioning key (e.g. create_date), but a performance hit for queries that did not.  They found that the benefits you get from partitioning for queries that constrain by the partitioning key (whereby the search can be limited to just a single partition) are significantly outweighed by additional costs for queries that are not partitioning key-constrained.  The issue was most noticeable on join queries as are used with list property retrieval, folder browsing, and other application joins.  This large customer then reverted back to a non-partitioned table and has good performance with more than 1.2 billion rows (at last count some time ago).  Good indexing solutions and application design should support billions of rows.

So having said that, we don't generally have a lot of data on partitioning since as mentioned we've only had a few customers implement it, and no word on these other customers success in that regard.

You could engage our Expert Services to perform health check, and they will help you identify updates to your Oracle and Application Server configurations that might improve performance. The other strategy is to determine whether you should roll over to a new object store on a regular basis to avoid performance issues.

And as a side note, P8 5.5.7 has been out of fix support for several years, and you should consider upgrading to the latest version: 5.7.0 (released in June 2025).

This is too vague a question. As Olaf states, "Indexing and query design is key".  I am assuming you created the indexes from the best practices tuning guides? If the workflow had a reference to the object_id, it would have the primary key and wouldn't need to search. Can the properties you use be stored with the workflow? If docversion queries are slow, it may be that you have too many indexes or indexes of the wrong type? allowing ad-hoc queries? not using property filters in your queries? asking for columns in the results that require a backend join? All of the above?