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.
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.
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.
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.
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.
No comments:
Post a Comment