In theory you can use event 10053 traces to see what choices the optimizer has made, which is very interesting if your query didn’t perform as it was supposed to do. In practice event 10053 traces contain an overwhelming amount of information in an not always readable fashion. And which part was decisive for the bad performance of your query? There are only few people that can answer that.
Compare and conquer
One way of attacking an event 10053 trace is by not really reading it, but looking for differences between two traces with a tool to compare textfiles. I use this method when I have a query that performs good in one situation and not so great on another. You can use tools like vimdiff (Linux), UltraCompare (Windows, Linux, Mac) or Beyond Compare (Linux, Windows, Mac (beta)) to compare text files. Now make an event 10053 trace like this:
alter session set tracefile_identifier='compare'; alter session set events '10053 trace name context forever, level 1'; EXPLAIN PLAN FOR select * from table_x where blabla=10; alter session set events '10053 trace name context OFF';
Now do this also for the other situation (could be different databases, time of running, parameters, etc.), find the traces and open both in the text compare tool.
Below you see how that looks in Beyond Compare 3. In the far left you can see in red what parts of both files are different and you can scroll to those parts. In this case everything is different, because of differences in timestamps, hostnames and domains, database names, paths, object numbers and such, but the observant reader will spot the really imporant differences nevertheless.
Here you can think of differences in parameters, indexing, statistics (like numbers of rows), dynamic sampling and other stuff that you would only had noticed after querying a lot of the data dictionary.
Here’s an example where you can see that on one database an index was used and not on the other. The real challenge is of course to find out why that is. Either the index doesn’t exist on the other database, or you’ll have to look for other differences.
Oracle calculates different costs for the same table when run parallel or not parallel, as shown in this example. I found out that the default degree of this table calculated to 96, which made the optimizer rather optimistic about running the query in parallel. In reality the database had to share the CPU cores with many other databases.
To be honest: event 10053 traces are usually my last way out, when all my other analysis has been inconclusive. But if I can compare two traces of the same SQL, I tend to use it a lot sooner, because I know that by comparing I usually find something and it costs a lot less work.