Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!z14g2000cwz.googlegroups.com!not-for-mail
From: iamoracledba@gmail.com
Newsgroups: comp.databases.oracle.server
Subject: Re: index fast full scan - why so slow and expensive
Date: 2 Feb 2005 07:35:18 -0800
Organization: http://groups.google.com
Lines: 46
Message-ID: <1107358518.897127.225380@z14g2000cwz.googlegroups.com>
References: <1107294624.883089.138330@f14g2000cwb.googlegroups.com>
   <Xns95F158A5DD31Fskiloversofthomenet@130.133.1.4>
NNTP-Posting-Host: 65.242.122.170
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1107358522 2302 127.0.0.1 (2 Feb 2005 15:35:22 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 2 Feb 2005 15:35:22 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: z14g2000cwz.googlegroups.com; posting-host=65.242.122.170;
   posting-account=fAM7XQ0AAABAuHWI00FyijdHHKW5jI8r
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:234803

Chuck wrote:
> iamoracledba@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.