Query tuning in Oracle

 

1. First Understand Query Tuning in One Line

Query tuning means:

“Reduce the amount of data Oracle reads.”

Because Oracle performance problem is mostly:

• Too many rows scanned
• Too many blocks read
• Wrong execution plan
• Wrong indexes used
• Unnecessary sorting/grouping/joining

2. Golden Rule of Query Tuning

Always ask these 5 questions:

3. Understand Execution Plan First (MOST IMPORTANT)

TABLE ACCESS FULL

means:

Oracle is reading entire table.

Like reading full book to find one word.

---

Example

Suppose EMP table has:

• 10 million rows

Query:

select * from emp where emp_id = 100;

If EMP_ID index exists:

Oracle reads few blocks only.

FAST.

---

But if query is:

select * from emp where upper(emp_name) = 'AMIT';

Even if index exists on EMP_NAME:

Oracle may do FULL TABLE SCAN.

Why?

Because:

upper(emp_name)

changed the indexed column.

4. Biggest Query Tuning Logic

NEVER APPLY FUNCTION ON INDEXED COLUMN

This is the most important tuning rule.

---

BAD

where trunc(order_date) = sysdate

Oracle cannot use normal index efficiently.

---

GOOD

where order_date >= trunc(sysdate) and order_date < trunc(sysdate)+1

Now index can be used.

---

Your Document Example

BAD:

TO_CHAR(t.DATE_TIME, 'MM/DD/YYYY')

BAD because function applied on column.

---

BETTER:

t.date_time >= trunc(sysdate) and t.date_time < trunc(sysdate)+1

This is real tuning.

5. Why FULL TABLE SCAN Happens

Oracle chooses FTS when:

• Large percentage rows needed
• No index
• Function used on indexed column
• Wrong datatype
• Optimizer thinks FTS cheaper

---

Important Interview Question

User already asked before:

If 75% rows fetched then INDEX or FTS?

Answer:

If query fetches huge amount of rows (like 75%), Oracle usually chooses:

FULL TABLE SCAN

Because using index + table access becomes costlier.

6. Learn Query Tuning Like Doctor Diagnosis

Step 1 — Check Execution Plan

Use:

explain plan for
<select query>;

select * from table(dbms_xplan.display);

OR

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));




7. Most Important Operations in Execution Plan






8. Real Layman Logic of Joins


NESTED LOOP
Like:
For every employee check department.
Good for:

Small data

Index exists



HASH JOIN
Like:
Create hashmap in memory and match.
Good for:
Large tables



9. Biggest Beginner Mistakes in Query Tuning


Mistake 1 — SELECT *

BAD: select * from orders;

GOOD: select order_id, customer_id from orders;

Less columns = less I/O.


Mistake 2 — Functions on Columns

BAD: where upper(name)='AMIT'


Mistake 3 — Leading Wildcard

BAD: where name like '%AMIT'

Index cannot be used.


GOOD: where name like 'AMIT%'


Mistake 4 — Wrong Join

BAD: where emp.name = dept.name

GOOD: where emp.dept_id = dept.dept_id

Join on numeric indexed columns.


10. DISTINCT Logic (Very Important)

DISTINCT means:
Remove duplicates.
But sorting required.
Sorting = expensive.


BAD :
select distinct emp_id from emp;

If EMP_ID already primary key:
DISTINCT useless.


Real Tuning Logic
Avoid unnecessary:

DISTINCT

ORDER BY

GROUP BY

Because all require SORT.


11. HAVING vs WHERE

BAD
select dept, avg(sal)
from emp
group by dept
having dept='IT';

HAVING runs AFTER grouping.


GOOD
select dept, avg(sal)
from emp
where dept='IT'
group by dept;

WHERE filters EARLY.
Less rows processed.
FASTER.


12. Correlated Subquery Problem


BAD
select *
from emp e1
where sal >
(
 select avg(sal)
 from emp e2
 where e1.deptno=e2.deptno
);

Subquery runs repeatedly.

Very slow.


BETTER : Use JOIN + GROUP BY.


13. Real Oracle DBA Tuning Flow

This is how senior DBAs tune.


STEP 1 — Identify Slow SQL

Using:

AWR

ASH

OEM

V$SQL


STEP 2 — Check Execution Plan

Look for:

FTS

Expensive joins

Sorts

High cost


STEP 3 — Check Statistics

Outdated stats cause bad plans.

exec dbms_stats.gather_table_stats(...)


STEP 4 — Check Indexes

Questions:

Missing index?

Wrong index?

Composite index needed?

Function causing index bypass?



STEP 5 — Rewrite Query
Most tuning happens here.


14. Simple Query Tuning Checklist

Memorize this.

Query Tuning Master Checklist

A. Filtering
✔ Filter early

✔ Reduce rows early

✔ Use WHERE properly


B. Index
✔ Indexed column used?

✔ Function on column removed?

✔ Leading wildcard avoided?


C. Joins
✔ Join indexed columns

✔ Numeric joins better

✔ Correct join order


D. Sorting
✔ Avoid unnecessary DISTINCT

✔ Avoid unnecessary ORDER BY

✔ Avoid unnecessary GROUP BY


E. Data Volume
✔ Fetch only required columns

✔ Fetch fewer rows


15. Real Example from Your Document

Your query contains:
TO_CHAR(t.DATE_TIME, 'MM/DD/YYYY')

This is one major tuning issue.


Rewrite

BAD:
TO_CHAR(t.DATE_TIME, 'MM/DD/YYYY')

GOOD:
t.date_time >= trunc(sysdate)
and t.date_time < trunc(sysdate)+1


Another issue:
Repeated conditions in outer query + subquery.
This increases work.


Another issue:

Too many FULL TABLE SCANS.
Meaning indexes missing/not used.


16. Exact Mindset to Become Query Tuning Expert

Whenever you see SQL, think:

How many rows?

Which table biggest?

Which filter reduces rows most?

Can index be used?

Is sorting happening?

Can subquery become join?

Can function be removed?

Can data be filtered earlier?



17. Best Way to Learn Query Tuning Practically

Do this daily.


Practice Method
Take one slow query.

Then:
Step 1

Run: explain plan


Step 2
Identify:
FTS

joins

sorts



Step 3
Rewrite query.


Step 4
Compare plans.


18. MOST IMPORTANT THING

Query tuning is NOT magic.
It is mainly:
“Reducing unnecessary work Oracle performs.”
That’s all.


19. Final Shortcut Formula
Remember this forever:
Fast Query Formula
Less Rows Read
+ Proper Index
+ Early Filtering
+ Good Join
- Unnecessary Sorting
= Fast SQL


20. Your Next Learning Path

To become master in query tuning, learn in this order:

Execution Plan

Indexes

Joins

Explain Plan Cost

Cardinality

Histograms

Bind Peeking

SQL Profiles

Partitioning

Parallelism



21. One Real DBA Secret

Senior DBAs do NOT memorize tuning.

They simply ask:
“Why is Oracle reading so much data?”
That single question solves most tuning issues.


For learning execution plans visually, this formula is extremely important:

$\text{Performance} \propto \frac{1}{\text{Rows Read} + \text{Sort Cost} + \text{Join Cost}}$

And index selectivity concept can be remembered like this:

$\text{Selectivity} = \frac{\text{Rows Returned}}{\text{Total Rows}}$


Low selectivity (few rows returned) → INDEX preferred

High selectivity (many rows returned) → FULL TABLE SCAN preferred