An Oracle blog about Oracle Enterprise Manager and Oracle Management Cloud

How Database Performance Tuning Best Practices Will Help You Perform Better

Most DBAs are painfully familiar with what can be an onerous task, database performance tuning. Using the automation and the integrated features of Enterprise Manager can help relieve that pain. In a recent webinar, Alfredo took us through how to use the Oracle Enterprise Manager Diagnostics and Tuning options to speed the troubleshooting process including recommended resolutions.  If you like what you see, you may appreciate the Deep Dive Series on Enterprise Manager. These webcasts have been popular and rewarding to be a part of. Attendance has been strong, and interactions on Q&A have been active, in-depth, technical topics, evidence to the value of having experts online answering your questions during the webinar. Download the presentation, view the YouTube recording (56:07) of it and/or read the rest of this blog for a great summary.

Database Performance Tuning and Diagnostics Best Practices For On-Premises and Cloud

Database Diagnostics Pack
You can access Diagnostics and Tuning Packs functionality through Database Server APIs, SQL Developer, Enterprise Manager Express and maybe most frequently and most comprehensively, through the Enterprise Manager Cloud Control console where the workflow is intuitive and visual.

What you get is pre-built automation.

It’s real-time diagnostics and monitoring of all your subscribed databases. Yes, many features of these packs you can get in other ways, including writing your own scripts, which many do. The vast majority of those responsible for running many Oracle Database Enterprise Edition databases have found it’s more efficient and effective to use Enterprise Manager (EM). You may know Enterprise Manager is built in collaboration with the Database development team, which includes having core functionality built-in the Oracle Database kernel and exposed through the EM interface. Building on that deep information gathering is the efficiency of having the Diagnostics and Tuning Packs, and Real Application Testing integrated so they work together as one in a find, fix, and validate workflow.

A foundation for identifying relative performance is DB Time. Everything builds from there. The Automatic Workload Repository (AWR) provides a built-in, automatic performance statistics dataset. Typically you can compare a good period to a troubled period and compare the AWR reports. The AWR warehouse continues to keep AWR reports on a regular cadence. A very popular report most DBAs use AWR reports to get status and to do comparisons.  Alfredo goes through a few examples. Use Automatic Database Diagnostic Monitor (ADDM) to get recommendations. It’s designed using Oracle’s best tuning experts. While AWR covers the entire system, there’s Active Session History (ASH) which looks at an individual session. I’ll leave it to Alfredo to share his insights in the recorded session and you’ll see his slides as well. Suffice it to say the tools cover diagnostics for every database, including Exadata.

Diagnostics Pack Portfolio

Database Tuning Pack

More Automation. And Integration.

The Database Tuning Pack provides automatic and deterministic SQL tuning and It’s core functionality is built into the Oracle Database kernel and exposed through the EM console. As I mentioned above, and it’s worth mentioning again, it’s seamlessly integrated with the Diagnostics Pack, Real Application Testing and Automatic Tuning Optimizer functionality. This integration is one of the strengths of EM and why these packs are almost always used together.

Comprehensive Toolset

Both the Diagnostics and Tuning packs have been a staple for DBAs for over a decade. That’s not to say they haven’t evolved quite a bit over the years. Some of these tools might look familiar, and some have updates and new ones. You’ll see more in the webinar on each feature in more detail where we go through a number of problems and how to use the tools to evaluate and tune both performance and storage space.

Tuning Pack Portfolio

Integrated To Meet DBA Workflows Find, Fix, and Validate

I didn’t go into the Real Application Testing option, but it’s a good example of these tools integrated to simplify and speed the work entailed in finding, fixing, and validating fixes before pushing them live into production. This is shown below, each pane, another tool integrated together.

Find, Fix, and Validate Workflow


You can jump forward to the demo recording. It’s short but illustrative. 

Business Benefits

I like mentioning this new report, which did a great job of gathering metrics from a large number of our customers through a survey and in-depth interviews. You’ll find detailed analysis and quantified benefits laid out in a well-written report and charts. Like with any project, there is always someone who has to make tradeoffs on what to do and what not to. We’ve helped many companies go through an evaluation to help make a business case.

Pique Solutions

We have experts in the field who are versed in doing a consult on this very topic. If you are interested, contact your Oracle sales rep and let them know you are interested.

These are some of the resources made available in the presentation.


Join the community

Follow us on Twitter.

Subscribe to our YouTube channel

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.