SQL Performance (MOSC)

MOSC Banner

How to map explain plan and predicate information to get exact SQL code line number?

edited May 31, 2018 5:00AM in SQL Performance (MOSC) 3 commentsAnswered ✓

How to map explain plan and predicate information to get exact SQL code line number?

Example:

From below explain plan, the operation at line number 309 is doing a full table scan on 48million row table. And, the predicate information section displays (access(:Z>=:Z AND :Z<=:Z)) for the explain plan line number. The SQL code is very lenghty (more than 2000 lines and referencing some views which too has > 2000 lines). How can I spot the exact line in the SQL code which is fulfilling the operation?

Plan hash value: 2785661414

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                              | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center