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

Marcel-Jan is de PR-functionaris van de Werkgroep Maan en Planeten. Hij verzorgt ook het nieuws op de Facebook pagina en deze blog.
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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s