Re: Freelist confusion

My understanding is exactly what you described, however, I have never tested that what
I had read/learned was in fact true. If I had read this book first I may have thought otherwise.

Perhaps the observations the author is making are specific to a certain OS and version of
Oracle.

In the end if the book is riddled with mistakes, prove the author wrong and ask for your money
back. There is nothing quite like misinformation to make your head spin.

Scott.

"Samantha Hall" <samanthahall_at_qwest.net> wrote in message news:3B17207D.6DD2EDDB_at_qwest.net...
> Has anyone seen the new Oracle Press book titled 'Oracle High
> Performance Tuning with Statspack' ? It has really confused me on he
> area of freelist contention, which is something I have seen a number of
> times. I have listed a number of questions below and page references to
> see if any of you guys can add any input or explanations:
>
> 1. Pg 286: says that the freelists paramater tells Oracle how many
> segment header blocks to create for a table or index. I always thought
> that there will ALWAYS be just one segment header. Doesn't the freelist
> parameter just define how many process freelists are created in the
> segment header or in the freelist group blocks, should they be defined ?
>
> 2. Pg 290: says that the high water mark moves in sizes (5*(number of
> freelists+1)) and adds these blocks to the master freelist. I was always
> under the impression that Oracle moves the HWM one block at a time until
> it has moved five blocks after the segment header, and then will move in
> increments of 5 blocks or more if close to the extent boundary. Does
> anybody agree or think differently ?
>
> 3. Pg 298: talks about how if you see waits on the 'free list' block
> class in v$waitstat, you are seeing the number of times Oracle has
> waited for a freelist to become available. Surely this is only if you
> have defined freelist groups. I always thought and seen freelist
> contention show up as buffer busy waits, and the block class of 'data
> block'. The reason being that with few process freelists, processes will
> contend for the head of their particular process freelist or the master
> freelist. Wouldn't the 'free list' block class show up like contention
> for the 'segment header', when we are changing the freelist structure ?
>
> 4. Pg 307-309: talks you through identifying the exact block causing
> buffer busy wait contention. The author talks about looking at the
> tablespace and block number, but never mentions about the file number.
> The examples then show how to dump a block to get the object number
> which you can then use to query dba_objects. Is this not an extremely
> long winded way of doing it? Why can't you just query dba_extents and
> get the segment name ? v$session_wait would give the values of the
> file#, and block# in p1 and p2 and then you could go to dba_extents for
> the extent file_id, start block number and number of blocks for the
> extent.
>
> 5. Pg 321-322: talks about identifying 'sparse tables' which are tables
> defined with multiple freelists that have heavy inserts/deletes but the
> deletes are carried out in one process. It makes out that if one process
> frees lots of blocks, then they are put on a process freelist, not to be
> used by other sessions mapping to a different process freelist. I
> thought that if a process frees up space in a block that can be returned
> to a freelist, it goes to that processes Transaction freelist which are
> available for other processes ONCE comitted. Earlier on in the book, the
> algorithm for searching for free blocks is given, and mentions that the
> committed transaction free lists are searched if no blocks are found in
> the process freelist or master freelist. These blocks from the commited
> transaction freelists are then merged into the master freelist and then
> five blocks are put onto the process freelist. This would mean that once
> the transaction freelists are merged into the master freelist they are
> available to ALL processes. Is this not right ? Any ideas ???
>
> All help and ideas are greatly appreciated.
>
> Samantha
>
>
>
>
>
Received on Fri Jun 01 2001 - 11:49:16 CDT