Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

An Overview of the Oracle Database Instance Oracle Tips by Burleson

Before we explore Oracle instance tuning, we must first get a clear idea about the nature of an Oracle instance. At a very general level, an Oracle instance consists of two components: the System Global Area (SGA) and the Oracle background processes, as shown in Figure 9-1.

Figure 9-55: An Oracle instance

Within these structures, we find that we can control both the SGA and the background processes by adjusting Oracle parameters. Let's begin with a high-level look at the Oracle SGA.

The Oracle SGA

When Oracle is started, the Oracle executable issues the malloc( ) command to create a region of RAM memory. The SGA is commonly called the Oracle region, because it is a region of RAM memory on the database server RAM heap.

The Oracle DBA controls the size of the SGA, and proper SGA management can have a huge impact on performance. However, we must remember that the SGA is a static memory region, and the needs of the Oracle database are constantly changing. Until Oracle, the SGA was not dynamic and could not be altered. Once Oracle becomes commonplace, the dynamic adjustment of the SGA will be possible, and eventually the SGA will reconfigure itself based on the needs of the database. However, until then, changing the SGA requires constant monitoring, and it is sometimes a good approach to develop a general setting for the SGA parameters based on the historical needs of the application.

With STATSPACK, we can observe the historical needs of any application that is running in the SGA, and we can develop a set of global parameters. Using historical STATSPACK information to tune the SGA region works out well because of Oracle's high level of sophistication. Tuning Oracle's memory involves a number of Oracle parameters. While there are hundreds of initialization parameters, there are a handful of Oracle parameters that are very important for Oracle tuning:

• buffer_pool_keep This data buffer pool is used to store small tables that perform full table scans.

• buffer_pool_recycle This pool is reserved for table blocks from very large tables that perform full table scans.

• db_cache_size This parameter determines the number of database block buffers in the Oracle SGA and represents the single most important parameter to Oracle memory.

• db_block_size The size of the database blocks can make a huge improvement in performance. While the default value may be as small as 2,048 bytes, data warehouses and applications that have large tables with full table scans will see a tremendous improvement in performance by increasing db_block_size to a larger value. As a general rule, the larger the block size, the less physical I/O, and the faster the overall performance.

• db_file_multiblock_read_count This parameter is used for multiblock reads when performing full table scans or large range scans.

• large_pool_size This is a special area of the shared pool that is reserved for SGA usage when using the multithreaded server. The large pool is also used for parallel query and RMAN processing.

• log_buffer This parameter determines the amount of memory to allocate for Oracle's redo log buffers. If there is a high amount of update activity, the log_buffer should be allocated more space.

• shared_pool_size This parameter defines the pool that is shared by all users in the system, including SQL areas and data dictionary caching. As we will learn later in this chapter, a large shared_pool_size is not always better than a smaller shared pool. If your application contains nonreusable SQL, you may get better performance with a smaller shared pool.

• sort_area_size This parameter determines the memory region that is allocated for in-memory sorting. When the stats$sysstat value sorts (disk) becomes excessive, you may want to allocate additional memory.

• hash_area_size This parameter determines the memory region reserved for hash  joins.  Starting with Oracle, Oracle does not recommend using hash_area_size unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting pga_aggregate_target, and hash_area_size is retained only for backward compatibility purposes.

To see the size of your SGA, you can issue the show sga command from SQL*Plus, as shown below. The output of the show sga command appears here:

Now let's take a quick look at the important regions within the SGA. We will return to these areas later in this chapter and see how to use STATSPACK to tune each memory area.

The Data Buffer Caches

Starting in Oracle8, the Oracle SGA has three areas of RAM for caching incoming data blocks from disk. They are the DEFAULT pool, the KEEP pool and the RECYCLE pool, as shown in Figure 9-2.

Figure 9-56: The Oracle data buffer pools

The buffer caches are in-memory areas of the SGA where incoming Oracle data blocks are kept. On standard UNIX databases, the data is read from disk into the UNIX Journal File System (JFS) buffer where it is then transferred into the Oracle buffer. The size of the data buffers can have a huge impact on Oracle system performance. The larger the buffer cache, the greater the likelihood that data from a prior transaction will reside in the buffer, thereby avoiding expensive physical disk I/O.

Remember, the KEEP and RECYCLE pools in Oracle are only applicable to the DEFAULT pool (the one that is created with the default db_block_size.  In Oracle you can configure multiple regions from 2k-32k block size, and each has a distinct buffer cache.

The Log Buffer

As you may know, Oracle creates redo logs for all update transactions. These redo logs contain the after image of all row changes and are used to roll forward in cases where Oracle experiences a disk failure. Oracle keeps these redo logs in three separate areas, as shown in Figure 9-3.

Figure 9-57: The Oracle redo log areas

As we see in Figure 9-3, the after images are first written to an area of RAM called the log buffer. The images are then transferred to Oracle online redo log files. Finally, the online redo log files are written to the archived redo log file system where they can be used to recover the database.

The Oracle Shared Pool

After the data buffer caches, the shared pool is the most important area of the SGA. The SGA contains a number of subareas, each with a specific purpose, as shown in Figure 9-4. One of the confounding problems with Oracle is that all of these subareas are controlled with a single parameter called shared_pool_size.

Figure 9-58: The Oracle shared pool

It is impossible to dedicate separate regions of memory for the components within the shared pool. The shared pool is normally the second largest memory component within the SGA, depending upon the size of the db_block_size parameter. The shared pool holds RAM memory regions for the following purposes:

• Library cache The library cache stores the execution plan information for SQL that is currently being executed. This area also holds stored procedures and trigger code.

• Dictionary cache The dictionary cache stores environmental information, including referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.

• Session information Stores session information for systems that are using SQL*Net version 2 with Oracle's multithreaded server. Starting in Oracle, the v$session view also contains information related to Oracle Net users.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.