Oracle DBA Basics: Architecture, Locks, CDB/PDB & SQL Tuning

TAKEAWAYS:

• Oracle’s architecture is a three-layer model (memory, processes, storage) with SGA, PGA, and background processes handling queries and transactions.
• Locking in Oracle prevents data corruption via row-level, table-level, and DML locks, acquired automatically or explicitly via LOCK TABLE commands.
• Container Database (CDB) and Pluggable Database (PDB) enable multi-tenancy, isolating workloads while sharing resources (e.g., Ncell’s customer databases in a single CDB).
• SQL Tuning Advisor analyzes slow queries and suggests optimizations (e.g., Daraz’s order-processing SQL queries tuned for peak sales).
• DBA tasks include security, backup, performance tuning, and automation—critical for systems like eSewa’s transaction processing.
• Oracle Net Services (listener, TNS) enable client-server communication (e.g., Khalti’s mobile app connecting to its Oracle backend).

Core Concepts: What is Database Administration?

Database Administration (DBA) is the management of database systems to ensure:

• Data integrity (no corruption),
• Performance (fast queries),
• Security (authorized access),
• Availability (uptime),
• Recovery (disaster resilience).

Key DBA Roles (from TU syllabus):

Why Oracle? Oracle is used by 80% of Fortune 100 companies (e.g., banks, airlines) for its:

• Scalability (handles millions of transactions),
• Security (fine-grained access control),
• High Availability (RAC, Data Guard).

Oracle Database Architecture: The 3-Layer Model

Oracle’s architecture is divided into three layers, each with specific components:

1. Memory Structures

Oracle uses System Global Area (SGA) and Program Global Area (PGA) for processing.

graph TD
    A["Oracle Memory"] --> B["SGA: Shared Global Area"]
    A --> C["PGA: Per-Process Memory"]
    B --> D["Buffer Cache: Stores data blocks"]
    B --> E["Redo Log Buffer: Records changes"]
    B --> F["Shared Pool: SQL and PL/SQL cache"]
    C --> G["Sort Area: Temporary sorting"]
    C --> H["Session Memory: User session data"]

Key Components:

• Buffer Cache: Holds data blocks (e.g., customer records) to avoid disk I/O.
• Redo Log Buffer: Records transactions for recovery (e.g., if a bank transfer fails).
• Shared Pool: Stores parsed SQL and execution plans (e.g., Daraz’s "get_order_status" query).

2. Background Processes

Oracle uses background processes to manage tasks like:

• DBWR (Database Writer): Writes dirty blocks from buffer cache to disk.
• LGWR (Log Writer): Writes redo log entries to disk (critical for recovery).
• SMON (System Monitor): Recoveries failed instances (e.g., after a power cut).

Example Workflow:

1. User queries SELECT * FROM accounts WHERE balance > 100000; (e.g., Ncell’s high-value customers).
2. PGA allocates memory for the session.
3. SGA’s Shared Pool checks for a cached execution plan.
4. DBWR flushes modified blocks to disk.

3. Storage Structures

Data is stored in:

• Tablespaces: Logical storage units (e.g., USERS for user data, SYSTEM for Oracle metadata).
• Datafiles: Physical files on disk (e.g., system01.dbf).
• Redo Log Groups: Circular logs for crash recovery.

Locking in Oracle: Preventing Data Corruption

Locks ensure concurrency control (multiple users accessing data safely). Oracle uses:

Types of Locks

How Locks Are Acquired

1. Implicit Locking: Oracle acquires locks automatically during DML.

   -- Example: Locks the 'account' row for update
   UPDATE accounts SET balance = balance - 1000 WHERE account_id = 12345;
   

2. Explicit Locking: Use SELECT FOR UPDATE or LOCK TABLE.

   -- Locks rows for a reservation system (e.g., Pathao’s ride booking)
   SELECT * FROM rides WHERE ride_id = 5678 FOR UPDATE;
   

Lock Escalation: If many rows are locked, Oracle may escalate to a table lock to avoid overhead.

Deadlocks: Occur when Transaction A locks Resource X and waits for Resource Y, while Transaction B locks Y and waits for X. Solution: Oracle detects deadlocks and rolls back one transaction (e.g., the less critical one).

Container Database (CDB) and Pluggable Database (PDB)

Oracle 12c introduced multitenancy via CDB and PDB to:

• Isolate databases (e.g., different banks in one Oracle instance).
• Share resources (memory, storage) efficiently.

Key Terms:

• CDB: The container holding all PDBs (e.g., a server hosting multiple company databases).
• PDB: A portable database (e.g., Ncell’s customer data).
• Seed PDB: Template for creating new PDBs.

Advantages:

Real-World Example:

• Nepal’s Banking Sector: A single Oracle CDB hosts PDBs for Nabil Bank, Global IME, and Standard Chartered Nepal, each with isolated data but shared backup/recovery systems.

SQL Tuning Advisor: Optimizing Performance

SQL Tuning Advisor (part of Oracle’s Automatic Workload Repository (AWR)) analyzes slow SQL and suggests fixes.

How It Works:

1. Capture SQL: AWR records SQL execution statistics (e.g., SELECT * FROM orders WHERE status = 'shipped').
2. Analyze: Advisor identifies bottlenecks (e.g., full table scans, missing indexes).
3. Recommend: Suggests optimizations like:
   • Adding an index:

     CREATE INDEX idx_order_status ON orders(status);
     

   • Rewriting SQL (e.g., avoiding SELECT *):

     -- Bad: Scans all columns
     SELECT * FROM products;
     -- Good: Only fetches needed columns
     SELECT product_id, name, price FROM products;
     

Example: Tuning Daraz’s Order System

• Problem: Query SELECT * FROM orders WHERE order_date > SYSDATE - 7 takes 5 seconds.
• Advisor Recommendation:
  • Add index on order_date.
  • Use a materialized view for frequent reports.
• Result: Query runs in 0.2 seconds.

Oracle Net Services: Client-Server Communication

Oracle Net Services enable client applications (e.g., eSewa app) to connect to the database server.

Key Components:

graph TD
    A["Client Application"] --> B["Oracle Net Services"]
    B --> C["Listener: Port 1521"]
    B --> D["TNS Names: tnsnames.ora"]
    C --> E["Database Server: SID=ORCL"]
    D --> F["tnsnames.ora: Configuration file"]
    E -->|"Connects to"| G["SGA/PGA Memory"]

1. Listener: Listens for client connections (default port: 1521).
2. TNS (Transparent Network Substrate): Handles naming and routing (e.g., eSewa_DB → 192.168.1.10:1521).
3. tnsnames.ora: Configuration file mapping service names to IP/port.

Example Connection String:

# tnsnames.ora entry for eSewa's database
eSewa_DB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db-server.esewa.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = esewa_prod)
    )
  )

In the Real World

1. eSewa’s Transaction Processing

   • Idea Used: Locking and CDB/PDB
   • How: eSewa’s Oracle CDB hosts multiple PDBs (e.g., eSewa_Payments, eSewa_Users). When you transfer Rs. 5000 from your account to a merchant, Oracle:
     • Acquires row-level locks on both accounts.
     • Uses redo logs to ensure the transaction survives a crash.
     • Isolates payment data in a PDB for security.

2. Ncell’s Customer Data Management

   • Idea Used: Tablespaces and SQL Tuning
   • How: Ncell’s Oracle database uses:
     • Separate tablespaces for customers, billing, and network_logs.
     • SQL Tuning Advisor to optimize queries like:

       -- Slow query: Finds all prepaid users with balance > 0
       SELECT * FROM customers WHERE account_type = 'prepaid' AND balance > 0;
       

       Fix: Add a composite index on (account_type, balance).

3. Daraz’s Order Fulfillment System

   • Idea Used: Oracle Net Services and Locking
   • How: When you place an order:
     • Your app connects via Oracle Net Services (listener on port 1521).
     • The system acquires a row-level lock on the orders table to prevent duplicate orders.
     • Data Pump exports order data to warehouses for shipping.

Exam Tip: How to Score Full Marks

1. Diagrams Are Mandatory

   • Always draw Oracle’s 3-layer architecture (memory, processes, storage) with labels.
   • For CDB/PDB, show the container-pluggable relationship (as above).

2. Locking Questions

   • Describe types: Row-level, table-level, DML, DDL.
   • Give an example: "When two bank tellers update the same account, Oracle uses row-level locks to prevent corruption."
   • Mention deadlocks: "Oracle resolves deadlocks by rolling back one transaction."

3. SQL Tuning Advisor

   • Explain AWR (Automatic Workload Repository) collects stats.
   • Recommendations: Indexes, SQL rewrites, materialized views.
   • Example: "For Daraz’s slow order query, the advisor suggested adding an index on order_date."

4. CDB/PDB

   • Define CDB (container) and PDB (portable tenant).
   • Advantage: "Isolates Ncell’s and NTC’s databases in one Oracle instance."
   • Command: CREATE PLUGGABLE DATABASE pdb_ncell ADMIN USER pdb_admin IDENTIFIED BY password;

5. Oracle Net Services

   • Listener: "Runs on port 1521 to accept client connections."
   • tnsnames.ora: "Maps service names (e.g., eSewa_DB) to IP/port."
   • Example: Show a connection string for eSewa.

Past Exam Questions Answered

1. Describe levels of locking in Oracle.

   • Answer: Oracle uses row-level locks (default for DML), table-level locks (LOCK TABLE), DML locks (for INSERT/UPDATE/DELETE), and DDL locks (exclusive for schema changes). Example: Two bank tellers cannot update the same account simultaneously without locks.

2. Explain Oracle architecture with a diagram.

   • Answer: Use the 3-layer model (memory: SGA/PGA; processes: DBWR, LGWR; storage: tablespaces/datafiles). Label Buffer Cache, Redo Log Buffer, and Background Processes.

3. How is SQL Tuning Advisor used?

   • Answer: It analyzes AWR data to find slow SQL (e.g., full table scans). For Daraz’s order system, it might suggest:

     CREATE INDEX idx_order_date ON orders(order_date);
     

4. Explain Container Database and Pluggable Database.

   • Answer: A CDB is a root container holding PDBs (e.g., Ncell’s and NTC’s databases). PDBs are portable and isolated. Command to create a PDB:

     CREATE PLUGGABLE DATABASE pdb_ncell;