Oracle BRM: Is your BRM application slow though the database is super responsive?

 When the batch applications or real time operations are slow in Oracle BRM, we can use the module named 'perfLib' to identify any bottlenecks especially when the bottlenecks are on the application side rather than on the database or the network.

Note: Contact Oracle support to get this library which suits your specific operating system and the version of BRM.

To fix, performance issues in your BRM code, do the below.

• Identify and fix memory leaks.
• Use Perflib to fix performance bottlenecks.
• Diagnose database response using AWR/ADDM/ASH reports.
• Do hardware sizing when required.

It is very important to fix memory leaks to achieve a good performance, because connection manager(CM) in BRM will have the ceiling limit of 2 GB. Once the leaked memory reaches to 2 GB for all the child threads of CM then it will generate a core-dump and halts further processing. We will discuss in another post on how to diagnose and fix memory leaks in detail.

Using perflib tool you may identify the bottlenecks in different areas.

• In custom code or customized policy opcode.
• In OOTB (Out of the box) code

You might be able to fix/optimize if it is custom code, but incase of OOTB one should reach Oracle's support team by raising an SR. Most of the OOTB performance issues can be fixed only from BRM's 7.5 onwards.

How to fix performance issues with PerfLib

Download the tool “PerfLib” from the link below for your BRM server Operating System.

Oracle Communications Software Downloads

Install it under $PIN_HOME/perf_test or any of your desired folder.

Do not install this tool in your production server.

stop connection manager (stop_cm)

Now, go to the folder where you installed ‘perflib’ and you will find a file named “start_it”, use below command to start connection manager.

./start_it start_cm 

Now, do the operation that you want to identify the bottleneck of performance. You can use the following options to do your operation.

• Testnap
• CRM (Siebel or any custom GUI)
• SoapUI
• OSM (Oracle Order Management)

For example, let us diagnose a slow invoicing process.

Especially, in invoicing you shouldn’t use “PIN_FLIST_COPY” of the input flist in “fm_inv_pol_prep_invoice.c” policy opcode file. Because each invoice input flist will be around 20 MB for an account with 50 services and 100 products. Hence one should eliminate it in first place.

Out of the box Opcode for invoicing is ‘PCM_OP_INV_MAKE_INVOICE’.

• Run invoicing for one account using the opcode. Perflib tool will generate a trace file. Execute the following command to get timing by each operation within the OOB opcode.

perl perf_parse.pl -s0 –rlCpS <name_of_trace_file> 

After executing above command, you will see below output.

As you see in above image, Total operation is taking about 343 seconds in total and there is a search in database operation in “fm_inv_pol_prep_invoice.c” at line number 3478 and this search is taking around 248 seconds and doing 6 round trips to database. Once after fixing the problem(in this case i removed the search operation , compile the code and restart CM (./start_it start_cm).

Execute above perl command to see if the timing is ok now.

Invoicing completed in ~47 seconds as you see in above picture. Thus saved around ~6–7 times of invoicing time for each account. Though it shows 47 seconds for one invoice, after using “pin_inv_accts” MTA one can generate thousands of invoices per minute based on the number of children processes defined in MTA config(pin.conf) file.

In another post, we will discuss on how to fix problems on database server if database response is slow to application.

After doing the above exercise, one should consider sizing of the server for BRM if desired results are not achieved. A server with 2 Giga hertz of clock speed will definitely perform better than a server with 1 Giga Hertz of clock speed.

CPU and RAM utilization reports will also show the list of processes consuming high amounts of resources, hence optimization of hardware is the last resort.

For maintaining your database, consider the following as well. You will need to have retention policy in place to find out the things that are eligible for purging.

1. List down all the performance issues and then find out whether the application is slow or the database is slow or is there a network component causing the slowness.
2. Application performance bottlenecks can be identified further by using perflib
3. Database performance bottlenecks can be identified by using AWR, ASH and ASSR reports
4. Network latency can be monitored by using Nagios etc., 
5. Do not load those which are not needed to be in BRM, example: usage charges with $0 impact.
6. Do not create temporary tables which are not being used by BRM application.
7. Purge events by using partition_utils with object as /event and without using force option. 
8. Purge items by using partition_utils app with object as /item and without force option.
9. Purge sub balances which are expired - pin_sub_balance_cleanup
10. Purge audit tables - purge_audit_tables.pl is the app
11. Consider generating virtual columns for event table which can save plenty of space in the database
12. Make sure the partitions are created for all big objects in BRM including those created as custom.