Understanding on buffering

[Former Member]

Dear friends,

Suppose i execute one query in abap

select * from mara where mtart = 'IFIG'.

How my system behaves.I feel first DBInterface looks data on to buffer area of appserver.Here is there any role of message server ?.

Suppose If buffer has data ,it fetches from buffer and dbinterface passes data back to request dispatcher.

If not then it looks into databse server and updates buffer and then return back the result.

However what will be query fired then ,will it be select * from mara irrespective of where clause means all data will be fetched or partial .

Also suppose i do setting No buffering in mara table,will buffering on appserver be referred or not.

I have coded many report where on execution it takes first time to be slow.But next time in seconds.Though tables used on such reports\ have No Buffering setting.How then buffering applies ?.

I am confused about buffering management and even readings cannot convince me.

I leave this topic on debate for all readers of this topic .

Regards,

Aditya

[Former Member]

Dear friends,

Suppose i execute one query in abap

select * from mara where mtart = 'IFIG'.

How my system behaves.I feel first DBInterface looks data on to buffer area of appserver.Here is there any role of message server ?.

Suppose If buffer has data ,it fetches from buffer and dbinterface passes data back to request dispatcher.

If not then it looks into databse server and updates buffer and then return back the result.

However what will be query fired then ,will it be select * from mara irrespective of where clause means all data will be fetched or partial .

Also suppose i do setting No buffering in mara table,will buffering on appserver be referred or not.

I have coded many report where on execution it takes first time to be slow.But next time in seconds.Though tables used on such reports\ have No Buffering setting.How then buffering applies ?.

I am confused about buffering management and even readings cannot convince me.

I leave this topic on debate for all readers of this topic .

Regards,

Aditya

[HermannGahm]

Hi,

looks like you confuse the SAP table buffers with the DB caches.

your first execution (solw query) will do phyical i/O and read the db pages

in the db cache. 2nd exectution can get pages from the cache and will be

much faster.

Kind regards,

Hermann

[Former Member]

the SAP table buffer is only for tables which change rarely and which are usually not so large, i.e. no application tables.

And it does only work, if it is activated in the technical settings.

MARA is definitely not in the table buffer. There the DB cache as explained above has the effect that second and further executions are faster than the first one.

[Former Member]

Your answer fully explains my doubt.However i just want to understand

say i pass select * from mara where mtart= 'IFIG'

WHAT WILL TECHNICALLY HAPPEN

[HermannGahm]

Hi,

since MARA is not buffered, the DBI (DBSL to be more precisse)

transforms the statement to native SQL and sends the request to the DB

and waits for the result in order to process it (copy it in ABAP variables...)

That's basically it.

Kind regards,

Hermann

[volker_borowski2]

Hi,
> 
> since MARA is not buffered, the DBI (DBSL to be more precisse)
> transforms the statement to native SQL and sends the request to the DB
> and waits for the result in order to process it (copy it in ABAP variables...)
> That's basically it.
> 
> Kind regards,
> 
> Hermann

Just to complete Hermanns fully correct statement:

However, IF INDEED someone modified MARA to be fully buffered, strange things will happen:

The first request, no matter what WHERE clause it has, will do a

SELECT * from MARA WHERE MANDT='xxx'

Most likely this will run for quite a while and will either give a timeout if called in dialog,

or will get a buffer failiure, because your SAP buffer will likely be too small to hold a complete MARA.

However, this error will be stored as a buffer information, and any second call will proceed to the DB-layer,

and be processed as if the table is not buffered. After a while, the "buffer-fail" information is cleared and the next

call will begin right again to try buffering.

I had this recently (not with MARA, but with a huge Z-table, that was set to FULL BUFFERED).

The first call of the day ran forever and did fail, and every call after this was lightning fast, because all

statements executed were well index-supported.

When we did a SQL trace it was quite funny to see a full table scan as execution plan fired by a FAE select

with a selective WHERE. I assumed a kernel bug first, because I could not even imagine someone would set

a 300 GB table to be fully buffered. Checking the table buffer after spotting the bufferflag revealed, that indeed

a "buffer failed" status is as well a buffer status information which disables buffering (for a while).

Volker

[HermannGahm]

Hi Volker,

yes what you describe happens once in a while.

> Checking the table buffer after spotting the bufferflag revealed, that indeed
> a "buffer failed" status is as well a buffer status information which disables buffering (for a while).
> 
> Volker

The status "error" in ST10 shows if it happened to a table. This status however is kept till the instance

is shut down (or if someone manually removes the table from the buffer).

Kind regards,

Hermann

[Former Member]

Thanx all of you for sharing your experiences till now.

[Former Member]

If your question is answered, please assign points to helpful answers and mark this as closed.

Rob

[Former Member]

I agree to you Volker.Just recently we found one query doing full table scan and table was fully buffered.

SAP suggested us to remove Full Buffering.As we did we could see query lightning fast and CBO using correct index.

[Former Member]

I still have one doubt.In all books i read,even in material released by SAP,i could not find DB Cache as part of architecture component.I was therefore believing Buffer area of application server doing everything.Where is this DB Cache situated,

[volker_borowski2]

The status "error" in ST10 shows if it happened to a table. This status however is kept till the instance
is shut down (or if someone manually removes the table from the buffer).

Hello Hermann,

well it was an ERP System with BASIS version 7 on a SP level I think in the low "teens".

The status in ST10 was not "error" but "buf fail" or similar (abrevieated two words, highlighted in red!).

I do not know what kernel the system had at that time.

I am pretty sure about the problem was re-appearing each morning without the system being restarted.

I do not know how any action should take this buffer flag of, beside transports (which the system got a couple per day).

But if that would be a reason for the reset, I do not understand why we did not had the error in the afternoon again.

It was always only the first call in the morning. This is why I concluded some sort of timing.

Volker

[HermannGahm]

Hi,

>Just recently we found one query doing full table scan and table was fully buffered.

in the table buffer

> SAP suggested us to remove Full Buffering.As we did we could see query lightning fast and CBO using correct index

yes, this happens once in a while...

Kind regards,

Hermann

[HermannGahm]

Hi,

I still have one doubt.In all books i read,even in material released by SAP,i could not find DB Cache as part of architecture component.I was therefore believing Buffer area of application server doing everything.Where is this DB Cache situated,

i wonder what kind of material you have seen... i'm sure it IS mentioned in BC490 and ADM315 and others.

In my book:

http://www.sap-press.com/products/ABAP-Performance-Tuning.html

in figure 2.1 and 3.3 it is clearly illustrated.

and in chapter 5.1 it is explained ...

"The data cache is the largest part of the database main memory. It buffers data

from the data files. After receiving an SQL query, the corresponding database process

first queries this cache for the required data. This takes CPU resources. If the

searched data is not found in the cache, it must be read to the data cache through

the physical I/O. The physical I/O is about ten times slower than the access to data

in the data cache."

Kind regards,

Hermann

[HermannGahm]

Hi Volker,

i must admit that i've never seen this status before so far... .

Will see what i can find about it...

Kind regards,

Hermann

[HermannGahm]

Hi Volker,

i must admit that i've never seen this status before so far... .

Will see what i can find about it...

Kind regards,

Hermann