Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ORACLE record locking acting like page locking

Re: ORACLE record locking acting like page locking

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: 1997/02/15
Message-ID: <166pqCAyScBzEw43@jimsmith.demon.co.uk>#1/1

In article <855685281.10315_at_dejanews.com>, rclewell_at_ariessys.com writes
>ORACLE record level locking? When I try to update an EXPIRATIONDATE
>field in approximately 23,0000 records with SQL*Plus, my DLL, which uses
>the Oracle Objects for OLE (OO402 Class Library) has to wait a long time
>(normally less than 3 seconds becomes about 1.5 minutes) to create a
>dynaset.
>
>The dynaset is just a simple SELECT statement (no update) with flags
>ODYNASET_READONLY | ODYNASET_NOCACHE.
>
>
>It's as if Oracle is doing page or block level locking. From what I've
>read, I know this is not the case, so I wonder what's going on.
>
>I am running Oracle Workgroup Server 7.3.2.1.1 on Windows NT. We have a
>simple database for our Website that just verifies a
>username/password/access rights/expiration dates.
>
>My SQL*PLUS statments are:
>SET AUTOCOMMIT ON;
>UPDATE UDBA SET EXPIRATIONDATE='19970331235959'
>WHERE EXPIRATIONDATETIME='19970228235959';
>
>Since the SQL commit is immediate I would expect the performance of my
>DLL dynaset creation to change very little. Instead it can take a user
>1.5 minutes to log in!
>
>Your thoughts and comments would be greatly appreciated.

What evidence do you have that this is a locking problem? It sounds to me as the server is busy.

Have you looked at either System stats (CPU usage, IO rates) or database stats while this is going on?

How long does the update statement take? How many records are there in the table altogether and is the expirationdate indexed? If the update is taking a long time, then you should look into tuning it.

I don't understand your comment about the immediate commit. AUTOCOMMIT executes a commit after each statement and should have no effect on performance of either the current session or anybody elses.

-- 
Jim Smith
Received on Sat Feb 15 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US