ImposSiebel Toolbar, SQL Profiling

How many of us actually care about SQL performance? Mostly, only Solution Designers, but its important that we *all* consider how much impact our config has on the system.

Have you ever clicked on a button and waited a good 30 seconds before the system comes back? If yes, then this article is for you.

There could be a number of factors contributing to performance issues, but i'm going to focus on SQL performance.

Siebel produces a spool file for us to analyse, which is really useful, but only a speed reading SQL geek would be able to correlate the SQL generated with the Application usage and add up the SQL cost in their head on the fly.

For the rest of us, we have the ImposSiebel SQL profiling feature.

This feature brings SQL profiling to the masses. The idea is, that any junior should be able to use this to identify parts of the config that is taxing the system, and suggest ways of improving performance.

Before you start, make sure you are running a thick client connection, and have specified a spool file. This can be done, by adding " /s c:\spool.sql" to your Siebel Client shortcut.

Scenario
Identify a part of your application that takes a long time to respond.

It could be a button, or navigating to a certain view, or triggering a bunch of scripts, or generating some data.

Find it, but dont touch it.

Profile it!
I assume at this point, you already have the ImposSiebel toolbar running.

Now click the SQL profile icon, its that funny looking icon, which looks like two cyclinders behind a refresh icon. It should be next to the IS menu.


This starts the profiling feature. The first time you click this, it will prompt you for the location of your SQL spool file. The default is c:\spool.sql, just accept this if you've followed the instructions above or enter your own file location.


If you want to specify another file, this can be accessed by the following menu option.


Now engage your performance hogging function, and wait until it has finished.

Click that SQL profiling icon again to toggle it off. We are now ready to analyse the SQL.


You should be presented with the following dialog.


This shows you the total SQL cost of your function, and also displays the top 10 SQL ordered descending by Cost, along with the corresponding line numbers in the spool file.

When a button is taking 30 seconds to return control, its usually caused by 1 or 2 bad SQLs, with this tool, we can instantly identify the culprit, and focus our efforts in reducing this cost.

Click on the cost for the desired result, and the offending SQL will be shown to you.


Now, unless that speed reading geek has photographic memory as well, i think we have the competitive advantage here, and have just saved ourselves atleast 30 minutes, swimming around neck deep in SQL statements.

Although this tool was designed for analysing SQL, another un-intended benefit, is general troubleshooting.

This dialog can reveal some interest things, just browsing the application can trigger database updates, such as read audit insert/updates in Siebel 8, docking inserts and unexpected write records.

I hope this encourages people to think more about the SQL cost of their config, and proactively address any issues before it becomes a BAU problem.

Troubleshooting
ActiveX warning


This dialog needs to be accepted. The profiling feature needs ActiveX to function.

IE Settings

If you are still having problems. Go into your IE Trusted Zone security settings, and enable the above options.

Remember to add impossiblesiebel.com and your localhost to your trusted zone.


Reactions:

7 comments:

  1. Hi,

    Your toolbar is very interresting.



    I can use it only in a siebel application connected on Internet. If I disconnect my computer, the toolbar doesn't launch.

    Is it a normal behaviour?

    Thank you for your help.

    ReplyDelete
  2. The Managed installation will only work if you are connected to the web. If you want to use this on an isolated local machine, you can use Option 2, as documented here in the first post.

    http://www.impossiblesiebel.com/2009/09/impossiebel-toolbar-beta.html

    Please note only version 0.50 has been released as a bookmark. Currently only the Managed option has the SQL profiling feature.

    ReplyDelete
  3. Hi,

    This SQL profiling is a boon to non-dba users.
    I'm already using this toolbar and when I clicked 'Refresh toolbar' its not updating the SQL profiling icon.
    Even I enabled the required options in security level and restarted the system but its not..
    Pls let me know how long it'll take to update it as I need to verify performance of drilling down SRs...

    Thanks in advance...

    ReplyDelete
  4. the above comment was answered by the reader in the following post

    http://www.impossiblesiebel.com/2009/09/impossiebel-toolbar-version-052-release.html

    ReplyDelete
  5. The SQL profiling icon does not show up in my running toolbar. I have v0.52 beta installed.

    ReplyDelete
  6. The icon only appears when you are running a thick client.

    ReplyDelete
  7. Interesting, I am doing the same as your programm with UltraEdit or NotePad++ with (FIND All & List in a separate windows)

    Good job !

    ReplyDelete