Unfortunately, the table lock really won't help. Given that there are
only two users on the system, the use of a table lock will do nothing except
cause problems for other users should they try to access data. How are you
performing the data load? If by PL/SQL, you may need to rethink that
approach. I like Tom's idea of reviewing the process before determining
what the mechanics should be.

Thank You

Stephen P. Karniotis
Compuware Corporation
Direct: (313) 227-4350
Toll Free: (800) 462-7740 ext. 74350
Mobile: (248) 408-2918
Email: Stephen.Karniotis_at_Compuware.com
Web: www.compuware.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org On
Behalf Of Mercadante, Thomas F
Sent: Tuesday, June 08, 2004 2:49 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: can table locking improve performance ??


My guess is that you will ether see no improvment at all, or it will get

Oracle will issue it's own locks - your placing a lock on a row may actually
slow it down. I can't see it getting any better. As you said, you will be
the only person on the database at the time - you will be contending with
anyone else trying to lock either the table or a row in the table.

I say leave it alone. Don't issue any locks at all - let Oracle take care
of them.

To speed up the process, you may need to re-engineer how you are doing it.
Are you performing a straight sqlldr load, CTAS, procedural

Tom Mercadante
Oracle Certified Professional
-----Original Message-----
From: babette.turnerunderwood_at_sdc-dsc.gc.ca

Sent: Tuesday, June 08, 2004 2:42 PM
To: oracle-l_at_freelists.org
Subject: can table locking improve performance ??

We have an initial load process that is very long running.
The update of 38M rows takes hours and hours.
There are two users on the entire system, us doing the
load and another user to look at some of the v$ views
during the load process.
Updates need to put exclusive row-level locks before
being able to update rows. If we do a table level lock
PRIOR to the update statement, what effect will it
have on performance. Intuitively, we think it should be
more efficient, but I don't know if Oracle will be more
efficient in checking locks when updating rows
or does the exact same source code apply, regardless
if there is a table level lock.
In other words, in this case, can a table level lock
improve performance ?
- thanks

The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Search Discussions

Discussion Posts


Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 3 | next ›
Discussion Overview
grouporacle-l @
postedJun 8, '04 at 1:39p
activeJun 8, '04 at 2:19p



site design / logo © 2022 Grokbase