Re: SIEBEL performance help wanted

Siebel only certifies rule based optimizer (and tells you NOT to use CBO) and does NOT want any statistics.
Post the query that you are talking about and the plan. You can get the query by adding a /s path_and_file_name to the command line when you start up Siebel (client). It will spit out the sql into a file with timings and the bind variables. I then usually take that and generate the execution plan.

One especially bad thing is things like case insensitive searches. Siebel does it by
Let's say I want to find Oregon in myfield

(myfield like 'or%' or myfield like 'Or%' or myfield like 'oR%' or myfield like 'OR%') and upper(myfield)=upper('OREGON')

However, it turns out to usually NOT use the index (assuming an index on myfield as the leading part.) I got around this by requiring a few fields that had the data already UPPER and the GUI was uppering them anyway by changing the default case sensititivity field property to false. (default is true - it defaults to the global setting in the cfg file for the application) Then it removed the upper and was much faster. Also eliminate outer joins where you can (not always possible). For example, if both join fields cannot be null then remove the outer joun on that field in the object.

Jim

"Ron Reidy" <rereidy_at_indra.com> wrote in message news:3C436EF4.4E3FB6A2_at_indra.com...
> Andy wrote:
> >
> > I am assisting with a SIEBEL implementation, and the performance is
> > terrible. I have narrowed it down to INDEX usage (or lack thereof) but
> > can't get the queries to use the indexes. This is from the
> > v$librarycache view:
> >
> > NAMESPACE Hit Ratio Pin Hit Ratio Reloads
> > --------------- ---------- ------------- ----------
> > SQL AREA 98 99 198
> > TABLE/PROCEDURE 91 93 28
> > BODY 93 92 0
> > TRIGGER 100 100 0
> > INDEX 19 3 1588
> > CLUSTER 99 99 0
> > OBJECT 100 100 0
> > PIPE 100 100 0
> >
> > Their queries are taking ages to run, and I did an explain plan on one
> > of the problem ones and discovered it's doing 2 full scans of a 7M row
> > table. The query contains a '<>' in the where clause, which I believe
> > won't use an index. The column isn't the first one in the index
> > either.
> >
> > The SIEBEL consultant (hah!) isn't any good, all he knows about are a
> > couple of initialisation parameters.
> >
> > For further info:
> >
> > Oracle V8163 on Solaris
> > shared_pool_size = 600M
> > 8k block size
> > sort area size=500m
> > sort area retained size=250M
> >
> > Any ideas? Please? Help me...
> What is your optimizer mode? When was the last time statistics were
> gathered? How many db_block_buffers? Have you looked into stored
> outlines? etc., etc.
> --
> Ron Reidy
> Oracle DBA
Received on Mon Jan 14 2002 - 20:45:56 CST