No data with DB Analysis?

[Former Member]

Hello all,

I am trying to use one of the DB tools that come with SAP. So, I do the following:

1. Txn db02old

2. Menu : DB Analysis -> Other DB Analysis

Firstly, I saw that buffer busy waits was > 0. Since the system uses Locally Managed Tablespaces, should this not be 0???

Anyways,

I then went to:

Performance

--> Wait Event Analysis

-----> Buffer Busy Waits

Double-clicking on this does not work. It produces nothing. What is one supposed to do?

TIA

[stefan_koehler · Accepted solution]

Hello Casey,

at first you don't tell us the oracle version that you are using. Depending on the oracle version the wait event "buffer busy waits" is split into more specific events or not.

I will post the oracle documentation from Oracle 10g:

http://68.142.116.68/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref2987

> Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is the buffer cache, but in a incompatible mode (that is, some other session is changing the buffer).

http://68.142.116.68/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#sthref3158

> This event occurs when a session requests a buffer that is currently being read into the buffer cache by another session. Prior to release 10.1, waits for this event were grouped with the other reasons for waiting for buffers under the 'buffer busy wait' event

> Since the system uses Locally Managed Tablespaces, should this not be 0???

So why shouldn't this happen with LMTs? There are some special cases in which you face more buffer busy waits without ASSM, but this has nothing to do with LMTs directly (LMT is "just" a requirement for ASSM).

Maybe the transaction DB02n or DBACOCKPIT is working better... but this also depends on the sap release and oracle versions.

Regards

Stefan

[Former Member · Accepted solution]

Hello Stefan and thanks for responding ...

The Oracle version I have is 10.2 on a Linux system

I basically saw this documentation

When multiple tasks want to insert or update rows in a table, there may be contention in the segment header for the table. This contention can manifest itself as a buffer busy wait or a freelist wait

I also read that

-> There were freelists associated with the segments

-> these freelists were in the segment header

-> That the freelists contained blocks that could have data inserted into it

-> That a block goes on (and off) the freelists based on PCTFREE

-> To reduce contention, add more freelists

I also read that buffer_busy_waits normally take place on the segment header of a segment.

But

With LMTS, supposedly all this goes away because there are no freelists to manage - supposedly, all is taken care of inside the segment.

This would mean: no more buffer_busy_waits..

TIA

[stefan_koehler · Accepted solution]

Hello Casey,

the articles that you read are more and less correct, but to understand why the "buffer busy waits" are reduced without the FREELISTs is much more complex and i think this would go far to deep to answer your question.

> With LMTS, supposedly all this goes away because there are no freelists to manage - supposedly, all is taken care of inside the segment.

And this statement is not correct.. the LMT itself has nothing to do with the "FREELIST management". The FREELIST mangement depends on the automatic segment space management (ASSM) or manual segment space management. But LMTs are the requirements to use ASSM.

Just read this:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN10065

> In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual. Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.

Regards

Stefan

[Former Member · Accepted solution]

Yikes!

I thought when creating a LMTS, ASSM was always used (no option for MANUAL).

My thoughts also were that the free lists contained a list of actual blocks in the segment. That in order for a process to be sure that the row could fit into the block, that it would first have to read it into memory.

If a second process wanted access to the same block, then it would have to wait (meaning a buffer busy wait?)

And this statement is not correct.. the LMT itself has nothing to do with the "FREELIST management". The FREELIST mangement depends on the automatic segment space management (ASSM) or manual segment space management. But LMTs are the requirements to use ASSM.

I think I should have said that on a manually managed tablespace :

-> freelists reside in the header of the segment.

-> If an insert/update on the segment takes place, the header block must be read into memory (providing an opportunity for a buffer_busy_wait)

-> The header block is read into memory in order to traverse the freelist to find an appropriate block

-> when traversing the list, each block on the list will have to be read into memory to see if the row will fit into it (providing another opportunity for a buffer_busy_wait)

But with ASSM,

-> There is no need to read the header of a segment

-> There is no need to traverse any kind of list looking for free blocks (i.e. there are no blocks to read)

because there is no such animal in a ASSM segment. All of that is taken care of within the segment itself.

Since there is no need for reading blocks into the memory, this is why I thought there would be less of an opportunity for buffer_busy_waits.

This was based on my understanding - although - I understand what you have written as well.

TIA

[stefan_koehler · Accepted solution]

Hello Casey,

But with ASSM,

>-> There is no need to read the header of a segment

>-> There is no need to traverse any kind of list looking for free blocks (i.e. there are no blocks to read)

You also need to read the segment header, if you are using ASSM. The difference between manual and automatic segment space handling is the type of finding free blocks. The ASSM is using something like an index structure.

There is no linked list between the block headers.. but the segment header and some other blocks (level X BMBs) also need to be read.

If you really want to know this in detail.. please check this really great presentation from Tanel Põder:

http://perfvision.com/docs/Poder_Freelists_vs_ASSM.ppt

Regards

Stefan