Oracle Scratchpad

January 10, 2010

first_rows_N again

Filed under: CBO,Performance,Tuning — Jonathan Lewis @ 7:02 pm BST Jan 10,2010

If you run a query using first_rows_N optimisation you could run into a massive performance problem in cases where the optimizer thinks the complete result set is quite large when it is actually very small.

If both conditions are true the optimizer may choose a very resource-intensive execution path “expecting” to stop (or at least pause between fetches) after N rows – hoping to give the impression that it can respond very quickly – but find that the query has to run to completion because the N rows simply don’t exist.

As a simple thought experiment, consider the query:


select  /*+ first_rows(1) */
        *
from
        t1
where
        flag = 'X'
;

Assume t1 holds 1,000,000 rows at roughly 50 per block (for a total of 20,000 blocks). Now assume that the optimizer thinks, for whatever reason, that 100,000 rows (one in ten) have the value ‘X’ for coumn flag.

Based on this information the optimizer will estimate that there are five suitable rows in every block in the table and may therefore choose to do a tablescan because that will be the fastest way to return one row from the table. Unfortunately if there are no rows matching the predicate your query is going to have to scan the whole table to return no rows.

In a variation on the same theme – maybe there really are 100,000 rows that match the predicate, but if they are all located in the last 2,000 blocks of the table, Oracle will have to scan 18,000 blocks before it finds the first row.

Cases like this may be a little unusual – at least on paper – but they do happen. Remember, though, that the two basic optimisation questions will tell you what’s going on if you see a performance problem like this: “how much data”, “where is it”.

Further reading:

rownum effects and first_rows_n, and an example of the problem in this item on the OTN database forum.

5 Comments »

  1. I come across this every now and then, usually when running adhoc queries. A variation is with a ROWNUM predicate, e.g. WHERE ROWNUM<=5; if there are only 4 matching rows in the table, the query will not return until it's finished the full scan.

    Comment by Jeffrey Kemp — January 11, 2010 @ 2:25 am BST Jan 11,2010 | Reply

  2. Hello Sir,

    who about dynamic sampling will it help in this scenario?

    Thanks

    Comment by Henish — January 19, 2010 @ 9:00 pm BST Jan 19,2010 | Reply

  3. another problem with first rows i frequently see (at least on 10.2.0.3 and 10.0.2.4)

    select
    from
    where
    very_selective=bind
    order by field1, field2

    where there is an index on (field1,field2), but the query does not filter on these in any way. there is also an index on very_selective.

    now to honour the first rows, oracle seems to like the index (field1,field2) to get the data ‘already sorted’, and then filters out the very_selective field, ignoring the index on it. it takes ages. we have to force the plan; accurate statistics do not help here. the first rows is a mandatory setting enforced by the application… any ideas on the mechanics here?

    Comment by Jan-Marten Spit — January 27, 2010 @ 11:36 pm BST Jan 27,2010 | Reply

  4. [...] 23-Possible reason begin poor performing first_rows_n ? Jonathan Lewis- first_rows_N again [...]

    Pingback by Blogroll Report 08/01/2009 – 15/01/2010 « Coskan’s Approach to Oracle — February 2, 2010 @ 6:58 pm BST Feb 2,2010 | Reply

  5. [...] pointed out that a predicate like “rownum <= N" generally makes the optimizer use “first_rows(N)” optimisation methods – known in the code as first_k_rows [...]

    Pingback by Rownum effects « Oracle Scratchpad — September 30, 2010 @ 6:43 pm BST Sep 30,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,014 other followers