Problems with a plan

#1

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
--------------------------------------------------------------------------------

Quote:

0 | SELECT STATEMENT | | 29 | 377 |
4368

(8)| 00:00:14 |

Quote:

* 1 | COUNT STOPKEY | | | |
|

2 | VIEW | | 16198 | 205K|
4368

(8)| 00:00:14 |

Quote:

* 3 | SORT ORDER BY STOPKEY | | 16198 | 442K|
4368

(8)| 00:00:14 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Quote:

* 4 | HASH JOIN | | 16198 | 442K|
4364

(8)| 00:00:14 |

Quote:

5 | TABLE ACCESS CLUSTER| ARTICLE_TAGS | 16198 |
205K| 10

(0)| 00:00:01 |

Quote:

* 6 | INDEX UNIQUE SCAN | TAG_CLU_IND | 1 |
1
(0)| 00:00:01 |

Quote:

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

--------------------------------------------------------------------------------
---------------

Quote:

PU)| Time |

--------------------------------------------------------------------------------
---------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Quote:

0 | SELECT STATEMENT | | 29 | 377 |
16256

(1)| 00:00:49 |

Quote:

* 1 | COUNT STOPKEY | | | |
|

2 | VIEW | | 16198 | 205K|
16256

(1)| 00:00:49 |

Quote:

* 3 | SORT ORDER BY STOPKEY | | 16198 | 442K|
16256

(1)| 00:00:49 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Quote:

4 | NESTED LOOPS | | 16198 | 442K|
16252

(1)| 00:00:49 |

Quote:

5 | TABLE ACCESS CLUSTER| ARTICLE_TAGS | 16198 |
205K| 10

(0)| 00:00:01 |

Quote:

* 6 | INDEX UNIQUE SCAN | TAG_CLU_IND | 1 |
1
(0)| 00:00:01 |

Quote:

* 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

#2

On Apr 4, 8:28*am, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:

Quote:

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
--
@home.com is bogus.
http://blogs.hbr.org/cs/2011/03/big_...ling_amer.html

#3

"Mladen Gogala" <no (AT) email (DOT) here.invalid> wrote

Quote:

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

#4

On Mon, 04 Apr 2011 18:53:36 +0100, Jonathan Lewis wrote:

Quote:

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

It looks like that. I will try analyzing the tables involved tonight.

Quote:

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.

Hmmm, thanks. I didn't realize that.

Quote:

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.

SQL> show parameter db_file_multiblo

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_file_multiblock_read_count integer
64
SQL> show parameter db_block_size

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_block_size integer
16384

Quote:

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

SQL> select pname,pval1 from sys.aux_stats$;

PNAME PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS 1
CPUSPEEDNW 1319.45496
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 3
MREADTIM 19.428
CPUSPEED 1232
MBRC 16

--
http://mgogala.byethost5.com

#5

On Mon, 04 Apr 2011 09:06:48 -0700, joel garry wrote:

Quote:

Hopefully that will get you mad enough at the support optimizer team to
make me wrong.

Joel, there is no need to get mad. When you cluster tables, they only
exist as a logical entities. The physical entity here is the cluster
segment. The data distribution within the cluster is much more sparse and
there are many more blocks to read. Cluster is a killer for full table
scans, full index scans and large range scans, as there are many more
blocks to read. Clustering tables will usually increase the level of your
indexes.
However, the 3 tables clustered together are almost always queried
together, based on the common key and the queries almost always request a
very small portion of the data. Clustering the tables was my ideas of how
to solve a performance problem, and the developers love this particular
cluster.
In this case, however, a full index scan was selected over a range scan.
Fortunately, I was able to help with that query. The good news is that
the plan is correct on the 11G staging database. It's only 10G production
and UAT that are having problems.

--
http://mgogala.byethost5.com

#6

On Mon, 04 Apr 2011 18:53:36 +0100, Jonathan Lewis wrote:

Quote:

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

The problem was with the statistics. Funny thing, I even pointed to the
right direction. One of the two tables in that query is clustered. For
some reasons, the database statistics collection job didn't gather the
stats for the cluster, just for the member tables. I ran the following:

"analyze cluster TAG_CLU estimate statistics
sample 5 percent for table for all indexed columns size 254"

and everything fell in its right place. I'll check whether there are any
bugs with the statistics collection and clusters, when using DBMS_STATS,
but even if there are, setting up a job which will do that separately
shouldn't be a big deal.

--
http://mgogala.byethost5.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Problems with a plan

comp.databases.oracle.server comp.databases.oracle.server

Problems with a plan - 04-04-2011 , 10:28 AM

Re: Problems with a plan - 04-04-2011 , 11:06 AM

Re: Problems with a plan - 04-04-2011 , 12:53 PM

Re: Problems with a plan - 04-04-2011 , 01:41 PM

Re: Problems with a plan - 04-05-2011 , 08:03 AM

Re: Problems with a plan - 04-05-2011 , 06:58 PM