《Pro Oracle SQL》Chapter2-2.2 SGA – The Shared Pool

2.2 SGA – The Shared Pool  共享池 (page 53)
    The shared pool is one of the most critical memory components particularly when it comes to how SQL executes. The way you write SQL doesn’t just effect the individual SQL statement itself.  The combination of all SQL that executes against the database has a tremendous effect on overall performance and scalability due to how it affects the shared pool.  
    共享池是最重要的内存组件，特别是它关乎SQL如何执行。你书写SQL的方式不仅只是影响SQL语句本身。在数据库上运行的所有SQL的组合(整体)极大的影响全局性能和扩展性，由于它极大的影响共享池。
    The shared pool is where Oracle caches program data.  Every SQL statement executed will have its
parsed form stored in the shared pool.  The area within the shared pool where statements are stored is called the library cache.  Even before any statement is parsed, Oracle will check the library cache to see if that same statement already exists there.  If it does exist, then Oracle will retrieve and use the cached information instead of going through all the work to parse the same statement again. The same thing goes for any PL/SQL code you run.  The really nifty part is that no matter how many users may want to execute the same SQL statement, Oracle will typically only parse that statement once and share it among all users who want to use it. Maybe you can see where the shared pool gets its name.
    共享池是Oracle缓存程序数据的地方。每条执行过的SQL语句将会把它的解析形式存储在共享池中。在共享池中存放语句的区域称之为库缓存。 只要之前有解析语句，Oracle将检查库缓存，看是否有相同的语句已经存在。如果存在，Oracle将检索和使用缓存中的信息，而不是再做一遍相同语句的解析工作。对于你运行的任何PL/SQL也是一样的(原理)。真正漂亮的 部分是：不论执行相同SQL语句的用户有多少，Oracle将经典的只解析那条语句一次再把它共享给所有要使用它的用户。你可能能明白“共享池"名字的含义了吧。
    SQL statements you write aren’t the only things stored in the shared pool.  The system parameters
Oracle uses will be stored in the shared pool as well.  In an area called the dictionary cache, Oracle will also store information about all the database objects.  In general, Oracle stores pretty much everything you could think of in the shared pool.  As you can imagine, that makes the shared pool a very busy and important memory component. 
    你所写的SQL语句不是存放在共享池中的唯一东西。Oracle使用的系统参数也会存放在共享池中。在(共享池中)一处被称之为字典缓存的区域，Oracle也将存储所有数据库对象的信息(在其中)。 一般而言，Oracle把相当多的，你该能想到的，所有东西存入共享池中。可以想象，这使得共享池成为非常忙且重要的内存组件。
    Since the memory area allocated to the shared pool is finite, statements that originally get loaded
may not stay there for very long as new statements are executed.  A Least Recently Used (LRU) algorithm regulates how objects in the shared pool are managed.   To borrow an accounting term, it’s similar to a FIFO (First In First Out) system. The basic idea is that statements that are used most frequently and most currently are what are retained.  Unlike a straight FIFO method, how frequently the same statements are used will effect how long they remain in the shared pool.  If you execute a SELECT statement at 8 A.M.and then execute the same statement again at 4 P.M., the parsed version that was stored in the shared pool at 8 A.M. may not still be there.  Depending on the overall size of the shared pool and how much activity it has between 8 A.M. and 4 P.M., as Oracle needs space to store the latest information throughout the day, it will simply reuse older areas and overlay newer information into them.  But, if you execute a statement every few seconds throughout the day, the frequent reuse will cause Oracle to retain that information over something else that may have originally been stored later than your statement but hasn’t been executed frequently, or at all, since it was loaded.
    由于分配给共享池的内存区是有限的，最初装载的语句可能不会保持在其中，因为新的语句要执行。有一个“最近最少使用”算法控制着如何管理共享池中的对象。 借用一会计术语，它类似于FIFO(先进先出)系统。基本的思想是：保持使用的最多，最近的语句。不同于直接的FIFO方法，同一语句的使用频率将影响到它在共享池中的保持时间。如果你在早上8点执行一SELECT语句，然后在下午4点执行相同的SQL语句，早上8点解析的(SQL语句)版本可能不会存在了。依赖于共享池的整体大小和早上8点到下午4点期间的活动数量，Oracle需要空间存储一天中的最近的信息，它将简单的重用原有的区域和覆盖新信息于其上。但是，如果你在一天中每隔几秒就执行一次(相同的)语句，反复的重用将使得Oracle保持那信息，相比其它那些在你的语句之后(执行)最初被存储但是至装载后，不经常或全然不执行的语句而言。
    One of the things you need to keep in mind as you write SQL is that in order to use the shared pool
most efficiently, statements need to be shareable.  If every statement you write is unique, you basically defeat the purpose of the shared pool.  The less shareable it is, the more effect you’ll see to overall response times.  I’ll show you exactly how expensive parsing can be in the next section.  
    对于书写SQL而言，你需要记住的一件事是：为了最有效的使用共享池，语句必须是可共享的。 如果你所写的每条语句都是唯一的，你根本性的违背了共享池的(设计)目的。语句的共享性越差，越会影响到你所见的全局响应时间。下一节我将为你准确的展现解析操作如何是这般“昂贵”。