Autotrace – no cost

This was a bit of a throwback which threw me for a few moments, so long has it been since I came across it.

I was investigating the reported slow performance of a query on an 8.1.7.4 database.

So, I thought I would do a quick and easy autotrace using “set autotrace trace explain”.

Not unsurprisingly, a comparison with another system showed different explain plans, with the slow system having horrendous numbers in the cost, cardinality and bytes details.

As a next step, I thought I would break down the query into simpler parts to try to investigate the differences.

The query itself is not complex but involves two subqueries:

select c.newsid, c.RELNEWSID, c.ITEMORDER
from relatednewsnews c
where c.NEWSID in
 (select b.newsid
  from newsindex b
  where b.sectionid in
  (select a.id
  from descriptions a
  start with a.id in 109193
  connect by childof = prior a.id));

I thought I would strip off the outer layers and compare the inners.
As soon as I stripped off the outer layer and tried:

select b.newsid
  from newsindex b
  where b.sectionid in
  (select a.id
  from descriptions a
  start with a.id in 109193
  connect by childof = prior a.id)

I noticed something odd but the penny did not drop instantly.

Still doing “set autotrace trace explain”, the cost, cardinality and bytes details had disappeared.

After a tumbleweed moment, it dawned on me that this query was using the RBO, rule-based optimizer.

So, this inferred that the tables in the subqueries did not have statistics whilst the outer table did. As explained by Wolfgang Breitling, if one table has statistics then the CBO will be used and I imagine that the CBO will have stuck some default figures into the calculations for the tables with statistics.

At this point, I should trace the 10053 event to see what numbers it is defaulting to, but annoyingly I don’t actually have access to the box and the trace files which would be produced.

I’ve already highlighted accurate statistics on the roadmap to arrive at a healthy system (in big bold letters).

In the meantime, the way forward is probably a set of hints to guide the CBO towards the plan that works well on the other system.

About these ads

One Response to Autotrace – no cost

  1. Pingback: Using Stored Outlines to figure out a hint or two « OraStory

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

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: