---

🎯 Oracle SQL Tuning – Interview Questions & Answers

---

1. What is SQL Tuning in Oracle?

Answer:
SQL Tuning is the process of improving the performance of SQL queries by optimizing execution plans, reducing resource consumption (CPU, I/O), and improving response time.

---

2. How do you identify a slow SQL query?

Answer:
We can identify slow SQL using:

• V$SESSION (currently running SQL)

• V$SQL (high resource SQL)

• AWR reports

• ASH reports

Example:

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

---

3. What is SQL_ID and why is it important?

Answer:
SQL_ID is a unique identifier for a SQL statement in Oracle.
It helps in:

• Tracking SQL performance

• Fetching execution plans

• Monitoring historical execution

---

4. How do you check the execution plan of a query?

Answer:
Using:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST'));

This shows:

• Execution steps

• Actual vs estimated rows

• Cost and time

---

5. What is the difference between EXPLAIN PLAN and DBMS_XPLAN.DISPLAY_CURSOR?

Answer:

Feature	EXPLAIN PLAN	DISPLAY_CURSOR
Type	Estimated	Actual
Source	Optimizer	Cursor cache
Accuracy	Less	High

---

6. How do you check historical performance of a SQL?

Answer:
Using AWR views:

• DBA_HIST_SQLSTAT

• DBA_HIST_SQLTEXT

This helps analyze:

• Plan changes

• Execution trends

• Performance degradation

---

7. What is PLAN_HASH_VALUE?

Answer:
PLAN_HASH_VALUE uniquely represents an execution plan.
If it changes, it means the execution plan has changed.

---

8. What is SQL Profile?

Answer:
A SQL Profile is a set of optimizer hints that helps Oracle generate a better execution plan without modifying the SQL.

---

9. How do you create a SQL Profile using COE script?

Answer:

1. Identify best plan:

START coe_xfr_sql_profile.sql <SQL_ID>

2. Generate profile:

START coe_xfr_sql_profile.sql <SQL_ID> <PLAN_HASH_VALUE>

3. Apply:

@coe_xfr_sql_profile_<SQL_ID>_<PLAN_HASH>.sql

---

10. How do you verify SQL Profile is being used?

Answer:

SELECT sql_id, plan_hash_value
FROM v$sql
WHERE sql_id = '<SQL_ID>';

Also:

SELECT * FROM dba_sql_profiles;

---

11. How do you drop a SQL Profile?

Answer:

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('profile_name');

---

12. What is SQL Tuning Advisor?

Answer:
SQL Tuning Advisor is an Oracle tool that analyzes SQL and provides recommendations such as:

• Index creation

• SQL Profile

• Statistics gathering

• Alternative execution plans

---

13. How do you run SQL Tuning Advisor?

Answer:

BEGIN
    DBMS_SQLTUNE.create_tuning_task(
        task_name   => 'TASK1',
        sql_id      => '<SQL_ID>',
        time_limit  => 60
    );

    DBMS_SQLTUNE.execute_tuning_task('TASK1');
END;
/

---

14. What kind of recommendations does SQL Tuning Advisor give?

Answer:

• Create index

• Gather statistics

• Accept SQL Profile

• Rewrite query

• Suggest better execution plan

---

15. What is the role of statistics in SQL tuning?

Answer:
Optimizer relies on statistics to estimate:

• Cardinality

• Cost

• Execution plan

Stale statistics can lead to bad plans.

---

16. What is cardinality misestimation?

Answer:
When optimizer incorrectly estimates number of rows, leading to:

• Wrong join method

• Poor execution plan

---

17. When should you use SQL Profile vs Index?

Answer:

Scenario	Use
Temporary fix	SQL Profile
Long-term optimization	Index

---

18. What are common causes of slow SQL?

Answer:

• Missing indexes

• Full table scans

• Stale statistics

• Bad execution plans

• High I/O operations

---

19. What is ALLSTATS LAST in DBMS_XPLAN?

Answer:
It shows actual execution statistics of the last run, including:

• Actual rows processed

• Actual time taken

---

20. Real Interview Scenario Question

Question:
A query suddenly became slow. What will you do?

Answer:

1. Capture SQL_ID

2. Check current execution plan

3. Compare with historical plans

4. Identify plan change

5. Use COE or SQL Profile

6. Run SQL Tuning Advisor

7. Validate performance

---

✅ Pro Tip (For Interviews)

Always say:

“First I check whether the issue is due to plan change or data growth, then I decide whether to fix using SQL Profile, Index, or statistics.”

---

🚀 Bonus: Quick One-Liners

• SQL_ID → Unique identifier

• PLAN_HASH_VALUE → Execution plan identifier

• SQL Profile → Optimizer hint set

• AWR → Historical performance data

• ASH → Real-time session activity

---