@New2740    it is not clear from what Pega version and database version you updated from.

Can you clarify where you see slow COMMIT transactions?

To help answer your question I will quote some useful information which was provided to another client who experienced performance issues in the past.  

Our SME quoted the below references to give a brief overview of exactly what BASICFILE and SECUREFILE functionality does is provided in the links below:

https://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_tables.htm#i1012988 https://oracle-base.com/articles/11g/secure-files-11gr1  

                                 ---------------------------------------------------

"The SecureFiles functionality is a complete redesign of the implementation of large object (LOB) storage. 

The original LOB storage, now known as BASICFILE, is still the default storage method, but the SECUREFILE keyword enables the new storage method, which allows encryption for security and space savings using compression and deduplication.

Pega Platform fully supports the use of the Oracle DBMS and its related features and capabilities.  Because of the highly configurable nature of the database and those features, *** it is left to the purview of the database administrator to decide how to implement Pega on top of the DBMS ****.

Because the database can be extensively customized in a computing environment, Pega provides a basic implementation of the product in the installation - this basic implementation makes use of only the fundamental pieces of the DBMS - not the advanced features - thereby simplifying the installation, and permitting the Database administrator the flexibility to decide what advanced features to implement in support of their own environment.

 Examples:

- the distribution of Oracle Tablespaces:        a DBA has the flexibility to design their hardware implementation in such a way as to disperse their tablespace configuration across their hardware storage solution (ie a storage array) to maximize performance.             This is acheived by dispersing INDEX data and table space across across separate physical devices.

- Oracle table partitioning:       by segmenting how work item data is stored within the Oracle database tablespace structure, it is possible to enhance SELECT performance by reducing the number of table instances to sample when satisfying a WHERE clause.  By partitioning very large tables, data can be "presorted" on some attribute (ie a datetime value), and improve the performance of instance lookups.

Neither of these features is invoked in a Pega implementation "out of the box" - invoking these features is left to the discretion of the DBA.

So it is with the use of BASICFILE and SECUREFILE when defining the use of LARGE OBJECT (LOB) data within a DBMS table definition.

Looking at the Schema Definition script for Process Commander v8.5.1, and looking at a typical table that uses a BLOB definition, we see:

CREATE TABLE schemaname.pr_notification_channels (     "PZINSKEY" VARCHAR2(512 CHAR) NOT NULL,     "PXOBJCLASS" VARCHAR2(96 CHAR) NULL,     "PXINSNAME" VARCHAR2(512 CHAR) NULL,     "PXSAVEDATETIME" DATE NULL,     "PXCOMMITDATETIME" DATE NULL,     "PXCREATEDATETIME" DATE NULL,     "PXUPDATEDATETIME" DATE NULL,     "PYRULESETNAME" VARCHAR2(255 CHAR) NULL,     "PYCHANNELNAME" VARCHAR2(255 CHAR) NULL,     "PYCHANNELDESCRIPTION" VARCHAR2(255 CHAR) NULL,     "PYCHANNELCLASS" VARCHAR2(255 CHAR) NULL,     "PYUSERDEFINED" VARCHAR2(5 CHAR) NULL,     "PZPVSTREAM" BLOB NULL )

NOTE that in ALL Process Commander tables, wherever a BLOB object is defined, it will be named as property PZPVSTREAM.

As in all invocations of the BLOB type within the schema creation script, the property is simply defined.  In this example:

"PZPVSTREAM" BLOB NULL

This example defines the property PZPVSTREAM as a BLOB; the definition does not define which storage type to use - either BASICFILE or SECUREFILE.  The reason why is because the selection of an advanced/new feature like SECUREFILE is left to the discretion of the DBA.  Notice that when we look closer at the definition of the SECUREFILE keyword, certain aspects of the environment must be known.  

Example:

CREATE TABLE PR_NOTIFICATION_CHANNELS (   ...   PZPVSTREAM BLOB NULL   ... ) LOB (PZPVSTREAM) STORE AS SECUREFILE <SFSEGNAME> (   TABLESPACE  <TABLESPACENAME>   RETENTION <MODE>   CACHE LOGGING   STORAGE (MAXEXTENTS <X>) );

In the above example, in order to invoke the SECUREFILE functionality, the DBA would have to specify WHERE to store the SECUREFILE LOB data as well as a retention attribute and tablespace extents data.  None of this could be defined by Pega in an install script - this would have to be custom defined by the DBA.

This is why Pega does not specify what kind of LOB storage to use - this is advanced Oracle Functionality that your DBA has to choose and implement".

                -------------------------------------

Suggestion: We would suggest that you configure SECUREFILE for use with the following Pega tables:

- Work Item Tables (example: PC_DATA_WORKATTACH, PC_WORK_ACCEL, PC_WORK) - History Tables (example: PC_HISTORY_WORK, PR_HISTORY_DATA, PR_HISTORY) - Data Item Tables (example: PR_DATA_FILE, PR_DATA, PR_DATA_ADMIN) - Assignment tables (example: PC_ASSIGN_WORKLIST, PC_ASSIGN_WORKBASKET, PR_ASSIGN) - Rule Reference tables (example: PR4_RULE_HTML_PROPERTY, PR4_RULE_APPLICATION, PR4_BASE, PR4_RULE, PR4_RULE_SECTION) - Pega Database Cache Tables (example: PR_SYS_CACHE_ENTRY, PR_SYS_CACHE_IMPL, PR_INDEX) - OTHERS: PR4_FIELDVALUE, PR_LOG

NOTES: - this list is generated with consideration to a PROD environment. - this list is not exhaustive; some testing may reveal other tables that might need to be included in this list (depending upon business logic config) - depending upon the roles and parties used in business flow processing, other tables (ie PR_OPERATORS) might have to be included. - Declarative Processing (ie DECLARE-INDEX): include any tables derived from PR_OTHER.    You did not provide examples of which COMMIT statements are causing issues.

Questions:

 1.  Can you give us details from which Pega version you upgraded, and from which database?

 2.  Were there any errors in the upgrade logs?

 3.  Please check the following details about the Hybrid Data Storage Model  and Monitoring the Stream service   and Improving database batch processing performance  

If you have a specific example and related logs that you need a more in-depth analysis for I would recommend that you open a support incident and provide the support team with the full PAL, pega logs, scenarios etc.

If you do decide to open a support ticket, could I please ask that you provide us with the INCIDENT id so that we can help track your issue?