RE: changing session parameters after logon trigger
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: "'Paul Drake'" <bdbafh@xxxxxxxxx>
- Date: Sat, 22 Oct 2005 13:29:30 +0200
Hi Paul,
Thanks for your prompt reply.
I ma aware that setting "optimizer_features_enable=8.1.7" is NOT a
solution, it is just
A quick and (very) dirty fix of the problem(the project leader is getting
pretty nervous since time passes by, and there is no REAL solution of the
problem). The root of problem is that SBL is issuing "alter session set
optimizer_mode=first_rows_10" right after login. Instance-wide parameter is
"optimizer_mode = CHOOSE". With the latter setting there is NO performance
problem. That is why I asked the question you are seeing in the "Subject:"
of the mail.
Anyway, I hope that till July 2007 Oracle will change the "CBO readiness" of
SBL (they have paid enough to get the chance to do so, right ;) )
BTW. The second "best" fix was to set "_new_initial_join_order"=FALSE.
The "cpu parse time" went from ~ 4 seconds to 0.95 seconds.
Tracing the sessions (event 10053) with different settings
("_new_initial_join_order"=TRUE|FALSE) I have noticed that when
"_new_initial_join_order"=FALSE then oracle is considering only 30 join
orders (instead of 40).
In both cases "optim_max_permutations" was set to
100 (recommendation from SBL).
Regards. MILEN
-----Original Message-----
From: Paul Drake [mailto:bdbafh@xxxxxxxxx]
Sent: Thursday, October 20, 2005 9:56 PM
To: makulev@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: changing session parameters after logon trigger
On 10/20/05, Milen Kulev <makulev@xxxxxxx> wrote:
> Many thanks for answer Jared.
> The problem is that Siebel suggestions are sometimes pretty irrational
> ***and*** their suggstions are not giving the expected (performance)
> results. If some parameter changes proposed from Siebel don't work,
> they (SBL) say "Ask your DBA for assistance". Anyway, the solution of
> the problem was to set "optimizer_features_enable=8.1.7".
Milen,
Tourniquet applied.
Good luck taking that one off.
(what I am implying, is that one does not remove a tourniquet. the
doctor(s) in the hospital remove it, after severing the affected limb).
The solution to the problem is likely not setting o_f_e='8.1.7', instance-wide.
That won't likely survive an update to
10g R1. The good news for you is that 9.2 is on support until 01-JUL-2007.
Pd
> After setting this parameter we got "parse time cpu=0.01 sec". The
> funny thing is that SBL is stating that SBL (version 7.7) ist "CBO
> ready", but they are forcing the CBO in any possible way to behaive
> just like RBO.
>
> Once again thank you for your time and help.
>
> Regards. Milen
>
>
> -----Original Message-----
> From: Jared Still [mailto:jkstill@xxxxxxxxx]
> Sent: Tuesday, October 18, 2005 6:06 PM
> To: makulev@xxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: changing session parameters after logon trigger
>
>
> On 10/13/05, Milen Kulev <makulev@xxxxxxx> wrote:
> ...
> > 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;
> >
> ...
> >
> > When I set "_optimizer_sortmerge_join_enabled" to TRUE AND
> > optimizer_dynamic_sampling to 0, then I am getting 0.1 seconds
> > elapsed time!
> >
>
> Regardless of your testing results or advice you get from this list,
> you should probably discuss this with Sieble, er, Oracle?, support.
>
> As these are underscore parameters, Oracle doesn't directly support
> them, Siebel *should* be able to offer some guidance.
>
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
Other related posts:
