Oracle Database monitoring, tuning deliver performance goods

microworks - Fotolia

What Oracle performance tuning tools can and can't do for DBAs

Two Oracle experts discuss the tuning and monitoring capabilities of database performance management tools -- and the ongoing role that DBAs play in the process.

Database performance management is one of the more time-consuming tasks that Oracle database administrators face....

But for DBAs, properly tuning databases and then monitoring query performance and resolving problems is a must to ensure that Oracle Database systems meet the transaction processing and data warehousing needs of business users.

Ideally, database performance management tools can help DBAs "stay on top of things and identify bottlenecks before they become a problem," said Brian Hays, an Oracle data architect at database services company Remote DBA Experts LLC, which operates under the name RDX. In this Q&A, Hays and Chris Foot, RDX's vice president of delivery strategies and technologies, discuss Oracle performance tuning and monitoring trends, technologies and challenges.

How is the introduction of the Oracle Autonomous Database cloud service, which is supposed to be able to tune itself, changing the performance tuning and management work that Oracle DBAs do?

Chris Foot: There's more tuning automation in the data warehouse version of Autonomous Database than in the transaction processing one now. You still need to manually create tuning indexes and partitions more in the transaction processing product. And, overall, the automated features don't reduce the importance of database performance management or the work required to tune systems for optimal performance. We're in the early stages of automated tuning.

Right now, Oracle, Microsoft and Amazon all have very basic performance tuning automation. It's not to the point where they can review a workload, take a look at a SQL statement and how it's written, and rewrite that to take a better access path. Efficient query writing is still important, even with the automated tuning features in Oracle Autonomous Database.

Is Oracle performance tuning something that DBAs are ready to hand off to automated tools?

Chris Foot, vice president of delivery strategies, RDXChris Foot

Foot: For the immediate to midterm future, I think most DBAs will tell you that they're very suspicious of the automated tuning features. Oracle Autonomous Database has just been released, so it's going to take some time for administrators to trust its tuning recommendations, partly because we haven't gotten a lot of user feedback about the technology yet. Most of the information that we see on it is coming from Oracle now.

Do you always see a need for performance tuning and monitoring tools that let DBAs take a deep dive into database systems to identify and fix problems?

Foot: I think they'll go away in the cloud. A lot of the feature set for deep-dive drilldowns isn't available in the cloud version of Oracle Database because you don't need to do them. On premises, the DBA is responsible for analyzing data provided by the tools to identify where an issue is and then working with other people -- in IT operations, for example -- to improve performance. In the cloud, Oracle and other vendors take responsibility for that.

But there's always going to be a need to enhance on-premises tools for monitoring and tuning databases. They're not going away. Oracle and other vendors are trying to incentivize customers to move to the cloud as quickly as possible, but I think [on-premises and cloud systems] are going to coexist for a much longer time period than many people are saying. Companies have huge investments in their on-premises systems and a lot of systems that they don't want to move to the cloud.

What features and capabilities should users look for in database performance management tools from Oracle and third-party vendors?

Brian Hays, Oracle data architect, RDXBrian Hays

Brian Hays: The ability to set performance baselines and compare different workloads against those baselines is one. Another is the ability to review historical performance data: what the top SQL statements were [on resource utilization], for example. Then you can drill down further and run an analyzer against the data. You also want to be able to use the tools to resolve a problem at the click of a button without having to do much manual labor. So being able to quickly see what's going on in the system and then come up with a solution or take one that [a tool] recommends and approve it.

Are the Oracle performance tuning recommendations that the tools make usually trustworthy?

Hays: For the most part, the recommendations are reliable. But if you get the wrong SQL statement running, it can really have an impact on a database. There's still room for improvement in the tools. They're not right 100% of the time -- and in the 1% when they aren't right, you can really get into trouble in a system.

What are the biggest challenges that DBAs face on Oracle performance tuning and management?

Foot: DBAs are always going to get surprised. People will increase data volumes or the amount of data being accessed, or they'll introduce new SQL statements into a system that aren't written for optimal performance, or you'll get a mix of workloads that magically come together to impact the environment.

There's still room for improvement in the tools. They're not right 100% of the time.
Brian HaysOracle data architect, RDX

Hays: It's kind of Murphy's Law, where what can go wrong will go wrong. Then the challenge for DBAs is to be able to get in there quickly and come up with a solution -- because when you start to affect users, you don't have time to take all day to analyze a performance problem to see what's happening.

How should DBAs make sure they're ready to deal with surprises like that?

Hays: The best way to be prepared is to be proactive: Do historical analysis, check the baselines, know what workloads are running, set alerts so you can see when problems arise. That way, you're always ready to hop on the system and do the work that needs to be done.

Dig Deeper on Oracle database administration

Data Management
Business Analytics
Data Center
Content Management