Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Statistics slowing down query
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