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?

SELECT
  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
FROM
  T3,
  T2,
  T1
WHERE
  T1.C1=T3.C1
  AND T1.C1=T2.C1
  AND T1.C1 BETWEEN 1 AND 10

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

11 02 2010
Frank

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..)

11 02 2010
Charles Hooper

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:

strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

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:

Sub test()
'Version 1.0

Const adCmdText = 1
Const adCmdStoredProc = 4
Const adParamInput = 1
Const adVarNumeric = 139
Const adBigInt = 20
Const adDecimal = 14
Const adDouble = 5
Const adInteger = 3
Const adLongVarBinary = 205
Const adNumeric = 131
Const adSingle = 4
Const adSmallInt = 2
...
End Sub

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).

11 02 2010
David Mann

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;

15 02 2010
David Mann

Hey Charles,

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.

Code is here: http://ba6.us/CodeExamples/XPlan-Trace-Tool.zip

-Dave

15 02 2010
Charles Hooper

Dave,

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.

16 02 2010
David Mann

Oh man… more challenges :)

So I see the screenshot from the Toy here: http://hoopercharles.files.wordpress.com/2009/11/investigate3.jpg

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.

17 02 2010
Charles Hooper

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.

9 03 2010
Blogroll Report 05/02/2009 – 12/02/2010 « Coskan’s Approach to Oracle

[...] Charles Hooper-Automated DBMS_XPLAN, Trace, and Send to Excel [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: