数据库性能分析新手：如何从Dynatrace提取AWR报告及监控参数

Hey there! As someone who’s spent a fair bit of time working with Dynatrace and Oracle AWR reports, let me walk you through these questions clearly.

1. How to Get AWR Reports from Dynatrace? #

First off, make sure your Oracle database is properly monitored by Dynatrace (either via the OneAgent installed on the database host, or through the Oracle database extension). Once that’s set, follow these steps:

• Navigate to your database entity: In the Dynatrace console, go to the Databases menu, then locate and select your target Oracle database instance.
• Find the AWR report section: On the database instance’s detail page, look for a Performance or Reports tab—you’ll usually see an option like AWR Reports or Oracle AWR here.
• Configure and generate the report: Select the time window you want to analyze (e.g., the last hour, or a specific time range where performance issues occurred), then click the "Generate" button. Dynatrace will pull the AWR data directly from Oracle and generate a report you can download as HTML or PDF.
• Bonus: For automation, you can use the Dynatrace API with an endpoint like GET /api/v2/entities/{entityId}/reports/awr (you’ll need valid API authentication for this). But for beginners, the console method is way more straightforward.

2. Key Parameters to Monitor in an AWR Report #

AWR reports are packed with data, but these are the critical metrics you should focus on first:

Overall Performance Metrics #

• DB Time: The total time the database spent processing user requests. A high DB Time relative to elapsed time means your database is under heavy load.
• CPU Time: The percentage of DB Time spent on CPU operations. If this is close to 100%, you might be hitting a CPU bottleneck.
• Wait Time: The portion of DB Time spent waiting on resources (IO, locks, etc.). A high wait time usually indicates a bottleneck that needs investigation.

Critical Wait Events #

These tell you what the database is waiting on:

• db file sequential read: Typically from index lookups or small table access—high values might mean slow storage or inefficient indexes.
• db file scattered read: Associated with full table scans—if this is top of the list, check for missing indexes or poorly written SQL.
• latch free: Indicates contention for internal database latches (common in high-concurrency environments).
• enqueue: Signals lock contention between sessions (e.g., two sessions trying to update the same row).

Hit Ratio Metrics #

• Buffer Cache Hit Ratio: Measures how often data is retrieved from memory instead of disk. Aim for 90%+—a low ratio suggests you need more memory or have inefficient SQL.
• Library Cache Hit Ratio: Tracks how often SQL statements are reused from the shared pool. A low ratio means lots of hard parsing, which wastes CPU and memory.

SQL-Related Metrics #

• Top SQL by Elapsed Time/CPU Time: These are your biggest performance culprits. Focus on optimizing these queries first—even small improvements here can make a huge difference.
• Parse Calls: Look at the number of hard vs. soft parses. Hard parses are expensive, so minimize them by using bind variables or adjusting shared pool settings.

Resource Usage #

• Tablespace Usage: Monitor free space in tablespaces to avoid unexpected outages from full storage.
• PGA/Aggregate PGA Target: Check if the Program Global Area is sized correctly. If PGA usage exceeds the target, you might see slow query performance due to disk swapping.
• SGA Component Breakdown: Review how memory is allocated to shared pool, buffer cache, and other SGA components—adjust based on your workload (e.g., more buffer cache for data-heavy apps).

内容的提问来源于stack exchange，提问作者Om Prakash