Re: Stepping through the SQL Execution plan [was: WTB: Oracle Visual SQL Debugger]
Date: Thu, 23 Apr 2009 15:16:30 -0600
How about Tanel (Poder)'s os_explain:
At 02:54 PM 4/23/2009, Charles Schultz wrote:
>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
>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.
>Sent from Champaign, Illinois, United States
Centrex Consulting Corporation