Home » RDBMS Server » Performance Tuning » SQL statment running for ever (Oracle server 11g Enterprise Edition Release 11.1.0.7.0 - 64bit , Linux x86_64 GNU/Linux )
| SQL statment running for ever [message #464871] |
Sat, 10 July 2010 02:13  |
samiraly
Messages: 57
Registered: June 2009
|
Member |
|
|
Hello The follwing sql statment is running for hours , first i got an error telling that "unable to extend temp segment " as shown in the screen shot attached , then i added newa datafile to the temp tablespace and it is now running for 2 hours to finish ,
SELECT
T10.CONFLICT_ID,
T10.LAST_UPD,
T10.CREATED,
T10.LAST_UPD_BY,
T10.CREATED_BY,
T10.MODIFICATION_NUM,
T10.ROW_ID,
T4.ATTRIB_49,
T33.CUST_STAT_CD,
T4.ATTRIB_45,
T4.ATTRIB_50,
T33.ALIAS_NAME,
T33.X_ATTRIB_123,
T33.X_CCB_DT_ERSTE_AKTIVIERUNG,
T33.X_CCB_DT_ERSTER_DIENST,
T33.X_CCB_HIERARCHIE_STUFE,
T33.X_ATTRIB_108,
T33.X_CCB_KUNDENNUMMER,
T33.X_CCB_STATUS,
T4.ATTRIB_24,
T33.CMPT_FLG,
T33.BASE_CURCY_CD,
T33.HIST_SLS_CURCY_CD,
T4.ATTRIB_42,
T16.LOGIN,
T4.ATTRIB_12,
T33.DUNS_NUM,
T23.KEY_VALUE,
T33.DESC_TEXT,
T33.DIVISION,
T33.ENTERPRISE_FLAG,
T33.CREATED,
T33.FRGHT_TERMS_INFO,
T13.COMMENTS,
T33.X_ATTRIB_124,
T33.URL,
T33.INTEGRATION_ID,
T33.INT_ORG_FLG,
T33.PR_BL_OU_ID,
T22.NAME,
T33.X_KONDITIONENMODELL,
T4.ATTRIB_48,
T33.DEDUP_KEY_UPD_DT,
T33.X_LETZTE_AKTIVITAET_DATUM,
T33.X_LETZTER_BESUCH_DATUM,
T33.LOC,
T33.LOCATION_LEVEL,
T33.MAIN_FAX_PH_NUM,
T33.MAIN_PH_NUM,
T4.ATTRIB_21,
T33.X_ATTRIB_101,
T33.NAME,
T33.PO_PAY_CURCY_CD,
T33.PAR_OU_ID,
T7.NAME,
T33.PAR_DUNS_NUM,
T33.PRTNR_FLG,
T4.ATTRIB_37,
T4.ATTRIB_38,
T4.ATTRIB_34,
T10.NAME,
T33.X_POTENTIAL,
T12.NAME,
T33.CURR_PRI_LST_ID,
T11.COUNTRY,
T11.ZIPCODE,
T11.STATE,
T33.PR_ADDR_ID,
T33.PR_REP_DNRM_FLG,
T33.PR_REP_MANL_FLG,
T33.PR_REP_SYS_FLG,
T33.PR_REP_ASGN_TYPE,
T33.PR_BL_ADDR_ID,
T33.PR_BL_PER_ID,
T33.PR_MKT_SEG_ID,
T33.PR_CRDT_AREA_ID,
T33.PR_INDUST_ID,
T33.X_PR_OPTY_ID,
T33.BU_ID,
T33.PR_ORG_PAY_PRFL_ID,
T33.PR_POSTN_ID,
T33.PR_SRV_AGREE_ID,
T33.PR_SHIP_ADDR_ID,
T33.PR_SHIP_PER_ID,
T33.PR_SYN_ID,
T33.PR_TERR_ID,
T33.X_VERTRAGSPARTNER_ID,
T33.REFERENCE_CUST_FLG,
T33.X_REFERENZKUNDE,
T33.REGION,
T14.FST_NAME,
T14.LAST_NAME,
T1.X_REGION,
T32.ATTRIB_41,
T1.X_KANAL,
T19.OWN_INST_ID,
T19.INTEGRATION_ID,
T33.X_SUMME_S0,
T33.X_SUMME_VERTRAEGE,
T33.SURVEY_TYPE_CD,
T4.ATTRIB_05,
T33.PTNTL_SLS_CURCY_CD,
T33.CONTRACT_VIS_FLG,
T33.X_BAN,
T33.X_ATTRIB_133,
T6.AGREE_NUM,
T33.X_VOID,
T33.X_VO,
T29.LOW,
T4.ATTRIB_57,
T33.X_ATTRIB_109,
T33.X_WIEDERVORLAGE,
T33.X_SCELETON_CONTRACT,
T33.ACTIVE_FLG,
T33.X_READONLY,
T4.ATTRIB_66,
T33.X_DOUBLE_CHK_FLG,
T11.LAST_UPD,
T33.OU_TYPE_CD,
T33.X_TYPE_2,
T33.X_MAIL_SERVER,
T27.NAME,
T15.ROW_ID,
T28.ROW_STATUS,
T24.LOGIN,
T30.ATTRIB_03,
T30.ATTRIB_08,
T30.ATTRIB_35,
T30.ATTRIB_34,
T30.ATTRIB_16,
T30.ATTRIB_06,
T30.ATTRIB_49,
T30.ATTRIB_47,
T3.ROW_ID,
T30.ATTRIB_18,
T30.ATTRIB_19,
T30.ATTRIB_17,
T30.ATTRIB_05,
T30.ATTRIB_13,
T30.ATTRIB_07,
T30.ATTRIB_43,
T30.ATTRIB_10,
T30.ATTRIB_04,
T30.ATTRIB_12,
T30.ATTRIB_09,
T30.ATTRIB_26,
T3.CITY,
T3.COUNTRY,
T3.ZIPCODE,
T3.STATE,
T3.ADDR,
T30.ATTRIB_45,
T18.ADDR,
T5.NAME,
T2.ADDR,
T21.PAR_BU_ID,
T17.NAME,
T17.SIC,
T33.ROW_ID,
T33.PAR_ROW_ID,
T33.MODIFICATION_NUM,
T33.CREATED_BY,
T33.LAST_UPD_BY,
T33.CREATED,
T33.LAST_UPD,
T33.CONFLICT_ID,
T33.PAR_ROW_ID,
T19.ROW_ID,
T19.PAR_ROW_ID,
T19.MODIFICATION_NUM,
T19.CREATED_BY,
T19.LAST_UPD_BY,
T19.CREATED,
T19.LAST_UPD,
T19.CONFLICT_ID,
T19.PAR_ROW_ID,
T4.ROW_ID,
T4.PAR_ROW_ID,
T4.MODIFICATION_NUM,
T4.CREATED_BY,
T4.LAST_UPD_BY,
T4.CREATED,
T4.LAST_UPD,
T4.CONFLICT_ID,
T4.PAR_ROW_ID,
T15.ROW_ID,
T28.ROW_ID,
T26.ROW_ID,
T3.ROW_ID,
T18.ROW_ID,
T5.ROW_ID,
T2.ROW_ID,
T31.ROW_ID,
T9.ROW_ID,
T25.ROW_ID,
T17.ROW_ID
FROM
SIEBEL.S_POSTN T1,
SIEBEL.S_ADDR_ORG T2,
SIEBEL.S_ADDR_ORG T3,
SIEBEL.S_ORG_EXT_X T4,
SIEBEL.S_OPTY T5,
SIEBEL.S_DOC_AGREE T6,
SIEBEL.S_ORG_EXT T7,
SIEBEL.S_POSTN T8,
SIEBEL.S_PARTY T9,
SIEBEL.S_PARTY T10,
SIEBEL.S_ADDR_ORG T11,
SIEBEL.S_PRI_LST T12,
SIEBEL.S_ASGN_GRP T13,
SIEBEL.S_CONTACT T14,
SIEBEL.S_PARTY T15,
SIEBEL.S_USER T16,
SIEBEL.S_INDUST T17,
SIEBEL.S_ADDR_ORG T18,
SIEBEL.S_ORG_EXT_SS T19,
SIEBEL.S_LST_OF_VAL T20,
SIEBEL.S_ORG_EXT T21,
SIEBEL.S_ORG_EXT T22,
SIEBEL.S_DQ_ORG_KEY T23,
SIEBEL.S_USER T24,
SIEBEL.S_ORG_INDUST T25,
SIEBEL.S_PARTY T26,
SIEBEL.S_ORG_EXT T27,
SIEBEL.S_ACCNT_POSTN T28,
SIEBEL.S_LST_OF_VAL T29,
SIEBEL.S_ADDR_ORG_X T30,
SIEBEL.S_ORG_BU T31,
SIEBEL.S_CONTACT_X T32,
SIEBEL.S_ORG_EXT T33
WHERE
T4.ATTRIB_42 = T16.ROW_ID (+) AND
T33.PR_BL_OU_ID = T22.PAR_ROW_ID (+) AND
T33.PAR_OU_ID = T7.PAR_ROW_ID (+) AND
T33.PR_ADDR_ID = T11.ROW_ID (+) AND
T33.PR_POSTN_ID = T1.PAR_ROW_ID (+) AND
T1.PR_EMP_ID = T14.PAR_ROW_ID (+) AND
T1.PR_EMP_ID = T32.PAR_ROW_ID (+) AND
T1.PR_TERR_ID = T13.ROW_ID (+) AND
T10.ROW_ID = T23.ACCNT_ID (+) AND
T4.ATTRIB_34 = T29.VAL (+) AND T29.ACTIVE_FLG (+) = 'Y' AND T29.TYPE (+) = 'PARTNER_VT_KANAL' AND
T33.CURR_PRI_LST_ID = T12.ROW_ID (+) AND
T33.X_SCELETON_CONTRACT = T6.ROW_ID (+) AND
T10.ROW_ID = T33.PAR_ROW_ID AND
T10.ROW_ID = T19.PAR_ROW_ID (+) AND
T10.ROW_ID = T4.PAR_ROW_ID (+) AND
T33.X_TYPE_2 = T20.NAME (+) AND T20.TYPE (+) = 'ACCOUNT_TYPE_2' AND T20.LANG_ID (+) = :1 AND
T33.X_VERTRAGSPARTNER_ID = T15.ROW_ID (+) AND
T33.X_VERTRAGSPARTNER_ID = T27.PAR_ROW_ID (+) AND
T28.POSITION_ID = :2 AND T33.ROW_ID = T28.OU_EXT_ID AND
T26.ROW_ID = T28.POSITION_ID AND
T28.POSITION_ID = T8.PAR_ROW_ID (+) AND
T8.PR_EMP_ID = T24.PAR_ROW_ID (+) AND
T33.PR_ADDR_ID = T3.ROW_ID (+) AND
T33.PR_ADDR_ID = T30.PAR_ROW_ID (+) AND
T33.PR_SHIP_ADDR_ID = T18.ROW_ID (+) AND
T33.X_PR_OPTY_ID = T5.ROW_ID (+) AND
T33.PR_BL_ADDR_ID = T2.ROW_ID (+) AND
T33.BU_ID = T31.BU_ID (+) AND T33.ROW_ID = T31.ORG_ID (+) AND
T31.BU_ID = T9.ROW_ID (+) AND
T31.BU_ID = T21.PAR_ROW_ID (+) AND
T33.PR_INDUST_ID = T25.INDUST_ID (+) AND T33.ROW_ID = T25.OU_ID (+) AND
T33.PR_INDUST_ID = T17.ROW_ID (+) AND
((T10.ROW_ID IS NOT NULL) AND
(T33.INT_ORG_FLG != 'Y' OR T33.PRTNR_FLG = 'Y'))
ORDER BY
T28.POSITION_ID, T20.VAL
Bind variable 1: DEU
Bind variable 2: 1-1CWC
your cooporation is highly appreciated
Thanks.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL statment running for ever [message #464931 is a reply to message #464871] |
Sun, 11 July 2010 00:18  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you are running out of TEMP space, then it is either the HASH joins or the ORDER BY that is using up all of that space.
Note that the innermost table in your Explain Plan at step 42 has the filter:
42 - access("T26"."ROW_ID"='1-1CWC')
So for you to run out of TEMP space, it means that this filter is not very selective (selects lots of data).
I can also see from your plan that most of your joins are on Unique keys, but some of them are performing Range Scans and some Hash Joins. Are you sure that these joins are unique? Or are you joining many-to-many there somewhere. If so, that would probably explain the TEMP segment blowing out: many-to-many join generates millions of rows, ORDER BY tries to sort all of them.
If you remove the ORDER BY do you get the right results (albeit unsorted), or are there duplicates?
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Sat May 04 13:47:12 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
you are glad that you no lonnger support sieble and i am sad i have to support sieble , so any clue