Good Feedback

Good Feedback on my book from a reader in the USA:
"I must  mention that this is THE book I have ever read and digested in the field of cloud control through Oracle Enterprise Manager, I am very impressed how categorically  it has explained the core details."
Thanks Friend.
http://www.packtpub.com/oracle-enterprise-manager-12c-cloud-control/book

Answers to Your Common Database Performance Questions

By Scott McNeil on Jan 15, 2013

An Interview with Oracle Database Manageability Expert, Deba Chatterjee

Throughout the year we hear from lots of customers and get many questions about managing Oracle Database. In this blog, I thought I would try and provide some answers to common diagnostics and tuning questions with the help of our product manager and residence expert for Oracle Database Manageability, Deba Chatterjee. Deba has a wealth of database performance tuning experience both inside and outside of Oracle managing large data warehouses. Deba is responsible for Oracle Diagnostics Pack for Database and Oracle Tuning Pack for Database. I recently sat down with Deba and had a chat about database manageability.

Scott McNeil: Deba, we get many people asking questions about database performance—many still don't know about all the deep diagnostics capabilities Oracle Enterprise Manager 12c has to offer for Oracle Database. Capabilities such as; Compare Period ADDM, Real-Time ADDM, Active Session History (ASH) Analytics Real-Time SQL Monitoring, using Metric Extensions, and SQL Tuning Advisor—how do customers get all these capabilities for their database?

Deba Chatterjee: We recently ran a webcast: Maximize Oracle Database Performance with Oracle Enterprise Manager 12c: Top 10 Tips and Tricks that explains how many of these features work. I highly recommend people watch the webcast to get a better understanding of the capabilities you mentioned. But the short answer is: for Compare Period ADDM, Real-Time ADDM, Metric Extensions you need to license Oracle Enterprise Manager 12c Diagnostics Pack for Database. For Real-Time SQL Monitoring, SQL Tuning Advisor, you will need the Oracle Tuning Pack for Database.

Scott McNeil: Another question customers repeatedly ask is around Cloud Control and Database Control. Deba, can you explain the difference between Oracle Enterprise Manager Cloud Control 12c (formerly Grid Control) and Database Control.

Deba Chatterjee: Although they belong to the same family of products there is a fundamental difference between the two. Database Control can be used to manage only a single database with which it has been configured, while Oracle Enterprise Manager Cloud Control 12c allows you to manage all your databases under the same centralized management console. Plus Cloud Control lets you manage not only all your databases but your entire application and technology stack too, whether it's in a private cloud or in a traditional environment.

How do you modify the Metric Value History retention in Enterprise Manager? For example; Enterprise Manager only shows the last 7 days, how do you display longer than 7 days?

Deba Chatterjee: There are various retention times based on the type of metric data:

• Raw metric data: default retention time is 7 days
• Hourly aggregated metric data: default retention is 31 days
• Daily aggregated metric data: default retention is 12 months

Refer to the documentation here, if you want to change the default retention time.

For Compare Period ADDM, how do you relate that in the context of system load?

Deba Chatterjee: In the resource usage tab, you can check the system CPU, Memory, I/O and interconnect (for RAC databases) utilizations across the 2 comparison periods in the same database.

Does Compare Period ADDM take into account the average read and average write in order to identify why the variance is happening in performance?

Deba Chatterjee: Compare Period ADDM uses database time to compare the performance across two periods. It does not compare based on average read or write times.

Does Enterprise Manager have the ability to create customized performance graphs? For example; can you create a graph for CPU usage in the last 24 hours on a given Host?

Deba Chatterjee: This is possible through the information publisher or BI publisher reports. As for the CPU usage, the chart is available out-of-the box in the target page for hosts.

How do you connect to the database itself when it’s hung and won't allow any extra connections? Do you use command line? Can you use ADDM even though the database is hung?

Deba Chatterjee: When the database is hung, you can connect to it using the diagnostic connection mode in Real-Time ADDM. The agent that is used to monitor the database makes the connection. No, command line is used. You have to use Real-Time ADDM for the connection.

Is Oracle Enterprise Manager 12c compatible with older versions of the database?

Deba Chatterjee: You can use Oracle Enterprise Manager 12c to monitor Oracle Database versions: 9.2.0.8, 10.1.0.5, 10.2.0.4, 10.2.0.5, 11.1.0.7, 11.2.0.1, 11.2.0.2, and 11.2.0.3.

Is SQL Performance Analyzer part of Oracle Database 11g or do you need to use Oracle Enterprise Manager 12c?

Deba Chatterjee: SQL Performance Analyzer is built into the database and provides command line APIs. However, Oracle Enterprise Manager 12c provides the complete orchestration needed to capture the SQL tuning set, run the performance trials, and then to create a performance comparison report.

How do you enable Enterprise Manager to monitor and send alerts to the DBAs for a "Runaway Query?”

Deba Chatterjee: You can use EM to monitor runaway queries and send alerts using Metric Extensions which rely on the data captured in SQL Monitoring. The method was explained in this webcast presentation.

How do you create a report for all the SQL running in an instance during a 30 minute timeframe? Can you export this to a spreadsheet?

Deba Chatterjee: ASH stores sampled (1 seconds in memory or 10 seconds on-disk) SQL. However ASH is meant to capture the high load SQL statements so it won’t have all SQL statements.

Do you recommend enabling automatic gathering of SQL baselines as a preventative measure of SQL regression?

Deba Chatterjee: No. This would be overkill. Identify the queries that frequently change plans and then create SQL plan baselines.

If a server has multiple database instances, what is the best way to effectively do resource allocation?

Deba Chatterjee: There is no silver bullet. Carefully study your database load and decide on use of services, instance caging and resource managers to manage load on servers.

When using SQL Performance Analyzer, do you create the baseline first before the code change is deployed or at peak DB time?

Deba Chatterjee: These are two different problems. While testing for Code change, the baseline should be created before the code is deployed. While testing for an upgrade scenario the baseline needs to be created at a peak DB time.

Is there a metric to monitor ASM disk group utilization at the cluster level?

Deba Chatterjee: Yes. ASM Disk Group Usage metric is what you can use. (See image below)

Can Oracle Enterprise Manager’s alerts be configured to monitor elements in the audit trail such as table creation or table drop?

Deba Chatterjee: Yes, these type of alerts can be configured using Metric Extensions.

By Scott McNeil on Jan 15, 2013

Printing Errata

I have received a copy of my new EM12c book
and while going through it, I found a printing error on page 308.
This is in Chapter 10 which is titled "Ease the Chaos with
Exadata Management". On page 308, there are two paragraphs on the page
(2nd and 3rd paragraphs from the top of the page) that mention that
the “BP1” patch is recommended. The same page also mentions in the fourth
paragraph that Release 2 is recommended. The paragraphs mentioning BP1
were supposed to be removed during the final edit, however due to a
edtorial slip they were not. This is confusing to the reader since
there are two conflicting recommendations on the same page. So,
I have decided to make things clear to the reader.

The paragraphs that should be ignored/removed are as follows:

"For Exadata discovery, the Enterprise Manager Cloud Control 12cinstallation is
recommended to be on Bundle Patch 1(BP1)if it is not already on that patch level.
Note that applying the BP1 will not change the EM12cversion, since it is not a new
release or even a patch set of Enterprise Manager. The method to verify that the bundle
patch has been applied is to use the opatch utility in the OMS home. In the opatch
output, you should see that the BP1 patch has been applied, which is patch 13242773.

"Otherwise, please refer to the My Oracle Support(MOS)reference document for
applying the BP1 patch. The document ID is 1393173.1, and it is titled Enterprise
Manager Cloud Control Workbook for Applying Bundle Patch 1 (February 2012) and
12.1.0.2 Plugins."

Please ignore these paragraphs. I have asked for them to be removed in all
future printed versions of the book and also the e-book version.