X

Pat Shuff's Blog

Try Oracle Cloud Platform For Free
  • PaaS
    June 15, 2016

database option - diagnostics

Patrick Shuff
Keeping a database tuned is a full time job. Automating some of these tasks helps a DBA support more databases and reduce the time required to generate the data. Automatic report generation is a good way of getting these reports. One of the questions I constantly get asked is what is included with PaaS or DBaaS and what is done for me. With DBaaS, database tuning and diagnostics are not part of the services provided. The DBA still needs to look for processes that are holding locks. The DBA still needs to look for run away sql statements. The DBA still needs to look for alternate execution plans and sql tuning to make the database run faster. The Diagnostics Pack is a key tool to help with this. The services that are included with DBaaS include database and operating system patching, making sure that the database is restarted if it stops (unless you issue a shutdown command), and performs automated backups that you can tweak and tune in frequency and amount of data stored. Tools like ADDM, ASH, and AWR are still required by the DBA and can be accessed from the command line through sqlplus, using SQL Developer, or Enterprise Manager. In 10g, many diagnostic tools like ASH and AWR were embedded into the database. In 11g they were automated to collect the data into a central location. In 12c reports were automated so that DBAs did not need to schedule the jobs and generate reports late at night to look at in the morning. Many of these features started in Enterprise Manager but got migrated into the database. There was some controversy with the 10g release because it did impact performance compared to the 9i release but that seems to have gone away with the 11g and 12c releases. An overall architecture of the performance collection can be seen in the diagram below.

The key features to the Diagnostic Pack for the database include

  • Active Session History (ASH)
  • Automated Workload Repository (AWR)
  • Automatic Database Diagnostic Monitor (ADDM)
  • Enterprise Manager Performance reporting
  • SQL Developer Performance reporting

More information on all of these topics can be found in a variety of locations. Most of the information in this blog can be found at

ASH

ASH statistics are enhanced to provide row-level activity information for each captured SQL statement. This information can be used to identify which part of the SQL execution contributed most significantly to the SQL elapsed time. The ASH views, accessible to DBAs, provide historical information about the active sessions in the database.

AWR

The Automated Workload Repository (AWR) reports provide baseline statistics for the database and show the database performance during specified intervals. A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. Several types of baselines are available in Oracle Database: fixed baselines, moving window baselines, and baseline templates.

ADDM

DBAs and developers can use ADDM to analyze the database and generate recommendations for performance tuning. In Oracle 11g, ADDM was enhanced to perform analysis on database clusters on various levels of granularity (such as database cluster, database instance, or specific targets) over any specified time period.

You can access ADDM through SQL Developer or Enterprise Manager. To access these functions you must first enable the Diagnostics Pack which allows you access to the reports.


You can manually run the ADDM report with a command line

@?rdbms/admin/addmrpt.sql

If you look at SQL Developer and go to the DBA navigation link and expand the database for Performance you can see the AWR and ADDM reports. Expanding on these links shows you the various reports. For the ADDM, for example, you can quickly see if there is a recommendation or not and drill down into the recommendation.


If we click on one of the finding with a Yes in the recommendation column we can look at the report and recommendations that it has. For the example we found it had two suggestions for tuning. Below are samples of this report and the two recommendations.



We can look at similar information from Enterprise Manager by navigating to the Performance page and selecting the report that we want.

Typical AWR report output usually contains an incredible amount of information about an Oracle database’s application workload behavior. When a database instance is suffering from a gc buffer busy wait event during the time period chosen for the AWR report, however, that event will usually surface as one of the Top 5 Timed Events

With AWR you can create a baseline and look at spot performance or variation from a baseline. Page 21 of Diagnostic Pack Presentation does a good job of describing how to read and understand an AWR Report. The blog Bash DBA does a good job of walking through an AWR and looking for issues and problems in a system.

In summary, we are not going to dive deep into AWR and ADDM diagnostics. Knowing how to do this differentiates a high paid DBA from a junior DBA. There are classes through Oracle Education - 12c Performance Management and Tuning and other vendors that teach you how to understand this option as well as the books we mentioned above and certification exams to help show that you know this material. It is important to note that all of these tools work with platform as a service (Oracle and Amazon RDS) as well as infrastructure as a service and on-premise installations. The key difference is that the diagnostic and tuning tools are bundled with the High Performance and Extreme Performance Editions. For IaaS and on-premise you need to purchase a perpetual license that we discussed a few blogs ago.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha

Recent Content