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).
