SYSTEM and SYSAUX Tablespaces

• The SYSTEM and SYSAUX tablespaces are mandatory tablespaces that are created at the time of database creation. They must be online.

• The SYSTEM tablespace is used for core functionality (for example, data dictionary tables).

• The auxiliary SYSAUX tablespace is used for additional database components (such as the Enterprise Manager Repository,AWR).

Logical and Physical Database Structures

• Segments exist in a tablespace.

• Segments are collections of extents.

• Extents are collections of data blocks.

• Data blocks are mapped to disk blocks.

Automated SQL Execution Memory Management

Automatic PGA Memory Management

Besides SQL, various components in the database server make use of PGA memory. These other parts are known as the untunable parts of the PGA because they require an allocation of memory that cannot be adjusted. This is the case for:

• The context information of each session
• Each open cursor
• PL/SQL, OLAP, or Java memory

The tunable portion of the PGA represents the memory available to SQL work areas. This portion could represent 90% of the overall PGA memory for decision support systems, whereas it could be less than 10% in pure OLTP systems.

With automatic PGA memory management, the system attempts to keep the amount of private memory below the target specified by the PGA_AGGREGATE_TARGET initialization parameter by adapting the size of the work areas to private memory. When increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.



Note: Oracle Corporation does not recommend the use of static SQL memory management. For more information about SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE, refer to the Oracle Database Reference guide.

SQL Memory Usage

Complex database queries require memory-intensive operators such as sort and hash-join. Those operators need what is called “work area memory” to process their input data. For example, a sort operator uses a work area to perform the in-memory sort of a set of rows. Similarly a hash-join operator uses a work area to build a hash table on one of the tables in the FROM clause.

The amount of memory allocated by these operators greatly affects their performance, and a larger work area can significantly improve the performance of a SQL operator. The optimal size of a work area is big enough to accommodate the input data and auxiliary memory structures.

Because there is only a finite amount of memory in the system shared by all concurrent operators, an operator cannot always allocate its optimal size. When the size of the work area is smaller than its ideal cache size, the response time increases because an extra pass is performed on all or some of the input data. This is referred to as the one-pass size of the work area.

When the work area size is less than the one-pass threshold, multiple passes over the input data are needed, causing dramatic increase in response time. This is referred to as the multi-pass size of the work area. For example, a sort operation that needs to sort 10 GB of data needs a little more than 10 GB of memory to run in cache, and 40 MB to run in one-pass mode. It will run in multiple passes with less than 40 MB. 

Setting PGA_AGGREGATE_TARGET Initially




Assume that an Oracle instance is configured to run on a system with 4 GB of physical memory. Part of that memory should be left for the operating system and other non-Oracle applications running on the same hardware system. You might decide to dedicate only 80% (3.2 GB) of the available memory to the Oracle instance.

You must then divide the resulting memory between the SGA and the PGA.

For OLTP systems, the PGA memory typically accounts for a small fraction of the total memory available (for example, 20% of the instance memory), leaving 80% for the SGA.

For DSS systems running large, memory-intensive queries, PGA memory can typically use up to 70% of the instance memory (up to 2.2 GB in this example).

Good initial values for the PGA_AGGREGATE_TARGET parameter might be:

• For OLTP: PGA_AGGREGATE_TARGET=(total_mem*80%)*20% 
• For DSS: PGA_AGGREGATE_TARGET=(total_mem*80%)*50% 

where total_mem is the total amount of physical memory available on the system.

In this example, with a value of total_mem equal to 4 GB, you can initially set PGA_AGGREGATE_TARGET to 1600 MB for a DSS system and to 655 MB for an OLTP system.

Automatic Shared Memory Management

Automatic Shared Memory Management: Overview
Automatic Shared Memory Management (ASMM) simplifies the configuration of the System Global Area (SGA). ASMM uses memory advisor data to evaluate the best memory configuration, and then resizes the amount of memory to be allocated for the database buffer cache, shared pool, Java pool, large pool, and Streams pool by using the dynamic SGA feature. 

ASMM makes more effective use of available memory and thereby reduces the cost incurred for acquiring additional hardware memory resources, and significantly simplifies Oracle database administration with a more dynamic, flexible, and adaptive memory management scheme.

ASMM enables you to specify a total memory amount to be used for all SGA components. The Oracle database server periodically redistributes memory between the components given in the slide according to workload requirements. 

For example, in a system that runs high-concurrency OLTP workload during the day, which requires a large buffer cache, you would have to configure both the buffer cache and the large pool to accommodate your peak requirements. With ASMM, when the OLTP workload runs, the buffer cache is given the required memory to optimize buffer cache access. When the decision support system (DSS) batch job starts up later, the memory is automatically migrated to the large pool so that it can be used by parallel query operations without producing memory overflow errors.

Note: With ASMM, component sizes are saved across a shutdown if an SPFILE is used. The sizes are resurrected from before the last shutdown to avoid relearning.




Automatic Memory Management: Overview

With Automatic Memory Management (AMM), the system causes an indirect transfer of memory from SGA to PGA (and vice versa). It automates the sizing of PGA and SGA according to your workload.

This indirect memory transfer relies on the OS mechanism of freeing shared memory. After memory is released to the OS, the other components can allocate memory by requesting memory from the OS.

Currently, this is implemented on Linux, Solaris, HP-UX, AIX, and Windows. Set your memory target for the database instance and the system then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the aggregate program global area (PGA).

The slide displays the differences between the Automatic Shared Memory Management mechanism and the Automatic Memory Management.

The simplest way to manage memory is to allow the database to automatically manage and tune it for you. To do so (on most platforms), you have to set only a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. The maximum memory size serves as an upper limit so that you do not accidentally set the target memory size too high. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the database also prevents you from setting the target memory size too low.

Oracle Database Memory Parameters

The slide displays the memory initialization parameters hierarchy. Although you have to set only MEMORY_TARGET to trigger Automatic Memory Management, you still have the ability to set lower-bound values for various caches. So if the child parameters are set by the user, they will be the minimum values below which that component is not auto-tuned.



The SGA is made up of several components, some are auto-tuned when ASMM is enabled, some are dynamic, and some are static. When Automatic Memory management is enabled the Automatic Shared Memory Management is also enabled.

Physical Structure

An Oracle database consists of physical files.  The database itself has:

·        Datafiles – these contain the organization's actual data.

·        Redo log files – these contain a chronological record of changes made to the database, and enable recovery when failures occur.

·        Control files – these are used to synchronize all database activities and are covered in more detail in a later module.

Other key files as noted above include: 

·        Parameter file – there are two types of parameter files. 

o   The init.ora file (also called the PFILE) is a static parameter file.  It contains parameters that specify how the database instance is to start up.  For example, some parameters will specify how to allocate memory to the various parts of the system global area.

o   The spfile.ora is a dynamic parameter file.  It also stores parameters to specify how to startup a database; however, its parameters can be modified while the database is running.

·        Password file – specifies which *special* users are authenticated to startup/shut down an Oracle Instance.

·        Archived redo log files – these are copies of the redo log files and are necessary for recovery in an online, transaction-processing environment in the event of a disk failure.

Oracle Database Memory Structures

The SGA is a read/write memory area that stores information shared by all database processes and by all users of the database (sometimes it is called theShared Global Area). 

The SGA is allocated when an Oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE or SPFILE). 

The SGA has the following mandatory memory structures:

·        Database Buffer Cache

·        Redo Log Buffer

·        Java Pool

·        Streams Pool

·        Shared Pool – includes two components:

o   Library Cache

o   Data Dictionary Cache

·        Other structures (for example, lock and latch management, statistical data)

Additional optional memory structures in the SGA include:

·        Large Pool

The SHOW SGA SQL command will show you the SGA memory allocations. 

·        This is a recent clip of the SGA for the DBORCL database at SIUE. 

·        In order to execute SHOW SGA you must be connected with the special privilege SYSDBA (which is only available to user accounts that are members of the DBA Linux group).

Oracle Database Architecture

Oracle Database Architecture

A database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

• Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost-effective way to manage information and applications.
• Enterprise grid computing creates large pools of industry-standard, modular storage and servers.
• With this architecture, each new system can be rapidly provisioned from the pool of components.
• There is no need to provide extra hardware to support peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.