Looking Into The Oracle DBA Arsenal

Posted on 05-Dec-2013 by Craig Shallahamer, craig@orapub.com

Every successful performance-minded Oracle Database Administrator has an arsenal of tools. As DBAs, we are constantly on the lookout for tools that increase the value of our work.

I spend a lot of time thinking about Oracle Database performance tools lately. He has come to realize that every performance tool naturally fits into one of four categories: raw data extraction, formatted data, drill-down and automated analysis. A performance-minded DBA's tools arsenal will cover each of these categories.

Think about your tools for a moment… Are there any gaps? If so, Stori can fit into those gaps and compliments even the most advanced DBA tools. You may be surprised with what you discover!

Raw Data Extraction Tools

Every DBA has a set of raw data tools. OraPub has its OSM tool kit and there are probably 500 raw data tools available on the internet. Every DBA needs a way to access raw performance data. Because of this, we built into Stori the capability to extract raw performance data.

Here are a couple of quick examples:

How can I help you? get stats systimemodel db%cpu

db cpu, 570596627224

How can I help you? get stats sgastat_end %

fixed size, 2244192
variable size, 2315256224
database buffers, 13690208256
redo buffers, 26505216

The advantage of raw data tools is you can retrieve almost any piece of data you want. But that is also the problem! It’s all about pieces of data. An analysis is much more than pieces of data. As a result, formatted data tools can be very useful.

Formatted Data Tools

The title, Formatted Data is pretty harsh because there are some wonderful tools on the market that take raw performance data and format it nicely. Statspack and Automatic Workload Repository Report (AWR) reports and trace file analyzers fall into this category.

Formatted data tools help us to stick to the facts, that is, the data. It is very easy to resort to performance folklore and start guessing. Having the data easily available helps us to stay focused on the numbers.

Formatted data tools also help us process and understand the raw data by presenting it in a cleaner format. The problem is, there is so much data available it can be overwhelming. This is why the best formatted data tools are very specific. For example, there are fantastic SQL trace file analysis tools.

The advantage of formatted data tools is they can format and present to us massive amounts of raw data. But that’s also their challenge—lots and lots of information. So much so, it easily becomes overwhelming. Drill-down tools help lead us through the mass amount of formatted data.

Drill-Down Tools

There are many drill-down tools available today. In fact, most monitor-centric tools provide very nice drill-down capabilities. Confio Software’s Ignite product is one of the finest drill-down performance tools available on the market today. You can start from any perspective (e.g., wait event) and end up at another useful perspective (e.g., top program within the top wait event). Here is a sample drill-down screenshot.

The advantage of drill-down tools is you can quickly navigate through lots of data to what is important to you. But the problem is what is important to you may not be important to the users. What’s more, you may be spending your time on something that may not actually help the users. What is needed is some analytical guidance.

Automated Analysis Tools

While an automated analysis is no substitute for an Oracle performance expert, when the expert is not around they are priceless. Oracle’s Automated Database Diagnostic Monitoring (ADDM), which is affectionately known as called Adam, is the route Oracle Corporation has taken to help the non-expert. It is an impressive product. Here is an example excerpt of an ADDM report:

FINDING 1: 31% impact (7798 seconds) 
------------------------------------ 
SQL statements were not shared due to the usage of literals. This resulted in additional hard parses which were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 31% benefit (7798 seconds)
  ACTION: Investigate application logic for possible use of bind variables
    instead of literals. Alternatively, you may set the parameter 
    ":cursor_sharing” to "force”
  RATIONALE: SQL statements with PLAN_HASH_VALUE 3106087033 were found to be
    using literals. Look in V$SQL for examples of such SQL statements.

Stori is obviously an automated analysis tool, but with a unique and powerful twist. Stori not only finds problems and makes ranked recommendations, but it turns the performance situation into an understandable story. It’s the story formation, the distilling of the performance situation down to the core, while leading you through the process. This is where Stori shines. There is nothing quite like it.

Compare the above ADDM output with this textual dialog with Stori.

How can I help you? Is there a problem?
There is a very serious CPU resource shortage.

Simplicity rules here. To understand why there is a CPU resource shortage and what specifically to do about it (i.e., recommendations) just ask Stori for the analysis. Here is an example of just one aspect of a visual Stori analysis.

At the time of this posting Stori is available on Oracle Standard Edition and ADDM is only available on Oracle Enterprise Edition. Stori will eventually be available for Oracle Enterprise Edition.

The DBAs Full Arsenal

I think every DBA needs quick and unlimited access to raw performance data, data sub-setted and nicely formatted, visual drill-down tools and automated analysis capabilities. This is why I don't tell people to stop using SQL*Plus or to stop using Confio’s Ignite product and only use Stori. It doesn't make sense. What I recommend is a DBA's suite of tools that encompasses all four areas. This results in a very well rounded and robust set of tools empowering the DBA to handle just about any performance problem that comes their way.

All the best in your Oracle database performance tuning work!

Craig.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

First Posting How To Tell If The IO Subsystem Reads Are Struggling Looking Into The Oracle DBA Arsenal