topic Re: ORA 4031 in Application Development and Automation Discussions

ORA 4031

Former Member — Wed, 21 Dec 2005 12:06:25 GMT

ora 4031 is caused by a lot of 'in' and/or 'or' in one sql statement.

and not by an (maybe) poor performance statement caused by a not existing index.

SELECT LIKE THIS : p_rsize = 3500

LOOP AT i_edidc INTO w_edidc.

r_docnum-low = w_edidc-docnum.

APPEND r_docnum.

IF sy-tabix EQ p_rsize.

SELECT docnum counter segnum segnam

APPENDING TABLE i_edid4

FROM edid4

WHERE docnum IN r_docnum

AND segnam IN s_segnam.

FREE r_docnum.

ENDIF.

ENDLOOP.

LAST DOCUMENT TO RETRIEVE

IF NOT r_docnum[] IS INITIAL.

SELECT docnum counter segnum segnam

APPENDING TABLE i_edid4

FROM edid4

WHERE docnum IN r_docnum

AND segnam IN s_segnam.

ENDIF.

Re: ORA 4031

Former Member — Wed, 21 Dec 2005 12:12:03 GMT

hi why are using this kind of selects,

instead of that you can use <b>IDOC_READ_COMPLETELY</b>

   CLEAR: IT_EDIDS,IT_EDIDD.
    REFRESH: IT_EDIDS,IT_EDIDD.
*-- Reading the IDoc Completely
    CALL FUNCTION 'IDOC_READ_COMPLETELY'
      EXPORTING
        DOCUMENT_NUMBER         = I_IDOC_NUMBER
      IMPORTING
        IDOC_CONTROL            = X_EDIDC
      TABLES
        INT_EDIDS               = IT_EDIDS
        INT_EDIDD               = IT_EDIDD
      EXCEPTIONS
        DOCUMENT_NOT_EXIST      = 1
        DOCUMENT_NUMBER_INVALID = 2
        OTHERS                  = 3.
    IF SY-SUBRC <> 0.
      MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
          WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    ENDIF.

try this.., you need to pass only IDoc number.

Re: ORA 4031

Former Member — Wed, 21 Dec 2005 12:25:26 GMT

Hi,

I use this select to improve perf.

'IDOC_READ_COMPLETELY'

will decrease performence...

cheerz

Re: ORA 4031

Former Member — Wed, 21 Dec 2005 12:28:35 GMT

I have used it, but you are using selects in side loops.

thats why i suggested ,

vijay

Re: ORA 4031

Former Member — Wed, 21 Dec 2005 12:30:32 GMT

Yes I know!

But I use selected with RANGES...

Lot of data to retrieve...

The fm module will read idoc by idocs

Re: ORA 4031

christian_wohlfahrt — Wed, 21 Dec 2005 13:10:29 GMT

Hi Stephan!

Your coding is in general a good improvement. You can also go for select all entries in... - then you don't have problems with to big SQL-statements, but you don't need if you don't like it.

Otherwise I propose to split dynamical, maybe based on number of entries in your second range: 5000 / 2nd range = split.

Regards,

Christian

Re: ORA 4031

Former Member — Wed, 21 Dec 2005 13:46:26 GMT

Hi Chris,

The split wouldn't change the way the optimizer prepare the code, don't you think ?

But indeed it could be intersting to test this!

I'll let you know

regards,

Re: ORA 4031

christian_wohlfahrt — Wed, 21 Dec 2005 14:24:44 GMT

No, not the way, just the amount of and/or.

But what exactly is ORA4031 - I don't have a DB2 (or whatever system this is). I thought it's the typical statement to large dump.