《Pro Oracle SQL》Charpter2 --2.5 SGA – The Buffer Cache

SGA – The Buffer Cache    SGA-缓冲区缓存   (page59)
    The buffer cache is one of the largest components of the SGA. It stores database blocks after they have been read from disk or before they are written to disk.  A block is the smallest unit that Oracle will work with.   Blocks contain rows of table data or index entries, and some blocks will contain temporary data for sorts.  The key thing to remember is that Oracle must read blocks in order to get to the rows of data needed to satisfy a SQL statement.   Blocks are typically either 4KB, 8KB, or 16KB in size, although the only restricting factor to the size of a block depends on your operating system.  
    缓冲区缓存是SGA中最大的组件之一。 它存储数据库块，在从磁盘读取(数据块)后或者在(数据块)写入磁盘前。块是Oracle处理(数据)的最小单位。 块包含表数据行或者索引入口，还有一些块会包含排序的临时数据。关键点要记住的是Oracle必须读块才能获得满足SQL语句所需的数据行。 块大小上通常 有4KB，8KB，16KB，但是仅有的限制块大小的因素取决于你的操作系统。
    Each block has a certain structure.  Within the block there are a few areas of block overhead that
contain information about the block itself that Oracle uses to manage the block.  There is information
that indicates the type of block it is (table, index, etc.), a bit of information about transactions against the block, the address where the block physically resides on the disk, information about the tables that store data in the block, and information about the row data contained in the block.  The rest of the block
contains the actual data or free space where new data can be stored.  There’s more detail about how the
buffer cache can be divided into multiple pools and have varying block sizes, but I’m going to keep it
simple for this discussion and just consider one big default buffer pool with a single block size.  
    每个块都有一定的结构。在块内部有少许块“自身开销”，包含Oracle用于管理块的自身的信息。这些信息指出块的类型(表，索引，等)，一些与块相关的事务信息，块驻留在磁盘上的物理地址，块中存储数据的表信息，块中包含的行数据信息。块的其他部分包含实际的数据或者是可用来存储新数据的自由空间。还有很多细节关于缓冲区缓存如何能被分割成多个池且能改变块的大小，但是为了讨论简便(这里)只考虑一个大的默认只有一单块的缓存池(的情况)。
    At any given time, the blocks in the buffer cache will either be dirty, which means they have been
modified and need to be written into a physical location on disk, or not dirty.  In the discussion on the
shared pool, I mentioned the Least Recently Used (LRU) algorithm employed by Oracle to manage the
information there. The buffer cache also uses a LRU list to help Oracle know which blocks are most
recently used in order to know how to make room for new blocks as needed.  Besides just the LRU list,
Oracle maintains a touch count for each block in the buffer cache.  This count indicates how frequently a
block is used; blocks with higher touch counts will remain in the cache longer than those with lower
touch counts.
    在任何给定的时间内，在缓冲区中的块要么是脏的，意味着他们将被修改且需要被写入磁盘的物理位置中，要么不是脏的。在讨论共享池时，我曾经提及Oracle运用Least Recently Used (LRU)算法管理那里的信息。缓冲区缓存也使用LRU列表帮助Oracle知道哪些块是最近使用的为了确定如何为新块腾出所需空间。除了LRU列表以外，Oracle还为在缓冲区缓存中的每个块维护了一个“触摸”计数器。该计数器指出一个块的使用频率；触摸计数值越高的块相比值低的块在缓存中保持的 (时间)要长。
    Also like in the shared pool, latches must be acquired to verify if blocks are in the buffer cache and
to update the LRU information and touch counts.  One of the ways you can help Oracle use less latches is
to write your SQL in such a way that it accesses the fewest blocks possible when trying to retrieve the
rows needed to satisfy your query.  I’ll discuss how you can do this throughout the rest of the book, but
for now, keep in mind that if all you think about when writing a SQL statement is getting the functionally
correct answer, you may write your SQL in such a way that it inefficiently access blocks and therefore
uses more latches than needed.  The more latches required, the more chance for contention and the
more likely your application will be less responsive and less scalable. 
    也如同共享池，确认块是否在缓冲区缓存中且更新LRU信息和触摸计数值时，闩必须获得。你能帮助Oracle使用更少的闩的方法之一是：书写SQL的方式，当试图检索满足你的查询所需行时，尽可能访问最少的块。 我将在书中的其他部分讨论你如何能做到这点，但是现在，记住如果你认为写SQL语句只求功能上获得正确的结果，可能你所写的SQL，低效率的访问块从而使用了更多不必要的闩。获取的闩越多，越有可能产生竞争且越有可能你的应用响应慢，扩展性差。
    Executing a query whose blocks are not in the buffer cache requires Oracle to make a call to the
operating system to retrieve those blocks and then place them in the buffer cache before returning the
result set to you.  In general, any block that contains rows that will be needed to satisfy a query must be
present in the buffer cache.  When Oracle determines that a block already exists in the buffer cache, such access is referred to as a  logical read.  If the block must be retrieved from disk, it is referred to as a physical read.   As you can imagine, since the block is already in memory, response times to complete a
logical read is faster than physical reads.  Listing 2-3 shows the differences between executing the same
statement multiple times under three scenarios.  First, the statement is executed after clearing both the
shared pool and the buffer cache.  This means that the statement will be hard parsed, and the blocks that contain the data to satisfy the query (and all the queries from the system objects to handle the hard
parse) will have to be physically read from disk.  The second example shows what happens if only the
buffer cache is cleared.  The final example shows what happens if both the shared pool and buffer cache
are populated. 
    执行一查询，而(它所需的)块没有在缓冲区缓存中就需要Oracle调用操作系统去检索这些块，然后在返回结果集给你之前将它们填入缓冲区缓存中。一般而言，任何包含满足查询所需行的块必须呈现在缓冲区缓存中。当Oracle判断一个块已经存在于缓冲区缓存中，这样的访问称之为逻辑读。如果块必须从磁盘上检索，它就被称之为物理读。 你可以想象，由于块已经在内存中，完成一次逻辑读的响应时间要快于物理读。列表2-3展示了在三种场景下执行相同的语句多次 它们之间的区别。首先，语句执行在清空共享池和缓冲区缓存之后。这意味着语句将是硬解析，且包含满足查询的数据的块(和所有从系统对象到处理硬解析的查 询)将必须从磁盘物理读。第二个例子展示如果仅仅是清空缓冲区缓存会发生什么。最后的例子展示如果共享池和缓冲区缓存都填充了会发生什么。

Listing 2-3. Hard Parsing and Physical Reads vs. Soft Parsing and Logical Reads     硬解析和物理读比较软解析和逻辑读
SQL> alter system set events 'immediate trace name flush_cache';
 
System altered.
 
SQL> alter system flush shared_pool;
 
System altered.
 
SQL> set autotrace traceonly statistics
SQL>
SQL> select * from employees where department_id = 60;
 
5 rows selected.

Statistics
----------------------------------------------------------
        951  recursive calls
          0  db block gets
        237   consistent gets    逻辑读
         27   physical reads      物理读
          0  redo size
       1386  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
          5  rows processed
 
SQL> set autotrace off
SQL>
SQL> alter system set events 'immediate trace name flush_cache';
 
System altered.

SQL> set autotrace traceonly statistics
SQL>
SQL> select * from employees where department_id = 60;
 
5 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4   consistent gets
          2   physical reads
          0  redo size
       1386  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed
 
SQL> select * from employees where department_id = 60;
 
5 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4   consistent gets
          0   physical reads
          0  redo size
       1386  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed
 
SQL> set autotrace off
    You can see from the statistics that when a query is executed and does only a soft parse and finds
the blocks in the buffer cache, the work done is at a minimum.  Your goal should always be to develop
code that will promote reusability in both the shared pool and buffer cache.
    你能从统计中看出，当执行一查询若只是进行软解析和在缓冲区缓存中查找块，做的工作最少。你的目标应该一定是：开发将能在共享池和缓冲区缓存中提升可重用性的代码。