Re: Problems with a plan

From: joel garry <joel-garry_at_home.com>
Date: Mon, 4 Apr 2011 09:06:48 -0700 (PDT)
Message-ID: <5af305ad-4603-4011-917f-654e6498daf5_at_34g2000pru.googlegroups.com>



On Apr 4, 8:28 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> I have a "top query" that is exhibiting a strange problem. The query
> looks like this:
>
> Select Article# From
> (
> Select
>   a.article#
> From
>   Articles A, Article_Tags At
> Where
>   A.Article# = At.Article# And
>   At.Tag# = 340384
>   Order By A.Published Desc
> ) where rownum < 30;
>
> The plan that Oracle comes with includes a full index scan on the very
> large ARTICLES table:
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT         |                    |    29 |   377 |  
> 4368
> (8)| 00:00:14 |
>
> |*  1 |  COUNT STOPKEY           |                    |       |       |
>    |          |
>
> |   2 |   VIEW                   |                    | 16198 |   205K|  
> 4368
> (8)| 00:00:14 |
>
> |*  3 |    SORT ORDER BY STOPKEY |                    | 16198 |   442K|  
> 4368
> (8)| 00:00:14 |
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
>
> |*  4 |     HASH JOIN            |                    | 16198 |   442K|  
> 4364
> (8)| 00:00:14 |
>
> |   5 |      TABLE ACCESS CLUSTER| ARTICLE_TAGS       | 16198 |  
> 205K|    10
> (0)| 00:00:01 |
>
> |*  6 |       INDEX UNIQUE SCAN  | TAG_CLU_IND        |     1 |      
> |     1
> (0)| 00:00:01 |
>
> |   7 |      INDEX FAST FULL SCAN| ARTICLES_PUBLISHED |  5293K|    75M|  
> 4210
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> (5)| 00:00:13 |
>
> --------------------------------------------------------------------------------
> ---------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    1 - filter(ROWNUM<30)
>    3 - filter(ROWNUM<30)
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
>    4 - access("A"."ARTICLE#"="AT"."ARTICLE#")
>    6 - access("AT"."TAG#"=340384)
>
> 22 rows selected.
>
> Elapsed: 00:00:00.11
> SQL>
>
> The FIRST_ROWS(30) hint should be an automatic consequence of using the
> ROWNUM<30 condition. This plan can only be improved with a direct USE_NL
> hint:
>
> explain plan for
> Select Article# From
> (
> Select /*+ USE_NL(A,AT) */
>   a.article#
> From
>   Articles A, Article_Tags At
> Wher  2  e
>   A.Article# = At.Article# And
>   At.T  3  ag# = 340384
>   Order By   4  A.Published Desc
> ) where rownum < 30;dbms_xplan.display);) where rownum < 30;
>
> select * from table(dbms_xplan.display);
>
> Explained.
>
> Elapsed: 00:00:00.08
> SQL> SQL>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> Plan hash value: 866945626
>
> --------------------------------------------------------------------------------
> ---------------
>
> | Id  | Operation                | Name               | Rows  | Bytes |
> Cost (%C
> PU)| Time     |
>
> --------------------------------------------------------------------------------
> ---------------
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT         |                    |    29 |   377 |
> 16256
> (1)| 00:00:49 |
>
> |*  1 |  COUNT STOPKEY           |                    |       |       |
>    |          |
>
> |   2 |   VIEW                   |                    | 16198 |   205K|
> 16256
> (1)| 00:00:49 |
>
> |*  3 |    SORT ORDER BY STOPKEY |                    | 16198 |   442K|
> 16256
> (1)| 00:00:49 |
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
>
> |   4 |     NESTED LOOPS         |                    | 16198 |   442K|
> 16252
> (1)| 00:00:49 |
>
> |   5 |      TABLE ACCESS CLUSTER| ARTICLE_TAGS       | 16198 |  
> 205K|    10
> (0)| 00:00:01 |
>
> |*  6 |       INDEX UNIQUE SCAN  | TAG_CLU_IND        |     1 |      
> |     1
> (0)| 00:00:01 |
>
> |*  7 |      INDEX RANGE SCAN    | ARTICLES_PUBLISHED |     1 |    15
> |     1
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
> (0)| 00:00:01 |
>
> --------------------------------------------------------------------------------
> ---------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    1 - filter(ROWNUM<30)
>    3 - filter(ROWNUM<30)
>
> PLAN_TABLE_OUTPUT
> --------------------------------------------------------------------------------
>    6 - access("AT"."TAG#"=340384)
>    7 - access("A"."ARTICLE#"="AT"."ARTICLE#")
>
> 22 rows selected.
>
> Elapsed: 00:00:00.12
> SQL>
>
> 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

Maybe they just don't want to even bother fixing the optimizer for clusters, figuring if you are smart enough to cluster, you don't need the optimizer dumbing you down. Ruminate on not-a-Bug 2971480: OPTIMIZER USE FULL TABLE SCAN INSTEAD OF HASH IN CLUSTER Hopefully that will get you mad enough at the support optimizer team to make me wrong.

jg

--
_at_home.com is bogus.
http://blogs.hbr.org/cs/2011/03/big_content_is_strangling_amer.html
Received on Mon Apr 04 2011 - 11:06:48 CDT

Original text of this message