Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Statistics slowing down query
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
> --
If you have not changed the default...Try this
alter session set OPTIMIZER_INDEX_COST_ADJ=30;
then try your query and see the time/plan...
and try other values 10 20 .. 90 100
Changing this parameter globally (init.ora) should only be done after testing on your system... testing to get optimal value for all your sql. It will tell the optimizer the weight to give indexes in the execution plan.
Thanks,
Meyer
Received on Mon Oct 27 2003 - 16:32:10 CST