Shared SQL Areas and Private SQL Areas:

Topic: SGA=> Shared Pool => Library Cache


Shared SQL Areas and Private SQL Areas:

Oracle represents each SQL statement it executes with a shared SQL area and a private SQL area. Oracle recognizes when two users are executing the same SQL statement and reuses the shared SQL area for those users. However, each user must have a separate copy of the statement’s private SQL area.

[See: Oracle documet part number a67781.pdf - Oracle 8i Concepts, Page : 186)

Suppose same SQL statement using by 5 users (Cache hit). So by the last statement "However, each user must have a separate copy of the statement’s private SQL area." , there must be 5 SQL statements in 5 different private SQl Area and one in Shared Library Cache... So total Six SQl statements in the memory..? is it true...? Please somebody explain...

Thanks
Thomas P S

Hi friends...

Any comments on my doubt..

Thanks

Thomas

The "private" area is where the bind variables will get stored for each user. The SQL is in the shared pool once and there are 5 different copies of the different bind varaibles.

same book, just after what you copied, go and look for the definitions of Shared SQL Area and Private SQL Area, it's different : the shared one contains the parse tree, the execution plan for the query. private area just contains data relative to the user and to *his* execution of the query.
when 5 users do the same query, execution plan and so on are in the shared area, and each user has a private area with data concerning his own usage of the query

Thanks Pipo and Marist

Thomas