Re: index fast full scan - why so slow and expensive

Chuck wrote:
> iamoracledba_at_gmail.com wrote in news:1107294624.883089.138330
> @f14g2000cwb.googlegroups.com:
>
> > Hi, I tried to post this at asktom.oracle.com but he was busy. So I
> > think I might try my luck here:
> >
> > I have a table with about 16m rows. When end users try to pull up
> > reports using the table, response is very slow, if they try to pull
up
> > more, cpu will spike up to 100%. I look at the query and explain
plan
> > on it, shows it is using index range scan, but when I query
> > v$session_longops almost every time it is actually using INDEX FAST
> > FULL SCAN - which is probably why it is slow: the index includes a
> > varchar2 column with 24 bytes, so it is quite a big index. I do
have
> > updated stats on the table - it is analyzed at 3am each day by a
> > database job using ANALYZE TABLE FOR TABLE XXX ESTIMATE STATISTICS
FOR
> > TABLE FOR ALL INDEXED COLUMNS
>
> Your analyze isn't analyzing the index. Change it to "analyze table
xxx
> estimate statistics for table FOR ALL INDEXES for all indexed
columns" and
> see if it makes a difference. Other suggestions may help too but I
like to
> try the simplest solution first.
>
> Better yet, use one of the DBMS_STATS procedures to gather your
statistics.
> ANALYZE still works but DBMS_STATS is what Oracle started
recommending in
> back in version 8.
> --
> Chuck

Thanks for all of your valuable input. ANALYZE TABLE XXX FOR ALL INDEXES is certainly an oversight on my part, and yes, dbms_stats is the next step in plan.

I am really impressed with the quality and quantity of feedback I got back on my first venture into this forum. Received on Wed Feb 02 2005 - 09:35:18 CST