Oracle 1Z031学习笔记

Oracle 1Z031的学习已经拖了太长时间，除了自己太忙，其中一个关键问题是学习不够系统连贯。现在强制规定12天内每天写一章的学习笔记。

今天第一章 Oracle achitechtural components

user process -> server process(PGA) -> INSTANCE { SGA [ shared pool(library cache,data dict cache),database buffer cache,redolog buffer cache], processes [PMON,SMON,DBWR,LGWR,CKPT... ...]} -> DATABASE {data files,control files,redo log files, }(parameter files,password files,archive log files)。

因为贴图不方便只能用这样的一个简单模式描述Oracle主要的components

这其中有几个重点地方需要注意。

1、PGA在dedicated 模式和shared server模式下是不一样的。在dedicated模式下PGA的内容如下 stack space, session information,sort area,curssor information.而在shared server模式下session information是放在SGA中的。

2、几个重要的概念，instance 实例，包括memory structure和 backgroud structure。database 数据库，不过要注意这里Oracle的数据库特指数据文件或者说是物理结构。包括三类文件control files,data files,redo log files。还有2个重要的文件，但不是database的组成部分，它们是parameter files和archive log files。

3、memory structure 包括 SGA,PGA ， SGA由shared pool, database buffer cache, redo log buffer cache和一些其他结构如 lock latch management，和statistical data 组成，通常还有其他2个pool,large pool和java pool.

4、SGA 设置

   SGA是由几个parameter决定其大小的，对SGA大小影响最大的几个parameter如下：

   DB_CACHE_SIZE:标准block的cache 的size.

   LOG_BUFFER：The number of bytes allocated for the redo log buffer cache.

   SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL.

   LARGE_POOL_SIZE: The size of the large pool; the default is zero.

   SGA 是通过SGA_MAX_SIZE来动态设置的。

   Unit of Allocation
   A granule is a unit of contiguous virtual memory allocation. The size of a granule depends on the estimated total SGA size whose calculation is based on the value of the parameter SGA_MAX_SIZE.
         – 4 MB if estimated SGA size is < 128 MB
         – 16 MB otherwise
The components (buffer cache, shared pool, and large pool) are allowed to grow and shrink based on granule boundaries. For each component which owns granules, the number of granules allocated to the component, any pending operations against the component (e.g.,
allocation of granules via ALTER SYSTEM, freeing of granules via ALTER SYSTEM, corresponding self-tuning), and target size in granules will be tracked and displayed by the V$BUFFER_POOL view. At instance startup, the Oracle server allocates granule entries, one
for each granule to support SGA_MAX_SIZE bytes of address space. As startup continues, each component acquires as many granules as it requires. The minimum SGA configuration is three granules (one granule for fixed SGA (includes redo buffers; one granule for buffer cache; one granule for shared pool).

通过这段可以知道oracle是如何分配SGA中各组件的空间。是通过granule来控制的，每个组件的大小是整数个的granule。而granule的大小是受制于SGA_MAX_SIZE。

 

5、shared pool

  shared pool 包括2个组件，library cache 和 data dictionary cache，它的大小是由SHARED_POOL_SIZE参数来控制的。

  比如：ALTER system set shared_pool_size = 64M;

 Sizing the Shared Pool
Since the shared pool is used for objects that can be shared globally, such as reusable SQL execution plans; PL/SQL packages, procedures, and functions; and cursor information, it must be sized to accommodate the needs of both the fixed and variable areas. Memory
allocation for the shared pool is determined by the SHARED_POOL_SIZE initialization parameter. It can be dynamically resized using ALTER SYSTEM SET. After performance analysis, this can be adjusted but the total SGA size cannot exceed SGA_MAX_SIZE.

 

Library Cache
The library cache stores information about the most recently used SQL and PL/SQL statements.

The library cache:
• Enables the sharing of commonly used statements
• Is managed by a least recently used (LRU) algorithm
• Consists of two structures:
– Shared SQL area
– Shared PL/SQL area
• Has its size determined by the shared pool sizing

 

Data Dictionary Cache
The data dictionary cache is a collection of the most
recently used definitions in the database.
• It includes information about database files,
tables, indexes, columns, users, privileges, and
other database objects.
• During the parse phase, the server process looks
at the data dictionary for information to resolve
object names and validate access.
• Caching the data dictionary information into
memory improves response time on queries.
• Size is determined by the shared pool sizing.

 

6、Database Buffer Cache

The database buffer cache stores copies of data
blocks that have been retrieved from the data files.
• It enables great performance gains when you
obtain and update data.
• It is managed through a least recently used (LRU)
algorithm.
• DB_BLOCK_SIZE
determines the
primary block size.

 

database buffer cache中每个buffer的大小等于ORACLE中block的大小。由DB_BLOCK_SIZE参数决定的。

 

DB_CACHE_SIZE: Sizes the default buffer cache size only, it always exists and cannot be
set to zero.
DB_KEEP_CACHE_SIZE: Sizes the keep buffer cache, which is used to retain blocks in memory that are likely to be reused.
DB_RECYCLE_CACHE_SIZE: Sizes the recycle buffer cache, which is used to eliminate blocks from memory that have little change of being reused.

database buffer cache 可以动态的通过

     alter system set db_cache_size = 96M

来调整。

Buffer Cache Advisory Parameter 通过统计数据给预定的特性不同的cache size，可以帮助DBA来调整cache的大小。可以通过V$DB_CACHE_ADVICE视图来查看。

通过alter system set db_cache_advice = (OFF,ON,READY)来关闭，开启，READY主要是在on,off之间切换的时候预分配存储空间从而避免ORA-4031错误。

 

7、Redo Log Buffer Cache

The redo log buffer cache records all changes made to the database data blocks.
• Its primary purpose is recovery.
• Changes recorded within are called redo entries.
• Redo entries contain information to reconstruct or redo changes.
• Size is defined by LOG_BUFFER.

 

redo log buffer cache是一个循环使用的buffer用来存储data file block的改变。这些信息存储在redo entrie中，redo entries存放有重新创建那些被INSERT,UPDATE,DELETE,CREATE,ALTER 或 DROP 等操作改变前的数据的必须信息。

 

8、Large Pool

The large pool is an optional area of memory in the SGA configured only in a shared server environment.
• It relieves the burden placed on the shared pool.
• This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore operations.
• Unlike the shared pool, the large pool does not use an LRU list.
• Sized by LARGE_POOL_SIZE.
ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;

 

这里要注意large pool 只在shared server模式下有，它存在于shared pool 中。用途主要是UGA、I/O Slaves、和备份，恢复操作。并且不使用LRU算法管理。通过LARGE_POOL_SIZE管理空间。

 

9、Java Pool
The Java pool services the parsing requirements for
Java commands.
• Required if installing and using Java.
• It is stored much the same way as PL/SQL in database tables.
• It is sized by the JAVA_POOL_SIZE parameter.