Database Performance Tuning & AWR Reports: Metrics, Tools & Optimization

TAKEAWAYS:

• AWR captures 1-hour snapshots of database metrics (CPU, I/O, SQL) to identify performance trends and bottlenecks.
• Top 5 SQL statements in AWR’s “SQL Ordered by Elapsed Time” reveal the most resource-intensive queries needing optimization.
• Wait events (e.g., db file sequential read) pinpoint I/O or lock contention—fixes include indexing or partitioning.
• Automatic Database Diagnostic Monitor (ADDM) in AWR provides actionable recommendations (e.g., “Add index on customer_id”).
• Flashback Database and Undo retention settings directly impact performance tuning for recovery scenarios.
• Real-world tie: Ncell’s billing system uses AWR to detect slow INSERT queries during peak hours (e.g., 6–9 PM) and pre-optimizes indexes.

1. Why Performance Tuning? The DBA’s Silent Battle

Databases slow down due to three killers:

1. Poorly written SQL (e.g., SELECT * FROM large_table without WHERE).
2. Missing indexes (forcing full-table scans).
3. Resource contention (CPU, memory, or I/O bottlenecks).

AWR (Automatic Workload Repository) is Oracle’s built-in time machine: it records metrics every 60 minutes (configurable) and lets you compare snapshots to spot regressions. Think of it as a flight data recorder for databases.

2. AWR Reports: The DBA’s Dashboard

AWR reports are generated via:

@?/rdbms/admin/awrrpt.sql

Key sections (visualized in the report):

MERMAID: AWR Report Workflow

flowchart TD
    A["AWR Snapshots (Hourly)"] -->|"Stored in SYSAUX tablespace"| B["AWR Repository"]
    B --> C["Generate Report via awrrpt.sql"]
    C --> D["Analyze: Top SQL/Waits"]
    D --> E["ADDM: Recommend Fixes"]
    E --> F["Implement: Indexes/Partitioning"]
    F --> G["Verify: Next AWR Report"]

Worked Example: eSewa’s Payment Query Slowdown

• Problem: During Diwali, SELECT * FROM transactions WHERE status='completed' took 12 seconds (vs. 0.2s normally).
• AWR Insight: The query was a full scan on a 50M-row table with no index.
• Fix: Added a composite index:

  CREATE INDEX idx_txn_status ON transactions(status, amount);
  

• Result: Query time dropped to 0.8 seconds.

3. Performance Tuning Techniques

A. SQL Optimization

Common Anti-Patterns (and fixes):

MERMAID: SQL Execution Plan

graph LR
    A["Query: SELECT * FROM orders WHERE customer_id = 100"] --> B["Full Table Scan"]
    B --> C["10,000 rows scanned"]
    C --> D["CPU: 500ms, I/O: 200ms"]
    D --> E["Fix: Add INDEX on customer_id"]
    E --> F["Index Range Scan\nCPU: 5ms, I/O: 1ms"]

B. Indexing Strategies

• B-Tree Indexes: Best for equality (=) and range (>, <) queries.

  CREATE INDEX idx_customer_email ON customers(email);
  

• Bitmap Indexes: Ideal for low-cardinality columns (e.g., gender, status).
• Function-Based Indexes: For queries using functions:

  CREATE INDEX idx_upper_name ON employees(UPPER(last_name));
  

Worked Example: NEPSE Stock Data

• Problem: SELECT symbol, price FROM stocks WHERE price > 1000 scanned 1M rows.
• Fix: Created a function-based index on price:

  CREATE INDEX idx_high_value ON stocks(FLOOR(price/1000));
  

• Result: Scan reduced to 500 rows.

C. Partitioning

Split large tables by range, list, or hash to avoid full scans.

Example: Daraz Order History

CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    order_date DATE
) PARTITION BY RANGE (order_date) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

Benefits:

• Queries on p_2023 ignore p_2024 data.
• Easier backups (partition-level).

4. Advanced Tools in Oracle

MERMAID: ADDM Workflow

sequenceDiagram
    participant DBA
    participant AWR
    participant ADDM
    DBA->>AWR: Take snapshot (SNAP_ID=123)
    AWR->>ADDM: Analyze snapshots 120-123
    ADDM->>DBA: "Findings:\n1. Missing index on INV_CUSTOMERS.NAME\n2. High latch contention on UNDO tablespace"
    DBA->>ADDM: Implement fixes
    ADDM->>DBA: Verify improvement in next snapshot

5. Real-World Applications

Case 1: Khalti’s Transaction Logs

• Challenge: During Dashain, Khalti’s INSERT INTO transactions queries caused lock contention on the TRANSACTIONS table.
• AWR Insight: enq: TX - row lock contention was the top wait event.
• Fix:
  • Increased UNDO_RETENTION to 7200 seconds.
  • Added a partition by range on transaction_time.
• Result: Transaction throughput increased by 40%.

Case 2: NTC’s Customer Billing

• Problem: Monthly billing reports (SELECT * FROM customers JOIN bills) took 3 hours.
• AWR Finding: The query was a cartesian product due to missing joins.
• Fix: Added a hash join hint:

  SELECT /*+ HASH_SJ */ * FROM customers JOIN bills ON customers.id = bills.customer_id;
  

• Result: Report time dropped to 12 minutes.

Case 3: Pathao Driver App

• Issue: Driver location updates (UPDATE driver_locations SET lat=?, lng=?) caused high db file scattered read waits.
• Solution:
  • Created a composite index on (driver_id, last_updated).
  • Used batch updates (100 rows at once) instead of row-by-row.

6. Common Pitfalls & How to Avoid Them

Exam Tip

1. AWR Questions:

   • Memorize the 4 key sections of an AWR report (Instance Efficiency, Top SQL, Wait Events, ADDM).
   • Know how to generate a report (awrrpt.sql) and interpret ADDM findings.
   • Example Answer:

     "The AWR report’s ‘Top 5 SQL by CPU’ section shows that SELECT product_id FROM inventory WHERE stock < 10 consumes 45% CPU. The wait event db file sequential read indicates missing indexes. The ADDM recommends creating an index on INVENTORY(STOCK), which would reduce full-table scans."

2. Performance Tuning:

   • Always start with AWR before guessing fixes.
   • Indexing: Know when to use B-Tree vs. Bitmap.
   • Partitioning: Explain range vs. list partitioning with an example.

3. Real-World Scenarios:

   • eSewa: Use AWR to detect slow payment queries during festivals.
   • Ncell: Partition call logs by month to speed up billing reports.
   • Daraz: Optimize ORDER BY clauses for product searches.

4. Commands to Remember:

   -- Check AWR snapshots
   SELECT snap_id, begin_interval_time, end_interval_time FROM dba_hist_snapshot;
   
   -- Generate AWR report
   @?/rdbms/admin/awrrpt.sql
   
   -- ADDM recommendations
   SELECT * FROM dba_advisor_findings;
   

Final Visual Summary

mindmap
  root((Database Performance Tuning))
    AWR Reports
      Snapshots
      ADDM Findings
      Top SQL/Waits
    SQL Optimization
      Indexes
      Partitioning
      Query Rewrites
    Tools
      ASH
      SQL Tuning Advisor
    Real-World
      eSewa: Payment Queries
      Ncell: Billing Reports
      Khalti: Transaction Logs