Automated DBMS_XPLAN, Trace, and Send to Excel

11 02 2010

February 11, 2010

If you have spent some time looking at the posts on this site you might have seen my Toy Project for performance tuning mentioned in a couple of those posts.  One of the windows in the program allows me to submit a SQL statement to the database and retrieve the execution plan using DBMS_XPLAN while simultaneously generating a 10046, 10053, 10032, or 10033 trace files.  That program window looks like this:

What would it take to implement something like the above using a VBS script with an Internet Explorer browser window acting at the user interface?  It might also be nice to have the ability to send the query results into Excel on demand.  The end result might look something like this (note that the array fetch setting might not have any effect):

If we use the sample tables from this blog post, what is the execution plan for the following SQL statement?

  T3.C1 T3_C1,
  SUBSTR(T3.C2,1,10) T3_C2,
  T2.C1 T2_C1,
  SUBSTR(T2.C2,1,10) T2_C2,
  T1.C1 T1_C1,
  SUBSTR(T1.C2,1,10) T1_C2
  AND T1.C1=T2.C1

If we submit the SQL statement with the TYPICAL format parameter specified, the following execution plan will appear (note that on Vista and Windows 7, the execution plan may hide behind the main window – a pop-under effect):

The first 100 rows from the SQL statement appear at the bottom of the window.  With the TYPICAL format parameter specified, we are only able to determine the estimated number of rows that will be returned, and the estimated execution time.

If we change the format parameter to ALLSTATS LAST and change the Statistics Level to ALL, we are able to see the actual execution statistics for the plan:

Of course at this point, we might wonder if nested loops joins might be more efficient than hash joins, so we could test the change in execution time with a hinted SQL statement:

Looking closely at the plans, we see that the plan with the hash joins completed in 4.31 seconds, while the plan with the nested loops joins completed in 5.0 seconds.  The cost-based optimizer correctly selected the fastest executing plan for the SQL statement.

We also have the option for enabling several types of trace files and determining what, if any, performance impact we see when various trace files are enabled:

Notice that the program assigned a unique trace filename (displayed on the Status line) so that it is easy to find the trace file for our test execution.

The final option on the web page sends the query results into an Excel workbook – do not close the Excel window until you first close the Internet Explorer window, a new worksheet will be created in the workbook every time the Send to Excel button is clicked:

If you want to experiment with this script, you may download it here: XPlanViewerWithTrace.vbs (version 1.0, save as XPlanViewerWithTrace.vbs).


Update February 18, 2010:

See the documentation for details of the permissions required to use DBMS_XPLAN.DISPLAY_CURSOR.  See comment #2 for the items that need to be changed in the script in order to connect to your database.