Siebel uses statements with bind variables throughout the application. Bind variables are good for performance, as Oracle does not need to hard-parse the query every time it is executed. However, in some cases, bind variables lead to major performance problems. One of the more significant problems we faced was the issue of bind variables in SQL LIKE predicates. By default Siebel uses bind variables in passing query criteria values entered by users. In many cases, where users used trailing wildcards (asterisk), Oracle 10g Optimizer’s calculated query paths that were inefficient, resulting in queries that took from several minutes to an hour. There are four query circumstances in which this happens:
- A user uses a partial query string and a trailing wildcard. Example: "ABC*".
- A user opens a pick applet and queries by entering any value in the Starting With box of the pick list, unless the query string is preceded by = sign. Example: "ABC". (=ABC will prevent wildcard).
- The implementation is using "AutomaticTrailingWildcards = TRUE" in the SWE section of the application's CFG file (such as uagent.cfg) or is not present. (It is recommended that implementations have the AutomaticTrailingWildcards parameter set to FALSE in order to prevent unnecessary trailing wildcards that impact performance).
- Automatic Trailing Wildcard is not disabled in the Siebel Search Center. This parameter is also typically disabled. This is done through a configuration change in OOTB business service Search Execution Service. A new Business Service User Prop would be created with the following parameters: Name = AppendWildcard; Value = FALSE to prevent unnecessary trailing wildcards that impact performance. It is assumed that this is done.
In most cases, (1) and (2) above are unavoidable, based on how users use Siebel. In both cases the solution is to use a little known Field User Property(Use Literals For Like).