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