Re: performance issue after upgrade to oracle linux 32 bit.

From: Jonathan Lewis <>
Date: Sat, 7 Nov 2009 09:31:52 -0000
Message-ID: <>

"lsllcm" <> wrote in message
> The trace file is too long, I use three parts.
> *************************************
> ******************************
> Compilation Environment Dump
> optimizer_features_enable =
> optimizer_mode = first_rows_100
> optimizer_index_cost_adj = 10
> optimizer_index_caching = 90
> _optimizer_cost_based_transformation = off

Sorry about picking this one up so late.

I've had a quick read through the posts I can see at the moment. The critical issue is (as Charles and Randolf have pointed out) the use of first_rows_100, combined with Randolf's observation that the expected result set is 40 million rows.

>> First K Rows: K = 100.00, N = 40640900.00
>> First K Rows: Setup end

When you don't have the order by clause, Oracle is working on a plan that will pick up any 100 rows as quickly as possible. When you add the order by the optimizer "knows" it has to acquire 40M rows and sort them before returning the first 100. Consequently any path that avoids doing the sorting is likely to be a good path - hence the choice of index to drive the query.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Sat Nov 07 2009 - 03:31:52 CST

Original text of this message