{"id":1701,"date":"2013-03-18T12:00:00","date_gmt":"2013-03-18T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1701"},"modified":"2013-03-18T12:00:00","modified_gmt":"2013-03-18T12:00:00","slug":"10053-trace-files-getting-started","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2013\/03\/18\/10053-trace-files-getting-started\/","title":{"rendered":"10053 Trace Files – Getting Started"},"content":{"rendered":"

Before getting into the contents of a 10053 trace file and looking at any useful stuff, you need to know what the files are for and how and where they are created. <\/p>\n

Essentially, setting event 10053 causes the Cost Based Optimizer to write information to a trace file describing the information it is using and the results of it’s calculations whilst walking through the decision-making process to determine the best execution plan. It includes the options that it has considered and discarded, those that it has accepted and options which are unavailable for various reasons. Because the decision making process is detailed and extensive, the files tend to be large for all but the most trivial statements and I’d challenge most people to read and understand an entire 10053 trace file! However, you are often looking for the reason for a particular bad decision, which helps to narrow the scope and, personally, I’ve found recent versions of 10053 trace files much more verbose and readable.<\/p>\n

The best and most detailed reference I’ve seen is Wolfgang Breitling’s paper ‘A Look under the Hood of the CBO<\/a>‘ although it was written many years ago and, as with all low-level undocumented Oracle information, things change frequently and without warning. At the start of the paper he describes how to set event 10053 to generate the related trace file in the instance’s user dump destination. However, since Oracle 11g, there is a more flexible way to generate the trace file that Maria Colgan describes in a couple of posts on the Optimizer Development Groups blog here <\/a>and here<\/a>. The latter post is particularly interesting because that approach automatically triggers a hard parse of the statement in order to generate the trace file. <\/p>\n

Which solves what I suspect is one of the most confusing aspects of generating 10053 trace files when you’re first getting used to it. The statement needs to be hard-parsed to ensure that the trace file will be generated. It might help you to remember what it is that is being traced – the CBO making it’s decisions as it chooses an optimal execution plan. So, if the plan has already been generated then no trace file! One simple technique to get around this prior to 11g is to add a new comment to the statement to force a hard parse. Remember that if you find that the trace file is not being produced as expected, that might be the reason.<\/p>\n

In the next post, I’ll start to look at the contents of a 10053 trace file.<\/p>\n","protected":false},"excerpt":{"rendered":"

Before getting into the contents of a 10053 trace file and looking at any useful stuff, you need to know what the files are for and how and where they are created. Essentially, setting event 10053 causes the Cost Based Optimizer to write information to a trace file describing the information it is using and… Continue reading 10053 Trace Files – Getting Started<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1701","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1699,"url":"http:\/\/orcldoug.com\/blog\/2013\/03\/17\/10053-trace-files\/","url_meta":{"origin":1701,"position":0},"title":"10053 Trace Files","date":"March 17, 2013","format":false,"excerpt":"Sometimes I'm really not sure whether a blog post is a good idea or not. This is one of those times.I remember a while ago that Neil Chandler wrote a blog post about why you probably don't need 10046 trace files that I didn't completely agree with and I kept\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1705,"url":"http:\/\/orcldoug.com\/blog\/2013\/09\/02\/10053-trace-files-global-stats-on-partitioned-tables\/","url_meta":{"origin":1701,"position":1},"title":"10053 Trace Files – Global Stats on Partitioned Tables","date":"September 2, 2013","format":false,"excerpt":"One of the reasons why it's taken a while to get around to the next 10053 trace file post (apart from the more human reasons I talked about here) is that I'd planned to show how 10053 trace files can show whether the CBO has used Global Statistics on Partitioned\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1623,"url":"http:\/\/orcldoug.com\/blog\/2011\/01\/18\/cost\/","url_meta":{"origin":1701,"position":2},"title":"Cost","date":"January 18, 2011","format":false,"excerpt":"As soon as I saw the title of Jonathan Lewis' post, I had an inkling of what it might have to say and I wasn't too far off the mark. Although I don't disagree with a single statement of his post (I've read it a few times to make sure),\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":976,"url":"http:\/\/orcldoug.com\/blog\/2005\/05\/29\/px-slave-trace-files\/","url_meta":{"origin":1701,"position":3},"title":"PX Slave Trace Files","date":"May 29, 2005","format":false,"excerpt":"It's sad that it takes a Bank Holiday weekend for me to get round to doing some more research on parallel execution. Anyway, I was on the verge of modifying the following line from the PX paper ...The trace files for the slave processes may be created in background_dump_dest, rather\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1031,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/21\/monitoring-index-usage-ii-event-10132\/","url_meta":{"origin":1701,"position":4},"title":"Monitoring Index Usage II – Event 10132","date":"July 21, 2006","format":false,"excerpt":"That's a shame.I was hoping that setting the event at the instance level might generate one trace file in the background_dump_dest but, like event 10046, it generates one trace file per session in user_dump_dest. I should have expected that, but I suppose my optimism blinded me.It doesn't make event 10132\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1112,"url":"http:\/\/orcldoug.com\/blog\/2009\/10\/29\/10g-consolidated-trace-files-and-px\/","url_meta":{"origin":1701,"position":5},"title":"10g Consolidated Trace Files and PX","date":"October 29, 2009","format":false,"excerpt":"At the end of my Tracing Parallel Execution presentation at the Scottish OUG conference, Michael M\u00f8ller of Miracle asked whether the consolidated trace files produced by the DBMS_MONITOR package and trcsess utility are in strict time order. i.e. Will I see the actions of the query coordinator, then the actions\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1701","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1701"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1701\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1701"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}