Re: Block Locks

In article <323042EF.440C_at_teldta.com> "Brian P. Mac Lean" <brian.maclean_at_teldta.com> writes:
>Ed Jennings wrote:
>>
>> If an application is using array processing with a large array
>> size ( > 2000); and it is doing inserts into a table: At what
>> point will Oracle escalate the row level locks to block locks
>> and/or table locks???
>>
>> Does anyone know if this formula is published?
>> --
>> ~~~~~~~~~~~~~~~~~~~~~~~~~
>> jennings_at_dca.net
>
>
>My understanding of Oracle is that a lock is never ever escalated, period.

Yes, although a Row Share Table can be upped to Row Exclusive Table Lock. The net effect going from allowing other transactions to lock the table in share mode to not allowing that. Since the above example is already RX, not a problem. Unless you are looking for transaction level read consistency, and have to muck with the default locking mechanism.

>
>As a side note. You are using a heck of a large array. I once thought
>bigger was better myself. I increased an array to 5000 from 1000 and
>things went to purgatory. I did some testing about a year ago with
>array sizes from 1 -> 5000. In a PRO*C SQL*Net TCP/IP client/server environment
>we found the best all around array size was 100. For local connections
>we continued to get better results up to around 1000. Because the same
>program(s) is run at multiple sites, some local, some client/server we
>picked 100 as a standard array size. The moral of the story is if the
>array is two large the program could be running longer than it might
>be at a lower size. If doesn't take that long to build a small program
>to test this stuff. I highly recommend that people test their unique
>configuration.

Absolutely agree with the last sentence.

>
>Sorry if I ranted to long.
>

Actual test results are never a rant!
SQLNET often surprises people with bottlenecks.

>
> \\|//
> (0-0)
> +-----oOO----(_)-----------+
> | Brian P. Mac Lean |
> | Database Analyst |
> | brian.maclean_at_teldta.com |
> | http://www.teldta.com |
> +-------------------oOO----+
> |__|__|
> || ||
> ooO Ooo

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O