changing session parameters after logon trigger
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 13 Oct 2005 17:45:50 +0200 (MEST)
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 +++
--
//www.freelists.org/webpage/oracle-l
Other related posts:
