Shared sql area v/s Private sql area

[Sonia]

Hi,
What is difference between shared sql area
and private sql area


Regards
sonia

[Sureshy]

shared area
Contains parse tree and execution path.

private area
contains session specific info such as bind variable, session parameters, runtime stacks.

Both are in library cache.

[pando]

If a session is connected via a dedicated server, private SQL areas are located in the user's PGA. However, if a session is connected via the multi-threaded server, the persistent areas and, for SELECT statements, the runtime areas, are kept in the SGA.

http://otn.oracle.com/docs/products/...memor.htm#8628

[quester]

Sureshy, I thk there are afew corrections to your explaination:

Shared SQL Area contains all the parsed and compiled recently executed SQLs, that are shared across the application.

Private SQL Area contains the Private SQLs (such as Explicit Cursor SQLs), which are "private" for each session.

The bind variables and arrays are NOT in the Private SQL Area, they are inside a buffer called "Stack Space". "Stack Space" is in PGA for a dedicated architecture and in SGA for a multi-threaded.

Again, Private SQL Area is in the PGA for a dedicated architecture and in SGA for a multi-threaded architecture.
Shared SQL Area is in the Library Cache.

QUESTER

[Edited by quester on 10-01-2002 at 05:25 AM]