Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Statistics slowing down query

Re: Statistics slowing down query

From: Meyer <mjtollen_at_hotmail.com>
Date: 27 Oct 2003 14:32:10 -0800
Message-ID: <29c4958.0310271432.61d0517d@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US