Author: Burleson

Additional Machine Resources:

One of the primary jobs of a database administrator is to predict when the system will need additional machine resources and ensure that it gets those resources before the database experiences performance problems. To meet this duty, the administrator must have the information that shows how the database server consumes resources. In this Daily Feature, I'll show you how to gather the data you need to form a cohesive picture of server performance and use that information to keep your database from bogging down.

Resource Usage:

Sadly, Oracle database servers vary widely in resource usage. They can be stressed one minute and idle the next. For example, it's common for an Oracle server to be at 100 percent utilization from 8:00 A.M. to 5:00 P.M. and then revert to 90 percent idle for the remaining hours of the day. These swings in usage generate misleading “average utilization” metrics.

Effective Oracle Optimization:

For Oracle optimization to be effective, you must gear it to those times when the database is performing at peak levels. Even if the server is idle all night, you'll still need to have enough CPU, RAM, and disk bandwidth to keep response time fast during the day, when all of your users are hammering it.

Disk Activity:

All Oracle databases heavily access your server's hard drives. Tracking, managing, and controlling disk activity is critical for a database administrator who wants to make the most efficient use of the server. To see how disk tracking works, let's take a look at some sample reports that are used for disk hardware capacity planning.

Measuring Total Disk Consumption:

Oracle provides a utility called STATSPACK for tracking disk I/O. You can easily extend STATSPACK to keep track of the amount of space used within the whole database. You can use this information to plot the overall database growth rate and predict the future database size. This type of report is especially useful for ensuring that there's enough disk to accommodate the future growth of the database system.

Measuring total disk I/O and Tracking R/W Operations:

You must also measure total disk I/O and track the number of read and write operations over long periods of time. It's interesting to note that common signatures often begin to appear when you capture and plot disk I/O information for Oracle databases.

RAM Cache:

As you may know, RAM caches are used to minimize disk I/O. On the disk storage, mass storage disk devices (such as EMC disk arrays) contain several gigabytes of RAM cache, and the Oracle database includes an area called db_cache_size (formerly db_block_buffers in Oracle8 i ), which contains RAM buffers for incoming disk blocks.

Overall Disk Configuration:

When optimizing the disk I/O subsystem on the database server, it's also important to take a look at the overall disk configuration. This includes the RAID configuration for the disks and the mapping between the disks and the physical data files.

RAID 1+0:

The most popular disk configuration for Oracle databases is RAID 1+0, which is also RAID 10. RAID 1+0 combines disk mirroring with block-level striping. The disk mirroring is insurance against a failed disk, while the block-level striping ensures that no individual disk becomes overburdened with I/O.

Block-Level Striping:

With block-level striping, each data block in a file is distributed across the stripe set. For example, a file named customer.dbf might physically exist on eight different disk spindles. Hence, all the I/O will be randomly distributed across all the Oracle database files, eliminating the possibility that any one spindle will experience contention.

RAM for SGA and PGA:

The Oracle database needs enough RAM to allocate the System Global Area (SGA) and also enough reserve RAM available for the Program Global Area (PGA) for any session that will be connecting to the database server. In an optimal environment, you'll have enough RAM on the database server to handle the maximum number of connected users, plus the RAM consumed by the database SGA region.

RAM Paging:

A database that is short on system memory resources will experience RAM paging, a condition in which memory frames are written to the swap disk in order to make enough memory available for competing tasks.

Shared RAM Resources:

In Oracle9 i , you can use the pga_aggregate_target parameter, which provides shared RAM resources for PGAs. However, you'll still need a simple way to measure RAM consumption by individual Oracle sessions. A huge amount of RAM consumption takes place when a session sorts a result set from Oracle, so monitoring sorts can give you an idea of the amount of RAM used on the database server.

Optimal CPU resources for Oracle:

Because of the huge changes in an Oracle database's CPU demands, determining the optimal amount of CPU resources for Oracle is a challenge. Some databases can experience hundreds or thousands of transactions per second, so the load on an Oracle database can vary significantly at different points in time.

Uniform Response Times:

Oracle users who want to be sure that they always have uniform response times will commonly look at the server run-queue metric that is captured within the UNIX vmstat utility. The run queue is the number of tasks waiting for execution, including those tasks that are currently being serviced.

CPU Bottleneck:

If your UNIX server with eight CPUs has a run queue of nine, all eight CPUs will be busy processing tasks. A ninth task will wait for an opportunity to be serviced by the CPU. Whenever the run queue exceeds the number of CPUs on the database server, the Oracle database can experience a CPU bottleneck. You should make additional CPU resources available to that server in order to eliminate the bottleneck.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Power of 11g Snapshot Standby Database for Oracle 10g!!
   Oracle Security Guide: Beware of all DBAs having full OS access!!
   Great Tips on Tuning Database Materialized Views
   DBA Tips: Switching Oracle Users and Back!!