11g bug with optimizer_mode=first_rows_N
- From: Charles Schultz <sacrophyte@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 May 2011 01:32:40 -0500
Good day, listers,
Back in March, I asked Kerry Osborne some questions:
http://kerryosborne.oracle-guy.com/2010/04/funny-developer-tricks-first_rows/#comment-38234
In the 10053 trace output for first_rows(10) (above), we see:
—————————————–
BEGIN Single Table Cardinality Estimation
—————————————–
Table: SKEW Alias: A
Card: Original: 10 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00
Is it expected that the estimated cardinality of a table is equal to the
optimizer_mode in this case? From what I understand, first_rows is kind of
‘fooling’ the CBO into thinking that a set number of rows may (or may not)
be returned, but if first_rows artificially effects the cardinality of a
rowsource, it would seem that more query plans would not be optimal. For
example, choosing a table with an estimated cardinality of 10 when it really
has 10000000 rows to be the inner table of a nested loop join.
This evening, Oracle updated my SR and told me that this is now going
through BDE as bug 11858963. I can understand first_rows_N working like a
shortcut where you tell Oracle you only want the first N rows from the last
operation. But to propagate the assumption about the number of rows to each
and every single operation seems a bit strange to me.
PS - As I mentioned in Kerry's blog, I am very much grateful for the work he
and his peers have done. My point in bring this to oracle-l is basically
just to make folks aware of the bug.
--
Charles Schultz
Other related posts:
- » 11g bug with optimizer_mode=first_rows_N - Charles Schultz
