i thought i saw a post recently that asserted that with 9.2 and statistics
the optimizer is so good that we will never need to use hints anymore --
sounds like you have ran smack dab into neverland
one possibility is that the optimizer now believes that for the size of your
tables and the complexity of the joins, everything would happen faster in
memory -- i have heard that this can sometimes be a side-affect of larger
the SORT_AREA_SIZE settings
--
----------------------------------------
Mark C. Stock
www.enquery.com
(888) 512-2048
"Michael Rothwell" <marothwellRemoveThis_at_yahoo.com> wrote in message
news:WIydnWi2af6SwQWiRVn-ig_at_comcast.com...
> I have a very complex query hitting a view (that is a very complex query
> joining several tables) in an Oracle 9.2 db. On my test box (not a lot
> of memory and not a very big cpu) when I run this query, I get a
> resultset in about 1:05. I then analyzed the tables and indexes, it now
> takes over 3 minutes to retrieve the same data.
>
> I then looked at the cost in the query plan. Before analyzing - all the
> the tables were being accessed by index (range-scan) and most of my cost
> was in the nested loops (most ranging between 10 and 20). After
> analyzing, I see many tables accessed by full-scan. The cost of table
> access didn't change much, but the nested loop cost jumped to the 5000 -
> 9000 range. What in the world happened?
>
> Michael
> --
>
Received on Thu Oct 23 2003 - 19:55:02 CDT
