changing session parameters after logon trigger

From: Milen Kulev <makulev_at_gmx.net>
Date: Thu, 13 Oct 2005 17:45:50 +0200 (MEST)
Message-ID: <29441.1129218350@www53.gmx.net>

Hi listers,
I have a problem with a couple SQL statements (from the simple ones- joins between 20 tables ;) ) which are coming from SIEBEL.

The problem is that SBL application server is executing the following statements right after login :

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;

This statements are issued AFTER the databse logon triger fires (I have traced the sessions with event=10046).

Is there any possibility to change this parameters again (after SBL changed them)?

OS= HPUX 11.11
Oracle 9.2.0.4

The problem is that I am getting pretty high "parse time cpu" times:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 4.13 4.04 0 0 0   0
Execute 1 0.00 0.00 0 0 0   0
Fetch 2 0.01 0.00 0 393 0   9
------- ------ -------- ---------- ---------- ---------- ----------

---

total 4 4.14 4.04 0 393 0   9

When I set "_optimizer_sortmerge_join_enabled" to TRUE AND optimizer_dynamic_sampling to 0, then I am getting 0.1 seconds elapsed time!

Do this situation something to do with optimizer_mode = first_rows_10 and 9.2.0.4 , as the one-site SBL consultant suggested?

The funny thing is that when I set optimizer_features_enable=8.1.7, the "parse time cpu" problem disappears, and all the statement are pretty performant.

Does anyone have similar (bad)experience with SBL ?

Regards. Milen

P.S. More SBL fun:
optimizer_max_permutations= 100
optimizer_index_cost_adj= 1

--

10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail +++ GMX - die erste Adresse für Mail, Message, More +++
--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 13 2005 - 10:51:26 CDT