ITPub博客

Oracle represents each SQL statement it runs with a shared SQL area and a privateSQL area. Oracle recognizes when two users are executing the same SQL statementand reuses the shared SQL area for those users. However, each user must have aseparate copy of the statement’s private SQL area.[@more@]

The library cache includes the shared SQL areas, private SQL areas (in the case of ashared server configuration), PL/SQL procedures and packages, and controlstructures such as locks and library cache handles.Shared SQL areas are accessible to all users, so the library cache is contained in theshared pool within the SGA.库高速缓存1. 包括共享SQL区域， 私有SQL区域， PL/SQL过程和包 ， 锁和library cache handles等控制结构2. 共享SQL区域对所有用户都提供访问[@more@]

The shared pool portion of the SGA contains the library cache, the dictionary cache,buffers for parallel execution messages, and control structures.The total size of the shared pool is determined by the initialization parameterSHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platformsand 64MB on 64-bit platforms. Increasing the value of this parameter increases theamount of memory reserved for the shared pool.共享池1. 包括库高速缓存， 字典高速缓存， 控制结构， 并行执行信息

The redo log buffer is a circular buffer in the SGA that holds information aboutchanges made to the database. This information is stored in redo entries. Redo entriescontain the information necessary to reconstruct, or redo, changes made to thedatabase by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redoentries are used for database recovery, if necessary.Redo entries are copied by Oracle database processes from the user’s memory space tothe redo log buffer in the SGA. The redo en

You can configure the database buffer cache with separate buffer pools that either keepdata in the buffer cache or make the buffers available for new data immediately afterusing the data blocks. Particular schema objects (tables, clusters, indexes, andpartitions) can then be assigned to the appropriate buffer pool to control the way theirdata blocks age out of the cache.■ The KEEP buffer pool retains the schema object’s data blocks in memory.■ The RECYCLE buffer pool eliminates data blocks fr

Oracle supports multiple block sizes in a database. The standard block size is used forthe SYSTEM tablespace. You specify the standard block size by setting the initializationparameter DB_BLOCK_SIZE. Legitimate values are from 2K to 32K.Optionally, you can also set the size for two additional buffer pools, KEEP andRECYCLE, by setting DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. Thesethree parameters are independent of one another.The cache has a limited size, so not all the data on disk can fit

When the user process is performing a full table scan, it reads the blocks of the tableinto buffers and puts them on the LRU end (instead of the MRU end) of the LRU list.This is because a fully scanned table usually is needed only briefly, so the blocksshould be moved out quickly to leave more frequently used blocks in the cache.You can control this default behavior of blocks involved in table scans on atable-by-table basis. To specify that blocks of the table are to be placed at the MRU endof t

The buffers in the cache are organized in two lists: the write list and the least recentlyused (LRU) list. The write list holds dirty buffers, which contain data that has beenmodified but has not yet been written to disk. The LRU list holds free buffers, pinnedbuffers, and dirty buffers that have not yet been moved to the write list. Free buffersdo not contain any useful data and are available for use. Pinned buffers are currentlybeing accessed.When an Oracle process accesses a buffer, the proce

The database buffer cache is the portion of the SGA that holds copies of data blocksread from datafiles. All user processes concurrently connected to the instance shareaccess to the database buffer cache.The database buffer cache and the shared SQL cache are logically segmented intomultiple sets. This organization into multiple sets reduces contention onmultiprocessor systems.Database Buffer Cache : 保存从数据文件中读取的数据块 ， 用户进程可共享 。逻辑的分为多个子段 ， 以便减少多用户之间的竞争[@

A database administrator expands the SGA use of a component with an ALTERSYSTEM statement to modify the values of the initialization parameters associatedwith the respective components. Oracle Database rounds up the newly specified sizeto the nearest multiple of 16MB and adds or removes granules to meet the target size.The database must have enough free granules to satisfy the request. As long as thecurrent amount of SGA memory is less than SGA_MAX_SIZE, the database can allocatemore granules un

Oracle Database remembers the sizes of the automatically tuned components acrossinstance shutdowns if you are using a server parameter file (SPFILE). As a result, thesystem does need to learn the characteristics of the workload again each time aninstance is started. It can begin with information from the past instance and continueevaluating workload where it left off at the last shutdown.Oracle数据库能记住上次实例关闭时各动态调整组件的值 ，前提条件是使用了spfile .so , 当实例启动时 ， 系统不需要再

There are a few SGA components whose sizes are not automatically adjusted. Theadministrator needs to specify the sizes of these components explicitly, if needed bythe application. Such components are:■ Keep/Recycle buffer caches (controlled by DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE)■ Additional buffer caches for non-standard block sizes (controlled by DB_nK_CACHE_SIZE, n = {2, 4, 8, 16, 32})The sizes of these components is determined by the administrator-defined value oftheir correspondi

When you set a value for SGA_TARGET, Oracle Database 10g automatically sizes themost commonly configured components, including:■ The shared pool (for SQL and PL/SQL execution)■ The Java pool (for Java execution state)■ The large pool (for large allocations such as RMAN backup buffers)■ The buffer cache■ The Streams poolYou need not set the size of any of these components explicitly. By default theparameters for these components will appear to have values of zero. Whenever acomponent needs m

The SGA_TARGET initialization parameter reflects the total size of the SGA andincludes memory for the following components:■ Fixed SGA and other internal allocations needed by the Oracle Database instance■ The log buffer■ The shared pool■ The Java pool■ The buffer cache■ The keep and recycle buffer caches (if specified)■ Nonstandard block size buffer caches (if specified)■ The Streams poolIt is significant that SGA_TARGET includes the entire memory for the SGA, in contrastto earlier rele

In previous database releases, a database administrator (DBA) was required tomanually specify different SGA component sizes by setting a number of initializationparameters, including the SHARED_POOL_SIZE, DB_CACHE_SIZE, JAVA_POOL_SIZE, and LARGE_POOL_SIZE parameters. Oracle Database 10g includes the Automatic Shared Memory Management feature which simplifies the SGA memory management significantly. In Oracle Database 10g, a DBA can simply specify the total amount of SGA memory available to an in

The SGA comprises a number of memory components, which are pools of memoryused to satisfy a particular class of memory allocation requests. Examples of memorycomponents include the shared pool (used to allocate memory for SQL and PL/SQLexecution), the java pool (used for java objects and other java execution memory), andthe buffer cache (used for caching disk blocks). All SGA components allocate anddeallocate space in units of granules. Oracle Database tracks SGA memory use ininternal numbers of

Another way to rename a column is to use the RENAME statement together with theCREATE TABLE statement with AS subquery. This method is useful if you arechanging the structure of a table rather than only renaming a column. Thefollowing statements re-create the sample table hr.job_history, renaming a column fromdepartment_id to dept_id:CREATE TABLE temporary (employee_id, start_date, end_date, job_id, dept_id)AS SELECT employee_id, start_date, end_date, job_id, department_idFROM job_history;

Parameter type : Big integerSyntax : DB_CACHE_SIZE = integer [K | M | G]Default value : If SGA_TARGET is set: If the parameter is not specified, then the defaultis 0 (internally determined by the Oracle Database). If the parameter isspecified, then the user-specified value indicates a minimum value forthe memory pool.If SGA_TARGET is not set, then the default is either 48 MB or 4MB *number of CPUs * granule size, whichever is greaterModifiable : ALTER SYSTEMBasic : NoDB_CACHE_SIZE specifies the

Parameter type : IntegerDefault value : 0Modifiable : No Range of values : 50 to an operating system-specific maximumBasic : NoReal Application Clusters : Multiple instances can have different values, and you can change the values as needed.Note: DB_BLOCK_BUFFERS cannot be combined with the dynamic DB_CACHE_SIZE parameter; combining these parameters in the same parameter file will produce an error.DB_BLOCK_BUFFERS specifies the number of database buffers in the buffer cache. It is one of several

Parameter type : IntegerDefault value : 8192Modifiable : NoRange of values : 2048 to 32768, but your operating system may have a narrower rangeBasic : YesReal Application Clusters : You must set this parameter for every instance, and multiple instances must have the same value.DB_BLOCK_SIZE specifies (in bytes) the size of Oracle database blocks. Typical values are 4096 and 8192. The value of this parameter must be a multiple of the physical block size at the device level.The value for DB_BLOCK_