Comments in-line.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley
Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
Samantha Hall wrote in message <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 ?
>
Correct -
>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 ?
>
I think there is room for variation here - I have seen the hwm climbing
one block at a time (in the early life of a table) and 5 blocks at a time.
Also OPS allows other alternatives, and there is/was a hidden parameter
(_bump_highwater_mark) that can be used to adjust this.
>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 ?
>
1) 'free list' wait are indeed waits for 'free list group' blocks.
2) 'free list' contention shows up as 'segment header' waits
if there are no freelist groups, as all the free lists are
in the segment header. The requirement for extra
free lists may be indicated by 'data block' waits.
>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.
>
Possibly the reference to 'tablespace' is a typing error. Surely the
block dump is done by file number and block number anyway.
Yes, it would be easier in some respects to use the scan of
dba_extents direct, but in a large system, and with LMTs you
may find that this would have a significant impact on the rest
of the system, and since the book is about tuning, that may
have been the author's over-riding consideration.
>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 ???
Blocks are put on a transaction free list as they become free.
They become available for moving to segment free lists when
required only after the commit. Yes, when there is no alternative,
Transaction freelists will (at least in 8.1.7) be transferred to the
'master free list', and then 5 blocks moved to the segment
free list that caused the demand. The problem with sparse
tables, however, appears most strongly when you have multiple
freelist groups. Blocks put on to a free list in one freelist group
may not migrate to a freelist in another free list group. This
makes it possible for certain patterns of activity to leave the
table badly populated - especially if you have set up multiple
freelist groups to help deal with OPS issues.
Received on Thu May 31 2001 - 15:03:09 CDT
