testing freelists

From: Robert Eskridge <bryny_at_dfweahs.net>
Date: Mon, 04 Feb 2002 20:14:05 -0800
Message-ID: <F001.00405B32.20020204201529@fatcity.com>

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_at_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: bryny_at_dfweahs.net

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: ListGuru_at_fatcity.com (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).

Received on Mon Feb 04 2002 - 22:14:05 CST