Archive for October, 2007

More on Script Assist

A great update on the Script Assist functionality I mentioned a while back. In a comment on my original post, Jake pointed out a “*huge* performance impact when the method listing and autocomplete options were enabled on scripted BCs in v8”.

Now, an anonymous commenter posts that this problem has been recently fixed and “should be available as a QF and in the FixPacks”. The comment also highlights some of the additional scripting improvements in 8.0 that I hadn’t come across – including “Fix & Go” debugging, Siebel’s version of Visual Studio’s “Edit & Continue” – and signposts an “upcoming 8.1 feature, Script Performance Profiler”.

I’m very (inordinately?) pleased with the additional information. Partly because the improvements are all good, legitimate changes that drag Siebel Tools towards with other fully featured development environments, but moreso because it’s great to see an employee of Oracle monitoring and feeding back directly into the development community. This is exactly the sort of thing I was hoping to see when I started the blog… may a thousand geeky conversations bloom 🙂

October 29, 2007 at 9:30 am 1 comment

Why there may be a place for Direct SQL

Over at Service Oriented Shenanigans Graham’s written a good, provocative piece titled Why Direct SQL is a very stupid thing to do. I’m happy to be baited… 😉 I started to respond there, but it turned into something worth a full post. Hopefully trackback pulls the whole conversation together like it’s supposed to…

Graham argues that because Siebel implements referential integrity through the repository rather than through database rules, it’s idiotic bypass all these rules and run SQL directly against the database. I’d make a few points:

  • If you’re using Siebel Remote, then direct SQL could be additionally stupid. Any changes made directly to the database are not be propagated to the transaction log, so won’t be routed to the local databases, so your server will be out of sync with your remote clients… Nasty!
  • If, however, you’re NOT using Siebel Remote, or you’re re-extracting all your local databases anyway, then I’d argue that there is a place for direct SQL. A limited place, sure, requiring utmost care, diligence etc etc, but at the end of the day it’s just another tool to only be used when appropriate. Siebel Expert Services will happily run direct SQL when the alternatives are ridiculously cumbersome. The alternatives (e.g. EIM, EAI) are also quite capable of significantly damaging your database and also require an appropriate level diligence: developers shouldn’t lapse into thinking that just because things are being done with the ‘proper’ methods they can’t do harm (EIM Delete in the wrong mode, anyone?).
  • If you use direct SQL to insert a record, then you won’t get a Siebel Row Id. The procedures to generate a Row Id are in the database and could be called directly, but this really is idiotic. (I actually worked on an implementation where Siebel Expert Services had done this – hmmm…)

So don’t use direct SQL to insert records, and don’t break your referential integrity, but do remember that SQL is just another tool, with pros and cons to be weighed against the alternatives. And don’t come crying to me if you break your database and your architect justifiably calls you an idiot.

October 17, 2007 at 11:26 am 2 comments

Debugging SQL with Oracle Cost Based Optimizer

I recently had a performance issue in Siebel 7.8 that on brief inspection looked like a SQL issue: the application was taking forever to build a list applet, re-query was just as slow, there weren’t any obvious calculated fields, no dodgy MVGs etc etc… it all pointed at the database.

So I did the obvious thing and spooled the SQL. Sure enough, the applet query was taking nearly 30 seconds to complete. Next step, fire up Toad to get an explain plan and start picking apart the SQL. Except when I pasted my query into Toad, it completed in half-a-second. The explain plan showed perfect use of the expected unique index scans, with no full table scans or other anomalies. Why the discrepancy?

After a bit of digging around, the answer came from our often-invaluable TAM: SupportWeb TechNote 582. The technote has all the background to the Cost Based Optimizer, and the key is the explanation of Siebel’s Oracle session variables. In summary, to make your SQL client behave like a Siebel session, you need to set the following session variables:

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
/

In Toad, you add these statements and choose to ‘Execute as script’. The really significant variable here is that first one: optimizer_mode = FIRST_ROWS_10. This tells Oracle to optimize for the first page, rather than the whole query, which can dramatically change the way indexes are evaluated.

In my case, the session variables allowed me to replicate the slow performance of the query in Toad, the explain plan of which showed Oracle using the wrong index, which identified the incorrect evaluation of costs, which (eventually) pointed to a root cause of stats being generated on empty tables. Obviously!

October 16, 2007 at 9:38 am 4 comments

Discussion Forums for Siebel

A new Siebel web resource… The Oracle Technical Network have recently added Community Discussion Forums for Siebel (you’ll need to sign up for free membership of TechNet).

Early days on the discussion pages, but in time I’d expect this to replace ITToolbox as the default destination for Siebel queries. I’ll be keeping an eye on the Siebel Technology thread and hoping to learn a few things.

(via both Andy and Graham)

October 4, 2007 at 9:41 am 3 comments


Feeds