• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • Last Modified:

Need Urgent Help for Siebel SQL tuning on Oracle 9

There is a SQL query as following:
SELECT
      T10.CONFLICT_ID,
      T10.LAST_UPD,
      T10.CREATED,
      T10.LAST_UPD_BY,
      T10.CREATED_BY,
      T10.MODIFICATION_NUM,
      T10.ROW_ID,
      T24.PR_DEPT_OU_ID,
      T2.PRTNR_FLG,
      T2.SURVEY_TYPE_CD,
      T24.PR_REGION_ID,
      T24.CELL_PH_NUM,
      T24.CON_IMAGE_ID,
      T7.ATTRIB_07,
      T24.EMAIL_ADDR,
      T24.ALT_EMAIL_ADDR,
      T24.EMP_FLG,
      T24.EMP_ID,
      T24.FAX_PH_NUM,
      T24.FST_NAME,    
      T24.HOME_PH_NUM,
      T24.AGENT_FLG,
      T24.JOB_TITLE,
      T24.LAST_NAME,
      T21.LOGIN,
      T24.MEMBER_FLG,
      T24.MID_NAME,
      T19.POSTN_ID,
      T24.OWNER_PER_ID,
      T10.NAME,
      T24.PERSON_UID,
      T19.LAST_CALL_DT,
      T24.PR_OU_ADDR_ID,
      T2.NAME,
      T19.PR_ADDR_ID,
      T24.PR_AFFL_ID,
      T24.PR_EMAIL_ADDR_ID,
      T24.PR_ALT_PH_NUM_ID,
      T24.PR_REP_DNRM_FLG,
      T24.PR_REP_MANL_FLG,
      T24.PR_REP_SYS_FLG,
      T24.PR_MKT_SEG_ID,
      T22.PR_EMP_ID,
      T9.PR_EMP_ID,
      T14.LOGIN,
      T17.LOGIN,
      T24.PR_GRP_OU_ID,
      T24.PR_INDUST_ID,
      T24.PR_NOTE_ID,
      T24.PR_OPTY_ID,
      T24.BU_ID,
      T24.PR_SYNC_USER_ID,
      T24.PR_PER_ADDR_ID,
      T24.PR_POSTN_ID,
      T24.PR_PROD_LN_ID,
      T24.PR_RESP_ID,
      T24.PR_SECURITY_ID,
      T6.NAME,
      T24.MED_SPEC_ID,
      T24.PR_STATE_LIC_ID,
      T24.PR_TERR_ID,
      T24.PROVIDER_FLG,
      T24.RACE,
     T24.WEB_REGION_ID,
      T24.ROW_ID,
      T13.OWN_INST_ID,
      T13.INTEGRATION_ID,
      T12.SHARE_HOME_PH_FLG,
      T24.CUST_SINCE_DT,
      T24.SOC_SECURITY_NUM,
      T19.STATUS,
      T24.SUPPRESS_MAIL_FLG,
      T24.SEND_SURVEY_FLG,
      T24.PREF_LANG_ID,
      T23.ADDR,
      T23.CITY,
      T23.COUNTRY,
     T23.ZIPCODE,
      T23.STATE,
      T24.WORK_PH_NUM,
      T12.EMP_PH_NUM_EXT,
      T7.ATTRIB_35,
      T7.ATTRIB_02,
      T3.FST_NAME,
      T3.LAST_NAME,
      T18.ROW_STATUS,
      T15.NAME,
      T15.LOC,
      T15.PR_BL_ADDR_ID,
      T15.PR_BL_PER_ID,
      T15.PR_SHIP_ADDR_ID,
      T15.PR_SHIP_PER_ID,
      T15.CUST_STAT_CD,
      T15.OU_TYPE_CD,
      T15.OU_NUM,
     T5.ROW_ID,
      T15.PR_SRV_AGREE_ID,
      T5.ROW_ID,
     T15.INTEGRATION_ID,
      T11.CITY,
      T11.COUNTRY,
      T11.ZIPCODE,
      T11.STATE,
      T11.ADDR,
      T11.ZIPCODE,
     T20.LOGIN,
      T12.ROW_ID,
      T12.MODIFICATION_NUM,
      T12.CREATED_BY,
      T12.LAST_UPD_BY,
      T12.CREATED,
     T12.LAST_UPD,
      T12.CONFLICT_ID,
      T12.PAR_ROW_ID,
     T21.ROW_ID,
      T21.MODIFICATION_NUM,
      T21.CREATED_BY,
      T21.LAST_UPD_BY,
      T21.CREATED,
      T21.LAST_UPD,
      T21.CONFLICT_ID,
      T21.PAR_ROW_ID,
      T24.ROW_ID,      
      T24.MODIFICATION_NUM,
      T24.CREATED_BY,
      T24.LAST_UPD_BY,
      T24.CREATED,
      T24.LAST_UPD,
      T24.CONFLICT_ID,
      T24.PAR_ROW_ID,
      T7.ROW_ID,
      T7.MODIFICATION_NUM,
      T7.CREATED_BY,
      T7.LAST_UPD_BY,
      T7.CREATED,
      T7.LAST_UPD,
      T7.CONFLICT_ID,
      T7.PAR_ROW_ID,
      T13.ROW_ID,
      T13.MODIFICATION_NUM,
      T13.CREATED_BY,
      T13.LAST_UPD_BY,
      T13.CREATED,
      T13.LAST_UPD,
      T13.CONFLICT_ID,
      T13.PAR_ROW_ID,
      T18.ROW_ID,
      T16.ROW_ID,
      T5.ROW_ID,
      T4.ROW_ID,
      T11.ROW_ID,
      T1.ROW_ID
   FROM
       SIEBEL.S_PARTY T1,
       SIEBEL.S_ORG_EXT T2,
       SIEBEL.S_CONTACT T3,
       SIEBEL.S_CON_ADDR T4,
       SIEBEL.S_PARTY T5,
       SIEBEL.S_MED_SPEC T6,
       SIEBEL.S_CONTACT_X T7,
       SIEBEL.S_POSTN T8,
       SIEBEL.S_POSTN T9,
       SIEBEL.S_PARTY T10,
       SIEBEL.S_ADDR_PER T11,
       SIEBEL.S_EMP_PER T12,
       SIEBEL.S_CONTACT_SS T13,
       SIEBEL.S_USER T14,
       SIEBEL.S_ORG_EXT T15,
       SIEBEL.S_PARTY T16,
       SIEBEL.S_USER T17,
       SIEBEL.S_POSTN_CON T18,
       SIEBEL.S_POSTN_CON T19,
       SIEBEL.S_USER T20,
       SIEBEL.S_USER T21,
       SIEBEL.S_POSTN T22,
       SIEBEL.S_ADDR_PER T23,
       SIEBEL.S_CONTACT T24
   WHERE
      T24.PR_DEPT_OU_ID = T2.PAR_ROW_ID (+) AND
      T2.PR_POSTN_ID = T22.PAR_ROW_ID (+) AND
      T24.PR_POSTN_ID = T9.PAR_ROW_ID (+) AND
      T10.ROW_ID = T19.CON_ID (+) AND T19.POSTN_ID (+) = '0-5220' AND
      T22.PR_EMP_ID = T14.PAR_ROW_ID (+) AND
      T9.PR_EMP_ID = T17.PAR_ROW_ID (+) AND
      T24.PR_PER_ADDR_ID = T23.ROW_ID (+) AND
      T24.MED_SPEC_ID = T6.ROW_ID (+) AND
      T10.ROW_ID = T12.PAR_ROW_ID (+) AND
      T10.ROW_ID = T21.PAR_ROW_ID (+) AND
      T10.ROW_ID = T24.PAR_ROW_ID AND
      T10.ROW_ID = T7.PAR_ROW_ID (+) AND
      T10.ROW_ID = T13.PAR_ROW_ID (+) AND
      T24.PR_POSTN_ID = T18.POSTN_ID AND
      T24.ROW_ID = T18.CON_ID AND
      T18.POSTN_ID = T16.ROW_ID AND
      T18.POSTN_ID = T8.PAR_ROW_ID (+) AND
      T8.PR_EMP_ID = T3.PAR_ROW_ID (+) AND
      T24.PR_DEPT_OU_ID = T5.ROW_ID (+) AND
      T24.PR_DEPT_OU_ID = T15.PAR_ROW_ID (+) AND
      T24.PR_PER_ADDR_ID = T4.ADDR_PER_ID (+) AND
      T24.ROW_ID = T4.CONTACT_ID (+) AND
      T24.PR_PER_ADDR_ID = T11.ROW_ID (+) AND
      T24.PR_SYNC_USER_ID = T1.ROW_ID (+) AND
      T24.PR_SYNC_USER_ID = T20.PAR_ROW_ID (+) AND
      (T24.PRIV_FLG = 'N') AND
     (T24.WORK_PH_NUM LIKE '111%' OR T24.HOME_PH_NUM LIKE '111%'
        OR T24.CELL_PH_NUM LIKE '111%')


Explain Plan to get info " TABLE ACCESS FULL  SIEBEL.S_CONTACT", also SQL Tuning on Toad to get "Every Row in the SIEBEL.S_CONTACT table is read".

I checked the indexes on the SIEBEL.S_CONTACT table, seems not an index problem.

I don't know why I got this message "every row is read". How to tuning this SQL?  

Thanks,

Lily
0
nkwzxbz
Asked:
nkwzxbz
  • 2
1 Solution
 
actonwangCommented:
you could use /*+ INDEX(S_CONTACT indexname) */ .
0
 
Mark GeerlingsDatabase AdministratorCommented:
The big problem here is the outer join operators (+) on most of the join clauses!  Outer joins can cause a huge performance penalty.  The results will vary based on: the number of records in the tables; the version of Oracle; some init parameters; the size of the SGA, the amount of RAM in the server, etc.

Try removing the outer joins, but you will have to test this to see if you still get the same number of records returned (you may not get any - and that will be a problem).  If outer joins are required to get the answer, then either: the application is poorly-designed, or at least not designed to answer this question; or the data entry is incomplete or inaccurate.

Since the s_contact table (T24) is the last table listed in the "from" clause, and since TOAD indicates a "full table scan" on that table, it appears that Oracle's query optimizer is using this table as the "driving" table.  That would suggest that your database is set for the older rule-based optimizer, and not the default cost-based optimzer of Oracle9.  Please check you init parameter for "optimizer mode" and tell us what value you have for that.

The query should probably start with s_contact (T24) as the "driving" table, since that is the only one that has values provides in the "where" clauses (other than T19.POSTN_ID (+) = '0-5220' but that is an outer-join, so not a good place to start). The "where" clauses should be re-arranged so that the values from T24 are all to the right of the "=" and not to the left, in the clauses that join T24 to other tables.  For example, change these two lines (and the other lines for T24 that include a second table):
    T24.PR_POSTN_ID = T18.POSTN_ID AND
    T24.ROW_ID = T18.CON_ID AND
to:
    T18.POSTN_ID = T24.PR_POSTN_ID AND
    T18.CON_ID = T24.ROW_ID AND

The only indexes on s_contact that could help you would be the indexes on one of the three phone number columns, but since the query provides only a partial value, not the full phone number, even these indexes may not help.  It may be fastest to do a "full-table scan" of s_contact.
0
 
nkwzxbzAuthor Commented:
Thanks everyone for suggestion. This code is generated by Siebel 7.5, we can't remove the outer joins, also Siebel 7.5 only support rule-based optimizer. I will try to re-arrange "where" clauses.

Lily
0
 
Mark GeerlingsDatabase AdministratorCommented:
Many "canned" applications, like: Siebel, SAP, etc. either do not know how to optimize their code for Oracle, or they don't try to optimize their code for Oracle.  Of course they don't tell you that before you buy it!
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now