Dontcheff

On Return On Investment and New Database Management Features in Oracle Enterprise Manager Grid Control 11g

In DBA on May 24, 2011 at 23:00

“A picture is worth a thousand words”. This picture is worth a thousand dollars. Times ten. And times ten again (at least).

Or even more depending on the number of packs you are using and the number of CPUs used in the DB stack. However, it is still worth having it! It is the best thing Oracle has ever developed. There is RAC, there is Exadata but these products are incomparable with Grid Control.

Forrester Research was able to estimate costs, benefits, and risks for a composite sample Organization. Their findings indicate that the Organization should achieve a three-year risk-adjusted NPV of $1,427,320, a very favorable 100% ROI, and a payback period of 16 months. It is very close to what I have experienced, witness and calculated.

Being one of the first Grid Control customers in the world and as far as I know the first (company) to start using it in production in Europe, I must say that DBA productivity can grow up to 5 times with DBAs managing more than 150 databases which is much, much above the industry standards and average (picture below is from Forrester):

I recommend that you also check the 9 papers under featured resources.

Of course, Grid Control is free to use if no packs are used but that is where the functionally is. Here are all the database packs for Database Management:

There is a very interesting thread on wiki.oracle.com about why (some) companies do not use EM Grid Control. I agree that it is time for Oracle to make an official ROI Calculator as blogs and forums might not be enough convincing for the decision makers. Who usually are not the DBAs 🙂

11g comes with several new improvements. For details, check the following article by Scott McNeil: What’s new in Oracle Enterprise Manager 11g for Oracle Database 11g Release 2 and Exadata V2 or directly the Oracle Documentation.

A very recent Q&A blog post by Porus Homi Havewala has attracted my attention. It is about his favourite questions and answers from EM. Very good one indeed!

If you are still with me I will switch to new features. The ones I find most important are the ones related to performance. Two of the new SQL Tuning features are alternate execution plan identification in SQL Tuning Advisor based on real-time or historical performance and parallel SQL Profile recommendation in SQL Tuning Advisor to enable parallel execution of a long-running query. I really like the way Oracle let’s us compare the profile testing results, have a look. A picture is worth a thousand words:

The second new feature is parallel SQL Profile recommendation in SQL Tuning Advisor to enable parallel execution of a long-running query. I could not find any example for this on the Internet, so here is one:

I think it is an excellent improvement from Oracle!

But here is an Oracle internal statement:

SELECT MAX(NVL(sev.severity_code,0)) severity_code, count (*)
FROM mgmt_current_severity sev, (
select t.target_guid, m.metric_guid
from mgmt_metrics m, mgmt_targets t
where (m.metric_name, m.metric_column) in ( ('ha_rac_intrconn_traffic', 'interconnect_rate'), ('ha_rac_intrconn_type', 'if_type') ) AND m.type_meta_ver = t.type_meta_ver AND t.target_guid in (
select member_target_guid
from mgmt_target_memberships
where composite_target_type = :1 and composite_target_name = :2 ) AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ') ) t1
where sev.target_guid = t1.target_guid AND sev.metric_guid = t1.metric_guid AND sev.severity_code >= 20;

Interesting enough Oracle seems to struggle with its own execution plans, in its own tables. Should we really consider the recommendations and add indexes to SYSMAN’s tables? I guess just the SQL Profile 🙂

But look at the comparison of the execution plans. The Cost will drop from 624006 to 19 and the I/O Cost will drop from 623607 to 18:

Bottom line: it is safe to enable automatic SQL tuning and let Oracle profile the worst statements in the database. I would recommend running the following:

And before hitting enter make sure you have already licensed the Tuning and Diagnostics packs!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: