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".
Last updated on APRIL 30, 2018
Applies to:Siebel Tools - Version 7.7.2 SIA  to 188.8.131.52 SIA  [Release V7 to V8]
Siebel CRM - Version 7.7.2  and later
z*OBSOLETE: Microsoft Windows Server 2003
Product Release: V7 (Enterprise), V8 (Enterprise)
Version: 7.7.2  Fin Svcs to 184.108.40.206 SIA 
Database: Oracle 9i
Application Server OS: Microsoft Windows 2003 Server
Database Server OS: IBM AIX 5L 5.1
This document was previously published as Siebel SR 38-1781145701.
""Checked for relevance on 28-Feb-2014""
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. .
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms