Forum Stats

  • 3,759,490 Users
  • 2,251,552 Discussions
  • 7,870,675 Comments

Discussions

SQL query statement profiling

user476575
user476575 Member Posts: 121 Blue Ribbon

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?