Forum

Forums
Oracle
Interview Questions...
Oracle Performance ...
 
Share:
Notifications
Clear all

Oracle Performance Tuning & Query Optimization Questions — Asked in Accenture & Infosys (2025 Edition)

 
Interview Questions & Answers
Last Post by SQL Admin 4 months ago
1 Posts
1 Users
0 Reactions
195 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Nov 09, 2025 5:49 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

Performance tuning is one of the most important skills for Oracle professionals — and it’s a hot topic in Accenture and Infosys technical interviews.

This 2025 guide compiles real Oracle performance tuning and optimization questions, helping you understand how to make SQL queries faster, reduce execution time, and optimize resource utilization in real-world scenarios.

These questions are ideal for Oracle developers, DBAs, and performance engineers preparing for production-level interviews.

🔹 Top Oracle Performance Tuning & Query Optimization Questions (2025 Edition)

1️⃣ What are the key steps in Oracle SQL performance tuning?

Answer:

  1. Identify slow-running SQL using AWR/ASH reports.

  2. Analyze the EXPLAIN PLAN to check table access paths.

  3. Tune the query using indexes, hints, or query rewrites.

  4. Monitor execution cost and I/O statistics.
    ✅ This structured answer is often expected in Infosys DBA interviews.

2️⃣ How do you view the execution plan of a SQL query in Oracle?

Use the EXPLAIN PLAN command:

 
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

📌 Helps identify full table scans, index scans, and join methods used by the optimizer.

3️⃣ What is the difference between a full table scan and an index scan?

TypeDescriptionPerformance
Full Table ScanReads all rows in the tableSlow for large tables
Index ScanUses an index to locate rowsMuch faster if column is indexed

✅ Common question in Accenture SQL optimization rounds.

4️⃣ What are Oracle optimizer modes?

Oracle’s Cost-Based Optimizer (CBO) chooses the best query plan based on statistics.
Modes include:

  • ALL_ROWS: Optimizes for throughput (best for large queries).

  • FIRST_ROWS: Optimizes for response time (best for OLTP).
    Example:

 
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

5️⃣ How can you detect and resolve high CPU usage in queries?

  • Identify high-CPU queries using:

     
    SELECT sql_id, cpu_time, executions FROM v$sql ORDER BY cpu_time DESC;

  • Tune inefficient queries (missing indexes, complex joins).

  • Update optimizer statistics using:

     
    EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

  • Break large transactions into smaller batches.

6️⃣ What are bind variables and why are they important?

Bind variables improve performance by allowing query reuse and preventing hard parses.
Example:

 
SELECT * FROM employees WHERE emp_id = :emp_id;

✅ A favorite question in Accenture Oracle interview rounds.

7️⃣ How do you identify and fix missing indexes?

  • Use EXPLAIN PLAN or AUTOTRACE to check access paths.

  • Look for “TABLE ACCESS FULL” on large tables.

  • Create appropriate indexes:

     
    CREATE INDEX idx_emp_dept ON employees(department_id);

  • Verify improvement via execution cost reduction.

8️⃣ What is a SQL hint and when should it be used?

Hints influence the optimizer’s behavior. Example:

 
SELECT /*+ INDEX(e idx_emp_dept) */ * FROM employees e WHERE department_id = 50;

⚠️ Use hints only when the optimizer’s plan is inefficient. Overuse can make SQL non-portable.

9️⃣ What is partitioning and how does it improve performance?

Partitioning splits large tables into smaller, manageable segments.
Types:

  • Range Partitioning — based on date or numeric range

  • Hash Partitioning — distributes rows evenly

  • List Partitioning — based on discrete values

✅ Reduces scan time and improves query parallelism.

Example:

 
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE
)
PARTITION BY RANGE(sale_date) (
PARTITION p_2024 VALUES LESS THAN (TO_DATE('01-JAN-2025', 'DD-MON-YYYY'))
);

🔟 What are AWR and ASH reports used for?

  • AWR (Automatic Workload Repository): Captures database performance statistics.

  • ASH (Active Session History): Tracks active sessions in real-time.
    They help identify high-load SQLs, wait events, and resource bottlenecks.

📌 Example:

 
@?/rdbms/admin/awrrpt.sql

Generates a detailed report for tuning analysis.

💡 Pro Tips for Oracle Query Optimization:

  • Keep statistics up to date using DBMS_STATS.

  • Use composite indexes for multi-column filtering.

  • Prefer set-based operations over row-by-row loops.

  • Use materialized views for heavy aggregation.

  • Monitor buffer cache hit ratio and shared pool usage.

🧩 Conclusion

These Oracle performance tuning and optimization questions (2025 Edition) asked in Accenture & Infosys interviews focus on real-world diagnostic and tuning skills.

Mastering execution plans, indexing strategies, and query rewrite logic will make you stand out in both developer and DBA interviews.
Keep practicing on Oracle LiveSQL or a local test environment to sharpen your optimization skills.


Topic Tags
Oracle Performance Tuning Oracle SQL Optimization Accenture Oracle DBA Que Infosys Query Performanc Oracle Execution Plan Tu

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Topic Tags:  Oracle Performance Tuning (1) , Oracle SQL Optimization (1) , Accenture Oracle DBA Que (1) , Infosys Query Performanc (1) , Oracle Execution Plan Tu (1) ,
Share: