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 -> Statistics slowing down query

Statistics slowing down query

From: Michael Rothwell <marothwellRemoveThis_at_yahoo.com>
Date: Thu, 23 Oct 2003 15:53:22 -0700
Message-ID: <WIydnWi2af6SwQWiRVn-ig@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 - 17:53:22 CDT

Original text of this message

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