Useful stuff from event 10053 traces – part 1

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.

Comparing event 10053 traces

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.

Comparing event 10053 traces with and without an index

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.

Event 10053 traces compared - different costs

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.

Advertisements

About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning and tagged , , , , , , , , . Bookmark the permalink.

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