Oracle Scratchpad

April 30, 2010

10053 viewer

Filed under: CBO,Execution plans,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 7:49 pm BST Apr 30,2010

I’ve been trying to find a way to post an executable for several weeks because I’ve been sent a simple viewer for 10053 trace files written by Hans-Peter Sloot of Atos Origin and Robert van der Ende. They wrote this viewer because trace files from event 10053 can be enormous, and scrolling back and fore through them to cross reference the interesting bits can be extremely tedious. Their “tree-viewer” allows you to see all the important headings and expand only the detail you’re interested in.

It works very simply: start the program from a command line, wait for the graphic interface to pop up and click on “Open trace file” to get a standard “File Open” menu from windows. After opening your selected file you get a drop-down list of registered queries in that file; scroll down to one of them and click on “Show trace file”. From that point on you can click on the “+” and “-” signs that appear in the text, or make use of the right (+) and left (-) arrows to expand and contract text.

The following is a screen snapshot (which may look very messy on smaller screens) of the tool in action:

If you want to download the viewer, I’ve been able to post it the Oak Table Network site but had to do something a little strange with it. I’ve saved it on the site with a “.doc” suffix, so it appears to WordPress to be a Word document. To download it and use it you have to do the following:

Right click on this link and choose the option to “save target as”
The default filename to save will be new_treeview.zip.doc
Edit the filename to remove the .doc suffix and then save the file
You now have a zip file that should unzip correctly.

I have tested the download from this page and it does seem to be working properly.

Note: this is a viewer for trace files from Oracle 10g – it’s not supposed to work with earlier versions.

Update Dec 2011:

Hans-Peter sent me an updated file over a year ago – fixing some bugs in the earlier version – and I’ve only just got around to uploading it. It’s using the same odd naming tricks as the original, but I have managed to load it on to my own blog this time around. If you download this version you’ll get a file called tvzip1.doc on your system; change the file extension from .doc to .zip and you will be able to extract from it a file called tv10053.xxx; change the extension to .exe and you have the executable (DOS/Windows only).

29 Comments »

  1. Finally it became easy to go through the 10053 trace.

    Thank your for sharing

    Comment by coskan — April 30, 2010 @ 8:00 pm BST Apr 30,2010 | Reply

  2. I found that if you give the file a double extension, for example, 10053tree_view.zip.doc, WordPress will allow you to post it to a blog, and the displayed file name would be 10053tree_view.zip – of course you would probably need to leave a note that the filename should be changed when it is downloaded.

    This method has worked for me with Excel files, VBS scripts, and ZIP files.

    Comment by Charles Hooper — April 30, 2010 @ 8:14 pm BST Apr 30,2010 | Reply

  3. Jonathan, the zip file seems to be corrupted. Any ideas?

    Raj

    Comment by Raj Jamadagni — April 30, 2010 @ 8:16 pm BST Apr 30,2010 | Reply

  4. I cannot open the winzip file either.

    Comment by Brian — April 30, 2010 @ 8:35 pm BST Apr 30,2010 | Reply

  5. Okay, problem solved. The file now seems to download properly from the Oak Table site, but I’ve had to upload it with a .doc extension (as per Charles Hooper’s suggestion above) so you have to knock that off when you save it to your local machine.

    Comment by Jonathan Lewis — April 30, 2010 @ 9:51 pm BST Apr 30,2010 | Reply

  6. Ok, that worked. Many thanks.

    Raj

    Comment by Raj — May 1, 2010 @ 1:50 am BST May 1,2010 | Reply

  7. Many Thanks for this useful tool!

    Comment by kireal — May 1, 2010 @ 2:10 am BST May 1,2010 | Reply

  8. Another graphical tool is SQLTXPLAIN (AKA sqlt).

    It is provided by Oracle Support.

    MetaLink note: 215187.1
    https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=215187.1

    Comment by Eyal Yurman — May 2, 2010 @ 11:26 am BST May 2,2010 | Reply

    • Eyal,

      Thanks for mentioning this: sqltxplain is an excellent tool for extracting and reporting all the information you need to analyse a statement – but I don’t think it does anything with the 10053 trace file. I’ve just checked the latest release notes on MOS for 10.2 and 11.2, but don’t see the 10053 mentioned at all.

      Comment by Jonathan Lewis — May 2, 2010 @ 11:49 am BST May 2,2010 | Reply

      • Hi,

        SQLT automatically generates a 10053 trace file (At least with XECUTE method) and alot of the information is entered into the HTML report.

        But unfortainaly the “OPTIMIZER STATISTICS AND COMPUTATIONS” part is left outside of the HTML report.

        Comment by Eyal Yurman — May 2, 2010 @ 1:48 pm BST May 2,2010 | Reply

  9. Tool works fine in 11.2 too

    Comment by Fairlie Rego — May 2, 2010 @ 11:06 pm BST May 2,2010 | Reply

  10. Very nice! Finally a clean way to visualize the traces from 10053. Hotsos makes a product that grabs trace files as well.

    Comment by Ben Prusinski — May 3, 2010 @ 12:15 am BST May 3,2010 | Reply

  11. Thanks a lot for sharing this tool!
    Now i’ll get back to study Your book about CBO…

    Comment by Alexander 'sure' Podkopaev — May 3, 2010 @ 4:19 pm BST May 3,2010 | Reply

  12. A very useful tool even if it lacks a bit of colour
    :-)

    Comment by Ahmed AANGOUR — May 5, 2010 @ 8:54 am BST May 5,2010 | Reply

  13. If anyone would like to have the code for this viewer, Hans-Peter has told me that you can email him and he will let you have it. His email address is hans-peter.sloot@atosorigin.com

    Comment by Jonathan Lewis — May 6, 2010 @ 5:56 pm BST May 6,2010 | Reply

  14. [...] Lewis, on Oracle Scratchpad, posts a brilliant little utility that makes viewing 10053 trace files much easier. The only drawback is that most don’t look at 10053 traces all that often, so it [...]

    Pingback by Log Buffer #188, a Carnival of the Vanities for DBAs | The Pythian Blog — May 7, 2010 @ 3:56 pm BST May 7,2010 | Reply

  15. Thanks for sharing this !

    Comment by Guillaume Goulet-Vallières — May 7, 2010 @ 7:17 pm BST May 7,2010 | Reply

  16. [...] системы (instance) — Igor Usoltsev @ 11:40 Tags: CBO, Oracle tools В блоге Jonathan Lewis появилась ссылка на полезную программу 10053tree_view.exe для [...]

    Pingback by 10053-viewer для Windows « Oracle mechanics — May 16, 2010 @ 10:49 am BST May 16,2010 | Reply

  17. [...] Hier de link naar het artikel op de Oracle scratchpad website. [...]

    Pingback by 10053 trace file viewer « DBdude — May 18, 2010 @ 8:47 am BST May 18,2010 | Reply

  18. [...] in the way of information about it out there, although I did see a reference to it in a comment on one of Jonathan’s recent posts. Maybe I’ll get around to doing another post on that topic some other time. Anyway, the name [...]

    Pingback by Kerry Osborne’s Oracle Blog » Blog Archive SQLT - coe_xfr_sql_profile.sql - Kerry Osborne’s Oracle Blog — July 24, 2010 @ 8:03 pm BST Jul 24,2010 | Reply

  19. [...] oddity with a distributed query between two 11.1.0.7 databases a few days, I noticed something in the 10053 trace file that made me change my mind, and go back to look at earlier versions of [...]

    Pingback by Distributed Queries – 2 « Oracle Scratchpad — November 24, 2010 @ 9:46 am BST Nov 24,2010 | Reply

  20. [...] trace file viewer by Jonathan [...]

    Pingback by Usn's IT Blog » DOAG Conference 2011 – Impressions and Look-at’s — November 23, 2011 @ 7:47 am BST Nov 23,2011 | Reply

  21. [...] review trace 10053 https://jonathanlewis.wordpress.com/2010/04/30/10053-viewer/ LD_AddCustomAttr("AdOpt", "1"); LD_AddCustomAttr("Origin", "other"); [...]

    Pingback by 10053 viewer « Oracle Spin for Project Managers — November 26, 2011 @ 6:56 pm BST Nov 26,2011 | Reply

  22. Jonathan, there is another free 10053 trace viewer. It uses a navigation section, similar to the Contents section in PDF reader, for example. It allows jumping to the most important parts of 10053 trace. It also uses highlighter to improve readability of the trace and optionally formats the final query after transformations. Here is the link: http://www.lab128.com/free_downloads.html
    Regards,

    Comment by Sergei Romanenko — January 9, 2012 @ 9:37 pm BST Jan 9,2012 | Reply

  23. I have tried both and found the Lab128 product to be better. The other tool was not picking up some of the query blocks.

    Comment by vikramrathour — January 7, 2014 @ 4:38 am BST Jan 7,2014 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers