Re: Problems with a plan

From: Jonathan Lewis <>
Date: Mon, 4 Apr 2011 18:53:36 +0100
Message-ID: <>

"Mladen Gogala" <> wrote in message
>I have a "top query" that is exhibiting a strange problem. The query
> looks like this:
> Now, my question is why is Oracle optimizer picking the plan with the
> full index scan? Costs of both plans is the same, which is also not quite
> clear to me. The hinted plan executes in 2 seconds, while the original
> plan takes full 27 seconds to execute. I thought that ROWNUM<n condition
> should imply an automatic FIRST_ROWS(n) hint, which, according to the
> documentation, should favor nested loops joins over the hash joins. It
> seems to me that CBO is skewed to prefer hash join in almost all cases.
> Not even FIRST_ROWS(1) hint helps. The version is, 64 bit.
> Table ARTICLE_TAGS is clustered with an another two tables, not the
> ARTICLES table.
> I experimented with various things, like setting OPTIMIZER_INDEX_CACHING
> to 0 (it's 70 by default), but nothing short of USE_NL hint actually
> helped.
> --

It looks to me as if there's an error somewhere in the statistics.

You have an index unique scan which gets one rowid from the index, then finds 16,198 rows in the table - the rest of the plan is the natural consequence of that error. (16,198 times round the nested loop is more expensive than the index fast full scan).

The num_rows < 30 / first_rows(30) is irrelevant - you have an order by clause inline, so the optimizer has to collect all the data and sort it before delivering the first 30 rows, so the plan is implicitly going to fall back to all_rows.

What's your setting for db_file_multiblock_read_count ? Setting your optimizer_index_caching to 100 might "help" - it should be 0 by default anyway.

What have you got in sys.aux_stats$ for your system statistics ?


Jonathan Lewis
Received on Mon Apr 04 2011 - 12:53:36 CDT

Original text of this message