Re: update takes table level lock

In article <a1mtkh$r1i18_at_kcweb01.netnews.att.com>, "S.A." says...
>
>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.
>

the individual rows are locked, the TM ? (DML Enqueue) Lock:

These locks are used to protect a table from being altered while you are in the middle of modifying the contents of it. For example, if you have updated a table, you will acquire a TM lock on that table. This will prevent someone else from DROPping that table, or ALTERing it in some fashion. If they attempt to perform certain DDL on the table while you have a TM lock on it, they will receive the following:

drop table dept

           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

which is a confusing message at first, since there is no method to specify NOWAIT or WAIT on a drop table at all. It is just the generic message you get when you attempt to perform an operation that would be blocked but the operation does not permit blocking. It is the same message you would get if you SELECTed from the table with the FOR UPDATE NOWAIT clause, and hit a locked row. .....

during a large update like that, you would expect lots of log writing as the redo log buffer is flushed frequently. You may be experiencing contention on your log devices if you have very high waits on these or your log buffer might be sized a tad too small. something to look into.

>thanks for any help.
>
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp