Re: ORA-04031 on query

Hi,

What is your shared pool? Did you try to increase it?

Andrew Protasov

> I have a particularly large canned query that's joining five tables, three
> are @ 700,000 rows and the other two are small. We always get the ORA-04031
> query ... cannot allocate 57342 bytes of memory in shared pool.
>
> At a high level, I'm wondering what my first step is in solving this
> problem. The .trc file
> is below.
>
> Thanks,
> Rob.
>
> *** SESSION ID:(25.2007) 1998.11.05.14.38.31.000
> ksedmp: internal or fatal error
> ORA-04031: unable to allocate 57372 bytes of shared memory ("unknown
> object","cursor work he","sort merge buffer")
> Current SQL statement for this session:
> SELECT
> T5.ROW_ID,
> T5.MODIFICATION_NUM,
> T5.CREATED_BY,
> T5.LAST_UPD_BY,
> T5.CREATED,
> T5.LAST_UPD,
> T5.CONFLICT_ID,
> T4.NAME,
> T4.BASE_CURCY_CD,
> T5.PR_DEPT_OU_ID,
> T4.LOC,
> T5.ALT_PH_NUM,
> T5.ASST_PH_NUM,
> T5.BIRTH_DT,
> T5.COMMENTS,
> T5.X_PR_EMAIL_ID,
> T1.ATTRIB_27,
> T5.FAX_PH_NUM,
> T5.FST_NAME,
> T5.HOME_PH_NUM,
> T5.JOB_TITLE,
> T4.ROW_ID,
> T5.LAST_NAME,
> T5.SEX_MF,
> T5.PER_TITLE,
> T1.ATTRIB_08,
> T5.CSN,
> T5.OWNER_PER_ID,
> T5.PR_OU_ADDR_ID,
> T5.PR_PER_ADDR_ID,
> T5.PR_POSTN_ID,
> T5.SUPPRESS_MAIL_FLG,
> T5.WORK_PH_NUM,
> T5.EMP_NUM,
> T5.MID_NAME,
> T5.PRIV_FLG,
> T1.ATTRIB_46,
> T1.ATTRIB_39,
> T1.ATTRIB_05,
> T1.ATTRIB_47,
> T1.ATTRIB_36,
> T5.RACE,
> T5.X_CALLS_YTD,
> T5.SOC_SECURITY_NUM,
> T1.ATTRIB_01,
> T1.ATTRIB_43,
> T5.X_TOTAL_PORTFOLIO,
> T1.ATTRIB_10,
> T1.ATTRIB_11,
> T1.ATTRIB_03,
> T1.ATTRIB_40,
> T1.ATTRIB_28,
> T1.ATTRIB_19,
> T5.MARITAL_STAT_CD,
> T1.ATTRIB_45,
> T5.CON_CD,
> T1.ATTRIB_20,
> T2.CITY,
> T2.COUNTRY,
> T2.ZIPCODE,
> T2.ADDR,
> T3.NAME,
> T4.ROW_ID,
> T1.ROW_ID,
> T1.MODIFICATION_NUM,
> T1.CREATED_BY,
> T1.LAST_UPD_BY,
> T1.CREATED,
> T1.LAST_UPD,
> T1.CONFLICT_ID,
> T1.PAR_ROW_ID,
> T2.ROW_ID,
> T3.ROW_ID
> FROM
> SIEBEL.S_CONTACT_X T1,
> SIEBEL.S_ADDR_PER T2,
> SIEBEL.S_CONTACT_XM T3,
> SIEBEL.S_ORG_EXT T4,
> SIEBEL.S_CONTACT T5
> WHERE
> T5.PR_DEPT_OU_ID = T4.ROW_ID (+) AND
> T5.ROW_ID = T1.PAR_ROW_ID (+) AND
> T5.PR_PER_ADDR_ID = T2.ROW_ID (+) AND
> T5.X_PR_EMAIL_ID = T3.ROW_ID (+) AND
> (T5.MID_NAME <= :1)
> ORDER BY
> T5.LAST_NAME, T5.FST_NAME, T5.MID_NAME
>
>
>
>
>
>
Received on Fri Nov 06 1998 - 15:14:43 CST