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

"lsllcm" <lsllcm_at_gmail.com> wrote in message news:871566ee-22ef-4895-bcc6-753269a7bc3a_at_f18g2000prf.googlegroups.com...
> The trace file is too long, I use three parts.
>
> *************************************
> PARAMETERS WITH ALTERED VALUES
> ******************************
> Compilation Environment Dump
> optimizer_features_enable = 11.1.0.6
> 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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html