Interview with Oracle Database Manageability Expert, Deba
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.
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
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?
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.
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.
Chatterjee: Although they belong to the same family of
products there is a fundamental difference between the two.
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.
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?
Chatterjee: There are various retention times based on
the type of metric data:
metric data: default retention time is 7 days
aggregated metric data: default retention is 31 days
aggregated metric data: default retention is 12 months
to the documentation
here, if you want to change the default retention time.
Compare Period ADDM, how do you relate that in the context
of system load?
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.
Compare Period ADDM take into account the average read and
average write in order to identify why the variance is happening
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.
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?
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.
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?
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
Oracle Enterprise Manager 12c compatible with older versions
of the database?
Chatterjee: You can use Oracle Enterprise Manager 12c
to monitor Oracle Database versions: 220.127.116.11, 10.1.0.5, 10.2.0.4,
10.2.0.5, 18.104.22.168, 22.214.171.124, 126.96.36.199, and 188.8.131.52.
SQL Performance Analyzer part of Oracle Database 11g or do
you need to use Oracle Enterprise Manager 12c?
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.
do you enable Enterprise Manager to monitor and send alerts
to the DBAs for a "Runaway Query?”
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
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?
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.
you recommend enabling automatic gathering of SQL baselines
as a preventative measure of SQL regression?
Chatterjee: No. This would be overkill. Identify the
queries that frequently change plans and then create SQL plan
a server has multiple database instances, what is the best
way to effectively do resource allocation?
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.
using SQL Performance Analyzer, do you create the baseline
first before the code change is deployed or at peak DB time?
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.
there a metric to monitor ASM disk group utilization at the
Chatterjee: Yes. ASM Disk Group Usage metric is what
you can use. (See image below)
Oracle Enterprise Manager’s alerts be configured to
monitor elements in the audit trail such as table creation
or table drop?
Chatterjee: Yes, these type of alerts can be configured
using Metric Extensions.