RE: Stepping through the SQL Execution plan [was: WTB: Oracle Visual SQL Debugger]

From: Bobak, Mark <>
Date: Thu, 23 Apr 2009 17:08:18 -0400
Message-ID: <>

Hi Charles,

Are you familiar w/ the work that Tanel Poder has done w/ connecting SQL execution plans to C function calls? It’s not a direct answer to your question, but you may find this:



From: [] On Behalf Of Charles Schultz Sent: Thursday, April 23, 2009 4:54 PM
To: Oracle-L Freelists
Subject: Stepping through the SQL Execution plan [was: WTB: Oracle Visual SQL Debugger]

After talking with some folks, it sounds like this functionality is not even available at present, so there are no such tools that can take advantage of it.

Which got me to thinking. Hypothetically, how would this work? We know Oracle builds a query plan for the purposes of being executed in a particular fashion. Would it be a "trivial" matter of adding a stop check flag (ie, breakpoint) to the existing code? I am thinking it would have to be more complex than that, since the execution code must be optimized to run extremely fast, and having a check for each operation could potentially be expensive.

I looked through Julian Dyke's most excellent "Internals" papers, but did not find any detailed information about query execution internals. Has anyone published anything along those lines?

I blame Jonathan Lewis for getting me started down this track. *grin* His copious contributions to the field, and the CBO in particular, are well thought-out and extremely helpful, and his personality of always looking for the truth is infectious. On Sat, Apr 18, 2009 at 21:55, Charles Schultz <<>> wrote: Good day, list,

When I was taking a C++ class in college, for a short time we had access to a really powerful X debugger (running on HPUX) that not only allowed one to step through code but depicted the state of any watched objects (variables, pointers, classes, etc) as a linked box with all relevant details (addresses, values, members, etc). I really miss that tool.

Is there anything related for stepping through SQL? Not PL/SQL, mind you. I am looking for a tool that can show me, graphically, how access predicates get rowids out of an index, which are then passed up to a table access with a filter predicate, then passed into a nested loop operation as a driving rowsource which dictates the rows wanted from the 2nd child operation. I want to see data; which rows were gotten and why, one row at a time. I am sure we have all see powerpoint slides that show us this detail one painful click at a time, but what about a run-time tool? Direct memory attach programs come to mind, but they usually do not cater to the same audience for some strange reason (*grin*). The audiences I have in mind are in classroom settings, teaching Jr. DBAs and developers; I doubt any experienced DBA would admit to wanting something like this. But I do. =)

PS - I did look around on google, but the hits were not promising. I was not able to find much information about Visual SQL 4.2 (seems old), nor the MS Visual Studio series - nothing in my quick driveby on the information superhighway really satisfied me.


Charles Schultz


Charles Schultz
Sent from Champaign, Illinois, United States
-- Received on Thu Apr 23 2009 - 16:08:18 CDT

Original text of this message