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:
- 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 ?
- 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 ?
- 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 ?
- 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.
- 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 Thu May 31 2001 - 23:56:29 CDT
