Re: Problems with a plan

"Mladen Gogala" <no_at_email.here.invalid> wrote in message news:pan.2011.04.04.15.28.35_at_email.here.invalid...
>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 10.2.0.5, 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.
>
> --
> http://mgogala.byethost5.com

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 ?

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com