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...
Thomas P S
Any comments on my doubt..
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