(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.
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.
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.
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.
So I guess I should try to make this into a question.... hmmn...
Am I barking up the wrong tree?
-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).
