Archive for the ‘SGA’ Category

Recursive Calls

Recursive calls is a call(sql statement) that must be completed before the completion of user’s SQL statement. Basically, recursive calls are generated by Oracle internal sql statements to maintain changes to tables for internal processing.

Reasons for Recursive Calls

Recursive calls can be generated due to following reasons:

The recursive calls Oracle metric maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates a SQL statement. These internal SQL statements generate recursive calls.

Oracle MetaLink documents also offer information regarding “OPTIMIZING ORACLE7.1 DATABASE PERFORMANCE” including some great points on the recursive calls metric:

“Recursive Calls : occur because of cache misses and dynamic storage
extension. If the dictionary data is found in cache, a recursive call is not
made and the data is read from cache directly. In general if recursive calls
is greater than 4 per process, the data dictionary cache (described below)
should be optimized and segments should be rebuilt with storage clauses
to have a few large extents. Segments include tables, indexes, rollback
segment, and temporary segments.
Also, Recursive calls should be fewer than user calls (less than one-
tenth). Where there is an imbalance the aim should be to reduce parsing.
review : recursive calls to user calls parse count to user calls. High levels
of recursive sql may also be attributable to significant use of PL/SQL.
For each sql statement in a PL/sql block, on each iteration, there are
recursive calls to do the equivalent of bind and define.”

SGA

Larger Pic Link

clip_image002[5]

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. SGA information is shared among multiple users of oracle database instance, its sometimes called shared global area. Oracle automatically allocates memory to SGA when an instance starts up, and the operating system reclaims the memory when the instance shuts down.

The SGA contains the following data structures:

  • Database buffer cache
  • Redo log buffer
  • Shared pool
  • Java pool
  • Large pool (optional)
  • Streams pool
  • Data dictionary cache
  • Other miscellaneous information

Dynamic SGA:

Sizing the SGA:

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

DB_CACHE_SIZE: The size of the cache of standard blocks. The default is 48 MB on UNIX and 52 MB on NT

LOG_BUFFER: The number of bytes allocated for the Redo Log Buffer

SHARED_POOL_SIZE: The size in bytes of the area devoted to shared SQL and PL/SQL. The default is 16 MB. If 64 bit, then the default size is 64 MB

LARGE_POOL_SIZE: The size of the Large Pool. The default is zero (Unless the init.ora parameter PARALLEL_AUTOMATIC_TUNING is set to TRUE, then the default is automatically calculated.)

JAVA_POOL_SIZE: The size of the Java Pool. The default is 24 MB

Therefore, the SGAs size can not exceed SGA_MAX_SIZE – DB_CACHE_SIZE – LOG_BUFFER – SHARED_POOL_SIZE – LARGE_POOL_SIZE – JAVA_POOL_SIZE.

Components of the SGA

The SGA consists of the following four (five if MTS) parts:

Fixed SGA

The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release. It is compiled into the database. The fixed SGA contains a set of variables that point to the other components of the SGA and variables that contain the values of various parameters. The size of the fixed SGA is constant and cannot be changed through any means and it is generally very small. Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.

Variable portion

The variable portion is called variable because its size (measured in bytes) can be changed.The variable portion consists of:

  • large pool (optional)
    It provides large memory allocation for backup and restoration operations, session memory for shard servers and Oracle XA interface, I/O server processes(DBWO) or parallel query buffers.
  • Java pool The location where session-specified Java code and application variables reside during program execution. 
  • Shared pool The shared pool is used for objects that are shared among all users. For example: table definitions, PL/SQL definitions, cursors and so on.

Shared Pool

Shared Pool

The shared pool portion of the SGA contains the Library Cache, the dictionary cache, buffers for parallel execution messages, and control structures.

Library Cache

The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.

The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data). The other area in memory to hold dictionary data is the library cache. All Oracle user processes share these two caches for access to data dictionary information.See dictionary cache efficiency for monitoring dictionary cache.

If the data dictionary cache is too small, requests for information from the data dictionary will cause extra I/O to occur; these I/O-bound data dictionary requests are called recursive calls and should be avoided by sizing the data dictionary cache correctly.

 

Streams Pool

The streams pool holds data and control structures to support the Oracle Streams
feature of Oracle Enterprise Edition. Oracle Streams manages the sharing of data and events in a distributed environment. If the initialization parameter STREAMS_POOL_SIZE is uninitialized or set to zero, the memory used for Streams operations is allocated from the shared pool and may use up to 10 percent of the shared pool.

The Oracle Streams pool stores buffered queue messages in memory, and it provides memory for capture processes and apply processes.

Continue reading