From oracle-l-bounce@freelists.org Tue Jun 8 14:19:49 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i58JJO521244 for ; Tue, 8 Jun 2004 14:19:34 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i58JJ2621154 for ; Tue, 8 Jun 2004 14:19:13 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E2FE872CF23; Tue, 8 Jun 2004 14:04:14 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29389-11; Tue, 8 Jun 2004 14:04:14 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2F3D372CF74; Tue, 8 Jun 2004 14:03:36 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 08 Jun 2004 14:01:51 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B088272CEAD for ; Tue, 8 Jun 2004 14:01:50 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28405-61 for ; Tue, 8 Jun 2004 14:01:50 -0500 (EST) Received: from mx1.compuware.com (mx1.compuware.com [63.115.132.34]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CEED172CE9F for ; Tue, 8 Jun 2004 14:01:49 -0500 (EST) Received: from bh1.compuware.com (bh1 [10.10.1.31]) by mx1.compuware.com (Postfix) with ESMTP id 243C2536B3 for ; Tue, 8 Jun 2004 15:21:03 -0400 (EDT) Received: by compuware.com with Internet Mail Service (5.5.2657.72) id ; Tue, 8 Jun 2004 15:21:00 -0400 Message-ID: <8D81DA9D264CD311A60C0008C75D553B1775B03A@cwus-dtw-pri02.compuware.com> From: "Karniotis, Stephen" To: "'oracle-l@freelists.org'" Subject: RE: can table locking improve performance ?? Date: Tue, 8 Jun 2004 15:20:58 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C44D8D.7BE02592" X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2278 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Stephen.Karniotis@compuware.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org ------_=_NextPart_001_01C44D8D.7BE02592 Content-Type: text/plain; charset="iso-8859-1" Babette: 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@Compuware.com Web: www.compuware.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Mercadante, Thomas F Sent: Tuesday, June 08, 2004 2:49 PM To: 'oracle-l@freelists.org' Subject: RE: can table locking improve performance ?? Babette, My guess is that you will ether see no improvment at all, or it will get worse. 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 loop/insert/update? Tom Mercadante Oracle Certified Professional -----Original Message----- From: babette.turnerunderwood@sdc-dsc.gc.ca [mailto:babette.turnerunderwood@sdc-dsc.gc.ca] Sent: Tuesday, June 08, 2004 2:42 PM To: oracle-l@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 Babette 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. ------_=_NextPart_001_01C44D8D.7BE02592 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message

Babette:

 

=

   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:       &nbs= p;  (313) 227-4350

Toll Free:  (800) 462-7740 ext. = 74350

Mobile:       &nbs= p; (248) 408-2918

Email: Stephen.Karniotis@Compuware.com

Web:  www.compuware.com

 

=

-----Original Message-----
From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org]On Behalf Of Mercadante, Thomas = F
Sent: Tuesday, June 08, = 2004 2:49 PM
To: = 'oracle-l@freelists.org'
Subject: RE: can table = locking improve performance ??

 

Babette,<= /font>=

 =

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

 =

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 = loop/insert/update?=

 =

Tom Mercadante
Oracle Certified = Professional =

-----Original Message-----
From: babette.turnerunderwood@sdc-dsc.gc.ca = [mailto:babette.turnerunderwood@sdc-dsc.gc.ca]
Sent: Tuesday, June 08, = 2004 2:42 PM
To: = oracle-l@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
Babette




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.


------_=_NextPart_001_01C44D8D.7BE02592-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------