Skip to Content

Oracle DBSL - Determine / Define size of fetch array

Hello guys,

i have a question about the Oracle DBSL and how it determines the size of the fetch array.

I am refering to the following documents / documentations:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/86a0b490-0201-0010-9cba-fd5c804b99a1

-


Quote:

As long as you do not program an UP TO ROWS in ABAP, the database gets as many table rows as possible per communication set (as many as fit in 32k).

-


http://help.sap.com/saphelp_nw04/helpdata/en/d1/801f96454211d189710000e8322d00/frameset.htm

-


Quote:

You have to specify the size of an array before runtime. However, because you cannot know the size of the dataset the system will return, you must define a very large array to avoid an overflow.

To circumvent this problem, the SAP Basis System translates ABAP Open SQL statements into Embedded SQL. To do this, the system defines a cursor.

-


Ok but the translated embedded SQL code has also to specifiy an array fetch size (how many rows are retrieved by one fetch).

Is this a profile parameter or is it really calculated (how many rows of a specified table would fit into 32k) dynamically in the DBSL?

Regards

Stefan

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Apr 01, 2008 at 03:21 PM

    As long as you do not program an UP TO ROWS in ABAP, the database gets as many table rows as possible per communication set (as many as fit in 32k).

    Might be the sqlnet parameter DEFAULT_SDU_SIZE, it is set to 32k. I fiddled around with it a bit, but my little abap program was always fetching net data at about 24k.

    The default is 2k, but in the standard tnsnames.ora 32k is specified (SDU).

    SID.WORLD=
      (DESCRIPTION =
          (SDU = 32768)
          (ADDRESS_LIST =
              (ADDRESS =

    Regards

    Michael

    Add comment
    10|10000 characters needed characters exceeded

    • Hello guys,

      now i have found a little bit time for some testing - it seems like that the "SELECT <> INTO TABLE" has no impact on the rows that are returned by one FETCH.

      My test object was the table USR05 - now take a look at the tests:

      -


      SQL> desc SAPSR3.USR05;

      Name Null? Type

      MANDT NOT NULL VARCHAR2(9)

      BNAME NOT NULL VARCHAR2(36)

      PARID NOT NULL VARCHAR2(60)

      PARVA NOT NULL VARCHAR2(120)

      -


      ABAP Coding in LOOP:

      >SELECT * from USR05.

      > write USR05-BNAME.

      >ENDSELECT.

      ST05-Trace

      >Time Object Operation Rows RC

      >491 USR05 PREPARE 0

      >5 USR05 OPEN 0

      >2.113 USR05 FETCH 411 0

      >1.793 USR05 FETCH 411 0

      >997 USR05 FETCH 200 1403

      -


      ABAP Coding with INTO TABLE:

      > SELECT * from USR05 INTO TABLE T_USR05.

      ST05-Trace

      >Time Object Operation Rows RC

      >7 USR05 REOPEN 0

      >1.846 USR05 FETCH 411 0

      >1.880 USR05 FETCH 411 0

      >1.319 USR05 FETCH 200 1403

      -


      So as you can see in both cases the FETCH returns 411 or 200 rows.. so the "SELECT INTO TABLE" has no impact on the rows of the array fetch.

      So the statement "The database system reads the entries in bundles, not singly" from the example link (http://sap.mis.cmich.edu/sap-abap/abap04/sld017.htm) seems not to be true..

      So why should a "SELECT INTO TABLE" improve the performance of a report?

      Regards

      Stefan