update takes table level lock

From: S.A. <a_at_a.com>
Date: Fri, 11 Jan 2002 10:04:01 -0500
Message-ID: <a1mtkh$r1i18@kcweb01.netnews.att.com>

Friends,

We running 8.1.7.0 on sun E-10000.

An update statement takes about 7 minutes to update 6000 rows in a 50,000 rows table.
The statement is:

UPDATE <table_name> SET amount=:V001
WHERE col1=:V002 AND col2=:V003 AND col3=:V004 AND col4=:V005 AND col5=:V 006 AND col6=:V007 AND col7=:V008 AND col8=:V009 AND col9=:V010 AND col10=:V011 AND col11=:V012 AND col12=:V013 AND col13=:V014 AND

col14=:V015 AND col15=:V016 ;

I see following during the update activity:

SELECT object_name, object_type, v$lock.type FROM dba_objects, v$lock
WHERE object_id = v$lock.id1

OBJECT_NAME            OBJECT_TYPE            TY
----------------------       ------------                    --
table_name                       TABLE                        TM

I saw 'TM' - table level lock throughout the update activity. Does it mean that all 6000 updates are done serially? Is there anyway I can force a row level lock?

The statspack report also shows high log file sync and log file parallel write waits.

thanks for any help. Received on Fri Jan 11 2002 - 09:04:01 CST