Oracle体系结构及备份（六）sga-shared_pool

一 什么是shared_pool The shared pool portion of the SGA contains the library cache, the dictionary cache, the result cache, buffers for parallel execution messages, and control structures. This div includes the following topics: Library Ca

一 什么是shared_pool

The shared pool portion of the SGA contains the library cache, the dictionary cache, the result cache, buffers for parallel execution messages, and control structures.

This div includes the following topics:

Library Cache

The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.

Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle Database accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle Database.

The data dictionary is accessed so often by Oracle Database that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle Database user processes share these two caches for access to data dictionary information.

Result Cache

The result cache is composed of the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.

The DBMS_RESULT_CACHE package provides administration subprograms, which, for example, flush all cached results and turn result-caching on or off systemwide. The dynamic performance views V$RESULT_CACHE_* allow the developer and DBA to determine, for example, the cache-hit success for a certain SQL query or PL/SQL function.

Similar to the result cache, the client result cache also caches results, except that the caching is done on the client side.

用于存储：

最近执行的SQL语句

最近使用的数据定义

由三个与性能相关的部分组成

库缓存

数据字典缓存

结果缓存

由参数SHARED_POOL_SIZE决定大小。

二 操作示例

SQL> show parameter shared_pool_size;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size		     big integer 12M
SQL> show parameter sga_target;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
sga_target			     big integer 160M

三 总结

1. 用于存储：最近执行的SQL语句、最近使用的数据定义；

2.由三个与性能相关的部分组成：库缓存、数据字典缓存、结果缓存；

3.可以通过参数shared_pool_size查看共享池的大小。

我的邮箱：wgbno27@163.com  新浪微博：@Wentasy27
  微信公众平台：JustOracle（微信号：justoracle）
 IT交流群：336882565（加群时验证 From CSDN XXX）
 Oracle交流讨论组：https://groups.google.com/d/forum/justoracle
  By Larry Wen

	@Wentasy