Tuesday Mar 19, 2013

Improve Database Performance

Untitled Document

Best Practices: Proactive Data Collection for Performance Issues


A common problem for analysis of performance issues is having the right diagnostic tool or trace in place during the first occurrence of the problem. This is not always possible, but chances for a quicker resolution and potential Root Cause Analysis (RCA) are possible if you adopt a proactive best practice around performance data collection.

The best practice described here includes a methodology around data collection:

  • Top-Down approach to data collection
  • Establishing multiple baselines
  • Having the right tools already installed and running before the problem occurs
  • Deploying specialized tools for unstable environments

The performance of the Oracle database depends upon the performance of the hardware and operating system that it runs on. This is why it is important to consider operating system metrics as part of a "big picture" when diagnosing performance problems. Oracle support has a utility named OSWatcher Black Box, which will capture information about the operating system. We strongly recommended all customers run OSWatcher Black Box because it provides a "look back" capability on the OS. If for example, we had a node eviction at 2:00 in the morning, we can look back in time to see what happened on the OS at 2:00 or more importantly, what happened at 1:59, just prior to the actual event.

Document 301137.1 OSWatcher Black Box User Guide (Includes: [Video])

If we drill down from the operating system into the different layers of Oracle, we can take advantage of utilities which have been available since version 10g. The Automatic Workload Repository (AWR) and Active Session History (ASH) are two very important utilities to allow us to see what is happening on the database both from a holistic point of view and also at the session level. These 2 utilities are part of the Oracle Diagnostic Pack and require a special license to use. In order to obtain the most effective diagnostics possible, it is strongly recommended that an Oracle Diagnostic Pack license is obtained.

Document 1363422.1 Automatic Workload Repository (AWR) Reports
Document 243132.1 Active Session History (ASH)

The AWR reports can be taken at regular intervals and then used to determine what if any performance problems exist on the database. The use of ASH allows us to see specifically what individual sessions are waiting on or what resources these sessions are consuming.

Red path The good news is that if you proactively take advantage of these tools, it may be possible to diagnose a system or database hang, just by having these tools in place. Many times bug signatures are available within AWR and hangs can be matched to these bug signatures. This may allow for a diagnostic analysis without the need to take more intrusive diagnostics such as systemstate dumps, hanganalyze dumps, etc.

It is very important for you to establish baselines using OSWatcher Black Box and AWR. Baselines should be used as points of reference and are extremely valuable for differential analysis when problems arise. AWR has a compare utility when can be run on two different reports such as your baseline report and the report from the period when the problem actually occurred. This facilitates quick identification of any differences in statistics between the two reports and allows us to focus and identify specific problems. Baselines should be taken during different phases of your business cycle. Examples would be at the busiest time of the day, batch reporting at night, monthly or quarterly end of business cycles, etc.

In addition to OSWatcher Black Box, AWR and ASH, there are other support tools worth considering when it comes to being proactive.

Oracle support has a tool which automates the collection of hang diagnostics. This tool, hangfg, is a simple UNIX shell script that allows users to choose how intrusive they want hang diagnostic traces like systemstate dumps and hang analyze dumps to be on their production servers. The complex syntax and kind of diagnostic trace is embedded in the utility. This frees you from having to know the kind and level of trace and the cryptic syntax to generate these traces. All you need to do when experiencing a hang is run the shell script, which will collect the appropriate diagnostic trace depending upon how intrusive you choose to make it.

There are 3 levels:

  1. Light impact on system. This option collects 2 hanganalyze level 3 traces and then determines whether it can also collect 1 hanganalyze level 4 trace with minimal impact to the system. If so, it collects the hanganalyze level 4 trace. If not, it does not collect an additional trace file.
  2. Medium impact on system (default value). This option collects 1 hanganalyze level 3 trace and then determines whether it can also collect 2 hanganalyze level 4 traces with minimal impact to the system. If so, it collects the 2 additional hanganalyze level 4 traces. If not, it collects an additional hanganalyze level 3 trace. This option also collects 1 systemstate level 266 trace.
  3. Heavy impact on system. This option collects 2 hanganalyze level 4 traces and 2 systemstate level 266 traces.

Document 362094.1 HANGFG User Guide

SQL Tuning Tools

Oracle support also has specialized tools around SQL tuning. SQLT and SQLHC are recommended best practice tools to have downloaded and installed on you systems if possible. SQLT is the best diagnostic tool support has to resolve complicated SQL tuning issues. Both tools extract statistical and environmental data from the database. SQLT is a far more extensive diagnostic tool but does require a separate scheme to be installed on the database. SQLHC does not have a footprint on the database and provides quick health check information. Both tools can be explored in more detail by clicking on the links associated with them.

Document 215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly
Document 1366133.1 SQL Tuning Health-Check Script (SQLHC)

Hopefully these tips will encourage you to become more proactive with data collection. Our ultimate goal is to reduce your resolution time if you have a performance problem.

Related Resources: Advisor Webcast Recordings

Tuesday Mar 05, 2013

Improve Your Hardware Support Experience

Untitled Document

Use These Hardware Essentials


Oracle Hardware customers who are running Oracle Solaris or Oracle Solaris x86, can improve their support experience. Oracle Support recommends these three Hardware Essentials to all Oracle Hardware customers:

  1. Download and install the Services Tools Bundle. This bundle has Oracle Explorer Data Collector, Oracle Remote Diagnostic Agent, Oracle Serial Number in EEPROM (SNEEP), and Oracle Automatic Crash Dump Tool (ACT). The bundle is available for Solaris and Solaris x86, release 8 onwards.
    1. Download—Log into My Oracle Support and follow the links on this document to download the latest.
    2. Learn More—Take a few minutes to read the Oracle Explorer Information Center. Ensure you set up the explorer so that it regularly sends data to Oracle. As it is the data from these tools that help to improve your support experience.

  2. Set up Oracle Explorer. How healthy are your Oracle Systems? Did you know that once you set up Oracle Explorer to send data to Oracle you can view two reports that will help you understand their health? The first is a mission critical report and the second is the Systems Risk Index based on the Sun Alert Program.  If the Oracle explorer was correctly configured, and you have access to the same Support Identifier as the system you would like to review, then you can get started today. If you cannot view reports please verify the following setup:

    1. Validate the chassis serial number is on contract with a valid Support Identifier. Using the Assets Tab, enter the serial number if it does not come back then you don’t have access to the CSI it’s associated with or it’s not on contact.
    2. Validate in My Oracle Support, via My Account that you have the same Support Identifier associated to your account as the chassis serial number.
    3. Install Oracle Services Tools Bundle for Solaris on any system you are interested in viewing reports on.
    4. Set up Oracle Explorer ensure the serial number is entered into the appropriate Oracle Explore Data Collector Field and that it matches exactly to the Serial Number as displayed in the Assets Region of the Systems Tab on My Oracle Support.
    5. Configure the explorer output to be sent to Oracle every 30 days.

  3. Enable Automatic Service Request (ASR) for known hardware issues. This is a secure, scalable, customer installable software solution that automatically generates a Service Request for specific hardware faults. Available for all ASR capable systems that are covered by an Oracle Support Contract.

    Learn MoreReview the ASR page for documentation and downloads.


Let the 'Get Proactive' team help you achieve resolution faster; simplify your daily operations; reduce risks and maximize uptime through preventative maintenance.

How? By sharing our own best practices, tips, and tricks. Get on the ‘Insider's Track.’

Stay Connected


« March 2013 »