Oracle Health Check

The Health Check for Oracle is a carefully crafted program designed to review the efficiency as well as effectiveness of Oracle’s database technologies in business environments. The efficiency of your Oracle-based system is evaluated by determining the deployment and usage of Oracle’s products; essentially, is the system “firing on all cylinders” and capitalizing on the deployed products?

The efficiency of a system takes into consideration issues such as:

• Is the database and operating system properly tuned?
• Are there sufficient hardware resources for the various applications?
• Have schemas been normalized or denormalized where appropriate?
• Have the proper indexes been created?
• Are critical administrative utilities run regularly?
• Does the staff have the requisite skills needed to maintain an efficient system?

In a complex computing environment, the extent to which these and other issues are addressed will affect the overall efficiency and effectiveness of the systems in place. The Oracle Database Server Health Check addresses these issues of efficiency and effectiveness by having a skilled Oracle DBA assess your Oracle Database environment over a one to five day period. The environment includes the database server, application servers, Oracle client PCs and round-trip network traffic to and from the database.

Ideal System Performance

To achieve the optimal performance for a given system one must ensure the optimal performance of each component of the system.The components addressed in this performance analysis are:

• Hardware utilization including CPU, I/O bottlenecks, memory
• Operating System and Storage configuration
• Oracle Database’s Physical and Logical design
• Client authentication and connection resolution
• Application Implementation
• Operations and Maintenance including backup, recovery, consistency checks, system housekeeping and others.

Each of these components is critically important to the optimal performance of the overall system.

The following are some of the issues related to each system component.

Hardware Resources:

The server hosting your Oracle database relies primarily on three hardware subsystems for efficient performance – CPU, Memory, and I/O.

A well-architected system will allow for ample distribution of workload across spindles, controllers and CPUs. If RAID is being used on-site, this will be analyzed for its effect on the database and applications. I/O configuration is the most flexible of the resources. Database and System Administrators can work jointly to balance the I/O load across all available resources.

The objective is to eliminate bottlenecks and sustain throughput for the life of the data- base server.

A large server may contain several gigabytes of main memory. The use of that memory has tobe carefully divided among the functions of the server – applications, database, and operating system. The goal is to allocate sufficient free memory to meet the peak demands of the workload, optimally tune Unix kernel memory-specific parameters where applicable, and dedicate a tunable chunk to the Oracle database and its many memory structures.

Operating System:

When Oracle is deployed on a Microsoft Windows platform, it behaves differently than one deployed in a Unix environment. Both operating systems must be closely examined and tuned to optimally support an Oracle database deployment. In both situations, the memory options and other Oracle database parameters must be tuned within the context of the respective operating system.

Oracle Database Server:

There are numerous items that relate to database performance. However, key elements of this score will reflect the use of indexes, table fragmentation and I/O balance, optimizer statistics, parameter settings, transaction logging, database layout, session activity, cache utilizations, and others. These areas will be investigated extensively based on the overall behavior of the database. Each component of the analysis will be detailed in the report that is delivered at the completion of the service.

Client Communications:

There are several optimizations available for client PCs connecting to your Oracle-based database management system. These will be evaluated for appropriateness in the environ- ment. Additionally, if clients are connecting to an Oracle database with older versions of the client software, there is tremendous opportunity for performance improvement through client library upgrades.

Application Implementation:

Database applications can often be enhanced through techniques that have been intro- duced since the application was originally designed. A discussion with the application developers will aid the DBA in making recommendations for improving the application.

While the above analysis strives to leverage hardware and software to its fullest potential, this area of the assessment strives to provide operational stability to the environment. Looking at batch jobs, backup and recovery strategies, logging strategies, upgrade strategies, and test platform capability will enable the DBA to provide recommendations for improving up time of the environment. During the Performance Analysis, clients will provide access to pertinent systems and key personnel to ensure a thorough and productive assessment. Key individuals within the environment will be the DBA, the System Administrator, the Application Team Leader,and others identified as subject matter experts.