DBA Hub

📋Steps in this guide1/26

Fixing Slow Queries in Oracle

Learn how to fixing slow queries in Oracle using a step-by-step DBA approach. Analyze execution plans, indexes, statistics, wait events, and SQL tuning.

oracle configurationintermediate
by OracleDba
15 views
1

Step 1: Confirm the Problem (Is the Query Really Slow?)

Before tuning, always validate the issue . Ask these questions: - Is the query slow now , or was it slow earlier? Is the query slow now , or was it slow earlier? - Is it slow for all users or only specific sessions? Is it slow for all users or only specific sessions? - Is the slowness consistent or intermittent ? Is the slowness consistent or intermittent ?
2

Useful Checks

Check: - Active vs waiting sessions Active vs waiting sessions - Long-running SQL Long-running SQL - Blocking sessions Blocking sessions

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
SELECT
status, state, seconds_in_wait
FROM
v$session
WHERE
username
=
'APP_USER'
;
3

Step 2: Identify the SQL Causing the Slowness

Once confirmed, identify the exact SQL statement .
4

From Active Sessions

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
SELECT
sql_id, sql_text
FROM
v$
sql
WHERE
sql_id
=
'&sql_id'
;
5

From AWR / ASH Reports

Look for: - High Elapsed Time High Elapsed Time - High CPU Time High CPU Time - High Buffer Gets High Buffer Gets - Frequent executions. Frequent executions.
6

Step 3: Check the Execution Plan (Explain Plan Is Mandatory)

The execution plan tells Oracle how it is fetching data.
7

Generate Execution Plan

Or for actual execution:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
EXPLAIN PLAN
FOR
SELECT
...;
SELECT
*
FROM
TABLE
(DBMS_XPLAN.DISPLAY);

SELECT
*
FROM
TABLE
(DBMS_XPLAN.DISPLAY_CURSOR(
'&sql_id'
,
NULL
,
'ALLSTATS LAST'
));
8

Red Flags in Execution Plans

- Full Table Scans on large tables Full Table Scans on large tables - Nested Loops on huge datasets Nested Loops on huge datasets - Cartesian joins Cartesian joins - Missing index access paths Missing index access paths
9

Step 4: Verify Index Usage and Strategy

Indexes are critical, but wrong or missing indexes cause slow queries.
10

Check Existing Indexes

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
SELECT
index_name, column_name
FROM
dba_ind_columns
WHERE
table_name
=
'EMPLOYEES'
;
11

Common Index Issues

- Index exists but not used Index exists but not used - Index on low-cardinality columns Index on low-cardinality columns - Function used on indexed column: Function used on indexed column: 💡 Solution: - Use function-based indexes Use function-based indexes - Rewrite SQL to avoid functions on columns Rewrite SQL to avoid functions on columns
12

Step 5: Check Statistics (Very Common Root Cause)

Oracle relies heavily on optimizer statistics . Stale or missing stats lead to bad plans.
13

Check Last Analyzed

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
SELECT
table_name, last_analyzed
FROM
dba_tables
WHERE
table_name
=
'ORDERS'
;
14

Gather Fresh Statistics

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXEC
DBMS_STATS.GATHER_TABLE_STATS(
  ownname
=
>
'SCHEMA'
,
  tabname
=
>
'ORDERS'
,
  cascade
=
>
TRUE
);
15

Step 6: Analyze Bind Variables & Hard Parsing

Lack of bind variables causes: - Excessive hard parsing Excessive hard parsing - High CPU usage High CPU usage - Library cache contention Library cache contention
16

Bad Example

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
*
FROM
emp
WHERE
empno
=
7369
;
SELECT
*
FROM
emp
WHERE
empno
=
7499
;
17

Good Example

Check parsing:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
SELECT
parse_calls, executions
FROM
v$
sql
WHERE
sql_id
=
'&sql_id'
;
18

Step 7: Check Wait Events (Database Is Waiting for What?)

If SQL is slow, Oracle is waiting on something .
19

Identify Wait Events

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
SELECT
event, total_waits, time_waited
FROM
v$system_event
ORDER
BY
time_waited
DESC
;
20

Step 8: Review SQL Logic & Rewrite If Needed

Sometimes the issue is not Oracle — it’s bad SQL design .
21

Common SQL Mistakes

- Using Using - Unnecessary joins Unnecessary joins - Correlated subqueries Correlated subqueries - Using instead of (in some cases) Using instead of (in some cases)
22

Example Rewrite

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- Bad
SELECT
*
FROM
orders
WHERE
order_id
IN
(
SELECT
order_id
FROM
payments);
-- Better
SELECT
o.
*
FROM
orders o
WHERE
EXISTS
(
SELECT
1
FROM
payments p
WHERE
p.order_id
=
o.order_id
);
23

Step 9: Validate Fixes in Lower Environment First

Never apply changes directly in production without testing. Validate: - Execution plan improvement Execution plan improvement - Reduced elapsed time Reduced elapsed time - No functional impact No functional impact Use: - SQL Monitor SQL Monitor - AWR comparison AWR comparison - Application testing Application testing
24

Step 10: Monitor After Fix (Very Important)

After tuning: - Monitor performance for next few days Monitor performance for next few days - Check for plan regression Check for plan regression - Use SQL Plan Baselines if required Use SQL Plan Baselines if required

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
ALTER
SYSTEM
SET
optimizer_capture_sql_plan_baselines
=
TRUE
;
25

Final Thoughts

Fixing slow queries in Oracle is about discipline and process , not shortcuts.
26

Key Takeaways

- Identify the real problem - Analyze execution plans - Use correct indexes & statistics - Optimize SQL logic - Monitor continuously When done correctly, performance tuning leads to: - Faster applications Faster applications - Stable production systems Stable production systems - Happy users 😄 Happy users 😄 Explore more with Learnomate Technologies! Want to see how we teach? Head over to our YouTube channel for insights, tutorials, and tech breakdowns: www.youtube.com/@learnomate To know more about our courses, offerings, and team: Visit our official website: www.learnomate.org Interested in mastering Oracle Database Administration? Check out our comprehensive Oracle DBA Training program here: https://learnomate.org/oracle-dba-training/ Want to explore more tech topics? Check out our detailed blog posts here: https://learnomate.org/blogs/ And hey, I’d love to stay connected with you personally! Let’s connect on LinkedIn: Ankush Thavali Happy learning! Ankush😎

Comments (0)

Please to add comments

No comments yet. Be the first to comment!