Siebel Database Performance Tuning Guide for Oracle

Oracle has published on Oracle Support an update to the whitepaper “Performance Tuning Guidelines for Siebel CRM Applications on Oracle Databases“. It has more recent details on best-practices specific to Siebel implementation on Oracle 12c. Along with this update, updates to the SQL scripts for best-practice optimizer statistics collection and Siebel workload profiling have been added.

And it’s always good to refresh your memory from time to time. Reading the whitepaper again, I read about this core database feature I really did not know: “SQL Plan Management”.

Just from reading a very useful feature to take knowledge (and advantage…) of e.g. when migrating between Oracle database releases, as it can counter and mitigate risk associated with such an excise. It addresses the issue of ensuring plan stability. Although plans might change to good, the real world can be more challenging. The authors write about SQL Plan Management:

When SQL plans are required, it is strongly recommended to use SQL Plan Management (SPM) to record and evaluate the execution plans of SQL statements over time. SPM provides the ability to build baselines that are known to be efficient and that are composed of existing plans or plans imported either from other instances or previous versions. SQL plan baselines are used to maintain performance of the matching SQL statements. Over time, new plans are evaluated and preserved when their performance improvements are significant. Subsequently, these plans can be evolved to “accepted” if their performance is superior to the baseline’s SQL plan.

If you’d read a bit further, it addresses actually a broader set of scenario’s such as:

• A database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements. Most of these plan changes result in either no performance change or improvement. However, some plan changes may cause performance regressions. SQL plan baselines significantly minimize potential regressions resulting from an upgrade.

• Ongoing system and data changes can impact plans for some SQL statements, potentially causing performance regressions. SQL plan baselines help minimize performance regressions and stabilize SQL performance.

• Deployment of new application modules means introducing new SQL statements into the database. The application software may use appropriate SQL execution plans developed in a standard test configuration for the new statements. If the system configuration is significantly different from the test configuration, then the database can evolve SQL plan baselines over time to produce better performance.

I suppose I need to dive a bit deeper into this core database functionality and how well it applies to the many-many execution plans which get calculated by Oracle when running a Siebel implementation.

As said, good refresher 🙂

– Jeroen

Keep That Siebel Session Alive

In a recent article I described a new feature introduced in IP15.3 called ‘white listing’ where one could define invocations which would not lead to a reset of the SessionTimeout timer. Particularly interesting, because one could ‘white list’ the Message bar (or better call it ‘Notifications’), so that your session would nicely time-out after the configured time. Regardless whether or not you are pulling broadcast messages from the server at the fixed interval at which it is configured.

On the other side of the medal, there are these scenario’s where one would like to prevent a session time-out to occur. Typically a session time-out is configured at say 30 minutes or so. But there are always use-cases where 30 minutes in general would apply, but not in 100% of the cases.

Working with a financial services customer, an important group of their end-users complained that while writing call reports, they quite often lost their work due time-outs. How frustrating that would be, is clear. Pulling the cables and throwing your laptop of desktop out of the window (especially if it happens on Friday COB). You could try to train, and tell them to commit once and a while. But that will take a way only a percentage of the pain. And the behavior simply is damaging to the overall UX!

Trying out a simple approach, which would work in both HI and OUI – I came to the following rough-edged solution.

1. On the Applet_Load (= browser script) call a function “keepAlive()”
2. This keepAlive() function would, well, keep the session alive.

But how? The bare minimum would be just these few lines of code:

There is no magic, just a recursive function call every 5000 milliseconds. During this recursive call, the call to theApplication().GetProfileAttr() forces a server-rountrip. And it keeps your session alive. Of course, 5000 is just a sample figure. Reality, this should be a bit less than the SessionTimeout configured the eapps.cfg.

The beauty of this tiny solution, you can make it very selective. Add some little logic such that the code. Simple as that.

The same would work for both HI and OUI clients. If you have to be in an OpenUI-only environment, I would re-write it as a physical renderer instead. Leading practice: prevent leveraging traditional browser scripts. At some point in time these will be deprecated.

– Jeroen

Tools Please Do Not Crash On Me…

Victim of Tools crashes after patching to either IP15.2/14.8 or IP15.3/14.9 ? Well you will be more than likely affected by Tools crashes while opening any script editor. So far the closest ‘workaround’ would have been to stay with Tools on IP15.1/14.7. But Oracle always (strongly) recommend compiling with the Tools release which is on-par with the patch set the enterprise is running on.

But… Although the root-cause of this bug is under triage at this moment, it came out that a temporary workaround would be to rename the file ‘samatrix.txt’ in the \bin folder.

Interesting file, looking at its contents. It lists all the VB and eScript methods for the different objects. After re-naming all seems to be in perfect order. Opening scripts editor is fine, auto-complete stills works for strong-typed objects, syntax checking is not broken and we can compile objects too. So, is their a real use for this particular file? Debatable because apparently the file in question only ships with the ‘release build’ of Siebel (which customers download), but it’s not available on in-house development environments. That is why initially reproducing the crash led nowhere 😉

– Jeroen

How Do I Benchmark Siebel Open UI?

I got some remarks lately on “how” I benchmark the different Siebel patch sets which I discuss now and then against the usual suspects (IE, Chrome and Firefox). Let me first point back to this old post, from a bit more than a year back. I crafted together with Duncan Ford a small framework which measures the milliseconds spend by the browser between the preload and the postload event. This framework actually made it into the must-read Oracle Siebel Open UI Developer’s Handbook. The time spend between these two events informs me about the number of milliseconds the browser needed to process the DOM and all the triggered events. And with Open UI quite a bit happens to process the ‘raw’ DOM delivered by the Siebel Web Engine to be able to finally display it.

Next I created a set of four progressively complex views, which are based of a dramatically simple virtual business component. The VBC approach makes it extremely simple to port this framework in other environment, when needed. These four views are built per below specifications where I stress that these views not necessarily are representative 😉 The complexity of view 3 & 4 definitely are out of the ordinary. They are primarily meant to identify any kind of ‘hockey-stick’ behavior in performance degradation (call it stress-testing).

View 1 (indicative: 40 controls in total)

• One Form applet with 20 controls
• One List applet with 20 list controls

View 2 (indicative: 240 controls in total)

• One Form applet with 20 controls
• One Form applet with 100 controls
• One List applet with 20 list controls
• One List applet with 100 list controls

View 3 (indicative: 440 controls in total)

• One Form applet with 20 controls
• Two Form applet with 100 controls
• One List applet with 20 list controls
• Two List applet with 100 list controls

View 4 (indicative: 640 controls in total)

• One Form applet with 20 controls
• Three Form applet with 100 controls
• One List applet with 20 list controls
• Three List applet with 100 list controls

When I run my tests, I make sure my laptop is completely offloaded and running at 0-5% CPU. Running the good-old Siebel Dedicated Client is fit-for-purpose to test the views 1 thru 4 in a variety of browsers. First I do a ‘warm-up’ cycle touching all views. Next I take the measurements at least three times, and if the standard deviation between these measurements is too high I re-run the test. Et voilá. That’s it – no rocket science – but an apples & apples comparison.

Below a graph focused on IE11 which just compares IP15.1 (June) with IP15.3 (August) to demonstrate the terrific improvement. Yes, even IE can perform (though still lacking its competition, but anyways).

Click for interactive chart

– Jeroen

Patch Set 15.3/14.9 Skyrockets Performance!

This result deserves truly a big applause. Oracle development worked closely together with Microsoft engineers to boost Open UI performance. With IP15.3/14.9 a complete overhaul of the List Applet renderer has been delivered. This went into the release quite silent, with no notice in the patch set’s release notes. The improvements were focused to give a boost for Internet Explorer, but the improvement is evident for Chrome (hence other browsers, including Edge) too!

Without more wording, just have a look at the results! These results are just amazing. We can safely say that Open UI will come close or exceed what you have ever experienced with High Interactivity clients. Think of the improvement it will make on mobile deployments for tablets… Skyrocketing performance.

– Jeroen