I've heard from a couple of customers recently asking about BPM audit table growth, specifically BPM_AUDIT_QUERY. It led me to investigate the impact of the various audit levels in SOA/BPM on these table and to propose options to them.
It is important to note up-front that BPM is a human-centric workflow application and therefore should be expected to audit often and in detail... the reality is that business users probably will want to know who did what and when, and also who didn't do what when they were supposed to. BPM auditing is very rich and can provide this kind of information and more. The "downside" of this is that audit tables can grow at a faster rate than expected, and BPM_AUDIT_QUERY is normally the most prominent of these.
Clearly there are well documented strategies for archiving/purging and partitioning which can control/limit the impact of table growth but there may also be simple changes to the BPM audit settings which can prove beneficial in certain business situations.
There are essentially three places where the auditing of BPM applications can be controlled....
Now we know where to set the audit levels, let's see what impact various combinations have...
In order to demonstrate what effect the audit levels have, we'll use a very simple BPM process with one human activity....
For each test we'll run through a complete instance of this process: initiate it from the Enterprise Manager composite tester & approve the human task inside BPM Workspace. We'll then look at the number of rows added to the tables and also the instance view & flow trace in the Enterprise Manager.
For the first test we'll use the out-of-the-box settings shown in the screenshots above....
...so 8 rows added to BPM_AUDIT_QUERY, this will be two for each activity (in/out) and two for the scope of the process itself (in/out). Looking at the flow trace....
...exactly as we would expect, we can see all activities and the input & output data would be visible for all of them (as they are highlighted in blue and clickable).
A standard best practice is to "set the audit level to production" we've all read this in numerous documents/blogs/white papers, so let's see what effect this has....
...so, no different to development mode in terms of the number of rows added. What about the Flow Trace....
...we can see that visually it is the same, but not all of the data will be visible, only the data associated with the human activity is clickable. So, an interesting result... this change does not seem to have made any positive impact at all on the two tables we're interested in... we'll understand why later.
Let's see what impact turning off auditing at the composite level has....
...so, a reduction in the number of rows written to BPM_AUDIT_QUERY, But what about the flow trace....
...we can't even launch it, worse still it doesn’t even show in the list of instances….
...and even worse still, this instance will have no composite instance id and is known as "orphaned". This combination is clearly a complete non-starter.
Let's turn off auditing at the level of the BPMN engine, after turning it back on at the level of the composite....
...so a reduction in the number of rows written to BPM_AUDIT_QUERY, what about the flow trace this time....
...we just see the human activity, no more, and it is not drillable so we will not be able to see any instance data. A better result but perhaps not ideal.
Let's look at the "help" that appears when we click on the BPMN engine audit level to understand what the options mean...
...i.e. when set to "off", if measurement is enabled then it is overridden to "minimal". What does this mean ? Well, if we're collecting analytic data for our process then the engine will always need to audit regardless, so let's disable the BPMN monitors & sensors...
...no rows added to either table, but at what cost to visibility ?
...well, we still see a composite flow trace, but what happens when we drill down to the bpm process ?
...there's nothing. This may be an acceptable compromise for some composites given the huge impact it has on the BPM audit tables.
But.... we've turned auditing off at the level of the BPMN engine, this will affect everything, how can we mange at composite level ?
Let's try making one change, the composite level from "inherit" to "production"...
...we have the audit rows written again....
...and we have visibility in the Enterprise Manager.
So, we have shown that by simply changing the composite audit level between "inherit" & "production" we can effectively turn on & off the auditing at composite level.
We have seen in this blog the effects of altering the audit levels available to BPM on the BPM audit tables, BPM_AUDIT_QUERY & BPM_CUBE_AUDITINSTANCE, and on the monitoring available within the Enterprise Manager. Bear in mind we have not looked at the impact on process tracking within BPM Workspace, this will be affected.
Knowing which levels are appropriate for which composite is a business decision...it's a simple trade-off between visibility and data storage, the more visibility we require the more we have to insert, and these inserts have a dual cost... the cost in terms of storage required and the runtime cost of the SQL insert, both of which could be relevant.
If your project is impacted by the amount and/or frequency of auditing I suggest you investigate the audit levels in a similar manner to that described above to determine what is best for you & your business.
Director - Oracle A-Team
Previous Post
Next Post