While implementing some additions to the existing Oracle Incentive Compensation implementation at our customer site we got complaints from the end users about the performance of some of the processes in Incentive Compensation.
Specifically adding a new rate table to an existing plan element was taking a lot more time then expected. At our customer site this is actually done using a customization which first duplicates all existing rate tables of one month to the next month and then, after updates are made by the users, links the new tables to the existing plan elements. This linking process was taking about 25 minutes.
It should be fairy simple task for Oracle: make a connection of one rate table to one plan element. But when digging into this I found out that each commission rate in the rate table is linked to each individual resource that has the plan element assigned to him. In some extreme test cases we had multi dimension rate tables with over 20000 commission rates linked to all of the 400 resources. That’s 8.000.000 lines instead of the one tables entry I expected. The above extreme case even let to a “Can’t extend Undo Table Space…†error when trying to link that table to an existing plan element. All these entries are saved in the table cn_srp_rate_assigns_all, which by then had about 75.000.000 entries. While the table holding the actual commission rates had about 300.000 entries.
So I started investigating the reason why each commission rate is saved for each resource. Soon I found out that it is possible to change the plan element details and the commission rates in the rate table for each individual resource. Something that might be useful for some implementations but not for the implementation at our customer: All resources who are assigned the same plan element should have exactly the same commission rates.
I was expecting something like this:
Oracle was actually doing something like this:
I then came across a profile option called: OSC: Default Custom Flag. This profile option was set to Yes which is the default setting. So by default each commission rate can be changed for each resource.
The customize flag can be changed at the resource screen. So you can still change the commission rates at the resource level if necessary. So the solution for a problem was to change the default setting of the profile option. And also change the custom flag on all existing resource – plane elements assignmnets.
Changing over 2800 customize flags (400 resources x 7 plan elements) manually was a bit too much so we build an update tool using a standard oracle API cn_srp_customize_pub.change_srp_quota_custom_flag.The performance of this update was about to cause problems itself, because I estimated it would be running for10 full days based on calculations from a test environment. The production database was a lot faster; still the update tool ran for more then 2 full days.
These changes did improve the performance of at least the following processes
Linking new rate tables to existing plan Elements: -82% (now less then a mintue)
Adding rate tiers to an existing Rate Dimension: -97%
Calculate process: -27%
It also saved around 6 gig on the server.
And another problem from the past was also solved: Previously when changing a commission rate in the rate table after it was linked to a Plan element the rate table had to be removed and linked again to the Plan element. Otherwise the unchanged commission rate at the resource level would have been used. Now only the commission rates at the Plan Element level are used.
On metalink there’s not a lot of information about the customize flag and on all subjects about performance and incentive compensation this is not mentioned. It might be that the above situation is very specific to our customer because the have many resources and also many multi dimension rate tables. But a bit more information about this profile option from Oracle might have triggerd the initial implementation team and made them reconsider the default settings.
