Oracle Health Check Tips

The goal of the Oracle health check is to evaluate the Oracle environment, recommend performance related improvements, assist with implementing improvements as needed and mentor the existing client’s staff on Oracle tuning health and best practices. BC Oracle health checks utilize proprietary scripts that we spent years developing, specialized techniques that get right to the heart of any Oracle issue. The basic areas of an Oracle health check include:

• Best Practices health check
• Security vulnerabilities health check
• Oracle environment health check (CPU, disk, network)
• Oracle backup & recovery, disaster recovery review
• Oracle instance health check
• Oracle objects health check
• Oracle SQL environmental health check

Oracle health check for standard best practices

Part of any Oracle health check is certifying that the Oracle database meets Oracle's standards for best practices.

• Oracle Architecture best practices (OFA, SAME, RAID)
• Oracle Instance best practices (parameters, configuration)
• Oracle Patch management
• Oracle disaster recovery / replication
• Oracle real application clusters (RAC) best practices
• Oracle security best practices (users, roles, profiles, VPD.)
• Schema Management best practices
• Advanced Features best practices (Partitioning, IOT’s, MV’s, Streams, etc.)
• Monitoring & Diagnosis best practices (alerts, logs, trace files, events, etc.)
• Data Warehouse best practices
• Backup & Recovery best practices
• Naming Conventions best practices

Upon request, BC can also perform an Oracle health check for application architecture and application code.

Oracle best practices for the hardware environment

This phase of the Oracle health check reviews the external hardware environment. Oracle does not run in a vacuum and it's important to review every hardware component for compliance with Oracle standards:

• Disk health - This health check task reviews the disk striping (RAID level) and samples disk latencies for probe and multi-block reads. The health check also evaluates disk enqueues and overall latency by spindle to locate hot spots and bottlenecks.  We also examine hardware Raid (3Ware IDE Raid) and controllers.
   
• Network health - This section reviews the network topology and conforms that the TCP/IP protocols are installed appropriately for Oracle. We also verify the proper settings for the Oracle transparent network substrate (TNS) and check to ensure that all packets are optimized.
   
• CPU health check - We survey the processor activity at peak loads looking for CPU enqueues and processor-related system bottlenecks.
   
• RAM health check - We check all aspects of the server RAM configuration identifying areas of RAM swapping, memory leaks and un-used RAM resources.

Oracle health check for the operating system environment

We check all aspects of the OS environment including kernel parameters (registry for Windows) and evaluate OS optimization for these areas:

• Kernel parameters
• CPU configuration
• Software Raid (Linux)
• Filesystem integrity (AIDE, Tripwire)
• SAN and NAS settings
• DNS, FTP, HTTP (Connection and content), pop3/imap,
• Security - SMTP, ssh
• Filesystem survey - mounts points

Oracle instance health check

The Oracle instance health check includes a review of the environmental optimization, instance configuration and parameters, and object and SQL optimization.

Oracle environment health check

In this part of the Oracle Health check we focus on the Oracle internal parameters and certify Oracle best practices and optimization.

• Initialization parameters - A review of over 250 parameters governing the behavior of the SQL optimizer, SGA, PGA, etc.
   
• SQL*Net - A review of all configuration and networking configuration (sqlnet.ora, listener.ora, protocol.ora)
   
• Specialized features - A review of optional features such as Oracle Streams, replication, failover and disaster recovery configuration.

Health check Review of Oracle objects

Oracle object parameters control how tables and indexes are optimized, and the BC Oracle health check verifies that all key tables are the appropriate settings for Pctfree, Pctused, Freelists and Freelist Groups.

Oracle tablespace health check

The BC health check examines all tablespaces for proper configuration including, bitmap freelists (automatic segment storage management) and temporary tablespace settings.

Health Check Performance review

The best approach in an Oracle health check is to identify the component that is the bottleneck and then drill-down and identify the component resource that is responsible for the latency. The bottleneck may be hardware related (CPU, RAM, Disk I/O, or Network shortages), or software related (locks, latches or contention).

In an Oracle Real Application Cluster environment, the health of the cache fusion interconnect and the state of block pinging is also examined.

The general methodology for the Oracle performance health check utilizes STATSPACK/AWR for time-series performance analysis, setting the foundation for workload-based Oracle optimization.  We check over 100 aspects of Oracle performance including all of the important health metrics:

• DB Block Buffer Usage
• Database IO Profile
• Database IO Timing Report
• Shared Pool Status Reports
• Latch Contention Report
• Rollback Segment Health
• Library Caches Report
• Clustering Factor Analysis
• RAC configuration
• RAC pinging and cache fusion block access reports
• Adjusting SGA RAM Resources
• Pinning Objects in SGA (determine tables, procedures, etc…)
• Data buffer utilization
• Automated KEEP pool assignment
• SQL Tuning by identifying the Top Resource consumers
• Identify unnecessary Large-table full-table scans (LTFTS)

Oracle SQL health check analysis

The final step is examining the top SQL statements for optimization.  Common causes of sub-optimal SQL is missing materialized views and indexes, especially function-based indexes

The final health check report

The final deliverable of the BC Oracle health check is a complete report of findings and recommendations. For each finding there will be a recommendation that will include the expected improvement on performance and the estimated effort to implement the recommendation.

This consulting engagement is for a database health check taking no less than 15 hours of work, the maximum number of hours to be determined between the client and Burleson Consulting. Considering such factors as database complexity and time that maybe devoted to reviewing results with the client (dba staff, management) and some possible implementations of recommendations as time allows.

The BC Oracle Health Check can be performed by Burleson Consulting either remotely or on-site. BC consultants have been Oracle performance tuning for more than a decade and we are experts in quickly identifying and correcting Oracle performance problems from OLTP applications to VLDB. Our consultants are all senior level Oracle certified DBAs with extensive database tuning experience.