Oracle Performance Tuning: SQL & AWR – Locks, SQL Analysis & AWR Reports

TAKEAWAYS:

• Oracle uses locking levels (row, table, DML, transaction, schema, and system locks) to manage concurrency, with methods like SELECT FOR UPDATE and LOCK TABLE to acquire them.
• SQL tuning relies on execution plans (via EXPLAIN PLAN), bind variables (to avoid hard parsing), and proper indexing (B-tree vs. bitmap) to reduce I/O and CPU overhead.
• The AWR automatically captures historical performance data (wait events, CPU usage, buffer cache hits) and generates reports (awrrpt.sql) to pinpoint bottlenecks.
• Real-world impact: Nepal’s Ncell uses AWR to optimize call routing during peak hours, while Daraz tunes SQL queries to handle 10,000+ concurrent orders during festivals.
• Worked example: A slow SELECT * FROM orders WHERE customer_id = 1234 can be fixed by adding a composite index on (customer_id, order_date) and checking the execution plan.
• Exam focus: Link SQL tuning to AWR reports (e.g., "The AWR shows 90% of CPU time is spent on PARSING IN CURSOR—suggest using bind variables").

1. Oracle Locking Mechanisms: Levels and Acquisition

Oracle enforces locking to prevent concurrent transactions from corrupting data. Locks are hierarchical, from fine-grained (row) to coarse-grained (system).

Locking Levels in Oracle

• System Lock: Global resource (e.g., ALTER DATABASE).
• Schema Lock: Schema-level DDL operations (e.g., CREATE TABLE).
• Table Lock: Exclusive lock on a table during ALTER TABLE.
• DML Lock: Shared (SELECT) or exclusive (INSERT/UPDATE/DELETE) locks on rows.
• Transaction Lock: Holds until commit/rollback.
• Row Lock: Granularity for row-level operations.

How Locks Are Acquired

Worked Example: Deadlock in Nepal’s NTC Call Center Two agents, Agent A and Agent B, try to update the same customer’s bill status:

• Agent A locks row customer_id=1001 (status: "pending").
• Agent B locks row customer_id=1001 (status: "paid").
• Deadlock: Both wait indefinitely. Oracle resolves it by killing one session:

  SELECT sid, serial# FROM v$locked_object WHERE owner = 'NTC';
  ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;
  

2. SQL Tuning: Execution Plans and Bind Variables

Slow SQL queries waste resources. Oracle provides tools to diagnose and fix them.

erDiagram
  CUSTOMERS ||--o{ ORDERS : "places"
  ORDERS ||--o{ ORDER_ITEMS : "contains"

  CUSTOMERS {
    int customer_id PK
    string customer_name
    string country
  }

  ORDERS {
    int order_id PK
    int customer_id FK
    date order_date
  }

  ORDER_ITEMS {
    int item_id PK
    int order_id FK
    int product_id FK
    int quantity
  }

Execution Plans with EXPLAIN PLAN

EXPLAIN PLAN FOR SELECT customer_name FROM customers WHERE country = 'Nepal';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Output Interpretation:

| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
|-----|--------------------|--------------|-------|-------|------------|----------|
|   0 | SELECT STATEMENT   |              |     1 |    12 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN  | idx_country  |     1 |    12 |     3   (0)| 00:00:01 |

• INDEX RANGE SCAN: Ideal (uses index).
• FULL TABLE SCAN: Bad (scans entire table).

Fix: Add a missing index:

CREATE INDEX idx_country ON customers(country);

Bind Variables vs. Hard Parsing

• Hard Parsing: Oracle recompiles SQL for every execution (slow).
• Bind Variables: Reuse execution plans (fast).

  -- Bad: Hard parsing
  EXECUTE IMMEDIATE 'SELECT * FROM orders WHERE order_id = ' || order_id;
  
  -- Good: Bind variable
  v_order_id := 1234;
  EXECUTE IMMEDIATE 'SELECT * FROM orders WHERE order_id = :order_id' USING v_order_id;
  

Real-World Tie: Daraz’s Festival Sales During Tihar, Daraz processes 10,000+ orders/sec. A poorly tuned query like:

SELECT product_name FROM products WHERE category = 'Electronics' AND price < 5000;

could trigger a full table scan. Solution:

1. Add a composite index: CREATE INDEX idx_category_price ON products(category, price).
2. Use bind variables in stored procedures to avoid hard parsing.

3. Automatic Workload Repository (AWR)

AWR automatically captures database performance metrics every hour and stores them in the DBA_HIST_* views. Key components:

• Snapshot: Captures CPU, I/O, waits, and buffer cache stats.
• Baseline: Compares current performance to historical baselines.
• Report: Generated via awrrpt.sql.

AWR Report Key Metrics

Example AWR Snippet:

Wait Event: "db file sequential read" - 30% of CPU time
Top SQL: "SELECT * FROM sales WHERE date > SYSDATE - 7" (Cost: 1000)
Suggestion: Add index on `sales(date)`.

Generating an AWR Report

-- Run as SYSDBA
@?/rdbms/admin/awrrpt.sql

Output Highlights:

Worked Example: Ncell’s Call Routing Ncell’s database slows down during midnight peak hours. The AWR report shows:

Top Wait Event: "enq: TX - row lock contention" (25%)

Solution:

1. Add row-level locks to high-contention tables:

   ALTER TABLE call_logs ADD CONSTRAINT pk_call_logs PRIMARY KEY (call_id, timestamp);
   

2. Partition large tables by time:

   CREATE TABLE call_logs (
     call_id NUMBER,
     timestamp DATE,
     -- ...
   ) PARTITION BY RANGE (timestamp) (
     PARTITION p_jan VALUES LESS THAN (TO_DATE('01-FEB-2023', 'DD-MON-YYYY'))
   );
   

4. Performance Tuning Techniques

Advantages/Disadvantages:

5. Real-World Applications

In the Real World

1. Nepal Rastra Bank (NRB) – Loan Processing

   • Idea: AWR monitors slow SQL during loan approval season.
   • How: NRB’s AWR report flags a query:

     SELECT applicant_name FROM loans WHERE status = 'pending' AND amount > 500000;
     

     Fix: Added a composite index on (status, amount) and used SELECT * FROM loans WHERE status = :status AND amount > :amount.

2. Khalti – Payment Gateway

   • Idea: Bind variables prevent hard parsing during high transaction volumes.
   • How: Khalti’s stored procedures use bind variables for:

     -- Instead of:
     EXECUTE IMMEDIATE 'UPDATE transactions SET status = ''completed'' WHERE txn_id = ' || txn_id;
     
     -- Use:
     v_txn_id := 'txn_12345';
     EXECUTE IMMEDIATE 'UPDATE transactions SET status = ''completed'' WHERE txn_id = :txn_id' USING v_txn_id;
     

3. NTC – Call Center Automation

   • Idea: Deadlock resolution via LOCK TABLE prevents call drops.
   • How: NTC locks high-contention tables during peak hours:

     LOCK TABLE call_records IN EXCLUSIVE MODE;
     -- Perform bulk updates
     UNLOCK TABLE call_records;
     

Exam Tip

• Link SQL tuning to AWR: Always mention how an AWR report (e.g., high db file sequential read) leads to a specific fix (e.g., add an index).
• Deadlocks: Explain with a real scenario (e.g., NTC agents) and the ALTER SYSTEM KILL SESSION command.
• Bind variables: Contrast with hard parsing in exam questions—show how bind variables reduce parsing overhead.
• AWR report structure: Know the key sections (top SQL, wait events, CPU usage) and how to interpret them.
• Indexing trade-offs: Discuss when to use B-tree (OLTP) vs. bitmap (data warehousing) indexes.

Sample Answer Structure for Exam:

1. Locking Levels: Explain row/table/transaction locks with a diagram.
2. Deadlock Example: Describe NTC’s call center deadlock and resolution.
3. SQL Tuning: Show EXPLAIN PLAN output and suggest an index.
4. AWR Report: Paste a snippet and explain the top wait event.
5. Real-World Fix: Apply the fix (e.g., add index) and quantify improvement (e.g., "reduced query time from 5s to 0.1s").

In the real world

• Ncell’s Call Routing System: Uses AWR reports to detect enq: TX - row lock contention during midnight peak hours (25% of wait events) and partitions call_logs table by timestamp to reduce lock contention.
• Daraz’s Festival Sales: Implements bind variables in stored procedures to avoid hard parsing during Tihar (10,000+ orders/sec) and adds composite indexes on (category, price) to optimize SELECT product_name FROM products WHERE category = 'Electronics' AND price < 5000.
• Nepal Rastra Bank’s Core Banking: Employs row-level locks (SELECT FOR UPDATE) for high-frequency transactions (e.g., ALTER TABLE accounts LOCK ROWS during batch processing) to prevent deadlocks in multi-branch operations.