Query Performance (Doc ID 1472592.1)

Last updated on JULY 20, 2016

Applies to:

Siebel CRM - Version 8.0 SIA [20405] and later
Information in this document applies to any platform.
***Checked for relevance on 5-SEP-2012***
***Checked for Relevance on 03-AUG-2014 ***

Goal

Customer was upgrading from siebel 7.7.2.8 to siebel 8.1.1.7 and the new database is oracle 11gR2.
During the performance testing, some predefined queries have much longer run time in siebel 8. This query went from 4 seconds in Siebel 7 to 12 seconds in Siebel 8.

The siebel 8 trace file is showing the query is doing several binds and having a lot more logical reads than the same query in Siebel 7.

Customer wanted to know the following:

  1. Why does siebel 8 perform the bind several times? Based on the trace file, looks like at least 6 seconds were lost in the bind. Siebel 7 trace file showed only 1 bind.
    Is there any way siebel 8 can be changed to do the binding only once?
  2. Why does the query in Siebel 8 need 1893514 logical reads while the same query in siebel needed only 684709 logical reads? (The two databases have about the same volume of data)
  3. There is a defect for bind peeking degradation [Doc ID 1273535.1]. Is it still a defect in 8.1.1.7? Is there anything we need to change in the 8.1.1.7 environment to improve performance?
  4. Is there anything else we need to change in our environment?

Solution

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