Re: How is enqueue_resources influencing memory usage / server performance?

An enqueue resource is a memory structure representing something that is lockable, such as a table (resrouce of type TM), a transaction slot (resource of type TX), a parallel execution slave (resource of type PS) and so on.

Many locks (technically enqueues) can hang off a single resource. If you have one hundred sessions updating a table, then there would be one enqueue resource needed by each session of type TX, but only one enqueue resource of type TM needed for the table, with one hundred enqueues hanging off it.

The resource itself takes only a small amount of memory - in the order of a few dozen bytes. I think that there are then other object types whose number increase with the enqueue resources (the array of enqueues is an obvious guess) but again we are talking about a few dozen to a couple of hundred bytes for each individual item. Given typical systems operate in the hundreds of megabyte region, the extra memory you would use by doubling or trebling your enqueue resources is probably insignificant.

As a rough guideline, you probably need something in the region of :

    number of files +
    number of PX slaves +
    number of expected concurrent transactions +     2 x number of concurrently locked objects

(Remember that each partition or subpartition of a partitioned table can be locked separately, so needs its own enqueue resource - trying creating an index on a partitioned table and you will an enqueue resource demanded for every partition in the table at once, and (I think) an enqueue resource corresponding to every partition of the index that is going to exist. Parallel execution, especially on partitioned objects, and most especially parallel DML, can chew up enqueue resources very quickly)

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG -  v$ and x$
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Patrick" <newsspam_at_vanzweden.nl.eu.org> wrote in message
news:5f5eddec.0401282302.1e93efdc_at_posting.google.com...


> Hi all,

>

> Probably as simple question for most of you.

> I'm using a oracle db server and had the problem of not having enough

> enqueue resources avavailable. The proces i'm starting is running a

> lot of concurrent queries so that's probably the cause of it.

>

> Systems administration increased the value but they want to know how

> this influences the performance and memory usage of the server. Does

> it increase memory usage a lot or doesn't it affect it at all ?

> Also do you get degraded server performance due to this modification

> (increasing the enqueue_resources value in ora.init)?

>

> Greetings,

>

> Patrick