Oracle DBSL - Determine / Define size of fetch array

[stefan_koehler]

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-fd5c804b...

-

---

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

[Former Member]

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

[stefan_koehler]

Hello Michael,

i am refering to the documentation of DEFAULT_SDU_SIZE:

> Use the parameter DEFAULT_SDU_SIZE to specify the session data unit (SDU) size, in bytes to connections.

> session data unit (SDU)

>A buffer that Oracle Net uses to place data before transmitting it across the network. Oracle Net sends the data in the buffer either when requested or when it is full.

Ok, that controls the transmit moment in the network layer.. but not the number of rows which are returned by a single FETCH call (through the OCI). (For example in sqlplus it can be set with set arraysize XX)

I have tested also a little bit around with different sap standard tables in an ERP 2005 system.

I have activated a SQL Trace (ST05) and done a "select * from <TAB>" via SE16 (no count limit!).

In my tests there were fetched different number of rows - take a look by your own:

Time Object Operation Rows Returncode

2.451 T100 FETCH 321 0

1.915 T100 FETCH 321 0

1.912 T100 FETCH 321 1406

1.246 E071K FETCH 109 0

1.218 E071K FETCH 109 0

1.155 E071K FETCH 109 0

T100 (4 VARCHAR columns)

E071K (14 columns of different types)

So it seems like the DBSL dynamically calculates the number of rows which can be returned by a single fetch call based on the row size (number of columns and column sizes) and other unknown factors.

If this is true, it would be really great and nothing have to be done by us (the customers!).

But if the DBSL dynamically calculates the max. number of rows per fetch:

Why is a "SELECT * FROM <TAB> INTO <ITAB>" preferred - the SELECT Statement in a LOOP (like in the example) is also fetching the max number of rows by each fetch, or?

The example: http://sap.mis.cmich.edu/sap-abap/abap04/sld017.htm

That was only a notice on some small tests, which i have done.. maybe someone knows it in detail.

Regards

Stefan

[stefan_koehler]

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