Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdppt\tdppt_method012.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <title>Common Performance Problems Found in Oracle Databases</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" /> <meta name="date" content="2009-05-27T15:13:1Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content="Common Performance Problems Found in Oracle Databases" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E10822-01" /> <link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" /> <link rel="contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="prev" href="tdppt_method011.htm" title="Previous" type="text/html" /> <link rel="next" href="tdppt_auto.htm" title="Next" type="text/html" /> <script src="./callback.js" type="text/javascript"></script> <noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript> </head> <body> <div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr valign="bottom"> <td align="left"></td> <td align="center"><a href="tdppt_method011.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdppt_auto.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="i11221" name="i11221"></a><a id="TDPPT018" name="TDPPT018"></a></p> <div class="sect1"> <h1>Common Performance Problems Found in Oracle Databases</h1> <a name="BEGIN" id="BEGIN"></a> <p>This section lists and describes common performance problems found in Oracle databases<a id="sthref50" name="sthref50"></a>. By following the Oracle performance method, you should be able to avoid these problems. If you have these problems, then repeat the steps in the Oracle performance method, as described in <a href="tdppt_method007.htm#CIHFFHBI">"Using the Oracle Performance Method"</a>, or consult the appropriate section that addresses these problems:</p> <ul> <li> <p>CPU bottlenecks</p> <p>Is the application performing poorly because the system is CPU-bound? Performance problems caused by CPU bottlenecks are diagnosed by ADDM, as described in <a href="tdppt_auto.htm#CHDEBDAJ">Automatic Database Performance Monitoring</a>. You can also identify CPU bottlenecks by using the Performance page in Enterprise Manager, as described in <a href="tdppt_realtime020.htm#CHDFEGDA">"Monitoring CPU Utilization"</a>.</p> </li> <li> <p>Undersized memory structures</p> <p>Are the Oracle memory structures such as the System Global Area (SGA), Program Global Area (PGA), and buffer cache adequately sized? Performance problems caused by undersized memory structures are diagnosed by ADDM, as described in <a href="tdppt_auto.htm#CHDEBDAJ">Automatic Database Performance Monitoring</a>. You can also identify memory usage issues by using the Performance page in Enterprise Manager, as described in <a href="tdppt_realtime021.htm#CHDFAGCD">"Monitoring Memory Utilization"</a>.</p> </li> <li> <p>I/O capacity issues</p> <p>Is the I/O subsystem performing as expected? Performance problems caused by I/O capacity issues are diagnosed by ADDM, as described in <a href="tdppt_auto.htm#CHDEBDAJ">Automatic Database Performance Monitoring</a>. You can also identify disk I/O issues by using the Performance page in Oracle Enterprise Manager, as described in <a href="tdppt_realtime022.htm#CHDCCFDG">"Monitoring Disk I/O Utilization"</a>.</p> </li> <li> <p>Suboptimal use of Oracle Database by the application</p> <p>Is the application making suboptimal use of Oracle Database? Problems such as establishing new database connections repeatedly, excessive SQL parsing, and high levels of contention for a small amount of data (also known as application-level block contention) can degrade the application performance significantly. Performance problems caused by suboptimal use of Oracle Database by the application are diagnosed by ADDM, as described in <a href="tdppt_auto.htm#CHDEBDAJ">Automatic Database Performance Monitoring</a>. You can also monitor top activity in various dimensions—including SQL, session, services, modules, and actions—by using the Performance page in Enterprise Manager, as described in <a href="tdppt_realtime001.htm#CHDEIBAB">"Monitoring User Activity"</a>.</p> </li> <li> <p>Concurrency issues</p> <p>Is the database performing suboptimally due to a high degree of concurrent activities in the database? A high degree of concurrent activities might result in contention for shared resources that can manifest in the forms of locks or waits for buffer cache. Performance problems caused by concurrency issues are diagnosed by ADDM, as described in <a href="tdppt_auto.htm#CHDEBDAJ">Automatic Database Performance Monitoring</a>. You can also identify concurrency issues by using Top Sessions in Enterprise Manager, as described in <a href="tdppt_realtime003.htm#CHDEFEEH">"Monitoring Top Sessions"</a>.</p> </li> <li> <p>Database configuration issues</p> <p>Is the database configured optimally to provide desired performance levels? For example, is there evidence of incorrect sizing of log files, archiving issues, excessive number of checkpoints, or suboptimal parameter settings? Performance problems caused by database configuration issues are diagnosed by ADDM, as described in <a href="tdppt_auto.htm#CHDEBDAJ">Automatic Database Performance Monitoring</a>.</p> </li> <li> <p>Short-lived performance problems</p> <p>Are users complaining about short-lived or intermittent performance problems? Depending on the interval between snapshots taken by AWR, performance problems that have a short duration may not be captured by ADDM. You can identify short-lived performance problems by using the Active Session History report, as described in <a href="tdppt_transient.htm#CHDHHDHE">Resolving Transient Performance Problems</a>.</p> </li> <li> <p>Degradation of database performance over time</p> <p>Is there evidence that the database performance has degraded over time? For example, are you or your users noticing that the database is not performing as well as it was 6 months ago? You can generate an AWR Compare Periods report to compare the period when the performance was poor to a period when the performance is stable to identify configuration settings, workload profile, and statistics that are different between these two time periods. This technique will help you identify the cause of the performance degradation, as described in <a href="tdppt_degrade.htm#BABEIEDD">Resolving Performance Degradation Over Time</a>.</p> </li> <li> <p>Inefficient or high-load SQL statements</p> <p>Are any SQL statements using excessive system resources that impact the system? Performance problems caused by high-load SQL statements are diagnosed by ADDM, as described in <a href="tdppt_auto.htm#CHDEBDAJ">Automatic Database Performance Monitoring</a> and <a href="tdppt_sqlid001.htm#BABFJJCJ">"Identification of High-Load SQL Statements Using ADDM Findings"</a>. You can also identify high-load SQL statements by using Top SQL in Enterprise Manager, as described in <a href="tdppt_sqlid002.htm#BABECDJG">"Identifying High-Load SQL Statements Using Top SQL"</a>. After they have been identified, you can tune the high-load SQL statements using SQL Tuning Advisor, as described in <a href="tdppt_sqltune.htm#CAEBFCFJ">Tuning SQL Statements</a>.</p> </li> <li> <p>Object contention</p> <p>Are any database objects the source of bottlenecks because they are continuously accessed? Performance problems caused by object contention are diagnosed by ADDM, as described in <a href="tdppt_auto.htm#CHDEBDAJ">Automatic Database Performance Monitoring</a>. You can also optimize the data access path to these objects using SQL Access Advisor, as described in <a href="tdppt_sqlopt.htm#BABGHCAE">Optimizing Data Access Paths</a>.</p> </li> <li> <p>Unexpected performance regression after tuning SQL statements</p> <p>Is the performance of SQL statements degrading after they have been tuned? Tuning SQL statements may cause changes to their execution plans, resulting in a significant impact on SQL performance. In some cases, the changes may result in the improvement of SQL performance. In other cases, the changes may cause SQL statements to regress, resulting in a degradation of SQL performance.</p> <p>Before making changes on a production system, you can analyze the impact of SQL tuning on a test system by using SQL Performance Analyzer. This feature enables<a id="sthref51" name="sthref51"></a> you to forecast the impact of system changes on a SQL workload by:</p> <ul> <li> <p>Measuring the performance before and after the change</p> </li> <li> <p>Generating a report that describes the change in performance</p> </li> <li> <p>Identifying the SQL statements that regressed or improved</p> </li> <li> <p>Providing tuning recommendations for each SQL statement that regressed</p> </li> <li> <p>Enabling you to implement the tuning recommendations when appropriate</p> </li> </ul> <p>To learn how to use SQL Performance Analyzer, see <a href="topicid:RATUG166">Introduction to SQL Performance Analyzer</a>.</p> </li> </ul> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="tdppt_method.htm#CIHIEGEC">Oracle Database Performance Method</a></p> </div> </div> <!-- class="sect1" --> <!-- Start Footer --> <div class="footer"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr> <td align="left"><span class="copyrightlogo">Copyright © 2007, 2009, Oracle and/or its affiliates. All rights reserved.</span><br /> <a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td> <td align="center"><a href="tdppt_method011.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdppt_auto.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de