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_IDFROM
SIEBEL.S_ORG_EXT T1WHERE(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.
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 !!!
.
hey this is awesome man, good job
ReplyDeleteThanks GG, it's awesome.
ReplyDeleteIt is really good to know that it is possible to provide hints from configuration.
ReplyDeleteI have seen siebel use ALL_ROWS hints for objects which are internally used by siebel.
Is it possible to apply hint to use a particular index instead of just providing ALL_ROWS because it is possible that if you use that BC in some other place and with some other configuration it might start creating problems with this hint.
Neel,
ReplyDeleteYou can try this, lets say you want to use a index hint (S_ORG_EXT_X1) in above query, then create the user property as :
Property Name : OracleCBOHint
Property Value : index(S_ORG_EXT.S_ORG_EXT_X1)
But we need to keep in mind that this solution would work as far as query is having only a single instance of table (S_ORG_EXT, in our example). And it would become tricky if your query have multiple instances of S_ORG_EXT table in FROM clause. Though Siebel generate the synonym for each table and you can also use "index(T1.S_ORG_EXT_X1)", but I won't recommend to do that because it is not the best way I see to achieve the solution and any configuration in the business component might messed up the performance. So we actually need to be very careful before using it for Index Hint.
This is one of the coolest tip I have ever seen in Siebel. Thanks GG
ReplyDeletenice tip
ReplyDeletethere is no SR mentioned for this ...is this recommended solution from Supportweb & upgradeable?
ReplyDeleteHi Anonymous, this is not upgradeable as this is not recommeded by supportweb. You need to be very careful before using this as forcing a query to use some index today for Oracle CBO might impact the performance of the same query in future.
ReplyDeletedoes this index thing work. I tried in our siebel 7.7 callcenter application...I could see as hint in the spool but at the d/b side, Oracle does not use that hint. It uses its own index.
ReplyDeleteHi Anonymous, the example I mentioned in this post is the live example where we actually achieved the performance gain. Logically speacking as far as you see the hint mentioned in the spool correctly it will definitely used on the database. How you identified in your case that query used some different index?
ReplyDelete@Anonymous
ReplyDelete"does this index thing work. I tried in our siebel 7.7 callcenter application...I could see as hint in the spool but at the d/b side, Oracle does not use that hint. It uses its own index."
Were you looking at the SQL from Oracle Enterprise Manager by any chance? This will not show the true explain plan that is being executed it will generate an explan plan according to Oracle parameters not those set in the Siebel session.
@Josh
ReplyDeleteI have used dynamic view V$SQL and V$sqlplan to see the plan...is it not correct?
Thx
Hi,
ReplyDeleteI guess I got it working. I could say it works...there was a typo in my syntax
This was a life saver! User the Property Name and Property Value as per above on a problem which was an exact match, response tome down from 1600 seconds to less than 2 seconds.
ReplyDeleteThank you.
Does the OracleCBOHint User Property works for db configured with SQL Server as well ?
ReplyDeleteIts a bad practice. OracleCBOHint is not supported, for many reasons. When you put this in at the BC level it will change every SQL fired for that BC. The example shows an index hint so this assumes that the index hint will work for every query fired, probably not what you want. Also, changing OracleCBOhint to all_rows basically switches to Oracle cost based optimization ignoring long history of Siebel engineering tuning the out of the box application. Also, a different combination of stats and parameters may completely hose your BC performance so backing out your changes will not be easy because it requires a restart of the Siebel tier. What you are showing us is a nice trick but it is insane from a standpoint of having to support in production.
ReplyDelete