Today we will look at a way to see if you are generating slow sqls via your Siebel code. So your customers are complaining that their page load times are too slow and its up to you to figure out why. One reason for the performance issue can be because of slow sqls being generated.

SQLs show up in Siebel logs. There are two ways you can generate the logs.

1, The logs can be generated via your Siebel Dedicated (Thick client) instance.

>> To get this to work right click on your shortcut to your dedicated client for your target append the following /s c:\Spool.txt. This just means /s (spool) the sqls to location c:\Spool.txt. You might end up with a target that looks like this C:\Siebel\8.1\Client\BIN\siebel.exe /c C:\Siebel\8.1\Client\bin\enu\scomm.cfg /s c:\Spool.txt.

2, The logs can be generated via the Siebel Server (thin).

>> On your thin client go to SiteMap > Administration – Server Configuration > Components – Select your component from the second applet and in the third applet (Events) search for “*SQL*” – change the corresponding events to 4. Your logs will now be generated to your siebsrvr/log (for windows) or to your siebsrvr/enterprises/[Enterprise Name]/[Server Name]/log location for *nix.

Ok so now you have your log files and want to analyze it. The first thing you will notice is that these log files get really really large and unless you plan on spending the whole day looking through them its not easy to figure out the slow sqls.

Voila theres now a tool: I have run into this problem before and I eventually decided to write an app to do the heavy lifting for me. Its written in C# and has about 3k lines of code and best of all its free. Just start the app open the file you want to analyze and there you go it will give you a list of all the SQL execution times.

You can now sort the time lists and see your slowest sqls.

Some good features of this tool.

1, You can right click on a line of sql and the app will show you your sql with all the bind variables filled in. Just copy the sql and you can paste it into your favourite SQL tool — heads up I will be giving out a free SQL Query tool shortly.

2, You can analyze an entire folder of log files.

So how do you get the tool – go to look for the tool Siebel SQL Performance and select the “Download Application Freeware” link.

Thats it for now

>> George Verghis

 

Reference http://it.toolbox.com/blogs/scbroker/siebel-sql-performance-44159