Skip to Main Content

SQL query statement profiling

Justin BleisteinJul 30 2021 — edited Jul 30 2021

I often come across complex SQL query statements which when I look at their execution plans I see objects which aren't in the FROM clause of the query text. I learn that they are synonyms, mvs, views data via db links, etc? This is important information. I often have to dig through the data dictionary to determine what is what, and I have to tear the SQL statement apart query block by query block to put this together. It's a form of reverse engineering for sure. Is there an Oracle supplied PL/SQL API or option in one of the IDEs like SQL Developer which will break down a long complex SQL statement to show what it's dependencies are, what the components of the query are, and also goes deeper, i.e. if the query is referencing a view you can see what the base table of that view is, what foreign key tables it's referencing, etc?

Post Details