Title: Message

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:[EMAIL PROTECTED]

Web:   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-----
From: Charles Hirst [mailto:
[EMAIL PROTECTED]]
Sent
: 08 October 2002 05:26 AM
To:
Leonard, George
Subject: RE: Siebel NOTE number for approving CBO for EIM

 

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

 

 

Service 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  
2  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.


Activity Type: Resolution
GMT Created:
05/09/2001 05:12:27
Messages:
1  2  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-----
From: Leonard, George [mailto:[EMAIL PROTECTED]]
Sent:
Monday, October 07, 2002 2:55 PM
To: '[EMAIL PROTECTED]'
Subject: FW: Siebel NOTE number for approving CBO for EIM

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

________________________________________________

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:[EMAIL PROTECTED]

Web:   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-----
From: Schultz, Johan
Sent: 07 October 2002 13:52 PM
To: Leonard, George
Subject: RE: Siebel NOTE number for approving CBO for EIM

 

-----Original Message-----
From: Leonard, George
Sent: 07 October 2002 14:51
To: Schultz, Johan
Subject: FW: Siebel NOTE number for approving CBO for EIM

Hi there

 

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

 

Can you help.

 

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:[EMAIL PROTECTED]

Web:   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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent:
07 October 2002 13:44 PM
To: Leonard, George
Subject: RE: Siebel NOTE number for approving CBO for EIM

 

Het nie sy address nie, kry sy contact details by johan schultz

-----Original Message-----
From: Leonard, George [mailto:[EMAIL PROTECTED]]
Sent:
07 October 2002 15:15
To: '[EMAIL PROTECTED]'
Subject: RE: Siebel NOTE number for approving CBO for EIM

Hi

 

Can you give me a email address ?

 

thx

 

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:[EMAIL PROTECTED]

Web:   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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent:
07 October 2002 13:11 PM
To: Leonard, George
Subject: RE: Siebel NOTE number for approving CBO for EIM

 

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

-----Original Message-----
From: Leonard, George [mailto:[EMAIL PROTECTED]]
Sent:
07 October 2002 13:14
To: '[EMAIL PROTECTED]'
Subject: RE: Siebel NOTE number for approving CBO for EIM

Cost Based Optimizer.

 

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:[EMAIL PROTECTED]

Web:   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-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent:
04 October 2002 14:54 PM
To: Leonard, George (DD)
Subject: RE: Siebel NOTE number for approving CBO for EIM

 

Miskien as jy my se wat "CBO" is sal ek weet....
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 04 October 2002 07:33
To: [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Siebel NOTE number for approving CBO for EIM

 

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
________________________________________________
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:[EMAIL PROTECTED]
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!
 

 

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.

Reply via email to