In this Document
|Ask Questions, Get Help, And Share Your Experiences With This Article|
|Questions and Answers|
|Collecting and Maintaining Baselines|
|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|
|Performance and Scalability White Papers and Documentation|
|Communities: Database Tuning and SQL Performance|
|Discuss Proactive Avoidance!|
This Document provides information on how to proactively avoid performance issues as much as possible.
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.
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.
With regards to pro-active avoidance, there are 2 different types of 'baselines' you should collect.
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:
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:
Manual steps can also be followed to try to reduce the impact on queries as outlined in the following article:
Also see the upgrade planning section in:
Note that the Real Application Testing option requires the Real Application option license.See:
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 :
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:
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:
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:
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:
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:
Note that usage of these features requires the Diagnostic, Tuning and Real Application pack licenses. For more details see:
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.
For more information, see:
Note that SQL monitoring is a feature of the Oracle Database Tuning Pack. See:
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:
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:
There are a number of troubleshooting guides that can help resolve various 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
A series of Performance related webcasts, including topics such as SQLHC, SQLTXPLAIN and OSWBB, can be found here:
Performance and Scalability White Papers:
Oracle Open World Presentations containing useful case studies:
Links to the main Tuning and Performance documentation:
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:
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 220.127.116.11 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 18.104.22.168 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/，如需转载，请注明出处，否则将追究法律责任。