Shared SQL area && Private SQL area

Shared SQL area : Shared SQL Area 包含了SQL的parse tree 和 execution plan，当有多个用户同时访问同一个应用，oracle使用shared sql area为多次执行的sql来保留内存。

 当一个sql完成解析，当在shared sql area中存储时，oracle在shared pool中为其分配内存，这片内存区域的大小取决于sql的复杂程度。如果shared pool空间都被用尽，oracle使用lru算法释放已经分配的shared sql area为新的解析过的sql分配shared sql area。如果释放了一块shared sql area，那么与之相关联的sql语句必须被重新parse,并分配一块新的shared sql area才可执行。

官方文档描述:

A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.

Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement’s shared SQL area. If Oracle deallocates a shared SQL area, the associated SQL statement must be reparsed and reassigned to another shared SQL area at its next execution.

Private SQL area: Private SQL area包含SQL中的绑定变量的信息还有一些运行时的内存结构;

每个发出sql的session都有一个private sql area。每个使用同样sql的 session都有其自己私有的private sql area和使用同一个shared sql area.

因此，一个Shared SQL area 能与多个Private SQL area做关联;

Private SQL area 存在的地方取决于数据库的链接方式如果是使用独立服务器(Dedicated Server)，则把Private SQL area

存放在PGA中, 如果使用共享服务器(Shared Server)则保持在SGA中。

官方文档描述:

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area.

Shared SQL area 和 Private SQL area的区别

oracle通过shared sql area和private sql area 来发布运行一条sql。当两个用户的sql一致时，oracle通过shared sql area来为他们重用sql的执行计划。

但是，每个用户都必须有个在private sql area的独立拷贝。

Oracle represents each SQL statement it runs 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.