X

An Oracle blog about Oracle Enterprise Manager and Oracle Management Cloud

Answers to Your Common Database Performance Questions

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.

LEARN
MORE:

Have a
database manageability question? Ask us on Twitter
or Facebook.



Stay Connected:

Twitter
|
Facebook
| YouTube
|
Linkedin
|
Newsletter

Download
the Oracle Enterprise Manager Cloud Control12c Mobile app


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.