A Siebel Query with a Subquery is very Slow in an Oracle CBO Siebel Environment. The Slow Performance was improved by using the Hidden init.ora parameter “_always_semi_join”
Performance problems are observed within the system. A certain query is taking, on average, 7 minutes to be returned. The DBA has run that query exactly as it came out of Siebel using the bind variables within a script. That script ran in seconds. It appears as though, when running from the system, there is a very large timeframe for gaps with no activity and waited idle time. The amount of time spent at the database level is minimal.
The poor SQL performance happened with a configuration that executed a nested subquery. Hidden init.ora parameter “_always_semi_join” was not set to off.
(1) When testing and verifying the SQL performance of a Siebel client in that is running in an Oracle Cost Based Optimizer (CBO) environment, it is important to first run the following alter session statements to have the same session environment.
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;
These are the same session parameters that the Siebel Oracle CBO client will set.
(2) In this case the Siebel client had very poor performance when executing a query with a subquery.
As per Non-Primary Manager visibility mode – poor performance hidden init.ora parameter was set.
alter session set “_always_semi_join” = off;
I will be happy to respond to your queries , Please use below comment box and i will get back to you. Thanks.