Automated DBMS_XPLAN, Trace, and Send to Excel

11022010

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?

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.

Actions

Information

8 responses

11022010

Frank(08:57:41) :

Thanks for the tool Charles. One question, whether I paste a SQL Statement into the tool or type a statement into the tool I am receiving an error message:
“Error Opening SQL Statement “. Any idea why? (Sorry not much of a VB guy..)

I lost 90% of this post when I inserted the last picture in WordPress’ full screen view. It was late at night, so I did not have a chance to recreate all of the text that was part of the original blog article. In short, some of the instructions for using the tool were omitted.

What you need to do is to edit the VBS file using Windows Notepad or another text editor. Find this section of the code in the VBS file:

If your database name is ORCL, the username is SCOTT, and the password is TRIGGER, you would need to change the above section like this:

strUsername = "SCOTT"
strPassword = "TRIGGER"
strDatabase = "ORCL"

When entering a SQL statement, do not include the ending semicolon ( ; ) that is typically entered at the end of a SQL statement – the program should try to automatically remove the ending semicolon.

If you are still having trouble, you can copy the code from the VBS script file into an Excel macro – just type Sub Test in Excel’s Visual Basic editor, press the Enter key, and paste the VBS code just before the End Sub, it will look like this:

To try the code in Excel you may need to add a single quote ( ‘ ) in front of all of the lines that begin with Wscript.Sleep. With the code in Excel’s Visual Basic editor, you can debug the code to determine what is causing the problem (F5 runs the current procedure, F8 steps through the procedure line by line, and you can click in the left margin to cause the code execution to temporarily stop on that line).

Thanks for sharing this, it will definitely become part of my workflow as I am so done with messing with Toad and ugly SQLPLUS scripts. Impressive for 550 lines of code. My last foray into MS languages was FoxPro a decade ago but I am thinking I might need to brush up on my VBS.

For other potential users out there I had to grant the following to my generic user account in order to get the XPLAN features working:
GRANT EXECUTE ON DBMS_XPLAN TO DAVID;
GRANT SELECT ON V_$SESSION TO DAVID;
GRANT SELECT ON V_$SQL TO DAVID;
GRANT SELECT ON V_$SQL_PLAN TO DAVID;
GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO DAVID;

So I was reading your script comments and took the “I will leave that for someone else to code” challenge.

1) Added login window & invalid login logic
2) Changed window title to include username/database info – easier to get your bearings with 20 windows open :)
3) Added some comments and header noting you were original developer of the code. I didn’t catch if you were releasing for fun or intended to release under a specific license, hope I covered bases appropriately.

The only other thing I might do is change the Status textbox to be an IE status bar, other than that I already got a lot of use out of this program in the few days I have been using it, thanks again for sharing.

The script was released for educational purposes, as a way to challenge people to enjoy working with/learning about Oracle, and for a bit of fun. The header information that you added to the script is perfect.

Nice improvements to the original script – I like the logon window that you added to the script. The logon window was one of the things that I had planned to suggest to people to try adding to the script.

I guess that I now need to raise the bar. See if you are able to make the program handle SQL statements with bind variables. You can see that my Toy Project (in the first screen shot) is able to handle SQL statements with bind variables, and there are several examples on this site that show how to submit SQL statements with bind variables either in an Excel macro or in a VBS script. And if you are able to make that work, see if you are able to directly translate bind variable definitions found in a 10046 trace file so that you may use those bind variable definitions in the DBMS_XPLAN calls (my Toy Project does this). This change will then allow you to see how different hints affect execution plans, just as they would in a well written application that also uses bind variables – and also you will be able to experiment with the effects of bind variable peeking.

By the way, thanks for posting in your previous comment that permissions are needed on a couple of views in order to use DBMS_XPLAN – I had not considered that as a potential problem.

What is the sequence of events ?
1) Paste in a SQL Statement with bind variables into “SQL Statement to Execute” text field
2) Push it through an Explain Plan with 10046 tracing on
3) Load the Binds section from the 10046 into the lower left text field (is this manual or automatic?)
4) Populate the detailed list of binds in the bottom right
5) Update values of binds as desired
6) More tracing, XPLANS, etc to see the effect

Does this sound about right? Feel free to e-mail me offline if you don’t want to get too off-topic in this comment thread.

1) Paste in a SQL statement found in a 10046 trace file that was captured at either level 4 or level 12 – the screenshot that you referenced shows an apparent bug in Oracle Database 10.2.0.2 when CURSOR_SHARING was set to force. Oracle replaced constants with bind variables (the :”SYS_B_nn” bind variables) in a SQL statement that was already using bind variables (the :n bind variables).
2) Paste in the raw bind variable definitions found in a 10046 trace file that was captured at either level 4 or level 12.
3) Click the Translate Bind Variables button to read the raw bind variable definitions and translate into a user editable list of bind variable definitions and data values.
4) When the user clicks the DBMS XPLAN for Query button, replace all of the bind variable names (:”SYS_B_nn”, :n) with a single ? character (in memory, so that the user’s SQL statement is not changed). Add a bind variable parameter definition to an ADO Command type object (see how this is done already in the script for the comXPLAN object) for each defined bind variable. Set snpData = comData.Execute (assuming that you name the ADO Command object as comData). Read the returned rows from snpData as the script does now.
Steps 5 and 6 are the same as what you listed.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: