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

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:
http://blog.tanelpoder.com/2008/06/15/advanced-oracle-troubleshooting-guide-part-6-understanding-oracle-execution-plans-with-os_explain/

interesting.

-Mark

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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 
<sacrophyte@xxxxxxxxx<mailto:sacrophyte@xxxxxxxxx>> 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

Other related posts: