Re: Block Locks

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.

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.

Sorry if I ranted to long.

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