Oracle-L: FW: Siebel NOTE number for approving CBO for EIM

From: Leonard, George <george.leonard_at_farnell.com>
Date: Tue, 08 Oct 2002 00:48:25 -0800
Message-ID: <F001.004E2FBF.20021008004825@fatcity.com>

Hi guys.

That Siebel note about CBO that was asked for.

George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:george.leonard_at_za.didata.com
Web: http://www.didata.co.za <http://www.didata.co.za>

You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
-----Original Message-----

Sent: 08 October 2002 05:26 AM
To: Leonard, George

George

The only 'NOTE' I have regarding this is the following SR from SupportWeb. This is for Siebel 6.3 running against Oracle and shows Support suggesting to the client that the use of CBO is acceptable, providing statistics are kept up to date.

Why are you interested in this? I thought we had now loaded our bulk data and only had deltas to do, or are you now looking at Newark data?

If you want to go down this route you may want to open your own SR to Support, referencing the one below and asking for clarification that the use of CBO is OK.

The only thing Siebel normally has against CBO is that it can perform poorly compared to RBO, given that the SQL Siebel generates is tuned for the use of the RBO. However if you test both approaches and find that CBO is faster for you I cannot see why it will cause you any problems, it is simply a performance change, not a functional one.

Regards

Chuck

BM_1Service Request #: 38-240228854
Area: EIM S2000
Product Release: V6 (Siebel 2000-Enterprise) Version: 6.3 [10169]
Database: Oracle
Database Version: 8.1.6
Status: Resolved
GMT Date Opened: 02/12/2001 22:06:29
Abstract: EIM Performance on EIM_COMM
Messages: 1
<http://supportweb.siebel.com/supportsearch/sr/38-240228854.html#2> 2 <http://supportweb.siebel.com/supportsearch/sr/38-240228854.html#3> 3 Description:
We are doing mass imports to the S_COMMUNICATION table and have noticed slow EIM performance. I've set the the SQL PROFILE and have determined that one particular query has resulted in 95.4% of the total processing time.

I've done the explain plan. It showed 3 non-unique range scans for the update statement. The first index choice seems to be a bad choice, but there doesn't seem to be one that's better.

The production is targeted to contain 5MM records loaded into this table on a tight conversion schedule. Any performance robbers must be removed to prevent a lengthy conversion time.

BM_2Activity Type: Resolution
GMT Created: 05/09/2001 05:12:27
Messages:
<http://supportweb.siebel.com/supportsearch/sr/38-240228854.html#1> 1 2 <http://supportweb.siebel.com/supportsearch/sr/38-240228854.html#3> 3 Comment:
For the benefit of other readers, the performance degradation is owning to the fact that the database optimizer picked an inefficient index. Analyzing s_org_ext resolved the problem.

The slow query is:

UPDATE SIEBEL.EIM_COMM IT
SET T_COMMUNICA_PRCONI =
(SELECT /*+ INDEX(BT S_CONTACT_U3)EIM Index Hint */ MIN(BT.ROW_ID)

       FROM    SIEBEL.S_CONTACT BT
     WHERE (BT.BU_ID = IT.COMM_CON_BI AND
        BT.FST_NAME = IT.COMM_CON_FST_NAME AND
        BT.LAST_NAME = IT.COMM_CON_LAST_NAME AND
        BT.PRIV_FLG = IT.COMM_CON_PRIV_FLG AND
        (BT.MID_NAME = IT.COMM_CON_MID_NAME OR
         (BT.MID_NAME IS NULL AND IT.COMM_CON_MID_NAME IS NULL)) AND
        BT.PR_DEPT_OU_ID =
        (SELECT    MIN(BT2.ROW_ID)
           FROM SIEBEL.S_ORG_EXT BT2
         WHERE (BT2.BU_ID = IT.COMM_CON_ACC_BI AND
            (BT2.LOC = IT.COMM_CON_ACC_LOC OR
             (BT2.LOC IS NULL AND IT.COMM_CON_ACC_LOC IS NULL))
AND
            BT2.NAME = IT.COMM_CON_ACC_NAME))))

WHERE (IF_ROW_BATCH_NUM = 4036 AND
IF_ROW_STAT_NUM = 0 AND
T_COMMUNICA__STA = 0) According to the execution plan, s_org_ext_m17 instead of s_org_ext_u2 was picked originally for the subquery on s_org_ext. Although Oracle optimization mode is set to Rule for the application, cost-based optimizer is used to generate the execution plan for the above query because of the existence of the index hint. This is the behavior of the database. The cost-based optimizer relied on statistics to generate an efficient execution plan, while the rule-based optimizer generates plans based only on SQL statements. The fact that there was no statistics available for s_org_ext is the reason why s_org_ext_m17 was selected.

In this case, there are actually two ways to have the correct index, s_org_ext_u2, to be picked in the plan. One is to add the parameter "USE INDEX HINTS = FALSE" into the ifb file, which eliminates all the index hints during the EIM job. The other is to gather statistics for s_org_ext. Considering index hints might be useful for other queries, we decided to adopt the latter. Please note that it is necessary to update the statistics periodically if the EIM job is run on regular basis. Also note that there is no need to gather statistics for the whole database.

The EIM tuning methods mentioned above only apply when Oracle database is used.

Key Words: performance tuning optimizer
-----Original Message-----

Sent: Monday, October 07, 2002 2:55 PM
To: 'chirst_at_siebel.com'
Hi Chris

I got your contact detail from Johan Schultz, via Riaan van Deventer.

I am the Oracle DBA for the Challenge project done by Dimension Data for Farnell.

You mentioned to Riaan a while back that Siebel supports cost based optimizer during EIM.

Can you please forward the note number for me where this is approved/listed.

thx

George

chirst_at_siebel.com <mailto:chirst_at_siebel.com>
-----Original Message-----

Sent: 07 October 2002 14:51
To: Schultz, Johan
Hi there

I am looking for contact (email) for Hirst (PF Siebel TAM)

Can you help.

George

Het nie sy address nie, kry sy contact details by johan schultz
-----Original Message-----

Sent: 07 October 2002 15:15
To: 'RVanDeventer_at_newark.com'
Hi

Can you give me a email address ?

thx

George

There was no note. Speak to Charls Hirst (PF Siebel TAM)
-----Original Message-----

Sent: 07 October 2002 13:14
To: 'RVanDeventer_at_newark.com'
Cost Based Optimizer.

George

Miskien as jy my se wat "CBO" is sal ek weet....
-----Original Message-----

<mailto:George.Leonard_at_za.didata.com> ] Sent: 04 October 2002 07:33
To: Riaan.vanDeventer_at_za.didata.com; Johan.Swart_at_za.didata.com; Paula.deWaard_at_za.didata.com; seancpoulter_at_hotmail.com

Hi guys

I have been asked by a 3rd arty if I happen to have the note number of siebel whereby they approve CBO for EIM.

Can you please forward this to me.

thx

George

This message contains information intended solely for the addressee, which is confidential or private in nature and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate,

distribute or copy this message or any file attached to this message. Any such unauthorised use, is prohibited and may be unlawful. If you have received this message in error, please notify the sender immediately by e-mail,
facsimile or telephone and thereafter delete the original message from your machine.
Furthermore, the information contained in this message, and any attachments thereto, is for information purposes only and may contain the personal views

and opinions of the author,
which are not necessarily the views and opinions of Dimension Data (South Africa) (Proprietary) Limited or its subsidiaries and associated companies ("Dimension Data").
Dimension Data therefore does not accept liability for any claims, loss or damages of whatsoever nature, arising as a result of the reliance on such information by anyone.
Whilst all reasonable steps are taken to ensure the accuracy and integrity of information transmitted electronically and to preserve the confidentiality thereof,
Dimension Data accepts no liability or responsibility whatsoever if information or data is, for whatsoever reason, incorrect, corrupted or does not reach its intended destination.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Leonard, George
  INET: george.leonard_at_farnell.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Oct 08 2002 - 03:48:25 CDT