Query performance issue : Dedicated Client Vs Thin Client

Very strange behaviour I observed today while working on a performance issue where whenever user do a query in Purchase Order Pick applet, query was taking more than 1 minute to execute in thin client but the same query behaves perfectly fine in dedicated client.

So while working on any performance issue, you always want to see the performance on dedicated client as well, so I did the same.

1. Took the same SRF running in thin client, and connect it to the Dedicated client.
2. Followed the same steps: open the pick applet and queried the Purchase Order#

To my surprise, on dedicated client the PO# query took only 6 seconds which is quite acceptable performance. So, this is the issue I faced first time when the query is running slow in thin client but not in dedicated. Now, lets try to find out what exactly is going on behind the scene.

I referred the spool file and took out the culprit query. Execution time mentioned in the query was same around 6 seconds. I ran the "Alter Sessions" commands as mentioned in my earlier post and ran the query. It again took only 6 seconds.

***** SQL Statement Execute Time: 6.014 seconds *****

hmmm..... now it looks like everything seems working fine from configuration perspective but why is there a difference in the query execution when run in Dedicated client Vs Thin client???

I was looking into the issue more and found that when I did a query in PO# field with value "1234" and it returned multiple records with PO# like: 1234, 12345, 1234a etc. So, it is quite clear that "AutomaticTrailingWildCards" parameter in the CFG was not set to False. But, anyways the query should perform similar in both the dedicated and thin client, no matter this parameter is set to False or not. Though, this thought is quite logical but even then I wanted to give a try by disabling it. So, I thought of disabling it only on the "PO#" field on the pick applet, so that whenever user do a query on PO# field, system should not put the "like" keyword in the SQL generated at the backend.

I put the following User Property on the business component:

Name : Disable Automatic Trailing Wildcard Field List
Value : Purchase Order Number

I compiled the SRF, tested it in dedicated client, it worked fine. Now it is the time to test it in thin client as well. At this moment I was not sure how it would behave. I put the same SRF on thin client and performed the same steps for querying the Purchase Order#, I got the result in around 6 seconds. A big smile and relief too, but again I don't have the correct explanation yet for this problem why is it happening, but this user property came as the life saver :)

If you ever faced this kind of performance issue and know the exact reason please comment.

Building Link as a Join??

The very first expression that any Siebelized person would have after reading the title of this post would be, "hey, what is that?? Join and Link are two different objects in Siebel. Join used for building 1-1 and M-1 relationship and link is being used for building 1-M and M-M relationship. So how come you can build a link via join??".......

I knowwww and I am there with you what you just thought and completely agree to this fact too :) but keep in mind you get functionalities working in Siebel by SQLs running in the background on the database and as far as you know how to play with the configuration to achieve desired results (provided it will not result in performance issue) you can do wonders.

Alrightyyyy then, lets see what I got for you today to let you know an interesting fact in Siebel Configuration that can be used to achieve some different kind of requirement. The requirement we got to implement was:

"Create the Account Screen, which has got various sub-tabs like Opportunities, Quote, Assets, Service Requests, Invoices. So this is a kind of Parent-child relationship which is available in Siebel vanilla, nothing new.



But we were asked to provide an ability to query on the Accounts header form applet with a) Opportunity Id b) Quote Id c) Asset Number d) Service Request# e) Invoice#. That means if I have an Asset Number with me and query it on Account form applet in some field, then system should return the corresponding Account record to which this Asset Number is associated with. Similarly for Invoice# and so on. You might be thinking why this ever be a requirement and for what purpose, can't we navigate to Assets screen for query the Asset Number and drilldown from there to Account Screen and similar for other entities? You are right, you can do that as well but in our requirement user want to have the ability to query in a single place. "

I think the requirement is clear now. The very first solution come to the mind can be achieved by following the below steps:

a. Create MVLs (which in-turn use some "Link") on Account business component.
b. "Use Primary Join" property of these MVLs should be "False".
c. Create MVFs for based on each MVL and expose on Account form applet.
d. Let the user query on any of the MVF and system will return the corresponding Account record.

This is very straightforward solution, but HUGE performance impact. When the view will get loaded, system will run (1 + (Number of MVFs)) queries for each account you will traverse on the UI. Lets try another good option here and create "Join" on the business component instead of "Link".

Here is below what I tried:

a) On Account business component, create following joins (as per the need)

i. Field Name: S_INVOICE
Source Field : ROW Id (Row_id of S_ORG_EXT)
Destination Column : ACCNT_ID (Foreign Key to S_ORG_EXT on S_INVOICE)
Alias : INVOICE

ii. Table Name: S_ASSET
Source Field : ROW Id (Row_Id of S_ORG_EXT)
Destination Column : OWNER_ACCNT_ID (Foreign Key to S_ORG_EXT on S_ASSET)
Alias : ASSET

iii. Table Name: S_OPTY
Source Field : ROW Id (Row_Id of S_ORG_EXT)
Destination Column : PR_DEPT_OU_ID (Foreign Key to S_ORG_EXT on S_OPTY)
Alias : OPTY

b) Now create corresponding join fields:

i. Field Name : Invoice Number
Join : INVOICE
Column : INVC_NUM

ii. Field Name : Asset Number
Join : ASSET
Column : ASSET_NUM

iii. Field Name : Opty Id
Join : OPTY
Column : OPTY_ID

c) Expose the fields on the UI and query for any Invoice#, Asset#, OptyId on the Account form applet and system will bring the Account record for you.

Let's see how it is working on the UI. I queried for Invoice# = "410194-13385102"




and now let's navigate to "All Invoices" view to see if system has returned the right Account or not. And yessss, this is the right account, I can see the Invoice#.




You can try it at your end and the best part is, if you see the SQL spool generated by the system, you will find that only single query is returning the data, not likely the case with MVFs, and there is no performance issue at all.

How to apply Oracle hint in query via Siebel Configuration?

Our team was facing one performance issue in our application in which navigation to a view was taking hell lot of time. As every Siebel developer does, we also spooled the query which is running in the background and found the culprit query for the issue. SQL query seems to be very simple:

SELECT T1.CONFLICT_ID, T1.LAST_UPD, T1.CREATED, T1.LAST_UPD_BY,
T1.CREATED_BY, T1.MODIFICATION_NUM, T1.ROW_ID,
T1.ACCNT_TYPE_CD, T1.NAME, T1.OU_NUM, T1.DOM_ULT_DUNS_NUM, T1.BU_ID

FROM
SIEBEL.S_ORG_EXT T1

WHERE

(T1.ACCNT_TYPE_CD = 'Establishment')

But why this simple query is taking time?? This seems to be a very simple query which is resulting in performance issue. Ok, no probs, lets try running this query at the database and see how it behaves, so I followed the instructions I mentioned in my earlier post. i.e.

http://siebelmantra.blogspot.com/2009/01/alter-session-parameter-for-siebel.html

After setting the session parameters, I ran the query and it took 105 seconds. hmmm..... then I checked the execution plan and found that it was doing the Full Table scan. If you look at the query, you will easily point out that create a index on ACCNT_TYPE_CD and hopefully you are done, but this is not the case. We already have a index on this field but not sure why it is not being used in the query, then you might say that get the statistics regenerated for it your issue get resolved, but that we have already tried, still not able to figure out what the cause is. Finally, our DBA helped us out here. DBAs are just magician of databases, I don't know what kind of magic stick they just use and sql query itself come to them and tell them what needs to be done here. :)..... anyways, joke apart, our DBA analysed the query and suggested that we should use "ALL_ROWS" in the query, the resultset is coming in just 2 seconds. I was surprised after hearing this because I never came across a scenario where oracle hint ALL_ROWS actually solves the performance issue, and also Siebel itself uses the Optimizer Mode = FIRST_ROWS, for session it establish with the oracle database, before running any sql query. But to my surprise, after putting the hint into the query, it ran fine with just 2 seconds on the database.

Anyways, this is also the first time learning for me, but the challenge is how to put this Oracle Hint "ALL_ROWS" in the query from Siebel configuration? And after lot much of search I found something that is worth sharing here. But before applying this, you need to very careful and get the approval from DBAs or your solution architects, so that it should not impact anywhere else in the application. Now, here is below how you can force a Siebel query to use the Oracle Hint. Identify the business component and add the following User Property there :

Property Name : OracleCBOHint

Property Value : ALL_ROWS

Compile the SRF and check the spool. You will see the difference in the query this time.

Check it out !!!

.

Alter Session Parameter for Siebel Query

I got surprised by the result of the execution time of one of the query, on which I was working on, as we were facing a performance issue in Production env while navigating one view.

As per the general process, I checked the Spool and Siebel Object Manager log to check for the query, which one is the culprit. I found the query which was taking around 70 seconds (as per the spool file). So that means I have won half of the battle, rest is to analyse the query and I will be thru. But now when I checked that query how it is behaving at the database, I ran the query on our Siebel Database (Oracle 9i) and got surprised when query returned the result in 200 seconds. This is something strange, how could this happened that if database is returning the result in 200 seconds then getting this result on UI is taking just 70 seconds.

In later investigation, I found that this is due to fact that whenever Siebel run any query on Oracle (Cost Based Optimizer), it sets few session variables for better executions of SQLs. So while verifying the SQL performance of a Siebel Client that is running on Oracle Cost-based optimizer mode, it is important to run the following alter session statements on the database :

For Oracle 9i

alter session set optimizer_mode = first_rows_10

alter session set hash_join_enabled = false

alter session set "_optimizer_sortmerge_join_enabled" = false

alter session set "_optimizer_join_sel_sanity_check" = true

For Oracle 10g

alter session set optimizer_mode = first_rows_10
alter session set "_optimizer_sortmerge_join_enabled" = false
alter session set "_optimizer_join_sel_sanity_check" = true

After setting these parameters, query started behaving the same way as it was behaving on the UI. So, now the only thing we need to check is what is the execution plan is being generated by Oracle, and I found that there was a "Full Scan" for one of the where clause, despite of the fact an index already exists on that column. The Explain Plan query being used was :

Explain Plan for

"Query"

Select * from Plan_table

Now, our investigation was narrowed down to the point that since index was not being used by the query, that's why it is resulting in performance issue. We asked our DBA to regenerate the statistics for that particular index and finally performance issue was resolved.

Automatic Trailing WildCards : A CFG Parameter

If you do a Query in any View of Siebel application and you observe some performance issue in getting the query result set, then "AutomaticTrailingWildCards" parameter is one of the culprit, which is available in Siebel.CFG under [SWE] section.

What it does

Lets say you want to query on "Name" field of Opportunity List Applet with value "siebelmantra", then system will fetch Opportunities with name like siebelmantra, siebelmantra1, siebelmantra Oppty, etc etc. That means it automatically suffix a "*" (asterick wild card character) after the string being queried. To confirm this, press "Alt + r" (Refine Query), you will see "siebelmantra*" was being queried in Name field, though you never mentioned "*" while querying. This is the effect due to the TRUE value of "AutomaticTrailingWildCards" parameter.

Recommendation

Open Siebel.cfg file and search for "AutomaticTrailingWildCards" parameter under [SWE] section and set its value to "FALSE". Restart the Siebel Servers and observe the difference by again querying on the UI.