ITPub博客

首页 > Linux操作系统 > Linux操作系统 > The Shared Pool(共享池)

The Shared Pool(共享池)

原创 Linux操作系统 作者:pingley 时间:2012-02-06 20:49:28 0 删除 编辑
The Shared Pool(共享池
共享池是SGA中最复杂的组件,可以划分为几十个子结构。
这里只关注几个重要的子结构。
■ The library cache
■ The data dictionary cache
■ The PL/SQL area
■ The SQL query and PL/SQL function result caches
All the structures within the shared pool are automatically managed.
共享池的大小是可以动态伸缩的,根据DBA的指令或者自动管理功能。

The Library Cache(库缓冲区)
The library cache is a memory area for storing recently executed code, 
in its parsed form.
解析就是转换用户编写的代码为可执行的格式,该过程是oracle根据需要自动执行的。
缓存解析过后的代码,可以提高性能,节省解析SQL语句的时间。解析一条SQL语句的
时间往往是执行一句SQL语句时间的数倍,因此函数缓冲区的目的是缓存解析过后的SQL
语句,以便马上执行。
The algorithm used to find SQL in the library cache is based on the ASCII values 
of the characters that make up the statement. The slightest difference (even something
as trivial as SELECT instead of select) means that the statement will not match but will
be parsed again.
库缓冲查找的原理。

The Data Dictionary Cache(数据字典缓冲区)
数据字典缓冲区有时也称为行缓冲区。
it stores recently used object definitions: descriptions of tables, indexes, users, and other 
metadata definitions.把这些对象的定义缓存在SGA中,以便他们能够马上被用户访问,而不是每个用户
都从磁盘中读取数据字典,极大的提高了性能。数据字典缓冲对象的定义,可以提高语句的解析效率。

Shared pool tuning is usually oriented toward making sure that the library cache is the right size. 
This is because the algorithms Oracle uses to allocate memory in the SGA are designed to favor the
dictionary cache, so if the library cache is correct, then the dictionary cache will already be correct.

The PL/SQL Area(PL/SQL区)
Stored PL/SQL objects are procedures, functions, packaged procedures and functions, object type definitions,
and triggers.These are all stored in the data dictionary, as source code and also in their compiled form。
这些对象需要从数据库字典中读取出来,为了不重复的从数据字典中读取,PL/SQL区会把这些PL/SQL对象缓存起来,以便提高
对这些对象的访问效率。

PL/SQL can be issued from user processes, rather than being stored in the data dictionary. This is called anonymous PL/SQL. Anonymous PL/SQL cannot be cached and reused but must compiled dynamically. It will therefore always perform worse than stored PL/SQL. Developers should be encouraged to convert all anonymous PL/SQL into stored PL/SQL.

The SQL Query and PL/SQL Function Result Cache(SQL和PL/SQL函数结果缓存区)
In many applications, the same query is executed many times, by either the same session or many different sessions.Creating a result cache lets the Oracle server store the results of such queries in memory. The next time the query is issued, rather than running the query the server can retrieve the cached result.
SQL和PL/SQL函数结果缓存区中的数据不会出现过时的错误,该区会智能的跟踪查询的表是否已经更新,如果更新基于该表的查询就无效了。当传递给PL/SQL函数的参数不同,或者查询的表已经变化的时候,PL/SQL函数会重新执行,产生新的结果。
By default, use of the SQL query and PL/SQL function result cache is disabled,The cache is within the shared pool:unlike the other memory areas described previously, it does afford the DBA some control: he/she can specify a maximum size. 如果启用该项特性(oracle11g的新特性)通常性能会戏剧性的提高。

共享池的大小的调整对性能的影响非常关键的,必须足够大以便缓存频繁执行的代码,频繁访问的对象的定义,如果共享池比较小就失去了缓存的意义,解析过后的语句,缓存的对象的定义需要为新的解析结果新的对象的定义让出出空间。缓存的命中率会降低。如果太大,将会增加查找池中解析的语句或者对象定义的时间。
Determining the optimal size is a matter for performance tuning, but it is probably safe to say that most databases will need a shared pool of severalhundred megabytes. Some applications will need one of more than a gigabyte, and very few will perform. adequately with less than a hundred megabytes.

注:Shared Pool最初被引入的目的,也就是它的本质功能在于实现共享。如果用户的系统代码是完全异构的(假设代码从不绑定变量,从不反复执行),那么就会发现,这时候Shared Pool完全就成了一个负担,它在徒劳无功地进行无谓的努力:保存代码、执行计划等待重用,并且客户端要不停的获取Latch,试图寻找共享代码,却始终一无所获。如果真的如此,那这是我们最不愿看到的情况,Shared Pool变得有害无益。当然这是极端的,可是在性能优化中我们发现,大多数性能低下的系统都存在这样的通病:代码极少共享,缺乏或从不实行变量绑定。优化这些系统的根本方法就是优化代码,使代码(在保证性能的前提下)可以充分共享,减少无谓的反复硬/软解析。
实际上,Oracle引入Shared Pool就是为了帮助我们实现代码的共享和重用。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26110315/viewspace-715717/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-02-06

  • 博文量
    169
  • 访问量
    741088