My Oracle Support Banner

Siebel: Improving Performance of Queries with ORDER BY on Oracle (Doc ID 475413.1)

Last updated on MARCH 26, 2018

Applies to:

Siebel System Software - Version 8.0 [20405] and later
Information in this document applies to any platform.

Purpose

When running Siebel Business Applications with Oracle Cost-Based Optimizer (CBO), there are some situations that can cause a specific SQL query to generate a poor Execution Plan.

This is commonly observed after upgrading the Database Server version to Oracle10g, and usually happens with queries that contain an ORDER BY clause, such as queries performed on the Contact Business Component (BC), whose Sort Specification is defined as "Last Name, First Name" in the standard Siebel Repository File (SRF).

When running queries on the Contact BC, it is common to see Execution Plans starting with an INDEX FULL SCAN on S_CONTACT_M12, since the first two columns it indexes are LAST_NAME and FST_NAME, and the ORDER BY clause of the query is formed by these same two columns.

Scope

This document is informational and intended for any user.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 1. Gathering more accurate Database Statistics
 2. LIKE conditions with Bind Variables
 3. Oracle initialization parameter _SORT_ELIMINATION_COST_RATIO
 4. Oracle initialization parameter OPTIMIZER_INDEX_COST_ADJ
 5. Modifying the Sort Specification or a Custom Index
 6. Using Stored Outlines for Plan Stability
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.