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

Warning (2022): this viewer was written for trace files from Oracle 10g – it’s not supposed to work with earlier versions, and the more recent the version of Oracle the more likely you are to set that sections of the file are missing from the output.

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:

I have managed to load it on to my own blog 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 the link and choose the option to “save target as”
  • The default filename to save will be tvzip1.doc
  • Edit the filename to (something like) and save
  • You now have a zip file from which you can extract a file called
  • change the extension of this file to .exe and you have the executable (DOS/Windows only).

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



  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,, WordPress will allow you to post it to a blog, and the displayed file name would be – 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?


    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.


    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

    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

    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 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 GMT 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 GMT Nov 23,2011 | Reply

  21. […] review trace 10053 LD_AddCustomAttr("AdOpt", "1"); LD_AddCustomAttr("Origin", "other"); […]

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

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: