My Oracle Support Banner

Siebel Administration Generating Execution Plans for Oracle CBO (Doc ID 475230.1)

Last updated on NOVEMBER 22, 2019

Applies to:

Siebel System Software - Version 7.7.2 [18325] to 16.0 [IP2016] [Release V7 to V16]
Information in this document applies to any platform.

Purpose

Starting with Siebel version 7.7, if using Oracle9i or above, the Database Server must run with Cost-Based Optimizer (CBO).

The way an Application Object Manager (AOM) works with Oracle CBO is a bit different from the Rule-Based Optimizer (RBO) approach, so there are some steps to follow in order to get an accurate Execution Plan.

Scope

This document is informational and intended for any user.

When using Oracle CBO, a Siebel AOM sets some database parameters at session level which influence the behavior of the Optimizer when parsing a SQL statement, and makes use of Bind Variables in order to take advantage of SQL sharing.

This is considered by the CBO when determining the best Execution Plan for each statement, and must be taken into consideration when tuning the performance of a SQL statement, so that you can see the exact access path used by Oracle within a Siebel session.

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. Identifying the Query
 2. Identifying Bind Variables type
 3. Creating the PLAN_TABLE table
 4. Editing the script with the information gathered
 Case A - use this script when you want to retrieve the execution plan only - in this case the Oracle Optimizer will output the execution plan without running  the sql itself
 Case B - use this script when there is a need of collecting time statisitcs for analysis of the performance issue along with the execution plan
 5. Formatting the SQL Trace file generated for case 4.B
References

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