shared versus dedicated oracle instance mode

[aistswc]

Good day,
When creating a database using Oracle's Configuration database agent, one option is using shared or dedicated mode. I am curious as to the implications of this selection; does dedicated mode cause a "single threading" effect on client's accessing the database?? I have noticed that all of our databases have been created in dedicated mode, and since we don't have a dba, no one is sure why.

thanx,
steve c.

 

[jimbopalmer]

If you have a 'small' number of users, giving each their own database connection is fastest. At some point sharing data base connections will free up RAM, which can reduce swapping or allow more SGA or some other need the database has for RAM.

So for 'large' numbers of users, shared connections free so much RAM they become a faster way to work. If RAM is infinite, we would always want Dedicated connections, on a 32 bit box I suspect somewhere near 350 users is a breakover.

I tried to remain child-like, all I acheived was childish.

 

[aistswc]

Ah,
From your description, it sounds like dedicated refers to memory mapping of client connections, not database-to-client threads. That makes more sense.

Thanx a bundle,
steve c.

 

[jimbopalmer]

Well each database to client connection consumes RAM, when you run out of RAM, multi-threading a single connection can make more sense than giving each client their own single threaded connection. You can mix and match if you have a group of rarely used clients who can share, plus some heavy (batch?) processes which should not share.

I tried to remain child-like, all I acheived was childish.