SQL Tuning Advisor — Complete Guide

Introduction

SQL Tuning Advisor — Complete Guide is essential for developers and DBAs building OracleCore Enterprise Oracle Platform — Toolliyo's 96-article Oracle master path covering installation, architecture, SQL*Plus, multitenant PDBs, tablespaces, PL/SQL, RMAN, Flashback, AWR, RAC, Data Guard, OCI, 23ai features, and enterprise OracleCore projects. Every article includes EXPLAIN plans, SGA/PGA internals, transaction flows, and minimum 2 ultra-detailed enterprise database examples (banking RAC, airline reservations, telecom billing, ERP multitenant, healthcare TDE, OCI Autonomous, Data Guard DR).

In Indian IT and product companies (TCS, Infosys, HDFC, Flipkart), interviewers expect sql tuning advisor with real banking transactions, e-commerce scale, deadlock handling, and query tuning — not toy SELECT * demos. This article delivers two mandatory enterprise examples on Banking Core.

After this article you will

• Explain SQL Tuning Advisor in plain English and in Oracle SQL / instance architecture terms
• Apply sql tuning advisor inside OracleCore Enterprise Oracle Platform (Banking Core)
• Compare naive literal SQL vs OracleCore indexed, bind-variable, and AWR-monitored production patterns
• Answer fresher, mid-level, and senior Oracle SQL, PL/SQL, RAC, and DBA interview questions confidently
• Connect this lesson to Article 74 and the 96-article Oracle roadmap

Prerequisites

• Software: Oracle 23ai, SQL Developer or SQL*Plus
• Knowledge: Basic computer literacy
• Previous: Article 72 — ADDM — Complete Guide
• Time: 28 min reading + 30–45 min hands-on

Concept deep-dive

Level 1 — Analogy

SQL Tuning Advisor on OracleCore teaches Oracle step by step — architecture, PL/SQL, RAC, Data Guard, and enterprise database patterns.

Level 2 — Technical

SQL Tuning Advisor powers enterprise databases in OracleCore: normalized schemas, tuned indexes, ACID transactions, AWR monitoring, and secure bind-variable SQL. OracleCore implements Banking Core with RAC, Data Guard, and RMAN production patterns.

Level 3 — Query execution flow

[App / .NET / Java / PL/SQL]
       ▼
[Oracle Net → Listener → Service/PDB]
       ▼
[Parse → Optimize (CBO) → Execute]
       ▼
[B-tree indexes / Row locks / Redo log]
       ▼
[AWR · ADDM · RMAN · Data Guard]

Project structure

OracleCore/
├── tablespaces/         ← Datafiles and storage
├── schema/              ← Tables, views, constraints
├── indexes/             ← B-tree and bitmap indexes
├── plsql/               ← Packages and procedures
├── security/            ← Users, roles, TDE
├── ha/                  ← RAC + Data Guard
└── monitoring/          ← AWR · OEM · ADRCI

Step-by-Step Implementation — OracleCore (Banking Core)

Follow: design schema → write bind-variable SQL → add indexes → run EXPLAIN PLAN → wrap in transaction → enable AWR → integrate into OracleCore Banking Core.

Step 1 — Anti-pattern (literal SQL, no index, full scan)

-- ❌ BAD — literal SQL + full table scan
SELECT * FROM orders WHERE customer_id = ${customerId};
-- Hard-coded literal; no bind variable; no index on customer_id

Step 2 — Production Oracle SQL

-- ✅ PRODUCTION — SQL Tuning Advisor on OracleCore (Banking Core)
SELECT order_id, order_date, total
FROM orders
WHERE customer_id = :customer_id
ORDER BY order_date DESC
FETCH FIRST 50 ROWS ONLY;
-- Bind variable; index on (customer_id, order_date)

Step 3 — Full script

SELECT sql_id, elapsed_time, executions FROM v$sql
WHERE elapsed_time > 1000000 ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

-- Verify in SQL Developer: EXPLAIN PLAN + AWR top SQL
-- Check ADDM findings after deploy

The problem before Oracle — SQL Tuning Advisor

Mission-critical systems need proven ACID, HA, and DBA tooling. OracleCore replaces fragile setups with RAC, Data Guard, RMAN, and enterprise-grade security.

• ❌ Single-instance DB with no DR — hours of downtime on hardware failure
• ❌ Manual backups without RMAN validation — untested restore when crisis hits
• ❌ Full table scans on billion-row tables — billing batch misses SLA
• ❌ Shared SYSDBA credentials — audit failure and security breach risk

OracleCore applies Oracle architecture, indexing, RMAN, and HA patterns from day one.

Database architecture

SQL Tuning Advisor in OracleCore module Banking Core — category: PERFORMANCE.

AWR, ADDM, explain plans, RAC, ASM, and Data Guard HA.

[App / .NET / Java / PL/SQL]
       ↓
[Oracle Net → Listener → Service/PDB]
       ↓
[Instance: SGA + PGA + Background Processes]
       ↓
[Database Files: Datafiles, Redo, Control]
       ↓
[AWR · ADDM · RMAN · Data Guard]

SQL execution flow

Real-world example 1 — HDFC Banking Core on Oracle RAC

Domain: Banking / Fintech. Core banking requires 99.99% uptime and ACID transfers. OracleCore uses 2-node RAC, Data Guard standby, and explicit transactions with SELECT FOR UPDATE on account rows.

Architecture

CDB with PDB per business unit
  RAC nodes: srv1, srv2 (SCAN listener)
  Data Guard physical standby (DR site)
  tablespace BANK_DATA with ASSM

Oracle SQL / PL/SQL

CREATE TABLE accounts (
  account_id NUMBER PRIMARY KEY,
  balance NUMBER(18,2) NOT NULL,
  status VARCHAR2(20) DEFAULT 'ACTIVE'
);

-- Transfer with row lock
UPDATE accounts SET balance = balance - :amt
WHERE account_id = :from_id AND balance >= :amt;
UPDATE accounts SET balance = balance + :amt
WHERE account_id = :to_id;
COMMIT;

Outcome: Zero balance corruption; failover RTO 90s; RBI audit passed.

Real-world example 2 — Apollo Healthcare with TDE

Domain: Healthcare. Patient PHI must be encrypted at rest and audited. OracleCore enables TDE tablespace encryption and unified auditing on patient access.

Architecture

TDE wallet auto-login
  tablespace HEALTH encrypted
  UNIFIED AUDIT POLICY patient_access
  Data Guard for DR compliance

Oracle SQL / PL/SQL

CREATE TABLE patients (
  patient_id NUMBER PRIMARY KEY,
  mrn VARCHAR2(20) UNIQUE,
  diagnosis CLOB,
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP
) TABLESPACE health_enc;

AUDIT SELECT ON patients BY ACCESS WHENEVER SUCCESSFUL;

Outcome: HIPAA-aligned audit trail; pen test passed encryption checks.

DBA & performance tips

• Use bind variables in application SQL — reduces hard parses in shared pool
• Review AWR top SQL and ADDM findings weekly on production
• Test RMAN restore quarterly — backup without tested restore is worthless
• Document RAC/Data Guard runbooks before go-live

When not to use this Oracle pattern for SQL Tuning Advisor

• 🔴 Small dev apps — Oracle licensing and ops overhead may exceed benefit
• 🔴 Document-heavy flexible schema — consider NoSQL or JSON-first stores
• 🔴 RAC before exhausting single-instance tuning and Data Guard
• 🔴 Over-partitioning tiny tables — management cost exceeds query benefit

Testing & validation

-- Manual assertion
SELECT COUNT(*) INTO v_count FROM SQLTUNINGADVISOR WHERE status = 'ACTIVE';
-- Assert v_count = expected

Pattern recognition

Lookup by PK → index unique scan. Join heavy → index FK columns. Reporting → materialized views. Money moves → explicit COMMIT. Read scale → Active Data Guard standby. Slow after deploy → AWR top SQL.

Common errors & fixes

🔴 Mistake 1: Literal SQL with concatenated user input
✅ Fix: Use bind variables — prevents SQL injection and reduces hard parses.

🔴 Mistake 2: Missing indexes on WHERE/JOIN columns
✅ Fix: Create B-tree indexes on FK and filter columns used in joins.

🔴 Mistake 3: Long-running transactions holding row locks
✅ Fix: Keep transactions short; COMMIT minimal work units.

🔴 Mistake 4: Ignoring EXPLAIN PLAN and AWR reports
✅ Fix: Run EXPLAIN PLAN on new queries; review AWR top SQL weekly.

Best practices

• 🟢 Use bind variables — never concatenate user input into SQL
• 🟢 Index WHERE and JOIN columns on large Oracle tables
• 🟡 Enable AWR snapshots on every production database from day one
• 🟡 Run EXPLAIN PLAN after schema or data volume changes
• 🔴 Never run money/inventory updates outside explicit transactions
• 🔴 Never deploy without backup strategy and tested restore procedure

Interview questions

Fresher level

Q1: Explain SQL Tuning Advisor in a database design interview.
A: Cover schema, indexes, normalization trade-offs, concurrency, security, backup/HA, and monitoring.

Q2: B-tree vs bitmap index in Oracle?
A: B-tree is default for OLTP equality/range; bitmap suits low-cardinality DWH columns.

Q3: What is Oracle RAC?
A: Multiple instances share ASM storage; Clusterware handles node failover for continuous service.

Mid / senior level

Q4: How do you find and fix a slow query?
A: AWR top SQL → EXPLAIN PLAN → missing index? → add index → verify in next AWR.

Q5: Explain deadlock and how to prevent it.
A: Circular lock wait — consistent lock order, shorter transactions, retry in app.

Q6: How do you secure Oracle?
A: Least-privilege roles, no shared SYSDBA in apps, TDE, unified auditing, Oracle Net encryption.

Coding round

Write Oracle SQL for SQL Tuning Advisor in OracleCore Banking Core: show CREATE script, sample query, explain plan notes, and test assertions.

-- SQLTuningAdvisor validation
SELECT COUNT(*) AS actual FROM sqltuningadvisor WHERE is_active = 1;
-- Assert actual = expected

Summary & next steps

• Article 73: SQL Tuning Advisor — Complete Guide
• Module: Module 9: Performance & High Availability · Level: ADVANCED
• Applied to OracleCore — Banking Core

Previous: ADDM — Complete Guide
Next: Explain Plans — Complete Guide

Practice: Run today's SQL in SQL Developer with EXPLAIN PLAN — commit with feat(oracle): article-73.

FAQ