Re: Simple Oracle Query Taking Too Long

Bryan Hunter wrote:
> I have oracle 8.16 running on a sun enterprise 450 lots of ram and
> processor.
> I have a table abc with no index on it and 40 million rows in it.
> I am running the query "select * from abc where rownum = 1" and that takes
> 42 seconds to run. The table was analyzed this morning. I then applied and
> index on the leading column and re ran the same query, still take 42
> seconds. I then ran the same query but on table xyz with 80 million rows,
> two indexes and a primary key, it took .03 seconds to run. I looked at the
> explain plan on both and they are both table access full with a count
> stopkey on it. I am confused as to why this would be so slow. Any ideas?
>
> Bryan

You need to run a trace, because explain plan can lie.

I bet you are running with dictionary managed tables and have some really bizarro segments. This has quite possibly made the dictionary tables that track segments kookoo. Dan's mention of high water mark may be relevant, too. You may also have swiss-cheesed your data.

The pctincrease of 1 was a big mistake and should be rectified. You may have to recreate your database to do so, to fix the system tables that track segments.

You should look and see what Oracle is waiting on, too. Maybe some old transaction is forcing too much usage of undo.

jg

-- 
@home.com is bogus.
It's maaaaaaaagggggiiiiiiiccccccckkkkkkk.