Robert,
Simulating the problem and proving the correction is an excellent idea.
One thing to consider when putting together your testing scenario is
that writes to the database block by a transaction are done in memory,
that is they are made to the cached database blocks.
An internal locking mechanism ( a latch ) is used to control access to
the cache, and it is normally held for only a short time.
The data may be written to the disk before a commit, and it may not
be written to disk until after a commit. ( referring to datafiles only
here, not the redo log ).
The 60 second sleep in your script is only simulating user think time,
it's not actually blocking anything. You will need to similate several
session simultaneously inserting, and you will need to know if the
contention
is in the table or in an index: I'm guessing it's an index or indexes.
Some time spent with the Concepts manual would help you out here.
Here's an excerpt from the section on database writer:
Database Writer (DBWn)
The database writer writes modified blocks from the database buffer
cache to the datafiles. Although one database writer process (DBW0)
is sufficient for most systems, you can configure additional processes
(DBW1 through DBW9) to improve write performance for a system that
modifies data heavily. The initialization parameter DB_WRITER_PROCESSES
specifies the number of DBWn processes.
Since Oracle uses write-ahead logging, DBWn does not need to write
blocks when a transaction commits. Instead, DBWn is designed to perform
batched
writes with high efficiency. In the most common case, DBWn writes only
when more
data needs to be read into the system global area and too few database
buffers are free. The least recently used data is written to the datafiles
first. DBWn
also performs writes for other functions such as checkpointing.
Take a look at Chapter 15 on Transactions as well.
Also check out Steve Adams' web site, www.ixora.com.au. Lots of
interesting stuff
there if you want to learn about the internals. Here's an excerpt from
the section
on Free lists:
As mentioned previously, free list contention occurs when multiple
processes using the same free list attempt to modify the data block on the
head of the free list concurrently. It is shown in V$WAITSTAT against the data block
class. V$WAITSTAT can also show contention for the segment header and free list
blocks. This occurs where multiple transaction in the same free list
group need to update their free list header records simultaneously. There
are various ways of addressing these problems such as rebuilding the table
with more free list groups, or increasing _bump_highwater_mark_count, or the novel
idea of fixing the application.
To drill down on which segments are causing data block contention, I suggested using
event 10046, level 8. This creates a trace
file much like to one produced by the sql_trace facility, except that for each event
wait a line is printed to the trace
file. In particular, each buffer busy wait is recorded together with the
P1 and P2 values which are the data file and block number of the wait. So
to find which blocks a process has been waiting on, you just grep the trace file for
buffer busy waits lines and produce a histogram of the file and block numbers most
commonly
waited for. Once you have suspect file and block numbers, you can relate
them to a segment by querying DBA_EXTENTS. In the case of free list
contention on a table it is common to have several hot blocks just below
the high water mark for the segment.
If you really want to learn the internals, his book is excellent for that.
It's not normally necessary
IMO to delve that deep into the internals to deal with tuning problems, at
least in my experience.
It will certainly help you develop insight and intuition as to what is
going on with your database though.
HTH
Jared
Robert Eskridge <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/04/02 08:15 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: testing freelists
I've got a database that I'm experiencing blocking locks on insert
statements into the largest, most active transaction table. The
freelists currently=1 and it's on a 4 CPU Sparc under 8.0.5 in a 24/7
environment.
I think this points to freelists needing to be increased. The "powers
that be" want a guarantee before they give me a maintenance window so I
can go through the rebuild on this table to change the freelists.
(We've got an 8.1.7 conversion project going but this can't wait.)
So I'm trying to put together a test set to prove that the freelist
increase will help. What I've been trying has two parts. A simple
sql script like:
$cat blocktest.sql
insert into block_test values
('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
host sleep 60
commit;
exit;
And a shell script to run it.
$ cat block.sh
itr=1
echo $itr
while :
do
sqlplus me/mypasswd@sid @blocktest &
itr=`expr $itr + 1 `
echo $itr
if [ $itr -eq $1 ]
then
break
fi
done
I've run starting up to the max processes allowed by the database, and
still don't get the blocking lock on the database. If I can't get
blocking locks to appear in a test situation, then I can't prove that
increasing the freelists helps the situation.
Any suggestions?
-rje
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robert Eskridge
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
