Performance Tuning Methodology

I recently had an opportunity to do a bit of performance tuning on a newly deployed production App and thought I would share a methodology for tackling some of the low hanging fruit, sort of the 80/20 rule of siebel performance tuning.  My experience is that with Siebel 7.8 and higher, on Oracle 10 and higher, most performance issues are Siebel configuration issues.  Of those, most of the issues fall into one of two categories:

• Missing Indexes
• Sort Specs

When customizing Siebel, you will frequently create new relationships between logical objects via a new foreign key.  There should always be a corresponding index for that foreign key on the M side of the 1:M or M:M  linked table.  Typically, it is just a single Id column but if for some reason, there are multiple columns (perhaps a join spec and a join constraint) make sure all of the columns from the child table are part of the index.

Be aware that all the perfectly planned indexes in the world will frequently be ignored if there is a sort spec on a query.  The sort essentially takes precedence and any index that optimizes the sort will usually be used to the exclusion of other indexes that perhaps optimize what a user is doing on that view.  I frequently see performance problems on visibility views (All/All Across) without any query refinement at all.  When this occurs, it is usually because of the All Mode Sort user property settings.  If you are seeing performance problems on an All view, try changing the settings of that property for the BC to fix the issue.

Here is a general methodology for identifying and fixing performance issues.

• For the OM component having the issue, change the Server Configuration event logging level to 4 for these events:
• SQL Parse and Execute
• Object Manager SQL Log
• Execute the operation that performs the slow query
• Open the corresponding OM log and find the SQL statement representing the slow query
• Finding the statement can be done in a couple of ways, but I use the following:
• Query for this string in the log '***** SQL Statement Execute Time'
• Look at the seconds for this line and the subsequent '***** SQL Statement Initial Fetch Time' to see a large value
• Copy the preceeding SQL statement into a SQL editor such as Toad or Benthic or whatever your fancy, swapping out the bind variables
• Run an explain plan on the statement
• Look for a line that says Table Access Full.  If you see such a line, look at the table being accessed this way and look back at the where clause to see how the SQL statement is joining to that table.  Then look in tools to see if there is an index for that table on the columns in the join to that table.
• If indexes are not an issue, but there is an Order By in the SQL statement, try commenting out the Order By and rerunning the explain plan to see how it changes.  If you see the explain plan change significantly (Cost goes down) than confirm that you really need the sort spec in the particular scenario you are in.

This is really just meant to be a way to find low hanging fruit performance issues.  It is important to configure with performance in mind (especially when using script or workflow algoritms).  Other sources of performance bottlenecks include (but are not limited to):

• Synchonous Interface round trips
• File System operations
• Network bandwidth  (especially if using a VPN)
• Memory or CPU bound servers

ADM - List Of Values

There are plenty of posts on support web discussing the issues with migrating LOVs, but for my own sanity, I thought I would summarize all of the relevant issues in one place.

First, we need to address the defects. These are documented on support in Document 731411.1 but I will summarize here:

(1) Go to BC 'List Of Values Child (UDA)'
(2) Add a new field 'Parent Type' based on Join 'Parent LOV' and Column 'TYPE' with Text Length '30'.
(3) Expand the pickmap for the 'Parent' field. Replace pickmap field 'Type' with 'Parent Type' and uncheck Constrain flg.
(4) Go to the integration object 'UDA List Of Values'
(5) Find the Integration component 'List Of Values Child (UDA)'
(6) Add a new field to the integration component with Name = 'Parent Type'. Data Type = 'DTYPE_TEXT', Length = '30', Type = 'Data', External Name = 'Parent Type', External Data Type = 'DTYPE_TEXT', External Length = '30', External Sequence = '38', XML Tag = 'ParentType'
(8) Compile changes.

The SR then goes into some more detail on why after all that it still does not quite work. To understand, we need to see that the LOV ADM Integration Object is Hierarchical in one dimension. That is, there is the LOV_TYPE record and then there are the value records. But LOVs are frequently Hierarchical in two dimensions, by virtue of the Parent value. What I mean is that a given LOV value record will always have one 'parent' record, it's type or technical parent, and may have a second parent record, it's functional parent, if you will.

ADM loads the first, technical parent in the standard way, through the relationships of the Integration Object. To load the functional parent though, ADM must run in two passes, the first to create all the parent and child records, and the second to relate them. This is necessary because we cannot guarantee the sequence with which LOV value records will be placed in the extract file. If these value records do not exist in the target already, and the parent is alphabetically (or however else we chose to sort the records) after the child, then it would error if ADM did not take this approach. So how ADM takes two passes is by virtue of the ADM Data Type explorer. You will notice that the explorer does not actually specify the foreign key fields of an object to link them to each other. Its only purpose is to run ADM in multiple passes. But the twist is that ADM will actually process dependent data types setup in the explorer in reverse order, importing the children before the parent. I personally find this confusing from a terminology perspective. Perhaps a better way of naming these Data Types is to use 'LOV-2ndPass' instead of 'LOV-HierParent' and 'LOV-1stPass' instead of 'LOV-HierChild'. This way when we set up the search specifications for an ADM Export session, it is clear what we are trying to do.

OK, one more wrinkle to throw into the mix (just when you thought it was all making sense). There is actually a third parent relationship involved. That is the records that populate the S_LOV_REL table. I will be honest; I do not use the LOV explorer view that often and I don't really know what the point of this table is. In theory it can make LOVs M:M but I just don't think this is practical. Nevertheless, there are some vanilla uses of LOVs where these records are in fact used that way. The one that comes to mind is in payments, where the PAYMENT_TYPE_CODE values are children of the PAYMENT_METHOD_CODE and there are S_LOV_REL records created to store the relationships. The same issue applies when migrating these relationships. The related value must exist prior to the relationship being built.

One final note. I think the whole not deleting LOVs is well intended but more likely to cause confusion than solve anything. Here is why. Users can and will just change the Name/Value of a value record to something else in which case any sense of history is lost anyway. There are no foreign key relationships to LOVs so business data using these values is unaffected regardless. But others may disagree so this step is completely optional. I remove the no delete properties from the 'List Of Values Child (UDA)' BC and Integration Component. (I also allow deletes from the GUI but that is a separate issue). So my migration methodology is to synchronize values between environments for an initial release. You would take a different approach on a point release where values are likely to have been added directly to production and therefore may not exist in your DEV and TEST environments.

Anyway, what are we trying to do. Quite simply, we are trying to create all the Value records in pass 1, then we need to relate them to each other in Pass 2. I have already discussed how to group LOVs together for a release. This is where I diverge from Siebel's example because I am trying to think of real life scenarios where I am deploying releases, not just one LOV_TYPE. When creating the ADM Project/Session, here are the session items I use:

Data Type	Child Delete	Deployment Filter
LOV-2ndPass	Y	[Release] = '1.1'
LOV-1stPass	N	[Release] = '1.1' AND [List Of Values Relationship.Name] IS NULL

What this means is that the first pass includes all LOV_TYPE records that have been marked for this release and all LOV value records related to them. The second part of the expression basically just insures that no relationship records are included in the first pass. When ADM attempts to set the parent value on a child, it may not be able to find it so it will log a warning and move on. In the second pass, ADM will load all the Relationship records and set the Parent values that it missed on the first pass. I have also set child delete to true on the second pass so that this job effectively synchronizes the value records for the type records marked.

Common (or not) eScript Syntax Errors

I would love to post a comprehensive list of gotchas, but then that would make them not gotchas if you know what I mean as I would know them all. So instead, I will mention what sidelined me for several hours last night and hope to spur some discussion about what other people have come across. If I think of others over time, I will try to update this post.

Space after the function name. I had copied and pasted some functions from somewhere else in my client's repository and the functions had no space between the name and the opening parenthesis of the passed variable declarations. I was not (and I guess still am not) aware of a limitation in this regard, but I saw all sorts of strange behavior afterward. Namely, the calls to these functions seemed to be ignored which took me a long time to realize. They seem to work fine in their original home elsewhere in the repository so this may be related to context, but suffice to say this is some thing to think about when troubleshooting.

SR Class Level Read Only

In case anyone has ever wondered about the intricacies of how a Service Request BC record becomes read only when the Status is set to Closed, I hope to add a bit of enlightenment. The premise, is that in Vanilla Siebel, if I set the Status of an SR to 'Closed', the entire record becomes read only. There are many tickets about this on My Oracle support so this may not be new territory. Instead of implementing this through standard configuration, which seems easily poossible in modern versions, Siebel has done this at the class level, in CSSBCServiceRequest. The interesting thing for me in researching this is what the actual triggering point is that makes the record read only. Basically, Siebel compares the value of the Status field for the particular record against the Display Value of the LOV where Type = 'SR_STATUS' and Name (Language Independent Code) = 'Closed'.

So if for instance the Display value has been changed in the LOV table to Done for the 'Closed' LOV record in type 'SR_STATUS', then the record must have a value of 'Done' in the status in order for this record to be read only. Maybe this result is not very interesting. So far you are right. What is interesting is that if you remove the picklist altogether from the Status field, or change it to a different picklist using a different LOV_TYPE, the same evaluation occurs. The long and short of it is that there is not a lot of opportunity to customize this functionality without doing some more complicated things behind the scenes as the Class does a lot of hard coded checks in order to implement this requirement

Why can't I see that View???

There are times when troubleshooting an issue, that you start searching Oracle support and you find a ticket that has your problem, but the solution does not apply, and then you keep looking and you find another one that looked in a different direction yet still, the solution did not apply. And you think, I wish there was a single place that listed all the reasons that this could happen. Siebel does do this sometimes a handful of their generic error messages, listing ten different reasons you could be getting that message. Well I am going to apply that format to some basic configuration items.

The first one is View Visibility. So without further ado, here is a list of reasons you may not see a particular view in the GUI (Please feel free to add additional reasons in comments):

1. The basics: View has been created in Tools, compiled into the SRF, and the GUI you are looking at matches the SRF you compiled the view into. I know this part should be obvious, but we are aiming at completeness
2. The View needs to be added to a screen. Make sure the View attribute spelling matches the spelling of the View object. Check the Display In Page unless you want this to be hidden, and probably the Display In Site Map. These default to True.
3. The View exists in the GUI meta data. Administration - Application -> Views. Again make sure the spelling matches the repository object
4. The View has been added to a responsibility that your user login has access to
5. The Responsibility Cache has been cleared. Doh!
6. Log Out and Log Back In (Views are not immediately visible in the session where it was added)

Ok those are the basics. Now for the advanced:

1. Navigate to the Application - Administration -> Responsibilities -> Tab Layout view. Check the responsibility which is primary for your user login in the top applet. Query for the Application you are logged into in the middle applet. Find the Screen you have placed the view under in the third applet, and in the fourth applet, insure the Hide flag is not checked. This is more applicable for trying to figure out why a vanilla view is not exposed
2. Navigate to the Application - Personalization -> Views view. Query for the view which is you are having issues with. Review the Condition Expression. This expression should either be null, or should evaluate to true for the logged in user. The date range should either be null or should include today's date in its range.
   
3. Last but not least, it may be a license key issue. Siebel implement license keys through their views. One way to test whether this is the issue*** is to copy the view, add the copied view to a responsibility, add the copied view to the View admin and to a responsibility and clear the cache, log out and back in. If you can see the view now, then you need to get the license key. Not all license keys indicate additional purchases. There are a couple of instances I have found where a view just dropped out of the standard set as a defect and the fix was to provide a license key to get it back (Remote System Preferences view is an example I ran into in 8.o)

Some Additional Pointers:

• Always copy and paste view names between objects or between Tools and the UI to avoid spelling errors, as they are one of the most common problems in this area.
• Avoid the use of apostrophes to indicate possesive as this will often cause issues down the road. (I know siebel has some vanilla instances where theu use it with ...Manager's... but trust me, avoid this).
• When copying views, insure the Thread and Visibility applets are actually present as View Web Template Items for that view. No error is thown to the UI if they do not match, but buried in the Siebel.log, you will find them. They manifest by not executing the correct search when navigating across views. For instance if you are on an All view and navigate to the correlated My view, but one that has an invalid applet, the view will appear to change in the UI, but the data does not, so the My view will show All view data.

*** This should be done just to test that the license key is the issue. A copied view really should not go into production as this is a slippery slope which is really not a good idea in the long term (and its against the license agreement too)

UPDATE: Dos, in comments, points out a better way to see if Licensing (or some other problem) is at issue. Just paste the following into your URL after the start.swe? replacing the view name in bold with the view you are having problems with:

SWECmd=GotoView&SWEView=Quote+List+View

You need to replace a space with a +. Since 'Quote List View' is part of the Orders module requiring a specific license key, you get the following message if you do not have the key:

View 'Quote List View' is not licensed for this site.(SBL-DAT-00327)