DB performance: 120-point database health check across multiple databases

Did you know that you can easily monitor your database health via Quest^® Toad for Oracle^®? The database health check helps ensure database stability across your application needs. Toad for Oracle Pro DB Admin subscription can run 120 different database health checks, and then generate a report across multiple databases with the outcome of the health check and the differences from the prior report. The report can be automated and emailed to others.

120 different database health checks

The Toad for Oracle Pro DB Admin suite greatly assists with database stability by providing important application and security checks across your databases in the form of an HTML report.

These database health checks include:

• Configuration
• Performance
• Alert log
• Storage
• Schema
• Production
• Vulnerability assessment
• RAC
• Databases on vIrtual machines

We’ll review each of these areas, below. Note that the entire health check category can be selected or individual areas for monitoring can be selected.

Toad for Oracle database health check

• Configuration: This section includes a host of settings that are important to both database stability and keeping ahead of performance issues. It lists the default initialization parameters so when you run the differences only report, you can instantly see if anyone changed any database parameters. For database stability, this section also confirms the sizes of various buffer caches, block sizes, read count, cursor sharing (very important this does not change), and the like.
• Performance: This section covers system global area statistics, log file utilization (including wait times), and more.
• Alert Log: Generally, this is under-monitored. This health check option, combined with the differences report (Diffs Since Last Run) shows if any alerts are showing up. Again, this is for both application and database stability.
• Storage: Here, you’ll find useful topics such as levels of fragmentation, but the most important check is “List tablespaces with < 10% free space remaining.” This check takes into account the autoextend feature. Nothing stops a database faster than running out of space. Other checks, such as which temporary tablespaces are being used by which accounts, are just good database practice.
• Schema: This area covers useful performance issues (such as unanalyzed objects). For database stability, it also monitors those tables that are about to run out of allocated space.
• Production: This section monitors important PL/SQL, trace and Java features within the database.
• Vulnerability assessment: This health check monitors for lapses in security around grants and roles, and also for the use of system privileges and who has them. The use of the PUBLIC role should be cycled out of use as this opens a major security risk. This section monitors for the use of the PUBLIC role as well.
• The final two sections — RAC and Databases on virtual machines — monitor specific areas important to these types of database

Run across multiple databases

The database health check can be run across multiple databases Click the green “plus sign” circle and select the database connections you wish to monitor.

Database health check covers multiple databases

The database health check report

This report is organized much like the configuration section. The report defaults to being organized by database but can also be organized by reporting item. Items that need attention appear in red.

Database health check report

Differences from prior report

This is a powerful feature of the Toad database health check. This report shows the differences in the monitored items from the last time the report was run. Also, if you have assigned database stability items to be fixed/adjusted, these items can be monitored here to see if the work has been done, and what was done.

Database health check differences report

Email the report

Toad can email these reports, and there are options as to the content of the reports and whether they should be sent.

Database health check settings panel

Notice the parameters can be set to only email when there are differences (probably would be my choice) or when more database stability (bad) differences exist. Email notifications are set up using Toad Options --> Editor --> Email.

Email options panel

Automate the report

The database health check can be automated, and scheduled to run at specific times or on specific days. Automating the database health check saves time and ensures regular attention to health items. Selecting the types of reports you receive allows you to only be notified when database stability is at risk.

Database health check via automation designer

Closing

The Toad for Oracle DBA Suite can easily monitor your databases for possible issues. This monitoring helps ensure database stability by alerting you to fix issues before they become a problem. Toad allows for the database health check to be automated so it can run daily or weekly, depending on your application needs.

Related information:

Blog: DB performance: 6 benefits from maintaining database health and stability

Data sheet: Toad for Oracle DB Admin Module.

Blog: Toad SQL Editor Code Templates

Blog: Toad Explain Plan More Advanced Options

Blog: Quick and Easy SQL Optimization with Toad for Oracle

Video: VIDEO TUTORIAL: Toad Advanced Explain Plan Tips and Techniques

Video: Video: 'Dan's Dozen' Quick Toad Tips - Tip #5 - Advanced Explain Plans

Blog and Video Demo: Why an Oracle Ace thinks Toad® has the best SQL editor in the business

Oracle Documentation: Explain Plan Content

Oracle Documentation: Explaining a SQL statement: Basic Steps

Webcast: Five Cool DBA Features in Toad for Oracle Base Edition

Webcast: Top Five Use Cases for Developers for Toad for Oracle Base Edition

Chapter 10 “Toad as a SQL Tuning Tool” in Toad for Oracle Unleashed.

Dan Hotka has several course offerings in and around SQL Performance Tuning using the Toad for Oracle suite.

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

Dan Hotka has several course offerings that use Toad and Toad Data Point.