ITPub博客

首页 > 数据库 > Oracle > Proactively Avoiding Database and Query Performance Issues_1482811.1

Proactively Avoiding Database and Query Performance Issues_1482811.1

Oracle 作者:rongshiyuan 时间:2014-04-03 10:12:29 0 删除 编辑
Best Practices: Proactively Avoiding Database and Query Performance Issues (Doc ID 1482811.1)

In this Document

Purpose
  Ask Questions, Get Help, And Share Your Experiences With This Article
Questions and Answers
  Introduction
  Proactive advice
  Managing Change
  Collecting and Maintaining Baselines
  Upgrades
  Recommended Setup
  Patches
  Collect Optimizer Statistics
  Prepare to collect diagnostics
  Know how to interact effectively with Support
  Monitor for Potential Problems
  Use ADDM findings to drive Investigation
  Use Real Time SQL Monitoring to your advantage
  Useful references
  Troubleshooting Guides
  Performance Webcasts
  Performance and Scalability White Papers and Documentation
  Communities: Database Tuning and SQL Performance
  Discuss Proactive Avoidance!
References

Applies to:

Oracle Database - Personal Edition - Version 7.1.4.0 and later
Oracle Database - Enterprise Edition - Version 6.0.0.0 and later
Enterprise Manager for Oracle Database - Version 8.1.7.4 and later
Oracle Database - Standard Edition - Version 7.0.16.0 and later
Information in this document applies to any platform.
Checked for relevance7th September 2012


Purpose

This Document provides information on how to proactively avoid performance issues as much as possible.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Questions and Answers

Introduction

In the vast majority of cases, the Oracle database will perform well 'out of the box' with little user intervention. In the unlikely event of problems, there are things that you can do to mitigate those problems by being prepared and ready for them.
This document outlines a number of tips that you can use to mitigate the impact of any such issue.

Note that the Suggestions in this document are guidance as to recommended Best Practices. As such, some of the suggested features may require the purchase of additional licenses to open up extra functionality. That said some of the more general features are included within the the standard database license. Where licenses are required, this is noted in the document. 

Proactive advice

Managing Change

Collecting and Maintaining Baselines

With regards to pro-active avoidance, there are 2 different types of 'baselines' you should collect.

  1. Systemwide 'performance' AWR Baselines recording the resource usage of the system running normally

    If your system is performing well, then this is a good time to record that information to form a baseline for comparison against in the event of any issue in the future. The automatic workload repository (AWR) allows you to capture baseline data and enables you to specify and preserve a pair or a range of snapshots as a baseline. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely. Reports can then be created from these baselines allowing you to compare them with current situations to identify differences. The details for creating such a baseline can be found here:

    Oracle? Database 2 Day + Performance Tuning Guide
    11g Release 2 (11.2)
    Part Number E10822-04
    Chapter 8 Resolving Performance Degradation Over Time
    http://docs.oracle.com/cd/E11882_01/server.112/e10822/tdppt_degrade.htm#autoId0
     
    In addition to providing you with a comparison to a previous situation, baselines can provide a number of other benefits outlined in the Monitor for Potential Problems section below. There are more extensive suggestions to potential baselines you may wish to collect in the following article:

    Document 1477599.1 Best Practices: Proactive Data Collection for Performance Issues
     
    Use of the Automatic Workload Repository requires the Diagnostics Pack License. See:

    Document 1490798.1 AWR Reporting - Licensing Requirements Clarification
     
  2. SQL Plan Baselines to maintain plan stability and allow the restoration of a recorded plan in the event of a change

    With any change to a system, you want to create a situation where you can quickly and easily restore the previous performance in the event of change. The best way to ensure that stability is to use SQL Plan Management (SPM) to ensure that critical operations use known good plans and perform the same before and after the change. For systems with relatively static data and usage profiles, support would recommend that you record SQL Plan Baselines for key application queries so as to fix their access paths avoiding unexpected change. If data volumes and usage profiles are variable, then such baselines can be useful to provide a stable fallback position, but you may need the optimizer to be able to change plans to reflect changing data distributions. For details see:

    Document 1359841.1 Plan Stability Features (Including SPM) Start Point

    https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_1_of_4_creating_sql_plan_baselines

    Oracle? Database Performance Tuning Guide
    11g Release 2 (11.2)
    Part Number E16638-06
    Chapter 15 Using SQL Plan Management
    http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm#PFGRF007

    Note that for statistical changes, the database automatically retains the last 30 days worth of statistics, which can be restored in the event of a dramatic change. See:
    Document 452011.1 Restoring table statistics in 10G onwards

    Also Note that SPM is available as part of Oracle Database Enterprise Edition 11g, so no additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. See:
Upgrades

Upgrades can introduce significant changes in terms of software and hardware usage. Prior to any upgrade it makes good sense to ensure that provision for performance baselines are in place so that the system performs at least as well as the previous iteration. Additionally we recommend using Real Application Testing RAT to enable 'trial' testing on real workloads to ensure that the system will work as expected on transfer. There is an example of the capabilities of this suite of tools in the following Oracle Open World Presentation entitled "Load Testing" which provides a Case Study of Capture / Replay. There is further explanation in the manuals:

Oracle? Database Real Application Testing User's Guide
11g Release 2 (11.2)
Part Number E16540-06
Chapter 1 Introduction to Oracle Real Application Testing
http://docs.oracle.com/cd/E11882_01/server.112/e16540/rat_intro.htm#RATUG101

Chapter 7 Testing a Database Upgrade
http://docs.oracle.com/cd/E11882_01/server.112/e16540/spa_upgrade.htm#RATUG210

Additionally there are examples of RAT usage at the end of the Oracle Open World Presentation entitled "Tuning Toolkit for Advanced DBAs". Note: Real Application Testing is a licensed feature. See:

Document 1361401.1 Where to Find Information About Performance Related Features
Document 1464274.1 Master Note for Real Application Testing Option

Manual steps can also be followed to try to reduce the impact on queries as outlined in the following article:

Document 167086.1 Tips for Avoiding Upgrade Related Query Problems

Also see the upgrade planning section in:

Document 402983.1 Master Note: Database Performance Overview

Note that the Real Application Testing option requires the Real Application option license.See:

Oracle? Database Licensing Information
12c Release 1 (12.1)
Part number E17614-08
Chapter 2 Options and Packs
Oracle Real Application Testing
http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC154

Recommended Setup

It is important to use recommended settings for the system as much as possible. Your application providers may have specific requirements in this area. Refer to their specific documentation for advice regarding this.

Patches

Support recommends use of the latest version and patchset available to ensure that known issues are resolved. Specific performance related recommendations can be found here :

Document 1392633.1 Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
Document 1320966.1 Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results
Collect Optimizer Statistics

Good SQL performance is dependent on the presence and availability of accurate statistics on the objects being queried. By default (from Oracle 10g) statistics are gathered automatically providing adequate coverage for the majority of systems. Support also provides recommendations to enhance these statistics as required. See:

Document 1226841.1 How To: Gather Statistics for the Cost Based Optimizer
Prepare to collect diagnostics

If an issue occurs, it is no use collecting information about that issue after the event. Oracle will by default collect some information, but this may be insufficient for all issues. For information on suggested preparations in case diagnostics are required, see:

Document 1477599.1 Best Practices: Proactive Data Collection for Performance Issues
Know how to interact effectively with Support

In case you encounter problems you are unable to deal with or need to request support assistance see the following documents which outline how to get the most out of your interaction with Performance Support:

Document 210014.1 How to Log a Good Performance Service Request
Document 166650.1 Working Effectively With Global Customer Support
Document 1198303.1 Support Policy for Generic SQL Performance Issues

Monitor for Potential Problems

Use ADDM findings to drive Investigation

By default, the database collects various performance information that can be used to determine the cause of and resolve performance issues. The addition of the diagnosis and tuning packs to this delivers a built-in diagnostics and recommendations engine (Automatic Database Diagnostics Monitor (ADDM), provides automated tuning (SQL Tuning Advisor) and facilities to validate performance changes before implementation on production (SQL Performance Analyzer). By creating baselines and using these tools you can compare current performance to known good performance.

For example, running ADDM reports can provide information about potential problems and advice for dealing with them.  Additionally the Database Home page on Enterprise Manager Cloud Control 12c provides a summary of the ADDM findings that can be acted on before a critical situation develops.  The following image indicates where this information can be found:

EM Database Home Page showing ADDM Findings

There is a good example of the capabilities of this suite of tools in the following Oracle Open World Presentation entitled "Tuning Toolkit for Advanced DBAs" which provides SQL Performance Analyzer Usage and Case Studies. There is further explanation regarding SQL Performance Analyzer in the manuals:

Oracle? Database Real Application Testing User's Guide
11g Release 2 (11.2)
Part Number E16540-06
Chapter 2 Introduction to SQL Performance Analyzer
http://docs.oracle.com/cd/E11882_01/server.112/e16540/spa_intro.htm

Note that usage of these features requires the Diagnostic, Tuning and Real Application pack licenses. For more details see:

Document 1361401.1 Where to Find Information About Performance Related Features
Use Real Time SQL Monitoring to your advantage

The real-time SQL monitoring feature enables you to monitor the performance of SQL statements while they are executing and by default it automatically starts when a SQL statement runs parallel, or when it has consumed at least 5 seconds of CPU or I/O time in a single execution. Enterprise manager displays a useful report of the top SQL from the last hour which can be monitored for sudden changes and high usage SQL can be examined for improvements.

Real Time SQL Monitor Example

For more information, see:

Oracle? Database Performance Tuning Guide
11g Release 2 (11.2)
Part Number E16638-07
Chapter 10 Instance Tuning Using Performance Views
Section 10.4 Real-Time SQL Monitoring
http://docs.oracle.com/cd/E11882_01/server.112/e16638/instance_tune.htm#PFGRF94543

Note that SQL monitoring is a feature of the Oracle Database Tuning Pack. See:

Oracle? Database Licensing Information
12c Release 1 (12.1)
Part number E17614-08
Chapter 2 Options and Packs
Oracle Diagnostics Pack
http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC165
Oracle Tuning Pack
http://docs.oracle.com/cd/E16655_01/license.121/e17614/options.htm#DBLIC170

Alternatively you can do the analysis manually using the baselines you have created earlier setting up thresholds etc. for alerting you to potential issues before they occur based upon changes from this baseline position. See:

Oracle? Database 2 Day + Performance Tuning Guide
11g Release 2 (11.2)
Part Number E10822-04
Chapter 8 Resolving Performance Degradation Over Time
Comparing Current System Performance to a Baseline Period
http://docs.oracle.com/cd/E11882_01/server.112/e10822/tdppt_degrade.htm#autoId11

 

Useful references

Once potential issues have been identified, they can be prevented and resolved using the 'reactive' resources available. The following section outlines some of these. For the Database Performance Frequently Asked Questions FAQ, information regarding Performance Related Features or  issues involving individual SQL statements, see:

Document 1360119.1 FAQ: Database Performance Frequently Asked Questions
Document 1361401.1 Where to Find Information About Performance Related Features

Document 199083.1 Master Document SQL Query Performance Overview
Document 402983.1 Master Note: Database Performance Overview
Document 398838.1 FAQ: SQL Query Performance - Frequently Asked Questions

Troubleshooting Guides

There are a number of troubleshooting guides that can help resolve various issues:

Document 1543445.2 Troubleshooting Assistant: Troubleshooting Performance Issues
Document 1542678.2 Troubleshooting Assistant: SQL Performance Issues

Document 1528847.1 Troubleshooting: Avoiding and Resolving Database Performance Related Issues After Upgrade
Document 60.1 Troubleshooting Assistant: Oracle Database ORA-00060 Errors on Single Instance (Non-RAC) Diagnosing Using Deadlock Graphs in ORA-00060 Trace Files

Performance Webcasts

A series of Performance related webcasts, including topics such as SQLHC, SQLTXPLAIN and OSWBB, can be found here:

Document 1597373.1 Archive of Database Performance Related Webcasts

Performance and Scalability White Papers and Documentation

Performance and Scalability White Papers:

Oracle Open World Presentations containing useful case studies:

Document 1380043.1 Selected Performance Related Seminars from Oracle Openworld

Links to the main Tuning and Performance documentation:

Document 1195363.1 Database Performance and SQL Tuning Documentation on OTN

Communities: Database Tuning and SQL Performance

Communities have been set up for My Oracle Support (MOS) users moderated by Oracle. The goal of these communities is to exchange "Database Tuning" and "SQL Performance" knowledge and concepts . The community can be found via the following article:

Document 1383594.1 Collaborate With MOS Database Tuning Community Members
Document 1551797.1 Collaborate With MOS SQL Performance Community Members

 

References

NOTE:250655.1 - How to use the Automatic Database Diagnostic Monitor
NOTE:1477599.1 - Best Practices: Proactive Data Collection for Performance Issues
NOTE:1456176.1 - Oracle Database Advisor Webcast Schedule and Archive recordings
NOTE:210014.1 - How to Log a Good Performance Service Request
NOTE:166650.1 - Working Effectively With Support Best Practices
NOTE:1195363.1 - Database Performance and SQL Tuning Documentation on OTN
NOTE:1383594.1 - Collaborate with MOS Database Tuning Community Members
NOTE:167086.1 - Avoiding Upgrade Related Query Issues
NOTE:1464274.1 - Master Note for Real Application Testing Option
NOTE:1226841.1 - How To: Gather Statistics for the Cost Based Optimizer
NOTE:398838.1 - FAQ: SQL Query Performance - Frequently Asked Questions
NOTE:1377446.1 - * Troubleshooting Performance Issues
NOTE:1360119.1 - * FAQ: Database Performance Frequently Asked Questions
NOTE:1380043.1 - Selected Performance Related Seminars from Oracle Openworld (OOW)
NOTE:402983.1 - * Master Note: Database Performance Overview
NOTE:1392633.1 - Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
NOTE:1359841.1 - Master Note: Plan Stability Features (Including SQL Plan Management (SPM))
NOTE:1320966.1 - Things to Consider Before Upgrading to 11.2.0.2 to Avoid Poor Performance or Wrong Results
NOTE:1490798.1 - AWR Reporting - Licensing Requirements Clarification
NOTE:1361401.1 - Where to Find Information About Performance Related Features

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-1135189/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-11-24

  • 博文量
    798
  • 访问量
    3250014