10g Tuning; Tuning Oracle has Radically Changed
10g Tuning; Tuning Oracle has Radically ChangedAbstract
Tuning has radically changed in Oracle10g compared to earlier versions and the main tool to use is either Enterprise Manager Grid Control or something very close to it by a third party vendor. In Oracle10g & Oracle9i, if you could choose just two Oracle utilities to monitor and find performance problems of your system, those two utilities would be Enterprise Manager and Statspack or AWR Report. In Oracle9i (as of Oracle 8.1.6), you can use the STATSPACK utility to monitor the performance of your database. STATSPACK replaces the UTLBSTAT/UTLESTAT scripts available with earlier versions of Oracle and offers several significant enhancements to those scripts. In this paper, I would like to focus on some of the solutions to advanced issues regarding tuning Waits and also using Enterprise Manager to run the AWR Report. The waits portion of the paper listed here was a tough section to write and is definitely not for the beginner. Beginners should start with the Oracle documentation or the 10g Performance Tuning Book. See the Oracle Database 10g Performance Tips and Techniques book for a detailed look at this topic.
The Oracle Enterprise Manager (EM) product-suite continues to change over time, but this particular version covered here (10gR2), Oracle has taken a giant leap forward. With the statistics from the Automatic Workload Repository (AWR), this tool is now tremendously powerful. AWR snapshots are taken every hour by default and once the AWR snapshot is taken, the Automatic Database Diagnostic Monitor (ADDM) analysis occurs immediately (STATISTICS_LEVEL must be TYPICAL or ALL) via the MMON background process. The results of ADDM are also stored in the AWR and accessible via EM. The AWR Report uses these statistics. Oracle’s Enterprise Manager standard applications include a central management console and additional packs and many products have an additional cost (please check with Oracle for any price implications of different modules). Accessing the AWR requires the Diagnostics Pack and running SQL Tuning Sets requires the Tuning Pack. While this tool helps you tune things quickly, the benefits are achieved only when you know what’s under the covers. Under the covers are a lot of the same things you looked for related to performance in the past, but now you have a front end to quickly find and solve your problems.
页:
[1]