Multiplexing Redo log File

Multiplex redo log by adding group:

Redo log files are used to record changes made to the database. Redo log files are important for the recovery purpose.So thats why in some cases you might need to create additional log file groups. To create a new group of online redo log files use the following SQL command:
Alter database add logfile group 3 ('/opt/luck/redo03_00.log',
                                                  '/opt/luck/redo03_01.log') size 10m;

Multiplexing redo log by adding member:

You can add new members to existing redo log file groups using the following ALTER DATABASE ADD LOGFILE MEMBER command:

Alter database add member '/opt/luck/redo02.log' to group 1,

                                         '/opt/luck/redo02.log' to group 2,

                                         '/opt/luck/redo03.log' to group 3;

Use the fully specified name of the log file members; otherwise the files are created in a default directory of the database server. If the file already exists, it must have the same size, and you must specify the REUSE option.

Read More

How Redo Logs Work

The online redo log of a database consists of two or more online redo log files. Oracle needs a minimum of two files to guarantee that one is always available for writing while the other is being archived.The Oracle server sequentially records all changes made to the database in the redo log buffer. The redo entries are written from the redo log buffer to one of the online redo log groups called the current online redo log group by the LGWR process. LGWR writes under the following situations:

• When a transaction commits
• When the redo log buffer becomes one-third full
• When there is more than a megabyte of changed records in the redo log buffer
• Before the DBWn writes modified blocks in the database buffer cache to the data files

LGWR writes to online redo log files in a circular fashion.Each redo log file group is identified by a log sequence number that is overwritten when log is reused.LGWR writes on the online redo log files continueslly. When the current online redo log grooup is filled log writer starts writing to the next group. When the last online redo log file is filled, LGWR returns to the first online redo log group and starts writing again.

Read More

Online Redo Log File

Each transaction is recorded in the redo logs.Redo logs stroe all changes made to the database. In the oracle database every instance has online redo log to protect the database in any case of an instance failure.Every oracle database instance has its own online redo log group. 

Online redo log files are filled with redo records are also called redo entry. If you change any value in a table then you generate a redo record that describe changes to the data segment block for the table and the transaction table of the rollback segments.Redo entries record all data that you can use to reconstruct all changes made to the database.Online redo log also protects rollback data, when you recover the database using redo data.

 In the above fig you can see a set of identical copies of online redo log files. DBA can set up the oracle database to maintain copies of online redo log files to avoid losing database information. The Log Writer LGWR background process writes the same information to all online redo log files in a group.So, Oracle server needs minimum two online rredo log file groups for the normal operation of database.In each group each online redo log file is known as member and each member in a group has Log Sequence Number.The current log sequence number is stored in the control file and in the header of all data files.
Using the alert log,v$logfile, v$loghist, v$recovery_log, you can monitor redo logs.

Read More

Stream Pool

This is a new area in Oracle Database 10g that is used to provide buffer areas for the streams components of Oracle.  To configure the Streams pool explicitly, specify the size of the pool in bytes using the streams_pool_size initialization parameter. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams Pool is zero or not specified, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.

Read More

Large Pool & Java Pool

Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance.

• Session memory for the a shared server and the Oracle XA Interface when distributed transactions are involved
• I/O Server Processes
• Parallel Query Buffers
• Oracle Backup and Restore Operations using RMAN

 Large Pool plays an important role in Oracle Database Tuning since the allocation of the memory for the above components otherwise is done from the shared pool. Also due to the large memory requirements for I/O and Rman operations, the large pool is better able to satisfy the requirements instead of depending on the Shared Pool Area.

Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead casued by shrinking the SQL Cache Area.

This area is only used if shared server architecture, also called multi-threaded server (MTS), is used, or if parallel query is utilized. The large pool holds the user global areas when MTS is used and holds the parallel query execution message queues for parallel query.

By issuing a summation select against the v$sgastat view, a DBA can quickly determine the size of the large pool area currently being used.

SELECT name, SUM(bytes) FROM V$SGASTAT WHERE pool='LARGE POOL' GROUP BY ROLLUP(name);

The select above should be used when an "ORA-04031: Unable to allocate 16084 bytes of shared memory  large pool unknown object large pool hea PX large pool error is received with a configured large pool the number of bytes specified may differ

Read More

Data Dictionary Cache

An important part of an Oracle database is its data dictionary, which is a read-only set of tables that provides administrative metadata about the database. A data dictionary contains information such as the following:

• The definitions of every schema object in the database, including default values for columns and    integrity constraint information.
•  The amount of space allocated for and currently used by the schema objects
• The names of Oracle Database users, privileges and roles granted to users, and auditing information related to users

The data dictionary is a central part of data management for every Oracle database. For example, the database performs the following actions:

• Accesses the data dictionary to find information about users, schema objects, and storage structure
• Modifies the data dictionary every time that a DDL statement is issued

Contents of the Data Dictionary

The data dictionary consists of the following:

Base Tables

The underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.

 Views

The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.

Related Post:- What Is View?

Storage of the Data Dictionary

The data dictionary base tables are the first objects created in any Oracle database. All data dictionary tables and views for a database are stored in the SYSTEM tablespace. Because the SYSTEM tablespace is always online when the database is open, the data dictionary is always available when the database is open.

How Oracle Uses the Data Dictionary

Data in the base tables of the data dictionary is necessary for Oracle to function. Therefore, only Oracle should write or change data dictionary information. Oracle provides scripts to modify the data dictionary tables when a database is upgraded or downgraded. During database operation, Oracle reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Oracle also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.

For example, if user Kathy creates a table named parts, then new rows are added to the data dictionary that reflect the new table, columns, segment, extents, and the privileges that Kathy has on the table. This new information is then visible the next time the dictionary views are queried.

Dictionary Cache is place in Shared Pool which contains Data Dictionary. Oracle frequently requires Data Dictionary. Most parts of Data Dictionary are cached into Dictionary Cache. Oracle utilizes Dictionary Cache information in query parsing. Dictionary cache is also called Row Cache as data inside Dictionary Cache is maintained into rows instead of buffer

Read More

Library Cache

The library cache stores information about the most recently used SQL and PL/SQL statements. Library cache is very important part of Oracle Sharedpool. Shared Pool controls execution of SQL statements. Shared pool is divided into Data dictionary Cache and Library Cache. In Dedicated server configuration Private SQL area is created in PGA of server process. Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool with in the SGA.

Shared SQL Area

Shared SQL Area contains parse tree and execution plan of SQL cursors and PL/SQL programs. So executable form of SQL statements is available here which ca be reused. When a query is submitted to oracle server for execution, oracle checks if same query has been executed previously. If the parsed execution plan is found then this event is known as Library cache hit or soft parsing. If pared form of the statement is not found in the shared pool then new statement is parsed and its parsed version is stored in Shared SQL area. This is known as hard parse.

Oracle allocates memory from shared pool when a new statement is submitted. If required, oracle may deallocate memory from previous statements. As a result of this, deallocated statements shall require hard parsing when re-submitted. More resources are used to perform a hard parse. So it is very important to keep the size for shared pool large enough to avoid hard parsing.

As Library cache is kept inside Shared Pool so use SHARED_POOL_SIZE initialization parameter to increase the size of Shared Pool. It will indirectly increase memory available for Shared SQL Area.

Private SQL area

Each session issuing a SQL statement has a private SQL area in its PGA (see "Private SQL Area"). Each user that submits the same statement has a private SQL area pointing to the same shared SQL area. Thus, many private SQL areas in separate PGAs can be associated with the same shared SQL area.

The database automatically determines when applications submit similar SQL statements. The database considers both SQL statements issued directly by users and applications and recursive SQL statements issued internally by other statements. The location of the private SQL area depends on the connection established for the session. If a session is connected through a shared server, then part of the private SQL area is kept in the SGA.

Read More

Shared Pool

The shared pool is used to store the most recently executed SQL statements and the most recently used data definitions. The shared pool caches various types of program data. The shared pool stores parsing, interpreting, and executing all of the SQL statements and data dictionary information. The shared pool includes the following components:

• The library cache
• The dictionary cache
• Reserved Pool

The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.

Related post:-  Introduction to SGA

Read More

Redo Log Buffer

Oracle database keeps record of changes made to data. Every time user performs a DML, DDL or DCL operation, its redo entries are also created. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary. Oracle Database processes copy redo entries from the user memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background process log writer (LGWR) writes the redo log buffer to the active online redo log group on disk

If database is running in archiving mode then archive log files are created from redo log files. These archive log files are used to in various database recovery techniques.

LOG_BUFFER initialization parameter is used to set the size Redo Log buffer

Related Post :- System Global Area (SGA)

Read More

Buffer Pools

A buffer pool is a collection of buffers. The database buffer cache is divided into one or more buffer pools.

You can manually configure separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks. You can then assign specific schema objects to the appropriate buffer pool to control how blocks age out of the cache.

The possible buffer pools are as follows:

Default pool

This pool is the location where blocks are normally cached. Unless you manually configure separate pools, the default pool is the only buffer pool.

Keep pool

This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space. The goal of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.

Recycle pool

This pool is intended for blocks that are used infrequently. A recycle pool prevent objects from consuming unnecessary space in the cache.

You can create a tablespace with a block size that differs from the standard size. Each non default block size has its own pool. Oracle Database manages the blocks in these pools in the same way as in the default pool. The structure of the buffer cache when multiple pools are used. The cache contains default, keep, and recycle pools. The default block size is 8 KB. The cache contains separate pools for tablespaces that use the nonstandard block sizes of 2 KB, 4 KB, and 16 KB.

Read More

Database Buffer Cache

Database Buffer cache is one of the most important components of System Global Area (SGA). Database Buffer Cache is the place where data blocks are copied from datafiles to perform SQL operations. Buffer Cache is shared memory structure and it is concurrently accessed by all server processes. The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list.

The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk.

The LRU list holds pinned buffers,clean, free and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed

When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list. When oracle server process requires a specific data block, it first searches it in Buffer cache. If it finds required block, it is directly accessed and this event is known as Cache Hit. If searching in Buffer cache fails then it is read from datafile on the disk and the event is called Cache Miss. If the required block is not found in Buffer cache then process needs a free buffer to read data from disk. It starts search for free buffer from least recently used end of LRU list. In process of searching, if user process finds dirty block in LRU list it shifts them to Write List. If the process can not find free buffers until certain amount of time then process signals DBWn process to write dirty buffers to disks.

When the user process is performing a full table scan, it reads the blocks of the table into 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 blocks should be moved out quickly to leave more frequently used blocks in the cache.


Size of the Database Buffer Cache :- 

Oracle allows different block size for different tablespaces. A standard block size is defined in DB_BLOCK_SIZE initialization parameter . System tablespace uses standard block size. DB_CACHE_SIZE parameter is used to defiane size for Database buffer cache.

you can also set the size for two additional buffer pools, KEEP and RECYCLE, by setting DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE. These three parameters are independent of one another.

Read More

Program Global Area (PGA)

Program Global Area Components

The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process.

Content of the PGA

The content of the PGA memory varies, depending on whether the instance is running the shared server option. But generally speaking, the PGA memory can be classified as follows.

Private SQL Area

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Each user that submits the same SQL statement has his or her own private SQL area that uses a single shared SQL area. Thus, many private SQL areas can be associated with the same shared SQL area. The private SQL area of a cursor is itself divided into two areas whose lifetimes are different: The persistent area, which contains, for example, bind information. It is freed only when the cursor is closed. The run-time area, which is freed when the execution is terminated

Cursors and SQL Areas

The application developer of an Oracle precompiler program or OCI program can explicitly open cursors, or handles to specific private SQL areas, and use them as a named resource throughout the execution of the program. Recursive cursors that Oracle issues implicitly for some SQL statements also use shared SQL areas. The management of private SQL areas is the responsibility of the user process. The allocation and deallocation of private SQL areas depends largely on which application tool you are using, although the number of private SQL areas that a user process can allocate is always limited by the initialization parameter OPEN_CURSORS. The default value of this parameter is 50

PGA Memory Management for Dedicated Mode

You can automatically and globally manage the size of SQL work areas. The database administrator simply needs to specify the total size dedicated to PGA memory for the Oracle instance by setting the initialization parameter PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global target for the Oracle instance, and Oracle tries to ensure that the total amount of PGA memory allocated across all database server processes never exceeds this target.

Related Post :- 

• Introduction to an Oracle Instance 
• System Global Area (SGA)
• Dynamic SGA

Read More

Dynamic SGA and SGA Granules

A dynamic SGA implements an infrastructure that allows the SGA configuration to change without shutting down the instance. With the dynamic SGA infrastructure, the size of the buffer cache, the shared pool, the large pool, and the process-private memory can be changed without shutting down the instance. Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE. If SGA_MAX_SIZE specified in the initialization parameter file is less than the sum of all components specified or defaulted at initialization time, then the setting in the initialization parameter file is ignored.

For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically, because portions of the SGA are paged (written to and read from disk) by the operating system. The amount of memory dedicated to all shared areas in the SGA also has performance impact.

The size of the SGA is determined by several initialization parameters. The following parameters most affect SGA size:

DB_CACHE_SIZE: The size of the cache of standard blocks.

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

JAVA_POOL_SIZE  :- The size of the Java pool.

Dynamic SGA Granules

With dynamic SGA, the unit of allocation is called a granule. Components, such as the buffer cache, the shared pool, the java pool, and the large pool, allocate and free SGA space in units of granules. Oracle tracks SGA memory use in integral numbers of granules, by SGA component. All information about a granule is stored in a corresponding granule entry. Oracle maintains the state of each granule in the granule entry and the granule type.

Granule size is determined by total SGA size. On most platforms, the size of a granule is 4 MB if the total SGA size is less than 128 MB, and it is 16 MB for larger SGAs. There may be some platform dependency, for example, on 32-bit Windows NT, the granule size is 8 MB for SGAs larger than 128 MB.

The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.

If you specify a size for a component that is not a multiple of granule size, then Oracle rounds the specified size up to the nearest multiple. For example, if the granule size is 4 MB and you specify DB_CACHE_SIZE as 10 MB, you will actually be allocated 12 MB.

Related post:

• System Global Area (SGA)
• Program Global Area (PGA)
• SGA_MAX_SIZE Initialization Parameter
  

Read More

System Global Area (SGA)

 The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle instance. All Oracle processes use the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle  initialization parameters.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA. The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA contains the following data structures:

Database buffer cache

Redo log buffer

Shared pool

Java pool

Large pool

Data dictionary cache

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. 

Related posts:- 

• Introduction to an Oracle Instance 
• Program Global Area (PGA)
• Dynamic SGA

Read More

Introduction to an Oracle Instance

Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.

An instance is the (executed) Oracle software (Oracle processes) and the memory they use (SGA). It is the instance that manipulates the data stored in the database. An instance is identified by using methods specific to each operating system. The instance can open and use only one database at a time.

Related Post:-

• Instance Memory Structure 
• Background Process
• Initialization Parameter Files 

Read More