Content Management and Capture

Content Management and Capture

Come for answers. Stay for best practices. All we’re missing is you.

 View Only
Back to discussions
Expand all | Collapse all

Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

  • 1.  Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 14 days ago

    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.



    ------------------------------
    Mahmoud Abd El Aziz
    ------------------------------


  • 2.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 11 days ago

    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



    ------------------------------
    Asif Pasha Senior FileNet Consultant
    ------------------------------

    Original Message


  • 3.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 11 days ago

    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. 



    ------------------------------
    Olaf Erasmus
    ------------------------------

    Original Message


  • 4.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 11 days ago
    Edited by Ronald Heerema 11 days ago

    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
    ------------------------------

    Original Message


  • 5.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 5 days ago
    Edited by Mahmoud Abd El Aziz 5 days ago

    HI Ronald
    objects in database or on ASA?
    No, We are storing objects on ASA.

    Second, IBM is looking into support for partioning, check with IBM and last?
    I already created PMR with IBM support first response that  partitioning  is not supported, I escalated  this ,and they promised to look it again

     why just one object store?
    Actually this the current setup ,As we have custom application this will need application and code changes to support cross search ,also we may need migration between object stores as we have huge data



    ------------------------------
    Mahmoud Abd El Aziz
    ------------------------------

    Original Message


  • 6.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 11 days ago

    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).



    ------------------------------
    RUTH Hildebrand-Lund
    ------------------------------

    Original Message


  • 7.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 11 days ago

    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?



    ------------------------------
    Stephen Weckesser
    ------------------------------

    Original Message


  • 8.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 10 days ago

    Hi Mahmoud, there are several DB indexes mentioned in IBM performance tuning and PMRs for slow performance which are very helpful for any P8 system. Past that you should have indexes on custom fields using the tolower function for case sensitive collation databases and use composite indexes when appropriate. Next level is moving data files, index files and log files to different disks which will yield some benefits. By far the best way to increase performance and what I would do before considering spanned tables is using enterprise SSD disk(s) or multiple drive arrays using a storage appliance of your choice.  

    One other item of consideration is the number of columns in docversion. If your ostore does not store similar documents you end up with a lot of properties and unused columns, indexing will not provide as much benefit since there will be so many null columns where the document class(es) do not use those properties. 



    ------------------------------
    Jay Bowen
    www.bowenecmsolutions.com
    Medina, OH
    ------------------------------

    Original Message


  • 9.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 4 days ago

    Hello Mahmoud,

    This is a good question and a common challenge in large-scale FileNet environments.

    As per the IBM documentation for Additional Database Information for the 5.7.0 Release (under the Advanced Technologies section), it is stated that:

    "Advanced technologies, such as partitioning, can be used. However, since these technologies might have a negative impact on performance and functionality, it is important to evaluate the use of these technologies in a non-production environment prior to using them in production."

    This means that database partitioning is technically allowed but not explicitly certified by IBM. You can implement it at the database level, but it must be carefully tested to ensure it does not interfere with FileNet CPE operations or queries - especially those managed internally by the platform.

    In addition to partitioning, I would also recommend:

    • Consulting with your database vendor (Oracle) to explore performance optimizations that can be applied independently of the FileNet application layer.

    • Performing database statistics updates, index rebuilds, and query plan analysis to identify slow operations.

    • Reviewing database I/O performance - sometimes the issue lies in the underlying storage or OS-level tuning.

    • If possible, leveraging Oracle Exadata or similar optimized database appliances, which are specifically tuned for large-scale data workloads and can offer significant performance gains.

    In short, partitioning may help, but it should be part of a broader tuning exercise that includes database, OS, and hardware optimization - all validated first in a non-production environment.



    ------------------------------
    Ahmed Alsareti
    ------------------------------

    Original Message


  • 10.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted 2 days ago
      |   view attached

    Have a look at the attached scalability technote, 5 billion objects in docversion no table spanning and this was back in 2014.  Boost your disk performance on the database and that will take care of your issues. 



    ------------------------------
    Jay Bowen
    www.bowenecmsolutions.com
    Medina, OH
    ------------------------------

    Original Message


  • 11.  RE: Performance issue due to large DOCVERSION table (45 GB) - need guidance on partitioning or best practices

    Posted yesterday

    Thanks, Jay, for sharing this valuable documentation.



    ------------------------------
    Mahmoud Abd El Aziz
    ------------------------------

    Original Message


Related Content