Database Performance Tuning: SQL, Memory, Storage, and RMAN

TAKEAWAYS:

• SQL tuning identifies slow queries using the Automatic Workload Repository (AWR) and SQL Tuning Advisor, with hints like /*+ INDEX */ to optimize execution plans.
• Memory structures (SGA: shared pool, buffer cache, redo logs; PGA: per-session memory) directly impact performance—misconfiguration causes timeouts or crashes.
• Storage tuning involves segment management (tablespaces, extents, freelists), multiplexing redo logs/control files, and Automatic Storage Management (ASM) for redundancy.
• RMAN automates backups (full, incremental, differential) and recovery (point-in-time, crash, media failure) using channels and retention policies.
• Automatic Diagnostic Repository (ADR) logs errors, traces, and alerts for proactive troubleshooting (e.g., "ORA-00600" deadlocks).
• Real-world example: Nepal Rastra Bank’s core banking system uses RMAN for daily incremental backups and ASM for high availability during peak transaction hours (e.g., salary disbursements).

1. Why Performance Tuning Matters

Databases are the backbone of modern systems. In Nepal, eSewa processes thousands of transactions per second during festival seasons, while Ncell’s billing system must handle 10M+ records without delays. Poor tuning leads to:

• Slow queries (e.g., a Daraz order status check taking 5+ seconds).
• Resource exhaustion (e.g., NTC’s network outages due to overloaded database buffers).
• Data loss (e.g., NEPSE’s stock trading system crashes during volatility).

Key metric: Response time (target: <2s for 95% of queries). Tuning focuses on CPU, memory, I/O, and SQL.

2. SQL Performance Tuning

A. Identifying Slow Queries

Oracle provides tools to analyze query performance:

1. Automatic Workload Repository (AWR):

   • Stores performance statistics (CPU, I/O, waits) every 1 hour.
   • Query: SELECT * FROM DBA_HIST_SQLSTATS WHERE SQL_ID = '...';
   • Example: A SELECT * FROM CUSTOMERS query runs in 10s due to a full table scan. AWR shows it uses 80% CPU.

2. SQL Tuning Advisor:

   • Automatically suggests optimizations (e.g., "Add index on customer_id").
   • Command:

     BEGIN
       DBMS_SQLTUNE.TUNE_SQL(
         sql_id => 'g12345678',
         scope => DBMS_SQLTUNE.SCOPE_ADVISOR,
         time_limit => 60
       );
     END;
     

B. Optimization Techniques

Worked Example: Pathao’s Ride Requests

• Problem: A query to fetch nearby drivers takes 3s during peak hours (5–9 PM).
• Solution:
  1. Add a spatial index on driver_location (latitude/longitude).
  2. Use a partitioned table for RIDE_REQUESTS by hour.
  3. Rewrite the query with a hint:

     SELECT * FROM RIDE_REQUESTS
     WHERE driver_location WITHIN_DISTANCE('POINT(27.7172 85.3240)', 500)
     /*+ INDEX(rq idx_driver_location) */;
     

• Result: Response time drops to <500ms.

3. Memory Management: SGA vs. PGA

A. Oracle Memory Structures

graph TD
    A["SGA: Shared Global Area"] --> B["Buffer Cache"]
    A --> C["Redo Log Buffer"]
    A --> D["Shared Pool"]
    E["PGA: Program Global Area"] --> F["Sort Area"]
    E --> G["Session Memory"]
    E --> H["Cursor Cache"]

B. Key Differences: SGA vs. PGA

Real-World Impact:

• Nepal Investment Bank’s core system crashes during month-end due to PGA exhaustion from complex reports. Solution: Increase PGA_AGGREGATE_TARGET and use parallel queries for batch jobs.

4. Storage Tuning

A. Tablespace and Segment Management

erDiagram
    TABLESPACE ||--o{ DATAFILE : contains
    TABLESPACE ||--o{ TABLESPACE_GROUP : part of
    TABLESPACE ||--o{ TABLE : stores
    TABLE ||--o{ INDEX : uses
    TABLE ||--o{ LOB : stores

Key Terms:

• Extent: Logical storage unit (1–32 blocks).
• Freelist: Tracks free extents for new rows.
• PCTFREE: % of space left empty to avoid row chaining (default: 10%).

Example: Daraz’s Product Catalog

• Problem: PRODUCTS table has row chaining (fragments across blocks), slowing queries.
• Solution:

  ALTER TABLE PRODUCTS MOVE TABLESPACE products_ts
  STORAGE (INITIAL 100M NEXT 50M PCTINCREASE 0 FREELISTS 3 FREELIST GROUPS 3);
  

  • PCTFREE=20 to reduce chaining.
  • FREELISTS=3 for concurrent inserts.

B. Multiplexing for Reliability

Multiplexing duplicates critical files (redo logs, control files) across disks to prevent single-point failures.

Worked Example: NTC’s Billing System

• Scenario: A power outage corrupts the primary control file.
• Recovery:
  1. Start in mount mode:

     STARTUP MOUNT;
     

  2. Recreate control file from multiplexed copy:

     ALTER DATABASE CREATE CONTROLFILE REUSE DATABASE;
     

  3. Open database:

     ALTER DATABASE OPEN;
     

5. Backup and Recovery with RMAN

A. RMAN Backup Types

mindmap
  root((RMAN Backup Strategies))
    Full Backup
      Pros: Complete, fast restore
      Cons: Large storage
      Command: `BACKUP DATABASE PLUS ARCHIVELOG;`
    Incremental Backup
      Pros: Saves space
      Cons: Slower restore
      Command: `BACKUP DATABASE INCREMENTAL LEVEL 1;`
    Differential Backup
      Pros: Balanced speed/storage
      Cons: Complex recovery
      Command: `BACKUP DATABASE DIFFERENTIAL;`
    Archived Redo Logs
      Pros: Point-in-time recovery
      Cons: Requires archiving
      Command: `BACKUP ARCHIVELOG ALL;`

B. Recovery Scenarios

Worked Example: NEPSE’s Trading System

• Problem: A TRUNCATE TABLE SHARES command accidentally deletes 2023’s data.
• Solution:
  1. Flashback (if enabled):

     FLASHBACK TABLE SHARES TO BEFORE DROP;
     

  2. RMAN restore (if flashback disabled):

     RESTORE TABLESPACE shares_ts;
     RECOVER TABLESPACE shares_ts;
     

C. RMAN Configuration

1. Set Up RMAN:

   RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK;
   RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
   

2. Backup Command:

   RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
   

3. Verify Backup:

   RMAN> LIST BACKUP;
   

6. Automatic Diagnostic Repository (ADR)

ADR stores:

• Alert logs (critical errors like ORA-00600).
• Trace files (slow SQL, deadlocks).
• Incident dumps (crash diagnostics).

Example: Diagnosing a Deadlock

1. Check ADR for trace files:

   SELECT name, value FROM v$diag_info;
   

2. Analyze deadlock graph:

   SELECT * FROM DBA_DEADLOCKS;
   

3. Solution: Rewrite the transaction to avoid locks (e.g., use SKIP LOCKED in SELECT).

Real-World Use:

• Khalti’s payment system uses ADR to detect and resolve deadlocks during Diwali sales (peak: 50K transactions/minute).

7. Performance Tuning Checklist

In the Real World

1. eSewa’s Transaction Processing

   • Idea Used: Partitioning + Indexing
   • How: The PAYMENTS table is partitioned by transaction_date (monthly), with a bitmap index on payment_status (success/failure). This reduces query time from 5s to <100ms during festival seasons.

2. Ncell’s Charging System

   • Idea Used: PGA Tuning + Parallel Queries
   • How: During month-end, the system runs batch jobs to generate bills. To avoid PGA exhaustion:
     • PGA_AGGREGATE_TARGET is set to 8GB.
     • Queries use /*+ PARALLEL(4) */ to distribute load across CPU cores.

3. Nepal Rastra Bank’s Core Banking

   • Idea Used: RMAN + ASM for High Availability
   • How: The bank uses:
     • ASM to stripe data across 6 disks for I/O performance.
     • RMAN incremental backups (daily) + archived redo logs for point-in-time recovery.
     • Automated failover to a standby database in Chitwan during Kathmandu outages.

Exam Tip

1. SQL Tuning Questions:

   • Always show AWR/SQL Tuning Advisor output in your answer.
   • For indexing, explain why a column needs an index (e.g., "frequent WHERE clause").

2. Memory Management:

   • Compare SGA vs. PGA in a table (as above).
   • Mention V$SGASTAT and V$PGASTAT for monitoring.

3. RMAN Backup/Recovery:

   • Describe 3 backup types (full, incremental, differential) and their use cases.
   • For recovery, list 4 failure types and the corresponding RMAN command.

4. ADR:

   • Define ADR and its 3 components (alert logs, trace files, incident dumps).
   • Relate to deadlocks or ORA-00600 errors.

5. Worked Examples:

   • Always tie your answer to a real-world scenario (e.g., "Like eSewa’s payment system...").
   • Use commands (even if not asked) to show practical knowledge.