In this blog I put together some best practices to consider when using Oracle Database with Sterling B2B Integrator.
Character set : Mandatory value AL32UTF8.
Cursor sharing : the SI documentation recommends to use the Exact setting. However the Force setting can also be used this will reduce parsing CPU overhead caused by some SFG queries not using bind variables.
Processes : should be greater than the sum of DB pool connections across all nodes. This is to avoid the ORA-02000 maximum number of precesses exceeded error.
Open cursors: per session. The documentation suggests to use 2000 or higher. This is to avoid the ORA-01000 too many open cursors error.
Redo logs: redo logs should be implemented on a dedicated fast alternating disks for optimal performance. Size your redo logs so that you do not switch logs more than once every 12 to 20 minutes. If redo logs are undersized, switching frequently or not fast enough, this will translate into a high COMMIT wait events in AWR reports. These particular events will impact severely the application performance.
Query tuning: Analyse bad queries Explain plans, run advisor, add suggested indexes/profiles, test, repeat …
Analyse AWR, ADDM and ASH reports:
Analyzing these Oracle reports is crucial to understand your application performance metrics. Always compare a baseline AWR report taken when the Database is well performing with a report taken when the DB is slow or having performance problems. ADDM or advisor reports are also very useful and provide information about bottlenecks and where DB time is spent the most.
Rebuild indexes or not rebuild? Big debate!
Rebuilding indexes will free some space and make you feel good for a while but that space will be used again and the index will be right back where it was a few days ago. Avoid this intensive operation as it will rarely increase the performance of well tuned queries. Rather make sure your queries have an optimal execution plan and using efficient indexes. In some occasions you will need to add custom indexes to improve the execution plan of some SQL queries. Look for the Segments by table scan section in your AWR report. This will give you an idea about queries doing full table scans instead of using indexes.
The graph below from Oracle Entreprise Manager OEM from a typical high volume installation, shows the huge impact of Index rebuilding on the Database I/O. It also shows that the performance will be the same after the index rebuild.
The official documentation states that: Sterling B2B Integrator is a high-volume OLTP application, and rarely, there might be times when you might need to shrink the tables or rebuild indexes.
RAC: Do not use RAC load_balance option in the JDBC url as it will result in performance issues. It should be set to off LOAD_BALANCE=OFF. You should also turn off the load balancing on the Database service (server side).
RAC supports high availability. Set FAILOVER=ON in the JDBC url.
Oracle Fast Connection Failover (FCF) and Transparent Application Failover (TAF) are not supported.
Degree of parallelism of some indexes:
On large installations, removing the high degree of parallelism from indexes related to MBX_MAILBOX,MBX_MESSAGE and DOCUMENT such as SCI_PK_25 would improve mailbox listing operations. The degree of parallelism can be computed/added automatically by Oracle.
Using securefiles:
Prior to Oracle 12c, some users changed the TRANS_DATA.DATA_OBJECT associated lob segment from BASICFILES to SECUREFILES with the deduplicate option turned ON following Oracle recommendations. The securefiles option is now default since Oracle 12c.
Lastly, use SIDBMonitor to Monitor your Database growth and major KPIs.
Some useful SQL queries to understand your B2B Integrator Database.
1 Pingback