Oracle Share Pool and Large Pool

组成：
The main components of the shared pool are the library cache and the dictionary
cache.

	shared pool	library cache	An important purpose of the shared pool is to cache the executable versions of SQL and PL/SQL statements，Java classes
		dictionary cache.	includes usernames, segment information, profile data, tablespace information, and sequence numbers. The dictionary cache also stores descriptive information, or metadata, about schema objects. Oracle uses this metadata when parsing SQL cursors or during the compilation of PL/SQL programs.
	large pool	Parallel query	Parallel query uses shared pool memory to cache parallel execution message buffers
		Recovery Manager	Recovery Manager uses the shared pool to cache I/O buffers during backup and restore operations. For I/O server processes and backup and restore operations, Oracle allocates buffers that are a few hundred kilobytes in size.
		Shared server	In a shared server architecture, the session memory for each client process is included in the shared pool

share pool的重要性：（甚至比buffer cache都重要，应优先满足）
A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached.

各部分包含的内容：
An important purpose of the shared pool is to cache the executable versions of SQL and PL/SQL statements.

Cursor对性能的影响：（内存量和解析次数？）
The frequency with which your application either closes cursors or reuses existing cursors for new SQL statements affects the amount of memory used by a session and often the amount of parsing performed by that session.

• 监控：

查看share pool内存情况：

select * from V$SHARED_POOL_RESERVED;

select REQUEST_FAILURES,REQUEST_MISSES,FREE_MEMORY,SHARED_POOL_RESERVED_SIZE from V$SHARED_POOL_RESERVED; 
是否够用 ？
REQUEST failure 是否接近为零或者不增长。如果增长说明share pool太小，应在增加 shared_pool_size

查看LIBRARYCACHE 整体情况： V$LIBRARYCACHE view

The statistic that shows the amount of reloading (that is, reparsing) of a previously cached SQL statement that was aged out of the cache is the RELOADS column in the V$LIBRARYCACHE view.
1、RELOADS  值越大说明交换出去的次数越多，说明内存不足。 
理想情况，调整目标：In an application that reuses SQL effectively, on a system
with an optimal shared pool size, the RELOADS statistic will have a value near zero.
2、INVALIDATIONS ：should be near zero.
This means SQL statements that could have been shared were
invalidated by some operation (for example, a DDL). This statistic should be near
zero on OLTP systems during peak loads.

检查是否存在浪费：the amount of free memory in the shared pool
视图：V$SGASTAT,
调整目标：Optimally, free memory should be as low as possible, without causing any reloads on the system.

查看lib cache中的SQL内容：

select * from v$sqlarea;

查看 Shared Cursors（复用的SQL）情况： V$SQL_SHARED_CURSOR

重复使用cursor，之后要使用时，中间不要关闭。 
For SQL statements which are identical but are not being shared, you can query
V$SQL_SHARED_CURSOR to determine why the cursors are not shared.

large pool的来历
A number of features make large memory allocations in the shared pool: for
example, the shared server, parallel query, or Recovery Manager. Oracle
recommends segregating the SGA memory used by these features by configuring a
distinct memory area, called the large pool.