0567-0569

Previous Table of Contents Next

Page 567

 # buffer is 50M shared_pool_size = 52428800 

You should also ensure that these values are always set appropriately high, but not inordinately high. The ramifications of changing the size of the SGA are discussed later in this chapter.

NOTE

When tuning Oracle, you might find yourself adjusting parameters in the INIT.ORA file numerous times. Always document any changes that you make to this file, including the previous value, the date changed, and who made the change. Documenting your INIT.ORA file makes it easier to analyze which values for the various parameters are most efficient.

Database Block Size

The building blocks of any database are the size of its blocks. You set this value with the DB_BLOCK_SIZE parameter, and its range is operating system-specific ”approximately 512 bytes to 16MB.

This value represents how data pieces are transferred to and from the instance's SGA during an operation. The more data that the database can transfer in a single operation, the fewer operations it has to perform; consequently, the overall performance of the instance improves . The value of DB_BLOCK_SIZE should be a multiple of the operating system block size. On some systems, the default operating system block size is sufficient. On other systems, the best speed is twice that value. The best way to determine this is to generate a test instance; use different sizes of blocks and conduct benchmark testing. Always keep in mind the limits imposed by the operating system when you do this. As with all other areas of performance tuning, a trade-off occurs: Setting the size of the block too high can actually degrade the performance.

After a database is created, the only way to change the value of DB_BLOCK_SIZE is to re-create the database. This makes sense. Whenever a database instance is created, Oracle physically allocates several database files of size X in which it will store various forms of information ”the data dictionary, tables, indexes, and so on. These files are created with blocks of size DB_BLOCK_SIZE and are mapped so that the database can recognize each one. If the value of DB_BLOCK_SIZE is changed, the blocks no longer begin and end where the database expects. The RDBMS cannot correctly manipulate data if it cannot recognize the blocks.

To change the size of the blocks:

1. Perform a full, cold backup of the database.
2. Use the exp utility to do a full database export on the database instance as the user SYS.
3. Remove all the physical data files.
4. Re-create the database with the new DB_BLOCK_SIZE.

Page 568

5. Use the imp utility to import the data from the original database instance into the new database instance as the user SYS.

CAUTION

When you create a database with a new DB_BLOCK_SIZE, examine each INIT.ORA parameter for possible ramifications. Many parameters are allocated in database blocks; increasing the size of each block might adversely affect the performance of the database.

This process is time-consuming and should be done only if the performance increase will be significant.

Database Buffer Cache

The database buffer cache is the memory buffer within the SGA that holds copies of data that has been read and often changed from the physical database files. There are as many buffers in this buffer cache as the value of DB_BLOCK_BUFFERS. They include

Dirty buffers	Buffers that have been changed but not written back to disk
Pinned buffers	Buffers that are currently being accessed
Free buffers	Buffers that are available for use

Because it is desirable to have Oracle work within the SGA memory area as much as possible, the hit rate within the database buffer cache should be very high ”greater than 70 percent. To determine the rate, execute the following query on the database buffer cache:

 select name, value from v$sysstat where name in (`consistent gets', `db block gets', `physical reads') / 

The query returns three values, which you can plug into the following mathematical formula to obtain the current database buffer cache hit ratio:

 hit ratio = 1 - (physical reads / (db block gets + consistent gets) ) 

If the hit ratio returned is less than 70 percent, you should seriously consider raising the number of blocks allocated to the database buffer cache of the SGA. To do that, increase the value of the INIT.ORA parameter DB_BLOCK_BUFFERS.

Shared Pool Size

The SGA shared pool area is composed primarily of two entities: the shared SQL cache and the data dictionary cache. Each one serves a distinct function. The shared SQL cache is used to retain previously executed queries, procedures, and other SQL-based operations in the SGA. Thus, frequently executed SQL statements reside in memory and do not have to be reparsed by the database before each execution. The data dictionary cache contains calls made to the

Page 569

data dictionary, which must be done before every single action in the database. In previous versions of Oracle, the data dictionary cache had individually tunable parameters, but they are now encompassed under the shared pool.

As with the database buffer cache, the efficiency of the shared pool cache is determined by a hit ratio that indicates how often the Oracle RDBMS can process information in memory and how often it must retrieve information from disk. The database should work as much from memory as possible without going to disk. Although that is not always practical, you should examine the various caches to ensure that their values are in acceptable ranges.

The following script compares the number of pins (how often an item was executed) to the number of reloads (how often a miss occurred):

 select sum(pins) pins, sum(reloads) reloads from v$librarycache / 

Use the following formula to determine the ratio of reloads to pins. If the result is 1 or greater, you need to tune the shared SQL area by increasing the size of the shared pool.

 ratio = (reloads / pins) * 100 

Similarly, the data dictionary cache determines how often the RDBMS goes to disk when it accesses information on users, privileges, tables, indexes, and so on. Most database systems reuse the same database objects repeatedly. Therefore, if a high degree of disk access takes place for operations that run the same programs, the information is likely being aged out too often. The same rule holds true for the other shared pool areas.

The following code segment enables the DBA or the user to retrieve the number of gets (information requests on an object) and getmisses (cached or missed queries).

 select sum(gets) gets, sum(getmisses) getmisses from v$rowcache / 

The formula for the ratio of gets to getmisses is

 ratio = ( getmisses / gets) * 100 

If the ratio is greater than 10 percent, you should consider increasing the value of the SHARED_POOL_SIZE parameter. It is usually a good idea to have a large shared pool. In a few cases, however, this can adversely affect the database.

Sort Area Size

During a single day, a database instance performs many operations that involve sorting. They include everything from an explicit command to sort (such as the SQL ORDER BY or GROUP BY option) to an implicit command (such as creating an index to a database table). Working in memory is faster than working on disk, and sorting is no exception.

Previous Table of Contents Next