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

刺骨的言语ヽ痛彻心扉 2022-01-27 20:07 206阅读 0赞

### 一 什么是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 section 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

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a> &nbsp; <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"> <img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>