Path: news.cambrium.nl!textnews.cambrium.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!194.134.4.91.MISMATCH!news2.euro.net!feeder.news-service.com!216.196.110.146.MISMATCH!border3.nntp.ams.giganews.com!Xl.tags.giganews.com!border1.nntp.ams.giganews.com!nntp.giganews.com!local2.nntp.ams.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail
NNTP-Posting-Date: Mon, 04 Apr 2011 12:52:42 -0500
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
References: <pan.2011.04.04.15.28.35@email.here.invalid>
Subject: Re: Problems with a plan
Date: Mon, 4 Apr 2011 18:53:36 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
X-RFC2646: Format=Flowed; Original
Message-ID: <8b-dndAeI7X3mAfQnZ2dnUVZ8rCdnZ2d@bt.com>
Lines: 50
X-Usenet-Provider: http://www.giganews.com
X-AuthenticatedUsername: NoAuthUser
X-Trace: sv3-xspU87xRc4J0iSd00nXG4hq+CvWjMdXVvZXXw6G1agSofvekCu9itUwpoC7djStMHLX0ZZmi+yEJKh+!Q5cAO7mJHSM00iGzHb7Ls4l6NKZ3e+peHewRgMoSqfYrYx2ZOCtSTHLRayjFwInpFsQNouvQJ4U=
X-Complaints-To: abuse@btinternet.com
X-DMCA-Complaints-To: abuse@btinternet.com
X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly
X-Postfilter: 1.3.40
X-Original-Bytes: 3108
Xref:  news.cambrium.nl


"Mladen Gogala" <no@email.here.invalid> wrote in message 
news:pan.2011.04.04.15.28.35@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