See my comments in the text:
Robert Eskridge wrote:
> (20 days later -- occurrences aren't that common)
>
> Jared,
>
> Ok, I've re-RTFM'd the Concepts manual and have dog eared Steve's book
> unbelievably. I've used the event 10046 traces while the blocking
> lock event was going on. The curious thing is that I'm not seeing any
> buffer busy waits. The blocked process sits there and cranks out an
> equeue wait trace every 3 seconds and does not report a file, block
> or row that it is waiting on. If I'm reading Steve's book correctly,
> I should see file and block but no row if it's an initrans problem, so
> that's out.
>
I am assuming that the mode for the TX enqueue is 4. Right ?
>
> In Katz's article in the Feb 2002 elementK Journal he says that if all
> three are 0 then it's a problem with free space in the block and maybe
> pctfree is low. The table I'm inserting into has 2k blocks and
> PCTFREE=30. The insert is only populating 7 of the 185 columns,
> that's 6 varchar2's and a date. There should be plenty of room,
> unless as you pointed out that the problem was really not in this
> table -- possibly in the indexes. This guy does have 15 indexes (all
> single column, all PCTFREE=10). A curious thing is that when I look
> at the blocker, he shows that he's blocking on 2 tables, this one that
> he has a transaction open on, and a journaling table that triggers off
> the first insert into -- but his rows are only 70 bytes and he has
> PCTFREE=10 also.
>
Are the indexes UNIQUE ? If yes, what is the chance of people inserting into with the
same value ?
Are the indexes BITMAP ?
>
> So I've got 17 suspects, all with plausible alibis. And I haven't
> figured out how to get the enqueue wait to squeal on any of them. If
> I'm understanding the file=0 block=0 row=0 properly, he tried, he
> couldn't get a block so he waits 3 seconds and tries again
> indefinately. But when one of these happens, I see a rash of them so
> something's causing it.
>
Forget about the file, block, row values. They don't mean a thing if you are not
waiting
on TX mode 6.
>
> I keep returning to free lists. This is an evolved application (he
> says euphemistically) and it's had a freelist=1 on everything since it
> slithered out of the slime. Now it's running on a 4 cpu sparc. I'm
> wondering if the free list is just getting overran and if bumping it
> to the sometimes advised cpu*2 could clear the problem. I have
> lingering doubts because I haven't found anything that specifically
> says what I should be seeing when that happens.
>
The only freelist problem that you could have is transaction freelists. But you
are doing inserts so that is not the case here.
>
> So I guess I should try to make this into a question.... hmmn...
>
> Am I barking up the wrong tree?
>
The right tree, but the tree doesn't understand barking ;-)
Anjo.
>
> -rje
>
> > From: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
> > Date: Tuesday, February 05, 2002, 10:36:27 AM
> > Subject: testing freelists
> >
> > ===8<==============Original message text===============
> > 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: Anjo Kolk
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).
