Oracle Performance Interview Questions

Master Oracle Performance Tuning with interview-focused questions covering Cost-Based Optimizer, AWR, ASH, ADDM, SQL Trace, TKPROF, Wait Events, Buffer Cache, Shared Pool, PGA, Execution Plans, Parallel Query, and production best practices.

Introduction

Oracle Performance Tuning is one of the most important skills for

Oracle DBA
Java Backend Developer
Database Engineer
Performance Engineer
Solution Architect

A poorly tuned Oracle database can lead to

Slow Queries
High CPU Usage
Memory Exhaustion
Lock Contention
Long Response Time
Poor User Experience

Oracle provides powerful performance tuning tools such as

Cost-Based Optimizer (CBO)
EXPLAIN PLAN
SQL Trace
TKPROF
AWR
ASH
ADDM
Dynamic Performance Views

Oracle Performance Architecture

flowchart LR

Application --> OracleInstance["Oracle Instance"]

OracleInstance["Oracle Instance"] --> SqlParserCostBased["SQL Parser --> Cost Based Optimizer --> Execution Plan --> Execution Engine --> Buffer Cache --> DataFiles["Data Files"]"]

1. What is Oracle Performance Tuning?

Answer

Oracle Performance Tuning is the process of improving database performance by optimizing

SQL
Memory
Storage
Indexes
Configuration
Hardware

Goals

Faster Queries
Lower CPU
Better Throughput
Reduced Disk IO

2. Why is Performance Tuning important?

Without tuning

Slow SQL

↓

High CPU

↓

Application Timeout

With tuning

Optimized SQL

↓

Milliseconds

↓

Happy Users

3. What factors affect Oracle performance?

Major factors

SQL Queries
Indexes
Statistics
Buffer Cache
Shared Pool
PGA
Disk IO
Network
Hardware

Performance Workflow

flowchart LR

SlowQuery --> Analyze --> RootCause --> Optimization --> Validation

4. What is the Cost-Based Optimizer (CBO)?

Oracle's optimizer calculates

execution cost

using

Statistics
Indexes
Data Distribution
Cardinality

and selects

the lowest-cost execution plan.

5. What is Rule-Based Optimizer (RBO)?

Older Oracle versions supported

Rule-Based Optimizer.

It selected plans

using predefined rules.

Modern Oracle uses

Cost-Based Optimizer

6. What is EXPLAIN PLAN?

Displays

how Oracle plans

to execute SQL.

Example

EXPLAIN PLAN FOR

SELECT *

FROM employees;

SELECT *

FROM TABLE(DBMS_XPLAN.DISPLAY);

Execution Plan Flow

flowchart LR

SQL --> Optimizer --> ExecutionPlan

7. What information does an Execution Plan show?

Table Access
Index Usage
Join Order
Join Method
Cost
Estimated Rows

8. What is SQL Tuning?

Improving SQL statements by

Using indexes
Rewriting SQL
Reducing scans
Optimizing joins

9. What is SQL Trace?

SQL Trace records

Parse Time
Execute Time
Fetch Time
CPU
Wait Events

Useful for detailed SQL analysis.

10. What is TKPROF?

TKPROF formats SQL Trace output

into an easy-to-read performance report.

Shows

CPU Time
Elapsed Time
Logical Reads
Physical Reads
Execution Counts

SQL Trace Workflow

flowchart LR

SQLTrace --> TraceFile --> TKPROF --> PerformanceReport

11. What is AWR?

AWR

Automatic Workload Repository

stores historical database performance statistics.

Collected automatically.

12. What information does AWR contain?

SQL Statistics
Wait Events
CPU Usage
Memory Usage
IO Statistics
Active Sessions
Top SQL
Load Profile

13. What is ASH?

ASH

Active Session History

captures

active database sessions

every second.

Useful for diagnosing

performance issues.

14. What is ADDM?

ADDM

Automatic Database Diagnostic Monitor

analyzes AWR data

and recommends

performance improvements.

Oracle Diagnostic Tools

flowchart TD

Oracle --> AWR

Oracle --> ASH

Oracle --> ADDM

Oracle --> SQLTrace

SQLTrace --> TKPROF

15. What are Wait Events?

Oracle records

why sessions are waiting.

Examples

Disk IO
Lock Wait
Network
Latch
Buffer Busy Wait

16. Why are Wait Events important?

Instead of asking

"Why is Oracle slow?"

Oracle answers

"What is Oracle waiting for?"

17. What is Buffer Cache?

Buffer Cache stores

frequently accessed

database blocks

in memory.

Reduces disk reads.

Buffer Cache

flowchart LR

Disk --> BufferCache --> SQL

18. What is Shared Pool?

Stores

SQL Statements
Execution Plans
Data Dictionary Cache

Reduces hard parsing.

19. What is PGA?

Program Global Area

stores

session-specific memory.

Used for

Sorting
Hash Joins
Session Variables

SGA vs PGA

SGA	PGA
Shared	Private
SQL Cache	Sort Memory
Shared by Sessions	Per Process

20. What is Hard Parse?

Oracle must

Parse SQL
Validate Objects
Generate Execution Plan

Consumes CPU.

21. What is Soft Parse?

Oracle reuses

an existing execution plan.

Much faster.

Hard Parse vs Soft Parse

Hard Parse	Soft Parse
New Plan	Reuse Existing Plan
Slower	Faster
Higher CPU	Lower CPU

22. How do Bind Variables improve performance?

Instead of

WHERE id = 100

Use

WHERE id = :id

Benefits

Plan Reuse
Lower Parsing
Lower CPU

23. What is Parallel Query?

Oracle executes

one SQL query

using multiple CPU cores.

Useful for

Data Warehouse
Large Reports
Batch Jobs

Parallel Query

flowchart LR

LargeQuery --> CPU1

LargeQuery --> CPU2

LargeQuery --> CPU3

CPU1 --> Result

CPU2 --> Result

CPU3 --> Result

24. What Dynamic Performance Views are commonly used?

Common V$ views

V$SESSION
V$SQL
V$SYSTEM_EVENT
V$SQLAREA
V$LOCK
V$PROCESS
V$SGA
V$PGASTAT

25. What is V$SESSION?

Shows

currently connected sessions.

Useful for

Blocking Sessions
Long Running Queries
Active Users

26. What is V$SQL?

Contains

executed SQL

along with

CPU Time
Elapsed Time
Buffer Gets
Execution Count

27. Banking Example

Problem

Transfer Screen

15 Seconds

Investigation

AWR
ASH
Execution Plan

Solution

Index added

↓

Execution Time

20 ms

28. E-Commerce Example

Product Search

↓

SQL Trace

↓

Missing Index

↓

Milliseconds

29. Production Example

High CPU

↓

Check

V$SESSION

↓

AWR

↓

Top SQL

↓

Optimize Query

30. Common Oracle Performance Problems

Full Table Scans
Missing Indexes
Hard Parsing
Small Buffer Cache
Shared Pool Contention
Lock Contention
High IO
Outdated Statistics

Performance Tuning Workflow

flowchart LR

Alert --> AWR --> ASH --> ExecutionPlan --> SQLOptimization --> Validation

Enterprise Best Practices

Use Cost-Based Optimizer.
Keep statistics updated.
Use bind variables.
Analyze execution plans before production.
Monitor AWR reports regularly.
Use ASH for active session analysis.
Review ADDM recommendations.
Avoid unnecessary hard parsing.
Monitor Buffer Cache hit ratio.
Tune SQL before increasing hardware.

Quick Revision

Topic	Key Point
Performance Tuning	Improve Database Speed
Cost-Based Optimizer	Chooses Best Plan
EXPLAIN PLAN	Execution Plan
SQL Trace	SQL Diagnostics
TKPROF	Formats Trace
AWR	Historical Performance
ASH	Active Sessions
ADDM	Automatic Recommendations
Buffer Cache	Data Cache
Shared Pool	SQL Cache
PGA	Session Memory
Bind Variables	Reduce Hard Parse
Parallel Query	Multi-Core Execution

Interview Tips

Interviewers frequently ask

What is Oracle Performance Tuning?
Explain Cost-Based Optimizer.
What is AWR?
What is ASH?
What is ADDM?
SQL Trace vs TKPROF.
Buffer Cache vs Shared Pool.
Hard Parse vs Soft Parse.
Why use Bind Variables?
How do you troubleshoot a slow Oracle database?

A strong production troubleshooting answer is:

Check AWR report.
Review ASH for active sessions.
Identify Top SQL.
Generate execution plan.
Verify index usage.
Check wait events.
Tune SQL or indexes.
Validate improvements using AWR and monitoring tools.

This demonstrates real production Oracle performance tuning experience.

Summary

Oracle provides one of the industry's most comprehensive performance tuning ecosystems. Features such as the Cost-Based Optimizer, Execution Plans, SQL Trace, TKPROF, AWR, ASH, ADDM, and Dynamic Performance Views help engineers identify and resolve performance bottlenecks efficiently.

Understanding Oracle memory structures, optimizer behavior, SQL tuning, diagnostic tools, wait events, and performance monitoring is essential for Oracle Developers, Database Engineers, DBAs, Performance Engineers, and Solution Architects managing enterprise Oracle databases.