ORACLE資料庫常見性能問題【Common Performance Problems Found in Oracle Databases】

ORACLE資料庫常見性能問題【Common Performance Problems Found in Oracle Databases】

本部分列出了ORACLE資料庫常見的性能問題，通過使用ORACLE性能調整方法，你可以避免這些問題。如果你有這些性能問題，重複ORACLE 性能調整方法的步驟，直到解決。具體的調整方法見後面的英文連結。也可以檢視本手冊的其它部分。【This section lists and describes common performance problems found in Oracle databases. 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 "Using the Oracle Performance Method", or consult the appropriate section that addresses these problems:】

CPU瓶頸【CPU bottlenecks】

資料庫系統性能低下是由于機器的CPU限制引起的嗎？由CPU瓶頸引起的性能問題可以通過ADDM診斷，如後面第一個英文連結，也可以通過企業管理器（OEM）來檢測CPU的瓶頸，如後面第二個英文連結。【Is the application performing poorly because the system is CPU-bound? Performance problems caused by CPU bottlenecks are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify CPU bottlenecks by using the Performance page in Enterprise Manager, as described in "Monitoring CPU Utilization".】

較小的記憶體【Undersized memory structures】

ORACLE資料庫的記憶體結構如SGA，PGA，CACHE等大小合适嗎？由記憶體大小引起的資料庫性能問題可以通過ADDM診斷，如後面的第一個英文連結，也可以通過企業管理器（OEM）來診斷。如後面的第二個英文連結中的描述。【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 Chapter 3, "Automatic Database Performance Monitoring". You can also identify memory usage issues by using the Performance page in Enterprise Manager, as described in "Monitoring Memory Utilization".】

I/O吞吐能力【I/O capacity issues】

I/O讀寫子系統的性能是你期望的嗎？由I/O子系統引導起的吞吐量性能問題可以通過ADDM診斷 ，也可以通過企業管理器來診斷。【Is the I/O subsystem performing as expected? Performance problems caused by I/O capacity issues are diagnosed by ADDM, as described in Chapter 3, "Automatic Database Performance Monitoring". You can also identify disk I/O issues by using the Performance page in Oracle Enterprise Manager, as described in "Monitoring Disk I/O Utilization".】

應用程式問題【Suboptimal use of Oracle Database by the application】

應用程式對資料庫系統的利用合理嗎？類似過多的、不斷重複的資料庫連接配接，過多的SQL解析這樣的問題，都會大大降低應用系統及資料庫系統的性能。這方面的性能問題可以由ADDM來檢測，診斷。也可以通過企業管理器OEM的性能頁面來檢測活動比較TOP的SQL，會話，子產品，服務等。具體見後面的英文連結。【 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 Chapter 3, "Automatic Database Performance Monitoring". 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 "Monitoring User Activity".】

并發連接配接問題【Concurrency issues】

不理想的資料庫性能是由過高的并發操作引起的嗎？過多的并發操作可能會導緻對共享資料的連接配接問題，導緻其它會話會因為不能及時得到所需要的資源、鎖，緩存等而處于等待狀态。此并發操作引起的性能問題可以通過ADDM來診斷。你也可以通過企業管理器中的TOP Session頁面來檢測。具體見後面的英文連結。【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 Chapter 3, "Automatic Database Performance Monitoring". You can also identify concurrency issues by using Top Sessions in Enterprise Manager, as described in "Monitoring Top Sessions".】

資料庫配置問題【Database configuration issues】

資料庫的配置是否能達到期望的性能水準呢？比如，是否有比較明顯的不合适的日志檔案大小問題，歸檔問題，大量的系統檢測點，或者不合适的參數設定？因資料庫配置引起的性能問題，可能通過ADDM來檢測，診斷。具體見後面的英文連結。【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 Chapter 3, "Automatic Database Performance Monitoring".】

無常的、短暫的性能問題【Short-lived performance problems】

使用者在抱怨無常的，莫名的性能問題嗎？這種瞬間的性能問題通常不能夠被ADDM捕獲。你可以通過使用活動會話曆史報告來檢測發現這種瞬間的性能問題。具體見後面的英文連結。【（actiive session history report）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 Chapter 7, "Resolving Transient Performance Problems".】

資料庫性能夠日益下降【Degradation of database performance over time】

随着時間的推移，資料庫的性能是否在明顯的下降呢？你或你的使用者是否覺的你的資料庫性能是否不在像6個月以前的性能那樣好嗎？你可以生成一個AWR對比日期報告來對比兩個不同時期（性能好的時期、性能不好的時期）的性能差别。此技術可以幫你檢測出性能下降的原因。具體見後面的英文連結。【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 Chapter 8, "Resolving Performance Degradation Over Time".】

效率低的或高負載的SQL語句【Inefficient or high-load SQL statements】

是否有一些SQL語句過多的占用着系統的資源，以緻于影響着系統的性能？由高負載SQL語句引起的性能問題可以通過ADDM來診斷，檢測，具體見後面的第一個、第二個英文連結。你也可以通過使用企業管理器中的頂極TOP SQL語句來檢測出高負載的SQL語句。具體見後面的第三個英文連結。在檢測出這些SQL語句之後，你可以通過使用SQL Tuning Advisor優化指導來優化這些高負載的語句，具體見最後一個英文連結。【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 Chapter 3, "Automatic Database Performance Monitoring" and "Identification of High-Load SQL Statements Using ADDM Findings". You can also identify high-load SQL statements by using Top SQL in Enterprise Manager, as described in "Identifying High-Load SQL Statements Using Top SQL". After they have been identified, you can tune the high-load SQL statements using SQL Tuning Advisor, as described in Chapter 10, "Tuning SQL Statements".】

對象連結【Object contention】

是否有一些資料庫對象引起了性能瓶頸問題，當他們進行持續讀寫的時候？這方面的性能問題可以通過ADDM來診斷，具體見後面的英文連結。你也可以優化這些資料的通路路徑，通過使用SQL Access Advisor通路指導。具體見後面的第二個英文連結。【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 Chapter 3, "Automatic Database Performance Monitoring". You can also optimize the data access path to these objects using SQL Access Advisor, as described in Chapter 11, "Optimizing Data Access Paths".】

在調整SQL語句之後出現不可意料的性能下降【Unexpected performance regression after tuning SQL statements】

SQL語句的性能是否下降了，在它們被調整之後？調整SQL語句可能會引起SQL語句的執行計劃的改變，進而導緻性能的變化。在某些情況下，你所做的變化可能會降低你的系統的性能，這是正常的。是以，在你對生産系統作出改變前，你應該通過SQL分析工具(SQL Performance Analyzer)在測試系統上進行預調整。具體見後面的英文連結。【Is the performance of SQL statements degrading after they have been tuned? Tuning SQL statements may cause changes to execution plans of SQL statements, 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. Before making changes on a production system, you can analyze the performance impact from tuning SQL statements on a test system by using SQL Performance Analyzer, as described in Chapter 12, "Analyzing SQL Performance Impact".】