Oracle 的SQL解析過程，先會在共享區內查找是否執行過此條SQL，找到了則直接從共享區域內直接返回，如果沒有查找到，那么才會去執行查找匹配。
通過oracle的架構圖里的查詢，可以知道，shared sql area是在shared pool里，PRivate私有sql area是在PGA里，主要是cursor[游標]之類的定義。
Shared SQL area : Shared SQL Area 包含了SQL的parse tree 和 execution plan官方文檔描述: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中的綁定變量的信息還有一些運行時的內存結構;一個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 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 areaEG:
27:Which two statements are true about Shared SQLArea and Private SQLArea? (Choose two.) 選項 A.Shared SQLArea will be allocated in the shared pool. B.Shared SQLArea will be allocated when a session starts. C.Shared SQLArea will be allocated in the large pool always. D.The whole of Private SQLArea will be allocated in the Program Global Area (PGA) always. E.Shared SQLArea and Private SQLArea will be allocated in the PGA or large pool. F.The number of Private SQLArea allocations is dependent on the OPEN_CURSORS parameter.
解析： B選項， shared sql area里的sql 如果空間充裕的話，一直是會在shared pool里的，所以說它和session是沒有關系的。 C選項，必定是在shared pool里。 ！！！D和E選項，這個是選項是最難的，私有的區域應該只出現在PGA和SGA中，
Private SQL area: Private SQL area包含SQL中的綁定變量的信息還有一些運行時的內存結構;一個Shared SQL area 能與多個Private SQL area做關聯; Private SQL area 存在的地方取決于數據庫的鏈接方式如果是使用獨立服務器(Dedicated Server)則把Private SQL area
所以還是要看服務器的模式。
新聞熱點
疑難解答
